Excel规划求解在数据分析与处理中的应用

第37卷第3期2021年3月
福建电脑
Journal of F ujian Computer
Vol.37No.3
Mar.2021
Excel规划求解在数据分析与处理中的应用
初道忠1陈瑞鑫2
】（山东理工大学资源与环境工程学院山东淄博255000）
2（渤海大学附属高级中学辽宁锦州121000）
摘要数据处理与分析主要解决两方面问题：一是对大数据进行统计分析，找出其内在规律，作为决策的依据；二是对实验数据进行分析、归纳，建立数学模型，探求科学真理。

Microsoft Excel以其强大的数据处理功能，广泛地应用于管理、统计财经、金融等众多领域。

其中的规划求解宏能很好地求解线性规划问题、数学模型中的参数优化以及单变量求解。

随着对规划求解功能的探索，它将会解决数据分析与处理中更多的问题。

关键词数据处理与分析；Microsoft Excel；规划求解；参数优化
中图法分类号G643.0D0I:10.16707/ki.fjpc.2021.03.033
Application of Excel Solver in Data Analysis and Processing
CHU Daozhong1,CHEN Ruixin2
"College of Resources and Environmental Engineering,Shandong University of Technology,Zibo,China,255000）
2（Senior High School Affiliated to Bohai University,Jinzhou,China,121000）
1引言
数据分析与处理是对数据的采集、存储、检索、加工、变换和传输。

其主要目的是从海量的、似乎没有相互联系的、不好理解的数据中筛选、处理并归纳岀对于需要的人们来说是有价值、有意义的数据⑴。

其具体表现在两个方面：一是对大数据的统计分析，找岀其内在规律性，并以此对未来进行预测，将预测结果作为决策的依据；二是对实验数据进行分析、归纳、建立数学模型，探求科学真理。

目前数据分析与处理方面的软件很多，如Python、Matlab、Oringe、Spss、Excel等。

其中Microsoft Excel以其使用的普遍性、强大的数据分析与处理功能，被大学生、研究生、教师及科研工作者广泛使用。

规划求解是Microsoft Excel加载项程序，可用于模拟分析。

使用“规划求解”查找一个单元格（称之为目标单元格）中公式的优化值，有时可能是最大或者最小值，以工作表上其他单元格公式的条件或者数值为约束。

它与一组用于计算目标和约束单元格中公式的单元格（称为决策变量或变量单元格）一起工作。

“规划求解”调整决策变量单元格中的值以满足约束单元格上的限制，并产生对目标单元格期望的结果。

用Microsoft Excel中的“规划求解”可以解决线性规划与非线性规划中的优化问题，同时还应用于数学模型拟合过程中的参数优化、单变量求解等方面。

下面针对这三方面情况的应用分别进行讨论。

2在线性规划中的应用
2.1问题的提出
某咨询服务中心每天值班安排时间段、各班需要的咨询服务人员数量如1表所示。

每班咨询话务员在各时段一开始上班，并连续工作9小时。

问咨询中心每天至少需要多少话务人员[2]?
2.2建立数学模型
因为每个人需要连续工作9小时,即三个班次。

如果设Ni（i=1,2,...,8）表示班次1至8开始工作的
本文得到山东理工大学2019年研究生精品课程建设项目（No.219084）资助。

初道忠，男，1967年生，主要从事矿业系统工程、数据分析处理方法等方面的教学和研究工作。

E-mail:**************。

2021年福建电脑105
人数，这样可以建立如下的数学模型：
目标函数：Min Z=N1+N2+N3+N4+N5 +N6+N7+N8
约束条件：
N7+N8+N1三12
N8+N1+N2三8
N1+N2+N3三16
N2+N3+N4三20
N3+N4+N5三26
N4+N5+N6三30
N5+N6+N7三26
N6+N7+N8三16
其中，N1、N2、N3、N4、N5、N6、N7、N8 $0且为整数。

表1咨询服务中心每班需保证的人数
班次时间段最少需求人数
10-3点12
23-6点8
36-9点16
49-12点20
512-15点26
615-18点30
718-21点26
821-24点16
2.3用规划求解求得最优解
在利用规划求解解决线性规划问题，还需要使
用Microsoft Excel函数SUMPRODUCT（）。

该函数参数数组不能为空，每个参数数组包含的数量相等，返回值为给定的参数数组中对应数值的乘积之和［3］。

语法形式为SUMPRODUCT（参数数组1,参数数字2,...,参数数组n）。

其中参数1必需，是相应元素需要进行相乘并求和的第一个数组参数；参数数组2,参数数组3,...可选，其相应元素需要进行相乘并求和。

数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值。

下面利用Microsoft Excel2010列岀表格数据以及规划求解。

如图1所示规划求解中对话框的参数设置，其中“设置目标”是图2中“目标函数”对应的单元格，目标函数求最小值，所以点中“最小值”前面的单选按钮，“可变单元格”对应图2的“N1-N8”对应的单元格。

