电子表格怎么合规（四）——来自欧洲EDQM的验证举例

恨我不成钢

INTRODUCTION 介绍

The present document is the 1st Annex ofthe core document “Validation of Computerised Systems”, and it should be usedin combination with it when planning, performing and documenting the validationprocess of computerised systems.

本文件是核心文件 “计算机化系统的验证”的第一个附件（关于EDQM“计算机化系统的验证”指南的正文和其他内容将后续的日志中）。在计划、实施和记录计算机化系统验证过程时，本文件应与核心文件结合使用。

The core document contains theIntroduction, Scope and general requirements for the validation of differenttypes of computerised systems.

核心文件包括了不同计算机化系统验证的介绍、范围和一般要求。

This Annex presents an example of Excel?spreadsheet validation, which is to be used in combination with the generalrecommendations given in the core document.

本附件给出了一个EXCEL表格验证的例子，它可以与核心文件中给出的通用性推荐结合使用。

This document should be considered as aguide to OMCLs for planning, performing and documenting the validation of theircomputerised systems. It should not be taken as a list of compulsoryrequirements. It is left to the professional judgement and backgroundexperience of each OMCL to decide on the most relevant procedures to beundertaken in order to give evidence that their computerised systems areworking properly and are appropriate for their intended use.

本文件应作为OMCL在计划、实施和记录其计算机化系统时的指南。它不应被当成一个强制的要求。各OMCL应根据其专家的判断和背景经验来决定其需要执行的最相关的程序，以提供证据来证明来计算机化系统工作正常，适用于其既定的用途。

1. SOFTWAREPRESENTATION AND GENERAL INFORMATION 软件说明和一般信息

This software’s aim is to calculate avaccine titration (Figure 1). From results obtained for a reference product(height measured at 4 concentrations), a calibration curve and its formula areprovided. Both of them are needed to calculate the concentrations correspondingto the height measured for the tested vaccine.

这个软件的目的是计算一个疫苗的滴定（图1）。从一个对照产品（在4个浓度测得的高度）得到的结果，绘制一个校正曲线并计算出一个公式。这两者均需要计算浓度和对应被检测的疫苗所得的高度。

In Figure 1, grey cells are filled withnumerical data from experimentation and are the only ones that can be changedby the operator. All cells including formula are locked. No more than one cellfrom the calibration range can be empty; all cells for vaccines must be filledto guarantee proper use.

在图一中，灰色单元格填充了试验用的数字格式的数据，仅这些单元格可以由操作者更改。所有单元格，包括公式被锁定。校正范围内不超过一个单元格可以为空，所有单疫苗用的单元格应进行填充以保证适当使用。

To access the software, a password isneeded to log on the computer.

应设置登录计算机的密码，以限制进入软件。

Back ups are regularly performed to ensureoriginal files preserving.

应定期进行备份以保证原始文件的保存。

2. VALIDATIONSTAGES 验证阶段

The different stages of the validationare: 验证的不同阶段为

2.1 Printing of formulas 打印公式

2.2 Validation of the calculations 计算验证

2.3 Validation certificate 验证证书

2.4 Software installation anddocumentation 安装软件和记录

2.1 Printing of formulas 公式打印

In order to validate the Excel?spreadsheet, formulas are printed, and the print is kept in the softwarevalidation file (Figures 2.1 and 2.2).

为了验证EXCEL表格，要把公式打印出来，打印的文件要保存在验证文件里（图2.1和2.2）

2.2 Validation of the calculations 计算的验证

All calculations are verified with asystem completely independent from the self developed software. One part of therecalculation is performed by validation versus a commercial software, theother part with a pocket calculator.

所有的计算应采用一个独立于自主开发软件的完整的系统进行确认。计算的一部分与商业软件进行对比，另一部分与计算器进行对比。

2.2.1 Validation of the calculationsversus commercial software 与商业软件对比的计算验证

Then, a dataset as close to real values aspossible is chosen (Figure 1). Excel? calculations are compared to the resultsgiven by commercial software, since it is considered as validated (Figure 3).The commercial software provides the coefficient of correlation, R2 and thecoefficients of the calibration curve. As no discrepancy occurs, the validationof this part of calculation is considered as fulfilled.

然后，可以选择一套尽可能接近真实值的数据（图1），用EXCEL进行计算，将其结果与商业软件给出的结果进行比较，因为该商业软件被认为是已经过验证的（图3）。商业软件给出线性系数，R2和校正曲线。由于没有出现不同结果，计算的该部分验证被认为是满足要求的。

