• 未找到友好提示：第四个参数直接返回"未找到"等自定义文案，告别 #N/A；

• 多列返回：一个公式返回多列结果。

▍ 动态数组函数：一个公式溢出一片结果

Excel 2021 起新增 6 个动态数组函数，一个公式自动"溢出"到相邻单元格，彻底告别 Ctrl+Shift+Enter 老式数组公式 ：

💡 组合拳示例：=SORT(FILTER(A2:D100,(C2:C100="北京")*(B2:B100>5000),"无结果"))——先过滤出北京区域、销售额大于 5000 的记录，再排序，一个公式完成过去需要 3 个辅助列 + 多次操作才能做的事。

🪄 四、Power Query：数据清洗的工业革命

Power Query 是微软的数据转换和数据准备引擎，业务用户高达 80% 的时间花在"数据准备"上，这正是 Power Query 要解决的核心痛点 。

它在 Excel 中的入口叫"获取和转换数据"（Get & Transform），Microsoft 365 在 Windows、Mac、Web 三端均可使用（Web 端查看和刷新查询对所有 Microsoft 365 订阅者开放，完整编辑功能需商业或企业计划） 。

▍ 四大工作阶段

① 连接（Connect）：从 Web、文件、数据库、Azure、当前工作簿等数据源连接数据；

② 转换（Transform）：在 Power Query 编辑器里删列、改数据类型、筛选行、拆分列；

③ 合并（Combine）：整合多数据源，形成独特视图；

④ 加载（Load）：加载到工作表或数据模型，以后数据更新只需一键刷新 。

▍ 零基础操作路径

① 顶部「数据」选项卡 →「获取数据」（或「获取和转换数据」组）；

② 选择数据源（如「从文件」→「从工作簿」）；

③ 在Power Query 编辑器里逐步操作，每一步操作都会被记录；

④ 完成后「关闭并加载」到 Excel 工作表；

⑤ 下次数据源更新，右键查询 →「刷新」即可，所有清洗步骤自动重跑 。

▍ 适合什么场景

• 每月重复的报表：定义一次查询，以后每月刷新；

• 多文件合并：把同一文件夹下几十个 CSV 自动合并；

• 脏数据清洗：去重、拆列、类型转换、空值处理；

• 跨源整合：把 SQL Server、Web API、Excel 文件的数据整合到一起。

🧮 五、LET + LAMBDA：把 Excel 变成编程语言

这两个函数是 Microsoft 365 的"独门武器"，让 Excel 公式具备了编程语言的能力 。

▍ LET：给公式里的计算起名字

=LET(名称1, 值1, 名称2, 值2, ..., 最终结果)

示例——计算毛利率和提成：

=LET(收入, SUMIFS(销售[金额],销售[年份],2025), 成本, SUMIFS(成本[金额],成本[年份],2025), 毛利, 收入-成本, 提成, IF(毛利/收入>0.3, 毛利*0.1, 0), "提成: "&TEXT(提成,"0.00%"))

💡 好处：同一个中间结果只算一次（提升性能），公式可读性大幅提升，调试更容易 。

▍ LAMBDA：创建自己的函数（无需 VBA）

=LAMBDA(参数1, 参数2, ..., 计算逻辑)

示例——创建一个"含税价"函数：

=LAMBDA(税率, 金额, 金额*(1+税率))

配合「公式」→「名称管理器」→「新建」，把这个 LAMBDA 命名为「WithTax」，以后就能像内置函数一样调用：=WithTax(0.2, 1000) 返回 1200 。

🎯 革命性意义：不用写 VBA，就能创建可复用、可审计的自定义函数，配合 MAP、REDUCE、BYROW 等辅助函数，Excel 公式语言具备了图灵完备性 。

🎨 六、条件格式 + 数据验证：让表格会"说话"

▍ 条件格式：视觉化凸显关键信息

Excel 2013 起支持色阶、数据条、图标集等高级选项 ，让大片数据瞬间可读：

• 色阶：数值大小用颜色深浅表示，热力图一目了然；

• 数据条：单元格内嵌迷你条形图，横向对比；

• 图标集：用箭头、信号灯等图标标示趋势；

• 自定义规则：用公式定义高亮逻辑，异常值、重复值、到期日自动标红。

▍ 数据验证：守住数据质量的第一道防线

