摘 要:作為Office系列組件之一的電子表格軟件Excel已成為日常工作中最常用的工具之一,本文通過利用Excel制作一個校內(nèi)通用的學生信息統(tǒng)計表,并給出相應的制作思路、制作要點和注意事項,講述Excel在學生信息統(tǒng)計中的具體應用。
關(guān)鍵詞:Excel函數(shù);數(shù)據(jù)處理;學生信息統(tǒng)計;表格制作
中圖分類號:TP391.13
1 利用Excel制作學生信息統(tǒng)計表的背景
Excel作為功能強大的電子表格應用軟件,以其強大的數(shù)據(jù)計算、處理、統(tǒng)計、分析、管理功能而應用于各個領(lǐng)域,靈活應用好Excel的格式、公式、函數(shù)、圖表等功能,不僅可以更好地服務(wù)我們的工作,而且可以大大地提高工作效率,減少數(shù)據(jù)出錯概率。借助Excel對數(shù)據(jù)進行處理,能有效解決數(shù)據(jù)不統(tǒng)一的問題,完成數(shù)據(jù)的處理、合并與傳遞。
有過多年使用Excel的用戶,在其所使用的工作簿中仍不難發(fā)現(xiàn)存在格式不規(guī)范、數(shù)據(jù)錯誤、統(tǒng)計不準確、多個表格數(shù)據(jù)重復錄入又沒有實現(xiàn)相互關(guān)聯(lián)等問題,這不僅給相關(guān)部門增加重復的工作量,而且不利于數(shù)據(jù)的分析、統(tǒng)計和匯總,又容易導致數(shù)據(jù)出錯,還不利于文件的管理。
2 利用Excel制作學生信息統(tǒng)計表的思路
一般來說,制作一個校內(nèi)通用的學生信息統(tǒng)計表包括多項內(nèi)容,具體有:學號、姓名、性別、院系、專業(yè)、年級、班級、聯(lián)系電話、QQ號碼、電子郵件、家庭電話、家庭地址、郵政編碼、輔導員、輔導員電話等。不同學校所要收集和統(tǒng)計的信息可能會有所不同或有所增減。當面對很多信息字段時,如果不對表格進行相應的設(shè)置,使表格提供更友好的提示或交互,即使填寫人很細心,也難免會出現(xiàn)一些錯誤。那么,需要怎樣制作才能最大限度地避免不必要的輸入錯誤呢?
第一,要有原始的基礎(chǔ)數(shù)據(jù)。原始的基礎(chǔ)數(shù)據(jù)從哪里來?學生的基本信息,如學號、姓名、性別、院系、專業(yè)、年級、班級等,可以直接從學校的學籍管理系統(tǒng)中導出來,輔導員及其電話等信息可以從學生管理系統(tǒng)中導出來。但學生的聯(lián)系方式,如聯(lián)系電話、QQ號碼、電子郵件、家庭電話、家庭地址、郵政編碼等可能不定時發(fā)生變更,則無法直接利用。
第二,工作簿的建立與工作表的構(gòu)成。要制作這樣一個學生信息統(tǒng)計表,一般需要原始基礎(chǔ)數(shù)據(jù)工作表、學生信息統(tǒng)計表、填寫使用說明表和各年級各院系(或各專業(yè)、各班級)學生人數(shù)統(tǒng)計表四個工作表。
第三,要有相應的提示、批注和使用說明。一個好的表格,不僅體現(xiàn)在填寫的簡便性,而且體現(xiàn)在填寫時友好的提示和交互,同時還要有相應的使用說明。簡便性可以大大提高工作效率,友好的提示和交互可避免填寫時出現(xiàn)不必要的錯誤,使用說明則可以更詳細地傳遞信息。
第四,實現(xiàn)表格數(shù)據(jù)關(guān)聯(lián)與自動統(tǒng)計。熟練使用公式的引用和函數(shù)的調(diào)用是實現(xiàn)表格數(shù)據(jù)關(guān)聯(lián)與自動統(tǒng)計的基礎(chǔ),也是制作學生信息統(tǒng)計表的關(guān)鍵。
3 利用Excel制作學生信息統(tǒng)計表的要點
制作學生信息統(tǒng)計表會用到Excel的許多命令,以下列舉其中的八點供參考。
3.1 數(shù)據(jù)的格式設(shè)置。制作學生信息統(tǒng)計表,需要設(shè)置目標單元格的數(shù)字類型、字體、字型、字號、顏色、對齊方式、邊框線、使用樣式等,需要設(shè)置相應的行高和列寬,需要設(shè)置列標題的批注作為輸入的提示。另外,可以使用條件格式來檢查填表時是否存在漏填。其中一個簡單的方法是選中所有預填寫的單元格(如以后需用此表格在A2:P100輸入學生的信息,則選中A2:P100),依次選擇“開始”選項卡-“條件格式”-“新建規(guī)則”-“使用公式確定要設(shè)置格式的單元格”,為符合此公式的值設(shè)置格式:“=LEN(TRIM(A2))=0”,再選擇“格式”按鈕,在彈出的“設(shè)置單元格格式”對話框中選擇“填充”選項卡,再選擇一種背景色(最好是不容易引起視覺疲勞的顏色,如淺綠色),再依次點擊兩次“確定”按鈕,即可讓所有選中的單元格填充淺綠色背景。只要往這些單元格輸入數(shù)據(jù),相應單元格的背景自動消失,沒有輸入數(shù)據(jù)的依然顯示,通過這種智能方法,可直觀看出存在哪些信息漏填。
3.2 數(shù)據(jù)的自動填充。通常,表格的第一列為“序號”,第二列為“學號”,在Excel中可以做好只要輸入相應的學號,即可自動填充“序號”,否則不填充。要實現(xiàn)這樣的功能,首先選中單元格A2,然后在編輯欄輸入“=IF(B2<>\"\",ROW()-1,\"\")”,再把單元格A2所使用的公式復制粘貼到A2以后的單元格。
3.3 數(shù)據(jù)的有效性。制作學生信息統(tǒng)計表時,可以對不同列設(shè)置不同的格式。如學生的學號長度通常都是固定的,因此可以通過設(shè)置數(shù)據(jù)有效性,限制輸入學號的長度(如長度等于12),并可結(jié)合設(shè)置“輸入信息”和“出錯警告”,以便提供更好的交互。但是,如果僅僅通過限制輸入學號的長度是不能避免輸入不存在的學號的,因此,還可以將輸入學號限制為原始的基礎(chǔ)數(shù)據(jù)中所有預統(tǒng)計學生的學號。假如單元格B201:B1200存放1000位預統(tǒng)計學生的學號,而B2:B100是所要設(shè)置的單元格,首先選中單元格B2:B100,然后依次選擇“數(shù)據(jù)”選項卡-“數(shù)據(jù)有效性”-“設(shè)置”選項卡,有效性條件允許選擇“序列”,在來源中輸入“=$B201:$B1200”,再點擊“確定”按鈕即可。
3.4 數(shù)據(jù)的唯一性。為了保證統(tǒng)計學生信息的準確性與唯一性,不能單純從學生的姓名來區(qū)分,因為可能出現(xiàn)同名同姓的學生,因此學號成為校內(nèi)學生的唯一“ID”,也成為用來驗證輸入時是否出現(xiàn)重復的方法。假如單元格B2:B100將要輸入學號,則選中B2:B100,依次選擇“開始”選項卡-“條件格式”-“新建規(guī)則”-“僅對唯一值或重復值設(shè)置格式”-“重復”,再選擇“格式”按鈕,在彈出的“設(shè)置單元格格式”對話框中選擇“填充”選項卡,再選擇一種背景色(最好是容易引起視覺沖擊的顏色,如紅色),再依次點擊兩次“確定”按鈕。只要在不同的單元格輸入相同的學號,所有相同學號的單元格馬上顯示紅色背景,表示輸入信息重復。
3.5 數(shù)據(jù)的傳遞。為了使學生信息統(tǒng)計表填寫簡便,需要將數(shù)據(jù)進行關(guān)聯(lián),并使用公式引用和函數(shù)調(diào)用。首先,將存有所有預統(tǒng)計的學生基礎(chǔ)數(shù)據(jù)(即學籍系統(tǒng)中導出的原始數(shù)據(jù))放在Sheet1工作表,列A~H的列標題分別為序號、學號、姓名、性別、院系、專業(yè)、年級和班級。Sheet2工作表用來輸入要統(tǒng)計的學生信息,其中列A~H的列標題和Sheet1的列標題一樣,那么當需要輸入一個學生的信息時,Excel可以做到只在列B輸入正確的學號,列A和列C~H即可自動生成。列A的生成方法已在上面第(2)點“數(shù)據(jù)的自動填充”講述過。列C“姓名”的生成方法可使用VLOOKUP函數(shù)來實現(xiàn),即選中C2,插入VLOOKUP函數(shù),各參數(shù)如圖1所示,但是只要單元格B2為空,C2就會顯示“#N/A”,為了避免出現(xiàn)這種情況,可以用ISERROR函數(shù)來檢查單元格C2的值是否為錯誤,如果錯誤顯示為空,否則返回VLOOKUP函數(shù)的查找值,因此可以直接在編輯欄輸入“=IF(ISERROR(VLOOKUP(B2,'Sheet1'!$B:$H,2,F(xiàn)ALSE)),\"\",VLOOKUP(B2,'Sheet1'!$B:$H,2,F(xiàn)ALSE))”,并將此公式應用到其他單元格即可,列D~H的生成方法也是如此,其他列如有需要實現(xiàn)自動生成也可參考此方法。通過使用VLOOKUP查找函數(shù),可減少不必要的輸入,又可減少出錯概率,實現(xiàn)數(shù)據(jù)的一致性、規(guī)范性。
3.6 數(shù)據(jù)的保護。數(shù)據(jù)的保護包括對單元格、工作表或工作簿的保護。雖然Excel可以實現(xiàn)數(shù)據(jù)的格式設(shè)置、填充、傳遞等功能,但是如果沒有對相應的單元格、工作表或工作簿進行保護,所制作的學生信息統(tǒng)計表很容易被破壞,也不便于表格的更新。例如,自動生成的列可以設(shè)置其單元格格式為“鎖定”和“隱藏”,當在保護工作表的時候,不勾選“選定鎖定單元格”復選框,并設(shè)置相應的密碼后,即可讓不知道密碼的用戶無法選定或更改被鎖定的單元格,與此同時,保護單元格的時候還要考慮是否允許用戶使用數(shù)據(jù)的排序、篩選等功能,即不能一味強調(diào)保護,而影響用戶對其他功能的使用。Excel還可以保護工作簿的結(jié)構(gòu)和窗口,讓不知道密碼的用戶無法增加、刪除或重命名工作表。
3.7 數(shù)據(jù)的統(tǒng)計與匯總。一般收集學生的信息后還要統(tǒng)計各年級各院系(或各專業(yè)、各班級)的學生人數(shù),如果每次都是手工統(tǒng)計,很容易出錯,對于新增或減少的數(shù)據(jù),倘若沒有及時更新,也容易增加統(tǒng)計出錯的概率。因此,可以利用Excel的數(shù)組公式或SUM、SUMPRODUCT等函數(shù)實現(xiàn)人數(shù)的自動統(tǒng)計與匯總,并且生成相應的圖表,使結(jié)果一目了然。
3.8 數(shù)據(jù)的打印。收集和統(tǒng)計好的數(shù)據(jù)往往需要打印,在打印之前,可以先進行頁面設(shè)置,比如選定紙張的方向、大小,打印質(zhì)量,設(shè)置頁邊距,頁眉和頁腳,打印的范圍等。如果熟悉VBA,還可以設(shè)置更人性化的打印,真正做到“隨心所印”。
4 利用Excel制作學生信息統(tǒng)計表的注意事項
(1)所制作的學生信息統(tǒng)計表要方便更新,不出現(xiàn)提示錯誤或無法輸入數(shù)據(jù);(2)因表格的制作要使用大量公式引用或函數(shù)調(diào)用,會占用系統(tǒng)內(nèi)存資源,因此不宜設(shè)置太大范圍;(3)所制作的學生信息統(tǒng)計表要便于使用,可以附帶必要的填寫說明。
5 結(jié)束語
利用Excel制作學生信息統(tǒng)計表,表面上看是很一件很簡單的事,事實上卻要求制作者熟練掌握Excel許多功能和命令。制作此表格的特點是使用多種格式對單元格進行設(shè)置,使用公式引用和函數(shù)調(diào)用實現(xiàn)數(shù)據(jù)的傳遞與匯總,使用保護功能保證表格的完整性,這也是制作此表格的意義所在,服務(wù)用戶、方便用戶。
參考文獻:
[1]馬亮明.EXCEL的函數(shù)在教師信息統(tǒng)計中的應用[J].福建電腦,2013(12).
[2]周愛國,高晶晶,劉錦啟.Excel表在醫(yī)院信息統(tǒng)計中的應用[J].科技資訊,2012(01).
[3]李愛紅.Excel在會計數(shù)據(jù)處理中的應用[J].河南財政稅務(wù)高等??茖W校學報,2011(12).
[4]王建敏.淺議excel函數(shù)vlookup?if在編制會計表格中的應用[J].赤峰學院學報,2011(11).
作者簡介:陳喜華(1985.11-),男,廣東潮州人,教務(wù)員,中級網(wǎng)絡(luò)工程師,學士學位,研究方向:教育教學管理;陳媛苑(1989.08-),女,廣東廣州人,機房管理員,學士學位,研究方向:實驗教學管理。
作者單位:廣州工商職業(yè)技術(shù)學院,廣州 510850