编按：
说到数据的多条件查询，我们更多地想到的是Vlookup函数的应用。可是，在名企任职的大神们不会固守这一观点，他们会用更简单的思路，选择当下更实用的查询公式，去轻松实现数据的自动提醒和动态查询！例如，在货品库存中心，查询待补货的店铺和补货数量时，大神们都会这样做……

正文：

小伙伴们，大家好！今天继续向大家介绍一个多维引用的实例，希望大家可以尽快掌握这个技巧。

下面是国际著名公司麦必德某大区的门店配送中心的库存报表。

该图显示了每天门店的库存情况（空白则表示该门店没有此款产品）。物流人员会根据每天各门店的库存来安排实物配送。安排配送的条件如下：

1.   以“包装数量”为参照标准，将“配送中心”库存的商品自动分配到现库存数小于“包装数量”标准的门店。

2.   库存小于等于1/2个“包装数量”的标准时，配送2个的标准“包装数量”的量；库存大于1/2个“包装数量”的标准，同时又小于1个“包装数量”的标准时，配送1个标准“包装数量”的量。

3.   颜色标记需要配货的门店。

第一步：标记颜色

这个非常简单，用条件格式就可以完成。在条件格式中输入公式“=(E7<$C7:$C10)*(E7<>"")”，这里不再赘述了，最后效果如下。

第二步：提取门店清单

首先使用“数据验证”功能在单元格A13中创建商品代码的下拉清单。

然后在单元格B13中输入公式“=IFERROR(INDEX($E$6:$N$6,SMALL(IF(($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10)*($E$7:$N$10<>""),COLUMN($E$7:$N$10)-4),ROW(A1))),"")”，按三键“Ctrl+shift+回车”并向下拖曳。

本质上讲，这也是一个一对多的查询应用。

函数解析：

1.   IF(($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10)*($E$7:$N$10<>""),COLUMN($E$7:$N$10)-4)部分，对于同时满足条件的单元格（对应的商品代码、小于包装数量和非空值）则返回它们所对应的列号。

2.   利用SMALL函数依次提取上面的列号。

3.   利用INDEX函数返回对应的门店名称。

第三步：计算配送数量

在单元格C13中输入公式“=IFERROR(IF(N(INDIRECT(TEXT(RIGHT(SMALL(IF(($E$7:$N$10<>"")*($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10),ROW($E$7:$N$10)/1%+COLUMN(E:N)*10001),ROW(A1)),4),"r0c00"),))>INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,))/2,INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,)),INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,))*2),"")”，按三键“Ctrl+shift+回车”并向下拖曳即可。

计算时，大家也可以使用更简单的INDEX+MATCH组合来完成。这里向小伙伴们介绍的多维引用的思路和技巧，虽然公式看起来比较长，但其中的逻辑思路是比较简单的，掌握以后将来能更容易地处理各种不同的问题。

函数解析：

1.   IF(($E$7:$N$10<>"")*($A$7:$A$10=$A$13)*($E$7:$N$10<$C$7:$C$10),ROW($E$7:$N$10)/1%+COLUMN(E:N)*10001)部分，对于那些满足条件的单元格（对应的商品代码、小于包装数量和非空值），把它们对应的行号扩大100倍，列号扩大10001倍，并将这两部分相加。其结果为{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;51005,FALSE,FALSE,81008,FALSE,FALSE,111011,FALSE,FALSE,FALSE}。

2.   SMALL(IF())部分利用SMALL函数特性可依次从小到大提取上面的数值。

3.   利用RIGHT函数提取4位字符。请注意，这里是关键的一步。上面的步骤中已经将对应数据的行号扩大了100倍，列号扩大了10001倍。所以，在两个数据相加后，从右侧开始第1位和第2位是列信息，第3位和第4位是行信息。用RIGHT函数即可提取到行列号的信息，其结果为{"1005"}，表示第10行第5列。

4.   用TEXT函数将其转换为R1C1的格式，返回值为{"r10c05"}。

5.   用INDIRECT函数提取目标值，即为{75}。

6.   接下来还要做一个判断，即根据补货规则做一个判断。用三个INDEX（）函数来判断当前物料的数量，其满足大于1/2包装数量且小于1个包装数量的条件时，补货一个整包数量；不满足条件则补货2个整包数量。

7.   下面另外一个重点内容：在运用的多维方法中，INDIRECT函数的结果是不能直接和INDEX($C$7:$C$10,MATCH($A$13,$A$7:$A$10,))/2来做比较的。在比较前，大家还需要用N函数来降维处理后才能比较。

这样，所有的公式都已经输入完毕了。录入不同的商品代码后，一方面，EXCEL会用颜色标识出需要补货的门店信息，另一方面，它也列出了具体的清单。

今天的分享就是这些。

多维引用的公式看似复杂，其实是很简单的思路应用。学习过程中，可能不会一帆风顺，但是多多练习掌握以后，小伙伴们在未来即使面对不同的问题，也可以轻松应对。所以还是那句话，多多练习才是快速掌握公式的捷径哦~

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

IF函数的新用法，早会早下班！

Excel数字提取技巧:从无规律文本中提取手机号的5种方法

5种最实用的提取文件名称的妙招！

Excel数字提取技巧:用简单公式从混合文本中提取数字的3种情景

版权申明：

本文作者EXCEL应用之家；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。