excel数据分析篇（1）

一、Excel在数据分析中的简单应用

（一）Excel在数据输入处理的应用

Excel大多数情况下是用“列表格式”存储数据，将书面数据信息输入到计算机很多情况下须手工完成，但有时也可根据数据本身的规律性或借助Excel的某些功能来提取数据。

例如，将xx大学2011级学生考研信息录入到Excel的表格中，有姓名，序号，性别，学号，班级，身份证号等几项数据。

其中，姓名、学号、身份证号、性别手工输入；序号利用Excel本身定义的拖动复制功能输入；出生日期的输入可以利用文本截取函数从身份证号中分别提取，并用日期函数结合成出生日期。

（二）Excel在数据审核处理中的应用

数据本身要求符合一定的逻辑，但在手工输入过程中难免出现错误。在输入的同时让计算机自动识别不合逻辑之处并给出提示，有助于及时判断输入数据是否有误。

（三）Excel在数据描述统计的应用

Excel在计算平均数、方差（标准差）、众数、中位数时分别利用公式“AVERAGE”、“VAR”（STDEV）、“MODE”、“MEDIAN”，并选择选择需要求解的范围即可得出最终结果。例如，对“学业成绩A”计算均值。

利用公式“AVERAGE”，选择需要求解平均数的范围C5：C38，计算出最后结果78.16118。

二、SPSS在数据分析中的简单应用

（一）SPSS在数据输入处理的应用

用SPSS统计软件进行数据输入可以通过直接输入和间接导入两种方式读取数据。SPSS可以导入不同类型文件的数据，如xls、dat、inc、csv等。

（二）SPSS在数据筛选排序中的应用

利用SPSS选择“数据”―“选择个案”，进行条件的筛选。SPSS的数据排序是对数据窗口中的数据按照某个或几个指定变量的变量值升序或降序重新排列。以排序变量的多少分为“单值排序”、“多重排序”。

（三）SPSS在数据描述统计的应用

SPSS在计算平均数、众数、中位数时可通过“分析”―“描述统计”―“概率”菜单下进行依次选择求解。也可以同时求解出均值、众数、中位数。并可以利用“描述数据”功能求出所研究数据的方差（标准差）。

三、Excel与SPSS比较分析

Excel与SPSS在进行统计数据分析中都有各自的特点，具有分析如下：

其一，Excel在数据输入分析中应用较简便，不需对有关数据进行额外的变量设置，但处理较多大数据操作比较困难；SPSS相对专业性较强，对大数据的处理步骤和结果都较为详细，但在数据变量设置方面不太简便。

其二，Excel在数据分析中最为基础且易掌握，图形工具强大，但不适宜大型统计分析；SPSS为较为专业的统计应用软件，对于大型数据的统计应用较多但对于图形工具不太全面。

其三，就统计学原理所涉及的统计方法而言，Excel没有直接提供包括箱线图、相关系数的p-值、方差分析中的多重比较、非参数检验方法、质量控制图等方法，而SPSS功能比较完善。

其四，大部分情况下Excel的计算结果都是可靠的，但在一些极端情况下Excel的计算程序不够稳定和准确，有些自动功能可能会导致意想不到地结果。相比之下，SPSS计算结果比较准确，适用于学术研究。

其五，SPSS的数据编辑窗口与Excel类似且可定义数据的属性，但其数据管理功能相对较弱如只允许同时打开一个数据文件，这对有些分析工作来说可能不够方便。

四、结论

不敢预测也不可能断言，在未来的统计学理论研究中统计软件处理会占据统治地位，但是统计软件处理数据越来越渗透到经济学研究中，特别是数据分析方面，并且发挥着越来越重要的作用已成为事实。而且还应当说，统计学学不仅应用了统计软件，如Excel和SPSS，而且还会不断地应用着统计软件发展的最新的成果。因为统计软件的不断发展也在致力于解决能够描述复杂现象的数据现象。当然，虽然统计软件是统计学中必不可少的应用，但是统计软件在统计学中只是作为一种工具被用来考虑或研究基本现象或行为背后的规律。作为工具和方法必须在理论的合理框架中才能真正发挥其应有的作用而不能替代真正经济数据的发展，否则会出现因噎废食。我们从另一个角度上看，这反过来也推动了Excle和SPSS等有关统计软件的发展，为其的发展提供了源源不断的动力。在未来的数据分析领域中，我们要好好地运用这一重要工具进行更加精确、更为深入的研究，科学地融合统计软件与统计数据研究，推动共同进步发展。

参考文献：

excel数据分析篇（2）

中图分类号：TP391 文章编号：1009-2374（2017）07-0056-02 DOI：10.13535/ki.11-4406/n.2017.07.027

1 概述

在水电站的维护管理中，对水库大坝的监测是极其重要的一项内容，保障了水库大坝日常的运行安全。通过监测技术得到了许多大坝的数据信息，我们对这些数据信息进行系统的分析，可以及时地获取大坝工作运行的实际状态，为水电站的调度与日常维护提供可靠的数据信息。但是在实际的安全检测工作中，监测点是非常多的，每个监测点的数据信息非常多，在许多时候我们需要得到某个事件说明的时候，就需要在这些数据信息中寻找想要的信息，一般需要人工来进行，其效率就会非常慢，并且使用人工就非常容易出错，其结果也会出错。然而Excel有着数据信息统计的能力，只需要使用一些常用的函倒δ埽就能够很容易处理需要检测的数据信息，进而提高数据信息的分析能力。

2 函数工具

在Excel软件里面函数工具极其多，在大坝监测工作中所用到的函数工具主要有以下三种：

2.1 运算符号

在函数公式中，需要用到很多运算符号，主要有算术运算符号、逻辑运算符号、文本运算符号、引用运算符号等，这些运算符号和数学计算法则比较一致的，其运算逻辑也相同。

2.2 查找与引用

Excel系统的查找与引用，主要是把符合有些条件的字符串与数值按照要求查找并引用到指定的单元格内或者放在公式中。我们在对大坝进行监测中会获取许多的数据信息，这些数据信息在分析处理的时候，经常会用到LOOKUP函数与VLOOKUP函数。

LOOKUP函数主要的功能是一个数值和一行数据或者一列数据逐一进行比较，在找到匹配数值之后，把另外一组数据信息中所对应的数值提取出来，但是这种函数公式只能够进行模糊查找。

VLOOKUP函数主要是一个数据与整个表对比，并非像LOOKUP函数那样和某一列数据或者某一行数据进行对比，并且使用VLOOKUP函数公式可选择的范围比较广，精确查询与模糊查询都可以。

2.3 IF函数