2.2.2 Validation of the calculations witha pocket calculator (Manual calculations) 采用计算器进行计算的验证（手动计算）

Concerning the other calculations, fromprinted formula from the spreadsheet, concentrations are calculated using apocket calculator (Figure 4) and then compared to the results of Figure 1. Asno discrepancy occurs, the validation of this part of calculation is consideredas fulfilled.

关于其它计算，从表格中打印出公式，用计算器计算浓度，然后与图1中的结果进行比较。由于产生不同结果，计算的这部分验证可以认为是满足要求。

Moreover, calculations in paragraph 2.2.1and 2.2.2 are re-performed with other datasets including exceptionalsituations, as for example: OOS-results, missing data, or nonsense-data.Calculations are also validated under these conditions (data not shown).

另外，在第2.2.1和2.2.2段中的计算应采用其它数据包，包括一些例外情况进行重新计算，例如：OOS结果、数据缺少、或无意义数据。这些情况下的计算也应进行验证（数据不显示）。

At this stage, the software is consideredas validated.

在本阶段，软件可以认为已经过了验证。

To ensure traceability of validation acertificate is emitted.

为保证验证的可追溯性，应发放一份验证证书。

2.3 Validation certificate 验证证书

As the software is validated, acertificate of validation is provided. It includes the name and the version ofthe software, the date of validation, the person responsible for thevalidation, the person responsible for the release for use of the software andtheir signatures. This document (Figure 5) is kept in the softwaredocumentation.

在软件经过验证后，应提供一个验证证书。它包括软件名称和版本号，验证日期，验证负责人，软件使用放行负责人及其签名。本文件（图5）应保存在软件文档中。

Name of the control laboratory 控制实验室名称                          Certificate of software validation 软件验证证书 Name of the software 软件名称 Validated version 软件版本号 Date of validation 验证日期 Person responsible for the validation 验证负责人 Person responsible for the release of  the use of the software 软件使用放行负责人 Conclusion of the validation 验证结论 Date and signature of the person  responsible for the validation 验证负责人签名和日期 Date and signature of the person  responsible for the release of the use of the software 软件使用放行负责人签名和日期

Figure 5:Certificate of software validation

Comment: This certificate is an example ofthe formalisation of the validation. Alternatively, a Quality Assurance formcould be used.

备注：本证书是一个正式验证的举例，也可以采用一个质量保证格式来代替。

Moreover, the person responsible for thevalidation can be the same as the one responsible for the release for use ofthe software.

另外，验证负责人可以与软件使用放行负责人是同一个人。

2.4 Software installation anddocumentation 软件安装和记录

The concrete installation is completedafter validation of the software. The operator signs the life form to attestits proper installation. This form, which actually is a QA document, includesthe name of the software, unique identification, localisation, and personresponsible for the software. It includes also verification and otherspecification as updates or any problem encountered (Figure 6). Verification iscompleted after installation and reported in the life form.

在验证之后，软件安装全部完成。操作人员在历史记录上签字证明其已被正确安装。该表格实际上是一个质量保证文件，包括软件名称、唯一识别号、位置和软件负责人。它还包括确认和其它更新标准，或遇到的问题（图6）。在安装后确认即告完成，在历史记录上应进行报告。

Life form 历史记录表 Name of the software 软件名称 Form number 表格编号 Unique identification 唯一识别号 Manufacturer 生产商 Location 位置 Person responsible for the software 软件负责人 Date 日期 Encountered problem 遇到的问题 Intervention 干预类型 Comment next intervention 建议下次干预 Operator 操作人员 Signature of the person responsible    for the software 软件负责人签字 30/04/2009 30/04/2009 Installation Verification 安装确认 Next verification 下次确认 30/10/2009 X C XC AQ number: ELYO/XXX Version Y 30/01/2007 Validated by ZZZ

Figure 6. LifeForm 历史记录表

Comment: 备注

Another way to secure this Excelspreadsheet would have been to install the software on a network-drive withrestricted access. Only authorised staff would be able to write on this drive.The user would have no right to save data or spreadsheets and would have onlythe right to fill the (permitted) cells and to print the data.

保护EXCEL表格的另一种方式是在网卡驱动上安装软件限制进入。仅被授权的员工可以写入这个驱动，用户无权存贮数据或表格，只有权限填写（允许的）单元格以及打印这些数据。

3. REGULARVERIFICATION OF THE SOFTWARE 软件的周期确认

