快速搞定Excel计算表格在数据完整性/可靠性方面的设定

​

先给大家看看一个Excel计算表格的示例。需要关注的地方，用红色圈出。

 http://s14/mw690/006UxxM0zy7dS6KyAod7d&690

“路径”后的单元格，写入函数=cell(“filename”)。这个单元格会自动显示Excel文件的安装位置（“E:\计算\产品A含量计算.xlsx”）。检查这个单元格，你就知道，分析员是否使用指定位置的经过验证的Excel表格。

“打印日期”后的单元格，写入函数=today()。这个单元格显示当前日期，即打印日期（“2017年5月10日”）。检查这个单元格，你就可以确保，分析员没有倒签或者提前写日期。

需要输入原始数据的单元格，用灰色底纹。隔行错色，可以有效避免看错行。计算人和审核人，都可以很快找到这些单元格。避免遗漏数据。

你使用的Excel计算表格，是否有考虑这些数据完整性/可靠性方面的设定？

 

Excel计算表格的优势

 

Excel计算表格广泛用于QC检测的结果计算。对计算人而言，它计算快速方便；对审核人而言，只需要复核数据输入是否正确，无需复核计算。与手工计算相比，Excel计算表格大大提高了计算过程的效率和可靠性。

Excel计算表格的发行速度也远远快于纸质记录。第一个Excel计算表格的设计和验证可能花些时间。将其作为模板，编制更多的Excel计算表格时，每个表格的设计时间通常1-2小时，验证时间1-2小时。可以说第一天设计和验证完，第二天就可以马上投入使用。

 

法规当局乐于见到工业界使用Excel计算表格取代手工计算。EDQM指南“VALIDATIONOFCOMPUTERISEDSYSTEM,ANNEX1:VALIDATIONOFCOMPUTERISEDCALCULATIONSYSTEMS:EXAMPLEOFVALIDATIONOFIN-HOUSESOFTWARE”（PA/PH/OMCL(08)872R）详细讨论了Excel计算表格的管理和验证。

 

和任何其它工具一样，Excel计算表格也有其风险。我在这里分享的，是指南中没有讨论或者详细说明的方面，主要是如何控制Excel计算表格在数据完整性/可靠性方面的风险。

 

下面是更多干货。

 

问题1：如何避免不要的法规要求？

 

（1）计算完成后，直接打印结果为纸质记录；不应保存有批次计算数据的Excel表格。这点在指南中有说明，但很多人没有注意到。如果保存批次计算数据，会把Excel计算表格从计算器变成电子数据，要吃缺陷项的。

 

（2）不要把判定检测是否合格的机制加入Excel计算表格。Excel计算表格作为计算器就可以了，千万不要弄成批次放行工具。

 

问题2：如何避免Excel计算表格被错误地修改或删除？

http://s7/mw690/006UxxM0zy7dS6RmruSc6&690

工作簿密码保护：保护那些应当受保护的单元格，例如有公式的单元格。

 

工作表密码保护：避免工作簿被错误删除，或者随意添加工作簿。

 

文件修订密码保护：如果没有这个密码，用户可能误操作，将批次计算数据保存在经过验证的Excel计算表格中，导致文件被覆盖，验证状态失效。

 

安装位置：Excel计算表格，应当安装在一个Windows普通用户没有更改权限的文件夹内。也就是说，普通用户无法增加或者删除这个文件夹里的文件。

 

问题3：数字舍入导致“所见非所得”，如何控制该风险？

 

Excel的单元格，可以设定小数点后数字显示位数。有时Excel会“帮助”你自动设定。

 

单元格内显示的数字，可能不是全部的数字，“所见非所得”。例如，下图峰面积的单元格显示“10”，而实际数字是9.5。

http://s10/mw690/006UxxM0zy7dS6UFTDze9&690

这种情况，审核人看纸质记录是无法发现的。对于峰面积小的杂质，这种风险无法忽略。

 

为了应对这种风险，可以增加单元格的小数点显示位数。在上面的情况，可以设定单元格，多显示2位小数。例如，峰面积10，使其显示为10.00。管理程序应说明这个要求。

 

最终计算结果的小数点后位数，也应根据需求确定。有的公司的舍入规则是“四舍六入五成双”，而Excel只能做到“四舍五入”。这种情况，可以多留几位小数点后数字，分析员转抄结果的时候，人工执行舍入。

 

小诀窍：快捷键“CTRL+1”可以进入格式设定。

 

问题4：用什么数据执行验证？

 

可以用过去的批次数据，或者模拟数据，执行Excel计算表格的验证。

 

过去的批次数据，可能没有检测到某些杂质，没有杂质峰面积值。为了验证这个杂质计算单元格的准确性，我们可以使用模拟数据。

 

有时Excel计算表格中设定了一些判断机制，例如系统适用性分离度大于5并且RSD小于2%时“通过”，否则“失败”。这时候，需要用一些模拟数据，确认系统适用性失败的时候，Excel计算表格的判定是否正确。

 http://s14/mw690/006UxxM0zy7dS6WrQ2V8d&690

问题5：如何评估手工计算结果与Excel计算结果之间的差异？

 

由于手工计算在获得中间计算结果时，会进行舍入，而Excel计算的中间结果是不会进行舍入的，因此手工计算结果和Excel计算结果，可能出现一定程度的差异。

 

我们只需要比较最终计算结果的差异，无需评估中间结果的差异。

 

我做过的验证，含量数据，手工计算结果与Excel计算结果的差异，通常出现在小数点后两位以后。杂质数据，有可能在小数点后第二位有差异；例如手工计算得到0.20%，Excel计算得到0.21%。

 

0.01%的差异是可以忽略的，不需要评估。

 

如果有人一定要进行评估，可以这样写：

 

（1）产品规格不高于0.50%。回顾过去一年的历史数据，合格批次最高报告值0.30%，OOS批次最低报告值0.60%。

 

（2）根据公司OOT程序，0.48%到0.50%之间的报告值，需要提起OOT调查。

 

因此，无论使用计算器计算还是Excel计算，都不影响产品合格与否的判断。

 

----------（正文结束）-----------

 

这篇文章，我最初发表在微信号公众号《重力询》。欢迎扫描二维码，关注《重力询》。

http://s7/small/006UxxM0zy7dS6YsR70a6&690