电子表格软件

电子表格软件

学习指南（操作要点）

（1）软件介绍

我们经常会对数据进行处理，例如记录某次活动的各项支出，对学习成绩进行分析、排序等等，电子表格软件就是方便数据处理的软件。电子表格软件是一种以表格形式组织、分析、处理数据的计算机软件，它就像一张聪明的纸，可以自动对其上的数据进行计算，并能制作出漂亮的表格和图形，极大地增强了数据的可视性。常用的电子表格软件有Microsoft Excel、Open Office Calc、WPS Excel等，其操作方式类似，我们以Microsoft Excel为例讲解。

（2）基本概念

图a-1是Microsoft Excel的工作界面，每一个文件是一个工作簿，每一个工作簿包含多张工作表，每个工作表都是相对独立的，可以存放不同的内容，每一工作张表都由行和列组成，行和列的交叉点就是单元格，每个单元格都由列和行的二维坐标唯一表示，列用字母表示，而行用数字表示。如图a-1中，工作簿包含三个工作表“sheet1”、“sheet2”和“sheet3”，其中，“sheet1”中被选中区的左上角单元格表示为“B5”,右下角单元格表示为“E10”，整个区域表示为“B5: E10”。

图a-1 Microsoft Excel 工作界面

（3）录入和格式化数据表

电子表格中的数据主要有数字、文本、日期和时间三种。通常会格式化数据表以方便读写和打印输出，这可以通过单元格设置来完成。单元格设置是指针对单元格的显示方式进行格式化操作，主要包括：数字类型设置，如设置显示小数的位数、正负数的显示格式，以及是否以货币形式显示数值；日期时间类型设置，如日期时间的显示方式（按年月日、月日年，还是日月年的方式显示）；文本的显示格式。另外还可以设置单元格数据的字体、字型、字

号、颜色，为单元格设置边框、背景颜色等。

图a-2 设置单元格格式对话框

如图a-2就是Excel中设置单元格格式对话框中设置数字选项卡界面。

有时我们希望突出显示所关注的单元格或单元格区域，强调异常值，例如在成绩表里突出显示90分以上的成绩，但是由于这些单元格出现在数据表中的哪个位置确是不确定的，所以不能事先确定突出显示哪些单元格，这时可以设置条件格式。条件格式基于条件更改单元格区域的格式，如果条件为真，则以特别的背景、字体、边框突出显示单元格格式；如果条件为假，则仍正常显示单元格格式。图a-3显示的设置条件格式菜单项。

图a-3 设置条件格式菜单

（4）公式与函数

在使用电子表格时，有些数据是通过公式计算得到的，这时可以编写公式来完成，如图a-4中，第K列的总分是由其前面的六个科目求和而成，也就是说，K2中的数据是E2、F2、G2、H2、I2、J2六个单元格的数据之和，因此可以在K2中输入公式“=E2+F2+G2+H2+I2+J2”，这样，当我们修改其中某项数据时（如将E2中65改为75），K2中的公式会自动重新计算其值（由295变为305）。常用的加减乘除对应的符号分别是键盘上的“+、-、*、/”，“^”代表乘方，可以用小括号来改变运算的次序。

图a-4 Excel中的公式与函数

函数是电子表格中定义好的公式，需要时直接使用即可。例如前面的K2就可以使用求和函数SUM，在K2中输入“=SUM(E2:J2)”即可。Excel提供了一个庞大的函数库，共有400多个，利用这些函数可以完成各种计算，这些函数虽然作用不同，但其使用方式是类似的，我们应熟练掌握一些在工作、生活中常用的函数。

（5）单元格的引用

在如图a-4的计算中，单元格“K2”中的数据是其左侧六个单元格的数据之和，表示为“=SUM(E2:J2)”，在这一单元格下面的单元格“K3”、“K4”等也都是其左侧六个单元格的数据之和。如果我们选中单元格“K2”进行复制，将它粘贴到单元格“K3”、“K4”中，会发现单元格中“K3”中的公式自动表示为“=SUM(E3:J3)”，单元格中“K4”中的公式是“=SUM(E4:J4)”，也就是说，公式的含义不变——“等于左侧六个单元格的数据之和”，而地址自动解析为新的地址——“当前单元格左侧六个单元格”，在公式和函数中地址的这种

引用方式被称为“地址的相对引用”。

