电子表格制作技巧教程

1、常用电子表格软件介绍 活动1  常用电子表格软件介绍 一、Excel Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。 在教育教学领域中，教师可用excel进行学生成绩统计，数据分析，制作评价量规、制作成绩通知单等工作。 二、百会格格 百会格格是百会旗下的一款在线电子表格编辑器。百会

2、格格对个人用户完全免费，无需任何代价即可轻松享受在线办公乐趣。只需要一个网络浏览器，您就可以随时随地访问并编辑您的电子表格。支持导入常用格式表格与转换其他格式，支持xls、sxc、ods、csv 及tsv等常见表格文件。您可以共享电子表格给朋友、同事，或在浏览器中浏览/编辑共享的电子表格。例如：允许多位教师同时对学生成绩统计表进行编辑，节省了成绩汇总的麻烦。 三、Excel OnlineExcel Online 是Office Online中的一款在线电子表格编辑器。它结合了常用的 Office 功能和实时共同创作功能，让您可以与朋友和家人免费协作处理共享的文档、演示文稿、电子表格和笔记本。它

3、和百会格格的性质的一样的。 学生成绩的录入技巧活动2  学生成绩的录入技巧 实例介绍 张老师在制作学生成绩统计表时，从输入数据这步就开始犯难了，他主要有以下烦恼： 问题一：在输入学号时他觉得一个一个的输入真是太麻烦了，有没有简单快捷的方法呢？ 问题二：由于学生比较多，电脑屏幕不能一下显示全部的学生成绩，再输入数据时要上下或左右滚动，这时就会看不到学生的名字，或者各科的名称，经常会输错。 问题三：每当到单元测验、期中、期末考试结束后，看着好几个班级的学生成绩需要录入excel中，张老师都特别的犯难，因为收上来的试卷顺序并不是按照excel中已有的学生名单的顺序排列的，每次都要先把学生的

4、试卷按名单顺序排好，再依次输入。这无形中加大了张老师的工作量。 张老师除了平日的教学工作外，有经常上网学习的习惯，会学习MOOC、视频公开课、云课堂这些学习资源。在一次学习中他偶然发现了解决这些问题的方法，这令他欣喜若狂。 解决上述问题的方法和技术： 解决“问题一”的技术方法：自动填充柄。 解决“问题二”的技术方法：冻结窗格。 解决“问题三”的技术方法： IFERROR 函数、VLOOKUP函数。 下面介绍其操作步骤。 第1步：解决“问题一” 张老师根据需求制作了自己的学生成绩统计表，如图： 然后输入学号，输入两个学号后，同时选中这两个学号，把鼠标放到单元格的右下角后，鼠标会变成一个黑色的十字

5、，这个就是自动填充柄，如图： 然后向下拉动鼠标，excel 2007会按照你输入的数据规律自动为你计算出之后的数据，如图： 此招要点：至少输入两个数据。待鼠标变成黑色十字后向下拉动。别看它只是一个小小的技巧，却节省了张老师不少时间。 温情提示：学号的“数据类型”要修改，excel 2007在输入数字后，会把单元格自动默认为数值类型，但是我们的学号虽然外观是由数字组成，但实质是描述性的文本，自然是文本类型了，如果这个时候我们不修改它们的数据类型的话，在把“学号”这一列拉窄后，就会出现如下图的状况。 2E+06表示2的后面还有6位数，这是由于列宽不够的原因。在知道了这种状况之后，我们要如何避免呢？

6、解决方案：选中“学号”列>右击鼠标，选择“设置单元格格式”>在“数字”选项卡下选择“文本”>单击“确定”。文本类型的单元格左上角会有一个绿色的角，这是文本类型单元格的标记，如图：  第2步：解决“问题二” excel2007为我们提供了“冻结窗口”的命令。步骤：选中“C4”单元格>“视图”选项卡下>冻结窗口>冻结拆分窗格，如下图。然后我们的“13”行和“A到B”列就被冻结了。极大地方便了教师的查阅。 第3步：解决“问题三” 最快速的录入成绩的方法应该是按试卷的顺序在Excel 2007中逐个输入学号和分数，由电脑按学号把成绩填入成绩表相应学生的记录

