有關物(wù)質自動計算(suàn)與判斷的(de)Excel表格優化(huà)設計案例

幾乎所有藥廠實驗室都會用(yòng)到Excel來(lái)進行檢測結果計算(suàn)。這(zhè)些用(yòng)于檢測結果計算(suàn)的(de)Excel表格大(dà)多(duō)是将公式固化(huà)（鎖定）下(xià)來(lái)作爲模闆使用(yòng)的(de)，比如含量、溶出度、含量均勻度等的(de)計算(suàn)模闆。而另外一些表格，比如用(yòng)于有關物(wù)質或者溶殘計算(suàn)的(de)，由于雜(zá)質或殘留溶劑并不像含量那樣穩定出峰，在很多(duō)實驗室并未被固化(huà)爲模闆使用(yòng)。這(zhè)種情況下(xià)，計算(suàn)不同批次有關物(wù)質或溶殘檢驗結果時(shí)，分(fēn)析人(rén)員(yuán)往往需要修改計算(suàn)公式或者進行人(rén)工判斷。

使用(yòng)這(zhè)種計算(suàn)公式不固定的(de)，依據不同批次具體情況臨時(shí)修改公式的(de)計算(suàn)表格，顯然是有合規風險的(de)，既爲驗證增加了(le)麻煩，也(yě)爲表格的(de)複核帶來(lái)了(le)不便，需要複核人(rén)員(yuán)花費更多(duō)的(de)時(shí)間去核對(duì)。FDA有多(duō)封警告信涉及這(zhè)一問題。

本文将介紹如何通(tōng)過優化(huà)設計，做(zuò)到有關物(wù)質計算(suàn)模闆的(de)固化(huà)，避免臨時(shí)修改公式的(de)情況，從而提高(gāo)工作效率，降低風險。

下(xià)邊我們通(tōng)過某藥企一款産品的(de)有關物(wù)質計算(suàn)表格優化(huà)前後的(de)對(duì)比進行具體講解。

優化(huà)前效果：

我們看到雜(zá)質出峰并不穩定，如雜(zá)質1隻在第2個(gè)樣品裏檢出，雜(zá)質6隻在第3個(gè)樣品裏檢出。點擊”公式”選項卡-顯示公式，雜(zá)質平均值的(de)計算(suàn)公式更是五花八門，完全是”具體情況具體分(fēn)析”，分(fēn)析人(rén)員(yuán)是結合了(le)實驗的(de)實際情況後再進行相應的(de)計算(suàn)公式編輯。

造成這(zhè)種需要臨時(shí)修改雜(zá)質平均值計算(suàn)公式的(de)原因是該産品有關物(wù)質檢測時(shí)有如下(xià)需求：

•    在進行樣品中有關物(wù)質檢驗時(shí)，需進3針平行樣品，計算(suàn)每針不同雜(zá)質的(de)相對(duì)保留時(shí)間，以及3針樣品的(de)每個(gè)雜(zá)質相對(duì)保留時(shí)間平均值、雜(zá)質百分(fēn)值大(dà)小的(de)平均值以及總雜(zá)質；

•    單個(gè)雜(zá)質：如果三個(gè)平行樣品結果均不小于0.05%，則報告三針結果的(de)平均值；

•    如果三個(gè)平行樣品結果均小于0.05%，則報告結果爲小于0.05%；

•    如果其中一個(gè)樣品小于0.05%，其它兩個(gè)樣品均不小于0.05%，則報告這(zhè)兩針結果的(de)平均值；

•    如果其中兩個(gè)樣品均小于0.05%，其它一個(gè)樣品不小于0.05%，則報告最大(dà)的(de)這(zhè)針結果；

•    總雜(zá)質：僅計算(suàn)含量大(dà)于等于0.05%（報告限）的(de)雜(zá)質。

表格設計難點分(fēn)析：

由此可(kě)見，三針樣品雜(zá)質平均值的(de)計算(suàn)公式，根據每針樣品中該雜(zá)質與0.05%（報告限）的(de)大(dà)小不同而不同。可(kě)能是一針結果的(de)最大(dà)值，也(yě)可(kě)能是兩針或三針結果的(de)平均值，所以單純的(de)平均值公式并不能适用(yòng)于所有的(de)情況，無法簡單固化(huà)。這(zhè)裏可(kě)以結合IF條件判斷函數來(lái)進行雜(zá)質平均值公式的(de)設計優化(huà)。

優化(huà)後效果：

