有(yǒu)關物(wù)質自(zì)動計(jì)算(suàσ™n)與判斷的(de)Excel表格優化(huà)設計(jì)案例α© 

幾乎所有(yǒu)藥廠(chǎng)實驗室都(dōu)會(h↕≤δuì)用(yòng)到(dào)Exce≤$l來(lái)進行(xíng)檢測結果計(jì)算(suàn)。這(zh≥φè)些(xiē)用(yòng)于檢測結果計(jì)算(suàn)的(de) ♥✔"Excel表格大(dà)多(duō)是(shì)将‍ π•公式固化(huà)（鎖定）下(xià)來(lái)作§δ€(zuò)為(wèi)模闆使用(yòng)的(de)§★，比如(rú)含量、溶出度、含量均勻度等的(d‍©e)計(jì)算(suàn)模闆。而另外∞≤(wài)一(yī)些(xiē)表格，比如(rú)‍∏♥​用(yòng)于有(yǒu)關物(wù)質或者溶殘計(jì)算(suànγ )的(de)，由于雜(zá)質或殘留溶劑并不(↕ ‍bù)像含量那(nà)樣穩定出峰，在很(hěn)多(duō)實驗室并未被固×←♦化(huà)為(wèi)模闆使用(yòng)。這(zhè)種情況下(x≤♥₩♠ià)，計(jì)算(suàn)不(bù)同批次有(yǒu)關物(♣✘σwù)質或溶殘檢驗結果時(shí)，分(fēn)析人(rén)員(yuá£<±≥n)往往需要(yào)修改計(jì)算(suàn)公式或者進行™$(xíng)人(rén)工(gōng)判斷。

使用(yòng)這(zhè)種計(jì)算(suàn)公式不('↑bù)固定的(de)，依據不(bù)同批次具體(☆₽γ∏tǐ)情況臨時(shí)修改公式的(de)計(jì)算(suànδ​ )表格，顯然是(shì)有(yǒu)合規風(fēng>←)險的(de)，既為(wèi)驗證增加了(le)φ₽€麻煩，也(yě)為(wèi)表格的(de)複核帶來(lái)了(le) Ω不(bù)便，需要(yào)複核人(rén)員(yu™÷↔$án)花(huā)費(fèi)更多(duō)的(de)✘δβ時(shí)間(jiān)去(qù)核對(duì)。FDA有(yǒu∞§ )多(duō)封警告信涉及這(zhè)一(yī)→ε問(wèn)題。

本文(wén)将介紹如(rú)何通(tōng)過‌≥'優化(huà)設計(jì)，做(zuò)到(↔δ✔★dào)有(yǒu)關物(wù)質計(jì)算(suàn)模闆的(d'♦e)固化(huà)，避免臨時(shí)修改公式的(de)情✔×況，從(cóng)而提高(gāo)工(gōng)作(zuò)效率，降低(d₽×€✘ī)風(fēng)險。

下(xià)邊我們通(tōng)過某藥企一(yī)款産品的(de)有(•™←↕yǒu)關物(wù)質計(jì)算(suàn)表格優化(®£φ♠huà)前後的(de)對(duì)比進行(xíng)具體(tǐ↑ε)講解。

優化(huà)前效果：

我們看(kàn)到(dào)雜(zá)質出峰并不(bù)穩定，如(rú←≠¶δ)雜(zá)質1隻在第2個(gè)樣品裡(•✘lǐ)檢出，雜(zá)質6隻在第3個(gè)樣品裡(l♠♥↓≤ǐ)檢出。點擊”公式”選項卡-顯示公式，雜(zá)質≠↑平均值的(de)計(jì)算(suàn)公式更是($₩shì)五花(huā)八門(mén)，完全是(shì)”具體(tǐ)情況具體(↑ ‍tǐ)分(fēn)析”，分(fēn)析人(rén)>‍員(yuán)是(shì)結合了(le)實驗的(de)實際情況☆♥$後再進行(xíng)相(xiàng)應的(de)計(jì)算"$≤(suàn)公式編輯。

造成這(zhè)種需要(yào)臨時(shí)修改±✔雜(zá)質平均值計(jì)算(suàn)公式的(Ω∑de)原因是(shì)該産品有(yǒu)關物(wù→∏↓)質檢測時(shí)有(yǒu)如(rú)下(x∞™φià)需求：

