Excel有空格如何篩選？輕鬆搞定！深入解析與實用技巧

Byadmin

Excel資料清理的神兵利器：掌握「空格」篩選的奧秘

嘿，你是不是也常常在整理Excel表格時，遇到一個令人頭疼的問題：明明看起來資料很完整，但怎麼篩選出來的結果總是怪怪的？甚至有些明明該出現的資料，卻被默默地忽略了？別擔心，這很可能就是「空格」在搞鬼！尤其是在處理從不同系統匯入的資料，或是多人協作的檔案時，各種隱藏的空格、前後綴空格、甚至只有空格的儲存格，都像個小惡魔，讓你的資料判斷失準。今天，我就要帶你深入了解，如何在Excel中，像個超級偵探一樣，精準地找出並篩選出這些惱人的「空格」，讓你的資料處理流程，從此順暢無比！

為什麼「空格」會成為篩選的絆腳石？

首先，讓我們來釐清一下，為什麼Excel中的「空格」會那麼討厭。我們通常以為的「空格」，是指我們在鍵盤上按「空白鍵」產生的那個可見的空白。但是，在Excel的數據世界裡，空格可不只這麼簡單。它可能包含：

  • 一般空格 (Space)： 就是我們最常遇到的，鍵盤空白鍵產生的那個。
  • 非中斷空格 (Non-breaking space)： 這個符號在文字編輯軟體中常用來避免單詞被強制換行，但在Excel中，它會被視為一個實實在在的字元，讓你以為是空格，實際上卻不是。
  • 字元碼為 32 的空格： 這就是一般我們常見的空格。
  • 字元碼為 160 的空格 ( nbsp)： 這就是前面提到的非中斷空格，常常來自於網頁複製貼上。
  • 其他不可見字元： 有些從特定系統匯入的資料，可能還會帶有Tab鍵、換行符號、或其他在ASCII表中顯示為控制字元的特殊符號，這些在肉眼上都可能表現為「空格」，但Excel卻將它們視為真實的字元。

這些「看不見的」字元，最讓人頭痛的地方在於，它們會影響到Excel的各種判斷。例如：

  • 文字比對： 當你試圖比較兩個儲存格內容時，即使肉眼看起來一樣，但如果其中一個含有隱藏空格，兩者就不會被視為相等。
  • 查找與取代： 你以為你已經把所有「空白」都刪除了，但因為存在隱藏空格，實際上並沒有完全清理乾淨。
  • 篩選與排序： 帶有空格的儲存格，會被Excel獨立出來，影響你正常的篩選和排序邏輯。

所以，要解決Excel有空格如何篩選的問題，首先得了解這些「空格」的種類，才能對症下藥，對吧？

快速掌握：Excel篩選出「有空格」儲存格的步驟

好了，理論講了不少，我們直接來點實際的！在Excel中，最直接、最有效率的方法，就是利用「尋找與取代」功能，搭配一些小技巧，來找出帶有空格的儲存格。這絕對是你資料清理的必學技能！

方法一：利用「尋找與取代」揪出一般空格

這招最適合用來找出你肉眼可見、或是隱藏在文字前後的一般空格。讓我們一步一步來：

  1. 選取範圍： 首先，在你想要檢查的資料範圍內，點擊滑鼠左鍵並拖曳，或者直接點選整欄或整列，將你的目標範圍選取起來。
  2. 開啟「尋找與取代」： 按下鍵盤上的 Ctrl + H (Windows) 或 Cmd + Shift + H (Mac)。這會彈出一個「尋找與取代」的對話框。
  3. 設定「尋找目標」： 在「尋找目標」欄位中，請你「不要輸入任何東西」，而是直接按下鍵盤上的「空白鍵」。你會看到欄位中出現一個小小的空白。
  4. 設定「取代為」： 在「取代為」欄位中，同樣的，「不要輸入任何東西」，而是再次按下鍵盤上的「空白鍵」。
  5. 執行尋找： 點擊「尋找全部」按鈕。Excel就會列出所有在你的選取範圍內，包含一般空格的儲存格。
  6. 選取結果： 在「尋找全部」的結果列表中，你可以點擊任何一個找到的儲存格，Excel就會跳到該儲存格。為了方便後續處理，你可以一次選取所有找到的項目：按一下列表中的第一個項目，然後按住 Shift 鍵，再點擊列表中的最後一個項目。這樣，所有包含空格的儲存格都會被反白選取。
  7. 後續處理： 現在，這些含有一般空格的儲存格都已經被選取了。你可以選擇：
    • 刪除空格： 如果你只想刪除這些空格，可以在「尋找目標」和「取代為」欄位都留空（或是輸入一個你想要的字元），然後點擊「全部取代」。這樣，所有找到的空格都會被移除。
    • 標記出來： 如果你只想知道哪些地方有空格，可以將選取的儲存格設定為某種醒目的背景色或字體顏色。

