excel数据分析汇总十篇

序论：好文章的创作是一个不断探索和完善的过程，我们为您推荐十篇excel数据分析范例，希望它们能助您一臂之力，提升您的阅读品质，带来更深刻的阅读感受。

篇（1）

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

篇（2）

以“格式”选项卡为例，这里提供了数据条、色阶、图标集、大于、文本包含、清除格式等功能按钮，鼠标移到“图标集”上，我们会看到不同的图标风格（如图1），预览满意之后即可应用。“图表”选项卡则可以预览不同风格的图表效果，从而帮助你快速创建相应的图表，例如使用散点图（如图2）。如果需要查看更多的图表，可以点击最右侧的“更多图表”按钮。最常用的操作当然是“汇总”下的求和、平均值、计数、汇总百分比、汇总，相信这也是绝大多数朋友接触最多的操作。在选择相应的功能之后，Excel 2013会在相应的位置上添加一行列出所得出的结果，比如平均值（如图3），点击“>”按钮还可以选择更多的汇总功能。“表”选项卡提供了表、数据透视表、其他等功能选项，可以快速创建非常美观的图表。“迷你图”的效果就不用多介绍了吧？

简而言之，借助Excel 2013的快速分析工具，我们可以在最短时间内快速实现相应的操作，而且是提供充分预览，从而提高操作效率。

午后的三点一刻是下午茶的时间，忙碌了一天，现在放下手中的工作，稍稍休息一会儿吧。

首先，我们来听一首歌，一首很适合在午后时分听的歌：《Down at the coffee shop》，这是一首甜腻腻的歌，就好像草莓味的水果糖。尾音和间隙处衔接非常紧密，她粘粘的唱，那大家就粘粘的听吧。再来一杯有着玫瑰香的泡泡水，清淡、回味。就让一个小清新的午后由此开始吧……

篇（3）

3、方法三：分组对比法。分组之后，我们就可以对数据进行汇总计算了。常见的方法是通过求和、平均值、百分比、技术等方式，把相同类别的数据，汇总成一个数据，减少数据量。

篇（4）

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

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

参考文献:

篇（5）

关键词： 药动学； 数据分析； Excel； PKSolver 1.0

目前在药动学数据处理方面有很多专业软件可供选择，如使用最为广泛的WinNonlin和3P87/97程序等，使用这些功能强大的专业软件，即使是常规的药动学数据处理，也要进行复杂的操作，而大部分新药研究部门的科研人员往往畏惧于学习专业软件的操作和设置。Excel是使用最为广泛的统计软件，具有界面明晰、操作方便的优点，本研究在充分挖掘Excel的VBA编程语言的基础上，完全以Excel为平台，编制了药动学数据分析程序――PKSolver 1.0。同时，参照国内外权威药动学数据处理程序的结果，对本程序的药动学数据计算能力进行了评价，程序操作极其简单。程序的编制扩展了Excel在药动学数据处理中的应用，对于扩大药动学基础理论的实际应用，提高基层药动学研究水平具有一定的参考意义。PKSolver 1.0程序目前共包括10个模块，分别为静注1室、静注2室、静注3室、非脉管1室、非脉管1室（有时滞）、非脉管2室、非脉管2室（有时滞）、非房室模型分析、生物等效性分析（双交叉）以及缓控释制剂体内外相关分析。限于篇幅，本研究以静注2房室模块为例，简要介绍本程序的设计思想及编写过程。

1 材料

11 软件 Microsoft Excel 2002(Microsoft Corporation)； 3P87(The Chinese Society of Mathematical Pharmacology)； WinNonlin 4.1(Pharsight Corporation).

12 数据选自文献［1］中某药物静脉推注给药后在不同时间点测得的血药浓度时间数据。

2 方法

21 程序的编制