Regularly (for example, every 6 months) orafter every change performed in the soft- or hardware configuration, thesoftware is checked to be sure that results have not changed. A known datasetis used and the results are compared to the standard one (Figure 1).

对软件进行周期（例如每6个月）确认，或在每次软件或硬件参数变更后进行确认，以检查保证结果未被更改。使用一个已知的数据包并将结果与标准表格进行比较。

In order to help the operator,verification instructions with required information have been written (Figure7).

为了帮助操作者，确认指示和需要的信息已写入（图7）。

Results from the verification are printed,dated, signed and kept with the life form.

将确认的结果打印出来，注明日期，签字，与使用的表格一起保存。

Each verification is registered in thelife form (Figure 6) with the following information: Date of operation,intervention (i.e. verification), comments, and operator’s signature.

每次确认应登记在使用表格中（图6），包括以下信息：操作日期、类型（即确认）、评价和操作人员签字。

Comments: 备注

In case of an installation on a networkdrive with restricted access (cf. comment in chapter 2.5), the regularverification would be optional.

如果安装在一个网络硬盘上，并设有登录限制（参见第2.5章备注），则周期性的确认是可选的。

The check could have included theverification if the save date of the software is still the same as afteroriginal installation.

检查可以包括确认软件保存日期还与原始安装日期相同。

Verification instructions 确认指令 This document provides instructions for  the periodic review of the software described below. 本文件提供周期性软件确认所需的指令如下： Location of the original file: C:\Name  of the software\name.xls 原始文件位置：C:\Name of the  software\name.xls Verification: All grey cells must be  completed and compared to the template below: 确认：所有灰色单元格必须填写完整，并与下面模板进行比较。 l          Information: Name of the operator, form number and date of verification. l          信息：操作者姓名，表格编号和确认日期 l          Reference product data: Heights. l          对照产品数据：高度 l          Vaccine data: Batch number and heights for the two batches. l          疫苗数据：批号和两批的高度 Results: Print, date, sign and keep the  results in the software file. 结果：将结果打印、签名及填写日期并保存在软件文件中。 Life form: Register your verification in  the software life form. 历史记录表：将软件的确认情况登记在软件历史记录表中。

Figure 7. Verificationinstructions

4. SPECIFICDOCUMENTATION 指定文件

According to the guideline, in-housesoftware should be completed with the following specific documentation. Thecorrespondence between the guideline table and each information/documentationof the validation of this Excel? software (Specific documents and Figures) isindicated below:

根据指南，自主开发软件应包括下列指定文件，以保证其完整。指南表格和被验证的EXCEL软件（特定的文件和图）每个信息/文件对应见下表。

Information/documentation that should be  available 应有信息/文件	In- House 自主开发	Specific documents 指定文件	Figures 图号
Name, version and unique identification  of the software 软件的名称、版本和唯一识别号	X	Life form and printings 历史记录表格和打印文件	Figure 6 图6
Original files (CD-ROM…) or storage  location to install the software and software to manage the computer  environment 原始文件（CD-ROM。。。）或软件安装的贮存位置，以及管理计算机环境的软件	X	Backup of the software on the network 在网络上对软件进行备份
Date at which the software was put into  operation 软件开始使用的日期	X	Life form 历史记录表	Figure 6
Current physical location, where  appropriate 现在的物理位置，如适用	X	Life form历史记录表	Figure 6
Responsible person in charge of the  software 软件管理责任人	X	Life form历史记录表	Figure 6
Conditions under which the software  runs, where applicable (hardware, operating system……)	X	Not applicable 不适用
Name of the person who developed and  validated the software, and the date of validation 开发和验证软件的人的姓名，验证的日期	X	Validation certificate 验证证书	Figure 5
Source code (if available) 源代码（如可获得）	X	Printing of formulas 公式打印	Figure 2.1 and 2.2
Operating rules, if appropriate 操作规则，适用时	X	Not appropriate 不适用
Documentation on software regular  verification 软件定期确认的文件	X	Life form verification instructions 确认指令的历史记录表	Figure 6 and 7
Documentation on software validation 软件验证的文件	X	Software validation file 软件验证文件	Figure 1, 2.2, 2.2, 3, 4, 5
Follow-up encountered failures,  maintenance of the process, updated versions and where appropriate,  configuration management 跟踪遇到的失败、过程维护、版本升级以及适用时，参数设置管理	X	Life form 历史记录表	Figure 6

wucz_123

谢谢楼主的分享

syhorchid

学习一下