用Excel处理数据

第4单元 数据统计与分析

第1节 数据收集与录入

一、电子表格处理软件Excel 简介

Excel 是办公自动化软件Office 中的一个组件。作为一个功能强大的电子表格处理软件，它不仅具有传统电子表格处理软件的一般功能，例如建立报表、统计数据、打印报表等，而且还具有较强的数据分析、图表演示、数据库管理等方面的功能。

动手做：手工计算下列奖牌榜。

如果用Excel 软件处理上列表格，则非常简单、快速。

二、初识Excel

1、启动Excel

单击“开始”按钮，打开“程序”菜单，单击其中的“Microsoft Excel”命令，启动Excel 程序，即可进入Excel 的用户界面。

打开“入门.xls ”文件，可以看出，Excel 的工作界面主要包括Excel 应用程序窗口和Excel 工作簿窗口两大部分。

2、应用程序窗口

标题栏：表明正在使用的软件，由于应用程序窗口经常与工作簿窗口合用一个标题栏，因此正在处理的工作簿的名称也显示在标题栏中。例如，“Book1”是启动Excel 后系统自动生成的空工作簿的名称。

菜单栏：包含了Excel 中的所有命令。

工具栏：把经常使用的菜单命令图形化，非常直观。最常用的是常用工具栏和格式工具栏。

编辑栏：这是Excel 所特有的一个栏目，用来显示活动单元格中的数据内容或具体公式。

状态栏：位于窗口底部，用来显示有关选定命令或操作进程的信息。

3、工作簿窗口

工作簿窗口主要由工作表区、单元格、行号、列号、工作表标签、滚动条等组成。

（1）工作簿:一个Excel 文件称为一个工作簿，它的文件扩展名为xls ，一个工作簿可以包含一张或多张工作表。

（2）工作表:默认情况下一个工作簿中有三张工作表，Sheet1、Sheet2、Sheet3分别是三个默认工作表的名字。每个工作表里都可以输入各自的数据。

工作表最左边一列是行号区，从上到下依次用数1到65536标出工作表的行号。最上面一行是列号区，从左到右用大写字母A ，B ，C „IU ，IV 等标出工作表的列号。如果把一个工作簿比做一本书的话，那么一个工作表就可看作书中的一页。

工作表的名称显示在工作簿窗口底部的工作表标签上。单击工作表标签可以从一个工作表切换到另一工作表。通常，Sheet1工作表是活动工作表，也叫当前工作表。

要更改工作表的名称，可以右击工作表标签，在弹出的快捷菜单中单击“重命名”命令。

（3）单元格: 列与行交叉处的矩形区域叫做单元格，是工作表存放数据的基本单元。每一张工作表由若干个单元格组成。

有粗黑线边框的单元格叫做活动单元格或当前单元格。用鼠标单击某个单元格，这个单元格就成为活动单元格，它的名称随即出现在编辑栏中，所对应的列号和行号均突出显示。

单元格内可以存放文字、数字和公式，一个单元格最多可存放3200个字符。

（4）地址:每个单元格都有一个编号，称之为地址。地址用“列号+行号”表示。例如，A1就表示第A 列第1行的单元格。

（5）数据区域:数据区域是指若干相邻单元格组成的矩形区域。

一个数据区域习惯上用左上角单元格地址和右下角的单元格地址来表示，中间用“:”分隔，如A1:D5就表示从左上角A1这个单元格到右下角D5这个单元格组成的一个数据区域。

4、退出Excel

单击菜单栏右侧的“关闭”按钮，将关闭打开的工作簿文件。单击标题栏右侧的“关闭”按钮，则关闭所有的工作簿文件，退出Excel 软件。

三、Excel 的基本操作

1、输入数据

选定某个单元格，双击之，即可输入数据，按回车键确认。也可在选定单元格后，向编辑栏中输入数据。

Excel 允许用户向单元格中输入文字、数字、时间、日期、公式等多种类型的数据。表格的标题、栏目名称、姓名都是字符型数据，Excel 自动将字符型的数据沿单元格左对齐。而输入数字型数据则沿单元格右对齐。

当某个单元格例如A1被填满后，剩余的文字会自动显示在B1、C1单元格中。表面上看，似乎B1、C1单元格中也有内容了，但实际上全是A1单元格的内容。

2、修改数据

双击单元格，即可进行数据的编辑，也可以选定该单元格后，在编辑栏中修改。

3、保存数据

执行菜单命令“文件/保存”，或单击常用工具栏上的“保存”按钮，在“保存”对话框中选择保存位置、输入文件名。

探究学习：

（3）如何插入、删除行或列？

