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