Excel数据统计与管理.docx

Excel数据统计与管理.docx

《Excel数据统计与管理.docx》由会员分享，可在线阅读，更多相关《Excel数据统计与管理.docx（30页珍藏版）》请在冰豆网上搜索。

Excel数据统计与管理

实训八Excel数据统计与管理

实训目的

一、学会建立数据清单，能用记录单添加、修改与删除记录；

二、能掌握记录的排序、分类汇总，

三、掌握数据透视表的使用。

教材内容

1．建立数据清单

（1）数据清单

工作表中的数据具有的特殊组织的形式叫数据清单。

数据清单中的列为一个字段。

数据清单中的第一行为字段名，此行中的数据应为文本格式。

以下的每一行叫一条记录。

同列的数据其类型和格式必须相同。

数据清单中没有合并的单元格。

在同一工作表中只建立一个数据清单。

每个单元的数据间不要插入多余的空格，也不要多余的空行或空列。

例如：

学生成绩表就是一个最为简单的，也最为典型的数据清单，其中包含了学生的学号、姓名、班级、各科成绩以及各科成绩的统计，同时还可以包括学生的单科成绩和总成绩等内容。

如下图所示。

在Excel中利用“数据”菜单，可以进行对数据排序、筛选、分类汇总，建立数据透视表等操作。

（2）使用数据清单

在工作表中选定某单元格后，单击“数据/记录单”命令，在弹出的对话框中可以进行添加记录、删除记录、修改记录、还能查找记录。

这些操作很简单，一看就会，我们这里就不多讲了。

1）使用“记录单”在“学生成绩表”中查找数据记录

例如我们要查找计算机成绩大于85分的学生记录。

在工作表中任意选定一个单元格，单击“数据/记录单”命令，在弹出的对话框中单击“条件”钮，在“计算机”框中输入“>=85”，再单击“下一条”钮，此时在记录单会显示满足条件的第一条记录，不断单击“下一条”钮，可显示所有满足条件的记录。

如下图所示。

2）使用“记录单”在“学生成绩表”中修改数据记录

工作表中的记录可以直接在单元格中进行修改。

但如果记录太多，就容易出错，我们使用数据清单来修改就不会出错。

例如要将“李国华”的英语成绩改为84分，可在“记录单”对话框中，用“条件”钮查找姓名是“李国华”的记录，然后在“英语”框中修改分数。

4）使用“记录单”在“学生成绩表”中删除数据记录

在“记录单”对话框中，用“条件”钮定位记录，然后单击“删除”钮，则可删除该记录。

2．数据排序

排序是依据某一个或几个字段值，按一定的顺序将工作表的记录重新排列。

1．单字段排序

在Excel中将排序的依据字段叫“关键字”。

要给数据排序，首先选择关键字段的列标，然后单击工具栏上的“升序”或“降序”按钮。

也可以使用“数据/排序”命令，在“排序”对话框的选择“主要关键字”并确定“升（降）序”。

2．多字段排序

如果“主要关键字”字段里有相同数据时，可再按“次要关键字”来排序，依此类推。

Excel最多只能按3个关键字进行排序。

图5.70使用两个关键字排列结果

说明：

1．选中“有标题行”单选按钮。

在排序的时候，Excel不会把这一行也当成要参加排序的数据了。

2.选择数据区域。

这一步很重要，如果我们选择的数据区域范围不正确，就会直接导致后面的排序结果错误，甚至将表格的数据弄得一团糟。

因为无论我们将要怎样排序，每一条记录的内容是不容改变的，改变的只是它在数据库中显示的位置。

如果只对某一列强行进行排序的话，结果会是只有这一列的数据顺序被改变了，而其它列的数据顺序没有被改变，那么表格就全乱了！

3．排序时，对数字，是按大小进行排序；对字母，是是按A到Z的顺序进行排序；对汉字，是汉语拼音字母顺序进行排序（可通过“选项”钮，设置为按笔划进行排序）。

3．数据筛选

利用筛选操作可在一大堆数据中找出某一类记录或符合我们所指定的条件的记录。

筛选的结果就是把不满足条件的记录隐藏起来，只在屏幕上显示满足条件的记录。

1）自动筛选

