讓您的資料更簡潔的十大絕佳方法

拼錯的字、難以處理的結尾空格、不需要的前置詞、不適當的大小寫，以及非列印字元，都會讓人留下不好的第一印象。 而這還不是讓資料變雜亂之方法的完整清單。 捲起衣袖。 現在正是使用 Microsoft Excel 對您的工作表進行一些主要大掃除的時候了。

清理資料的基本概念

您無法永遠掌控從外部資料來源匯入的資料格式和類型，例如資料庫、文字檔或網頁。 在您分析資料之前，通常需要先清理資料。 幸好，Excel 有許多功能可以協助您以想要的精確格式取得資料。 有時候，工作非常簡單，而且有特定功能可以替您完成工作。 例如，您可以輕鬆使用拼字檢查程式，清理內含註解或描述的欄中拼錯的字。 或者，如果您想要移除重複的列，您可以使用 [移除重複項] 對話方塊來快速進行這項操作。

有時候，您可能需要操作一個或多個欄，透過使用公式即可將匯入的值轉換為新的值。 例如，如果您想要移除結尾空格，您可以建立新的欄以使用公式清理資料、向下填滿新欄、將新欄的公式轉換為值，然後移除原始欄。

清理資料的基本步驟如下：

1. 從外部資料來源匯入資料。

2. 在個別活頁簿中建立原始資料的備份。

3. 確認資料是使用欄和列的表格格式，其中：每欄中的資料相似，可顯示所有欄和列，而且範圍內沒有空白的列。 為獲得最佳結果，請使用 Excel 表格。

4. 首先執行不需要操作欄的工作，例如拼字檢查，或使用 [尋找及取代] 對話方塊。

5. 接下來，執行需要操作欄的工作。 操作欄的一般步驟如下：

   1. 在需要清理的原始欄 (A) 旁邊插入一個新欄 (B)。
   2. 新增公式，該公式將會轉換新欄 (B) 頂端的資料。
   3. 在新欄 (B) 中向下填滿公式。 在 Excel 表格中，向下填滿值即會自動建立計算結果欄。
   4. 選取新欄 (B)，複製該欄，然後以值形式貼到新欄 (B)。
   5. 移除原始欄 (A)，這樣會將新欄從 B 轉換為 A。

若要定期清理相同的資料來源，可考慮錄製巨集或撰寫程式碼以自動執行整個過程。 還有一些列於 [第三方提供者] 區段中由第三方廠商編寫的外部增益集，如果您沒有時間或資源來自行自動化程序，則可以考慮使用這些增益集。

其他資訊	描述
在工作表儲存格中自動填入資料	顯示如何使用 [填滿] 命令。
建立表格和設定其格式 藉由新增或移除列和欄調整表格大小 使用 Excel 表格中的計算結果欄	顯示如何建立 Excel 表格，以及新增或刪除欄或計算結果欄。
建立巨集	顯示使用巨集自動化重複工作的多種方式。

拼字檢查

您不只可以使用拼字檢查程式尋找拼錯的字，還能透過將這些值 (例如產品或公司名稱) 新增到自訂字典來尋找使用不一致的值。

其他資訊	描述
拼字檢查與文法檢查	顯示如何修正工作表中拼錯的字。
使用自訂字典將字新增至拼字檢查程式	說明如何使用自訂字典。

移除重複的列

當您匯入資料時，重複的列是常見問題。 在移除重複值之前，最好先篩選唯一值，以確認是您想要的結果。

其他資訊	描述
篩選唯一值或移除重複的值	顯示兩個密切相關的程序：如何篩選唯一列和移除重複的列。

尋找和取代文字

您可能想要移除一般前置字串 (例如後面接著冒號和空格的標籤)，或後置字元 (例如字串尾端過時或不必要的附加字詞)。 您可以尋找該文字的執行個體，然後以沒有任何文字或其他文字將其取代來執行此操作。