IF函数通常用在真假值的判断之后，按照逻辑判断真假值的返回找出不同结果。在大坝监测的数据分析中，在查找函数没有办法在所选表格区域当中找到匹配值，系统会出现“#N/A”的错误值，在“#N/A”错误的时候，可能分析内容起就很容易出错，有时候还要结合其他函数一起使用才行。

3 大坝监测数据分析Excel的应用

3.1 数据的收集

在水库大坝监测工作中，要对监测出来的数据进行分析研究，我们要先明确想要的结果，并且根据实际需求来收集整理监测数据信息，通过Excel函数工具把这些数据信息整理成想要的结果。

3.2 成图

在大坝监测数据分析工作中，Excel函数工具中有插入图表的功能，可以对数据信息进行研究分析整理成想要的图表，一般情况下都是采用散点图来分析趋势的。

4 实例

以某水电站为例，使用Excel函数工具来分析监测数据信息。该水电站工程是大型水电工程，水电站工程的主要任务是发电。水库蓄水位正常的是1300米左右，洪水位约为1300米，死水位大约为1285米，水库的总容量为1.59亿立方米，总装机100兆瓦，保证出力约10兆瓦，装机利用小时数3500小时，今年来平均年发电量约4亿千瓦小时。这个水电站挡水的建筑物是碾压式沥青混凝土墙坝，大坝顶的高程大约为1300米，防浪墙顶的高程大约为1300米，大坝顶的宽度大约为10米，坝高最高为大约为90米，大坝的长度约为439米。在大坝的上游设置半米左右厚混凝土的护坡，在大坝的下游设置预制的网格梁，可以种植草皮来护坡。心墙顶厚度大约为0.5米，心墙两侧摆放过渡料，水平的宽度一般为3米，在大坝的两岸设置4个测压孔，测压孔的参数见（表1）。

4.1 数据的统计与图表

从数据库中导出测压计的历史数据（表2），从测压计的历史数据来看每个测压点的数据信息相互交错，如果要从这些复杂庞大数据库里面找到想要的数据信息，就会用到Excel函数工具数据库里面的每项数据，复制粘贴到各种工作表中：

在使用Excel函数工具把数据进行汇总之后，会发现在数据列里面出现了许多“#N/A”错误，使得过程线无法连续，因此用Excel函数工具来判断数据是否是“#N/A”错误，Excel函数工具的使用可以得到连续的过程

线图。

4.2 数据分析

对上述数据表格进行研究分析，能够发现UP1测压管的历史过程线和库水位的历史过程线的关联非常密切，其相关性非常强，我们结合其他数据信息得知，左岸UP1测压管钻孔和水库内是通着的。

5 结语

总而言之，在以上的实例分析中仅仅使用了Excel数据分析工具中很少的一部分功能，即已经达到了大幅减少大坝安全数据分析工作量的目的，Excel数据分析工具还有很多，通过其强大的分析功能，能够进行各种数据处理并辅助监测人员进行决策，通过更加直观的表达方式大大地提高数据分析工作效率。

参考文献

[1] 赵小莉，李芙蓉，王立志.浅析水库大坝变形监测分析与预报[J].水利大坝监测，2015，23（25）.

excel数据分析篇（3）

1 Excel中加载“数据分析”工具

首先，启动Excel，点击工具菜单中的“加载宏”选项，在出现的对话框中选中“分析工具库”，确定安装后，在菜单栏的“工具”下会出现“数据分析”选项。

2 使用Excel进行数据统计描述

用Excel可进行数据的统计描述，包括：（1）集中趋势（集中指标），它包括算术平均数，中位数，几何平均数，众数等。Excel提供有现成的公式及内置函数可进行这几个指标的计算。如首先输入一组数字，然后建入公式=AVERAGE（常数），=MEDIAN（常数），和=MODE（常数），按回车健可得到算术平均数，中位数和众数；（2）离散趋势（变异指标），它包括全距，百分位数，四分位数间距，方差，标准差，标准误，偏度系数和峰度系数等。而其中以方差，标准差，百分位数和标准误较为常用。同样在电子表格中建入公式=VARP（常数）和=VAR（常数）两函数可计算总体方差和样本方差。建入公式=STDEVP（常数）和=STDEV（常数）便可得到总体标准差和样本标准差。

3 t检验

t检验是医学统计分析中最常用的统计分析方法，用来检验标准试样测定结果的平均值与标准值之间是否有统计学意义。Excel“数据分析”中提供了多种不同条件的t检验工具，如“平均值的成对二样本分析”，“双样本等方差假设”，“双样本异方差假设”及其他统计分析工具，当样本中的观察值存在配对关系时，可以使用“平均值的成对二样本分析”t检验。例如对一个样本组在实验前后进行了两次检测，为确定实验前后样本均值是否相等，应使用成对t检验，此t检验并不假设两个总体的方差是相等的。例如，用某药物治疗高血压患者10名，治疗前后舒张压变化如下：

在工作表中输入上面的数据，比如数据区为A1至J2。分析时，在“工具”菜单中，单击“数据分析”命令。在数据分析对话框中，选择t检验：平均值的成对二样本分析，拉出平均值的成对二样本分析对话框，其中有如下输入项：变量1的区域：输入需要分析的第1个数据区域的单元格引用。该区域必须由单列或单行数据组成。可单击输入框右面的按钮，回到电子表格上自数据开始的单元格向结尾的单元格拖动。此时变量1的区域（A1~J1）自动进入输入域中。然后单击输入域右面的按钮，回到原对话框。变量2的区域：输入需要分析的第2个数据区域的单元格引用。该区域必须由单列或单行的数据组成。输入方法同前。变量2的区域为（A2~J2）。假设平均差：在此输入期望中的样本均值的差值。缺省为0值，即假设样本均值相同。标志：如果输入区域的第1行或第1列中包含有标志项，应选中此项：如果输入区域没有标志项，Excel将在输出表中生成适宜的数据标志。这里选中此项。α在此输入检验的统计意义水平。该值范围为0～1之间。缺省为0.05。

输出区域信息可选择如下单选项：输出区域和新工作簿。我们选择新工作表，结果为平均95.88889，方差80.86111，观测值9，泊松相关系数0.881366，假设平均差0，df 8，tstat 5.230769，P（T≤t）单尾0.000396，t单尾临界1.859548，P（T≤t）双尾0.000792，t双尾临界2.306004。两组比较差异有统计学意义（P

4 方差分析（analysis of variance，ANOVA）和卡方检验

在数据分析工具库中提供了3种基本类型的方差分析：单因素方差分析、可重复双因素分析和无重复双因素分析，现简单介绍方差分析的应用。

