洪金明

基于大数据时代下Excel的两则实用技巧研究

洪金明

（湖南邮电职业技术学院，湖南长沙 410015）

在大数据时代背景下，需要使用计算机分析处理的信息量越来越多，高效、快捷利用Excel数据库的重要性与日俱增。文章对Excel中的两个实用数据处理事例进行探索研究，介绍了Excel 2016在数据录入与编辑、制作标准化试卷、自动阅卷、成绩统计处理等方面的实用技巧。

Excel 2016；数据录入；选择性粘贴；电子试卷；自动阅卷

Office是现代企事业单位行政管理的一个常用办公软件，其中Word、Excel和Power Point这三大组件在日常及商务办公中是不可或缺的。

微软公司推出了Excel 2016制表软件，尽管其中大部分的常规功能在Excel 2010和Excel 2013中都有出现，操作方式也基本一致，但其中一少部分新功能无法在老版本中使用。新增功能包括树状图、旭日图、直方图、箱形图、瀑布图、三维地图功能组、预测函数，新增的功能数据工具有“获取和转换”、“管理数据模型”和“预测工作表”等。这些都是为适应“大数据时代”越来越宽泛的应用而拓展的。

1 Excel数据的录入与编辑实用技巧

1.1 巧用Excel的选择性粘贴功能

选择性粘贴（special paste）是Excel中经常使用的功能之一。在进行数据或图形粘贴、数据横竖转置、数据核对等过程中十分有用。数据粘贴途径不同，效果也不同。直接使用“粘贴”功能，原始输入的数据可以粘贴，但是经过计算之后所得到的数据因其附带了其他信息，直接采用复制、粘贴时数据不会显示，采取复制再特殊粘贴，只能把数据所代表的具体数值粘贴，附带的其他信息不会“粘贴”随移。

例如，要把Excel数据表中C列的数据全部粘贴到G列中，其操作步骤如下:第1步，复制C列数值，然后右击G列第1行所在位置，在快捷菜单中选择选择性粘贴命令，打开选择性粘贴对话框；第2步，在该对话框中选择数值选项，单击确定按钮，选择性粘贴即完成，其附带的其他信息也一并“粘贴”转移到G列中。

1.2 快速实现Word文本向Excel工作表的数据交换

Word适合制作表头比较复杂、但数据不多的表格，适合画各类斜线表头，可以拆分小表格。Excel则适合做数据较多、表头相对简单的表格，只能合并单元格和对合并后的单元格取消合并，不能对基本的单元格进行拆分。一般来说，将Excel的表格直接贴到Word文本里，一般不会发生错误码和数据丢失；但如果是把一个单元格中有多行文本的Word表格粘贴到Excel时，却会因为多行的关系，使粘贴上去的结果在Excel里变成多行。在Excel 2016中则可以通过执行下述程序代码来完成：

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间的数据交换.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 自由设置工作表区域

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

方法1：选中:10行按CTRL+Shift+下箭头，直到到达最后一行，选择隐藏就可以把10行以后的行数隐藏掉。选中第U列按CTRL+Shift+右箭头，直至到达最右一列，选择隐藏就可隐藏U列以后的列。

方法2：按Ctrl+A全选单元格→按Ctrl+9隐藏所有行→按Ctrl+0（零）隐藏所有列→按F5或Ctrl+G，弹出定位对话框，在引用位置输入想要显示的单元格区域如A1：H10，点确定→按Ctrl+Shift+9显示指定区域的行号→按Ctrl+Shift+0（零）显示区域列号，此时工作表中只显示指定区域的单元格，当然，以后同样可以插入新行新列。

2 教学考试方面的实用技巧

2.1 使用Excel制作标准化试卷

在推行MOOC课、微课辅助教学的今天，对学生课程知识和技能掌握情况的纸版考核方式已不太符合教学和讲评的需要，越来越多的教师应用电子试卷做在线考试，特别是使用Excel制作标准化试卷来提高练习效率，简化对试卷的批改对错、统分等简单重复操作。当然，要使得试卷制作规范合理，布局美观大方，是需要掌握一些实用技巧和多次实践的。

2.1.1 题型、答题要求和分值设定

在采用Excel制作标准化试卷时，出卷者可以根据知识点的重要性、考试的难易度、差别化考评学生对不同内容知识点和技能的掌握情况等评估目的的不同，对考试题型、答题要求和分值进行差别化设定和提出要求，并以醒目的形式告知参考者。以这些多样化的命题、答题、考生在限定范围内自选的形式检验考生对课程知识的掌握情况和差异程度，能够更加中肯地评价教学效果，发现学生的差异化优势。学生答题结束，点击“提交”后，计算机考试系统即可自动统计其得分。

2.1.2 卷面版式设计

首先启动Excel文件包，新建一个Excel空白文档，结合课程名称和考核旨意给定一个合适的文件名。把该工作表Sheet 1中的A-M列指定为试卷的使用范围，N列作为考生作答的填写输入区域，O列作为判断答案正确与否的给(记)分区域。在表格的1、2行中输入试卷标题，满分分值及其分值比重说明，班级、姓名、得分等待填的空格。根据出卷者的审美观、卷面格式和幅面大小、显示习惯对各栏目的字体类型和字号、加粗等项进行设计选定。为了与其它栏目的内容相区分和醒目起见，一般会在上述某个(些)栏目做加填底色处理。通过上述设置，可以得到如后面表1所示的工作表Sheet 1表头。

