(财会盲,本文相关称谓除沿用 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」,左上窗口中选择透视表所在工作表,右键 -「查看代码」,粘贴如下代码
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 之类(还有对应的资金去向条目需要撤销筛选后修改),遗漏条目在电子账单上注记,最终修改账目使两者一致(为了对账方便,建议尽可能的按照时间顺序记录账目条目)
倒序记录
不额外处理的话,Ledger 默认是在底部添加新记录的,搜索时会出现先远(期)后近(期)的问题,不大好用。可以通过录制宏的方式来创建一个格式化的热键宏,主要操作:先对整个底账以日期等列为依据进行重新排序,可以根据需要添加辅助列,使底账根据日期等因素倒序排列;再在账簿顶部插入若干空行;最后用拖动的方式将空行右侧单元格填充公式。从而实现在顶部添加新纪录、账目以日期倒序呈现、搜索结果先近后远等要求。空行记录快满的时候热键激活宏,就能先排序再自动插入填充好公式的空行。
谢谢博主提供的 Excel 表格。花了一晚上时间搞明白怎么用复式记账法,感觉以后花钱有谱了。
@板砖加身 欢迎交流反馈~
坚持记录不一定能花钱有谱,但一定可以知道没谱的钱花在哪了……
@Zephur 刚好遇到一个问题,如果是白条这样的消费应该怎么记?关于债务这部分不太明白。
@板砖加身
白条属于债务,相当于一张虚拟信用卡,举个栗子
12-20 充话费 -100「债务」-「白条」
12-20 充话费 100「基本」-「话费」
01-10 还白条 100「债务」 -「白条」
01-10 还白条 -100「资产」-「银行卡」
账目汇总结果,话费多了100,银行卡少了100,白条归零(注意账目日期应与实际情况完全一致,这样才能真实反映科目情况,也方便对账)
债务可以理解为应付而未付的资金,实际不属于自己,在计算净值的时候需要从资产中扣除(还完所有负债,剩下的才是你的真实资产——净值)
与债务概念相对的是应收而未收的资金(如借出的钱、垫付的差旅费),这些属于资产
@Zephur 感谢回复。那么也就是同样理解为记两笔账,只是现在钱从债务那边流出到实际支出那里,等还款之后是钱从资产流入债务清零。保证在两条账目间流动的资金总额为零就完了。
想明白自己为什么会绕进去了:我老想着借来的钱要先在自己手上转一圈才花出去,实际上这笔钱不经过我的钱包,直接去了目的地(
内容更新 添加「Ledger 工作表自动填充日期」
内容更新 添加「账单对账操作」
谢谢博主的模板, 希望以后有新功能持续更新下去
@Billy 多谢支持~
按说 Excel 这种万金油神器,一般需求基本都能实现,不过动力不足加 VBA 苦手,就得用且用了
目前已经用了半年多,感觉还不错
您好,我看到您的三级科目设置里,第三级出现信用卡,这里不太理解,请指教:
——
百度三级科目得到如下信息:
一级科目是总账科目,可以从资产负债表或损益表中或科目表中看到.
二级科目是一级科目的明细分类,是隶属于一级科目,是父子关系.
最简单明白的科目举例:
管理费用—办公费,(只有一级,二级),
管理费用–办公费–报纸杂志费(共三级)
管理费用–办公费–报纸杂志费–行政部(共三级,外加一个核算项目–部门)
——-
我比较希望实现如下分级,但是需要手动输入食品和信用卡两级条目,不知道您是怎么处理的,谢谢
报纸杂志费 信用卡 办公费 债务
食品 储蓄卡 生活 支出
———–
非常感谢!!!!
看到您的上一篇博文,讲到每一笔账目对应两个科目,稍微理解了一些~
另外,我明明分行了,不知道为什么最后成了一段,不好意思~
再请教一下,“期初余额”是什么概念,是不是我应该把记账起始点的每一个科目的起始情况给写出来?
如果是这样,很难确定每一张卡当时的数据,还是说,我应该放弃先前的数据,从现在开始重新记账呢,谢谢。
ps, 您可以考虑将每个部分做成“表”,而不是单纯的区域,这有利于后续的数据透视表效果。
@tkr4finance ……我说的三级科目不是术语,而只是一个(生造的)级别代号,也可以理解为子科目
一级科目一般是固定的大类(「权益」、「资产」、「负债」、「收入」、「支出」,这些倒是术语),子科目就可以自行设置了
示例账目里信用卡放在了第三级只是因为「负债」下面的科目比较少,如果愿意,完全可以将科目设置为:
XX 信用卡(三级科目) – 信用卡(二级科目,与之平行的是其他类型的债务) – 负债(一级科目)
而你所举的例子里的 「报纸杂志费 信用卡 办公费 债务」这个是把资金来源(信用卡)和资金去向(杂志费)混在一起了,这样虽然能方便的记录简单账目,但无法准确的记录复杂账目。
例如一笔杂志费用,用信用卡付了订金,用现金付了尾款,用复式记账可以记成这样
杂志费订金 -100 「XX信用卡」-「信用卡」-「负债」
杂志费尾款 -200「 现金」-「 /」-「资产」
杂志费全款 300 「 办公费」-「 /」-「支出」
虽然繁琐一些,但各个科目的收支清晰直观,更方便各种统计和分析
具体的可以参考之前的《专业开源记账软件 GnuCash 简易上手指南》
(忽略分行是站点主题的原因~)
理论上期初余额就是这个意思,实际操作起来可以以当前时点为基础倒推,兼顾之前的账目和现在各科目的金额
你说的表是指名称管理器吗?这样除了引用的便利还有其他什么优点吗?
感谢回复!
ctrl+L 可以把选定区域转换为表。表不是名称管理器,而是一种可以自动扩展的区域,因此非常适合作为数据透视表的数据来源。此外,表内可以便捷地排序、筛选和汇总。
@tkr4finance 多谢指点,看了下这个「创建表」功能,除了方便引用,主要还有表内自动扩展公式这个优点(其实还有快捷添加汇总行方便求和的功能),在 Ledger 中能够减少不小的手动扩展公式的操作量
@tkr4finance 实际操作了下,对于记录类表格,这种「表」无法插入行的问题太严重,只能放弃了……
我好像还没怎么遇到过问题,不过它的功能倒也可以用些函数实现,所以都行吧~
非常感谢博主~舍弃了随手记 拿着一本会计基础再看复式记账 尝试在excel上自己折腾也没弄出个所以然
@nekokira 会计基础……汗……
@Zephur 是的 国内的会计科目分了六类 多了个成本类 不过如果是个人记账的话成本类和权益类都不太能用上?
@nekokira 个人/家庭记账不用那么复杂,只需要基本理解复式记账的概念就行了,具体可以参考《专业开源记账软件 GnuCash 简易上手指南》里的说明
@Zephur 嗯嗯 之前一昧地想追求标准 反而太复杂了无从下手 所以看到你分享的模板后恍然大悟 现在已经用了半个月 再来感谢你~
还想请教下你 加入是电子产品 日后可能会二手出售的那种 你一般记在什么科目下呢?算支出收入还是固定资产?
@nekokira 有帮助就好,欢迎交流~
(固定资产是一个标准的会计术语,这里用「资产」比较合适)
这个问题我这个财务盲是这么考虑的,对于电子产品,虽然可能会出二手,但是否出手不定,具体售价不定,记到资产里容易造成净值虚高的错觉,毕竟我们也不大可能对其记折旧,所以买入的时候记为支出比较好。卖出的时候自然也不能记为收入,同样记到该类似「电子产品」的支出子科目(记减),这样该科目的数字会互相冲抵,体现真实的消费金额。当然,如果总是能低买高卖,那就应该反过来都记为收入了~
内容更新 添加「倒序记录」
您好,显示Dropbox的模板已删除了,请问还可以在哪里找到呢?
@axn 多谢提醒,Dropbox 共享已恢复,另外下面的百度网盘共享文件也是一样的
透视表的数据源不应该引用的是ledger吗?就是记账那张表,为什么却无法计算?
能否麻烦说下日期自动填充是怎么操作的吗,vba改了表的名字,然后在第三列加了内容,但日期却无法复制上一行的。。。
@lotor 请逐步核对操作,特别是最后一步的保存为 xlsm,还是无法找出原因可以试下《使用 Excel 进行柳比歇夫式时间记录 》中的代码帮助发现问题
@Zephur 感谢回复,不过,因为对vba不了解所以还是找不出原因,明明是同一个工作薄,透视表自动刷新的vba可以正常运行,但底账的自动添加却不可以。代码是没问题的,因为加了代码后,在前一行的内容全写好的条件下,如果第二行是直接从内容列也就是第三列开始写,那么会出现一个弹窗,内容是 .Cells(Target.Row, 2) = Date 这行要求对象,图片在这:https://i0.hdslb.com/bfs/album/ebb40c47bb4bf2e5f156595b6cdfe63cda481876.png https://i0.hdslb.com/bfs/album/8a590422c7e3df8cbe8a5cd13fd5d860b7a2169e.png,真的就是我在某一处的操作存在问题
@lotor 我对 vba 也不了解……
不过你这个问题可以试下把 .Cells(Target.Row, 2) = Date 中 的 Date 改成 Date() 或者 Format(Now, “yyyy-mm-dd”) 或者 Format(Date, “yyyy-mm-dd”) 看看,
如果还是出问题可以试试把 Date 改成 “Date”,修改后如果自动填充 “Date” 字符串就能(排除其他问题)明确是你的 Excel 处理不了 Date 这个函数
感觉是某种 Ecxel 版本或者设置问题?
@Zephur 好的,感谢又提供了一个方法,我会再尝试一次,如果不行的话,也不打算马上解决这问题了,因为虽然当前没时间去琢磨vba,但是借鉴了“倒序记录”中的方法,用录制宏也间接实现了自动添加日期
日期自动填充的 .Cells(Target.Row, 2) = Date代码多了一个.导致运行时有问题,我到网上去查找了很多相关问题,然后试着删去就能正确执行你所描述的功能了,真是瞎猫碰到了死耗子