单因素方差分析：在进行单因素方差分析之前，须先将试验所得的数据按一定的格式输入到工作表中，其中每种水平的试验数据可以放在一行或一列内，具体的格式如表，表中每个水平的试验数据结果放在同一行内。数据输入完成以后，操作“工具-数据分析”，选择数据分析工具对话框内的“单因素方差分析”，出现一个对话框，对话框的内容如下：（1）输入区域：选择分析数据所在区域，可以选择水平标志，针对表中数据进行分析时选取；（2）分组方式：提供列与行的选择，当同一水平的数据位于同一行时选择行，位于同一列时选择列，本例选择行；（3）如果在选取数据时包含了水平标志，则选择标志位于第一行，本例选取；（4）α：显著性水平，一般输入0.05，即95%的置信度；（5）输出选项：按需求选择适当的分析结果存储位置。 双因素无重复试验方差分析 与单因素方差分析类似，在分析前需将试验数据按一定的格式输入工作表中。 数据输入完成以后，操作“工具-数据分析”，选择数据分析工具库中的“双因素无重复方差分析”，出现一个对话框，对话框的内容如下：（1）输入区域：选择数据所在区域，可以包含因素水平标志；（2）如果数据输入时选择了因素水平标志，请选择标志按钮；（3）显著性水平α：根据实际情况输入，一般选择0.05；（4）输出选项：按需要选择分析结果存储的位置。

可重复双因素分析：双因素可重复方差分析与双因素无重复方差分析数据输入的区别在于对重复试验数据的处理，就是将重复试验的数据叠加起来。 数据输入完成以后，操作“工具-数据分析”，选择数据分析工具库中的“双因素可重复方差分析”，出现一个对话框，对话框的内容基本与双因素无重复方差分析相同，区别在于每一样本的行数选项，在此输入重复试验的次数即可。若须对数据进行方差分析时，在输入区域选择数据所在区域及因素水平标志，在每一样本的行数处输入3，即每种组合重复3次试验，显著性水平选择0.05。在输出选项中可以按照需求选择分析结果储存的位置。选择确定以后分析结果。在工具菜单中均有现成的这3种分析工具。

5 非参数检验

Excel也没有提供非参数检验的分析工具，但可利用其提供的函数和公式可进行分析。如利用IF（指定要执行的逻辑检验函数）和COUNTIF（ 条件函数 ）进行编秩。利用=SUMIF（条件单元格求和函数 ）计算正负秩和。=COUNT（个数函数）利用=ABS和=SQRT计算绝对值和z值。

6 相关回归

为了反映两个或多个变量之间的关系，描述相关关系的方向与密切程度，需采用相关分析；为了反映两个或多个变量之间的依存关系，建立回归方程，采用回归分析。先将数据输入工作表中，然后用Excel提供的函数可进行线性回归（linear regression）又称简单回归的分析和Spearman等级相关分析，用菜单中的“工具数据分析相关糸数可进行相关分析。用“工具数据分析协方差工具可进行协方差分析。用Excel的XY散点图工具可以进行散点图的绘制。

Excel是一种使用极方便的电子表格软件，它有强大的数据管理功能，不仅能够根据需要分类管理数据信息，能进行数据统计、筛选、排序、汇总、汇制图表等，还能利用其强大的函数功能以及分析工具库为建立复杂的统计或计量分析工作带来极大的方便。

excel数据分析篇（4）

中图分类号： TP317.1 文献标识码：A 文章编号：1009-3044（2013）04-0802-03

The Analysis of Variety of Data Aggregation Methods in Excel

LI Hua

（Computer and Network Center， Communication University of China， Beijing 100024， China）

Abstract： Excel provides a variety of data aggregation methods to summarize and analyze large amounts of data. The valuable information can be extracted by these methods to help make decision. This paper introduced several general applications of data aggregation method by detail examples and pointed out the characteristics of each method. It can help users analyze data more efficient in working practice.

Key words： Excel； data aggregation； data analysis

Excel软件已广泛应用于各个行业，无论是哪一个行业，只要和数据打交道，Excel几乎是首选的工具。数据分析是Excel提供的主要功能之一，而数据汇总又是主要的数据分析工具，可以对数据进行由粗到细、由多到少的处理，为管理人员做好决策提供有用的参考信息。Excel提供了多种数据汇总方法，常用的汇总方法有“分类汇总”、“数据透视表”、“合并分析”、“模拟运算表”等。

1 分类汇总

分类汇总是将数据按照某个关键词段分类，并对关键词段值相同的记录进行汇总的方法。以下面图1“考勤应扣款计算表”为例子，用分类汇总计算每个部门的基本工资的总和以及扣款合计的总和。

操作如下：先按部门排序（和排序次序无关，可以升序排序也可以降序排序），然后单击“数据”选项卡的“分类汇总”命令，在“分类汇总”对话框中选择分类字段为部门，汇总方式为求和，汇总项为基本工资和扣款合计，将得到所需要的汇总结果。

分类汇总的特点：

1）以三级结构的形式显示出汇总结果和明细数据，既可只显示汇总数据，也可以根据需要显示明细数据。

2）不适合进行多级汇总分析。当分类字段增加或对某一个被汇总字段进行多种不同的汇总时，需要完成多级分类汇总，而分级结构的层数增加使得表的结构复杂。如在“考勤应扣款计算表”中计算不同部门、不同职位的基本工资的最大值时或计算不同部门基本工资的最大值和最小值时，需要做多级分类汇总。

3）不能直接“剥离”汇总结果。若要复制汇总结果，简单的操作方式是单击分级显示符号数字2后，选择汇总结果，在“定位条件”对话框中选择“可见单元格”，进行复制粘贴到目标区域。

4）当数据源需要更新时，只能在“分类汇总”对话框中选择“删除分类汇总”，重新完成分类汇总。

2 数据透视表

数据透视表是Excel提供的可用来快速汇总大量数据的工具，可交互式地进行数据的分析，被公认为是Excel最强大的数据分析工具。

操作如下：单击目标单元格，选择“插入”选项卡的“数据透视表”命令，在“创建数据透视表”命令中选择数据源和数据透视表的位置，在“数据透视表字段列表”窗格中设置筛选字段、行标签字段、列标签字段、数字字段以及汇总方式。

数据透视表的特点：

1）运算速度快，与函数相比，计算速度提升很快，可以认为是Excel计算速度最快的功能。

2）布局结构非常灵活，修改非常方便。可以用不同的角度对数据进行汇总，可以对多个汇总字段进行汇总，也可以对同一个被汇总字段设置不同的汇总方式，还可以增加计算字段或计算项。

3）数据透视表的数据源可以是数据列表，也可以是数据库等外部数据源，也就是说可以直接根据外部数据源创建数据透视表，这是其他汇总方式所没有的特点。

4）数据透视表不能自动更新，当需要更新时，需单击数据透视表工具中的“刷新”按钮。

数据透视表功能非常强大，是用户首选的汇总工具，但使用时也有一些缺陷。如：