211 操作运行界面的设计 本程序运行界面主要包括4个部分，分别为数据输入区、结果输出区、操作设置区以及图表输出区。以静注2房室模块为例，程序运行界面如图1所示。其中数据输入区、结果输出区以及图表输出区的设置较简单，分别利用Excel程序的内置函数和图表向导即可完成，不再赘述；操作设置区是控制程序的主要部位，通过在Excel中添加按钮、按钮选项、组合框、文本框等控件，利用这些控件可以方便的进行药动学参数的选择和设置，并调用后台程序进行数据处理和结果输出。

212 前台数据的计算 利用Excel的内置函数和单元格引用，可以在操作界面上直接计算出很多中间参数，如“ln(实测浓度)”、“预测浓度”、“ln(预测浓度)”、“残差”以及“权重”等，程序可以在后台直接调用这些中间参数，运用VBA程序进行模型的拟合和优化，大大提高程序的运行效率，这种“前后结合”的数据处理方式可部分提高程序的运行效率。

213 优化过程的VBA程序编写 利用VBA语言编写大部分优化计算过程是本程序的一个特点，尽管各个模块的数据处理过程各不相同，但总的来说，每个模块的后台数据处理都大概分为以下几部分内容：①输入数据的基本合法性验证。主要包括有输入时间点的顺序是否合法、药物浓度时间点的个数是否足够用于相应房室模型分析、药物浓度时间数据是否配对、是否出现不符合实际情况的数值(如负值或者非数字型字符)等等；②待拟合参数的初始值计算及初始值的合理性验证。参数拟合结果除与优化方法有关，更重要的是拟合参数初始值的选取，选取恰当的初始值不仅仅能够加快拟合速度，还能够避免拟合过程中的溢出现象。为获得相对准确的初始值，本程序的编制过程中进行了大量的尝试和改进，除运用最为经典的残数法估算初始值外，在非脉管给药房室模型分析模块中还采用了一些经验设置，能够较快地获得非脉管给药模型参数的初始值。同时，本程序还对计算得到的初试值进行合理性验证，并根据验证结果进行参数拟合或者针对数据特征向使用者提出建议，进一步提高模型拟合的成功率；③参数拟合优化。该部分是程序参数求解的核心部分，以计算的初始值为基础，Excel自带的SOLVER加载宏为主要工具，基于GaussNewton迭代的寻优法为核心算法，进行参数的优化求解，为避免出现拟合结果数学上最优，但实际药动学上不合理的情况(如负值参数的出现)。在拟合过程中，对所有参数的结果以及参数间的相应关系进行了相应的规范和限制，从而保证拟合出符合实际情况且数学上最优的结果，最后对优化结果进行合理性验证；④二级参数及模型判据的求解。根据输入的药动学参数、设置的参数单位以及拟合得到的一级参数，求算出15个二级参数，其单位设置和计算公式参见文献［2］，9个模型判据按文献［3］公式计算，分别为r、SS、WSS、R2、WR2、Rsq、S、AIC、SC，所有二级参数和模型判据均在程序后台调用Excel内置函数计算。

图1 PKsolver 1.0药动学数据分析程序运行界面　略

214 输出报告的程序编制 好的程序应具有友好的操作界面和简单的使用方法，利用本程序求得所有参数后，只要在运行界面上点击“报告输出”按钮，即可通过后台启动Microsoft Word应用程序，新建Word文件，并自动生成完整的数据分析报告，报告生成速度快且操作简便。

215 其他模块的编制 本程序中7个药动学房室模型分析模块的编制思路基本一致，非房室模型分析、生物等效性分析以及缓释制剂体内外相关性分析模块则根据相应内容进行编制，限于篇幅不再详细介绍。

22 程序的验证由于程序的模块较多，仅选取其中的代表模块，列出其计算结果进行评价，选取文献［1］中某药物静脉推注496mg给药后在不同时间点测得的血药浓度时间数据，分别用3P87、WinNonlin和本程序采用2室模型以1、1/C、1/C^2为权重进行模型拟合，所得结果用于评价本程序计算的二级参数。另外，用三种软件对此数据分别进行静注1室、2室、3室模型3种权重进行模型拟合，计算AIC值，用于评价本程序计算所得房室模型判据。用PKSolver程序进行模型拟合和参数求解时，仅需输入“时间血药浓度”原始数据，在操作设置区中设置好相应参数，点击“参数求解”按钮即可完成所有参数的自动求算，再点击“报告输出”按钮即可以生成完整的报告文件。

