对于初学 Excel 人来说，函数公式是其中比较重要的一部分。但 Excel 中函数有 400 多个，常用的也有 40 个左右。
除了学会每个函数的使用方式外，有哪些关于 Excel 公式的小技巧，可以提高函数的使用效率呢？今天就来浅聊一下~

一、常见错误规避

01丨用好混合引用

这个技巧在学习 Excel 公式时，很基础但也很重要，如果这个不了解，可以会出现下面的错误。

假设我们有这样的一组成绩单：

▌表格中姓名信息均为模拟

你需要将每个学生的总成绩找过来，使用 VLOOKUP 函数就可以搞定：

但当你向下拖拽时，发现某些数据在原数据中存在，但无法抓取过来：

检查后发现，VLOOKUP 的第二参数（在哪儿找）变动导致出错。

这个问题很多人应该也知道如何解决，按下F4锁定行和列，切换为绝对引用即可：

但碰上相对引用与绝对引用混用时，可能就需要花费一点时间了。

比如说，在上面的基础上，你需要查询各个科目的成绩：

由于查询表的表头与数据源表头的顺序并不相同，无法使用 COLOMUN 函数完成。此时，你想到了可以嵌套使用 MATCH 函数：

但当你向右或向下拖拽填充公式时，无论是切换为纯相对引用还是纯绝对引用，都会出错：

此时，必须使用混合引用：

那么，问题来了，如何确定锁定行还是列？

给大家说一下我的记忆方法，谁不变锁谁。还可以进一步联想这样记忆，穷则思变，穷=$。

具体到这个例子中，向右拖拽时，姓名列列号不能变，向下拖拽时，科目的行号不能变。因此，前者锁列，后者锁行。

02丨用好转换符号

同样在上面例子基础上，由于姓名容易重复，统一使用八位数字的学号替代，而数据源如下：

那么，我们这次简单一点，只需要找到某个学号的总成绩，但使用VOOKUP 函数查找时，却找不到：

这是因为，查找表中的学号是数值，而数据源表中的学号是文本。

此时，你可以让查找的学号强制转换为文本类型，也就是添加一个空字符串，具体公式如下：

当然，如果你的数据源的学号列本身是数值形式的，那么，可以使用--学号做强制转换。你看到其他公式的0+学号学号*1等也是这个思路。

另外，在 VLOOKUP 函数是完整匹配的，如果你仅需要匹配其中的某几个字，可以使用"*"&查找值"*"作为第一参数。比如要查询某个公司的全称：

这里的“*”表示通配符。除此以外，也可以使用"?"代表一个字符。

03丨用好辅助列

很多情况下，我们需要嵌套使用多个公式，同样是上面的例子，需要匹配不同姓名不同科目的成绩，我们使用了VLOOKUP 和 MATCH 两个函数的嵌套，公式就比较长了，差不多占据了 10 个单元格的距离：

而实际工作中，我们可能还会添加一些容错函数，比如 IFERROR 等，这样一来，公式就更长了。

但公式一长，不仅是占地多，出错的概率也随之增大。并且，在整个公式录入过程中不能中断，否则会出现各种错误，比如参数个数不对，括号未正确匹配等：

如果原数据源允许复制的情况下，我强烈建议你复制到临时表，并且使用辅助列一步步算，帮你理清思路：

最后再整合为一个公式。这样哪一步骤有问题改哪个步骤就可以，不用再去那么长的公式里找括号了。

04丨用好单元格内换行

上面说到，针对较长的函数公式，可以使用辅助列，但如果遇到必须一个公式完成的情况，那么，我建议你，将编辑栏区域拉高，在公式单元格内使用Alt + Enter换行，并使用空格来区分不同的层级：

当然，如何划分层级关系，看你的个人习惯，我这里是将每个参数都单独成行了。

二、利用函数说明

01丨基本函数

在 Office 比较高的版本（如 365、2019 等），当你新建 Excel 文件时，可以在联机模板中找到一份《公式教程》：

这份文档中给出了常用的函数介绍及使用方法，比如反复提到的 VLOOKUP：

