Excel文本數(shù)據(jù)的處理方法和技巧

在Excel中文本數(shù)據(jù)是工作表數(shù)據(jù)的重要組成元素之一，每一張Excel工作表中的數(shù)據(jù)既有文本又有數(shù)值，因此了解和掌握Excel文本數(shù)據(jù)的處理方法和技巧是非常重要的。

Excel提供了27個文本函數(shù)。在這些文本函數(shù)中。最常用的是從字符串中取字符的幾個文本函數(shù)：LEN函數(shù)、LEFT函數(shù)、RIGHT函數(shù)、MID函數(shù)以及FIND函數(shù)。

LEN函數(shù)用于獲取文本字符串中的字符數(shù)。

LEFT函數(shù)用于獲取字符串左邊指定個數(shù)的字符數(shù)。

RlGHT函數(shù)用于獲取字符串右邊指定個數(shù)的字符數(shù)。

MlD函數(shù)用于獲取字符串從指定位置開始指定個數(shù)的字符數(shù)。

FIND函數(shù)用于在區(qū)分大小寫的情況下。查找某字符在字符串中第一次出現(xiàn)的位置。

下面舉倒說明這幾個函數(shù)的使用方法。

圖1所示是從數(shù)據(jù)庫導入的員工聯(lián)系地址信息，員工姓名、郵政編碼和地址之間用符號“|”分隔?，F(xiàn)在要求把這3項數(shù)據(jù)分開呈3列保存。

圖1

這個問題最簡單的解決方法是使用“分列”工具。不過。這里使用相關(guān)的文本函投進行分列。

首先設計表格。如圖2所示。

圖2

在單元格B2中輸入公式“=LEFT（A2.FlND（"|",A2）-1）”。并向下復制。得到員工姓名。

在單元格C2中輸入公式“=MID（A2.LEN（B2）+2.6）”。并向下復制。得到郵政編碼。

在單元格D2中輸入公式“=MlD（A2.LEN（B2）+9.99）”。并向下復制，得到地址。

注意，這里要先用FlND函數(shù)確定符號“|”第一次出現(xiàn)的位置，然后利用LEFT函數(shù)把該符號左邊的字符取出來，就是姓名。

郵政編碼的開始位置是姓名字符個數(shù)加上2（因為符號“|”占用一個位置）。因此提取郵政編碼要使用MID函數(shù)。其起始位置是表達式LEN（B2）+2的結(jié)果。而字符長度是6。

地址的開始位置是姓名字符數(shù)加上9（因為郵政編碼宇符數(shù)是6.還有兩個符號“|”）。因此提取地址也要使用MID函數(shù)，其起始位置是表達式LEN（B2）+9的結(jié)果。而字符長度是后面所有的字符，這里設置了一個充分大的數(shù)字99.因為很少有地址的字符數(shù)超過99位的。如果仍不放心。也可以把這個數(shù)改為999甚至更大的數(shù)宇。

可以利用有關(guān)的文本函數(shù)。從身份證號碼提取信息。圖3所示是根據(jù)員工身份證號碼提取有關(guān)信息的表格。在這個表格中。把出生日期分成3列分別保存年、月、日3個數(shù)字。這樣做一方面可以簡化公式。另一方面也便于數(shù)據(jù)分析，例如可以篩選某年、某月、某日出生的員工。

圖3

在單元格E3中輸入公式“=1*IF（LEN（D3）=15.19&M1D（D3.7.2）.MID（、"D3.7.4））”，并向下復制。得到出生年份數(shù)字。

在單元格F3中輸入公式“=1*IF（LEN（D3）=15.MID（D3.9.2）.MID（D3.11.2））”。并向下復制。得到出生月份數(shù)字。

在單元格G3中輸入公式“=1*IF（LEN（D3）=15.MlD（D3.11.2）.MID（D3.13.2））”。并向下復制。得到出生日數(shù)宇。

在單元格H3中輸入公式“=DATE（E3，F(xiàn)3.G3）”，并向下復制。將出生年、月、日3個數(shù)字合并為一個真正的出生日期。

在單元格13中輸八公式“=lF（ISEVEN（IF（LEN（D3）=15.RIGHT（D3）。MID（D3.17.1））），“女”?！澳小保薄２⑾蛳聫椭?，以判斷性別。注意，判斷性別時。對于15位身份證號碼。是根據(jù)量后一位數(shù)字判斷的；而對于18位身份證號碼。則是根據(jù)倒數(shù)第二位數(shù)字（也就是第17位數(shù)字）判斷的。

圖4所示是一個會計科目分錄表。要計算凈利潤。一般會使用下面的公式：

=C2+C7+C8+C9+C10+C11+C22+C23+C24+C70+C110+C116+C117+C118

圖4

這個公式最大的缺點是要一個單元格一個單元格地相加。很容易加錯單元格。造成計算結(jié)果錯誤。

考慮到計算凈利潤時。僅僅計算總賬科目。而總賬科目的科目編碼只有4位數(shù)。這樣就可以利用LEN函數(shù)進行判斷。并構(gòu)建相應的高效計算公式了。計算公式如下：

=SUMPRODUCT（（LEN（A2：All8）=4）*C2：Cll8）

了解和掌握了Excel文本數(shù)據(jù)的處理方法和技巧以后，我們就再也不用擔心加錯單元格的問題了。今天我們學習了5個文本處理函數(shù)和列舉了2個例子，大家應該好好熟悉一下。