如何在Excel找相同：精準找出重複、唯一與特定條件的資料！

Byadmin

如何在Excel找相同？解決資料整理的痛點，讓你的報表更精準！

您是不是常常在整理Excel報表時，一不小心就出現重複的資料，或是想知道哪些資料是獨一無二的？這絕對是許多Excel使用者在資料處理過程中，最常遇到的難題之一！像是小美，她在公司負責彙整客戶名單，結果發現好幾位客戶重複登記了好幾次，這不僅讓她花費了額外時間去釐清，也可能影響到後續的客戶聯繫。又或者，你可能是一名業務人員，想快速知道哪些產品是本季的銷售冠軍，哪些是銷量掛零的冷門品項。如果沒有一套有效的方法，在成千上萬筆的資料中大海撈針，那可真的是讓人頭痛欲裂！

其實，Excel提供了許多強大的功能，可以幫助我們輕鬆地「在Excel找相同」資料。這篇文章，我將會帶你一步步深入了解，如何運用Excel內建的工具，從找出完全重複的項目，到辨識唯一的數值，甚至是找出符合特定條件的「相同」資料。這不只是一個簡單的技巧，更是提升你工作效率、確保資料準確性的關鍵！讓我們一起來學習，讓Excel成為你資料整理的得力助手吧！

快速解答：如何在Excel找相同？

總的來說，要在Excel中找出相同資料，主要可以透過以下幾種方法：

  • 條件式格式設定 (Conditional Formatting)：這是最直觀、最快速的方法，用顏色高亮顯示重複或唯一的儲存格。
  • 移除重複項目 (Remove Duplicates)：直接刪除重複的列，讓資料變得乾淨。
  • COUNTIF 函數：透過計數來判斷某個值出現的次數，進而找出重複或唯一的值。
  • 進階篩選 (Advanced Filter)：可以找出唯一值，或將符合條件的資料複製到其他地方。
  • Power Query (取得與轉換資料)：對於大量、複雜的資料處理，Power Query提供了更強大的資料清理與轉換功能，包含找出重複、分組等。

接下來，我們將會詳細解析這些方法，讓你針對不同的需求，都能找到最適合的解決方案。

一、 運用條件式格式設定：讓相同資料「現形」！

如果你想快速看到哪些資料有重複，或是哪些是獨一無二的，條件式格式設定絕對是你最好的朋友！它不需要你寫任何公式，就能透過視覺化的方式，直接告訴你答案。這也是我個人最常使用的方法之一，因為它操作簡單，而且效果立竿見影！

找出重複的儲存格

假設你有一份客戶訂單列表，你擔心會有相同的訂單編號重複出現，這可能會導致重複出貨。這時候，你就可以這樣做：

  1. 選取範圍：首先，選取你想要檢查重複資料的儲存格範圍。例如，如果你想檢查A欄的訂單編號是否有重複，就選取A欄的所有資料。
  2. 進入條件式格式設定：點選Excel上方工具列的「常用」選項，然後找到「條件式格式設定」。
  3. 選擇規則：在下拉選單中，選擇「醒目提示儲存格規則」，然後點選「重複的值」。
  4. 設定格式：在彈出的對話框中，你會看到「重複」和「唯一」的選項。我們在這裡選擇「重複」。接著，你可以選擇你想要的醒目提示格式，例如「淺紅填滿與深紅文字」，或是自訂格式。
  5. 確定：點選「確定」。

這樣一來，所有在選取範圍內重複出現的訂單編號，都會被標記上你所設定的顏色。是不是超級方便呢？

找出唯一的儲存格

反之，有時候你也可能想找出那些「獨特」的項目。例如，在員工資料表中，你可能想知道哪些員工的員工編號是唯一的，沒有被其他人使用。操作方式與找出重複值非常相似：

  1. 選取範圍：同樣地，選取你想要檢查的儲存格範圍。
  2. 進入條件式格式設定：點選「常用」>「條件式格式設定」。
  3. 選擇規則：選擇「醒目提示儲存格規則」>「重複的值」。
  4. 設定格式：在對話框中，將選項從「重複」改成「唯一」。然後選擇你喜歡的醒目提示格式。
  5. 確定：點選「確定」。

