Excel XP数据分析应用指南

ExcelXP数据分析应用指南Excel之所以强大，是因为其具有完整的数据分析函数和图表功能，让我们对数据进行归纳后，能够利用这些函数和图表对数据进行分析。本文以教育、金融和财务应用为例，介绍Excel??XP数据分析的主要手段和操作方法。??一、数据的排序与筛选??排序与筛选是数据分析的基本手段。下面以图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区域，请你将光标放在该区域以外的任意一个单元格。点击“工具→数据分析”菜单命令，在打开的对话框中选中“方差分析：单因素方差分析”，“确定”之后打开同名对话框(如无此选项，请检查你的Office??XP安装方式)。??其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“行”排列的相邻数据区域组成，我们这个实例应当输入“S|AS|2:S|BS|46”(可以用鼠标选中区域的方法输入)；“分组方式”用来确定输入区域中的数据如何排列。由于两个班级的成绩分别存放在A、B列，所以应当选中“分组方式”中的“列”选项；又由于A1、B1单元格带有班级代码，故应将“标志位于第一行”选中；它下面的“0.05”称为显著性水平，一般取默认值0.05即可；“输出区域”必须输入一个空白单元格引用，用来确定计算结果存放区域左上角的位置；如果你选中了“新工作表组”，就需要在右侧的框中输入该工作表的名称；若选中了“新工作簿”，则可以创建一个新的工作簿，并在其中粘贴计算结果。(不同教学方法)，“组内”就是误差，“总计”就是总和，“差异源”则是方差来源，“SS”就是平方和，“df”称为自由度(上例为1)，“MS”就是均方，“F”称为F比，“P-value”则是原假设(结论)成立的概率(这个数值越接近0，说明原假设(实验班和对照班的数学成绩没有显著差异)成立的可能性越小，反之原假设成立的可能性越大)，“Fcrit”为拒绝域的临界值。P-value”等于0.1，因此在显著性水平0.05的条件下原假设不成立，实验班和对照班的数学成绩有显著的差异，说明教学方法对成绩有显著影响。?4.实现利润分析??上面介绍的内容属于统计分析的范畴，目的是大量数据中寻找统计规律。而在企业管理等领域，管理人员则要了解不同因素或方案对经营目标的影响。例如确定四季度的销售利润总额以后，如何确定其他开支的数额，才能保证完成销售利润目标等。下面介绍一个分析实例：???假设某企业四季度的销售利润指标定为1650万元，如果其他条件保持不变，销售收入需要增加多少。由于销售利润与销售收入不是简单的线性关系(例如销售收入增加2万元，销售利润同比增加1.5万元)，而要受到多种因素的制约。例如增加销售收入就要加大销售成本和费用，还要缴纳更多的营业税。使用手工解决这类问题非常麻烦，需要根据销售收入与成本的关系逐步计算。Excel的单变量求解命令，就可以快速计算出结果，甚至可以针对不同情况反复计算。下面介绍这个问题的求解方法：设Excel工作表A2、B2、C2、D2和E2单元格分别存放“销售收入”、“销售成本”、“销售费用”、“营业税”和“销售利润”的数值。根据经验，“销售成本”约占“销售收入”的8%，“销售费用”约占“销售收入”的25%，“营业税”约占“销售收入”的10%。据此可以建立“销售收入”与其他因素的数学关系，这是执行单变量求解的关键。?根据上面的分析，可以选中E2单元格，在编辑栏输入公式“=A2-(A20.08+A20.25+A20.1)”。点击“工具→单变量求解”??菜单命令，在打开的对话框中可见E2自动进入“目标单元格”。接着在对话框的“目标值”内输入“1650”，在“可变单元格”框内输入“S|AS|2”(也可以将光标放入框中，然后点击A2单元格)。上述操作完成后点击“确定”按钮，就会弹出“单变量求解状态”对话框，说明已经求得一个解，而且目标值和“当前解”相同。与此同时，你可以在A2单元格中看到求出的“销售收入”，上面这个例子的计算结果是2894.737万元，即要想完成1650万元的销售利润指标，销售收入必须达到2894.737万元。?5.成绩评价分析??上面的实例讲解的是目标设定以后，实现这个目标必须满足的条件。但是实际中往往存在这样的问题，就是实现目标的某个或多个条件发生了变化，它会对结果产生哪些影响。???下面以图1所示的“学籍管理表为例”，说明某个学生单科成绩发生的变化，会对全体学生的总平均分有什么影响。??假如我们要分析学生“赵明君”的“数学”成绩提高到了70、80或90??分，全班学生的总平均分的变化情况如何。由于这种分析只涉及一种数据(“赵明君”的“数学”成绩)，所以称之为单变量数据表。分析的具体操作过程是：??在F10、F11、F12单元格中，依次输入70、80和90。然后在第一个数据(70)的上一行，而且位于该数据列右边的单元格(即G9)中输入公式“=SUM(F5:K5)”，敲回车计算出结果。然后在这个公式右边的单元格(即H9)中输入总平均分计算公式“=AVERAGE(L2:L7)”，再次敲回车计算出结果。?接着选中含有待分析数据(70、80、90)和个人总分及总平均分计算公式在内的单元格区域(F9:H12)，点击“数据→模拟运算表”??菜单命令打开相应对话框。因为我们要引用的数据放在列方向，所以必须将光标放入“输入引用列的单元格”，点击“赵明君”的“数学”成绩所在的单元格(即F5)，让它的绝对引用“S|FS|5”进入“输入引用列的单元格”，确定即可看到如图2所示的计算结果。(70、80、90)是沿着行存放(例如F9、G9和H9)，就应该在第一个数据所在单元格(F9)左边一列，而且位于数据行下方的单元格(E10)中输入公式“=SUM(F5:K5)”，然后在其下方输入总平均分计算公式“=AVERAGE(L2:L7)”。最后选中E9:H11区域，按上面介绍的方法打开“模拟运算表”对话框，点击“赵明君”的“数学”成绩所在的单元格(即F5)，让它的绝对引用“S|FS|5”进入“输入引用行的单元格”，回车即可计算出如图3所示的计算结果。

