Excel电子表格验证

------------------------------
1、Excel电子表格验证倒底是啥？
------------------------------
我们谈论任何话题都不能离开GMP这个根本，GMP的宗旨是什么？或者说为什么要实行GMP？
建议大家再去查看一下GMP条款的第三条：
“旨在最大限度地降低药品生产过程中污染、交叉污染以及混淆、差错等风险，确保持续稳定地生产出符合预定用途和注册要求的药品。

”
显然针对Excel电子表格的验证，目地是为了“防止出错”，这是我们今天分享内容的核心原则。

现在回到Excel，是不是所有的Excel电子表格都需要验证？
不是，只有涉及到产品放行、关键控制指标的计算表格，必须经过验证后才能使用。

而平时方便自己简单计算、用于日常数据汇总的Excel并不需要经过验证。

根据计算机系统的三个部分：输入、计算和输出。

下从数据输入、公式核对和数据测试三个方面来介绍一下。

------------------------------
2、数据输入是从源头把关
------------------------------
大多数人把Excel验证的重点放在公式的检查、计算结果的复核，往往会忽略数据输入的重要性，想一想，如果数据一开始就是错误的，那后面的公式和结果又有什么意义呢？
拿到一个Excel模板，第一件事是要分清哪些单元格是能够输入内容的，哪些单元格是不需要输入内容，然后把这些单元格锁定。

具体的办法是，在”单元格的格式“窗口中，选择”锁定“。

然后在”保护工作表“窗口中选择”保护工作表及锁定的单元格内容“，再加上密码。

这样一套组合拳下来，那些提示性单元格、计算公式、结果和结论，再也不能修改。

除了输入数据的单元格外，其它单元格均应锁定，包括空白单元格，也不能进行修改、删除、更改类型等操作，但可以进行选择和复制的操作。

数据输入验证时要用到一个重要的功能：“数据有效性”，在英文版的Excel中叫做“Datavalidation”，最新版的Excel2016中已经改成“数据验证”。

如果输入文字内容，比如输入品名，这种相对固定的内容，可以选择“序列”的功能，提前输入我们需涉及到的产品。

在单元格输入时，可以直接用下拉菜单选择对应的产品，效率高，不会出错。

如果输入一个没有的产品名称，就会出现错误提示。

如果输入的是数据，在输入时要对数据的范围进行限制，对数据的正确性做一个初步的判断。

比如峰面积、稀释倍数，一般来说都会是正整数；
检验日期，这是一个日期，应该在某个日期之后。

称样量，应该是一个小数，大于0；
对照品含量，一定是个百分比数，介于0.9-1.0之间；
做好了这些，数据验证工作就做好一半了。

在验证也可以有意的输入一些符号、乱码，或者明显不合逻辑的数值（比如负值），看一下是否能够出现错误提示信息，如果不改更，无法输入下一个数据。

------------------------------
3、公式审核
------------------------------
单元格的坐标是用字母和数字组成的，比如：A1、C1:C10。

公式就是用这样的方式来引用单元格范围，单元格的引用方式包括相对引用和绝对引用，用“$”表示，这两种引用方式的区别是在进行公式自动填充时，单元格是否自动变化，比如：
这里给出一个小技巧，可以用“F4”这个快捷键来切换单元格的四种引用方式。

顺序是:
A1 -->$A$1 --> A$1 -->$A1
验证时要显示公式，在“公式”标签选择“显示公式”，这时所有的公式都以单元格引用的方式显示，也可以打印到纸张上进行核对，根据每个人的不同经验和习惯。

一个好的习惯是公式书写的规范，可以提高验证的效率，减少出错的机会，比如下面这个公式：
=$C18*$H$9/$B$6/$F$6*$H$6*100/$E18*$F$18 …….(a)
是不是有一种掉到钱堆里的感觉，一时半会理不出头绪，这种公式验证的难度大、错误多。

如果写成下面这样，将分子和分母用括号区分，计算结果是完全一样的，公式的可读性却大大增加：=($C18*$H$9)/($B$6*$F$6)*($H$6*100*$F$18)/$E18 …….(b)
更好的办法是采用定义名称的方式，将单元格或范围定义成具体的名称，在公式中可以直接引用名称，这已经和实际的公式没有什么区别了，基本上可以避免低级错误：
=($C18*样品稀释)/(标示量*对照品稀释)*(F值*100*平均片重)/$E18 …….(c)
------------------------------
4、数据测试
------------------------------
Excel计算模板使用前必须经过数据测试，确认没有错误，才能最终发布。

这是验证中最重要、也是工作量最大的部分，主要目地是为了发现遗漏的错误和一些特别的错误，不能省略这项工作，小错误害死人的事情发生太多。

方法简单又枯燥，输入数据，然后核对结果，测试过程要有记录，要将每一个批次单独命名存盘，打印存档备查。

数据量致少要在50批以上。

不要只核对最终结果，要对每一个过程数据进行复核。

如果有条件，可以对结果进行手工复核。

要用大量的已有的检验数据来进行测试。

经过这样的测试，一些数据类型错误、格式错误、有效性设置方面的错误、公式的计算和显示错误都能发现。

要选用一些特别的数据进行测试。

比如有意输入一些不合格的数值，看看最终结果是否会体现为不合格。

尤其要设计一些限度值边缘的数据，看一下限度值上下数据的微小变化，能否导致结论的变化，这个测试很重要，因为微小的数据变化就会涉及到合格与否的判断。

------------------------------
5、模板的发布也很重要。

------------------------------
经过测试的模板应该将输入内容清空，工作表经保护，并加上密码。

正式发布的模板应该按GMP文件管理，有相应的审批流程，存档备案。

为了防止模板被随意修改，模板编制完成后需要对文件进行加密运算，常用的Hash工具很多，计算结果为唯一值，只要模板内容有任何的变动，计算值都不一样，验证开始时应首先确认文件的MD5值是否一致，见下图：
总结：
Excel电子表格验证采用GMP的理念，根本目地是为了防止出现差错，计算错误带来的后果，往往比操作失误更严重，事后查找原因难度也很大。

验证只是一种手段，要在模板编制过程就养成良好的习惯和规范，从源头避免错误。