網友問到:在 Excel 的工作表中有一個編號清單,如何從中取出含有指定字串者?

參考下圖,例如要取出編號中含有「PS」和「RS」者,該如何處理?

Excel-取出清單中符合多個條件的項目(OFFSET,SMALL,ROW)

【公式設計與解析】

選取儲存格B1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。

儲存格D2:{=IFERROR(OFFSET($B$1,SMALL(IF((LEFT(編號,2)="PS")+(LEFT
(編號,2)="RS"),ROW(編號),""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

複製儲存格D2,貼至儲存格D2:D25。

其中:

(1) (LEFT(編號,2)="PS")+(LEFT(編號,2)="RS")

利用 LEFT 函數取出字串左邊2字元,「+」用以執行邏輯 OR 運算。

(2) IF((LEFT(編號,2)="PS")+(LEFT(編號,2)="RS"),ROW(編號),"")

在陣列公式中,判斷符合條件者傳回對應的列號,否則傳回空字串(「""」)。

(3) SMALL(第(2)式,ROW(1:1))

當公式向下複製時,可以在陣列公式中,由小至大依序取出最小者。

當公式向下複製,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(4) OFFSET($B$1,第(3)式-1,0)

根據第(3)式傳回結果代入 OFFSET 函數,傳回對應的儲存格內容。

(5) IFERROR(第(4)式,"")

利用 IFERROR 函數將第(4)式傳回錯誤訊息時,顯示為空字串("")。

同理:

儲存格E2:{=IFERROR(OFFSET($C$1,SMALL(IF((LEFT(編號,2)="PS")+(LEFT
(編號,2)="RS"),ROW(編號),""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

arrow
arrow
    文章標籤
    Excel OFFSET SMALL ROW
    全站熱搜

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