有哪些很实用的Excel函数？

先问个问题：Excel有多少个函数？

有500+个，你答对了吗？

那么，你在工作中一般会用到多少个函数？是不会，“书到用时方恨少”，还是没必要，“大不了就是费点手工”。

这么多个函数肯定是不需要都掌握的，但常用的函数及其组合的应用我们需要有所认识。但也不需要像考试一下死记硬背。初学者也只需要理解应用场景及其原理即可，建议记住一些专用词汇，以后Excel处理中碰到类似问题，首先知道有Excel函数能够解决，然后具体用法通过搜索获取，记住专用词汇是为了在搜索时可以更精准。本节中的专用词汇第一次出现时会加粗，留心即可。

其实在微软官网上，就有非常好的学习资料。

官网链接在阅读原文中。以下是截图。

比如下图介绍的XLOOKUP函数，是Excel 2021新增的VLOOKUP函数的改进函数，简介之下有视频介绍，然后是详细的语法介绍。

对于初学者，建议先去找类似这样的资料自学一下。本节不会去一一讲解所有函数，仅会在花少量篇幅以示例形式列举简单常用函数的用法之后，讲解数据分析中常用的函数用法。

之后会再花一小节的篇幅，讲解特定场景下需要&好用的函数进阶用法。这里主要强调的是要理解函数特性、灵活组合构造函数、编写函数。

最后会再简单介绍一下，函数在其他场景下（如条件格式、图表）中的应用。

打开Excel就能看到一个个单元格组成的二维表，水平方向延伸的是“行”，序号是从1开始往下累加一直到1048576，垂直方向延伸的是“列”，序号是从A-Z、AA-AZ，一直到XFD（共16384列），每个单元格就可以通过行和列的序号被定位，在Excel中这就是“引用”。相反，引用了其他单元格的单元格就是“从属”单元格，从属单元格也有自己的序号地址。当引用的是多个连续的单元格时，即区域引用。

当在公式中使用单元格（或区域）引用时，可以使用4种类型的引用：

行列相对引用：当把公式复制到其他单元格中时，行或列引用会发生改变，因为这些引用实际上是相对于当前行或列的偏移量。默认情况下，Excel会在公式中创建相对单元格引用（如A5）。

行列绝对引用：当复制公式时，行和列引用不会发生改变，因为引用的是单元格的实际地址。绝对引用会在其地址中使用两个美元符号：一个用于列字母，另一个用于行号（如$A$5）。

行绝对列相对引用：当把公式复制到其他单元格中时，列引用会发生改变而行引用不变（如A$5）。

列绝对行相对引用：当把公式复制到其他单元格中时，行引用会发生改变而列引用不变（如$A5）。

如果我们想在多个单元格使用同一公式，就必须考虑对单元格的引用的行和列是相对引用还是绝对引用。

举个例子，如下电商购物流程漏斗数据，若是环节转化率，每个环节都是除以上一个环节的人数，相对位置不变，所以这里用的是相对引用；整体转化率都是除以最开始的那个环节，随着公式往下复制，相对位置就变大了，这里就该使用绝对引用，以保证不会随着公式往下复制，导致引用位置也相对移动。

其中非常好用的TEXTJOIN是Excel 2019之后版本新增函数。

以上函数中，TEXT函数需要单独拎出来着重讲一下，因为它的用法主要是格式代码的多样性，这里列举常用格式代码如下。

数字格式代码、含义及示例

日期和时间格式代码、含义及示例

关于日期和时间格式显示的函数，在上面的TEXT函数已经讲过了，这里主要讲日期和时间处理函数。

比较简单，看下图即可。

查找与引用类型函数是相对比较复杂的一类函数，我们先将比较重要的这类函数的基本用法示例说明，然后再针对比较常用的函数组合用法详细展开。

XLOOKUP，比VLOOKUP更强

世人只知VLOOKUP，而不是XLOOKUP，殊不知XLOOKUP比VLOOKUP其实更好用，弥补了VLOOKUP的很多缺陷。刚刚结果的VLOOKUP的复杂用法，其中不乏一些巧思，但用XLOOKUP来解决，就变得异常简单了。下边的几个例子中，每个例子都用了多种解法，整体对比下来，XLOOKUP函数比VLOOKUP使用上更简约、理解上更容易。

较为复杂的引用函数

引用函数

前面提到的函数是放任何领域都比较通用的函数，而从这里开始之后的函数，可能在数据分析领域更为常见一些。我们先介绍最最最常用的计数和求和函数。

