201803 EXCEL表格的验证 OMCL指南附录1

重要的话只说一遍：本附录为OMCL指南“计算机化系统验证”的附录，并非EU GMP附录“计算机化系统验证“的附录！！！

PA/PH/OMCL (08) 87 R6 计算机化系统的验证—附录1 EXCEL表格的验证

生效日期：2018-08-01

ANNEX 1 OF THE OMCL NETWORK GUIDELINE “VALIDATION OF COMPUTERISED SYSTEMS”
计算机化系统的验证 附录1
VALIDATION OF EXCEL SPREADSHEETS
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.
注：本指南及其附录中的强制要求采用术语“应”或“必须”进行定义。使用“应”表示是建议，其下所列要求可采用经过适当论证的其它方法来实施。术语“可”表示一种可能性，或非强制特性举例。

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.
本文是“计算机化系统的验证”核心文件的附录1，应在规划、实施和记录用于实验室数据处理所用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.
-    用于呈现计算结果的单元格（输出）可采用特别颜色进行识别。如果结果要与可接受标准进行比较，建议使用条件格式（开始>条件格式）来着重显示OOS结果。

-    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.
-    文件路径、表格文件名称和MS 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 VBA  macros are used, the VBA code should also be printed and kept in the validation file.
如果使用了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.
创建表格和验证表格中所用的微软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.
每次核查均应登记以下信息：操作日期、调整（即核查）、备注和操作人员签名。核查的结果应保存在验证文件中或系统文件中。

译文仅供参考，中英文PDF版本点击阅读原文下载，英文官网原文参见 https://www.edqm.eu/sites/default/files/guidelines-omcl-computerised-systems-annex1-march2018.pdf

转自微信公众号：Julia法规翻译