JHU-Excel-笔记-全-

JHU Excel 笔记（全）

001：Excel预算创建入门教程 💰

在本课程中，我们将学习如何使用Microsoft Excel创建一个简单的个人预算表。我们将从基础功能开始，逐步构建一个包含收入、支出和净储蓄的预算模型。通过这个练习，你将掌握Excel的基本操作、公式使用和格式设置。

概述 📋

我们将创建一个个人预算表，模拟计算年度收入、支出和净储蓄。你将学习如何输入数据、使用公式进行计算、格式化表格以及调整数据以优化预算。

第一步：设置收入部分 💵

首先，我们需要在Excel中设置收入部分。我们将输入年薪，并计算扣除税款和其他费用后的净收入。

在A1单元格中输入“收入”，然后按Enter键。接下来，在A2单元格中输入“年薪”，在B2单元格中输入数字50000。注意，我们只输入数字，不添加美元符号或逗号。

输入完成后，选中B2单元格，点击“开始”选项卡中的“货币”格式按钮，将数字显示为货币格式。通常，在财务表格中，我们不需要显示美分，因此可以点击“减少小数位数”按钮，将小数部分隐藏。

现在，我们需要计算税款。假设税率为22%，我们需要计算50000的22%。在A3单元格中输入“税款”，然后在B3单元格中输入公式：

=B2*0.22

这个公式引用了B2单元格中的年薪，并计算其22%。按Enter键后，B3单元格将显示税款金额。

为了更清晰地展示公式，我们可以在C3单元格中使用FORMULATEXT函数：

=FORMULATEXT(B3)

这样，C3单元格将显示B3单元格中的公式，方便查看和验证。

第二步：添加其他扣除项 🏥

除了税款，我们还需要考虑其他扣除项，如医疗、牙科和退休储蓄。以下是具体步骤：

在A4单元格中输入“医疗”，在B4单元格中输入数字3000。
在A5单元格中输入“牙科”，在B5单元格中输入数字5000。
在A6单元格中输入“退休储蓄”，在B6单元格中输入数字5000。

选中B4到B6单元格，点击“开始”选项卡中的“货币”格式按钮，将这些数字显示为货币格式，并隐藏美分。

接下来，我们需要计算净收入。在A7单元格中输入“净收入”，然后在B7单元格中输入公式：

=B2-B3-B4-B5-B6

这个公式从年薪中减去税款、医疗、牙科和退休储蓄，得到净收入。按Enter键后，B7单元格将显示计算结果。

同样，我们可以在C7单元格中使用FORMULATEXT函数，显示B7单元格中的公式：

=FORMULATEXT(B7)

第三步：设置支出部分 🛒

现在，我们来设置支出部分。我们将列出年度各项支出，并计算总支出。

在A9单元格中输入“支出”，然后按Enter键。接下来，在A10单元格中输入“房租”，在B10单元格中输入公式：

=1500*12

这个公式计算每月1500美元的房租，乘以12得到年度总房租。按Enter键后，B10单元格将显示计算结果。

以下是其他支出项的示例：

  • 在A11单元格中输入“食物”，在B11单元格中输入数字5000。
  • 在A12单元格中输入“交通”，在B12单元格中输入数字3000。
  • 在A13单元格中输入“旅行”，在B13单元格中输入数字1000。
  • 在A14单元格中输入“宠物”，在B14单元格中输入数字1000。
  • 在A15单元格中输入“礼物”，在B15单元格中输入数字500。
  • 在A16单元格中输入“个人储蓄”，在B16单元格中输入数字5000。
  • 在A17单元格中输入“贷款还款”，在B17单元格中输入数字10000。

选中B10到B17单元格，点击“开始”选项卡中的“货币”格式按钮，将这些数字显示为货币格式，并隐藏美分。

第四步：计算总支出和净储蓄 📊

接下来，我们需要计算总支出。在A18单元格中输入“总支出”，然后在B18单元格中输入公式：

=SUM(B10:B17)

这个公式使用SUM函数，将B10到B17单元格中的数字相加，得到总支出。按Enter键后，B18单元格将显示计算结果。

最后，我们需要计算净储蓄。在A19单元格中输入“净储蓄”，然后在B19单元格中输入公式：

=B7-B18

这个公式从净收入中减去总支出，得到净储蓄。按Enter键后，B19单元格将显示计算结果。

如果净储蓄为负数，我们可以将其格式化为会计格式，用括号表示负数。选中B19单元格，点击“开始”选项卡中的“会计数字格式”按钮，选择适当的格式。

第五步：美化表格 🎨

为了让表格更美观，我们可以进行一些格式化操作。以下是具体步骤：

  1. 选中A1和B1单元格，点击“开始”选项卡中的“合并后居中”按钮，将这两个单元格合并为一个，并居中显示标题。
  2. 选中合并后的标题单元格，点击“开始”选项卡中的“单元格样式”按钮，选择一个样式（如“标题1”）来美化标题。
  3. 选中收入部分的标签（A2到A7），点击“开始”选项卡中的“加粗”按钮，使其更突出。
  4. 选中支出部分的标签（A10到A19），同样点击“加粗”按钮。
  5. 使用“缩进”按钮调整标签的对齐方式，使表格更整齐。

第六步：调整数据以优化预算 🔧

Excel的强大之处在于，当你调整数据时，所有相关公式会自动更新。例如，如果你减少退休储蓄的金额，净收入和净储蓄会自动重新计算。

尝试将B6单元格中的退休储蓄从5000改为4000，然后按Enter键。观察B7和B19单元格的变化，你会发现净收入和净储蓄自动更新。

同样，你可以调整支出项的数字，观察总支出和净储蓄的变化。通过这种方式，你可以优化预算，找到收支平衡或增加储蓄的方法。

总结 📝

在本课程中，我们一起学习了如何使用Excel创建一个简单的个人预算表。我们从输入收入开始，逐步添加扣除项和支出项，使用公式进行计算，并通过格式化使表格更美观。最后，我们通过调整数据来优化预算，展示了Excel的动态计算能力。

通过这个练习，你掌握了Excel的基本操作，包括数据输入、公式使用、格式设置和动态调整。这些技能是进一步学习Excel高级功能的基础。希望你能继续练习，探索更多Excel的强大功能！

002：NCAA T恤供应商业务模型 🧮

在本节课中，我们将学习如何为一家小型企业——NCAA T恤供应商——创建一个基础的电子表格模型。我们将通过这个案例，掌握更多Excel的核心功能，包括成本与收入的计算、公式的应用以及如何构建一个清晰、可调整的业务分析模型。

第一步：建立标题与记录已知条件

首先，我们为电子表格创建一个清晰的标题。这有助于快速识别模型的主题。

NCAA T恤供应商

接下来，我们需要仔细阅读问题，并将所有已知条件整理到表格中。这是构建准确模型的基础。

以下是已知的成本信息：

  • 固定成本：$750。这是下订单的基础费用。
  • 可变成本：$8。这是每件T恤的生产成本。

第二步：定义关键变量与收入结构

上一节我们记录了成本信息，本节中我们来看看收入部分和需要计算的关键变量。

我们需要定义几个可以调整的变量，以便进行“假设分析”。通常，我们会用颜色（如绿色）来标记这些可调整的单元格。

  • 订购数量：供应商计划订购的T恤总数。
  • 市场需求：预计会以全价购买的T恤数量。
  • 全价：$18。这是锦标赛后一周内的销售价格。
  • 折扣价：$6。这是一周后剩余的T恤销售价格。

第三步：计算实际销量与剩余库存

现在我们已经定义了成本和价格，接下来需要计算实际的销售情况。这里有一个关键点：实际销量受限于“订购数量”和“市场需求”中较小的那个。

我们需要计算两个数值：

  1. 全价售出数量：这取决于我们有多少库存，以及市场需要多少。公式为：=MIN(订购数量, 市场需求)。
  2. 折扣价售出数量：这是订购后剩余的T恤数量。但剩余数量不能为负数。公式为：=MAX(订购数量 - 市场需求, 0)。

例如，如果订购1600件，但市场需求只有1500件，那么全价售出1500件，剩余100件将以折扣价出售。

第四步：计算总成本、总收入与总利润

在明确了销量之后，我们现在可以计算最终的财务指标了。一个好的电子表格模型，其利润公式应该非常简单明了。

  • 总成本：这是固定成本与总可变成本之和。
    • 公式：总成本 = 固定成本 + (可变成本 * 订购数量)
    • 在Excel中：=750 + (8 * [订购数量单元格])
  • 总收入：这是全价收入与折扣价收入之和。
    • 公式：总收入 = (全价 * 全价售出数量) + (折扣价 * 折扣价售出数量)
  • 总利润：这是整个模型的核心输出。
    • 公式：总利润 = 总收入 - 总成本
    • 在Excel中，这应该是一个简单的减法公式，确保所有复杂计算都在上游完成。

第五步：模型测试与“假设分析”

模型构建完成后，必须进行测试。通过调整“订购数量”和“市场需求”这两个绿色变量，我们可以观察利润如何变化，从而为商业决策提供支持。

例如，你可以尝试：

  • 如果市场需求增加，利润会如何变化？
  • 如果订购数量减少，是否会出现缺货损失？
  • 找到能使利润最大化的最佳订购量。

这个模型的优势在于，所有计算都是动态联动的。修改任何一个输入变量，总利润都会自动更新。

本节课中我们一起学习了如何为一个T恤供应商构建完整的业务分析模型。我们掌握了记录已知条件、定义变量、使用MIN和MAX函数处理业务逻辑、计算成本收入以及进行利润分析的全过程。这个模型不仅计算结果准确，而且结构清晰、易于调整，是进行商业决策分析的实用工具。

003： Elementary to Advanced

课程编号：P3-2 木工书架公司 📊

在本节课中，我们将学习如何为一家名为“木工书架公司”的企业建立财务模型。我们将重点掌握Excel中的绝对引用技巧，并学习如何根据成本增长率创建成本预测表和图表。

模型概述与数据准备

首先，我们需要理解公司的业务。木工书架公司生产两种定制家具：樱桃木书架和橡木书架。我们的目标是建立一个模型，以预测未来几年内，在材料成本和劳动力成本预期增长的情况下，生产这两种书架的总成本将如何变化。

以下是建模的初始数据：

  • 材料成本：樱桃木每板英尺550美元，橡木每板英尺430美元。每个书架需要30板英尺木材。
  • 劳动力成本：组装每个书架需要16小时，每小时工资为18.50美元。
  • 预期年增长率：
    • 樱桃木材料成本：2.4%
    • 橡木材料成本：1.7%
    • 劳动力成本：1.5%

计算当前成本

上一节我们介绍了公司的基本成本结构，本节中我们来看看如何计算生产一个书架的当前成本。

首先，我们分别计算材料和劳动力的当前成本。

材料成本计算公式：
材料成本 = 所需板英尺数 × 每板英尺单价

劳动力成本计算公式：
劳动力成本 = 所需小时数 × 每小时工资

将上述数据输入Excel并应用公式后，我们得到以下当前成本：

  • 樱桃木书架材料成本：$165
  • 橡木书架材料成本：$129
  • 每个书架的劳动力成本：$296

接着，我们将材料和劳动力成本相加，得到生产每个书架的当前总成本。

  • 樱桃木书架总成本：$461
  • 橡木书架总成本：$425

构建成本预测表

现在我们已经有了当前的成本数据，接下来需要构建一个表格来展示未来几年的成本预测。这个表格将是我们绘制图表的基础。

我们将创建一个包含“年份”、“樱桃木材料成本”、“橡木材料成本”、“劳动力成本”以及“樱桃木总成本”和“橡木总成本”的表格。我们从“第0年”（即当前）开始，预测到第5年。

关键步骤：

  1. 将第0年的成本数据引用到表格中。
  2. 对于第1年，我们需要用上一年的成本乘以 (1 + 增长率) 来计算新的成本。
    • 例如，第1年的樱桃木材料成本公式为：=B18*(1+$B$14)，其中B18是第0年的成本，B14是2.4%的增长率。

掌握绝对引用技巧

当我们尝试将第1年的公式向下拖动以填充后续年份时，可能会遇到问题。数字可能不更新或出现错误。这是因为单元格引用发生了我们不希望的移动。

问题分析：
在公式 =B18*(1+B14) 中，当我们向下拖动时，Excel会自动将引用调整为 =B19*(1+B15)。我们希望B18（上一年的成本）随行移动，但B14（增长率）必须固定不变。