（4）“删除”与“清除”操作的结果相同吗？

（5）如何把一个（或一些）单元格里的数据移动（或复制）到另一个区域？

（6）能否将Word 文档中的表格直接导入到Excel 中？

四、数据的收集

1、数据的来源。

一般而言，数据的来源主要有两个渠道，一是通过间接的方式获得，例如上网查找、从图书杂志上获得、找权威机构调查等，这些都是“第二手”资料，使用时要注意数据的真实性、可靠性。二是通过亲手实验、亲自调查等方式直接获得“第一手”资料。

2、使用调查问卷获取数据。

需要制定一个调查计划（进度）表，以保证调查活动有计划地开展。一般可归纳为以下几个步骤：

（1）确定调查目的、对象、范围、形式、填表说明、要求等。

（2）设计调查内容（即每个具体问题及选项），确定调查问题的次序。

（3）制作、复印调查表，开展调查活动。

（4）整理调查问卷，采集数据。

（5）数据统计，分析提炼，撰写调查报告。

第2节 表格规划与修饰

比较“超市1”和“超市2”这两个“超市商品库存”工作表，可以看出前者将所有商品存储在一个工作表中，后者将商品分类存储在不同的工作表中。很明显，后者比前者条理清楚。

一、规划工作表

规划工作表就是把数据“表格化”，其关键在于确定工作表的行和列分别代表的含义。一般把一列称之为一个“字段”，或者一个“属性”。每个字段都有一个字段名。一行称之为一条“记录”，一般表示一个对象的若干属性值。

每个字段的数据都有相同的数据类型，它们的取值范围往往是有限制的。例如单位名称是字符型，条形码是数字型，供货时间是日期型等。

规划工作表时，应该根据实际需要，确定工作表的个数和每个工作表的名称、功能，字段的个数和类型，甚至行数、列数等。工作表的规划一般要能完整、全面地表示数据和便于统计分析。

在指定工作表的标签上右击，然后在快捷菜单中执行相应的命令，即可完成工作表的增加、删除、复制、移动或重命名等操作。

二、修饰工作表

打开“奥运会金牌数”工作簿，比较工作表Sheet2和工作表Sheet1，你觉得哪一个更好，为什么？

修饰工作表时一般会用到“格式”工具栏或“格式”菜单，在“单元格格式”和“页面设置”等对话框中进行设置。结合“奥运会金牌数”工作簿，尝试下列操作，探究各种工具、菜单和选项的作用。

1、调整行高、列宽。

工作表的行高决定打印时的数据行间距。Excel 能根据输入内容的字体大小自动调整行的高度，使其能够容纳行中最大的字。实际操作时，还可以用鼠标操作或菜单命令等方法改变行高。

例如，要加大第一行的高度，可以移鼠标指针到行号1、2的分隔线处，当指针形状变为垂直箭头时，按住左键向上或向下拖动行分隔线，调整这一行到合适的高度。

列宽的调整与行高的调整类似。移鼠标指针到两个列号（如列A 、列B ）的中间分隔线处，当指针形状变为水平箭头时，按住左键向左或向右拖动列分隔线，调整这一列到合适的宽度。

在Excel 工作表中，默认的行高值是14.25， 默认的列宽值是8.38。除了用鼠标操作调整行高、列宽外，还可以使用菜单命令调整行高和列宽。

选定相应的行，依次打开“格式”“行”菜单，单击其中的“行高”命令，在弹出的“行高”对话框中输入具体的值，然后单击按钮。

选定相应的列，依次打开“格式”“列”菜单，单击其中的“列宽”命令，在弹出的“列宽”对话框中输入具体的值，然后单击按钮。

2、合并单元格。

选定某个单元格区域，例如A1：F1，单击工具栏中的“合并及居中”按钮，标题就会位于选定区域的中间位置。

3、显示网格线。

工作表中的网格线在实际输出时是不显示的。选定需加边框的单元格或单元格区域，如A1:F16，单击边框按钮中的下拉箭头，弹出列表框，单击其中的“所有框线”按钮。

以上操作都可以通过单击“格式”菜单中的“单元格”命令，在打开的“单元格格式”对话框中完成，更多的操作需要大胆探索。

4、设置单元格背景色。

选定需要设置背景色的单元格或区域，执行菜单命令“格式/单元格”，单击“单元格格式”对话框中的“图案”选项卡，即可设置单元格的底纹颜色和图案。

5、设置对齐方式。

在Excel 中，单元格中数据的对齐方式有水平对齐与垂直对齐两种。默认状态下，所有单元格的水平对齐都约定为“常规”，即根据输入到单元格中数据的类型确定水平水平对齐的方式，若输入的是文本，靠左对齐，若输入的是数字，靠右对齐。所有单元格默认的垂直对齐为靠下对齐。如果对这些约定不满意，可以根据需要改变单元格的对齐方式。

