엑셀 가계부 만들기 응용편: SUMIF 함수로 항목별 합계 구하기

지난 번, ‘데이터 유효성 검사’를 통해 가계부 양식을 만드는 법을 소개해드렸습니다. ‘식비’를 선택했을 때 ‘간식비/외식비’ 등의 항목을 자동으로 불러와 가계부 작성을 조금 더 편리하게 할 수 있는 방법이었죠. 그렇다면 내가 한 달에 지출하는 교통비는 얼마인지, 현금과 체크카드, 신용카드 중 어떤 지출수단을 가장 많이 이용하는지를 확인하려면 어떻게 해야 할까요? 혹시 일일이 계산기를 두드리며 계산하고 계신 것은 아닌지요.

항목별 합을 구할 때에도 함수 하나만 기억하면 뚝딱 해결되는데요, 오늘은 계산 시간을 줄여주는 ‘SUMIF’ 함수를 소개해드릴게요~ 가계부 만들기 기본편은 아래 링크를 참고해주세요. ^^

▶ 엑셀 가계부 만들기 기본편: 데이터 유효성 검사 이용하기 바로가기

* 본문의 모든 이미지는 클릭하면 크게 보실 수 있습니다.

* 오피스 2010 버전을 기준으로 작성되었습니다.

 항목별 지출 합계 구하기

지난번, 가계부 만들기 기본편에서 위와 같이 지출과 수입 항목을 구분해보았습니다. 위 항목을 참고하여 지출 중 저축/식비/쇼핑/공과금/통신비/경조사/의료비/기타 합계와 수입 중 월급/용돈/이자/상여금/기타 합계를 구해볼 거예요. 먼저, 아래와 같이 합계를 구할 표를 만들어주세요.

표를 만들었다면 합계를 구해볼까요, ‘SUMIF’ 함수의 공식은 ‘=SUMIF(range, criteria, [sum_range])’입니다. ‘=SUMIF(항목 범위, 기준, 금액 범위)’인데요, 대분류가 적혀 있는 범위 중 ‘저축’이 있는 부분을 찾아 해당 부분의 숫자를 계산하라’는 의미가 되죠.

range: 항목의 범위 criteria: 기준이 되는 항목 sum_range: 숫자의 합을 구하고자 하는 범위

보이시나요? range는 ‘C4~C19’로 설정하고, 이중 ‘저축’의 합을 구할 것이므로 ‘저축’ 항목이 있는 L3을 criteria, 그리고 지출 금액이 적혀 있는 ‘G4~G19’을 [sum_range]로 설정하여 ‘SUMIF’함수를 적용해주었습니다.

위와 같이 합계가 구해진 것이 보이시나요? 나머지 항목도 마찬가지로 적용해줍니다. 

마지막으로 ‘SUM’ 함수를 이용하여 지출 합계를 구해주시면 완료. 왼쪽의 지출 금액 합계와 오른쪽의 합계가 일치하는지 확인해주세요. 만약, 소분류별 합을 구하고자 한다면, ‘range’를 소분류가 있는 ‘D4~D19’로 설정하여 수식을 적용해주시면 됩니다.

 항목별 수입 합계 구하기

이번에는 수입 합계를 구해보기로 해요. 수입도 지출과 마찬가지로 ‘SUMIF’ 함수를 사용해서 구해주시면 돼요. 단, 지출과는 달리 수입은 분류 항목이 적으므로, ‘소분류’를 기준으로 ‘range’를 설정해주었습니다.

range는 소분류가 포함되어있는 ‘D4~D19’로, criteria는 ‘L15’, [sum_range]는 ‘I4~I19’로 설정하였습니다.

나머지 항목도 마찬가지로 구해주신 후, 수입 합계를 구해주시면 완료됩니다.

 번외편, IF함수로 합계 비교하기

지금까지 ‘SUMIF’ 함수로 수입과 지출 합계를 구해봤습니다. 그런데, 왼쪽 표의 합계와 오른쪽 항목별 합계 값이 늘 같기만 할까요? 수식의 오류나, 누락된 값이 있다면 오차가 생기기도 할 거예요. 이럴 때 ‘IF’ 함수로 쉽게 확인할 수 있는 방법을 알려 드릴게요.

‘IF’ 함수는 ‘=if(logical_test), [value_if_true], [value_if_false])’ 순으로 입력해주시면 되는데요, ‘logical_test’는 ‘참이나 거짓을 판단하는 논리식’이고, ‘[value_if_true]’는 논리식이 참일때의 결과, ‘[value_if_false]’는 논리식이 거짓일 때의 결과를 나타냅니다. 왼쪽 표의 합계와 오른쪽 표의 합계에 오차가 없다면 ‘0’이라고 나타내고, 그렇지 않을 경우 오차 숫자를 나타내주면 되겠죠?

logical_test: 참이나 거짓을 판단하는 논리식 value_if_true: 논리식이 참일때의 결과 value_if_false: 논리식이 거짓일 때의 결과

제일 윗쪽에 ‘지출차액’란과 ‘수입차액’란을 만들었습니다. 여기에 if함수의 결과값을 입력하려고 해요. ‘logical test’는 ‘왼쪽 표의 지출금액 합계’와 ‘오른쪽 표의 지출금액 합계’가 같은 지를 확인하기 위해 ‘F22=M13’으로, value_if_true는 ‘0’으로, ‘value_if_false’는 F22와 M13의 차액인 ‘F22-M13’으로 적어주세요. 즉, ‘=if(F22=M13,0,F22-M13)’이 되는 것이죠.

왼쪽과 오른쪽 합계의 값이 차이가 났을 때, ‘지출차액’란에 차액 값이 표시된 것이 보이시나요? 물론 ‘F22-M13’으로 바로 계산해주셔도 차액은 계산될 거예요. 하지만 IF함수는 워낙 자주 쓰이기 때문에, 알아두시면 유용하게 사용하실 수 있을 거예요. 가령, 지출 금액에 따라 A부터 F까지 소비 등급을 매긴다거나 할 때도 쓰일 수 있죠. ^^

지금까지 ‘SUMIF’ 함수와 ‘IF’ 함수로 엑셀 가계부를 조금 더 적극적으로 사용하는 법을 소개해드렸는데요, 유용하셨나요? ^^ 이 밖에도 가계부에 활용할 수 있는 엑셀 함수는 무궁무진하니, 기회가 되면 몇 가지 더 소개해드릴게요. 궁금하신 점 있으면 언제든 댓글 남겨주세요. 가계부 작성으로 현명한 소비생활을 하시기를 바라며, 효블지기는 이만 마칩니다~!