使用文字函式清理資料

---

---

從不同來源獲得的資料可能並非以適合分析的形式存在。在本節中，您將瞭解如何準備以文字形式存在的資料以進行分析。

首先，您需要清理資料。資料清理包括從文字中刪除不需要的字元。接下來，您需要將資料結構化為進一步分析所需的格式。您可以透過以下方式做到這一點：

• 使用文字函式查詢所需的文字模式。
• 從文字中提取資料值。
• 使用文字函式格式化資料。
• 使用文字函式執行資料操作。

從文字中刪除不需要的字元

當您從其他應用程式匯入資料時，它可能包含不可列印字元和/或多餘空格。多餘的空格可以是：

• 前導空格，和/或
• 單詞之間的額外空格。

如果您對這樣的資料進行排序或分析，將會得到錯誤的結果。

請考慮以下示例：

這是您獲得的產品資訊原始資料，包含產品 ID、產品描述和價格。字元“|”分隔每一行中的欄位。

當您將此資料匯入 Excel 工作表時，它看起來如下所示：

如您所見，所有資料都在一列中。您需要構造此資料以執行資料分析。但是，首先您需要清理資料。

您需要刪除資料中可能存在的任何不可列印字元和多餘空格。您可以為此目的使用 CLEAN 函式和 TRIM 函式。

• 選擇單元格 C3 – C11。
• 鍵入 =TRIM (CLEAN (B3))，然後按 CTRL + Enter。

公式填充到單元格 C3 – C11 中。

結果將如下所示：

使用文字函式查詢所需的文字模式

要構造您的資料，您可能需要進行某些基於文字模式匹配的操作，您可以根據這些模式提取資料值。一些為此目的有用的文字函式是：

從文字中提取資料值

您需要從文字中提取所需的資料才能構造相同的資料。在上面的示例中，假設您需要將資料放置在三列中 - ProductID、Product_Description 和 Price。

您可以透過以下方式之一提取資料：

• 使用“將文字轉換為列”嚮導提取資料值
• 使用文字函式提取資料值
• 使用快速填充提取資料值

使用“將文字轉換為列”嚮導提取資料值

如果您要提取的資料欄位：

• 由某個字元分隔，或
• 在列中對齊，欄位之間有空格。

在上面的示例中，欄位由字元“|”分隔。因此，您可以使用“將文字轉換為列”嚮導。

• 選擇資料。

• 複製並貼上到同一位置。否則，“將文字轉換為列”將把函式而不是資料本身作為輸入。

• 選擇資料。

• 在功能區的“資料”選項卡上的“資料工具”組中，單擊“分列”。

**步驟 1** - “將文字轉換為列”嚮導 - 步驟 1/3 出現。

• 選擇“分隔符號”。
• 單擊“下一步”。

**步驟 2** - “將文字轉換為列”嚮導 - 步驟 2/3 出現。

• 在“分隔符”下，選擇“其他”。

• 在“其他”旁邊的框中，鍵入字元“|”。

• 單擊“下一步”。

**步驟 3** - “將文字轉換為列”嚮導 - 步驟 3/3 出現。

在此螢幕上，您可以在嚮導中選擇資料的每一列，併為該列設定格式。

• 對於“目標”，選擇單元格 D3。

• 您可以單擊“高階”，並在出現的“高階文字匯入設定”對話方塊中設定“小數點分隔符”和“千位分隔符”。

• 單擊“完成”。

您轉換為列的資料將顯示在三列中 - D、E 和 F。

• 將列標題命名為 ProductID、Product_Description 和 Price。

使用文字函式提取資料值

假設您的資料中的欄位既沒有由字元分隔，也沒有在列中對齊，欄位之間有空格，您可以使用文字函式提取資料值。即使在欄位已分隔的情況下，您仍然可以使用文字函式提取資料。

一些為此目的有用的文字函式是：

您還可以根據手頭的資料組合兩個或多個這些文字函式，以提取所需的資料值。例如，使用 LEFT、RIGHT 和 VALUE 函式的組合，或使用 FIND、LEFT、LEN 和 MID 函式的組合。

在上面的示例中，

• 第一個 | 左側的所有字元都構成名稱 ProductID。

• 第二個 | 右側的所有字元都構成名稱 Price。

• 第一個 | 和第二個 | 之間的所有字元都構成名稱 Product_Description。

• 每個 | 前後都有一個空格。

觀察此資訊，您可以按照以下步驟提取資料值：

• 查詢第一個 | 的位置 - **第一個 | 位置**

  • 您可以使用 FIND 函式

• 查詢第二個 | 的位置 - **第二個 | 位置**

  • 您可以再次使用 FIND 函式

• 文字的開始到（**第一個 | 位置** – 2）個字元構成 ProductID

  • 您可以使用 LEFT 函式

• 文字的（**第一個 | 位置** + 2）到（**第二個 | 位置** - 2）個字元構成 Product_Description

  • 您可以使用 MID 函式

• 文字的（**第二個 | 位置** + 2）到結尾的字元構成 Price

  • 您可以使用 RIGHT 函式

結果將如下所示：

您可以觀察到價格列中的值為文字值。要對這些值執行計算，您必須設定相應單元格的格式。您可以檢視下面給出的部分以瞭解文字格式設定。

使用快速填充提取資料值

使用 Excel **快速填充** 是從文字中提取資料值的另一種方法。但是，這僅在 Excel 能夠在資料中找到模式時才有效。

**步驟 1** - 在資料旁邊建立三列用於 ProductID、Product_Description 和 Price。

**步驟 2** - 從 B3 複製並貼上 C3、D3 和 E3 的值。

**步驟 3** - 選擇單元格 C3，然後在“資料”選項卡上的“資料工具”組中單擊“快速填充”。ProductID 的所有值都將填充。

**步驟 4** - 對 Product_Description 和 Price 重複上述步驟。資料已填充。

使用文字函式格式化資料

Excel 有幾個內建的文字函式，您可以使用它們來格式化包含文字的資料。這些包括：

**根據您的需要格式化文字的函式**：

**將數字轉換為文字和/或格式化數字的函式**：

**將文字轉換為數字的函式**：

使用文字函式執行資料操作

您可能需要對資料執行某些文字操作。例如，如果組織中員工的登入 ID 更改為新格式，則根據格式更改，可能需要進行文字替換。

以下文字函式可幫助您對包含文字的資料執行文字操作：