基于Excel 2003设计最佳营养配方

[摘要] 利用excel 2003提供的规划求解工具可以解决使食谱在满足营养要求的前提下，产生最大利润且费用最小。

[关键词] Excel 最佳营养配方 规划求解

一、前言

所谓最佳营养配方指的是不仅能满足各项营养指标的要求，而且成本又是最低的营养食谱配方。膳食中各种营养素的正确摄取，直接关系到人们的健康。解决营养问题的关键就是要合理膳食。不同的人群有不同的营养要求。结合自己的具体情况，选择适合自己的最佳营养配方，维持均衡的营养状况，为健康的机体打下坚定的基础，这是良好生活质量的保证。本文介绍利用Excel 2003提供的规划求解工具设计食谱最佳营养配方，以解决手工状态下图解法的不准确性和单纯形法的繁琐。

二、实例

设计如下菜谱：其中含有鸡蛋，瘦猪肉，扁豆，胡萝卜，考虑到菜谱的适口性，胡萝卜最高用量为10%。要求配制如下营养成分含量且成本最低的营养配方:250千卡/500克≤能量≤400千卡/500克，17克≤蛋白质≤21克/500克，钙≥26毫克/500克，铁≥1.5毫克/500克，胡萝卜素≥2毫克/500克，维生素B1≥0.35毫克/500克，维生素B2≥0.48毫克/500克,尼克酸≥3.2毫克/500克，维生素C≥38毫克/500克。已知各原料价格如下:瘦猪肉7.00元/500克，鸡蛋2.70元/500克，扁豆1.50元/500克，胡萝卜0.50元/500克。在符合膳食营养成份要求及符合口感的前提下，如何配合这些原料，使食谱既满足营养需求且费用最低呢？为便于列出数学模型，可将问题归纳为表1所示:

根据以上资料，设四种原料的需要量分别为X1、X2、X3、X4，可列线性规划模型如下：

三、Excel 2003规划求解步骤

1.安装规划求解加载宏

在Excel 2003窗口中单击“工具”菜单，在弹出的下拉菜单中单击“加载宏”命令，出现图1所示“加载宏”对话框，在“可用加载宏”列表框中选定“规划求解”复选框，单击“确定”按钮。加载宏后，“工具”菜单中出现“规划求解….”命令。

2.建立规划求解工作表

建立如图2所示的规划求解工作表：

在上图相关单元格中输入如下公式：

（1)原料中各种营养物质含量计算如下：

D8=E8*E$5+F8*F$5+G8*G$5+H8*H$5

D9=E9*E$5+F9*F$5+G9*G$5+H9*H$5

D10=E10*E$5+F10*F$5+G10*G$5+H10*H$5

D11=E11*E$5+F11*F$5+G11*G$5+H11*H$5

D12=E12*E$5+F12*F$5+G12*G$5+H12*H$5

D13=E13*E$5+F13*F$5+G13*G$5+H13*H$5

D14=E14*E$5+F14*F$5+G14*G$5+H14*H$5

D15=E15*E$5+F15*F$5+G15*G$5+H15*H$5

（2)各种原料费用计算如下：

E17=E5*E4

F17=F5*F4

G17=G5*G4

H17=H5*H4

（3)配制500克最佳食谱的费用计算如下：

E18=SUM(E17:H17)

（4)配制500克最佳食谱各原料的用量计算如下：

E20=E5*500

F20=F5*500

G20=G5*500

H20=H5*500

（5)各种原料总用量计算如下：

E21=SUM(E20:H20)

3.求最佳组合解

（1）选择“工具”“规划求解…..”命令，出现如图3所示的对话框：

（2）在“设置目标单元格”框中输入E19。

（3）选定“最小值”选项。

（4）)在“可变单元格”框中输入E5:H5。

（5）单击“添加”按钮，出现“添加约束”对话框，在“添加约束”对话框中输入：

单元格引用位置运算符号约束值

D8:D16>= B8:B16

单击“确定”按钮，完成输入。

按照上述操作步骤，再输入以下约束条件：

D8:D9

E5:H5>=0

H5

E21= 500

（6）在“规划求解参数”对话框中单击“求解”按钮。

（7）显示计算结果，如图5所示。

（8）在“规划求解结果”对话框中选择“保存规划求解结果”选项，单击“确定”按钮即可。

四、结论

结果显示:满足条件的食谱配方每500克用鸡蛋79.22396克、瘦猪肉17.79928克、扁豆352.9768克、胡萝卜50克。每500克该食谱最低费用为1.79元。通过线性规划求解，在保证营养及口感的前提下，尽可能的降低产品成本，可作为医院、餐厅、宾馆、快餐等行业制作营养经济食谱的参考。同样，利用Excel 2003提供的规划求解工具可以解决作业分配问题，诸如运输问题、饲料配方问题、人事安排等，只要与生产、制造、分配、投资、财务、工程等有关的求最大利润，最小费用等问题均可使用规划求解法找到答案。

参考文献:

高俊德徐鹏等:食品营养及其计算[M].北京:中国食品出版社,1987.1～196

注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。