在 Excel 的工作表中有一個項目清單，其中有「區間」的欄位（參考下圖），如何列出區間範圍有包含指定數值的清單？
【公式設計】
儲存格E2：
{=IFERROR(INDEX(項目,SMALL(IF(($D$2>=1*MID(區間,1,FIND("-",區間)-1))*
($D$2<=1*MID(區間,FIND("-",區間)+1,99)),ROW(項目),""),ROW(1:1))-1,0),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格E2，貼至儲存格E2:E23。
儲存格F2：
{=IFERROR(INDEX(區間,SMALL(IF(($D$2>=1*MID(區間,1,FIND("-",區間)-1))*
($D$2<=1*MID(區間,FIND("-",區間)+1,99)),ROW(項目),""),ROW(1:1))-1,0),"")}
本單元先不介紹公式原理，先理解以下的做法，再回來想這些公式，你比較容易看懂。
【公式設計與解析】
上圖是將數值區間以三個欄位表示，先選取儲存格A1:D23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：項目、起、迄。
1. 列出項目
儲存格G2：{=IFERROR(OFFSET($A$1,SMALL(IF(($F$2>=起)*($F$2<=迄),
ROW(項目),""),ROW(1:1))-1,0),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格G2，貼至儲存格G2:G23。（以下各項同）
(1) ($F$2>=起)*($F$2<=迄)
條件一：$F$2>=起，判斷儲存格F2是否大於或等於「起的陣列」中的每個儲存格內容。
條件二：$F$2<=迄，判斷儲存格F2是否小於或等於「迄的陣列」中的每個儲存格內容。
(2) IF(($F$2>=起)*($F$2<=迄),ROW(項目),"")
公式中的「*」，乃執行邏輯 AND 運算。本式為假如符合雙條件者，傳回其項目陣列對應的儲存格列號，否則傳回空字串。
(3) SMALL(第(2)式,ROW(1:1))
根據第(2)式傳回的列號，依序由小至大找出相對最小者。
ROW 函數向下複製時，會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(4) OFFSET($A$1,第(3)式)-1,0)
將第(3)式傳回的列號值代入 OFFSET 函數以取得對應的儲存格內容。
(5) IFERROR(第(4)式,"")
利用 IFERROR 函數將錯誤訊息顯示空字串。
因為第(2)式傳回空字中時，代入 SMALL 函數時，會傳回錯誤訊息。
2. 列出「起」項
儲存格H2：{=IFERROR(OFFSET($B$1,SMALL(IF(($F$2>=起)*($F$2<=迄),
ROW(項目),""),ROW(1:1))-1,0),"")}
3. 列出「-」
儲存格I2：{=IF(ISERR(SMALL(IF(($F$2>=起)*($F$2<=迄),ROW(項目),""),
ROW(1:1))),"","-")}
4. 列出「迄」項
儲存格J2：{=IFERROR(OFFSET($D$1,SMALL(IF(($F$2>=起)*($F$2<=迄),
ROW(項目),""),ROW(1:1))-1,0),"")}
【延伸閱讀】
參考：本部落格中其他關於 Excel OFFSET 函數的應用
參考：本部落格中其他關於 Excel INDEX 函數的應用
留言列表