1）Excel 2007开始，数据透视表创建时不能以多重合并计算数据区域为数据源。若需要使用此项功能，只能自定义功能区，自行添加“数据透视表和数据透视图向导”按钮，并单击此按钮创建数据透视表。

2）数据透视表对数据透视图的格式有一定的限制。如数据透视图的图表类型不能是XY散点图、股价图及气泡图等，当对数据透视表进行筛选或刷新时，数据透视图的有些自定义格式会受到影响。

3 合并计算

合并计算是大家不太熟悉的一个汇总工具。以图1为数据源，利用合并计算工具计算每个部门的基本工资的总和以及扣款合计项目的总和。

操作如下：先用鼠标单击目标单元格，再单击“数据”选项卡的“合并计算”按钮，在“合并计算”对话框中设置参数，如图2所示，得到的合并计算的结果如图3所示。

合并计算的特点：

1）选中“创建指向源数据的链接”复选框，结果以二级分级结构的形式显示出汇总结果和明细结果，且计算结果随着数据源的改变自动更新。若没有选中此复选框，结果将以二维表格的形式显示，不能显示明细数据，结果也不能随着数据源的改变自动更新。

2）合并计算效果有一点类似于一级分类汇总，但合并计算比分类汇总方便，不需要排序，且汇总结果和数据源是分开的。

3）适合于汇总多个独立的数据列表，可计算同一工作簿不同工作表或不同工作簿不同工作表中的数据，前提条件是这些数据所在数据列表具有相同标签，而且合并计算前工作簿文件需要打开。如汇总12个月每个部门基本工资的总和以及扣款合计的总和，而每个月的明细数据在不同的工作表内。操作时，选择目标单元格，在“合并计算”对话框中将每一张工作表的数据区域添加到引用区域中，标签位置选择“首行”和“最左列”。

操作时注意，当创建链接时，存放合并计算结果的区域不能位于数据源所在的工作表中。

4 模拟运算表（也称数据表）

模拟运算表是Excel提供的假设分析的数据分析工具，可以分析模型中参数值的改变对结果的影响。如果要考察一个参数值的改变对结果的影响，则可以使用单变量模拟运算表。如果考察两个参数值的改变对结果的影响，则可以使用双变量模拟运算表。在实践中，也可以用模拟运算表进行数据汇总。

下面以图1为数据源，用双变量模拟运算表汇总不同部门、不同职位的人数。这时部门可能的值和职位可能的值就是两个可变的参数值，公式中用数据库函数计算某一个部门某一个职位的人数。

操作如下：在数据列表区域外，创建数据库函数的条件区域。设条件区域为A18：B19，其中作为条件值的A19单元格和B19单元格的值各自输入某一部门和某一职位的值，这两个单元格在模拟运算表中是两个变量参数。选择某一目标单元格A22，输入数据库函数=DCOUNT（A2：F16， “基本工资”，A18：B19），并以A22为交叉点，在连续的行区域和列区域中输入部门和职位可能的值，在“模拟运算表”对话框的“输入引用行的单元格”中输入$B$19，“输入引用列的单元格”中输入$A$19，如图4所示。

若只需汇总每个部门的基本工资的总和以及扣款合计的总和，则可以利用单变量模拟运算表计算，公式中可以使用SUMIF函数或DSUM函数。

模拟运算表的特点：1）模拟运算表需自行设计，公式和参数的位置以及公式的内容等。结果区域为数组，不可以单独修改某一个汇总数据。2）布局上不如数据透视表灵活，但可以用来描述和分析被汇总字段的值随着汇总参考字段也就是分类字段的值变化的函数关系。当使用数据库函数进行分类统计时，不需要建立多个不同的条件区域，只需要一个条件区域即可。3）数据源改变时，结果也会自动更新。

5 结论

本文介绍的Excel数据汇总方法，各自有不同的特点，适合于不同的情况。即使是数据透视表工具也不是万能的，也有局限性。因此，在实践中用户应从实际需求出发，综合考虑运算速度、布局、数据更新、数据的图形表示等方面的因素，合理选择最适合的工具，才能达到最好的效果。

参考文献：

excel数据分析篇（5）

问卷调查是项目研究过程中使用的一种重要手段，对问卷调查结果的统计和分析是调查研究的关键，现在网上出现了许多调查问卷网站，能够进行复杂的结果分析，但有时没有网络环境，只好用纸质问卷或电子表格问卷，这样就得进行大量的人工录入及统计。我们使用的Excel软件就可以进行调查问卷的统计和分析，Excel提供了大量的统计函数，还有进行数据处理的排序、筛选、分类汇总、数据透视表等功能。下面以Excel2003为平台，以“油田员工培训需求的调查问卷”为例，介绍如何使用Excel数据透视表对调查问卷的结果进行统计分析。

1、问卷表格格式

从“油田员工培训需求的调查问卷”中提取几个问题及选项如下表进行分析，为了统计方便我们把各选项分别给予编号O、1、2、3、4，共5个选项。问卷答题结果也使用该编号，如下图，每列第一行为问题字段，下面是答题时选择的答案的前5行，共90人答卷。

在该问卷中，要统计出不同技术职务、不同年龄段的员工对培训需求的程度，愿意接受的培训频率以及接受的每次培训时间等情况。这是多个条件的交叉统计分析问题。可以利用公式进行统计，但在不同的条件下要选择不同的数据源区域来计算，操作起来麻烦，容易出错，我们现在利用数据透视表来进行统计分析。

2、利用数据透视表进行统计分析

在答题结果工作表窗口中，单击“数据”菜单，选择“数据透视表和数据透视图”命令，弹出“数据透视表和数据透视图向导-3步骤之1”对话框，在“请指定待分析数据的数据源类型：”里，选择第一项“Microsoft Office Excel数据列表或数据库”，在“所需创建的数据表类型：”里，选择“数据透视表”，单击“下一步”，在“步骤2”中选择数据透视表的数据源区域，选中全部数据后返回，按“下一步”，在“步骤3”中，让选择透视表的显示位置，在这里选择了“新建数据表”，单击“完成”，在当前工作表前新插入了一个带有透视数据表的工作表。

现在进入了我们操作的关键，根据统计该问卷的要求，要统计出按不同技术职务、不同年龄段的员工对培训需求的程度，例如：要统计不同年龄段的具有高级工职务的员工对培训需求的程度的情况，我们可以这样操作：在“数据透视表的字段列表”中拖动“技术职务”到数据透视表的“页面区域”的位置，将“年龄”拖到“行区域”，将“培训需求程度”拖到“列区域”，再将“培训需求程度”拖到“数据区域”。然后双击“求和项：培训需求程度”，弹出“数据透视表字段”对话框，在“汇总方式”中选择“计数”，“确定”之后，就出现了数据透视表，如下图。也可以进入“表格选项”对透视表的数据选项进行一些设置，是否要总计或者空白处是否显示数字“0”等。

