엑셀에서 3차원 참조로 여러 시트 데이터 합계하기

글의 목적 : 본 글은 엑셀 3차원 참조(3-D Reference)를 이용하여 여러 시트의 동일 위치 데이터를 한 번에 합계하고 평균 등으로 요약하는 정확한 실무 방법을 정리하는 것이다. 문법, 활용 패턴, 한계, 대안, 오류 예방까지 현장에서 바로 적용할 수 있게 예제 중심으로 설명한다.

3차원 참조 개념과 문법

3차원 참조는 연속된 시트 범위의 같은 셀 또는 같은 범위를 하나의 수식으로 집계하는 기능이다. 시트가 가로축으로 추가되어도 수식을 고치지 않고 합계를 갱신할 수 있어 월별·지점별 시트 집계에 적합하다.

기본 문법

=SUM(시작시트:끝시트!B2)
=AVERAGE(시작시트:끝시트!B2:B10)
=MAX('Sheet 1:Sheet 3'!C5)   

요소	설명	예시
시작시트:끝시트	연속된 시트 범위이다. 시작시트와 끝시트 사이의 모든 시트를 포함한다.	Jan:Dec, '지점A:지점F'
!	시트 영역과 셀/범위를 구분한다.	Jan:Dec!B3
셀/범위	모든 시트에서 동일 위치를 가리킨다.	B3, B3:D8
따옴표	공백·특수문자 포함 시트명에 필요하다.	'Sheet 1:Sheet 3'!A1

주의 : 3차원 참조는 연속 시트만 가능하다. 떨어진 시트들을 한 번에 지정하는 문법은 지원하지 않는다.

빠른 시작: 월별 시트 합계

월별 시트 Jan~Dec가 있고 모든 시트의 B2에 매출이 입력되어 있다고 가정한다.

=SUM(Jan:Dec!B2)   

시나리오	수식	설명
월별 매출 합계	=SUM(Jan:Dec!B2)	12개 시트의 B2 합계를 구한다.
월별 매출 평균	=AVERAGE(Jan:Dec!B2)	12개 시트의 B2 평균을 구한다.
항목범위 합계	=SUM(Jan:Dec!B2:B10)	모든 시트의 동일 범위를 누적한다.

경계 시트(북마크 시트) 패턴

시트가 수시로 추가될 때는 경계 시트를 두고 그 사이에 집계 대상 시트를 끼워 넣는 패턴이 안전하다.

1. 맨 앞에 ▶Start, 맨 뒤에 ▶End라는 빈 시트를 만든다.
2. 대상 시트는 항상 두 경계 사이에 배치한다.
3. 집계 수식은 다음과 같이 작성한다.

=SUM('▶Start:▶End'!D5)   

• 새 시트를 ▶Start와 ▶End 사이에 끼우면 수식이 자동 반영된다.
• 대상 시트를 범위 밖으로 이동하면 집계에서 제외된다.

지원 함수와 제한 사항

구분	지원	예시	비고
단순 집계	지원	SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, PRODUCT, STDEV, VAR	대표 집계 함수 대부분이 동작한다.
조건 집계	미지원	SUMIF, COUNTIF, AVERAGEIF	시트 범위를 인수로 받지 못한다.
조회/매칭	미지원	XLOOKUP, VLOOKUP, INDEX/MATCH	3차원 참조 문법이 아님
동적 배열	간접 지원	LET, LAMBDA, REDUCE, MAP	INDIRECT와 조합하여 우회한다.

요약 : 조건이 필요 없으면 3차원 참조만으로 끝난다. 조건이 필요하면 INDIRECT 기반 우회식 또는 데이터 통합·Power Query를 고려한다.

조건부 합계 필요 시 두 가지 접근

방법 A. 시트별 조건합계를 합산

모든 시트가 동일한 구조를 갖고, A열 품목, B열 금액이 있다고 가정한다. 기준 품목은 요약시트의 E2이다.

=SUM(
  SUMIF(Jan!A:A, $E2, Jan!B:B),
  SUMIF(Feb!A:A, $E2, Feb!B:B),
  SUMIF(Mar!A:A, $E2, Mar!B:B)
)   