其他資訊	描述
檢查儲存格是否包含不區分大小寫 (文字) 檢查儲存格是否包含大小寫區分的文字 ()	顯示如何使用 [尋找] 命令和多個函數來尋找文字。
移除文字的字元	顯示如何使用 [取代] 命令和多個函數來移除文字。
尋找或取代工作表中的文字和數字	顯示如何使用 [尋找] 和 [取代] 對話方塊。
FIND、FINDB SEARCH、SEARCHB REPLACE，REPLACEB SUBSTITUTE LEFT，LEFTB RIGHT，RIGHTB LEN，LENB MID，MIDB	這些函數您可以用來執行各種字串操作工作，例如尋找和取代字串中的子字串、擷取部分字串，或決定字串長度。

變更文字大小寫

有時候文字會以混雜形式顯示，特別是涉及文字大小寫的情況下。 使用三個 Case 函數的其中一或多個，您可以將文字轉換為小寫字母 (例如電子郵件地址)、大寫字母 (例如產品代碼)，或適當的大小寫 (例如名稱或書名)。

其他資訊	描述
變更文字大小寫	顯示如何使用三個 Case 函數。
LOWER	將文字字串中的所有大寫字母轉換成小寫字母。
PROPER	將文字字串中的第一個英文字母和任何其他非英文字母的字元後之字母轉換成大寫。 其餘所有的字母則都轉換成小寫。
UPPER	將文字轉換成大寫字母。

移除文字中的空格和非列印字元

有時文字值包含前導、後置或多個嵌入空格字元， (Unicode 字元集值 32 和 160) ，或非列印字元 (的 Unicode 字元集值 0 到 31、127、129、141、143、144 和 157) 。 當您排序、篩選或搜尋時，這些字元有時候會導致無法預期的結果。 例如，在外部資料來源中，使用者可能會因無意中新增額外的空白字元而產生拼字錯誤，或者從外部來源匯入的文字資料可能包含內嵌在文本中的非列印字元。 由於這些字元不易被發現，因此無法預期的結果可能會難以理解。 若要移除這些不想要的字元，您可以使用 TRIM、CLEAN 和SUBSTITUTE 函數的組合。

其他資訊	描述
CODE	傳回文字字串中第一個字元的數值碼。
CLEAN	從文字中移除 7 位元 ASCII 碼 (值 0 至 31) 中的前 32 個非列印字元。
TRIM	從文字中移除 7 位元 ASCII 空白字元 (值 32)。
SUBSTITUTE	您可以使用 SUBSTITUTE 函數，針對所設計的 TRIM 和 CLEAN 函數，以 7 位元 ASCII 字元取代值較高的 Unicode 字元 (值 127、129、141、143、144、157 和 160)。

修正數值和數字記號

數值有兩個主要問題，可能需要您清理資料：數值無意中以文字形式匯入，而負號需要變更為適用於組織的標準。

其他資訊	描述
將以文字形式儲存的數值轉換成數值格式	顯示如何轉換儲存格中以文字格式儲存的數值，這可能會在計算時造成問題，或是在排序數值格式時造成困擾。
DOLLAR	將數值轉換為文字格式並套用貨幣符號。
TEXT	將值轉換為特定數值格式的文字。
FIXED	將數字四捨五入到指定的小數位數、使用句號和逗號以小數點格式來格式化數字，再以文字形式傳回結果。
VALUE	將代表某數值的文字字串轉換成數字。

修正日期和時間

由於有許多不同的日期格式，且這些格式可能會因為數字部分代碼或包含斜線標記或連字號的其他字串產生混淆，因此日期和時間經常需要轉換並重新設定格式。

其他資訊	描述
變更日期系統、格式或兩位數年份解譯	描述日期系統在 Office Excel 中的運作方式。
轉換時間	顯示如何在不同的時間單位間轉換。
將以文字形式儲存的日期轉換成日期	顯示如何轉換儲存格中以文字格式儲存的日期，這可能會在計算時造成問題，或是在排序日期格式時造成困擾。
DATE	傳回代表特定日期的連續序列值。 如果儲存格格式在輸入函數之前是 [通用格式]，則結果會以日期形式設定其格式。
DATEVALUE	將以文字表示的日期轉換為序列值。
TIME	傳回特定時間的十進位數字。 如果儲存格格式在輸入函數之前是 [通用格式]，則結果會以日期形式設定其格式。
TIMEVALUE	傳回以文字字串代表之時間的十進位數字。 該十進位數字是介於 0 (零) 到 0.99999999 之間的值，代表自 0:00:00 (上午 12:00:00) 到 23:59:59 (下午 11:59:59) 的時間。

