excel数据分析应用指南培训讲座课件.docx

excel数据分析应用指南培训讲座课件.docx

《excel数据分析应用指南培训讲座课件.docx》由会员分享，可在线阅读，更多相关《excel数据分析应用指南培训讲座课件.docx（15页珍藏版）》请在冰豆网上搜索。

excel数据分析应用指南培训讲座课件

ExcelXP数据分析应用指南

Excel之所以强大，是因为其具有完整的数据分析函数和图表功能，让我们对数据进行归纳后，能够利用这些函数和图表对数据进行分析。

本文以教育、金融和财务应用为例，介绍ExcelXP数据分析的主要手段和操作方法。

一、数据的排序与筛选

排序与筛选是数据分析的基本手段。

下面以图1所示的“学籍管理表”为例，介绍数据排序与筛选的操作方法。

1.数据排序

（1）数据清单：

Excel排序和筛选的操作对象是“数据清单”。

它是在工作表首行的单元格内输入列标记（相当于数据库中的字段，可以作为关键字参与各种操作），在列标记以下各行依次输入数据（相当于数据库是的一条条记录）而构成的表。

其基本结构如图1所示，与Access数据表基本相同。

（2）常规排序：

Excel数据清单中的列标记可以作为关键字参与排序，具体操作方法有以下两种：

如果你需要根据“年龄”、“总分”等数值关键字（字段）对数据清单排序，只须将图1中的“年龄”或“总分”选中，然后点击工具栏中的“升序排序”或“降序排序”按钮，整个数据清单就会根据该关键字重新排列。

如果你需要按姓氏笔划对图1数据清单排序，则应选中图1中B1单元格，点击“数据→排序”菜单命令，在打开的对话框中点击“选项”按钮打开“排序选项”对话框。

选中“笔画排序”选项，根据图1中的数据排列方向选择“按列排序”选项，点击“确定”按钮后返回到“排序”对话框。

将其中的“有标题行”选中，然后打开“主要关键字”下拉列表，选择其中的“姓名”选面，最后选中排序方式（“升序”或“降序”）并确定，数据清单就会根据姓名的笔划重新排列。

如果数据清单排序依据多个关键字，你只要打开“排序”对话框，在“次要关键字”和“第三关键字”下拉列表中选择即可。

（3）自定义排序：

如果你要求图1中的数据按照“广东省”、“云南省”、“四川省”、“山西省”和“河北省”的顺序排列，前面介绍的两种方法就无能为力了，只能采取自定义排序规则的方法处理。

首先你要建立一个自定义序列：

点击“工具→选项”菜单命令，打开“选项”对话框中的“自定义序列”选项卡。

选中左边“自定义序列”下的“新序列”选项，此时光标就会在右边的“输入序列”框内闪动，你可以输入“广东省”、“云南省”等自定义序列，输入的每个序列之间要用英文逗号分隔，或者每输入一个序列就敲一次回车。

完成后点击“添加”按钮，将自定义序列添加到“自定义序列”框内备用。

自定义序列排序的方法与笔划排序很相似，你只要打开“排序选项”对话框中的“自定义排序次序”下拉列表，选中前面定义好的序列，其他选项保持不变。

回到“排序”对话框后根据需要选择“升序”或“降序”，“确定”后即可完成数据清单的排序。

注意：

假如数据按自定义序列的顺序排列，就应选中“排序”对话框中的“升序”排列，否则应该选择“降序”排列。

2.数据检索

从数据结构的角度看，图1所示“数据清单”就是一个小型数据库，其中的每一条记录占用一个表格行。

对此，可以采用下面的方法检索数据：

点击“数据→记录单”菜单命令，在打开的对话框中点击“条件”按钮。

对话框中的字段就会变成空白等待输入，而且“条件”按钮转变为“表单”按钮。

你可以在对话框的相应字段中输入条件，例如在“姓名”字段框内输入“李小丫”回车，则姓名是“李小丫”的数据就会显示在对话框中。

当然，你输入的检索条件可以使用>、<、>=、<=、<>等逻辑符号。

例如点击“条件”按钮后在“总分”框内输入“>500”，表示检索“总分”大于500分的所有记录，回车后“新建”按钮上方就会显示“1/6”字样，说明6条记录中的第一条符合条件。

