下面是21个常用的Excel函数公式的用法，学会这些套路，让工作效率再提高一些。建议收藏！1、IF函数条件判断IF函数是最常用的判断类函数之一，能完成非此即彼的判断。如下图，考核得分的标准为9分，要判断B列的考核成绩是否合格。=IF(B4>=9,"合格","不合格")IF，相当于普通话的“如果”，常规用法是：IF(判断的条件,符合条件时的结果,不符合条件时的结果)2、多条件判断如下图所示，如果部门为生产、岗位为主操 有高温补助。在D列使用公式：=IF(AND(B3="生产",C3="主操"),"有","无")AND函数对两个条件判断，如果同时符合，IF函数返回“有”，否则为无。3、条件求和如下图所示，使用SUMIF函数计算方小文的总成绩：=SUMIF(A2:A16,E2,B2:B16)SUMIF用法是：=SUMIF(条件区域,指定的求和条件,求和的区域)用通俗的话描述就是：如果D2:D5区域的班级等于F2单元格的“一班”，就对C2:C5单元格对应的区域求和。4、多条件求和如下图所示，要统计部门为生产,并且岗位为主操的补助总额。公式为：=SUMIFS(D2:D10,B2:B10,F2,C2:C10,G2)SUMIFS用法是：=SUMIFS(求和的区域，条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……)5、条件计数如下图，要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。=COUNTIF(B2:B12,E3)COUNTIF函数统计条件区域中，符合指定条件的单元格个数。常规用法为：=COUNTIF(条件区域,指定条件)6、多条件计数要求：统计小组及格人数公式为：=COUNTIFS(C2:C10,F2,D2:D10,G2)COUNTIFS函数统计条件区域中，符合多个指定条件的单元格个数。常规用法为：=COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……)7、条件查找VLOOKUP函数一直是大众情人般的存在，函数的语法为：VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)如下图，要查询F2单元格中的员工姓名是哪个小组。=VLOOKUP(F2,A2:D10,4,0)使用该函数时，需要注意以下几点：1、第4参数一般用0（或FASLE）以精确匹配方式进行查找。2、第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。3、如果查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A。4、查找值必须位于查询区域中的第一列。8、多条件查找如下图所示，要求查询部门为生产,并且岗位为部长的姓名。公式为：=LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9)LOOKUP函数多条件查询写法为：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)9、计算文本算式如下图，要计算单元格中的文本算式，先单击第一个要输入公式的单元格，定义名称 ：计算 = EVALUATE(C2)然后在单元格中输入公式：=计算10、合并多个单元格内容要连接合并多个单元格中的内容，可以使用&符号完成。如下图，要合并A列的姓名和B列的出生日期，可以使用公式：=E2&F211、合并带格式的单元格内容合并带有格式的内容时，Excel默认按常规格式进行合并，但是如果是日期、时间或是其他有格式的数值，结果就会让人大失所望了：如何才能正确连接出需要的字符串呢？其实很简单，C2公式为：=A2&TEXT(B2," y年m月d日")首先使用TEXT函数，把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接，就变成了最终需要的样式。12、比较大小写的单词是否相同如果在A1和A2单元格中分别输入大小写的单词，使用以下公式判断时，Excel会默认二者是相同的：=A2=B2如需区别大小写，可以使用公式：=EXACT(A2,B2)EXACT函数 区分大小写，但忽略格式上的差异。13、提取混合内容中的姓名如下图，要从A列姓名电话中提取出姓名，除了使用高版本的自动填充功能，还可以使用公式完成：=LEFT(A2,LENB(A2)-LEN(A2))LENB函数将每个汉字（双字节字符）的字符数按2计数，LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。LEFT函数从文本字符串的第一个字符开始，返回指定个数的字符，最终提取出员工姓名。14、根据身份证号码提取出生年月计算公式为：=1*TEXT(MID(B2,7,8),"0-00-00")首先使用MID函数从B2单元格的第7位开始，提取出表示出生年月的8个字符，结果为："19820319"再使用TEXT函数将字符串转换为日期样式："1982/3/19"然后通过*1计算，将其转换为真正的日期。最后设置为日期格式即可。15、替换部分电话号码如下图所示，要将手机号码的中间四位换成星号，公式为：=SUBSTITUTE(B2,MID(B2,4,4),"****",1)SUBSTITUTE函数的用法是：SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])先使用MID函数取得B列号码中的中间4位，再用“*****”替换掉这部分内容。最后一个参数使用1，表示只替换第一次出现的内容。比如第九行的电话号码是13801010101，最后四位和中间四位相同，如果不指定1，就会全部替换掉了。16、屏蔽函数公式返回的错误值在使用函数公式过程中，经常会返回一些诸如#N/A、#NAME?之类的错误值，要屏蔽这些错误值其实很简单，只需在原公式外侧加上一个IFERROR函数就好。IFERROR函数的用法为：=IFERROR(原公式,出现错误时要返回的内容)如果公式正确，就返回原有计算结果，如果公式返回的是错误值，就返回用户指定的显示内容。17、四舍五入函数ROUND函数这个想必大家经常用到吧，就是对数值按指定的位数四舍五入。比如：=ROUND(8/9,3)就是将8/9的计算结果四舍五入到三位小数，结果为0.889。18、取整的间隔小时数计算两个时间的间隔小时数，不足一小时部分舍去，计算加班时经常会用到，说多了都是泪……=TEXT(B2-B1,"[h]")19、提取日期时间中的日期值要从日期时间数据中提取出日期，可以使用以下公式：=INT(A2)要继续提取时间，只需要做个减法，就欧了：20、生成随机数RANDBETWEEN能够在指定范围内生成一组随机数据，对于广大质检、监理、统计人员来说，这的确是一个伟大的函数。函数的用法是：=RANDBETWEEN(数字下限,数字上限)比如以下公式，就是生成60到100之间的随机数：=RANDBETWEEN(60,100)21、万能查询函数XLOOKUP以下是2020最新版office365的新函数XLOOKUP，几乎一个公式完成了所有查询函数所需的功能。完全可以代替LOOKUP、VLOOKUP、HLOOKUP、INDEX、MATCH组合。向左查询错误提示近似比对横向查询模糊查询逆向查询双向筛选}