当然，如果你的 Office 版本比较低或者有其他情况，那么我也为你准备了离线版本，可以在「未央暮城」公众号后台回复「函数技巧」领取。

02丨常见函数

上面的官方教程，仅仅是一些基础的函数和使用方法，很多函数并没有添加进来，比如 INDEX 和 MATCH 的经典搭配。

而微软官网上，给出了所有公式的使用方法：

但说明性的文档 + 类似“坐和放宽”的奇怪翻译，普通人不是很容易理解。

因此，我也为你找到了一份更容易懂，并且非常详细的公式说明文档：

里面的内容非常详实，以 VLOOKUP 为例，介绍各种参数的说明：

还有很多示例来帮你理解函数如何使用：

同样，需要的同学可以在「未央暮城」公众号后台回复「函数技巧」领取。

当然，上面这些都是别人总结的，可能并不适合你，你可以参考制作一份自己的函数手册。

并且，不要局限在单个函数的使用方法上。比如我们知道可以通过 MATCH 和 INDEX 实现逆向查找，那么，就可以定义一个组合函数：

有了这个函数使用说明文件，常用的直接套用就可以，不用再去一个个看参数解释了。

03丨自创函数

上面介绍的两种函数，都是使用系统内置的函数来完成。优点是发给别人时，不用过多考虑兼容性的问题，只要你使用的不是 FILTER、XLOOKUP 这种仅在新版 Office 版本中才有效的函数。但缺点就是，某些功能无法实现或者实现起来比较复杂。

比如说，我想要获取单元格内的手机号：

看起来挺简单的一个需求，但仅限使用内置函数时，因为涉及到数组，运算逻辑很复杂：

其实， VBA 中的正则表达式可以轻松完成，也很好理解：

另外，它还可以处理更加复杂的文本，比如提取 8~10 位这种不确定个数的纯数字编号：

当然，不仅仅是提取数字，提取邮箱、QQ号、身份证号等均可以实现，这里只是正则的冰山一角。

如果你对使用 VBA 制作自定义函数感兴趣，可以参考以下两个开源免费的小工具：

🔗公式plus开源，VBA还可以这么玩

🔗浅北表格助手 - 自定义函数

当然，自创函数的缺点也很明显，迁移比较麻烦。将计算后的结果同步给别人时，需要把公式转换为值，或者将自定义公式模块复制到 Excel 文件中。

浅北文件助手中的「原位粘贴」功能

三、个人妙招

最后，再附加给大家两个关于 Excel 公式的小技巧。

01丨批量填充公式

我们知道，在向下拖拽填充公式时，可以直接双击单元格右下角的方点，但要实现向下+向右拖拽自动填充公式，好像只能手动拖拽。

其实，遇到这种情况时，我们可以先选中已经编写好公式的单元格，然后扩选到要填充公式的区域（比如按住 Shift 选择两单元格所在的矩形区域），依次按下F2 Ctrl + Enter，公式就可以批量填充了：

02丨长公式编辑技巧

举个简单的例子，我需要从一个完整路径中提取文件名称，我需要用很长的函数实现：

如果要提取文件主名（不包括扩展名的），那么还需要嵌套一层：

转换为比较通用的公式如下：

虽然我已经将公式收藏了，但在将公式的路径替换为具体的单元格时，一个个改太繁琐了，此时可以使用替换功能完成：

如果公式内涉及到跨表引用，直接输入单元格的地址比较复杂。此时我们可以先在临时单元格内输入=，然后使用鼠标选择具体的单元格引用，最后在编辑框中就可以获得自动生成的引用文本了：

最后

对于函数的学习，就我个人来说，把函数的基础用法学好就可以，不要觉得函数是万能的，无论计算什么总想着“用函数解决”。Excel 中有一些更好用的工具等你使用。

对了，如果你需要微软官方和我本无尘编写的函数说明文档，由于链接可能会失效，B站由没有自动回复功能，你可以在「未央暮城」公众号后台回复「函数技巧」领取。