使用规划求解确定最佳产品组合

注意:  我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的 英文版本 以便参考。

本文讨论如何使用规划求解、 Microsoft Excel 加载项程序可用于模拟分析,以确定最佳产品组合。

如何确定每月最大化利润的产品组合?

公司通常需要确定以生成每月每个产品的数量。最简单的形式在产品组合问题涉及如何确定应在最大化利润月份产生每个产品的数量。通常,产品组合必须遵循以下限制:

  • 产品组合不能使用不是可用的更多资源。

  • 没有对每个产品的有限的请求。我们无法生成的产品的详细信息在比 demand 规定月份因为多余生产浪费 (例如,易药物)。

让我们来解决产品组合问题的下面的示例。您可以在文件 Prodmix.xlsx,显示图 27-1 中找到此问题的解决方案。

书籍图像
图 27-1 产品组合

假设我们药物公司产生在他们的工厂六种不同的产品的工作。生产每种产品需要人工和 raw 材料。第 4 行图 27-1 显示人工生产一磅的每个产品,所需的时间,第 5 行显示 raw 需的材料以生成一磅的每个产品的磅数。例如,生成产品 1 磅需要六个小时人工和 3.2 磅 raw 材料。每个药物,井现价给定行 6 中,每磅单位成本提供,第 7 行和每磅的利润贡献提供第 9 行中。例如,产品 2 销售的每磅 11.00 $、 井,5.70 美元的单位成本和占 $ 利润就是 5.30 每磅。第 8 行以弧度药物每个月的需求。例如,产品 3 需求为 1041 磅。本月、 4500 人工工时和 1600 磅的 raw 材料都可用。此公司如何最大化其每月利润?

如果我们知道有关 Excel 的规划求解的任何操作,我们将通过构建工作表以跟踪与产品组合相关联的利润和资源使用状况解决这个问题。然后我们将使用反复试验变化产品组合以优化利润,而无需使用更多人工或 raw 材料比可用,和而不会生成任何药物超过需求。我们在只能在试用版错误阶段在此过程中使用规划求解。实际上,规划求解是完美地执行试用版错误搜索优化引擎。

解决产品组合问题的关键是要高效地计算的资源使用状况和利润与任何给定的产品组合相关联。我们可以使用进行此计算重要工具是 SUMPRODUCT 函数。SUMPRODUCT 函数将单元格区域中的对应值相乘,并返回这些值的总和。使用 SUMPRODUCT 求值的每个单元格区域必须具有相同的尺寸,这意味着您可以使用 SUMPRODUCT,带有两个行或两个列,但不能使用一列和行。

我们可以如何使用 SUMPRODUCT 函数在我们的产品的示例为组合示例中,我们来尝试来计算我们资源使用状况。通过计算得到人工用量

(每药物 1 磅使用人工) *(Drug 1 pounds produced) +
(每磅药物 2 使用人工) * (生产的药品 2 的磅数) +...
(每磅药物 6 使用人工) * (生产的药品 6 的磅数)

我们无法计算方式为更过于繁琐人工用法D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4。同样,可能会作为计算 raw 材料用法D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5。但是,在工作表中输入六种产品使这些公式非常耗时。假设它将花费的时间如果您正在使用生成,例如,公司在他们的工厂 50 产品。更简单方法来计算人工和 raw 材料用法是复制 D14 到 D15 SUMPRODUCT($D$2:$I$2,D4:I4)的公式。此公式计算D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (这是人工用量),但可以更加轻松地输入 !请注意,使用 $ 符号 d2: i2 的范围以便在复制公式时仍捕获产品组合第 2 行。单元格 D15 中的公式计算 raw 材料用法。

在类似的方式,我们的利润是通过确定

(药品每磅 1 的利润) * (药物 1 磅生产) +
(每磅药物 2 利润) * (生产的药品 2 的磅数) +...
(每磅药物 6 利润) * (生产的药品 6 的磅数)