我的經驗談： 很多時候，我們遇到的「空格」其實是藏在文字的開頭或結尾，這個方法非常有效！而且，按下「尋找全部」後，它會直接把所有符合條件的儲存格一次列出來，真是省時省力！

方法二：利用「篩選」功能結合「TRIM」函數找出前後綴空格

對於Excel中那些隱藏在文字開頭或結尾的「一般空格」，我們除了用「尋找與取代」之外，還可以用一個非常聰明的函數「TRIM」來輔助。TRIM函數的作用，就是移除文字字串開頭和結尾的所有空格，並將中間連續的兩個或更多空格，縮減為一個。這個特性，正好可以幫助我們判斷哪些儲存格有「多餘」的空格。

步驟如下：

  1. 新增輔助欄： 在你的原始資料旁邊，新增一個空白的欄位，作為輔助欄。假設你的原始資料在A欄，你可以在B欄開始操作。
  2. 輸入TRIM函數： 在B欄的第一個儲存格（例如 B2，假設A1是標題），輸入公式：=TRIM(A2)。然後按下Enter。
  3. 複製公式： 將B2儲存格的公式，向下拖曳複製到所有你原始資料所在的列。
  4. 比較原始資料與TRIM結果： 現在，B欄的資料是已經被TRIM函數處理過的。你可以仔細觀察A欄（原始資料）和B欄（TRIM處理後）的內容。如果A欄的資料在TRIM後，內容看起來有明顯變化（例如，原本開頭或結尾有多餘的空格，現在消失了），或者A欄的資料跟B欄的資料不一樣，那A欄的對應儲存格很可能就含有多餘的空格。
  5. 篩選出差異：
    • 方法 A： 複製A欄的資料，貼到另一個新的表格區域。然後在B欄的TRIM結果上面，選擇「資料」>「篩選」。
    • 方法 B： 直接在B欄新增一個篩選欄，然後比較A欄和B欄的內容。
  6. 使用「篩選」進階功能：

    在B欄（TRIM處理後的欄位）啟用篩選後，你可以進行以下操作：

    • 篩選出與原始欄位不同的資料： 雖然Excel沒有直接篩選「不等於」的功能，但你可以透過一些間接方法。一個簡單的做法是，在C欄新增一個比較欄，輸入公式 =IF(A2=B2,"相同","不同")，然後篩選出C欄為「不同」的項目。這就直接顯示了原始資料與TRIM處理後有差異的儲存格。
    • 篩選出空白的TRIM結果： 如果你發現A欄的資料，經過TRIM後變成空值（B欄顯示為空白），那代表A欄的儲存格內，可能原本就只有空格，或是包含一些Excel無法辨識的特殊字元。

專業建議： TRIM函數對於清理前後綴空格非常有效。當你發現某些資料怎麼刪除空格都刪不乾淨，試試TRIM函數，它能幫你找出那些「頑固」的空格。

處理那些「看不見」的殺手：非中斷空格和其他特殊字元

前面兩種方法，主要針對的是一般空格。但如果你的資料來源是網頁複製、或是從某些特殊格式的系統匯入，你可能會遇到「非中斷空格」(Non-breaking space, nbsp)，或是一些你根本不知道從何而來的特殊字元。這些字元，肉眼看起來可能就是個空格，但它在Excel中，卻是一個實實在在的字元！

要處理它們，我們需要稍微進階一點的技巧，利用「SUBSTITUTE」函數來進行替換。

方法三：利用「SUBSTITUTE」函數替換特殊字元

SUBSTITUTE函數的功能是將一個字串中的指定字元，替換成另一個字元。這裡的關鍵是，我們需要知道那個特殊字元的「真實身份」，也就是它的字元碼。

第一步：找出特殊字元的字元碼

如果你懷疑有非中斷空格，它的字元碼通常是 160。如果是其他特殊字元，你可以透過CHAR()函數來嘗試找出：

  1. 在一個新的儲存格輸入公式：=CODE(MID(A2,ROW(A2)-ROW(A$2)+1,1)) （假設A2是你的原始資料，並且你在一列上拖曳公式）。這個公式會逐一讀取A欄每個儲存格中的第一個字元，然後顯示它的字元碼。
  2. 你可以觀察出現的字元碼，看看是否有平常看不到的特殊數字。

