如下顧客消費樣表:
能不能隨意查詢每個顧客的消費記錄,而且隨著消費記錄的增多,查詢結果也可自動更新?
結果如下:
公式實現
在G1單元格輸入公式:
=INDEX(B:B,SMALL(IF($B:$B=$G$1,ROW(B:B),ROWS(B:B)),ROW(A1)))&""
<ctrl>三建結束,即得G1單元格指定消費者第一次消費記錄中的姓名,公式向下向右填充,可得該消費者的所有消費記錄。/<ctrl>
給消費記錄添加序號:
=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))&""
如果查詢姓名和查詢記錄姓名中有一個為空,也不填充序號。
閱讀更多 韓老師講office 的文章