電子表格制作實(shí)例1用Excel制作學(xué)生成績統(tǒng)計(jì)與查詢

1、用excel實(shí)現(xiàn)學(xué)生信息與成績查詢問題1，班主任老師能否一次查詢a生各次考試成績？問題2，任課老師能否一次查詢所教學(xué)科的各次考試成績？回答：能。制作過程一、制作學(xué)生信息與成績工作表1.打開excel，新建一個(gè)名為“學(xué)生信息與成績查詢”的工作簿。2.反復(fù)插入工作表，使工作簿內(nèi)共有10個(gè)工作表。然后依次命名為：xsxx(學(xué)生信息)，cj1(成績1，代表學(xué)期的第1次考試，其余依此類推)，cj2，cj3，cj4，cj5，cx1(查詢1)，cx2。3.制作xsxx(學(xué)生信息)的工作表。在a1中輸入“學(xué)生基本信息”，選中a1：k2單元格區(qū)域，格式合并居中。在a3：k3單元格中，依次輸入列的標(biāo)題字段學(xué)號、學(xué)

2、生、 性別、民族、籍貫、出身年月、家庭住址、郵政編碼、家長姓名、聯(lián)系電話、備注。輸入學(xué)號：學(xué)號一般都是一組序列號，因此可以用填充柄（填充柄：位于選定區(qū)域右下角的小黑方塊。用鼠標(biāo)指向填充柄時(shí)，鼠標(biāo)的指針變?yōu)楹谑郑┩侠斎搿Ｔ赼4、 a5中先分別輸入xh01、xh02，然后選中a4、a5用填充柄向下拖拉至需要填入學(xué)號的單元格。學(xué)生姓名的輸入：這個(gè)工作量比較大，無捷徑可走，只能仔細(xì)一個(gè)個(gè)的輸入，需要說明的是姓名為兩個(gè)字的中間留一空格，以便在后面的查詢時(shí)供條件判斷。名稱命名：命名的名稱便于在學(xué)生成績表中引用，選中a3，單擊“插入”，指向“名稱”，單擊“定義”，名稱為“學(xué)號”，引用位置：=xsxs!$

3、a$4:$a$97。選中a4，名稱為“姓名”，引用位置：=xsxx!$b$4:$b$97。(或者選中b4:b97單元格區(qū)域，在名稱框中輸入“姓名”后按下回車。特別說明：本文重在說明使用電子表格的一些方法與技巧，因此有些地方不免累贅)點(diǎn)撥：在數(shù)據(jù)的有效性設(shè)置中，“序列”來源的引用只能是單一的行或列，并且不能是其它工作表的引用，單元格名稱可以在工作簿中引用。4.制作學(xué)生“xxxx年x考試學(xué)生成績表”工作表。選擇cj1，后按住shift，選擇cj5，使以cj1到cj5五個(gè)工作表成為一個(gè)工作組，以便同時(shí)編輯。工作組中表格的字段設(shè)計(jì)如下表（圖附后）圖表 1abcdefghijklmn1xx年x考試學(xué)生成

4、績表 2學(xué)號學(xué)生語文 數(shù)學(xué)英語物理化學(xué)政治歷史地理生物總分百分率名次3120120120100100505050507601004xh01xs0189.591.0103.080.583.544.04445.048.562982.845xh02xs0282.061.085.043.560.536.015.529.038.545159.3476xh03xs0374.06271677134.03541.04650165.928c3：k3分別為各科考試的卷面總分值。a4單元格中輸入公式：='xsxx'!a4。(公式實(shí)現(xiàn)cj表中的學(xué)號與xsxx表中學(xué)號的鏈接)b4單元格中輸入公式：=&#