单击选中工作表Sheet 1的“A5”单元格，使用菜单命令“窗口-冻结窗口”把上述试卷表头冻结。然后在该工作表的适当位置，按照对考试题型、答题要求的归类分别输入，避免考生在答题要求上发生错误。

2.1.3 制卷技巧

在输入考题时，有可能会发生一些技术问题。如，在工作表Sheet 1的某一个单元格内输入更多的字符，需要格内换行，可以将光标定位在需要换行的位置，同时按下“Alt+Enter”组合键进行强制换行；或者在单元格的格式菜单中选择“对齐”标签，勾选“文本控制”中的“自动换行”功能。当试卷上需要插入图片做答题参考时，先选择好需要引用插入的图片，按下“Ctrl+V”组合键做快捷粘贴，或者启用菜单命令中的“插入-图片-来自文件”功能，在需要插入的位置对选择的图片做插入操作。

表1 Excel标准化试卷工作表Sheet 1的表头示例表

为了方便评阅试卷，快速区分各个题型的答题区域，可以对各个试题的文字部分、填写答案的区域、判定计分区域加上不同的底色做区分。制卷者可将表头中的N-O这两列隐讳利用，预先输入参考答案，并做隐讳加密处理，方便评卷时使用。

试卷制作好后，为了避免学生误删除或者修改试卷内容，必须对试题单元格做保护处理：选择整个工作表，选定菜单“格式→单元格→单元格格式”，在对话框中选择“保护”选项卡，勾选“锁定”复选框，输入设定的显示密码，再勾选“隐藏”复选框，以使考生不能看到此显示密码。对需要学生答题的操作单元格，如姓名、专业、班级，选择和填写答案项等分别做“解除加密”处理：将光标置于所在的单元格，选择菜单“格式→单元格→单元格格式”，在对话框中选择“保护”选项卡，取消对“锁定”复选框的勾选。

2.2 电子试卷自动阅卷分析过程的实现

2.2.1 各试题分值的设定和答案判断

对各试题分值的设定采用计算公式输入法：对于只有唯一正确答案、分值为2分的试题，先将光标定位在该题目行所在的M7单元格，双击选中，插入函数“=IF（L7=“”，“未做”，IF（L7=N7，2，0）”,即当 L7 单元格录入的内容为空时，显示“未做”，记为0分；当与N7单元格里的标准答案一致时，记为2分；也可以利用菜单命令“插入-插入函数”实现。

单击选中L7单元格，按住Ctrl键，连续选中其它的选择、判断及填空题中标准答案是唯一的且分值为2分的M列单元格，按下组合键“Ctrl+D”，实现上述公式的自动复制和填充。对于分值为1分，正确答案且不是唯一（有2个备选答案）的题目，也可采用上述函数，进行评判，仅需要将第二个选项的分值“2”改为“1”即可。

2.2.2 卷面得分的统计

先制作“成绩工作表”，其中设置卷面答案、标准答案、评分标准、各题得分和最后的总成绩栏目，结构如表2所示。

表2 卷面成绩统计工作表

在这个工作表中，卷面答案是利用单元格引用取自试卷上考生所填写的对应单元格的内容，标准答案则是出卷者输入的各题的正确答案，自动评分是利用Excel中的“IF”函数判断卷面答案与标准答案是否一致做给分处理，一致的按照分值设定给分，不一致的不给分。然后使用“SUM”函数对各大题求得分和，再用“SUM”函数求试卷总成绩。

需要注意的是，必须利用Excel的隐藏工作表功能和保护工作簿功能对本工作表做加密隐藏：选择“卷面成绩统计工作表”，在菜单中选择“格式→工作表→隐藏”，先把它隐藏起来；再在菜单中选择“工具→保护→保护工作簿”，于弹出的对话框中设置密码，并勾选“结构”。如此一来，考生就不能“反隐藏”卷面成绩统计工作表，也不能看到标准答案。

2.2.3 制卷关键点

制作Excel电子试卷的关键在于利用函数进行得分统计，因此，理解各种Excel的函数原理很重要。如果不是用于专用教室做限时考试，则在对答题时间的设置、计时和限制方面须利用技术手段进行控制。

与传统的纸版考试系统相比，Excel电子试卷的安全性较低，对于标准答案的加密隐藏处理如果处理不力，试卷就须作废。再者，利用Excel制作的电子试卷基本上是不变的，较难实现商业化考试系统那样的随机抽题、随即组卷，灵活性较差。利用Excel电子试卷进行考试时，必须借助多媒体教室和相应的教学软件进行“发卷”和“收卷”，难以实现网络化考试。

2.3 使用Excel函数ROUND和RAND随机抽取试题