這樣，所有在該範圍內只出現一次的數值，就會被醒目標示出來，讓你一目了然。

我的經驗談：

我曾經處理過一份包含數千筆學生考試成績的資料，老師希望我找出那些只有一位學生拿到滿分的科目。透過條件式格式設定中的「唯一值」功能，我輕輕鬆鬆就完成了這項任務，比起手動去一筆一筆核對，效率提升了至少十倍！這讓我深刻體會到，善用Excel的這些小工具，真的可以省下大量的寶貴時間。

二、 移除重複項目：讓你的資料乾淨溜溜！

如果你發現資料中存在重複項，而且你確定這些重複的列是「多餘」的，那麼「移除重複項目」功能就非常實用了。這就像是給你的資料做一次大掃除，讓它變得整潔、精煉。

操作步驟

  1. 選取範圍：選取你包含重複資料的整個範圍，或是你想要檢查的特定欄位。如果你的資料有標題列，請務必包含標題列。
  2. 找到移除重複項目：點選Excel上方工具列的「資料」選項，然後在「資料工具」群組中找到「移除重複」。
  3. 選擇欄位：一個「移除重複」的對話框會彈出。Excel通常會自動選取你選取範圍內的所有欄位。如果你只想針對特定欄位檢查重複（例如，只看訂單編號是否有重複，即使其他欄位不同），就在這裡取消勾選其他欄位，只保留你想檢查的欄位。
  4. 確定：點選「確定」。

Excel會告訴你移除了多少重複的列，以及還剩下多少唯一值。這是一個非常直接了當的方法，但請注意，這個操作是「不可復原」的，所以在使用前，我強烈建議你先備份你的資料，以免誤刪。

小提醒：

當你選擇「移除重複」時，Excel預設會移除「整列相同」的資料。如果你只想移除「特定欄位相同」的資料，例如，即使其他欄位不同，只要A欄的訂單編號相同，就視為重複並移除，那記得在步驟3中，只勾選你要檢查重複的那個欄位。

三、 善用 COUNTIF 函數：精準判斷重複次數

如果你不只想知道「有沒有」重複，還想知道某個項目「重複了幾次」，那麼COUNTIF函數就是你的得力助手。這個函數可以幫你在指定範圍內，計算符合特定條件的儲存格數量。

找出重複值

假設你有一份產品銷售記錄，你想知道哪些產品的銷售次數超過一次（即有重複記錄）。你可以這樣做：

  1. 新增輔助欄：在你資料的旁邊新增一個欄位，例如命名為「重複次數」。
  2. 輸入 COUNTIF 公式：在第一個資料列的「重複次數」欄位中，輸入以下公式：
    =COUNTIF(A:A, A2)
    這裡，假設你的產品名稱在A欄，而你正在處理的是第2列的資料 (A2)。這個公式的意思是：「在A欄的所有範圍內，計算與A2儲存格內容相同的儲存格有幾個。」
  3. 拖曳公式：將滑鼠移到該儲存格右下角，當游標變成一個小黑十字時，向下拖曳，將公式應用到所有資料列。

現在，每一列都會顯示該產品出現的總次數。如果數值大於1，就代表這個產品有重複記錄。

找出唯一值

要找出唯一值，原理也是一樣的，只是我們在判斷時看數值是否等於1：

  1. 新增輔助欄：同樣新增一個欄位，例如命名為「是否唯一」。
  2. 輸入 COUNTIF 公式：輸入公式：
    =COUNTIF(A:A, A2)
  3. 套用篩選器：在輔助欄位上套用篩選器，然後只選取數值為「1」的儲存格。

這樣，你就能快速篩選出所有出現一次的唯一值。

