EDQM更新---附录1-Excel电子表格的验证

General European OMCL Network (GEON)QUALITY MANAGEMENT DOCUMENT欧洲OMCL网络（GEON）质量管理文件

PA/PH/OMCL (08) 87 R6

VALIDATION OF COMPUTERISED SYSTEMS 计算机化系统验证

ANNEX 1 – VALIDATION OF EXCEL SPREADSHEETS Excel

附录1-Excel电子表格的验证

ANNEX 1 OF THE OMCL NETWORK GUIDELINE

“VALIDATION OF COMPUTERISED SYSTEMS”

VALIDATION OF EXCEL SPREADSHEETS

计算机化系统验证 附录1—Excel 电子表格的验证

Note: Mandatory requirements in this guideline and its annexes are defined using the terms «shall» or «must». The use of «should» indicates a recommendation. For these parts of the text other appropriately justified approaches are acceptable. The term «can» indicates a possibility or an example with non-binding character.

备注：本指南及其附录中的强制性要求是使用应（shall）或必须（must）来规定的。应该（should）代表建议。正文的这些内容，其他合理的方法也是可接受的。术语can表明具有非约束性质的一个可能或举例。

1. INTRODUCTION 概述

This is the 1st Annex of the core document “Validation of Computerised Systems”, and it should be used in combination with the latter when planning, performing and documenting the validation process of Excel® spreadsheets used for the processing of laboratory data.

这是核心文件“计算机化系统验证”的第一个附录，当计划、进行并记录用于处理实验室数据的Excel 电子表格验证时，这份文件应与之后的附录联合使用。

This Annex presents an example of Excel spreadsheet validation, which should be used in combination with the general requirements and recommendations given in the core document.

本附录列举了Excel 电子表格的验证的例子，这应与核心文件中给出的要求和建议联合运用。

2. INSTALLATION AND SECURITY  安装和安全

To guarantee that only the latest validated version of the spreadsheet is being used and to maintain the validated state of the spreadsheet, all validated Excel spreadsheets should be stored with read-only access rights for the end users (e.g., on a protected network share). Only responsible persons should have write access to the network share.

为保证仅使用电子表格经过验证的版本，维持电子表格的验证状态，所有经验证的excel电子表格都应保存在终端用户具有只读权限之下（如，一个受保护的网络共享）。只有负责人才有共享网络的写入权限。

End users should have no right to modify a validated spreadsheet, add a non-validated spreadsheet to the share, or save data on the share. End users should only have the right to fill in the (permitted) cells and to print the data or save a copy to a data repository if needed.

终端用户没有权限修改经验证的电子表格，在共享中新增一个非验证的电子表格或在共享中保存数据。终端用户应只拥有在单元格填写（经允许）并打印或需要时在数据储存库保存一份副本。

Installation shall be documented, e.g. in the validation file, in a system log book or on a QA form. The name of the spreadsheet, unique identification, localisation, and the person responsible for the spreadsheet shall be documented. The records shall also include verification, regular verification and other issues such as updates or any problem encountered. Verification is completed after installation and recorded.

安装应经记录，比如，记录在验证文件中、系统日志或一种QA表格。电子表格的名称、唯一的识别号、未知及表格的负责人都应记录下来。记录也应包括确认、定期确认及其他问题，比如更新或遇到的问题。确认是在安装之后完成并记录。

3. GOOD PRACTICES 良好规范

When setting up a new spreadsheet, following the good practices below will reduce the risk of accidental modifications of the template and erroneous data input:

新建一个电子表格时，遵循以下良好规范将减少模板意外改动及错误数据输入的风险：

  - All calculating cells shall be locked (Format Cells > Protection > Locked) in order to protect cells containing calculations against unintended modification, except those used for data input.

-所有计算单元格均应锁定（设置单元格格式＞保护＞锁定），以保护除了用于数据输入之外包含计算的单元格出现意外的修改。

    - Cells used for data input can be identified by a specific colour

      用于数据输入的单元格可用特别颜色标识

     - Data validation rules (Data tab > Data Validation) can be applied to data input cells to prevent the introduction of aberrant values. Input messages and Error alert messages can be used to inform the end user of the expected data type and acceptable range.

  可在数据输入单元格中运用数据验证规则（数据选项卡＞数据有效性），以防止异常值的引入。输入信息和错误警告信息可用来告知终端用户期望数据类型和可接受范围。

   - Cells used for presenting the results of the calculations (output) can be identified by a specific colour. When the results are tested against acceptance criteria it is recommended using conditional formatting (Home tab > Conditional Formatting) to highlight out-of-specifications results.

 用于显示计算结果（输出）的单元格可用特定颜色加以标识。当检验结果不符合可接受标准时，建议使用条件格式（首页选项卡＞条件格式），将超标结果标亮。

              - The name of the operator responsible for data entry, and the date and time of data entry should be recorded in dedicated input cells or the spreadsheet is printed, signed and dated after calculation.

