使用excel万金油公式的注意事项

执剑走天涯xp

已于 2023-01-31 14:20:59 修改

阅读量1.1k

点赞数 2

分类专栏： linux 文章标签： linux

于 2021-05-08 11:57:57 首次发布

本文链接：https://blog.csdn.net/qq_41566366/article/details/116521618

版权

linux 专栏收录该内容

40 篇文章 2 订阅

订阅专栏

如何使用万金油请查看：Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读-部落窝教育

如图，我想实现如下效果：

选择类型的TestAction，将A3:D10范围中所有符合条件的数据展示在A18:D18下放

万金油公式说明

=IFERROR(
   INDEX( $A$3:$D$10,    
          SMALL(  IF($B$3:$B$10 = $A$13, ROW($1:$8),  999999999 ),  1  ),     
          MATCH(  A$18:$D$18,  $A$2:$D$2,0)  
   )&"", 
   ""
)

index公式：包括三个参数

所查询的范围Arr1 即上面公式的 $A$3:$D$10

Arr1 中的第 row（int型）行 即上面公式的SMALL(...)

Arr1中的column（int型）列 即上面公式的MATCH(...)

就是查询arr1 中第row行第column列

small公式：SMALL( IF($B$3$B$10 = $A$13, ROW($1:$8), 999999999 ), ROW(A1))。该函数含义是：在 b2:b9列中，查找出所有等于a12单元格的数据中倒数第一小的数据。 small第二个参数填1就行了，意思是small函数处理后的Arr1中取最小值，即我们查出来的值。
if公式： IF( $B$2:$B$9 = $A$12, ROW($1:$8) , 99999) 。该函数含义是：根据b2:b9列，整理成一个这样的数组，如果b2:b9中某个单元格=a12，那么就显示他的行数，否则显示一个尽可能大的数值。if的第二个参数，row($1:$8)，一定是从1开始，否则会出错，$1与￥8的之间的行数与Arr1的行数相同。 if的第三个参数99999999 必须>Arr1的行数。
match公式：match 公式的结果是 int （数字）类型，这里要匹配a19-d22列的结果，所以用match一个个查询。如果只查一列结果，该处填写 column(Arr1中对应结果列)即可。比如，如果只查询 D18 父任务主题下的结果， match直接替换成数字4即可，代表获取第四列结果。
注意输入完公式后不要按enter键，而应该按ctrl+alt+enter键，这样才能计算正确的值，否则SAMLL函数得出的永远都是后面那个最大值。比如上面的公式SMALL( IF($B$3:$B$10 = $A$13, ROW($1:$8), 999999999 ), 1 )，永远显示的都是999999999
index最后接了&""，如果不加，空单元格就会被转化为0，加了之后就会保持空白，但是注意，如果转换前的单元格中本来就是0，或者Fales之类非真的值，都会转换为空白，如果单元格中有这些值要慎用