选定需要调整对齐方式的单元格或区域，执行菜单命令“格式/单元格”，单击“单元格格式”对话框中的“对齐”选项卡，再根据需要改变对齐方式，最后单击“确定”按钮。

6、使单元格内的字符自动换行。

当单元格中的数据较长，一行放不下时，执行菜单命令“格式/单元格”，单击“单元格格式”对话框中的“对齐”选项卡, 在“文本控制”中选购“自动换行”复选框，即可实现自动换行。

第3节 数据处理与统计

一、公式

如果在一个单元格中先输入一个等号“=”，那么Excel 会将等号后面输入的式子作为一个“代数式”，按回车键确认后，该单元格中显示该代数式的值。编辑一个单元格时，以等号开始的代数式称为“公式”。

在构建公式时应尽量使用单元格地址，不要直接引用单元格中的具体数据。这样，当单元格的数据发生变化时，与它相关的公式可以自动重新计算。 公式中一般包括常数、运算符号、引用地址和函数。

常数包括数值型（如“10”）、文本型（如“计算机”）、日期型（如“2007-01-21”）等。运算符号包括+（加）、－（减）、*（乘）、/（除）、^（幂运算）等。引用地址如A1、B2、A3:B5等。

在运用公式进行计算时，并不需要逐项进行。

例如在以上计算中，算出参加“羽毛球”兴趣小组的总人数后，只要选定F3单元格，移动鼠标指针到该单元格的右下角，指针由形变为形时，按住左键并拖动至F11单元格，就可以完成所有的计算。这就是用填充柄完成其他单元格公式计算的输入方法。

根据日常经验，不同单元格运算的公式有所不同，Excel 软件能够根据前面的运算规律，自动推断和调整后面单元格的内容。

用填充柄复制填充的是公式的运算规律，公式中引用的单元格地址自动调整为当前行的相应单元格，这就是使用单元格地址而不使用具体数值的好处。 另一种输入公式的方法是：单击编辑栏上的“=”按钮，出现“×”和“∨”两个按钮，同时，编辑栏中出现“=”和闪动的光标，在光标后输入代数式，然后敲回车键或者单击“∨”按钮，同样会出现计算的结果。

二、函数

Excel 中有一批预定义的内置公式，称作函数。

函数的引用一般是以函数名开始的，后面紧跟一对圆括号，括号中的参数用逗号隔开，参数可以是数值常量、存放数值的单元格或单元格区域。 三、相对引用和绝对引用

在公式和函数的使用中，要区分两个重要的概念：相对引用和绝对引用、相对地址和绝对地址。

当需要调用某单元格中的数据时，一般直接使用该单元格的地址，这种引用叫“相对引用”，这样的地址叫“相对地址”。用“填充柄”工具自动填充序列，则相对地址会自动按拖动的方向进行调整。

例如，在“兴趣小组报名表”中计算羽毛球小组占年级总人数G3的公式是“=F3/F12”，到了G4单元格时，公式就变成了“=F4/F13”。同样地，G5=F5/F14，G6=F6/F15，而F13、F14、F15这些单元格中都没有数据，Excel 默认为0，所以出现了分式中分母为0的情况，这就是除0错误“#DIV/0”。

使用“填充柄”工具自动填充序列时，若希望某个单元格地址保持不变，则在公式中引用到该地址时，必须在列号和行号前都加上“$”，例如$F$12，这种引用称为“绝对引用”，这样的地址称为" 绝对地址。

例如，在上面的问题中，只要把G3单元格中的公式改为“=F3/$F$12”，再用填充柄拖动即可。

有时在一个工作簿中有多张工作表，往往需要在多张工作表中相互调用数据，这种调用称为“表间引用”。引用的方法是在地址前加上工作表的名称。如在Sheet2的某个单元格中调用Sheet1的A1单元格，则公式为“= Sheet1！A1”。

操作练习：

（1）打开“体育运动.xls ”，用适当的方法完善它。

（2）根据姚明进入NBA 以后，代表休斯顿火箭队参加的5个赛季数据统计(截止2007年元月1日) “姚明.xls ”，用公式和函数完善工作表。

四、查找

当一个工作表中的数据很多时，可通过执行“编辑/查找”命令方便、快捷地找到符合一定条件的数据。

有时为了快速地对每一条记录进行浏览、检查和编辑，还可以使用“记录单”功能。

五、排序

