CoreGPT
Google Workspace阅读约 17 分钟

Google Sheets AI：公式生成、数据清理与分析工作流

了解如何在 Google Sheets 中使用 AI 编写公式、清理杂乱数据并构建分析工作流，附可直接复制的提示词和 Sheets 原生写法。

Google Sheets AI：公式生成、数据清理与分析工作流

电子表格是工作变得真实的地方：数字变成决策，杂乱的导出文件变成报告，“简单的小问题”变成数小时的公式调试。Google Sheets AI 改变了这个工作流程：你不必再翻阅函数文档、靠反复试错，而是可以用大白话描述你想要什么，然后像分析师一样验证并优化输出结果。

本文重点介绍 AI 在 Google Sheets 中始终能节省时间的三个实用领域：

  • 公式（编写、修复、解释和优化）
  • 数据清理（规范杂乱的列、日期、姓名、ID 和重复项）
  • 分析工作流（同期群、数据透视表、汇总、图表和可供决策的洞察）

在此过程中，你会获得可直接复制的提示词和 Sheets 原生写法，让你的工作保持可复现、可审计。

如何看待 Google Sheets AI（才能真正帮上忙）

最好把 AI 视为电子表格逻辑的快速“初稿”生成器。行之有效的做法是：

  • 要求输出你可以验证的内容（公式、一组步骤、数据透视表配置、验证规则）
  • 提供上下文（示例行、列标题、期望输出的示例）
  • 在 Sheets 中验证（抽查结果、测试边界情况，并用 IFERROR 包裹）

如果你使用办公套件内的 AI 助手，体验会更顺畅，因为你可以在构建电子表格的同一个地方工作。

例如，CoreGPT Apps 把 GPT 驱动的工具带入 Google Workspace，让你在文档和电子表格中工作时可以使用 ChatGPT、Gemini 和 Claude 等模型，设计注重隐私，且无需注册。（评估任何 AI 工具时，请优先考虑隐私控制以及针对敏感数据的组织政策。）

一张简洁的 Google Sheets 风格电子表格，显示列中杂乱的客户数据（姓名、邮箱、日期、收入），侧边栏标有“AI 助手”，建议使用 TRIM/PROPER 清理公式和基于 QUERY 的汇总。

用于 Google Sheets 公式的 AI：从“我想要什么？”到可用的表达式

大多数公式问题并不在于是否认识某个函数，而在于把意图转化为精确的规则。AI 可以帮你快速弥合这个差距。

1）用自然语言生成公式，并设置防护栏

向 AI 索要公式时，请提供：

  • 工作表名称（可选）
  • 列字母和标题
  • 一行示例输入
  • 确切期望的输出

提示词模板（生成公式）

“请为单元格 E2 编写一个 Google Sheets 公式。列：A=订单 ID、B=客户、C=订单日期、D=金额。在 E 列中，如果金额 >= 500 则返回 High，如果在 200 到 499.99 之间则返回 Medium，否则返回 Low。要处理空白单元格。”

典型的结果如下：

=IF(D2="","",IF(D2>=500,"High",IF(D2>=200,"Medium","Low")))

需要验证的内容

  • 空白处理（"" 与 0）
  • 数值解析（金额是否以文本形式存储？）
  • 边界条件（恰好 200、恰好 500）

2）让 AI 解释或调试现有公式

Google Sheets 公式很容易变得难以阅读，尤其是嵌套时。AI 擅长把公式转换成大白话，并指出可能出错的地方。

提示词模板（调试）

“请解释这个 Google Sheets 公式的作用，然后在可能的情况下给出更简单的版本： =IFERROR(ARRAYFORMULA(IF(A2:A="","",VLOOKUP(A2:A,Map!A:B,2,FALSE))),"")”

常见的结果包括：

  • 一份易读的分步解释
  • 诸如用 XLOOKUP（如果你的账户中可用）或 INDEX/MATCH 替换 VLOOKUP 之类的建议
  • 关于性能的建议（尽可能限制整列引用）

3）用 AI 选择合适的函数类别

很多时间是因为用错了工具而浪费的：

  • 查找问题（根据需求使用 XLOOKUP、VLOOKUP、INDEX/MATCH 或 FILTER）
  • 多条件聚合（使用 SUMIFS、COUNTIFS、AVERAGEIFS）
  • 类 SQL 的报表（使用 QUERY）
  • 文本提取（使用 REGEXEXTRACT 和 REGEXREPLACE）

如果你把约束条件告诉 AI，它就能推荐合适的函数类别，并给出第一版公式。

4）AI 能快速生成的高杠杆公式模式

下面是常见的“值得交给 AI”的模式，因为手动编写很容易出错。

