蒲公英 - 制药技术的传播者 GMP理论的实践者

搜索
查看: 23726|回复: 105
收起左侧

[张金巍] 很火的话题,Excel电子表格验证

  [复制链接]
大师
发表于 2016-8-16 05:29:17 | 显示全部楼层 |阅读模式

欢迎您注册蒲公英

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
本帖最后由 蒲公英 于 2016-8-16 05:31 编辑

文:楚广庆   来自蒲公英小一班微信群原创主题分享,转载请与作者联系,加入小一班,联系微信:kingway003

这是一个很火的话题,Excel电子表格模板的制作和验证是分不开的,我们做的不是Excel验证,我们要做的是一个能通过验证的Excel表格,今天我们从更实用的角度出发,围绕Excel来讨论具体的内容。
------------------------------
1Excel电子表格验证倒底是啥?
------------------------------
我们谈论任何话题都不能离开GMP这个根本,GMP的宗旨是什么?或者说为什么要实行GMP
建议大家再去查看一下GMP条款的第三条:
旨在最大限度地降低药品生产过程中污染、交叉污染以及混淆、差错等风险,确保持续稳定地生产出符合预定用途和注册要求的药品。
显然针对Excel电子表格的验证,目地是为了防止出错,这是我们今天分享内容的核心原则。
现在回到Excel,是不是所有的Excel电子表格都需要验证?
不是,只有涉及到产品放行、关键控制指标的计算表格,必须经过验证后才能使用。而平时方便自己简单计算、用于日常数据汇总的Excel并不需要经过验证。
根据计算机系统的三个部分:输入、计算和输出。
下从数据输入、公式核对和数据测试三个方面来介绍一下。
------------------------------
2、数据输入是从源头把关
------------------------------
大多数人把Excel验证的重点放在公式的检查、计算结果的复核,往往会忽略数据输入的重要性,想一想,如果数据一开始就是错误的,那后面的公式和结果又有什么意义呢?
拿到一个Excel模板,第一件事是要分清哪些单元格是能够输入内容的,哪些单元格是不需要输入内容,然后把这些单元格锁定。
具体的办法是,在单元格的格式窗口中,选择锁定
001.png

然后在”保护工作表“窗口中选择”保护工作表及锁定的单元格内容“,再加上密码。
002.png
这样一套组合拳下来,那些提示性单元格、计算公式、结果和结论,再也不能修改。除了输入数据的单元格外,其它单元格均应锁定,包括空白单元格,也不能进行修改、删除、更改类型等操作,但可以进行选择和复制的操作。
数据输入验证时要用到一个重要的功能:“数据有效性”,在英文版的Excel中叫做“Datavalidation”,最新版的Excel2016中已经改成“数据验证”。

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


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

004.png


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

如果输入的是数据,在输入时要对数据的范围进行限制,对数据的正确性做一个初步的判断。
比如峰面积、稀释倍数,一般来说都会是正整数;
006.png

检验日期,这是一个日期,应该在某个日期之后。
007.png

称样量,应该是一个小数,大于0;

对照品含量,一定是个百分比数,介于0.9-1.0之间;
008.png
做好了这些,数据验证工作就做好一半了。

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


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

公式就是用这样的方式来引用单元格范围,单元格的引用方式包括相对引用和绝对引用,用“$”表示,这两种引用方式的区别是在进行公式自动填充时,单元格是否自动变化,比如:

009.png

这里给出一个小技巧,可以用“F4”这个快捷键来切换单元格的四种引用方式。顺序是:
A1 -->$A$1 --> A$1 -->$A1

验证时要显示公式,在“公式”标签选择“显示公式”,这时所有的公式都以单元格引用的方式显示,也可以打印到纸张上进行核对,根据每个人的不同经验和习惯。
010.png

一个好的习惯是公式书写的规范,可以提高验证的效率,减少出错的机会,比如下面这个公式:
=$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值是否一致,见下图:

011.png

总结:

Excel电子表格验证采用GMP的理念,根本目地是为了防止出现差错,计算错误带来的后果,往往比操作失误更严重,事后查找原因难度也很大。验证只是一种手段,要在模板编制过程就养成良好的习惯和规范,从源头避免错误。



本帖被以下淘专辑推荐:

回复

使用道具 举报

药徒
发表于 2016-8-18 14:18:11 | 显示全部楼层
制药行业越走越偏啊!
回复

使用道具 举报

药徒
发表于 2016-8-16 06:43:59 | 显示全部楼层
谢谢整理,讲的很受用!
回复

使用道具 举报

