本文近10000字，约25分钟精读重读，有一定可能颠覆掉你对 Excel 的传统认知。本文不做任何广告，完全属于来自于长期思考的输出，希望大家留出时间阅读，因为我相信能看懂本文者，都能通过本文带来的启发大幅度优化工作甚至直接实现商业变现，如果你做不到这些，还可以来听听我们的直播分享。——BI佐罗Excel 最普遍的是用来做计算。在这方面从 Excel 诞生后 30 年这么久，都没有什么大的变化吗？其实，一直以来，有很多小伙伴发来各种困惑，问：看过大量 Excel 教程，书籍，但为什么 Excel 中的知识你永远学不完，其表现就是：大家发现在 Excel 中有学不完的技巧，真的那么多吗？大家发现复杂的 Excel 技巧，普通人是根本学不会的，是这样吗？大家发现即使冒死学习了大量 Excel 技巧，工作问题还不能被解决，是这样吗？大家发现在简历上写精通 Excel 的工资水平，还是默认和大学毕业生一样，是这样吗？...这到底是怎么回事？来帮助大家理清这些问题的。相信在了解这些后，你会用更高地性价比来使用 Excel，并在自己的职业发展中对工具的理解更深入，自己也更有效率。看到这些，我郁闷过你一定刷到过很多这样的文章来讲解 Excel 的技巧，而且保证凡是普通人看了都会得到一个结论：那就是 Excel 太神奇了，我完全看不懂。来感受这种体验吧：例如：《Excel 超级公式帮你数据去重》，如下：你需要做的是能看懂并徒手写出这个公式，你能做到吗？你觉得应该做到吗？再例如：《Excel 超级公式帮你找到只出现一次的值》，如下：你需要做的是能看懂并徒手写出这个公式，你能做到吗？你觉得应该做到吗？再例如：《Excel 超级公式帮你保持大小写数据去重》，如下：你需要做的是能看懂并徒手写出这个公式，你能做到吗？你觉得应该做到吗？这三个案例能让大概 90% 的 Excel 用户感叹：这是什么公式，好牛啊。在人们迟疑自己到底能不能，该不该写出这么复杂的公式时。又要来一波了。紧接着，你又会刷到这样的文章。例如：《Excel 计算列中的唯一值》，如下：这个公式看着不是那么复杂，但正在思考时，又来一个猛的：例如：《Excel 计算列中的唯一文本值》，如下：什么？认为这个还不够猛吗？例如：《Excel 计算每列都按字母顺序显示》，如下：看到这里，其实大部分就放弃了...理性总结：Excel 表格计算问题来了：对于复杂的 Excel 公式，到底需要去学习和领会吗？Excel 中使用公式进行计算，具有一个重要特点，那就是：计算是连锁的。如果 A 单元格的公式引用了 B，B 单元格的公式引用了 C，...任何一个单元格的数据改变，都会自动触发整套计算链条重算。这就是 Excel 表格计算的重要特点。也正是因为这个特点，我们的大量工作将依赖于表格的公式计算，而不是一次性的功能点击。例如，在上述的案例中，排序就是一个可以通过点击按钮实现的，但是，它不会自动触发，因此，我们需要利用公式的动态性。这又是 Excel 表格计算的一个重要特点，那就是：计算是动态的。综上，Excel 表格计算的重要特点就是：动态连锁计算。因此，为了使用这一特性来自动化复用办公业务逻辑，那么，得到一个结论：【重点】办公用户必须学习 Excel 表格计算。那么，问题又来了，这么复杂的公式，学不会怎么办呢？没有错，这个说法很实在。在 10 多年前，罗叔就发现这个问题，但可能是因为 Excel 太灵活了，从能力上，它可以完成各种复杂的挑战，只是复杂一些，而对 Excel 的改良将影响全球的 Excel 用户，是一个大事。但是，但是，但是，这么明显的问题，难道微软不知道吗？而在微软发生的故事，却很少有人告诉大家，告诉大家的永远都是：赶快学习 VLOOKUP，它很牛。Excel 从表格计算到表格编程其实，早在 2003 年，微软 Excel 研究人员就发表了《用户为中心的 Excel 函数》（A User-Centered Approach to Functions in Excel），描述了 Excel 的潜在扩展，将用户定义的函数集成到电子表格中：强调了如何将编程语言的认知应用于普遍不被视为编程语言的产品中。他们研究的技术被用于早期研究 Excel 中用户定义函数的好处，这一研究方向为 LAMBDA（一个改变 Excel 计算世界的机制，本文后面详解） 和其他产品增强奠定了基础。Microsoft Excel 是世界上最重要的软件工具之一，全世界的用户都依赖它来创建、理解、建模、预测和协作。随着 Excel 团队致力于利用计算机科学的新领域 —— 编程语言、NLP、人工智能、机器学习的进步 —— 他们与微软研究院（Microsoft Research）一起完成令人难以置信的工作，共同创建 Excel 在未来几的愿景。Excel 团队与位于全球的微软研究院实验室建立了多项长期合作关系，在数据类型、编程语言、分析、网络架构和错误检测等领域开展工作，其中许多采用人工智能、自然语言处理和机器学习。这些研究是战略上的定位，使 Excel 团队能够提供其他方式根本不可能实现的创新，对 Excel 的愿景和最终路线图产生重大影响。简而言之，Microsoft 研究人员现在是 Excel 团队的核心部分，帮助创造该产品的未来。 微软 Office 产品 VP David Gainer微软 Office 产品 VP 指出：Excel 团队和微软研究院深度合作，因此，研究的成果会加入到 Excel 中，来让 Excel 变得更强，但是这种功能的增加，不是简单的增加，而是有深度思考的。随着本文的展开，你会继续看到这一点。那 Excel 从表格计算到了表格编程，是让大家都变成 Excel 程序员吗？不是变成。其实，已经是了。只不过每个人有权力选择仅仅用办公部分；也可以仅仅选择用复杂的函数技巧去实现某些需求；还可以选择用正统的现代 Excel 表格编程方式来增强自己的工作。2018-09 Excel 推出动态数组函数Excel 产品组非常知道用户的痛点，而且在不断研究优化 Excel 计算引擎的理论基础和改良。传统要在 Excel 里计算，要在电子表格的每个单元格编写一个公式。一个公式，一个值。如果您想要另一个值，您可以编写（或复制）另一个公式。即使你使用传统的数组公式（需要按 Ctrl + Shift + Enter 完成编写）也需要在不同的单元格写一次。因此，CSE 数组公式还是传统意义上的公式。使用动态数组，一切都将改变。现在，没错，此时此刻，用户可以编写一个公式，按下回车键并获得返回值的数组。一个公式，多个值。这将使任何人能够更快地构建功能更强大的电子表格，使用更少的公式并减少出错的机会。为了利用动态数组的强大功能，微软已经添加了一些惊人的新函数。因此，例如，您可以使用 SORT 函数对列表进行排序，使用 UNIQUE 函数从该列表中删除重复项，然后使用 FILTER 函数从列表中获取您想要的内容。当您的数据发生变化时，动态数组将自动调整大小并重新计算！看动态数组是如何实现数据去重以及过滤的，如下：动态数组的支持已经深入集成到 Excel 中，它不仅限于新增的函数，任何返回数组的公式都会表现出动态数组的特点。如下所示，在 B3 中输入 =A3:A13 将导致 A3:A13 中的值溢出到 B3:B13 中。就像 Excel 的网格一样，动态数组可以是二维的，如下面的乘法表示例所示。对于新增的动态数组函数，你用过了吗？值得一提的是，上述几个非常复杂的公式案例，用了动态数组函数以后，都会非常简单。从此，你再也不必研究 CSE 的传统数组公式了，记住：再也不需要了。这里会有很多人来喷的，因为，很多过去的教程就是靠 CSE 传统数组公式来收割大家知识付费的，但目前其本质就是：为了已经过去的知识付费了。 CSE 传统数组公式可以用吗？可以的。就像有了 iPhone，也不影响 Nokia 打电话，只是你不知道 iPhone 存在而已，然而今天你知道了。那 Nokia 的商家肯定不开心的。 这个也千万不要来怪我们啊，这些是微软自己出的自己的东西，连本文所引述的所有观点都出自微软自己的人的博客，这里只是把它们说得更加通俗一点罢了。动态数组函数的推出，其本质是 Excel 计算引擎的重写和优化，很多重要的函数都自动适配了动态数组函数。包括：UNIQUE - 从一系列单元格中提取独特的项目。FILTER - 根据您定义的标准过滤数据。SORT - 按指定列对一系列单元格进行排序。SORTBY - 按另一个范围或数组对一系列单元格进行排序。RANDARRAY - 生成随机数数组。SEQUENCE - 生成序列号列表。TEXTSPLIT - 跨列或 / 和行按指定的分隔符拆分字符串。TOCOL - 将数组或范围转换为单个列。TOROW - 将范围或数组转换为单行。WRAPCOLS - 根据每行指定的值数将行或列转换为二维数组。WRAPROWS - 根据每列指定的值数将行或列重新整形为二维数组。TAKE - 从数组的开头或结尾提取指定数量的连续行或列。DROP - 从数组中删除一定数量的行或列。EXPAND - 将数组增长到指定的行数和列数。CHOOSECOLS - 从数组中返回指定的列。CHOOSEROWS - 从数组中提取指定的行。此外，还有两个流行的 Excel 函数的现代替代品，它们未正式包含在该组中，但利用了动态数组的所有优势：XLOOKUP - 是 VLOOKUP、HLOOKUP 和 LOOKUP 的更强大的继承者，可以在列和行中查找并返回多个值。XMATCH - 是 MATCH 函数的更通用的后继函数，可以执行垂直和水平查找并返回指定项的相对位置。从一定意义上，现在所有的 Excel 教程都需要重写，用动态数组函数的模式给出过往问题的更简单方法。如果你已经不是那种连 VLOOKUP 都不会的小白，那么，学习 Excel 的课程只有一个方向：动态数组函数 + Lambda 表达式。后文会继续展开。2020-12 Excel 推出 LAMBDA 表达式作为世界上使用最广泛的编程语言，Excel 中缺少一项最基本的原则，即使用公式语言定义用户自己的可重用函数的能力。即：可重复使用的自定义函数。这个要感谢与 Excel 产品组长期战略合作的微软研究院的计算智能团队。以下两个例子中的函数，都不是 Excel 中的非原生函数，如下：不难发现，这些函数帮助用户直接获得结果，用户并不需要知道这些函数内部完成了什么。这就是：自定义函数，它们在内部封装了实现的细节。问题来了：你知道什么是 Lambda 表达式吗？在 Excel 中，它长这个样子，如下：用 Lambda 表达式可以定义出非常实用的函数，例如：（在下图的引用公式中就是定义的 Lambda 表达式。）这是硬生生的定义了一个逆透视的函数啊，来看看吧。（建议好好看看这个图的每一步细节。）这里使用了一个预先定义的称为 Z.UNPIVOT 的函数，而关键是根本没用任何 Excel 公式以外的东西，没用过 VBA，没用过 Python，仅仅就是用 Excel 函数来定义 Excel 函数，然后就实现了逆透视。根据前文已经进行的论述，不难追加总结如下：必须使用 Excel 的函数，因为它们是动态连锁计算的。必须使用动态数组函数，因为它们是新一代引擎，大大简化原有计算逻辑。必须使用 Lambda 表达式，因为它，它，它让 Excel 计算从此实现图灵完备。什么？图灵完备？什么是图灵完备？图灵完备，是成熟的编程语言的试金石，标志着模仿图灵机的能力。根据这个定义，意味着，“如果一种编程语言可以实现任何可能的算法，那么它就是图灵完备的。”2021-1-25 日，微软研究人员在其博客上说 “原则上，你现在可以用 Excel 公式语言编写任何计算。”，Excel 计算已经实现图灵完备。因此，因此，Lambda 表达式，不是多一个公式的概念和意义，它彻底从底层逻辑上改变了 Excel 公式计算的能力。在这里例子中，就是用 Lambda 表达式定义了一个新的函数：UNPIVOT，来轻松实现公式级别的动态逆透视。有的伙伴会说：这会不会把大家都变成程序员呢？答案是：完全不会。微软的产品经理当然知道这些，虽然研究人员起初怀疑 LAMBDA 是否会引起编程语言爱好者的兴趣，但事实证明它对日常用户来说太难了，但他们表示早期的社区反应令人鼓舞。用户已经采用 LAMBDA 并将其应用于各种案例，其中一些甚至是团队从未设想过的。也就是说，Excel 提供了一种新的可能性而已。小白，学点按钮和 SUM，最多再加 VLOOKUP。办公，学 SUMIF 等。专业办公，只需要用现成的函数。等等？？？没提到 LAMBDA 啊？没错。LAMBDA 留给某些特别的角色来给大家定义直接可用的函数。如果你现在正在学习 Excel，在 2021 年以前的教程都需要重写，更准确地讲，不讲动态数组函数和 LAMBDA 表达式，反而要学习 CSE 数组公式，那相当于不学计算机，在用算盘。2021-01 微软宣称 Excel 终极表函数：Lambda自 1980 年代发布以来，Microsoft Excel 改变了人们组织、分析和可视化数据的方式，为每天使用它的数百万人提供了决策依据。它也是世界上使用最广泛的编程语言。编写 Excel 公式的用户比世界上所有 C、C++、C#、Java 和 Python 程序员的总和还要多一个数量级。尽管 Excel 取得了成功，但作为一种编程语言， Excel 具有根本性的弱点。多年来，有两个特别突出的缺点：(1) Excel 公式语言实际上只支持标量值 —— 数字、字符串和布尔值。(2) 它不允许用户定义新函数。Microsoft Research Cambridge 的 Calc Intelligence 项目与 Excel 团队建立了长期合作伙伴关系，旨在将电子表格公式转换为成熟的编程语言。这种伙伴关系的成果开始出现在产品本身中。在 2019 年 ACM SIGPLAN 编程语言原理研讨会 (POPL 2019) 上，我们宣布了两项重大进展：数据类型使 Excel 超越了文本和数字，并允许单元格包含一流的记录，包括链接到外部数据的实体，以及动态数组允许普通公式计算溢出到相邻单元格的整个数组。2020 年 12 月，微软发布了 LAMBDA，它允许用户定义用 Excel 自己的公式语言编写的新函数，直接解决了第二个挑战。这些新定义的函数可以调用其他 LAMBDA 定义的函数，任意深度，甚至可以递归调用。有了 LAMBDA，Excel 就变成了图灵完备的。原则上，用户现在可以用 Excel 公式语言编写任何计算。背后的大神我们总是会不忘来了解是谁为大家提供了这么底层的帮助，他们潜心研究多年，在这么庞大的 Excel 产品中做了底层的机制上的改进，这种改进看似是小的，但它从根本上影响了 Excel，将 Excel 从电子表格办公时代，带入了表格智能编程时代。请让我们来认识下背后的大佬，是他们的工作，让我们今天可以演示出这种案例。今天，全球各地的人们 —— 从教师到小企业主再到财务主管 —— 都使用 Microsoft Excel 来理解占据他们各自世界的信息，无论他们是否意识到，这样做，他们正在承担程序员的角色。领导 Microsoft Research Calc Intelligence 团队的高级首席研究经理 Andy Gordon（下图右） 和高级首席研究员 Simon Peyton Jones（下图左） 提供了 Excel 作为一种编程语言的长期工作，包括数据类型的扩展解锁了更大的数据使用范围和发布了 LAMBDA 函数，这使得 Excel 公式语言图灵完备。本文所有素材来源之一就是：整理和深度学习了以上两位大神历史所有讲座，文章，演讲，他们的战略前瞻性和持续的工作，才能让现在的 Excel 有如此不同的可能。未来就是现在，现在就是未来Excel 世界已经发生了根本的变化，Excel 界也将出现全新的玩法，你能想象刚刚案例的 UNPIVOT 是在这样的体验下编写而成的吗？我们知道在 Excel 中，传统方法根本编写不了大段的公式，且公式内容也不能复用，在新的模式下，我们可以实现很多。小心上瘾，变成新一代小学奥林匹克的信徒了。如下所示：不难看出，这个文档的编写已经超过了 1000 行，妥妥的 Excel 程序员。代码提示，变量，数据结构，算法逻辑，用函数定义函数，模块与库，一切编程世界的内容在 Excel 世界都存在了。但更准确地讲：【Excel 智能表格编程】时代来了。【Excel 表格编程】这一概念由领导 Microsoft Research Calc Intelligence 团队的高级首席研究经理 Andy Gordon 和其团队提出。如果考虑到 ChatGPT 等 AI 能力，那么，【Excel 智能表格编程】已经现在此时此刻。你正看到的博客文章，全部建立在这套方法论的基础上。为什么今天才写本文很显然，我们今天描述的事情，是个过去式。由于 Excel 的版本更新和普适性问题，我们常常描述完一个新闻，大家都会发现，自己根本没还法用上。例如：ChatGPT，虽然有大量文章和视频来描述它？然而，你用上它了吗？并没有。Excel 在 Microsoft 365 的演化下，也存在版本更新的问题，当这些特性刚刚推出的时候，往往大多数人的 Excel 版本还不具备使用的条件，而我们今天的介绍，是基于一个前提，那就是：你看完文章，打开你 Microsoft 365 的 Excel，你就可以使用本文所描述的一切特性，一切。（请确保你正在使用的是 Microsoft 365 的 Excel，如果你不知道什么是 Microsoft 365 的 Excel，可以自行搜索下，不要问：“为啥我的没有？”，因为你的不是 Microsoft 365 的 Office 的 Excel。）所以，今天才有本文。虽然，这个问题在 2 年前就已经在那里。可是，为什么 2 年过去了，还是在告诉你 VLOOKUP 和 CSE 公式。我们需要一篇文章来帮助大家理清整个体系，帮助每个人的职业发展做出重要理性参考。还需要学习传统 Excel 技巧吗问题来了：我们天天刷到各种 Excel 文章，1 元从小白到精通，学习 Excel 多个神奇技巧公式，那还需要吗？回答是：看情况。举一个例子吧：大家都上过小学，在小学都有一门课，叫做：小数数学奥林匹克。请大家自己想想，如果一直学习小数数学奥林匹克，你是不是连小学都毕业不了呢？然而，又有多少人在后续的人生中用过小学奥林匹克呢？真相就是：如果你愿意一直学习小学奥林匹克，那你就可以一直学习，学会了还可以再教别人，并向别人演示小学奥林匹克中的问题，大家都不会，有多少种技巧和方法来解题，让大家一起学习小学数学奥林匹克。所以，还需要学习传统 Excel 技巧吗？的回答是：看情况。在领悟了一些基本技巧后，工作的业务价值本身决定了每个人是否应该沉浸在 Excel 计算技巧的研究，还是上升到更高思维模式来利用合适的工具和方法来处理工作中的任务。和我们有什么关系Excel 的计算引擎，革命在于：计算引擎，全系优化为适配于动态数组函数模式。计算体系，通过 LAMBDA 表达式彻底实现图灵完备，支持任何计算。这两点是最底层的改变。和我们的关系在于，根据大家的角色不同，你可以从不同的角度受益。对于普通用户，除了普通的 Excel 公式计算，通过理解动态数组函数，可以轻松解决很多复杂问题。对于专业用户，可以通过自己写一套自己的 Lambda 表达式，来在类似的工作中重复使用自己的积累。对于超级用户，还可以编写一套新的库，来帮助普通用户更好和更轻松地使用 Excel。Excel BI Pro V2 来了在这里，我们为大家带来 Power BI 的顶级干货，熟悉的伙伴知道我们的博客叫：http://excel120.com，那 Excel 的东西在哪里呢？大概在 2015 年，那时候做了很多实验，在 Excel 中是无法完成的，我们的工作也做到了 Excel 的能力边界，如下：以上演示展示了 2015 年，在 Excel 中极致地使用各种技巧的和体验的感觉。由于各种问题的限制，我们在 Power BI 中探索整合了一套完美的方案《BI 真经》（如果你还没有学习，那可以看看），但我们时刻没有忘记 Excel 和 Power BI 的这两个世界。今天，我们不仅通过 Excel BI Pro V1 版本接通了这两个世界，更是在即将推出的 Excel BI Pro V2 版本基于对动态 Excel 新的洞见，将 Excel 动态数组函数和 Lambda 表达式的可能性和潜力完全释放。我们知道你不一定会写复杂的公式，我们理解。那为什么不直接使用一套更加强大的函数体系呢，如下：想象一下，我们所有日常的工作都可以通过函数直接完成，会不会变得很轻松。Excel BI Pro V2，对于接通 Excel 和 Power BI 世界，并更轻松地使用 Excel，是一个神器。它可以帮助所有人迅速建立与 Power BI 数据模型的连接，打通两个体系；还直接内置并将继续内置更精华的功能，如：日期表，自动数据字典等。更为重要的是，它将允许用户使用超越 Excel 原生的超级公式，这些公式全部通过 LAMBDA 表达式定义，普通用户也无需理解其中细节，只需享受强大的功能即可。这些能力全部源自原生 Excel，可以在企业内随意安全流转。总结Excel 引擎的革命已经进行，且还在进行中。我们感谢 Excel Team 以及微软研究院与之长期的合作所孕育出的成果。Excel 绝不是守着自己已有的天下，停滞不前。Excel 是一个极大的生态，它除了满足日常办公的用户，它的总用户规模从编程角度，超越了这个星球上所有编程语言用户的总和。在 Excel 计算引擎底层实现动态数组函数来彻底释放更强大的数据结构，以及推出 LAMBDA 表达式来完成图灵完备，这足可以满足 Excel 智能表格编程程序员的极大编程欲望。我们在这里做的工作，仅仅是将这些事实系统化的整理分析并告知大家，原来这个世界是这样的。每个人都可以根据自己的兴趣，规划，工作，来更好地使用 Excel。（以及 Power BI）当然，我们工作的一部分还包括基于我们有限的能力，来利用现有新技术，再次简化这个过程，让更多人更轻松而富有乐趣地工作。我是 BI 佐罗，感谢大家。于 2023.03.06最后，看完两件事：1、双击屏幕点赞+收藏，让更多的人能看到这个回答。2、关注我，第一时间了解 Power BI 方面最新动态。写作不易，点个赞再走吧~