7、行中。在Excel中实现这个要求并不难。 首先需要学习2个excel 2007中的函数： 1.IFERROR 函数 说明：如果公式计算有错误则返回您指定的值；否则返回公式结果。使用 IFERROR 函数来捕获和处理公式中的错误。 语法：IFERROR(value，value_if_error)参数： 参数 简单说明 Value是需要检查是否存在错误的参数 Value_if_error是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!注解：如果value或value_if_error是空单元格，则I

8、FERROR将其视为空字符串值("")。 案例中出现的语句：=IFERROR(VLOOKUP(A8，L:M，2，FALSE)，"")这句话的意思就是：VLOOKUP(A8，L:M，2，FALSE)这段程序执行有无错误，无错误则返回这段程序的计算结果，有错误则将其结果视为空。 2.VLOOKUP函数 说明：是按列查找，最终返回该列所需查询列序所对应的值。 语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)。 参数： 参数 简单说明 输入数据类型 lookup_value要查找的值

9、数值、引用或文本字符串 table_array要查找的区域 数据表区域 col_index_num返回数据在区域的第几列数 正整数 range_lookup模糊匹配 TRUE（或不填） /FALSE温情提示：VLOOKUP最容易出错的地方是查找区域的首列必须含有查找的内容。例如：1个表a列是序号，b列是姓名，c列是身份证，你在d1输入其中的一个姓名，在e1得到其身份证的公式为=vlookup(d1，b:c，2，0)，而不能是=vlookup(d1，a:c，3，0)。 案例中出现的语句：VLOOKUP(A8，L:M，2，FALSE)这句话的意思是在LM列中查找到单元格A8的值后，把该值在LM中对

10、应的第2列的值输出。如图： 学习完了这两个函数后，我们就可以在“输出结果”这列输入语句了，我们在上图中找到N4这个单元格，输入=IFERROR(VLOOKUP(A4，L:M，2，FALSE)，"")，然后把光标放到N4单元格的右下角，待鼠标变成黑色十字后向下拉动，一直拉到最后一名学生。随意点击N列的单元格，就会发现每个单元格都输入了语句，唯一的区别就是：要查找的数据值在A4A33之间变动（案例中一共有30个学生）。这些步骤都做完后，教师就可以拿出试卷，在L和M列输入学号和成绩了，如图，全部输入完成后，把“输出结果”列的数据复制到相应的成绩列中即可。 温情提示：有些学校，学生

11、没有学号，教师可直接为班级的学生编号，然后要求在考试中写上自己的学号即可。“学号”和“输入学号”这两列的单元格数据格式一定要一样，否则查找时，函数不会起作用。这里介绍另一种将单元格设置为文本类型的方法，如上图，选中L列>右击，选择“设置单元格格式”>在“数字”选项卡下，选择“文本”即可。那么我们如何用肉眼直接辨别出数字类型和文本类型的单元格呢？excel 2007非常人性的做到了，文本类型的数据左对齐，数字类型的数据右对齐，如上图。 技能拓展 其他录入数据录入技巧： 录入数字时，须遵照下面的规则： 在数字前输入的正号“+”被忽略；可以在数字中包括逗号，如：26,468,508； 数

12、值项目中的单个圆点“.”作为小数处理；输入分数（如5/7）时，要先输入“0”和空格，然后输入分数，否则系统将按日期对待； 对于全部由数字组成的字符串（如电话号码、邮政编码等），常常当作字符处理。为了不使系统把它当作数字看待，输入时，可在其前面添加一个英文状态下的单引号（例如：24346688）。 日期的输入形式有多种,常见的日期格式为：mm/dd/yy、dd-mm-yy、例如我们要输入1999年9月10日，只要输入09/10/99或者10-09-99就可以了。若想在单元格中输入当前日期，只要按组合键Ctrl + ；即可。 活动4  在线编辑发布学生成绩 这里我们以在线电子表格工具百会

13、格格为例。百会格格是支持共享和协作功能的在线表格编辑器。其是百会Office中的一个组件，百会Office不仅具备传统Office的基本功能，还开创性的将常用办公工具与数据存储、协作办公、云计算进行无缝整合，打破了传统Office的局限，使办公更为方便、快捷！ 百会格格独具特色的共享和协同编辑功能，可实现多人同时编辑同一文档，极为便捷。 实例介绍 以往求实中学（化名）在学期末汇总学生各科成绩时，是各科任教师分别输入学生成绩，然后再汇总到一起，由于有的学生成绩有时候会弄错，还要再找到汇总后的文档进行修改，非常麻烦，现在他们都是利用百会格格进行各科成绩汇总，只需要一个百会格格在线电子表格，各科任教