Excel软件对随机函数“rand()”描述是:“返回大于或等于0且小于1的平均分布的随机数字。每次重新计算包含此函数的单元时，返回的数字都会更改。”例如，需要随机抽取100以内的正整数，可在相应单元格中输入=RAND()*100，随即就会出现一个小于100的随机正整数。依此可以实现考生差异化抽题考试，现介绍3种较常用而简便的抽题方法。

2.3.1 通过设置单元格格式

用鼠标右键点击单元格，出现右键菜单，选择“设置单元格格式”，出现设置单元格格式窗口，选定数字选项卡，在分类中选择数值，把小数位数改为0，按确定退出窗口。

2.3.2 运用整数函数int()

int()函数的意义是“将数值向下取整为最接近的整数”(不是4舍5入。上述使用设置单元格格式的方法是将数值4舍5入)，即在需要随机选取的单元格中输入“=int(rand()*100)”即可。

2.3.3 应用ROUND和RAND函数

ROUND的功能是按指定的位数对数值执行4舍5入。在需要随机数的单元格中输入公式“=ROUND（RAND（）*100，0）”。如图 1所示。

图1 取100以内随机正整数图

下面以AutoCAD课程为例，介绍随机抽题考试的实施方法。

学院开设的AutoCAD课程，期末考试时一般以全国计算机信息高新技术考试之“AutoCAD试题汇编”（绘图员级）为考核标准，学生考试时从该“试题汇编”中随机抽取。以往考试制卷时，都是手工输入题单打印，如图2所示。如果有50位考生参加考试，就要制作50份这样的不重复题单，为测试学生的绘图操作技能。

使用Excel函数ROUND和RAND实现无纸差异化考试的程序是，首先启动Excel，在新工作表设计出如图3所示框架。

图2 制作考试题单示意图

图3 抽题单框架图

然后在对应单元号的单元格中输入公式，如第二单元随机抽取的题号是1至20题，即在“二”单元号的单元格中输入公式“=ROUND(19*(RAND()),0)+1”。其他单元题号照此设限，对公式稍作修改就可指定抽取题号的范围，如对于通讯类学生，第六单元只要求其做第1到第10题，制作抽题单时在“六”单元号下输入公式“=ROUND(9*(RAND()),0)+1”。抽题单设计完成后如图4所示，完成后应对该工作表进行保护，以防被他人修改，并在评卷时核对选题是否符合要求。

图4 完成后的抽题单示意图

课程考试时，将该Excel文件通过教学软件发送给学生，学生打开后可自动随机获取一份抽题单，而且每个学生抽题得到的考试题号都不一样，这样既鼓励了学生课后加强自我训练，也避免了考试时相同试卷(题)间的相互拷贝(舞弊)行为。

2.4 巧用Excel进行学生成绩的统计分析

应用Excel电子试卷考试结束后，可以很方便地利用其数据排序功能对全部参考者的成绩进行排序，分出优秀、良好、中等、及格、不及格各等次的考生名单，很快求出平均成绩。

在统计学生多门课程的成绩绩点用于奖学金评定时，使用Excel电子试卷的统分功能能很便捷地做出统计。通常，同年级同专业多个班的学生各课程的总学分积是按照各个相同课程的学业成绩确定的，即：课程学分积=课程学分×绩点分，其中绩点分是按照各课程的学业成绩档次来给定的(通常是优秀为4、良好 3、中等 2、及格 1、不及格为 0)。

利用Excel公式计算多个考生多门课程的总学分积，方法如下：先建立一个学生成绩排名的Excel工作表，对参选学生各科成绩的工作表做“选择性粘贴/粘贴链接”，复制到学生成绩排名的Excel工作表中，分别、逐个点击每位学生的成绩单元格，然后点击编辑栏，在编辑栏内分别输入各门课程的绩点分计算公式：f(x)=课程学分×绩点分。

3 结束语

Office是现代企、事业单位常用的办公软件，微软公司的Excel 2016软件是为适应“大数据时代”越来越宽泛的应用而拓展的。在大数据时代背景下，高职教育也应与时俱进。作者对Excel 2016中与教学、考试相关的两个实用数据处理事例开展了探索研究，希望与同行专家教师们分享该经验。

[1]罗刚君.Excel 2016实用技巧自学宝典[M].北京:电子工业出版社,2016.

[2]恒盛杰资讯.Excel 2016办公专家从入门到精通[M].北京:机械工业出版社,2016.

[3]洪金明.基于函数的Excel软件的应用技巧研究[J].湖南邮电职业技术学院学报,2015(3):22-27.

[4]周磊.翻转课堂在高职院校网络类课程中的教学模式研究[J].湖南邮电职业技术学院学报,2016(3):132-134.

[5]魏德才，程倩.创新型教学模式下高校专业课课堂教学中多媒体技术的应用研究[J].湖南邮电职业技术学院学报,2015(1):107-111.

[6]前沿文化.别说你懂Word/Excel——500招玩转Word/Excel办公应用[M].北京:科学出版社,2013.

[7]启典文化.Word/Excel/PPT商务办公应用于技巧[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-），女，湖南宁乡人，湖南邮电职业技术学院副教授，研究方向：计算机应用技术。