excel办公小窍门 办公小技巧Excel出手

数学模型可以用来分析和解决实际工作中的很多问题，不过许多人提到“数学”就头疼，更别说应用了。其实大家日常所见的Excel中，已经内置了多种分析工具，在无需深入数学模型内部细节的情况下，即可让它为我们所用。

找出Excel的分析工具库

为了调用Excel（本文以2016版为例）内置的数学模型工具，我们需要导入分析工具库。首先启动Excel并新建一个空白文档，然后点击菜单“文件→选项”打开选项对话框，选中左侧“加载项”，再将右侧的“管理”设置为“Excel加载项”（图1），点击“转到”按钮，再在打开的加载宏窗口中勾选“分析工具库”等项（图2）。

图1 Excel 2016的选项对话框

图2 可用加载宏

点击“确定”返回，此时切换到“数据”选项卡，当看到右侧“分析”一栏中显示出“数据分析”项，就说明加载成功了。

描述统计的数学模型和分析

对某一给定的样本数据（或总体）进行统计分析，勾勒出这一数据资料的概要性特征，即所谓的描述统计。下面的例子，通过对某班级学生语文考试的成绩数据进行统计，从而了解该班同学对语文课程的掌握情况。

启动Excel，输入学生们考试成绩的原始数据，这里我们假设有66名学生，数据存入A1～A66区域中。点击工具栏上的“数据分析”，从弹出对话框中选中“描述统计”后点击“确定”，再在打开的新窗口中设置输入区域和输出区域的位置，勾选需要统计的项目（图3），点击“确定”后很快就会显示出结果（图4）。

图3 设置描述统计选项

图4 描述统计的分析结果

分析结果主要分两大类，一类是趋中性信息，描述数据集分布的中心位置，如平均值、中位数、众数等。另一类是变异性信息，描述数据集的离散数据，如方差、最小值、最大值、峰度和偏度等。

每一项分析结果背后都有相对应的数学模型，比如平均值的数学模型（mod01.png）、标准差的数学模型（mod02.png）等，我们不需要理解这些数学模型背后的细节，Excel已经帮我们完成了计算。

mod01

mod02

快速绘制直方图模型

直方图作为数学模型的一种表现方式，它使数据看起来更为形象、直观。

接上例，在数据分析窗口中选中“直方图”，确定后即可调出直方图对话框，其中“输入区域”即原始数据之所在，本例为A1～A66区域。“接收区域”可不填，Excel会自动填写。“输出区域”可选择任意空白单元格，另外勾选“柏拉图”、“累积百分率”及“图表输出”等选项（图5）。点击“确定”，即可得到直方图以及相关的统计结果了（图6）。

图5 设置直方图选项

图6 数据统计结果及直方图

回归分析和预测

当我们需要对某些事物的发展做出预测时，经常会用到回归分析（Regression Analysis）数学模型。比如商品销量和价格及广告之间是否存在关系，能否在已知价格和广告的基础上预测出销量等，就可以应用这一模型进行分析。

首先搜集以往的数据资料，并输入到Excel中。接下来打开数据分析窗口，选中“回归”（图7）。

图7 模拟销售数据并调用回归分析工具

在回归选项对话框中，“Y值输入区域”选择销售量这一列（A1：A8），这是本例需要预测的变量，也称之为因变量。“X值输入区域”选择价格和广告这两列（B1：C8），这些是解释变量，也称之为自变量。注意，这些列必须彼此相邻。“输出区域”设置为一空白单元格（如A12）。最后勾选“标志”、“残差”等项（图8），点击“确定”后即可得到详细的回归分析结果了（图9）。

图8 回归分析参数设置

图9 回归分析结果

本例的回归统计中，相关系数R值（Multiple R）高达0.9多，表示销量与价格及广告有着非常强的正相关关系。判定系数R平方值（R Square）高达0.85多，表示模型拟合度较好（一般大于70%的就算不错了）。

更多数据，大家可以参考回归分析相关的图书学习了解，本文限于篇幅，就不一一解说了。