1、1模拟题（III）操作步骤【第 1 题】某公司需采购某零件，全年需求量为 15000 件，每次订货成本为 500 元，单件零件的年储存成本为 30 元，当订货量为 900 件时，要求：1、 计算年订货成本、年储存成本、年订储成本。2、 依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本。3、 在本工作表中生成一个运算表，计算当该零件的年订货成本、年储存成本、年订储成本随订货量从2001400(按行分布)变化的值(此值的计算方式不限，填入对应的单元格中)。4、基于上述运算表绘制反映该零件的年订货成本、年储存成本、年订储成本随订货量(从 2001400) 变化的图形(无数据点平滑线
2、散点图 )5、在图形中使用微调框与文本框控制当该零件年需求量从 10000 按增量 1000 变化到 20000 时，经济订货量及经济订货量下的年订储成本的值，并在图形上反映出来。6、在图形中使用微调框与文本框反映出当订货量从 400 按增量 10 变化到 1000 时年订货成本、年储存成本、年订储成本的值，并在图形上反映出来。7、生成的图形如下所示，并按所给图示对图形进行相应的格式化。注： 储存成本=(单位存储成本订货量)/2=(存货单价存储费率订货量)/2、订储成本=订货成本+储存成本。 要求 5 与 6，需在图形上添加当前订货量和经济订货量的垂直参考线及显示参考点的数据值来满足。年需求量
3、(D) 15000 一次订货的订货成本(A) 500 单位年储存成本(PK) 30 订货量(Q) 900.0 年订货成本 年储存成本 年订储成本 经济订货量(EOQ) EOQ 下的年订货成本 EOQ 下的年储存成本 EOQ 下的年订储成本 2操作步骤：1、计算年订货成本、年储存成本、年订储成本：C7=C3*C2/C6C8=C4*C6/2C9=C7+C82、依据基本的经济订货量模型，计算经济订货量及经济订货量时的年订储成本：C11=SQRT(2*C3*C2/C4)C12=C3*C2/C11C13=C4*C11/2C14=C12+C133、 在本工作表中生成一个运算表，计算当该零件的年订货成本、年
4、储存成本、年订储成本随订货量从2001400(按行分布)变化的值(此值的计算方式不限，填入对应的单元格中)。3在 F3:F15 中输入：200 、300、1400G3=$C$2/F3*$C$3 复制到 G4:G15 中H3=F3/2*$C$4 复制到 H4:H15 中I3=G3+H3 复制到 I4:I15 中4、基于上述运算表绘制反映该零件的年订货成本、年储存成本、年订储成本随订货量(从 2001400) 变化的图形(无数据点平滑线散点图 )6250.014400.020650.018973.70.005000.0010000.0015000.0020000.0025000.0030000.0
5、035000.0020030040050060070080090010001100120013001400订货量订 货 成 本 储 存 成 本 订 储 成 本 当 前 订 货 量 经 济 订 货 量年 需 求 量 =12000 当 前 订 货 量 =960选中 F3:I15图表向导 XY 散点图无数据点平滑线散点图调整 X 轴刻度：右击 X 轴坐标轴格式刻度最小值： 200最大值：1400主要刻度单位：100同样的方法调整 Y 轴刻度5、在图形中使用微调框与文本框控制当该零件年需求量从 10000 按增量 1000 变化到 20000 时，经济订货量及经济订货量下的年订储成本的值，并在图形上反
6、映出来。在数据表中添加可动态显示年需求量的单元格 B16： =“年需求量=“&C24在数据表中添加可动态显示当前订货量的单元格 B17： =“当前订货量=“&C6在图表中添加反映年需求量变化的微调框与文本框：添加微调框最小值：10000最大值：30000步长：1000单元格链接：$C$2 利用绘图工具栏中的文本框按钮添加文本框 在地址栏中输入：= B16在图表中添加反映当前订货量变化的微调框与文本框：添加微调框最小值：400最大值：1000步长：10单元格链接： $C$6 利用绘图工具栏中的文本框按钮添加文本框 在地址栏中输入：= B176、添加当前订货量和经济订货量的垂直参考线在数据表中的
7、B21:C25 区域中添加用于制作当前订货量垂直参考线的数据，其中：B21=C6、B22=B21、B23=B22、B24=B23、B25=B24C21=0、C22=C7、C23=C8、C24=C9 、C25=35000添加当前订货量垂直参考线选中 B21:C25复制点击图表区域 编辑选择性粘贴添加单元格为：新建系列数值（Y ）轴在：列勾选“首列为分类 X 值 ”在数据表中的 G21:H25 区域中添加用于制作经济订货量垂直参考线的数据，其中：G21=C11、G22= C11、G23= C11、G24= C11、G25= C11H21=0、H22=C12、H23=C14、H24=C15、H25=
8、35000添加经济订货量垂直参考线选中 G21:H25复制点击图表区域 编辑选择性粘贴添加单元格为：新建系列数值（Y ）轴在：列勾选“首列为分类 X 值 ”7、生成的图形如下所示，并按所给图示对图形进行相应的格式化。8、 右击经济订货量垂直线 源数据 系列 系列 5 名称：=“经济订货量”5右击当前订货量垂直线 源数据 系列 系列 4 名称：=“当前订货量”第 2 题 利用 Excel 建立基本的经济订货量模型。假设一件商品在仓库里储存一年的费用与年需求量的关系如本工作表中单元格区域 A1:B6 所示，订一次货的成本为 300 元。要求：1、在本工作表中先建立一个运算表(“静态“的二维表格)。
9、计算当年需求量 (按列分布)分别为10000、15000、20000、25000 和 30000，订货量(按行分布) 以增量 100 从 100 起始变化到 1500 时，年总成本(为年订货成本与年储存成本之和) 的各个值(此值的计算方式不限，填入对应的行与列的交叉单元格中)。2、计算在不同年需求量下，当订货量为 500 时的年订货成本、年储存成本和年总成本。3、计算在不同年需求量下的经济订货量(EOQ)以及 EOQ 下的年订货成本、年储存成本和年总成本。4、基于建立的运算表，绘制在不同年需求量下的年总成本随订货量变化的图形(无数据点平滑线散点图) 。其中在当前年需求量下的年总成本线设为大红色
10、，其它年总成本线为“灰色-25%” ，线形的粗细均为“次粗线” 。5、在图中添加一个可以对当前年需求量进行调节的微调框和文本框，年需求量的最小值为 10000，最大值为 30000，步长为 5000。使在当前年需求量下的红色年总成本线可以随之移动。6、在图中添加一个“不同年需求量下的经济订货量”垂直参考线，该参考线经过红色成本线的最低点并显示该点（大小为 6 磅）的数据值。7、生成的图形如下图所示，并按所给图示对图形进行相应的格式化。提示： 在建立的运算表中应添加一“动态数据列” ，该列数据将“动态”反映不同年需求量下的年总成本随订货量变化的值；“对应的单位年储存成本”单元格的值可使用 IND
11、EX()函数嵌套 MATCH()函数的方式依据不同的年需求量从单元格区域 A1:B6“动态”获取； B8 单元格中的“年需求量”的值可从微调框所链接的单元格获取，此单元格可为“动态数据列”的第一个单元格。年需求量(D) 单位年储存成本(PK)10000 3015000 2520000 2025000 1530000 10年需求量（D） 对应的单位年储存成本 一次订货的订货成本（A） 300订货量（Q） 500年订货成本 年储存成本 年总成本 经济订货量(EOQ) EOQ 下的订货成本 EOQ 下的储存成本 EOQ 下的年总成本 6提示： 在建立的运算表中应添加一“动态数据列” ，该列数据将“动
12、态”反映不同年需求量下的年总成本随订货量变化的值；“对应的单位年储存成本”单元格的值可使用 INDEX()函数嵌套 MATCH()函数的方式依据不同的年需求量从单元格区域 A1:B6“动态”获取； B8 单元格中的“年需求量”的值可从微调框所链接的单元格获取，此单元格可为“动态数据列”的第一个单元格。操作步骤：1、 根据公式计算如下数据：输入年需求量：B8=30000 计算对应的单位年存储成本：B9=INDEX(B2:B6, MATCH(B8, A2:A6, 0)或：B9=VLOOKUP(B8, A2:B6, 2)计算年订货成本：B13=B10*B8/B12计算年储存成本：B14=B9*B12
13、/2计算总成本： B15=B13+B147计算经济订货量（EOQ）： B17=SQRT(2*B10*B8/B9)计算 EOQ 下的订货成本：B18=B10*B8/B17计算 EOQ 下的储存成本：B19=B9*B17/2计算 EOQ 下的年总成本：B20=B18+B192、 在本工作表中先建立一个运算表(“静态”的二维表格) 。计算当年需求量(按列分布)分别为10000、15000、20000、25000 和 30000，订货量(按行分布) 以增量 100 从 100 起始变化到 1500 时，年总成本(为年订货成本与年储存成本之和 )的各个值(此值的计算方式不限，填入对应的行与列的交叉单元格
14、中)。在 E3:E17 中输入：100、200、300、1500在 F2、G2、H2、I2 、J2 中输入：10000、15000、20000、25000、30000 F3 =$B$10*F$2/$E3+$B$2*$E3/2复制到 F4:F17 中G3 =$B$10*G$2/$E3+$B$3*$E3/2复制到 G4:G17 中H3 =$B$10*H$2/$E3+$B$4*$E3/2复制到 H4:H17 中I3 =$B$10*I$2/$E3+$B$5*$E3/2复制到 I4:I17 中J3 =$B$10*J$2/$E3+$B$6*$E3/2复制到 J4:J17 中或：F3=E$2/$D3*$B$
15、10+$D3/2*INDEX($B$2:$B$6,MATCH(E$2,$A$2:$A$6,0) 复制到 G3:J3 中选中 F3:J3公式复制到 F4:J17或：选中 E2:J17数据模拟运算表输入引用行的单元格：B8输入引用列的单元格：B12确定3、绘制曲线图选中 E3:J17图表向导XY 散点图无数据点平滑线散点图将所有曲线的颜色都设置成灰色。右击 Y 轴坐标轴格式刻度最小值：10000最大值：50000主要刻度单位:5000右击 X 轴坐标轴格式刻度最小值：100最大值：1500主要刻度单位:200 4、其中在当前年需求量下的年总成本线设为大红色，其它年总成本线为“灰色-25%” ，线形
16、的粗细均为“次粗线” 。 添加一列数据用于显示当前年需求量下的年总成本：K2=B8 K3 =$B$10*K$2/$E3+$B$9*$E3/2 或 =HLOOKUP($K$2,$F$2:$J$17,ROW()-1) 将 K3 中的8公式复制到 K4:K17 中 选中图表，单击右键选择“源数据” ，选择“系列”选项卡，在“系列（S） ”下单击“添加”按钮，添加了“系列 6”，在右侧“X 值”后设置为 E3:E17， “Y 值”后设置为 K3:K17，单击“确定”按钮 将新添加的曲线设置成红色。5、在图中添加一个可以对当前年需求量进行调节的微调框和文本框，年需求量的最小值为 10000，最大值为 3
17、0000，步长为 5000。使在当前年需求量下的红色年总成本线可以随之移动。在数据表的 A22 单元格中输入： = “当前年需求量 =”&B8设置 A22 单元格为白底红字 添加微调框右击微调框设置控件格式控制 最小值：10000 最大值：30000步长：5000单元格链接：B8添加文本框在编辑栏输入：= A226、在图中添加一个“不同年需求量下的经济订货量”垂直参考线，该参考线经过红色成本线的最低点并显示该点（大小为 6 磅）的数据值。添加垂直线数据：F22=B17、F23=B17、F24=B17G22=10000、G23=B20、G24=50000添加垂直线：选择 F22:G24复制选择图
18、表编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列首列为分类 X 值调整垂直线的属性：红色中粗在于成本线最低点交叉处显示数值数字大小为：6 磅右击选择垂直线与红色曲线的交叉点 数据点格式 图案 数据标记 自定义样式：矩形 前景色 枚红色 数据标志 勾选 Y 值7、并按所给图示对图形进行相应的格式化。9第 3 题 某炼钢厂每年生产需要消耗 20000 吨矿石，每吨矿石价格 250 元，该厂每次向矿石供应商订购时，需要花费手续费等合计 6000 元，储存 1 吨矿石的年成本为 50 元。要求：1、在本工作表中构造一个模型来确定当矿石订购量等于 3000 吨时的全年采购成本、全年订货成本、全年
19、储存成本、全年总成本（为前三项成本之和） 。利用理论公式计算出最优订货量及全年总成本极小值。2、 （1）在本工作表中建立一运算表（二维表格） ，利用理论公式计算当订货量（按行分布）从 1400 按步长 100 变化到 3400 时全年总成本随订货量变化的值。（2）基于此运算表制作一个订货量从 1400 按步长 100 变化到 3400 时表示全年总成本随订货量变化的曲线（蓝色、粗细为“次粗线” ）图形(无数据点平滑线散点图) 。3、 （1）在本工作表中建立一运算表（二维表格） ，利用理论公式计算当储存 1 吨的年成本（按行分布）从 50 按步长 1 变化到 60 时的最优订货量和全年总成本极小
20、值。（2）基于此运算表，在图形上添加当储存 1 吨的年成本从 50 按步长 1 变化到 60 时的最优订货量和全年总成本极小值构成的空心红色点子(大小为 6 磅) 和红色曲线。4、在图形上添加一条反映当前最优订货量且随控件变化的红色水平参考线（粗细为“次粗线” ）及实心的红色点子(大小为 6 磅)和该参考点的数据值。5、在图形上添加一个微调框和文本框控制当储存 1 吨的年成本从 50 按步长 1 变化到 60 时，红色水平线、点子及蓝色曲线随之而动。6、依据要求 2 中建立的运算表的数据，使用 INDEX()函数嵌套 MATCH()函数与 MIN()函数，分别查表求出最优订货量及对应的全年总成
21、本的极小值。7、生成的图形如下所示，并按所给图示对图形进行相应的格式化。年需求量(D) 20000 每次订货成本(A) 6000 储存 1 吨的年成本(PK) 50 每吨矿石价格(P) 250 每次订货量(Q) 3000 全年采购成本 全年订货成本 全年储存成本 全年总成本 公式法： 最优订货量 全年总成本极小值 查表法： 最优订货量 全年总成本极小值 10操作步骤：1、在本工作表中构造一个模型来确定当矿石订购量等于 3000 吨时的全年采购成本、全年订货成本、全年储存成本、全年总成本（为前三项成本之和） 。利用理论公式计算出最优订货量及全年总成本极小值。C7=C2*C5 C8=C3*C2/C
22、6 C9=C4*C6/2 C10=C7+C8+C9C12=SQRT(2*C3*C2/C4) C13=SQRT(2*C3*C2*C4)+C5*C22、（1）在本工作表中建立一运算表（二维表格） ，利用理论公式计算当订货量（按行分布）从 1400 按步长100 变化到 3400 时全年总成本随订货量变化的值。在 E2:E22 中生成不同的订货量：1400、1500、3400在 F2:F22 中计算不同订货量下的全年总成本：F2=$C$2*$C$5+$C$2*$C$3/E2+$C$4*E2/2 公式复制到 F3:F22 中（2）基于此运算表制作一个订货量从 1400 按步长 100 变化到 3400
23、 时表示全年总成本随订货量变化的曲线（蓝色、粗细为“次粗线” ）图形(无数据点平滑线散点图) 。11选中 E2:F22 图表向导XY 散点图无数据点平滑线散点图调整 Y 轴刻度最小值：5105000最大值：5125000主要刻度单位：200003、 （1）在本工作表中建立一运算表（二维表格） ，利用理论公式计算当储存 1 吨的年成本（按行分布）从 50 按步长 1 变化到 60 时的最优订货量和全年总成本极小值。在 E26:E36 中输入： 50、51、52、60F26=SQRT(2*$C$3*$C$2/E26) 复制到 F27:F36 中G26=SQRT(2*$C$3*E26*$C$2)+$
24、C$5*$C$2 复制到 G27:G36 中12（2）基于此运算表，在图形上添加当储存 1 吨的年成本从 50 按步长 1 变化到 60 时的最优订货量和全年总成本极小值构成的空心红色点子(大小为 6 磅) 和红色曲线。选中 F26:G36复制进入图表页面编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列确定右击最优订货量和全年总成本极小值构成的曲线数据系列格式线形颜色：红色粗细：中粗样式：圆形前景色：红色背景色：白色大小（Z）：6 磅4、在图形上添加一条反映当前最优订货量且随控件变化的红色水平参考线（粗细为“次粗线” ）及实心的红色点子(大小为 6 磅)和该参考点的数据值。设置水平参考线
25、数据：B25=1400、B26=C13、B27=3400、C25=C14、C26= C14、C27= C14根据水平参考线数据绘制水平参考线：选中 B25:C27复制 进入图表编辑选择性粘贴添加单元格为：新建系列数值（Y）轴在：列首列为分类 X 值5、在图形上添加一个微调框和文本框控制当储存 1 吨的年成本从 50 按步长 1 变化到 60 时，红色水平线、点子及蓝色曲线随之而动。在绘图区添加一个微调框 属性 最小值：50 最大值：60 步长：1单元格链接：C4设置文本框内容：B20=“当前单位年储存成本=“&C4在绘图区添加一个文本框 在编辑栏输入：=B206、依据要求 2 中建立的运算表的
26、数据，使用 INDEX()函数嵌套 MATCH()函数与 MIN()函数，分别查表求出最优订货量及对应的全年总成本的极小值。C18= MIN(G26:G36)C17= INDEX(F26:F36, MATCH(C18, G26:G36, 0)7、按所给图示对图形进行相应的格式化。13第 4 题 某企业每年需要配件 20000 件，每次订货费用为 1000 元，存储费用是零件单价的 15%。供货商规定：凡一次性购买 3000 件以下的价格为 8 元/件、3000 件或以上但 6000 件以下的价格为 7 元/ 件、6000 件或以上但 8000 件以下的价格为 6 元/件、8000 件或以上的价
27、格为 5 元/ 件。根据此规定建立的折扣起点批量与零件单价折扣价的对应关系如本工作表中单元格区域 A1:B5 所示。要求：1、当 B11 单元格显示不同的折扣起点批量时，在 B13 单元格中计算不同折扣起点批量下的总存货费用。总存货费用=年采购费用+ 年订货费用+ 年储存费用。2、根据基本的经济订货量模型在 A16:A19 单元格区域计算出不同单价折扣价下的经济订货量。3、判断以上计算出的经济订货量是否有效，并使用 IF()函数嵌套 AND()函数的公式在 B16:B19 的单元格区域显示“有效”或“无效” 。4、对有效的经济订货量求出其对应的总存货费用。5、在本工作表中建立一运算表（二维表格
28、） ，利用理论公式计算当订货量（按行分布）从 1000 按步长500 变化到 9000 时总存储费用随订货量变化的值。6、依据此运算表的相关数据和有效经济订货量的总存货费用，求出总存货费用的最小值。7、根据总存货费用的最小值找出订货量的最优解。8、基于此运算表制作一个订货量从 1000 按步长 500 变化到 9000 时，总存货费用随订货量变化的曲线（绿色、粗细为“次粗线” ）图形(无数据点平滑线散点图) 。9、在图形中添加一个垂直参考线（粉红色、粗细为“次粗线” ） ，该参考线显示“折扣起点批量下的总存货费用”数据参考点（大小为 6 磅）及其值。10、在图形上添加一个可以对折扣起点批量进行
29、选择的组合框及文本框。当选择不同的折扣起点(3000、6000、8000)时，B11 单元格显示相应的折扣起点值，并且垂直参考线也将随之而动。11、生成的图形如下所示，并按所给图示对图形进行相应的格式化。提示：（1）此题使用非连续价格的折扣优惠方式处理；（2） “对应的配件单价折扣价”单元格的值可使用 INDEX()函数嵌套 MATCH()函数的方式依据不同的“折扣起点批量”从单元格区域 A1:B5“动态”获取；（3）订货量的最优解请使用 INDEX()函数嵌套 MATCH()函数的方式根据总存货费用的最小值从建立的运算表中获取。操作步骤：141、 （先做第 10 题中的添加组合框控件）在图形
30、上添加一个可以对折扣起点批量进行选择的组合框及文本框。当选择不同的折扣起点(3000、6000、8000) 时，B11 单元格显示相应的折扣起点值。添加组合框 属性 数据源区域：A3:A5 单元格链接：C11建立列表框与折扣起点批量（B11）的关联：B11=INDEX(E20:E22,F20)2、当 B11 单元格显示不同的折扣起点批量时，在 B13 单元格中计算不同折扣起点批量下的总存货费用。总存货费用=年采购费用+ 年订货费用+ 年储存费用。B12=IF(B11=3000,A17=6000,A18=8000,“有效“,“无效“)5、对有效的经济订货量求出其对应的总存货费用。C16=IF(B
31、16=“有效“,$B$7*B2+$B$7/A16*$B$8+A16/2*B2*$B$9,“无效“)复制到 C17:C19 中156、在本工作表中建立一运算表（二维表格） ，利用理论公式计算当订货量（按行分布）从 1000 按步长500 变化到 9000 时总存储费用随订货量变化的值。生成订货量列：E2=1000 E3=1500 选中 E2:E3 拖动句柄至 E18计算不同订货量下的总存储费用：在 F2：F5 中单价用 B2：F2=$B$2*$B$7+$B$8*$B$7/E2+$B$2*$B$9*E2/2在 F6：F11 中单价用 B3：F2=$B$3*$B$7+$B$8*$B$7/E2+$B$
32、3*$B$9*E2/2在 F12：F15 中单价用 B4：F2=$B$4*$B$7+$B$8*$B$7/E2+$B$4*$B$9*E2/2在 F16：F18 中单价用 B5：F2=$B$5*$B$7+$B$8*$B$7/E2+$B$5*$B$9*E2/2另一种方法：添加单价折扣列：G2=IF(E2=1500,$C$8,$C$7)+$C$4*$C$5/E3+E3*$C$6/2将 F3 中的公式复制到 F4:F12 中4、基于运算表绘制 XY 散点图，在该图形上分别添加两个微调框和两个文本框用以控制当折扣阈限值从191000 按增量 50 变化到 3500、单价折扣率从 5%按增量 1%变化到 2
33、0%。修改 C2 和 C3 单元格的内容，使之随控件同步变化。（1）绘制无数据点平滑散点图选中 E3:F12图表向导XY 散点图无数据点平滑散点图右击 Y 轴坐标轴格式刻度最小值：80000最大值：120000主要刻度单位：10000右击 X 轴坐标轴格式刻度最小值：300最大值：3800主要刻度单位：500右击曲线数据系列格式图案不勾选平滑线（2）添加微调框、文本框B22 ：=B3&“=“&C3 B23： =B2&“=“&C2*100&“%“B25： =IF(C20=F23,“不接受优惠价格，采用经济订货量“,“采用价格优惠政策“)添加折扣卡阀限值微调框：最大值：3500最小值：1000步长
34、：50链接单元格：C3添加折扣卡阀限值文本框：在编辑栏输入：=B2220添加折扣率微调框：最大值：20最小值：5步长：1链接单元格：C23 C2 =C23/100添加折扣率文本框：在编辑栏输入：=B23添加结论文本框：在编辑栏输入：=B255、在图形中，添加经过经济订货量的垂直参考线与参考点（显示值）和年总成本极小值的水平参考线与参考点（显示值） 。添加垂直参考线数据E18=C16 E19=C16 E29=C16 F18=80000 F19=C20 F20=120000添加垂直参考线选中 E18:F20 复制 选中图表 编辑 选择性黏贴 添加单元格为：新建系列数值（Y）轴在：列 勾选首列为分类
35、 X 值添加水平参考线数据E23=300 E24:=IF(C20=F24,C16,INDEX(E3:E12,MATCH(F24,F3:F12,0) E25=3800 F23: =MIN($F$3:$F$12,$C$20) F24=F23 F25=F23添加水平参考线选中 E23:F25 复制 选中图表 编辑 选择性黏贴 添加单元格为：新建系列数值（Y）轴在：列 勾选首列为分类 X 值右击垂直线、水平线与曲线的交叉点 数据系列格式 数据标志 勾选 Y 值6、以上图形及参考线可随控件而变化。7、在图形上添加一个文本框，显示是采用经济订货量、还是接受折扣优惠。（在第 4 小题处已完成）8、生成的图形如下所示，并按所给图示对图形进行相应的格式化。注：生成的图线在折扣阈限值处应有一个陡降的效果。