第二步：利用SUBSTITUTE替換

假設我們已經知道，你的資料中有字元碼為 160 的非中斷空格，我們要將它替換成一般的空格，或是直接刪除。

  1. 替換成一般空格： 在輔助欄位（例如B欄），輸入公式：=SUBSTITUTE(A2,CHAR(160)," ")。這個公式的意思是，在A2儲存格的文字中，找到所有字元碼為160的字元（也就是非中斷空格），並將它們全部替換成一個一般的空格（” “）。
  2. 直接刪除： 如果你希望直接將它移除，不想留下任何空格，則輸入：=SUBSTITUTE(A2,CHAR(160),"")。
  3. 組合使用： 很多時候，你可能同時有一般空格和非中斷空格。這時候，你可以將TRIM和SUBSTITUTE組合起來。例如，先用SUBSTITUTE移除非中斷空格，再用TRIM移除前後綴的一般空格：=TRIM(SUBSTITUTE(A2,CHAR(160),""))。
  4. 應用並篩選： 將這個組合公式向下拖曳複製。然後，你可以像前面一樣，比較原始資料和處理後的資料，或者篩選出與原始資料不同的項目。

實際應用場景： 我曾經處理過一個從某個舊系統匯出的客戶名單，裡面很多名字後面都帶有一個奇怪的符號，肉眼根本看不出來。但一做篩選，就發現很多名字被分開了！後來仔細檢查，才發現那個符號是字元碼 194 的特殊字元。透過 =SUBSTITUTE(A2,CHAR(194),"")，才把所有資料整合好。

進一步優化：利用「條件式格式設定」標記出有空格的儲存格

有時候，我們不一定需要立即刪除空格，而是想先「標記」出來，方便我們之後再做處理。這時候，「條件式格式設定」就是你的好幫手！

  1. 選取範圍： 選取你想要設定條件式格式的資料範圍。
  2. 開啟「條件式格式設定」： 點選「常用」標籤頁，在「樣式」群組中，選擇「條件式格式設定」。
  3. 新增規則： 選擇「新增規則」。
  4. 選擇規則類型： 在「新增格式規則」對話框中，選擇「使用公式來決定要套用格式的儲存格」。
  5. 輸入公式： 這邊有幾個常用的公式，可以讓你標記出不同類型的空格：
    • 標記含有任何形式空格（包含特殊字元）的儲存格： 假設你的資料從A2開始，你可以輸入公式：=LEN(TRIM(A2))=0。這個公式的意思是，如果一個儲存格經過TRIM處理後長度為0，代表它原本就只有空格，或是空的。但這個方法無法區分「真正空白」和「只有特殊字元」的儲存格。

      更精確一點，如果你想找出「有額外空格」的儲存格，可以這樣比較：=A2<>TRIM(A2)。這個公式會檢查A2儲存格與其TRIM處理後的值是否不同，如果不同，代表A2儲存格含有前後綴的空格。

      如果想找出「含有特定特殊字元」（例如字元碼160）的儲存格，則可以這樣寫：=ISNUMBER(FIND(CHAR(160),A2))。
    • 標記含有「純粹空格」的儲存格： 如果你只想標記那些「只包含一個或多個普通空格」的儲存格，但不是其他特殊字元，可以使用：=A2=" " （請注意，這裡的空格是按鍵盤上的空白鍵輸入的）。
  6. 設定格式： 點擊「格式」按鈕，選擇你想要的標記方式，例如設定背景填滿顏色、字體顏色等。
  7. 確定： 點擊「確定」關閉對話框。

我的小提醒： 條件式格式設定非常適合用來「視覺化」你的資料問題。當你設定好之後，所有包含你設定條件的儲存格，都會自動變色，讓你一目了然，大大提高資料檢查的效率！

綜合應用：實際案例分析

讓我們來模擬一個常見的場景：你從公司 ERP 系統匯出了一份客戶名單，裡面有「客戶名稱」和「聯絡電話」兩欄。你發現，當你想用「篩選」功能，只看某些特定客戶時，總是篩選不完整，有些應該出現的客戶卻被漏掉了。

問題分析：

