首页 > Word

Excel函数与公式2.0时代，享受革新带来的便利

Word 2024-04-14 00:00:14 31

01
Excel函数与公式的江湖排名

你们知道，Excel座下猛将如云，
最厉害的四大金刚分别是函数与公式、数据透视表、图表和Power Query，还有一位大长老VBA，以及去年新加入的客座长老Python。

对于用户来说，
最常用的，最核心的，
最容易上手又最难进步的，
最需要学习又最容易忘的，
最烧脑但是又最有用的，
最简洁明了又最深不可测的，
最保守又最推陈出新的，
就是函数与公式。

02
推陈出新的函数2.0时代

为啥是最保守又最推陈出新的呢？
先说保守。
作为一个从Excel 5.0一直玩到现在的Excel资深玩家，我告诉你们，这几十年来，Excel的函数与公式几乎就没有什么变化，最多不过是在升级新版本时加了几个函数，而且这些新增的函数大部分都默默无闻。

最耀眼的是那几个带S的统计函数，SUMIFS、COUNTIFS、MAXIFS之流。
而且，因为Excel一直是向下兼容，所以老函数永远不会被新函数替代，大家是和谐幸福的一家人。
甚至，直到今天，Excel还保持了对一个已经消失的曾经的对手的公式兼容，

以及早期Excel宏表函数的兼容。

够保守吧？
但是，在跟随Office套件成为订阅制的Office 365之后，可能是为了增加365这个版本的优越性，Excel 365在2019年发布了XLOOKUP这个万众瞩目的新函数，其威力远超传统的VLOOKUP。
之后，Excel的函数与公式功能仿佛开了挂，新功能层出不穷。
主要包括可编程函数LAMBDA、LET，动态数组以及一大波支持动态数组溢出的新函数。
但是，大部分新功能，并没有及时加到单机版的Excel里面，一直到Excel 2019，都无法用上这些新特性。
幸运的是，Excel 2021终于可以用上了。
当然，如果你已经是Office 365的订阅者，那么你已经早用上几年了。
所以，重点来了，Excel 2021和最新版的Excel 365，已经进入了函数与公式2.0时代。

03
XLOOKUP函数

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

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

04
可编程函数LAMBDA

LAMBDA函数其实一直是多个编程语言中存在的高档货，用来自定义函数。
以前在Excel里面如果想要得到一个自定义功能的函数，必须使用VBA来实现。
现在，LAMBDA现身Excel，我们可以直接在工作表里面自定义函数。
举个简单的例子：
已知一组直角三角形两个直角边的边长，我们想要定义一个可以计算斜边边长的函数，就可以这样：
=LAMBDA(x,y,(x^2+y^2)^0.5)(B2,C2)

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

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

05
动态数组和它的函数们

Excel一直都支持数组运算，也支持数组公式。
但是，传统数组公式只能返回结果到一个单元格里面，这在很多情况下是很不方便的。
让我们来看看动态数组的威力：

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

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

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

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

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

我就问你们想不想烧脑学这么复杂的公式？
在Excel 2021和Excel 365版本中，只要在F5单元格输入公式，按回车即可。优雅不优雅？

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

以前，这种需求使用公式处理非常复杂。公式太长，这里就不展示了。
在Excel 2021和Excel 365版本中，只要在F2单元格输入公式，按回车即可。

总结一下，使用一个公式，就能返回一整个表格的结果，这就是动态数组。
当下，Excel的函数数量已经增加到了500多个，
尽管熟练掌握20个左右就可以算入门了，但这是下限指标。
熟练掌握50个左右以后，就过了临界点，再往后的话，函数水平增长与继续掌握更多函数之间没有显著的相关性。
而动态数组和相关新函数的加入，让以前很多难以计算的场景变得轻松很多，
无论你目前是新手还是老表哥大表姐，我都强烈建议升级到最新版本的Excel，享受函数与公式2.0带来的效率福利。

06
拥抱新时代，做Excel函数的主人

学Excel，最需要学习的就是函数与公式，这是四大金刚之首。
但是这东西对于很多人来说确实不好学。
很多人费力的掌握了一些函数的基础用法，组合的时候就抓瞎了。
更多的人连单个函数的基础特性都没搞清楚。
搜索引擎用的倒是熟练，但碎片化的信息每次只能让人一知半解。
猛学一阵子，三五天不用，又忘了。
如此往复，就总也学不好。
还有人寄希望于AI，AI确实是个好帮手，但我作为AI课程的讲师可以负责人地告诉你，在目前阶段，学好AI和学好Excel本身的功能，不是二选一，而是互相促进。

天下人苦Excel函数难学难用久矣，
如今，老爷车已经升级为超级跑车，性能倍增，门槛降低，我们如果不马上上车，还怎么在数据分析之路上愉快的玩耍？
并且，ExcelHome经典图书《Excel函数与公式应用大全》也升级到了最新版本。