每位資料分析師在開啟新電子表格時，都會有那種沮喪的感覺，看到格式錯誤的數字、不一致的條目、隨機的空白單元格以及到處都是重複項！清洗這些資料對於開始工作至關重要。無論您是要整理季度報告、進行消費者行為分析還是趨勢預測，解讀的質量都取決於您事先清洗資料的程度。在 Excel 中清洗資料不僅僅是一個技術步驟；它是將原始資訊轉化為企業敏銳洞察的基礎。在本文中，我將向您解釋什麼是資料清理，並指導您如何在 Excel 中刪除重複項並清洗資料。
Excel中的資料清洗是什麼？
Excel 表格中的資料清洗包括識別和修復錯誤、消除不一致之處以及刪除重複和不準確的資料。使用 Excel 的內建函式和工具檢查原始資料以識別和處理異常值（例如重複條目和缺失值），可以確保結果更準確、更可靠。
乾淨資料有哪些特點？
乾淨資料可以根據以下特點來識別：
- 準確性：資料應重現真實值，不留任何錯誤空間。
- 完整性：所有必要的值都存在，缺失值很少。
- 一致性：相似的資料在整個資料集中遵循相同的格式。
- 統一性：計量單位、縮寫和命名約定應標準化。
- 唯一性：資料集中不應存在不必要的重複記錄。
- 有效性：資料必須在可接受的範圍內並符合定義的規則。
- 時效性：資料應保持最新並與分析時間相關。
如何在Excel表格中清洗資料？
在本節中，我們將探討一些用於清洗 Excel 表格中資料的標準技巧：
1. 刪除重複項
重複記錄會嚴重扭曲分析結果，導致對交易量或頻率產生錯誤的認知。假設同一位客戶的銷售額被重複統計，這將導致整個資料集出現差異。因此，為了確保資料分析的準確性，刪除重複項至關重要。
刪除重複項的步驟
1. 選擇要刪除重複項的資料範圍（包括標題）。
2. 轉到選單欄中的“資料”選項卡。
3. 點選資料清理並選擇刪除重複項。
4. 在這裡，您將看到一個彈出視窗，用於選擇要從中刪除重複值的列。您可以選擇全部或僅選擇某些特定的列。
4. 點選“刪除重複項”即可刪除重複項。
2. 格式標準化
格式不一致會阻礙資料分析。即使是像排序這樣基本的任務，如果日期、數字或文字使用不同的格式或約定，也可能會失敗，因此有必要對資料格式進行標準化。
格式標準化步驟
1. 選擇需要標準化的列或所需資料範圍，例如在本例中，我們將選擇包含日期的列。
2. 在選單欄中，轉到“格式”，然後選擇“數字”。
3. 從列表中選擇您想要遵循的格式。在這裡，我們選擇“日期”，它會將選定的資料轉換為該格式。
4. 您還可以選擇其他格式選項。
3. 清理文字資料
所有文字分析都始於清理。原始文字資料經常包含不一致的內容，例如多餘的空格、不恰當的大小寫、拼寫錯誤或特殊符號。這可能會干擾分組、過濾或解釋。如果沒有充分的清理，即使是最先進的方法或模型也難以得出有價值的結果。
清理文字資料的步驟
讓我們考慮這個資料集：
1. 使用 PROPER 函式將每個單詞的首字母大寫。該函式的公式為：=PROPER(cell)
2. 使用 TRIM 函式刪除多餘的空格。該公式的寫法為：=TRIM(cell) 
3. 使用“LOWER”和“UPPER”函式將文字轉換為全部大寫或小寫格式。該公式的寫法為：=LOWER(cell) 或 =UPPER(cell)
4. 我們可以結合使用這兩個函式來更全面地清理資料。該公式的寫法為：=FIRST FUNCTION(SECOND FUNCTION(cell))
4. 填充缺失值
在某些情況下，您可能會看到缺失值，這些值可能會在分析中造成盲點。用一些隨機值填充資料並非解決方案，但有幾種方法可以適當地處理這些缺失值。
填充缺失值的步驟
考慮以下資料集
1. 您可以使用 AVERAGE 公式輕鬆填充缺失的數值。這將新增計算出的平均值，該平均值是現有範圍內更現實的值。公式可以寫成：=AVERGAGE(min,max)
2. 對於分類資料，您可以在適當的地方使用邏輯假設，例如“不可用”或“未知”。
3. 您還可以使用“智慧填充”來檢測模式，然後填充缺失值。
5. 驗證資料
資料驗證是控制和設定單元格中可輸入和不可輸入內容規則的過程。使用資料驗證來預防錯誤比事後修復錯誤更容易。
資料驗證步驟
1. 選擇需要驗證資料的行或列。
2. 轉到選單欄上的“資料”選項卡，然後選擇“資料驗證”。
3. 在驗證規則下選擇具體的驗證條件，例如整數、日期、列表等。
4. 然後設定可在單元格中新增的具體引數或各種選項，例如特定格式的日期或時間、部門名稱等。
5. 設定完成後，您的資料將得到驗證。
6. 應用條件格式
透過高亮顯示符合特定條件的值，一些視覺提示可以幫助我們快速識別資料中的潛在問題。對於資料清理，它們基本上可以高亮顯示重複值、標記異常值、識別缺失值以及標記包含錯誤公式的單元格。
條件格式步驟
1. 選擇要清理的資料範圍。
2. 轉到選單欄上的“格式”選項卡，然後選擇“條件格式”選項。
4. 選擇要應用的規則型別（高亮顯示單元格規則、頂部/底部規則等）。
5. 然後定義格式樣式和所需條件。例如，這裡我應用的是“以紅色高亮顯示指定列中大於 2000 的單元格”。
6. 設定完成後，點選“完成”。
7. Power Query
Microsoft Excel 較新版本中新增了一種名為“獲取和轉換”的高階資料清理方法。該方法可用於更復雜的資料清理。它提供了強大的選項，可在將資料匯入電子表格之前對其進行清理和重塑。
如果您使用的是 Excel 2016 或更高版本，則內建了 Power Query 功能。或者，您也可以將其作為外掛新增到 Excel 2010 及後續版本中。
使用 Power Query 的步驟
1. 點選 Power Query 選項卡中的“獲取資料”按鈕，您將看到一個下拉選單，其中包含多種檔案型別，例如 CSV 檔案、網頁等。
2. 選擇您的資料來源。
Source: Power Query
3. 選擇資料來源後，Excel 會根據資料來源型別提示需要特定資訊的連線。對於檔案等資料來源，系統會要求您提供檔案路徑（瀏覽到該位置）。另一方面，對於 Web 資料來源，您需要輸入有效的 URL。
4. 指定要載入的資料來源後，可能會出現以下選項。系統可能會要求您選擇工作表、表格或區域，然後輸入憑據進行授權。
5. 請務必仔細檢查列，並僅選擇您真正需要的列。載入或轉換資料，使其顯示在 Power Query 編輯器中，以便進行進一步的清理。
Source: Power Query
6. 您甚至可以使用 Power Query 根據需要篩選資料。例如，您可以按照以下步驟處理缺失資料或刪除列：
- 轉到 Power Query 編輯視窗中的“主頁”選項卡。
- 選擇要處理的資料。
- 從選單中選擇“刪除列”選項，即可獲得輸出。
8. 查詢和替換功能
查詢和替換功能是一種更簡便的方法，可以在不造成任何中斷的情況下對大量資料進行一致的更改。
使用查詢和替換功能的步驟
1. 從選單欄中選擇“編輯”，然後點選“查詢和替換”。或者，您也可以輕鬆使用快捷鍵 Ctrl+H。
2. 輸入要查詢的文字，然後輸入替換文字。
3. 您可以使用“匹配大小寫”等選項來提高精度，如上圖所示。
4. 點選“替換”可單獨控制更改，或點選“全部替換”可一次性更改所有出現的文字。
5. 點選“完成”，即可獲得輸出。
9. 拆分帶分隔符的資料
有時，資料可能會將多條資訊擠在一個單元格中，因此拆分這些資料可以更輕鬆地進行分析。
拆分帶分隔符的資料的步驟
1. 首先，選擇包含合併資料的列或行。
2. 轉到選單欄上的“資料”選項卡，然後選擇“拆分文字到列”。
3. 輸入分隔符（用於分隔要拆分的單詞的值或字元）並預覽結果。
在此示例中，我們使用了“-”，它會根據該分隔符拆分列。但是，如果存在多個分隔符，例如“-”和“，”則需要在“自定義分隔符”彈出視窗中指定要使用的分隔符。
10. 提取字首和字尾
處理各種資料時，可能會遇到只需要每個單元格中部分資料的情況，例如從電話號碼中提取區號，或從電子郵件地址中獲取域名。這時，您可以使用提取函式。
提取字首和字尾的步驟
我們來考慮以下資料集
1. 要從開頭提取字元，我們可以使用 LEFT 函式。公式如下：=LEFT(text, FIND(character, text) – 1)
此處的 FIND 函式用於查詢單元格中 @ 的位置，而 LEFT 函式則提取 @ 之前的所有字元。
2. 要從結尾提取字元，我們可以使用 RIGHT 函式。公式如下：=RIGHT(text, LEN(text) – FIND(delimiter, text))
此處的 FIND 函式用於定位分隔國家/地區程式碼和數字的連字元，而 LEN 函式則用於計算字串的總長度。整個公式將返回連字元後的子字串。
要從中間提取字元，我們可以使用 MID 函式。此函式的公式為： =MID(text, FIND(“-“, text) + 1, FIND(“-“, text, FIND(“-“, text) + 1) – FIND(“-“, text) – 1)
FIND(“-“ D2) 函式返回第一個連字元的位置。由於我們想提取第一個連字元之後的資訊，因此新增了“+1”。FIND(“-“, D2, FIND(“-“, D2) 返回第二個連字元的位置。由於我們想提取第二個連字元之前的文字，因此新增了“-1”。MID(D2, starts_pos, num_chars) 函式從第一個連字元之後開始提取，直到出現第二個連字元為止。
小結
清理資料不僅是技術上的必需，更是商業智慧的先決條件。它為構建和指導價值數百萬美元的商業決策奠定了基礎。雖然在 Excel 上清理資料是一項艱鉅的任務，但我相信，有了本文討論的方法和公式，這項工作會變得輕鬆得多。
掌握 Excel 資料清理的技巧，將使您比普通的資料錄入員更上一層樓。它使您成為值得信賴的顧問，其分析將成為公司戰略發展的一部分。現在，要做到這一點，您只需在 Excel 上練習這些資料清理解決方案，並不斷提升自己的技能。
評論留言