???6.贷款成本分析???上面介绍的单变量模拟运算表只能分析其他因素不变时，一个参数的变化对目标值的影响。如果要分析两个参数的变化对目标值的影响，例如贷款利率和偿还期限同时变化时，每月偿还金额发生的变化，就必须使用双变量模拟运算表。???假设某企业准备贷款6000万元，贷款期限预计为10年，已知该笔贷款的现行月利率为5%。企业领导考虑到这笔贷款的期限较长，必须分析利率变动和还款时间变化的影响。为此，双变量模拟运算表分析以上两个因素对偿还金额的影响。下面介绍这类问题的解决方法：???首先打开一个空白工作表，在有关单元格中输入说明数据意义的文字图4，然后在B3、B4和B5单元格中依次输入“现行年利率”、“贷款年限”和“贷款金额(万元)”的值。接着选中B2单元格，在其中输入公式“=PMT(B3/12,B412,-B5)”。公式中的第一个参数是利率，因为还贷额是按月计算的，所以要将年利率除以12变为月利率；第二个参数是还款年限，由于按月还贷的缘故，必须将B4中的还贷年限乘以12；第三个参数为贷款金额，如果不在B5前面加负号，计算出来的月还款金额就是负数。为了照顾人们的阅读习惯，事先在贷款金额前加上负号，即可使计算出来的还贷金额便为正数。此时依据上述公式计算出来的结果是“63.64”，即年利率为5%、期限10年的条件下，每月偿还贷款的金额是63.64万元。???另外，PMT函数还有Fv和Type两个参数。Fv是贷款全部归还完毕后剩余的金额，省略时该值为零，即一笔贷款归还完毕后其账面金额为零。Type的值是0或1，用来指定贷款的还款时间是在月初还是月末，0或省略表示还款时间是月初。???为了给模拟运算表提供分析依据，要紧接着公式“=PMT(B3/12,B412,-B5)”的右侧，即D2、E2、F2和G2单元格中分别输入“可能发生的还款年限”(8、9、11、12)。最后在公式下方的C3、C4和C5中依次输入“可能贷款利率”(4%、6%、7%)。???完成后将公式所在的单元格、“可能发生的还款年限”和“可能贷款利率”两种数据所在的区域(C2:G5)选中。点击“数据→模拟运算表”???菜单命令，在打开的对话框中，在“输入引用行的单元格”框中，输入由行数值(就是“可能发生的还款年限”)替换的输入单元格(B4)的绝对引用(S|BS|4)。然后在“输入引用列的单元格”框中，输入由列数值(就是“可能贷款利率”)替换的输入单元格(B3)的绝对引用(S|BS|3)。???上述内容输入结束以后，点击“确定”按钮，D3:G5区域就会显示分析结果。从中可以看出不同还款年限和利率所对应的月还款金额，从而为贷款成本评估提供依据。???7.最小还贷分析???模拟运算表可以分析某个或某两个因素改变时，由它们决定的结果会发生怎样的变化。但在企业管理、金融证券等领域，还存在着另外一类问题。就是在财力、物力和劳动力等资源受到限制的情况下，如何使经营利润最大或生产成本最小。这就是所谓的规划问题，寻求答案的过程就是“规划求解”。下面介绍它的加载及使用方面的有关问题：???(1)加载“规划求解”：???与上面介绍的其他分析工具不同，“规划求解”是以“加载宏”形式提供的工具。在默认情况下，它并不随着Excel的启动而运行，所以在“工具”菜单中看不到“规划求解”命令。如果你要加载“规划求解”，可以点击“工具→加载宏”???菜单命令，在打开的对话框中选中“加载宏”命令，确定之后即可在“工具”菜单下看到“规划求解”命令。???(2)规划求解方法：???假设某公司需要从不同银行贷三笔款项，金额分别为5000万元、6000万元和3000万元。假设贷款年利率的计算办法是贷款年限加一，然后乘以6‰，原定三笔贷款的还款期限分别是8年、9年和10???年，现在需要计算各笔贷款的还款期限分别是几年，才能确保到期时的还贷总金额最少。???首先建立有关的数据清单，在A2、A3和A4单元格分别输入三笔贷款的金额，接着在C2、C3和C4单元格输入原定的贷款年限。然后在B2单元格建立利率计算公式“=(C2+1)0.006”，并将它复制到B3和B4单元格，分别计算出三笔贷款的利率。继续在D2单元格建立公式“=PMT(B2，C2，-A2)”(相关参数的意义见上文)，并将它复制到D3和D4单元格，分别计算出三笔贷款的年还款金额。最后在D5单元格内输入公式“=SUM(D2:D4)”，计算出到贷款期时的还款总金额。???因为我们的目的是寻找D5单元格满足什么条件才能使还贷总金额最少，所以是一个求解“最小值”的问题，并且应该将D5将其作为目标单元格。点击“工具→规划求解”???菜单命令，打开“规划求解参数”对话框(如图5所示)。???此时D5单元格的绝对引用S|DS|5会自动出现在“目标单元格”框中，接下来就应该将“最小值”选中。由于贷款期限是决定还款总金额的因素，选择合适的贷款期限是我们的目标。所以要用鼠标点击“可变单元格”框，将光标拖过C2:C4区域，其绝对引用S|CS|2:S|CS|4自动进入其中。由于贷款年限都是整数，因此要对“可变单元格”进行“约束”。请点击“添加”按钮打开“添加约束”对话框，在“单元格引用位置”中指定C2:C4区域的地址S|CS|2：S|CS|4；再打开对话框中间的下拉列表，选择“INT”关系符，使“约束值”框内显示“整数”。点击“求解”按钮，Excel开始进行计算，最后出现“规划求解结果”对话框。???在规划求解找到结果的情况下，一般应出现在“报告”下的“运算结果报告”。确定之后，即可在原来的工作表旁边建立一个“运算结果报告”。从计算结果中可以看出，最佳的还款是14年。原来的计划需要归还贷款2087.57万元，而现在的计划只需1798.06万元，后一方案可以节约289.50万元。???当然，不是每一个规划求解问题都可以获得答案。如果问题的数学关系建立错误，约束条件选取不当等等。均可能导致目标单元格数值不收敛，或者在目标或约束条件单元格中发现错误。这都需要我们仔细分析问题的数学关系，重新建立模型和设置约束条件。???三、分析图表???Excel提供的信息不容易理解。如果你要更直观地观察数据反映的信息，必须借助图表这种数据分析和表现手段。???1.直接制作图表???如果你要利用图1的数据制作一个各科成绩随序号变化的簇状柱形图，只要选中相关的数据区域(图1的F2：K7)，然后按F11键即可在当前工作簿插入一个图表。???2.图表向导???点击“插入→图表”菜单命令，或者点击工具栏中的“图表向导”按钮，就可以打开“图表向导”，在它的指引下一步步建立图表。下面以建立60090105d^4的分析结果图表为例，说明图表向导的操作方法：???(1)选择图表类型：???“图表向导”第一步是选择你需要的图表类型，它的“标准类型”以及“自定义类型”选项卡总共提供了14(不含子图表)和20种图表。各种图表都有自己的最佳适用范围，例如柱形图显示一段时间内的数据变化或比较结果，用来反映数据随时间的变化很合适。条形图可以对数据进行比较，用来反映数据间的相对大小比较好。???如果你不知道当前工作表中的数据使用何种图表，可以通过以下方法找到满意的图表类型：选中工作表数据清单中的任意一个数据，对标准类型的图表来说，你可以选中“图表类型”及其“子图表类型”。然后点击“按下不放可查看实例”按钮，就可以在选项卡右侧看到数据生成的图表实例。查看“自定义类型”选项卡的图表实例更简单，你只要将其中的图表类型选中，就可以在选项卡右侧看到结果了。???(2)选择数据源：???图表向导的第二步打开“图表源数据”对话框。如果你需要将图4中的数据按“还款年限”分类，应把对话框中的“列”选中；如果你需要将图4中的数据按“可能贷款利率”分类，应把对话框中的“行”选中。然后将光标放进“数据区域”框内，根据图标制作需要，选中工作表中的数据区域(如图4中的D3:G5)，则“=Sheet1!???S|DS|3:S|GS|5”引用就会出现在“数据区域”框内。???如果你要引用的数据区域不连续。可以按下Ctrl键，然后用鼠标逐个选中工作表中的数据区域。采用这种方法可以在一个图表中引用多个工作表中的多处数据。???“图表源数据”对话框中的“系列”选项卡用来添加新的数据区域和X轴标志等内容。如果你要将新的数据区域(即“系列”)加入图表，点击“添加”按钮，就可以看到“系列”框中增加了一个“系列3”。将其选中后按上面介绍的方法将光标放入“值”框内，就可以根据需要选中工作表(包括其他工作表)中的数据区域。???(3)设置图表选项：???图表选项的第三步是设置图表选项，该对话框共有六个选项卡供你输入或设置图表选项。例如“标题”选项卡可供你输入X轴和Y轴的名称(如“周”、“数量和售价”)，“坐标轴”选项卡可以设置Y轴显示“刻度”与否等。这里的操作比较简单，你只要边预览边尝试就可以掌握。???3.图表加工???向导制作图表结束后，可以对图中的内容进行加工，从而使它变得更加美观。???(1)修改图例格式：???如果你感觉向导使用的图例字体和背景等不合适，可以右击图表中的图例，选择“图例格式”命令。打开“图例格式”对话框，可以看到“图案”、“字体”、“位置”三个选项卡。“图案”选项卡用来设置图例的边框和内部填充色等属性。通过“字体”选项卡你可以像Word那样设置图例的字体、字号、字形等。“位置”选项卡提供了“靠下”、“右上角”等五个位置选项，你只须选中一个并确定，图例就会自动放到你需要的地方。当然，你也可以选中图例，然后用鼠标将它拖到图表的任何位置。如果你不需要图例，选择右键菜单中的“清除”命令即可删除。???(2)修改坐标格式：???与“图例”相似，图表的坐标轴文字的属性也可以修改。你只要右击坐标轴文字，选择“坐标轴格式”命令，即可打开“坐标轴格式”对话框。其中“图案”、“字体”两个选项卡的用途与“图例”相同。“对齐”选项卡用来设置坐标轴文本的对齐方式，它和“单元格格式”、“对齐”选项卡基本相同，了解工作表格式设置的用户很容易掌握有关操作。???(3)修改背景和字体：???如果你感觉图表的背景不够靓丽，只须右击图表的背景，选择“图表区格式”命令打开相应的对话框。其中“图案”选项卡可以设置图表区的边框样式、是否显示阴影和圆角等，还可以选择不同颜色或填充效果美化图表背景。“字体”选项卡可以修改图表的文字格式，使文字显示的更加协调。“属性”选项卡用来设置“对象位置”和“锁定”等。你可以充分发挥自己的想象力，把图表打扮得漂漂亮亮。???(4)修改图表类型：???如果你感觉向导中选择的图表类型不合适，只要右击图表中的任意位置，选择“图表类型”命令，即可打开“图表类型”对话框，你就可以再次进行选择了。???(5)添加趋势线：???为了准确了解数据的变化趋势，我们还可以给图表加上趋势线，具体操作方法是：用鼠标右击图表中需要添加趋势线的数据系列(就是与数据对应的图线)，选择“添加趋势线”命令，就可以在对话框中选择你需要的趋势线类型了。???4.单变量分析图表???上文介绍过的某企业四季度销售利润总额指标定为1650万元，如果其他条件保持不变，销售收入需要增加多少的问题。这个问题采用图表同样可以解决，下面介绍处理的具体方法：???(1)创建图表：???选中存放“销售收入”、“销售成本”、“销售费用”、“营业税”和“销售利润”的A2、B2、C2、D2和E2单元格，点击“图表向导”按钮，按提示制作一个柱形图。为方便查看，在“图表向导步骤之2”中，打开“系列”选项卡，点击“名称”框，点击数据清单中的列表记，点击“添加”按钮，将系列名称放入图例完成图表制作。???(2)分析图表：???接着双击利润总额数据的柱状图，使其四周出现六个黑色的控点。再用鼠标指向利润总额柱状图的上沿，向上拖动直到显示的数据为1650为止(光标附近会显示当前数据大小)。此时松开鼠标，就会弹出“单变量求解”对话框。Excel自动将利润总额地址“E2”填入“目标单元格”框，同时将“1650”填入到“目标值”框(如果利润总额的数值不准确，可以在这里手工填写)。在可变单元格框中输入销售收入所在的单元格“S|AS|2”，也可以点击“可变单元格”选项，然后用鼠标点击A2单元格。点击“确定”按钮开始执行单变量求解，完成后再次“确定”。与图表关联的工作表数据就会发生变化，显示出单变量求解的结果。???四、数据透视表???Excel图表侧重于数据的静态分析，很难使用数据字段的组合动态查看数据。为此，Excel提供了面向数据清单的汇总和图表功能，这就是数据透视表和数据透视图。下面重点介绍数据透视表的数据分析方法。???1.创建数据透视表???数据透视表可以根据分析要求进行数据操作。例如进行不同级别的数据汇总，添加或删除分析指标，显示或隐藏细节数据，甚至改变数据透视表的版式等等。下面以图6所示的数据清单为例，说明数据透视表的创建方法。???(1)选择数据来源和报表类型：???首先将作为数据源的数据清单全部选中(即图6中的A1:D9区域)，点击“数据→数据透视表和图表报告”???菜单命令，打开“向导步骤之一”对话框，选中其中的“MicrosoftExcel数据清单或数据库”和“数据透视表”进入“下一步”。???(2)选择数据区域：???由于我们事先选中了待分析的数据区域，所以区域的绝对引用“S|AS|1:S|DS|9”会自动进入“选定区域”框。否则你必须点击“选定区域”框，然后拖动鼠标选择要分析的数据区域。???(3)选择显示位置：???制作的最后一步是选择数据透视表的显示位置，可以选择“新建工作表”或“现有工作表”。如果选择后者，则需要点击当前工作表的某个空白单元格，以确定数据透视表左上角的位置。点击向导中的“确定”按钮，带有数据源列字段的“数据透视表”对话框弹出，前面选定的位置显示报表框架。???(4)字段拖放：???这一步是创建数据透视表的关键，你必须根据数据分析的需要，挑选某些数据字段将其拖入报表框架。假如我们需要分析各家电连锁店5、6月份的销售数量，应当将“数据透视表”对话框中的“连锁店”拖入报表框架的“列字段”处，再将“月份”拖入报表框架的“行字段”处，最后将“销售数量(台)”???拖入报表框架中间的“数据项”，一个销量分析报表就建好了。???2.分析内容调整???与普通表格相比，用户对数据透视表的内容有更大控制权，这给数据分析带来了更多的自由。???(1)内容分组：???上面建好的数据透视表含有“月份”，它是一个日期型数据。此类型的数据可以根据需要按日、月或季度等重新分组。假如你需要分析2002年度的家电销量，其具体操作步骤是：用鼠标右击数据透视表中“5月”或“6月”所在的单元格，选择“组及分级显示”中的“组合”命令。打开“分组”对话框，先点击“依据”列表框中的“月”(将其取消)，再点击其中的“年”并确定。则数据透视表“月份”下的“5月”、“6月”就会消失，代之以“2002年”。???如果你希望按年度查看各月的数据，可以同时选中“依据”列表框中的“月”和“年”。数据透视表就会在“求和项”下分别显示“年”和“月”，并据此分组显示数据。???(2)修改分析对象：???如果你想再次分析各月份不同家电的销售量，也就是更换分析对象。并不需要将已完成的数据透视表删除，只须在原来基础上删除或添加分析字段，即可生成一个新的报表，这是数据透视表的重要优点。???生成新报表的操作方法是：将数据透视表中的原有列字段删除，你只须用鼠标按住图中的列字段“连锁店”，将它拖到数据透视表对话框即可。再将数据透视表对话框中的“家电名称”拖入列字段位置，即可获得各月不同家电销售量的分析报表。???按照这种方法，我们很容易对行字段和数据项进行修改，从而进行多个项目的快速分析。???(3)分类显示：???Excel中所有行、列字段都带有下拉按钮。如果你想了解表中某个分类的数据，可以点击该字段的下拉按钮，在列表中选择需要显示的分类数据。假如你想显示5月份的家电销售数据，可以点击“月份”下拉按钮。保留列表中的“2002年5月”，将其他项目前的选中标记全部去掉，此后的数据透视表仅仅显示5月份的销售数据。???(4)显示明细数据：???数据透视表的数据一般都是由多项数据汇总而来。为此，Excel提供了查看明细数据的方法。假如你想查看5月份各家连锁店的销售额，只须双击行字段中的“2002年5月”，就会弹出一个“显示明细数据”对话框。你只要选中明细数据所在的字段(例如“连锁店”)，点击“确定”按钮。数据透视表就会增加一个“连锁店”字段，并显示该字段对应的明细数据。此后，再次双击字段“2002年5月”，可以将“连锁店”字段及其明细数据关闭，反之可以重新打开。???(5)采用“页显示”：???在一个数据透视表中，若某个字段的数据分别显示在不同的行，就称其为行字段，这种显示方式称为“行方向显示”；同理，若某个字段的数据分别显示在不同的列，就称其为列字段和“列方向显示”。与此对应，数据透视表允许用户使用页字段，让对应这个字段的数据按“页方向显示”。假如你要分析每个家店连锁店的销售情况，可以设置“连锁店”字段的“页方向显示”，你只要将“连锁店”字段从框架的列字段处拖到页字段处。然后打开“连锁店”字段旁的下拉列表，从中选择你要查看的连锁店名称，确定以后即可在数据透视表中看到该店的销售情况。???(6)修改汇总函数：???数据透视表中的数据分为数值和非数值两大类。在默认情况下，Excel汇总数值数据使用求和函数，而非数值数据则使用计数函数。如果实际应用需要，用户还可以选择其他函数执行数据汇总。比较快捷的一种操作方法是：鼠标右击某个数据字段，选择快捷菜单中的“字段设置”命令，即可打开“数据透视表字段”对话框。选中“汇总方式”列表中的某个选项，点击“确定”按钮就可以改变数据的汇总方式了。

???(7)自动套用格式：???如果你感觉Excel默认的数据透视表格式不美观，可以按下面的方法自动套用格式：鼠标右击数据透视表中的任意单元格，选择快捷菜单中的“设置报告格式”命令。即可打开“自动套用格式”对话框，你只需选中需要的报表格式然后确定即可。???(8)修改显示方式：???在默认情况下，数据透视表中显示的是汇总数据。为了便于用户分析数据之间的关系，可以指定数据透视表以其他方式显示数据。为此，Excel提供了差异、百分比等八种显示方式。

???假如你要分析5月和6月家电销售量变化情况，可以选择差异方式显示汇总数据。其具体操作方法是：按上面介绍的方法打开“数据透视表”字段对话框，点击“选项”按钮使对话框展开。再打开“数据显示方式”下拉列表，先选择“差异”，再选中“基本字段”列表框中的“月份”，最后选中“基本项”中的“2002年6月”。确定以后则会显示以六月为基准的销售量变化情况。???(9)数据更新：???与图表不同，如果数据透视表的源数据被修改，则数据透视表的结果不能自动更新。需要按以下原则处理：首先对数据源所在的工作表进行修改，然后切换到数据透视表。鼠标右击其中的任意单元格，选择“更新数据”命令即可。