有轉導老師問到：如果每天將有來輔導的學生資料做成記錄，該如何在 Excel 中查詢？
假設記錄的內容至少有：日期、姓名、事項。
如何透過日期或姓名來查詢記錄的內容？
使用「篩選」工具，可以利用手動的程序來取得想要的學生資料。
如果想要透過公式來查詢，則參考以下二種方式：
1. 依日期查詢
假設所記錄的資料放在儲存格E1:G100。（請自行調整資料範圍）
選取儲存格E1:G100，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、學生、事項。
儲存格A3：{=IFERROR(OFFSET($E$1,SMALL(IF((日期=$B$1),ROW(日期),""),
ROW(1:1))-1,0),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格A3，貼至儲存格A3:A11。
(1) IF((日期=$B$1),ROW(日期),"")
在日期陣列中找出符合儲存格B1內容的儲存格，並傳回其列號；否則，傳回空字串。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數由小至大依序找出第1小的數、第2小的數、第3小的數、…。
當公式向下複製時，公式中 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($E$1,第(2)式-1,0)
利用 OFFSET 函數將第(2)式的傳回值代入，會傳回對應的儲存格內容。
(4) IFERROR(第(3)式,"")
利用 IFFERROR 函數將查詢錯誤的訊息以空白（空字串）顯示。
同理：
儲存格B3：{=IFERROR(OFFSET($F$1,SMALL(IF((日期=$B$1),ROW(日期),""),
ROW(1:1))-1,0),"")}
（注意：只修改了第(1)式中 OFFSET 函數中的儲存格E1為F1）
儲存格C3：{=IFERROR(OFFSET($G$1,SMALL(IF((日期=$B$1),ROW(日期),""),
ROW(1:1))-1,0),"")}
（注意：只修改了第(1)式中 OFFSET 函數中儲存格E1為G1）
2. 依姓名查詢
儲存格A3：{=IFERROR(OFFSET($E$1,SMALL(IF((學生=$B$1),ROW(學生),""),
ROW(1:1))-1,0),"")}
（注意：只修改了「1. 依日期查詢」公式中的名稱「日期」為「學生」，以下亦同。）
儲存格B3：{=IFERROR(OFFSET($F$1,SMALL(IF((學生=$B$1),ROW(學生),""),
ROW(1:1))-1,0),"")}
儲存格C3：{=IFERROR(OFFSET($G$1,SMALL(IF((學生=$B$1),ROW(學生),""),
ROW(1:1))-1,0),"")}
以上全是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
【延伸閱讀】
參考：本部落格中其他關於 Excel OFFSET 函數的應用
留言列表