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

2016. 2. 2. 13:00



민족의 대명절, 설 연휴가 어느덧 일주일 앞으로 다가오고 있습니다. 대가족이 한 자리에 모이는 만큼 지출 또한 커지기도 하는데요, 차례 비용과 각종 선물 비용, 교통비까지 지출하고 나면 남는 것은 텅 빈 지갑과 카드고지서뿐. 취업포털 잡코리아의 설문조사에 따르면 올해 설날에 예상하는 경비는 기혼자 평균 34만 원, 미혼자 평균 21만 원이라는데요, 조금이라도 절약할 수 있다면 월말은 조금 더 풍족하게 지낼 수 있겠죠? 그럴 때는 가계부를 써보는 것은 어떨까요? 엑셀로 만들어 명절날 고향집에서도 쉽게 작성할 수 있도록 해보았어요. 알고 보면 어플보다 더 간단한 엑셀 가계부 만드는 법, 오늘은 ‘데이터 유효성 검사’로 항목 구분하는 법을 설명해드립니다.


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

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



 1. 수입, 지출 항목을 구분해보자


가계부의 기본은 수입과 지출을 구분하여 자산의 흐름을 확인하는 것이 아닐까 하는데요, 수입 중에서도 월급과 기타 유동수입을 구분하고, 지출 역시 항목별로 세분화하여 어떤 항목에 지출이 가장 많은지를 확인하는 것이 필요합니다. 그러므로 가장 먼저 수입과 지출 항목을 구분해보도록 합시다. 



위 이미지와 같이 ‘항목’이라는 시트를 만든 후 대분류를 나누고, 각 항목의 하위 항목을 나열해줍니다. 여러분의 생활 패턴에 따라 항목을 추가하거나 삭제하셔도 됩니다.



표를 만들었다면 각 대표 분류에 이름을 붙여줄 거예요. 아래와 같이 ‘수입’과 ‘지출’에 해당하는 부분을 모두 블록설정한 후, 수식>선택 영역에서 만들기>첫행을 체크한 후 확인을 눌러줍니다. 블록설정한 영역의 가장 윗칸에 위치한 ‘수입/저축/식비/쇼핑/공과금…’ 등이 이름이 됩니다.


‘지출구분’ 이름 만들기


‘수입구분’ 이름 만들기


‘지출구분’과 ‘수입구분’도 마찬가지로 각각 블록설정하여 이름을 만들어주세요.



 2. 데이터 유효성 검사로 항목을 불러 오자



수입과 지출 항목을 구분해주었다면, 새로운 시트를 만들어 대략적인 가계부의 틀을 잡아줍니다. 날짜와 각 항목에 따른 사용내역, 금액 입력란을 만들어보았습니다.


대분류와 소분류에는 아까 표로 만들어 둔 내용을 불러올 거예요. 만약 내용을 불러오지 않고 직접 기입해야 한다면 어떤 칸에는 ‘간식’이라고 적혀 있고, 어떤 항목은 ‘간식비’로 적혀있는 등 항목명이 뒤죽박죽 섞여 헷갈릴 수 있으니까요. 이럴 때 ‘데이터 유효성 검사’를 사용하면 편리합니다.


데이터 유효성 검사는 사용자가 셀에 정확한 데이터를 입력하도록 제한하는 기능으로, 여러 가지 제한 조건이 있지만, 특히 미리 정해놓은 목록 내에서만 선택할 수 있도록 하는 ‘목록’ 조건이 가장 많이 이용되고는 해요.



위 노란색으로 표시된 부분에 데이터 유효성 검사를 적용해줄 건데요, 대분류와 소분류는 잠시 접어두고 지출구분 부분을 먼저 적용해볼까요?



위 이미지와 같이 ‘지출구분’에 해당하는 칸에 모두 블록설정을 한 후, ‘데이터>데이터 유효성 검사>데이터 유효성 검사’를 클릭해줍니다.

‘제한 대상’은 ‘목록’으로 선택하고, 원본의 범위는 앞서 만들어 둔 이름, ‘=지출구분’이라고 적어줍니다. 수입구분도 마찬가지로 해주시면 됩니다.



