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

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

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

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

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

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

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

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

‘지출구분’ 이름 만들기

‘수입구분’ 이름 만들기

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 4. 가계부 작성하기

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

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