所谓排序，就是按照工作表中某一个或几个字段的数据重新排列记录的顺序。排序所依据的字段称为“关键字段”。

将记录按关键字段值从小到大排序称为“升序”排列，反之称为“降序”排列。

以一个关键字段来排序的操作比较简单，只需单击关键字所在列的任意单

元格，然后单击工具栏中的“升序”按钮或“降序”按钮，就可以按递增或递

减的方式排序 。

如果关键字所在列的值出现相同情况，无法确定先后次序时，就需要参考其他项目，用多关键字排序。方法是：先单击数据表中的任一单元格，再执行菜单命令“数据/排序”来完成。

讨论学习：

（1）如果在“校园十佳歌手”大奖赛中，两位选手的最后得分相同，又应如何排序？

（2）英文字符和中文汉字按什么排序？

六、筛选

筛选就是从大量记录中选择符合要求（筛选条件）的若干条记录，并显示出来。执行菜单命令“数据/筛选/自动筛选”，在字段名的右侧出现一个下拉按钮。单击之，选择其中的类别、数值或自定义，就可以显示所需要的数据了。 筛选的目的是在数据清单中提炼出满足条件的数据，不满足条件的数据暂时被隐藏起来，一旦筛选条件取消，数据表又可以恢复原样。

七、分类汇总

分类汇总就是按照某个字段进行分类，再根据某些字段的取值进行统计，如分类求和、分类求平均值等。

第4节 数据图表与分析

一、数据描述方式的多样性

常用的数据描述方式有文字、表格和图表三种。很明显，后两种描述方式比第一种更直观、更生动。

打开工作簿“奥运会金牌数”，观察“Sheet1”工作表。

二、图表的制作

单击Excel 常用工具栏中的“图表向导”按钮，可以方便地制作图表。制作过程一般分为下列四个阶段：

设置图表位置。 探究学习：

（1）根据“奥运会金牌数.xls ”中的工作表数据，尝试绘制图表。

（2）观察：当工作表数据发生改变时，与之相关联的图表会自动改变吗？

（3）比较：图表向导的第1步中常见的图表类型，总结出不同图表类型适用的（4）尝试对图表的局部进行修改和美化。如文字的大小、背景颜色、图例位置讨论学习：

打开“动漫作品人物调查.xls ”，用合适的图表描述出其中的数据关系，并尝试总结出一些调查结论。

三、数据加工的多元性

数据是对客观事物的量化，由于客观世界的丰富性和多元化，数据所包含的信息也是多义的。因为人们的角色不同，需求有所差异，对数据的加工总是

带有一定的选择性。

打开“企业收益分配情况.xls ”工作簿，它反映的是某企业2004年-2006年三年的收益情况。该企业有股东5人，员工100人。

股东大会、企业工会、某员工分别从各自的角度根据此表的数据绘制了三个图表，并得出了各自的结论：股东大会认为劳资双方共同发展，有利同享。工会认为广大员工的工资增长幅度只有股东红利增长幅度的一半，应当适当增加员工的工资。员工认为股东和员工的收入差距十分悬殊，而且差距越来越大，应该大幅度增加员工的工资。

请对以上三幅图表加以比较，并发表你的观点。

我们应该养成用数据说话的习惯，用具体、充分、科学的数据表达自己的

观点。

四、常见错误信息

在处理工作表时，难免会出错，了解计算机显示的错误提示信息的含义和解决方法是非常必要的。

1、#####

错误原因：输入到单元格中的数值太长或公式产生的结果太长，造成单元格容纳不下。

解决方法：适当增加列的宽度。

2、#DIV/0！

错误原因：公式中除数为0。

解决方法：修改单元格的引用，或者将除数调整为非零的数。

3、#N/A

错误原因：函数或公式中没有可用的数值。

解决方法：如果工作表中某些单元格暂时没有数值，在这些单元格中输入# N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。

4、#NAME？

错误原因：公式中使用了Excel 不能识别的文本。

解决方法：确保使用的名称存在。如果没有列出所需的名称，添加相应的名称。如果名称存在拼写错误，要及时修改。

5、#NULL！

错误原因：指定了两个并不相交的“交叉”区域。

解决方法：如果要引用两个不相交的区域，请使用联合运算符（逗号）。

6、#NUM！

错误原因：公式或函数中某些数字有问题。

解决方法：检查数字是否超出限定区域，确保函数中使用的参数类型正确。

7、#REF！

错误原因：引用单元格无效。

解决方法：更改公式。

8、#VALUE！

错误原因：使用错误的参数、运算对象类型，或不能自动更改公式。 解决方法：确保公式或函数所需的运算符或参数正确，且公式引用的单元格包含有效的数值。