Excel怎么用函数公式筛选特定项对应的数据？

发布网友 发布时间：2022-02-21 07:29

我来回答

共5个回答

热心网友 时间：2022-02-21 08:59

在sheet2表的G6单元格中输入公式=TEXT(IFERROR(IF($C$2="中山广电网络电视费",INDEX(Sheet1!$A$1:$A$10000,SMALL(IF(Sheet1!$B$1:$B$10000<>"",ROW(Sheet1!$B$1:$B$10000),4^8),ROW(A2))),IF($C$2="房屋货款",INDEX(Sheet1!$A$1:$A$10000,SMALL(IF(Sheet1!$C$1:$C$10000<>"",ROW(Sheet1!$C$1:$C$10000),4^8),ROW(A2))),IF($C$2="电话费",INDEX(Sheet1!$A$1:$A$10000,SMALL(IF(Sheet1!$D$1:$D$10000<>"",ROW(Sheet1!$D$1:$D$10000),4^8),ROW(A2))),IF($C$2="代扣电费",INDEX(Sheet1!$A$1:$A$10000,SMALL(IF(Sheet1!$E$1:$E$10000<>"",ROW(Sheet1!$E$1:$E$10000),4^8),ROW(A2))),IF($C$2="城乡医疗征收",INDEX(Sheet1!$A$1:$A$10000,SMALL(IF(Sheet1!$F$1:$F$10000<>"",ROW(Sheet1!$F$1:$F$10000),4^8),ROW(A2))),IF($C$2="手机话费",INDEX(Sheet1!$A$1:$A$10000,SMALL(IF(Sheet1!$G$1:$G$10000<>"",ROW(Sheet1!$G$1:$G$10000),4^8),ROW(A2))),)))))),""),"YYYY年M月d日")

在sheet2表的H6单元格中输入公式=IFERROR(IF($C$2="中山广电网络电视费",INDEX(Sheet1!$B$1:$B$10000,SMALL(IF(Sheet1!$B$1:$B$10000<>"",ROW(Sheet1!$B$1:$B$10000),4^8),ROW(A2))),IF($C$2="房屋货款",INDEX(Sheet1!$C$1:$C$10000,SMALL(IF(Sheet1!$C$1:$C$10000<>"",ROW(Sheet1!$C$1:$C$10000),4^8),ROW(A2))),IF($C$2="电话费",INDEX(Sheet1!$D$1:$D$10000,SMALL(IF(Sheet1!$D$1:$D$10000<>"",ROW(Sheet1!$D$1:$D$10000),4^8),ROW(A2))),IF($C$2="代扣电费",INDEX(Sheet1!$E$1:$E$10000,SMALL(IF(Sheet1!$E$1:$E$10000<>"",ROW(Sheet1!$E$1:$E$10000),4^8),ROW(A2))),IF($C$2="城乡医疗征收",INDEX(Sheet1!$F$1:$F$10000,SMALL(IF(Sheet1!$F$1:$F$10000<>"",ROW(Sheet1!$F$1:$F$10000),4^8),ROW(A2))),IF($C$2="手机话费",INDEX(Sheet1!$G$1:$G$10000,SMALL(IF(Sheet1!$G$1:$G$10000<>"",ROW(Sheet1!$G$1:$G$10000),4^8),ROW(A2))),)))))),"")

两个公式均为数组公式，按Ctrl+Shift+Enter结束公式，使公式前后出现大括号{}，拖动填充柄下拉到任意。 如图：

公式有点长！

热心网友 时间：2022-02-21 10:17

热心网友 时间：2022-02-21 11:51

热心网友 时间：2022-02-21 13:43

热心网友 时间：2022-02-21 15:51