《excel数据分析教程》由会员分享，可在线阅读，更多相关《excel数据分析教程（15页珍藏版）》请在金锄头文库上搜索。
1、-目 录实现E*cel动态外部数据库2用E*cel做数据排序的常用方法与技巧11用E*cel做数据分析移动平均15用E*cel做数据分析抽样分析工具18用E*cel做数据分析相关系数与协方差22E*cel的数据分析排位与百分比24用E*cel做数据分析描述统计27用E*cel做数据分析直方图30用E*cel做数据分析回归分析方法35实现E*cel动态外部数据库我们有时需要在E*cel中调取其他数据库的数据，并且希望其他数据库数据改变时，E*cel中调取的数据也随之动态改变。下面介绍在E*cel过新建数据库查询(Microsoft Query)的方法来实现动态数据库。您在E*cel中第一次使用新
2、建数据库查询查询数据时，如果系统未安装Microsoft Query，系统会提示您安装。一、E*celvfp数据库vfp是常用的数据库，先以此来介绍。假设从p*.dbf表中取出相应学校的数据，学校的后5位代码，数据所在字段为*16，数据值为03行和06行相加，放入E*cel单个单元格中。步骤如下:1、点击菜单数据导入外部数据新建数据库查询，出现选择数据源对话框，选择Visual Fo*pro Tables*，按确定。如果您对SQL语句不是很熟悉的话，请使用默认的查询向导创立。(图1)2、在configure connection对话框中，按Browse按钮选择表p*.dbf所在的路径，按ok键
3、。(图2)3、在查询向导选择列对话框中，选择查询结果中的列，要选取的数据在*16字段，所以将可用表p*中的*16列移至查询结果中的列，按下一步。(图3)4、在查询向导筛选数据对话框中，因为此例中设置的条件在字段学校代码*dm和行号bh，非字段*16，所以点击下一步，暂时不筛选数据。(图4)5、在查询向导排序顺序对话框中，因为此例中所取的是单个数据，所在点击下一步。(图5)6、出现查询向导完成对话框，因还要对数据进展筛选，所以选择在Microsoft Query中查看数据或编辑查询，点击完成。(图6)7、进入Microsoft Query，看到查询结果*16列出了所有行的数据。接下来要通过设置条
4、件来筛选所需的数据。点击菜单条件增加条件(A).，字段bh,运算符等于，指定值为03，按添加。(图7)继续添加条件对话框，选择或，字段bh,运算符等于，指定值为06，按添加。(图8)继续添加条件对话框，选择与，字段*dm,运算符等于，指定值为11201，按添加后按关闭。(图9)但是看到符合条件*16为空，这是因为学校代码的后五位为11201。可以通过显示SQL来直接修改条件语句,将SQL语句中p*.*dm=11201改成right(p*.*dm,5)=11201,按确定。(图10)也可以在条件字段直接修改。(图11)光标放在*16字段，点击循环总计，将查询出的两个值相加，当然也可以直接在SQL
5、语句中修改。(图12)8、关闭Microsoft Query，弹出导入数据对话框，选择数据的放置位置，选择$B$2”，按确定。(图13)9、但我们发现显示了标题行，真正的数据却显示到了$B$3，这时我们需要修改$B$2单元格的数据区域属性。点击$B$2单元格，按右键菜单的数据区域属性，弹出外部数据区域属性窗口，将数据格式与布局中包含字段名等前面的对勾去掉，将数据区域中的行数随刷新而更改的方式改成用新数据覆盖现有单元格，并去除没有使用的单元格容，按确定。但还是有标题，没有关系，点击$B$2单元格，按右键菜单的刷新数据即可。(图14)10、如果p*.dbf数据库有所改变，则如何在E*cel中刷新，
6、可以点击数据所在单元格右键菜单中的刷新手工刷新;也可以在数据区域属性刷新控件中选上翻开工作簿时，自动刷新(见图14)，从而在每次翻开工作簿时，在查询刷新的提示框中按启动自动刷新按钮刷新(见图15)。(图15)11、如果需要编辑查询，则点击$B$2单元格，按右键菜单的查询编辑，如果出现查询向导无法编辑此查询的提示，只要按确定即可进入Microsoft Query。12、$B$3单元格查询条件与$B$2相差的只是学校代码，可以将$B$2单元格复制到$B$3单位格，再用$B$3的编辑查询来修改，修改一下学校代码的值即可。(图16)二、E*celSQL数据库SQL数据库与vpf大致是一样的。这里介绍的
7、是连接两表的查询。点击菜单数据导入外部数据新建数据库查询，出现选择数据源对话框，可直接选取所需的SQL数据库名。如果SQL数据库有密码的话，将提示输入密码，按确定。如果SQL SERVER效劳管理器未开启的话，将出现错误提示。如果不使用查询向导的话，可直接进入Microsoft Query。添加表news和表type，news表中的typeid和type表的typeid建立关联。在条件字段中选择字段名，并在值中输入条件值，这里输入的条件是type表的typeid字段值为1且news表的topic中含有小学生。点击选择要输出的字段名，这里选择的是type表中的typename字段和news表的t
8、opic字段。(图17)当然，如果您对SQL语句熟悉，也可以直接输入SQL语句。接下来的步骤请参考一、中的8、至10、。用E*cel做数据排序的常用方法与技巧在用E*cel制作相关的数据表格时，我们可以利用其强大的排序功能，浏览、查询、统计相关的数字。下面，我们以图1所示的员工根本情况登记表为例，来全面体验一番E*cel的排序功能。一、快速排序如果我们希望对员工资料按*列属性(如工龄由长到短)进展排列，可以这样操作:选中工龄列任意一个单元格(如I3)，然后按一下常用工具栏上的降序排序按钮即可(参见图1)。小提示:如果按常用工具栏上的升序排序按钮，则将工龄由短到长进展排序。如果排序的对象是中文字
9、符，则按汉语拼音顺序排序。如果排序的对象是西文字符，则按西文字母顺序排序。二、多条件排序如果我们需要按学历、工龄、职称对数据进展排序，可以这样操作:选中数据表格中任意一个单元格，执行数据排序命令，翻开排序对话框(图2)，将主要关键词、次要关键词、第三关键词分别设置为学历、工龄、职称，并设置好排序方式(升序或降序)，再按下确定按钮就行了。三、按笔划排序对进展排序时，国人喜欢按姓氏笔划来进展:选中列任意一个单元格，执行数据排序命令，翻开排序对话框(参见图2)，单击其中的选项按钮，翻开排序选项对话框(图3)，选中其中的笔划排序选项，确定返回到排序对话框，再按下确定按钮即可。小提示:如果需要按*行属性
10、对数据进展排序，我们只要在上述排序选项对话框中选中按行排序选项即可。四、自定义排序当我们对职称列进展排序时，无论是按拼音还是笔划，都不符合我们的要求。对于这个问题，我们可以通过自定义序列来进展排序:先把相应的职称序列按需要排序的顺序输入到相应的单元格区域(如N2至N18)中(图4);执行工具选项命令，翻开选项对话框(图5)，切换到自定义序列标签下，在从单元格中导入序列右侧的方框中输入$N$2:$N$18”(也可以用鼠标选择输入)，然后单击导入按钮，将相应的序列导入到系统中，确定返回。小提示:序列导入后，原来N2至N18区域中输入的数据可以删除，导入的序列在其他E*cel文档中均可直接使用。选中
11、职称列任意一个单元格，执行数据排序命令，翻开排序对话框，单击其中的选项按钮，翻开排序选项对话框(参见图3)，按自定义排序次序选项右侧的下拉按钮，在随后弹出的下拉列表中，选中上述导入的序列，确定返回到排序对话框，再按下确定按钮即可。五、用函数进展排序有时，我们对*些数值列(如工龄、工资等)进展排序时，不希望打乱表格原有数据的顺序，而只需要得到一个排列名次。对于这个问题，我们可以用函数来实现(以工龄为例):在工龄右侧插入一个空白列(J列)，用于保存次序(图6)，然后选中J2单元格，输入公式:=RANK(I2,$I$2:$I$101)，然后再次选中J2单元格，将鼠标移至该单元格右下角成细十字线状时(
12、这种状态，我们通常称之为填充柄状态)，按住左键向下拖拉至最后一条数据为止，次序即刻显示出来(图6)。本文图片来自网络小提示:假设要升序排序，可在公式最后增加一个非零参数，如将上述公式改为:=RANK(I2,$I$2:$I$101,1)。六、让序号不参与排序当我们对数据表进展排序操作后，通常位于第一列的序号也被打乱了，如何不让这个序号列参与排序呢我们在序号列右侧插入一个空白列(B列)，将序号列与数据表隔开。用上述方法对右侧的数据区域进展排序时，序号列就不参与排序了。小提示:插入的空列会影响表格的打印效果，我们可以将其隐藏起来:选中B列(即插入的空列)，右击鼠标，再选择隐藏选项即可。用E*cel做
13、数据分析移动平均*化工反响过程，每隔2分钟对系统测取一次压力数据。由于反响的特殊性，需要考察每8分钟的压力平均值，如果该压力平均值高于15MPa，则认为自属于该平均值计算围的第一个压力数据出现时进入反响阶段，请使用E*cel给出反响阶段时间的区间。移动平均就是对一系列变化的数据按照指定的数据数量依次求取平均，并以此作为数据变化的趋势供分析人员参考。移动平均在生活中也不乏见，气象意义上的四季界定就是移动平均最好的应用。注:本功能需要使用E*cel扩展功能，如果您的E*cel尚未安装数据分析，请依次选择工具-加载宏，在安装光盘支持下加载分析数据库。加载成功后，可以在工具下拉菜单中看到数据分析选项。
14、操作步骤1.翻开原始数据表格，制作本实例的原始数据要求单列，请确认数据的类型。本实例为压力随时间变化成对数据，在数据分析时仅采用压力数据列。需要注意的是，因为平均值的求取需要一定的数据量，则就要求原始数据量不少于求取平均值的个数，在E*cel中规定数据量不少于4。2.选择工具-数据分析-直方图后，出现属性设置框，依次选择:输入区域:原始数据区域;如果有数据标签可以选择标志位于第一行;输出区域:移动平均数值显示区域;间隔:指定使用几组数据来得出平均值;图表输出;原始数据和移动平均数值会以图表的形式来显示，以供比较;标准误差:实际数据与预测数据(移动平均数据)的标准差，用以显示预测与实际值的差距。数字越小则说明预测情况越好。3.输入完毕后，则可立即生成相应的数据和图表。从生成的图表上可以看出很多信息。根据要求，生成的移动平均数值在9:02时已经到达了15.55MPa，也就是说，包含本次数据在的四个数据前就已经到达了15MPa，则说明在8分钟前，也就是8:56时，系统进入反响阶段
