여러 Excel 시트 값을 합산하는 2가지 방법

들어가기 전에

VLOOKUP은 특히 대규모 데이터 세트를 처리할 때 여러 가지 방법으로 도움이 될 수 있는 유용한 함수입니다. VLOOKUP은 시트에 있는 텍스트를 검색하는 데만 사용되는 것은 아니며, 시트 전체의 숫자를 통합하는 데에도 사용 가능합니다. 여러 시트에서 VLOOKUP 및 SUM을 사용하여 데이터를 관리하는 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

(이미지: 아이엑셀러닷컴)

 

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

---

• 원문: How to use VLOOKUP and SUM across multiple sheets in Excel – 2 simple methods
• URL: https://www.pcguide.com/office/excel/vlookup-and-sum-across-multiple-sheets/

---

개요

3개월 동안의 매출에 대한 데이터가 있습니다. 각 시트에는 해당 달의 판매량 데이터가 들어 있습니다.

 

(이미지: pcguide)

 

별도의 합계를 구하고자 하는 제품 이름이 기재되어 있습니다.

 

(이미지: pcguide)

 

방법 1: SUM 및 VLOOKUP 중첩 함수 사용

SUM 수식은 간단합니다. =sum을 입력하고 합계할 값에 대한 값 또는 셀 주소를 입력합니다. 그러나 여러 시트에 데이터가 있는 경우 이를 요약하려면 VLOOKUP이 필요합니다. SUM과 VLOOKUP을 함께 사용할 수 있는 수식은 다음과 같습니다.

=SUM(VLOOKUP({Cell address of lookup value},{Sheet name}!{Table array with $},{Column index number} ,FALSE),VLOOKUP({Cell address of lookup value},{Sheet name}!{Table array with $},{Column index number},FALSE)

 

검색해야 하는 시트 수만큼 VLOOKUP 수식이 반복됩니다. 세 개의 시트에 대해 이 수식을 사용했습니다.

=SUM(VLOOKUP(B3,January!$C$6:$E$12,3,FALSE),
VLOOKUP(B3,February!$C$6:$E$12,3,FALSE),
VLOOKUP(B3,March!$C$6:$E$12,3,FALSE))

 

결과는 다음과 같습니다.

 

(이미지: pcguide)

 

C3 셀 우측 하단의 채우기 핸들을 이용하여 수식을 아래로 복사합니다. 일부 제품은 모든 시트에서 사용할 수 없기 때문에 수식은 일부 제품에 대해 #N/A라는 오류 메시지가 표시됩니다.

 

(이미지: pcguide)

 

IFERROR 함수를 추가하여 수식이 값을 찾지 못하는 시트를 건너뛰도록 해야 합니다. 첫 번째 SUM 값, 즉 첫 번째 시트에 IFERROR를 추가했습니다.

=SUM(IFERROR(VLOOKUP(B5,January!$C$6:$E$12,3,FALSE),0),
VLOOKUP(B5,February!$C$6:$E$12,3,FALSE),
VLOOKUP(B5,March!$C$6:$E$12,3,FALSE))

 

IFERROR 수식 끝에 "0"을 추가했기 때문에 "#N/A" 오류를 반환하는 대신 0이 표시됩니다. 이 수식을 표 전체로 확장하면 두 개의 #N/A 오류가 제거됩니다. 아직은 완전한 수식이 아닙니다.

 

이제 세 가지 합계 값 모두에 IFERROR를 추가합니다. 그러나 여러 ISERROR 함수를 합산하여 적용하려면 값 사이에 + 기호를 추가해야 합니다. 사용할 수 있는 수식은 다음과 같습니다.

=SUM(IFERROR(VLOOKUP(B3,January!$C$6:$E$12,3,FALSE),0))+
(IFERROR(VLOOKUP(B3,February!$C$6:$E$12,3,FALSE),0))+
(IFERROR(VLOOKUP(B3,March!$C$6:$E$12,3,FALSE),0))

 

이 수식이 적용된 결과는 다음과 같습니다. 원하는 결과가 제대로 표시됩니다.

 

(이미지: pcguide)

 

방법 2: 시트 이름 나열

(방법 1)은 비교적 간단합니다. 그러나 여러 시트에 많은 양의 데이터가 분산되어 있는 경우 시트 이름을 입력하면 오류가 발생할 가능성이 높아집니다.

 

(방법 2)에서는 조금 다르게 접근해 보겠습니다. 모든 작업을 수행 중인 시트 이름을 나열하고 이를 사용하여 데이터를 가져올 수 있습니다.

 

(1) 시트 이름을 나열합니다(F2:H2 셀 참고).

 

(이미지: pcguide)

 

(2) 시트 이름을 선택하고 [수식] 탭 - [정의된 이름] 그룹 - [이름 정의]를 선택하고 적절한 이름을 입력합니다.

 

(이미지: pcguide)

 

(3) 분기별 매출 열의 첫 번째 셀에 다음 수식을 입력합니다.

=SUMPRODUCT(SUMIF(INDIRECT(“'”&월&”'!$C$6:$C$12”),B3,INDIRECT(“'”&월&”'!$E$6:$E$12”)))

 

다른 데이터 세트가 있는 경우 주어진 구문을 따르고 이에 따라 셀 값을 변경합니다. 두 번째 방법이 작동하려면 데이터가 시트 전체에서 동일한 셀에 있는지 확인하세요.

 

여기서는 VLOOKUP이나 SUMIF, SUMPRODUCE 함수 등을 이용한 방법을 소개했습니다만 이것 말고도 다양한 방법으로 해결할 수 있습니다.

Excel과 VBA의 모든 것 아이엑셀러 닷컴 · 강사들이 숨겨 놓고 보는 엑셀러TV