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







댓글
  1. 은자매 2017.05.09 12:11 프로필 이미지
    가계부 만들고 있는데 정말 유용한 수식이네요 ㅎ
    궁금한게 있어요~
    날짜별&항목별 합계 내려면 함수 어떻게 써야 할까요?
    예를 들어, 5월 1일 식비와 교육비를 지출했는데
    5월 1일 식비 합계
    5월 1일 교육비 합계
    이런거요~
    매월마다 왼쪽 열에 날짜를 놓고
    윗쪽 행에 세부항목을 놓고
    날짜와 항목에 해당하는 금액들 합계를 내고싶어서요~
    이런게 가능한 수식도 있을까요??
  2. Favicon of https://blog.hyosung.com BlogIcon 효성blog 2017.05.10 09:25 신고 프로필 이미지
    은자매님 날짜와 항목에 해당하는 지출 합계는 'SUMIF' 수식을 활용하면 된답니다.
    본문 내 '항목별 지출 합계 구하기' 부분을 참고하시면 구하실 수 있을 거예요. ^^
  3. 혜주~ 2017.10.17 17:35 프로필 이미지
    감사해요
    정말 좋은 글이예요 !!
  4. 유후 2018.04.18 23:02 프로필 이미지
    좋은 정보 감사합니다~ 많이 배웁니다~
  5. 오래된미래 2018.05.29 10:10 프로필 이미지
    멋진 수식 덕분에 가계부 지출합계 쉽게 구하게 되었어요.
    감사합니다.
  6. 장미향기 2019.06.28 23:43 프로필 이미지
    자세한 설명 덕분에 항목값을 잘 구했습니다. 감사합니다~^^
  7. 33 2019.07.22 00:39 프로필 이미지
    감사해요 ㅎㅎ 헤매고 있었는데 알고나니 간단한거였네요^^!
  8. 쩡이 2020.03.24 16:23 프로필 이미지
    엑셀 가계부를 만드는게 처음이지만 내용이 너무 알기 쉽게 설명이 되어있어 유용합니다. 감사합니다.
    한가지 질문드리고 싶은게 있는데,
    지출합계를 구하기 위해 sumif를 이용하여 구하고 있는제 지출차액이 생기더라구요
    지출차액은 오류가 나서 생기는 건가요?
    오류가 나는거면 수기로 해야하는건가요? 엑셀을 잘 모루는데 따라 하다보니 술술 넘어가면 좋은데
    막혀버려서 ㅠㅠ 어느행열에서 잘못된건지도 모르겠고, 왜 차액이 뜨는지 모르겠아여
  9. 짱구맘 2020.11.13 22:51 프로필 이미지
    수기로 작성하다 엑셀로 가계부를 만드는데 유용하게 활용하고 있습니다. 감사합니다.