Excel中函數進行快速整理錯亂成績表格操作方法

Excel中函數進行快速整理錯亂成績表格操作方法

如何用Excel函數快速整理錯亂成績表，具體該怎么操作?今天，小編就教大家在Excel中函數進行快速整理錯亂成績表格操作方法。

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列進行公式復制即可。

Excel中函數進行快速整理錯亂成績表格操作方法

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。