选定表格或任意一个单元格。

单击“数据/筛选”命令，选“自动筛选”。

这时每一列标题会出现一个下拉钮。

按下箭头，会出现一个下拉列表框，有许多可选项：

全部”就是不筛选；

“前10个”可筛选出前n个（n>0）最大或最小的记录；

“自定义”是设置这一列的筛选条件；

下面的是可以指定这一列里某一数值。

例：

使用“自动筛选”中的“自定义”在“学生成绩表”中显示“总分”为440分以上的记录

同一张表格，我们可以同时按多个条件进行筛选。

比如说，我们要看看“总分”大于440分，同时“计算机”是90分以上的记录

还可使用多条件的“与”或“或”选项。

如在“学生成绩表”中显示“总分”为430分到450分之间的记录，即大于430分“与”小于450分的记录。

如下图所示。

如在“学生成绩表”中显示“总分”≥450分或“总分”＜350分的记录。

如下图所示。

自动筛选中还可使用通配符“？

”或“﹡”。

如在“学生成绩表”中显示姓张的学生记录：

单击“姓名”的下拉列表框中选“自定义”，在“自定义自动筛选方式”框中输入条件：

“等于，张*”。

如下图所示。

说明：

（1）“自动筛选”的“自定义”选项提供了对同一字段进行筛选的两个条件，并且这两个条件可以是“与”或“或”的关系，同时，该对话框还提供“等于”、“大于”、“起始于”等许多条件选项，这些选项可以在上面的例子中看到。

（2）在筛选时可以使用通配符。

在设置自动筛选的自定义条件时，可以使用通配符，其中问（？

）代表任意单个字符，星号（﹡）代表任意一组字符。

（3）取消自动筛选也很简单，我们只要按照前面说的选择“自动筛选”的方法，再做一次就行了，这时应把菜单里“自动筛选”左边的那个小勾去掉。

2）高级筛选

如果数据清单中的字段比较多，筛选的条件也比较多，则可以使用“高级筛选”功能来筛选数据。

高级筛选可以设置行与行之间的“或”关系条件，也可以对一个特定的列指定三个以上的条件，还可以指定计算条件，这些都是它比自动筛选优越的地方。

要使用“高级筛选”功能，必须先建立一个条件区域，用来指定筛选的数据需要满足的条件。

而且条件区域和数据清单不能连接，必须用一个空行将其隔开。

条件区域的第一行是作为筛选条件的字段名，这个字段名必须与数据清单中的字段名完全相同，条件区域的其他行则用来放置筛选条件。

需要注意的是：

在条件区域的筛选条件的设置中，同一行上的条件认为是“与”条件，而不同行上的条件认为是“或”条件。

例：

使用“高级筛选”在“学生成绩表”中显示“软一1班”的计算机成绩在80分以上的记录

在数据清单所在的工作表中选定一个区域作条件区域，并输入筛选条件：

如在B45单元格中输入“班级”，在B46单元格中输入“软一1班”，在C45单元格中输入“计算机”，在C46单元格中输入“＞=80”，见下图。

然后，选定数据清单中的任意一个单元格，单击“数据/筛选”命令中的“高级筛选”选项。

将弹出“高级筛选”对话框。

我们选择“在原有区域显示筛选结果”；在“列表区域”选定要筛选的数据区域（取整个数据清单表）：

“A2：

J42”；在“条件区域”选定含有筛选条件的区域（即上面设定的条件区域）：

B45：

C46。

进行相应的设置并单击“确定”按钮，结果如图所示。

说明：

（1）“高级筛选”对话框中各选项的含义如下：

●在原有区域显示筛选结果：

筛选结果显示在原数据清单位置。

●将筛选结果复制到其他位置：

筛选后的结果将显示在“复制到”文本框中指定的区域，与原工作表并存。

●列表区域：

指定要筛选的数据区域，可以直接在该文本框中输入区域引用，也可以用鼠标在工作表中选定数据区域。

●条件区域：

指定含有筛选条件的区域，如果要筛选不重复的记录，则选中“选择不重复的记录”复选框。

（2）条件设置方法几个范例：

1）表示“不等”的情况：

英语

总分

>80

>350

平均分

平均分

>＝80

<90

