第31天:Excel必备!6个神奇函数公式，让数据处理快人一步

在处理Excel数据时，很多人常因函数公式复杂而头疼，即便收藏了很多，不用就忘。其实，关键在于理解函数与公式的原理。函数是预设指令，公式由函数构成、功能更强大。接下来将详细讲解6个实用公式，帮你提升数据处理能力。

1. 计算两个时间差

函数讲解：

基本语法：TEXT(value,format_text)

功能及参数解析： TEXT函数用于将数字转换为按指定数字格式表示的文本 。value：必填，是要进行格式设置的数据，在此处是两个时间相减的差值。format_text：必填，用于指定输出格式，[m]在时间格式里代表分钟数，加[ ]可显示大于60分钟的结果。

示例：

求解的问题：计算起始时间与结束时间之间的分钟数差值。比如在这个例子里，要算出2023/5/1 8:00到2023/5/1 10:30之间具体间隔了多少分钟，以及2023/5/2 15:15到2023/5/2 17:45的分钟差值。

函数应用：在C2单元格输入 =TEXT(B2 - A2,"[m]分钟")，再向下填充。这里从C2单元格开始输入公式，是因为我们的数据从第二行开始，并且C列是用来存放计算时长结果的列。向下填充公式时，软件会自动根据每一行A列和B列的时间数据进行计算。

运算结果：

参数解析：B2 - A2：这部分是计算起止时间的差值。以第一行为例，就是用B2单元格（2023/5/1 10:30）的时间减去A2单元格（2023/5/1 8:00）的时间，得到这两个时间点之间的间隔时长数值。[m]分钟：它的作用是指定将前面计算出的差值以分钟数的格式显示。如果没有加[ ]，当分钟数超过60时，显示可能会不准确，加了[ ]就能完整显示实际的分钟数，像这里计算出的150分钟就能正确呈现。

2. 计算到期时间

函数讲解：

基本语法：EDATE(start_date,months)

功能及参数解析： EDATE函数用于返回指定日期（start_date）之前或之后指定月份的日期 。start_date：必填，是开始日期。在实际应用场景中，比如计算合同到期时间，这个参数就是合同的起始日期。months：必填，为间隔月数，正数表示之后的月数，负数表示之前的月数。在计算合同到期时间时，它就是合同规定的时长月数。

示例：

求解的问题：根据给定的合同起始日和合同时长，准确计算出合同的到期时间。例如，已知合同从2023/3/1开始，时长为6个月，我们要通过这个函数公式算出它的到期时间；还有从2023/5/15开始，时长3个月的合同，也要算出相应的到期时间。

函数应用：在C2单元格输入 =EDATE($A2,$B2)，并向下填充。这里使用美元符号（$）锁定A列，是为了在向下填充公式时，保证每一行都是以A列对应的合同起始日为基准，结合B列的合同时长来计算到期时间。如果不锁定A列，向下填充时A列的引用会随着行数变化而错误变动，导致计算结果出错。

运算结果：

参数解析：$A2：通过美元符号锁定A列，使得在向下填充公式的过程中，始终获取A列对应行的合同起始日。比如在计算第二行的到期时间时，$A2就固定指向A2单元格（2023/5/15），保证计算基准的准确性。$B2：获取B列对应行的合同时长的月数。同样在计算第二行时，$B2指向B2单元格（3），用于和起始日结合计算到期时间。

3. 隔列求和公式

公式讲解：

问题背景：在处理有规律标题的数据时，经常会遇到需要对特定列进行隔列求和的情况。例如，在统计产品不同月份的销售数据时，有实际销售数据列和计划销售数据列，我们可能只需要对实际销售数据列进行求和统计，这时候就可以用到这个隔列求和公式。

示例：

求解的问题：按标题要求，对“实际”列数据进行隔列求和。在这个表格中，就是要分别计算出产品A和产品B的1月、2月、3月实际销售数据的总和，并将结果填写在“实际合计”列对应的单元格中。

