|
|
本帖最后由 ydat0595 于 2021-9-26 17:25 编辑
多条件查询用Vlookup需要辅助列，把编号用&连接起来，具体怎么操作我就不说了。
可以尝试使用sumproduct函数。具体为=INDEX(F:F,SUMPRODUCT(($AC4=$B$4:$B$26)*($AD4=$C$4:$C$26)*($AE4=$D$4:$D$26)*ROW($B$4:$B$26)),1)
我来说一下公式的逻辑：
一、(AC4=B4:B26) 返回的一串0,1型逻辑数组，代表着TRUE与FALSE。比如说B4,B5,B7与AC4不相等，B6,B8与AC4相等，那么返回的是结果就是0,0,1,0,1。
二、sumproduct函数表示的多组据相乘后的累加的和。举例sumproduct（{1,2,3}，{4,5,6}）表示的就是1*4+2*5+3*6的结果。通过嵌套函数，SUMPRODUCT((AC4=B4:B26)*(AD4=C4:C26)*(AE4=D4:D26)）就能返回一串（0,0,1,0,0）的数据。这个也就是我们的条件判定，如果多条件查询的返回数据是一串数字型数据，那么直接sumproduct后用逗号或者乘号直接相乘都行。比如查询条件为姓名、月份、编号，返回结果为销售额，可以直接返回的结果为（0,0,,销售额,0,0）返回的数据为销售额。如果说通过查询条件，返回的结果是字符串数据，比如说销售产品，那么返回的结果为（0,0,销售产品,0），数据返回为0或者报错。
三、这里我们引入了ROW函数，表示的单元格的行号。
ROW(A1),ROW(B5),ROW(DX35)返回的分别为1,5,35。如果是ROW(A5:A8)表示的{5,6,7,8}这串数据，在单元格会显示第一个数据，也就是5。
这里在科普一下ROWS函数，ROWS函数表示的所选区域的总行数。举例：ROW(A1:A3)返回总共3，表示总共3行，ROWS(A2:D10)返回的是9，代表9行。
通过sumproduct函数，再把ROW函数引入与查询条件相乘，我们就可以得到与条件相符的行号计算的的{000110}与{4,5,6,7……}相乘后累加，结果为{0,0,,行号,0,0}，单元格显示行号。我给出的公式是ROW(B4:B26)，你可以换成A4:A26,也可以用A4:G35，这个没关系。
四、index函数表示的指定单元格区域内的行号与列号交叉的单元的值。举例INDEX(B2:D4,2,3)表示的是在B2:D4这个区域内，第2行第3列的单元的值，也就是D3单元的值。
通过前面公式的计算结果，我们已经有了行号，现在需要的是查询数据的区域和列号就行了。区域我们就用F:F列这列数据，他的行号也只有1行。
当然，我们也选定多列的数据，比如F:O，或者整张表1:1048576。列号的话，如果取F:O区域的话，那就是1-10，可以使用column函数，这个函数跟row函数是一样的，就不解释了。用一个绝对引用column(单元格)-column（$单元格$）+1就可以了，如果选择的区域是整张表，那就把+1改成+6就行了。
五、为了方便下拉右拉，我们需要进行绝对引用，比如判定的区域($AC4=$B$4:$B$26)把AC列号固定，源数据绝对引用，当然了，你也可以改成($AC4=$B:$B)，这样可能会更方便操作，直接引用一整列。
六、查询数据与源表不符。sumproduct函数进行多条件查询还是有缺陷，要求你查询的条件只能有1个匹配，如果多个匹配，那么就会出现查询数据不符。举例：查询表中第13行与21行的查询条件相同，都是035编号的话，那么结果就会返回13+21=34行的数据。
|
|
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2021-9-26 14:43
发表于 2021-9-26 16:20
楼主