확인을 누르면 위와 같이 드롭다운 메뉴가 생기는 것을 볼 수 있습니다.



 3. 이중 데이터 유효성 검사로 대분류와 소분류를 채워 보자


데이터 유효성 검사를 통해 지출구분과 수입구분을 선택하여 입력할 수 있게 되었습니다. 이번에는 사용내역의 대분류와 소분류란에 데이터 유효성 검사를 적용해 볼 건데요, 대분류의 선택에 따라 소분류의 목록이 나오는 '이중 데이터 유효성 검사'를 적용해봅시다. 예를 들면, ‘식비’를 선택했을 때, 식비의 하위에 있는 ‘식비 / 간식 / 외식’이 나오게 하는 것이죠.



앞서 설명 드린 것과 같이 ‘데이터>데이터 유효성 검사’ 선택 후 항목 셀의 ‘수입’과 ‘지출’에 해당하는 부분을 선택한 후 확인을 클릭합니다.



드롭다운 메뉴가 생겼죠? 소분류는 대분류에서 무엇을 선택하느냐에 따라 목록을 노출시켜야 하므로, ‘=INDIRECT’ 함수를 이용해줍니다.



대분류와 마찬가지로 ‘데이터>데이터 유효성 검사’에서 목록을 선택해주는 데요, 원본에는 ‘=INDIRECT(대분류에 해당하는 셀 위치)’을 적어 줍니다. 제가 만든 시트에서는 대분류에 해당하는 셀이 D4에 있으므로, ‘=INDIRECT(D4)’라고 적어주면 되겠죠?


내용 추가: 가끔 소분류에서 'INDIRECT' 함수 오류가 난다는 분들이 계신데요, 오류가 생긴다면 소분류의 가장 첫행(본문의 이미지에서는 E4에 해당)만 선택한 후 '데이터 유효성 검사>목록>INDIRECT 함수 입력' 순으로 진행해보세요. 그 다음, 첫행의 수식을 나머지 칸들도 복사해주시면 오류 없이 진행될 거예요!



대분류에서 ‘의료비’를 선택하면 소분류에서는 이에 해당하는 ‘병원비/약값/보험’만 노출되는 모습을 볼 수 있습니다.



 4. 가계부 작성하기



마지막으로 데이터 유효성 검사로 미리 지정해 둔 목록을 선택하여 가계부를 작성하면 되겠죠~ 엑셀의 기본 ‘=sum’ 함수를 이용해서 수입 및 지출 합계를 계산해주시면 되겠습니다.


