Excel数据分析应用指南Word格式文档下载.docx
《Excel数据分析应用指南Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《Excel数据分析应用指南Word格式文档下载.docx（14页珍藏版）》请在冰豆网上搜索。
点击“工具→选项”菜单命令，打开“选项”对话框中的“自定义序列”选项卡。
选中左边“自定义序列”下的“新序列”选项，此时光标就会在右边的“输入序列”框内闪动，你可以输入“广东省”、“云南省”等自定义序列，输入的每个序列之间要用英文逗号分隔，或者每输入一个序列就敲一次回车。
完成后点击“添加”按钮，将自定义序列添加到“自定义序列”框内备用。
自定义序列排序的方法与笔划排序很相似，你只要打开“排序选项”对话框中的“自定义排序次序”下拉列表，选中前面定义好的序列，其他选项保持不变。
回到“排序”对话框后根据需要选择“升序”或“降序”，“确定”后即可完成数据清单的排序。
注意：
假如数据按自定义序列的顺序排列，就应选中“排序”对话框中的“升序”排列，否则应该选择“降序”排列。
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安装方式）。
其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“行”排列的相邻数据区域组成，我们这个实例应当输入“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-（A2*0.08+A2*0.25+A2*0.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,B4*12,-B5）”。
公式中的第一个参数是利率，因为还贷额是按月计算的，所以要将年利率除以12变为月利率；
第二个参数是还款年限，由于按月还贷的缘故，必须将B4中的还贷年限乘以12；
第三个参数为贷款金额，如果不在B5前面加负号，计算出来的月还款金额就是负数。
为了照顾人们的阅读习惯，事先在贷款金额前加上负号，即可使计算出来的还贷金额便为正数。
此时依据上述公式计算出来的结果是“63.64”，即年利率为5%、期限10年的条件下，每月偿还贷款的金额是63.64万元。
另外，PMT函数还有Fv和Type两个参数。
Fv是贷款全部归还完毕后剩余的金额，省略时该值为零，即一笔贷款归还完毕后其账面金额为零。
Type的值是0或1，用来指定贷款的还款时间是在月初还是月末，0或省略表示还款时间是月初。
为了给模拟运算表提供分析依据，要紧接着公式“=PMT（B3/12,B4*12,-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.图表向导
点击“插入→图表”菜单命
升级会员 