Excel表格电子化应用与验证

电子表格的规范化使用e-record/e-signature杨博数据库系统工程师[e-mail]表格在GMP中使用存在的问题不进行验证，验证文档缺失错误率高数据更新不及时、不一致电子表格的制作，使用不可控修约的问题换算因子未体现预定义的标准或限度不在Excel表格中未采取保护措施，以防止数据和公式受到修改Excel不具备确保数据安全和完整性的功能FDA关于Excel的警告信2012年6月13日具体而言，本次检查发现你公司并没有就分析人员用来记录分析测试结果的电子表格建立书面的程序进行控制和说明。QC实验室的分析人员将QC实验室计算机中的Excel工作簿以不受控的数量打印出来，而没有监管。例如，你公司并没有验证用于产品分析结果计算的Excel电子表格的准确性，所有的产品生产都用来供给美国市场，因此需要验证所得结果的准确性。2011年4月10日你公司的实验室分析人员都能访问和修改用于愈创甘油醚及药物成品的分析计算的Excel电子表格中的公式。由于不受限制的访问，无法保证Excel电子表格中的公式是准确和有效的。•版本之间难以区分•Excel的打印版本与电子版本之间对应不上•公式容易受到更改而没有进行保护•Excel的发放和使用不受控•没有审计跟踪功能（影响数据完整性的最重要方面）Excel电子表格在GxP领域应用存在的问题无充分的安全访问控制措施:标准的Excel电子表格无法完全满足Part11所要求的安全与访问控制措施无审计跟踪:标准的电子表格无审计跟踪功能无电子签名功能:对于数据变更和数据审核无电子签名的功能电子表格在实验室应用的方式仅仅用来制表，格式化和美观导入原始数据并进行计算处理（二次数据）-数据由计算机化系统产生（设备，软件），数据手动或自动，复制粘贴的方式拷贝到电子表格中，需要确认拷贝过程中数据没有变化-利用原始记录中的数据，如标准品和样品的称重，稀释倍数等直接采集原始数据并对数据进行计算处理（原始数据）-原始数据就在Excel中-例如电子天平的称量数据直接传输到电子表格中，动物称量的数据手动输入到Excel中一次性使用反复使用的分类复杂性对电子表格使用的限制3简单用于临时的表格设计、报告一次性的数据图表展示结果的手动复核对产品质量没有直接影响4简单仅仅使用标准功能公式不过于复杂：不要有超过2层的嵌套公式无VBA应用5复杂无限制Measure345使用说明xxx用户培训xxx开启审计跟踪xxx说明OS版本和电子表格版本xxx每页显示页眉xxx描述用户的总体需求，输入、计算、输出xx验证计划xx系统分类与编目xx电子表格保护xx测试xx对照URS打印公式并复核xx对照预期结果进行测试数据录入和复核xx检查区域保护、条件格式、非法输入检查xx将表格转移至受保护区域，并启用变更控制xx开发人员培训、供应商审计x公式说明、算法、界面、系统边界定义x根据开发指导和编程标准进行开发、源代码审核x对照功能说明拟定测试计划并对计划进行复核及QA批准x制定验证报告并有QA批准xExcel应用于合规环境的基本策略充分利用Excel内置的安全机制和相关功能，并采用Excel功能扩展插件采用一些最佳实践开发电子表格应用模板，并辅以充分的文档说明实验室中要建立公用的，通用性强的计算处理模板采用系统化的方法验证电子表格的计算公式和宏。提供充足的培训，提升Excel的使用水平服务器用户用户以太网用户Template文件夹用户文件夹1用户文件夹2用户文件夹3合规环境下电子表格设计的最佳实践采用垂直的，模块化的设计方法。-将Excel的划分为不同的功能区，如数据的输入区，计算处理区，结果区，标准或规范区，配置参数区等。-在单个工作簿模板中使用多个工作表用户使用的电子表格界面风格应该有一致性-如果两个区域具有相同的含义，那么在界面中应该表现出同样的属性和行为-数据输入单元格采用统一颜色突出显示，并明确告知用户哪种颜色标记的单元格是可以自行更改数据的，那种颜色标记的是不能修改的。尽可能从其他位置获取关联数据，避免重复的数据输入。整合电子签名和审计跟踪模块，作为通用模块进行验证，而不再需要对每个电子表格进行重复的验证。封面页数据页计算处理页结果页标准与常量页说明页验证记录垂直分割模块化设计举例移液枪移液准确性的重量法控制数值输入区计算处理区常量和标准区公式的使用设计时考虑将来的扩展性清晰的计算流程-复杂的公式尽可能将其计算过程分散到多个单元格，而不是都挤在一个单元格内。采用条件函数，设定错误处理和报告方法-例如采用ISERROR()函数避免由于某个输入数据为空，导致结果计算出现错误标识。尽量少采用宏，能够通过公式和配置实现的尽量避免宏，以免增加验证的工作量=Round(E31/D4)*100,1)#DIV/0，#Value,#REF!,#NUM,#NAME,NULL=IF(ISERROR(Round(E31/D4)*100,1)),””,Round(E31/D4)*100,1))数据格式与命名对单元格和单元格区域以具有明显意义的名字命名-例如使用“Std1替代“Sheet2!E$12定义数值的精确度和修约规则负数的显示可以调整为以括号显示，例如-5显示为(5)单位的数值引入单位，通过Excel的自定义格式为具备单位的数据引入单位-直接在“G/通用格式”后添加单位“mg”.对输入参数进行控制和检查检查输入数据的合理性-尽量通过Excel的“数据验证”功能设置下拉菜单供用户选择可选项对计算结果设置限制范围-如果计算结果不在预定范围内以警告色显示或标识=IF(K3580,”Fail”,”Pass”)电子表格的保护不允许用户随意改变电子表格的一些关键属性保护带公式的单元格，如果你采用将数据输入和数据处理分成不同的工作表的方式，那就将数据处理的工作表保护起来，从某种程度来说，数据处理工作表等同于软件开发上的源代码。配套完整的验证文档电子表格的基本描述，作者，目的与用途，相关联的其他电子表格以及所使用到的宏对关键单元格添加解释性注释说明使用意义明显的列表头如果电子表格需要打印出来，注意在页眉或页脚显示打印文件名和文件路径对电子表格中的宏命令和公式归档保存为复杂的公式设计添加说明，可以放到单元格的注释中GxP下的Excel电子表格验证策略优先处理合规风险和业务风险最高的电子表格优先处理简单的电子表格尽量采用通用化的模板文件把URS和FS，DS组合成单一的文件把IQ、OQ、PQ组合成单一文件通过电子表格原型回顾性地制作规范文档Excel验证文件的单一文档结构章节章节主要内容章节1-简介对项目进行介绍并提出项目的目标。章节2-系统概述介绍电子表格使用的相关信息，比如使用部门背景、概述电子表格使用和功能、定义使用的数据和生成的记录、以及介绍电子表格是如何工作以符合数据处理/批准程序的。在这个章节还需要简要介绍规范文档的格式和内容，以及对附录进行说明。章节3-用户需求（URS）章节3详细描述用户的需求，如系统必需做到哪些，或者应该做到哪些。用户需求是必不可少的，尽管一般是在电子表格原型版确认后回顾性地生成。注意这里的用户需求包含了法规的需求章节4-功能规范（FS）章节4详细描述对用户需求的回应。它由原型回顾性地生成，对电子表格功能进行详细描述。事实上可以回顾性地编写。章节5-术语描述使用到的缩写或术语。章节6-参考文件描述使用到的参考文件，比如相关软件（如Excel或第三方软件包）的测试文件。主体附录A–电子表格功能测试附录B–电子表格安装确认附录C–性能确认附录D和E–附加数据表附录F–可追溯性索引附录G–确认报告如何编写对电子表格的需求输入数据的类型和范围（对应Part11中的Sequenceverification和DeviceCheck）计算过程与公式-公式在需求描述时采用数学表示法图表安全：包括电子表格本身，用户，数据系统性能，质量，错误处理，启动，关闭等等编号需求描述URS-1该电子表格可以适合于0.01ml到10ml体积URS-2只能接受10个样品URS-3能计算基于10个样品的平均值，标准偏差(SD)，相对标准偏差（RSD＝(SD/average)*100%)URS-4该电子表格只能使用20℃-25℃的水密度，并以1℃为步进值URS-5移液枪的编码必须在在屏幕上可见，并可打印URS-6能显示准确度和RSD是否在规定范围内URS-7电子表格的输入区域只能输入数值型的数据URS-8用于计算的区域应该受到保护以防止未授权的更改URS-9完成测试后，电子表格应该有测试人员签字并写明日期URS-10签字后该电子表格不再允许更改URS-11该电子表格模板和已经完成的电子表格应存放于安全的位置，并进行必要的访问控制电子表格的DQ•启用公式显示视图•启用链接追踪视图•工作簿结构描述：工作表构成•工作表保护说明•链接文件Excel的IQ检查语言，区域设置是否为中国检查运行Excel的客户端的操作系统版本是否符合当前Excel版本的要求确认客户端电脑的Excel程序版本检查当前计算机硬件配置是否符合Excel版本的要求补丁Excel的安装路径，文件名，文件类型电子表格的OQ测试的内容-结构测试：数据格式，布局、引用、按钮、下拉框、文本框-计算测试：公式是否正确，与手工计算是否一致、分支函数需要测试所有可能性的判断，公式的位置-宏和VBA：代码的白盒和黑盒测试-计算公式有效范围内的值，有效范围限值（上限或下限）和微超限值（向上或向下）输入变量导致不可接受的或不可能的结果-在单元格中尝试输入不正确的格式或者值（text代替数字，超出范围，浮点数代替整数）-写明系统应该做出何种反馈。每一个功能必须有最少一个测试案例对应电子表格的功能测试举例验证记录A=AVERAGE（X：Y：Z）测试组20，20，20A=AVERAGE（X：Y）或者A=AVERAGE（Y：Z）OQ（运行验证）对应URS测试测试结果/接受标准…URS-7在样品1的重量单元格中输入字母”q”应出现警示信息“请输入以g为单位的重量URS-7在温度输入单元格中输入字母”q”应出现警示信息”请输入20到25度之间的温度值…URS-21将鼠标定位在单元格E4中，按住”Delete”以删除其中内容出现警示信息”您当前要变更的单元格已被保护锁定“…URS-22进入A1单元格输入移液枪的编号,并按下回车键出现警示信息”你必须在绿色区域内输入数据“包含所有的基本功能测试输入值导致不可接受或不可能结果的测试输入错误格式的数据或者浮点型，整数型数据的替换输入包含安全性测试PQ（性能验证）对应URS测试测试结果/标准…URS-3URS-6在移液枪编号中输入KS099，在NominalValue中输入10，温度输入22，输入下列样品值：9.98，9.98，9.99，10，10，10.01，10.01，10.02，10.02结果：平均值10g,SD0.015RSD%0.15准确度OK,RSDOKURS-3URS-6输入下列样品值：9.95，9.95，9.99，9.99，10，10，10.05，10.05，10.04，10.4结果：平均值10.078g,SD0.173,RSD%1.717,准确度ok,RSD超出范围URS-3URS-6输入下列样品值：10.3，10.3，10.3，10.3，10.3，10.4，10.4,10.4,10.4结果：平均值10.35g,SD0.053,RSD%0.512，准确度超出范围，RSDOKURS-3URS-6输入下列样品值：10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,结果：平均值10.00g,SD0.000，RSD%0.000，准确度ok,RSDokExcel的安全管理建立所用Excel表格的详细信息列表，文件名，版本号，或制定统一的命名规