负责数据输入操作者的姓名、数据输入的日期和时间应记录在专门的输入单元格，或打印电子单元格，计算后签名和签日期。

              - File path, spreadsheet filename and MS Excel® version number can be displayed within the print area of the spreadsheet. The Excel functions ‘=CELL("filename")’ ‘=INFO("RELEASE")’ can be used to display the path, filename, active sheet and the version number of MS Excel® in use.

=CELL("filename") 、‘=INFO("RELEASE")’

文件路径、电子表格文件名及MS Excel®版本号可陈列在表格的打印区域内。用Excel功能=CELL("filename")’ ‘=INFO("RELEASE")’来展示路径、文件名、当前表格和MS Excel®使用版本号

 - Password protection is recommended for all cells containing calculations (Review tab > Protect Sheet), with only the default options checked. The same password can be used for all sheets and can be documented in the validation file. The sheet protection password should not be communicated to the end users.

建议对所有设计计算的单元格设置密码保护(审阅选项卡> 保护表格)，只选默认选项。相同的密码可用于所表格，并在验证文件中记录。表格保护密码不应与终端用户交流。

 - After protecting each sheet, the workbook structure should also be password protected (Review tab > Protect Workbook). The same password can be used as the one for sheet protection.

每个表格保护之后，工作薄结构也应受到密码保护。（审阅选项卡＞保护工作薄）。可以使用与表格保护相同的密码。

An example of a spreadsheet used to calculate a vaccine titration is shown on the image below. From results obtained for a reference product (height measured at 4 concentrations), a calibration curve and its formula are provided. Both of them are needed to calculate the concentrations corresponding to the height measured for the tested vaccine.

下图展示了使用电子表格来计算疫苗滴定的例子。参照品监测得到的结果（4个浓度下测量高度），提供了一个矫正曲线及其公式。需要这两者来计算受检疫苗测量高度下对应的浓度

In the image, grey cells are filled with numerical data from experimentation and are the only ones that can be changed by the operator. All other cells are locked. No more than one cell from the calibration range can be empty; all cells for vaccines must be filled to guarantee proper use.

在这个图中，灰色单元格填满了从实验中获得的数据，并且是唯一的能被操作者更改的单元格。所有其他单元格是锁定的。校验范围中不能有一个单元格是空白的；疫苗一列的所有单元格必须都被填满，以保证正确使用。

4. VALIDATION STAGES 验证阶段

4.1. Documentation of the spreadsheet 电子表格的记录

There should be a general description of the spreadsheet explaining its purpose, general layout, input types and data validation rules if required (some spreadsheet might be self-explaining). This description can be documented in the spreadsheet itself (e.g. in a dedicated sheet), in a SOP or in the validation file.

应有一份电子表格的概述，解释其目的、主要布局。输入类型，必要时，包括数据验证规则。（有些表格可自述）。这类描述可记录在电子表格中（如单独的一个表中）、在一份SOP中或验证文件中。

Next to the general description, a full print-out of the spreadsheet where all formulas are shown (Formulas tab > Show Formulas) should be kept in the validation file.

When VBA1（Visual Basic for Applications） macros are used, the VBA code should also be printed and kept in the validation file.

一般描述之后，一份所有公式都展现出来的表格全打印应保存在验证文件中。如果运用了Basic程序语言（VBA）宏，那么VBA应打印出来并保存在验证文件中。

If matrix-formulas (array-formulas) are used, this must be indicated. An individual printout of each matrix formula is necessary. 如果运用了矩阵公式（数列公式），也必须显示出来。每个矩阵公式的单独打印是必要的。

All print-outs shall clearly identify the spreadsheet name or identification and version number. When a new version of the spreadsheet is being validated, a summary of the changes since the previous version should be given.

所有打印应清晰识别表格名称或识别号及版本号。当该表格的新版本需要验证时，应给出一份与前一版本的变化总结。

The version of Microsoft Excel used for the creation and validation of the spreadsheet should be traceable (either by the documentation of the spreadsheet or by the change log of the IT department), and any known incompatibilities with older or newer versions should be documented.

用于创建和验证电子表格的Microsoft Excel版本应是可追踪的（通过该电子表格的记录或通过IT部门的变更日志），任何与前一版本或新版本已知的不一致性都应记录。

The documentation of the spreadsheet can be considered as the URS.

电子表格的记录可看作是URS。

In order to properly document the spreadsheet, formulas shall be printed and entered into the validation document (see example below).

为了适当的记录电子表格，公式应打印出来，并输入验证文件中（举例如下）

4.2. Validation of the calculations of the spreadsheet  电子表格计算的验证