解决方案：使用绝对引用。
在Excel中，在行号或列标前添加美元符号$可以“锁定”该引用。

  • $B$14：锁定B列和第14行，无论公式复制到哪里，都引用单元格B14。
  • B$18：只锁定第18行，列可以变化。
  • $B18：只锁定B列，行可以变化。

对于我们的模型，应将增长率的单元格引用改为绝对引用（如$B$14）。修正后，第1年樱桃木材料成本的正确公式为：=B18*(1+$B$14)。这样，向下拖动时，成本单元格（B18， B19...）会依次变化，而增长率单元格（$B$14）始终保持不变。

以下是应用绝对引用后，正确填充预测表的步骤：

  1. 修正第1行的公式，为所有增长率引用加上美元符号（$）。
  2. 选中第1行中已修正的公式区域。
  3. 将填充柄向下拖动至第5年。此时，表格将正确显示逐年增长的成本。

创建可视化图表

一个清晰的图表能让数据趋势一目了然。所有优秀的图表都源于结构良好的数据表，而我们刚刚已经完成了这一步。

现在，我们将基于预测表创建一个折线图，直观展示两种书架总成本在未来几年的变化趋势。

创建图表的步骤如下：

  1. 选中包含年份和两种书架总成本的数据区域。
  2. 点击 “插入” 选项卡，选择 “推荐的图表”。
  3. 从推荐图表中选择一个折线图类型。
  4. 生成图表后，进行以下优化以提高可读性：
    • 修改图表标题：将默认的“图表标题”改为更具描述性的名称，例如“木工书架公司成本预测”。
    • 添加坐标轴标题：为横坐标轴（年份）和纵坐标轴（成本）添加清晰的标签。
    • 调整图例和样式：确保图例清晰，可以调整线条颜色以使图表更美观。

总结与模型应用

本节课中我们一起学习了如何为木工书架公司构建一个动态的成本预测模型。我们完成了从数据录入、成本计算，到利用绝对引用（$B$14）构建预测表，最后创建可视化图表的全过程。

这个模型的核心价值在于其动态性。通过将增长率（如材料成本增长率）设置为可输入的变量（并用绿色标出），管理人员可以轻松调整这些假设，并立即在表格和图表中看到对应的成本变化结果。这为企业的定价策略和长期财务规划提供了有力的数据支持。

核心技能总结：

  • 使用绝对引用创建动态公式：这是构建可扩展预测模型的关键。
  • 从表格到图表：掌握了基于准确数据表生成专业图表的完整工作流。
  • 模型的可读性与交互性：通过格式化和突出关键输入单元格，使模型易于他人理解和使用。

004： Elementary to Advanced

课程编号：P4 - 优质毛衣公司案例建模 🧮

在本节课中，我们将学习如何为“优质毛衣公司”建立一个财务模型。我们将使用Excel来模拟公司的成本和收入，并学习如何使用数据表和单变量求解这两个强大的工具来分析关键业务问题，例如盈亏平衡点。

模型输入与假设

首先，我们需要从案例描述中提取所有关键数据，并将其清晰地组织在Excel工作表中。清晰的标签和格式对于构建可靠的模型至关重要。

以下是案例中给出的所有输入参数：

  • 固定印刷成本：$20,000
  • 每本目录的印刷可变成本：$0.10
  • 每本目录的邮寄可变成本：$0.15
  • 每个订单的直邮信封成本：$0.20
  • 平均订单金额：$40
  • 每个订单的可变成本（占订单金额80%）：$32
  • 计划邮寄目录数量：100,000

此外，案例中未给出但我们需要分析的关键变量是响应率。我们将其设置为一个可以调整的假设单元格。

构建计算模型

上一节我们整理了所有输入参数，本节中我们来看看如何利用这些参数计算公司的收入、成本和利润。

我们首先需要计算基于响应率产生的订单数量。公式如下：
订单数量 = 计划邮寄目录数量 * 响应率

接着，我们可以计算总收入：
总收入 = 订单数量 * 平均订单金额

总成本的计算稍复杂，它由固定成本和可变成本组成：
总成本 = 固定印刷成本 + (每本目录总成本 * 计划邮寄目录数量) + (每个订单总成本 * 订单数量)
其中，每本目录总成本 = 印刷可变成本 + 邮寄可变成本，每个订单总成本 = 直邮信封成本 + 订单处理可变成本。

最后，利润的计算很简单：
利润 = 总收入 - 总成本

通过设置一个初始的响应率（例如5%），我们可以得到模型的初始结果。

使用数据表分析响应率的影响

我们已经建立了一个可以计算单次利润的模型。为了系统地分析不同响应率如何影响利润，手动更改单元格效率很低。此时，我们可以使用Excel的数据表功能。

数据表允许我们指定一个输入变量（如响应率）的一系列值，并自动计算出对应的输出结果（如利润）。

以下是创建数据表的步骤：

  1. 在工作表的空白区域，创建两列。第一列列出你想要测试的一系列响应率值（例如从3%到8%，间隔0.5%）。
  2. 在第二列标题行的单元格（通常在第一行响应率值的右侧），创建一个指向模型中利润计算公式的链接。例如，如果利润在单元格B20，则在此处输入公式 =B20。这个单元格是数据表的“输出引用”。
  3. 选中整个数据表区域（包括你列出的响应率值和输出引用单元格）。
  4. 点击 数据 选项卡，在 预测 组中找到 模拟分析，然后选择 数据表。
  5. 在弹出的对话框中，由于我们的变量值（响应率）是列方向的，所以在 输入引用列的单元格 框中，选择模型中存放响应率假设的单元格（例如B14），然后点击确定。

Excel会自动为列表中的每一个响应率计算出对应的利润值，并填充在表格中。我们可以基于此数据快速创建图表，直观地观察利润随响应率变化的趋势。

使用单变量求解计算盈亏平衡点

通过数据表，我们可以观察到利润从负转正的大致区间。为了精确找到使利润为零的响应率（即盈亏平衡点），我们可以使用 单变量求解 工具。

单变量求解通过反向计算，找到达到目标结果所需的输入值。

以下是使用单变量求解的步骤：

  1. 确保你的利润计算公式正确无误。
  2. 点击 数据 选项卡，在 预测 组中找到 模拟分析，然后选择 单变量求解。
  3. 在弹出的对话框中：
    • 目标单元格：选择包含利润计算公式的单元格。
    • 目标值：输入 0（因为我们要找盈亏平衡点）。
    • 可变单元格：选择存放响应率假设的单元格。
  4. 点击 确定。

Excel将进行计算，并弹出一个对话框显示已找到解。点击确定后，响应率单元格的值就会被更新为精确的盈亏平衡点（例如5.77%）。

总结与思考

本节课中我们一起学习了如何为一个商业案例构建完整的Excel财务模型。我们掌握了提取和整理输入参数、建立收入-成本-利润计算框架的方法。更重要的是，我们学会了两个高级分析工具：数据表用于批量模拟不同情景，单变量求解用于精确反向求解目标值。

最后，值得思考的是：模型的有用性如何受到不确定性的影响？我们的模型依赖于许多假设（如平均订单额、可变成本比例）。在现实中，这些参数都可能波动。一个稳健的决策不仅依赖于单一结果，还应考虑不同假设下的情景分析，这正是我们接下来可以深入探索的方向。

005：BN书店案例

📚 概述

在本节课中，我们将学习如何为一家名为BN的书店构建一个Excel模型，以确定订购新书的最佳数量。我们将处理不确定的需求、批量折扣以及剩余库存的降价销售问题。通过这个案例，我们将掌握几个关键的Excel功能。

构建模型框架

首先，我们需要根据问题描述，在Excel中搭建模型的基本框架。这包括输入已知参数和设置决策变量。

以下是需要输入的基本信息：

  • 售价：每本书的常规售价为 $30。
  • 折扣成本表：根据订购数量，每本书的成本会变化。这是一个阶梯式折扣。
  • 需求范围：预计需求在 500 到 4500 本之间。
  • 清仓价：平装本上市后，剩余的硬皮书将以 $10 的价格出售。

我们将“订购数量”和“需求数量”设置为可变的输入单元格，以便后续分析。

计算剩余库存（Surplus）

上一节我们介绍了模型的基本参数，本节中我们来看看如何计算可能剩余的库存数量。关键在于，我们不能让剩余库存出现负值。

剩余库存的计算逻辑是：用“订购数量”减去“需求数量”。但如果需求大于订购量，这个差值将为负数，这在实际库存中是不合理的。我们需要将这个负数修正为0。

以下是两种实现方法：

  1. 使用 MAX 函数：公式为 =MAX(订购数量 - 需求数量， 0)。这个函数会取两者中的较大值，如果差值为负，则返回0。
  2. 使用 IF 函数：公式为 =IF(需求数量 > 订购数量， 0， 订购数量 - 需求数量)。这个函数会先进行逻辑判断，如果需求大于订购，则返回0；否则返回实际差值。

两种方法结果相同，选择你更熟悉的一种即可。

使用VLOOKUP确定单位成本

接下来，我们需要根据订购数量，在折扣成本表中查找对应的单位成本。手动查找效率低下且容易出错，Excel的 VLOOKUP 函数可以完美解决这个问题。

VLOOKUP 函数用于在表格的首列查找指定的值，并返回该行中指定列的数据。其基本语法为：
=VLOOKUP(查找值， 表格区域， 返回列号， [近似匹配/精确匹配])

对于本例：

  • 查找值：我们的订购数量（例如3500）。
  • 表格区域：我们构建的折扣成本表（例如 $G$5:$H$9）。
  • 返回列号：我们需要“单位成本”，它在表格区域的第2列。
  • 匹配方式：由于我们的订购数量（如3500）可能不在成本表中精确列出，我们需要“近似匹配”（TRUE）。VLOOKUP 会找到小于或等于查找值的最大值所对应的成本。例如，查找3500会返回对应“至少3000本”的成本 $21.75。

因此，单位成本的公式为：=VLOOKUP(订购数量， 折扣成本表， 2， TRUE)。

计算收入与利润

现在，我们可以计算总成本、总收入，最终得出利润。

总成本 的计算相对直接：
总成本 = 单位成本（通过VLOOKUP获得） * 订购数量

总收入 的计算需要仔细考虑销售逻辑，它由两部分组成：

  1. 常规销售收入：我们能销售的数量是“需求数量”和“订购数量”中较小的那个，按常规价格出售。公式为：=MIN(需求数量， 订购数量) * 常规售价
  2. 清仓销售收入：将计算出的“剩余库存”按清仓价格出售。公式为：=剩余库存 * 清仓价

总收入 = 常规销售收入 + 清仓销售收入。

最后，利润 = 总收入 - 总成本。

创建双向数据表进行分析

模型构建完成后，我们需要回答核心问题：为了最大化利润，应该订购多少本书？这涉及到“需求”和“订购量”两个变量。双向数据表可以帮助我们快速模拟所有可能组合下的利润情况。

首先，我们创建一个表格，行标题是可能的需求值（500到4500），列标题是可能的订购量值（同样500到4500）。表格中间需要填充的是对应每个（需求，订购量）组合的利润值。

以下是创建双向数据表的步骤：

  1. 在表格的左上角单元格（即行标题和列标题交叉处），引用我们模型中计算出的“利润”单元格。这个单元格是数据表的“驱动源”。
  2. 选中整个数据表区域（包括标题和这个利润驱动单元格）。
  3. 点击【数据】选项卡下的【模拟分析】，选择【数据表】。
  4. 在弹出的对话框中：
    • “输入引用行的单元格”：选择模型中代表“需求数量”的单元格。因为数据表中的行标题是需求值。
    • “输入引用列的单元格”：选择模型中代表“订购数量”的单元格。因为数据表中的列标题是订购量值。
  5. 点击确定，Excel会自动填充整个表格，展示每种情景下的利润。

通过这个数据表，管理者可以一目了然地看到在不同需求预测下，最佳的订购决策是什么。例如，如果预测需求是2000本，那么沿着“需求2000”的那一行查找，利润最高的列对应的订购量就是最佳选择。

🎯 总结

本节课中我们一起学习了如何构建一个完整的商业分析模型来解决BN书店的订购决策问题。我们掌握了几个核心技能：使用 IF 或 MAX 函数处理逻辑计算；运用 VLOOKUP 函数实现基于折扣表的成本查找；以及最强大的工具——创建双向数据表，来对两个关键变量进行模拟分析，从而为决策提供直观的数据支持。这个案例综合运用了Excel的数据处理与建模能力，是商业分析中非常实用的技巧。

