INDEXSMALLIFROW一对多查询的函数组合
在 WPS表格或Excel中用函数公式做查询时,很多人都能够娴熟运用 INDEX、MATCH、 LOOKUP、VLOOKUP等函数进行一对一查询,但常常会遇到一个更难的问题,就是如何应用函数进行一对多查询?一对多查询可以利用函数组合的数组公式来实现。下面举例说明如何利用 INDEX+SMALL+IF+ROW函数组合实现"未填报人员统计"的一对多查询。
下图为问卷填报统计结果和未填报人员统计结果:
C22输入公式:
=IFERROR(INDEX($C$2:$C$16,SMALL(IF($D$2:$D$16="未填报",
ROW($D$2:$D$16),4^8),ROW(C1)))&"","")
按Ctrl+Shift+回车,向下拖拽公式(拖拽行数不超过填报人数即可),就能实现自动统计未填报人员。
其中,
IF函数:条件,为真则返回D列单元格的行号,为假则返回4^8即65536,就是4的8次方幂,即65536,在xls格式文档中相当于最大行号)。
SMALL函数:将IF函数返回的数组作为SMALL函数的第一参数,SMALL(IF($D$2:$D$16="未填报",
ROW($D$2:$D$16),4^8),ROW(C1)),取判断结果的第一小值,下拉就是取第二小值,第三小值,……。
INDEX函数:返回C2:A16区域某一行的值。
IFERROR函数:容错函数,如果INDEX函数取得值为错误值,则返回空值。
这样,通过 INDEX+SMALL+IF+ROW函数组合的一对多查询我们就可以实现未填报人员的自动统计。
图中,
D22输入公式:
=IF(C22<>"","未填报","")&T(NOW()) 可以显示填报状态为"未填报"
D21输入公式:
=COUNTIF(D2:D16,"未填报") 可以显示未填报人数