3 结果

对文献［1］数据分别采用3P87、WinNonlin和本程序进行房室模型分析的所有结果见表1和表2，三种程序对数据的拟合结果显示该程序在房室模型参数求算和模型判别上均和权威药动学软件非常接近，所求参数基本一致，应用本程序进行简单的药动学数据分析具有一定的可靠性，分析结果基本准确。利用VBA语言编程能够省去大量的繁琐操作，用计算机代替手工操作，将VBA语言编程应用到药学数据处理方面的报道甚少［4］，本研究尝试将VBA语言编程应用于药动学数据处理，取得了预期的效果。

表1 3P87、 WinNonlin、 PKSolver对文献［1］数据按静注二室模型拟合结果的全面比较　略

表2 3P87、 WinNonlin、 PKSolver对文献［1］数据按不同模型拟合AIC判据的全面比较　略

4 讨论

41 程序的核心原理和优点利用本程序进行药动学数据分析的核心是首先通过前台界面输入数据并进行自动初步计算，然后在后台对数据进行一系列合理性验证、参数优化求解、计算二级参数、输出最终结果并自动生成报告文件，所有操作仅需点击两个按钮即可完成，操作非常简便。本程序完全基于Excel程序内置的VBA语言编制，利用Excel强大的数据处理能力和自动作图功能，将Excel应用于药动学数据分析，取得了较好的结果。如表3所示，与国内外常用的药动学数据处理程序相比，本程序具有界面友好、体积小巧、操作简单、结果输出直观方便等特点。特别是本程序充分挖掘了Excel编制宏程序的自动操作潜力，代替手工操作分析数据，将一系列药动学数据处理步骤全部集中在一个按钮上由计算机一步完成，不仅极大的提高了使用方便性，还能避免由于手工处理数据可能带来的错误，这在生物等效性分析和缓释制剂体内外相关性分析模块中表现尤为突出，这也是本程序优于一般药动学处理程序之处。

表3 国内外主要药动学数据处理软件部分功能特点的比较　略

42 程序的改进本程序在模型参数的优化时仅能使用GaussNewton迭代寻优法和共轭法两种方法，运用直观简洁的Simplex法或者Marquardt法［5］进行参数优化的VBA程序正在开发之中。另外，PKSolver 1.0目前的版本仅包括最常用的10个模块，更多的药动学数据分析模块正在开发之中，相信通过进一步优化算法、增加模块有望使本程序的数据分析能力不断完善，从而为药学工作者提供一个操作简单的智能化药动学数据处理程序。致谢： 感谢美国Pharsight Corporation提供药动学软件WinNonlin 4.1试用版!本论文在软件试用期内完成，我们正考虑通过申请PAL (Pharsight Academic License)计划获得软件注册。

参 考 文 献

1 Gabrielsson J， Weiner D. Pharmacokinetic and pharmacodynamic data analysis:concepts and applications. Stockholm， Sweden: Swedish Pharmaceutical Press， 1994，129.

2 Gibaldi M， Perrier D. Pharmacokinetics， 2nd edn. New York: Marcel Dekker， 1982，2~108.

篇（6）

用友、金蝶等财务软件以及企事业其他信息系统的应用和普及,大大提高了中小企事业单位会计信息化程度,减轻了会计人员的负担,节约了财务成本。但实务工作者也发现,财务软件更偏重于会计账务的处理及标准财务报表的提供,而分析功能不足,未提供良好的分析工具,分析方法也不够灵活,其他信息系统更偏重于业务处理。这时就需要借助EXCEL等外部工具进行个性化的财务分析。

一、财务分析数据库的构建