006：第19洞案例解析 🏌️♂️

在本节课中，我们将通过一个名为“第19洞”的高尔夫球杆销售案例，学习如何在Excel中进行更复杂的建模与分析。我们将使用散点图、趋势线拟合、误差评估以及数据表等工具，来确定能使利润最大化的最优定价策略。

数据可视化与初步观察

首先，我们需要理解数据。案例提供了一个价格与需求（以百为单位）的对应表格。价格越高，需求通常越低，这符合基本的经济学规律。

为了直观地观察价格与需求之间的关系，我们首先将数据绘制成散点图。

以下是操作步骤：

  1. 选中包含价格和需求的两列数据。
  2. 转到“插入”选项卡。
  3. 在“图表”组中，选择“散点图”。
  4. 可以添加图表标题（如“价格与需求关系”）以及X轴（价格，单位：美元）和Y轴（需求）的标题。

生成的图表显示数据点呈下降趋势，并且似乎存在一定的曲线形态，而非严格的直线。

拟合趋势线与选择模型

散点图可以帮助我们观察趋势，但要进行预测，我们需要一个数学函数来描述这种关系。Excel提供了多种趋势线（即模型）供我们拟合。

线性模型

线性模型是最简单的形式，其公式为：y = mx + b。其中，m是斜率，b是截距。

添加线性趋势线并显示其方程后，我们得到一组系数。但观察散点图，数据点有明显的弯曲，这表明线性模型可能不是最佳选择。

幂函数模型

幂函数模型的公式为：y = a * x^b。其中，变量x位于底数位置。

为数据添加幂函数趋势线并显示方程。你可能需要右键点击趋势线标签，选择“设置趋势线标签格式”，将数字格式调整为更易读的形式（例如，不使用科学计数法），以获取准确的a和b值。

指数函数模型

指数函数模型的公式为：y = a * e^(b*x)。其中，常数e（约2.718）为底数，变量x位于指数位置。

同样地，为数据添加指数趋势线并显示其方程。

现在，我们得到了三个候选模型：线性、幂函数和指数函数。关键在于如何判断哪个模型最能准确反映数据。

评估模型准确性：平均绝对百分比误差

为了科学地比较哪个模型预测效果最好，我们引入“平均绝对百分比误差”作为评估标准。误差越小，模型预测越准确。

以下是计算步骤：

  1. 在新的表格区域，列出原始价格数据。
  2. 分别使用三个模型的公式，计算在每个价格点上的“预测需求”。
    • 例如，对于幂函数模型，公式为：= $D$16 * (A21 ^ $D$17)。需要使用绝对引用（$）锁定模型参数单元格。
  3. 将“实际需求”数据粘贴到旁边。
  4. 为每个模型计算“绝对百分比误差”。
    • 公式为：= ABS(预测需求 - 实际需求) / 实际需求
    • 使用ABS函数确保误差为正数。将结果设置为百分比格式。
  5. 计算每个模型所有数据点误差的平均值，即得到“平均绝对百分比误差”。

比较结果发现，幂函数模型的平均误差最小。因此，我们选择幂函数模型作为后续利润分析的基础。

构建利润模型并寻找最优价格

确定了需求预测模型后，我们可以构建利润模型。已知每套球杆的生产成本为250美元。

利润模型的核心公式为：利润 = 收入 - 成本 = (销售价格 * 实际需求) - (单位成本 * 实际需求)

以下是建模步骤：

  1. 设定“单位成本”为250美元。
  2. 设定“销售价格”为一个初始值（例如400美元），这是我们将要调整的变量。
  3. 使用幂函数模型计算“预测需求”：= $D$16 * (销售价格 ^ $D$17)。
  4. 计算“实际需求”（单位：套）：= 预测需求 * 100。
  5. 计算“总成本”：= 实际需求 * 单位成本。
  6. 计算“总收入”：= 销售价格 * 实际需求。
  7. 计算“利润”：= 总收入 - 总成本。

手动更改销售价格，你会发现利润会随之变化，并且似乎存在一个最大值。

使用数据表进行敏感性分析

为了系统性地找出最大利润对应的价格，我们使用“数据表”功能。

单向数据表（分析价格对利润的影响）

  1. 在一列中列出你想要测试的一系列价格（例如，从260到600，间隔10美元）。
  2. 在相邻列的顶部单元格，引用你的利润计算结果单元格。
  3. 选中整个数据表区域（包括价格列和利润引用单元格）。
  4. 转到“数据”选项卡，点击“模拟分析”，选择“数据表”。
  5. 在“数据表”对话框中，因为价格列是垂直的，所以在“输入引用列的单元格”中选择你模型中“销售价格”所在的单元格。点击“确定”。
  6. Excel会自动填充每个价格对应的利润值。通过条件格式（如“等于最大值”）可以快速找到利润最高的价格。

分析表明，在单位成本为250美元时，定价为530美元能最大化利润（约104万美元）。

双向数据表（同时分析价格和成本对利润的影响）

如果单位成本发生变化，最优价格也会改变。双向数据表可以同时分析这两个变量。

  1. 将一列设置为不同的价格。
  2. 将一行设置为不同的单位成本（例如，200， 250， 300， ... 550）。
  3. 在价格列顶端、成本行左端的交叉单元格，引用利润计算结果。
  4. 选中整个数据表区域。
  5. 打开“数据表”对话框。
  6. “输入引用行的单元格”选择模型中“单位成本”所在单元格。
  7. “输入引用列的单元格”选择模型中“销售价格”所在单元格。点击“确定”。
  8. 表格将填充所有价格与成本组合下的利润。你可以为每一列（每个成本水平）应用条件格式，快速找到该成本下的最优价格。

从双向数据表可知，当生产成本上升时，最优销售价格也需要相应调整。

总结与思考

本节课中，我们一起完成了一个完整的数据驱动决策案例：

  1. 数据可视化：通过散点图观察价格与需求的关系。
  2. 模型拟合与选择：尝试了线性、幂函数、指数函数三种趋势线，并使用平均绝对百分比误差这一量化指标，选择了预测最准确的幂函数模型。
  3. 构建利润模型：基于选定的需求模型，建立了包含收入、成本和利润的计算框架。
  4. 优化分析：利用单向数据表找到了在固定成本下的最优定价。进一步使用双向数据表分析了单位成本变动时最优价格的动态变化。

这个模型的有效性建立在历史数据的准确性和所选幂函数模型的合理性之上。在实际应用中，可以思考是否有更复杂的模型（如多项式）能更好地拟合数据，或者是否需要考虑更多变量（如市场竞争、营销支出等）来使模型更贴近现实。通过这个案例，我们掌握了从数据分析到商业决策模拟的完整Excel技能链。

007： Elementary to Advanced

课程编号：P7 - 净现值示例：地鼠制药 📊

在本节课中，我们将学习如何使用Excel的NPV（净现值）函数，并基于NPV计算来决定一个产品是否值得投资。

概述

我们将通过一个名为“地鼠制药”的案例，模拟一家大型制药公司评估其新药“ES”是否值得继续开发。我们将根据给定的财务数据，在Excel中构建一个20年的现金流模型，并使用净现值法做出决策。

第一步：列出已知条件

首先，我们需要在Excel工作表中整理并输入所有给定的信息。

以下是已知条件列表：

  • 开发成本：930万美元，将于第1年年初发生。
  • 产品生命周期：20年。
  • 第一年毛利：120万美元。
  • 增长率：前8年，每年增长10%。
  • 下降率：第9年至第20年，每年下降5%。
  • 年贴现率：12%，用于计算净现值。

第二步：构建现金流模型

上一节我们列出了所有已知条件，本节中我们来看看如何构建未来20年的预期现金流表格。

我们创建一个表格，列标题为“年末”和“毛利（百万美元）”，行则从第1年填充到第20年。

以下是计算各年毛利的步骤：

  1. 第1年：直接输入给定的120万美元。
  2. 第2年至第8年（增长阶段）：使用公式 =上一年毛利 * (1 + 增长率)。例如，第2年的公式为 =C3*(1+$B$4)。注意：必须对增长率单元格（如$B$4）使用绝对引用，以确保公式向下拖动时引用正确。
  3. 第9年至第20年（下降阶段）：使用公式 =上一年毛利 * (1 - 下降率)。例如，第9年的公式为 =C10*(1-$B$5)。同样，需要对下降率单元格使用绝对引用。

第三步：优化模型灵活性

目前我们的模型将增长阶段固定为8年。为了使模型能灵活应对不同假设，我们可以使用 IF函数 来改进公式。

改进后的通用公式为：
=IF(当前年份 <= 增长截止年份, 上一年毛利*(1+增长率), 上一年毛利*(1-下降率))

例如，假设增长截止年份在单元格B6，则第2年的公式可以写为：
=IF(A4<=$B$6, C3*(1+$B$4), C3*(1-$B$5))

这样，只需修改B6单元格中的年份数字，整个现金流表就会自动重新计算，大大增强了模型的“假设分析”能力。

第四步：计算净现值并做出决策

在构建了完整的现金流模型后，我们现在需要使用Excel的NPV函数来计算该项目的净现值，并据此做出投资决策。

核心概念解析：

  • 净现值：将未来所有现金流以特定贴现率折算到当前时点的价值总和。正值通常表示项目能增加公司价值，值得投资；负值则表示项目会损害公司价值。
  • 重要提示：Excel中的 NPV 函数实际上计算的是一系列期末现金流的现值，而非“净”值。要得到真正的净现值，需要手动减去期初投资。

计算步骤如下：

  1. 计算未来现金流的现值：使用公式 =NPV(贴现率, 现金流范围)。在本例中，公式为 =NPV(B8, C3:C22)，其中B8是12%的贴现率，C3:C22是第1年至第20年的毛利。
  2. 计算真正的净现值：从上述现值中减去期初的开发成本。公式为 =NPV计算结果 - 开发成本。在本例中，即 =NPV(B8, C3:C22) - B3。

根据计算结果，如果净现值为正（例如255万美元），则意味着在考虑资金时间价值后，该项目能为公司创造额外价值，值得继续推进。反之，则应放弃。

总结

本节课中我们一起学习了如何运用Excel进行商业投资决策分析。

  1. 我们首先系统梳理了案例的所有给定条件。
  2. 接着，我们构建了一个动态的20年现金流预测模型，并利用IF函数增强了模型的灵活性。
  3. 最后，我们掌握了Excel NPV 函数的实际应用方法，理解了其计算的是现值而非净值，并通过“现值 - 初始投资”得到了最终的净现值，作为项目决策的依据。

通过这个案例，你不仅学会了NPV的计算，更重要的是掌握了如何用Excel构建一个可动态调整、用于辅助商业决策的财务模型。

008：营销活动优化 🎯

在本节课中，我们将学习如何在 Microsoft Excel 中使用线性规划模型，来决定广告的最佳分配方案，以最大化触达人数。

概述

我们将通过一个具体案例来学习线性规划在 Excel 中的应用。案例背景是：一位市长候选人拥有 40,000 美元的预算用于选举前的最后阶段广告投放，广告形式为广播和电视。我们的目标是找到购买广播广告和电视广告的最佳数量组合，在满足所有限制条件的前提下，最大化广告触达的总人数。

第一步：理解问题与设定变量

上一节我们介绍了案例背景，本节中我们来看看如何将问题转化为 Excel 模型。首先，我们需要明确三个核心要素：目标、决策变量和约束条件。

  • 目标：最大化触达的总人数。
  • 决策变量：需要购买的广播广告数量和电视广告数量。
  • 约束条件：总预算、每种广告的最低投放量要求等。

在 Excel 中，我们首先创建决策变量。为清晰起见，我们使用描述性名称并填入假设值，以便后续构建公式。

以下是决策变量的设置示例：

  • 广播广告数量：假设为 4
  • 电视广告数量：假设为 5

我们将这些决策变量的单元格标记为绿色。

第二步：构建目标函数

明确了决策变量后，我们需要构建目标函数，即计算总触达人数的公式。

每个广播广告可触达 3000 人，每个电视广告可触达 7000 人。因此，总触达人数的计算公式为：
总触达人数 = 3000 * 广播广告数量 + 7000 * 电视广告数量