14、师同时编辑这一份电子表格即可，每个教师的成绩有什么问题，直接登录百会格格账号，找到这个文档，就能直接修改了。成绩汇总完成后，直接把电子表格导出到本地就可以了。下面介绍其操作步骤。 第1步：注册百会格格 各科任教师必须都注册一个百会格格账号，才能登录百会格格，再编辑共享的文档。打开浏览器，输入网址 图表 14 单击右上角“免费注册”，进入图表15的界面，完成注册后，登录即可。 图表 15 登录后，进入百会格格在线编辑界面，如图16： 图表 16 第2步：在线编辑发布学生成绩 步骤一：共享文档 首先，先把此文档共享给诸位教师，点击“共享”选项卡下的共享，如下图： 单击“共享”后，如下图：添加成员共

15、同编辑，邮箱必须是该成员在百会注册完的邮箱，然后选择“共同拥有者”，单击“共享”。这就完成了该文档的共享，该成员即可登录自己的百会格格，对该文档进行编辑。 步骤二：协同编辑 运用百会格格的协作功能，教师可先利用前面讲的excel成绩录入技巧，在本地计算机中把各科的学生成绩输入完毕，然后在直接把全部成绩粘贴到百会格格中。各科任教师都做完这个工作后，再由一位教师进行成绩统计，百会格格的数据统计方法和excel2007 一样，例如“求总分”，如下图： 完成编辑后，即可点击“文件”>“保存”或者“导出”excel工作簿，如下图： 这样一个成绩汇总的电子表格就在协同功能下，做好了。如需修改，直接登

16、录百会格格修改即可。 技能拓展 利用在线电子表格网站的协同编辑功能进行学生成绩统计很便捷，文档也不用拷来拷去，直接上网查看就行，不过也有其弊端，例如：在网速不够畅通的情况下，在线编辑会有些卡，这样会影响者的心情。不过科技在发展，网速，硬件等很快会得到提升，我们要培养的是这种在线化的思维，对于教师今后的教育教学有深渊意义。 除了教师协同编辑成绩单外，学生也可利用百会格格实现合作学习。教师在实际的应用中要善于思考，善于发现其他功能。 教学评价数据的处理和分析（一） 活动3  学生成绩的统计 每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑已经普及的今天，我们就把这项繁杂的工

17、作交给Excel 2007去自动完成吧。 这里使用“各科成绩统计表”。 一、求总分 步骤：选中“I4”单元格>在“公式”选项卡下单击“自动求和”>单击“求和”>回车，如图。Excel 2007会为我们自动计算出第一位同学的各科总分，利用自动填充柄工具，把剩下的学生成绩填充完整。“自动求和”下的5个函数是我们在统计成绩时经常用的，非常便捷。 二、求平均分 步骤：选中J列>右击后选择“设置单元格格式”>“数字”选项卡下选择“数值”，小数位数设为1>点击确定。如图： 选中“J4”单元格>在“公式”选项卡下单击“自动求和”>单击“平均值”选中第一位学生所

18、有科目的成绩（即C4：H4）>回车，利用自动填充柄工具，把剩下的学生平均分填充完整，所有的学生平均分就都计算出来了。和求总分的步骤一样。 总分、平均分求出来之后，往往需要进行大排名，首先来学习一个函数：RANK函数。 说明：计算一个数在一组数中的排名。 语法：RANK(number， ref， order)。 参数： 参数 简单说明 输入数据类型 number要排名的第一个单元格 必须有一个数字值 ref要排名的区域 数据表区域 order逻辑值 0降序，1升序 步骤：选中“K4”单元格>在“公式”选项卡下单击“其他函数”>单击“统计”，选择RANK函数，如图： 然后在“nu

19、mber”处选择“I4”单元格，在“Ref”处选择全部学生的总分区域后按F4，把其转化成绝对引用后，点击“确定”，如图。利用自动填充柄工具，把剩下的学生排名填充完整，所有的学生排名就都计算出来了。 教学评价数据的处理和分析（二）三、求最高分，最低分步骤：同计算总分的步骤一样，选择函数时选择“最大值”或“最小值”即可。最后制作完成的各学科成绩统计表，如图：四、标准分标准分是在学生成绩服从正态分布的假设前提下做出统计。标准分能够比较科学、合理的反应学生学习的状况，同时还能反映学生成绩在团体中的位置。标准分Z用公式表示为：Z=（X-X）/S。其中：X为该次考试中考生个人所得的原始分；X为该次考试中全