用EXCEL建立财务分析数据库时,可以充分发挥其灵活性,从各个维度、各个层次对企事业单位数据进行分析。尽管EXCEL2007已经解除了以前版本只能处理65 536行数据的限制,但是也要考虑到数据库如果过于庞大,运行速度会降低。因此,建立数据库时如果数据库偏大,可以将数据库拆分成几个小的数据库。

明确了数据库的构建目标后,就可以从不同地方收集财务分析数据。这些数据既可以从用友、金蝶等财务软件获取,也可以从企事业单位的其他信息系统导入,如医院的HIS系统、药品管理系统、材料进销存系统等等。导入的数据既可以是用友等财务软件整个科目数据,也可以是固定资产数据等等。

财务软件和其他信息系统一般都有导出成EXCEL电子表格的功能,如果这些信息系统软件可以导出成分隔符区分的文本文件,也可以通过EXCEL中的分栏工具进行拆分。

(一)基础数据字段整理

在进行基础数据整理时,经常会用到这些函数:

1.时期处理函数。如根据日期提取年、月、周的数据:YEAR(),MONTH(),WEEK()。

2.字符处理函数。如提取字符串中的某一内容:LEFT(),MID(),RIGHT()。字符串联结:CONCATENATE()。

3.数据格式转换函数。如将数字强制转换成文本:TEXT()。

4.逻辑判断函数。如条件判断:IF()。信息提取:ISNA(),ISTEXT()。

(二)数据的归类

财务分析时经常会对数据进行分类,这时可以借助函数VLOOKUP()来实现。如对食品进行分类时,可以首先建立一张字典表,如表1。

然后在数据库里面在目标单元格输入函数vlookup()并设置函数参数,再对整个字段进行填充,就可以根据字段食品名称获取其对应分类了。

(三)数据库的定义

数据收集整理完后,可以在EXCEL里形成一张表单(sheet),可将有数据的区域定义为一张表,并为之命名。本文以医院收入分析数据库为例,基础数据通过医院用友、HIS系统收集整理后,存放在一张名为“医疗收入”的表单内。这时先选择所有数据区域,点击右键,选择“命名单元格区域”,命名为如“数据表”。

定义数据区域可以更方便地对数据库进行维护。建立的数据库如图1。

二、财务数据的分析

数据库建立好之后,就可以充分利用EXCEL的数据分析挖掘工具,灵活方便地对数据进行分析。下面介绍几种常用的分析方法。

(一)对比分析

建立的数据库如果包含了一段时期的数据并进行归类后,可方便地实现对比分析。这里面需要用到EXCEL的数据透视表工具,首先将光标定位到“数据表”,单击“插入数据透视表”,并根据需要选择报表筛选字段、行标签、列标签字段以及值字段。在作对比分析时,应该在列标签选择时间字段,并选择需要进行对比的时间段。

透视表建立后,可以通过报表等字段筛选数据的范围,例如既可以看全院收入,也可关注某一个或几个科室的收入分析。同时,在邻近单元格建立公式对数据进行比较分析。建立的数据对比分析如图2。

(二)趋势分析

趋势分析也是财务分析中的一个常用方法,分析人员可以从数据变化趋势看出数据的长期变化趋势及季节趋势等。

用EXCEL建立趋势分析时,也要建立数据透视表并选择要分析的字段。作趋势分析时,可以辅以图片更能直观地进行分析。将光标定位在数据透视表内,单击“插入折线图”或其他图形模板,并调整图形格式就可以制作出专业的图表。如图3。

(三)结构分析

结构分析可以看出数据之间的构成分析。在EXCEL里面作数据的结构分析时也可以通过建立数据透视表,选择要分析的数据字段进行分析。同时,还可以制作数据饼图等进行直观的观察分析。数据的结构分析如图4。

三、财务分析数据库的维护与更新

财务分析数据库建立完成后,可以根据个性需求灵活地对数据进行抽取和分析。同时,这个数据库也是可以动态更新的。更新数据库时,只需在数据表末端追加数据记录,数据库就会智能地将数据加入。当然,这个数据库也支持对以前数据记录进行修改,批量修改数据时可以使用EXCEL的批量替换功能;具备支持增加和删减字段的功能。

