【Excel 教學】如何使用 Power Query 快速比對兩份資料的差異？找出新增、刪除與共同項目

在日常的辦公室工作中，我們經常需要比對兩份清單的差異，例如：比較這個月和上個月的訂單清單、核對自己與廠商的帳目、或是找出兩份客戶名單的增減。傳統方法不僅耗時，還容易出錯。

今天，我們要介紹如何使用 Excel 內建的強大工具 Power Query，只需要設定一次，未來就能一鍵更新比對結果，輕鬆找出兩份資料中**「新增」、「刪除」以及「共同擁有」**的項目。

本文重點

1. 共同項目：找出兩份資料中都存在的紀錄。

2. 差異項目 (A 有 B 沒有)：找出只存在於第一份資料的紀錄（可視為被刪除的項目）。

3. 差異項目 (B 有 A 沒有)：找出只存在於第二份資料的紀錄（可視為新增的項目）。

4. 一鍵更新：修改來源資料後，如何快速刷新比對結果。

---

範例說明

假設我們有兩份訂單資料，分別是「店家」的紀錄和「一整天」的紀錄。我們的目標是比對出這兩份清單的差異。

• 店家資料 (A)：缺少了訂單編號 A010，但有 A005。

• 一整天資料 (B)：有訂單編號 A010，但缺少了 A005。

雖然兩份資料的筆數可能相同，但內容卻有出入。接下來，我們將從頭開始，一步步教您如何使用 Power Query 完成比對。

操作步驟

首先，我們需要將這兩份資料載入到 Power Query 編輯器中，並建立連線。

1. 在一個新的 Excel 活頁簿中，分別建立兩個工作表，命名為「店家」與「一整天」，並將對應的資料貼上。

2. 點選「店家」工作表中的任一儲存格，前往 [資料] 索引標籤，點選 [從表格/範圍]。

3. 在彈出的視窗中，確認範圍無誤，並勾選 「我的表格有標題」，然後按 [確定]。

4. Excel 會開啟 Power Query 編輯器。在右側的 [查詢設定] 中，將名稱改為「店家」。

5. 點選左上角的 [關閉並載入] 的下拉箭頭，選擇 [關閉並載入至...]。

6. 在匯入資料視窗中，選擇 「僅建立連線」，然後按 [確定]。這一步驟是為了不將 Power Query 的處理過程重複載入到工作表中，保持版面乾淨。

7. 對「一整天」工作表重複以上步驟，建立一個名為「一整天」的連線。

完成後，您會在右側看到兩個僅建立連線的查詢。

我們要使用「合併查詢」功能，找出兩份清單中都存在的訂單。

1. 前往 [資料] 索引標籤 > [取得資料] > [合併查詢] > [合併]。

2. 在合併視窗中：

   • 第一個下拉選單選擇「店家」。

   • 第二個下拉選單選擇「一整天」。

   • 分別點選兩邊表格中的「訂單編號」欄位，讓 Power Query 知道要用此欄位進行比對。

   • 聯結種類選擇 「內部 (僅限相符的資料列)」。

   • 點選 [確定]。

3. 在 Power Query 編輯器中，將右側的查詢名稱改為「店家與一整天都有的訂單」。

4. 您會看到最後多出一個名為「一整天」的資料行，因為我們只需要共同項目，可以直接對此欄按右鍵選擇 [移除]。

5. 點選 [關閉並載入]，Power Query 會將結果呈現在一個新的工作表中。

這個步驟要找出只存在於「店家」清單中的訂單。

1. 再次執行 [資料] > [取得資料] > [合併查詢] > [合併]。

2. 設定如下：

   • 第一個表格選「店家」。

   • 第二個表格選「一整天」。

   • 比對欄位同樣是「訂單編號」。

   • 聯結種類選擇 「左方反向 (僅限第一個資料表中的資料列)」。

   • 點選 [確定]。

3. 將查詢名稱改為「店家有一整天沒有的訂單」。

4. 最後的「一整天」資料行可以移除。

5. 點選 [關閉並載入]，即可得到結果。

這個步驟與上一步類似，只是將兩個表格的順序對調。

1. 再次執行合併查詢。

2. 設定如下：

   • 第一個表格選「一整天」。

   • 第二個表格選「店家」。

   • 比對欄位同樣是「訂單編號」。

   • 聯結種類選擇 「左方反向 (僅限第一個資料表中的資料列)」。

   • 點選 [確定]。

3. 將查詢名稱改為「一整天有店家沒有的訂單」，並移除最後多餘的「店家」資料行。

4. 點選 [關閉並載入]。

---

Power Query 的強大之處：一鍵更新

完成以上設定後，您已經建立了一個自動化的比對模型。未來，無論來源資料（「店家」或「一整天」工作表）如何變動，您都不再需要重複上述步驟。

只需要在任一個結果表格上按右鍵 > [重新整理]，或是到 [資料] 索引標籤點選 [全部重新整理]，所有的比對結果都會自動更新！

總結

透過 Power Query 的「合併查詢」功能，我們可以輕鬆建立出一個可重複使用的資料比對範本。這不僅大大提升了工作效率，也有效降低了人工核對時可能發生的錯誤。下次當您需要比對兩份複雜的清單時，不妨試試這個強大的工具吧！

[範例檔案下載]

• PowerQuery比對資料AB.xlsx

Google Sheets 教學：輕鬆將表單多選結果拆分成多筆資料 (FLATTEN, SPLIT, QUERY 組合應用)

