使用 Excel 进行复式记账

/ 8评 / 0

(财会盲,本文相关称谓除沿用 GnuCash 翻译外均系生造,达人多指正)

折腾背景

之前 用了 GnuCash 进行复式记账,在充分体验了 GnuCash 的强大之后……问题接踵而至:

首先,作为一个版本号 2.6 似乎开发了十七年的软件,GnuCash 表示目前还无法导入自身导出的交易记录文件(csv) ……那么要如何导入呢,请先把记录转成 qif(Quicken) 格式——这当然不是最糟糕的——在去年的时候开发者就表示这个问题可能在今年年底的 2.8 版本到解决——这仍然不是最糟糕的——抱着希望去看了下官方页面,开发者表示这个问题在 2.7 中已经解决了,但是—— 2.7 版本已知问题的第一条是「 Windows 客户端无法启动」……

然后,之前看到 GnuCash 报表菜单按钮诸多设置复杂天真的以为这是它的强项,结果到具体使用的时候才发现——GnuCash 似乎只能生成一些花里胡哨的饼图、条形图——搜索中发现在软件 Wiki 中有达人指导如何实现数据汇总——先导出交易记录至 Excel……

于是,本着折腾到底的精神尝试从 GnuCash 转回 Excel。Excel 的统计功能吊打 GnuCash,但相应的在账目录入等其他方面操作略微繁琐,目前已基本实现个人/家庭财务的记录、统计和分析,记录如下

表格模板

Dropbox 预览及下载 (弹出 Dropbox 注册面板可关闭)

百度盘下载 (仅能预览 Reports 工作表,提示页面不存在请刷新)

设置思路

因为分割交易的存在(一笔交易记录中包含多个资金去向或来源),为了精确记录资金流向,采用了类似 GnuCash 的多行记录模式。为了操作方便,金额栏仅设一列,以数字正、负区分。以底账为基础通过透视表实现自定义统计功能。

特色功能

多样的数据汇总 可以以任意日期范围任意科目筛选汇总分析,如以月为单位的多级科目收支记录

强大的预算功能 根据类别设定每月常规支出预算,根据日期设定大宗开支预算,进而可以测算未来各时点的净值

灵活的表格操作 支持表格内容复制粘贴,像包含多项物品的电子订单,可以稍加整理直接粘贴至工作表。底账通过筛选操作直接复制出科目账单,方便对账

工作表说明

Ledger 底账

A1 全账金额汇总 账目无误应为0(不为零表示有账目错误,可切换至 Check 校核表 确定错误账目日期以便修改)

D:D 金额列 以记录科目为基准,出为正,入为负(例如购买,消费科目为正,资金科目为负,而收入则是资金科目为正,收入科目为负)

E:E 成员标识 需要细分到人或者项目的记录可填

F:H 科目标识 考虑到科目的细分程度和复杂程度,建议设定三级科目,具体见 Accounts 科目表,其中 三级科目 (F 列)需手动填写(建议英文或拼音,方便自动补全),二级科目(G 列)、一级科目(H 列)通过公式根据 Accounts 工作表自动匹配

I:I 年度标识 自定义账目年度,方便根据个人财务年度统计,需手动填充,可以春节之类日期分界

J:J 月度标识 配合年度标识,通过公式将上年度的1月、2月替换为13月、14月,方便透视表汇总

K:K 对账标识 类似 GnuCash 的概念,(手工)对账后可以(手动)标识

L:L 预算标识 将预算类账目标记为 P 之类字样(普通账目留空),这样在常规统计中可以剔除预算内容,仅在需要考虑预算的情景下引用

记录说明 每笔账目至少包括两行。对于常规交易,两行记录,日期相同,内容相同,金额相反(一正一负)。对于分割交易,三行以上记录,日期相同,内容和科目根据具体的资金流向分别记录,具体操作可参考 使用 GnuCash 进行复式记账

日期内容金额年度月度
2017/11/20cloudlet.*-9000.00信用卡/债务1711
2017/11/20cloudlet.*-1000.00现金/资产1711
2017/11/20cloudlet.info2000.00域名其他支出1711
2017/11/20cloudlet.com2000.00域名其他支出1711
2017/11/20cloudlet.net2000.00域名其他支出1711
2017/11/20cloudlet.org2000.00域名其他支出1711
2017/11/20cloudlet.cc2000.00域名其他支出1711

Accounts 科目表

科目建议全小写英文(拼音) 方便键入及自动补全,一级科目通常是权益、资产、债务、收入、支出五类,可根据需要自定义其他级别科目

No
1域名其他支出
2工资/收入
3现金/资产
4信用卡/债务
5期初余额/权益

Reports 财务状态表

使用透视表分别统计净值、资产、债务、当月开销、详细当月开销

净值资产债务
预算(空白)预算(空白)预算(空白)
(多项)资产债务
(全部)(全部)(全部)
求和项:金额汇总求和项:金额汇总求和项:金额
汇总500汇总9500汇总
信用卡-9000
总计-9000
月开销月开销详细
年度17年度17
月度11月度11
支出支出
求和项:金额求和项:金额
汇总汇总
其他10000其他
总计10000域名10000
总计10000

Expense/Income 月支出/收入汇总表

年度17
支出
求和项:金额月度
67891011总计
其他1001001001001001000010500
域名1001001001001001000010500
总计1001001001001001000010500
年度分项
年度17
收入
求和项:金额月度
5总计
/10001000
工资10001000
总计10001000