修改数据库后,应确保增加的记录在数据表内可以通过扩大数据表区域来实现。同步更新数据透视表和数据图时,只需要在任意表内,单击“更新数据源”,则所有基于数据库的透视表和数据图都可以同步进行更新,操作非常方便。

从上述研究可以看出,EXCEL具备灵活、简便而功能强大的数据分析和挖掘功能,而基于EXCEL数据库的维护和更新也非常简单,财务人员通过简单的操作,就可以从各个维度、范围对财务数据进行深入的分析。当然,EXCEL还具备更多的数据分析潜能,需要财务人员在工作中发挥主动性和创造力,从而提高财务分析的效率和作用。

篇（7）

中图分类号：TP39 文献标志码：A 文章编号：1674-9324（2012）03-0176-02

在实际问题中，我们经常遇到各种数据需要处理，往往要涉及许多繁琐的计算与各种统计图表的绘制。若不熟悉数据处理的方法和专业统计软件的使用，单凭手工操作进行，不仅工作量大，效率低下，而且容易出错。我们这里使用常用的办公软件――Excel软件的数据分析功能，快速分析统计学生成绩，有利于提高我们的教学水平。

一、使用“数据分析”功能的基本方法

Excel提供了“数据分析”工具包，包含常用的方差分析、描述统计、直方图、回归分析、t-检验等分析工具。若要使用这些工具，应先单击“工具”菜单中的“数据分析”。首次调用，需先加载宏“分析工具库”。步骤如下：

1.在“工具”菜单上，单击“加载宏”。

2.在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。出现“数据分析”菜单。

3.选择“工具”菜单中的“数据分析”，出现“数据分析”对话框，单击要使用的分析工具的名称，再单击“确定”。

二、分析学生成绩绘制频率直方图的基本步骤

我们以2010年级某专业同学某门课程期末成绩为例，我们来讨论绘制频率直方图的主要步骤：

（一）输入数据，进行区域排序

为方便起见，将82个数据以矩阵形式输入到学生成绩统计表中，存入Excel的工作表中的适当区域。

学生成绩原始数据：

将学生成绩数据存放后，利用SMALL函数进行排序，排序公式SMALL（数据存放区域，ROW（Amn）*k+column（Amn）-mk-（n-1）），Amn为存放第一个数据所在位置，k为排序后每一行存放数据个数。任选新的区域存放排序后数据，选横拉k个数，再下拉，直到数据排序完毕。得到如下成绩表：当然也可以把学生成绩输入为一列，再利用Excel排序功能进行排序。

（二）点击工具数据分析描述统计，选定原始数据列存放形式，任选结果输出区域，得到统计描述结果

从中看出，82个学生平均成绩为73.89分，方差为307.18.

（三）计算极差R

最大的数据减去最小的数据

R=xmax-xmin=99-18=81

（四）分组

1.根据要求，不及格为0~59分，及格60~75，良好76~89分，优秀90~100分。

2.确定分点。

左端点，右端点为b，所表示区间范围：

（五）生成频数分布表和直方图

1.打开“工具/数据分析”，在分析工具窗口中选择“直方图”；点击确定。

2.在直方图弹出窗口“输入区域”输入数据方阵“82位学生成绩数据位置”：

在“接收区域”输入分组右端点所在列区域：

3.在输出选项中，点击“输出区域”，表示频率数表输出存放区域，位置任选。

4.在输出选项中，点击“图表输出”。其他选项，若没需要，可以不选。

点击“确定”按钮，立即显示如下所示的频率分布表（表1）和直方图（图1）。

图1

注：1.此图表中的“频率”实乃频数，所以应将表中“频率”改为“频数”，接收区改为相应分组文字，得表2

2.直方图是按照左开右闭的方式对落在各区间的数据进行频数统计绘制的；

3.从直方图来看，学生成绩基本符合正态分布。

