admin管理员组文章数量:1567283
2024年5月22日发(作者：)
第
37
卷第
3
期
福建电脑
Vol.
37
No.3
Mar.
2021
2021
年
3
月
Journal
of
Fujian
Computer
Excel
规划求解在数据分析与处理中的应用
初道忠
1
陈瑞鑫
2
】
（山东理工大学资源与环境工程学院山东淄博
255000
）
2
（
渤海大学附属高级中学辽宁锦州
121000
）
摘要
数据处理与分析主要解决两方面问题
：
一是对大数据进行统计分析
，
找出其内在规律
，
作为决策的依据
；
二是对实
验数据进行分析
、
归纳
，
建立数学模型
，
探求科学真理
。
Microsoft
Excel
以其强大的数据处理功能
，
广泛地应用于管理、
统
计财经
、
金融等众多领域
。其中的规划求解宏能很好地求解线性规划问题、
数学模型中的参数优化以及单变量求解
。
随着对
规划求解功能的探索
，
它将会解决数据分析与处理中更多的问题
。
关键词
数据处理与分析
；
Microsoft
Excel
；
规划求解
；
参数优化
中图法分类号
G643.0
D0I:10.16707/.2021.03.033
Application
of
Excel
Solver
in
Data
Analysis
and
Processing
CHU
Daozhong
1
,
CHEN
Ruixin
2
"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
引言
数据分析与处理是对数据的采集、
存储
、
检索
、
加工
、
变换和传输
。
其主要目的是从海量的
、似乎
没有相互联系的
、
不好理解的数据中筛选
、
处理并
变量或变量单元格
）
一起工作
。
“
规划求解
”
调整
决策变量单元格中的值以满足约束单元格上的限
制
，
并产生对目标单元格期望的结果
。
用
Microsoft
Excel
中的
“
规划求解
”
可以解决线性规划与非线
性规划中的优化问题
，
同时还应用于数学模型拟合
归纳岀对于需要的人们来说是有价值
、
有意义的数
据⑴
。
其具体表现在两个方面
：
一是对大数据的统
过程中的参数优化
、
单变量求解等方面
。
下面针对
这三方面情况的应用分别进行讨论
。
计分析
，
找岀其内在规律性
，
并以此对未来进行预
测
，
将预测结果作为决策的依据
；
二是对实验数据
2
在线性规划中的应用
2.1
问题的提出
某咨询服务中心每天值班安排时间段、
各班需
要的咨询服务人员数量如
1
表所示
。
每班咨询话务
进行分析
、
归纳
、
建立数学模型
，
探求科学真理
。
目前数据分析与处理方面的软件很多
，
如
Python
、
Matlab
、
Oringe
、
Spss
、
Excel
等
。
其中
Microsoft
Excel
以其使用的普遍性
、强大的数据分
析与处理功能
，被大学生
、
研究生
、
教师及科研工
员在各时段一开始上班
，
并连续工作
9
小时
。问咨
作者广泛使用
。
规划求解是
Microsoft
Excel
加载
项程序
，
可用于模拟分析
。
使用
“
规划求解
”
查找
询中心每天至少需要多少话务人员
[2]
?
一个单元格
（
称之为目标单元格
）
中公式的优化
值
，
有时可能是最大或者最小值，以工作表上其他
2.2
建立数学模型
因为每个人需要连续工作
9
小时,即三个班次
。
单元格公式的条件或者数值为约束
。
它与一组用于
如果设
Ni
（
i
=
1,2,
...,8
）
表示班次
1
至
8
开始工作的
本文得到山东理工大学
2019
年研究生精品课程建设项目
（
No.219084
）
资助
。
初道忠
，
男
，
1967
年生
，
主要从事矿业系统工程
、
数据分析处理方法等方
面的教学和研究工作
。
E-mail:**************
。
2021
年
福建电脑
105
人数
，
这样可以建立如下的数学模型
：
目标函数
：
Min
Z=
N1
+
N
2
+
N
3
+
N
4
+
N
5
+
N
6
+
N
7
+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
咨询服务中心每班需保证的人数
班次
时间段
最少需求人数
1
0-3
点
12
2
3-6
点
8
3
6-9
点
16
4
9-12
点
20
5
12-15
点
26
6
15-18
点
30
7
18-21
点
26
8
21-24
点
16
2.3
用规划求解求得最优解
在利用规划求解解决线性规划问题
，
还需要使
用
Microsoft
Excel
函数
SUMPRODUCT
（
）
。
该函
数参数数组不能为空
，
每个参数数组包含的数量相
等
，
返回值为给定的参数数组中对应数值的乘积之
和
［3
］
。
语法形式为
SUMPRODUCT
（
参数数组
1,
参
数数字
2,
...,
参数数组
n
）
。
其中参数
1
必需
，
是相应
元素需要进行相乘并求和的第一个数组参数
；
参数
数组
2,
参数数组
3,...
可选
，
其相应元素需要进行
相乘并求和
。
数组参数必须具有相同的维数，
否则
，
函数
SUMPRODUCT
将返回错误值
。
下面利用
Microsoft
Excel
2010
列岀表格数据
以及规划求解
。
如图
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.
56
Y
0.4684
0.4884
0.5361
0.5861
0.6284
通过分析
，
符合反比例函数
Y=K/X
模型
，
图
3
是用规划求解优化常数
K
的值
。其中规划求解里的
参数设置目标单元格是
2/
Y2
最小
，
可变单元格
K
的值
，
没有约束条件
，
优化结果
K
值为
65.5069
。
X
Y
拟合
Y
常
UlK
（
?
：
）
161.
1721
0.46840.40644
0.00383899
65.
50685567
146.5201
0.4884
0.
447084
0.
001706976
123.0769
0.5361
0.
532243
1.
48741E-05
105.4945
0.5861
0.62095
0.
001214553
98.
5641
0.6264
0.
664612
0.
001311288
0.
008086681
图
3
规划求解优化的常数
K
值
4
单变量求解
单变量求解就是用计算机方法寻求公式中的
一个变量的值
，
可以通俗地理解为解一元方程
。
由
于普通的一元高次方程没有通用的解法
，
所以可以
借助于单变量求解求得任意的一元高次方程解
。
它
具体是通过某种搜索方法来调整可变单元格中的
数据
，
根据给定的公式来求得满足目标单元格中的
目标值
。
单变量求解是函数公式的逆运算
。
尽管在
Excel
中有单变量求解宏
，
但是规划求
解宏除了上述介绍的功能外
，
完全能实现单变量求
解宏的功能
。
下面用一个例子来说明其用法
。
假定一名研究生在本学期共学习了五门课
，
其
中课程
1
、
课程
2
、
课程
3
、
课程
4
的成绩分别为
81
、
93
、
94
、
86
，
学校规定平均成绩在
90
以上才
有资格评优
，
那么该学生课程
5
最少得多少分才有
资格评优
？
这是一个典型的单变量求解问题
，
数学模型为
假定己考完的四门课成绩分别是
X1
、
X2
、
X3
、
X4
,
则课程
5
的成绩
X5
满足
(
X1+X2
+
X3+X4+X5
)
/5
M
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
版权声明：本文标题：Excel规划求解在数据分析与处理中的应用 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/dianzi/1716388259a500903.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论