《ExcelXP数据分析应用指南》由会员分享，可在线阅读，更多相关《ExcelXP数据分析应用指南（13页珍藏版）》请在金锄头文库上搜索。
1、Excel XP数据分析应用指南张迎新Excel之所以强大，是因为其具有完整的数据分析函数和图表功能，让我们对数据进行归纳后，能够利用这些函数和图表对数据进行分析。本文以教育、金融和财务应用为例，介绍Excel XP数据分析的主要手段和操作方法。一、数据的排序与筛选排序与筛选是数据分析的基本手段。下面以60090105a1所示的“学籍管理表”为例，介绍数据排序与筛选的操作方法。1.数据排序(1)数据清单：Excel排序和筛选的操作对象是“数据清单”。它是在工作表首行的单元格内输入列标记(相当于数据库中的字段，可以作为关键字参与各种操作)，在列标记以下各行依次输入数据(相当于数据库是的一条条记录
2、)而构成的表。其基本结构如图1所示，与Access数据表基本相同。(2)常规排序：Excel数据清单中的列标记可以作为关键字参与排序，具体操作方法有以下两种：如果你需要根据“年龄”、“总分”等数值关键字(字段)对数据清单排序，只须将图1中的“年龄”或“总分”选中，然后点击工具栏中的“升序排序”或“降序排序”按钮，整个数据清单就会根据该关键字重新排列。如果你需要按姓氏笔划对图1数据清单排序，则应选中图1中B1单元格，点击“数据排序”菜单命令，在打开的对话框中点击“选项”按钮打开“排序选项”对话框。选中“笔画排序”选项，根据图1中的数据排列方向选择“按列排序”选项，点击“确定”按钮后返回到“排序”
3、对话框。将其中的“有标题行”选中，然后打开“主要关键字”下拉列表，选择其中的“姓名”选面，最后选中排序方式(“升序”或“降序”)并确定，数据清单就会根据姓名的笔划重新排列。如果数据清单排序依据多个关键字，你只要打开“排序”对话框，在“次要关键字”和“第三关键字”下拉列表中选择即可。(3)自定义排序：如果你要求图1中的数据按照“广东省”、“云南省”、“四川省”、“山西省”和“河北省”的顺序排列，前面介绍的两种方法就无能为力了，只能采取自定义排序规则的方法处理。首先你要建立一个自定义序列：点击“工具选项”菜单命令，打开“选项”对话框中的“自定义序列”选项卡。选中左边“自定义序列”下的“新序列”选项
4、，此时光标就会在右边的“输入序列”框内闪动，你可以输入“广东省”、“云南省”等自定义序列，输入的每个序列之间要用英文逗号分隔，或者每输入一个序列就敲一次回车。完成后点击“添加”按钮，将自定义序列添加到“自定义序列”框内备用。自定义序列排序的方法与笔划排序很相似，你只要打开“排序选项”对话框中的“自定义排序次序”下拉列表，选中前面定义好的序列，其他选项保持不变。回到“排序”对话框后根据需要选择“升序”或“降序”，“确定”后即可完成数据清单的排序。注意：假如数据按自定义序列的顺序排列，就应选中“排序”对话框中的“升序”排列，否则应该选择“降序”排列。2.数据检索从数据结构的角度看，图1所示“数据清
5、单”就是一个小型数据库，其中的每一条记录占用一个表格行。对此，可以采用下面的方法检索数据：点击“数据记录单”菜单命令，在打开的对话框中点击“条件”按钮。对话框中的字段就会变成空白等待输入，而且“条件”按钮转变为“表单”按钮。你可以在对话框的相应字段中输入条件，例如在“姓名”字段框内输入“李小丫”回车，则姓名是“李小丫”的数据就会显示在对话框中。当然，你输入的检索条件可以使用、=、=、等逻辑符号。例如点击“条件”按钮后在“总分”框内输入“500”，表示检索“总分”大于500分的所有记录，回车后“新建”按钮上方就会显示“1/6”字样，说明6条记录中的第一条符合条件。点击“上一条”或“下一条”按钮，
6、可以查看检索出来的其他记录，并显示“4/6”、“5/6”字样。3.自动筛选上面介绍的方法可以逐条查看检索出来的记录。如果你要批量查看符合条件的所有记录，仅靠“记录单”进行检索就不能满足你的需要。此时可以使用Excel的自动筛选功能，具体操作方法是：点击“数据筛选自动筛选”菜单命令，数据清单的列标记(字段)右侧会显示一个下拉按钮。如果你需要将总分大于500分的记录全部筛选出来，可以点击“总分”下拉按钮，选择“自定义”选项打开“自定义自动筛选方式”对话框。点击“总分”下面第一行右侧的下拉按钮，选择“大于”选项，然后在其右边的框内输入“500”，选中两行中间的“与”后确定，工作表就会显示所有符合筛选
7、条件的记录。当然，筛选出来的记录还可以再次筛选。假如你要将总分大于500分中“性别”为“女”的记录筛选出来。可以按相同方法打开“性别”下拉列表选择“女”，则“张晓菲”的记录就会显示在工作表中。自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。如果你要清除筛选结果，点击“数据筛选自动筛选”菜单命令即可。另外，执行数据自动筛选时，如果“自定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应当选中“或”选项。4.高级筛选虽然“自动筛选”操作简单，但是可供使用的筛选条件有限。为此，Excel提供了“高级筛选”功能。它能够使用各种条件对数据清单进行筛选，其功能强大和
8、使用灵活远非“自动筛选”可比。(1)条件区域：使用高级筛选必须在工作表中构造区域，它由条件标记和条件值构成。条件标记和数据清单的列标记相同，可以从数据清单中直接复制过来；条件值则须根据筛选需要在条件标记下方构造，是执行高级筛选的关键部分。构造高级筛选的条件区域需要注意：如果条件区域放在数据清单的下方，那么两者之间应至少有一个空白行。如果条件区域放在数据清单的上方，则数据清单和条件区域之间也应剩余一个或几个空白行(一般不要这样设计，这样影响其他功能使用数据清单)。(2)单列多条件：如果某一个条件标记下面输入了两个或多个筛选条件，我们将其称为单列多条件，你只要在条件标记下自上而下依次输入筛选条件即
9、可。例如你需要列出图1中年龄大于17和年龄小于17的学生名单，只需在图1条件标记“年龄”的下方D10和D11单元格输入“17”和“=85”即可。(4)多行单条件：构造高级筛选条件有这样的要求：如果多个筛选条件需要同时满足，它们必须分布于条件区域的同一行，这就是所谓的“与”条件，否则筛选条件必须分布于条件区域的不同行，也就是“或”条件。假如你要在图1中找出“性别”是“男”，或“语文”成绩“=80”，或“化学”成绩“=90”的所有记录。可以在图1条件区域的C10单元格内输入“男”，然后在G11单元格内输入“=80”，最后在J12单元格内输入“=90”即可。(5)两列两组条件：如果你需要在图1中寻找
10、物理成绩大于等于90的男生或者物理成绩大于等于80的女生，可以按照如下方法构造条件区域。C10、C11单元格内分别输入“男”和“女”，接着在H10、H11单元格内分别输入“=90”和“=80”即可。(6)执行筛选操作：数据清单和条件区域建立完毕，你就可以执行数据筛选任务了，具体操作方法是：点击“数据筛选高级筛选”菜单命令，在打开的对话框中首先选择筛选结果的显示方式，若选择“在原有区域显示筛选结果”，则像自动筛选那样在数据清单中显示结果；否则就要在“复制到”框内指定筛选结果的显示位置。接着点击“数据区域”框，选中数据清单所在的区域，使该区域的绝对引用进入框内；再用相同方法完成对“条件区域”的绝对
11、引用。点击“确定”按钮，就会在你选择的位置显示筛选结果。如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数据清单。此时点击“数据筛选全部显示”菜单命令，即可恢复原来的数据清单。二、数据的分析与求解Excel提供了多种数据分析手段，从函数、分析工具库、加载宏等，一直到数据透视表和数据透视图。下面介绍以函数和加载宏为主的若干分析工具。1.成绩分布频率分析学生成绩分析的一项重要任务，就是统计各分数段中的人数，为研究成绩分布提供基础数据。下面以图1为例，说明如何计算550500、500450、450400、400350，以及小于350分数段内的人数。首先在图1中的M2：M6
12、区域依次输入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区域就会显示各分数段中的成绩个数。注意：数组公式“=FREQUENC
13、Y(B2:B6，C2:C6)”可以对一组或多组数值进行多重计算，并得出一个或多个计算结果。数组公式中的大括号“ ”不能用手工输入，只能按组合键Shift+Ctrl+Enter自动生成。2.对象相关性分析教育教学研究的任务之一是了解各学科之间的相互关系，例如数学的学习是否对物理成绩有影响。而商品销售领域，也需要分析两组数据(单位可以不同)之间是否相关。例如空调和冷饮的销售量与气温之间的关系，或者商场的客流人数与销售额是否相关等等。这里仍然以图1所示的“学籍管理表”为例，我们的任务是分析这些学生的物理成绩是否与数学成绩相关。分析的操作方法是：选中数据清单中的一个空白单元格，在编辑栏内输入公式“=C
14、ORREL(F2:F7，I2:I7)”，回车即可得到数学和物理成绩的相关系数。这个计算结果有以下三种情况：如果物理成绩中的高(低)分与数学成绩中的高(低)分对应，说明这两个数据集合是正相关。计算结果应该是一个小于1的实数，它越接近于1，说明两者相关性越强；如果物理成绩中的低(高)分与数学成绩中的高(低)分对应，说明这两个数据集合是负相关。计算结果应该是接近-1的实数，它越接近于-1，说明两者负相关越强；如果上述计算结果接近零，说明这两个集合中的数据互不相关。另外，为了保证分析结果的准确性。用于分析的两个数据集合中的数据个数不能太少，一般应在30个以上。如果你将60090105a1中的数学成绩换
15、成一段时间内的商场客流量，同时将物理成绩换成同一时间内的商场销售额。运用相同的方法进行计算，就可以得知客流量与销售额是否相关，从而为制定营业策略提供依据。3.方差分析在质量检验等领域，经常需要检验两个对象的平均值是否存在差异。例如两个班级的数学课分别采用了不同的教学方法，我们需要通过期中考试成绩检验两种方法是否存在实质性差异，以便对教学改革的成果做出判断。再如，一种手机经过改进，我们要了解两种手机的通话距离是否存在实质性差异。就可以随机抽取数目相同的两种手机，在不同条件下测试它们的通话距离，从而了解手机的改进是否有效。在上述两个例子中，影响实验结果的因素只有一个。我们将其称为单因素实验，对应的方差分析就是单因素方差分析。下面我们以不同教法的两个班级的数学课为例，说明单因素方差分析的操作方法：假设这两个班级的期中考试数学成绩分别存放在A2:A46和B2:B46区域，请你将光标放在该区域以外的任意一个单元格。点击“工具数据分析”菜单命令，在打开的对话框中选中“方差分析：单因素方差分析”，“确定”之后打开同名对话框(如无此选项，请检查你的Office XP安装方式)。其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“