点击“上一条”或“下一条”按钮，可以查看检索出来的其他记录，并显示“4/6”、“5/6”字样。

3.自动筛选

上面介绍的方法可以逐条查看检索出来的记录。

如果你要批量查看符合条件的所有记录，仅靠“记录单”进行检索就不能满足你的需要。

此时可以使用Excel的自动筛选功能，具体操作方法是：

点击“数据→筛选→自动筛选”菜单命令，数据清单的列标记（字段）右侧会显示一个下拉按钮。

如果你需要将总分大于500分的记录全部筛选出来，可以点击“总分”下拉按钮，选择“自定义”选项打开“自定义自动筛选方式”对话框。

点击“总分”下面第一行右侧的下拉按钮，选择“大于”选项，然后在其右边的框内输入“500”，选中两行中间的“与”后确定，工作表就会显示所有符合筛选条件的记录。

当然，筛选出来的记录还可以再次筛选。

假如你要将总分大于500分中“性别”为“女”的记录筛选出来。

可以按相同方法打开“性别”下拉列表选择“女”，则“张晓菲”的记录就会显示在工作表中。

自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。

如果你要清除筛选结果，点击“数据→筛选→自动筛选”菜单命令即可。

另外，执行数据自动筛选时，如果“自定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应当选中“或”选项。

4.高级筛选

虽然“自动筛选”操作简单，但是可供使用的筛选条件有限。

为此，Excel提供了“高级筛选”功能。

它能够使用各种条件对数据清单进行筛选，其功能强大和使用灵活远非“自动筛选”可比。

（1）条件区域：

使用高级筛选必须在工作表中构造区域，它由条件标记和条件值构成。

条件标记和数据清单的列标记相同，可以从数据清单中直接复制过来；条件值则须根据筛选需要在条件标记下方构造，是执行高级筛选的关键部分。

构造高级筛选的条件区域需要注意：

如果条件区域放在数据清单的下方，那么两者之间应至少有一个空白行。

如果条件区域放在数据清单的上方，则数据清单和条件区域之间也应剩余一个或几个空白行（一般不要这样设计，这样影响其他功能使用数据清单）。

（2）单列多条件：

如果某一个条件标记下面输入了两个或多个筛选条件，我们将其称为单列多条件，你只要在条件标记下自上而下依次输入筛选条件即可。

例如你需要列出图1中年龄大于17和年龄小于17的学生名单，只需在图1条件标记“年龄”的下方D10和D11单元格输入“>17”和“<17”即可。

（3）多列单条件：

多列单条件是指筛选条件由多个条件标记构成，但每个条件标记下面只有一个条件。

如果你要列出图1中“性别”为“女”，且“语文”成绩大于等于85的学生，可以在图1条件区域的C10单元格内输入“女”，在G10单元格内输入“>=85”即可。

（4）多行单条件：

构造高级筛选条件有这样的要求：

如果多个筛选条件需要同时满足，它们必须分布于条件区域的同一行，这就是所谓的“与”条件，否则筛选条件必须分布于条件区域的不同行，也就是“或”条件。

假如你要在图1中找出“性别”是“男”，或“语文”成绩“>=80”，或“化学”成绩“>=90”的所有记录。

可以在图1条件区域的C10单元格内输入“男”，然后在G11单元格内输入“>=80”，最后在J12单元格内输入“>=90”即可。

（5）两列两组条件：

如果你需要在图1中寻找物理成绩大于等于90的男生或者物理成绩大于等于80的女生，可以按照如下方法构造条件区域。

C10、C11单元格内分别输入“男”和“女”，接着在H10、H11单元格内分别输入“>=90”和“>=80”即可。

（6）执行筛选操作：

数据清单和条件区域建立完毕，你就可以执行数据筛选任务了，具体操作方法是：

点击“数据→筛选→高级筛选”菜单命令，在打开的对话框中首先选择筛选结果的显示方式，若选择“在原有区域显示筛选结果”，则像自动筛选那样在数据清单中显示结果；否则就要在“复制到”框内指定筛选结果的显示位置。

接着点击“数据区域”框，选中数据清单所在的区域，使该区域的绝对引用进入框内；再用相同方法完成对“条件区域”的绝对引用。

点击“确定”按钮，就会在你选择的位置显示筛选结果。

