Excel全攻略 (5)

要使单元格内的文字竖排，可以在其输入前(或后)选中这些单元格。打开“单元格格式→对齐”选项卡，将“方向”下竖排的“文本”选中，然后“确定”即可。 14.常数引用命名

为常数指定名称可以方便使用。如果某个工作表用2.1%计算利息，可以将它指定为常数“LX”。具体方法是：单击“插入→名称”子菜单下的“定义”命令打开对话框，在“当前工作薄的名字”框内输入“LX”，在“引用位置”框中输入“＝0.02.1”，“确定”之后即可使用该名称引用2.1%了。

Excel XP数据分析应用指南

Excel之所以强大，是因为其具有完整的数据分析函数和图表功能，让我们对数据进行归纳后，能够利用这些函数和图表对数据进行分析。本文以教育、金融和财务应用为例，介绍Excel XP数据分析的主要手段和操作方法。 一、数据的排序与筛选

排序与筛选是数据分析的基本手段。下面以图1所示的“学籍管理表”为例，介绍数据排序与筛选的操作方法。 1.数据排序 (1)数据清单：

Excel排序和筛选的操作对象是“数据清单”。它是在工作表首行的单元格内输入列标记(相当于数据库中的字段，可以作为关键字参与各种操作)，在列标记以下各行依次输入数据(相当于数据库是的一条条记录)而构成的表。其基本结构如图1所示，与Access数据表基本相同。 (2)常规排序：

Excel数据清单中的列标记可以作为关键字参与排序，具体操作方法有以下两种：如果你需要根据“年龄”、“总分”等数值关键字(字段)对数据清单排序，只须将图1中的“年龄”或“总分”选中，然后点击工具栏中的“升序排序”或“降序排序”按钮，整个数据清单就会根据该关键字重新排列。

如果你需要按姓氏笔划对图1数据清单排序，则应选中图1中B1单元格，点击“数据→排序”菜单命令，在打开的对话框中点击“选项”按钮打开“排序选项”对话框。选中“笔画排序”选项，根据图1中的数据排列方向选择“按列排序”选项，点击“确定”按钮后返回到“排序”对话框。将其中的“有标题行”选中，然后打开“主要关键字”下拉列表，选

46

择其中的“姓名”选面，最后选中排序方式(“升序”或“降序”)并确定，数据清单就会根据姓名的笔划重新排列。 如果数据清单排序依据多个关键字，你只要打开“排序”对话框，在“次要关键字”和“第三关键字”下拉列表中选择即可。

(3)自定义排序：

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

首先你要建立一个自定义序列：点击“工具→选项”菜单命令，打开“选项”对话框中的“自定义序列”选项卡。选中左边“自定义序列”下的“新序列”选项，此时光标就会在右边的“输入序列”框内闪动，你可以输入“广东省”、“云南省”等自定义序列，输入的每个序列之间要用英文逗号分隔，或者每输入一个序列就敲一次回车。完成后点击“添加”按钮，将自定义序列添加到“自定义序列”框内备用。

自定义序列排序的方法与笔划排序很相似，你只要打开“排序选项”对话框中的“自定义排序次序”下拉列表，选中前面定义好的序列，其他选项保持不变。回到“排序”对话框后根据需要选择“升序”或“降序”，“确定”后即可完成数据清单的排序。

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

从数据结构的角度看，图1所示“数据清单”就是一个小型数据库，其中的每一条记录占用一个表格行。对此，可以采用下面的方法检索数据：

点击“数据→记录单”菜单命令，在打开的对话框中点击“条件”按钮。对话框中的字段就会变成空白等待输入，而且“条件”按钮转变为“表单”按钮。你可以在对话框的相应字段中输入条件，例如在“姓名”字段框内输入“李小丫”回车，则姓名是“李小丫”的数据就会显示在对话框中。当然，你输入的检索条件可以使用>、<、>=、<=、<>等逻辑符号。例如点击“条件”按钮后在“总分”框内输入“>500”，表示检索“总分”大于500分的所有记录，回车后“新建”按钮上方就会显示“1/6”字样，说明6条记录中的第一条符合条件。点击“上一条”或“下一条”按钮，可以查看检索出来的其他记录，并显示“4/6”、“5/6”字样。

47

3.自动筛选

上面介绍的方法可以逐条查看检索出来的记录。如果你要批量查看符合条件的所有记录，仅靠“记录单”进行检索就不能满足你的需要。此时可以使用Excel的自动筛选功能，具体操作方法是：点击“数据→筛选→自动筛选”菜单命令，数据清单的列标记(字段)右侧会显示一个下拉按钮。如果你需要将总分大于500分的记录全部筛选出来，可以点击“总分”下拉按钮，选择“自定义”选项打开“自定义自动筛选方式”对话框。点击“总分”下面第一行右侧的下拉按钮，选择“大于”选项，然后在其右边的框内输入“500”，选中两行中间的“与”后确定，工作表就会显示所有符合筛选条件的记录。

当然，筛选出来的记录还可以再次筛选。假如你要将总分大于500分中“性别”为“女”的记录筛选出来。可以按相同方法打开“性别”下拉列表选择“女”，则“张晓菲”的记录就会显示在工作表中。

自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。如果你要清除筛选结果，点击“数据→筛选→自动筛选”菜单命令即可。另外，执行数据自动筛选时，如果“自定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应当选中“或”选项。 4.高级筛选

虽然“自动筛选”操作简单，但是可供使用的筛选条件有限。为此，Excel提供了“高级筛选”功能。它能够使用各种条件对数据清单进行筛选，其功能强大和使用灵活远非“自动筛选”可比。 (1)条件区域：

使用高级筛选必须在工作表中构造区域，它由条件标记和条件值构成。条件标记和数据清单的列标记相同，可以从数据清单中直接复制过来；条件值则须根据筛选需要在条件标记下方构造，是执行高级筛选的关键部分。 构造高级筛选的条件区域需要注意：如果条件区域放在数据清单的下方，那么两者之间应至少有一个空白行。如果条件区域放在数据清单的上方，则数据清单和条件区域之间也应剩余一个或几个空白行(一般不要这样设计，这样影响其他功能使用数据清单)。 (2)单列多条件：

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

48

依次输入筛选条件即可。例如你需要列出图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)执行筛选操作：

数据清单和条件区域建立完毕，你就可以执行数据筛选任务了，具体操作方法是：点击“数据→筛选→高级筛选”菜单命令，在打开的对话框中首先选择筛选结果的显示方式，若选择“在原有区域显示筛选结果”，则像自动筛选那样在数据清单中显示结果；否则就要在“复制到”框内指定筛选结果的显示位置。接着点击“数据区域”框，选中数据清单所在的区域，使该区域的绝对引用进入框内；再用相同方法完成对“条件区域”的绝对引用。点击“确定”按钮，就会在你选择的位置显示筛选结果。

如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数据清单。此时点击“数据→筛选→全部显示”菜单命令，即可恢复原来的数据清单。 二、数据的分析与求解

49

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的实数，它

50

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新经管营销Excel全攻略 (5)全文阅读和word下载服务。