英语>80，或总分>350平均分在80～90之间

姓名

刘*

2）表示“包含”的情况

书名

*文学*

书名中含有“文学”二字的姓“刘”的（以“刘”开头的）

3）表示复合条件的情况

年龄在（30～40）岁的男性工程师或助工

4．分类汇总

分类汇总就是将相同类别的数据，即数据清单中某一字段的数据放在一起，再进行数量求和、计数、求平均值之类的汇总运算。

它还可逐级进行汇总计算，并将结果自动分级显示出来。

我们使用分类汇总不仅可以建立清晰、明了的总结报告，还可以在报告中只显示第一层次的信息而隐藏其他层次的信息。

分类汇总的前提是必须按进行分类汇总的字段，对表格进行排序（升序或者是降序都无所谓），让同类别的记录连续排列。

分类汇总的三个基本要素：

●分类字段：

选定要进行分类汇总的列，并按此列对表格进行排序（最易忽略的一步）。

●汇总方式：

利用求和、计数、求平均值等汇总函数，进行对分类字段的计算。

●汇总项：

可选择进行汇总的多个字段。

分类汇总具体操作步骤如下：

对将要进行分类汇总的字段排序，然后单击“数据/分类汇总”命令；

在“分类汇总”对话框中的“分类字段”下拉列表框中选择进行分类汇总的字段；在“汇总方式”列表框中选择汇总方式；

在“选定汇总项”列表框中选择要汇总的字段。

最后按“确定”按钮。

下面以“学生成绩表”为例，来介绍对数据进行分类汇总的方法：

1．使用“分类汇总”在“学生成绩表”中显示各班级各科的总分

图5.77“分类汇总”对话框

1）按班级进行排序，以便进行分类汇总。

2）单击“数据/分类汇总”命令，在弹出“分类汇总”对话框中的“分类字段”下拉列表框中选择“班级”选项，在“汇总方式”列表框中选择“求和”选项，在“选定汇总项”列表框

图5.78

中选中“大语”、“高数”、“英语”、“邓论”、“计算机”等复选框，如右图所示。

然后单击“确定”按钮。

分类汇总后的表格具备了分级显示的功能。

汇总完后在表格的左侧出现了一些类似于Windows资源管理器的目录树相似的按钮，而最上方有标着1、2、3的按钮，它们就是用来控制分级显示的。

3是最明细的数据，2是上一级的汇总数据，1就是最上级的汇总数据了。

目前，我们正处于第3级的显示状态，所以能看到所有的明细数据与汇总数据。

这个时候，如果我们单击一下“2”这个按钮，就会从第3级进入到第2级汇总的状态下进行显示。

结果如下图所示。

“+”按钮表示这个级别的数据还有下级明细，我们只要单击一下它，就能看到相应的下层数据；“-”按钮恰恰相反，如果单击一下它，就会隐藏相应的明细数据，回到上一级的汇总里去。

如果不想再要这些分类汇总的数据，只要单击分类汇总的数据区域里任意一个单元格，然后再选择“数据”菜单中的“分类汇总”命令，在“分类汇总”对话框中单击“全部删除”按钮，就可将数据清单恢复为原来的样式。

5．数据透视表（教材没讲）

数据透视表可以将排序、筛选和分类汇总这三个过程结合在一起，并且还可将表与图链接起来，它的使用给我们带来极大方便。

数据透视表对数据进行快速汇总和建立交叉列表，它不仅可以转换行和列以显示源数据的不同汇总结果，也可以显示不同页来筛选数据，还可显示用户关心区域中的明细数据，它可生动、全面地对表格中的数据重新组织和统计整理。

创建数据透视表可以利用“数据透视表和图表报告向导”完成：

先任选表格中任一单元格为活动单元格，然后执行“数据/数据透视表和数据透视图”命令。

例：

对下图的“职工表”建立“数据透视表”来显示各部门各年龄段的平均工资。

图5.81数据透视表向导一3步骤之1

（1）首先选定“职工表”中任一单元格，然后单击“数据/数据透视表和数据透视图”命令，将弹出“数据透视表和数据透视图向导一3步骤之1”对话框，在“请指定待分析数据的数据源类型”选项区中选中“MicrosoftExcel数据列表或数据库”单选按钮（该项为默认设置）。

