有關物質自動計算與判斷的Excel表格優化設計案例

幾乎所有藥廠實驗室都會用到Excel來進行檢測結果計算。這些用于檢測結果計算的Excel表格大多是將公式固化(鎖定)下來作為模板使用的,比如含量、溶出度、含量均勻度等的計算模板。而另外一些表格,比如用于有關物質或者溶殘計算的,由于雜質或殘留溶劑并不像含量那樣穩定出峰,在很多實驗室并未被固化為模板使用。這種情況下,計算不同批次有關物質或溶殘檢驗結果時,分析人員往往需要修改計算公式或者進行人工判斷。

使用這種計算公式不固定的,依據不同批次具體情況臨時修改公式的計算表格,顯然是有合規風險的,既為驗證增加了麻煩,也為表格的復核帶來了不便,需要復核人員花費更多的時間去核對。FDA有多封警告信涉及這一問題。

本文將介紹如何通過優化設計,做到有關物質計算模板的固化,避免臨時修改公式的情況,從而提高工作效率,降低風險。

下邊我們通過某藥企一款產品的有關物質計算表格優化前后的對比進行具體講解。

優化前效果:

有關物質自動計算與判斷的Excel表格優化設計案例

我們看到雜質出峰并不穩定,如雜質1只在第2個樣品里檢出,雜質6只在第3個樣品里檢出。點擊”公式”選項卡-顯示公式,雜質平均值的計算公式更是五花八門,完全是”具體情況具體分析”,分析人員是結合了實驗的實際情況后再進行相應的計算公式編輯。

有關物質自動計算與判斷的Excel表格優化設計案例

造成這種需要臨時修改雜質平均值計算公式的原因是該產品有關物質檢測時有如下需求:

  • 在進行樣品中有關物質檢驗時,需進3針平行樣品,計算每針不同雜質的相對保留時間,以及3針樣品的每個雜質相對保留時間平均值、雜質百分值大小的平均值以及總雜質;
  • 單個雜質:如果三個平行樣品結果均不小于0.05%,則報告三針結果的平均值;
  • 如果三個平行樣品結果均小于0.05%,則報告結果為小于0.05%;
  • 如果其中一個樣品小于0.05%,其它兩個樣品均不小于0.05%,則報告這兩針結果的平均值;
  • 如果其中兩個樣品均小于0.05%,其它一個樣品不小于0.05%,則報告最大的這針結果;
  • 總雜質:僅計算含量大于等于0.05%(報告限)的雜質。

表格設計難點分析:

由此可見,三針樣品雜質平均值的計算公式,根據每針樣品中該雜質與0.05%(報告限)的大小不同而不同。可能是一針結果的最大值,也可能是兩針或三針結果的平均值,所以單純的平均值公式并不能適用于所有的情況,無法簡單固化。這里可以結合IF條件判斷函數來進行雜質平均值公式的設計優化。

優化后效果:

我們首先用綠色背景標識數據待輸入的單元格區域,用藍色背景標識計算處理單元格區域。此外給每針樣品預留8個雜質峰的輸入欄,以便容納未來隨著穩定性考察時間的增長,可能檢測到的更多雜質峰。每個雜質的百分值大小依然通過面積歸一化法由色譜系統自動算出,如果雜質檢出就輸入相應的保留時間和大小,如果雜質未檢出就輸入ND。

有關物質自動計算與判斷的Excel表格優化設計案例

下面以H12單元格雜質1平均值計算公式為例進行具體講解,其它雜質平均值計算單元格公式依次填充并修改相應的篩選條件即可。

由于公式比較復雜,我們采取”公式:解釋”的寫法進行表述。

IF(AND(N(E3)<0.05,N(E12)<0.05,N(E21)<0.05),〞<0.05〞,…:如果3針平行樣E3、E12、E21大小(%)均小于0.05,則雜質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大小(%)均大于等于0.05,則雜質1平均值(%)為這3針的平均值;

SUMIFS(E$3:E$29,C$3:C$29,”雜質1″,E$3:E$29,”>=0.05″)/COUNTIFS (C$3:C$29,”雜質1″,E$3:E$29,”>=0.05″))):如果3針平行樣E3、E12、E21大小(%)既有大于等于0.05又有小于0.05,則雜質1平均值(%)為通過條件求和函數SUMIFS(只計E3、E12、E21中大于等于0.05的值之和)除以條件計數函數COUNTIFS(只計E3、E12、E21中大于等于0.05的單元格數)間接得到的滿足大于等于0.05條件的平均值或最大值。這樣就可以覆蓋到所有不同的情形,而不再需要嵌套更多的IF函數來滿足每種可能的情形。

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

我們最后再將所有計算單元格進行鎖定,防止公式被任意的更改,輔以數據驗證、條件格式、打開密碼等,并進行系統化的計算模板驗證,就可以投入使用了。

一勞便可永逸!

結語:

小編此番講解希望對制藥同仁們有所啟示與幫助,畢竟Excel電子表格的設計與計算是受到監管機構重點關注的。

用好Excel能為提高工作效率帶來立竿見影的效果,投入成本低,適應性強。實驗室的信息化與自動化可以從Excel開始。

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

广东11选5开奖直播