（六）修改频率分布表

另存表（2）为表（3），将表（2）“频率”改为“频数”，并在频数列右侧增加一列频率，计算频率。即频率=■，本例中，只需计算第一组的频率，下拉即可得这列频率数据值。修改后频率分布表见表3

（七）完成频率分布直方图

将表（2）频数列替换成表（3）频率列，点击右键数据标志格式，得到频率分布直方图（见图2）。

此种绘制方法可以用在工程上，可以作进一步修改，使之符合我们使用要求。

篇（8）

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2014 . 16. 083

[中图分类号] G64 [文献标识码] A [文章编号] 1673 - 0194（2014）16- 0140- 06

教学管理中，经常要对大量的数据进行分析、汇总，由此也产生了大量的管理软件，但是，软件再多，毕竟无法解决层出不穷的新问题。因此，管理人员掌握常用的数据库软件的使用，可以及时、较好地解决实际工作中的各种问题，提升工作效率。在众多的数据库软件中，Excel作为普及度最高的软件，拥有着强大的数据管理分析功能，而且随着软件的升级，加入了更多、更强大的功能，掌握好Excel的使用、充分运用其功能，将成为解决实际问题的“利刃”。本文以多次考试成绩的汇总、分析为例，提出应用Excel解决类似问题的思路，并详细讲解实际操作方法。还要指出，本问题的解决，还可应用于公司销售业绩、进出货物管理等多种情形，因此具有相当的普适性。

1 提出问题

大量来自不同地区的人员参加考试，每次组织综合科目和不同的专业科目考试，同时通过综合科目和某一专业科目才能取得该专业执业证书，没通过相关科目的人员可能再参加后面批次的考试，要求汇总分析每一次考试和几次考试的人员通过情况和地区通过情况，整理出几次考试后可取得执业注册证书的人员名单，分析每一考试科目的总体通过情况。

2 思路分析

解决该问题的难点在于整理执业证书人员名单时，必须通过综合和专业考试科目，而这两个科目不一定是在一次考试通过，而且考试的专业比较多。

在分析问题时，也应当看到，结果并不要求对每次考试的具体成绩进行层次分析，这样即可简化我们解题思路，即将通过、不通过定义为1、0即可，而不需要分析具体的考试分数（当然也可通过其他办法来进行考试成绩的定量分析）。

基于以上两个主要方面的分析，我们可以得出，利用Excel的数据透视表来解决这一问题是一个比较适当的方法。因为数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集等数据源的特定字段中总比信息的分析工具。它是一种交互性报表，可以快速分类汇总比较大量的数据，并可以随时选择其中页、行和列中的不同元素，以达到快速查看数据源的不同统计结果，同时还可以随意显示和打印出用户感兴趣的区域和明细数据。数据透视表有机地综合了数据排序、筛选和分类汇总等数据分析方法的优点，可以方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。

3 实际操作

在本节中，以分析解决问题为主线，穿插了Excel数据处理中的常用方法。考虑到Excel 2010在数据透视表方面显著的功能升级和强化，本文以Excel 2010版本为平台进行操作。

3.1 整理数据源

篇（9）

在食品研究中，通径分析有利于在一个变量系统中更为深入清晰的分析变量间的相互关系，从而抓住关键因素，改进生产工艺，降低生产成本、提高产品质量及提高生产效率等提供信息依据。本文以常见的Excel软件对通径分析方法进行讲解，分析数据来自文献，具体为 在某品牌桃肉果汁加工过程中非酶褐变原因的研究中，测定了该饮料中的无色花青苷（x1）、花青苷（x2）、美拉德反应（x3）、抗坏血酸含量（x4）和非酶褐变色度值（y），结果见表1。

表1 桃肉果汁加工过程中非酶褐变原因研究测定值

一、数据录入

打开Excel工作表，建立表头，在单元格A1：F1内按顺序分别输入“测定序号”、“无色花青苷（x1）”、“花青苷（x2）”、“美拉德反应（x3）”、“抗坏血酸含量（x4）” 和“非酶褐变色度值（y）”，然后在A2：A17区域按顺序输入个体编号，在B2：F17输入各个变量的原始数据。

