使用 Excel 进行复式记账

Posted on | In

（财会盲，本文相关称谓除沿用 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/20 cloudlet.* -9000.00 信用卡 / 债务 17 11
2017/11/20 cloudlet.* -1000.00 现金 / 资产 17 11
2017/11/20 cloudlet.info 2000.00 域名 其他 支出 17 11
2017/11/20 cloudlet.com 2000.00 域名 其他 支出 17 11
2017/11/20 cloudlet.net 2000.00 域名 其他 支出 17 11
2017/11/20 cloudlet.org 2000.00 域名 其他 支出 17 11
2017/11/20 cloudlet.cc 2000.00 域名 其他 支出 17 11

Accounts 科目表

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

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

Reports 财务状态表

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

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

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

年度 17              
Ⅰ 支出              
                 
求和项:金额   月度            
Ⅱ Ⅲ 6 7 8 9 10 11 总计
其他   100 100 100 100 100 10000 10500
  域名 100 100 100 100 100 10000 10500
                 
总计   100 100 100 100 100 10000 10500
年度分项      
年度 17    
Ⅰ 收入    
       
求和项:金额   月度  
Ⅱ Ⅲ 5 总计
/   1000 1000
  工资 1000 1000
       
总计   1000 1000

AssetsFlow 时点净值表

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

预算 (全部)    
Ⅰ (多项)    
       
求和项:金额      
日期 汇总    
2017/3/1 10000 10000 10000
2017/5/20 1000 1000 11000
2017/6/20 -100 -100 10900
2017/7/20 -100 -100 10800
2017/8/20 -100 -100 10700
2017/9/20 -100 -100 10600
2017/10/20 -100 -100 10500
2017/11/20 -10000 -10000 500
2048/1/1 100000 100000 100500
2048/1/2 -100000 -100000 500
总计 500    

BudgetEx 预算月度表

预算月度汇总

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

Check 校核表

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

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

其他说明

透视表批量自动刷新

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

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

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

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

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

Ledger 工作表自动填充日期

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

1
2
3
4
5
6
7
8
9
10
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 之类（还有对应的资金去向条目需要撤销筛选后修改），遗漏条目在电子账单上注记，最终修改账目使两者一致（为了对账方便，建议尽可能的按照时间顺序记录账目条目）

倒序记录

不额外处理的话，Ledger 默认是在底部添加新记录的，搜索时会出现先远（期）后近（期）的问题，不大好用。可以通过录制宏的方式来创建一个格式化的热键宏，主要操作：先对整个底账以日期等列为依据进行重新排序，可以根据需要添加辅助列，使底账根据日期等因素倒序排列；再在账簿顶部插入若干空行；最后用拖动的方式将空行右侧单元格填充公式。从而实现在顶部添加新纪录、账目以日期倒序呈现、搜索结果先近后远等要求。空行记录快满的时候热键激活宏，就能先排序再自动插入填充好公式的空行。