在“所需创建的报表类型”选项区中，用户可以根据需要选中“数据透视表”单选按钮或“数据透视图”单选按钮（这时我们选“数据透视表”单选按钮）。

（2）下一步，在“数据透视表和数据透视图向导一3步骤之2”的对话框中按需要输入或选取要建立数据透视表的数据源区域，如A2：

I12。

注意：

选定数据区域时必须带有表头信息。

（3）单击“下一步”按钮，弹出“数据透视表和数据透视图向导一3步骤之3”对话框，如图5.94所示，在对话框中单击“布局”按钮，在弹出的对话框中对数据透视表的版式进行设置。

拖动“所属部门”字段到“请将行字段拖至此处”的行区域，拖动“年龄”字段到列区域，拖动“基本工资”字段到“请将数据项拖至此处”的数据区域，双击数据区域内的数据项，选择“平均值”，然后单击“确定”钮，如下图所示。

此时，在“职工登记表”工作簿中的新工作表里面建立了一个数据透视表，并且还随之产生一个“数据透视表”工具栏，透视表呈现如下图所示的形式，并且统计出了按部门各年龄段的平均工资。

说明：

1．“数据透视表”是用于快速汇总大量数据的交互式数据分析报表，也称为三维表。

通常是汇总较多的数据，且对这些数据进行多种比较时使用。

在使用中，可以根据不同的汇总要求，改变不同的布局，可以修改源数据的不同汇总。

还可以通过显示不同的页来筛选数据，或查看所关心的明细数据。

2.“数据透视表”主要由行、列、数据和页四个区域组成。

可以根据需要将代表数据列表中数据的各按钮拖到上述区域进行设置。

各区域的含义如下：

“页”区：

用来放置要建立的数据透视表的按页显示字段，该字段用来筛选显示数据；

“行”区：

用来放置要建立的数据透视表的按行显示字段，该字段用来决定按什么排列数据行；

“列”区：

用来放置要建立的数据透视表的按列显示字段，该字段用来决定按什么排列数据列。

“数据”字段区：

用来放置数据透视表中的各个数据。

3．修改数据透视表的布局：

（1）行、列字段互换：

将行字段拖到列字段区中，将列字段拖到行字段区中；

（2）在字段内移动数据项：

用鼠标拖动要移动的数据项；

（3）隐藏或显示数据：

单击字段名右侧的下拉钮。

在字段列表中选择要隐藏或显示的字段，去掉它前面小方框中的“√”，则隐藏此字段数据；

（4）删除字段：

将有此字段名的单元格拖到数据透视表的数据区域外即可；

（5）添加字段：

单击“数据透视表”工具栏右侧的“显示字段列表”钮，将要添加的字段名拖到“数据透视表”的适当位置。

4．修改汇总方式：

我们只要双击数据区域内的数据项，在弹出的“数据透视表字段”对话框中，选择不同的汇总方式，就可改变各种汇总方式。

如上图所示。

5．利用“数据透视表”，通过显示不同的页可以方便地进行数据筛选。

例：

为“职工表”建立“数据透视表”来显示各部门各年龄段男、女的平均工资

操作与上面完全一样，只是在“布局”时将“性别”字段拖到“页”区域。

结果如下图。

我们可以单击“数据透视表”中页区域“年龄”右边的下拉钮，选择不同的年龄段如“22”、“24”和“30”等，此时“数据透视表”显示的就是“22”、“24”和“30”年龄段的职工平均工资值。

可见，页字段项的“数据透视表”相当一叠卡片，每张“数据透视表”相当其中的一张卡片，选择不同的页字段，就选择了不同的卡片。

见下图。

6．数据透视表的格式化：

方法是选定一个要格式化的单元格后单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项，然后在打开的对话框中进行选择，其操作与表格的格式化操作基本一样。

实训内容

一、在D盘中建立自己姓名的文件夹，将“配套材料”中的Excel文档“学生成绩表”复制到姓名子目录中，再进行下述操作：

（以下各小题都将工作表“Sheet2”中数据复制到新的工作表，做完后用相应工作表名保存文件）

（1）对所有学生按照总分进行降序排列，在总分相等的情况下按英语升序排列。