相对引用为电子表格数据操作带来极大的方便，但有时在把公式复制到新的单元格时，不希望改变公式中原来的引用地址，例如图a-5中，单元格C2中的公式为“=B2/$B$7”，当将C2复制到C3时，C3中的公式为“=B3/$B$7”，其中公式的前一半地址B2变为B3，这是地址的相对引用，而公式的后一半地址$B$7没有发生任何改变，这样的地址引用方式被称为“地址的绝对引用”。显然，这样的设置是合理的，如果C2中公式的地址都采用相对引用方式“=B2/B7”，复制到C3后公式为“=B3/B8”，由于B8中没有数据，被认为是数字0，这样就会产生一个除0错误，显然也不符合原意。

图a-5 地址的相对引用与绝对引用

（6）数据图表

图表用于以图形形式显示数值数据系列，这样会更容易理解大量数据以及不同数据系列之间的关系。既可以以二维图的形式显示，也可以以三维图的形式显示。Excel以二维坐标系统描述图中信息，通常用水平的X轴对可区分的所有对象进行分类，用垂直的Y轴表示当前对象的某一属性的数值大小，通常称X轴为分类轴，Y轴为数值轴。

创建图表主要包含两个操作：选取数据源和确定图表类型。在选取数据源时应注意要选中分类依据的标题信息，这样有利于图表的理解。Excel中包含多种图表类型，如图a-6所示，其中柱形图、折线图、饼图和条形图最为常用，应根据需要选择合适的类型。

图a-6 选择图表类型命令

图表中包含许多元素。默认情况下会显示其中一部分元素，而其他元素可以根据需要添加。通过将图表元素移到图表中的其他位置、调整图表元素的大小或者更改格式，可以更改图表元素的显示。还可以删除不希望显示的图表元素。

图a-7 图表区的组成

Excel中的图表元素如图a-7所示，1是图表区，包含整个图表及其全部元素；2是绘图区；3是在图表中绘制的数据系列，也就是在图表中绘制的相关数据点，这些数据源自数据表的行或列，图表中的每个数据系列具有唯一的颜色或图案；4是X（分类）和Y（值）坐标轴；5是图例，用于标识为图表中的数据系列或分类指定的图案或颜色；6是图表标题或坐标轴标题；7是数据标签，可以用来标识数据系列中数据点的详细信息。

（7）数据处理

对数据进行排序是数据处理最为常用的一种操作方式。我们可能需要执行以下操作：按价格由低到高地显示商品列表，或按成绩由高到低的顺序显示某科目的考试成绩，或是按姓氏笔画顺序显示参加某项活动的人员名单。对数据进行排序有助于快速直观地显示数据并更好地理解数据，也有助于组织并查找所需数据，以做出更有效的决策。

可以对一列或多列中的数据按文本、数字，以及日期和时间进行排序。甚至还可以按格式（如单元格颜色、字体颜色）进行排序。当某些数据要按一列中的相同值进行分组，然后对该组相等值中的另一列进行排序时，可以按多个列对数据进行排序。例如，如果一个数据表中包含多个班级的考试成绩，就可以先按班级进行分组（升序），然后在一个班级内由高到低显示成绩（降序），如图a-8所示。

图a-8 排序对话框

当数据量比较大的时候，可以通过筛选操作来减少数据的显示数量，筛选过的数据仅显示那些满足指定条件的行，并隐藏那些不希望显示的行。例如只显示图a-4中语文、数学、英语85分以上的学生，结果如图a-9所示。筛选数据之后，对于筛选过的数据的子集，不需要重新排列或移动就可以复制、查找、编辑、设置格式、制作图表和打印。

图a-9 数据筛选

通过使用Excel表中的分类汇总命令，可以自动对数据进行分类并进行汇总和统计，如求和、求平均数等。如图a-10所示是对图a-4按性别分类汇总求各科平均值的结果，在图中，女同学的细节信息被折叠隐藏了。

图a-10 分类汇总

数据透视表是Excel中分析、重组数据，获得数据统计信息的一种有效工具。如果要分析相关数据的汇总值，尤其是在统计较大的数据列表并对每个数据进行多种不同类型的统计时，例如同时求平均值和计数时，通常使用数据透视表。如图a-11所示是对图a-4中成绩同时进行计数和求平均值操作的结果。

图a-11 数据透视表

对数据透视表中的数据可以直接以图表的形式直观地显示出来，这就是数据透视图表，如图a-12所示。

图a-12 数据透视图表

（8）常用函数

1)最常用函数

SUM：求和。例如求区间“B1:B5”的和“=SUM(B1:B5)”

