엑셀 가계부 만들기: 분류 드롭다운, SUMIF·SUMIFS 합계, 잔액 자동 계산

 

핵심 요약
엑셀 가계부는 날짜·분류·금액 열을 만들고, 분류는 데이터 유효성 검사로 드롭다운을 걸어 입력합니다. 분류별 지출은 SUMIF, 월과 분류를 함께 볼 때는 SUMIFS로 더하고, 잔액은 SUM으로 수입에서 지출을 빼 자동으로 계산합니다.

 

엑셀가계부를 만들려다 가장 많이 막히는 곳은 두 군데입니다. 분류를 매번 손으로 적다 보면 같은 항목을 '식비', '식 비', '식대'처럼 다르게 쓰게 되고, 그러면 나중에 분류별로 더할 때 합계가 맞지 않습니다. 또 한 달 치가 쌓이면 어느 분류에 얼마를 썼는지 한눈에 보이지 않습니다.

 

이 글에서는 분류를 드롭다운으로 골라 입력하게 만드는 데이터 유효성 검사, 분류별 지출을 더하는 SUMIF, 월과 분류를 함께 거르는 SUMIFS, 그리고 수입에서 지출을 뺀 잔액과 예산 초과 강조까지 순서대로 정리합니다. 함수 문법과 메뉴 경로는 마이크로소프트 지원 문서로 대조했습니다.

 

목차
① 가계부 틀과 분류 드롭다운 만들기
② 분류별 지출 합계 구하기 (SUMIF)
③ 월과 분류 두 조건으로 더하기 (SUMIFS)
④ 수입·지출·잔액 자동 계산과 예산 초과 강조
⑤ 엑셀 가계부 상황별 해결 11가지

 

1. 가계부 틀과 분류 드롭다운 만들기 (데이터 유효성 검사)

먼저 첫 행에 날짜, 월, 분류, 결제수단, 금액을 머리글로 적습니다. 분류를 손으로 매번 입력하면 표기가 흔들리므로, 데이터 유효성 검사로 드롭다운 목록을 걸어 정해둔 항목만 고르게 하는 편이 좋습니다.

1. 식비, 교통, 주거, 통신, 여가처럼 쓸 분류 항목을 빈 열이나 다른 시트에 적습니다.
2. 분류를 입력할 셀 범위를 선택합니다.
3. 리본의 데이터 탭에서 데이터 유효성 검사를 누릅니다.
4. 설정 탭의 허용 상자에서 목록을 고릅니다.
5. 원본 상자를 누르고 1번에서 적어둔 항목 범위를 지정한 뒤 확인을 누릅니다.

 

이렇게 하면 분류 칸을 누를 때 화살표가 생기고, 목록에서 골라 입력하게 됩니다. 항목을 나중에 늘릴 생각이면 항목 목록을 엑셀 표(삽입 탭의 표)로 만들어 원본으로 지정해 두면, 표에 항목을 추가할 때 드롭다운에도 그대로 반영됩니다.

 

2. 분류별 지출 합계 구하기 (SUMIF)

SUMIF는 지정한 조건에 맞는 셀만 골라 더하는 함수입니다. 가계부에서는 분류가 특정 값인 행의 금액만 합산할 때 씁니다. 구문은 다음과 같습니다.

=SUMIF(range, criteria, [sum_range])

 

range는 조건을 비교할 범위(분류 열), criteria는 찾을 조건(예: 식비), sum_range는 실제로 더할 범위(금액 열)입니다. 아래처럼 분류와 금액이 있을 때 식비 합계를 구해 보겠습니다.

분류	금액
식비	30,000
교통	1,250
식비	12,000
교통	2,500

 

수식	결과
=SUMIF(A2:A5,"식비",B2:B5)	42,000

 

식비 행의 금액 30,000과 12,000만 더해 42,000이 됩니다. 주의할 점은 조건 범위(A2:A5)와 더할 범위(B2:B5)의 행 수를 똑같이 맞춰야 한다는 것입니다. 분류 값에 공백이 섞이면 같은 분류로 보지 않으므로, 드롭다운으로 입력해 표기를 통일하는 편이 안전합니다.

 