Excel 中最常用的功能之一是 VLOOKUP 函数。它允许用户在表中查找数据并返回相应的值。然而，虽然 VLOOKUP 是一个强大的工具，但它也有其局限性。在本文中，我将探讨 VLOOKUP 的 8 个限制，并提供一些最佳解决方案来帮助您克服这些限制并充分利用 Excel 电子表格。第一个限制：VLOOKUP 只能向右搜索，不能向左搜索VLOOKUP 函数的主要缺点之一是它只能在表数组中向右查找。如果要提取的数据位于查找值的左侧，则此函数将返回 #N/A 错误。在下面的场景中，我尝试查找员工 ID 105 的工资。公式=VLOOKUP(A2,A5:D12,4,FALSE)VLOOKUP 函数完美运行并返回了相应的工资，$45,000。在本例中，我想要提取的数据（薪水）位于查找值（ID）的右侧。现在，我们尝试使用VLOOKUP函数来查找员工姓名对应的员工ID。公式=VLOOKUP(A2,A5:D12,1,FALSE)这次 VLOOKUP 函数提取数据失败，但返回 #N/A 错误。在本例中，我想要查找的数据 (ID) 位于查找值 (Name) 的左侧。克服VLOOKUP从左到右查找的局限性要同时查看右侧和左侧，您可以使用 INDEX 和 MATCH 函数而不是 VLOOKUP 函数。在以下场景中，我尝试提取员工 ID，并提供员工姓名作为查找值。此处，ID 列位于名称列的左侧。句法=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])公式=INDEX(A5:D12,MATCH(A2,B5:B12,0),1)配方分解MATCH(A2,B5:B12,0)：此处，MATCH 函数在 B5:B12 范围内查找 James Kim (A2) 的精确匹配项（用 0 表示）。然后，它返回匹配的行号，即 4。因此，公式变为 INDEX(A5:D12,4,1)。现在，INDEX 函数在表数组 A5:D12 中查找第四列和第一列的交集单元格。交叉单元格是 A8，其中包含 id 号 104。因此，上式的输出为104。第二个限制：VLOOKUP 不能查找多于一列比方说，我们想要检索 Bob 从 West 订购的数量。这里，我们有 2 位名叫 Bob 的客户。第一个鲍勃来自北方，第二个鲍勃来自西方。由于 VLOOKUP 函数无法基于多个条件工作，因此它只会检索第一个查找值（来自 North 的 Bob）的数据。这就是为什么输出是 458。克服VLOOKUP函数无法查找多列的问题由于VLOOKUP函数无法处理多个条件，因此我们需要将多列合并为一列。组合列是辅助列。在本例中，我使用以下公式将“客户”和“区域”列合并到“帮助程序列”中：公式=CONCAT(A5,"-",B5)在这里，我使用 CONCAT 函数将客户名称与其区域名称合并，并用破折号 (-) 分隔。现在，我使用客户-区域组合数据作为以下 VLOOKUP 公式中的查找值：公式=VLOOKUP(A2,C5:E12,3,FALSE)这次VLOOKUP函数就可以正常工作了。并且它已检索到客户 Bob 从 West 订购的数量。所以，输出是 442。请记住，这里的表数组是 C5:E12。所以，它是从辅助栏开始的。由于 VLOOKUP 函数从表数组的最左边的列搜索查找值，因此范围是从 C 列而不是 A 列开始的。第三个限制：VLOOKUP 仅限于表数组的最左列VLOOKUP 函数的另一个重要限制是它只能在表数组的最左列中搜索查找值。否则，它将返回 #N/A 错误。在以下数据集中，查找值 (Sarah Lee) 位于表数组 A5:D12 的第二列中。由于查找值不是表数组的最左列，因此它返回 #N/A 错误。VLOOKUP 仅限于表数组最左列的 3 种解决方案第一种方法如果查找值不在最左列中，则需要移动表中的列以确保查找值位于表数组的最左列中。第二种方法您可以更改表数组，使包含查找值的列成为表数组的第一列。第三种方法最后，您可以使用 INDEX-MATCH 公式来执行搜索。使用此公式，您可以足够灵活地选择查找值和表数组。公式=INDEX(F5:I12,MATCH(F2,G5:G12,0),1)第四个限制：VLOOKUP 不区分大小写VLOOKUP 的主要限制之一是它无法执行区分大小写的搜索。看下图，查找值是正确的大小写（Korba）。但查找公式返回的是 KOBRA 的工资，且全部大写。在这里，Korba 和 KOBRA 并不相同。克服VLOOKUP区分大小写的限制要使 VLOOKUP 函数区分大小写，必须使用 CHOOSE & EXACT 函数。句法=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(lookup_value, lookup_array), return_array), 2, 0)公式=VLOOKUP(TRUE, CHOOSE({1,2},EXACT(A2, A5:A12), D5:D12), 2, FALSE)配方分解VLOOKUP 函数的第一个参数是“TRUE”，这意味着该函数将搜索 A2 中的查找值与表数组第一列中的值之间的精确匹配。VLOOKUP 函数的第二个参数是嵌套的 CHOOSE 函数。 CHOOSE 函数用于创建两列的数组。=选择({1,2},精确(A2,A5:A12),D5:D12)={假,5000;假,3200;假,7500;假,6100;假,2800;假,4600;假,8900;假,3700}第一列包含 EXACT 函数的结果，如果 A2 中的值与范围 A5:A12 中的任何值匹配，则返回 TRUE，否则返回 FALSE。=精确（A2，A5：A12）={假;假;假;假;假;假;假;假}第二列包含 D5:D12 范围内的值，这些值对应于第一列中的每一行。VLOOKUP函数的第三个参数是“2”，这表示该函数将返回表数组第二列（即D列）中的值。VLOOKUP函数的第四个参数是“FALSE”，这意味着该函数只会返回精确匹配，而不返回近似匹配。总体而言，此公式正在搜索单元格 A2 中的值与 A5:A12 中的一系列值之间的精确匹配，然后返回找到匹配的行的 D 列中的相应值。第 5 个限制：大数据集性能低下处理大型数据集时，VLOOKUP 可能会很慢，尤其是在同一工作簿中多次使用时。这是因为 VLOOKUP 必须扫描表的每一行才能找到匹配的值，这可能非常耗时。通常，查找一个范围大约需要 6.6 秒，查找整个列大约需要 14.2 秒。为了提高性能，用户可以使用更快、更高效的替代函数，例如 INDEX-MATCH 或 XLOOKUP。第 6 个限制：每次查找仅限一个值即使表中有多个匹配值，VLOOKUP 每次查找也只能返回一个值。当处理重复值或用户想要从表中返回多个值时，这可能是一个问题。解决方案#1：使用公式查找并返回多个值为了克服此限制，您可以使用以下公式的 TEXTJOIN、IF、IFERROR、MATCH 和 ROW 函数。此公式可以返回单个单元格内的多个值，并用逗号 (,) 分隔。它还会自动消除所有重复值并仅返回值的第一个实例。句法=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(return_array, IF(lookup_value=lookup_array,return_array, ""), 0),"")=MATCH(ROW(return_array),ROW(return_array)),return_array, ""))公式=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(C5:C12, IF(A2=A5:A12,C5:C12, ""), 0),"")=MATCH(ROW(C5:C12),ROW(C5:C12)),C5:C12, ""))解决方案 #2：使用 VBA 查找并返回多个值如果您不想使用复杂的公式通过垂直查找返回多个值，可以考虑以下VBA代码。这段代码创建了一个名为 LookupMultipleValues 的用户定义函数。该函数只需要3个参数，使用起来很方便。Function LookupMultipleValues(gTarget As String, gSearchRange As Range, gColumnNumber As Integer) Dim g As Long Dim k As String For g = 1 To gSearchRange.Columns(1).Cells.Count If gSearchRange.Cells(g, 1) = gTarget Then For J = 1 To g - 1 If gSearchRange.Cells(J, 1) = gTarget Then If gSearchRange.Cells(J, gColumnNumber) = gSearchRange.Cells(g, gColumnNumber) Then GoTo Skip End If End If Next J k = k & " " & gSearchRange.Cells(g, gColumnNumber) & "," Skip: End If Next g LookupMultipleValues = Left(k, Len(k) - 1) End FunctionVisual Basic复制使用指南步骤_1：按 ALT + F11 打开 Visual Basic 编辑器。步骤_2：转到“插入”>“模块”。Step_3：粘贴上面的代码。步骤_4：再次返回工作表并使用以下公式。句法=LookupMultipleValues(lookup_value,lookup_array,col_index_num)公式=LookupMultipleValues(A2,A5:A12,3)第七个限制：VLOOKUP 函数在添加或删除列方面不灵活当您在数据区域中间插入或删除列时，可能会导致 VLOOKUP 公式出现问题。因为您的 VLOOKUP 引用的列索引号可能不再准确。通常，VLOOKUP 函数的第三个参数（即列索引号）是硬编码的。这就是为什么在表数组中添加或删除列可能会导致结果不准确的原因。解决 VLOOKUP 关于添加或删除列的灵活性问题为了解决这个问题，可以使用COLUMN函数来计算列索引号，而不是对其进行硬编码。对于以下场景，这就是使用和不使用 COLUMN 函数时公式的外观。静态列索引号=VLOOKUP(A2,A5:D12,3,FALSE)动态列索引号=VLOOKUP(A2,A5:D12,COLUMN(),FALSE)要了解更多信息，您可以阅读 Excel 中 VLOOKUP 计算列索引号的 2 种方法。第 8 个限制：默认情况下，VLOOKUP 返回近似匹配结果让我们记住 VLOOKUP 函数的语法。句法=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])您可以看到 VLOOKUP 函数的第四个参数是可选的。但 range_lookup 参数的默认设置是 TRUE – 近似匹配，这是一个问题。因为，如果不指定 range_lookup 参数，它将执行近似匹配。因此，您可能不会得到您想要的结果。如果您的数据未排序，并且忽略 range_lookup 参数或将其设置为 TRUE-近似匹配，则 VLOOKUP 函数将不会返回准确的结果。因此，如果您正在查找lookup_value和table_array之间的精确匹配，请确保将range_lookup参数指定为FALSE – 通过输入FALSE或0来精确匹配。Excel中各种查找函数的比较功能 优点 缺点 索引/匹配可以执行从左到右的查找。 更长的公式语法。 X查找支持近似匹配和精确匹配。需要较新版本的 Excel。 查询可以执行水平查找。仅限一排。 错误/查找可以处理错误并返回替代结果。仅限于垂直查找。 选择/匹配可以基于数字索引执行查找。仅限于垂直查找，不如 INDEX/MATCH 公式灵活。结论虽然 VLOOKUP 是 Excel 中用于数据分析的强大工具，但它也有其局限性。通过使用我们在本文中概述的最佳解决方案，您可以克服这些限制并将数据分析提升到新的水平。无论您选择使用 INDEX-MATCH、IFERROR、通配符、串联还是辅助列，都有多种方法可以充分利用 Excel 电子表格并获得您需要的结果。通过一些练习和实验，您将能够掌握这些技术并立即成为一名 Excel 专家。返回搜狐，查看更多
责任编辑：}