（结果见工作表“排序”。

如下图）

（2）用自动筛选操作将平均分大于等于90分的同学筛选出来。

（结果见工作表“筛选”，如下图）

（3）显示平均分在80分以上（含80分），90分以下的同学记录。

（结果见工作表“筛选2”，如下图）

（4）显示大语、高数、英语均大于85分的同学信息。

（结果见工作表“筛选3”，如下图）

（5）使用“高级筛选”在“Sheet2”中显示“软一1班”的计算机成绩在85分以上和“软一2班”的计算机成绩小于75分的记录。

（结果见工作表“高级筛选”，如下图）

（6）按照“班级”字段为依据，将表中所有的分数数据以平均值的方式汇总。

（结果见工作表“分类汇总”，如下图）

（7）用数据透视表显示各班级的各科平均分数。

（结果见工作表“数据透视表”，如下图）

实训过程

一、在D盘中建立自己姓名的文件夹，将“配套材料”中的Excel文档“学生成绩表”复制到姓名子目录中，再进行下述操作：

（以下各小题都将工作表“Sheet2”中数据复制到新的工作表，做完后用相应工作表名保存文件）

（1）对所有学生按照总分进行降序排列，在总分相等的情况下按英语升序排列。

（结果见工作表“排序”。

如下图）

操作：

打开“Sheet2”，选定数据区域中任一单元格，单击“数据”菜单下的“排序”命令，在“排序”对话框的“主要关键字”框中选择“总分”，并选中其右侧的“降序”钮，在“次要关键字”框中选择“英语”，并选中其右侧的“升序”钮。

再选中“有标题行”钮。

单击“确定”按钮。

（2）用自动筛选操作将平均分大于等于90分的同学筛选出来。

（结果见工作表“筛选”，如下图）

操作：

选定“Sheet2”中任一单元格后，单击“数据”菜单下的“筛选”命令，在其子菜单中选择“自动筛选”。

单击“平均分”右侧的下拉箭头，在下拉列表框中选“自定义”，在“自定义自动筛选方式”框中分别输入条件：

“大于或等于，90”（如下图所示）。

（3）显示平均分在80分以上（含80分），90分以下的同学记录。

（结果见工作表“筛选2”，如下图）

操作：

方法同上。

只是在“平均分”的“自定义自动筛选方式”框中输入条件：

“大于或等于，80”，再选“与”，并在下面的条件框中输入“小于，90”。

（如下图所示）

（4）显示大语、高数、英语均大于85分的同学信息。

（结果见工作表“筛选3”，如下图）

操作：

方法同上。

只是分别在“大语”、“高数”、“英语”的“自定义自动筛选方式”框中输入条件：

“大于，85”。

（5）使用“高级筛选”在“Sheet2”中显示“软一1班”的计算机成绩在85分以上和“软一2班”的计算机成绩小于75分的记录。

操作：

在数据清单所在的工作表中选定一个区域作条件区域，并输入筛选条件：

如在B45单元格中输入“班级”（必须与表头字段名“斑级”完全一样，下面“计算机”也是这样的），在C45单元格中输入“计算机”，在B46单元格中输入“软一1班”，在C46单元格中输入“＞=85”；在B47单元格中输入“软一2班”，在C47单元格中输入“<75”。

然后，选定数据清单中的任意一个单元格，单击“数据”→“高级筛选”选项。

在弹出的“高级筛选”对话框中选择“在原有区域显示筛选结果”；在“列表区域”选定要筛选的数据区域（取整个数据清单表）：

A2：

K42；在“条件区域”选定含有筛选条件的区域（即上面设定的条件区域）：

B45：

C47。

按“确定”按钮。

（结果见工作表“高级筛选”，如下图）

（6）按照“班级”字段为依据，将表中所有的分数数据以平均值的方式汇总。

操作：

首先选定“Sheet2”，按班级进行排序后，单击“数据”→“分类汇总”命令，在弹出“分类汇总”对话框中的“分类字段”下拉列表框中选择“班级”选项，在“汇总方式”列表框中选择“平均值”选项，在“选定汇总项”列表框中选中“大语”、“高数”、“英语”、“邓论”、“计算机”等复选框，然后单击“确定”按钮。

