1. 首頁 > 生活百科 > >

用Excel函數快速整理錯亂成績表2020新發布

電腦知識excel

單位教務部門拿來Excel兩張工作表 ， 要把“成績表”中成績列數據復制到“學生基本信息表”成績列中 。 我對照了兩個表 ， 發現幾個難點(如圖) 。

用Excel函數快速整理錯亂成績表2020新發布

文章插圖

用Excel函數快速整理錯亂成績表2020新發布

文章插圖

(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 ， 如果是 ， 設置為空 。
【用Excel函數快速整理錯亂成績表2020新發布】因此在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列進行公式復制即可 。

    推薦閱讀