我們首先用(yòng)綠色背景标識數據待輸入的(de)單元格區(qū)域，用(yòng)藍色背景标識計算(suàn)處理(lǐ)單元格區(qū)域。此外給每針樣品預留8個(gè)雜(zá)質峰的(de)輸入欄，以便容納未來(lái)随著(zhe)穩定性考察時(shí)間的(de)增長(cháng)，可(kě)能檢測到的(de)更多(duō)雜(zá)質峰。每個(gè)雜(zá)質的(de)百分(fēn)值大(dà)小依然通(tōng)過面積歸一化(huà)法由色譜系統自動算(suàn)出，如果雜(zá)質檢出就輸入相應的(de)保留時(shí)間和(hé)大(dà)小，如果雜(zá)質未檢出就輸入ND。

下(xià)面以H12單元格雜(zá)質1平均值計算(suàn)公式爲例進行具體講解，其它雜(zá)質平均值計算(suàn)單元格公式依次填充并修改相應的(de)篩選條件即可(kě)。

由于公式比較複雜(zá)，我們采取”公式：解釋”的(de)寫法進行表述。

IF(AND(N(E3)<0.05,N(E12)<0.05,N(E21)<0.05),〞<0.05〞,…：如果3針平行樣E3、E12、E21大(dà)小（%）均小于0.05，則雜(zá)質1平均值（%）顯示<0.05；

IF(AND(N(E3)>=0.05,N(E12)>=0.05,N(E21)>=0.05),AVERAGE(E3,E12,E21),…：如果3針平行樣E3、E12、E21大(dà)小（%）均大(dà)于等于0.05，則雜(zá)質1平均值（%）爲這(zhè)3針的(de)平均值；

SUMIFS(E$3:E$29,C$3:C$29,”雜(zá)質1″,E$3:E$29,”>=0.05″)/COUNTIFS (C$3:C$29,”雜(zá)質1″,E$3:E$29,”>=0.05″)))：如果3針平行樣E3、E12、E21大(dà)小（%）既有大(dà)于等于0.05又有小于0.05，則雜(zá)質1平均值（%）爲通(tōng)過條件求和(hé)函數SUMIFS（隻計E3、E12、E21中大(dà)于等于0.05的(de)值之和(hé)）除以條件計數函數COUNTIFS（隻計E3、E12、E21中大(dà)于等于0.05的(de)單元格數）間接得(de)到的(de)滿足大(dà)于等于0.05條件的(de)平均值或最大(dà)值。這(zhè)樣就可(kě)以覆蓋到所有不同的(de)情形，而不再需要嵌套更多(duō)的(de)IF函數來(lái)滿足每種可(kě)能的(de)情形。

還(hái)應值得(de)注意的(de)是N()函數的(de)運用(yòng)，它是Excel中最短的(de)函數之一，其作用(yòng)是将非數值形式的(de)值轉化(huà)爲數字，即數值型數據依然不變，而将日期轉換成序列值，TRUE轉換成1，其它的(de)文本型數據轉換成0。上述公式中若雜(zá)質未檢出需輸入ND，而如果直接将文本型的(de)值ND與數字型的(de)值0.05比大(dà)小，實際結果會出乎大(dà)家的(de)意料，那就是ND會比0.05大(dà)。所以配上N()函數後，ND會轉換爲0，這(zhè)樣它就比0.05要小了(le)，IF函數才會按照(zhào)需求進行正确的(de)邏輯判斷。

我們最後再将所有計算(suàn)單元格進行鎖定，防止公式被任意的(de)更改，輔以數據驗證、條件格式、打開密碼等，并進行系統化(huà)的(de)計算(suàn)模闆驗證，就可(kě)以投入使用(yòng)了(le)。

一勞便可(kě)永逸！

結語：

小編此番講解希望對(duì)制藥同仁們有所啓示與幫助，畢竟Excel電子表格的(de)設計與計算(suàn)是受到監管機構重點關注的(de)。

用(yòng)好Excel能爲提高(gāo)工作效率帶來(lái)立竿見影(yǐng)的(de)效果，投入成本低，适應性強。實驗室的(de)信息化(huà)與自動化(huà)可(kě)以從Excel開始。

eInfotree軟件能彌補Excel在GxP領域合規性上的(de)差距，實現Excel的(de)用(yòng)戶訪問控制，審計追蹤，電子簽名，完全符合CFR21 Part11的(de)要求，讓制藥企業無顧慮地享用(yòng)電子表格帶來(lái)的(de)便利。