用Excel函數快速整理錯亂成績表

單位教務部門拿來Excel兩張工作表,要把「成績表」中成績列數據複製到「學生基本信息表」成績列中。我對照了兩個表,發現幾個難點。   

(1) 「學生基本信息表」的姓名與「成績表」中的姓名不一樣,「學生基本信息表」中的「王一」在「成績表」中為「 王 一」,出現了全角或半角空格。   

(2) 「學生基本信息表」中王小平在「成績表」中無此人,即「學生基本信息表」的人數多於「成績表」的人數。   

(3) 「成績表」中成績列為文本方式,且出現了全角數字。   

(4) 每個表的數據為幾千條。如果對「成績表」中的姓名列進行排序,把成績列進行複製粘貼到「學生基本信息表」中的成績列,出現錯位。   

我通過Excel函數SUBSTITUTE和LOOKUP來解決,將「學生基本信息表」和「成績表」進行了一些修改,實現將「成績表」中的數據複製到「學生基本信息表」中,並且保持最終表格的清爽和數據的正確。   

除去「成績表」中全角或半角空格   

首先,我要解決的問題是將「成績表」中姓名的空格去掉,讓「成績表」中的學生姓名顯示和「學生基本信息表」中的一樣。此時我利用替換公式 SUBSTITUTE(SUBSTITUTE(A2,"半角空格 ",""),"全角空格","")。在D2單元格輸入公式=SUBSTITUTE(SUBSTITUTE(A2," ","")," ",""),然後在整個D列複製公式。選擇D列數據→進行複製,再選擇A列所有數據→選擇性粘貼→值和數字格式。   

轉化「成績表」中成績列為數字   

刪除了空格,下面的工作就是將「成績表」中的數字規範為半角形式。同樣利用函數SUBSTITUTE。在E2單元格輸入公式= (SUBSTITUTE(C2,"。","."))*1,其中SUBSTITUTE(C2,"。",".")表示句號「。」轉化為點號「.」,「*1」表示轉化為數字。然後在E列複製公式。同樣進行選擇性粘貼。選擇E列數據→進行複製,再選擇C列所有數據→選擇性粘貼→值和數字格式。刪除「成績表」中D 列、E列。   

複製「成績表」中數據到「學生基本信息表」   

最後一步就是複製「成績表」中的數據到「學生基本信息表」了,但是我們不能簡單地利用複製粘貼來實現,因為我們的這個具體案例中包含沒有成績的同學,所以為了數據的正確性,我們知道查詢函數LOOKUP有一個特性就是在查詢結束後會在指定的區域返回查詢結果,我就用它來達到複製「成績表」中數據的效果。   

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

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

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

因此在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不會出現在單元格中,最後在D列進行公式複製即可。

本文內容整理自網絡, 文中所有觀點看法不代表淘大白的立場