基于大數(shù)據(jù)時代下Excel的兩則實用技巧研究

洪金明

基于大數(shù)據(jù)時代下Excel的兩則實用技巧研究

洪金明

（湖南郵電職業(yè)技術(shù)學院，湖南長沙 410015）

在大數(shù)據(jù)時代背景下，需要使用計算機分析處理的信息量越來越多，高效、快捷利用Excel數(shù)據(jù)庫的重要性與日俱增。文章對Excel中的兩個實用數(shù)據(jù)處理事例進行探索研究，介紹了Excel 2016在數(shù)據(jù)錄入與編輯、制作標準化試卷、自動閱卷、成績統(tǒng)計處理等方面的實用技巧。

Excel 2016；數(shù)據(jù)錄入；選擇性粘貼；電子試卷；自動閱卷

Office是現(xiàn)代企事業(yè)單位行政管理的一個常用辦公軟件，其中Word、Excel和Power Point這三大組件在日常及商務(wù)辦公中是不可或缺的。

微軟公司推出了Excel 2016制表軟件，盡管其中大部分的常規(guī)功能在Excel 2010和Excel 2013中都有出現(xiàn)，操作方式也基本一致，但其中一少部分新功能無法在老版本中使用。新增功能包括樹狀圖、旭日圖、直方圖、箱形圖、瀑布圖、三維地圖功能組、預測函數(shù)，新增的功能數(shù)據(jù)工具有“獲取和轉(zhuǎn)換”、“管理數(shù)據(jù)模型”和“預測工作表”等。這些都是為適應(yīng)“大數(shù)據(jù)時代”越來越寬泛的應(yīng)用而拓展的。

1 Excel數(shù)據(jù)的錄入與編輯實用技巧

1.1 巧用Excel的選擇性粘貼功能

選擇性粘貼（special paste）是Excel中經(jīng)常使用的功能之一。在進行數(shù)據(jù)或圖形粘貼、數(shù)據(jù)橫豎轉(zhuǎn)置、數(shù)據(jù)核對等過程中十分有用。數(shù)據(jù)粘貼途徑不同，效果也不同。直接使用“粘貼”功能，原始輸入的數(shù)據(jù)可以粘貼，但是經(jīng)過計算之后所得到的數(shù)據(jù)因其附帶了其他信息，直接采用復制、粘貼時數(shù)據(jù)不會顯示，采取復制再特殊粘貼，只能把數(shù)據(jù)所代表的具體數(shù)值粘貼，附帶的其他信息不會“粘貼”隨移。

例如，要把Excel數(shù)據(jù)表中C列的數(shù)據(jù)全部粘貼到G列中，其操作步驟如下:第1步，復制C列數(shù)值，然后右擊G列第1行所在位置，在快捷菜單中選擇選擇性粘貼命令，打開選擇性粘貼對話框；第2步，在該對話框中選擇數(shù)值選項，單擊確定按鈕，選擇性粘貼即完成，其附帶的其他信息也一并“粘貼”轉(zhuǎn)移到G列中。

1.2 快速實現(xiàn)Word文本向Excel工作表的數(shù)據(jù)交換

Word適合制作表頭比較復雜、但數(shù)據(jù)不多的表格，適合畫各類斜線表頭，可以拆分小表格。Excel則適合做數(shù)據(jù)較多、表頭相對簡單的表格，只能合并單元格和對合并后的單元格取消合并，不能對基本的單元格進行拆分。一般來說，將Excel的表格直接貼到Word文本里，一般不會發(fā)生錯誤碼和數(shù)據(jù)丟失；但如果是把一個單元格中有多行文本的Word表格粘貼到Excel時，卻會因為多行的關(guān)系，使粘貼上去的結(jié)果在Excel里變成多行。在Excel 2016中則可以通過執(zhí)行下述程序代碼來完成：

Public Sub WordTableToExcel()

Cells.Clear

DimloCell As Cell

Dimt As Integer

Dimp As Integer

DimloWord As Variant

DimloWordApp As Word.Application

Set loWord=CreateObject("Word.Document")

Set loWordApp=loWord.Application

loWordApp.Documents.Open(ThisWorkbook.Path+"綜合技巧范例_Word與Excel間的數(shù)據(jù)交換.doc")

