常被問到在Excel裡的一個資料清單中,如何取出其中的XXX?
網友問到,在資料清單中利用一個欄位來標記某些項目要被移除,該如何重新取得正確的資料清單?
1. 設計公式
特性:資料來源內容更改,公式產生結果隨之改變,不用重寫公式。
選取儲存格A3:B24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、標記。
儲存格D2:
{=IFERROR(INDEX(項目,SMALL(IF(標記<>"X",ROW(項目),""),ROW(1:1))-2),"")}
複製儲存格D3,貼至儲存格D3:D24。
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上。「{}」。
(1) IF(標記<>"X",ROW(項目),"")
判斷標記陣列中不是「者」,傳回其列號。
(2) SMALL(IF(標記<>"X",ROW(項目),""),ROW(1:1))
利用 SMALL 函數將第(1)式傳回的列號,由小至大取出。
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) INDEX(項目,SMALL(IF(標記<>"X",ROW(項目),""),ROW(1:1))-2)
將第(2)式傳回的由小至大的列號,藉由 INDEX 函數取出對應的內容。
其中參數「-2」是因為第一筆資料起始在第3列。
(4) IFERROR(INDEX(項目,SMALL(IF(標記<>"X",ROW(項目),""),ROW(1:1))-2),"")
利用 IFERROR 函數將第(3)式可能傳回的錯誤訊息以空白(空字串)顯示。
2. 基本篩選
特性:資料來源內容更改,每次都要重新篩選一次。
透過基本篩選動作,將「X」內容取消選取:
結果如下,再複製結果出來使用。
3. 進階篇選
特性:資料來源內容更改,每次都要重新篩選一次。
如果利用進階篇選的概念來處理,則在第1、2列中建立篩選的條件。
並且設定資料範圍、準則範圍、複製目的的。
結果如下。
留言列表