巧用Excel编写饲料检验程序（转载）

apple12151007 · 发表于 2010-8-25 14:35:04

　　在对饲料进行检测分析时，通常我们是将化验所得的数据代入公式，然后用计算器一步步算出结果。如果检验的样品数量及检验项目都较多，同时实验还要求较高的精确性(如需要考虑到温度对滴定液体积的影响等)，这种情况下，如果用计算器计算就会有很多麻烦，不但会浪费时间，而且容易出错。
　　针对以上问题，这里介绍一种利用Microsoft Excel设计程序来计算饲料检验结果的方法。该程序有如下优点：(1)输入原始数据，自动计算出检验结果，不必理会每个检验项目的计算公式是什么;(2)根据输入的原始数据，自动校正温度补偿值，不必人工查表;(3)根据输入的原始数据自动生成两平行样的相对偏差值，不必另行计算;(4)根据生成的相对偏差值，自动与标准相对偏差值相比较，如果大于标准，则重复性返回“不好”，如果小于标准，则重复性返回“好”，不必计算出相对偏差后，再去查相对偏差标准。
　　现以粗蛋白质的计算为例进行说明：
　　1 计算公式
　　按GB/T 6432-94方法检验粗蛋白质的计算公式为：
　　式中： V2—滴定试样时所需标准酸溶液体积，mL;
　　V1——滴定空白时所需标准酸溶液体积，mL;
　　C——盐酸标准溶液浓度，mol/L;
　　m——试样质量，g;
　　V——试样分解液总体积(通常为100.00)，mL;
　　Vt ——蒸馏取用的试样分解液体积(通常为10.00)，mL;
　　0.0140——每毫克当量氮的克数;
　　6.25——氮换算成蛋白质的平均系数。
　　2 重复性
　　每个样品取两个平行样进行测定，以其算术平均值为测定结果。
　　当粗蛋白质含量在25%以上时，允许相对偏差为1%。
　　当粗蛋白质含量在10%～25%之间时，允许相对偏差为2%。
　　当粗蛋白质含量在10%以下时，允许相对偏差为3%。
　　3 计算步聚
　　(1)将温度补正表(GB601附录1)输入一个Excel工作薄中，并为工作薄起名为“温度补正”。
　　从表中我们发现，不同溶质的溶液受温度的影响不同，相同溶质不同浓度的溶液受温度的影响也不同。通过回归直线法我们还发现，溶液体积的改变与温度的改变并非呈良好的线性关系。因此，通过确定回归直线法来进行温度补偿是不准确的，应当采取的方法是严格按表中的数据，是多少度就补偿多少度。如下图所示：

　　(2)新建一个工作表，起名为“粗蛋白质含量”，按下表输入：

　　(3)编辑函数：表格建立完毕后，在C1单元格中输入“=IF(A2>0.08，“0.1修正值”，“0.05修正值”)”，在C2单元格中输入“=DGET(温度补正!$A$2:$C$34，C1，B1:B2)”，E4单元格中输入“=((D4-F2)+(D4-F2)*C2/1000)”，E5单元格中输入“=((D5-F2)+(D5-F2)*C2/1000)”，F4单元格中输入“=E4*A2*0.014*6.25*100/C4/0.1”，F5单元格中输入“=E5*A2*0.014*6.25*100/C5/0.1”，G4单元格中输入“=AVERAGE(F4:F5)”，H4单元格中输入“=(ABS(F4-F5)/(F4+F5))*100”，I4单元格中输入“=IF(G4>25，1，IF(G4>=10，“2”，IF(G4<10，“3”)))”，J4单元格中输入“=IF((H4-I4)>0，“不好”，“好”)”，程序输入完毕!再为I3单元格加入如下批注：
　　当粗蛋白质含量在25%以上时，允许相对偏差为1%;
　　当粗蛋白质含量在10%～25%之间时，允许相对偏差为2%;
　　当粗蛋白质含量在10%以下时，允许相对偏差为3%。
　　C1单位格0.08：温度对不同浓度溶液体积的影响以0.08mol/L为界定点，区分0.1mol/L和0.05mol/L两个不同浓度。
　　公式编辑完成后，得到表格如下：

　　公式说明：C1为IF函数，作用是判断滴定液的浓度，若大于0.08mol/L则返回“0.1修正值”，否则返回“0.05修正值”;C2单元格函数意义是根据B2单元格的温度，返回相应的温度补正值;E4~H4均为一些普通的计算函数，不再赘述;I4为IF函数，依据G4粗蛋白质的含量，返回相应的偏差标准;J4也是IF函数，根据I4与H4的比较，判定重复性的好坏。
　　(4)使用：下面就可以输入具体数据执行计算了，试试计算结果吧!以后每次使用该程序，只需要“复制”、“粘贴”就可以了，不必重新编制程序。当然，其他的检验项目诸如钙、磷、水分、盐分、粗脂肪等等，均可按以上方法编制程序，在这里不再赘述。
　　来源：饲料广角　　作者：尹衍涛刘荟祺