For t=1 To loWordApp.Documents(1).Tables.Count For Each loCell In loWordApp.Documents(1).Tables(t).

Range.Cells

Set loExcelRange=ActiveWorkbook.ActiveSheet.Cells

(loCell.RowIndex,loCell.ColumnIndex)

For p=1 ToloCell.Range.Paragraphs.Count loExcelRange.Value=loExcelRange.Value+

IIf(p=1,"",Chr(10))+loCell.Range.Paragraphs(p).Range

Next Next Next ActiveWorkbook.ActiveSheet.Cells.EntireColumn.AutoFit

ActiveWorkbook.ActiveSheet.Cells.EntireRow.AutoFit

ActiveWorkbook.ActiveSheet.Cells(1,1).Select

loWordApp.Quit

End Sub

1.3 自由設(shè)置工作表區(qū)域

例如：在工作表中只顯示A1：H10。

方法1：選中:10行按CTRL+Shift+下箭頭，直到到達最后一行，選擇隱藏就可以把10行以后的行數(shù)隱藏掉。選中第U列按CTRL+Shift+右箭頭，直至到達最右一列，選擇隱藏就可隱藏U列以后的列。

方法2：按Ctrl+A全選單元格→按Ctrl+9隱藏所有行→按Ctrl+0（零）隱藏所有列→按F5或Ctrl+G，彈出定位對話框，在引用位置輸入想要顯示的單元格區(qū)域如A1：H10，點確定→按Ctrl+Shift+9顯示指定區(qū)域的行號→按Ctrl+Shift+0（零）顯示區(qū)域列號，此時工作表中只顯示指定區(qū)域的單元格，當然，以后同樣可以插入新行新列。

2 教學考試方面的實用技巧

2.1 使用Excel制作標準化試卷

在推行MOOC課、微課輔助教學的今天，對學生課程知識和技能掌握情況的紙版考核方式已不太符合教學和講評的需要，越來越多的教師應(yīng)用電子試卷做在線考試，特別是使用Excel制作標準化試卷來提高練習效率，簡化對試卷的批改對錯、統(tǒng)分等簡單重復操作。當然，要使得試卷制作規(guī)范合理，布局美觀大方，是需要掌握一些實用技巧和多次實踐的。

2.1.1 題型、答題要求和分值設(shè)定

在采用Excel制作標準化試卷時，出卷者可以根據(jù)知識點的重要性、考試的難易度、差別化考評學生對不同內(nèi)容知識點和技能的掌握情況等評估目的的不同，對考試題型、答題要求和分值進行差別化設(shè)定和提出要求，并以醒目的形式告知參考者。以這些多樣化的命題、答題、考生在限定范圍內(nèi)自選的形式檢驗考生對課程知識的掌握情況和差異程度，能夠更加中肯地評價教學效果，發(fā)現(xiàn)學生的差異化優(yōu)勢。學生答題結(jié)束，點擊“提交”后，計算機考試系統(tǒng)即可自動統(tǒng)計其得分。

2.1.2 卷面版式設(shè)計

首先啟動Excel文件包，新建一個Excel空白文檔，結(jié)合課程名稱和考核旨意給定一個合適的文件名。把該工作表Sheet 1中的A-M列指定為試卷的使用范圍，N列作為考生作答的填寫輸入?yún)^(qū)域，O列作為判斷答案正確與否的給(記)分區(qū)域。在表格的1、2行中輸入試卷標題，滿分分值及其分值比重說明，班級、姓名、得分等待填的空格。根據(jù)出卷者的審美觀、卷面格式和幅面大小、顯示習慣對各欄目的字體類型和字號、加粗等項進行設(shè)計選定。為了與其它欄目的內(nèi)容相區(qū)分和醒目起見，一般會在上述某個(些)欄目做加填底色處理。通過上述設(shè)置，可以得到如后面表1所示的工作表Sheet 1表頭。

單擊選中工作表Sheet 1的“A5”單元格，使用菜單命令“窗口-凍結(jié)窗口”把上述試卷表頭凍結(jié)。然后在該工作表的適當位置，按照對考試題型、答題要求的歸類分別輸入，避免考生在答題要求上發(fā)生錯誤。