excel数据分析篇（6）

可以对大量的数据进行查询，其查询方式为友好的多用户方式；分类进行数据的汇总，并可以在分类下设子分类来汇总数据，运用函数来设定自定义公式与计算方法；将所关注的数据分级别进行展开与折叠，对重点关注的区域可以查看其明细的摘要数据；为便于查看不同的源数据的汇总，可以讲行与列之间进行相互移动；对最重要的数据，首先要进行筛选，再将其排序与分组，同时依照一定的条件进行设置格式，以方便能够获取所关注的信息；所提供的联机或者是打印报表，能够以带批注、简明的方式呈现出来，并且具有吸引力。

2.特殊元素

Excel函数和动态图表与标准图表相比较，不但具有坐标轴、数据标记分类与系列这些元素，还具有标准图表所没有的以下几种特殊元素：

(1)以不对分类信息与系列进行修改为前提，对数据子集进行快速汇总与集中处理的捷径是报表筛选字段，其作用是按照特定的项进行筛选数据的字段。

(2)特殊元素中有一项是值字段，它是产生于基本源数据，为数据分析提供数据来进行计算或者是比较。在报表中使用的各种源数据，汇总函数是最基本的使用，此外，其计算公式也能够使用其他诸如Product与Count等。

(3)在动态图表中系列字段是所指定的系列方向字段。在此字段中，独立数据系列由项提供。

(4)在列或者是行的字段里面项所代表的唯一的一个条目，同时在系列、分类与报表筛选等各字段中式出现在下拉列表中的，项在分类字段里，在图表上是以标签的形式显示在分类轴上的。项在系列字段里是在图例中列出的，为各数据系列提供名称。

(5)源数据中在动态图表中被分配至分类方向的字段是分类字段，能够提供给在绘图中应用到的数据点单一的分类。分类在图表中通常是在水平轴或者x轴上出现。

(6)在动态图表中，能够对图表的类型以及其他的一些选项进行更改的是自定义报表，比如说数据标签、标题以及图表与图例的位置等。

二、Excel函数和动态图表在会计数据分析方面的实际应用

下面以日常的会计工作为例，介绍Excel函数和动态图表在会计数据分析方面的实际应用，在会计工作中，通常会有汇总统计数据的情况，比如说：对各种产品几个月内的总产量以及各项目总成本进行统计汇总，或者对不同种类产品各自的月均产量进行计算。若手工进行统计，在这种情况下，需要将表格内的数据按产品种类进行重新提取再汇总，费时费力不说，出现错误的几率更大，如果运用Excel函数和动态图表，便能够准确快捷的解决问题。

1.首先在工作表的菜单中进行动态图表的创建，同时进行录入或者从外部导入所需数据源；然后统计汇总字段，并选择确定行标题与列标题的字段，是选择进行统计的方式，进行函数的选择，是求和还是平均值或者最大值等等。在表1中，行标题是产品名称，列标题是产量与材料成本等。

2.在Excel表格中，对个产品的单项进行汇总计算时，对各项进行拖动至对应行或者列，在对话框中所需的条目中进行释放。根据所所需结果的不同来选择拖动次数，如进行材料成本的计算时，要连续的进行两次的拖动，首次求的是总成本，二次求的是平均成本。

3.在所有的项目均处理完后，进行新建工作表将所生成的图表进行确定位置，如果想要生成更复杂的图表，可以将行标题或列标题栏根据需求进行拖动，来对各种不同项目的计算要求进行满足。

专门的企业会计电算化软件在处理企业会计数据的过程中实现了流程化,而Excel主要用于实现专门的会计电算化软件所无法实现的功能,如对企业会计数据的动态处理、预测、分析、加工、统计汇总等。Excel已经在各企业会计核算中有不同程度的应用,但大多是基于会计人员手工操作的,而且多是重复操作, 步骤烦琐, 这样既影响了工作效率,又不能快速、准确地生成有价值的会计信息。因此熟练掌握 Excel中内嵌的 VBA技术,不但能提高日常会计工作质量,而且可以Excel VBA加载宏的形式,一次生成特定的工作模板,多次使用,提高了会计日常工作效率,为确保会计信息质量以及试算平衡提供了最基础的保障。本文利用Excel VBA自动控制数据透视表技术动态生成企业会计科目试算平衡表,以避免电算化会计凭证录入工作中的错误,并比对分析企业会计科目数据在一段时间内的变化特征,以便及时、准确、高效地为领导层提供决策依

三、总结

通过以上分析我们可以了解到，动态图表可以为用户进行数据的分析与统计汇总，可以通过它对数据进行各种角度的分析汇总，以图表的方式，简明、快捷的得出用户所需的结果，进行数据的分析与判断。它是交互式的一种表格，可以利用它快速有效的对大量数据进行汇总，同时进行交叉式列表的建立，在筛选数据是可以以不同的页面进行显示，并通过行与列的转化来对不同的源数据汇总结果进行查看。除此之外，动态图表还能够对数据做出汇总、分类汇总以及排序，它的筛选能力以及与函数进行结合将会产生强大高效的数据分析能力。

在实际的工作过程中，Excel函数与动态图表具有方便快捷、使用灵活等优点，与编程方式相比较，Excel函数与动态图表在统计计算时则更加省时、省力，编程方式需要将程序进行固定，虽然用起来比较方便，计算也能达到不错的效果，但是不能直接实现很多的计算方式，比如说在编程方式中求平均值只能求出简单的平均数，但是无法依据产量进行材料成本加权平均值的计算，但是在动态图表中，将函数功能与之联合应用，加权平均值等类似问题就能够迎刃而解。

参考文献:

excel数据分析篇（7）

1.前言

Excel作为微软公司出品的Office系列办公软件中的组件之一，近年来成为日常生活中使用频率最高的数据处理软件，它的魅力在于不仅可以制作外观简洁漂亮的表格和图片，还可以在数据的管理和分析等方面具备强大的功能。与像Access这样专业的数据库软件相比，尽管Excel看起来简单，但是如果能够根据用户的不同需求，深入挖掘它的数据管理与分析功能，充分发挥其数据处理的优势，就可以体验到Excel在实际应用中的巨大魅力。

2.几种常用功能的高级应用

2.1 “排序”功能的高级应用

在Excel实际操作中，“排序”是常用的一项功能，最常用的排序就是使用工具栏中的“排序”选项进行排序。一般地，Excel会按照当前字段内容进行升序或者降序排练，但是我们通常会忽略到如果当前字段内容是中文汉字时，排序默认为以字母为基准，也就是按照当前内容拼音的首字母在英文字母中的出现顺序进行排列。如果我们想以笔画作为排列基准时，可以按照“数据”“排序”操作命令打开“排序”对话框，然后单击对话框左下角“选项”按钮，得到排序选项对话框如图1所示，在该对话框中选取“方式”选项下的“笔画排序”。

