회계 몰라도 누구나 가능한 엑셀 복식부기 작성 | 초간단 관리법

은행 통장내역을 피벗테이블과 함수만 사용해서 복식부기로 손쉽게 관리하는 방법을 순서대로 알아봅니다.

홈페이지 » 회계 몰라도 누구나 가능한 엑셀 복식부기 작성 | 초간단 관리법

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

엑셀 복식부기 작성 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [실무기초] 엑셀 복식부기 관리, 정말 쉬운 관리방법
    예제파일
  • [실무기초] 엑셀 복식부기 관리, 정말 쉬운 관리방법
    완성파일
  • [실무기초] 계정과목 총괄표
    보충파일

.

이전 강의와 이어지는 내용이 있습니다

단식부기와 복식부기의 차이점계정과목을 자동으로 분류하는 공식
에 대한 자세한 설명은 아래 강의를 참고하세요.


각 항목별 적요/계정과목 분리하기 (공식활용)

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

  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. 함수를 값 형태로 변경하기 : 현재 입력된 구분과 적요는 배열함수입니다. 따라서 엑셀 처리속도에 영향을 줄 수 있고, 이후 셀 위치가 바뀌면 문제가 발생할 수 있어 값 형태로 변경하겠습니다. 구분과 적요 범위를 선택 후, 우클릭 - 선택하여 붙여넣기에서 값 형태로 붙여넣기 합니다.

    함수 값 형태 붙여넣기
    입력한 구분/적요를 값 형태로 붙여넣기 합니다.

복식부기 장표 완성하기

복식부기 장표를 완성하겠습니다. 복식부기를 작성할 때 가장 중요한 것은, "왼쪽(차변)과 오른쪽(대변)의 합계가 같아야 한다"는 것입니다. 따라서 은행에서 다운받은 통장내역의 값을 왼쪽<->오른쪽을 바꿔서 우측 표에 입력합니다.

오빠두Tip : 실제 기업 회계에서는 하나의 거래에 여러 계정과목이 들어가거나, 자산의 변동 없이 자본/부채 변동만 발생하는 상황도 있어 정확한 회계 지식이 필요합니다. 하지만 일반 개인이 작성하는 가계부의 경우, 오늘 강의에서 소개해드린 방법만 적용해도 충분히 체계적인 자산관리를 할 수 있습니다.
  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. 이제 버튼을 클릭하면 각 계정과목별 수입/지출 내역이 빠르게 분석됩니다.
    엑셀 복식부기 피벗테이블 완성
    Ctrl 키를 누른채 버튼을 클릭하면 여러 버튼이 동시에 선택됩니다.
    오빠두Tip : 키보드 Ctrl 키를 누른 채로 슬라이서 버튼을 클릭하면 여러개의 버튼이 동시에 선택됩니다.
5 2 투표
게시글평점
guest
2 댓글
Inline Feedbacks
모든 댓글 보기
카이로스
카이로스
2021년 8월 21일 5:22 오전
게시글평점 :
     

좋은 내용 감사합니다.

3차원벤딩
3차원벤딩
2021년 10월 19일 3:31 오후
게시글평점 :
     

잘 따라해보겟습니다. ^^*

2
0
여러분의 생각을 댓글로 남겨주세요.x