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

幾乎所有(yǒu)藥廠(chǎng)實驗✘ ∑★室都(dōu)會(huì)用(yòng)到(dào)Excel來(lái)進行★β☆♦(xíng)檢測結果計(jì)算(suàn)。這(zhè)些(xiē)用(→★>yòng)于檢測結果計(jì)算(suàn)的(de ≥←€)Excel表格大(dà)多(duō)是(shì)将公式固化(h ₹>uà)（鎖定）下(xià)來(lái)作(z♣‍•∏uò)為(wèi)模闆使用(yòng)的(de)，比如(↓ ¥•rú)含量、溶出度、含量均勻度等的(de)計(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è)種情況下(xià)，計(♣α₽&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) §'♠模闆的(de)固化(huà)，避免臨時(s£₩hí)修改公式的(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ú)下(xià)需求：

•    在進行(xíng)樣品中有≈£(yǒu)關物(wù)質檢驗時(shí)，需進3針平行(xíng σ)樣品，計(jì)算(suàn)每針不(bù)同雜(zá)質'‌的(de)相(xiàng)對(duì)保留時(shí)間(ji£★ā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.0$₽5%；

•    如(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ì)動算(sα"uàn)出，如(rú)果雜(zá)質檢出就(j≤•®iù)輸入相(xiàng)應的(de)保留時(shí)間(jiān)和(hé)β&大(dà)小(xiǎo)，如(rú)果雜(¶≈ ©zá)質未檢出就(jiù)輸入ND。

下(xià)面以H12單元格雜(zá)質≠∑σ¥1平均值計(jì)算(suàn)公式為(wèi)例進行(xín≠"↕g)具體(tǐ)講解，其它雜(zá)質平均值計(jì)算(suàn)λ ≠單元格公式依次填充并修改相(xiàng)應的(de)篩選條€§☆件(jiàn)即可(kě)。

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

IF(AND(N(E3)<0.05,N(E12)&₽₽lt;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),AVERAGE(E3,E12,E÷'$21),…：如(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″)/C£ ÷¥OUNTIFS (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ì)Eδγ©₽3、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)，它是(s ‍hì)Excel中最短(duǎn)的(de)函數(shù)之一(yī)，↕σ♦其作(zuò)用(yòng)是(shì)将非數(shù)值形式的(de)值‌>♠≈轉化(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à)家(j←'$iā)的(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ī)構重點關注的(d™ e)。

用(yòng)好(hǎo)Excel能(n‌≠éng)為(wèi)提高(gāo)工(gōng)作(zuò)效率帶來(lái)π★©立竿見(jiàn)影(yǐng)的(de)效果，投入↕↓成本低(dī)，适應性強。實驗室的(de)信息化(huà)與自(zì↓¥₩≤)動化(huà)可(kě)以從(cóng)Excel開(kāi↑α)始。

eInfotree軟件(jiàn)能(néng)彌補Excel在G ←$xP領域合規性上(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)便利×¥≈。