公式应用：在H2单元格输入 =SUMIF ($A$1:$G$1,H$1,A2:G2)，向下填充。这里，$A$1:$G$1是条件判断区域，用于查找与H$1单元格内容（“实际合计”）相同的标题；H$1是条件，指定我们要对“实际合计”对应的“实际”列数据进行求和；A2:G2是实际求和的数据区域，通过向下填充公式，就能对每一行产品的数据进行相应的隔列求和计算。

结果展示：

步骤解析：SUMIF ($A$1:$G$1,H$1,A2:G2)：首先，SUMIF函数会在$A$1:$G$1区域查找与H$1相同的标题“实际合计”。找到后，它会在A2:G2区域中，对对应“实际”列的数据进行求和。以产品A为例，当找到“实际合计”标题后，会把A2单元格（10）、C2单元格（20）、E2单元格（30）这三个属于“实际”列的数据相加，得到60，并将结果显示在H2单元格。对于产品B也是同样的计算方式，从而实现隔列求和。

4. 超链接公式

函数讲解：

基本语法：HYPERLINK(link_location,[friendly_name])

功能及参数解析： HYPERLINK函数用于创建超链接，当用户点击该超链接时，会跳转到指定的位置。link_location：必填，指定跳转目标，如"#"&B2&"!A1"，B2为工作表名，通过这种格式可以指向同工作簿内指定工作表的A1单元格。例如，如果B2单元格内容是“销售数据”，那么最终链接就会指向名为“销售数据”的工作表的A1单元格。friendly_name：可选，是超链接显示的文本，此处设置为“进入”。这意味着在单元格中显示的不是复杂的链接地址，而是简洁的“进入”二字，方便用户识别和点击。

示例：

求解的问题：在Excel工作簿中，实现快速跳转到指定工作表。比如在包含多个工作表的工作簿里，有“销售数据”“库存数据”“财务数据”等工作表，通过创建超链接，用户点击“进入”链接，就能直接跳转到对应的工作表，无需手动在众多工作表中查找切换，提高操作效率。

函数应用：在C2单元格输入 =HYPERLINK("#"&B2&"!A1","进入")，向下填充。从C2单元格开始输入公式，是因为数据从第二行开始，C列用于存放超链接。向下填充时，公式会根据B列不同的工作表名，自动生成对应的超链接，方便用户快速访问各个工作表。

运算结果：在C列生成“进入”超链接，点击这些超链接，可分别跳转到对应工作表的A1单元格。当点击C2单元格的“进入”超链接时，会直接跳转到“销售数据”工作表的A1单元格；点击C3单元格的“进入”超链接，会跳转到“库存数据”工作表的A1单元格；点击C4单元格的“进入”超链接，会跳转到“财务数据”工作表的A1单元格，实现了快速的工作表切换。

参数解析："#"&B2&"!A1"：这部分构建了指向指定工作表A1单元格的链接。其中，"#"表示链接指向本工作簿内；B2是存放工作表名的单元格，通过连接B2单元格的内容，确定具体要跳转的工作表；"!A1"指定了要跳转到该工作表的A1单元格。"进入"：这是设置超链接显示的文本，将原本复杂的链接地址以“进入”二字呈现，让用户更容易识别和操作。

5. 跨表批量取值

函数讲解：

基本语法：INDIRECT(ref_text,[a1])

功能及参数解析： INDIRECT函数用于返回由文本字符串指定的引用。简单来说，它可以将我们输入的代表单元格地址的文本字符串，转换为真正可以引用单元格数据的引用。ref_text：必填，指定单元格引用，如 B2&"!b10"，B2为工作表名，通过这种连接方式，指向对应工作表的B10单元格。例如，如果B2单元格内容是“表1”，那么这个引用就指向“表1”工作表的B10单元格。a1：可选，指定引用样式，为 TRUE 或省略时为A1样式，为 FALSE 时为R1C1样式 。这里我们采用默认的A1样式，即常见的用字母表示列、数字表示行的单元格引用方式。

