Home > 강좌 > 엑셀수식강좌 > 수식에서 셀과 범위 참조 방식 > 엑셀에서 3차원 참조로 한번에 자료 집계하기

엑셀에서 3차원 참조로 한번에 자료 집계하기

엑셀에서 3차원 참조란?

 

엑셀에서 3차원 참조는 다음 그림과 같이 여러 시트에 있는 값을 하나의 시트로 집계할 때 참조하는 방식입니다.

만약 3차원 참조를 사용하지 않고 집계를 한다면 다음과 같이 시트별로 셀의 위치를 전부 입력해야 합니다.

=SUM('1월판매'!C3+'2월판매'!C3+'3월판매'!C3+'4월판매'!C3)

3차원 참조를 이용하면 다음과 같이 간단히 해결할 수 있습니다.

=SUM('1월판매:4월판매'!C3)

이 수식을 잘 보시면 [시작 시트:마지막 시트!셀 위치]의 형식으로 되어 있습니다.  값을 집계할 셀 위치는 마지막에 한번만 입력됩니다. 따라서 참조하는 여러 시트는 같은 레이아웃으로 되어 있어야 합니다.

 

아래의 실습용 엑셀파일을 다운로드해서 따라해 보세요.

실습용 엑셀파일 다운로드 : 엑셀참조-다른시트참조_3차원참조.xlsx

 

1) 여러 시트를 범위를 3차원 참조를 이용하여 합계 계산

다음과 같이 [다른시트참조] 시트에서 [1월판매], [2월판매], [3월판매], [4월판매] 시트의 [C3]셀의 값을 3차원으로 참조하여 합계를 계산하는 방법을 구체적으로 알아 보겠습니다.

엑셀에서 3차원 참조하기

 

첫번째로 다음과 같이 [다른시트참조]시트의 [C9]셀에 '=SUM('까지 입력합니다.

엑셀에서 3차원 참조하기

 

다음으로 마우스로 [1월판매] 시트를 클릭한 후 [C3] 셀을 선택합니다.

엑셀에서 3차원 참조하기

 

[Shift] 누른채로 마지막 [4월판매]시트선택, [Shift] 놓고 수식의 괄호를 닫고 [Enter]를 누르면

엑셀에서 3차원 참조하기

 

자동으로 수식을 입력한 시트로 돌아오고 다음 수식의 결과가 표시됩니다.

=SUM('1월판매:4월판매'!C3)

엑셀에서 3차원 참조하기

SUM함수의 인수가 ['1월판매:4월판매'!C3]의 형식으로 되어 있습니다.

중간에 있는 :(콜론)은 콜론 앞뒤의 모든 범위를 연결하여 하나의 참조를 만드는  '범위 연산자'입니다.

따라서 이 수식은 다음 그림과 같이 '[1월판매] 시트부터 [4월판매] 시트 사이에 있는 모든 [C3] 셀의 값을 더하라'는 의미입니다.

엑셀에서 3차원 참조하기

 

2) 여러 시트를 범위를 3차원 참조를 이용하여 상반기실적 집계하기

이번에는 위에서 살펴본 월별 시트를 그대로 사용하여 아래와 같이 실무에서 많이 사용하는 '상반기(1월~6월) 판매실적 집계'를 만들어 보겠습니다.

3차원 참조를 이용하여 상반기실적 집계하기

 

첫번째로 다음과 같이 [판매실적집계]시트의 [C3]셀에 '=SUM('까지 입력합니다.

3차원 참조를 이용하여 상반기실적 집계하기

 

다음으로 마우스로 [1월판매] 시트를 클릭한 후 [C3] 셀을 선택합니다.

3차원 참조를 이용하여 상반기실적 집계하기

 

[Shift] 누른 채로 마지막 [6월판매]시트선택, [Shift] 놓고 수식의 괄호를 닫고 [Enter]를 누르면

3차원 참조를 이용하여 상반기실적 집계하기

 

자동으로 수식을 입력한 시트로 돌아오고 다음 수식의 결과가 표시됩니다.

=SUM('1월판매:6월판매'!C3)

3차원 참조를 이용하여 상반기실적 집계하기

 

[C3] 셀에 입력된 수식을 복사하여 전체 범위 [C3:G7]를 선택하여 붙여넣기 합니다. [판매실적합계] 시트와 월별 시트의 레이아웃이 동일하므로 거래처별, 상품별로 월별 집계가 구해집니다.

3차원 참조를 이용하여 상반기실적 집계하기

 

최종적으로 다음과 같이 [판매실적집계] 시트가 완성되었습니다.

3차원 참조를 이용하여 상반기실적 집계하기

 

[더 알아보기]

이번 예에서는 SUM함수에서 3차원 참조를 사용했는데 엑셀의 모든 함수가 3차원 참조를 사용할 수 있는 것은 아니고 다음 함수만 가능합니다.

SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA

 

[주의!] 3차원 참조를 사용할 때 주의 할 점

3차원 참조는 수식에서 지정한 처음 시트와 마지막 시트 사이의 모든 시트를 계산합니다. 따라서 시트가 중간에 삽입되거나 지정한 시트 범위의 밖으로 이동하면 계산에 제외됩니다.

<지정된 범위내에 시트가 추가된 경우>

엑셀에서 3차원 참조하기

 

<지정된 범위밖으로 시트가 이동된 경우>

엑셀에서 3차원 참조하기

 

시트명을 입력할 때 주의할 점

다른 시트를 참조하여 시트명을 입력할 때 시트를 마우스로 선택하면 자동으로 시트이름이 입력되므로 크게 주의할 필요가 없으나 가끔 직접 입력해야 하는 경우에 주의가 필요합니다.

일반적인 경우에는 다음과 같이 시트명을 입력합니다.

=SUM(판매실적!C3:C7)

시트명!범위의 형식입니다.

 

만약 시트의 이름이 다음과 같은 경우에는 홑따옴표로 감싸야 합니다.

  • 숫자로 시작하는 시트이름 (예 =SUM('1월판매'!C3:C7))
  • 공백이 포함된 시트이름 (예 =SUM('판매 실적'!C3:C7))
  • 하이픈,괄호,달러표시,콤마 등 특수문자가 포함된 시트이름 (예 =SUM('전체-판매실적'!C3:C7))
  • 셀주소 형식이 포함된 시트이름 (예 =SUM('A1'!C3:C7) 또는=SUM('R1C1'!C3:C7) )

 

 


엑셀 수식 강좌 

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

 

 

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: