1、EXCEL在教務(wù)管理中的應(yīng)用黃陂區(qū)教學(xué)研究室丁云一、數(shù)據(jù)的獲取與數(shù)據(jù)的有效性n1.數(shù)據(jù)的類型常規(guī)型數(shù)值型日期型文本型貨幣型百分比型等 處理不同的數(shù)據(jù)，最好先定義不同的數(shù)據(jù)類型2.數(shù)據(jù)庫文件與工作薄文件可直接打開或通過導(dǎo)出導(dǎo)入的方式以缺省方式處理。dbf文件導(dǎo)出為xls文件時(shí)，要注意記錄數(shù)的限制，否則容易丟失記錄數(shù)據(jù)。一、數(shù)據(jù)的獲取與數(shù)據(jù)的有效性3.文本數(shù)值的處理若數(shù)據(jù)以文本型存放，則不能參與運(yùn)算。會(huì)影響到統(tǒng)計(jì)結(jié)果，所以需要進(jìn)行轉(zhuǎn)換。方法:使其與一空單元格相加即可。一、數(shù)據(jù)的獲取與數(shù)據(jù)的有效性4.快速選擇與輸入方法:Ctrl+方向鍵 Ctrl+Enter空格和0值的對(duì)比與處理一、數(shù)據(jù)的獲取與數(shù)據(jù)

2、的有效性5.填充快速填充：雙擊填充柄 （以相鄰區(qū)域?yàn)閰⒖迹┛朔白詣?dòng)運(yùn)算”填充：Ctrl+D ，Ctrl+R自定義序列填充：先定義序列，然后可根據(jù)定義的序列進(jìn)行填充。一、數(shù)據(jù)的獲取與數(shù)據(jù)的有效性6.菜單輸入在特定的區(qū)域輸入固定的內(nèi)容，可采用下拉菜單式輸入。方法：定義名稱；數(shù)據(jù)有效性設(shè)置。一、數(shù)據(jù)的獲取與數(shù)據(jù)的有效性7.表格的轉(zhuǎn)置即行列互換，可用選擇性粘貼完成或應(yīng)用函數(shù)Transpot完成。一、數(shù)據(jù)的獲取與數(shù)據(jù)的有效性8.數(shù)據(jù)的有效性 對(duì)于已輸入的數(shù)據(jù)： 通過條件篩選，可找出無效數(shù)據(jù)。 對(duì)于未輸入的數(shù)據(jù)，可事先通過對(duì)數(shù)據(jù)有效性進(jìn)行設(shè)置，設(shè)置數(shù)值輸入的允許范圍，超過此范圍則認(rèn)為出錯(cuò)。一、數(shù)據(jù)的獲取

3、與數(shù)據(jù)的有效性1.編號(hào)信息的獲取 應(yīng)用函數(shù) mid ，left，right等可獲取字符串中任意部分的內(nèi)容，以供需要。 應(yīng)用范圍：如從身份證中獲取有關(guān)數(shù)據(jù)；從考號(hào)中獲取學(xué)生有關(guān)信息。二、字符函數(shù)的使用2.字符串的連接運(yùn)算 將多個(gè)單元信息合并為單一信息時(shí)要用到字符串的連接。 實(shí)現(xiàn)方法：運(yùn)用&運(yùn)算符 或者concatenate函數(shù)。二、字符函數(shù)的使用3.字符串等長(zhǎng)分列 當(dāng)需要將一個(gè)字符串從某處截?cái)喑蔀閹讉€(gè)字符串時(shí)，可用分列方式完成。 方法：數(shù)據(jù)分列選擇“分列符號(hào)”或“固定寬度” 在標(biāo)尺上選定寬度，完成分列。二、字符函數(shù)的使用1.簡(jiǎn)單統(tǒng)計(jì)函數(shù) 應(yīng)用Sum，Average，Count，Sumif，Cou

4、ntif，Max，Min等常用函數(shù)可完成求和，求平均值，計(jì)數(shù)，條件統(tǒng)計(jì)，可最大值，最小值等方面的統(tǒng)計(jì)。三、簡(jiǎn)單統(tǒng)計(jì)2.不用公式的統(tǒng)計(jì)分類匯總 若只統(tǒng)計(jì)一些簡(jiǎn)單的統(tǒng)計(jì)項(xiàng)，可使用“分類匯總”完成，而不必使用函數(shù)統(tǒng)計(jì)。 在進(jìn)行匯總統(tǒng)計(jì)前，要對(duì)數(shù)據(jù)進(jìn)行索引，即對(duì)關(guān)鍵字排序。如學(xué)校、班級(jí)等。三、簡(jiǎn)單統(tǒng)計(jì)2.不用公式的統(tǒng)計(jì)分類匯總實(shí)現(xiàn)方法： 全選數(shù)據(jù)數(shù)據(jù)分類匯總分類字段匯總方式確定匯總結(jié)果獲?。?折疊表不能直接復(fù)制粘貼，通?？捎谩岸ㄎ弧钡姆绞竭M(jìn)行復(fù)制，用數(shù)值方式粘貼到其它表格中使用。 三、簡(jiǎn)單統(tǒng)計(jì)四、擴(kuò)展統(tǒng)計(jì)1.分?jǐn)?shù)線的劃定使用函數(shù)：Large，Small，Median可分別求出指定位置點(diǎn)的最大值、最小值

5、、及中位值，繼而確定分?jǐn)?shù)線。四、擴(kuò)展統(tǒng)計(jì)2.分?jǐn)?shù)段統(tǒng)計(jì)應(yīng)用頻率分布函數(shù)Frequency統(tǒng)計(jì)。 此函數(shù)為數(shù)組函數(shù)，參數(shù)有兩個(gè)，亦為數(shù)組，一個(gè)為設(shè)定的分?jǐn)?shù)段，第二個(gè)為數(shù)據(jù)源。最后的確定要用Ctrl+Shift+Enter組合鍵。 缺省狀態(tài)下，對(duì)應(yīng)分?jǐn)?shù)段為其上限，閉區(qū)間。下限為開區(qū)間。其次，要求累計(jì)結(jié)果還要另行處理。四、擴(kuò)展統(tǒng)計(jì)2.分?jǐn)?shù)段統(tǒng)計(jì)使用條件判斷與求和函數(shù)組合統(tǒng)計(jì)。思路：Sum if 分?jǐn)?shù)=分?jǐn)?shù)段True=1 ；對(duì)True求和 else False=0 endif 具體使用方法，見實(shí)例。3.百分比類統(tǒng)計(jì) 此類統(tǒng)計(jì)要用到兩個(gè)函數(shù) Percenttitle，Percentrank，分別完成百分

6、比數(shù)值點(diǎn)和百分比排位運(yùn)算。 確定了數(shù)值點(diǎn)后，即可完成后續(xù)統(tǒng)計(jì)工作。具體用法見實(shí)例。四、擴(kuò)展統(tǒng)計(jì)4.排序 EXCEL中的排序，既可運(yùn)用“排序”的方式打亂原表次序進(jìn)行排序，也可使用Rank函數(shù)直接顯示排列順序，而不打亂原表記錄次序。 Rank函數(shù)的應(yīng)用要注意使用絕對(duì)引用。四、擴(kuò)展統(tǒng)計(jì)5.評(píng)分專用函數(shù) 本例即是求內(nèi)部平均值，就是常說的“去掉一 高分，去掉一個(gè)最低分，再求平均分”，可用步函數(shù)，亦可應(yīng)用Trimmean 函數(shù)一次完成。 此函數(shù)有兩個(gè)參數(shù)：一是數(shù)值區(qū)域，二是要去掉的部分（以百分比形式出現(xiàn)）。四、擴(kuò)展統(tǒng)計(jì)1.分?jǐn)?shù)線和上線率的統(tǒng)計(jì)（及格率優(yōu)秀率統(tǒng)計(jì)） 分?jǐn)?shù)線的確定，運(yùn)用Large函數(shù)，或者直接

7、給出，如60分及格，80分優(yōu)秀。 通過條件嵌套計(jì)數(shù)統(tǒng)計(jì)出各指定條件下和上線人數(shù)，參考人數(shù)，繼而算出上線率。見實(shí)例。五、綜合應(yīng)用2.分?jǐn)?shù)段的統(tǒng)計(jì) 應(yīng)用函數(shù)Frequency和條件嵌套統(tǒng)計(jì)均可完成條件分?jǐn)?shù)段的統(tǒng)計(jì)。用條件嵌套統(tǒng)計(jì)比較靈活，而且不必另行累計(jì)計(jì)算。 見實(shí)例。 五、綜合應(yīng)用3.百分比的統(tǒng)計(jì)即按要求統(tǒng)計(jì)在某一定百分比下的分?jǐn)?shù)線，人數(shù)，平均分等指標(biāo)。也可用條件嵌套進(jìn)行條件統(tǒng)計(jì)，大大減少操作量。見實(shí)例。 五、綜合應(yīng)用4.等級(jí)及位置值轉(zhuǎn)換用條件函數(shù)，將分?jǐn)?shù)轉(zhuǎn)換成等級(jí)，或者將等級(jí)（位置值）轉(zhuǎn)換成分?jǐn)?shù)。注意:在進(jìn)行位置值轉(zhuǎn)換時(shí)要注意兩個(gè)問題，一是位置值對(duì)應(yīng)分?jǐn)?shù)的確定，二是嵌套層數(shù)，Excel2003

8、缺省不超過七層，因此完全轉(zhuǎn)換則需將數(shù)據(jù)庫分割。Excel2007的嵌套層數(shù)有所放寬。五、綜合應(yīng)用4.等級(jí)及位置值轉(zhuǎn)換處理思路:If 分?jǐn)?shù)=90 “優(yōu)秀”else 分?jǐn)?shù)=80 “良好”else 分?jǐn)?shù)=60 “合格” else “不合格”endif 五、綜合應(yīng)用1.用隨機(jī)函數(shù)進(jìn)行考場(chǎng)編制思路:先給同一年級(jí)學(xué)生數(shù)據(jù)庫創(chuàng)建新一個(gè)字段，用來存放隨機(jī)數(shù)；用Rand函數(shù)賦給每個(gè)學(xué)生一個(gè)隨機(jī)數(shù)，隨機(jī)數(shù)最多可保留到小數(shù)點(diǎn)后16位，基本上不會(huì)產(chǎn)生重號(hào)現(xiàn)象。六、隨機(jī)函數(shù)應(yīng)用1.用隨機(jī)函數(shù)進(jìn)行考場(chǎng)編制然后按隨機(jī)數(shù)排序，打亂原有次序。此操作可進(jìn)行多次。將最后確定亂序數(shù)據(jù)庫復(fù)制到其它表格（因每次操作會(huì)產(chǎn)生新隨機(jī)數(shù)），再根

9、據(jù)每考場(chǎng)的人數(shù)，應(yīng)用取整函數(shù)Int和列號(hào)獲取函數(shù)Row對(duì)學(xué)生分配考場(chǎng)。見實(shí)例。六、隨機(jī)函數(shù)應(yīng)用2.用隨機(jī)函數(shù)進(jìn)行平行分班平行分班，即在以總分為分班原則的前提下，保證每分?jǐn)?shù)段內(nèi)各班人數(shù)一致，總體上體現(xiàn)在各班成績(jī)均衡，平均分差異很小，保證了分班的公平性和公正性。六、隨機(jī)函數(shù)應(yīng)用2.用隨機(jī)函數(shù)進(jìn)行平行分班思路:獲取數(shù)據(jù)庫，必需含姓名，考號(hào)，以分班依據(jù)“總分”等信息。將數(shù)據(jù)庫以“總分”為關(guān)鍵字段排序。按班數(shù)以“總分”自上至下分組，每組人數(shù)與班級(jí)數(shù)相同。（相當(dāng)于體育比賽中的第一種子，第二種子的分類）六、隨機(jī)函數(shù)應(yīng)用2.用隨機(jī)函數(shù)進(jìn)行平行分班思路:為數(shù)據(jù)庫創(chuàng)建隨機(jī)數(shù)字段，并產(chǎn)生隨機(jī)數(shù)。打亂順序，按第一關(guān)鍵

10、字為“分組”第二關(guān)鍵字“隨機(jī)數(shù)”排序。創(chuàng)建標(biāo)識(shí)字段，在打亂順序后的數(shù)據(jù)庫里在同一組內(nèi)給每位學(xué)生設(shè)定一個(gè)標(biāo)識(shí)符（最好可自動(dòng)填充的字符）六、隨機(jī)函數(shù)應(yīng)用2.用隨機(jī)函數(shù)進(jìn)行平行分班思路:再次將新數(shù)據(jù)庫按“標(biāo)識(shí)符”排序，同一標(biāo)識(shí)符的為一個(gè)班組。除了以總分為分班依據(jù)外，還可以考慮加入單科因素，如需要文理分班作參考時(shí)。六、隨機(jī)函數(shù)應(yīng)用對(duì)于常用的統(tǒng)計(jì)項(xiàng)目，可按上述方法對(duì)各項(xiàng)完成統(tǒng)計(jì)，并分別完成表格標(biāo)題、表格裝飾及頁面設(shè)置工作，需要進(jìn)行新的統(tǒng)計(jì)時(shí)只更換原始數(shù)據(jù)庫或分析指標(biāo)即可。完成了這些設(shè)置的文件，可以認(rèn)為是統(tǒng)計(jì)的模版了。七、制作模版1.有一些表格只有其中兩至三列內(nèi)容，并且有較多的記錄，且都得打印出來，如果你真就這樣（每頁兩、三列）打印下來，老板會(huì)不會(huì)罵人？如何通過函數(shù)