TOROW、TOCOL行列转换函数,查找能力居然比VLOOKUP更厉害!
一、用法回顾
TOROW(数组,忽略特殊值,通过行或列扫描),可将数组转为一行;
TOCOL(数组,忽略特殊值,通过行或列扫描),可将数组转为一列。
用法举例:
如下图所示,将C5:E7数据区域转为一行或一列。
公式一:
=TOROW(C5:E7,,1)
解析:
表示将C5:E7按列扫描的方式,转为一行。
公式二:
=TOCOL(C5:E7,,0)
表示将C5:E7按行扫描的方式,转为一列。
二、一对多查找
应用场景:
如下图所示,根据指定名称查找对应的数量。公式一:=TOROW(IF(A2:A10=D3,B2:B10,X),2)解析:如果A2:A10的值等于D3,返回对应的数量,否则返回未定义的“X”生成错误值,TOROW将IF计算得到的数组,忽略错误值转为一行,从而查找出D3对应的数量。公式二:=TOCOL(IF(A2:A10=D6,B2:B10,X),2)如果A2:A10的值等于D6,返回对应的数量,否则返回未定义的“X”生成错误值,TOCOL将IF计算得到的数组,忽略错误值转为一列,从而查找出D6对应的数量。三、多条件查找
应用场景:如下图所示,根据左侧表格数据,查找出A组TOO6对应的数量。公式:=LET(a,SCAN(,A2:A13,LAMBDA(X,Y,IF(Y<>"",Y,X))),TOROW(IF(a&B2:B13=E5&F5,C2:C13,0/0),2))解析:LET(a,SCAN(,A2:A13,LAMBDA(X,Y,IF(Y<>"",Y,X))),将A列转化为图右侧数据区域,定义为a。如果a&B2:B13满足等于“AT006”的条件,则返回对应的数量,否则返回0/0得到错误值,TOROW去除错误值从而查找出结果。点评:虽然VLOOKUP也能通过组合实现一对多查找,或者用FILTER查找更简单,但是TOROW天生还可以将结果指定转为一行或一列。