- 최초 작성일: 2004-11-03
- 최종 수정일: 2026-09-26
- 조회수: 22 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 월별 계정별 집계하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
참으로 오래간만에 다시 인사를 드리는군요. *^^* 그동안 잘들 지내셨나요? Exceller는 그간 개인적으로 많은 일들이 있었습니다.
크고 작은 심경의 변화는 차치하고... 10여년간 살던 동네를 떠나 새로운 곳에 보금자리를 마련하였습니다. 그리고… 지난달 24일에 있었던 조선일보 춘천마라톤에서 드디어 풀코스를 완주 하였습니다.
모터사이클계에 이런 격언이 있다고 합니다. "타는 사람은 두 종류밖에 없다. 넘어진 사람과 넘어질 사람들이다."
이제 이런 말씀을 드릴 수 있을 것 같습니다. ^^ "달리는 사람은 두 종류밖에 없다. 풀코스를 뛰어본 사람과 뛰어볼 사람이다."
달리기 얘기는 이만 접도록 하겠습니다. 마라톤에 관심이 있는 분은 언제든 연락주시기 바랍니다. ^^
- 최신 글을 불러오는 중...
월별 계정별 집계하기
핵심 요약
- 날짜별 지출 내역을 계정과목별·월별로 집계하는 방법을 설명합니다.
- SUMPRODUCT를 이용하여 계정과목과 월을 동시에 조건으로 지정합니다.
- 이중 유효성 검사와 다른 시트의 목록을 이용하는 방법도 함께 언급합니다.
계정과목별·월별 집계하기
| [표 1]과 같은 형태의 데이터가 있습니다. 이것을 [표 2]와 같이 계정과목별/ |
월별로 집계하여야 한다면 어떻게 하면 좋을까요?
[표 1]
| 날짜 | 계정과목 | 지출내역 | 지출금액 | 비 고 |
|---|---|---|---|---|
| 2004-08-03 | 가족용돈 | 큰딸 | 150000 | |
| 2004-08-04 | 육아교육 | 놀이방 | 200000 | |
| 2004-08-05 | 식비 | 외식 | 100000 | |
| 2004-08-06 | 교통비 | 버스 | 2400 | |
| 2004-09-03 | 교통비 | 택시 | 5000 | |
| 2004-09-04 | 교통비 | 전철 | 15000 | |
| 2004-09-15 | 건강문화 | 화장품 | 50000 | |
| 2004-09-18 | 육아교육 | 놀이방 | 200000 | |
| 2004-10-01 | 의생활 | 옷 | 200000 | |
| 2004-10-04 | 가족용돈 | 큰아들 | 300000 | |
| 2004-10-07 | 식비 | 부식 | 30000 | |
| 2004-10-10 | 교통비 | 택시 | 7300 | |
| 2004-10-13 | 식비 | 외식 | 50000 | |
| 2004-11-02 | 건강문화 | 신문값 | 13000 | |
| 2004-11-05 | 의생활 | 잡화 | 200500 | |
| 2004-11-09 | 식비 | 외식 | 83000 |
[표 2]
| 구 분 | 8 | 9 | 10 | 11 |
|---|---|---|---|---|
| 가족용돈 | =SUMPRODUCT(($C$34:$C$49=$B54)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B54)*(MONTH($B$34:$B$49)=D$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B54)*(MONTH($B$34:$B$49)=E$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B54)*(MONTH($B$34:$B$49)=F$53)*($E$34:$E$49)) |
| 육아교육 | =SUMPRODUCT(($C$34:$C$49=$B55)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B55)*(MONTH($B$34:$B$49)=D$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B55)*(MONTH($B$34:$B$49)=E$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B55)*(MONTH($B$34:$B$49)=F$53)*($E$34:$E$49)) |
| 식비 | =SUMPRODUCT(($C$34:$C$49=$B56)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B56)*(MONTH($B$34:$B$49)=D$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B56)*(MONTH($B$34:$B$49)=E$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B56)*(MONTH($B$34:$B$49)=F$53)*($E$34:$E$49)) |
| 교통비 | =SUMPRODUCT(($C$34:$C$49=$B57)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B57)*(MONTH($B$34:$B$49)=D$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B57)*(MONTH($B$34:$B$49)=E$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B57)*(MONTH($B$34:$B$49)=F$53)*($E$34:$E$49)) |
| 건강문화 | =SUMPRODUCT(($C$34:$C$49=$B58)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B58)*(MONTH($B$34:$B$49)=D$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B58)*(MONTH($B$34:$B$49)=E$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B58)*(MONTH($B$34:$B$49)=F$53)*($E$34:$E$49)) |
| 의생활 | =SUMPRODUCT(($C$34:$C$49=$B59)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B59)*(MONTH($B$34:$B$49)=D$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B59)*(MONTH($B$34:$B$49)=E$53)*($E$34:$E$49)) | =SUMPRODUCT(($C$34:$C$49=$B59)*(MONTH($B$34:$B$49)=F$53)*($E$34:$E$49)) |
피벗 테이블을 이용할 수도 있겠고, 배열 수식을 사용할 수도 있을 법 합니다만, 이런 정도라면 간단히 수식으로 처리하는 것이 좋을 것입니다. 이런 경우 사용할 수 있는 함수가 Sumproduct 함수입니다. 위의 빨간색 셀에는 다음과 같은 수식이 들어있습니다.
=SUMPRODUCT(($C$34:$C$49=$B54)*(MONTH($B$34:$B$49)=C$53)*($E$34:$E$49))그동안 배열 수식을 잘 이해하신 분이라면 수식 자체를 이해하시는 데에는 별 문제가 없을 것입니다.
그런데… 어럽쇼? C53:F53 영역에는 분명히 8월, 9월,… 등과 같은 문자열 값이 들어있는데 어떻게 수식에서는 Month 함수의 결과값과 직접 비교를 했지? 하는 분이 계실 듯 합니다. C53:F53 셀에는 숫자값이 들어있습니다. 셀 서식을 이용하여 뒤에 '월'이라는 표시를 붙여준 것입니다.
이중 유효성 검사 살펴보기
| 눈여겨 보시면 이것 말고도 중요한 몇 가지 트릭이 더 숨어 있습니다. |
'계정과목'과 '지출내역'을 입력할 때, Exceller가 이름붙인 그 유명한(^^;;) '이중 유효성 검사'를 사용한 것과 유효성 검사에서 어떻게 다른 시트에 있는 내용을 목록의 형태로 불러오는지 등에 대해 잘 정리해 놓으세요!
지출내역 항목 부문에 있는 셀을 선택하고 '데이터-유효성 검사' 메뉴를 선택하면 그림과 같이 유효성 검사가 지정되어 있음을 알 수 있습니다.
'이중 유효성 검사'를 설정하는 방법에 대해서는 X0301 강좌를 참고하시기 바라며, 하시다가 도저히 이해가 안되는 대목이 있으면 질문주세요.
오늘은 여기까지…
마치며
SUMPRODUCT를 이용하여 지출 내역을 계정과목과 월별로 집계하는 방법을 살펴보았습니다. 이중 유효성 검사와 다른 시트의 목록을 이용하는 부분도 함께 확인했습니다.