任务Google Sheets 写法（示例）向 AI 提出的要求
多条件求和=SUMIFS(D:D,B:B,"Acme",C:C,">="&DATE(2026,1,1))“汇总 Acme 自 2026 年 1 月 1 日以来的金额”
拆分全名=SPLIT(A2," ")（或更智能的正则）“处理中间名和多余的空格”
从邮箱中提取域名=REGEXEXTRACT(A2,"@(.+)$")“提取域名，处理空白”
规范电话号码数字=REGEXREPLACE(A2,"\D","" )“去掉非数字字符，如果有开头的 + 则保留”
按日期分组=TEXT(C2,"YYYY-MM")“创建月度同期群键”
列表去重=UNIQUE(A2:A)“唯一值，忽略空白，并排序”

关于函数参考和边界情况，Google 的官方文档仍然是权威来源，尤其是 QUERY 和正则表达式的行为。一个好的起点是 Google Sheets 函数列表。

用于数据清理的 AI：可重复的“从杂乱导出到可分析”工作流

数据清理是 Google Sheets AI 能节省最多时间的地方，因为清理涉及几十个细小的转换。

步骤 1：在“修复”之前先分析问题

让 AI 根据几行示例和列标题提出清理方案。

提示词模板（清理方案）

“这是 CSV 导入数据中的 8 行示例（粘贴）。请按列找出可能的数据质量问题，然后使用 Google Sheets 的功能和公式提出清理方案。”

AI 通常能标出的问题：

  • 前导/尾随空格、不可见字符
  • 混合的日期格式
  • 以文本形式存储的数字（货币符号、逗号）
  • 不一致的大小写（ACME、Acme、acme）
  • 由空白或标点导致的重复项
  • 合并的字段（城市、州放在同一列）

步骤 2：规范文本字段（姓名、公司、SKU）

常见的 Sheets 原生工具：

  • TRIM() 用于删除多余空格
  • CLEAN() 用于删除不可打印字符
  • PROPER()、UPPER()、LOWER() 用于调整大小写
  • SUBSTITUTE() 和正则函数用于规范标点

示例：规范公司名称字段，同时保留原始值：

=IF(A2="","",PROPER(TRIM(CLEAN(A2))))

如果你的数据中有“Inc.”、“LLC”之类的模式，或标点不一致，AI 可以提出有针对性的 REGEXREPLACE 规则。

步骤 3：统一日期和数字

AI 可以帮你编写“防御式”的转换，处理多种输入格式。

  • 如果日期是文本，你可能需要 DATEVALUE() 或针对特定格式的解析。
  • 如果货币带有符号，请在转换之前将其删除。

示例：把“$1,234.50”这样的类货币文本值转换成数字：

=IF(A2="","",VALUE(REGEXREPLACE(A2,"[^0-9.-]","")))

验证提示： 转换之后，在辅助列中对几行快速使用 ISNUMBER() 检查来确认。

步骤 4：可靠地拆分和提取字段

当拆分规则比较微妙时，AI 尤其有用。

  • SPLIT() 适用于分隔符一致的情况。
  • REGEXEXTRACT() 更适合“提取匹配的部分”。

示例：从“12345-6789”中提取邮政编码的前 5 位数字：

=IF(A2="","",REGEXEXTRACT(A2,"^(\d{5})"))

步骤 5：正确去重（而不只是“删除重复项”）

Google Sheets 内置了用于删除重复项的数据清理选项，但分析工作流往往需要更多的控制。

AI 可以建议诸如以下的方法：

  • 先规范化（去除空格、统一大小写），再对规范化后的列使用 UNIQUE()
  • 使用 SORT() 加 UNIQUE()（或 QUERY）为每个 ID 保留最新的记录

示例：唯一的非空邮箱：

=SORT(UNIQUE(FILTER(A2:A,A2:A<>"")))

步骤 6：用辅助列和命名输出让它可复现

一种常见的反模式是通过直接编辑单元格来“修复”数据。正确的做法是：

  • 保留一个原始数据工作表
  • 用公式创建一个清洗后工作表
  • 创建一个基于清洗后输出的分析工作表

AI 可以针对你的具体数据集起草这个结构，并说明哪些列应保留为原始数据、哪些是派生列。

用于分析的 AI：构建经得起推敲的洞察

数据清理完成后，分析就是把一行行数据变成决策。当 AI 生成你可以验证的成果时，它才有帮助：数据透视表配置、QUERY 语句和汇总指标。

工作流 1：同期群或基于时间的汇总（按月、按周、按季度）

一种可靠的方法：

  • 创建时间键：=TEXT(OrderDate,"YYYY-MM")
  • 使用 QUERY 或数据透视表进行汇总