• 시트 수가 적을 때 단순하고 빠르다.
• 시트가 늘면 수식을 늘려야 하므로 유지보수가 어렵다.

방법 B. 시트목록 + INDIRECT + SUMIF + SUMPRODUCT

요약시트 A2:A13에 합산할 시트명을 나열한다. 예: Jan, Feb, …, Dec

=SUMPRODUCT(
  SUMIF(INDIRECT("'"&$A$2:$A$13&"'!A:A"), $E2,
        INDIRECT("'"&$A$2:$A$13&"'!B:B"))
)   

• 시트가 추가되면 목록에만 이름을 추가하면 된다.
• INDIRECT는 변동 함수라 재계산 비용이 증가한다.

성능 팁 : 전열 참조 대신 고정 범위를 권장한다. 예를 들어 A:A 대신 A2:A1000을 사용한다.

동적 배열로 깔끔하게 합계: LAMBDA + REDUCE

Office 365의 동적 배열과 LAMBDA를 활용하면 가독성이 높아진다. 이름 관리자에서 SheetList에 시트명 범위 =A2:A13을 정의한다고 가정한다.

=LET(
  sList, SheetList,
  REDUCE(0, sList,
    LAMBDA(acc, s,
      acc + SUM(INDIRECT("'"&s&"'!B2:B10"))
    )
  )
)   

• 수식 구조가 계단형으로 읽혀 유지보수가 쉽다.
• 다른 범위에도 재사용하기 용이하다.

조건까지 포함한 동적 배열 예시

=LET(
  sList, SheetList,
  REDUCE(0, sList,
    LAMBDA(acc, s,
      acc + SUMIF(INDIRECT("'"&s&"'!A2:A1000"), $E2,
                  INDIRECT("'"&s&"'!B2:B1000"))
    )
  )
)   

표준 패턴별 예제 모음

목표	시트 구조	수식	포인트
월별 동일 셀 합계	Jan~Dec, 각 시트 B2	=SUM(Jan:Dec!B2)	가장 단순한 3차원 참조이다.
월별 범위 합계	각 시트 B2:D10	=SUM(Jan:Dec!B2:D10)	범위가 같으면 그대로 누적한다.
지점별 목표 대비 최대값	지점A~지점F, C5	=MAX('지점A:지점F'!C5)	최대·최소도 동일 문법이다.
경계 시트 패턴	▶Start ~ ▶End	=SUM('▶Start:▶End'!E3)	시트 증감이 잦을 때 안전하다.
조건부 합계	시트목록 A2:A13	=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A13&"'!A:A"),$E2,INDIRECT("'"&A2:A13&"'!B:B")))	간접 참조를 활용한 우회식이다.

실무 시나리오: 월별 원가·매출·이익 요약표

각 월 시트의 동일한 표에서 B3는 매출, B4는 원가, B5는 이익이라고 가정한다.

항목	요약 시트 수식	설명
총매출	=SUM(Jan:Dec!B3)	월별 매출 누계이다.
총원가	=SUM(Jan:Dec!B4)	월별 원가 누계이다.
총이익	=SUM(Jan:Dec!B5)	월별 이익 누계이다.
이익률	=IFERROR([@총이익]/[@총매출],0)	표 서식과 함께 계산한다.

오류와 함정, 예방법

• 시트명에 공백 : 반드시 따옴표로 감싼다. 'Sheet 1:Sheet 3'!A1 형태가 안전하다.
• 병합 셀 : 병합으로 위치가 달라지면 시트 간 정합이 깨진다. 병합을 피한다.
• 시트 삭제 : 범위 중 하나의 시트를 삭제하면 재계산 시 #REF!가 발생할 수 있다. 삭제 전 수식 의존도를 확인한다.
• 열/행 구조 불일치 : 한 시트만 행이 삽입되면 합계 위치가 어긋난다. 구조 변경은 모든 시트에 동일하게 반영한다.
• 간접 참조 과다 : INDIRECT는 변동 함수라 대용량에서 느려진다. 가능하면 순수 3차원 참조로 처리한다.
• 비연속 시트 : 3차원 참조는 비연속 시트를 한 번에 지정하지 못한다. 시트목록+INDIRECT로 우회한다.