•    在進行(xíng)樣品中有(yǒu)關物(wù€≤£)質檢驗時(shí)，需進3針平行(xíng)樣品，計(∞<≤'jì)算(suàn)每針不(bù)同雜(zá)質的(de)相(αδ®↑xiàng)對(duì)保留時(shí)間(j☆ ₽↔iān)，以及3針樣品的(de)每個(gè)雜(zá)質相(xiàng♣♠​ε)對(duì)保留時(shí)間(jiān)平均值、雜(zá)質百分(‍ ‌←fēn)值大(dà)小(xiǎo)的(de)平均值以及總雜(zá)質；

•    單個(gè)雜(zá)質：如(rú)果三個(gè)平行(xíng)樣品結±∑果均不(bù)小(xiǎo)于0.05%，則報(bào)告三> ♠↓針結果的(de)平均值；

•    如(rú)果三個(gè)平行(xíng)樣£₹★π品結果均小(xiǎo)于0.05%，則報(bào)告結果為(∑γ₩≈wèi)小(xiǎo)于0.05%；

•    如(rú)果其中一(yī)個(gè)樣品小(xiλ¥↓ǎo)于0.05%，其它兩個(gè)樣品均§φ©€不(bù)小(xiǎo)于0.05%，則報(bào)告∏↑∞φ這(zhè)兩針結果的(de)平均值；

•    如(rú)果其中兩個(gè)樣品均小(xiǎo)于0.☆→★$05%，其它一(yī)個(gè)樣品不(bù)小(xiǎo♣₩)于0.05%，則報(bào)告最大(dà♠®δ∏)的(de)這(zhè)針結果；

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

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

由此可(kě)見(jiàn)，三針樣品雜(zá)質∞™平均值的(de)計(jì)算(suàn)公式，根據每針樣品中該雜(zá)質與&✘0.05%（報(bào)告限）的(de)大(©®$dà)小(xiǎo)不(bù)同而不(bù)同。可(kě)能(néng)是(<¶&✔shì)一(yī)針結果的(de)最大(dà)值，也(yě)可(kě)±₩ ☆能(néng)是(shì)兩針或三針結果的(de)平均值，所以單純的(de)平™ 均值公式并不(bù)能(néng)适用(yòng)‍β于所有(yǒu)的(de)情況，無法簡單固化(huà)。這(zhè)裡(lΩε£ǐ)可(kě)以結合IF條件(jiàn)判斷函₹•∑數(shù)來(lái)進行(xíng)雜(zá)質平均值公↑​式的(de)設計(jì)優化(huà)。

優化(huà)後效果：

我們首先用(yòng)綠(lǜ)色背景标識數(shù)據待輸入ε  ​的(de)單元格區(qū)域，用(yòng)藍(lán)色背景标識計(j$ ☆ì)算(suàn)處理(lǐ)單元格區(qū)域↑π™σ。此外(wài)給每針樣品預留8個(gè)雜(zá)質∞&​峰的(de)輸入欄，以便容納未來(lái)随著(zhe)穩定性考察時( £∏Ωshí)間(jiān)的(de)增長(cháng)，可(kě)能(néng‌β)檢測到(dào)的(de)更多(duō)雜(zá)質峰。每↔×≤個(gè)雜(zá)質的(de)百分(fē←≤ n)值大(dà)小(xiǎo)依然通(tōng)過面積歸一 ₽(yī)化(huà)法由色譜系統自(zì)動算(suàn)δ₩ 出，如(rú)果雜(zá)質檢出就(jiù)輸入"≥$"相(xiàng)應的(de)保留時(shí)間(jiān)和(hé)大(dàφ↕♦λ)小(xiǎo)，如(rú)果雜(zá)質 α$♥未檢出就(jiù)輸入ND。

下(xià)面以H12單元格雜(zá)質1平均值計(jì)算(s✘φ>πuàn)公式為(wèi)例進行(xíng)具體(tǐ)講解，其它雜(zá∏φ✔)質平均值計(jì)算(suàn)單元格公≠>式依次填充并修改相(xiàng)應的(d×>£₩e)篩選條件(jiàn)即可(kě)。

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

IF(AND(N(E3)<0.05 ∑₩λ,N(E12)<0.05,N(E21)<0.♦<β05),〞<0.05〞,…：如(rú)果3針平行(xí£™♣ng)樣E3、E12、E21大(dà)小(xiǎo)（%）均小($>Ω‌xiǎo)于0.05，則雜(zá)質1平均值（%）←♣顯示<0.05；

IF(AND(N(E3)>=0.05,N(E12)>★≈=0.05,N(E21)>=0.05),AVER¥∞™AGE(E3,E12,E21),…：如(rú)果3針平行(xíngαα←)樣E3、E12、E21大(dà)小(xiǎo)（%）均大(dà)±$Ω♣于等于0.05，則雜(zá)質1平均值（%）為(β$↓®wèi)這(zhè)3針的(de)平均值；

SUMIFS(E$3:E$29,C$3:C$29,”雜(×☆♣±zá)質1″,E$3:E$29,”>=0.05″)/COUNT¶©≤€IFS (C$3:C$29,”雜(zá)質π♦1″,E$3:E$29,”>=0.05″))•→)：如(rú)果3針平行(xíng)樣E3、E12、E21₩φ大(dà)小(xiǎo)（%）既有(yǒu)大(dà)于等于0.05又(yòu&φ↓≠)有(yǒu)小(xiǎo)于0.05，則雜(zá)質1平均值‌™♠π（%）為(wèi)通(tōng)過條件(jiàn)求和(hé)函數≤‍♣λ(shù)SUMIFS（隻計(jì)E3、E12、E21中大★♦λΩ(dà)于等于0.05的(de)值之和(hé)）除以條件±•÷≈(jiàn)計(jì)數(shù)函數(shù)COUNTIFS（隻計(j€←ì)E3、E12、E21中大(dà)于等于0.05的(de)單元格數(shù)₽★）間(jiān)接得(de)到(dào)的(≈↓<♠de)滿足大(dà)于等于0.05條件(jià$∞→★n)的(de)平均值或最大(dà)值。這(zhè)樣就(jiù)可↓∏☆(kě)以覆蓋到(dào)所有(yǒu)不(bù)同的(de)情形，而不(bù§±↔")再需要(yào)嵌套更多(duō)的(de)IF函數(π​αshù)來(lái)滿足每種可(kě)能(néng)的(dε‌e)情形。

還(hái)應值得(de)注意的(de)Ω$​是(shì)N()函數(shù)的(de)運用(yòng)，它是(shì)Exα₽↔✔cel中最短(duǎn)的(de)函數(shù)之一(yī)，φ ∏其作(zuò)用(yòng)是(shì)将非數(shù)值形式的(d&‍‍≠e)值轉化(huà)為(wèi)數(shù)字，即數(sh'​ ù)值型數(shù)據依然不(bù)變，而将日(rì)期轉換成序列≤ ε值，TRUE轉換成1，其它的(de)文(wén)本型→&數(shù)據轉換成0。上(shàng)述公式中若雜(zá)質未檢出需↔&輸入ND，而如(rú)果直接将文(wén)&♦•本型的(de)值ND與數(shù)字型的(de)值0.05比大(dà)小(♣>™βxiǎo)，實際結果會(huì)出乎大(dà)家(jiā<∑∑¶)的(de)意料，那(nà)就(jiù)是(shì♦β)ND會(huì)比0.05大(dà)。所以配✘×上(shàng)N()函數(shù)後，ND≥ 會(huì)轉換為(wèi)0，這(zhè)樣÷&&•它就(jiù)比0.05要(yào)小(xiǎo)了(le)，IF函數(≠₽ shù)才會(huì)按照(zhào)需求進行(xíng)↑$正确的(de)邏輯判斷。

我們最後再将所有(yǒu)計(jì)算(suàn)單元格進行(xíng)‍÷鎖定，防止公式被任意的(de)更改，輔以≥<↕‍數(shù)據驗證、條件(jiàn)格式、打開(kāi)密碼等，并進行(xí™∞ng)系統化(huà)的(de)計(jì)‍γΩ算(suàn)模闆驗證，就(jiù)可(kě)以投入₩δ使用(yòng)了(le)。

一(yī)勞便可(kě)永逸！

結語：

小(xiǎo)編此番講解希望對(duì)制(zhì)藥同仁們有(y>≥×≥ǒu)所啓示與幫助，畢竟Excel電(diàn)子(zǐ)表格的(de)設計→©(jì)與計(jì)算(suàn)是(shì)受到(dào)監管機(jī)構重•¥點關注的(de)。

用(yòng)好(hǎo)Excel能(néng)為(wβ​èi)提高(gāo)工(gōng)作(zuò)效率帶來(lái)立♠¥←竿見(jiàn)影(yǐng)的(de)效果，λ♥≥投入成本低(dī)，适應性強。實驗室的(de)信息化(h±γuà)與自(zì)動化(huà)可(kě)以從(cóng)Excel開(kāiα♦)始。

eInfotree軟件(jiàn)能(néng)彌補Excel在GxP領 <☆域合規性上(shàng)的(de)差距，實現(xiàn)Excel的(de)用 ∞≤(yòng)戶訪問(wèn)控制(zhì)'<←©，審計(jì)追蹤，電(diàn)子(zǐ)簽名，完全符合CFR21 ≈×£★Part11的(de)要(yào)求，讓制(zhì)藥企業(yè)無顧∑ π≥慮地(dì)享用(yòng)電(diàn)子(zǐ)¶∞表格帶來(lái)的(de)便利。