이렇게 해서 아주 간단한 양식의 가계부가 완성되었습니다. 여기에 몇 가지 수식을 추가하면 항목별 지출 내역과 신용카드의 경우 결제 예정 금액 등도 확인할 수 있답니다. 다음 번에 가계부 만들기 심화편도 소개해드릴게요. 그때까지 데이터 유효성 검사 방법, 꼭 기억해주세요~!





  1. 이전 댓글 더보기
  2. 작성자 대표 이미지
    2016.04.04 16:19
    비밀댓글입니다
  3. 작성자 대표 이미지
    2016.04.06 14:48 신고
    안녕하세요, 효블지기예요~
    가끔 소분류에서 'INDIRECT' 함수 오류가 난다는 분들이 계신데요,
    오류가 생긴다면 소분류의 가장 첫행(본문의 이미지에서는 E4에 해당)만 선택한 후
    '데이터 유효성 검사>목록>INDIRECT 함수 입력' 순으로 진행해보세요.
    그 다음, 첫행의 수식을 나머지 칸들도 복사해주시면 오류 없이 진행될 거예요!
  4. 작성자 대표 이미지
    짜라
    2016.04.06 23:59
    안녕하세요
    글 보면서 도전하고 있는데요
    한셀에는 데이터유효성 검사 제한대상에
    목록이 없어요ㅠㅠ
    모든값 정수 실수 날짜 시간 텍스트길이
    이게 다인데
    어떻게 해야 하나요ㅠㅠ
    • 작성자 대표 이미지
      2016.04.28 10:45 신고
      안녕하세요, 짜라 님. 해당 글은 엑셀을 기준으로 작성되어 있어요.
      한셀에서는 데이터 유효성 검사를 지원하지 않을 수도 있으니, 엑셀에서 확인해보시는 게 좋을 듯합니다.
      원하는 답변이 아니어서 죄송합니다. ^^;
  5. 작성자 대표 이미지
    다솜
    2016.04.21 13:29
    소분류를 하는데요.
    리스트 선택해서 데이터 유효성 검사하는건 오류가 나서
    한셀만 선택해서 햇는데
    소분류에 인다이렉트(d3) 이 메세지만 뜨는데요 ㅠㅠ
  6. 작성자 대표 이미지
    다솜
    2016.04.21 13:30
    해결했습니다. ^^;
  7. 작성자 대표 이미지
    오후맨
    2016.04.27 15:38
    대분류를 선택한 후 소분류를 선택하면 콤보박스에 리스트가 표시되는것처럼,
    소분류를 선택한 후 사용내역 콤보박스를 선택하면 리스트가 표시되게 할 수 있을까요?
    • 작성자 대표 이미지
      2016.04.28 10:43 신고
      안녕하세요, 오후맨 님. ^^
      소분류에 데이터 유효성 검사를 했던 것과 마찬가지로 적용해주시면 되는데요, 사용내역은 워낙 범위가 다양해서 목록으로 일일이 표시를 하는 게 번거롭지 않을까요? ^^;
  8. 작성자 대표 이미지
    2016.05.09 23:20
    이중데이터유효성검사에서 대분류를 지정하기위해 수입이랑 지출부위를 선택하려고하는데 나타나지가 않아서 입력하면 다른워크시트 또는 통합문서에대한 참조를 사용할 수 없습니다. 라고 뜨는데 어느부분이서 문제가 있는걸까요? 엑셀 2007이라 구런가?
  9. 작성자 대표 이미지
    vmangov
    2016.05.26 12:04
    소분류 'INDIRECT' 함수 가 계속오류가 나네요.
    내용 추가: 가끔 소분류에서 'INDIRECT' 함수 오류가 난다는 분들이 계신데요, 오류가 생긴다면 소분류의 가장 첫행(본문의 이미지에서는 E4에 해당)만 선택한 후 '데이터 유효성 검사>목록>INDIRECT 함수 입력' 순으로 진행해보세요.

    이거 보고 그대로 했는데...안되네요^^;; E4만 선택하고, =INDIRECT(D4)하라는 말씀이시죠?
    근데...계속 오류가 나네요 ㅜㅜ;;;
  10. 작성자 대표 이미지
    dfdf
    2016.06.18 19:33
    대분류 항목 지정할 때 다른 시트에서는 못하는지요,,
  11. 작성자 대표 이미지
    유은지
    2016.06.19 22:08
    대분류 할때 유효성검사 누르고 이제 항목시트가서 드래그를 하려고하는데 아예 항목 시트로 안넘어가지는데여 .. 퓨ㅠㅠ
  12. 작성자 대표 이미지
    짱가
    2016.07.19 11:16
    시트1 내역정하기에 수입과 지출 구분 해놓고 이름 지정 하고 시트2에서 지출구분 데이터유효성검사에서 범위 지정할 때 시트1로 이동이 안되어서요 어떻게 해야 이동이 가능한건가요?
  13. 작성자 대표 이미지
    다니맘
    2016.09.13 02:20
    자꾸INDIRECT 함수에서 에러가 나네요...
    오류가 나면 첫행에 삽입하라는 말 그대로 해도 원본이 자꾸 잘못됐다고 뜨는데요...
    왜 그런거죠.ㅠ.ㅠ
    • 작성자 대표 이미지
      다니맘
      2016.09.13 02:22
      원본이 오류상태라고 계속 떠욤.ㅠ.ㅠ
      엑셀2007 사용자예용
  14. 작성자 대표 이미지
    행조
    2016.12.06 09:54
    박수를 치지 아니할 수 없네요. 공부 잘하고 갑니다^^
    • 작성자 대표 이미지
      2016.12.06 10:30 신고
      좋은 정보가 되어드렸기를 바랍니당~
      유용하게 활용해주세요~ ^___^
  15. 작성자 대표 이미지
    박혜영
    2017.01.04 14:47
    위의 사람들처럼 계속 INDIRECT 함수에서 에러가 나고,
    첫행에 삽입하라는 말 그대로 해도 원본이 자꾸 잘못됐다고 떴던 사람입니다.
    대분류 항목에 교육/문화 이런 식으로 '/'가 들어 있으면 안 돼요..
    블로그 주인님 말대로 첫 행에 이름 만들 때 교육/문화 -> 교육_문화
    저런 식으로 이름이 저장되더라고요..
    그래서 모든 / 들어간 부분을 _ 로 바꿔서 이름을 통일시켰더니,
    에러 없이 잘 나옵니다. 안 되시는 분들 한 번 확인해 보세요.
  16. 작성자 대표 이미지
    임현정
    2017.02.03 13:47
    소분류에서 자꾸 에러가 뜹니다.
    소분류 전체를 블럭 지정했다가 에러나서
    E4만 선택해서 해도 똑같아요. =INDIRECT(D4)로 입력한게 틀린 건가요?
    드롭다운 모양은 생기는데 클릭하면 아무것도 없어요.
    • 작성자 대표 이미지
      2017.02.03 15:21 신고
      안녕하세요. 임현정님.
      혹시 항목 시트를 작성할 때, 대분류와 소분류에 대한 이름 정의를 하셨나요? 만약 이 부분을 생략할 경우 에러가 날 수 있습니다.
      본문에서 설명해드린 것과 같이, 대분류와 소분류 항목을 모두 블록 설정한 후, 수식>정의된 이름>선택 영역에서 만들기>첫행을 체크한 후 확인을 눌러 이름을 정의해주세요.
      그럼, 드롭다운에 항목이 보이실 겁니다.^^
  17. 작성자 대표 이미지
    김지혜
    2017.02.09 14:24
    안녕하세요 ㅎㅎ 엑셀 엄청 잘 배우고 갑니다
    근데 그 소분류에 문제가 생겨서요 ㅜㅜ
    수입에 대한 항목은 소분류에 뜨는데 나머지 애들은 아무것도 안뜨는데 왜 그런걸까요 ? ㅜㅜ
  18. 작성자 대표 이미지
    아댕쓰
    2018.01.26 17:29
    유익한 글 잘보고 갑니다 :) 올해 열심히 가게부 써볼게요 ^^
    • 작성자 대표 이미지
      2018.01.29 08:29 신고
      네 도움이 되셨다니, 기분이 좋네요! 가계부 열심히 쓰시길 응원하겠습니다 :)
  19. 작성자 대표 이미지
    차챠
    2018.04.02 11:02
    엑셀 가계부 양식중에 가장 효율적이고 편리한 양식인듯 합니다!
    잘 활용해 가계부 열심히 써보겠습니다 감사합니다^^
    • 작성자 대표 이미지
      2018.04.02 11:06 신고
      안녕하세요, 차챠님.
      저희 엑셀양식이 도움되셨다니 기분 좋네요 :)
      저희야 말로 감사합니다!
  20. 작성자 대표 이미지
    가프랑
    2018.08.26 03:24
    안녕하세요! 좋은 가계부 양식 감사합니다!
    그리고 저도 소분류에서 함수 애러가 떴는데 대분류 항목중에서 하나 선택하고 소분류 전체 선택해서 유효성 검사하니
    문제 없이 적용되었답니다! 혹시나 다른분들께 도움이 될까 글 남깁니다 >ㅁ<
  21. 작성자 대표 이미지
    엑셀초보
    2020.01.06 15:09
    계속 오류가 난답니다. 뭐가 잘못인지 모르겠어요. 소분류에서 오류가 납니다.
화면 상단으로 올라가는 버튼 아이콘