最可能的原因，就是「客戶名稱」這一欄，可能包含了各種形式的空格。例如：

  • 客戶名稱後面多了一個「 」
  • 客戶名稱前面多了一個「 」
  • 客戶名稱之間有多個「 」
  • 甚至，有些名稱可能根本是「 」（只有空格）

解決步驟：

  1. 判斷空格類型： 首先，對於「客戶名稱」欄，我們可以用前面學到的方法來檢查。
  2. 使用TRIM和SUBSTITUTE組合： 在旁邊新增一個輔助欄（例如 C 欄），輸入公式：=TRIM(SUBSTITUTE(A2,CHAR(160),"")) （假設客戶名稱在 A 欄，從 A2 開始）。這個公式會先移除非中斷空格，然後移除前後綴的一般空格。
  3. 找出「純空格」的客戶： 如果你懷疑有些客戶名稱欄位，可能根本就只有空格，那可以利用篩選來找出。在輔助欄 C 欄篩選出來的結果中，如果出現空白，那代表原始的 A 欄客戶名稱，可能只包含空格。
  4. 判斷差異並篩選： 你可以新增一個比較欄（例如 D 欄），輸入公式：=IF(A2=C2,"相同","不同")。然後篩選出 D 欄顯示為「不同」的項目。這些就是原始資料含有你想要移除的空格的客戶名稱。
  5. 執行清理：
    • 對於那些「不同」的項目，你可以複製 C 欄的內容，貼回 A 欄，這樣就完成了空格的清理。
    • 對於那些，TRIM後是空白的，你可以根據公司規定，選擇刪除該列，或將其標示為「待確認」。
  6. 重新篩選： 在清理完畢的「客戶名稱」欄位（A 欄）上，重新進行篩選。你應該會發現，所有你想要的客戶名稱，都能被精準地篩選出來了！

我的建議： 資料清理的過程，就像是偵探辦案一樣，一步一步抽絲剝繭。不要害怕嘗試不同的函數和方法，組合運用才是王道！

常見問題與詳細解答

在使用Excel篩選空格的過程中，你可能還會遇到一些其他的疑問，讓我們來一一解答。

Q1：為什麼我用Ctrl+H刪除了空格，但篩選出來的結果還是怪怪的？

A1： 這很有可能是因為你刪除的，只是「一般空格」。如我們前面提到的，Excel中的「空格」種類很多，可能還包含「非中斷空格」(nbsp) 或其他特殊字元。這些字元，即使肉眼看起來像空格，但Excel卻將它們視為不同的字元。你看到的「刪除空格」，其實只是清除了「一般空格」。

詳細解答： 要解決這個問題，你需要進一步檢查你的資料，看是否含有非中斷空格或其他特殊字元。最好的方法是結合使用 TRIM 和 SUBSTITUTE 函數。例如，你可以先在輔助欄位輸入 =SUBSTITUTE(A2,CHAR(160),"")，將非中斷空格替換成一般空格，然後再將這個公式的結果，套用 TRIM 函數，變成 =TRIM(SUBSTITUTE(A2,CHAR(160),""))。這樣，就能一次性處理掉大部分的隱藏空格和前後綴空格。

另外，也有可能是你的資料格式問題。例如，數字看起來像文字，或是日期格式不一致。這些情況雖然不是直接的「空格」問題，但在篩選時也可能造成混淆。

Q2：如何在Excel中判斷一個儲存格是否「真的」是空的，而不是只包含空格？

A2： 這是一個很常見的誤解。在Excel中，一個「看起來」是空的儲存格，可能實際上是：

  • 真正空白的儲存格： 裡面什麼都沒有。
  • 只包含空格的儲存格： 裡面可能是一個或多個「一般空格」，或甚至是非中斷空格或其他特殊字元。

詳細解答： 要區分這兩者，你可以使用 LEN 函數。LEN(A2) 會回傳儲存格 A2 的長度（字元數量）。

  • 如果 LEN(A2) 回傳 0，那代表這個儲存格是「真正空白」的。
  • 如果 LEN(A2) 回傳大於 0，但你肉眼看起來是空的，那代表它裡面包含空格或特殊字元。

你可以用這個邏輯來篩選。例如，你可以新增一個輔助欄，輸入公式 =LEN(A2)，然後篩選出長度大於 0 的項目，這些就是「看似空白但含有字元」的儲存格。

或者，結合 TRIM 函數，使用 =LEN(TRIM(A2))。如果這個公式回傳 0，則代表該儲存格經過 TRIM 處理後是空的，也就是說，原本的儲存格可能只包含空格或空白字元。