3. 월과 분류 두 조건으로 더하기 (SUMIFS)

달이 쌓이면 같은 식비라도 10월 식비만 보고 싶을 때가 있습니다. 조건이 둘 이상이면 SUMIFS를 씁니다. 구문은 다음과 같습니다.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

 

SUMIFS는 더할 범위(sum_range)를 맨 앞에 쓰고, 그 뒤로 조건 범위와 조건을 짝지어 넣습니다. 조건은 최대 127쌍까지 넣을 수 있습니다. 월 열과 분류 열이 있을 때 10월 식비만 더해 보겠습니다.

월	분류	금액
10월	식비	30,000
10월	교통	1,250
9월	식비	20,000
10월	식비	12,000

 

수식	결과
=SUMIFS(C2:C5,A2:A5,"10월",B2:B5,"식비")	42,000

10월이면서 식비인 행만 골라 30,000과 12,000을 더해 42,000이 나옵니다. SUMIF와 SUMIFS는 더할 범위의 위치가 반대입니다. SUMIF는 더할 범위를 맨 뒤에, SUMIFS는 맨 앞에 씁니다. 이 순서를 바꿔 쓰면 엉뚱한 값이 나오므로 주의합니다.

 

4. 수입·지출·잔액 자동 계산과 예산 초과 강조

전체 흐름은 SUM으로 잡습니다. 수입과 지출을 각각 더한 뒤, 잔액은 수입 합계에서 지출 합계를 빼 구합니다.

=SUM(수입범위)
=SUM(지출범위)
=수입합계셀-지출합계셀

 

수입과 지출이 한 열에 섞여 있다면, 구분 열(수입/지출)을 두고 SUMIF로 구분이 지출인 금액만 더하면 됩니다. 분류별 예산을 따로 정해 두었다면 조건부 서식으로 초과를 눈에 띄게 할 수 있습니다. 지출 합계 칸을 선택하고 홈 탭의 조건부 서식에서 셀 강조 규칙의 보다 큼을 고른 뒤, 비교할 예산 값을 지정하면 그보다 큰 칸이 빨갛게 표시됩니다.

 

이렇게 세 열(날짜·분류·금액)과 드롭다운, SUMIF 합계, 잔액 칸만 있어도 기본 가계부가 돌아갑니다. 월 열과 조건부 서식은 쓰면서 필요할 때 더하면 됩니다.

 

5. 엑셀 가계부 상황별 해결 11가지

 

1. SUMIF 합계가 0으로 나올 때

조건 범위와 더할 범위의 행 수가 다르거나, 분류 값에 공백이 섞였을 때 주로 생깁니다. 두 범위의 시작·끝 행을 똑같이 맞추고, 분류 값 앞뒤 공백을 없앱니다. 공백이 의심되면 TRIM 함수로 정리한 열을 조건으로 쓰면 됩니다.

 

2. 숫자를 넣었는데 합계가 안 될 때

금액이 텍스트로 저장돼 있으면 SUM과 SUMIF가 그 칸을 건너뜁니다. 셀 왼쪽 위에 녹색 삼각형이 보이면 텍스트 숫자라는 신호입니다. 해당 범위를 선택해 오류 단추의 숫자로 변환을 누르거나, 서식을 숫자로 바꾼 뒤 다시 입력합니다.

 

3. 특정 달 지출만 더하고 싶을 때

월 열을 따로 두고 SUMIFS로 월과 분류 두 조건을 겁니다. 월 열이 없으면 날짜에서 =TEXT(날짜,"m월") 수식으로 월을 뽑아 쓸 수 있습니다. 날짜 자체로 거를 때는 조건에 그 달 시작일 이상, 다음 달 시작일 미만을 지정합니다.

 

4. 카드·현금 결제 수단별로 나눠 볼 때

