用Excel函數(shù)快速整理錯亂成績表

單位教務(wù)部門拿來Excel兩張工作表，要把;成績表”中成績列數(shù)據(jù)復(fù)制到;學(xué)生基本信息表”成績列中。我對照了兩個表，發(fā)現(xiàn)幾個難點(diǎn)。

　　(1) ;學(xué)生基本信息表”的姓名與;成績表”中的姓名不一樣，;學(xué)生基本信息表”中的;王一”在;成績表”中為; 王　一”，出現(xiàn)了全角或半角空格。

　　(2) ;學(xué)生基本信息表”中王小平在;成績表”中無此人，即;學(xué)生基本信息表”的人數(shù)多于;成績表”的人數(shù)。

　　(3) ;成績表”中成績列為文本方式，且出現(xiàn)了全角數(shù)字。

　　(4) 每個表的數(shù)據(jù)為幾千條。如果對;成績表”中的姓名列進(jìn)行排序，把成績列進(jìn)行復(fù)制粘貼到;學(xué)生基本信息表”中的成績列，出現(xiàn)錯位。

　　我通過Excel函數(shù)SUBSTITUTE和LOOKUP來解決，將;學(xué)生基本信息表”和;成績表”進(jìn)行了一些修改，實(shí)現(xiàn)將;成績表”中的數(shù)據(jù)復(fù)制到;學(xué)生基本信息表”中，并且保持最終表格的清爽和數(shù)據(jù)的正確。

　　除去;成績表”中全角或半角空格

　 　首先，我要解決的問題是將;成績表”中姓名的空格去掉，讓;成績表”中的學(xué)生姓名顯示和;學(xué)生基本信息表”中的一樣。此時我利用替換公式 SUBSTITUTE(SUBSTITUTE(A2，"半角空格 "，"")，"全角空格"，"")。在D2單元格輸入公式=SUBSTITUTE(SUBSTITUTE(A2，" "，"")，"　"，"")，然后在整個D列復(fù)制公式。選擇D列數(shù)據(jù)→進(jìn)行復(fù)制，再選擇A列所有數(shù)據(jù)→選擇性粘貼→值和數(shù)字格式。

　　轉(zhuǎn)化;成績表”中成績列為數(shù)字

　 　刪除了空格，下面的工作就是將;成績表”中的數(shù)字規(guī)范為半角形式。同樣利用函數(shù)SUBSTITUTE。在E2單元格輸入公式= (SUBSTITUTE(C2，"。"，"."))*1，其中SUBSTITUTE(C2，"。"，".")表示句號;。”轉(zhuǎn)化為點(diǎn)號;.”，;*1”表 示轉(zhuǎn)化為數(shù)字。然后在E列復(fù)制公式。同樣進(jìn)行選擇性粘貼。選擇E列數(shù)據(jù)→進(jìn)行復(fù)制，再選擇C列所有數(shù)據(jù)→選擇性粘貼→值和數(shù)字格式。刪除;成績表”中D 列、E列。

　　復(fù)制;成績表”中數(shù)據(jù)到;學(xué)生基本信息表”

　　最后一步就是復(fù)制;成績 表”中的數(shù)據(jù)到;學(xué)生基本信息表”了，但是我們不能簡單地利用復(fù)制粘貼來實(shí)現(xiàn)，因?yàn)槲覀兊倪@個具體案例中包含沒有成績的同學(xué)，所以為了數(shù)據(jù)的正確性，我們 知道查詢函數(shù)LOOKUP有一個特性就是在查詢結(jié)束后會在指定的區(qū)域返回查詢結(jié)果，我就用它來達(dá)到復(fù)制;成績表”中數(shù)據(jù)的效果。

　　其語法 為LOOKUP(lookup_value，lookup_vector，result_vector)。其中Lookup_value為要查找的數(shù) 值，Lookup_vector為只包含一行或一列的區(qū)域，且必須按升序排列，否則要返回錯誤，Result_vector 返回只包含一行或一列的區(qū)域。

　　如果函數(shù)LOOKUP找不到lookup_value，則查找lookup_vector中小于或等于 lookup_value的最大數(shù)值，如果 lookup_value 小于lookup_vector 中的最小值，函數(shù)LOOKUP 返回錯誤值 #N/A，利用這個特性，我們把公式改為=LOOKUP(1，0/(條件)，引用區(qū)域)，條件——產(chǎn)生的是邏輯值True、False數(shù)組，0 /True=0，0/false=#DIV0!，即Lookup的第2參數(shù)便是由0、#DIV0!組成的數(shù)組(都比1小)，如果找到滿足條件，就返回對應(yīng) 行引用區(qū)域的值;如果沒有找到滿足條件的記錄則返回#N/A錯誤，從而實(shí)行精確查找。

　　在;學(xué)生基本信息表”中D2輸入公式=LOOKUP(1，0/(成績表!A$2:A$5=B2)，成績表!C$2:C$5)。在沒找到數(shù)據(jù)的一欄出現(xiàn)了#N/A，影響了表格的美觀。稍微改進(jìn)一下，利用ISNA函數(shù)判斷是否為#N/A，如果是，設(shè)置為空。

　 　因此在D2輸入公式=IF(ISNA(LOOKUP(1，0/(成績表!A$2:A$5=B2)，成績 表!C$2:C$5))，""，LOOKUP(1，0/(成績表!A$2:A$5=B2)，成績表!C$2:C$5))，這樣#N/A不會出現(xiàn)在單元格 中，最后在D列進(jìn)行公式復(fù)制即可。