在 Excel 中，我们可以使用 SUMPRODUCT 函数高效地计算这个值。我们将目标单元格（总触达人数）标记为灰色。

=SUMPRODUCT(广播广告数量单元格, 电视广告数量单元格), (3000, 7000))

第三步：定义约束条件

目标函数构建完成后，我们需要系统地定义所有限制条件。我们将以表格形式列出约束，包含三列：左侧（LHS）、符号 和 右侧（RHS）。

以下是本案例中的所有约束条件：

  1. 预算约束：广告总成本不能超过 40,000 美元。

    • 每个广播广告成本 200 美元，每个电视广告成本 500 美元。
    • 左侧公式：=SUMPRODUCT((广播广告数量, 电视广告数量), (200, 500))
    • 符号：<=
    • 右侧：40000

  2. 最低广播广告量：广播广告数量至少为 10 个。

    • 左侧公式：=SUMPRODUCT((广播广告数量, 电视广告数量), (1, 0)) （即直接引用广播广告数量）
    • 符号：>=
    • 右侧：10

  3. 最低电视广告量：电视广告数量至少为 10 个。

    • 左侧公式：=SUMPRODUCT((广播广告数量, 电视广告数量), (0, 1)) （即直接引用电视广告数量）
    • 符号：>=
    • 右侧：10

  4. 数量关系约束：广播广告数量必须至少等于电视广告数量。

    • 这个条件可以转化为：广播广告数量 - 电视广告数量 >= 0。
    • 左侧公式：=SUMPRODUCT((广播广告数量, 电视广告数量), (1, -1))
    • 符号：>=
    • 右侧：0

注意：所有约束的“左侧”都应使用 SUMPRODUCT 函数和相应的系数来构建公式，“符号”和“右侧”则直接输入数字或比较符。

第四步：使用 Excel 规划求解

设置好所有模型组件后，我们使用 Excel 的“规划求解”工具来找到最优解。

  1. 打开“数据”选项卡下的“规划求解”。
  2. 设置目标：选择灰色的总触达人数单元格，并选择“最大值”。
  3. 通过更改可变单元格：选择绿色的决策变量单元格（广播和电视广告数量）。
  4. 添加约束：根据第三步建立的约束表，逐个添加约束条件。
    • 点击“添加”，输入约束的左侧单元格引用、符号（<=, >=, =）和右侧单元格引用或数值。
  5. 选择求解方法：确保选择“单纯线性规划”。
  6. 求解：点击“求解”按钮。规划求解将运行并找到最优解。

第五步：解读结果与总结

规划求解完成后，我们得到了最优方案。

  • 最优决策：购买 175 个广播广告和 10 个电视广告。
  • 最大触达人数：595,000 人。

最后，我们应形成一个清晰的总结陈述，用于汇报：
“应购买 175 个广播广告和 10 个电视广告，以在满足所有约束条件的前提下，最大化触达人数至 595,000 人。”

我们可以验证此方案满足所有约束：总成本正好为 40,000 美元，两种广告数量均不低于 10，且广播广告数量大于电视广告数量。

总结

本节课中我们一起学习了在 Excel 中建立和求解线性规划模型的全过程。我们从理解问题、定义变量和目标函数开始，然后系统地列出了所有约束条件，并利用 Excel 的规划求解工具找到了最优广告分配方案。掌握这个基础模板是解决更复杂商业优化问题的关键第一步。记住，清晰的模型设置和结果解读同样重要。

009：线性规划进阶案例 - PC科技公司生产优化 📊

在本节课中，我们将学习一个更复杂的线性规划问题。我们将为PC科技公司建立一个模型，以决定两种电脑型号的最佳生产数量，目标是最大化净利润。这个问题结合了收入、成本和多种资源约束。

问题概述与建模准备

上一节我们介绍了线性规划的基本模板。本节中，我们来看看一个更贴近实际商业场景的案例。

首先，阅读问题时，我们需要明确三个核心部分：

  1. 目标：通常是最大化或最小化某个指标（如利润、成本）。
  2. 决策变量：管理者需要决定的未知数（如生产数量）。
  3. 约束条件：限制决策的各种条件（如资源上限、市场需求）。

以下是PC科技公司问题的关键信息梳理：

第一步：定义决策变量与初步约束

我们的目标是决定两种电脑型号的生产数量。因此，我们定义两个决策变量。

  • 决策变量：
    • B = 生产的Basic型号电脑数量
    • X = 生产的XP型号电脑数量

问题中立刻给出了两个市场需求约束：

  • 最多能销售600台Basic电脑。
  • 最多能销售1200台XP电脑。

我们在Excel中建立初步模板。绿色单元格存放决策变量，下方建立约束表。

约束表（部分）：

约束描述 LHS (公式) 符号 RHS
Basic电脑需求上限 =B <= 600
XP电脑需求上限 =X <= 1200

LHS的公式使用SUMPRODUCT(决策变量, 系数)。例如，对于Basic上限约束，系数为1（对应B）和0（对应X）。

第二步：构建目标函数——净利润计算

我们的目标是最大化净利润。净利润的基本公式是：

净利润 = 总收入 - 总成本

因此，我们需要分别计算总收入和总成本。

1. 总收入计算：

  • Basic电脑售价为 $300。
  • XP电脑售价为 $450。
    总收入公式为：总收入 = 300*B + 450*X
    在Excel中，使用 =SUMPRODUCT({B, X}, {300, 450})

2. 总成本计算：
成本分为两部分：零件成本和人工成本。

  • 零件成本：

    • Basic电脑零件成本为 $150。
    • XP电脑零件成本为 $225。
      总零件成本公式为：零件成本 = 150*B + 225*X

  • 人工成本：
    人工成本涉及组装和测试，且按小时计费。这需要两步计算。

    • 第一步：计算总工时
      • Basic电脑：组装需5小时，测试需1小时。
      • XP电脑：组装需6小时，测试需2小时。
      • 公司资源上限：最多10000组装工时，最多3000测试工时。
      • 总组装工时公式：组装工时 = 5*B + 6*X (需 <= 10000)
      • 总测试工时公式：测试工时 = 1*B + 2*X (需 <= 3000)
    • 第二步：将工时转化为成本
      • 组装工时费率：$11/小时
      • 测试工时费率：$15/小时
      • 总组装人工成本公式：组装成本 = (5*B + 6*X) * 11
      • 总测试人工成本公式：测试成本 = (1*B + 2*X) * 15

3. 完整的净利润公式：
将以上所有部分组合，得到最终的目标函数：
净利润 = (300*B + 450*X) - [(150*B + 225*X) + (5*B + 6*X)*11 + (1*B + 2*X)*15]

在Excel中，我们分别计算收入、零件成本、组装人工成本、测试人工成本，然后进行减法和汇总。

第三步：在Excel中建立完整模型并求解

现在，我们在Excel中整合所有信息。

  1. 设置决策变量：在单元格B2和C2中输入初始假设值（如4和5），用于测试公式。
  2. 建立收入与成本计算区域：分别计算总收入、总零件成本、总组装成本、总测试成本。
  3. 计算净利润：用总收入减去各项成本。
  4. 完善约束表：除了市场需求约束，添加工时约束。
    • 组装工时约束：5*B + 6*X <= 10000
    • 测试工时约束：1*B + 2*X <= 3000
  5. 使用规划求解：
    • 目标单元格：设为净利润单元格，选择最大值。
    • 可变单元格：选择决策变量单元格（B2, C2）。
    • 约束条件：添加所有四个约束（两个需求上限，两个工时上限）。
    • 选择求解方法：单纯线性规划。
    • 求解。

第四步：解读结果与报告

规划求解完成后，我们得到最优解。

  • 最优生产计划：
    • 生产 560台 Basic电脑。
    • 生产 1200台 XP电脑。
  • 最大净利润：$217,600。

我们需要撰写总结报告：
“为实现本月净利润最大化，PC科技公司应组装560台Basic电脑和1200台XP电脑。此计划可带来$217,600的最大净利润，且满足所有约束：Basic电脑产量未超过600台的市场需求上限，XP电脑产量达到1200台的市场需求上限，同时组装工时和测试工时均未超过可用资源上限。”

总结

本节课中，我们一起学习了一个整合收入与多类成本计算的线性规划进阶案例。关键步骤包括：

  1. 从问题描述中精准提取目标（最大化净利润）、变量（生产数量）和约束（市场需求、资源上限）。
  2. 将商业逻辑（净利润=收入-成本）转化为具体的数学公式。
  3. 在Excel中系统性地构建计算模型，注意区分直接成本（零件）和需要通过中间量（工时）计算的成本（人工）。
  4. 熟练使用规划求解工具，并学会批量添加约束以提高效率。
  5. 根据求解结果，用清晰的语言撰写商业决策建议。

通过这个案例，你将掌握处理更复杂、更贴近现实的线性规划问题的核心能力。

010：投资分配问题 🧮

在本节课中，我们将学习如何运用线性规划解决一个投资组合优化问题。我们将为一个客户分配25万美元的投资资金，目标是最大化总回报率，同时满足一系列投资指导方针。

问题概述与变量定义

上一节我们处理了包含多个部分的利润问题。本节中，我们来看看一个更复杂的投资分配问题。我们作为一家经纪公司，需要为客户分配25万美元到五种不同的投资产品中。

我们需要决定的变量是投入到每种产品中的金额。因此，我们定义五个决策变量：

  • x1 = 投入洛杉矶市政债券的金额
  • x2 = 投入汤普森电子的金额
  • x3 = 投入联合航空航天的金额
  • x4 = 投入帕尔默科技的金额
  • x5 = 投入HDN股票的金额

每种投资的年化回报率如下：

  • x1: 5.3%
  • x2: 6.8%
  • x3: 4.9%
  • x4: 8.4%
  • x5: 9.0%

我们的目标是最大化总回报金额。

建立目标函数

明确了变量后，我们来看看如何计算总回报。目标函数是我们要最大化的值，即总回报金额。

总回报金额的计算公式是每个投资金额与其回报率的乘积之和。在Excel中，我们可以使用SUMPRODUCT函数。

目标函数公式：
总回报 = SUMPRODUCT(投资金额区域, 回报率区域)

例如，如果投资金额在单元格B2:F2，回报率在B3:F3，则公式为：
=SUMPRODUCT($B$2:$F$2, $B$3:$F$3)

这个公式计算出的不是百分比，而是以美元计的实际回报金额，这正是我们要最大化的目标。

设定约束条件

接下来，我们需要根据客户的要求设定约束条件。以下是所有需要满足的限制。

1. 预算约束：
总投资金额不能超过客户提供的资金。
x1 + x2 + x3 + x4 + x5 <= 250,000

2. 市政债券比例约束：
市政债券（x1）的金额至少占总投资的20%。
x1 >= 20% * (x1 + x2 + x3 + x4 + x5)
可以整理为：
x1 >= 0.2 * 总投资

3. 科技股比例约束：
科技股（x2, x3, x4）的金额至少占总投资的40%。
x2 + x3 + x4 >= 40% * (x1 + x2 + x3 + x4 + x5)
可以整理为：
x2 + x3 + x4 >= 0.4 * 总投资

4. 高风险投资比例约束：
高风险投资（x5）的金额不得超过市政债券（x1）金额的50%。
x5 <= 50% * x1
可以整理为：
x5 - 0.5*x1 <= 0

5. 非负约束：
所有投资金额必须大于或等于零。
x1, x2, x3, x4, x5 >= 0

在Excel中配置求解器

现在，我们将在Excel中设置并运行求解器来找到最优解。

以下是配置求解器的关键步骤：

  1. 设置目标：选择包含SUMPRODUCT公式的单元格，并设置为“最大值”。
  2. 设置变量单元格：选择代表五个投资金额x1到x5的单元格区域。
  3. 添加约束：
    • 总投资和 <= 250,000
    • x1 >= 总投资 * 20%
    • x2 + x3 + x4 >= 总投资 * 40%
    • x5 - 0.5*x1 <= 0
    • 所有变量 >= 0
  4. 选择求解方法：选择“单纯线性规划”。
  5. 求解：点击“求解”按钮。

解读最优解

求解器运行完成后，会给出最优的投资分配方案。

根据求解结果，最优分配如下：

  • 洛杉矶市政债券 (x1)：$50,000
  • 汤普森电子 (x2)：$0
  • 联合航空航天 (x3)：$0
  • 帕尔默科技 (x4)：$175,000
  • HDN股票 (x5)：$25,000