5、39;xsxx'!b4。(或者對單元格進(jìn)行數(shù)據(jù)的有效性設(shè)置，允許序列，序列來源為“=姓名”，姓名就是xsxx表中定義的名稱，實(shí)際中引號不能輸入。設(shè)置后，就可以從下拉列表中選擇姓名，輸入姓名亦可。不過，數(shù)據(jù)的有效性設(shè)置不能選擇工作組，只能在一個(gè)工作表的單元格或區(qū)域中進(jìn)行，但設(shè)置后可以進(jìn)行復(fù)制粘貼操作。單擊“工具選項(xiàng)”，選擇“自定義序列”選項(xiàng)，選中列表框中“新序列”，在 “從單元格中導(dǎo)入序列”獲取框中輸入“xsxx!姓名”，然后點(diǎn)擊“導(dǎo)入確定”按鈕，完成序列添加。以后就可以以序列方式輸入學(xué)生姓名。不過，xsxs工作表中的數(shù)據(jù)改變，自定義序列就要重新添加。)提示：公式、有效性、序列輸入可以說

6、是殊途同歸，這里顯然用公式更好些。l3中輸入公式“=sum(c3:k3)”統(tǒng)計(jì)總分。m3中輸入公式“=l3/(sum($c$3:$k$3)*100”計(jì)算百分率。n4中輸入公式“=rank(l4,$l$4:$l$96)”根據(jù)總分排名次。復(fù)制公式，用填充柄向下分別將公式復(fù)制到需要的行(這里是96行，根據(jù)學(xué)生人數(shù)而定)。點(diǎn)撥1：工作表中行列刪除，切記不要刪除公式的開始行和結(jié)尾行，含有公式的列勿做刪除，如果刪除將破壞查詢工作表的單元格區(qū)域的引用，從而出現(xiàn)公式錯(cuò)誤，對于確實(shí)不需要的列，可以采取隱藏方式達(dá)到不顯示(也不會被打印)，方法是選中列標(biāo)，右擊鼠標(biāo)，在彈出的快捷菜單中，選擇“隱藏”。二、制作學(xué)生信息

7、與成績查詢工作表cx1工作表供班主任老師分析本班學(xué)生一學(xué)期成績使用1.在a1單元格中輸入“學(xué)生成績查詢1 (按名次每次可查20個(gè))”，用鼠標(biāo)拖拉到n1，后格式為“合并居中”。2.在a2，b2，c2，d2，分別輸入“輸入期考”“輸入名次”“學(xué)號”“學(xué)生”字段；回到cj1工作表，連續(xù)選中c2：n2并復(fù)制，返回cx1工作表，選中e2單元格，后粘貼；在p2單元格中輸入“名次”字段。3選中a3：a22單元格，格式為“合并居中”，再進(jìn)行數(shù)據(jù)的有效性設(shè)置，允許序列，來源為“1，2，3，4，5”,代表學(xué)期的5次考試，供后面公式中的條件判斷。4.在b4中輸入公式“=b3+1”并用填充柄向下將公式復(fù)制到b22單元

8、格。這樣以后在使用時(shí)只需在b3中輸入一個(gè)名次，b4：b22中就自動遞增。5.在c3單元格中輸入公式“=if($a$3=1,index('cj1'!a$4:a$96,match($b$3,'cj1'!$n$4:$n$96,0),if($a$3=2,index('cj2'!a$4:a$96,match($b$3,'cj2'!$n$4:$n$96,0),if($a$3=3,index('cj3'!a$4:a$96,match($b$3,'cj3'!$n$4:$n$96,0),if($a$3=4,index

9、('cj4'!a$4:a$96,match($b$3,'cj4'!$n$4:$n$96,0), match($b$3,'cj5'!$n$4:$n$96,0)，并用填充柄向右拖拉到p3單元格。點(diǎn)撥2：公式中僅有a$4:a$96 為相對引用列與絕對引用行的混合引用。因?yàn)楣较蛴覐?fù)制中，學(xué)科字段改變，所以公式中相對列也要改變，以返回相應(yīng)學(xué)科的數(shù)據(jù)。'cj1'!是對工作簿中cj1工作表的引用，'cj1'!a$4:a$96表示引用工作表cj1中a$4:a$96的區(qū)域。選中c3：p3單元格，先進(jìn)行“編輯替換(查找內(nèi)容為$b$3

10、，替換為$b3)”，將絕對引用替換為絕對引用列相對引用行的混合引用，因?yàn)楣皆谙蛳聫?fù)制中，公式中引用的b3：b22行的邏輯值也應(yīng)相應(yīng)變化。然后將c3：p3單元格的公式用填充柄向下復(fù)制到22行。公式看起來很繁瑣，觀察你會發(fā)現(xiàn)，一長串公式實(shí)際上只由三個(gè)函數(shù)嵌套而成，下邊我們對公式逆推一一解說， 公式match($b$3,'cj5'!$n$4:$n$96,0) 返回$b$3單元格中的值與“cj5”工作表$n$4:$n$96數(shù)組中相匹配的數(shù)組元素的相應(yīng)位置，參數(shù)0是指定與之相等的第1個(gè)元素。公式index('cj4'!a$4:a$96,match($b$3,'c

11、j4'!$n$4:$n$96,0) 返回指定的列(工作表cj4中a$4:a$96)與行(match($b$3,'cj4'!$n$4:$n$96,0)返回的結(jié)果)交叉處的單元格的數(shù)值。公式if($a$3=4,index('cj4'!a$4:a$96,match($b$3,'cj4'!$n$4:$n$96,0) ,match($b$3,'cj5'!$n$4:$n$96,0) 條件判斷，如果$a$3=4,( $a$3，邏輯值單元格，“=4”邏輯值，表明公式將在cj4工作表中查詢并返回結(jié)果。)返回公式的結(jié)果，否則返回公式的結(jié)果。點(diǎn)

12、撥3：如果將上述公式中$n$4:$n$96(名次列)替換成$a$4:$a$96(學(xué)號列)就可以按學(xué)號查詢，替換成$b$4:$b$96(姓名列)就可以按姓名查詢。制作完成的如圖2(因工作表較寬，為了適應(yīng)文檔版面制作圖片時(shí)隱藏了一些列，圖3、圖5同此。)圖表 26.在a23輸入“學(xué)生成績綜合查詢2”(按學(xué)號或姓名查詢一個(gè)學(xué)生一學(xué)期5次考試的綜合成績，或按名次查詢一學(xué)期5次考試任意名次的得主。)7.復(fù)制a2：p2的字段，粘貼到a24：p24，將“輸入名次”改為“查分條件”。8.在a25：a29分別輸入1，2，3，4，5，代表學(xué)期的5次考試。b25：b29格式為合并居中，插入批注，“按姓名兩字姓名中間

13、留一格空,要區(qū)分全半角，并且必須與cj表中的完全匹配學(xué)號至少5位數(shù)名次最多2位數(shù)查分”，條件查詢提示。9.在c25中輸入公式“=if(len($b$25)>4,index('cj1'!a$4:a$96,match($b$25,'cj1'!$a$4:$a$96,0),if(len($b$25)>2,index('cj1'!a$4:a$96,match($b$25,'cj1'!$b$4:$b$96,0),index('cj1'!a$4:a$96,match($b$25,'cj1'!$n$4:

14、$n$96,0)”，將公式復(fù)制到c26:c29,后將c26，c27，c28，c29公式中的'cj1'!(工作表的引用)分別替換為'cj2'! ，'cj3'! ，'cj4'!， 'cj5'!，選中c25：c29單元格并用填充柄拖拉向右復(fù)制公式到p25：p29。公式解說，len($b$25) 返回單元格$b$25中文本字符串的字符數(shù)，大于4表明輸入的是學(xué)號(在cj工作表中學(xué)號至少是5位)，大于2表明輸入的是姓名(如果姓名是2個(gè)字，中間加1空格)，否則輸入的是名次。公式if依據(jù)條件返回結(jié)果，這里就不贅述，依照c3單元格的

15、公式揣摩。如圖3、4、5分別按學(xué)號、學(xué)生姓名、名次查詢的結(jié)果。圖表 3圖表 4圖表 5cx2工作表 供科任老師分析所教班級學(xué)生一學(xué)期學(xué)科的走勢。1.a1中輸入“選擇學(xué)科”字段。2.b1中進(jìn)行數(shù)據(jù)的有效性設(shè)置，允許序列，來源為“1，2，3，4，5，6”,分別代表學(xué)生成績工作表中的c2,d2,e2,f2,g2,h2的學(xué)科默認(rèn)為n2的名次。插入批注：“語文c=1 數(shù)學(xué)d=2英語e=3 物理f4 化學(xué)g5政治h6名次n默認(rèn)”供條件選擇。3.c1中輸入表頭“學(xué)生單科考成績查詢表”并將c1合并居中到f1。4.a2:f2中分別輸入“輸入姓名”“cj1”“cj2”“cj3”“cj4”“cj5”5.b3中輸入公

16、式“=if($b$1=1,index('cj1'!$c$4:$c$96,match($a$3,'cj1'!$b$4:$b$96,0),if($b$1=2,index('cj1'!$d$4:$d$96,match($a$3,'cj1'!$b$4:$b$96,0),if($b$1=3,index('cj1'!$e$4:$e$96,match($a$3,'cj1'!$b$4:$b$96,0),if($b$1=4,index('cj1'!$f$4:$f$96,match($a$3,'

17、cj1'!$b$4:$b$96,0),if($b$1=5,index('cj1'!$g$4:$g$96,match($a$3,'cj1'!$b$4:$b$96,0),if($b$1=6,index('cj1'!$h$4:$h$96,match($a$3,'cj1'!$b$4:$b$96,0),index('cj1'!$n$4:$n$96,match($a$3,'cj1'!$b$4:$b$96,0)”，用填充柄向右將公式復(fù)制到f3，選中b3：f3將$a$3全部替換為$a3，再將c3，d3，e3

18、，f3中的'cj1'!分別替換為'cj2'! ，'cj3'! ，'cj4'!， 'cj5'!，然后選中b3：f3用填充柄向下將公式復(fù)制到97行。b3公式的意義是：當(dāng)邏輯值的單元格$b$1的值為1時(shí)(表明查找語文學(xué)科的成績)，運(yùn)行函數(shù)index，否則運(yùn)行嵌套函數(shù)if。運(yùn)行函數(shù)index（返回指定的行與列交叉處的單元格引用）時(shí)，先運(yùn)行函數(shù)match（返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。），查找邏輯值的單元格$a3中的值（學(xué)生名字）在數(shù)組'c1'!$b$4:$b$96（學(xué)生名字列）中的行

19、位置，再運(yùn)行函數(shù)index，返回其（學(xué)生名字行的位置）與數(shù)組列'c1'!$c$4:$c$96（語文科成績列）相交的值（即學(xué)生的語文成績）。選中a4單元格，進(jìn)行數(shù)據(jù)的有效性設(shè)置，允許序列，來源“=姓名”(實(shí)際輸入中不加引號，姓名就是xsxs表中定義的$b$4:$b$97單元格區(qū)域的名稱。)，在查詢中就可以從下拉列表中選擇姓名，輸入姓名亦可。制作完成的單科查詢表如圖6圖表 6制作tj統(tǒng)計(jì)表格。圖表 7選擇cj1，后按住shift，選擇cj5，使以cj1到cj5五個(gè)工作表成為一個(gè)工作組，后在cj1工作表a100：m117單元格區(qū)域中設(shè)計(jì)表格如圖。在b101單元格中輸入公式：=trunc($a$100*a102-0.01,2) ，確定分?jǐn)?shù)段。同時(shí)選中c101:c111輸入數(shù)組公式“=frequency(c$4:c$96,$b$101:$b$111) ”輸入完后按下“ctrl+shift+enter”組合鍵確認(rèn)(函數(shù) frequency 返回一個(gè)數(shù)組，所以必須以數(shù)