前言

您是否也曾遇過這樣的困擾？當您使用 Google 表單的「核取方塊」讓使用者進行多重選擇後，收集到的回覆在 Google Sheets 中會將所有選項擠在同一個儲存格裡，並用逗號隔開。例如，一個訂單表單中，客戶可能在「購買口味」欄位中一次選擇了「原味, 粉光, 人蔘」。

這樣的資料格式對於後續的數據分析、庫存管理或製作樞紐分析表都非常不便。我們真正需要的是將這筆資料拆分成三筆獨立的紀錄，每一筆對應一個口味。

今天，我們將分享一個強大的組合公式，利用 FLATTEN、SPLIT、QUERY 和 ARRAYFORMULA 這四個函數，一勞永逸地解決這個問題，將雜亂的多選結果自動轉換為乾淨、可分析的獨立資料列。

---

本次教學核心函數

在深入探討複雜的組合公式之前，讓我們先來了解這次會用到的幾個核心函數：

• ARRAYFORMULA：陣列公式的啟動器。它能讓原本只對單一儲存格運作的函數，擴展到對整個範圍或陣列進行運算，是處理大量資料不可或缺的利器。

• FLATTEN：此函數能將一個或多個範圍（二維陣列）中的所有值「扁平化」，合併成單一欄（一維陣列）。這是將多欄資料轉換為單欄的關鍵。

• SPLIT：根據指定的分隔符號（如逗號、空格）來分割文字。它會將一個儲存格的內容拆分成多個儲存格。

• QUERY：Google Sheets 中的 SQL 查詢語言。它能讓我們對資料範圍進行強大的篩選、排序和整理，功能非常強大。

---

實作步驟與公式詳解

假設我們的原始資料如下，位於 A、B、C 三欄：

日期 (A)	購買人 (B)	購買商品 (C)
6/24	一整天	原味;粉光
6/25	彰化一整天	原味
6/26	明和	厡味;粉光;人參

我們的目標是將其轉換成如下格式：

日期	購買人	購買商品
6/24	一整天	原味
6/24	一整天	粉光
6/25	彰化一整天	原味
6/26	明和	厡味
6/26	明和	粉光
6/26	明和	人蔘

以下是我們的完整公式，我們將由內而外逐步解析：

Generated excel

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A1:A&"|"&B1:B&"|"&SPLIT(C1:C,";")),"|"),"select * where Col3 is not null",0))

步驟一：拆分多選項目 (內層的 

SPLIT(C1:C, ";")

這是公式的核心起點。我們首先針對 C 欄（購買商品）進行處理，使用分號 , 作為分隔符號，將每個儲存格的內容拆分開來。例如，「原味, 粉光」會被拆成兩個獨立的欄位：「原味」和「粉光」。

步驟二：組合固定欄位與拆分後的項目 (

A1:A & "|" & B1:B & "|" & SPLIT(C1:C,";")

接著，我們將固定不變的欄位（A 欄的日期和 B 欄的購買人）與上一步拆分後的商品項目組合起來。這裡我們使用 & 符號進行文字串接，並以一個特殊符號 | 作為新的分隔符。

完成這一步後，我們的資料會暫時變成一個二維陣列，看起來像這樣：

6/24|一整天|原味	6/24|一整天|粉光
6/25|彰化一整天|原味
6/26|明和|原味	6/26|明和|粉光

步驟三：將二維陣列扁平化為一維 (

FLATTEN(...)

FLATTEN 函數在這裡發揮了關鍵作用。它會將上一步產生的二維陣列（多行多列）「壓平」，變成一個長長的單欄資料。所有儲存格的內容會由左至右、由上至下地被排列到一個欄位中。

步驟四：再次拆分，還原欄位結構 (外層的 

SPLIT(FLATTEN(...), "|")

現在我們有了一個乾淨的單欄資料，每一筆都包含了「日期|購買人|商品」。我們再次使用 SPLIT 函數，這次用我們自訂的分隔符 | 來拆分，將每一筆資料還原成三個獨立的欄位。

步驟五：清理空值，取得最終結果 (

QUERY(..., "select * where Col3 is not null", 0)

經過前幾步的轉換，資料中可能會產生一些空行或無效資料。最後，我們用 QUERY 函數來進行最後的清理。

• select *: 選取所有欄位。

• where Col3 is not null: 這是篩選條件，表示只保留第 3 欄（Col3，也就是商品欄）不是空值的資料列。

• 0: 表示我們的資料沒有標題列。

這樣一來，所有無效的空行都會被過濾掉，只留下我們需要的乾淨資料。

---

注意事項

1. 分隔符號的選擇：在步驟二中，我們使用 | 作為自訂的分隔符。請確保您選擇的符號 不會 出現在您的原始資料中，以免造成錯誤拆分。

2.  的欄位引用：在使用 QUERY 函數時，若資料來源是另一個公式的結果（而不是直接的儲存格範圍），我們需要使用 Col1, Col2, Col3... 來引用欄位。請注意，，否則會出錯。

3. 無限範圍：範例中使用 A1:A, B1:B, C1:C 是為了讓公式能自動應用到新增的資料列。

總結

透過 SPLIT -> & -> FLATTEN -> SPLIT -> QUERY 這樣一套行雲流水的組合技，我們成功地將複雜的多選資料轉換為結構清晰的表格。這不僅大大提升了資料的可讀性和可分析性，更將您從繁瑣的手動複製貼上中解放出來。希望這篇教學對您有所幫助！