这个方案完全满足了所有约束：

  • 总投资：$50,000 + $0 + $0 + $175,000 + $25,000 = $250,000 (用尽预算)。
  • 市政债券比例：$50,000 / $250,000 = 20% (恰好满足最低要求)。
  • 科技股比例：($0 + $0 + $175,000) / $250,000 = 70% (超过40%的要求)。
  • 高风险投资比例：$25,000 / $50,000 = 50% (恰好满足不超过50%的要求)。

在此方案下，预期总回报为 $20,300。任何试图手动调整以获取更高回报的尝试，都会至少违反上述约束条件之一。

总结

本节课中，我们一起学习了一个包含五个决策变量的投资组合线性规划问题。我们定义了变量x1到x5，建立了以最大化总回报金额为目标的目标函数，并成功地将客户的所有文字要求（预算、最低比例、最高比例）转化为数学约束条件。最后，我们使用Excel求解器找到了在满足所有限制条件下的最优资金分配方案，并学会了如何解读和验证这个方案。通过这个案例，我们看到了线性规划在复杂资源分配问题中的强大应用。

011： Elementary to Advanced

课程编号：P11 - 公交问题 🚌

在本节课中，我们将学习如何运用线性规划解决一个复杂的实际问题——公交问题。我们将通过约翰霍普金斯大学在巴尔的摩市设立三所特许学校的案例，学习如何建模、设置变量、定义约束并最终使用Excel Solver找到最优解。

问题概述与建模思路

上一节我们介绍了线性规划的基本框架。本节中，我们来看看一个更复杂的“公交问题”。

巴尔的摩市被划分为A、B、C、D、E五个社区。有三所新高中：Koko高中（位于B区）、Devon高中（位于C区）、Manny高中（位于E区）。每所学校容量为900名学生。现有来自五个社区的学生需要分配到这些学校，目标是最小化所有学生乘坐公交车的总里程数。如果一个学生被分配到其所在社区的高中，他可以步行，不产生公交里程。

以下是各社区到各学校的距离（英里）及学生人数：

社区 到Koko高中(B) 到Devon高中(C) 到Manny高中(E) 学生人数
A 5 8 6 700
B 0 4 12 500
C 4 0 7 100
D 7 2 5 800
E 12 7 0 400

我们的任务是：在满足学校容量和学生全部分配的前提下，找到一种分配方案，使得需要乘坐公交的学生们的总行驶里程最小。

第一步：定义决策变量

解决任何线性规划问题的第一步都是定义决策变量。对于此问题，我们需要决定从每个社区分配到每所学校的具体学生人数。

为了避免冗长的描述，我们采用简洁的命名方式。例如，变量 AB 代表从社区A分配到B区Koko高中的学生人数。同理，AC 代表从社区A分配到C区Devon高中的学生人数，以此类推。

由于有5个社区和3所学校，我们总共需要 5 × 3 = 15 个决策变量。在Excel中，我们将这些变量单元格标记为绿色。

以下是所有15个决策变量的列表：

  • AB, AC, AE （从社区A分配到三所学校）
  • BB, BC, BE （从社区B分配到三所学校）
  • CB, CC, CE （从社区C分配到三所学校）
  • DB, DC, DE （从社区D分配到三所学校）
  • EB, EC, EE （从社区E分配到三所学校）

我们可以先在变量单元格中输入一些测试值（例如1，2，3），以确保后续公式能正确计算。

第二步：设定目标函数

我们的目标是最小化学生公交总里程。目标函数是每个决策变量（学生人数）与其对应出行距离的乘积之和。

首先，我们需要一个与决策变量顺序完全对应的“距离”表。该表数据直接来源于问题中的表格。例如，AB的距离是5，AC的距离是8，AE的距离是6。特别注意，分配到本社区学校（如BB， CC， EE）的距离为0，因为这些学生步行。

计算总里程时，我们使用Excel的 SUMPRODUCT 函数。公式如下：
=SUMPRODUCT(决策变量区域, 对应距离区域)
我们将目标单元格标记为灰色。

第三步：确定约束条件

约束条件确保我们的解决方案是可行的。主要有两类约束：

1. 学校容量约束： 每所学校接收的学生总数不能超过900人。

  • 对于B区的Koko高中：AB + BB + CB + DB + EB <= 900
  • 对于C区的Devon高中：AC + BC + CC + DC + EC <= 900
  • 对于E区的Manny高中：AE + BE + CE + DE + EE <= 900

在Excel中，我们可以使用SUMPRODUCT函数配合一个“选择器”数组（由0和1组成）来方便地汇总分配到某所学校的所有学生。例如，对于Koko高中（B），选择器数组在AB, BB, CB, DB, EB下方为1，在其他变量下方为0。

2. 学生分配约束： 每个社区的所有学生必须被全部分配出去。

  • 社区A：AB + AC + AE = 700
  • 社区B：BB + BC + BE = 500
  • 社区C：CB + CC + CE = 100
  • 社区D：DB + DC + DE = 800
  • 社区E：EB + EC + EE = 400

注意，这里使用的是等号（=），因为必须恰好分配完所有学生。

第四步：使用Excel Solver求解

设置好变量、目标函数和约束表后，我们就可以使用Solver工具了。

  1. 打开 数据 选项卡下的 Solver。
  2. 设置目标： 选择灰色的总里程单元格，并选择 “最小值”。
  3. 通过更改可变单元格： 选择所有15个绿色的决策变量单元格。
  4. 添加约束：
    • 添加学校容量约束：三个汇总单元格 <= 900。
    • 添加学生分配约束：五个社区的学生分配汇总单元格 = 各自的学生总数（700， 500， 100， 800， 400）。
  5. 选择 “使无约束变量为非负数”。
  6. 选择求解方法为 “单纯线性规划”。
  7. 点击 “求解”。

Solver将快速计算出最优解。在本案例中，最优方案能实现最小化公交总里程。

结果解读与总结

本节课中，我们一起学习了如何解决“公交问题”。Solver给出的最优解显示，最小化的学生公交总里程为 5400 学生-英里。

通过检查解出的变量值，我们可以理解方案的逻辑：例如，B社区的所有500名学生都被分配到了本社区的Koko高中（BB=500），因为他们步行即可，不产生公交成本。其他社区的学生则按照距离和学校容量进行最优分配。

这个案例展示了线性规划在处理复杂资源分配问题上的强大能力。关键在于准确地将现实问题转化为数学模型：定义清晰的变量、建立正确的目标函数、并列出所有约束条件。一旦模型在Excel中搭建完成，Solver就能高效地找到人力难以计算的最优解。

记住，Solver的结果质量完全取决于你建立的模型。一个清晰、可读、结构良好的电子表格模型，是进行分析和与他人沟通的基础。

012：运输问题Excel模板 🚚

在本节课中，我们将学习一种特殊的线性规划问题——运输问题。我们将了解其核心概念，并使用Excel模板和Solver工具来寻找最优解决方案。

概述

运输问题是线性规划的一个子集，其目标是找到从多个供应源（如工厂）向多个目的地（如零售中心）配送货物的最佳方式，以最小化成本、时间或距离。我们将通过一个具体示例，学习如何在Excel中构建模型并求解。

运输问题简介

上一节我们介绍了线性规划的基本概念。本节中，我们来看看一种特殊类型的线性规划问题——运输问题。

运输问题旨在寻找从多个供应源向多个目的地配送货物的最优方案。其核心要素包括：

  • 目标：通常是最小化总成本（也可以是时间或距离）。
  • 决策变量：从每个供应源运送到每个目的地的货物数量。
  • 约束条件：
    1. 从每个供应源运出的货物总量不能超过其供应能力。
    2. 运送到每个目的地的货物总量必须等于其需求量。

示例问题描述

我们通过一个例子来具体说明。假设一家公司有三家工厂和三家商店。

供应源（工厂）及其产能：

  • 得梅因工厂：100单位
  • 埃文斯顿工厂：300单位
  • 劳德代尔堡工厂：300单位

目的地（商店）及其需求：

  • 奥尔巴尼商店：300单位
  • 波士顿商店：200单位
  • 克利夫兰商店：200单位

单位运输成本表（美元/单位）：

从 \ 到 奥尔巴尼 波士顿 克利夫兰
得梅因 5 4 3
埃文斯顿 8 4 3
劳德代尔堡 9 7 5

我们的目标是：确定从每家工厂向每家商店运送多少货物，才能在满足所有商店需求且不超过工厂产能的前提下，最小化总运输成本。

在Excel中构建运输模型

以下是构建运输问题Excel模板的步骤。

第一步：创建数据表

首先，将所有已知信息整理到一个清晰的表格中。

  1. 在左侧列列出所有供应源（工厂）。
  2. 在顶部行列出所有目的地（商店）。
  3. 在表格主体中填入对应的单位运输成本。
  4. 在供应源右侧添加一列，标明每个工厂的产能（供应量）。
  5. 在目的地下方添加一行，标明每个商店的需求量。

这样，我们就得到了一个包含所有给定数据的完整表格。

第二步：创建运输计划表（决策变量）

接下来，我们需要创建第二个表格，用于放置Solver将要计算的决策变量——即具体的运输数量。

  1. 复制第一步中数据表的格式（标题行和列），但清空成本、产能和需求数据的单元格。
  2. 这个新表格的每个单元格将代表从一个特定工厂运往一个特定商店的货物数量。这些就是我们的决策变量，初始可以填入一些假设值（如0）以便测试公式。
  3. 在决策变量表格的右侧，添加一列用于计算从每个工厂运出的总货物量。公式为对每一行求和：
    =SUM(决策变量行范围)
    例如，对于得梅因工厂：=SUM(C12:E12)
  4. 在决策变量表格的下方，添加一行用于计算运送到每个商店的总货物量。公式为对每一列求和：
    =SUM(决策变量列范围)
    例如，对于奥尔巴尼商店：=SUM(C12:C14)

第三步：设定目标函数（总成本）

我们的目标是最小化总运输成本。总成本的计算方法是：将每个运输路径的单位成本乘以运输数量，然后对所有路径求和。

这可以通过Excel的SUMPRODUCT函数轻松实现：
=SUMPRODUCT(单位成本数据表范围， 决策变量表范围)

例如，如果单位成本表在C5:E7，决策变量表在C12:E14，则总成本公式为：
=SUMPRODUCT(C5:E7, C12:E14)

使用Solver求解

模型构建完成后，即可使用Excel的Solver插件进行求解。

  1. 打开 数据 选项卡下的 Solver。
  2. 设置目标：选择总成本所在的单元格，并选择 最小值。
  3. 通过更改可变单元格：选择决策变量表的所有单元格（即C12:E14）。
  4. 添加约束：
    • 供应约束：每个工厂的“实际运出总量”（F12:F14）必须 小于等于 其“产能”（G5:G7）。
      $F$12:$F$14 <= $G$5:$G$7
    • 需求约束：每个商店的“实际送达总量”（C15:E15）必须 等于 其“需求量”（C8:E8）。
      $C$15:$E$15 = $C$8:$E$8
  5. 选择求解方法：选择 单纯线性规划。
  6. 点击 求解。

Solver将计算出最优的运输方案。在我们的示例中，最优解为：

  • 从得梅因运100单位至奥尔巴尼。
  • 从埃文斯顿运200单位至波士顿，100单位至克利夫兰。
  • 从劳德代尔堡运200单位至奥尔巴尼，100单位至克利夫兰。

此时，最小总成本为3900美元。

总结

本节课中，我们一起学习了如何用Excel解决运输问题。

我们首先了解了运输问题是线性规划的一个特定子类，其目标是优化从多个供应点到多个需求点的货物配送。接着，我们通过一个具体示例，逐步演示了在Excel中构建运输模型的关键步骤：创建数据表、建立包含决策变量的运输计划表、设置目标函数（总成本）以及添加供应与需求约束。最后，我们使用Solver工具求出了最小化总成本的最优运输方案。

掌握这个模板后，你就能高效解决类似的资源分配和物流优化问题。

013：替代Excel模板 📊

概述

在本节课中，我们将学习一种替代的Excel建模方法来解决运输问题。上一节我们介绍了使用“表格法”解决运输问题的第一个例子，本节中我们来看看另一种布局方式。这种布局在某些情况下更清晰、更透明，尤其适合处理较小规模的问题。

数据回顾

首先，让我们回顾一下上一问题中的输入数据。