AVERAGE：求平均值。例如求区间“B1:B5”的平均值“=AVERAGE(B1:B5)”

COUNT：计数，单元格中的数据可以是任何类型（数字、文本、日期）。例如求区间“B1:B5”的数据的个数“=COUNT(B1:B5)”

MAX：最大值。例如求区间“B1:B5”的最大值“=MAX(B1:B5)”

MIN：最小值。例如求区间“B1:B5”的最小值“=MIN(B1:B5)”

注意，在上述函数中，如果区域或单元格引用参数包含文本（count除外）或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。例如，如果B2中没有输入数字，则函数“=AVERAGE(B1:B5)”相当于求4个数的平均值（B1、B3、B4、B5），而不是5个数的平均值（B1、B2、B3、B4、B5）。

2)条件函数

有时在计算时，数据必须符合某一条件才能参加计算，例如在成绩单中，如果用-1代表缺考，那么在求参加考试的人数（计数）、求平均分时就不应该将其统计在计算结果中，这时可以使用带条件的函数解决此类问题。在进行数据比较时使用的比较运算符包括等于（=）、大于（>）、大于等于（>=）、小于（<）、小于等于（<=）。

SUMIF：返回某个区域内满足给定条件的所有单元格的和。例如求区间“B1:B30”的和，条件是单元格的值大于-1，表示为“=SUMIF(B1:B30,”>-1”)”

AVERAGEIF：返回某个区域内满足给定条件的所有单元格的平均值。例如求区间“B1:B30”的平均值，条件是单元格的值大于等于0，表示为“=AVERAGE(B1:B30,”>=0”)”

COUNTIF：计算符合给定条件的区域中的非空单元格数。例如求区间“B1:B30”的中的非空单元格数，条件是单元格的值大于等于0，表示为“=COUNTIF(B1:B30,”>=0”)”

IF：根据对指定的条件计算结果为“真”或“假”，返回不同的结果。如B2中的数大于等于60，则显示“合格”，否则显示“不合格”，表示为“=IF(B2>=60,”合格”,”不合格”)”。

可以嵌套使用IF函数来完成多种情况的处理，如果B2中数大于等于90显示“优秀”，大于等于80显示“良好”，大于等于60显示“合格”，否则显示“不合格”，可表示为“=IF(B2>=90,”优秀”, IF(B2>=80,”良好”, IF(B2>=60,”合格”,”不合格”)))”。

AND：如果所有参数为“真”，则返回“真”，否则返回“假”。

OR 如果任意一个参数为“真”，则返回“真”，否则返回“假”。

这两个函数的一种常见用途就是扩大用于执行逻辑检验的其他函数的效用。例如，IF 函数用于执行逻辑检验，它在检验的计算结果为“真”时返回一个值，在检验的计算结果为“假”时返回另一个值。通过将AND或OR函数用作IF 函数的第一个参数，可以检验多个不同的条件，而不仅仅是一个条件。如果B2的值大于等于90并且小于等于100，则显示“优秀”，否则为“合格”，表示为“=IF(AND(B2>=90,B2<=100),”优秀”,”合格”)”。

3)数学函数

ROUND：可将某个数字四舍五入为指定的位数。例如，如果单元格A1 含有23.7825并且希望将该数字四舍五入为小数点后两位，则可以使用公式“=ROUND(A1, 2)”，此函数的结果为23.78；若使用公式“=ROUND(A1, 0)”，则函数的结果为24。

INT：将数向下取整至最接近的整数，例如公式“=INT(B2)”，若B2中的数据是7.8，则函数的结果为7；若B2中的数据是-7.8，则函数的结果为-8。

MOD：返回两个整数相除的余数。例如“=MOD(3, 2)”，返回值1；“=MOD(7, 4)”，返回值3。

RAND：返回0和1之间的随机数。不需要参数，例如“=RAND()”。

RANDBETWEEN：返回指定数之间的随机数。例如产生1到100之间的随机数，表示为“=RANDBETWEEN(1,100)”。

这两个函数有时用于打乱数据表中数据项的顺序（产生随机数后再排序）。

4)字符串函数

LEN：返回文本字符串中字符的个数。例如“=LEN(“中华人民共和国”)”，返回值为7。

LOWER：将一个文本字符串中的所有大写字母转换为小写字母。如“=LOWER（”Hello World”）”返回值为“hello world”。

UPPER：将一个文本字符串中的所有小写字母转换为大写字母。

