使用 Excel 2007 进行家庭记账

• 下载源文件 - 46.1 KB

引言

本文提出了一种简单的方法来进行家庭记账。只需要 Excel。它基于复式记账法。在此系统中，每笔交易都有两个账户，第一个账户被减去，第二个账户被加上。例如，当您从杂货店购物时，10 欧元将从钱包账户中减去，10 欧元将添加到食品账户。当您收到工资时，1000 欧元将从收入账户（代表您为雇主提供的服务）中减去，1000 欧元将添加到您的银行账户。当您用信用卡购买汽油时，50 欧元将从信用卡账户中减去，50 欧元将添加到出行账户。当信用卡账单支付时，200 欧元将从银行账户中减去，200 欧元将添加到信用卡账户。当所有交易记录完成后，很容易得到每个账户的总支出金额以及账户余额。账户余额可以与银行账户的实际余额进行比较。提供的 Excel 表格每笔交易有两行。提供了一个用户窗体来编辑和添加交易，包含一个数据透视表来显示收入与支出，以及另一个数据透视表显示余额。以下快照提供了概览。

提供的 Excel 表格也可以作为如何构建用户窗体和数据透视表的示例。

会计科目表

账户分为三个级别。账户树称为会计科目表。会计科目表通常有 5 个顶级账户：资产、负债、权益、收入和费用。年初，以下等式成立：

Assets = Liability + Equity

或等价地

Equity = Assets - Liability

年末，净收益或净亏损将转入权益。净收益或净亏损计算如下：

Net Gain or Loss = Income - Expenses

当上述值为正时，表示收益，如果值为负，则表示亏损。

在提供的 Excel 表格中，负债、权益和收入为负值。因此，上述公式需要更改为：

Assets = -Liability - Equity

或等价地

Equity = -(Assets + Liability)
        
        Net Gain or Loss = -(Income + Expenses)

每个人都可以创建自己的会计科目表。在提供的 Excel 表格中，为约翰和凯特这对夫妇构建了一个会计科目表。

• 资产
  • ABC 银行
    • 账户 123456
  • DEF 银行
    • 账户 234567
  • 现金
    • 约翰的钱包
  • 现金
    • 凯特的钱包
• 权益
  • 期初余额
    • 期初余额
• 费用
  • 约翰的车
    • 汽油
  • 凯特 Car
    • 汽油
  • 沟通
    • 固定电话
    • 约翰的手机
    • 凯特的手机
  • 食品
    • 家乐福
    • 德拉艾泽
    • 杂货
  • 娱乐
    • 约翰的健身房
    • 凯特的健身房
    • 酒店
    • 博物馆
    • 餐厅
  • 公用事业
    • 电力
  • 抵押贷款利息
    • 抵押贷款 1
• 收入
  • John
    • 薪资
  • 凯特
    • 薪资
• 抵押贷款负债
  • ABC 银行
    • 抵押贷款 1
• 短期负债
  • ABC 银行
    • 约翰的 Visa 卡
  • DEF 银行
    • 凯特的 Visa 卡

顶级负债账户已拆分为“抵押贷款负债”和“短期负债”。原因是，在收入和支出数据透视表中查看抵押贷款还款很有用。可以立即在饼图中看到，如果收入部分超过半个圆。如果是这种情况，收入足以支付费用和抵押贷款还款。

事务

一笔交易包含以下项目：

1. 编号：交易的唯一编号
2. 日期：交易日期
3. 第一个账户：资金流出的账户
4. 第二个账户：资金流入的账户
5. 描述：交易的描述
6. 金额：交易涉及的金额

我更喜欢使用“第一个账户”和“第二个账户”，而不是会计中使用的“借方”和“贷方”账户，因为对于非会计人员来说，“借方”和“贷方”有点令人困惑。例如，在会计术语中，用现金购买食品时，现金账户被贷记，食品账户被借记。提供的 Excel 表格每笔交易有两行。第一行包含资金流出的账户，第二行包含资金流入的账户。这两行具有相同的交易编号、相同的金额但符号相反，以及相同的描述。

期初余额

等式“权益 = -(资产 + 负债)”意味着权益分配给资产和负债。年初，权益将转入不同的资产和负债账户。例如，要设置银行账户的期初余额，将进行以下交易：

日期	1 级	2 级	3 级	描述	金额
1/1/2015	权益	期初余额	期初余额	期初余额	-1.000,00
1/1/2015	资产	ABC 银行	账户 123456	期初余额	1.000,00

这意味着，从权益中，将 1000 欧元添加到 ABC 银行的账户 123456。对于抵押贷款的期初余额，将进行以下交易：

