电子表格怎么合规(三)——符合计算机化系统验证规则的验证实践(正文部分)

By Da vid Harrison & David A Howard
Introduction

简介
This continues the series of short articles describing a generic process forvalidating Excel Spreadsheets.

本文通过一系列的短篇文章来叙述 Excel 电子表格验证的一般程序。
Although this article specifically covers Excel, the principles can be adaptedto cover a wide variety of applications. This process has been successfully used on Access Databases, and other straightforward applications such as standaloneinstrumentation and off the shelf software packages8. The focus is on minimising the documentset, and providing all relevant information in a single generic specification with allcritical items covered.
尽管本文特别涵盖了 Excel，但其中的原则可适用于更为宽泛的应用。这套程序已经成功的运用于 Access Databases，其他简单的应用比如独立仪器和现成的软件包。本文关注点在于最小化文档集并在一个单个的通用的包括了所有关键项目的规范中提供所有相关信息。

This paper outlines the approach, and then provides a set of questions andanswers on the approach. The answers attempt to pre-empt any regulatory or QA questionsthat may arise.
本文对该方法进行了概述，并随后提供了一组关于该方法的问答。回答尝试预先解决可能出现的法规或 QA 问题。
The Streamlined Specification Approach

简化的规范方法
The recommended approach is summarised below.
推荐的方法总结如下：
l One single specification is used which incorporates the UserRequirements Specification (URS), Function Specification (FS) and Design Specification (DS).
一份单个的规范中包括用户需求标准（URS）、功能标准（FS）和设计标准（DS）
l The specification identifies each requirement with uniquenumbering so that they can be later used for traceability and qualification cross referencing.
标准中用唯一的序号定义每一条需求，以便可以在稍后的追溯性和确认交叉引用中使用
l Where possible, information is collated into tables andappendices to ensure a consistent and easy to understand format.
如果有可能的话，相关信息整理成表格和附件这样的一致和易理解的格式
l The process acknowledges that most spreadsheet specificationsare generated retrospectively from an existing prototype. As such, the detailed content of the prototype is used in the specification process to allow the reader of thespecification to better understand the spreadsheet and its functionality.

这套程序承认大部分电子表格规范均由一个现存的原型回顾性的生成。正因为如此，详细内容的原型用于规范程序以使规范的读者更好的理解电子表格和它的功能
l The version of the spreadsheet is clearly defined in thedocumentation.
在文档中应清晰的定义电子表格的版本
l A generic template document is used as the starting point whichis suitable for all spreadsheets with minimal modification
一份通用的模版文件作为起始点使用，使用于所有最小限度修改的电子表格
Specification Generation Process

规范生成程序
The specification document is generated retrospectively once the finalversion of the prototyped spreadsheet is complete. This is critical, as one commonerror is to generate the content, only to see the users change their mind on functionality,resulting in changes to the prototype. This‘scope creep’ results in time consumingchanges to the documents and an increased likelihood of errors.

一旦原型化的电子表格完成，规范文件就可以回顾性的生成。这一点是关键，因为一个普遍的错误容易发生在内容生成后，用户改变了对功能的想法，导致了原型发生了变动。这种“需求渐变”不仅是文件变更费时，而且增加了错误发生的可能性。
Specifications are usually generated by the spreadsheet developer and reviewed independently by a QA or validation function.
规范一般是由电子表格开发者生成，并由 QA 或验证部门独立审核。
Once generated and approved the specification (coupled with the spreadsheetitself) is presented for qualification.
一旦生成并得到了批准（包括电子表格本身），规范就可以提交确认了。
The specification will act as a living document, and future changes to thespreadsheet (through change control) will result in version controlled updates to thespecification.
该规范将作为一份动态的文档，电子表格未来的变动（通过变更控制）将会导致版本控制的规范升级。
Specification Content