二、相关系数的计算

首先计算变量之间的简单相关系数。单击菜单栏的“工具”“数据分析”“相关系数”，弹出相关系数对话框，指定输入区域B2：F17，分组方式选逐列，输出区域开始的单元格指定为A20，单击“确定”，计算出变量之间简单相关系数，结果见表2。

表2 变量之间的简单相关系数

三、因变量对自变量的多元回归分析

建立X1、X2、X3、X4对Y的多元回归方程的方法是：单击“工具”“数据分析”“回归”，弹出回归分析对话框。指定Y值输入区域为F2：F17，X值输入区域为B2：E17，Y值和X值的输入区域必须由按列输入的数据组成。输出区域开始的单元格指定为A30，单击“确定”，如表3。

表3 回归统计输出结果

表4 方差分析输出结果

表5 多元回归分析的输出结果

由表4中的方差分析结果可知，F=26.3690（P=0.0001），有统计学意义，说明建立Y关于X1、X2、X3、X4的多元回归方程是有意义的，进行通径分析也是有意义的。多元回归方程的各个偏回归系数分别为b1= - 69.6264 有统计学意义；b2 = 189.5118 有统计学意义；

b3= -53.6917 无统计学意义；b4= 1.3729 有统计学意义。

四、通径系数的计算

通径系数的计算公式为， bi为偏回归系数，Sxi为Xi的标准差、Sy为Y的标准差。计算变量标准差的方法：在单元格B57内输入公式“=STDEV（B2：B17）”，计算出无色花青苷X1的标准差，然后将B57的公式横向复制到C57 ～ F57，Excel 表格将自动计算出X2、X3、X4和Y的标准差，并计算出其通径系数（见表6）。

表6 各变量的标准差及通径系数

对通径系数的显著性测验与多元回归分析中对偏回归系数的显著性测验是等价的，故P1y = -0.5073 有统计学意义；P2y = 0.7618 有统计学意义； P3y =-0.1826 无统计学意义；P4y =0.3697 有统计学意义。回归方程的误差e对y也产生作用，其大小以剩余通径系数Pye表示。Pye的计算公式为：

5. 间接通径系数的计算

图1 通径图

通径系数是自变数对因变数直接作用的度量，通径系数又叫直接通径系数。如果一个自变数与其他自变数之间存在相关关系，则该自变数还可以通过其他自变数对因变数发生间接作用，其一自变量通过另一自变量间接作用于因变量的间接通径系数等于二者相关系数乘以另一自变量的直接通径系数。制作通径图并进行原因对结果直接作用与间接作用分析，结果见图1。将无色花青苷X1、花青苷X2、美拉德反应X3和抗坏血酸含量X4与非酶褐变色度值Y的相关系数剖分为直接作用与间接作用的代数和，结果见表7。

表7 相关系数的分解

通径分析的理论已证明，任一自变数与因变数之间的简单相关系数，可以分解为该自变数与因变数之间的直接通径系数加上所有其他间接通径系数加上所有其他间接通径系数的代数和。花青苷X2对非酶褐变色度的相对决定程度最大， 而美拉德反应对非酶褐变色度的影响不大，可以不必过多考虑。

小结

1. 由表6可知，3个原因变量对非酶褐变色度值的作用由大到小依次为花青苷>无色花青苷>抗坏血酸含量。花苷青与非酶褐变色度的相关系数和通径系数都有统计学意义，说明花苷青对非酶褐变色度的影响主要来自于自身的直接作用。

2. 本例中的剩余通径系数Pye=0.7472，其值较大，说明还有一些对对非酶褐变色度影响较大的因素没有被考虑。本文只是以这个例子说明在Excel内进行通径分析的简便方法，对非酶褐变色度影响因素的全面分析有待进一步深入。

