Excel 樞紐分析表列標籤時有重複資料，快速整理有問題的資料

最近這幾年常常處理許多的大量資料，最常使用的是 excel 樞紐分析表與排序的方式，但是從網路下載的資料常常是有很多問題，需要整理與清除的，尤其是發現有重複的資料時，常常做到一半就停止下來想辦法去找出產生重複資料的問題，不是使用函數的方式就是清除前後多餘空白的字元，但有時候清除整理之後還是出現，怎麼辦? 我先列舉幾個可以幾個整理重複資料的方式

• 手動清除字元或字串前後有空格
• 複製相同格式到有問題的儲存格
• 使用TRIM與SUBSTITUE函數自動去除多餘空白字元
• copy有問題excel的資料到google data sheet處理

以上這些只是列舉我曾經處理過的，有些方式可以整理出有重複的資料，但有些隱藏的未知資料是不容易發現的，後來我想到最快速的方法

有問題資料的xlsx檔 → 另存csv → 發現字串後面部分有問號的字元(清除) → 使用excel開啟確認正常 → 另存xlsx檔

步驟如下：

怎麼回事? 有些 excel 使用樞紐分析表工具時分析資料時，在列標籤顯示的資料裡面有重複的資料計數，檢查資料有些是正常，看不出來，有些字串後面有空白字元，清除後還是一樣

STEP 1 另存csv檔

原本的excel檔案原始資料可能隱藏些字元或不知道的東西，另存成csv(逗號分隔值)，可以用文字檔開啟的比較容易發現是什麼，例如我把csv檔存到我的桌面位置

 

STEP 2 使用記事本開啟csv檔

用滑鼠右鍵開啟csv檔案，如果直接點選檔案可能會被Microsoft Excel 打開，可以用記事本或像Notepad++可以開啟文字檔的軟體打開

STEP 3 用記事本檢查檔案

下方的圖片姓名欄位我把它清除(因為個資法的關係)，此時在記事本裡發現有些字串後面會帶有問號 ? 可能是影響之前excel 樞紐分析表重複資料，但資料看起來是一樣的東西，其實隱藏某些東西，在excel是看不到的

 

STEP 4 用取代功能清除奇怪的字元

使用記事本內的取代功能，把字串後面的奇怪字元，例如我發現的問號? 把它清除掉，尋找目標 ?  取代為空值(不用填)，最後按下全部取代

 

此時有問題的問號? 已經全部清除乾淨了，可以儲存檔案。

 

STEP 5 csv檔案用excel開啟

此時已整理好的csv檔案用excel開啟檢查看看，再一次用樞紐分析表工具試試看有無問題

 

STEP 6 再次用樞紐分析表分析

再次的檢查看看列標籤有無重複的資料，發現已經沒有重複的資料了，計數的也已經自動加總正常了

 

STEP 7 csv檔再另存成xlsx檔

確認資料沒有問題之後，再另存xlsx檔案，這個資料就恢復比較正常的可以整理狀態了!