LEFT：返回文本值中最左边的字符。如“=LEFT(”Hello World”,5)”返回值为“Hello”。

RIGHT：返回文本值中最右边的字符。如“=RIGHT(”Hello World”,5)”返回值为“World”。

MID：从文本串中的指定位置开始返回特定数目的字符。如“=MID(”Hello World”,3,5)”返回值为“llo W”。

可以使用这个函数从身份证号中获得用户的生日信息，如A2中存放的是某人的18位身份证号，可以用公式“=MID(A2,7,8)”获得其生日。

TRIM：除了单词之间的单个空格外，清除文本中所有的空格。通常用于删除字符串首尾的空格，如“= TRIM (”Hello World ”)”，得到字符串“Hello World”。

FIND：在其他文本值中查找文本值（区分大小写），用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。如“=FIND(“l”,”Hello World”)”返回值为3；“=FIND(“l”,”Hello World”,5)”返回值为10，因为从第二个字符串的第5个位置（o）开始查找。

SEARCH：在其他文本值中查找文本值（不区分大小写）。与FIND函数用法相同，只不过查找时不区分字母的大小写。

可以使用“&”运算符将两个字符串连接成为一个字符串，例如“=A1&B1”公式表示将

单元格A1中的内容与单元格A2中的内容连接成为一个字符串。若A1中存放的是“hello”，A2中是“world”，则返回值是“helloworld”。

5)日期和时间函数

TODAY：返回当天日期。此函数没有参数，例如“=TODAY()”返回当天日期。

YEAR：返回某日期对应的年份。例如“=YEAR(“2014-5-25”)”返回2014。

MONTH：返回某日期对应的月份。例如“=YEAR(“2014-5-25”)”返回5。

DAY：返回某日期对应的日。例如“=YEAR(“2014-5-25”)”返回25。

可以用日期函数计算年龄，如A2中存放的是生日，则公式“=YEAR(TODAY())-A2”可计算其年龄。

（9）打印和保护数据表

有时候我们需要打印输出工作表，与字处理软件页面视图下的所见即所得的打印输出不同，由于电子表格每页上数据量很大，往往一页打印不下，为了保证打印输出的效果，需要注意以下几个方面：

对于一个数据量较大的数据表的打印输出前，通常需要设置顶端标题、左端标题，例如打印图a-4所示的数据，因为数据行较多，需要打印两页，如果没有设置顶端标题，第二页的打印效果如图a-13所示，而将第一行设为顶端标题，打印效果则如图a-14所示，显然标题的打印使得图a-14的数据可读性更强。如果数据的列较多（例如有更多的科目），一页打印不下，还应该将左侧的列（A列和B列）设为左端标题，这样就不会出现某些打印页上只出现某科目的成绩而不知这一成绩是哪位学生的情况了。

图a-13 不设置顶端标题打印

图a-14设置顶端标题打印

图a-15所示是打印页面设置对话框，除了设置顶端标题行和左端标题列项外，打印顺序和页眉/页脚项在打印时也会经常被用到。

图a-15 页面设置对话框

因为Excel表格中的所有内容都是可以编辑的，因此在使用时可能会无意中破坏了表格的内容，这在多个人使用同一个工作簿时问题尤为突出。例如，不小心修改了单元格中公式的内容，造成整个数据计算上的错误。为了避免上述错误，防止用户意外或故意更改、移动或删除重要数据，可以使用保护工作表功能将工作表锁定即可。一旦锁定了工作表，用户就不能更改工作表中的内容了。

图a-16 保护工作表

如图a-16显示的是Excel中保护工作表对话框，通常应设置一个密码，以防止其他人随意取消锁定。如果允许用户编辑部分单元格，例如图a-16中的“D2:I21”，可以在设定保护工作表之前设定允许用户编辑区域，这样在设定保护工作表之后，用户只能编辑事先设定的允许用户编辑的单元格区域，而无法更改其他区域的内容。

实践指导（实践任务）

实践一、基本操作

任务一、数据录入和处理、单元格格式化

任务目标

（1）熟悉Excel基本操作

（2）熟练使用简单公式和函数

（3）熟练掌握格式化工作表

知识清单

（1）数字、文字、日期录入

（2）单元格的选择（单元格、区域、整行、整列、整个工作表）

（2）公式

（3）函数（求和、最大值、最小值）

（4）填充柄

（5）单元格格式化（保留两位小数、边框、底纹）

（6）添加、复制、重命名工作表

