最近推送的五篇文章:
原以为是个假新闻,后进到微软官网,看到了XLOOKUP的帮助,证实的确是推出了XLOOKUP函数(目前限于预览体验版的部分用户)。VLOOKUP函数,它帮我们节约了大把时间。曾经,我们需要手工查找、筛选,找到要的数据后,再Ctrl+C、Ctrl+V,粘贴到目标表格中。而有了VLOOKUP,几秒钟就可将目标数据“抄”过来。正是VLOOKUP函数,它和SUMIF函数一道,让我们领略了函数的强大,是它带领表哥表妹们跨入Excel函数的殿堂,并为之迷醉。将VLOOKUP比做表哥表妹们Excel函数的领路人,它是当之无愧的。而今,微软推出了功能更强大的XLOOKUP,我们在为Excel添加实用的函数而高兴的同时,也对VLOOKUP函数感到不舍,也替它感到难过。尽管,对VLOOKUP函数,龙逸凡是抱有深厚的感情,对它是恋恋不舍的,但在使用OFFICE软件方面,龙逸凡喜新不厌旧,对新功能、新函数从来都是欢迎的。毕竟,社会从来都是悲欢离合中推陈出新,进而螺旋式上升。毕竟VLOOKUP函数的确存在短板,也许是时候让新人替补上场了:由于VLOOKUP是在第二参数单元格区域的首列中查找,所以它无法逆向查找。比如下图,要在F列根据E列数据查找返回A列的职员代码,就要联用IF函数(或者INDEX+MATCH),编制图片中的公式进行逆向查找,=VLOOKUP(E4,IF({1,0},$B$4:$B$10,$A$4:$A$10),2,0)所以,如果有多条满足条件的数据,它只能返回第一条,而无法返回最后一条,只能用LOOKUP查找=LOOKUP(1,0/(C9:C23=B3),B9:B23)=VLOOKUP(A2,B3:$N$12,5,0)=VLOOKUP(A2,B3:$O$12,6,0)=VLOOKUP(A2,B3:$O$12,MATCH("4月",B3:O3,0),0)4、VLOOKUP近似匹配时只能返回小于等于查找值的最大值,而不会返回大大等于查找值的最小值。以上都是VLOOKUP的缺陷,而XLOOKUP有针对性的进行了改进我们从下图XLOOKUP的参数可以看出,XLOOKUP第四参数为匹配模式,可以指定等于、小于、大于指定值的项目。第四参数则是指定查找的方向,从前往后找,还是从后往前找。
示例 1
下面的示例使用一个简单的 XLOOKUP 查找国家/地区名称, 并返回其电话国家/地区代码。它仅包括 lookup_value (单元格 F2)、lookup_array(range B2: B11) 和 return_array (range D2: D11) 参数。它不包含 match_mode 参数, 因为它默认为精确匹配。
注意: XLOOKUP 与 VLOOKUP 的不同之处是它使用单独的查找和返回数组, 其中 VLOOKUP 使用一个表数组, 后跟一个列索引号。在此情况下, 等效的 VLOOKUP 公式为: = VLOOKUP (F2,B2: D11, 3, FALSE)
示例 2
以下示例在列 C 中查找在单元格 E2 中输入的个人收入, 并在列 B 中查找匹配的税率费率。它使用 match_mode 参数设置为 1, 这意味着该函数将查找精确匹配, 如果找不到它, 它将返回下一个较大的项。
注意: 与 VLOOKUP 不同, lookup_array 列位于 return_array 列的右侧, 而 VLOOKUP 只能从左到右查看。
示例 3
接下来, 我们将使用嵌套的 XLOOKUP 函数同时执行垂直和水平匹配。在这种情况下, 它将首先在列 B 中查找毛利润, 然后在表的首行中查找 "第 1季度" (区域 C5: F5), 并返回二者相交处的值。这类似于结合使用INDEX和MATCH函数。
单元格 D3 中的公式: F3 为: = XLOOKUP (D2,$B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))。
示例 4
此示例使用SUM 函数和两个 XLOOKUP 函数嵌套在一起, 对两个区域之间的所有值求和。在这种情况下, 我们希望对葡萄、香蕉和梨的值进行求和, 这些值位于两个值之间。
单元格 E3 中的公式为: = SUM (XLOOKUP (C3, C6: C10, F6: F10): XLOOKUP (D3、C6: C10、F6: F10))
美中不足的是,XLOOKUP没有返回指定第几条的记录参数,如果有的话就可以用它来查找指定条件的所有记录,而不必再用数组公式。