COUNTIF 函數的優勢：

COUNTIF 函數的彈性很大。你可以不只判斷「重複」或「唯一」，還可以設定其他條件，例如找出出現次數大於3次的資料，或是特定類別中的重複項目。這讓它成為在Excel找相同資料時，一個非常靈活的工具。

四、 進階篩選：找出唯一值與複製篩選結果

進階篩選是Excel中一個比較進階但功能強大的工具，它不僅可以幫助你找出唯一值，還能將篩選出來的結果複製到新的位置，對於需要進一步分析的資料來說，非常方便。

找出唯一值

  1. 設定範圍：確保你的資料有標題列。
  2. 開啟進階篩選：點選「資料」>「排序與篩選」群組中的「進階」。
  3. 設定進階篩選對話框：
    • 動作 (Action)：選擇「只篩選，在其他地方列出」(Filter the list, in-place) 是直接在原範圍篩選；選擇「將篩選結果列至其他位置」(Copy to another location) 則是複製到新的地方。
    • 清單範圍 (List range)：選取包含標題和所有資料的範圍。
    • 準則範圍 (Criteria range)：這個部分留空，因為我們是要找出唯一值。
    • 列至 (Copy to)：如果你選擇「將篩選結果列至其他位置」，在這裡指定一個空的儲存格作為複製的起始位置。
    • 勾選「選擇不重複的記錄」 (Unique records only)：這是關鍵步驟！務必勾選此選項。
  4. 確定：點選「確定」。

Excel就會根據你的設定，找出並顯示（或複製）唯一的記錄。這個方法特別適用於當你希望保留原始資料，同時又要得到一個乾淨的唯一值列表時。

為什麼進階篩選值得學習？

進階篩選不僅僅是找唯一值，它還能讓你設定複雜的篩選條件。例如，你可以設定「找出所有地區是「北部」且銷售額大於50000的產品」，然後將這些符合條件的資料，整齊地複製到另一個報表中。這對於製作客製化的報告來說，是非常實用的技能。

五、 Power Query：處理大量與複雜資料的終極武器

如果你的資料量非常龐大，或者你需要進行更複雜的資料清理和轉換，那麼Power Query (在較新版本的Excel中稱為「取得與轉換資料」) 就是你的終極武器！它提供了一個更強大的介面，讓你能夠視覺化地進行資料的清理、合併、轉換，並且可以將這些步驟自動化。

Power Query 如何協助找出相同資料？

Power Query 擁有多種功能，可以有效地找出相同資料：

  • 移除重複 (Remove Duplicates)：與Excel內建功能類似，但Power Query可以將這個步驟記錄下來，並且自動化。
  • 群組方式 (Group By)：你可以根據某個欄位進行分組，然後計算每個組別的數量，進而找出重複的項目。
  • 合併查詢 (Merge Queries)：如果你有兩個資料表，想要找出兩個表中「相同」的資料（類似於SQL的JOIN操作），Power Query的合併查詢功能非常強大。
  • 條件式欄位 (Conditional Column)：你可以新增一個欄位，根據其他欄位的條件來判斷是否重複或唯一。

簡單範例：使用Power Query找出重複的客戶ID

  1. 匯入資料：點選「資料」>「取得資料」>「從檔案」>「從活頁簿」，然後選擇你的Excel檔案，並選擇要處理的工作表。
  2. 開啟 Power Query 編輯器：資料匯入後，會開啟Power Query 編輯器。
  3. 選取欄位：選取你想要檢查重複的欄位，例如「客戶ID」。
  4. 移除重複：在「常用」選項卡中，點選「移除資料行」旁的下拉選單，選擇「移除重複的資料行」。
  5. 關閉並載入：點選「關閉並載入」，Power Query就會將清理後的資料載入到Excel中。

Power Query的強大之處在於，它會記錄下你執行的每一個步驟。當你的原始資料更新後，你只需要點擊「重新整理」，所有步驟都會自動重新執行，非常省時省力。