计数和求和函数主要分三组，计数、求和和平均（即求和除以计数），每组中均包含XX、XXIF和XXIFS的函数，另外求和函数还多了一个SUBTOTAL和SUMPRODUCT函数。

由于三组函数用法基本相同，本节以求和函数为示例来介绍。

SUBTOTAL与SUM的区别

快捷键ALT+=可以快速填充求和公式，与手写SUM公式的好处在于不用挨个选择求和区域，即可以自动选择（连续）求和区域。有两种用法。

第一种，Ctrl多选求和公式所在单元格后ALT+=，这时公式是SUM(X:X)

第二种，筛选可见单元格后ALT+=，这时对应的公式是=SUBTOTAL(9,X:X)，SUBTOTAL函数有个特性是可以仅对可见单元格求和（需要把=SUBTOTAL(9,X:X)改成=SUBTOTAL(109,X:X)），这也是SUM函数做不到的。

条件求和

说到求和函数，必须重点讲一下SUMIF，SUMIF仅针对满足条件的数据求和，也就是筛选后求和。这个筛选可以是数据本身，也可以是数据所在行的其他字段，筛选方式可以是数值比较，也可以是匹配通配符。

下面这张图可以讲清楚常用的条件求和函数。一题多解，主要是为了拓展函数思维。如有疑问，可在评论中提出来，我看到会解答的。

加权求和、加权平均

较为简单，这里只简单示例一下。

统计类型函数，还有各类分布的对应函数，如正态分布的NORM.DIST函数和γ分布的GAMMA.DIST函数。我实际工作中几乎没用到过，感兴趣的可以自己再深入学习了解。

“数组”这个概念在编程语言中并不陌生，但在Excel中可能很多人以为自己没接触过，但其实随手可见。

如下动图所示，引用的区域B3:C5其实就是一个数组，在编辑栏按F9，得到B3:C5对应的3行2列的常量数组{1,2;3,4;5,6}。从这里我们可以知道，数组是一对花括号{}把所有元素包含起来，用逗号代表列与列的间隔，分号代表行与行的间隔。

单单元格数组公式和多单元格数组公式

如果数组只存在于计算过程的内存中，最终输出结果只有一个单元格，那么这就是单单元格数值公式，典型的如SUM函数。

如果返回结果是数组，输出结果是存在多个单元格中，这就是多单元格公式了，典型的如TRANSPOSE函数。由于多单元格数组公式是一次计算产出多单元格结果，比起多单元格的多次重复计算，计算效率是高很多的。

在Excel 2019版本之前的数值公式，需要编辑完成后按下CSE（Ctrl+Shift+Enter）才是正确的，但现在可以跟普通函数一样，只按Enter即可。因为Excel特别的溢出设计。有了这个就不需要先计算好有结果是几乘几的数组，再选好同等大小区域来应用数组公式了。

没有花括号的单单元格数组公式，看起来跟普通公式毫无二致。比如上文提到的求最高三项平均值，公式是=AVERAGEA(LARGE(E3:E12,ROW(1:3)))，就是先利用ROW(1:3)产生一个{1,2,3}的常量数组，作为LARGE函数的第2个参数，取出区域中的TOP1、TOP2和TOP3，再对这三个值求平均。再比如查找与应用函数中的第一个例子，单列查找结果多列，公式=XLOOKUP($G5,B$2:B$26,C2:D26)也用到了溢出，不用考虑旁边各自的公式要怎么写了。

多单元格数组公式具有同一性，因为数组本来就是按相对位置计算的，所以绝对和相对引用的区分就没那么重要了。只需要在左上角编辑一个公式，Excel就会自动溢出到周边单元格，整个数组公式区域用一个发光的边框框起来。事实上只有数组的左上角单元格可编辑，其他单元格的编辑栏置灰不可编辑。

那么本小节的数组公式，就着重讲多单元格数组公式的应用了。

数组公式应用示例

若你需要经常做某种计算，而用Excel自带公式实现，步骤很多容易出错，建议你把计算逻辑固化为自定义函数。

我曾经编辑过一个函数，实现功能是：将一个指标值映射到Ymin-Ymax之间，也就是指数化。具体功能是，指标值大于Xmax时，Y取Ymax，指标值小于Xmin时，Y取Ymin，指标值在Xmin-Xmax之间时，Y在Ymin-Ymax之间线性分布。