이 글에서는 엑셀에서 시트 이름을 활용해 3D 참조(3D Reference)를 만드는 7가지 방법을 소개합니다. 3D 참조는 여러 시트에 나눠 저장된 매출이나 이익 데이터를 대상으로 합계, 평균 등 다양한 통계 값을 한 번에 구할 때 매우 유용한 기능입니다.
이번 예제에서는 자동차 회사들의 월별 이익 데이터를 사용합니다. 각 월의 데이터는 Month1, Month2, Month3, Month4라는 이름의 개별 시트에 저장되어 있습니다.
시트 이름으로 3D 참조 만드는 7가지 방법
1. AVERAGE 함수로 3D 참조 만들기
AVERAGE 함수는 3D 참조를 만들 때 가장 많이 사용되는 함수 중 하나입니다. 월별 이익이 저장된 여러 시트에 각각 이름을 지정한 뒤, 시트 이름을 이용해 4개월간 이익의 평균을 구해 보겠습니다. 아래 순서대로 따라 해 보세요.
단계:
- 먼저 평균 결과를 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=AVERAGE(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 모든 시트의 C5 셀에 입력된 이익 값의 평균을 반환합니다.
- Enter 키를 누르면 포르쉐(Porsche)의 평균 이익이 표시됩니다.
- 이후 채우기 핸들(Fill Handle)을 이용해 아래 셀까지 자동으로 채웁니다(AutoFill).
이렇게 하면 AVERAGE 함수와 시트 이름을 활용한 3D 참조가 완성되고, 각 회사의 평균 이익을 손쉽게 확인할 수 있습니다.
2. SUM 함수로 3D 참조 만들기
3D 참조를 만드는 데 사용할 수 있는 또 다른 함수는 SUM 함수입니다. 마찬가지로 월별 이익이 저장된 시트 이름을 이용해 4개월간 이익의 합계를 구해 보겠습니다.
단계:
- 먼저 합계를 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=SUM(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 모든 시트의 C5 셀 이익 값의 합계(총액)를 반환합니다.
- Enter 키를 누르면 포르쉐의 이익 합계가 표시됩니다.
- 채우기 핸들을 이용해 아래 셀까지 자동으로 채웁니다.
이렇게 SUM 함수를 활용하면 여러 시트에 흩어진 이익 데이터의 총합을 한 번에 계산할 수 있습니다.
3. MAX 함수로 3D 참조 만들기
MAX 함수를 사용하면 지정된 기간 동안의 최댓값을 구할 수 있습니다. 시트 이름을 이용해 4개월 중 가장 큰 이익을 찾아 보겠습니다.
단계:
- 먼저 최대 이익을 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=MAX(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 모든 시트의 C5 셀 이익 값 중 가장 큰 값을 반환합니다.
- Enter 키를 누르면 포르쉐의 최대 이익이 표시됩니다.
- 채우기 핸들을 이용해 아래 셀까지 자동으로 채웁니다.
이렇게 MAX 함수를 활용하면 해당 기간 동안 각 회사가 기록한 최대 이익을 빠르게 파악할 수 있습니다.
4. PRODUCT 함수로 3D 참조 만들기
3D 참조에 활용할 수 있는 또 다른 함수는 PRODUCT 함수입니다. 이 함수는 여러 값들을 곱한 결과를 반환하며, 주로 통계 분석에 활용됩니다.
단계:
- 먼저 곱셈 결과를 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=PRODUCT(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 모든 시트의 C5 셀 이익 값을 모두 곱한 결과를 반환합니다.
- Enter 키를 누르면 포르쉐의 이익 곱셈 결과가 표시됩니다.
- 채우기 핸들을 이용해 아래 셀까지 자동으로 채웁니다.
곱셈 결과값은 상당히 큰 숫자가 되므로, 읽기 편하게 단위 형식을 바꿔 보겠습니다. 이 단계는 선택 사항입니다.
- 숫자 서식(Number Format) >> 추가 번호 서식(More Number Formats…)으로 이동합니다.
- 사용자 지정(Custom)을 선택합니다.
- '형식(Type):' 입력란에 $0.00,,,,,,Q를 입력합니다.
- 확인(OK)을 클릭합니다.
이제 이익의 곱셈 결과가 퀸틸리언(10^18) 단위로 깔끔하게 표시됩니다. 데이터의 곱셈 값은 주로 통계적 응용에 유용하게 쓰입니다.
5. COUNT 함수로 3D 참조 만들기
COUNT 함수를 사용하면 숫자가 들어 있는 셀의 개수를 셀 수 있습니다. 예제에서는 이익 집계 기간이 몇 개월인지, 즉 몇 개의 시트에 데이터가 있는지 세어 보겠습니다.
단계:
- 먼저 월 수를 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=COUNT(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 범위 내에 숫자 데이터가 있는 셀의 개수를 반환하며, 모든 회사에 대해 4가 표시됩니다.
- Enter 키를 누르면 월 수가 표시됩니다.
- 채우기 핸들을 이용해 아래 셀까지 자동으로 채웁니다.
이렇게 COUNT 함수를 활용하면 이익 집계 기간이 몇 개월인지 간편하게 확인할 수 있습니다.
6. STDEV 함수로 3D 참조 만들기
STDEV 함수를 사용하면 여러 시트에 걸친 데이터의 표준 편차를 구할 수 있습니다. 표준 편차는 값들이 평균으로부터 얼마나 흩어져 있는지를 나타내는 통계 지표입니다.
단계:
- 먼저 표준 편차를 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=STDEV(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 모든 시트의 C5 셀 이익 값에 대한 표준 편차를 반환합니다.
- Enter 키를 누르면 포르쉐 이익의 표준 편차가 표시됩니다.
- 채우기 핸들을 이용해 아래 셀까지 자동으로 채웁니다.
이렇게 STDEV 함수를 활용하면 각 회사 이익 변동의 폭을 정량적으로 분석할 수 있습니다.
7. VAR 함수로 3D 참조 만들기
VAR 함수를 사용하면 여러 시트에 걸친 데이터의 분산(Variance)을 구할 수 있습니다. 분산은 표준 편차의 제곱으로, 데이터의 변동성을 나타냅니다.
단계:
- 먼저 분산을 저장할 새 시트를 만듭니다.
- 다음 수식을 C5 셀에 입력합니다.
=VAR(Month1:Month4!C5)
이 수식은 Month1부터 Month4까지 모든 시트의 C5 셀 이익 값에 대한 분산을 반환합니다.
- Enter 키를 누르면 포르쉐 이익의 분산이 표시됩니다.
- 채우기 핸들을 이용해 아래 셀까지 자동으로 채웁니다.
이렇게 VAR 함수를 활용하면 각 회사 이익 데이터의 변동성까지 함께 분석할 수 있습니다.
주의할 점
- 시트 순서를 반드시 유지해야 합니다. 시트 배치 순서가 바뀌면 이 글에서 사용한 수식은 잘못된 결과를 반환할 수 있습니다. 예를 들어 아래와 같이 시트 순서가 어긋나면 계산 오류가 발생합니다.
- 이 외에도 3D 참조를 만들 때 사용할 수 있는 함수들이 더 있습니다. 아래 목록을 참고하세요.
- AVERAGEA 함수
- COUNTA 함수
- MAXA 함수
- MIN 함수
- MINA 함수
- STDEVA 함수
- STDEVP 함수
- STDEVPA 함수
- VARA 함수
- VARP 함수
- VARPA 함수
연습하기
아래는 이 글에서 사용한 데이터 세트입니다. 직접 내려받아 위의 수식들을 연습해 보세요.
결론
지금까지 엑셀에서 시트 이름을 활용해 3D 참조를 만드는 방법을 7가지 함수 예제와 함께 살펴보았습니다. 3D 참조를 활용하면 여러 시트에 흩어진 데이터를 일일이 복사하거나 수식을 반복 작성할 필요 없이, 시간을 크게 절약하면서 통계 값을 계산할 수 있습니다. 이 글이 도움이 되었다면 댓글로 의견을 남겨 주세요. 소중한 피드백은 다음 콘텐츠 제작에 큰 도움이 됩니다.