今天一位朋友傳來(lái)數(shù)據(jù)表，要求：
根據(jù)“購(gòu)買記錄表”中的記錄，如下：
在“購(gòu)買查詢”表中實(shí)現(xiàn)查詢指定顧客的購(gòu)買記錄，效果如下：
本問(wèn)題，有兩個(gè)關(guān)鍵點(diǎn)：
1、購(gòu)買記錄必須是依據(jù)指定的姓名展示的；
2、購(gòu)買記錄的序號(hào)必須是1、2、3……連續(xù)的，且根據(jù)查找出的記錄數(shù)量改變。
公式實(shí)現(xiàn)
實(shí)現(xiàn)按指定顧客查詢
在B4單元格輸入公式：
=INDEX(購(gòu)買記錄表!B:B,SMALL(IF(購(gòu)買記錄表!$B$2:$B$12=購(gòu)買查詢!$B$1,ROW(購(gòu)買記錄表!$B$2:$B$12),ROWS(購(gòu)買記錄表!B:B)),ROW(A1)))&""，，以三鍵組合結(jié)束。
公式向下和向右填充，即得B1單元格指定的顧客的購(gòu)買記錄。
我們以查找“李四”的購(gòu)買記錄為例來(lái)分析：
第一步：
IF(購(gòu)買記錄表!$B$2:$B$12=購(gòu)買查詢!$B$1,ROW(購(gòu)買記錄表!$B$2:$B$12),ROWS(購(gòu)買記錄表!B:B))
用IF函數(shù)，建立一新的數(shù)組，這一新的數(shù)組建立的規(guī)則是：
如果購(gòu)買記錄表!$B$2:$B$12區(qū)域中的單元格內(nèi)容等于購(gòu)買查詢!$B$1單元格內(nèi)容，則返回該單元格所在的行，否則返回整個(gè)工作表的行數(shù)。
所以：此部分返回的數(shù)組是：
{1048576;1048576;4;1048576;6;1048576;1048576;1048576;1048576;11;1048576;1048576}
可以看到：凡是購(gòu)買記錄表B列單元格內(nèi)容等于李四的，返回的都是對(duì)應(yīng)的行數(shù)，不等于李四的，返回的都是整個(gè)工作表的行數(shù)1048576。
第二步：
SMALL(IF(購(gòu)買記錄表!$B$2:$B$12=購(gòu)買查詢!$B$1,ROW(購(gòu)買記錄表!$B$2:$B$12),ROWS(購(gòu)買記錄表!B:B)),ROW(A1))
在第一步形成的數(shù)組中，查找第第一小的數(shù)值。
用ROW(A1)做SMALL函數(shù)的第二個(gè)參數(shù)，即第幾小。
ROW(A1)是一個(gè)動(dòng)態(tài)的數(shù)值，公式往下填充一行，行數(shù)加1，即當(dāng)公式在B4單元格時(shí)，是ROW(A1)，當(dāng)公式填充到B5單元格是，是ROW(A2)，當(dāng)?shù)紹6單元格時(shí)，是ROW(A3)……
這樣，就在第一步的數(shù)組中找到了第1、2、3小的值，即4、6、11。
第三步：
INDEX(購(gòu)買記錄表!B:B,SMALL(IF(購(gòu)買記錄表!$B$2:$B$12=購(gòu)買查詢!$B$1,ROW(購(gòu)買記錄表!$B$2:$B$12),ROWS(購(gòu)買記錄表!B:B)),ROW(A1)))
當(dāng)公式在B4單元格時(shí)，返回購(gòu)買記錄表B列第4行的值，即顧客姓名李四。因?yàn)楣街蠭F部分是數(shù)組計(jì)算，所以公式以三鍵組合結(jié)束。
公式向下填充，得到B列購(gòu)買記錄表B列6、11行的值。
公式向右填充，自動(dòng)變?yōu)椴檎屹?gòu)買記錄表C列、Ｄ列４、6、11行的值。
第四步：
INDEX(購(gòu)買記錄表!B:B,SMALL(IF(購(gòu)買記錄表!$B$2:$B$12=購(gòu)買查詢!$B$1,ROW(購(gòu)買記錄表!$B$2:$B$12),ROWS(購(gòu)買記錄表!B:B)),ROW(A1)))&""
在最后加上&""，這一步是容錯(cuò)處理。用空單元格與空文本合并返回空文本的特性，將超出結(jié)果數(shù)量的部分不顯示出來(lái)。
實(shí)現(xiàn)序號(hào)自動(dòng)填充
在Ａ4單元格輸入公式：
=IF(OR($B$1="",B4=""),"",COUNTIF($B$4:B4,$B$1))&""
公式含義是：
如果$B$1姓名為空、或者對(duì)應(yīng)行Ｂ列為空，就不填充序號(hào)；否則，序號(hào)為Ｂ列姓名出現(xiàn)的次數(shù)。
COUNTIF($B$4:B4,$B$1)，是在一隨著行數(shù)增加的區(qū)域查找B1單元格指定姓名出現(xiàn)的次數(shù)。