规范内容
The specification document is routinely divided into two distinct sections, aURS Section and a FS section. The approval signatures on the front page approve allcontent.
该规范通常分为两个不同的部分，一个是 URS 部分，另外一个是FS 部分。在首页的签名即批准了所有的内容。
The document is divided up into the following sections.
文件一般分为下面几个章节：
Section 1 – Introduction
章节 1-简介
Section 1 provides an introduction to the project and the goal of the project.
在章节 1 会对项目进行介绍并提出项目的目标。
Section 2 – System Overview
章节 2-系统概述
2.1 User Background
2.1 用户背景
2.2 System Overview
2.2 系统概述
2.3 Specification Methodology
2.3 规范方法学
Section 2 provides information on the spreadsheet’s use such as departmental background and how the spreadsheet fits into the data processing/approvalprocess. It will define the data used and records generated. It will provide an overview ofthe spreadsheets use and function.
章节 2 中有一些电子表格使用的信息，比如各部分的背景以及电子表格怎样符合数据处理/批准程序。在章节 2 中将会定义使用的数据和生成的记录，同样会有电子表格使用和功能的一些概述。
This section also provides a brief introduction to the format and content ofthe document with particular note made to the use of Appendices.
在这个章节同样有文件的格式和内容的简要介绍，同时也特别注明附录的使用。
Section 3 - User Requirements
章节 3-用户需求
Section 3 details the user’s needs and requirements. Although it is generated retrospectively, it is still written as a request for functionality i.e. Thesystem must do this, or the system should do that.
章节 3 详细描述了用户的要求。尽管这是由回顾性的生成，但它仍然需要作为功能性的要求编写。例如，系统必须做这些，或者系统应该做那些。
Section 4 – Functional Specification
章节 4- 功能标准
Section 4 details the response to the User Requirements. It is generatedretrospectively and provides a detailed description of the functionality defined within thespreadsheet. It is written in the present tense i.e. The system does do this.
章节 4 详细描述了对用户需求的回应。它由回顾性的生成并提供了电子表格里定义的功能的详细描述。编写是用现在时态，比如系统做这些。
In sections 3 and 4 each item is structured into numbered bullet points whichallow cross reference and traceability. The majority of bullets are requested in the URSsection, and answered in the FS section.
在章节 3 和章节 4 中，每一项都应进行编号，这可以使其交叉引用和追溯。多数的项目要求在 URS 章节，而回答在FS 章节。
e.g.
例如：
URS Section 3.1.2 The spreadsheet must be compatible with Microsoft Excel 2002 and 2003.
URS section 3.1.2 电子表格必须和 MSExcel2002 和 2003 兼容
FS Section 4.1.2 The spreadsheet/template can be used within the followingMicrosoft Excel application software versions (Excel 97, Excel 2000, Excel XP, Excel2003).
FS Section 4.1.2 该电子表格/模版可以在以下版本 MS Excel 应用软件下使用(Excel 97, Excel 2000, Excel XP, Excel 2003)。
For many of the requirements this functional response is a straightforwardconfirmation of the need; for other requirements the FS section of the document will expand thedetail to explain how the functionality works.
对于许多需求，这种功能性的回应是一种直截了当的需求确认。对于其他需求，文件中的FS 部分将会额外的详细解释功能是如何运作的。

The following descriptions detail the relevant subsections of sections 3 and 4;the descriptions are combined in this paper, but are separate in the specificationdocument.
下面将会对章节 3 和章节 4 的子章节进行详细描述。在本文中将在一起叙述，但是在规范文档中是要分开的。
URS 3.1 and FS 4.1 – Application Software
URS 3.1 和 FS 4.1-应用软件
Describes the versions of Excel (and any other relevant software).
描述 Excel 的版本（以及任何其他相关软件）
URS 3.2 and FS 4.2 – Spreadsheet Workbook Characteristics
URS 3.2 和 FS 4.2-电子表格工作簿的特征
Describes the way in which the spreadsheet is structured into workbooks andworksheets. It will define the number of each and whether they operate as XLS or XLT files.
描述设计电子表格中工作簿和工作表的设计方法。明确每一个的编号以及是作为 XLS 或者XLT 文件运行操作。
URS 3.3 and FS 4.3 – Location of Operation
URS 3.3 和 FS 4.3-运行位置
Describes the proposed location of operation such as identification of theserver and whether the spreadsheet will be operating within a 3rd party software packagefor added security2
.描述建议运行的位置，比如服务器的标识以及因为额外安全性，电子表格是否需要在第三方软件包内运行。
URS 3.4 and FS 4.4 – Electronic Record Reproduction
URS 3.4 和 FS 4.4-电子报告复制
Describes the needs and functionality over the use and availability ofelectronic data and records to meet 21 CFR Part113
.描述对使用和可得到的电子数据和记录在需求和功能上符合 21CFR Part11 的要求。
URS 3.5 and FS 4.5 – Electronic Record Backup and Restore
URS 3.5 和 FS 4.5- 电子记录备份和恢复
Describes the needs and functionality for accessibility of saved and restoredelectronic records to meet 21 CFR Part 113
.描述保存和恢复的电子记录可访问性在需求和功能上符合 21CFR Part11 的要求。
URS 3.6 and FS 4.6 – Audit Trail
URS 3.6 和 FS 4.6-审计追踪
Describes the needs and functionality for audit trails and traceability to meet21 CFR Part 11
描述审计追踪和可追溯性在需求和功能上符合 21CFR Part11 的要求。
URS 3.7 and FS 4.7 – Physical and Logical Security
URS 3.7 和 FS 4.7-物理和逻辑安全

 Describes the needs and functionality for security to meet 21 CFR Part 113.This will describe the security around both the application software (Excel and possiblya 3rd party software package2) and the individual spreadsheet. It will provide detail onworkbook, worksheet and reference to cell protection.
