财务建模入门阅读约 5 分钟

如何在 Google Sheets 中创建收入追踪规则

了解什么是收入追踪规则，以及如何在 Google Sheets 中使用公式、数据有效性和 SUMIF 函数自动分类和汇总收入。

收入追踪规则是您在电子表格中定义的基于公式的条件，可以自动分类、标记和汇总收入交易——这样，您无需手动逐行检查，电子表格会自动处理这些工作。本指南将带您从数据结构到自动化汇总，完整地在 Google Sheets 中构建一个收入追踪规则系统。

开始前的准备

一个具有电子表格编辑权限的 Google Sheets 账户
熟悉 Google Sheets 基础公式（SUM、IF）
一份收入交易清单，或准备好创建示例数据
设置完整系统需要 20-30 分钟

分步指南

构建收入日志结构

在创建规则之前，您需要一个一致的数据结构。只有当每一行交易都具有相同位置上的相同列时，收入追踪规则才能可靠地工作。

A 列：日期 — 使用实际日期，而不是文本字符串（格式为 MM/DD/YYYY）
B 列：来源 — 客户名称、雇主、平台（Upwork、Stripe、直接发票）
C 列：金额 — 原始数字，单元格中不含货币符号
D 列：分类 — 规则将在此处写入输出
E 列：状态 — 已收到、待处理或逾期
F 列：备注 — 其他任何不适合其他列的内容

Pro Tip

冻结第 1 行（查看 → 冻结 → 1 行），这样当日志超过 100 多行时，您的表头仍然可见。

定义收入分类

收入追踪规则只能分类您已经命名过的内容。在编写任何公式之前，列出您的收入所属的每个分类，并确定准确的标签字符串——因为 SUMIF 和 IFS 函数对大小写不敏感，但对拼写敏感。按用户类型的常见分类集合：

业务类型	分类
自由职业者	咨询、固定费用、项目、推荐
SaaS 创始人	月度经常性收入、一次性、试用转化、退款
财务分析师	营业收入、其他收入、利息、补助金
副业	产品销售、服务、联盟、被动收入

选择您的分类集合，并将它们写入 Config 工作表的 A 列中，从 A2 开始，在 A1 中加入"收入分类"作为表头。您将在下一步中使用此范围进行数据有效性验证。

Pro Tip

保持标签简短——不超过 20 个字符——这样它们在数据透视表汇总和图表中显示整洁。

应用数据有效性锁定分类输入

规则就在这里。没有有效性验证，某人（包括未来的您）可能在一行中输入"consulting"，在下一行中输入带尾部空格的"Consulting "。您的 SUMIF 函数随后将静默地漏掉第二项。

在 Income_Log 工作表中选择 D 列（D2:D1000 涵盖 999 个交易行）
转到数据 → 数据有效性
将条件设置为"下拉列表（从范围中）"，并输入 Config!$A$2:$A$20
将无效数据处理设置为"拒绝输入"——而不仅仅是警告
选中"在单元格中显示下拉列表"

Pro Tip

如果您从银行导出或 Stripe CSV 导入交易，请在粘贴到日志之前添加一个清理列，使用 VLOOKUP 函数将导入的描述映射到您的分类列表。

编写自动分类公式

对于导入的数据或批量输入，手动为 200 行选择分类太繁琐。IFS 公式可以根据来源名称模式自动分配分类。在 D 列中，将手动输入替换为：

=IFS(
  ISNUMBER(SEARCH("stripe", B2)), "月度经常性收入",
  ISNUMBER(SEARCH("paypal", B2)), "项目",
  ISNUMBER(SEARCH("retainer", B2)), "固定费用",
  ISNUMBER(SEARCH("interest", B2)), "利息",
  TRUE, "未分类"
)

工作原理：

SEARCH 函数不区分大小写，如果找到文本则返回位置号，如果未找到则返回错误
ISNUMBER 将其转换为 TRUE/FALSE
IFS 从上到下评估条件并返回第一个匹配项
最后的 TRUE, "未分类" 是捕获所有未匹配的项

Pro Tip

自动分类运行后，您可以手动覆盖单个单元格——公式仅填充未被触及的单元格。如果需要审计追踪，请添加一个辅助列来标记手动覆盖。

构建分类合计的 SUMIF 规则

现在追踪变得可见。在单独的 Summary 工作表上，编写 SUMIF 公式来按分类和时间段获取合计。基本的按分类的月度合计：

=SUMIFS(
  Income_Log!$C:$C,
  Income_Log!$D:$D, A2,
  Income_Log!$E:$E, "已收到",
  Income_Log!$A:$A, ">="&DATE(2026,1,1),
  Income_Log!$A:$A, "<"&DATE(2026,2,1)
)