결제수단 열을 추가하고 SUMIFS에 조건을 하나 더 겁니다. SUMIFS는 조건 범위와 조건을 최대 127쌍까지 넣을 수 있어, 월·분류·결제수단을 함께 걸러 카드로 쓴 식비만 뽑아낼 수 있습니다.

 

5. 드롭다운 목록이 안 보일 때

데이터 유효성 검사 설정 창의 드롭다운 표시 확인란이 꺼져 있으면 화살표가 나타나지 않습니다. 해당 셀을 선택해 데이터 탭의 데이터 유효성 검사를 다시 열고 그 확인란을 켭니다. 셀에서 유효성 검사 자체가 지워졌다면 설정을 다시 지정합니다.

 

6. 분류 항목을 나중에 더 늘리고 싶을 때

원본을 고정 범위로 지정했다면 데이터 유효성 검사를 다시 열어 범위를 넓혀야 합니다. 항목 목록을 엑셀 표로 만들어 원본으로 쓰면, 표에 새 항목을 추가할 때 드롭다운에도 자동으로 반영돼 매번 손볼 필요가 없습니다.

 

7. 월별 요약표를 자동으로 만들고 싶을 때

피벗 테이블을 쓰면 분류와 월을 행·열에 놓고 금액 합계를 한 번에 정리할 수 있습니다. 삽입 탭에서 피벗 테이블을 넣고 분류를 행, 월을 열, 금액을 값에 둡니다. 원본 자료가 바뀌면 피벗 테이블에서 새로 고침을 눌러 반영합니다.

 

8. 예산을 정해두고 초과를 알고 싶을 때

분류별 예산 칸을 따로 만들고, 실제 지출인 SUMIF 합계와 비교합니다. 조건부 서식의 보다 큼 규칙으로 지출이 예산을 넘으면 칸을 빨갛게 칠해 바로 알아볼 수 있습니다. 예산 값을 바꾸면 강조도 함께 바뀝니다.

 

9. 수입과 지출이 한 열에 섞여 있을 때

금액을 한 열에 넣고 구분 열(수입/지출)을 둔 경우, SUMIF로 구분이 지출인 금액만 더하고 수입도 같은 방식으로 더합니다. 잔액은 수입 합계에서 지출 합계를 빼 구합니다. 지출을 음수로 적는 방식도 있지만, 구분 열을 쓰는 편이 합계를 나누기 쉽습니다.

 

10. 다른 시트의 자료를 합산할 때

SUMIF·SUMIFS의 범위에 시트이름!범위 형식으로 다른 시트의 범위를 넣으면 됩니다. 다만 월별로 시트를 쪼개면 수식이 길어지므로, 한 시트에 모아두고 월 열로 거르는 편이 관리가 간단합니다.

 

11. 휴대폰으로 입력하고 싶을 때

원드라이브에 저장해 두면 휴대폰의 엑셀 앱에서 같은 파일을 열어 외출 중에도 바로 적을 수 있습니다. 이미 넣어 둔 SUMIF·SUMIFS 수식은 모바일에서도 그대로 다시 계산됩니다.

 

분류별로 얼마 썼는지만 보려면 SUMIF, 달과 분류를 함께 보려면 SUMIFS, 전체 흐름은 SUM과 잔액 칸으로 잡으면 됩니다. 처음에는 날짜·분류·금액 세 열과 드롭다운만 만들어 두고, 월 열과 피벗 테이블은 필요해질 때 더하는 순서가 부담이 적습니다.

 

참고 자료

• SUMIF 함수 - Microsoft 지원
• SUMIFS 함수 - Microsoft 지원
• 드롭다운 목록 만들기 - Microsoft 지원

 

※ 더 보기

엑셀 COUNTIF 함수 활용법: 조건별 개수 세기, 와일드카드, COUNTIFS 비교

엑셀 조건부 서식 활용법: 행 전체 색칠, 수식 규칙, 날짜 임박 강조