회계 몰라도 누구나 가능한 엑셀 복식부기 작성

실습 가이드

은행에서 다운로드한 통장과 카드사용 내역의 비고(사용처, 메모 등)를 참고하여, 복식부기로 작성할 계정과목과 적요를 자동으로 분류해 보겠습니다. 분류에 사용된 공식의 자세한 설명은 아래 신용카드 사용내역 자동 분류 영상강의를 참고해 주세요.

1. 계정과목 자동 분류하기 : 예제파일을 실행한 뒤, [가계부 복식부기 예제] 시트로 이동합니다. 이어서 H2 셀에 아래 수식을 입력합니다.
   =IFERROR(INDEX($M$2:$M$30,MATCH(1,N(ISNUMBER(SEARCH($K$2:$K$30,E2))),0)),"")

   

2. 입력한 수식을 아래로 자동채우기 하면 각 거래별 계정과목이 자동으로 분류됩니다.

   

3. 적요 자동 분류하기 : I2 셀에 아래 수식을 입력한 뒤 아래로 자동채우기 하면, 각 거래별 적요가 자동으로 분류됩니다.
   =IFERROR(INDEX($L$2:$L$30,MATCH(1,N(ISNUMBER(SEARCH($K$2:$K$30,E2))),0)),"")

   

4. 기타 거래항목 분류하기 : 거래내역 중 포함 단어로 자동 분류되지 않는 기타 거래항목을 분류합니다. 표 안의 아무 셀이나 선택한 뒤 Ctrl + A 를 누르면 표 전체 범위가 선택됩니다. 범위가 선택된 상태에서 Ctrl + Shift + L 을 눌러 자동필터를 적용합니다.
   
   오빠두Tip : 또는 [데이터] - [필터] 버튼을 클릭해도 자동필터가 적용됩니다.
5. 구분과 적요가 비어있는 행을 필터링한 뒤, 비어있는 값을 하나씩 직접 입력하면 거래별 계정과목과 적요 분류가 완료됩니다.
6. 함수를 값 형태로 변경하기 : 현재 입력된 구분과 적요는 배열함수로 작성되어 있습니다. 배열함수는 엑셀의 처리속도에 영향을 줄 수 있고, 이후 셀 위치가 바뀌면 오류가 발생할 수 있어 값 형태로 변경하겠습니다. 구분과 적요 범위를 선택한 뒤, 우클릭 - 선택하여 붙여넣기에서 값 형태로 붙여넣기 합니다.

   

이제 복식부기 장표를 완성해 보겠습니다. 복식부기를 작성할 때 가장 중요한 원칙은 "왼쪽(차변)과 오른쪽(대변)의 합계가 같아야 한다"는 점입니다. 따라서 은행에서 다운로드한 통장내역의 값을 좌우로 바꿔서 우측 표에 입력합니다.

1. 거래설명의 차변/대변 입력하기 : 예제파일의 F2 셀은 C2 셀의 값을, G2 셀은 B2 셀의 값을 불러옵니다. 이후 수식을 아래로 자동채우기 하면 통장내역의 값이 좌우로 치환되어 입력됩니다.

   

2. 함수를 값 형태로 변경하기 : 차변과 대변 범위를 선택한 뒤 복사합니다. 이어서 우클릭 - 선택하여 붙여넣기에서 값 형태로 붙여넣기 합니다.

   

3. 복식부기 장표 완성하기 : 거래 설명이 입력된 F1:I75 범위를 선택한 뒤 Ctrl + X 로 잘라냅니다. 잘라낸 범위는 B76 셀에 붙여넣기 합니다.

   

4. 날짜 범위인 A1:A75 를 복사한 뒤 A76 셀에 붙여넣기 하면, 방금 잘라서 붙여넣기한 범위와 동일한 높이로 정확히 맞춰 입력됩니다.

   

5. 76행에 입력된 머리글은 우클릭하여 삭제합니다.

   

6. A:E 범위를 선택한 뒤 Ctrl + Shift + L 을 눌러 자동필터를 적용합니다. 이후 날짜를 오름차순으로 정렬하면 복식부기 장표가 완성됩니다.

   

완성된 복식부기 장표를 바탕으로 지출 분석을 위한 피벗테이블을 추가하겠습니다.

1. 피벗테이블 만들기 : 표 안의 아무 셀이나 선택한 뒤, [삽입] - [피벗테이블] 버튼을 클릭하여 피벗테이블 만들기 대화상자를 실행합니다. 대화상자에서 '새 워크시트'에 피벗테이블을 추가합니다.

   

2. [비고] 필드를 행 영역으로, [거래일자] 필드를 열 영역으로 이동합니다.

   

3. 계산필드 추가하기 : 피벗테이블을 선택한 뒤 [피벗테이블 분석] 탭 - [계산 필드] 로 이동하여 새 계산필드를 추가합니다. 이름은 '잔액', 수식은 '입금-출금'으로 입력한 뒤 [추가] 버튼을 누르면 계산필드가 등록됩니다. 등록이 완료되었으면 [확인] 버튼을 눌러 창을 닫습니다.

   

   오빠두Tip : 수식을 입력할 때 필드를 더블클릭하면 수식란에 필드명이 자동으로 입력되어 더 빠르게 수식을 작성할 수 있습니다.
4. 잔액 필드를 값 영역으로 이동하면 각 비고 항목별 지출 여부가 한눈에 정리됩니다.

   

5. 피벗테이블 꾸미기 : 피벗테이블에서 날짜를 우클릭한 뒤 [그룹] 으로 이동합니다. 이어서 날짜를 연과 월로 그룹화합니다.
6. [디자인] 탭 - [부분합] - [부분합 표시 안 함] 을 선택하면 연도별 부분합이 해제되어 피벗테이블 보고서가 한층 깔끔하게 정리됩니다.

   

   오빠두Tip : 또는 단축키 ALT - J - Y - T - D 를 차례대로 누르면 '모든 부분합 표시 안 함' 이 더 빠르게 실행됩니다.

마지막으로 슬라이서를 추가해서 각 계정과목별 지출 현황을 더욱 편리하게 분석해 보겠습니다.

1. 슬라이서 삽입 : 피벗테이블을 선택한 뒤 [피벗테이블 분석] - [슬라이서 삽입] 버튼을 클릭합니다. 슬라이서 삽입 대화상자가 열리면 '구분' 항목을 선택한 뒤 [확인] 버튼을 클릭합니다.

   

2. 슬라이서가 추가되었습니다.
3. 슬라이서 꾸미기 : 슬라이서 스타일에서 원하는 디자인으로 변경하면 슬라이서 추가가 완료됩니다.

   

4. 이제 버튼을 클릭하면 각 계정과목별 수입·지출 내역을 빠르게 분석할 수 있습니다.

   

   오빠두Tip : 키보드 Ctrl 키를 누른 채로 슬라이서 버튼을 클릭하면 여러 개의 버튼이 동시에 선택됩니다.