示例：

求解的问题：从不同工作表中批量获取指定单元格的值。在实际工作中，可能有多个工作表分别记录不同的数据，但又需要将这些工作表中特定单元格的数据汇总到一个工作表中进行分析。比如，“表1”和“表2”分别记录了不同地区的销售数据，现在要将这两个表中B10单元格的数据提取出来，汇总到当前工作表进行对比分析。

函数应用：在C2单元格输入 =INDIRECT(B2&"!b10")，向下填充。从C2单元格开始输入公式，是因为数据从第二行开始，C列用于存放跨表取值的结果。向下填充公式时，会根据B列不同的工作表名，从对应的工作表中提取B10单元格的值，并显示在C列相应单元格中。

运算结果：根据B列的工作表名，从对应工作表的B10单元格取值并显示在C列。假设“表1”的B10单元格值为100，“表2”的B10单元格值为200，那么C2单元格将显示100，C3单元格将显示200，实现了跨表数据的快速提取和汇总。

参数解析：B2&"!b10"：这部分构建了对应工作表B10单元格的地址引用。B2单元格存储着工作表名，通过连接运算符&，与"!b10"相连，形成了指向特定工作表B10单元格的文本字符串。例如，当B2单元格内容为“表1”时，这个表达式就变成了“表1!b10”，告诉函数要从“表1”的B10单元格取值。

6. 提取不重复值

公式讲解：

问题背景：在处理数据时，经常会遇到需要从数据列里提取不重复的值的情况。例如在员工信息管理中，员工姓名列可能存在重复记录，我们需要整理出一份不重复的员工名单，以便进行后续的数据分析、统计等工作，此时就可使用这个公式来提取不重复值。

示例：

求解的问题：从A列员工姓名中提取不重复的姓名。在这个示例中，A列有“张三”“李四”“张三”这些数据，我们要通过公式去除重复的“张三”，只保留一个“张三”，并将不重复的姓名依次显示在C列，形成一份不重复的员工名单。

公式应用：在C2单元格输入 {=INDEX(A$1:A$99,MATCH(0,COUNTIF(C$1:C1,A$1:A$99),0))&""}（需按Ctrl + Shift + Enter三键输入），向下填充。从C2单元格开始输入公式，因为C列是用来存放不重复值结果的列，且数据从第二行开始。按Ctrl + Shift + Enter三键输入是因为这是一个数组公式，只有这样输入，Excel才能正确理解和执行公式的运算逻辑。向下填充公式时，会逐步提取出A列中的不重复值。

结果展示：

步骤解析：COUNTIF(C$1:C1,A$1:A$99)：这个部分会统计A列每个值在C列已提取值中的出现次数。以第一行为例，C$1:C1此时为空，那么A列的每个值在这个空区域中的出现次数都为0。随着向下填充公式，C列逐渐有了提取的值，COUNTIF函数会重新计算A列每个值在已提取值中的出现次数，出现次数为0的就是还未被提取的不重复值。MATCH(0,统计的个数,0)：它的作用是查找统计结果中值为0的位置。因为出现次数为0代表该值是尚未提取的不重复值，所以通过这个函数找到0所在的位置，就确定了下一个要提取的不重复值在A列中的相对位置。INDEX(A$1:A$99,行数)：根据前面MATCH函数找到的行数，从A$1:A$99区域中提取对应的值。比如MATCH函数找到的行数是1，那么INDEX函数就会从A$1:A$99中提取A1单元格的值。&""：将前面提取的值转换为文本格式。因为INDEX函数返回的值可能是数值或其他数据类型，加上&""后，能确保最终结果是文本格式，避免在数据显示或后续处理中出现问题。

以上就是精心为大家准备的6个Excel函数公式，学会它们，你会发现数据处理轻松许多。觉得有用的话，记得点赞、转发，让更多人受益，也别忘记关注，获取更多Excel技巧！