合併及分割欄

從外部資料來源匯入資料之後的常見工作，是將兩欄或多欄合併成一欄，或將一欄分割成兩欄或多欄。 例如，您可能會想要將包含全名的欄分割成名字和姓氏。 或者，您可能會想要將包含地址欄位的欄，分割成個別的街道欄、城市欄、地區欄和郵遞區號欄。 反之亦然。 您可能會想要將名字欄和姓氏欄合併成全名欄，或是將個別的地址欄合併成一欄。 其他常見的值可能需要合併為一欄，或是分割成多欄，包含產品代碼、檔案路徑和網際網路通訊協定 (IP) 位址。

其他資訊	描述
名字和姓氏結合 合併文字與數字 將文字與日期或時間結合 利用函數合併兩個以上的欄	顯示將兩欄或多欄中的值合併的一般範例。
使用資料剖析精靈將文字分割成不同的欄	顯示如何使用這個精靈來根據各種常見的分隔符號來分割欄。
使用函數將文字分割成不同的欄	顯示如何使用 LEFT、MID、RIGHT、SEARCH 和 LEN 函數將姓名欄分割成兩欄或多欄。
結合或分割儲存格的內容	顯示如何使用 CONCATENATE 函數、& (& 符號) 運算子和資料剖析精靈。
合併儲存格或分割合併的儲存格	顯示如何使用 [合併儲存格]、[合併同列儲存格] 和 [跨欄置中] 命令。
CONCATENATE	聯結兩個或多個文字字串成為一個文字字串。

轉換及重新排列欄和列

Office Excel 中大部分的分析和格式化功能，會假設資料存在於單一、平面二維的表格中。 有時候您可能會想要讓列變成欄，或欄變成列。 其他時候，資料結構甚至不是表格格式，因此您需要將資料從非表格格式轉換為表格格式的方式。

其他資訊	描述
TRANSPOSE	將垂直的儲存格範圍以水平範圍的格式傳回，反之亦然。

透過加入或比對讓表格資料一致

有時候，當加入兩個或多個表格時，資料庫系統管理員會使用 Office Excel 尋找和修正比對錯誤。 這可能涉及讓來自不同工作表的兩個表格一致，例如，查看兩個表格中的所有記錄或比較表格並尋找不相符的列。

其他資訊	描述
在資料清單中查閱值	顯示使用 LOOKUP 函數來查閱資料的常見方式。
LOOKUP	從一列、一欄範圍或列陣中傳回值。 LOOKUP 函數有兩種語法形式：向量形式與列陣形式。
HLOOKUP	在表格或值列陣的第一列尋找值，然後傳回表格或陣列之指定列中同一欄的值。
VLOOKUP	搜尋表格列陣中第一欄的值，並傳回表格列陣中另一欄之同列的值。
INDEX	傳回表格或範圍內的某個值或值的參照。 INDEX 函數有兩種形式：列陣形式和參照形式。
MATCH	傳回列陣中項目的相對位置，該項目與特定順序的特定值相符。 當您需要的是項目在範圍內的位置而不是項目本身，請使用 MATCH 函數，而不要使用其中一個 LOOKUP 函數。
OFFSET	傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。 傳回的參照可以是單一儲存格或一個儲存格範圍。 您可以指定要傳回的列數和欄數。

第三方提供者

下列是第三方提供者的部分清單，其提供的產品可以用多種方式清理資料。

提供者	產品
Add-in Express Ltd.	Ultimate Suite for Excel、合併表格精靈、重複移除器、合併工作表精靈、合併列精靈、儲存格清理器、隨機產生器、合併儲存格、Excel 快速工具、隨機排序器、進階尋找 & 替換、模糊重複搜尋器、分割名稱、分割表格精靈、工作簿管理器
Add-Ins.com	重複尋找器
AddinTools	AddinTools 協助
WinPure	ListCleaner 輕量版 ListCleaner Pro

頁面頂端