我们有三家工厂供应产品：得梅因、埃文斯顿和劳德代尔堡。我们有位于奥尔巴尼、波士顿和克利夫兰的商店。所有运输成本均已提供。

以下是给定的运输成本：

  • 得梅因到奥尔巴尼、波士顿、克利夫兰：5美元、4美元、3美元
  • 埃文斯顿到奥尔巴尼、波士顿、克利夫兰：8美元、4美元、3美元
  • 劳德代尔堡到奥尔巴尼、波士顿、克利夫兰：9美元、7美元、5美元

工厂的产出能力有限：

  • 得梅因：100单位
  • 埃文斯顿：300单位
  • 劳德代尔堡：300单位

商店的需求如下：

  • 奥尔巴尼：300单位
  • 波士顿：200单位
  • 克利夫兰：200单位

我们的目标是最小化总成本。约束条件是：发货量不能超过工厂的生产能力，并且必须满足商店的需求。

创建替代布局

现在，让我们创建一个替代的运输问题布局。这种布局我认为更清晰。

以下是构建步骤：

首先，列出所有可能的“起点-终点”组合。与之前每个工厂只列出一行不同，现在我们将每个工厂针对其能发货到的每个目的地都单独列出一行。

得梅因 -> 奥尔巴尼
得梅因 -> 波士顿
得梅因 -> 克利夫兰
埃文斯顿 -> 奥尔巴尼
埃文斯顿 -> 波士顿
埃文斯顿 -> 克利夫兰
劳德代尔堡 -> 奥尔巴尼
劳德代尔堡 -> 波士顿
劳德代尔堡 -> 克利夫兰

接着，为每一行填入对应的目的地、单位成本和决策变量（发货量）。

构建Excel模型

在Excel中，我们将按照上述思路构建模型。

1. 基础数据表

我们创建一个表格，包含以下列：起点（工厂）、终点（商店）、单位成本（给定数据）、发货量（决策变量，用绿色标记）。

2. 供应约束（工厂产能）

我们需要跟踪从每个工厂实际发出的总量（流出量），并确保其不超过工厂的产能。

以下是计算每个工厂总流出量的方法。我们可以使用 SUMIF 函数。

=SUMIF($A$4:$A$12, "得梅因", $D$4:$D$12)
  • 公式解释：在A4:A12区域中查找“得梅因”，并对对应的D4:D12区域（发货量）进行求和。

将此公式应用于每个工厂，即可计算出各自的流出量。然后，我们设置约束：流出量 ≤ 工厂产能。

3. 需求约束（商店需求）

同样，我们需要跟踪运送到每个商店的总量（流入量），并确保其满足商店的需求。

计算每个商店总流入量的公式如下：

=SUMIF($B$4:$B$12, "奥尔巴尼", $D$4:$D$12)
  • 公式解释：在B4:B12区域中查找“奥尔巴尼”，并对对应的D4:D12区域（发货量）进行求和。

将此公式应用于每个商店，即可计算出各自的流入量。然后，我们设置约束：流入量 ≥ 商店需求（或 = 需求，因为过量发货不经济）。

4. 目标函数（总成本）

我们的目标是最小化总运输成本。总成本的计算方法是：每个路线的单位成本乘以该路线的发货量，然后对所有路线求和。

这可以通过 SUMPRODUCT 函数轻松实现。

=SUMPRODUCT(C4:C12, D4:D12)
  • 公式解释：将C列（单位成本）与D列（发货量）对应单元格相乘，然后求和。

使用规划求解

模型构建完成后，即可使用Excel的“规划求解”工具寻找最优解。

  1. 打开“数据”选项卡下的“规划求解”。
  2. 设置目标：选择总成本单元格，并选择“最小值”。
  3. 通过更改可变单元格：选择所有发货量（绿色）单元格。
  4. 添加约束：
    • 工厂流出量 ≤ 工厂产能
    • 商店流入量 ≥ 商店需求
  5. 选择求解方法：选择“单纯线性规划”。
  6. 点击“求解”。

规划求解将找到一组发货量，在满足所有供应和需求约束的同时，使总成本最小化。本例中，最优总成本应为 3900美元，与使用表格法得到的结果一致。

总结

本节课中我们一起学习了运输问题的另一种Excel建模布局。这种方法通过列出所有可能的运输路线，并使用 SUMIF 函数汇总流量，使得模型结构更加线性化，与常规的约束表格形式更匹配。虽然对于大型问题可能不够简洁，但对于中小型问题，它提供了清晰且可扩展的解决方案。你可以根据问题的复杂度和个人偏好，选择最适合的建模方法。

014： Elementary to Advanced

课程编号：P14

章节标题：🚀 新员工分配问题

在本节课中，我们将要学习一种特殊的运输问题——分配问题。我们将了解其核心概念，并学习如何在Excel中建立模型来求解，目标是实现成本最小化。

上一节我们介绍了标准的运输问题，本节中我们来看看它的一个特殊变体：分配问题。分配问题，顾名思义，就是如何将一组资源（如员工）最优地分配给另一组任务（如办公室）。它与标准运输问题的核心区别在于，每个资源的供应量和每个任务的需求量都恰好为1。

问题描述

想象你身处人力资源部门。公司新招聘了三名员工：Jones、Smith和Wilson。你需要将他们分配到四个办公室之一：Omaha、Dallas、New York或Miami。每个办公室只能接收一名新员工，每名新员工也只能被分配到一个办公室。公司为每位员工的搬迁支付费用，具体成本如下表所示：

员工 \ 办公室 Omaha Dallas New York Miami
Jones $800 $1100 $1200 $1100
Smith $500 $1600 $1300 $1400
Wilson $500 $1000 $1400 $1500

我们的目标是找到一种分配方案，使得公司的总搬迁成本最低。

建立Excel模型

我们将重建运输问题模板来适应分配问题。关键区别在于，我们省略了成本表旁边的“供应量”和“需求量”包装列，因为它们隐含为1。

以下是构建模型的步骤：

  1. 创建分配表：

    • 将成本表复制到工作表中，作为我们的“分配表”。
    • 清空成本数值中间的单元格区域，这将成为我们的决策变量区域。每个单元格的值代表“是否将对应员工分配到对应办公室”，其中 1 表示“是”，0 或空白表示“否”。

  2. 添加行与列汇总：

    • 在决策变量区域的右侧，为每一行（每位员工）添加一个“行合计”列。公式为 =SUM(该行决策变量区域)。例如，对于Jones所在行：=SUM(B5:E5)。
    • 在决策变量区域的下方，为每一列（每个办公室）添加一个“列合计”行。公式为 =SUM(该列决策变量区域)。例如，对于Omaha所在列：=SUM(B5:B7)。

  3. 设置约束条件：

    • 员工约束：每位员工必须且只能被分配到一个办公室。因此，每个“行合计”必须等于1。公式表示为：行合计 = 1。
    • 办公室约束：每个办公室最多只能接收一名员工（因为员工少于办公室）。因此，每个“列合计”必须小于或等于1。公式表示为：列合计 <= 1。

  4. 计算目标函数（总成本）：

    • 总搬迁成本是成本表中每个成本与其对应决策变量（0或1）乘积的总和。
    • 我们可以使用 SUMPRODUCT 函数来计算。假设成本表在区域 B2:E4，决策变量在 B5:E7，则总成本公式为：
      =SUMPRODUCT(B2:E4, B5:E7)
    • 将此单元格标记为灰色，作为我们的目标单元格。

使用Solver求解

模型建立完成后，我们使用Excel的Solver插件来寻找最优解。

以下是配置Solver的步骤：

  1. 打开“数据”选项卡，点击“规划求解”。
  2. 设置目标：选择我们计算总成本的单元格，并选择“最小值”。
  3. 通过更改可变单元格：选择决策变量区域 B5:E7。
  4. 添加约束：
    • 添加约束：$F$5:$F$7 = 1 （行合计等于1，确保每位员工都被分配）。
    • 添加约束：$B$8:$E$8 <= 1 （列合计小于等于1，确保每个办公室最多一人）。
    • 添加约束：$B$5:$E$7 = bin 或 $B$5:$E$7 <= 1 且 $B$5:$E$7 >= 0 并选择“使无约束变量为非负数”。对于分配问题，决策变量应为0或1整数，因此更严格的做法是添加 $B$5:$E$7 = bin （二进制）约束。
  5. 选择求解方法：选择“单纯线性规划”。
  6. 点击“求解”。

Solver将计算并给出最优分配方案。

解读结果

假设Solver给出的最优解如下：

员工 \ 办公室 Omaha Dallas New York Miami 行合计
Jones 0 0 0 1 1
Smith 0 0 1 0 1
Wilson 1 0 0 0 1
列合计 1 0 1 1

总成本：$2400

我们需要将Solver输出的数字结果转化为清晰的业务语言：

最优分配方案是：将 Wilson 分配到 Omaha 办公室，将 Smith 分配到 New York 办公室，将 Jones 分配到 Miami 办公室。这样，总搬迁成本最低，为 $2400。

这个总结句至关重要，因为Excel不会自动解释结果，你需要向你的经理或客户清晰地传达这个结论。

核心要点总结

本节课中我们一起学习了分配问题的建模与求解：

  1. 分配问题是运输问题的一个特例，其特点是供应量和需求量均为1。
  2. 在Excel建模时，无需在成本表旁列出显式的供应/需求包装列（即全为1的列），这是与标准运输模板的主要区别。
  3. 模型的核心是决策变量区域（0或1），以及两个关键约束：
    • 每个资源的行合计 = 1。
    • 每个任务的列合计 <= 1。
  4. 目标函数是成本与决策变量的 SUMPRODUCT，目标是使其最小化。
  5. 必须使用Solver求解，并记得将结果转化为易于理解的业务总结。

通过这个练习，你掌握了如何使用线性规划工具解决经典的资源分配优化问题。

015：MLB裁判分配问题 🏟️⚾

在本节课中，我们将学习如何解决一个带有特殊约束的分配问题。我们将使用Excel的Solver工具，为美国职业棒球大联盟（MLB）的裁判组分配比赛城市，目标是最小化总旅行距离，同时遵守“奥克兰组不能前往多伦多”的规则。

问题概述

我们的任务是：将四个裁判组（目前位于西雅图、阿灵顿、奥克兰和巴尔的摩）分配到四个城市（堪萨斯城、芝加哥、底特律和多伦多）进行下一场比赛。

核心规则如下：

  • 每个城市只能分配一个裁判组。
  • 每个裁判组只能被分配到一个城市。
  • 目标是最小化所有裁判组的总旅行距离（英里）。
  • 有一个特殊约束：不允许将奥克兰的裁判组派往多伦多。

我们得到了各城市之间的距离表，其中奥克兰到多伦多的距离被标记为“X”，表示此路线被禁止。

第一步：建立模型框架

首先，我们需要在Excel中搭建求解模型。这包括定义决策变量、设置约束条件和目标函数。

以下是构建模型的关键步骤：

  1. 复制成本表：将提供的城市间距离表复制到工作表中。
  2. 创建决策变量区域：再次复制该表格，但清空其中的距离数值。将这个区域的单元格填充为绿色，它们将代表我们的决策（分配方案）。1 表示“是”（派遣该组），0 或空白表示“否”。
  3. 计算行总和与列总和：
    • 在决策变量区域右侧添加“总计”列，对每一行求和。这用于确保每个裁判组只被分配一次（每行总和应等于1）。
    • 在决策变量区域下方添加“总计”行，对每一列求和。这用于确保每个城市只接收一个裁判组（每列总和应等于1）。
// 示例：计算西雅图裁判组被分配次数的公式（假设决策变量在B2:E5）
=SUM(B2:E2)
// 示例：计算被派往堪萨斯城的裁判组数量的公式
=SUM(B2:B5)
  1. 设置约束条件：在行总计和列总计旁边，明确写出约束值（即数字 1）。这将用于后续在Solver中设置约束。
  2. 定义目标函数：我们需要计算基于当前分配方案的总旅行距离。使用 SUMPRODUCT 函数将距离表中的每个距离与其对应的决策变量（0或1）相乘并求和。
// 示例：计算总距离的公式（假设距离表在B10:E13，决策变量在B2:E5）
=SUMPRODUCT(B10:E13, B2:E5)

第二步：配置并运行Solver

模型框架搭建完成后，我们就可以使用Excel的Solver插件来寻找最优解了。