药徒
发表于 2016-8-16 07:19:04 | 显示全部楼层
给了个验证模板啊这是
回复

使用道具 举报

药徒
发表于 2016-8-16 07:40:12 | 显示全部楼层
长知识了,谢谢楼主分享!
回复

使用道具 举报

药徒
发表于 2016-8-16 08:32:24 | 显示全部楼层
这些程序不是验证、而是控制。
回复

使用道具 举报

药徒
发表于 2016-8-16 08:35:06 | 显示全部楼层
电子表玩的溜啊。
回复

使用道具 举报

发表于 2016-8-16 08:35:12 | 显示全部楼层
感谢整理分享
回复

使用道具 举报

药徒
发表于 2016-8-16 08:37:32 | 显示全部楼层
谢谢楼主分享整理
回复

使用道具 举报

药徒
发表于 2016-8-16 08:40:00 | 显示全部楼层
学习,谢谢分享
回复

使用道具 举报

药徒
发表于 2016-8-16 08:41:18 | 显示全部楼层
回复

使用道具 举报

药徒
发表于 2016-8-16 08:44:45 | 显示全部楼层
楼主厉害!
回复

使用道具 举报

药徒
发表于 2016-8-16 08:46:08 | 显示全部楼层
现在回到Excel,是不是所有的Excel电子表格都需要验证?
不是,只有涉及到产品放行、关键控制指标的计算表格,必须经过验证后才能使用。而平时方便自己简单计算、用于日常数据汇总的Excel并不需要经过验证。

就这个,适用验证的情况多吗?哪位大侠指教一下。


(我接触的情况是,这个蛮虚的)
回复

使用道具 举报

发表于 2016-8-16 08:56:14 | 显示全部楼层
谢谢  长知识了 以前还没注意到这些
回复

使用道具 举报

药徒
发表于 2016-8-16 08:56:26 | 显示全部楼层
给力的分享,谢谢!
回复

使用道具 举报

大师
发表于 2016-8-16 08:56:35 | 显示全部楼层
不错,谢谢分享!
回复

使用道具 举报

药徒
发表于 2016-8-16 08:57:02 | 显示全部楼层
如果再加上用户权限和审计追踪就更好了。
回复

使用道具 举报

药徒
发表于 2016-8-16 09:10:53 | 显示全部楼层
长姿势了,谢谢LZ无私分享!
回复

使用道具 举报

药生
发表于 2016-8-16 09:11:10 | 显示全部楼层
excel验证还是能有效避免差错
回复

使用道具 举报

药士
发表于 2016-8-16 09:18:02 | 显示全部楼层
zel3961 发表于 2016-8-16 08:46
就这个,适用验证的情况多吗?哪位大侠指教一下。

虚?你怎么会有这种感觉?这个很实用哦,经过验证的电子表格,使用很方便,将实验数据输入,复杂的计算秒秒出结果,并且正确无误;还可以直接打印作为检验记录一部分,减少了检验员好多工作
回复

使用道具 举报

药徒
发表于 2016-8-16 09:31:49 | 显示全部楼层
首先我们要把2003系列换了才行···
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

×发帖声明
1、本站为技术交流论坛,发帖的内容具有互动属性。您在本站发布的内容:
①在无人回复的情况下,可以通过自助删帖功能随时删除(自助删帖功能关闭期间,可以联系管理员微信:8542508 处理。)
②在有人回复和讨论的情况下,主题帖和回复内容已构成一个不可分割的整体,您将不能直接删除该帖。
2、禁止发布任何涉政、涉黄赌毒及其他违反国家相关法律、法规、及本站版规的内容,详情请参阅《蒲公英论坛总版规》。
3、您在本站发表、转载的任何作品仅代表您个人观点,不代表本站观点。不要盗用有版权要求的作品,转贴请注明来源,否则文责自负。
4、请认真阅读上述条款,您发帖即代表接受上述条款。

QQ|手机版|蒲公英|ouryao|蒲公英 ( (京)-非经营性-2014-0058 京ICP证150354号 京ICP备14042168号-1 )

GMT+8, 2024-3-29 08:02

Powered by Discuz! X3.4运维单位:苏州豚鼠科技有限公司

Copyright © 2001-2020, Tencent Cloud.

声明:蒲公英网站所涉及的原创文章、文字内容、视频图片及首发资料,版权归作者及蒲公英网站所有,转载要在显著位置标明来源“蒲公英”;禁止任何形式的商业用途。违反上述声明的,本站及作者将追究法律责任。
快速回复 返回顶部 返回列表