一题多解,不用我说,你们看完之后就知道那种方法最好,Vookup最累,加权这个方法自作多情。SUMIF最爽,sumproduct棘手,题目要求:根据姓名查找数量,姓名和数量出现在多行多列区域中。更多Excel学习和问题请加群:289393114、570064677
如下图
一、解法1:Vlookup实现
1、公式截图
2、公式
=IFERROR(VLOOKUP(B8,A1:B4,2,0),"")&IFERROR(VLOOKUP(B8,D1:E4,2,0),"")&IFERROR(VLOOKUP(B8,G1:H4,2,0),"")&IFERROR(VLOOKUP(B8,J1:K4,2,0),"")
3、公式解释
4个区域,用4个vlookup连接
如果找不到就会报错,报错就显示空
这种方法的缺点就是区域多,公式越长,如果数据有50个区域,就要用50个vlookup,当然Vlookup累的趴下了,写公式的人也够呛了。
二、解法2:Sumprodict Text实现
1、公式截图
2、公式
=SUMPRODUCT((A2:J4=B8)*(TEXT(B2:K4,"0;-0;0;!0")))
3、公式解释
判断区域是否有等于“小老鼠”的,这个返回的是一个二维数组,由true和false组成的,要注意的是选择区域时要少先一列,最后一列不要选
把判断的结果和区域B2:K4相乘,注意这个也要是少选了一列,第1列不选,这样就前面的区域判断就吻合了,但是由于这个区域有姓名汉字,所以要用text处理一下,把汉字强制显示0用这个公式(TEXT(B2:K4,"0;-0;0;!0"))
最后用sumproduct求和,就不用三键了,如果用sum就要三键一齐下Ctrl Shift 回车
三、解法3:加权实现
1、公式截图
2、公式
=INDIRECT(TEXT(MIN(IF(A1:K4=B8,ROW(A1:A4)*10^4 COLUMN(A1:K1) 1)),"R0C0000"),)
3、公式解释
IF(A1:K4=B8,ROW(A1:A4)*10^4 COLUMN(A1:K1) 1)判断区域如果有等于"小老鼠",那么就行号上加权,乘以10的4次方,也就是10000再加上列号,还要加上,因为结果是在姓名的后一列
text(加权后的值,“R0C0000")目的让其显示R1C1引用样式,方便indirect函数引用
四、解法4:sumif函数轻松实现,一个字“爽”
1、公式截图
2、公式
=SUMIF(A1:J4,B8,B1:K4)
3、公式解释
这是sumif函数经典的错位用法
如果数据源没有重复的姓名,sumif可以当vlooku函数用,因为没有重复的,求和的结果就是vlookup引用返回的结果。
大家注意第一参少选最后一列;第3参数少选第1列