20、体考生的平均分；S为该次考试分数的标准差。步骤：我们以求语文科目的标准分为例，选中“I4”单元格，根据标准分的计算公式Z=（X-X）/S，AVERAGE是求平均数的函数，STDEV是求标准差的函数。所以在其中输入“=(C4-AVERAGE(C$4:C$33)/STDEV(C$4:C$33)”，如图，然后回车。利用自动填充柄工具把剩下的学生语文标准分填充完整。然后依据上面的方法，计算出其他科目的标准分。通过转换后得到的标准分Z在一般情况下都带小数，而且会出现负值，实际使用时不太方便，所以还要对Z分数进行线性变换（T变换）：T=100Z+500。公式中取500为平均分，100为标准差。我们以直接求

21、最后的各科标准分和为例选中“J4”单元格，输入=INT(I4*100+500)，如图，然后回车。这里INT函数是将一个数值向下取整为最接近的整数的函数。利用自动填充柄工具把剩下的学生标准分总分填充完整，即得出了学生标准分总分。附录：统计常用函数：1.求和： =SUM(D4:D33) 对D4到D33这一区域进行求和；2.平均数： =AVERAGE(D4:D33) 对D4到 D33这一区域求平均数；3.排名： =RANK(D4，D$4:D$33) 对30名学生的成绩进行排名；4.等级： =IF(D4>=85，"优"，IF(D4>=74，"良"，I

22、F(D4>=60，"及格"，"不及格")5.学期总评： =D4*0.3+E2*0.3+F2*0.4 假设D列、E列和F列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；6.最高分： =MAX(D4:D33) 求D4到D33区域（30名学生）的最高分；7.最低分： =MIN(D4:D33) 求D4到D33区域（30名学生）的最低分；8.分数段人数统计：（1）=COUNTIF(D4:D33，"100") 求D4到D33区域100分的人数；（2）=COUNTIF(D4:D33，">=95")- CO

23、UNTIF(D4:D33，"100") 求D4到D33区域9599.5分的人数；（3）=COUNTIF(D4:D33，">=90")- COUNTIF(D4:D33，">=95") 求D4到D33区域9094.5分的人数；（4）=COUNTIF(D4:D33，">=85")- COUNTIF(D4:D33，">=90") 求D4到D33区域8589.5分的人数；（5）=COUNTIF(D4:D33，">=70")- COUNTIF(D4:D33，

24、">=85") 求D4到D33区域7084.5分的人数；（6）=COUNTIF(D4:D33，">=60")-COUNTIF(D4:D33，">=70") 求D4到D33区域6069.5分的人数；（7） =COUNTIF(D4:D33，"<60") 求D4到D33区域60分以下的人数；说明：COUNTIF函数也可计算某一区域男、女生人数。如：=COUNTIF(C2:C256，"男") 求C2到C256区域（共255人）男性人数；9.优秀率： =SUM(D34:D37)/3

25、0*100，D34到D37是分数在85分以上的学生数；10.及格率： =SUM(D34:D39)/30*100，D34到D39是分数及格的学生数；11.标准差： =STDEV(D4:D33) 求D4到D33区域(30人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）；12.条件求和： =SUMIF(B4:B33，"男"，D4:D33) 假设B列存放学生的性别，D列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和。 两个班学生的成绩差异分析 五、两个班学生的成绩差异分析 实例介绍 在一次考试成绩统计中，由于张老师所带的班的学生缺考很多，那么单纯的用该班平均分、及格率、优秀率等来考核一个班级教学就显得不公平了，所以他决定用excel中的方差检验来比较该班与其他班级学生成绩有无显著性差异，以此来科学的统计学生的成绩。如图，是两个班的平均成绩，二班高于一班，那么二班真的比一班强很多吗？，下面我们就来进一步做差异性分析。 第1步：添加“数据分析”功能 由于excel在默认情况下，在选项卡中是不显示方差分析这些功能的，所以在开始计算之前，我们要先把该项功能在excel选项卡中显示出来。在“文件”选项卡下，选择“选项”，点击“分析工具库”，单击按钮“转到”。如图： 回到主界面，在“数据”选项