||
單位教務(wù)部門拿來(lái)Excel兩張工作表,,要把“成績(jī)表”中成績(jī)列數(shù)據(jù)復(fù)制到“學(xué)生基本信息表”成績(jī)列中,。我對(duì)照了兩個(gè)表,,發(fā)現(xiàn)幾個(gè)難點(diǎn),。
(1) “學(xué)生基本信息表”的姓名與“成績(jī)表”中的姓名不一樣,,“學(xué)生基本信息表”中的“王一”在“成績(jī)表”中為“ 王 一”,,出現(xiàn)了全角或半角空格,。
(2) “學(xué)生基本信息表”中王小平在“成績(jī)表”中無(wú)此人,即“學(xué)生基本信息表”的人數(shù)多于“成績(jī)表”的人數(shù),。
(3) “成績(jī)表”中成績(jī)列為文本方式,,且出現(xiàn)了全角數(shù)字。 轉(zhuǎn)自www.58116.cn
(4) 每個(gè)表的數(shù)據(jù)為幾千條,。如果對(duì)“成績(jī)表”中的姓名列進(jìn)行排序,,把成績(jī)列進(jìn)行復(fù)制粘貼到“學(xué)生基本信息表”中的成績(jī)列,出現(xiàn)錯(cuò)位,。
我通過(guò)Excel函數(shù)SUBSTITUTE和LOOKUP來(lái)解決,,將“學(xué)生基本信息表”和“成績(jī)表”進(jìn)行了一些修改,實(shí)現(xiàn)將“成績(jī)表”中的數(shù)據(jù)復(fù)制到“學(xué)生基本信息表”中,,并且保持最終表格的清爽和數(shù)據(jù)的正確,。
除去“成績(jī)表”中全角或半角空格
首先,我要解決的問(wèn)題是將“成績(jī)表”中姓名的空格去掉,,讓“成績(jī)表”中的學(xué)生姓名顯示和“學(xué)生基本信息表”中的一樣,。此時(shí)我利用替換公式 SUBSTITUTE(SUBSTITUTE(A2,,"半角空格 ",""),,"全角空格",,"")。在D2單元格輸入公式=SUBSTITUTE(SUBSTITUTE(A2,," ",,"")," ",,""),,然后在整個(gè)D列復(fù)制公式。選擇D列數(shù)據(jù)→進(jìn)行復(fù)制,,再選擇A列所有數(shù)據(jù)→選擇性粘貼→值和數(shù)字格式,。
轉(zhuǎn)化“成績(jī)表”中成績(jī)列為數(shù)字
刪除了空格,下面的工作就是將“成績(jī)表”中的數(shù)字規(guī)范為半角形式,。同樣利用函數(shù)SUBSTITUTE,。在E2單元格輸入公式= (SUBSTITUTE(C2,",。",,"."))*1,其中SUBSTITUTE(C2,,"。",,".")表示句號(hào)“,。”轉(zhuǎn)化為點(diǎn)號(hào)“.”,“*1”表 示轉(zhuǎn)化為數(shù)字,。然后在E列復(fù)制公式,。同樣進(jìn)行選擇性粘貼。選擇E列數(shù)據(jù)→進(jìn)行復(fù)制,,再選擇C列所有數(shù)據(jù)→選擇性粘貼→值和數(shù)字格式,。刪除“成績(jī)表”中D 列、E列,。
復(fù)制“成績(jī)表”中數(shù)據(jù)到“學(xué)生基本信息表” 轉(zhuǎn)自電腦入門到精通網(wǎng)
最后一步就是復(fù)制“成績(jī) 表”中的數(shù)據(jù)到“學(xué)生基本信息表”了,,但是我們不能簡(jiǎn)單地利用復(fù)制粘貼來(lái)實(shí)現(xiàn),因?yàn)槲覀兊倪@個(gè)具體案例中包含沒有成績(jī)的同學(xué),,所以為了數(shù)據(jù)的正確性,,我們 知道查詢函數(shù)LOOKUP有一個(gè)特性就是在查詢結(jié)束后會(huì)在指定的區(qū)域返回查詢結(jié)果,我就用它來(lái)達(dá)到復(fù)制“成績(jī)表”中數(shù)據(jù)的效果,。
其語(yǔ)法 為LOOKUP(lookup_value,,lookup_vector,result_vector)。其中Lookup_value為要查找的數(shù) 值,,Lookup_vector為只包含一行或一列的區(qū)域,,且必須按升序排列,否則要返回錯(cuò)誤,,Result_vector 返回只包含一行或一列的區(qū)域,。
如果函數(shù)LOOKUP找不到lookup_value,則查找lookup_vector中小于或等于 lookup_value的最大數(shù)值,,如果 lookup_value 小于lookup_vector 中的最小值,,函數(shù)LOOKUP 返回錯(cuò)誤值 #N/A,利用這個(gè)特性,,我們把公式改為=LOOKUP(1,,0/(條件),引用區(qū)域),,條件——產(chǎn)生的是邏輯值True,、False數(shù)組,0 /True=0,,0/false=#DIV0!,,即Lookup的第2參數(shù)便是由0、#DIV0!組成的數(shù)組(都比1小),,如果找到滿足條件,,就返回對(duì)應(yīng) 行引用區(qū)域的值;如果沒有找到滿足條件的記錄則返回#N/A錯(cuò)誤,從而實(shí)行精確查找,。
在“學(xué)生基本信息表”中D2輸入公式=LOOKUP(1,,0/(成績(jī)表!A$2:A$5=B2),成績(jī)表!C$2:C$5),。在沒找到數(shù)據(jù)的一欄出現(xiàn)了#N/A,,影響了表格的美觀。稍微改進(jìn)一下,,利用ISNA函數(shù)判斷是否為#N/A,,如果是,設(shè)置為空,。
因此在D2輸入公式=IF(ISNA(LOOKUP(1,,0/(成績(jī)表!A$2:A$5=B2),成績(jī) 表!C$2:C$5)),,"",,LOOKUP(1,0/(成績(jī)表!A$2:A$5=B2),,成績(jī)表!C$2:C$5)),,這樣#N/A不會(huì)出現(xiàn)在單元格 中,,最后在D列進(jìn)行公式復(fù)制即可。
銷售與市場(chǎng)官方網(wǎng)站 ( 豫ICP備19000188號(hào)-5 )
GMT+8, 2024-12-24 22:17 , Processed in 0.028862 second(s), 16 queries .
Powered by 銷售與市場(chǎng)網(wǎng) 河南銷售與市場(chǎng)雜志社有限公司
© 1994-2021 sysyfmy.com