专栏名称: Excel之家ExcelHome

excel技巧原创教程每日推送，excel表格职场模板干货仓库，图文/视频/动画等多种教学方式分享excel操作技巧教程/excel函数公式教程/excel数据透视表教程/excel图表教程/Word教程，助您轻松提高办公效率!

Excel函数与公式2.0时代，一不留神就掉队

Excel之家ExcelHome · 公众号 · Excel · 2024-09-27 06:45

正文

2019年，微软发布了XLOOKUP函数，其威力远超传统的VLOOKUP。

之后，Excel的函数与公式功能仿佛开了挂，新功能层出不穷。

主要包括可编程函数LAMBDA、LET，动态数组以及一大波支持动态数组溢出的新函数。从此，函数与公式进入了2.0时代。

XLOOKUP函数

例如，要根据F列的姓名，在左侧数据表中查找对应的门店信息，就可以使用XLOOKUP函数了。

XLOOKUP函数的查询区域和返回区域是分开的两个参数，不用再考虑查询的方向问题，不仅能实现从左到右，还能从右到左、从下到上、从上到下等任意方向的查询。

可编程函数LAMBDA

LAMBDA函数其实一直是多个编程语言中存在的高档货，用来自定义函数。

以前在Excel里面如果想要得到一个自定义功能的函数，必须使用VBA来实现。现在，LAMBDA现身Excel，我们可以直接在工作表里面自定义函数。

举个例子：

B列是一些合并单元格，使用以下公式可以创建一个内存数组，将B列的数据填充完整。

SCAN函数用于遍历数组中的每个值，并且能够记录LAMBDA函数运算体每次运算的值，在下一次运算中调用这个值。

本例中，SCAN函数将空文本作为累加器的初始值，B2:B9单元格是要遍历的数组。

第一次遍历时，a为空文本，b为B2单元格的“1005-2”，遍历结束后，IF表达式的结果为“1005-2”。这个结果在第二次遍历时会作为新的a。

第二次遍历，a为“1005-2”，b为B3单元格的空文本，第二次遍历结束后，IF表达式的结果仍然为“1005-2”。该结果在第三次遍历时继续作为新的a。

到第四次遍历时，a为“1005-2”，b为B4单元格的“1006-3”，遍历结束后，IF表达式的结果变成“1006-3”，该结果在第五次遍历时作为新的a……。

有了LAMBDA，我们从此可以把函数和公式当作编程工具来使用，语法仍然是传统的Excel函数，这对于高级用户，简直是解开了力量的封印。

再举个例子：

如下图，使用以下公式可以计算出各学科的最大值之和。

=SUM(BYCOL(B2:I6,LAMBDA(x,MAX(x))))

BYCOL函数在B2:I6单元格中逐列遍历，LAMBDA将BYCOL函数函数遍历的结果定义为x，再用MAX函数计算出每个x（逐列遍历结果）的最大值。

Excel一直都支持数组运算，也支持数组公式。

但是，传统数组公式只能返回结果到一个单元格里面，这在很多情况下是很不方便的。

让我们来看看动态数组的威力：

例子1，如下图，需要将B列内容转换为4列。

以前，只能在一个单元格输入公式，然后再向右向下拖动复制公式。

在Excel 2021和Excel 365版本中，只要在D2单元格输入公式，按回车即可。

例子2，如下图，需要在左侧数据表中提取出“老城区”的所有记录。

以前，需要使用一段超长的数组公式，按组合键输入后，再向右向下拖动复制公式才行。

在Excel 2021和Excel 365版本中，只要在F5单元格输入公式，按回车即可。

例子3，如下图，需要将左侧数据按照指定的职务顺序来排序。

以前，这种需求使用公式处理非常复杂。

而在Excel 2021和Excel 365版本中，只要在F2单元格输入公式，按回车即可。

使用一个公式，就能返回一整个表格的结果，这就是动态数组。

动态数组和相关新函数的加入，让以前很多难以计算的场景变得轻松很多，无论你目前是新手还是老表哥大表姐，我都强烈建议升级到最新版本的Excel，享受函数与公式2.0带来的效率与便捷。

图文制作：周庆麟

1、点击或搜索微信公众号【 Excel之家ExcelHome】到公众号首页。

2、点击右上角【···】，在下拉菜单中点击【设为星标】。

设置星标后，小伙伴们就不用担心错过咱们的推送了！

设为星标，精彩内容不错过