3. 目前，对影响因素的分析人们常采用的方法是多元回归分析，但多元回归分析只考察变量之间的直接作用，而实际上变量之间的关联关系往往是一个复杂的传递过程。通径分析将简单相关系数分解为直接通径系数和间接通径系数，使人们能够透过相关的表面现象深入研究原因变量与结果变量之间的因果关系，从而为统计决策提供可靠的依据。

篇（10）

中图分类号：TP31 文献标识码：A

Excel是微软公司开发的办公软件系列中的一款数据处理软件，它不仅可以用于输入和操纵数据，同时也为我们提供了许多数据管理与分析工具，有时我们在进行计算时常常需要假设某种情况，比如改变其中的一个条件，结果也要发生相应变化，同时我们又想对比这些情况，这时我们就可以使用excel提供的数据管理与分析工具，帮助我们模拟运行的可能结果，①如模拟运算表，方案管理器等。

下面我们就以购买商品房办理银行按揭为背景，利用“模拟运算表”和“方案管理器”来分别计算贷款额度、贷款年限及月利率分别改变时对月还款额的影响，为用户选择一种适合自己的贷款方案提供参考。

第一步：建立购房贷款计划表如图1所示，将所有月还款额对应的单元格设置为数值类型。

图1

第二步：利用PMT函数计算固定贷款额度，贷款年限，月利率下月还款额。

在E8单元格中输入公式：=PMT（C8，B8*12，A8），这里利用PMT函数来计算月还款额，PMT（rate，nper，pv，fv，type）是基于固定利率及等额分期付款方式下返回贷款的每期付款额。其中Rate代表贷款利率，Nper代表该项贷款的付款总次数，rate与nper要相匹配，譬如果rate为月利率，那么nper应为月数，Pv代表本金，Fv代表在最后一次付款后希望得到的现金余额，忽略表示0，Type值为0或省略表示付款时间是在期末，为1时表示在期初。

第三步：利用单变量模拟运算表计算固定贷款额度，贷款年限，不同月利率下月还款额的变化。

在B10单元格输入PMT公式计算月还款额，选择B9：F10区域，点击数据菜单下的模拟运算表，在输入引用行单元格输入$C$8，点击确定。使用单变量模拟运算表时有几个需要注意的地方。

（1）单元格公式位置：模拟表区域中可变条件在同一行，那么单元格公式在模拟表的左下方（如果可变条件在同一列，那么单元格公式在模拟表的右上方）；

（2）输入引用行（列）单元格选择：模拟表区域中可变条件在同一行，那么选择输入引用行单元格（如果可变条件在同一列，那么选择输入引用列单元格）；

（3）输入引用行（列）单元格中数据：根据模拟表区域中可变条件，在输入引用行（列）输入单元格公式中用到的对应的条件单元格。

第四步：利用双变量模拟运算表计算固定贷款额度，不同贷款年限，不同月利率下月还款额的变化情况

首先在A14单元格中输入公式=PMT（C8，B8*12，A8），计算某一固定贷款额度，贷款年限及贷款利率下月还款额，然后选中A14到F18这片连续的单元格，点击数据菜单下的模拟运算表，在输入引用行的单元格中填写$B$8，在输入引用列的单元格中输入$C$8，点击确定。使用双变量模拟运算表时有几个需要注意的地方。

（1）单元格公式位置：模拟表区域中左上角顶角的位置；

（2）输入引用行单元格中数据：根据单元格区域中第一行中的可变条件，在输入引用行的单元格中输入单元格公式中用到的对应的条件单元格；

（3）输入引用列单元格中数据：根据单元格区域中第一列中的可变条件，在输入引用列的单元格中输入单元格公式中用到的对应的条件单元格。

第五步：当贷款额度，贷款年限，月利率都发生改变的情况下可以使用方案管理器进行比较，方案是excel保存在工作表中并可以自动替换的一组值，使用方案管理器可以预测工作表模型的输出结果，在工作表中创建并保存了不同的数值组，通过切换到任意的新方案可以查看不同的结果。