办公小技巧:Excel出手 数学模型应用不用愁

CFan 电脑爱好者 2021-04-29 10:01应用 标签:模型 数学 技巧 办公

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

找出Excel的分析工具库

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

201922sxjm01

图1 Excel 2016的选项对话框

201922sxjm02

图2 可用加载宏

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

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

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

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

201922sxjm03

图3 设置描述统计选项

201922sxjm04

图4 描述统计的分析结果

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

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

mod01

mod01

mod02

mod02

快速绘制直方图模型

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

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

201922sxjm05

图5 设置直方图选项

201922sxjm06

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

回归分析和预测

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

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

201922sxjm07

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

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

201922sxjm08

图8 回归分析参数设置

201922sxjm09

图9 回归分析结果

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

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

QQ图片20210420165237