SuperAPI AI 工具集

spreadsheet-modeling

办公与效率

查看项目

更新于 10-26 16:17

技能介绍

用途

该技能用于在 Microsoft Excel 或 Google Sheets 中构建结构化、可审计、可维护的工作簿模型,覆盖从快速分析到企业级财务模型的场景。当用户需要编写或调试公式、创建数据透视表、设计仪表板、编写 VBA 宏或 Apps Script 自动化、构建财务预测与情景分析、设置数据验证,或在表内清洗和重构数据时启用。不适用于数据库 SQL 查询或 Python/R 数据分析。

主要能力

  • 公式编写与排错:XLOOKUP、INDEX-MATCH、SUMIFS、COUNTIFS、AVERAGEIFS、动态数组(FILTER、SORT、UNIQUE、SEQUENCE)等。
  • 数据透视表与透视图:字段布局设计、计算字段、切片器、刷新策略。
  • 仪表板设计:KPI 卡片、图表组合、条件格式(色阶、数据条、图标集)、数据验证下拉。
  • 自动化:Excel VBA 宏与自定义函数(UDF)、Google Apps Script 定时任务与邮件通知。
  • 情景与敏感性分析:Excel 数据表(Data Table)、Scenario Manager、CHOOSE 切换方案。
  • 模型审计与优化:分离输入/计算/输出、命名区域、消除魔法数字、排查循环引用与易失函数。

使用方式(核心原则)

  1. 分离输入、计算、输出:假设/输入放一个表,计算放另一个表,汇总/仪表板放第三个表,避免在公式单元格中混入硬编码输入。
  2. 每行/列保持同一公式模式:同列公式应可整列复制,出现不一致即模型脆弱。
  3. 命名一切:使用命名区域和结构化表引用,如 =Revenue * Tax_Rate 优于 =B7*$K$2。
  4. 无魔法数字:公式中字面量应为命名常量或明确标注的输入单元格。
  5. 为下一位使用者设计:统一格式、输入单元格用颜色标识(常见为蓝字黄底)、对非直观逻辑添加批注。

示例代码

查找公式:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not found", 0)

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

条件聚合:

=SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Date], ">="&DATE(2025,1,1))

VBA 宏(需保存为 .xlsm):

Sub FormatReport() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ws.Range("A1:Z1").Font.Bold = True ws.UsedRange.Columns.AutoFit ws.Range("D2:D" & lastRow).NumberFormat = "$#,##0.00" MsgBox "Report formatted: " & lastRow - 1 & " rows processed." End Sub

Apps Script 定时邮件(注意 6 分钟执行上限):

function sendWeeklyReport() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const dashboard = ss.getSheetByName("Dashboard"); const revenue = dashboard.getRange("B2").getValue(); const growth = dashboard.getRange("B3").getValue(); const subject = "Weekly Report - Revenue: $" + revenue.toLocaleString(); const body = ["Weekly KPIs:", "Revenue: $" + revenue.toLocaleString(), "Growth: " + (growth * 100).toFixed(1) + "%", "", "View full dashboard: " + ss.getUrl()].join("\n"); MailApp.sendEmail("team@company.com", subject, body); }

常见陷阱

  • 数据透视表会静默排除行/列字段为空的记录,透视前先清洗空值。
  • SUMIFS 各条件区域尺寸不一致时报 #VALUE! 且无明确提示。
  • 大型模型中数据表会在每次编辑时重算,可将计算模式设为手动(Ctrl+Alt+F9 强制重算)。
  • OFFSET、INDIRECT 为易失函数,在结构化表引用中使用易导致引用错误,优先用 INDEX。
  • Apps Script 超时 6 分钟后静默中断,大数据集需用 PropertiesService 存续跑令牌分批处理。

适用场景与限制

适用于电子表格内的建模、公式、透视表、仪表板、自动化与财务分析。数据库查询、SQL、Python/R 数据分析不在范围内。文中未提供该技能的收费、价格、平台要求或效果承诺等信息。

参考资料

技能可加载 references/formula-patterns.md(公式模式)、references/vba-patterns.md(VBA 与 Apps Script 模式)、references/financial-modeling.md(财务建模架构),仅在任务需要深入细节时加载。安装配套技能可用:npx skills add AbsolutelySkilled/AbsolutelySkilled --skill <name>。