按以下方式设置 Summary 工作表：

A（分类）	B（1 月）	C（2 月）	D（3 月）	E（Q1 合计）
咨询	[SUMIFS]	[SUMIFS]	[SUMIFS]	=SUM(B2:D2)
固定费用	...

A 列从 Config!$A$2:$A$20 获取数据。按列更改日期范围。此结构意味着添加新月份只需复制一列，添加新分类后会自动出现在汇总中。

Pro Tip

对于多年期模型，用动态期间选择器替换硬编码的 DATE() 值——将开始日期放在名为 ReportStart 的命名范围中，并在所有 SUMIFS 中引用它。

添加条件格式标志

条件格式将您的收入追踪规则转变为视觉预警系统。两个最有用的标志：逾期应收账款和异常大额交易。标记逾期收入（状态 = 待处理且日期超过 30+ 天）：

选择 E2:E1000
格式 → 条件格式
自定义公式：=AND(E2="待处理", A2<TODAY()-30)
填充颜色：红色
选择 C2:C1000
自定义公式：=C2>5000（根据您的上下文调整阈值）
填充颜色：黄色

Pro Tip

添加第三条规则以标记相同日期来自同一来源的重复金额：=COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2, $C$2:$C$1000, C2)>1。它不会捕获所有重复条目，但会捕获明显的。

使用 ModelMonkey 自动化规则维护

该系统中的手动摩擦点是当您的收入来源变化时更新分类规则，以及当您添加新期间或分类时重建 SUMIFS。这就是 ModelMonkey 的作用所在。与其每个月编辑 IFS 公式或拖动列，不如：

在 Google Sheets 侧边栏中打开 ModelMonkey
告诉它："将 3 月添加到 Summary 工作表，并更新所有 SUMIFS 以包括新期间"
或："我有一个名为 'Substack' 的新收入来源——将其添加到咨询分类中并更新我的自动分类公式"

总结

您现在拥有一个完整的收入追踪规则系统：结构化数据输入、强制分类验证、基于来源的自动分类、基于 SUMIF 的汇总，以及逾期或异常交易的视觉标志。该系统设计用于增长——新分类进入 Config 工作表，新期间只需复制一列，SUMIFS 会自动更新而无需重建。

这暴露的差距：当您的收入来源增加，您的期间汇总跨越季度和年份时，公式维护会复复合。复制列、调整日期范围、向 IFS 添加新的来源模式——这都是不需要判断的机械工作。这正是 ModelMonkey 在一个提示中处理的确切类型的任务。

免费尝试 ModelMonkey 14 天——它在 Google Sheets 和 Excel 中都有效。

常见问题

收入追踪规则和预算分类之间有什么区别？

预算分类只是一个标签。收入追踪规则是一个主动条件——一个基于该标签分配、强制或计算某项内容的公式或验证设置。您可以有一个称为"咨询"的分类而不对其做任何操作，或者您可以有一条规则，按月汇总所有咨询条目，并标记那些在 45 天内未被标记为已收到的。

为什么为收入汇总使用 SUMIFS 而不是数据透视表？

数据透视表初期构建更快，但当您的源数据结构更改时会崩溃，并且除非您手动刷新，否则添加新行时不会动态更新。与命名范围绑定的 SUMIFS 公式会自动更新，可以嵌入到多工作表财务模型中——就像直接将您的收入合计拉入损益表选项卡中一样。

我应该有多少个收入分类？

足以做决策，但不要太多以至于每次分类都变成一个判断调用。对于大多数小企业和自由职业者，4-8 个分类是正确的范围。如果您发现自己在创建子分类（咨询 > 策略、咨询 > 实施），请在复杂化规则之前考虑您是否真的需要报告中的那个粒度。

我可以将这些规则应用于从银行导出导入的历史交易数据吗？

可以。将您的 CSV 导出粘贴到一个临时区域中，在来源/描述列上运行 IFS 自动分类公式，然后将分类结果粘贴到 Income_Log 中。主要清理步骤是规范化日期格式——银行导出通常使用文本日期，Google Sheets 无法识别为日期，直到您使用 DATEVALUE 或"格式 → 数字"菜单重新格式化它们。

如果有人输入不在我的列表中的分类会怎样？

将验证设置为"拒绝输入"时，Google Sheets 会完全阻止该条目并显示错误。用户必须从下拉列表中选择。如果您改为将其设置为"显示警告"，无效条目会通过并静默破坏您的 SUMIFS——这就是为什么"拒绝输入"是共享工作表的正确默认设置。