在选项里确定变量是整数，然后确定得到优化结果。

结果表明：班次1至班次8对应的开始工作人数分别为6人、2人、8人、10人、8人、12人、6人和0人，每天最少配备人数为52名咨询服务人员。

3数学模型拟合中的参数优化
数学模型参数优化是通过求目标函数最大（小）值，使得模型输岀结果与实验测量数据之间达到最佳的拟合效果［4］。

由于实验环境本身很难达到理想的条件，通常优化算法很难达到参数在实验情况下的全局最优。

近年来，随着计算机运算效率的快速提高，这种优化方法得到了进一步开发与广泛应用。

图1规划求解中对话框中的参数设置
图2规划求解优化结果
在数学模型参数拟合中，参数的确定一般运用最小二乘法的原理进行优化。

最小二乘法是一种常用的数学优化技术，它以模型值与实验值之差的平方和最小为优化目标，来求得数据的最佳函数匹
106初道忠等：Excel 规划求解在数据分析与处理中的应用第3期
配。

最小二乘法还可用于线性和非线性拟合，如指 数、对数、多项式等函数拟合。

运用最小二乘法参数优化，需满足两个条件： 一是2 /X=0,二是2/Y2 最小［5］。

如通过实验获得一组数值：
X 161.17 146.52 123.08 105.49
98.56Y
0.4684 0.4884 0.5361 0.5861
0.6284
通过分析，符合反比例函数Y=K/X 模型，图3 是用规划求解优化常数K 的值。

其中规划求解里的
参数设置目标单元格是2/ Y2最小，可变单元格K 的值，没有约束条件，优化结果K 值为65.5069。

图3规划求解优化的常数K 值
X
Y 拟合Y 常 U lK （?</>：）
161.17210.46840.406440.0038389965. 50685567
146.5201
0.48840. 4470840. 001706976123.07690.53610. 532243 1. 48741E-05
105.49450.5861
0.62095
0. 00121455398. 5641
0.62640. 664612
0. 0013112880. 008086681
4单变量求解
单变量求解就是用计算机方法寻求公式中的 一个变量的值，可以通俗地理解为解一元方程。

由
于普通的一元高次方程没有通用的解法，所以可以
借助于单变量求解求得任意的一元高次方程解。

它 具体是通过某种搜索方法来调整可变单元格中的 数据，根据给定的公式来求得满足目标单元格中的
目标值。

单变量求解是函数公式的逆运算。

尽管在Excel 中有单变量求解宏，但是规划求
解宏除了上述介绍的功能外，完全能实现单变量求
解宏的功能。

下面用一个例子来说明其用法。

假定一名研究生在本学期共学习了五门课，其 中课程1、课程2、课程3、课程4的成绩分别为
81、93、94、86，学校规定平均成绩在90以上才
有资格评优，那么该学生课程5最少得多少分才有
资格评优？
这是一个典型的单变量求解问题，数学模型为 假定己考完的四门课成绩分别是X1、X2、X3、
X4,则课程5的成绩X5满足(X1+X2+ X3+X4+X5)
/5M 90,并求岀满足条件X5的最低值。

图4是在Excel 工作表中，输入相关数据：目 标值D2单元格输入的公式是：SUM (B2： B6) /5,
即五门课的平均成绩。

在规划求解对话框中设置目标为D2单元格，
目标值为90,可变单元格为B6。

通过规划求解得
到课程5的成绩为96,也就是课程5成绩在不小于
96 时，五门课程平均成绩不小于 90，该学生方有
资格参评奖学金，优化结果如图4所示。

值得注意
的是，并不是所有的问题都有解，若前四门课程成
绩较低，最后一门课程可能超岀正常得分范围，不
在0至100分之间，这样他最后一门课程无论考多 少分，都不能获得评优资格。

图4用规划求解运行结果
5结论
Excel 是微软公司的办公软件 Microsoft office
的主要组成部分。

它具有数据处理、统计分析和模
型求解等数据分析和处理功能，被广泛地应用于企
业管理、经济统计、金融等诸多领域。

Excel 中的 规划求解宏主要用来解决线性规划与非线性规划
中的优化问题，但在教学和科研实践中发现，可以
用来进行数学模型中的参数优化，以及满足单变量
求解中的功能。

随着以后在教学中进一步的探索， 相信还会发现其更多的功能，为广大学生在学习和 科研方面服务。

参考文献
［1］ 姜启源，谢金星，叶俊.数学模型.第四版.北京:高等教育出版社,2011
［2］ 韩伯棠.管理运筹学.第四版.北京:高等教育出版社,2015
［3］ 沈玮，李海燕,徐进华.实用Excel 数据处理.苏州:苏州大学出版
社,2012
［4］ 韦来生.数理统计.北京:科学出版社,2008
［5］ 李军.最优化原理与方法.广州:华南理工大学出版社,
2018。