엑셀에서 3차원 참조란?
엑셀에서 3차원 참조는 다음 그림과 같이 여러 시트에 있는 값을 하나의 시트로 집계할 때 참조하는 방식입니다.
만약 3차원 참조를 사용하지 않고 집계를 한다면 다음과 같이 시트별로 셀의 위치를 전부 입력해야 합니다.
=SUM('1월판매'!C3+'2월판매'!C3+'3월판매'!C3+'4월판매'!C3)
3차원 참조를 이용하면 다음과 같이 간단히 해결할 수 있습니다.
=SUM('1월판매:4월판매'!C3)
이 수식을 잘 보시면 [시작 시트:마지막 시트!셀 위치]의 형식으로 되어 있습니다. 값을 집계할 셀 위치는 마지막에 한번만 입력됩니다. 따라서 참조하는 여러 시트는 같은 레이아웃으로 되어 있어야 합니다.
아래의 실습용 엑셀파일을 다운로드해서 따라해 보세요.
1) 여러 시트를 범위를 3차원 참조를 이용하여 합계 계산
다음과 같이 [다른시트참조] 시트에서 [1월판매], [2월판매], [3월판매], [4월판매] 시트의 [C3]셀의 값을 3차원으로 참조하여 합계를 계산하는 방법을 구체적으로 알아 보겠습니다.
첫번째로 다음과 같이 [다른시트참조]시트의 [C9]셀에 '=SUM('까지 입력합니다.
다음으로 마우스로 [1월판매] 시트를 클릭한 후 [C3] 셀을 선택합니다.
[Shift] 누른채로 마지막 [4월판매]시트선택, [Shift] 놓고 수식의 괄호를 닫고 [Enter]를 누르면
자동으로 수식을 입력한 시트로 돌아오고 다음 수식의 결과가 표시됩니다.
=SUM('1월판매:4월판매'!C3)
SUM함수의 인수가 ['1월판매:4월판매'!C3]의 형식으로 되어 있습니다.
중간에 있는 :(콜론)은 콜론 앞뒤의 모든 범위를 연결하여 하나의 참조를 만드는 '범위 연산자'입니다.
따라서 이 수식은 다음 그림과 같이 '[1월판매] 시트부터 [4월판매] 시트 사이에 있는 모든 [C3] 셀의 값을 더하라'는 의미입니다.
2) 여러 시트를 범위를 3차원 참조를 이용하여 상반기실적 집계하기
이번에는 위에서 살펴본 월별 시트를 그대로 사용하여 아래와 같이 실무에서 많이 사용하는 '상반기(1월~6월) 판매실적 집계'를 만들어 보겠습니다.
첫번째로 다음과 같이 [판매실적집계]시트의 [C3]셀에 '=SUM('까지 입력합니다.
다음으로 마우스로 [1월판매] 시트를 클릭한 후 [C3] 셀을 선택합니다.
[Shift] 누른 채로 마지막 [6월판매]시트선택, [Shift] 놓고 수식의 괄호를 닫고 [Enter]를 누르면
자동으로 수식을 입력한 시트로 돌아오고 다음 수식의 결과가 표시됩니다.
=SUM('1월판매:6월판매'!C3)
[C3] 셀에 입력된 수식을 복사하여 전체 범위 [C3:G7]를 선택하여 붙여넣기 합니다. [판매실적합계] 시트와 월별 시트의 레이아웃이 동일하므로 거래처별, 상품별로 월별 집계가 구해집니다.
최종적으로 다음과 같이 [판매실적집계] 시트가 완성되었습니다.
[더 알아보기]
이번 예에서는 SUM함수에서 3차원 참조를 사용했는데 엑셀의 모든 함수가 3차원 참조를 사용할 수 있는 것은 아니고 다음 함수만 가능합니다.
SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA
[주의!] 3차원 참조를 사용할 때 주의 할 점
3차원 참조는 수식에서 지정한 처음 시트와 마지막 시트 사이의 모든 시트를 계산합니다. 따라서 시트가 중간에 삽입되거나 지정한 시트 범위의 밖으로 이동하면 계산에 제외됩니다.
<지정된 범위내에 시트가 추가된 경우>
<지정된 범위밖으로 시트가 이동된 경우>
시트명을 입력할 때 주의할 점
다른 시트를 참조하여 시트명을 입력할 때 시트를 마우스로 선택하면 자동으로 시트이름이 입력되므로 크게 주의할 필요가 없으나 가끔 직접 입력해야 하는 경우에 주의가 필요합니다.
일반적인 경우에는 다음과 같이 시트명을 입력합니다.
=SUM(판매실적!C3:C7)
시트명!범위의 형식입니다.
만약 시트의 이름이 다음과 같은 경우에는 홑따옴표로 감싸야 합니다.
- 숫자로 시작하는 시트이름 (예 =SUM('1월판매'!C3:C7))
- 공백이 포함된 시트이름 (예 =SUM('판매 실적'!C3:C7))
- 하이픈,괄호,달러표시,콤마 등 특수문자가 포함된 시트이름 (예 =SUM('전체-판매실적'!C3:C7))
- 셀주소 형식이 포함된 시트이름 (예 =SUM('A1'!C3:C7) 또는=SUM('R1C1'!C3:C7) )
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