Power Query 的深度應用：

對於需要定期處理大量報表的使用者來說，Power Query 絕對是必學技能。它可以讓你從根本上解決資料清理的痛點，並且將重複性的工作自動化。例如，你可以設定一個流程，自動從多個Excel檔案中匯入資料，找出重複的訂單，然後將唯一的訂單匯總到一個主報表中。這需要一些學習時間，但絕對是值得的投資。

常見問題與專業解答

在使用Excel找出相同資料的過程中，您可能會遇到一些常見的問題。這裡我整理了一些，並提供詳細的解答，希望對您有所幫助。

Q1：為什麼我使用條件式格式設定後，看不到任何顏色標記？

詳細解答：

這種情況通常有幾個可能的原因：

  • 範圍選取錯誤： 請確認您選取的範圍是否包含了您想要檢查的儲存格。如果您只選取了一小部分，而重複的資料在未選取的範圍內，當然就看不到顏色。
  • 資料格式不一致： Excel在判斷「相同」時，會嚴格區分資料格式。例如，數字「123」和文字「123」在Excel眼中是不同的。又或者，數字「01」和數字「1」雖然看起來一樣，但它們的儲存格式可能不同。請檢查您想比較的儲存格，確保它們的資料格式是一致的。有時候，一個看不見的空格（例如，數字後面多了一個空白）也會導致判斷為不同。
  • 套用的規則有誤： 請再次確認您在條件式格式設定中，選擇的是「重複的值」還是「唯一值」，以及您想要醒目提示的條件。
  • 篩選狀態影響： 如果您啟用了篩選，條件式格式設定的效果可能會受到影響。請嘗試清除所有篩選，再重新檢查。

您可以嘗試在一個非常簡單的測試範圍內，輸入幾個明顯重複和唯一的值，例如： A1=1, A2=2, A3=1, A4=3, A5=2。然後對A1:A5進行條件式格式設定，選擇「重複的值」，觀察A1、A3、A2、A5是否被標記。透過這種方式，可以幫助您快速排除問題。

Q2：我使用「移除重複項目」功能時，發現它移除了我不想移除的資料，該怎麼辦？

詳細解答：

「移除重複項目」功能預設是比較嚴格的，它會判斷「整列」是否相同。如果您希望根據「特定欄位」來判斷重複並移除，那麼關鍵就在於選擇要檢查的欄位。在「移除重複」的對話框中，您可以看到所有欄位的勾選列表。如果您只想根據「訂單編號」欄位來判斷重複，即使其他欄位（如訂單日期、客戶名稱）不同，也應該將其他欄位的勾選取消，只保留「訂單編號」的勾選。這樣，Excel就只會比較訂單編號，一旦發現相同的訂單編號，就會移除該整列資料。請務必在操作前備份您的資料，因為這個操作是無法復原的。

Q3：COUNTIF 函數計算出來的結果為什麼有時候不對？

詳細解答：

COUNTIF 函數在判斷「相同」時，同樣非常注重精確性。常見的問題包括：

  • 儲存格參照錯誤： 請仔細檢查您在 COUNTIF 公式中指定的範圍和條件。例如，如果您想計算A欄的重複值，公式應該是 `=COUNTIF(A:A, A2)`，而不是 `=COUNTIF(B:B, A2)` 或 `=COUNTIF(A:A, B2)`。
  • 隱藏字元或空格： 這是最常見的「鬼打牆」問題！在Excel中，即使肉眼看起來一樣，一個看不見的空格（例如，文字末尾多了一個空白）都會讓 COUNTIF 認為它們是不同的。您可以嘗試在 COUNTIF 公式中使用 `TRIM` 函數來去除首尾空格，例如：`=COUNTIF(A:A, TRIM(A2))`。
  • 數字與文字混雜： 如同條件式格式設定，COUNTIF 對數字和文字格式非常敏感。如果您在同一個欄位中，有時候輸入的是數字，有時候輸入的是文字格式的數字（例如，手動在數字前加上引號 `’123`），COUNTIF 就可能無法正確計數。建議您在COUNTIF公式中，如果確定比較的是數字，可以嘗試這樣寫：`=COUNTIF(A:A, VALUE(A2))`，反之，如果是文字，則可以嘗試 `=COUNTIF(A:A, TEXT(A2, “0”))`。
  • AND 邏輯的誤解： COUNTIF 只能判斷「一個」條件。如果您需要判斷「多個條件」同時成立（例如，訂單編號相同 *且* 產品名稱相同），則需要使用 `COUNTIFS` 函數。