대안 도구: 데이터 통합, 피벗, Power Query

데이터 → 데이터 통합(Consolidate)

1. 요약시트에서 데이터 탭 → 데이터 통합을 누른다.
2. 함수에 합계를 고르고 각 시트 범위를 추가한다.
3. 레이블 사용 옵션을 상황에 맞게 지정한다.

구조가 약간 달라도 레이블로 맞출 수 있으나, 원본 변경 시 자동 갱신 흐름을 별도로 설계해야 한다.

반응형

피벗테이블

여러 시트의 데이터를 한 표로 모아두는 전처리를 한 뒤 피벗테이블로 분석하면 조건 집계와 세분화가 쉬워진다.

Power Query

시트 구조가 동일하면 쿼리로 모든 시트를 결합한 후 한 번의 피벗 또는 집계로 처리할 수 있다. 갱신만으로 최신 상태를 유지할 수 있어 안정적이다.

성능과 유지보수 원칙

• 우선순위 : 3차원 참조 > 데이터 통합/Power Query > INDIRECT 조합 순으로 검토한다.
• 범위 축소 : 전열 참조보다 제한된 범위를 사용한다.
• 경계 시트 사용 : 시트 증감이 잦을수록 경계 시트를 기본으로 한다.
• 구조 잠금 : 서식 파일을 만들어 모든 시트가 동일 구조를 유지하게 한다.

검증 체크리스트

• 모든 대상 시트의 셀 주소가 동일한가 확인한다.
• 경계 시트 범위 안에 모든 대상 시트가 있는가 확인한다.
• 시트명에 공백·한글·특수문자가 있을 때 따옴표를 사용했는가 확인한다.
• 필요 시 별도의 합계 검증행을 두고 SUM과 SUBTOTAL로 이중 검증한다.

키보드와 작성 팁

• 수식 입력 중 시트 탭을 Shift+클릭하여 연속 범위를 빠르게 선택할 수 있다.
• 경계 시트 명칭은 정렬 상단에 오도록 특수기호(예: ▶, _)를 활용한다.
• 시트 रंग을 그룹별로 지정하여 범위를 눈으로 검증한다.

참고 자료 요약

주제	출처	핵심
3차원 참조 개요	Microsoft Support	시작시트:끝시트!범위 문법과 지원 함수 안내이다.
INDIRECT 함수	Microsoft Support	문자열로 만든 참조를 반환하며 변동 함수 특성이 있다.
LAMBDA·REDUCE	Microsoft Support	사용자 지정 계산 흐름과 누산 처리를 제공한다.
데이터 통합	Microsoft Support	여러 범위를 레이블 기준으로 합산한다.
Power Query	Microsoft Learn	여러 시트를 결합하고 새로 고침으로 자동 반영한다.

FAQ

Q1. 3차원 참조로 조건부 합계(SUMIF)를 직접 쓸 수 있는가

불가하다. INDIRECT와 SUMIF를 조합하거나 Power Query로 통합한 뒤 조건을 적용한다.

Q2. 비연속 시트만 선택해서 합계하고 싶다

3차원 참조로는 불가하다. 시트목록 범위를 만든 후 SUMPRODUCT+INDIRECT를 사용한다.

Q3. 시트명이 바뀌면 수식은 어떻게 되는가

3차원 참조는 시트명 변경을 자동 반영한다. 다만 INDIRECT 기반 수식은 문자열을 수정해야 한다.

Q4. 다른 통합문서의 여러 시트를 한 번에 3차원 참조할 수 있는가

일반적으로 동일 통합문서 내에서 관리하는 것을 권장한다. 외부 통합문서 연결은 안정성과 유지보수 비용이 증가한다.

Q5. Google Sheets에서도 같은 문법이 동작하는가

엑셀 전용 개념으로 이해하는 것이 안전하다. Sheets에서는 시트 결합·QUERY 등 다른 방식으로 처리한다.

Q6. 재계산이 느릴 때 해결책은 무엇인가

순수 3차원 참조로 대체, 참조 범위 축소, 불필요한 변동 함수 제거, Power Query로 전환 순으로 검토한다.