2.1.3 制卷技巧

在輸入考題時，有可能會發(fā)生一些技術(shù)問題。如，在工作表Sheet 1的某一個單元格內(nèi)輸入更多的字符，需要格內(nèi)換行，可以將光標定位在需要換行的位置，同時按下“Alt+Enter”組合鍵進行強制換行；或者在單元格的格式菜單中選擇“對齊”標簽，勾選“文本控制”中的“自動換行”功能。當試卷上需要插入圖片做答題參考時，先選擇好需要引用插入的圖片，按下“Ctrl+V”組合鍵做快捷粘貼，或者啟用菜單命令中的“插入-圖片-來自文件”功能，在需要插入的位置對選擇的圖片做插入操作。

表1 Excel標準化試卷工作表Sheet 1的表頭示例表

為了方便評閱試卷，快速區(qū)分各個題型的答題區(qū)域，可以對各個試題的文字部分、填寫答案的區(qū)域、判定計分區(qū)域加上不同的底色做區(qū)分。制卷者可將表頭中的N-O這兩列隱諱利用，預先輸入?yún)⒖即鸢福⒆鲭[諱加密處理，方便評卷時使用。

試卷制作好后，為了避免學生誤刪除或者修改試卷內(nèi)容，必須對試題單元格做保護處理：選擇整個工作表，選定菜單“格式→單元格→單元格格式”，在對話框中選擇“保護”選項卡，勾選“鎖定”復選框，輸入設(shè)定的顯示密碼，再勾選“隱藏”復選框，以使考生不能看到此顯示密碼。對需要學生答題的操作單元格，如姓名、專業(yè)、班級，選擇和填寫答案項等分別做“解除加密”處理：將光標置于所在的單元格，選擇菜單“格式→單元格→單元格格式”，在對話框中選擇“保護”選項卡，取消對“鎖定”復選框的勾選。

2.2 電子試卷自動閱卷分析過程的實現(xiàn)

2.2.1 各試題分值的設(shè)定和答案判斷

對各試題分值的設(shè)定采用計算公式輸入法：對于只有唯一正確答案、分值為2分的試題，先將光標定位在該題目行所在的M7單元格，雙擊選中，插入函數(shù)“=IF（L7=“”，“未做”，IF（L7=N7，2，0）”,即當 L7 單元格錄入的內(nèi)容為空時，顯示“未做”，記為0分；當與N7單元格里的標準答案一致時，記為2分；也可以利用菜單命令“插入-插入函數(shù)”實現(xiàn)。

單擊選中L7單元格，按住Ctrl鍵，連續(xù)選中其它的選擇、判斷及填空題中標準答案是唯一的且分值為2分的M列單元格，按下組合鍵“Ctrl+D”，實現(xiàn)上述公式的自動復制和填充。對于分值為1分，正確答案且不是唯一（有2個備選答案）的題目，也可采用上述函數(shù)，進行評判，僅需要將第二個選項的分值“2”改為“1”即可。

2.2.2 卷面得分的統(tǒng)計

先制作“成績工作表”，其中設(shè)置卷面答案、標準答案、評分標準、各題得分和最后的總成績欄目，結(jié)構(gòu)如表2所示。

表2 卷面成績統(tǒng)計工作表

在這個工作表中，卷面答案是利用單元格引用取自試卷上考生所填寫的對應(yīng)單元格的內(nèi)容，標準答案則是出卷者輸入的各題的正確答案，自動評分是利用Excel中的“IF”函數(shù)判斷卷面答案與標準答案是否一致做給分處理，一致的按照分值設(shè)定給分，不一致的不給分。然后使用“SUM”函數(shù)對各大題求得分和，再用“SUM”函數(shù)求試卷總成績。

需要注意的是，必須利用Excel的隱藏工作表功能和保護工作簿功能對本工作表做加密隱藏：選擇“卷面成績統(tǒng)計工作表”，在菜單中選擇“格式→工作表→隱藏”，先把它隱藏起來；再在菜單中選擇“工具→保護→保護工作簿”，于彈出的對話框中設(shè)置密碼，并勾選“結(jié)構(gòu)”。如此一來，考生就不能“反隱藏”卷面成績統(tǒng)計工作表，也不能看到標準答案。

2.2.3 制卷關(guān)鍵點

制作Excel電子試卷的關(guān)鍵在于利用函數(shù)進行得分統(tǒng)計，因此，理解各種Excel的函數(shù)原理很重要。如果不是用于專用教室做限時考試，則在對答題時間的設(shè)置、計時和限制方面須利用技術(shù)手段進行控制。

與傳統(tǒng)的紙版考試系統(tǒng)相比，Excel電子試卷的安全性較低，對于標準答案的加密隱藏處理如果處理不力，試卷就須作廢。再者，利用Excel制作的電子試卷基本上是不變的，較難實現(xiàn)商業(yè)化考試系統(tǒng)那樣的隨機抽題、隨即組卷，靈活性較差。利用Excel電子試卷進行考試時，必須借助多媒體教室和相應(yīng)的教學軟件進行“發(fā)卷”和“收卷”，難以實現(xiàn)網(wǎng)絡(luò)化考試。

2.3 使用Excel函數(shù)ROUND和RAND隨機抽取試題

Excel軟件對隨機函數(shù)“rand()”描述是:“返回大于或等于0且小于1的平均分布的隨機數(shù)字。每次重新計算包含此函數(shù)的單元時，返回的數(shù)字都會更改?！崩?，需要隨機抽取100以內(nèi)的正整數(shù)，可在相應(yīng)單元格中輸入=RAND()*100，隨即就會出現(xiàn)一個小于100的隨機正整數(shù)。依此可以實現(xiàn)考生差異化抽題考試，現(xiàn)介紹3種較常用而簡便的抽題方法。

2.3.1 通過設(shè)置單元格格式

用鼠標右鍵點擊單元格，出現(xiàn)右鍵菜單，選擇“設(shè)置單元格格式”，出現(xiàn)設(shè)置單元格格式窗口，選定數(shù)字選項卡，在分類中選擇數(shù)值，把小數(shù)位數(shù)改為0，按確定退出窗口。

2.3.2 運用整數(shù)函數(shù)int()

int()函數(shù)的意義是“將數(shù)值向下取整為最接近的整數(shù)”(不是4舍5入。上述使用設(shè)置單元格格式的方法是將數(shù)值4舍5入)，即在需要隨機選取的單元格中輸入“=int(rand()*100)”即可。

2.3.3 應(yīng)用ROUND和RAND函數(shù)

ROUND的功能是按指定的位數(shù)對數(shù)值執(zhí)行4舍5入。在需要隨機數(shù)的單元格中輸入公式“=ROUND（RAND（）*100，0）”。如圖 1所示。

圖1 取100以內(nèi)隨機正整數(shù)圖

下面以AutoCAD課程為例，介紹隨機抽題考試的實施方法。

學院開設(shè)的AutoCAD課程，期末考試時一般以全國計算機信息高新技術(shù)考試之“AutoCAD試題匯編”（繪圖員級）為考核標準，學生考試時從該“試題匯編”中隨機抽取。以往考試制卷時，都是手工輸入題單打印，如圖2所示。如果有50位考生參加考試，就要制作50份這樣的不重復題單，為測試學生的繪圖操作技能。

使用Excel函數(shù)ROUND和RAND實現(xiàn)無紙差異化考試的程序是，首先啟動Excel，在新工作表設(shè)計出如圖3所示框架。

圖2 制作考試題單示意圖

圖3 抽題單框架圖

然后在對應(yīng)單元號的單元格中輸入公式，如第二單元隨機抽取的題號是1至20題，即在“二”單元號的單元格中輸入公式“=ROUND(19*(RAND()),0)+1”。其他單元題號照此設(shè)限，對公式稍作修改就可指定抽取題號的范圍，如對于通訊類學生，第六單元只要求其做第1到第10題，制作抽題單時在“六”單元號下輸入公式“=ROUND(9*(RAND()),0)+1”。抽題單設(shè)計完成后如圖4所示，完成后應(yīng)對該工作表進行保護，以防被他人修改，并在評卷時核對選題是否符合要求。

圖4 完成后的抽題單示意圖

課程考試時，將該Excel文件通過教學軟件發(fā)送給學生，學生打開后可自動隨機獲取一份抽題單，而且每個學生抽題得到的考試題號都不一樣，這樣既鼓勵了學生課后加強自我訓練，也避免了考試時相同試卷(題)間的相互拷貝(舞弊)行為。

2.4 巧用Excel進行學生成績的統(tǒng)計分析

應(yīng)用Excel電子試卷考試結(jié)束后，可以很方便地利用其數(shù)據(jù)排序功能對全部參考者的成績進行排序，分出優(yōu)秀、良好、中等、及格、不及格各等次的考生名單，很快求出平均成績。

在統(tǒng)計學生多門課程的成績績點用于獎學金評定時，使用Excel電子試卷的統(tǒng)分功能能很便捷地做出統(tǒng)計。通常，同年級同專業(yè)多個班的學生各課程的總學分積是按照各個相同課程的學業(yè)成績確定的，即：課程學分積=課程學分×績點分，其中績點分是按照各課程的學業(yè)成績檔次來給定的(通常是優(yōu)秀為4、良好 3、中等 2、及格 1、不及格為 0)。

利用Excel公式計算多個考生多門課程的總學分積，方法如下：先建立一個學生成績排名的Excel工作表，對參選學生各科成績的工作表做“選擇性粘貼/粘貼鏈接”，復制到學生成績排名的Excel工作表中，分別、逐個點擊每位學生的成績單元格，然后點擊編輯欄，在編輯欄內(nèi)分別輸入各門課程的績點分計算公式：f(x)=課程學分×績點分。

3 結(jié)束語

Office是現(xiàn)代企、事業(yè)單位常用的辦公軟件，微軟公司的Excel 2016軟件是為適應(yīng)“大數(shù)據(jù)時代”越來越寬泛的應(yīng)用而拓展的。在大數(shù)據(jù)時代背景下，高職教育也應(yīng)與時俱進。作者對Excel 2016中與教學、考試相關(guān)的兩個實用數(shù)據(jù)處理事例開展了探索研究，希望與同行專家教師們分享該經(jīng)驗。

[1]羅剛君.Excel 2016實用技巧自學寶典[M].北京:電子工業(yè)出版社,2016.

[2]恒盛杰資訊.Excel 2016辦公專家從入門到精通[M].北京:機械工業(yè)出版社,2016.

[3]洪金明.基于函數(shù)的Excel軟件的應(yīng)用技巧研究[J].湖南郵電職業(yè)技術(shù)學院學報,2015(3):22-27.

[4]周磊.翻轉(zhuǎn)課堂在高職院校網(wǎng)絡(luò)類課程中的教學模式研究[J].湖南郵電職業(yè)技術(shù)學院學報,2016(3):132-134.

[5]魏德才，程倩.創(chuàng)新型教學模式下高校專業(yè)課課堂教學中多媒體技術(shù)的應(yīng)用研究[J].湖南郵電職業(yè)技術(shù)學院學報,2015(1):107-111.

[6]前沿文化.別說你懂Word/Excel——500招玩轉(zhuǎn)Word/Excel辦公應(yīng)用[M].北京:科學出版社,2013.

[7]啟典文化.Word/Excel/PPT商務(wù)辦公應(yīng)用于技巧[M].北京:中國鐵道出版社,2013.

Study on two practical skills for Excel in the big data age

HONGJin-ming
（Hunan Post and Telecommunication College,Changsha,Hunan,China 410015）

In the era of big data,the amount of information needed to be analyzed and processed by computers is more and more.The importance of quick and efficient use of Excel databases is increasing day by day.Through two examples,this paper briefly introduces the practical skills ofExcel 2016 in data entry and editing,standardized paper production,automatic scoring,and statistics of test results.

Excel 2016;data entry;selective paste;electronic exampaper;automatic scoring

10.3969/j.issn.2095-7661.2017.04.023】

TP317.3

A

2095-7661（2017）04-0072-05

2017-06-17

洪金明（1964-），女，湖南寧鄉(xiāng)人，湖南郵電職業(yè)技術(shù)學院副教授，研究方向：計算機應(yīng)用技術(shù)。