图1 排序选项对话框

若遇到笔画相同的情况，Excel会根据内码顺序进行排序操作。在此需要强调一点，在“排序选项”对话框中选择了排序方式后，工具栏中的顺序按钮将会一直按照此方式进行排序操作，若需要回到默认时，还需要在图1所示对话框中进行设置选项。

2.2 “高级筛选”功能的高级应用

在Ecxel实际操作中，“筛选”功能是在处理较复杂数据的情况下常用的功能。尽管普通的“自动筛选”操作简单，但是当面临更具体详细的条件来筛选数据时，就需要启用“高级筛选”功能。“自动筛选”受到对一个字段最多可以设定两个条件进行设置等限制，但这些都可以在“高级筛选”功能中得到解决。“高级筛选”功能是在某一个单元格内记录筛选条件，然后根据这些条件筛选出符合条件的记录。如果初次使用“高级筛选”功能可能会感觉到表达式的书写比较麻烦，但掌握习惯之后就可以很快速地在复杂数据中进行筛选操作。

筛选条件的编写需要遵循最基本的规则：编写作为字段的对象名称，并且需要将筛选条件编写在其下方的单元格中。筛选条件的表达式可以使用运算符与字符串、万用字符等的组合。如下面1所示筛选条件的示例。

2.3 “VBA存储”功能的高级应用

在利用Excel进行数据管理与分析的应用中常常要用VBA存储功能来转存筛选出的数据。通常我们需要将筛选出来的数据复制到其他的位置，而未被筛选出来的数据将会自动排除在被复制的范围之外。一般地我们会将筛选出的范围认定为“可见数据库”来进行复制，例如：

Set myNeed=Range（“B1”）.CurrentRegion

myNeed.AutoFilter Field：=7，Criterial：=”一班”

以第7个字段为基础进行筛选操作

myNeed.SpecialCells（xlCellType Visible）.Copy

仅仅复制可见单元格

按照刚刚提到的特性，也可以将代码改为下列形式，所得结果都是相同的：

Set myRng=Range（“B1”）.CurrentRegion

myNeed.AutoFilter Field：=7，Criterial：=”一班”

myNeed.Copy

这样我们就可以只复制筛选出来的显示范围

在此需要强调，在Excel中使用“隐藏”选项而隐藏的列和因为筛选而成为隐藏的列其操作方式是不相同的。如果使用了“隐藏”选项的单元格范围，复制操作就会连同处于隐藏状态的单元格一起复制。但是如果筛选过程中出现了有隐藏的行列，并且在其中输入了函数的情况，在复制操作时就必须要选择“看得见的单元格”来进行处理。若在实际操作中没有办法根据需要处理数据时，就要留意是否由两种不同隐藏状态造成的。

2.4 “蒙特卡洛”功能的高级应用

蒙特卡洛作为模拟随机现象的一种数学方法，它的基本思路是用一个随机过程去模拟另外一个相同概率的随机过程，也就是用这一事件是否发生来判断另一事件是否发生的过程。现实生活中的随机过程，很多问题采用解析法进行求解非常困难，但采用蒙特卡洛方法进行模拟则较为简单，首先建立随机过程概率模型，该模型的参数等于问题的解，然后对该随机过程概率模型进行观察抽样试验，计算这些参数的某一统计特性，最后根据这些统计特征求出参数的近似解。

基本操作主要有以下5步：

1）依据实际问题，建立相应的随机过程概率模型，要求所求参数的解应该是所建立模型的数学期望；

2）依据问题特点和计算的需要，采取直接模拟的方法或者降低方差的方法，来提高计算效率，降低运算时间；

3）确定伪随机数的产生方法，调用随机数发生器产生满足要求的随机数；

4）使用Excel软件中的数据分析功能，根据所求参数的统计特征，计算其统计近似值及方差；

5）将数据输入到Excel软件中，采用数据分析模块来模拟计算。

比如，设某个航空导弹射击飞机的命中概率为0.78，现在对来袭飞机发射3颗导弹，请判断射击效果。针对这个问题，我们都知道每次发射一颗导弹能否命中来袭飞机是一个随机事件，并且已知命中概率为0.78，那么我们就可以模拟这个作战过程，可以通过调用随机数来确定作战的结果。在Excel实际操作中，可以直接调用“RAND”函数来模拟产生0到1之间均匀分布的随机数，若产生的随机生小于或者等于0.78，我们就认为导弹命中来袭飞机，若随机数大于0.78则认为没有命中来袭飞机。如果我们在Excel软件中进行多次重复模拟，统计意义上的平均结果肯定接近于0.78。

发射三颗导弹，可以产生3个随机数，调用“RAND”函数得出的模拟结果如表2所示。

根据模拟结果可知，第2、3发命中来袭飞机，第1发没有命中，因此这一组3发的命中概率约为0.667。在此需要说明每一次试验都是独立的和随机的，并且还要避免重复。选择模拟次数N也是一个非常重要的问题，一般情况认为模拟次数N应该足够大，只有这样才可以得出一条更加光滑的概率分布图。

3.小结

只有不断深入挖掘Excel软件的功能，探索其在数据管理和分析方面的高级应用，其实在像Access这样专业数据库软件中的常用功能都可以由Excel来实现，尤其是如果能够继续深入探究采用VBA和Excel结合起来模仿数据库应用程序。在运用Excel软件进行蒙特卡洛模拟时既可以免去繁复的公式推导过程，又可以避免复杂程序的编写，同时在产生伪随机数和计算结果分析方面都非常方面。

作为一款卓越的数据处理软件，Excel软件拥有强大的数据管理和分析工具，不仅仅表现在“排序”、“高级筛选”、“VBA存储”和“蒙特卡洛”等功能上，还有如单变量求解工具、模拟运算表和规划求解工具等。但是，大部分用户对于这些管理和分析工具了解的太少，不能熟练运用一些具体操作。如果掌握了这些方法和技巧，并在实际情况下灵活运用这些数据管理和分析工具就能够轻松解决很多复杂的问题。

参考文献：

[1]陈邓安，丛中旖，王子明.在效能分析中应用Excel数据分析模块进行蒙特卡罗模拟[J].科学技术与工程，2012（2）.

[2]刘平.Excel在办公中的高级应用初探[J].电脑知识与技术，2010（27）.

excel数据分析篇（8）

 

Excel是Microsoft公司推出的电子表格软件，它不仅具有数据的统计分析、图形和图表处理等功能，而且具有智能化的计算和数据管理功能。这些简单易行的功能正适合应用于大量处理、分析和管理数据的会计专业，使其工作更加快速、准确、安全。

