有关物质自动计算与判断的Excel表格优化设计案例

武汉点风科技

几乎所有药厂实验室都会用到Excel来进行检测结果计算。这些用于检测结果计算的Excel表格大多是将公式固化（锁定）下来作为模板使用的，比如含量、溶出度、含量均匀度等的计算模板。而另外一些表格，比如用于有关物质或者溶残计算的，由于杂质或残留溶剂并不像含量那样稳定出峰，在很多实验室并未被固化为模板使用。这种情况下，计算不同批次有关物质或溶残检验结果时，分析人员往往需要修改计算公式或者进行人工判断。

使用这种计算公式不固定的，依据不同批次具体情况临时修改公式的计算表格，显然是有合规风险的，既为验证增加了麻烦，也为表格的复核带来了不便，需要复核人员花费更多的时间去核对。FDA有多封警告信涉及这一问题。

本文将介绍如何通过优化设计，做到有关物质计算模板的固化，避免临时修改公式的情况，从而提高工作效率，降低风险。

下边我们通过某药企一款产品的有关物质计算表格优化前后的对比进行具体讲解。

优化前效果：

我们看到杂质出峰并不稳定，如杂质1只在第2个样品里检出，杂质6只在第3个样品里检出。点击“公式”选项卡-显示公式，杂质平均值的计算公式更是五花八门，完全是“具体情况具体分析”，分析人员是结合了实验的实际情况后再进行相应的计算公式编辑。

造成这种需要临时修改杂质平均值计算公式的原因是该产品有关物质检测时有如下需求：

•       在进行样品中有关物质检验时，需进3针平行样品，计算每针不同杂质的相对保留时间，以及3针样品的每个杂质相对保留时间平均值、杂质百分值大小的平均值以及总杂；

•       单个杂质：如果三个平行样品结果均不小于0.05%，则报告三针结果的平均值；

•       如果三个平行样品结果均小于0.05%，则报告结果为小于0.05%；

•       如果其中一个样品小于0.05%，其它两个样品均不小于0.05%，则报告这两针结果的平均值；

•       如果其中两个样品均小于0.05%，其它一个样品不小于0.05%，则报告最大的这针结果；

•       总杂质：仅计算含量大于等于0.05%（报告限）的杂质。

表格设计难点分析：

由此可见，三针样品杂质平均值的计算公式，根据每针样品中该杂质与0.05%（报告限）的大小不同而不同。可能是一针结果的最大值，也可能是两针或三针结果的平均值，所以单纯的平均值公式并不能适用于所有的情况，无法简单固化。这里可以结合IF条件判断函数来进行杂质平均值公式的设计优化。

优化后效果：

我们首先用绿色背景填充数据输入单元格区域，用蓝色背景填充计算处理单元格区域。此外给每针样品预留8个杂质峰的输入栏，以便容纳未来随着稳定性考察时间的增长，可能检测到的更多杂质峰。每个杂质的百分值大小依然通过面积归一化法由色谱系统自动算出，如果杂质检出就输入相应的保留时间和大小，如果杂质未检出就输入ND。

下面以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的要求，让制药企业无顾虑地享用电子表格带来的便利。

文章转自微信公众号：实验室信息化与自动化

syhorchid

谢谢分享

北重楼

谢谢，看一下！！

Shadow_Way

感谢分享啊

gyy2

感谢分享感谢分享

YYsun

感谢分享感谢分享

yangxiaocheng

谢谢，谢谢分享，学习学习

boomer

谢谢楼主分享

wwwy1

感谢分享，大佬