打开Solver，按以下步骤进行设置：

  1. 设置目标：选择我们计算总距离的单元格，并选择“最小值”。
  2. 设置可变单元格：选择整个绿色的决策变量区域。
  3. 添加约束：
    • 每个裁判组分配一次：添加约束，令所有“行总计”单元格 等于 其旁边的约束值 1。
    • 每个城市接收一组：添加约束，令所有“列总计”单元格 等于 其旁边的约束值 1。
    • 处理特殊约束“X”：这是关键一步。仅仅在距离表中写“X”是不够的，Solver会将其视为0，从而错误地选择这条低成本路径。我们必须明确添加约束：禁止奥克兰到多伦多对应的决策变量等于1。即，找到代表“奥克兰->多伦多”分配的那个绿色单元格，添加约束令其 等于 0。
  4. 选择求解方法：选择“单纯线性规划”。
  5. 求解：点击“求解”按钮，让Solver计算最优分配方案。

第三步：解读结果与陷阱分析

Solver运行完毕后，会给出一个分配方案和对应的最小总距离。

重要陷阱：如果你在配置Solver时，没有为“奥克兰->多伦多”的决策变量添加 =0 的约束，Solver可能会给出一个看似更优的解（例如总距离2590英里）。在这个解中，奥克兰组很可能被分配去了多伦多，因为Solver将“X”当作零成本处理了。这是一个错误的解，因为它违反了问题给定的硬性约束。

正确的做法是明确添加该约束。重新运行Solver后，你会得到一个新的、遵守所有规则的最优解（例如总距离4580英里）。请检查最终方案，确保：

  • 每个裁判组所在的行只有一个 1。
  • 每个城市所在的列只有一个 1。
  • 奥克兰裁判组没有被分配去多伦多。

总结

本节课中，我们一起学习并解决了一个带有额外禁止条件的分配问题。我们回顾了在Excel中建立分配模型的基本步骤：定义决策变量、设置行列约束、构建目标函数。更重要的是，我们遇到了一个常见陷阱——如何处理非数值的禁止条件（如“X”）。解决方法是将其转化为明确的Solver约束（令特定决策变量 = 0），而不是依赖成本表中的标记。通过这个练习，我们掌握了处理更复杂、更贴近实际场景的优化问题的关键技巧。

016：整数规划 🧮

在本节课中，我们将学习整数规划。这是一种特殊的线性规划，其决策变量必须为整数。我们将通过一个实际案例来掌握如何设置和求解整数规划问题，并了解常见的错误做法。

概述

整数规划是线性规划的一个分支，其核心要求是所有决策变量必须为整数（即0, 1, 2, 3等，不能是1.5或2.7这样的小数）。虽然求解器会处理背后的复杂计算，但我们必须正确设置模型，特别是要明确添加整数约束，而不能简单地四舍五入线性规划的解。

问题描述：公主新娘广告投放

莉兹·贝利是“公主新娘”公司的所有者，她使用广播进行营销。有两种广告时段可供选择：黄金时段和非黄金时段。我们的目标是，在预算和广告数量限制下，最大化广告触达的总人数。

已知信息如下：

  • 每个黄金时段广告成本为 $390，可触达 8,200 人。
  • 每个非黄金时段广告成本为 $240，可触达 5,100 人。
  • 总预算为 $1,800。
  • 至少需要购买 2 个黄金时段广告。
  • 非黄金时段广告不能超过 6 个。
  • 隐含条件：广告数量必须是整数（不能购买半个广告）。

建立电子表格模型

首先，我们按照线性规划的通用模板来搭建模型。

1. 定义决策变量

我们在B列和C列分别定义两种广告的购买数量，并用绿色高亮标记，这是我们需要求解的未知数。

A B C
1 变量类型 Peak Off Peak
2 决策变量

2. 输入模型系数

接下来，我们输入目标函数和约束条件的系数。

A B C
3 成本 (约束) 390 240
4 触达人数 (目标) 8200 5100

3. 设置约束表

我们在决策变量右侧（通常空三列）建立约束表。约束表的左侧（LHS）是公式，右侧（RHS）是常数。

D E F G
5 LHS 符号 RHS 约束描述
6 =SUMPRODUCT($B$2:$C$2, B3:C3) <= 1800 预算约束
7 =SUMPRODUCT($B$2:$C$2, B4:C4) >= 2 至少2个黄金时段广告
8 =SUMPRODUCT($B$2:$C$2, B5:C5) <= 6 非黄金时段广告不超过6个

注意：上表中第7行和第8行的系数需要单独列出。在电子表格中，它们通常位于第4行和第5行（成本行下方），对应关系为：

  • 对于“至少2个黄金时段”约束：Peak系数为1，Off Peak系数为0（或留空）。
  • 对于“非黄金时段不超过6个”约束：Peak系数为0，Off Peak系数为1。

4. 设置目标函数

总触达人数是我们的目标，需要最大化。其计算公式为：
=SUMPRODUCT(B2:C2, B4:C4)
我们将此单元格标记为“总触达人数”并用灰色高亮。

求解线性规划（错误示范）

在上一节我们建立了完整的线性规划模型，本节中我们先来看看如果忽略整数约束，直接用单纯形法求解会发生什么。

  1. 打开数据选项卡下的规划求解。
  2. 设置目标单元格为总触达人数（灰色单元格），选择最大值。
  3. 设置可变单元格为决策变量（绿色单元格）。
  4. 添加所有约束：
    • 预算约束：LHS <= 1800
    • 黄金时段数量约束：LHS >= 2
    • 非黄金时段数量约束：LHS <= 6
  5. 选择求解方法为单纯线性规划，然后点击求解。

求解器会给出一个解。然而，这个解很可能是：

  • 购买 2 个黄金时段广告。
  • 购买 4.25 个非黄金时段广告。
  • 最大触达人数为 38,075 人。

这显然是不合理的，因为我们无法购买0.25个广告。一个常见的错误是直接对这个解进行四舍五入。如果我们四舍五入为（2， 4），总触达人数为 2*8200 + 4*5100 = 36,800 人。但这不是最优解。

正确方法：添加整数约束

现在，我们来学习如何正确地求解整数规划问题。关键步骤是为决策变量添加“整数”约束。

以下是添加整数约束的步骤：

  1. 再次打开规划求解参数对话框。
  2. 点击添加按钮，添加新约束。
  3. 在“单元格引用”区域，选择我们的决策变量单元格（B2:C2）。
  4. 在中间的下拉菜单中，选择 int（代表整数）。
  5. 此时，右侧的约束值会自动变为“整数”。点击确定。

现在，约束列表中会出现“$B$2:$C$2 = 整数”这一项。同时，请确保在选项中，“忽略整数约束”复选框没有被勾选。

添加整数约束后，再次点击求解。求解器将运行不同的算法来寻找整数解。

结果解读与总结

正确添加整数约束后，求解器给出的最优解是：

  • 购买 4 个黄金时段广告。
  • 购买 1 个非黄金时段广告。
  • 最大触达人数为 4*8200 + 1*5100 = **37,900** 人。

这个解（4， 1）的总触达人数为37，900，比四舍五入得到的解（2， 4）的36，800人多出1，100人。这清晰地证明了在整数规划问题中，对线性规划解进行四舍五入无法得到最优解。

本节课中我们一起学习了整数规划的核心概念和求解步骤。关键要点是：当问题要求决策变量为整数时，必须在规划求解中明确添加“整数”约束，而不能依赖对连续变量解的简单取整。在后续的问题中，请注意识别是否需要应用整数规划。

017：混合整数规划示例 - 咖啡交易所 ☕️

在本节课中，我们将学习一个混合整数线性规划（Mixed Integer Linear Programming）的示例。我们将通过一个咖啡店的案例，了解如何将整数约束条件整合到线性规划问题中，并使用Excel Solver找到最优解。

问题概述

我们是一家名为“Baled”的咖啡馆的顾问。该店使用三种咖啡豆（A、B、C）制作并销售两种产品：

  1. 预包装的20磅混合咖啡豆整袋。
  2. 按磅零售的研磨咖啡豆。

我们的目标是确定两种产品的最佳生产组合，以最大化总利润，同时考虑咖啡豆的供应限制和产品的特殊要求。

第一步：定义变量与目标

首先，我们需要明确决策变量和目标函数。

我们的决策变量是：

  • x1：销售的20磅整袋数量。
  • x2：销售的研磨咖啡豆磅数。

我们的目标是最大化总利润。根据问题描述：

  • 每袋20磅整袋售价为 $85。
  • 每磅研磨咖啡豆售价为 $1.50。

因此，目标函数（总利润）可以表示为：
总利润 = 85 * x1 + 1.5 * x2

在Excel中，我们设置变量单元格并输入目标函数公式。

总利润 = SUMPRODUCT(变量单元格区域， 单位利润区域)

第二步：建立约束条件

接下来，我们需要考虑限制因素。主要约束来自三种咖啡豆的有限库存：

咖啡豆类型 库存总量（磅） 每袋20磅整袋所需（磅） 每磅研磨豆所需（磅）
A 200 30 0.5
B 800 18 0.4
C 200 2 0.1

对于每种咖啡豆，其总使用量不能超过库存。因此，约束条件为：

  • A豆约束：30*x1 + 0.5*x2 <= 200
  • B豆约束：18*x1 + 0.4*x2 <= 800
  • C豆约束：2*x1 + 0.1*x2 <= 200

此外，变量应为非负数：x1 >= 0， x2 >= 0。

在Excel中，我们建立一个约束表，使用SUMPRODUCT函数计算每种豆的实际使用量（左边值），并与库存（右边值）进行比较。

第三步：首次求解与问题发现

设置好变量、目标函数和上述约束后，我们使用Excel Solver进行第一次求解（选择“单纯线性规划”方法）。

Solver可能会给出一个解，例如：x1 = 4.444袋， x2 = 0磅， 最大利润约为$377.78。

然而，这个解存在一个现实问题：你无法销售0.444袋预包装咖啡。x1（整袋数量）必须是一个整数。这就是“混合整数规划”中“整数”部分的来源——部分变量（此处是x1）需要取整数值，而其他变量（x2）可以取连续值。

重要提示：切勿在得到Solver的连续解后手动四舍五入。这样做可能违反约束或得不到最优解。

第四步：添加整数约束并重新求解

为了得到符合现实的解，我们需要返回Solver，添加整数约束。

  1. 在Solver参数对话框中，点击“添加”约束。
  2. 在“单元格引用”中选择代表x1（20磅整袋数量）的单元格。
  3. 在中间的下拉菜单中选择 “int” （表示整数）。Solver会自动在“约束”框中填入“整数”。
  4. 确保“使无约束变量为非负数”选项已勾选，且“忽略整数约束”选项未勾选。
  5. 再次点击“求解”。

Solver现在将找到满足所有约束（包括整数约束）的最优解。

第五步：解读最终结果

添加整数约束后，Solver给出的新解可能是：x1 = 4袋， x2 = 20磅， 最大利润为 $370.00。

现在，我们可以向咖啡店经理提供明确的建议：

  • 生产4袋20磅装的混合咖啡豆整袋。
  • 生产20磅按磅零售的研磨混合咖啡豆。
  • 此方案能最大化利润，预计总利润为$370.00，并且完全符合豆类库存和产品包装要求。

比较一下：如果我们将第一次的连续解（4.444袋）简单四舍五入为4袋，并错误地保持x2=0，利润仅为85*4 = $340.00，这并非最优解。这凸显了正确使用Solver整数约束功能的重要性。

总结

本节课我们一起学习了一个混合整数线性规划的实际案例。我们回顾了建立线性规划模型的步骤：定义变量、设定目标函数、列出约束条件。本次课程的重点是引入了整数约束，以处理像“产品数量”这类必须为整数的现实变量。

关键操作在于：在Excel Solver中直接为特定变量添加“int”约束，而不是在求解后手动取整。这种方法确保了解决方案既在数学上最优，又符合实际业务逻辑。掌握混合整数规划，能让你用Solver工具解决更多样化、更贴近现实的管理决策问题。

018：二元投资决策 📊

在本节课中，我们将学习如何使用线性规划解决一种特定类型的问题：二元决策问题。我们将通过一个投资组合选择的实际案例，演示如何利用Excel的Solver工具，在“是”或“否”的二元选择中，找到最大化投资回报的最佳方案。

概述