如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数据清单。

此时点击“数据→筛选→全部显示”菜单命令，即可恢复原来的数据清单。

二、数据的分析与求解

Excel提供了多种数据分析手段，从函数、分析工具库、加载宏等，一直到数据透视表和数据透视图。

下面介绍以函数和加载宏为主的若干分析工具。

1.成绩分布频率分析

学生成绩分析的一项重要任务，就是统计各分数段中的人数，为研究成绩分布提供基础数据。

下面以图1为例，说明如何计算550～500、500～450、450～400、400～350，以及小于350分数段内的人数。

首先在图1中的M2：

M6区域依次输入550、500、450、400、350，表示统计上述分数段内的成绩个数。

当然，你可以根据数据分析的具体要求，选择其他方式划分数据分段方法。

接着在M2:

M6区域右侧预留相同大小的单元格区域（N2：

N6），作为存放数据统计结果的位置。

上述工作完成后，选中存放统计结果的区域（N2：

N6），在编辑栏内输入公式“=FREQUENCY（L2:

L7，M2:

M6），最后让光标停留在公式的末尾。

按下Shift+Ctrl键敲回车，编辑栏内将显示“{=FREQUENCY（L2:

L6，M2:

M6）}”（大括号表示这是一个数组公式），N2：

N6区域就会显示各分数段中的成绩个数。

注意：

数组公式“{=FREQUENCY（B2:

B6，C2:

C6）}”可以对一组或多组数值进行多重计算，并得出一个或多个计算结果。

数组公式中的大括号“{}”不能用手工输入，只能按组合键Shift+Ctrl+Enter自动生成。

2.对象相关性分析

教育教学研究的任务之一是了解各学科之间的相互关系，例如数学的学习是否对物理成绩有影响。

而商品销售领域，也需要分析两组数据（单位可以不同）之间是否相关。

例如空调和冷饮的销售量与气温之间的关系，或者商场的客流人数与销售额是否相关等等。

这里仍然以图1所示的“学籍管理表”为例，我们的任务是分析这些学生的物理成绩是否与数学成绩相关。

分析的操作方法是：

选中数据清单中的一个空白单元格，在编辑栏内输入公式“=CORREL（F2:

F7，I2:

I7）”，回车即可得到数学和物理成绩的相关系数。

这个计算结果有以下三种情况：

如果物理成绩中的高（低）分与数学成绩中的高（低）分对应，说明这两个数据集合是正相关。

计算结果应该是一个小于1的实数，它越接近于1，说明两者相关性越强；如果物理成绩中的低（高）分与数学成绩中的高（低）分对应，说明这两个数据集合是负相关。

计算结果应该是接近-1的实数，它越接近于-1，说明两者负相关越强；如果上述计算结果接近零，说明这两个集合中的数据互不相关。

另外，为了保证分析结果的准确性。

用于分析的两个数据集合中的数据个数不能太少，一般应在30个以上。

如果你将图1中的数学成绩换成一段时间内的商场客流量，同时将物理成绩换成同一时间内的商场销售额。

运用相同的方法进行计算，就可以得知客流量与销售额是否相关，从而为制定营业策略提供依据。

3.方差分析

在质量检验等领域，经常需要检验两个对象的平均值是否存在差异。

例如两个班级的数学课分别采用了不同的教学方法，我们需要通过期中考试成绩检验两种方法是否存在实质性差异，以便对教学改革的成果做出判断。

再如，一种手机经过改进，我们要了解两种手机的通话距离是否存在实质性差异。

就可以随机抽取数目相同的两种手机，在不同条件下测试它们的通话距离，从而了解手机的改进是否有效。

在上述两个例子中，影响实验结果的因素只有一个。

我们将其称为单因素实验，对应的方差分析就是单因素方差分析。

下面我们以不同教法的两个班级的数学课为例，说明单因素方差分析的操作方法：

假设这两个班级的期中考试数学成绩分别存放在A2:

A46和B2:

B46区域，请你将光标放在该区域以外的任意一个单元格。

点击“工具→数据分析”菜单命令，在打开的对话框中选中“方差分析：

单因素方差分析”，“确定”之后打开同名对话框（如无此选项，请检查你的OfficeXP安装方式）。

其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“行”排