.描述安全措施在需求和功能上符合 21 CFR Part 11 的要求。不仅需要对电子表格个体进行安全描述，而且需要对相关的应用软件（Excel 以及可能的第三方软件包）进行描述。这里包括一些工作簿、工作表和单元格访问保护的一些细节。
URS 3.8 and FS 4.8 – Macro Functionality
URS 3.8 和 FS 4.8-宏功能
Describes the needs and functionality of any macros used in the spreadsheet. Depending upon the complexity of the macros used, this section can follow asimple or complex structure. The complex structure will break out functionality intoindividually numbered sections which details Functionality type (i.e. button, form, etc),VBA command, Use, associated error messages and cause, and audit trail entries.
在需求和功能上描述电子表格中任何使用的宏。基于使用宏的复杂性，这章节可以使用简单或复杂的结构。如果是复杂的结构，则将功能分解并独立编号若干个小部分，具体为功能类型（比如按钮或窗体）、VBA 命令、用途、相关错误消息及原因、审计追踪条目。
URS 3.9 and FS 4.9 – Spreadsheet Documentation
URS 3.9 和 FS 4.9-电子表格文档
Describes the supplied documentation such as specifications, SOPs for routineuse and the presence of developer and user training records.
描述提供的文件材料，比如标准、日常使用的 SOP 和参加的开发者和用户的培训记录。
URS 3.10 and FS 4.10 – GxP Records and Approval Signature Functionality
URS3.10 和 FS 4.10-GxP 记录和批准的签名功能
Identifies GxP critical electronic records to meet 21 CFR Part 113 anddescribes the process for handwritten or electronic approval of the data.
确定 GxP 关键性的电子记录符合 21 CFR Part 11 并描述手写或电子批准数据的程序。
URS 3.11 and FS 4.11 – Data Calculation Requirements
URS 3.11 和 FS 4.11-数据运算需求
Describes the detailed functionality of the calculations and formulas withinthe spreadsheet. This section is usually substantial and always unique to eachspreadsheet, as such the content is transferred out to suitable tables within Appendix A forthe URS and Appendix B for the FS. See examples below.
详细描述电子表格中运算和公式的功能。这部分内容最多并且每一个电子表格都不一样。这样的内容可以按附件 A（URS）和附件 B（FS）中的表格形式列出。参见下面的例子。
URS 3.12 and FS 4.12 – Input/Output Requirements and Data Flow
URS 3.12 和 FS 4.12-输入/输出需求和数据流
Describes the detailed functionality of input and output such as Excel datavalidation, conditional formatting, and the process by which any data is imported/exportedin the spreadsheet. For many of these functions cross reference can be made to Appendices A/B or to macros detailed elsewhere in the document.
描述输入和输出详细的功能。例如 Excel 数据验证、条件格式以及电子表格中输入输出的任何数据的过程。对于许多这些功能可以在附件 A/B 中或文件其他地方详细描述的宏中交叉引用。
URS 3.13 and FS 4.13 – Information Printout
URS 3.13 和 FS4.13-信息输出
Describes and displays the visual representation of the spreadsheet in use andany printed spreadsheet output. This section is usually covered by screen printsand printouts which are provided in Appendix C.
描述并展示使用的电子表格的直观形式和任何打印的电子表格输出。这部分通常包含了一些屏幕截图和附件 C 提供的一些打印输出。
URS 3.14 and FS 4.14 – Miscellaneous Requirements
URS 3.14 和 FS 4.14-其他需求
Describes the needs and functionality for any items not covered in previoussections. Example may include information on any manual processes that link thisspreadsheets output to another system.
在需求和功能上描述前面章节没有涉及到的部分。比如链接此电子表格输出到其他系统的任何手动处理信息。
Section 5 – Glossary and Section 6 – References
章节 5-术语和章节6-参考文件
Describes the acronyms and references used. Often the references will referback to the
testing of other applications such as any testing of the Excel environment or 3rdparty software packages2

.描述使用的缩写和参考文件。经常地，参考文件将回溯到其他应用软件的测试。比如 Excel 或第三方软件包的测试

‍

如果您觉得不错，请长按下面二维码关注，GMP办公室将为您推送更多精彩的内容！