學不完．教不停．用不盡

有轉導老師問到：如果每天將有來輔導的學生資料做成記錄，該如何在 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 函數的應用