QC实验室Excel-电子表格验证-讲义

[e-mail]2017.12目录Excel电子表格特点电子表格验证模型电子表格的设计与开发用户需求标准URS确认Excel电子表格（spreadsheet）特点优点：简单易学、方便使用缺点：容易出错、难以核实工作表及其产生的数据；固有的安全与审计追踪缺陷。电子表格验证模型电子表格属于GAMP5分类中的第4类，确认比较简单，可以进行3Q确认（IQ、OQ和PQ）。对于使用了VBA和宏的电子表格，属于GAMP5第5类，验证时需要增加设计确认（DQ）和源代码审核。（宏：是可以执行的一系列代码的封装，可以认为是一个小程序）电子表格验证模型用户接受（useracceptance）：电子表格开发人员与用户充分交流，编写出电子表格，经用户认可后，形成最终设计（finaldesign）。最终设计形成后，必须锁定电子表格，停止继续开发。工作表标准—URS与FS：工作表设计（spreadsheetdesign）锁定后，即可制作用户需求标准（URS）与功能标准（FS）.URS与FS文件可以合在一起——工作表标准（spreadsheetspecification）。工作表确认（spreadsheetqualification）：包括功能测试（functionaltesting）、安装确认IQ、运行确认OQ、性能确认PQ。功能测试：是在非最终运行环境下，证明电子表格中使用的数据、计算公式、宏是正确的。内容与同OQ/PQ相同，主要是计算与功能的手工测试。实际运行(liveuse)：在实际运行工作表之前，应有一些程序性文件:备份/保存程序；系统管理（包括系统进入/授权）；日常测试/周期审核/再确认；变更控制/配置管理；灾难恢复；记录保留；退役与数据迁移等。电子表格的设计与开发电子表格设计时，应注意以下要求：1工作簿（workbook）应有直观的布局，比如数据输入区、计算处理区、结果区、常量及标准区、配置和参数区等。2计算处理区和结果区涉及的公式需要保护起来，常量及标准区也应当保护。3所有的工作表应有同样的感观，比如数据输入区域的颜色、一致的使用建议。这将有利于使用人员培训、接受与执行。4尽可能引用其它位置或工作表（worksheet）中的数据，减少重复的数据输入。5尽可能采用Excel已有的函数，少用VBA和宏（macro），尤其是当它们对工作表的准确性或效率没有关键影响的时候。使用宏可以节省时间，但是将会给验证带来额外的负担。电子表格设计与开发电子表格设计时应满足数据完整性的法规要求：1电子表格安装的计算机应当对操作系统进行权限管理，并设置屏幕保护程序；2电子表格应当存在指定的文件目录中，文件夹和文件需要设置为不得删除和修改。3电子表格应采用模板管理的方式，每次使用时，使用人员不得在模板中直接使用表格，应当采用另存的方式，重新命名一个Excel电子表格进行使用。我们通常验证一个电子表格模板文件（.XLT），而不是一个工作簿文件（.XLS），模板文件可以被验证、保护、用作创建多个验证的工作表(mulitiple'valdated'spreadsheets)。4应当为电子表格建立命名规则，保证每一个电子表格都有唯一的标识。5如果使用纸质文件作为主文件，Excel电子文件应打印出来签字并由第二人复核后归档。6应当定期备份储存在制定文件夹下的电子表格，一般采用移动硬盘进行双备份。电子表格设计与开发技术控制措施：从法规对数据完整性要求的角度考虑，标准的Excel软件存在一定缺陷，比如缺少完善的安全与访问权限管理、审计追踪（audittrail），因此对工作表增加额外的技术控制措施是必要的，有两种途径：1定制开发宏：供一个或多个工作表使用。这种定制化开发，属于GAMP5分类中的第5类软件，需要严格的验证，包括设计确认和源代码的审核。2使用第三方软件包：这种买来即用的软件包增加了Excel环境下的技术控制措施，属于GAMP5分类中的第3或第4类，需要特定的验证。可供选用的软件包，例如CIMCONSoftwareInc.的eInfortree、OfniSystems公司的ExcelSafe以及CSVComplianceLtd的DaCS（应用广泛）。用户需求标准URS通常情况下，对于简单的电子表格而言，用户需求说明和功能标准可以合二为一。1起草URS文件的前提是，最终版本的电子表格已完成。电子表格的版本号应会在文件中明确描述。2电子表格URS文件的主要内容，来源于对已存在电子表格详细内容的追溯。URS文件的阅读人员将会更好的理解该电子表格及其功能。3URS是一个动态文件，电子表格的变更（通过变更控制程序）将导致URS文件版本升级。用户需求标准URSURS文件内容：1介绍introduction包括项目及项目目标的介绍。2系统简介systemoverview2.1用户背景userbackground谁将使用本电子表格2.2系统概述systemoverview电子表格的使用与功能说明，比如数据使用、处理、记录产生、批准程序等2.3标准说明specificationmethodology对文件格式与内容的简短说明，以及附件使用的注意事项等。2.4术语与参考资料3用户需求userrequirements3.1电子表格必须兼容MicrosoftExcel2010、2013;用户需求标准URS3.2电子表格工作簿（workbook）特性：描述哪个电子表格（spreadsheet）被构建于工作簿(workbooks)和工作表(worksheets)内。定义哪些作为XLS或XLT文件。3.3操作位置：描述建议的操作位置，比如服务器识别、是否有为额外安全而使用第三方软件包。3.4电子记录的产生：应满足数据完整性要求，描述需求与功能。3.5电子记录备份与储存：应满足数据完整性要求，描述需求与功能。3.6审计追踪：应满足数据完整性要求，描述需求与功能。3.7物理与逻辑安全：应满足数据完整性要求，描述需求与功能。包括对Excel、可能的第三方软件包、独立电子表格的安全性要求。应提供对工作簿、工作表、单元格保护的详细要求。3.8宏（Macro）功能：描述电子表格内所有宏的要求与功能。取决于宏的复杂性，本条款可增加简单或复杂的结构（structure），复杂的结构可拆分为功能独立的数个部分，包括详细的功能类型、VBA命令、使用、相关错误信息、审计追踪接口等。用户需求标准URS3.9电子表格文档：描述提供的文档，例如：标准、日常使用SOP、培训记录表格等。3.10GXP记录与批准签名功能：描述数据的电子签名批准或手写批准要求。3.11数据计算要求：描述电子表格内详细的计算功能与公式。3.12输入输出要求与数据流：描述输入输出的详细功能比如数据验证、条件格式、任何数据的导入导出。3.13信息打印：描述打印输出要求。3.14其他要求：比如将本电子表格输出链接到其他系统的手动过程。确认可以将IQOQPQ合并为一个文件，：确认文档（qualificationdocument）。确认的目的：证明使用电子表格可以得到准确的计算结果，其安装、运行符合标准文件（URS）要求。确认安装确认：记录并证实电子表格模板（.XLT）安装在了最终的操作环境中。主要包括以下内容：区域及语言的信息；操作系统的名称和版本号；Excel软件的版本号及安装位置；计算机的硬件配置；电子表格的名称及安装位置。确认OQ/PQ1安全性测试主要内容：操作系统的授权登录与密码保护；电子表格、工作簿、工作表的密码保护；电子表格文件的更改、删除、重命名保护；固定单元格为锁定模式不可以编辑，活动单元格可以录入或删除文本及数据；时间戳功能测试；审计追踪功能测试。确认2数据有效性验证（datavalidation）为减少数据输入时尽量少出错，电子表格模板中通常会设置单元格中允许输入的数据类型或有效数据的取值范围。测试方法：在单元格中输入不符合要求的数据，电子表格应提示输入错误。确认3数据准确性测试方法：通过手工计算，证明电子表格给出的结果与手工计算的结果是相同的。手工计算包括使用笔在纸上计算、使用计算器计算。步骤：（1）在电子表格上输入数据，得到计算结果；并电脑截屏作为记录。（2）在纸上计算或使用计算器手工计算，将得到的数据填写在纸质空白记录上（来源于方案）；（3）将两个结果进行比较，得出结论。注意事项：（1）测试者必须经过培训，熟悉Excel、验证要求、手工计算方法；（2）测试者应完全理解电子表格；（3）建议测试方案中提供有代表性的数据，供测试者选择使用。确认4宏测试：GAMP5第5类软件。按“黑盒”程序测试，这听起来让人怯步，但大多数的电子表格宏非常小、而且容易测试。