網友常問的問題:在 Excel 的資料清單中,如何挑選重複/不重覆的項目?本篇要以陣列公式和非陣列公式二種方式來處理。(在 Excel 中使用陣列公式時,常會因為儲存格陣列的數量龐大,而造成系統效能下降,所以不得已要放棄陣列公式的使用,所以得發展不需使用陣列公式的方式。)

 

【列出不重覆的項目】

在下圖中,A欄是資料清單,在B欄中有一輔助欄位:重覆數,如何以公式來篩選不重複的項目?

Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、重覆數。

在輔助欄位輸入公式,儲存格B2:=COUNTIF(項目,A2),複製儲存格B2,貼至儲存格B2:B23。

 

1. 陣列公式

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(COUNTIF(項目,項目)=1,
ROW(項目),""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

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

(1) IF(COUNTIF(項目,項目)=1,ROW(項目),"")

在陣列公式中找出項目陣列重覆個數為 1 者(表示未重覆項目)的列號。

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

將第(1)式傳回的列號陣列,利用 SMALL 函數由小至大取出其列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式傳回的列號代入 OFFSET 函數,傳回對應的儲存格內容。

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

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

 

2. 非陣列公式

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((重覆數=1)*
ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1")))-1,0),"")

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

(1) (重覆數=1)*ROW(項目)

SUMPRODUCT 函數中找出重覆數為 1 的列號。

(2) SMALL((重覆數=1)*ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1"))

COUNTIF(重覆數,">1"):計算在重覆數欄位中共有幾個大於 1 的儲存格。

本式可依列號由小至大依序傳回第1, 2, 3, ... 個具有「V」的儲存格列號。

(3) SUMPRODUCT(第(2)式)

SUMPRODUCT 函數中,第(1)式和第(2)式可以執行相當於陣列公式的功能。

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

最後,將第(3)式的列號代入 OFFSET 函數,傳回對應的儲存格內容。

最後利用 IFERROR 函數將傳回因為 SMALL 函數傳回錯誤訊息者顯示為空白。

 

【列出重覆的項目】

在下圖中,A欄是資料清單,在B欄中有一輔助欄位:重覆數,如何以公式來篩選重複的項目?

Excel-列出重覆和不重覆項目的清單(陣列公式和非陣列公式)(SUMPRODUCT,OFFSET)

先選取儲存格A1:B23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、重覆數。

在輔助欄位輸入公式,儲存格B2=COUNTIF($A$2:A2,A2),複製儲存格B2,貼至儲存格B2:B23。

 

1. 陣列公式

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(重覆數=2,ROW(項目),""),
ROW(1:1))-1,0),"")}

這是陣列公式,輸入公式後要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

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

參考列出非重覆的項目的1.陣列公式,其差異為將「COUNTIF(項目,項目)=1」,更新為「重覆數=2」。

 

2. 非陣列公式

儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((重覆數=1)*
ROW(項目),ROW(1:1)+COUNTIF(重覆數,">1")))-1,0),"")

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

參考列出非重覆的項目的2.非陣列公式。

【延伸閱讀:FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

圖片1 Excel-由資料清單中篩選一組

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

圖片1 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

圖片1 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

 

    文章標籤

    Excel SUMPRODUCT OFFSET

    全站熱搜

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