规划求解与Excel工作簿保护的最佳实践

1.Excel规划求解

Excel提供了功能强大的单变量求解、双变量求解、规划求解、模拟运算表、数据透视图和数据透视表等数据分析工具。

规划求解是一组命令的组成部分，这些命令有时也称为假设分析工具，它可以求出目标单元格中公式的最优值。从功能角度讲，规划求解可以求解各种优化问题，如线形规划、整数规划和网络规划等。从工作原理角度讲，它是通过调整可变单元格中的数值，从目标单元格的公式中求得所需结果，这样重复迭代，直到获得最优的数值解。在迭代求取最优解的过程中，各种约束条件对获得最优解的效率和精度都有很大的影响；使用者可以对可变单元格的数值应用约束条件，而且约束条件可以引用其他单元格。

2.工作簿的加密

工作簿（由多个工作表组成）的加密包括加密保存和加密保护。加密保存是用密码将工作簿保存起来，没有密码将无法打开工作簿，从而防止单位商业秘密的泄露。

加密保护是指对工作簿编辑等权限的保护，加密保护工作簿后能够打开工作簿，但限制其对工作簿的结构、窗口进行编辑或改变。其中的结构保护是指禁止对工作簿中的各工作表进行复制、移动、插入、重命名、隐藏和取消隐藏等操作。窗口保护是指禁止在每次打开工作簿时，改变工作簿的固定位置和大小。这种保护下，打开的Excel窗口的右上角只有程序窗口的最大化、最小化等按钮，在Excel 2010及其以前版本中没有工作簿窗口的最大化、最小化、关闭等按钮。

某公司在3个车间生产3种产品，各产品在车间耗用的工时、单台产品的销售利润、各车间能提供的总工时等见表6-3。要求按利润最大化原则，安排产品生产。

表6-3　各车间生产相关产品情况表

在Excel中设计规划求解模型，如图6-14所示。单击“数据”选项卡或菜单中的“规划求解”命令，再单击“求解”按钮，模型自动计算出C7至E7单元区域的最佳生产量。

图6-14　生产规划求解模型（Excel 2007数据选项卡）

（1）录入文字、已知数据，合并单元格，设置其他格式等。

（2）键入计算公式。在C8单元格录入3种产品销售利润总额公式“＝C6∗C7＋D6∗D7＋E6∗E7”；在F3单元格录入第1车间实用工时公式“＝C3∗＄C＄7＋D3∗＄D＄7＋E3∗＄E＄7”；自动填充第2、3车间的实用工时；在G3单元格录入第1车间剩余工时公式“＝B3-F3”；自动填充第2、3车间剩余工时。

（3）安装规划求解工具。Excel默认情况下是没有安装规划求解工具的，所以应先行安装。方法是：将原Microsoft Office安装光盘放入光驱，在Excel 2003及其以前版本中选择“工具/加载宏”菜单命令进入“加载宏”对话框，如图6-15（a）所示；选择“规划求解”，单击“确定”按钮。若原安装Microsoft Office软件时进行了全部安装，则不需要原安装光盘，但仍应加载宏。

在Excel 2007—Excel 2019中单击“Office按钮/Excel选项”或“文件/选项”命令，选择“加载项/规划求解加载项”，单击下部的“转到”按钮，按照提示安装（若原安装Office软件时没有进行全部安装，则需要原安装光盘）。安装后将在“数据”选项卡显示“规划求解”功能。

（4）对最佳产量进行规划求解，方法如下。

a.选定目标C8单元格，在Excel 2003及其以前版本中选择“工具/规划求解”菜单命令，在Excel 2007—Excel 2019中单击“数据/规划求解”命令，进入“规划求解参数”对话框，如图6-15（b）所示。

图6-15　安装规划求解工具（a）与设置规划求解参数（b）

b.确定求解要求。因为按利润最大化安排各车间产品生产量，所以目标单元为“＄C＄8”（利润总额），选择“最大值”，求解的可变单元格是“＄C＄7：＄E＄7”（最佳产量）。

c.添加约束条件。产量不能有小数，单击下部“添加”按钮进入“添加约束”对话框，如图6-16（a）所示；选定引用位置“＄C＄7：＄E＄7”的约束条件为“int整数”，单击“确定”按钮。(www.xing528.com)

各车间的实际工时不能超过最大可提供工时数，单击“添加”按钮进入“添加约束”对话框；选定引用位置“＄F＄3”的约束条件为“＜＝＄B＄3”，如图6-16（b）所示；单击“确定”按钮。