如右图所示。

（结果见工作表“分类汇总”，如下图）

（7）用数据透视表显示各班级的各科平均分数。

操作：

首先选定“Sheet2”→单击“数据/数据透视表和数据透视图”命令→在弹出的对话框中，选“数据透视表”，→选取要建立数据透视表的数据源区域，如A2：

H42，（注意：

选定数据区域时必须带有表头信息）→在“布局”对话框中拖“班级”字段到“行区域”，拖“大语”、“高数”、“英语”、“邓论”、“计算机”等字段到“数据项”区域→分别双击数据区域内的各数据项，并选择“平均值”→最后单击“确定”钮。

（结果见工作表“数据透视表”，如下图）

可以发现此数据透视表与上面的分类汇总表的内容是完全相同的，但数据透视表不须排序，且功能要强大得多。

若使用列字段，则显示的内容更详细、具体。

自我测试（时间20分钟）

1.在D盘中建立一个“学号－姓名”的文件夹（如“06-王大发”），并建立一新的Excel文档；[下面各题所用材料，均在“配套材料”中]

2.为了更好地进行自己小店的经营管理，小王用Excel制作了一个“销售明细表”（见“Excel实验（配套材料）”）），专门记录销售商品的情况。

现在小王要统计一下三天来的销售数量并计算毛利润。

你能帮助小王解决以下几个问题吗？

1．计算每天每件商品的“销售额”、“毛收入”和“毛利润率”。

（销售额=数量×售价、毛收入=（售价－进价）×数量、毛利润率=毛收入÷销售额）

2．查询每天的销售总额和毛利总额？

（1）用求和函数SUM；（已做，可不做了）

（2）用条件求和函数SUMIF；（已做，可不做了）

（3）用自动筛选；

（4）用分类汇总；

（5）用数据透视表。

3．查询每天各种商品的销售数量或销售总额？

（1）用求和函数SUM；（已做，可不做了）

（2）用自动筛选；

（3）用分类汇总；

（4）用数据透视表。

4．（高手进阶，较差的同学可不做）在输入饮料名称后，让系统自动从“商品基本信息表”中提取“单位”、“进价”和“售价”等信息，并自动放入到“销售明细表”的相应单元格中；

5．（高手进阶）建立图表，显示三天来毛收入前十名的商品名、销售额和毛收入。

按以下实验步骤完成本次实验。

自我测试操作解答

［操作结果如“销售明细表（结果）.xls”所示］

1．计算每天每件商品的“销售额”、“毛收入”和“毛利润率”

（销售额=数量×售价、毛收入=（售价－进价）×数量、毛利润率=毛收入÷销售额）

操作：

（1）计算销售额：

（已做，可不做了）

在放销售额的单元格（如G3）中输入公式：

=E3*F3

再拖填充柄至最末尾。

（2）计算毛收入：

在放毛收入的单元格（如H3）中输入公式：

=（E3-D3）*F3

再拖填充柄至最末尾。

（3）算毛利润率：

在放毛利润的单元格（如I3）中输入公式：

=H3/G3

再拖填充柄至最末尾。

2．查询每天的销售总额和毛利总额？

操作：

找一单元格存放销售总额。

为便于观察，最好是另用一工作表，取名为“日销售额”，如下图所示：

（1）用求和函数总数：

（已做，可不做了）

1）求日销售总额：

计算6月1日的销售总额，在单元格（如B3）中输入公式：

=SUM（销售情况!

G3:

G112）

计算6月2日的销售总额，在单元格（如C3）中输入公式：

=SUM（销售情况!

G113:

G229），

……

2）求毛利总额：

计算6月1日的毛利总额，在单元格（如B4）中输入公式：

=SUM（销售情况!

H3:

H112）

……

计算6月2日的毛利总额，在单元格（如C4）中输入公式：

=SUM（销售情况!

H113:

H229）

……

结论：

每个计算公式中要输入不同的计算区域，这样不但不能复制公式，而且容易弄错。

（2）用条件求和函数SUMIF：

（已做，可不做了）

SUMIF：

条件求和；SUMIF（条件判断区域，求和条件，实际求和区域）。

在条件判断区域内有