本文為大家介紹excel表格怎么打亂成績排名(如何用excel處理學(xué)生成績)，下面和小編一起看看詳細(xì)內(nèi)容吧。
單位教務(wù)處帶了兩張excel工作表，他們需要將《成績單》成績欄中的數(shù)據(jù)復(fù)制到《學(xué)生基本信息表》成績欄中。我對比了兩個表，發(fā)現(xiàn)了幾個難點(diǎn)。
（一）《學(xué)生基本信息表》中姓名與《年級表》中姓名不一致的。 《學(xué)生基本信息表》中的“王一”即為“成績單”中的“王一”。半角空格。
(2)《學(xué)生基本信息表》中，《成績單表》中沒有王小平，即《學(xué)生基本信息表》中的人數(shù)多于《成績單》中的人數(shù)形式”。
(3) “成績表”中的成績以文字形式列出，并出現(xiàn)全角數(shù)字。
(4) 每個表的數(shù)據(jù)有幾千條。如果將“成績單”中的姓名欄進(jìn)行排序，將成績欄復(fù)制粘貼到“學(xué)生基本信息表”中的成績欄，會出現(xiàn)錯位。
我用excel函數(shù)substitute和lookup解決了這個問題，對“學(xué)生基本信息表”和“成績表”做了一些修改，將“學(xué)生表”中的數(shù)據(jù)復(fù)制到“學(xué)生基本信息表”中”并保持最終表數(shù)據(jù)的清晰度和正確性。
去除“成績表”中的全角或半角空格
首先我要解決的問題是去掉“成績單”中姓名的空格，使“成績單”中學(xué)生姓名的顯示與“學(xué)生基本信息”中的姓名顯示一致床單”。這時，我使用替換公式substitute(substitute(a2,'半角空格',''),'全角空格','')。在d2單元格中輸入公式=substitute(substitute(a2,' ',''),' ','')，然后將公式復(fù)制到d列。選擇d列數(shù)據(jù)復(fù)制，然后選擇所有數(shù)據(jù)在a 列 粘貼特殊 值和數(shù)字格式。
將“成績表”中的成績轉(zhuǎn)換為數(shù)字
刪除了空格，接下來的工作是將“成績表”中的數(shù)字歸一化為半角形式。還可以使用函數(shù)substitute。在e2 單元格中輸入公式=(substitute(c2,'.','.'))*1，其中substitute(c2,'.','.') 表示句號“.”轉(zhuǎn)換為句點(diǎn)“.”，“*1”表示轉(zhuǎn)換為數(shù)字。然后復(fù)制e 列中的公式。對選擇性粘貼執(zhí)行相同操作。選擇e列數(shù)據(jù)復(fù)制，然后選擇c列所有數(shù)據(jù)選擇性粘貼數(shù)值和數(shù)字格式。刪除“等級表”中的d 和e 列。
將“成績單”中的數(shù)據(jù)復(fù)制到“學(xué)生基本信息表”中
最后一步是將“成績表”中的數(shù)據(jù)復(fù)制到“學(xué)生基本信息表”中，但是我們不能簡單地使用復(fù)制粘貼來實(shí)現(xiàn)，因?yàn)槲覀兊木唧w案例包括沒有成績的學(xué)生，所以為了正確性的數(shù)據(jù)，我們知道查詢函數(shù)lookup有一個特點(diǎn)，查詢完成后會在指定區(qū)域返回查詢結(jié)果，我用它來實(shí)現(xiàn)復(fù)制“分?jǐn)?shù)表”中數(shù)據(jù)的效果。
它的語法是lookup(lookup_value, lookup_vector, result_vector)。其中l(wèi)ookup_value是要查找的值，lookup_vector是只包含一行或一列的區(qū)域，必須按升序排列，否則會返回錯誤，result_vector會返回只包含一行或一列的區(qū)域一欄。
如果函數(shù)lookup 找不到lookup_value，它將在lookup_vector 中搜索小于或等于lookup_value 的最大值。如果lookup_value 小于lookup_vector 中的最小值，函數(shù)lookup 將返回錯誤值#n/a。利用這個特性，我們把公式改成=lookup(1, 0/(條件), 參考區(qū)域)，條件——產(chǎn)生一個邏輯值true和false的數(shù)組，0/true=0, 0/false=# div0!即lookup的第二個參數(shù)由0，#div0!組成的數(shù)組。 （均小于1），如果條件成立，則返回對應(yīng)行引用區(qū)域的值；如果沒有找到符合條件的記錄，則返回#n/a錯誤，以便進(jìn)行精確查找。
在“學(xué)生基本信息表”的d2中輸入公式=lookup(1, 0/(成績單!a$2:a$5=b2),成績單!c$2:c$5)。 #n/a出現(xiàn)在沒有查到數(shù)據(jù)的列中，影響了表格的美觀。稍微改進(jìn)一下，用isna函數(shù)判斷是否為#n/a，如果是則設(shè)置為空。
所以在d2中輸入公式=if(isna(lookup(1, 0/(results!a$2:a$5=b2),scores!c$2:c$5)),'',lookup(1,0/(results !a $2:a$5=b2), score sheet! c$2:c$5)), 所以單元格中不會出現(xiàn)#n/a，最后復(fù)制d列的公式。
好了，excel表格怎么打亂成績排名(如何用excel處理學(xué)生成績)的介紹到這里就結(jié)束了，想知道更多相關(guān)資料可以收藏我們的網(wǎng)站。