关于Excel的几点运用

前言

以前感觉Office的办公软件使用起来都比较简单,毕竟图形化界面,不会的我一个个从工具栏里找总可以找到,今天同学发了份题目来,发现居然完全无从下手,钻研一晚上总算是搞定了,赶紧分享出来免得忘记。

分别是规划求解数据透视工具

说明:以下分析都是我自己的理解,如有不对的地方欢迎大家及时指出,以免误导他人。

规划求解

题目

使用规划求解设计获利最大的生产方案
某企业需要生产A、B两种产品,其中每生产一件A产品需要x原料3kg、y原料4kg、z原料4kg,B产品需要x原料2kg、y原料11 kg、z原料6kg。已知每天各种原料的使用限额为x原料120kg、y原料400kg、z原料240kg。根据预测,每销售一件A产品可获利1.6万元,B产品可获利1.3万元。那么如何安排生产计划才能在有限的原料供应下获得最大的利润呢?

要求:应用Excel的规划工具求解,首先需要将其模型化,分别确定决策变量,设置约束条件和目标。

分析

首先要明白什么是规划求解,为什么要用到它。

Google了下,说了一堆没看懂,后来想找题来看看,发现规划求解归根结底就是求方程最优解,比如什么最小运输成本呀,找个一个组合使差额最小等等,利用工具快速求解。

回到问题。

要求模型化,决策变量就是你用数学方法解题的xy之类的未知数,这里我选择的是A、B每日的生产的个数,假设分别为a、b;然后设置约束条件,就是列的方程组,这里是每日各原料的使用限额;最后目标当然就是最优解,这里是最大利润。

具体操作。

本人使用的是office2013版本,“规划求解”这个选项如果按我之前讲的在工具栏里找,死活也没有,因为从07版本后微软好像觉得这功能少用就放得很隐蔽了,2013版本的要从文件->选项->加载中心->Excel加载项->转到里选上规划求解,然后在工具栏的右侧就能找到这个功能,07、10版本类似。

然后当然就是建表了。这个可以随意,只要你自己能明白哪个代表什么意思就行。

我的是:
建表

相关的功能我都注明了,因为规划求解要求目标必须是函数,所以得先为目标设置函数。

这里先假设从左上角向右分别为ABCDEF,向下为1234567。那么依题意有目标=C6\C7+D6D7即总利润=1.6A日产量+1.3B日产量。这里需要借助SUMPRODUCT这个函数,即
SUMPRODUCT

这个函数可以求几个数乘积的和。Array中各元素对应相乘再相加,如图中是M20M21+N20N21,所以每个Array中元素个数必须相同。

然后考虑到约束条件中还需使用到函数,即A日产量A每件需x的量+B日产量B每件需x量<=x的每日限额,而规划求解中约束条件只能指定单元格(个人不知道如何在约束条件插入函数,所以如果有方法望告知),所以我先将函数放入单元格这样就可以使用约束条件。如第一张图中蓝色部分,函数还是SUMPRODUCT,三个函数(如第一个:fx=A日产量A每件需x的量+B日产量B每件需x量)放入对应单元格即可。

最后就是最关键的使用规划求解了。工具栏->数据->规划求解
规划求解

设置目标:“目标” 到 “最大值”
通过更改可变单元格:决策变量
约束条件:决策变量=int整数
决策变量>=0
约束条件<=每日限额

其余默认点击求解即可。

求解结果

可以看出,规划求解可以很方便的求出最优解。

数据透视表

题目

创建数据透视表
根据当前生源情况信息创建数据透视表,统计不同地区的不同分数段的男生和女生的人数,分数段的划分:将成绩由最低分到最高分之间以50分为间隔划分成若干分数段。要求不显示汇总信息。

数据透视表

分析

依然要知道什么数据透视表,为什么要用它。

透视嘛,就是可以通过这张表能很容易看出原表的特性。在这是能看出每个省不同性别在不同分数段的人数情况。十分方便的一个功能!

具体实现。

选定原表所有数据(注意是所有数据,包含表头的学院名称等等),然后在工具栏->插入->数据透视表创建

创建

选择一个表或区域,刚才选中的就是,如果没有可以重新选中,然后下面有个选择放置数据透视表位置,我是放在现有工作表,这个随意。之后确定即可。

而后就可以根据要求选择你需要的数据进行分析。

分析

这里因为需要对入学成绩进行区间划分,所以先在列中添加入学成绩,再选择列成绩某一单元右键->创建组。

创建组

然后选择区间(步长)即可。

组合

这里有必要说明一下,可能有些人用的是WPS,我之前也是用这个,后来选择创建组后并没有步长这个选项,不知道为什么,就放弃了这个软件。Microsoft Office的霸主地位不可撼动啊==。

之后汇总这个不要的话可以从字段设置里删除,包括布局是表格形式还是大纲形式。这里根据题目给出的图表,就是需要对地区使用表格形式。

总结

总得来说数据透视表比规划求解简单些,多捣鼓会应该就会了,各种功能大家可以试试,如果错了就Ctrl+z呗,对吧。学习这种东西还是需要自己多动手实践,不会的多百度百度(虽然之前看一篇文章说百度这种东西让人们越来越不会思考-.-,不过我觉得这最多也不过是为了快速学习的一点缺陷罢了),不然就自己多尝试摸索。

本文转自我的CSDN

© 2019 lvbin's Blog All Rights Reserved.
Theme by hiero