excel表格有效性设置技巧（EXCEL表格的合理设置和验证）

用作记录模板使用的电子表格

此类表格没必要以excel形式出现，建议PDF形式使用

台账类缺陷：有限数据完整性

台账类好的做法目前已使用质量管理系统或文件管理系统代替。EXCEL本身并不包含能够确保数据完整性的功能，因此需要有更多的控制措施。FDA曾对使用EXCEL作为投诉台账而无法确保数据修改和没有审计追踪的警告信：

FDA警告信已对投诉台账不合规落下严重

your Complaint SOP states that QA shall maintain the customer complaint report files. Your firm’s QA unit maintains a separate Excel spreadsheet for those complaints that come directly into the Bridgeton QA unit. You have no centralized system for tracking all your complaints. The use of Excel requires many management controls to prevent data alteration, and Excel does not have an audit trail to identify data changes.你们的投诉SOP里说QA应该维护客户投诉报告文件。贵公司的QA部门使用一个单独的Excel表格来维护那些直接发到Bridgeton QA部门的投诉。你们没有集中式的系统来跟踪你们所有的投诉。使用Excel需要很多管理控制措施来防止数据修改，并且Excel没有审计追踪来识别数据变更。——FDA Warning Letter:PM Resources, Inc. dba Virbac Animal Health, Inc. 12/18/15

有关使用EXCEL作为台账登记的数据完整性评估和合理做法罗列如下：

- 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.每次核查均应登记以下信息：操作日期、调整（即核查）、备注和操作人员签名。核查的结果应保存在验证文件中或系统文件中。

来源：制药在线

编辑整理：德斯特GMP（深圳）咨询服务

版权及免责声明：本公众号所有文章除标明原创外，均来自网络。登载本文的目的为传播行业信息，内容仅供参考，如有侵权请联系德斯特删除。文章版权归原作者及原出处所有。本公众号拥有对此声明的最终解释权。

德斯特（深圳）咨询服务有限公司cGMP团队一直致力于国际GMP认证咨询；针对美国GMP认证, EU-GMP认证, PIC/S认证, WHO认证以及中国GMP认证，可为客户提供完整的GMP认证解决方案；同时，在产品注册和产品技术转移，以及新建项目的设计、验证服务可提供专业的咨询服务。

,