「数据」→「数据验证」（老版本叫"数据有效性"）：

• 下拉列表：限定只能选预设值，杜绝录入错误；

• 数值范围：限定 0-100、日期区间等；

• 自定义公式：用公式定义复杂校验逻辑；

• 错误警告：录入不合规数据时弹出提示。

📌 关键价值：财务、研究、企业报表中，数据验证防止错误录入蔓延，是数据完整性的守护神 。

📈 七、图表与仪表盘：让数据会"讲故事"

Excel 提供柱状图、折线图、组合图、三维地图等丰富图表类型，Microsoft 365 还包含推荐图表和动态图表功能 ；Sparklines（迷你图）则在单元格内显示紧凑趋势 。

▍ 专业仪表盘的 5 个要素

① 数据透视表 + 切片器：交互式筛选；

② 透视图：可视化呈现，跟随透视表同步；

③ 条件格式：KPI 红绿灯；

④ 表单控件：下拉框、单选按钮联动图表；

⑤ 动态图表：用 OFFSET + 定义名称实现数据源自动扩展。

💡 Forecast Sheet：Excel 2016 起内置的预测工作表，基于历史数据自动生成带置信区间的预测，销售预测、预算编制、项目规划一气呵成 。

🤖 八、宏与 VBA：桌面端自动化的终极武器

VBA（Visual Basic for Applications）是 Excel 的内置编程语言，从 Excel 2010 起就支持通过代码创建数据透视表和图表 。

▍ 零基础入门路径

① 录制宏：开发者工具 → 录制宏 → 执行操作 → 停止录制，Excel 自动生成 VBA 代码；

② 查看代码：Alt + F11 打开 VBA 编辑器，看录制的代码；

③ 改写代码：修改参数、加循环、加判断；

④ 实战场景：批量生成数据透视表、自动发邮件、定时报表、批量格式调整 。

▍ 现代自动化替代方案

• Office Scripts：Microsoft 365 Web 端的 TypeScript 脚本，跨平台；

• Power Automate：跨应用自动化编排；

• VBA：桌面端仍是王者，处理本地文件、复杂 GUI 操作无可替代 。

✨ 九、Copilot：2026 年 Excel 的 AI 革命

Microsoft 365 中的 Excel Copilot 用先进 AI 分析数据、生成公式、创建摘要、自动生成图表，能理解自然语言查询、建议洞察、自动化重复任务 。

零基础用户的使用姿势：

① 侧边栏输入"分析这份销售数据的季度趋势" → Copilot 自动生成图表；

② 输入"写一个公式，找出重复的客户名" → Copilot 生成 FILTER + COUNTIFS 组合公式；

③ 输入"总结这张表的关键洞察" → Copilot 输出文字摘要 。

💡 AI 不是替代学习：Copilot 生成的结果仍需要你理解底层逻辑才能正确使用和排错，所以前面 8 项基础是绕不开的。

🎯 十、30 天学习计划（零基础可执行）

第 1-7 天：数据透视表（插入、字段拖拽、分组、切片器、透视图）

第 8-14 天：XLOOKUP + FILTER + UNIQUE + SORT（动态数组全家桶）

第 15-21 天：Power Query（连接、转换、合并、刷新全流程）

第 22-26 天：LET + LAMBDA（自定义函数入门）

第 27-30 天：条件格式 + 数据验证 + 图表仪表盘综合实战

📌 VBA 和 Copilot 作为进阶选修，在前 30 天基础打牢后再碰。

⚠️ 版本兼容性提醒：• XLOOKUP：Microsoft 365 / Excel 2021 起支持 ；• 动态数组函数：Microsoft 365 / Excel 2021 起支持 ；• LET：Microsoft 365 / Excel 2021 起支持 ；• LAMBDA：仅 Microsoft 365 支持 ；• Power Query：Microsoft 365 三端（Win/Mac/Web）可用，Excel 2016/2019 for Mac 不支持 ；• 老版本用户打开含新函数的文件，动态数组结果可能中断，建议粘贴为值或替换为兼容函数 。

📌 本文功能边界核实自 Microsoft 支持文档（Excel 2021 新增功能、Power Query 官方说明、Microsoft Learn VBA 文档，截至2026年8月）具体功能可用性以你当前 Excel 版本为准，建议优先使用 Microsoft 365 订阅版