拼錯的字、難以處理的結尾空格、不需要的前置詞、不適當的大小寫，以及非列印字元，都會讓人留下不好的第一印象。 而這還不是讓資料變雜亂之方法的完整清單。 捲起衣袖。 現在正是使用 Microsoft Excel 對您的工作表進行一些主要大掃除的時候了。
您無法永遠掌控從外部資料來源匯入的資料格式和類型，例如資料庫、文字檔或網頁。 在您分析資料之前，通常需要先清理資料。 幸好，Excel 有許多功能可以協助您以想要的精確格式取得資料。 有時候，工作非常簡單，而且有特定功能可以替您完成工作。 例如，您可以輕鬆使用拼字檢查程式，清理內含註解或描述的欄中拼錯的字。 或者，如果您想要移除重複的列，您可以使用 [移除重複項] 對話方塊來快速進行這項操作。
有時候，您可能需要操作一個或多個欄，透過使用公式即可將匯入的值轉換為新的值。 例如，如果您想要移除結尾空格，您可以建立新的欄以使用公式清理資料、向下填滿新欄、將新欄的公式轉換為值，然後移除原始欄。
清理資料的基本步驟如下：
-
從外部資料來源匯入資料。
-
在個別活頁簿中建立原始資料的備份。
-
確認資料是使用欄和列的表格格式，其中：每欄中的資料相似，可顯示所有欄和列，而且範圍內沒有空白的列。 為獲得最佳結果，請使用 Excel 表格。
-
首先執行不需要操作欄的工作，例如拼字檢查，或使用 [尋找及取代] 對話方塊。
-
接下來，執行需要操作欄的工作。 操作欄的一般步驟如下：
-
在需要清理的原始欄 (A) 旁邊插入一個新欄 (B)。
-
新增公式，該公式將會轉換新欄 (B) 頂端的資料。
-
在新欄 (B) 中向下填滿公式。 在 Excel 表格中，向下填滿值即會自動建立計算結果欄。
-
選取新欄 (B)，複製該欄，然後以值形式貼到新欄 (B)。
-
移除原始欄 (A)，這樣會將新欄從 B 轉換為 A。
-
若要定期清理相同的資料來源，可考慮錄製巨集或撰寫程式碼以自動執行整個過程。 還有一些列於 [第三方提供者] 區段中由第三方廠商編寫的外部增益集，如果您沒有時間或資源來自行自動化程序，則可以考慮使用這些增益集。
|
其他資訊 |
描述 |
|---|---|
|
顯示如何使用 [填滿] 命令。 |
|
|
顯示如何建立 Excel 表格，以及新增或刪除欄或計算結果欄。 |
|
|
顯示使用巨集自動化重複工作的多種方式。 |
您不只可以使用拼字檢查程式尋找拼錯的字，還能透過將這些值 (例如產品或公司名稱) 新增到自訂字典來尋找使用不一致的值。
|
其他資訊 |
描述 |
|---|---|
|
顯示如何修正工作表中拼錯的字。 |
|
|
說明如何使用自訂字典。 |
當您匯入資料時，重複的列是常見問題。 在移除重複值之前，最好先篩選唯一值，以確認是您想要的結果。
|
其他資訊 |
描述 |
|---|---|
|
顯示兩個密切相關的程序：如何篩選唯一列和移除重複的列。 |
您可能想要移除一般前置字串 (例如後面接著冒號和空格的標籤)，或後置字元 (例如字串尾端過時或不必要的附加字詞)。 您可以尋找該文字的執行個體，然後以沒有任何文字或其他文字將其取代來執行此操作。
|
其他資訊 |
描述 |
|---|---|
|
顯示如何使用 [尋找] 命令和多個函數來尋找文字。 |
|
|
顯示如何使用 [取代] 命令和多個函數來移除文字。 |
|
|
顯示如何使用 [尋找] 和 [取代] 對話方塊。 |
|
|
FIND、FINDB
|
這些函數您可以用來執行各種字串操作工作，例如尋找和取代字串中的子字串、擷取部分字串，或決定字串長度。 |
有時候文字值包含開頭、結尾或多個內嵌空格字元 (Unicode 字元設定值 32 和 160)，或是非列印字元 (Unicode 字元設定值 0 到 31、127、129、141、143、144 和 157)。 當您排序、篩選或搜尋時，這些字元有時候會導致無法預期的結果。 例如，在外部資料來源中，使用者可能會因無意中新增額外的空白字元而產生拼字錯誤，或者從外部來源匯入的文字資料可能包含內嵌在文本中的非列印字元。 由於這些字元不易被發現，因此無法預期的結果可能會難以理解。 若要移除這些不想要的字元，您可以使用 TRIM、CLEAN 和SUBSTITUTE 函數的組合。
|
其他資訊 |
描述 |
|---|---|
|
傳回文字字串中第一個字元的數值碼。 |
|
|
從文字中移除 7 位元 ASCII 碼 (值 0 至 31) 中的前 32 個非列印字元。 |
|
|
從文字中移除 7 位元 ASCII 空白字元 (值 32)。 |
|
|
您可以使用 SUBSTITUTE 函數，針對所設計的 TRIM 和 CLEAN 函數，以 7 位元 ASCII 字元取代值較高的 Unicode 字元 (值 127、129、141、143、144、157 和 160)。 |
數值有兩個主要問題，可能需要您清理資料：數值無意中以文字形式匯入，而負號需要變更為適用於組織的標準。
|
其他資訊 |
描述 |
|---|---|
|
顯示如何轉換儲存格中以文字格式儲存的數值，這可能會在計算時造成問題，或是在排序數值格式時造成困擾。 |
|
|
將數值轉換為文字格式並套用貨幣符號。 |
|
|
將值轉換為特定數值格式的文字。 |
|
|
將數字四捨五入到指定的小數位數、使用句號和逗號以小數點格式來格式化數字，再以文字形式傳回結果。 |
|
|
將代表某數值的文字字串轉換成數字。 |
由於有許多不同的日期格式，且這些格式可能會因為數字部分代碼或包含斜線標記或連字號的其他字串產生混淆，因此日期和時間經常需要轉換並重新設定格式。
|
其他資訊 |
描述 |
|---|---|
|
描述日期系統在 Office Excel 中的運作方式。 |
|
|
顯示如何在不同的時間單位間轉換。 |
|
|
顯示如何轉換儲存格中以文字格式儲存的日期，這可能會在計算時造成問題，或是在排序日期格式時造成困擾。 |
|
|
傳回代表特定日期的連續序列值。 如果儲存格格式在輸入函數之前是 [通用格式]，則結果會以日期形式設定其格式。 |
|
|
將以文字表示的日期轉換為序列值。 |
|
|
傳回特定時間的十進位數字。 如果儲存格格式在輸入函數之前是 [通用格式]，則結果會以日期形式設定其格式。 |
|
|
傳回以文字字串代表之時間的十進位數字。 該十進位數字是介於 0 (零) 到 0.99999999 之間的值，代表自 0:00:00 (上午 12:00:00) 到 23:59:59 (下午 11:59:59) 的時間。 |
從外部資料來源匯入資料之後的常見工作，是將兩欄或多欄合併成一欄，或將一欄分割成兩欄或多欄。 例如，您可能會想要將包含全名的欄分割成名字和姓氏。 或者，您可能會想要將包含地址欄位的欄，分割成個別的街道欄、城市欄、地區欄和郵遞區號欄。 反之亦然。 您可能會想要將名字欄和姓氏欄合併成全名欄，或是將個別的地址欄合併成一欄。 其他常見的值可能需要合併為一欄，或是分割成多欄，包含產品代碼、檔案路徑和網際網路通訊協定 (IP) 位址。
|
其他資訊 |
描述 |
|---|---|
|
顯示將兩欄或多欄中的值合併的一般範例。 |
|
|
顯示如何使用這個精靈來根據各種常見的分隔符號來分割欄。 |
|
|
顯示如何使用 LEFT、MID、RIGHT、SEARCH 和 LEN 函數將姓名欄分割成兩欄或多欄。 |
|
|
顯示如何使用 CONCATENATE 函數、& (& 符號) 運算子和資料剖析精靈。 |
|
|
顯示如何使用 [合併儲存格]、[合併同列儲存格] 和 [跨欄置中] 命令。 |
|
|
聯結兩個或多個文字字串成為一個文字字串。 |
Office Excel 中大部分的分析和格式化功能，會假設資料存在於單一、平面二維的表格中。 有時候您可能會想要讓列變成欄，或欄變成列。 其他時候，資料結構甚至不是表格格式，因此您需要將資料從非表格格式轉換為表格格式的方式。
|
其他資訊 |
描述 |
|---|---|
|
將垂直的儲存格範圍以水平範圍的格式傳回，反之亦然。 |
|
有時候，當加入兩個或多個表格時，資料庫系統管理員會使用 Office Excel 尋找和修正比對錯誤。 這可能涉及讓來自不同工作表的兩個表格一致，例如，查看兩個表格中的所有記錄或比較表格並尋找不相符的列。
|
其他資訊 |
描述 |
|---|---|
|
顯示使用 LOOKUP 函數來查閱資料的常見方式。 |
|
|
從一列、一欄範圍或列陣中傳回值。 LOOKUP 函數有兩種語法形式：向量形式與列陣形式。 |
|
|
在表格或值列陣的第一列尋找值，然後傳回表格或陣列之指定列中同一欄的值。 |
|
|
搜尋表格列陣中第一欄的值，並傳回表格列陣中另一欄之同列的值。 |
|
|
傳回表格或範圍內的某個值或值的參照。 INDEX 函數有兩種形式：列陣形式和參照形式。 |
|
|
傳回列陣中項目的相對位置，該項目與特定順序的特定值相符。 當您需要的是項目在範圍內的位置而不是項目本身，請使用 MATCH 函數，而不要使用其中一個 LOOKUP 函數。 |
|
|
傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。 傳回的參照可以是單一儲存格或一個儲存格範圍。 您可以指定要傳回的列數和欄數。 |
下列是第三方提供者的部分清單，其提供的產品可以用多種方式清理資料。
附註: Microsoft 不提供第三方產品的支援。
|
提供者 |
產品 |
|---|---|
|
Add-in Express Ltd. |
|
|
Add-Ins.com |
|
|
AddinTools |
|
|
WinPure |
