Excel财务应用营养配餐问题

Excel财务应用营养配餐问题随着现代生活水平的提高，人们越来越注重健康问题，而科学的饮食则是保持健康和增强体质最简单、有效的方法。人们已经逐渐意识到，长期不良的膳食结构与饮食习惯会严重影响身体健康，很多人都是因为“吃不得法”，而罹患种种疾病，如肥胖、心脏病、高血压、动脉硬化等。因此，作为一家餐馆，合理的进行营养配餐是招揽顾客的最佳手段。营养配餐，是指按照人们身体的需要，根据食品中各种营养物质的含量，设计一天、一周或一个月的食谱，使人体摄入的蛋白质、脂肪、碳水化合物、维生素和矿物质等几大营养元素比例合理，即达到均衡膳食。下面我们利用线性规划模型，来解决餐馆的营养配餐问题。例如，某餐厅开发出一道新菜需要使用草鱼和平菇两种原料，这两种原料所包含的碳水化合物、蛋白质、脂肪含量以及人体每天所需的摄入量如图9-19所示，已知草鱼和平菇的成本分别为3元和2元。要求使用线性规划计算如何搭配这两种原料，才能在成本最小的基础上，满足顾客的营养要求？根据已知条件，可以在Excel工作表中建立求解的线性规划模型，如图9-20所示。图9-19已知条件 图9-20创建线性规划模型然后，分别计算这两种原料中，每种营养成分的合计值。选择碳水化合物合计值所对应的单元格，即D3单元格，在【编辑栏】中输入“=SUMPRODUCT(B3:C3,B7:C7)”公式，按Enter键即可得出该营养成分的合计值，如图9-21所示。再向下拖动D3单元格右下角的填充柄，将公式填充至D5单元格，如图9-22所示。图9-21碳水化合物的合计值 图9-22填充公式在创建好的线性规划模型中，带有“茶色”填充格式的单元格区域属于可变值，其默认值为0,因此，与其相关的营养成分合计值也为0。选择目标值所对应的单元格，即F7单元格，在【编辑栏】中输入“=SUMPRODUCT(B6:C6,B7:C7)”公式，按Enter键，即可计算出该问题的目标值，如图9-23所示。J［呷MFEULtgTCB&mC自卫72cHiJ［呷MFEULtgTCB&mC自卫72cHi*辛罂小新3止方U, jhttr.l典分Fl嫉本也告* J去后听 刊届后 6比是 3■及图9-23计算目标值条件和公式的设置完成之后，选择F7单元格，单击【规划求解】按钮，在弹出的【规划求解参数】对话框中，选择【最小值】单选按钮，设置【可变单元格】的数据范围为B7:C7,并依次单击【添加】按钮，分别添加“$D$3：$D$4>=$F$3:$F$4”和“$D$5<=$F$5”约束，如图9-24所示。然后，进行规划求解选项的设置，并单击【求解】按钮，在【规划求解结果】对话框中，选择【报告】栏中的【敏感性报告】选项，如图9-25所示。.iS^SHKiU-IT-可阻是若百的购藤或aIt选择【报告】栏中的【敏感性报告】选项，如图9-25所示。.iS^SHKiU-IT-可阻是若百的购藤或aIt他必值1用呼或时天用hf吴HiC任豆力度0陋图9-24设置规划求解参数 图9-25设置规划求解结果在设置线性规划的约束时，由于本例的限制条件不是单一的，而是由两种不同的约束条件组成的。因此，在本例中，需要添加两组约束条件。单击【选项】按钮，在弹出的【规划求解选项】对话框中，分别启用【采用线性 在模型】和【假定非负】复选框。 【规划求解】对话框中，可以选择生成三种形式的报告用，户既可以只选择一种报告也，可以同时生成三种报告。其中，每种报告的功能意义如下：运算结果报告该类形式的报告可以列出目标单元格和可变单元格及其初始值和最终结约果束、条件以及有关约束条件的信息。敏感性报告该报告提供有关求解结果对【规划求解参数】对话框中，目标单元格所指定公式的微小变化或约束条件的微小变化的敏感程度信息。但是，含有整数约束条件的模型不能生成该报告。对于非线性模型，该报告提供递减梯度和拉格朗日乘数；对于线性模型，该报告中将包含递减成本、阴影价格、目标式系数（允许的增量和允许的减量）以及约束右侧的区域。极限值报告在该报告中，可以列出目标单元格和可变单元格及其各自的数值、上下限和目标值。下限是在保持其他可变单元格数值不变并满足约束条件的情况下，某个可变单元格可以取到的最小值。而上限是在这种情况下可以取到的最大值。与敏感性报告相同，含有整数约束条件的模型不能生成该报告。单击【规划求解结果】对话框中的【确定】按钮，即可得到如图9-26所示的规划求解结果，以及图9-27所示的敏感性分析报告。图9-26规划求解结果 图9-27敏感性分析报告根据图9-26可以得出结论：若要在最小成本的基础上满足顾客的营养需求，只需将2.8份的草鱼与3份的平菇进行搭配即可。在Excel中，敏感性报告描述的是最优解的稳定性。其中，线性规划的敏感性报告提供了递减成本、阴影价格、目标式系数（允许的增量和允许的减量）等重要参数。在本例中，由于启用了【采用线性模型】复选框，因此可以分析比较详细的敏感性报告。“可变单元格”部分在该报表中，“允许的增量”和“允许的减量”数据列，表示对应的约束条件增加或者减少某个数值，规划