包含SUMPRODUCT(D9:I9,$D$2:$I$2)的公式的单元格 D12 中轻松地计算利润。

现在,我们可以确定产品组合规划求解模型的三个组件。

  • 目标单元格。我们的目标是最大化利润 (计算单元格 D12 中)。

  • 可变单元格。每个产品 (单元格区域 d2: i2 中列出) 生成的磅数

  • 约束。我们有以下限制:

    • 不要使用更多人工或 raw 材料比可用。也就是说,单元格 d14: d15 (使用的资源) 中的值必须小于或等于值的单元格 f14: f15 (可用资源)。

    • 不生成药物大于需求的详细信息。也就是说,单元格 d2: i2 (生产的每个药物的磅数) 中的值必须小于或等于对每个药物 (单元格 d8: i8 中列出) 的需求。

    • 我们无法生成任何药物负金额。

我将介绍如何输入目标单元格,更改为规划求解的单元格和限制。然后,您只需要是单击求解按钮以查找利润最大化产品组合 !

若要开始,单击数据选项卡,,然后在分析组中,单击规划求解。

注意: 说明章节 26,"引入到优化与 Excel 规划求解,"规划求解已安装通过单击 Microsoft Office 按钮,然后 Excel 选项,后面跟有加载项。在管理列表中,单击 Excel 加载项,选中规划求解加载项框中,,然后单击确定。

将显示规划求解参数对话框中,如下所示图 27-2。

书籍图像
图 27 2 规划求解参数对话框

单击设置目标单元格框,然后选择利润单元格 (单元格 D12)。单击更改单元格框中,然后指向区域 d2: i2,其中包含的每个药物产生的磅数。对话框中现在应如下图 27-3。

书籍图像
图 27 3 与目标单元格和可变单元格定义的规划求解参数对话框

现在我们准备好向模型添加约束。单击添加按钮。您将看到添加约束对话框,显示图 27-4。

书籍图像
图 27-4 添加约束对话框

要添加资源用量约束,请单击单元格引用框,然后选择区域 d14: d15。选择 < = 从中间的列表。单击约束框,然后选择单元格区域 f14: f15。添加约束对话框中现在应如下图 27-5 等。

书籍图像
输入资源使用状况约束图 27-5 添加约束对话框

现在我们已经确保,当规划求解尝试不同的值,用于更改单元格,只有满足两者的组合D14 < = F14 (所用人工小于或等于可用人工) 和D15 < = F15 (使用 raw 材料小于或等于将被视为 raw 材料可用)。单击添加输入需求约束。填写添加约束对话框中所示图 27-6。

书籍图像
图 27-6 输入了需求约束的添加约束对话框

添加约束确保当规划求解的值的可变单元格的不同组合时,将考虑只有满足以下参数的组合:

  • D2 < = D8(药物 1 的产量小于或等于药物 1 的需求)

  • E2 < = E8(生成的药物 2 量小于或等于药物 2 demand)

  • F2 < = F8(药物 3 所做的产量小于或等于药物 3 的需求)

  • G2 < = G8(药物 4 所做的产量小于或等于药物 4 的需求)

  • H2 < = H8(药物 5 所做的产量小于或等于药物 5 的需求)

  • I2 < = I8(药物 6 所做的产量小于或等于药物 6 的需求)

单击添加约束对话框中的确定。规划求解窗口中看起来像图 27-7。

书籍图像
图 27-7 最终的产品组合问题的规划求解参数对话框

我们输入可变单元格必须是限制规划求解选项对话框中的非负数。单击规划求解参数对话框中的选项按钮。检查采用线性模型框和假定非负框中,在下一页上显示图 27-8。单击确定。

书籍图像
图 27-8 规划求解选项设置

