|
这次我们谈一谈查找类公式的速度问题。 源数据如下图：
需要的结果数据如下图：
要从源数据中查找相应的Value，并添加到下表中，显然条件有多列组成：Year，Period，Sales Person，...， 为了使用合适的公式，我们在两边都添加了辅助列，将条件各列连接成为一个字符串。 注：源数据量比较大，在这个演示案例中，总共有40多万行数据。而结果数据有100行。 下面的15个查找公式都完成这个任务： (1)=VLOOKUP(H6,数据!$G$2:$H$42942,2,0)(2)=VLOOKUP(B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6&"-"&G6,数据!$G$2:$H$42942,2,0)(3)=INDEX(数据!$H$2:$H$42942,MATCH(Index!H6,数据!$G$2:$G$42942,0))(4)=XLOOKUP(H6,数据!$G$2:$G$42942,数据!$H$2:$H$42942)(5)=VLOOKUP(H6,数据!$G$2:$H$42942,2,1)(6)=IF(VLOOKUP(H6,数据!$G$2:$H$42942,1,1)=H6,VLOOKUP(H6,数据!$G$2:$H$42942,2,1),"")(7)=INDEX(数据!$H$2:$H$42942,MATCH(Index!H6,数据!$G$2:$G$42942,1))(8)=XLOOKUP(H6,数据!$G$2:$G$42942,数据!$H$2:$H$42942,,,2)(9)=LOOKUP(H6,数据!$G$2:$G$42942,数据!$H$2:$H$42942)(10)=FILTER(数据!$H$2:$H$42942,数据!$G$2:$G$42942=Index!H6)(11)=VLOOKUP(H6,SORT(数据!$G$2:$H$42942,1),2,0)(12)=LET( sorted_data,SORT(数据!G2:H429424,1), VLOOKUP(H6:H105,sorted_data,2,1))(13)=LET( sorted_data, SORT(数据!G2:H429424,1), MAP(Index!H6:H105, LAMBDA(a, VLOOKUP(a, sorted_data, 2, -1))))(14)=LET( sorted_data,SORT(数据!G2:H429424,1), data_col1, CHOOSECOLS(sorted_data,1), data_col2, CHOOSECOLS(sorted_data,2), IF( XLOOKUP(H6:H105,data_col1, data_col1,,,2)=H6:H105, XLOOKUP(H6:H105,data_col1, data_col2,,,2), "" )(15)=SUMIFS(数据!$H$2:$H$42942,数据!$G$2:$G$42942,Index!H6) 第一个VLOOKUP精确匹配公式是最常用的： =VLOOKUP(H6,数据!$G$2:$H$42942,2,0) 其计算需要0.40秒的时间（以我的计算机为准）。 鉴于这个公式是最常用的，我们可以将这个时间作为基准。 公式（2）是将公式（1）中的辅助列（H6）去掉，用字符串连接公式代替： =VLOOKUP(B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6&"-"&G6,数据!$G$2:$H$42942,2,0) 按说，多引用了单元格，并且增加了计算，但是这个公式的计算时间与公式（1）几乎一样。也就是说引入的字符串连接操作基本对数据无影响。 公式（3）使用INDEX+MATCH完成匹配，在很多人的心目中，这是效率较高的方式，其实，其真实速度跟公式（1）相仿。 公式（4）使用了XLOOKUP，在传说中，XLOOKUP的速度也比VLOOKUP快，但是实际的分析显示，这个公式需要0.57秒的时间，比公式（1）慢多了。 对于查找类公式来说，常规的精确匹配需要逐行遍历查找区域，因此该区域行数越多，速度越慢。但是如果采用近似匹配，由于近似匹配采用二分法，需要比较的行数得到了极大地减少，速度提升飞快。 所以公式（5） =VLOOKUP(H6,数据!$G$2:$H$42942,2,1) 的计算时间只有0.0009秒。相差几百倍了。 由于采用近似匹配后，会有错误匹配的情况，所以我们一般采用下面的公式（6）进行近似匹配： =IF(VLOOKUP(H6,数据!$G$2:$H$42942,1,1)=H6,VLOOKUP(H6,数据!$G$2:$H$42942,2,1),"") 即使这里用到了两个VLOOKUP函数，计算时间也只有0.0015秒。 公式（7）（8）（9）都是采用了近似匹配的方式（其中XLOOKUP比较特殊，精确匹配也可以采用二分法进行搜索），其计算时间跟公式（6）也差不多。 公式（10）使用了FILTER函数， =FILTER(数据!$H$2:$H$42942,数据!$G$2:$G$42942=Index!H6) 这个公式的速度很差，计算时间需要1.2秒。 从上面可以看出，查找公式最好还是VLOOKUP，INDEX+MATCH，当然需要使用近似匹配，或者使用XLOOKUP的二分法。 但是这样做是不公平的！ 因为二分法或者近似匹配都要求源数据区域排序。 在源数据区域固定的场景中，可以事先对源数据进行排序。但是源数据不固定时，这么做就不太现实。我们需要在进行查找前先排序。 这个动作可以通过SORT函数来完成。 因此，在这些场景中，我们应该将SORT函数的时间考虑在内。 比如，公式（11）， =VLOOKUP(H6,SORT(数据!$G$2:$H$42942,1),2,0) 但是，这个公式需要的时间非常夸张！6.24秒。 当然可以改进，比如公式（12）， =LET( sorted_data,SORT(数据!G2:H429424,1), VLOOKUP(H6:H105,sorted_data,2,1)) 这个公式将源数据的排序单独拿出来，只进行一次排序即可，其时间比前一个公式减少了很多，只要0.42秒的时间。 但是，跟公式（1）相比，如果考虑排序的话，其实近似匹配并没有节省多少时间。 我们再尝试其他方法。比如，使用MAP函数（公式13）， =LET( sorted_data, SORT(数据!G2:H429424,1), MAP(Index!H6:H105, LAMBDA(a, VLOOKUP(a, sorted_data, 2, -1)))) 看上去，这个公式跟公式（12）类似，但是却需要2.9秒的时间。 再尝试以下XLOOKUP（公式（14））， =LET( sorted_data,SORT(数据!G2:H429424,1), data_col1, CHOOSECOLS(sorted_data,1), data_col2, CHOOSECOLS(sorted_data,2), IF( XLOOKUP(H6:H105,data_col1, data_col1,,,2)=H6:H105, XLOOKUP(H6:H105,data_col1, data_col2,,,2), "" ) 这个公式只需要0.25秒的时间。跟最初的公式（1）相比，有明显提升。 这是我们在考虑排序的因素后，能找到的最佳方案。 最后看一个另类的查找公式： =SUMIFS(数据!$H$2:$H$42942,数据!$G$2:$G$42942,Index!H6) 这里用到了SUMIFS函数。当条件可以在源数据中限定唯一行时，就可以使用SUMIFS进行多条件查找。 但是这个公式的计算速度较慢，需要0.9秒的时间。 这也是SUMIFS等条件聚合公式的大致速度。 我们留一个问题供大家思考：对于SUMIFS这类公式，应该如何进行优化。 详细解释请看视频  |
|
|