Q3：我需要篩選出所有「只包含一個單字」，但該單字後面有一個空格的儲存格，該怎麼做？

A3： 這個需求比較具體，但我們可以用組合公式來達成。假設你的資料在 A 欄，從 A2 開始。

詳細解答： 我們需要先定義「一個單字」，以及「該單字後面有一個空格」。

一個常見的做法是，先利用 TRIM 函數移除前後綴的空格，然後再檢查處理後的字串，以及原字串是否不同。但是，要精確判斷「單字後面有一個空格」，還需要一些額外的邏輯。

一個可能的思路是，我們先用 TRIM 處理，然後檢查處理後的字串長度，以及原字串和處理後字串的差異。

假設你要判斷 A2 儲存格是否為「單字 + 空格」的形式。你可以考慮這樣的邏輯：

  1. 移除前後綴空格： TRIM(A2)
  2. 判斷總長度： LEN(A2)
  3. 判斷TRIM後的長度： LEN(TRIM(A2))

如果 LEN(A2) 減去 LEN(TRIM(A2)) 等於 1，且 LEN(TRIM(A2)) 大於 0，這可能代表 A2 儲存格的確是「單字 + 一個空格」的組合（假設TRIM只移除前後綴的空格，且沒有多餘的內部空格）。

更精確一點的條件，可能是：

  • LEN(A2) - LEN(TRIM(A2)) = 1 （總共多了一個字元，且這個字元應該是TRIM移除的）
  • LEN(TRIM(A2)) > 0 （TRIM後還有內容，確保不是純空格）
  • NOT(ISNUMBER(FIND(" ",TRIM(A2)))) （TRIM後的字串不包含空格，確保是「單字」）

所以，你可以寫一個較為複雜的公式來判斷，並利用條件式格式設定來標記。例如，在條件式格式設定中使用公式：
=AND(LEN(A2)-LEN(TRIM(A2))=1,LEN(TRIM(A2))>0,NOT(ISNUMBER(FIND(" ",TRIM(A2)))))

這個公式的意思是：儲存格 A2 的原始長度，減去移除前後綴空格後的長度，剛好等於 1（代表只有一個被移除的空格），並且移除空格後還有內容，而且移除空格後的內容不包含任何空格。這樣就能比較精準地篩選出「單字 + 一個空格」的儲存格。

Q4：我從網頁複製的資料，有些名字之間會有奇怪的符號，如何清理？

A4： 這通常是網頁上的「非中斷空格」 (Non-breaking space, nbsp) 或其他 HTML 實體符號。這些符號在瀏覽器中顯示為空格，但在Excel中卻是獨立的字元。

詳細解答： 如同前面方法三所述，你需要利用 SUBSTITUTE 函數來處理。非中斷空格的字元碼是 160。所以，你可以使用以下公式來替換：

  • =SUBSTITUTE(A2,CHAR(160),"")： 將非中斷空格直接刪除。
  • =SUBSTITUTE(A2,CHAR(160)," ")： 將非中斷空格替換成一般空格。

你也可以結合 TRIM 函數，先替換掉特殊空格，再清理前後綴空格：=TRIM(SUBSTITUTE(A2,CHAR(160),""))。

如果還有其他奇怪的符號，你需要找出它們的字元碼，然後套用相同的 SUBSTITUTE 函數進行替換。有時候，可能需要多次套用 SUBSTITUTE 來處理多種不同的特殊字元。

結論：告別「空格」困擾，讓Excel資料處理更精準！

怎麼樣？看完了這麼多關於Excel有空格如何篩選的技巧，是不是覺得這個問題其實沒有那麼可怕了？「空格」雖然隱藏在數據的細節裡，但只要掌握了正確的方法和工具，我們就能像個資料清理大師一樣，將它們一一揪出來，讓你的Excel表格乾淨、整潔，並且能夠進行精準的篩選和分析。

從簡單的 Ctrl + H 尋找與取代，到靈活運用 TRIM、SUBSTITUTE 函數，再搭配 **條件式格式設定** 的視覺化提示，這些都是你在處理Excel資料時，不可或缺的實用技巧。記住，資料的準確性，往往就藏在這些看似微不足道的細節裡。多花一點時間，把這些「空格」處理乾淨，絕對能讓你事半功倍，避免後續的無窮無盡的麻煩！

希望今天的分享，對你有真正的幫助！如果你在實踐過程中遇到任何問題，也歡迎隨時提出來一起討論唷！

Excel有空格如何篩選

發佈留言