一 利用Excel进行数据分析

数据分析是Excel比较重要的一个功能，而人们在利用Excel进行数据分析时，通常会使用熟悉的排序、筛选、分类汇总等命令。其实，Excel中还包含一些比较适合会计方面数据分析的命令，如记录单命令、模拟运算表命令。

（一）利用记录单进行数据分析

记录单是Excel中用来查询和添加记录的工具。利用记录单，可以轻松的设定查询条件，快速的追加记录，以及对查询到的记录进行编辑。其使用方法很简单。

1.将鼠标光标置于如表1的数据清单中，Excel会自动识别数据清单（此处要注意数据分析，设计数据表时请在标题行与数据清单间插入一行空白行，否则“记录单”会将数据清单中的字段行视为数据清单的记录区）。然后选择“数据”菜单中的“记录单”命令。在弹出的对话框中单击“条件”按钮，进入条件设定界面。

2.在如图1的“品种”文本框中输入“苹果”，然后单击“表单”按钮进入查询状态。

3.在查询状态可以通过单击“上一条”按钮与“下一条”按钮进行查询。在此状态下可对查询到的记录进行编辑，编辑后必须按Enter键确认；也可单击“删除”按钮对查询到的记录进行删除操作。如果想追加记录，可以单击“新建”按钮，即可在数据清单的末尾追加新建的记录。

表1 水果销售表

图1 条件设定对话框

（二）利用模拟运算表进行数据分析

在很多情况下，我们利用公式或函数计算数据时，常常想知道如果其中一个或两个数据发生变化，其结果将会怎样变化，我们想要对比这些情况，此时我们就可以利用“模拟运算表”来解决这个问题论文格式模板。

模拟运算表可以根据假设条件和公式计算出结果。模拟运算表分为单变量模拟运算表和双变量模拟运算表。使用单变量模拟运算表可模拟公式中一个数值发生变化对结果产生的影响；使用双变量模拟运算表可模拟公式中两个数值发生变化对结果产生的影响。

1.使用单变量模拟运算表求解

单变量模拟运算表根据变量分布在行或列中，进一步划分为行引用模拟运算表和列引用模拟运算表。下面对一张银行存款情况表（银行存款情况表是利用FV函数计算出存款人十年后可从银行提取的金额数。）分别使用行引用模拟运算表和列引用模拟运算表进行求解。

（1）行引用模拟运算表

将假设值分布在某行中，如图2所示，即应使用行引用模拟运算表来求解。

图2 行引用模拟运算表分析图

现想通过假设每年底存入的金额分别为2500、3000和3500，得出存款人十年后可从银行提取的金额数。

首先在（B9:D9）区域中输入替换“以后每年底存入的金额”的假设值。然后在假设值左下方的单元格里输入公式（此处的公式必须在假设值的左下方输入，否则结果会出错）“＝FV（B4,B5,B3,B2）”。输入完成后，选中包含假设值、公式和结果区三个部分的区域（此处选中的区域必须保证包含假设值、公式和结果区三个部分，否则不会得出正确的结果），再选择“数据菜单”中的“模拟运算表”命令。在弹出的对话框中输入引用的单元格，由于这里的假设值分布在行中，所以为单变量行引用模拟运算表，即在“输入引用行的单元格”后的文本框中输入假设值要替换的原公式中的引用“B3”（以后每年底存入的金额），然后单击“确定”按钮数据分析，回到工作表中即可看到根据假设值和公式计算出的结果。

（2）列引用模拟运算表

将假设值分布在某列中，如图3所示，即应使用行引用模拟运算表来求解。

图3 列引用模拟运算表分析图

现想通过假设总存款期分别为12年、15年和18年，得出存款人十年后可从银行提取的金额数。

首先在（A10:A12）区域中输入替换“总存款期（年）”的假设值。然后在假设值右上方的单元格里输入公式（此处的公式必须在假设值的右上方输入，否则结果会出错）“＝FV（B4,B5,B3,B2）”。输入完成后，选中包含假设值、公式和结果区三个部分的区域（此处选中的区域必须保证包含假设值、公式和结果区三个部分，否则不会得出正确的结果），再选择“数据菜单”中的“模拟运算表”命令。在弹出的对话框中输入引用的单元格，由于这里的假设值分布在列中，所以为单变量列引用模拟运算表，即在“输入引用列的单元格”后的文本框中输入假设值要替换的原公式中的引用“B5”（总存款期（年）），然后单击“确定”按钮，回到工作表中即可看到根据假设值和公式计算出的结果。

2.使用双变量模拟运算表求解

双变量模拟运算表可以同时根据公式中两个变量的假设值得出计算结果。在使用双变量模拟运算表时，必须保证其两个假设值分布于行和列中，且公式位于它们的交汇处，如图4所示。

图4 双变量模拟运算表分析图

现想同时通过假设每年底存入的金额分别为2500、3000和3500及总存款期分别为12年、15年和18年，得出存款人十年后可从银行提取的金额数。

首先分别在（A10:A12）区域和（B9:D9）中输入替换“总存款期（年）”和“以后每年底存入的金额”的假设值。然后在行和列的假设值的交汇处单元格里输入公式“＝FV（B4,B5,B3,B2）”。输入完成后，选中包含假设值、公式和结果区三个部分的区域（此处选中的区域必须保证包含假设值、公式和结果区三个部分，否则不会得出正确的结果），再选择“数据菜单”中的“模拟运算表”命令。在弹出的对话框中输入引用的单元格，由于这里的假设值分别分布在行和列中，分别在“输入引用行的单元格”和“输入引用列的单元格”后的文本框中输入假设值要替换的原公式中的引用“B3” （以后每年底存入的金额）和“B5”（总存款期（年）），然后单击“确定”按钮，回到工作表中即可看到根据假设值和公式计算出的结果。

二 利用Excel函数进行数据计算

Excel函数共有11类数据分析，下面介绍财务函数中几个常用的函数。

1.年金函数PMT

在很多情况下我们需要贷款解决资金问题，同时我们也想知道每期的还款金额，这就可以利用Excel中的PMT函数计算得出。

PMT函数是计算在固定利率下，贷款的等额分期偿还额，也就是平时所说的“分期付款”。在表2（贷款情况表）中计算每年的还款额只要将光标定位到B5单元格中，输入或插入函数：= PMT(B2,B3,B4)，此处要注意的是这里我们只对PMT函数的前三个参数（固定利率、总贷款期、本金）进行了赋值，对于后两个参数fv、type没有赋值（视为省略），是由于fv参数为未来值，一笔贷款的未来值即为零，所以这里不为fv参数赋值。而type参数的值若为0或省略代表各期付款的时间在期末，为1则为在期初，这里如没特别说明，我们则认为其付款时间在期末，即可省略论文格式模板。