再用类似的方法添加其他两个车间的工时约束条件。

图6-16　添加前两个约束条件

d.求解最佳产量。单击图6-15（b）规划求解参数对话框的“求解”按钮，弹出“规划求解结果”对话框，如图6-17所示。单击“确定”按钮，将计算出C7至E7单元区域的最佳生产量，甲产品为288、乙产品为816、丙产品为1 152，C8单元格的利润总额为56 064，G4单元格的第2车间剩余4小时，其他车间的工时被全部利用（不同Excel版本的计算结果有细微的差异）。

图6-17　规划求解结果（Excel 5.0）

（5）在“规划求解结果”对话框（图6-17），可做如下规划求解结果处理：

a.选择“保存规划求解结果”并单击“确定”按钮，则回到工作表并将规划求解的结果保存于工作表的相应单元格中。

b.选择“恢复为原值”并单击“确定”按钮，回到工作表后将不进行求解。

c.保存为报告。选择右部的“运算结果报告”并单击“确定”按钮，则在原有工作簿中将新建“运算结果报告1”，对目标单元、可变单元、约束条件等进行相应的信息提示。若没有整数约束，则还可生成敏感性报告、极限值报告（本例有整数约束，不能生成这2个报告）。

（6）工作簿加密保护。在Excel 2003及其以前版本中选择“工具/保护/保护工作簿”菜单命令，在Excel 2007—Excel 2019中选择“审阅/保护工作簿”，或“Office按钮/准备/保护工作簿/保护结构和窗口”“文件/信息/保护工作簿/保护结构和窗口”等相关命令，进入“保护工作簿”对话框，如图6-18（a）所示；选定要保护的元素，键入密码（也可不键入密码），单击“确定”按钮。

加密保护后，若对此工作簿下部的工作表标签进行双击，将弹出“工作簿有保护不能更改”的信息提示框。

取消工作簿保护方法是，选择“工具/保护/撤消保护工作簿”（Excel 2003及其以前版本）或再次单击“审阅/保护工作簿”（Excel 2007—Excel 2019）命令。

（7）工作簿的加密保存。工作簿加密保存有两种方法，操作方法如下：

a.另存为加密法。选择“文件/另存为”命令，弹出“另存为”对话框；选择该对话框右上角“工具/常规选项”（Excel 2003及其以前版本）或左下角（下方）的“工具/常规选项”（Excel 2007—Excel 2019），进入“保存选项”对话框，如图6-18（b）所示，在其中键入打开密码、修改密码；单击“确定”按钮，并再确认密码。

图6-18　保护工作簿（a）与加密工作簿（b）

b.选项保密法。在Excel 2003及其以前版本中选择“工具/选项”菜单命令，进入“选项”对话框，在“安全性”卡片中键入打开、修改的密码，并确认密码。

在Excel 2007—Excel 2019中选择“Office按钮/准备/加密文档”或“文件/信息/保护工作簿”等命令，在“用密码进行加密”中设置密码。

c.下次打开该工作簿时，将要求键入密码，若无打开密码，将无法打开此文件；若无修改密码，可以使用打开密码以“只读”的方式打开。

若要删除密码，先打开该工作簿，按加密的方法进入相关的对话框，删除相关密码。

（1）在Excel 2003及其以前版本的“工具/选项”菜单命令的“安全性”卡片中，若勾选“保存时从文件属性中删除个人信息”，则该工作簿中的作者、工作单位等信息将不保存，以达到保护个人隐私的目的。因为通过“文件/属性”菜单命令，能够查看、修改这些个人信息。

Excel 2007—Excel 2019在“Office按钮/准备”或“文件/信息”界面右部的“属性”中，能够查看、修改个人信息。所以，可选择这些界面中的“检查问题/检查文档”命令进行检查，再删除其中的文档属性和个人信息。

（2）隐藏工作簿的方法，打开保存工作簿的文件夹（资源管理器），右击该工作簿（Excel文件），选择“属性”命令，在“常规”卡片中勾选“隐藏”复选框，则此工作簿在计算机中将不会显示。若需显示已隐藏的工作簿（Excel文件），可在保存该工作簿的文件夹（资源管理器）界面，选择“工具/文件夹选项”命令，在“查看”卡片中勾选“显示所有文件和文件夹”或“显示隐藏的文件、文件夹和驱动器”等，则隐藏的工作簿将会显示出来。