巧用Excel編寫飼料檢驗程序（轉載）

apple12151007 · 發(fā)表于 2010-8-25 14:35:04

　　在對飼料進行檢測分析時，通常我們是將化驗所得的數(shù)據(jù)代入公式，然后用計算器一步步算出結果。如果檢驗的樣品數(shù)量及檢驗項目都較多，同時實驗還要求較高的精確性(如需要考慮到溫度對滴定液體積的影響等)，這種情況下，如果用計算器計算就會有很多麻煩，不但會浪費時間，而且容易出錯。
　　針對以上問題，這里介紹一種利用Microsoft Excel設計程序來計算飼料檢驗結果的方法。該程序有如下優(yōu)點：(1)輸入原始數(shù)據(jù)，自動計算出檢驗結果，不必理會每個檢驗項目的計算公式是什么;(2)根據(jù)輸入的原始數(shù)據(jù)，自動校正溫度補償值，不必人工查表;(3)根據(jù)輸入的原始數(shù)據(jù)自動生成兩平行樣的相對偏差值，不必另行計算;(4)根據(jù)生成的相對偏差值，自動與標準相對偏差值相比較，如果大于標準，則重復性返回“不好”，如果小于標準，則重復性返回“好”，不必計算出相對偏差后，再去查相對偏差標準。
　　現(xiàn)以粗蛋白質(zhì)的計算為例進行說明：
　　1 計算公式
　　按GB/T 6432-94方法檢驗粗蛋白質(zhì)的計算公式為：
　　式中： V2—滴定試樣時所需標準酸溶液體積，mL;
　　V1——滴定空白時所需標準酸溶液體積，mL;
　　C——鹽酸標準溶液濃度，mol/L;
　　m——試樣質(zhì)量，g;
　　V——試樣分解液總體積(通常為100.00)，mL;
　　Vt ——蒸餾取用的試樣分解液體積(通常為10.00)，mL;
　　0.0140——每毫克當量氮的克數(shù);
　　6.25——氮換算成蛋白質(zhì)的平均系數(shù)。
　　2 重復性
　　每個樣品取兩個平行樣進行測定，以其算術平均值為測定結果。
　　當粗蛋白質(zhì)含量在25%以上時，允許相對偏差為1%。
　　當粗蛋白質(zhì)含量在10%～25%之間時，允許相對偏差為2%。
　　當粗蛋白質(zhì)含量在10%以下時，允許相對偏差為3%。
　　3 計算步聚
　　(1)將溫度補正表(GB601附錄1)輸入一個Excel工作薄中，并為工作薄起名為“溫度補正”。
　　從表中我們發(fā)現(xiàn)，不同溶質(zhì)的溶液受溫度的影響不同，相同溶質(zhì)不同濃度的溶液受溫度的影響也不同。通過回歸直線法我們還發(fā)現(xiàn)，溶液體積的改變與溫度的改變并非呈良好的線性關系。因此，通過確定回歸直線法來進行溫度補償是不準確的，應當采取的方法是嚴格按表中的數(shù)據(jù)，是多少度就補償多少度。如下圖所示：

　　(2)新建一個工作表，起名為“粗蛋白質(zhì)含量”，按下表輸入：

　　(3)編輯函數(shù)：表格建立完畢后，在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)”，F(xiàn)4單元格中輸入“=E4*A2*0.014*6.25*100/C4/0.1”，F(xiàn)5單元格中輸入“=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單元格加入如下批注：
　　當粗蛋白質(zhì)含量在25%以上時，允許相對偏差為1%;
　　當粗蛋白質(zhì)含量在10%～25%之間時，允許相對偏差為2%;
　　當粗蛋白質(zhì)含量在10%以下時，允許相對偏差為3%。
　　C1單位格0.08：溫度對不同濃度溶液體積的影響以0.08mol/L為界定點，區(qū)分0.1mol/L和0.05mol/L兩個不同濃度。
　　公式編輯完成后，得到表格如下：

　　公式說明：C1為IF函數(shù)，作用是判斷滴定液的濃度，若大于0.08mol/L則返回“0.1修正值”，否則返回“0.05修正值”;C2單元格函數(shù)意義是根據(jù)B2單元格的溫度，返回相應的溫度補正值;E4~H4均為一些普通的計算函數(shù)，不再贅述;I4為IF函數(shù)，依據(jù)G4粗蛋白質(zhì)的含量，返回相應的偏差標準;J4也是IF函數(shù)，根據(jù)I4與H4的比較，判定重復性的好壞。
　　(4)使用：下面就可以輸入具體數(shù)據(jù)執(zhí)行計算了，試試計算結果吧!以后每次使用該程序，只需要“復制”、“粘貼”就可以了，不必重新編制程序。當然，其他的檢驗項目諸如鈣、磷、水分、鹽分、粗脂肪等等，均可按以上方法編制程序，在這里不再贅述。
　　來源：飼料廣角　　作者：尹衍濤劉薈祺