AssetsFlow 时点净值表

这个生造的说法有点拗口,具体反应的是汇总预算数据后,账目涉及的各个时间节点的净值数据

预算(全部)
(多项)
求和项:金额
日期汇总
2017/3/1100001000010000
2017/5/201000100011000
2017/6/20-100-10010900
2017/7/20-100-10010800
2017/8/20-100-10010700
2017/9/20-100-10010600
2017/10/20-100-10010500
2017/11/20-10000-10000500
2048/1/1100000100000100500
2048/1/2-100000-100000500
总计500

BudgetEx 预算月度表

预算月度汇总

预算(全部)
支出
年度47
求和项:金额月度
13总计
其他100000100000
总计100000100000

Check 校核表

显示每日账目金额汇总,辅助快速定位错误账目

预算(全部)
(全部)
求和项:金额
行标签汇总
2017/10/200.00
2017/11/200.00
2048/1/10.00
2048/1/20.00
总计0.00

其他说明

透视表批量自动刷新

在工作表加入相关 VBA 代码可在打开工作表时自动刷新指定透视表,而根据 Excel 的机制,刷新一透视表后,工作表内其他数据源相同的透视表也会同时刷新

具体操作,「开发工具」-「Visual Basic」,左上窗口中选择透视表所在工作表,右键 -「查看代码」,粘贴如下代码

Private Sub Worksheet_Activate()
        Sheets("Report").PivotTables("数据透视表1").RefreshTable
End Sub

(注意代码中的「数据透视表1」需实际存在,可在点击透视表 - 「数据表透视工具」- 「选项」面板查看各透视表名称。Sheets("Report") 即 Report 工作表,也需要手动替换为目标工作表名)

之后将工作簿保存为 「Excel 启用宏的工作簿」(xlsm),即可实现(录入底账后)切换到特定工作表后该工作表内所有透视表自动刷新显示实时结果(因安全原因此代码未加入模板,请手动设置)

Ledger 工作表自动填充日期

可实现在内容列添加内容后,日期列自动复制上一行日期,修改内容日期不变。(VBA 代码,添加方法同上,注意修改「Sheet2」为 Ledger 工作表实际名称。VBA 盲,删除多单元格时会报错,不影响使用)

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheet2
        If Target.Column = 3 Then
            If Target.Offset(0, -1) = "" Then
            .Cells(Target.Row, 2) = Date
            End If
        Else: Exit Sub
        End If
    End With
End Sub

账单对账操作

(通常的账单操作主要是信用卡,信用卡的账目虽然可以通过额度校对,但操作比较麻烦,需要额外操作获取卡片当前额度(往往还需要输密码之类操作),碰到账目不一致又需要重新通过电子账单对账,所以不如一开始就开始电子账单对账)

电子账单对账具体操作:通过筛选三级科目定位卡片,以电子账单为基准(重要,以 ledger 工作表为基准比较可能会遗漏账目)逐条核对数字,填入 R 标记,错误条目可简单修改后标记 W 之类(还有对应的资金去向条目需要撤销筛选后修改),遗漏条目在电子账单上注记,最终修改账目使两者一致(为了对账方便,建议尽可能的按照时间顺序记录账目条目)

相关文章

8条回应:“使用 Excel 进行复式记账”

  1. 板砖加身说道:

    谢谢博主提供的 Excel 表格。花了一晚上时间搞明白怎么用复式记账法,感觉以后花钱有谱了。

    • Zephur说道:

      @板砖加身 欢迎交流反馈~
      坚持记录不一定能花钱有谱,但一定可以知道没谱的钱花在哪了……

      • 板砖加身说道:

        @Zephur 刚好遇到一个问题,如果是白条这样的消费应该怎么记?关于债务这部分不太明白。

        • Zephur说道:

          @板砖加身

          白条属于债务,相当于一张虚拟信用卡,举个栗子

          12-20 充话费 -100「债务」-「白条」

          12-20 充话费 100「基本」-「话费」

          01-10 还白条 100「债务」 -「白条」

          01-10 还白条 -100「资产」-「银行卡」

          账目汇总结果,话费多了100,银行卡少了100,白条归零(注意账目日期应与实际情况完全一致,这样才能真实反映科目情况,也方便对账)

          债务可以理解为应付而未付的资金,实际不属于自己,在计算净值的时候需要从资产中扣除(还完所有负债,剩下的才是你的真实资产——净值)

          与债务概念相对的是应收而未收的资金(如借出的钱、垫付的差旅费),这些属于资产

          • 板砖加身说道:

            @Zephur 感谢回复。那么也就是同样理解为记两笔账,只是现在钱从债务那边流出到实际支出那里,等还款之后是钱从资产流入债务清零。保证在两条账目间流动的资金总额为零就完了。
            想明白自己为什么会绕进去了:我老想着借来的钱要先在自己手上转一圈才花出去,实际上这笔钱不经过我的钱包,直接去了目的地(

  2. Zephur说道:

    内容更新 添加「Ledger 工作表自动填充日期」

    内容更新 添加「账单对账操作」

  3. Billy说道:

    谢谢博主的模板, 希望以后有新功能持续更新下去

    • Zephur说道:

      @Billy 多谢支持~

      按说 Excel 这种万金油神器,一般需求基本都能实现,不过动力不足加 VBA 苦手,就得用且用了

      目前已经用了半年多,感觉还不错

发表评论

电子邮件地址不会被公开。 必填项已用*标注