多条件查询【Excel分享】

大家好，今天 和大家分享“多条件查询”，提问朋友描述：因为要做全国的业务报销津贴，需根据不同的职等、不同的交通方式，对应不同的报销额度。这个多条件查找的公式怎么设啊？麻烦指点下。如下图，下面我讲5种解法，希望能让大家函数水平不断的提高。建议大家在实际工作中，尽量用简单易理解的公式

一、解法1：Vlookup+Match

1、公式截图

2、公式

=VLOOKUP(G2,$A$1:$D$8,MATCH(H2,$A$1:$D$1,),0)

3、公式解释

  • Vlookup函数第1参数查找值G2职称

  • Vlookup函数第2参数：数据源A1：D8

  • Vlookup函数第3参数：这里是重点，我们用match函数来定位，根据H2的交通工具，到A1:D1中找位置，找到对应的位置之后作vlookup第3参数

  • Vlookup函数第3参数用0，精确查找

二、解法2：Index+Match行+Match列

1、公式截图

2、公式

=INDEX($B$2:$D$8,MATCH(G2,$A$2:$A$8,0),MATCH(H2,$B$1:$D$1,0))

3、公式解释

  • Index我们这里用了它3个参数格式

  • Index函数3个参数：第1参数数据源，第2参数返回数据源那一行？第3参数返回数据源那一列

  • 第2参数，由match函数MATCH(G2,$A$2:$A$8,0)定位，根据G2,在A2：A8中找位置

  • 第3参数，同理，也是通过match函数根据H2不同的交通工具在B1：D1中找位置

三、解法3：Sum+数组法

1、公式截图

2、公式

=SUM(($A$2:$A$8=G2)*($B$1:$D$1=H2)*($B$2:$D$8))

3、公式解释

  • 数组公式，记得复制公式到编辑栏，还要把光标定位到编辑栏，然后三键一齐下Ctrl+Shift+回车

  • A$2:$A$8=G2判断一个纵向单元格区域，相当于一个纵向的一维数组

  • $B$1:$D$1=H2判断一个横向单元格区域，相当于一个横向的一维数组

  • 一个纵向的一维数组和和一个横向的一维数组相乘，构建和一个二维数组，这个二维数组的行以纵向一维数组一样多，构建的这个二维数组的列和横向的一维数组的列数一样多

  • 最后构建的这个二维数组和单元格区域B2:D8相乘，然后用sum求和得到我们想要的效果

四、解法4：Lookup+Index+Match

1、公式截图

2、公式

=LOOKUP(1,0/($A$2:$A$8=G2),INDEX($B$2:$D$8,,MATCH(H2,$B$1:$D$1,)))

3、公式解释

  • lookup这里用了3个参数格式

  • 第1参数：查找值1，大于第2参数0/($A$2:$A$8=G2)任意一个值，返回第2参数最后一个数值对应第3参数那个值

  • 有的朋友会问，为什么第2参数还要用0除，目的是让true转为0，false报错

  • 第3参数用了index，index函数第2参数为0，那么就返回第3参数整列的数据，当然是index第1参数据源的数据，index函数第3参数用了match函数来定位那一列

五、解法5：Sum+Mmult

1、公式截图

2、公式

=SUM(MMULT(N(TRANSPOSE($A$2:$A$8=G2)),($B$1:$D$1=H2)*($B$2:$D$8)))

3、公式解释

  • Mmult妹妹函数，要求第1参数的列数要和第2参数的行数要一样，否则报错

  • Mmult妹妹函数两个参数不能是布尔值True,False ，所以这里我们用了N函数来处理

  • Mmult妹妹函数返回的结果是以第1参数的行列乘以第2参数的列数

赞 (0)

相关推荐