網友問到:在下圖中,根據表一的清單內容,只要表三出現的內容,就不會出現在表二,該如何處理?

研究題意:也就是表二和表三的內容互相不重覆,組合起來即為表一。

 

【公式設計】

(1) 根據G欄查詢得到H欄內容

儲存格H3:=IFERROR(VLOOKUP(G3,$A$3:$B$24,2,FALSE),"")

利用 VLOOKUP 函數,根據G欄內容查詢對應的B欄結果,其中參數 FALSE,乃為查詢時要求「要完符合」。

再利用 IFERROR 函數將查表結果為錯誤訊息者(因為G欄可能尚未填入資料,所以查不到對應的B欄結果。),改以空白顯示。

複製儲存格H3,貼至儲存格H3:H24。

 

(2)根據G欄產生不重覆的清單

儲存格D3:{=OFFSET($A$3,SMALL(IF(COUNTIF($G$3:$G$24,$A$3:$A$24)=0,
ROW($A$3:$A$24),999),ROW(1:1))-3,,,)}

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

公式分解:

COUNTIF($G$3:$G$24,$A$3:$A$24):利用 COUNTIF 函數,找出A欄內容已在G欄出現者得到 1,未出現者得 0。最後傳回 1/0 的陣列。

IF(COUNTIF($G$3:$G$24,$A$3:$A$24)=0,ROW($A$3:$A$24),999):根據上式的 1/0 陣列,對應 1 傳回列數(3~24),對應 0 傳回 999(假設資料列數不超過 999)。

SMALL(IF(COUNTIF($G$3:$G$24,$A$3:$A$24)=0,ROW($A$3:$A$24),999),
ROW(1:1)):在 SMALL 函數中使用 ROW(1:1)=1 來取得上式中的最小值。當公式往下各列複製時,ROW(1:1) 會變成 ROW(2:2)=2 → ROW(3:3)=3 → ...,可以依序取得第 2, 3, ... 小的數。

最後再藉由 OFFSET 函數將上式取得的列數找出對應的儲存格內容。其中結果為 0 者,乃因在第 999 列對應的內容為「0」所致。

複製儲存格D3,貼至儲存格D3:D24。

 

(3)根據D欄查詢得到E欄內容

儲存格E3:=IFERROR(VLOOKUP(D3,$A$3:$B$24,2,FALSE),"")

原理解釋同(1)。

複製儲存格E3,貼至儲存格E3:E24。

 

【類似觀念應用】

Excel-根據某欄摘要出符合條件的結果(陣列公式,OFFSET)

Excel-找出清單中不重覆者

Excel-使用篩選工具列出不重覆的資料

 

【延伸閱讀: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版新增函數的使用

    全站熱搜

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