All calculations are to be verified with a system completely independent from the self-developed spreadsheet. One validation method is to compare the results obtained by the spreadsheet with results obtained by commercial software or with a calculator, using the same dataset as input. Another validation method is to compare the results obtained by the spreadsheet with published reference data (e.g. physicochemical data of substances).

所有计算应使用与自主开发表格独立开来的系统来确认。其中一个验证方法 是将电子表格计算得到的结果与商业软件或计算器获得的结果在输入相同数据集时进行比较。另一验证方法是将电子表格得到的数据与已发布的参考数据比较（如物质理化数据）

If the spreadsheet will be used on computers running different versions of Excel it is required to perform the validation of the functionality using each of those different versions as some newer Excel functions are not retro-compatible with older versions of Excel.

如果电子表格是用来在计算机上运行不同版本的Excel ，要求用每个不同版本来进行功能验证，或新版Excel功能与旧版Excel相比没有退化。

4.2.1. Validation of the calculations by using commercial software or published data

通过商业软件或发布数据来验证计算

A dataset as close to real values as possible must be chosen. Excel calculations are compared to the results given by commercial software or by published data, which are considered as validated (see example in the image below). The commercial software provides the coefficient of correlation, R2 and the coefficients of the calibration curve.

必须选择尽可能接近真实值的数据集。Excel计算结果与认为经验证的商业软件或发布数据比较（举例详见下图）。商业软件提供了系数关系，R2及校验曲线的系数。

If no discrepancy occurs, the validation of this part of the calculation is considered as fulfilled. If a discrepancy is observed, a check and revision of the formulas must be performed (and the whole validation re-performed).

如果无差异，计算这部分的验证就认为是符合要求的，如果发现差异，必须进行公式核查及修订（并且整个验证重新进行）。

4.2.2. Validation of the calculations with a calculator (manual calculation) 用计算器（人工计算）验证计算

Using the printed formulas from the spreadsheet, all concentrations are calculated using a calculator (see next image) and compared with the results given by the spreadsheet.

用表格中打印出来的公示，用计算器（见下图）计算所有浓度，并与电子表格给出的结果相比较。

As an alternative, the PC calculator can be used and documented in screen shoots, as in the image below. 另一种选择，可使用PC计算器，并用截屏记录，如下图所见。

If no discrepancy occurs, the validation of this part of the calculation is considered fulfilled. If a discrepancy is observed, both the revision of the formulas and the manual calculations should be repeated (and the whole validation re-performed).

  如果无差异，计算这部分的验证就认为是符合要求的，如果发现差异，必须进行公式核查及修订（并且整个验证重新进行）。

Moreover, calculations in paragraph 4.2.1 and 4.2.2 should be re-performed with other datasets including exceptional situations, for example: OOS results, missing data, or nonsense data. Calculations should also be validated under these conditions, as applicable (data not shown).

另外4.2.1 和4.2.2段落的计算应用其他数据集，包括排除情况，如OOS结果、数据缺失或无意义数据来重新进行。如果适用，这些情况下的计算也应进行验证（数据未显示）

4.2.3. Validation of the protections  验证保护功能

The following points shall be verified and documented: 以下几点应确认和记录

   - Access rights to the spreadsheet (e.g. on the network share) are correct: the file cannot be modified or deleted by users. 电子表格的访问权限（如，在共享网络上）无误：用户不可修改或删除文件

     - The different sheets within the spreadsheet are properly protected: only input cells can be edited, all other cells are locked. 电子表格中的不同表格受到适当保护：只有输入单元格可编辑，其他单元格是锁定的。

       - A password (if applicable) is needed to remove sheet protection and workbook protection. 取消表格保护和工作薄保护需要密码（如果适用）

At this stage, the spreadsheet is considered as validated and its status is issued and filed.

这种情况下，这个电子表格被认为是经过验证。

5. REGULAR VERIFICATION OF THE SPREADSHEET 电子表格的定期确认

Regularly, in a risk-based approach an OMCL should define an appropriate frequency of regular verification of an existing spreadsheet. After every change performed in the soft- or hardware configuration, the spreadsheet should be verified to ensure that its validated state is maintained. A known dataset is used and the results are compared to the standard one.

一般，基于风险的方法，OMCL应规定一个现有电子表格定期确认合适的频率。在软件或硬件配置出现变更后，应对电子表格进行确认以保证验证状态的维持。用已知的数据集及结果与标准结果比较。

In order to help the operator, verification instructions containing the information required should be available.

为了帮助操作者，应有包括所需信息的确认指导。

Each verification is registered, with the following information: date of operation, intervention (i.e. verification), comments, and operator’s signature. Results from the verification should be kept in the validation file or system documentation.

每个确认都是登记过的，包含以下信息：生效日期，干扰（如确认）、评论和操作者签名。来自确认的结果应保存在系统记录的确认文件中。