日期	1 级	2 级	3 级	描述	金额
1/1/2015	权益	期初余额	期初余额	期初余额	100.000,00
1/1/2015	抵押贷款负债	ABC 银行	抵押贷款 1	期初余额	-100.000,00

费用交易

要记录用现金支付的博物馆参观费用，将进行以下交易：

日期	1 级	2 级	3 级	描述	金额
1/1/2015	资产	现金	约翰的钱包	考古博物馆	-30,00
1/1/2015	费用	娱乐	博物馆	考古博物馆	30,00

要记录用信用卡购买的汽油，将进行以下交易：

日期	1 级	2 级	3 级	描述	金额
16/1/2015	短期负债	ABC 银行	凯特的 Visa 卡	20 升	-40,00
16/1/2015	费用	凯特 Car	汽油	20 升	40,00

抵押贷款还款

抵押贷款还款包括本金部分和利息部分。因此，将写入两笔交易。本金部分减少抵押贷款负债，而利息部分增加费用。

日期	1 级	2 级	3 级	描述	金额
31/1/2015	资产	ABC 银行	账户 123456	2015 年 1 月	-900,00
31/1/2015	抵押贷款负债	ABC 银行	抵押贷款 1	2015 年 1 月	900,00
31/1/2015	资产	ABC 银行	账户 123456	2015 年 1 月	-100,00
31/1/2015	费用	抵押贷款利息	抵押贷款 1	2015 年 1 月	100,00

收入交易

收入交易减少收入账户并增加银行账户。如果有多个客户或项目，可以为每个客户或项目创建一个账户，以便年末可以按客户和项目细分收入来源。对于月薪，将进行以下交易：

日期	1 级	2 级	3 级	描述	金额
28/2/2015	收入	John	薪资	2015 年 2 月	-1.000,00
28/2/2015	资产	ABC 银行	账户 123456	2015 年 2 月	1.000,00

用户窗体 UI

可以通过填写上述单元格来添加交易。无需定义会计科目表。会计科目表是隐式生成的，由在级别 1、2 和 3 列中输入的不同值构建。用户必须注意在编号、描述列中输入相同的值，并在金额列中输入相反的值。提供了一个用户窗体来更轻松地编辑、添加和删除交易。要显示用户窗体，请单击工作表顶部的“编辑”按钮。

用户窗体显示当前活动单元格中的交易。

组合框分别填充了第 1、2 和 3 级列中的不同值。但是，组合框允许输入列表中不存在的值。

Add

通过单击“添加”按钮，交易编号将设置为最后一个交易编号加 1。其余字段保持不变。可以修改字段，按下“保存”后，交易将在最后一个交易之后保存。

Insert

通过单击“插入”按钮，当前交易将被复制并插入到当前交易之后。当前交易之后的所有交易编号将增加 1。

删除

通过单击“删除”按钮，当前交易将被删除，所有后续交易将向上移动，其交易编号将减 1。

保存

将用户窗体中的当前值保存到 Excel 工作表。第一行中的金额列将等于字段中的金额（符号相反），而第二行中的金额列将等于字段中的金额（符号相同）。

取消

卸载窗体，与单击窗口的 X 按钮相同。

第一条、上一条、下一条、最后一条

• <<：移至第一笔交易
• <：移至上一笔交易
• >：移至下一笔交易
• >>：移至最后一笔交易

用户窗体代码

代码是用 VBA 编写的。要查看代码：

1. Office 按钮/Excel 选项/常用：勾选“在功能区显示‘开发工具’选项卡”。
2. “开发工具”选项卡/Visual Basic：打开 Visual Basic 编辑器。
3. 双击 VBA 项目/窗体/UserFormTransaction：打开用户窗体。
4. 双击任意按钮：打开代码窗口。

此处提供了方法简述。

• UserForm_Activate

  当窗体加载时执行。如果活动单元格位于交易的第二行，则活动单元格将向上移动一行。活动单元格始终设置为第一列。窗体变量 `currentCell` 存储用户窗体中显示的当前交易的位置。

• SheetToForm

  将 `currentCell` 处的交易的工作表单元格复制到用户窗体的字段中。`ComboboxDL1` 将用第 2 列（偶数行）的唯一值填充。`ComboboxDL2` 将用第 2 列（偶数行）的唯一值填充，其中 `Level1` 等于 `ComboboxDL1` 中的值。`ComboboxDL3` 将用第 3 列（偶数行）的唯一值填充，其中 `Level2` 等于 `ComboboxDL2` 中的值。`ComboboxCL1` 将用第 1 列（奇数行）的唯一值填充。`ComboboxCL2` 将用第 2 列（奇数行）的唯一值填充，其中 `Level1` 等于 `ComboboxCL1` 中的值。`ComboboxCL3` 将用第 3 列（奇数行）的唯一值填充，其中 `Level2` 等于 `ComboboxCL2` 中的值。相应地启用或禁用“第一条”、“上一条”、“下一条”和“最后一条”按钮。