表2 贷款情况表

2.投资净现值函数NPV

对于财务人员和项目人员经常要了解投资中未来各期支出和收入的当前值的总和，那就可以利用Excel中的NPV函数计算得出。

NPV函数是基于一系列将来的收（正值）支（负值）现金流和一贴现率，返回一项投资的净现值。现值是指未来各期收益折算成现在的价值总和。在表3（投资收益表）中要计算投资的净收益只要在将光标定位在B8单元格中，输入或设置函数NPV。但此处需要注意的是NPV函数根据初期投资的时间是在期初还是在期末分为两种情况。

情况一：初期投资的时间在期初，即在第一年的年初投资50000（投资均用负数表示），第一年的年末收益12000，其余年份的收益也是在年末，则在B8单元格中输入：=NPV(B2, B4:B7)+B3，注意这里要在最后除去初期的投资额。

情况二：初期投资的时间在期末，即初期投资发生在第一年的年末。在B8单元格中输入：=NPV(B2,B3:B7)即可。

表3 投资收益表

三 利用Excel进行数据保护

我们利用Excel进行数据处理后数据分析，可能会对工作表进行保护，但这样只能保证其他人不能修改数据，而所使用的公式或函数还是可以暴露给别人，如果不想其他人查看所用公式的内容，就要使用Excel中的公式隐藏功能实现。

excel数据分析篇（9）

中图分类号：U671 文献标识码：A 文章编号：1672-3791（2016）10（b）-0004-03

Excel作槲⑷Office软件产品中的重要组成部分，性能优越，支持较强的数据分析、图表绘制、宏命令、VBA编程[3]及决策支持分析功能。特别是其可单纯依据工作表数据进行组织管理进而绘制出高质量的平面图形和立体图形，实现图、文、表混排。在对船舶进行倾斜实验的过程中，需要多次移动压铁读到多组摆锤移动距离的数值，技术人员因此往往要处理大批量的数据文件，多数情况下会将数据导入Excel中进行简单计算后自行绘制曲线，剔除不可用的数据点，导致工作量巨大，而且难免出现精度不满足工程要求的问题。该文创造性的通过Excel的回归分析[1]、数值拟合等功能直接剔除不可用点，得到关于合理有效的读数的拟合曲线，且可随工程精度要求随时更改程序结果。

1 倾斜试验中的数据来源

船舶倾斜试验的目的是确定空船的重量及重心位置[2]。试验重量一般应布置成移动力矩相近的六组，但实际中限于条件等原因，通常只采用四组，压铁重量移动按图1所示顺序进行。

其中，为试验移动重量位置；

试验中利用摆锤测量法，一般设置为3个，分别为船首中尾3个位置。每个摆锤每次移动都需读取左右两个数据，数据处理量大又繁琐。为保证测量结果的正确性，还需要进行误差检查和限值设定。也就是说，要将每次移动重量后的读数求取平均值标注到图中。当偏离拟合直线超过4%（此数据为该船厂要求）需分析原因并判定是否需要部分或全部重做试验。

该文正是基于Excel的回归分析功能将该试验中大量的数据处理问题简化，通过基本读数的录入，直接得到拟合直线以及试验中需要剔除的超过误差限值的数值，可极大地减轻工作人员的数据处理工作量，实现倾斜试验数据分析的“傻瓜”处理。

2 实船数据处理与运行结果

以某实船的倾斜实验其中一个摆锤为例，每次移动重量后会得到如图2所示的数据。

其中，C（1、2、18、19）、I1、C（5-14）-K（5-14）为参与实验的现场人员所需填写的数据，均直接从试验中读取，其余数据程序自行运算。

在经过Excel程序分析后，会自行得到经过线性回归分析拟合出的直线参数与直观图形表达以及超过误差限值的坏的数据点，即需要剔除的数据见图3。

其中，L列为数据处理的直观结论，其余部分仍为程序自行运算的过程数据。

计算过程中其极限偏差数据见图4，误差限值取为±4%，可剔除的点非常明显，对应图3。

3 结语

通过利用Excel分析的图表工具及回归分析工具，可以进行船舶倾斜试验的数据分析，得到数值拟合直线，直观剔除不可靠数据点，精度高，能够满足工程需求。

（1）若实验的压铁组数量以及摆锤数量增加，会导致待处理的实验数据成倍增加，而程序运行仍然只需要几秒的时间即可完成，相对于人工处理方式，极大提高了工作效率。

（2）该方法实现了计算机的自动处理功能，减少了人为因素可能引起的计算错误，提高了数据分析的精准性。

（3）在倾斜实验过程中，数据量越大越能体现出该处理方式的优越性。可推广至多数船厂及船检人员中使用。

参考文献

excel数据分析篇（10）

问题:

1.每名学生总分班名次和年级名次;

2.各班各学科平均分;

3.各科优秀线(总人数的20)及各班优秀人数。

解答:

Step1:选中单元格O2,输入公式“=SUMPRODUCT(($D$2:$D$22=D2)*($N$2:$N$22>N2))+1”,回车,得结果3,向下填充到O22,学生总分班级名次已完成。

Step2:选中单元格P2,录入“=RANK(N2,N$2:N$22)”(不含引号,下同),回车,得结果3。用填充句柄把单元格P2向下复制到P22,学生总分年级名次已完成。结果如图(2):

说明:如果希望在数据表班无序的状态下进行按班级排名,请使用SUMPRODUCT公式:“=SUMPRODUCT(($D$2:$D$22=D2)*($N$2:$N$22>N2))+1”

RANK函数返回某数字在一列数字中相对于其他数值的大小排位。它的函数表达式是:RANK(number,ref,order)。

Step3:选中单元格E25,输入公式“=AVERAGE(IF(($D$2:$D$22=$D25)*(E$2:E$22""),E$2:E$22))”,按Ctrl+Shift+Enter结束,向右填充至N25,接着向下填充至E27:O27,结果如图(3):

说明:数组公式{=AVERAGE(IF(($D$2:$D$22=$D25)*(E$2:E$22""),E$2:E$22))},表示计算D2:D22等于D25且E2:E22不为空的数值的平均数。

Step4:选中单元格E30,录入“=PERCENTILE(E2:E22,0.8)”,回车,得结果106,填充至N30,得到各科优秀线,见图(4):

说明:PERCENTILE函数返回数组的K百分比数值点,可以使用此函数来建立接受阈值。它的函数表达式是:PERCENTILE(Array,K)。

Step5:选中单元格E33,输入“=SUM(($D$2:$D$22=$D33)*(E$2:E$22>=E$30))”按Ctrl+Shift+Enter结束,得结果3。填充至N30,再将区域E30:N30向下填充至区域E30:N35,见图(5):