二元变量意味着决策结果只能是 0 或 1。在投资决策中，0代表“不投资”，1代表“投资”。本节课我们将构建一个模型，在满足客户一系列特定约束条件的前提下，决定对七支股票的投资选择，以实现预期年回报的最大化。

第一步：理解问题与决策变量

首先，我们需要明确决策的本质。客户要求我们从七支股票中做出选择，每支股票只能整体买入或不买，不能部分投资。这是一个典型的“是/否”二元决策。

因此，我们定义七个决策变量：x1, x2, x3, x4, x5, x6, x7。每个变量只能取值为0或1。

  • 公式： xi ∈ {0, 1}，其中 i = 1 到 7。

注意： 本例中所有金额单位均为“千美元”。例如，成本480代表480,000美元。

第二步：构建目标函数

我们的目标是最大化总投资回报。每支股票都有一个预期的年回报（单位：千美元）。

目标函数是各股票决策变量与其对应回报的乘积之和。只有当变量为1（投资）时，该股票的回报才会被计入总和。

  • 公式： Maximize Z = 50*x1 + 80*x2 + 90*x3 + 120*x4 + 110*x5 + 40*x6 + 75*x7

在Excel中，我们使用 SUMPRODUCT 函数来实现这个计算。

  • 代码： =SUMPRODUCT(决策变量区域, 预期回报区域)

第三步：定义约束条件

客户提出了四项投资约束，我们需要将它们转化为数学模型。

以下是构建约束条件的步骤：

  1. 至少投资两家德克萨斯州石油公司。

    • 涉及的股票：x1 (TransTexas Oil), x4 (Houston Drilling), x5 (Texas Petroleum)。
    • 公式： x1 + x4 + x5 >= 2
    • 含义： 这三支股票中被选中的数量必须大于或等于2。

  2. 最多投资一家外国石油公司。

    • 涉及的股票：x2 (British Petroleum), x3 (Dutch Shell)。
    • 公式： x2 + x3 <= 1
    • 含义： 这两支股票中被选中的数量必须小于或等于1。

  3. 必须恰好投资一家加利福尼亚州公司。

    • 涉及的股票：x6 (San Diego Drilling), x7 (California Petro)。
    • 公式： x6 + x7 = 1
    • 含义： 这两支股票中必须恰好选中一支。

  4. 总投资预算不超过300万美元（即3000千美元）。

    • 每支股票的投资成本已知。
    • 公式： 480*x1 + 540*x2 + 680*x3 + 1000*x4 + 700*x5 + 510*x6 + 900*x7 <= 3000
    • 含义： 所有被选中股票的成本总和不能超过预算。

第四步：在Excel中配置Solver

现在，我们将在Excel中设置Solver来求解这个优化问题。

  1. 设置目标： 选择包含 SUMPRODUCT 回报计算结果的单元格，并设置为“最大值”。
  2. 设置变量： 选择代表x1到x7的七个单元格作为可变单元格。
  3. 添加约束：
    • 将上述四个约束条件逐一添加到Solver中。
    • 关键步骤： 必须为七个决策变量添加 “二进制” 约束。在Solver的添加约束对话框中，从下拉菜单中选择“bin”（代表binary）。
  4. 选择求解方法： 选择“单纯线性规划”方法。
  5. 求解： 点击“求解”按钮，Solver将计算出最优解。

第五步：解读结果与总结

Solver运行后，会给出最优的决策组合。例如，结果可能显示：x3=1, x4=1, x5=1, x6=1，其余为0。

这意味着最优投资策略是：

  • 购买 Dutch Shell (x3), Houston Drilling (x4), Texas Petroleum (x5), 和 San Diego Drilling (x6)。
  • 预期最大总回报为 360（即360,000美元）。
  • 检查所有约束：
    • 德克萨斯州公司：投资了x4和x5，满足“至少两家”。
    • 外国公司：只投资了x3，满足“最多一家”。
    • 加利福尼亚州公司：投资了x6，满足“恰好一家”。
    • 总成本：680+1000+700+510=2890，低于3000的预算。

总结 🎯

本节课中，我们一起学习了如何应用二元线性规划来解决投资决策问题。核心在于将“是/否”的决策转化为 0/1 变量，并利用 SUMPRODUCT 函数构建目标函数和约束条件。通过Excel Solver的“二进制”约束功能，我们可以快速找到在满足各种商业规则和预算限制下的最优投资组合。这种方法将复杂的决策过程简化为数学模型，是商业分析中一个非常强大的工具。

019：投资组合方差 📊

在本节课中，我们将学习如何利用Excel的规划求解功能，处理包含非线性公式的优化问题。我们将通过一个投资组合优化的具体案例，构建一个非线性规划求解模板，并理解其核心概念和操作步骤。

非线性规划简介

上一节我们介绍了线性规划，但在现实世界中，并非所有问题都是线性的。许多问题涉及曲线和非线性关系。掌握线性规划后，我们可以将其原理扩展到非线性问题中。对于变量较多或公式复杂的情况，可能需要更强大的软件和算法，但对于小型案例，我们可以在Excel中构建模板来理解其工作原理。

案例背景与目标

在本例中，一位名为Ryan的金融投资者正在评估同一行业内的两只股票。他的目标是最小化投资组合的方差（即风险），同时要求投资组合的预期回报率达到9%。

我们定义两个决策变量：

  • X = 投资于股票1的资金比例
  • Y = 投资于股票2的资金比例

构建非线性规划模板

1. 定义决策变量与非线性变量

首先，我们设置决策变量。通常，我们用绿色单元格表示需要求解的变量。

决策变量：
X (单元格 B2)
Y (单元格 C2)

接下来是关键步骤：我们需要创建非线性变量。在线性规划中，变量本身是线性的（如 X, Y）。当变量以 X²、XY、Y² 等形式组合时，就变成了非线性项。

以下是需要计算的非线性变量及其公式：

非线性变量：
X² = B2 * B2  (单元格 D2)
XY = B2 * C2  (单元格 E2)
Y² = C2 * C2  (单元格 F2)

2. 设置目标函数（方差）

我们的目标是最小化投资组合的方差。方差公式由历史数据给出，其中包含非线性项：

方差 = 0.16*(X²) + 0.12*(XY) + 0.09*(Y²)

为了清晰和便于检查，我们不将系数直接写入公式，而是将它们列在对应的非线性变量下方（例如D3, E3, F3单元格）。

然后，使用 SUMPRODUCT 函数计算方差：

方差 = SUMPRODUCT(D2:F2, D3:F3)  (假设方差结果在单元格 B11)

3. 设置约束条件

我们需要建立约束表，包含左式（LHS）、关系符号和右式（RHS）。

以下是本例的两个约束：

  • 约束1：全部资金必须投资

    • 含义：投资于两只股票的比例之和必须为100%。
    • 公式：X + Y = 100%
    • 在Excel中，左式（LHS）可以设置为 =SUMPRODUCT(B2:C2, {1,1})，右式（RHS）为 1（或100%）。

  • 约束2：预期回报率不低于9%

    • 已知：股票1的历史回报率为11%，股票2为8%。
    • 公式：11%*X + 8%*Y >= 9%
    • 在Excel中，左式（LHS）为 =SUMPRODUCT(B2:C2, {0.11, 0.08})，关系为 >=，右式（RHS）为 0.09。

4. 使用规划求解

完成以上设置后，即可使用Excel的“规划求解”工具。

  1. 转到 数据 > 规划求解。
  2. 设置目标：选择方差结果单元格（B11），选择“最小值”。
  3. 通过更改可变单元格：选择决策变量单元格（B2:C2）。注意：不要选择非线性变量单元格（D2:F2），它们是由公式计算得出的。
  4. 添加约束：根据约束表添加两个约束条件。
  5. 选择求解方法：这是与线性规划的关键区别。由于目标函数包含非线性项，必须选择 “非线性 GRG” 作为求解方法。
  6. 点击 “求解”。

结果解读

规划求解完成后，会得到最优解。例如，结果可能显示：

  • 应将约 93% 的资金投资于股票X。
  • 将约 7% 的资金投资于股票Y。
  • 在此配置下，投资组合的最小方差（风险）值为一个具体数字（例如0.0342）。

最后，记得用一句话总结你的分析结果，例如：“为达到9%的预期回报率并最小化风险，建议将93%的资金投入股票1，7%投入股票2，此时投资组合的方差为0.0342。”

总结

本节课中，我们一起学习了如何扩展规划求解的应用，处理非线性优化问题。我们通过投资组合方差最小化的案例，掌握了构建非线性规划模板的核心步骤：定义决策变量、创建必要的非线性变量、设置包含非线性公式的目标函数、添加线性约束条件，并正确选择“非线性GRG”求解方法。这个模板是解决更复杂非线性规划问题的基础。

020：非线性整数规划问题实战 🏍️

在本节课中，我们将学习如何利用Excel Solver解决一个结合了非线性目标函数和整数约束的复杂规划问题。我们将以“威斯康星州摩托雪橇公司”的生产优化为例，逐步构建模型并求解。

概述

本节内容将引导你处理一个更贴近现实商业场景的问题：目标函数是非线性的，同时决策变量（产品数量）必须是整数。我们将复习如何设置决策变量、构建非线性公式、添加约束条件，并最终使用Solver的GRG非线性算法找到最优整数解。

问题描述与模型搭建

威斯康星州摩托雪橇公司生产两种型号的雪橇：XJ6和XJ8。在任意生产计划周内，公司的最终测试区有40小时可用时间。每台XJ6需要1小时测试，每台XJ8需要2小时测试。

以下是设置约束条件的步骤：

  1. 定义决策变量：我们首先确定需要决定的未知数。在本例中，是两种雪橇的生产数量。我们将代表XJ6和XJ8数量的单元格标记为绿色。
  2. 建立约束条件：根据测试时间限制，我们可以建立约束公式。总测试时间必须小于等于40小时。

我们使用以下结构来组织数据：

  • XJ6数量 单元格（例如A2）
  • XJ8数量 单元格（例如B2）
  • 左侧公式（LHS）：=A2 * 1 + B2 * 2
  • 约束符号：<=
  • 右侧值（RHS）：40

构建非线性目标函数

收入（以千美元计）由以下公式给出：R = x(4 - 0.1x) + y(5 - 0.2y)，其中x是XJ6的数量，y是XJ8的数量。

这个公式比线性公式复杂。虽然x和(4-0.1x)各自看是线性的，但它们的乘积会产生x²项，使得整个目标函数成为非线性。

为了避免手动进行代数展开，我们可以在Excel中分步计算：

  1. 在单元格中分别计算中间项：
    • = 4 - 0.1 * XJ6数量单元格
    • = 5 - 0.2 * XJ8数量单元格
  2. 然后，收入（目标函数）公式为：
    • = (XJ6数量单元格) * (第一个中间项单元格) + (XJ8数量单元格) * (第二个中间项单元格)

通过这种方式，我们清晰地构建了非线性目标函数，便于在Solver中引用。

使用Solver求解并添加整数约束

现在，我们进入求解阶段。

  1. 打开Solver，设置目标为最大化收入单元格。
  2. 将两个绿色的数量单元格设为可变单元格。
  3. 添加测试时间的约束条件。
  4. 勾选“使无约束变量为非负数”选项。
  5. 最关键的一步：选择求解方法为“GRG非线性”。

首次运行Solver可能会得到一个非整数解（例如，生产9.047台雪橇）。这在实际生产中是不可行的。

因此，我们需要添加整数约束：

  1. 返回Solver参数对话框。
  2. 添加新的约束条件：选择两个数量单元格，在中间的下拉菜单中选择“int”（整数）。这表示决策变量必须为整数。
  3. 再次运行Solver（仍使用GRG非线性方法）。

Solver会找到满足所有约束的整数最优解。

结果解读与总结

求解完成后，我们得到最优生产计划：生产10台XJ6和15台XJ8雪橇。对应的最大收入为100.5（千美元），即100，500美元。

在本节课中，我们一起学习了如何解决融合了非线性目标函数和整数约束的优化问题。我们回顾了设置决策变量、分步构建复杂公式、添加资源约束和整数约束，并正确选用GRG非线性算法进行求解的全过程。这个例子综合了本课程的多个核心技能，展示了Excel Solver处理现实世界复杂商业问题的强大能力。你可以尝试在此基础上引入更多复杂性，例如添加更多类型的约束条件。

posted @ 2026-03-26 08:54  布客飞龙II  阅读(0)  评论(0)    收藏  举报