示例（创建月份键）：

=IF(C2="","",TEXT(C2,"YYYY-MM"))

然后向 AI 提问：

提示词模板（月度汇总）

“我在 E 列有 MonthKey，在 D 列有 Amount。请给我一个 QUERY 公式，返回 MonthKey、总收入、订单数和平均订单价值，并按 MonthKey 升序排序。”

由于 QUERY 语法很容易稍有笔误，AI 能节省时间，但你仍应对照筛选出的某个月份上手动 SUM 的结果，抽查合计数。可参考 Google 的 QUERY 函数文档。

工作流 2：多维数据透视表，无需猜测配置

如果你不确定数据透视表应该把字段放在行、列还是筛选器中，请让 AI 提出数据透视表的设计方案。

提示词模板（数据透视表方案）

“我想了解按地区和产品类别划分的收入，带有月度筛选器，如果可能的话再加上同比对比。请提出 Google Sheets 中数据透视表的布局以及所需的计算字段。”

即使你是手动构建数据透视表，这份方案也能避免走进死胡同。

工作流 3：异常值检测与数据合理性检查

AI 可用于构建轻量级的质量检查：

  • 重复的 ID
  • 负数金额
  • 未来的日期
  • 缺失的必填字段

示例：标记负数或空白的金额：

=IF(D2="","Missing",IF(D2<0,"Negative","OK"))

让 AI 针对你的列提出一份“数据质量检查清单”，然后把最重要的 5 项检查用辅助列实现。

工作流 4：把指标与叙述结合起来的执行摘要

Sheets 可以计算数字，但把它们变成可读的叙述需要时间。

一个好的模式是：

  • 在单元格中计算指标（总收入、环比变化、热销产品）
  • 让 AI 使用这些单元格的值撰写简短的总结

提示词模板（分析叙述）

“请使用这些指标（粘贴数值），为每周业务回顾撰写一份 6 句话的总结。包含 2 个值得注意的驱动因素、1 项风险和 1 项建议的下一步行动。保持中立、具体。”

这样可以让 AI 基于计算结果，而不是靠猜测。

Google Sheets AI 实用提示词库（可复制并改编）

把这些作为起点，然后加入你的列标题和示例行。

创建公式

  • “编写一个公式，返回第 2 行中 A 到 Z 列里最后一个非空值。”
  • “创建一个 ARRAYFORMULA 版本，使其从第 2 行向下应用，输入行为空时保留空白。”
  • “修复这个公式，使其适用于欧洲的小数逗号格式并处理空白。”

数据清理

  • “提出用于规范这些产品 SKU 的正则规则（粘贴 10 个示例）。输出 REGEXREPLACE 公式。”
  • “为这些姓名格式（粘贴示例）创建一种一致的 FirstName 和 LastName 提取方法。”
  • “给定这些地址字符串（粘贴），提取城市和州，并指出哪些行无法被可靠地解析。”

分析

  • “我需要一个可用于仪表板的表格。请建议最精简的 KPI 集合以及计算它们的确切公式。”
  • “构建细分逻辑：根据邮箱区分新客户与回头客，并按细分群体计算收入。”
  • “为这些指标建议一种图表类型，以及如何构建数据源表。”

安全、隐私与质量检查（尤其是在工作中）

AI 可以加速电子表格工作，但电子表格中往往包含客户数据、财务数据和内部规划。

几条操作规则有助于保障安全：

  • 尽可能减少提示词中的敏感数据（使用样本、遮蔽后的值或汇总数据）。
  • 先验证再信任：清理转换之后，至少随机抽查 10 行。
  • 使用“原始/清洗/分析”分离的方式，让更改可复现、可审阅。
  • 在“备注”工作表中记录转换（改了什么、为什么改、何时改）。

如果你要为 Google Workspace 采用 AI 助手，请评估它如何处理隐私、管理员控制，以及是否需要账户注册。

整合起来：快速、可重复的 Sheets AI 工作流

下面是适用于大多数团队的最简端到端工作流：

  • 从“原始数据”工作表开始（粘贴/导入数据，不编辑单元格）。
  • 创建“清洗后”工作表，其中每个杂乱的列都有一个派生出来的干净列。
  • 运行质量检查（重复项、空白、无效日期、数值解析）。
  • 使用数据透视表或 QUERY 构建“分析”工作表，然后添加图表。
  • 根据计算出的指标生成叙述性总结。

使用应用内助手时，由于不必不停切换上下文，你的迭代速度会快得多。如果你想直接在 Google Workspace 应用中使用 ChatGPT、Gemini 或 Claude，可以了解 CoreGPT Apps，在保持注重隐私的前提下，把 AI 带入你日常的电子表格工作流。

继续阅读