舉個例子，如果您的A欄有「蘋果」、「蘋果 」（多一個空格）、「香蕉」，那麼 `=COUNTIF(A:A, “蘋果”)` 只會計算到 1。而 `=COUNTIF(A:A, TRIM(A2))`（假設A2是「蘋果 」）則可以正確計數為 2。所以，善用 `TRIM` 函數，對於解決COUNTIF的疑難雜症非常有幫助。

Q4：進階篩選我設定好了，但為什麼篩選結果是空的？

詳細解答：

進階篩選篩選結果為空，通常是因為您的「清單範圍」或「準則範圍」設定有誤，或者您沒有正確勾選「選擇不重複的記錄」選項。請您再次檢查以下幾個細節：

  • 清單範圍 (List range) 必須包含標題： 進階篩選需要標題列來辨識欄位。請確保您選取的清單範圍，是從標題列開始，一直到所有資料的最後一列。
  • 準則範圍 (Criteria range) 的設定： 如果您只是要找出唯一值，請將「準則範圍」留空。如果您設定了準則範圍，但實際上沒有符合條件的資料，那麼篩選結果自然會是空的。
  • 「選擇不重複的記錄」 (Unique records only) 的勾選： 這是找出唯一值的核心選項。請務必確認您已經正確勾選了它。
  • 資料本身的問題： 有時候，資料本身就沒有您預期中的唯一值。例如，如果您想找出唯一的員工編號，但事實上所有員工編號都有重複，那麼進階篩選就不會找出任何結果（這是正常的）。

如果您是希望在其他地方列出結果，請確保您指定的「列至 (Copy to)」儲存格是一個空白且可用的位置，並且該儲存格的上方沒有其他資料影響複製結果。

Q5：Power Query 聽起來很強大，但對我來說是不是太複雜了？

詳細解答：

Power Query 的確比 Excel 的內建函數功能更強大，也因此初學時可能會覺得稍微複雜一些。但是，它的學習曲線並不如想像中那麼陡峭。Excel 開發團隊將 Power Query 設計得相當直觀，大部分的操作都可以透過點擊按鈕來完成，而無需編寫程式碼。

您可以從最基本的功能開始學習，例如：

  • 匯入資料： 學習如何從不同的來源（Excel檔案、CSV、資料庫等）匯入資料。
  • 移除重複： 這是最簡單也最常用的功能之一，與Excel內建的「移除重複」相似，但更強大。
  • 篩選和排序： 學習如何根據條件篩選資料，以及如何排序。
  • 更改資料類型： 確保您的資料類型（數字、文字、日期等）是正確的。

一旦您掌握了這些基礎，再逐步學習「群組方式」、「合併查詢」等更進階的功能，就會順利很多。許多的教學資源（包括Microsoft官方網站和許多Excel教學部落格）都有提供非常詳盡的Power Query入門課程。重點是，一旦您學會了Power Query，它將極大地提升您處理大量、複雜資料的能力，讓您從繁瑣的資料整理工作中解放出來。

總之，無論你是想快速找出重複項，還是需要進行複雜的資料清理，Excel都提供了多樣化的工具。從簡單的條件式格式設定，到強大的Power Query，只要你掌握了這些技巧，絕對能讓你的資料處理工作事半功倍！

如何在Excel找相同