모든 시트 한번에 합산하여 정리하기 (Sumproduct , Sumif 사용)

엑셀을 사용하다 보면 연도 , 월간 , 주간 , 일간 등 다양한 규칙에 따라 시티를 구분할 때가 있습니다.

 

일정 기간 단위로 정리된 시트를 보지만 모두 합산 정리해야 될 때 편리하게 할 수 있는 방법입니다.

 

가장 대표적인 예시는 일정 기간 단위로 시트를 구분하는 출석부 , 출근부입니다.

 

해당 방법을 사용하기 위해서는 2가지 조건을 기억하셔야 합니다.

 

---

1. 시트는 숫자 1씩 증가하는 규칙이 존재한다.

2. 각 시트별 데이터 정리 양식은 동일해야 한다.

---

그러면 예시 파일과 함께 알아보도록 하겠습니다.

 

저는 출근부를 아주 간편하게(?) 작성해 봤답니다.

 

뭐 다른 조건들이 엄청 많겠지만 출근 횟수만 나타나는 출근부입니다.

 

앞서 이야기했던 1번 규칙대로 시트는 일정한 규칙이 있습니다.

 

1월 , 2월 , 3월 ,..... 숫자로 증가하는 시트 규칙이 존재한다.

 

그래서 합계라는 시트에는 1월부터 출근한 횟수를 합산해서 노출할 예정입니다.

 

그러면 각 시트별 양식은 어떻게 되었는지 한번 확인하겠습니다.

---

각 시트별 양식

특별한 양식이 없습니다. 예시를 위해 만든 것입니다.

 

각 시트에는 A열은 이름 , B열은 출석 횟수 양식이 공통 적용되어야 한다.

합계 시트는 반드시 해당 규칙을 지킬 필요는 없습니다.

 

 

---

앞서 이야기했던 규칙은 이렇게 지켜지면 됩니다.

 

그러면 이제 합산하는 방법을 알아보겠습니다.

---

예시에서 필요한 정보

1. 시트의 개수 : 1월 ~ 3월까지 (3개)

2. 조건을 검색하는 위치 : A열 (이름)

3. 합산을 해야 하는 위치 : B열 (출근 횟수)

---

이렇게 정보가 필요합니다.

 

그러면 함수를 작성해 보겠습니다.

 

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&"월! A:A"), A2, INDIRECT(ROW($1:$3)&"월! B:B")))

이렇게 적용했을 때 합산이 될 수 있습니다.

 

그러면 함수를 분석해 보겠습니다.

 

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&"월! A:A"), A2, INDIRECT(ROW($1:$3)&"월! B:B")))

INDIRECT라는 함수는 텍스트를 영역으로 옮겨주는 함수입니다. 

상세 내용은 추후 설명드리겠습니다.

 

ROW($1:$3) : 시트의 개수입니다. 1월 ~ 3월까지이기 때문에 $1:$3입니다.

 만약 , 4월부터 7월까지 라면? 그렇습니다. ROW($4:$7) 이렇게 하시면 됩니다!!

 그리고 1월부터 12월까지 라면? ROW($1:$12) 이렇게 하시면 됩니다.

 

& : 텍스트를 연결해주는 엑셀의 규칙입니다. 그냥 반드시 필요한 부분입니다.

 

"월! A:A" : 따옴표와 엔드 등 특수 문자들도 모두 포함되어야 합니다.

시트 이름이 1월 , 2월 , 3월 이기 때문에 『월! 』 가 존재합니다.

만약 1주 , 2주 , 3주 라면? 『주!』 가 됩니다.

그리고 A:A는 조건을 찾기 위한 영역입니다. 

1월 , 2월 , 3월 시트에서 이름은 A:A 영역에 있기 때문에 A:A가 있습니다.

 

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&"월! A:A"), A2, INDIRECT(ROW($1:$3)&"월! B:B")))

여기서 다른 것은 B:B입니다.

합산이 될 영역입니다. 출근 횟수가 존재하는 위치입니다.

 

여기까지는 전부다 각 월별 시트의 위치에 대한 내용이었습니다.

---

 

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&"월! A:A"), A2, INDIRECT(ROW($1:$3)&"월! B:B")))

 

A2 값은 합산할 사람의 이름입니다.

이 값은 『합계 』 시트에 존재하는 값입니다.

 

 

그렇게 해서 모두 작성해서 입력하면 합산이 됩니다.

첨부 파일로 공부해보시면 더 쉽게 할 수 있습니다.

SheetsSum.xlsx

0.01MB