现代办公室工作，Excel 大概是那个绕不过去的 app1。处理电子表格，尤其是处中小型体量的数据，Excel 表格的直观易用性还是非常强的，它提供了丰富的数据格式功能，可以对数据进行透视汇总分析，同时也支持很多提取、匹配数据等等的函数，都是日常工作中经常遇到的使用场景。
本文将从具体案例入手，讲解在我日常工作中常用的一些 Excel 功能，与站内外各位大佬交流。如果你看了之后受到一些启发，欢迎举一反三，在评论区，或者少数派发表自己的心得。文中也有一些「思考题」，供感兴趣的读者自行尝试拓展，参考答案也附在文章最后，与大家交流。
本文很长，建议先读目录，看看具体有哪些场景，可以从自己熟悉的开始读。囿于笔者水平，如文章有误，还请各位读者大大们不吝赐教。
格式化文本和数字
场景 1：使用 TEXT 等函数固定日期格式
例如：现在有一张表，上面记载了一些员工信息，包括姓名和生日。需要计算他们的年龄（截止到今天，可以自动更新），并把生日和年龄写成「XXXX.XX【换行】（XX岁）」的格式，并形成一列。
在这里，我们假设员工的日期数据已经清洗完成，是以 Excel 可读取的日期数据格式。
面对这种场景，我们需要用到的函数是 TEXT、DATEDIF、TODAY 函数。其中：
- TEXT 函数可以将数字格式的数据固定为特定格式的文本；
- DATEDIF 函数可以按不同单位计算两个日期间的差距，在这里我们用于计算年龄2；
- TODAY 函数返回数据表打开当天的日期；
- CHAR 函数用于在文本串中插入特定 ASCII 字符（在本例中为换行符）。
首先，考虑日期如何格式化。
Excel 读取日期格式的数据为一个整数序列：以 1900 年 1 月 1 日为「第一天」（也就是数字 1），此后的日期每天增加 13。
例如，2026 年 2 月 20 日为 46073。因此，每一个日期都是一个数字，当你在 Excel 表格中将数字单元格格式化为「日期」时，它就会按照这个规律将数字转换为日期来显示。
这样处理明显是为了计算方便。表格数据保持计算上的方便性和数据格式上的一致性非常重要。因此，我们尽量在不改动原数据的情况下，以文本特定格式的形式来把日期固定成为我们想要的格式。
在示例 1 电子表格中，员工的出生日期在 B2 单元格。下面的公式使用 TEXT 函数来重新将这些出生日期格式化为文本格式：
=TEXT(B2, "yyyy.mm")
TEXT 函数接受两个参数：需要变成文本的值，以及文本格式。函数接受的文本格式为 Excel 的标准格式代码，例如，「yyyy」表示「四位数年份」，比如 2026；「mm」表示「两位数月份」，比如 09。使用「.」来连接两个代码，即可将 B2 单元格中的日期转换为文本的「XXXX.XX」的格式。TEXT 函数接受的格式代码可以查看 微软官方函数支持 页面。
示例 1 中，张三的出生日期为 1998 年 9 月 10 日，重新格式化后，显示的文本是 1998.09。
其次，计算年龄。
计算自然年年龄，可以使用 DATEDIF 函数。这个函数相对古老，Excel 本身已不再为其提供参数提示（但 WPS 中仍然提供），但至今这个函数仍然可以正常在 Excel 中计算。
如果年龄计算以打开工作表的时间为准，需要动态更新，可以使用 TODAY 函数来获取当前的日期。它将按照打开工作表的系统时间为准返回一个日期。此时，函数为（「出生日期」数据在 B2 单元格）：
=DATEDIF(B2, TODAY(), "y")
如果年龄计算以某个固定日期（比如 2025 年 12 月 31 日）为准，无需动态更新，那么函数表达式为：
=DATEDIF(B2, "2025-12-31", "y")
DATEDIF 函数共有 3 个参数：开始日期、结束日期，以及计算单位。其中，开始日期必须早于结束日期，否则函数将报错。计算单位可以是日（"d"）、月（"m"）或者年（"y"），注意在输入公式时，计算单位参数也需要输入引号。
示例 1 中，李四的出生日期为 1993 年 2 月 28 日。以 2026 年 2 月 20 日计算，其不满 33 岁，因此函数给出的年龄是 32。
接下来，用 TEXT 函数将年龄格式化为「XX 岁」，公式中的「0」指代数字。
=TEXT(DATEDIF(B2, TODAY(), "y"),"0岁")
最后，将文本合并起来。
我们的任务是生成形如「XXXX.XX【换行】（XX岁）」的报表格式。目前我们已经有了格式化后的出生日期和年龄。使用 Excel 中连接文本的符号「&」就可以把我们所需要的要素连接起来。同时，为了实现换行的效果，需要使用 CHAR 函数，在文本中插入一个换行符。
=TEXT(B2, "yyyy.mm")&CHAR(10)&"（"&TEXT(DATEDIF(B2, TODAY(), "y"),"0岁")&"）"
CHAR(10) 表达式将插入换行符（ASCII 中编码为 10 的字符）。将括号等元素使用文本连接符「&」连接起来，同时打开「自动换行」，就可以得到我们想要的效果。
思考题
- 为什么一定要用 TEXT 函数来格式化日期，而不是将出生日期原始数据格式化成我们想要的样式后，直接进行拼接？
- 使用「&」连接符连接文本的时候，可能需要注意什么？
场景 2：使用条件格式，快速高亮想要的数据
例如：我们有一张运动会报名人数统计表，A 列为项目名称，B 列、C 列分别列出了 2024 年、 2025 年报名参加各项目的人数。现在，需要高亮 2025 年报名人数不及 2024 年的项目名称。
除了大家比较熟悉的「大于」「小于」「包含」「重复值」等条件格式，Excel 还提供了「使用公式确定条件」的格式选项，只是确实隐藏得比较深。选中需要高亮的区域后，在「条件格式」- 「突出显示单元格规则」中，径直点击最后一个「其他规则...」，打开窗口。在「新建格式规则」窗口中，选择「使用公式确定要设置格式的单元格」。
因为我们要高亮的是「2025 年报名人数不及 2024 年」的项目名称，在公式栏中，我们键入第一行 =C2<B2，点击「确定」，即可看到已经高亮的项目名称。
使用公式确定格式条件的优势是较为灵活，可以根据相同行的其他数据的计算结果（需要使用判断式，例如大小比较、逻辑函数等）来进行单元格高亮。
除了高亮数据，我们还可以通过筛选相关的函数（例如 FILTER）来直接将符合条件的数据挑选出来。在下面的「筛选和匹配数据」一节，还会介绍有关的操作。
文本查找、替换
场景 1：使用 SUBSTITUTE 函数计算单元格中特定字符的数量
例如：Excel 单元格中有一个名单，名字均使用中文顿号（、）连接。需要计算出单元格中的姓名数量。
既然姓名已经使用中文顿号来连接，则名单人数 = 顿号的数量 + 1。因此，我们可以将顿号完全替换后，再使用两个字符串长度之差的算法，再 + 1，计算出名单字符串中的人名数量。
假设名单在 C2 单元格。原完整名单的字符串长度（字符数量）可以使用 LEN 函数计算：
=LEN(C2)
使用 SUBSTITUTE 函数可以将字符串中的所有顿号删除：
=SUBSTITUTE(C2, "、"，"")
SUBSTITUTE 共接受三个参数：要替换的字符串所在的单元格、要替换的字符串（在本例中为「、」），以及替换后的字符（在本例中为空，即可以删除字符）。
两者的字符串长度相减再加 1，即为名单中的人名数量：
=LEN(C2)-LEN(SUBSTITUTE(C2, "、", ""))+1
场景 2：使用 LEFT、MID 和 RIGHT 函数，从身份证号4中提取生日信息
例如：在一份表格 A 列有一些身份证号，需要根据这些身份证号来提取出生日期，并将他们以日期格式的数据进行存储。
日常工作中，身份证号是非常常见的个人信息。根据身份证号国标，其 7-14 位共 8 位数存储个人出生日期。我们可以使用 Excel 中的文本公式来快速提取出这个日期，并把它转化为数字格式（也就是 Excel 可以用来计算的格式）。这个操作主要用到以下函数：
- LEFT：从字符串开头截取固定数量的字符。
- RIGHT：从字符串末尾截取固定数量的字符。
- MID：从字符串指定位置截取固定数量的字符。
- DATE：根据年、月、日创建日期格式的数据。
首先需要把表示出生日期的部分单独提取出来。可以使用 MID 函数，假设身份证号在 A2 单元格：=MID(A2, 7, 8)。MID 函数接受三个参数，分别是要处理的字符串、开始截取内容的字符位置（从左向右，第一个字符编号为 1），以及截取的字符长度。在这里，需要从身份证号的第 7 位开始截取，一共需要截取 8 个字符。
针对已经截取的字符串，我们可以分别使用 LEFT、RIGHT 和 MID 函数，来把它拆开成「年」「月」「日」。假设截取出来的字符串在 B2，那么表示年份的数字可以使用 =LEFT(B2, 4)，也即截取 8 位数字最开始的 4 个字符。表示月份的数字位于中间，可以使用 MID 函数，方法同上；表示日期的数字位于末尾，可以使用 =RIGHT(B2, 2)。
现在有了年、月、日的具体数据，就可以使用 DATE 函数来生成日期了。DATE 函数接受三个参数，分别是年份、月份和日期。只需要对应填写进去即可。方便起见，我们可以写一个稍微长点的公式：
=DATE(LEFT(B2, 4), MID(B2, 5, 2), RIGHT(B2, 2))
就不需要用很多个辅助列来存储数据了。生成的数据可以参与 Excel 的日期相关计算。例如，可以用上文介绍的 DATEDIF 函数计算年龄。
场景 3：使用 REGEX 系列函数5，利用正则表达式进行文本查找和替换
例如：Excel 表格中存储了一些客户的姓名和电话号码。现在需要单独提取出这些姓名和电话号码。
可以看到，电话号码中有些是手机号，有些是座机号，长度并不统一，同时开头的数字也不完全相同。这时候，我们可以借助正则表达式的力量，将数字提取出来。
正则表达式是一种强大的文本匹配工具，可以通过撰写不同的正则表达式来匹配不同规律的文本。有关正则表达式的教学，个人目前推荐参考菜鸟教程的这个网页，你还可以用 regex 101 这个网站学习和测试正则表达式。
在本例中，需要匹配的是姓名后出现的数字。数字在正则表达式中可以使用 \d 来表示，或者 [0-9]。由于数字出现的次数是 1 次或多次，我们使用 + 这一特殊字符，它代表前面的表达式至少出现 1 次。
因此，匹配电话号码的正则表达式为 [0-9]+ 。使用 Excel 的 REGEXEXTRACT 函数，可以快速将字符串中的电话号码全部提取出来：=REGEXEXTRACT(A1,"[0-9]+")。
除了 REGEXEXTRACT，Excel 中还有 REGEXTEST 和 REGEXREPLACE 函数，分别用于测试字符串中是否有符合正则表达式模式的字符，以及使用新的字符替换正则表达式找出的字符。
例如，使用 REGEXTEST 函数，我们可以快速找出一列单元格字符串中，含有数字的单元格：=REGEXTEST(A2, "[0-9]")。
又例如，使用 REGEXREPLACE 函数，我们可以把符合特定正则表达式的字符串替换为我们想要的内容（例如，将所有的数字打码）：=REGEXREPLACE(A2, "[0-9]", "*")。
正则表达式是非常强大的文本查找和替换工具，我本人也在学习中。如果实在不确定该怎么写，请 AI 帮忙是个好办法。你可以将字符串丢给 AI，告诉他你要替换或者查找的模式，AI 会帮你写出来符合 Excel 标准的正则表达式 —— 但在用于正式数据前，记得请务必测试好哦！
场景 4：一键删除单元格中讨厌的换行符
例如：处理数据时，你发现许多单元格中存在换行符，既不美观，也妨碍具体的数据分析处理。但如何把它们一键删除呢？
如果你使用的是 WPS，那么这个问题会简单很多，使用传统的「查找和替换」功能，就可以快速删除换行符。打开 WPS 表格的「查找和替换」窗口，在「查找」一栏输入 ^l，这个符号就代表单元格内的换行符。如果你觉得麻烦，也可以点击「特殊内容」，然后选择「换行符」，即可插入。「替换」一栏留空，点击确定，即可完成这个操作。
思考题
3. 还记得 CHAR 这个函数吗？如果你手边没有 WPS，使用 Excel 如何完成快速删除换行符的操作呢？
筛选和匹配数据
素材： 本部分使用的电子表格
考试结束，老师拿到了计算机导出的学生成绩，但上面只有学号和成绩两列。为了更好地对学生的成绩进行分析，老师打开了学生信息表，他要进行以下几种操作：
场景 1：使用 XLOOKUP 函数，根据列匹配数据
例如：学生信息表中有学生的学号、姓名、班级等基本信息。为了生成一份本次考试的成绩表进行分析，老师需要使用学号，将姓名、班级匹配到成绩表中。
这种按列匹配数据的操作，可以使用 XLOOKUP 这个函数来实现。这个函数相对来说比较新，但只要你用的是 2021 年以后版本的 Microsoft Excel，或者是 WPS，那就没问题。
XLOOKUP 接受的参数非常直观：
- 你要查找的具体数据（在本例中，即学生的学号）；
- 你要在哪里查找这个数据（在本例中，即学生信息表中的所有学号）；
- 你要返回的数据列在哪里（在本例中，即学生的姓名和班级）。
记得在引用列表数据时使用绝对引用6，方便后续自动填充。
=XLOOKUP(A2,信息表!$A$2:$A$26,信息表!$B$2:$C$26)
XLOOKUP 还提供一个「错误预防」机制。如果在给定的范围内找不到你提供的数据，可以返回一个用户指定的值，而不会直接提示「#N/A」错误。
默认情况下，XLOOKUP 使用的是精确查找模式，即两个数据完全匹配的情况下，才会返回结果。
一般情况下我们都是使用精确查找模式，但需要注意：两侧表格中的数据类型需要一致。例如，可能学生信息表中的学号使用文本存储、学生成绩表中的学号使用数字存储，在查找时可能出现问题。
此时我们需要进行一次格式转换（例如，使用前文提到的 TEXT 函数将数字学号转换为文本格式）再查找即可。
XLOOKUP 可以返回多于一列数据。在单元格范围允许的情况下，多出来的数据会自动溢出。如果你看到「#SPILL」错误，那么说明你引用的数据太多了，单元格空间显示不下。可以尝试多加几列。
考虑到向后兼容的需要，如果你的工作表后续需要给到使用旧版 Excel 的同事，那么可以在检查数据无误后，使用粘贴值操作将查找结果固定下来。这样别人打开你的工作簿的时候，就不会出现错误了。
思考题
4. 以前常用的 VLOOKUP 和 XLOOKUP 有什么区别和联系？
5. 有时，我们会遇到查找值相同的情况（例如，重名）。这种时候，如何更好地确保查找结果不出错？
