在 Excel 的工作表中有一個項目清單,其中有「區間」的欄位(參考下圖),如何列出區間範圍有包含指定數值的清單?

Excel-列出區間範圍包含指定數值的清單(INDEX,OFFSET,ROW,IFERROR)

【公式設計】

儲存格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),"")}

本單元先不介紹公式原理,先理解以下的做法,再回來想這些公式,你比較容易看懂。

 

Excel-列出區間範圍包含指定數值的清單(INDEX,OFFSET,ROW,IFERROR)

【公式設計與解析】

上圖是將數值區間以三個欄位表示,先選取儲存格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 函數的應用

學不完.教不停.用不盡文章列表

arrow
arrow

    vincent 發表在 痞客邦 留言(2) 人氣()