任务要求

（1）录入图a-17所示数据，并格式化工作表，命名此工作表为“数据表”。

图a-17 实验数据及其格式化

（2）计算显示时间段内数据的开盘价（第一天开盘价）、最高价、最低价、收盘价（最后一天收盘价）、总成交量；计算股价的涨跌（当天收盘价-前一天收盘价）、涨跌的百分比（涨跌/前一天收盘价）。结果如图a-18所示。

图a-18 公式及其使用

任务二、绘制图表

任务目标

（1）绘制图表

（2）图表的格式化

知识清单

（1）柱形图

（2）折线图

（3）饼图

（4）图表标题

（5）数据显示

（6）数轴调整

（7）图形区格式化

任务要求

（1）以前五天的价格数据绘制柱形图和折线图，对图表进行格式设置，结果如图a-19所示。

图a-19 绘制图表

（2）以成交量数据绘制饼图，添加数据标签并设置格式，结果如图a-20所示。

图a-20 带数据标签的饼图

任务三、数据处理

任务目标

（1）排序

（2）分类汇总

（3）筛选

（4）数据透视表

知识清单

（1）排序（数字、文本）

（2）筛选

（3）分类汇总

（4）数据透视表

任务要求

（1）新建排序和筛选表，按成交量递减排序，得到如图a-21所示结果；筛选出成交量介于50000000和90000000之间的数据，得到如图a-22所示结果。

图a-21 数据排序结果

图a-22 数据筛选结果

（2）新建分类汇总表，按周汇总成交量，结果如图a-23所示。

图a-23 分类汇总结果

（3）在使用分类汇总时，只能用一种方式汇总数据，例如不能在求和的同时计算平均值，数据透视表以一种全新的视角审视数据，可以完成这类任务。新建数据透视表，同时求出周最高价、周最低价和周成交量，结果如图a-24所示。

图a-24 数据透视表结果

实践二、运用提高

资源下载：学习平台中“电子表格软件-实践二”，文件中存放的是某班50名学生的某次考试成绩，如图a-25所示。通过对数据表的处理学习如何在实际应用中熟练使用公式完成复杂任务。

图a-25 部分实验数据

任务目标

（1）正确使用公式和函数对批量数据进行处理

（2）熟悉常用的复杂数据处理技术

（3）正确使用图表

（4）学习打印大数据表

知识清单

（1）表格格式化

（2）拆分和冻结窗格

（3）函数的使用

（4）筛选

（5）分类汇总

（5）数据透视表

（5）绘图

（6）设置顶端标题、左端标题

任务要求

（1）计算主综合（语文、数学、英语）、小综合（科学、社会、思品）和总分，按总成绩降序排序，如总成绩相同，按主综合排序。结果如图a-26所示。设置打印属性，能正确输出数据表。

图a-26 公式计算结果

（2）筛选出数学和英语成绩都在在90分以上的同学。结果如图a-27所示。

图a-27 筛选结果

（3）统计数学成绩的分布情况（90-100、80-90、70-79、60-69、60分以下），并以此数据绘制柱形图和饼图。结果如图a-28所示。

图a-28 统计及绘图结果

（4）按性别统计数学成绩的分布情况，作图并分析统计数据的意义。结果如图a-29所示。

图a-29 分类统计及绘图结果

实践三、应用实践（应用实战）

任务目标

（1）考核电子表格中利用公式处理规模数据的技术

（2）考核电子表格中的数据处理技术

（3）考核使用图表技术

（4）考核利用电子表格解决实际应用问题的能力

任务要求

资源：学习平台中“电子表格软件-实践三”，文件中存放的是某中学初一六个班296名学生的某次考试成绩，按下面要求处理这些成绩：

（1）计算主综合（语文、数学、英语）、小综合（科学、社会、思品）和总分，按总成绩降序排序，如总成绩相同，按主综合排序，如再相同，按姓名的姓氏笔画升序排序。

（2）求各科目的总平均分、各班级的平均分。

（3）各科成绩大于等于70分（后两科为35分）为达标，求各科目总达标人数、达标率，及各班达标人数、达标率。

（4）各科成绩大于等于90分（后两科为45分）为优秀，求各科目总优秀人数、优秀率，及各班优秀人数、优秀率。

（5）统计数学各班成绩分布情况（90-100、80-90、70-79、60-69、60分以下），并绘图加以对比。

注意，所有计算都要求使用公式或函数完成，手工填写不得分。