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



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

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

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

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

步驟如下:

excel樞紐分析表排序後發現有重複資料

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

STEP 1 另存csv檔

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

先將有重複資料的excel檔案另外存成csv檔

 

STEP 2 使用記事本開啟csv檔

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

csv檔案用記事本打開

STEP 3 用記事本檢查檔案

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

csv檔案裡發現原本的excel檔案的部分字串後面有多問號

 

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

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

用取代方式把問號清除

 

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

字串後面帶有問號符號已清除乾淨了

 

STEP 5 csv檔案用excel開啟

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

csv檔案再用excel來開啟

 

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

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

直接用excel樞紐分析表計數後發現重複資料不見了

 

STEP 7 csv檔再另存成xlsx檔

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

 

資料比較乾淨的csv檔案再另存成excel的xlsx檔案

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。