問題情境
如下顧客消費樣表:
能不能隨意查詢每個顧客的消費記錄,而且隨著消費記錄的增多,查詢結果也可自動更新?
結果如下:
公式實現
在G4單元格輸入公式:
=INDEX(B:B,SMALL(IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)),ROW(A1)))&''
給消費記錄添加序號:
=IF(OR($G$1='',G4=''),'',COUNTIF($G$4:G4,$G$1))&''
確定,即得序號1,公式向下填充,可得所有消費記錄的序號。
公式解析
1、=INDEX(B:B,SMALL(IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)),ROW(A1)))&'':
IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)
如果B列姓名與G1相等,返回對應行,如果不等,返回B列總行數,得一個數組。
SMALL(IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)),ROW(A1)
在剛才的數值中,取最小的值,即得一個“李四”顧客的行。
INDEX(B:B,SMALL(IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)),ROW(A1)))
查詢第一條消費記錄中的姓名。
公式向下向右填充,即得李四的所有記錄。
2、=IF(OR($G$1='',G4=''),'',COUNTIF($G$4:G4,$G$1))&''
如果查詢姓名和查詢記錄姓名中有一個為空,也不填充序號。
IF() 判斷進行篩選 ,ROWS(B:B) 只是為了返回一個比較大的數。為了更好理解,可以找一個很大的具體數。如9999代替。SMALL()進行數據的排序,一對多的查找
閱讀更多 薄荷草1977 的文章