• FormToSheet

  将窗体中的字段复制到 `currentCell` 处的交易的工作表单元格中。

• FillComboBox

  使用工作表中的值填充 `combobox` 的项目集合。

  • `parentText`: 父级 `combobox` 的文本
  • `parentColNr`: 父级列号
  • `childColNr`: 子级列号，即要填充的组合框的列号
  • `combobox1`: 要填充的 `combobox`
  • `startRowNr`: 开始获取唯一值的起始行号

  当 `combobox` 没有父级时，`parentText` 和 `parentColNr` 为空。在填充 `combobox` 时，将窗体变量 `fillingComboBox` 设置为 `True`。问题在于，当调用 `combobox1.Clear` 时，会触发 `combobox` 的更改事件，这会导致子级 `combobox` 被填充。然而，在父级 `combobox` 填充之前不应执行任何操作。通过设置此变量可以实现此目的。`combobox` 中的值通过 `QuickSort` 方法按字母顺序排序。

• QuickSort

  使用 `Quicksort` 算法就地排序数组。代码从 Konrad Rudolph 在 StackOverflow 上的回答中复制。

• CommandButtonAdd_Click

  通过 `currentCell.CurrentRegion.End(xlDown)` 找到最后一个单元格。将 `TextBoxNr.Text` 设置为最后一个单元格的值加 1。将活动单元格和 `currentCell` 设置为最后一个单元格的下一行。

• CommandButtonInsert_Click

  选择从 `currentCell` 到末尾的区域，并将其复制到下面两行。设置复制区域中的交易编号（编号列）。

• CommandButtonDelete_Click

  选择当前交易的两行，并通过指定向上移动其他单元格来删除它们。如果被删除的交易不是最后一笔，则重新编号交易编号（编号列）。

• CommandButtonSave_Click

  通过调用 `SheetToForm` 将用户窗体中的数据复制到工作表中。还会调用 `FormToSheet` 来刷新 `combobox` 中的项目，因为它们可能已因保存操作而更改。此外，将遍历当前工作表中的所有数据透视表，以更新数据透视表的数据源。

数据透视表

该工作表包含一个数据透视表和一个数据透视图。它们都以 A 列到 G 列中的交易作为数据源。

收入与支出

收入与支出数据透视图如下所示。

单击数据透视表中的任何单元格，顶部功能区会出现“数据透视表工具”按钮，单击该按钮即可出现相应的功能区。单击“字段列表”会显示“数据透视表字段列表”。

行标签定义为第 1、2 和 3 列，汇总值定义为金额列的总和。在报表筛选器区域，定义了描述列。在 Excel 工作表中，描述筛选器选中了所有项目，但“期初余额”除外。

行标签筛选器选中了“费用”、“收入”和“抵押贷款负债”项目。因此，此数据透视图显示了收入与费用和抵押贷款还款。

余额

这是一个正常的、没有任何筛选的数据透视表。总和应为 0。如果总和不为 0，则表示一笔交易的两侧金额不相等。数据透视表可以向下钻取到第 3 级。在此数据透视表中，可以核对银行账户、钱包和抵押贷款。

开启新的一年

我建议为每一年创建一个工作表。要复制工作表，请右键单击工作表名称，选择“移动或复制”，在对话框中选择“(移至末尾)”和“创建副本”。将新创建的工作表重命名为您想要的年份。

数据透视表仍然引用原始工作表中的数据。要更改新创建工作表中数据透视表的数据源，请单击“编辑”按钮显示一笔交易，然后单击“保存”。保存操作会将数据透视表中的数据源更新为当前工作表中的数据。

资产和负债账户的期初余额将是上一年末的结余。结余在“余额”数据透视表中给出。在新的一年里，需要像前一年的期初余额交易一样记录期初余额交易，但金额要使用“余额”数据透视表中给出的金额。

结论

可以使用简单的 Excel 工作表进行家庭记账。优点是无需安装其他软件，并且易于根据个人需求进行定制。

历史

1. Excel 表格，每笔交易占两行，包含用户窗体和两个数据透视表。
2. 已更正金额的保存和组合框的填充。