检查假定非负框中,确保规划求解认为只有可变单元格的每个可变单元格假定非负值的组合。我们已检查采用线性模型框中,因为产品组合问题是特殊类型的规划求解问题称为线性模型。实际上,规划求解模型是线性在以下情况下:

  • 通过将窗体的条件计算目标单元格(更改相加

  • 每个约束满足"线性模型要求"。这意味着将窗体的条件计算每个约束(更改相加和比较总和与某个常量。

为什么是线性此规划求解问题?我们的目标单元格 (利润) 计算为

(药品每磅 1 的利润) * (药物 1 磅生产) +
(每磅药物 2 利润) * (生产的药品 2 的磅数) +...
(每磅药物 6 利润) * (生产的药品 6 的磅数)

此计算遵循目标单元格的值通过将窗体的条件而派生模式(更改相加

通过比较派生(每药物 1 磅使用人工) 的值计算我们人工约束 * (药物 1 磅生产) + (每磅药物 2 使用人工) *(Drug 2 pounds produced) +...(技术人员上门我们教育每磅药物 6) * (生产的药品 6 的磅数)可用人工。

因此,通过将窗体的条件计算人工约束(更改相加和比较总和与某个常量。人工约束和 raw 材料限制满足线性模型要求。

我们的需求约束采用窗体

(药物 1 生成) < = (药物 1 需)
(产生药物 2) < = (药物 2 需)
第节
(产生药物 6) < = (药物 6 需)

每个 demand 约束也能满足线性模型要求,因为计算每个时将窗体的条件(更改相加和比较总和与某个常量。

具有显示我们的产品组合模型是线性模型,为什么我们应该注意?

  • 如果规划求解模型是线性,我们选择采用线性模型,保证规划求解查找规划求解模型的最佳解决方案。如果规划求解模型不线性,规划求解可能或可能无法找到最佳解决方案。

  • 如果规划求解模型是线性,我们选择采用线性模型,规划求解使用非常高效的算法 (simplex 方法) 来查找模型的最佳解决方案。如果规划求解模型是线性,我们不选择采用线性模型,规划求解使用低下算法 (GRG2 方法),并可能很难找到该模型最佳解决方案。

单击确定,在规划求解选项对话框中的后, 我们返回到主的规划求解对话框中,显示更早版本中图 27-7。当我们单击求解时,规划求解计算最佳解决方案 (如果存在) 我们的产品组合模型。我在章 26 陈述,产品组合模型的最佳解决方案会使利润最大化所有可行解决方案设置的一组的可变单元格值 (生产的每个药物的磅数)。同样,可行的解决方案是一组的可变单元格的值满足所有约束。显示图 27-9 的值的可变单元格是一个可行解决方案,因为所有生产级别都的非负数、 生产级别不能超过需求,和资源使用状况不超过可用资源。

书籍图像
图 27 9 可行解决方案阶乘乘积混合问题符合约束。

在下一页上显示图 27-10 中的值的可变单元表示不可行解决方案,原因如下:

  • 我们制作详细的要求比药物 5。

  • 我们使用的人工大于什么是可用。

  • 我们使用比所提供的更多 raw 材料。

书籍图像
图 27 10 产品组合问题的不可行解决方案不符合定义的约束。

单击求解后规划求解快速查找显示图 27-11 的最佳解决方案。您需要选择保存规划求解要保留在工作表中的最佳解决方案值。

书籍图像
图 27-11 产品组合问题的最佳解决方案

我们药物公司可以将其每月的利润的级别 6,625.20 美元的最大化通过产生药物 4、 1084 磅的药物 5 和无其他毒品 596.67 磅 !我们无法确定是否可以获得的最大利润的其他方法 6,625.20 美元。我们可以确定的是与我们有限的资源需求,无法使多 6,627.20 $ 本月。

假设满足需求必须每个产品。(请参阅在文件 Prodmix.xlsx无可行解决方案表)。然后,我们需要更改此约束从d2: i2 < = d8: i8d2: i2 > = d8: i8。要执行此操作,打开规划求解,选择 d2: i2 < = d8: i8 约束,然后单击更改。显示更改约束对话框,显示图 27-12。

书籍图像
图 27-12 更改约束对话框

选择 > =,,然后单击确定。现在,我们可以确保规划求解将考虑更改仅单元格值符合所有需求。单击求解时,您将看到消息"规划求解找不到可行解决方案。"此消息并不意味着我们与我们有限的资源在我们的模型,而所做误操作,我们不能满足需求的所有产品。规划求解只要告诉我们是否我们要满足每个产品的需求,我们需要添加更多的人工、 更多原料,或两者的详细信息。

让我们看看会发生什么情况如果我们允许无限制每个产品的需求,我们允许负产量为每个药物。(您可以看到这个规划求解问题 Prodmix.xlsx 文件中设置的值不执行汇聚工作表中。)以这种情况下查找最佳解决方案,请打开规划求解,单击选项按钮,然后清除假定非负框。在规划求解参数对话框中,选择 demand 限制 d2: i2 < = d8: i8,然后单击删除以删除限制。单击求解时,规划求解返回的消息"设置单元格的值不聚合。"此消息意味着如果目标单元格 (如我们的示例) 最大化,有具有任意大目标单元格值可行解决方案。(如果要将最小化目标单元格,该邮件"设置单元格的值不聚合"意味着有可行解决方案具有任意小目标单元格值。)在我们的情况下,通过允许负生产药物,我们实际上"创建"可用于生成的其他毒品任意大量的资源。提供我们无限制的需求,这样我们进行无限的利润。在实际的情况下,我们不能进行无限长的资金。简而言之,如果您看到"设置值不聚合",您的模型有错误。

  1. 假设我们药物公司可以购买多达 500 小时的人工多每小时当前人工成本 $1。我们如何最大化利润?

  2. 在一家芯片制造工厂,(A、 B、 C 和 D) 的四个技术人员产生三个产品 (产品 1、 2 和 3)。本月、 芯片制造商可以销售 80 单位产品 1、 产品 2、 50 个单位和最 50 个单位的产品 3。技术的人员可以使仅产品 1 和 3。仅产品 1 和 2,可以使技术员 B。技术员 C 可以使仅产品 3。技术员 D 可以使仅产品 2。对于每个单位产生,产品利润: 产品 1、 $6;产品 2、 $7;和产品 3、 10 美元。每个技术员需要制造产品的时间 (以小时) 如下:

    产品

    技术员 A

    技术员 B

    技术员 C

    技术员 D

    1

    2

    2.5

    不能执行的操作

    不能执行的操作

    2

    不能执行的操作

    3

    不能执行的操作

    3.5

    3

    3

    不能执行的操作

    4

    不能执行的操作

  3. 每个技术人员可以处理每月最多 120 小时。芯片制造商如何最大化其每月利润?假定可以生成小数单位数。

  4. 计算机制造工厂生产鼠标、 键盘和视频游戏操纵杆。下表给出了每个单位利润、 每个单位人工用法、 每月需求和单位计算机时间用法:

    鼠标

    键盘

    操纵杆

    利润/单位

    $ 8

    $ 11

    $ 9

    每个单位劳工用法

    .2 小时

    .3 小时

    .24 小时

    计算机时间/单位

    .04 小时

    .055 小时

    .04 小时

    每月需求

    15,000

    27000

    11000

  5. 每个月,总共 13000 工时和 3000 小时的计算机的时间都可用。制造商如何最大化工厂从其每月利润作品?

  6. 解决我们药物示例假设,必须满足每个药物 200 单位的最低要求。

  7. Jason 使菱形带、 项链和耳环。他希望合作 160 小时每月最大值。他有 800 盎司的菱形。下面给出了利润、 人工时间和盎司菱形生成每个产品所需。如果不受限制地对每个产品的需求,如何 Jason 最大化他的利润?

    产品

    单位利润

    每个单位人工小时数

    盎司的每个单位的菱形

    手镯

    ¥3,000

    .35

    1.2

    项链

    $200

    .15

    .75

    耳环

    ¥100

    .05

    .5

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×