计算机验证 附录1 EXCEL表格的验证（ 中英文）.docx

OfMedkines dmedkametmpmeHdW Vw CourtcM c EuropeEuroKdnDiMlOUIeDirKbCmeUrCPm(OKkQwIitydeUqualite&HeaIthCare&SoinsdewnteGeneralEuropeanOMCLNetwork(GEON)QUALITYMANAGEMENTDOCUMENT计算机化系统的验证PA/PH/OMCL(08)87R6VALIDATIONOFCOMPUTERISEDSYSTEMSANNEX1-VALIDATIONOFEXCELSPREADSHEETS计算机化系统的验证附录I-EXCEL表格的验证FulldocumenttitleandreferenceValidationofComputerisedSystemsAnnex1-ValidationofExcelSpreadsheetsPA/PH/OMCL(08)87R6文件全名和索引号PA/PH/OMCL(08)87R6计算机化系统的验证一附录1EXCEL表格的验证DocumenttypeGuideline文件类型指南Legislativebasis-立法基础-DateoffirstadoptionMay2009首次采纳日期2009年5月DateoforiginalentryintoforceJuly2009首次执行日期2009年7月DateofentryintoforceofreviseddocumentAugust2018修订后执行日期2018年8月Previoustitles/otherreferences/lastvalidversionValidationofComputerisedSystemsAnnex1:Validationofcomputerisedcalculationsystems:exampleofvalidationofin-housesoftwarePA/PH/OMCL(08)872R原文件名/其它索引号PA/PH/OMCL(08)872R计算机化系统的验证附录1：计算机化计算系统的验证：自编软件的验证样例CustodianOrganisationThepresentdocumentwaselaboratedbytheOMCLNetwork/EDQMoftheCouncilofEurope托管机构现行文件由EC的EDQM的OMCL网络制订ConcernedNetworkGEON相关网络GEONANNEX1OFTHEOMCLNETWORKGUIDELINE“VALIDATIONOFCOMPUTERISEDSYSTEMSh计算机化系统的验证附录1VALIDATIONOFEXCELSPREADSHEETSEXCEL表格的验证Note:Mandatoryrequirementsinthisguidelineanditsannexesaredefinedusingtheterms«shall»or«must».Theuseof«should»indicatesarecommendation.Forthesepartsofthetextotherappropriatelyjustifiedapproachesareacceptable.Theterm«can»indicatesapossibilityoranexamplewithnon-bindingcharacter.注:本指南及其附录中的强制要求采用术语应'或"必须进行定义。使用“应，表示是建议,其下所列要求可采用经过适当论证的其它方法来实施。术语”可i，表示一种可能性,或非强制特性举例。1. INTRODUCTIONJThisisthe1stAnnexofthecoredocument"ValidationofComputerisedSystems'',anditshouldbeusedincombinationwiththelatterwhenplanning,performinganddocumentingthevalidationprocessofExcelspreadsheetsusedfortheprocessingoflaboratorydata.本文是“计算机化系统的验证”核心文件的附录1,应在规划、实施和记录用于实验室数据处理所用EXCEL表格的验证过程中与核心文件联合使用。ThisAnnexpresentsanexampleofExcelspreadsheetvalidation,whichshouldbeusedincombinationwiththegeneralrequirementsandrecommendationsgiveninthecoredocument.本附录给出了一份EXCEL表格验证的例子，该例子应与核心文件中所述通用要求和建议联合使用。2. INSTALLATIONANDSECURITY安装和安全Toguaranteethatonlythelatestvalidatedversionofthespreadsheetisbeingusedandtomaintainthevalidatedstateofthespreadsheet,allvalidatedExcelspreadsheetsshouldbestoredwithread-onlyaccessrightsfortheendusers(e.g.,onaprotectednetworkshare).Onlyresponsiblepersonsshouldhavewriteaccesstothenetworkshare.为保证只会使用经过验证的最新版本表格，并且维护表格经过验证的状态，所有经过验证的EXCEL表格存贮后仅为最终用户提供只读权限(例如，放在一个受保护的网络共享中)。只有负责人才应具备对网络共享的写入权限。Endusersshouldhavenorighttomodifyavalidatedspreadsheet,addanon-validatedspreadsheettotheshare,orsavedataontheshare.Endusersshouldonlyhavetherighttofillinthe(permitted)cellsandtoprintthedataorsaveacopytoadatarepositoryifneeded.最终用户不应具备权限修改经过验证的表格、增加未经验证的表格至共享、或存贮数据在共享位置。最终用户应只有权力填写(允许填写)的单元格，可以打印数据，或(在必要时)存贮一份副本至数据存贮区域。Installationshallbedocumented,e.g.inthevalidationfile,inasystemlogbookoronaQAform.Thenameofthespreadsheet,uniqueidentification,localisation,andthepersonresponsibleforthespreadsheetshallbedocumented.Therecordsshallalsoincludeverification,regularverificationandotherissuessuchasupdatesoranyproblemencountered.Verificationiscompletedafterinstallationandrecorded.安装过程应有记录，例如，在验证文件中，可记录在系统登记本中或记录在一份QA表格里。应记录表格的名称、唯一识别号、位置和表格负责人。表格还应包括核查、定期核查和其它问题如更新或任何遇到的问题。安装之后应完成核查并记录核查过程。3. GoODPRACTICES优良规范Whensettingupanewspreadsheet,followingthegoodpracticesbelowW川reducetheriskofaccidentalmodificationsofthetemplateanderroneousdatainput:在设置一份新的表格时，遵守以下优良规范可以降低模板被意外修改和数据输入错误的风险。- Allcalculatingcellsshallbelocked(FormatCellsProtectionLocked)inordertoprotectcellscontainingcalculationsagainstunintendedmodification,exceptthoseusedfordatainput.- 所有计算单元格均应锁定(单元格格式保护锁定)，以保护含有计算功能的单元格免受无意修改，用于数据输入的单元格无需保护。-Cellsusedfordatainputcanbeidentifiedbyaspecificcolour.-用于数据输入的单元格可以用特别的颜色进行识别- Datavalidationrules(DatatabDataValidation)canbeappliedtodatainputcellstopreventtheintroductionofaberrantvalues.InputmessagesandErroralertmessagescanbeusedtoinformtheenduseroftheexpecteddatatypeandacceptablerange.- 数据验证规则(数据数据有效性)可用来防止在数据输入单元格中录入非法值。输入信息和错误报警信息可用来提醒最终用户所需的数据类型和可接受的数据范围。DMVahdMi5 InpuSMesMQe EnB ”数居有效性- Cellsusedforpresentingtheresultsofthecalculations(output)canbeidentifiedbyaspecificcolour.Whentheresultsaretestedagainstacceptancecriteriaitisrecommendedusingconditionalformatting(Hometab>ConditionalFormatting)tohighlightout-of-Specificationsresults.- 用于呈现计算结果的单元格(输出)可采用特别颜色进行识别。如果结果要与可接受标准进行比较，建议使用条件格式(开始>条件格式)来着重显示Oc)S结果。(本人使用版本2010professionalplus,因此本截屏有些不同)- Thenameoftheoperatorresponsiblefordataentry,andthedateandtimeofdataentryshouldberecordedindedicatedinputcellsorthespreadsheetisprinted,signedanddatedaftercalculation.- 负责数据录入的操作员姓名以及数据录入的日期和时间应记录在专用的输入单元格中，或将计算完成后的表格打印并签字/日期。- Filepath,spreadsheetfilenameandMSExcelversionnumbercanbedisplayedwithintheprintareaofthespreadsheet.TheExcelfunctions'=CELL("filename")''=INFO("RELEASE")'canbeusedtodisplaythepath,filename,activesheetandtheversionnumberofMSExcelinuse.- 文件路径、表格文件名称和MSEXCEL版本号可以显示在表格的打印区域。表格函数'=CELL("filename")'HNFO("RELEASE”)'可用于显示路径、文件名、活动表和所用的MSEXCEL版本号。Bl=CELLCfiIename")ABC1Path:UrProfessionalDesktopExcelTemplate.xlsxSheetlI2Version:1.02- Passwordprotectionisrecommendedforallcellscontainingcalculations(Reviewtab>ProtectSheet),withonlythedefaultoptionschecked.Thesamepasswordcanbeusedforallsheetsandcanbedocumentedinthevalidationfile.Thesheetprotectionpasswordshouldnotbecommunicatedtotheendusers.- 建议对所有含有计算的单元格进行密码保护(审阅>保护表格)，只勾选默认选项。相同的密码可用于所有表格，可记录在验证文件中。表格保护密码不应告诉给最终用户。- Afterprotectingeachsheet,theworkbookstructureshouldalsobepasswordprotected(Reviewtab>ProtectWorkbook).Thesamepasswordcanbeusedastheoneforsheetprotection.- 在保护了每个表格之后，工作薄结构也要进行密码保护(审阅>保护工作薄)。可以使用与工作表保护相同的密码。Anexampleofaspreadsheetusedtocalculateavaccinetitrationisshownontheimagebelow.Fromresultsobtainedforareferenceproduct(heightmeasuredat4concentrations),acalibrationcurveanditsformulaareprovided.Bothofthemareneededtocalculatetheconcentrationscorrespondingtotheheightmeasuredforthetestedvaccine.用于计算疫苗滴定的表格样例显示如下图。从对照品所得结果(4个浓度测量高度)得到一条校正曲线和公式.在计算待检疫苗测量高度对应浓度时需要使用该曲线和公式。TechncianBC_软件名称版本10 操作者+格日期F门3?“,*,正1 .,M .14151617181920212223242526272829303132333435:高度<mm>65 0028 0055 0027 0045 0020 5035 00185065 0028 0055 00245045 0021 0035 1850Ra= 095y=0 335x÷6 5R3=O 94729827 25.0017.00 23.8吆 21.00 糜19815.00308 35.00 408 45 8 50.00 55.00 88 65 8 70浓度(gml)批号高度mm计算不度<rm>计算浓度剂CV%X18 0034 3317 1817 5032 841642190037 311866平均值18.1734 8317416 55有效Z25 0055 2227.6127 0061 1930 6027 61 1930 60平均值26.3359 2029 605 82有效化蛤员由核员Intheimage,greycellsarefilledwithnumericaldatafromexperimentationandaretheonlyonesthatcanbechangedbytheoperator.Allothercellsarelocked.Nomorethanonecellfromthecalibrationrangecanbeempty;allcellsforvaccinesmustbefilledtoguaranteeproperuse.图中，灰色单元格填入了实验中获得的数字数据，操作人员只能改变这些单元格。所有其它的单元格均被锁定。校正范围中单元格只有一个可以为空，用于疫苗的所有单元格均必须填入数据以保证其适当使用。4. VALIDATIONSTAGES验证阶段4.1. Documentationofthespreadsheet表格文件记录Thereshouldbeageneraldescriptionofthespreadsheetexplainingitspurpose,generallayout,inputtypesanddatavalidationrulesifrequired(somespreadsheetmightbeself-explaining).Thisdescriptioncanbedocumentedinthespreadsheetitself(e.g.inadedicatedsheet),inaSOPorinthevalidationfile.应为表格制订一份常规描述来解释其用途、结构、输入类型和数据验证规则(如需要)(有些表格可能比较直观)。此描述可记录在表格本身中(例如，放在一张专用表格里)、在SoP中或在验证文件中。Nexttothegeneraldescription,afullprint-outofthespreadsheetwhereallformulasareshown(Formulastab>ShowFormulas)shouldbekeptinthevalidationfile.在常规描述之后，在验证中文件中要保存一份完整打印出的表格，在其中要显示所有公式(公式>显示公式)。WhenVBAVisuaI Basic for Applicationsmacrosareused,theVBAcodeshouldalsobeprintedandkeptinthevalidationfile.如果使用了VBA宏，则还应打印出VBA代码并保存在验证文件中。Ifmatrix-formulas(array-formulas)areused,thismustbeindicated.Anindividualprintoutofeachmatrixformulaisnecessary.如果使用了矩阵公式(数组公式)，则必须指明，每个矩阵公式需要单独打印。C40'=HAUFIGK日T(B5:B14;D38:D40)ICE3738=HAFIGKEIT(B5:B14;D38:D40)39=HAUFlGKElT(B5:B14;D38:D40)40I=H-UFIGKEIT(B5:B14;D38:D40)C4o1A=Frequency(b57bi4,d38?D40)C3738=frequency(bs：bi4,D38：D40)39FFREQUENCY(B5:Bl4.D38:D40)40I=FREQUENCY(B5:Bl4,D38:D40)(注：HAUFlGKElT为荷兰语，即频率，英文版本函数为FREQUENCY)Allprint-outsshallclearlyidentifythespreadsheetnameoridentificationandversionnumber.Whenanewversionofthespreadsheetisbeingvalidated,asummaryofthechangessincethepreviousversionshouldbegiven.所有打印件均应清楚标明表格名称或编号和版本号。如果正在验证该表格的一个新版本，要写出自上个版本以来变更摘要。TheversionofMicrosoftExcelusedforthecreationandvalidationofthespreadsheetshouldbetraceable(eitherbythedocumentationofthespreadsheetorbythechangelogoftheITdepartment),andanyknownincompatibilitieswitholderornewerversionsshouldbedocumented.创建表格和验证表格中所用的微软EXCEL的版本应可追溯(在表格文件中，或在IT部门的变更登录表中)，要记录所有与旧版或新版不兼容的已知信息。ThedocumentationofthespreadsheetcanbeconsideredastheURS.表格的文件可被作为URSoInordertoproperlydocumentthespreadsheet,formulasshallbeprintedandenteredintothevalidationdocument(seeexamplebelow).为了恰当地记录表格，应将公式打印出来并放入验证文件(参见下例)。3ISoftMrenaneVaeiOn1.OperatorDMe65Conc(MgAnI)H(mm)5545356555154535-COEFFICIENTDETERMINATION6)=SHB1»0a8511I<XV5Mli<nrBMChrWmgHeightmmCsicutetedMCentrettOn3M11)X18-(B24ORDONNEeORIONE(K39K$16.阳豺6哪ENTE(K39阮$16,5豺3BJ16)1751925-ORDONNEORIGINE(K39K$16,$8$93B116ftff>ENTE(K39K$16,$0t94B116)-(B26ORDONNEORIONE(K39阮$16.S母3BJ16)VPENTE(K396)mean=<B24*B25÷B2C)J=(B27-ORDONNEEORIGINEaCS9KW6.f豺3印16WENTE(K59K箱6,SQ四6)FX25-28-ORDONNEORlGlNEeK39K$1的38*6/ENTE(K49K$16.$0豺SEl6)2j27(B29.ORDONNEE.0RIGINErK9乳$161S豺：3四16WNTE(IC$9阮$161S厚3816)P27=®30.C«DONNEE闲。INE(IC$9K$16W豺3即6丽ENTE(W$9K$16A阳阳3附6)31meanXB28+B29+B3"3三(B31.ORDONNEE.ORIGINE(K39阮M6.阳豺.3Bil6mENTE(K49阮116.田豺:3BII6)-Z51TcicSOIhWOrwn<30Q4Z2009v*racn1f4.2. Validationofthecalculationsofthespreadsheet表格计算的验证Allcalculationsaretobeverifiedwithasystemcompletelyindependentfromtheself-developedspreadsheet.Onevalidationmethodistocomparetheresultsobtainedbythespreadsheetwithresultsobtainedbycommercialsoftwareorwithacalculator,usingthesamedatasetasinput.Anothervalidationmethodistocomparetheresultsobtainedbythespreadsheetwithpublishedreferencedata(e.g.physicochemicaldataofsubstances).所有计算功能均应在一个完全独立于内部开发表格的系统内进行核对。一个验证方法是采用相同的录入数据系列，将表格计算所得结果与商业化软件计算所得结果，或与计算器所得结果进行比较。另一个验证方法是比较表格所得结果与公开发布的对照数据(例如，物质的理化数据)。IfthespreadsheetwillbeusedoncomputersrunningdifferentversionsofExcelitisrequiredtoperformthevalidationofthefunctionalityusingeachofthosedifferentversionsassomenewerExcelfunctionsarenotretro-compatiblewitholderversionsofExcel.如果表格要用于运行不同EXCEL版本的计算机，则需要使用每个不同版本来执行功能验证，因为有些新的EXCEL函数不能向下兼容旧版EXCELo421.Validationofthecalculationsbyusingcommercialsoftwareorpublisheddata使用商业化软件或公开发布数据进行计算验证Adatasetasclosetorealvaluesaspossiblemustbechosen.Excelcalculationsarecomparedtotheresultsgivenbycommercialsoftwareorbypublisheddata,whichareconsideredasvalidated(seeexampleintheimagebelow).Thecommercialsoftwareprovidesthecoefficientofcorrelation,R2andthecoefficientsofthecalibrationcurve.必须选择一套尽可能真实的数据系列。将EXCEL计算与被认为是经过验证的商业化软件或公开发布的软件计算结果进行比较(参见下图中举例)。商业化软件提供了相关系数，R2和校正曲线因子。RegressionAnalysis-Linearmodel:Y三a+b*XDependentvariable:HIndependentvariable:ConcParameterEstimateStandardErrorTStatisticP-ValueIntercept6.51.65693.922990.0078Slope0.3350.032339310.35890.0000AnalysisofVarianceSourceSumofSquaresDfMeanSquareF-RatioP-ValueModel112.2251112.225107.310.0000Residual6.27561.04583Total(Corr.)118.57CorrelationCoefficient=0.973163R-SqUared-94.7046percentStandardErrorofEs.=1.02266TheStatAdvisorTheoutputshowstheresultsoffittingalinearmodeltodescribetherelationshipbetweenHandConc.TheequationofrhefittedmodelisH=.5+0.335*ConcSincetheP-valueintheANOVAtableislessthan0.OlzthereisastatisticallysignificantrelationshipbetweenHandConcatrhe99%confidencelevel.TheR-Squaredstatisticindicatesthatthemodelasfittedexplains94.7046%ofthevariabilityinH.Thecorrelationcoefficientequals0.973163,indicatingarelativelystrongrelationshipbetweenrhevariables.Thestandarderroroftheestimateshowsthestandarddeviationoftheresidualstobe1.02266.ThisvaluecanbeusedtoconstructpredictionlimitsfornewobservationsbyselectingtheForecastsoptionfromthetexrmenu.Ifnodiscrepancyoccurs,thevalidationofthispartofthecalculationisconsideredasfulfilled.Ifadiscrepancyisobserved,acheckandrevisionoftheformulasmustbeperformed(andthewholevalidationre-performed).如果没有差异，则可认为本部分计算的验证满足要求。如果发现有差异，则必须对公式进行检查和修订(然后重新执行整个验证)。4.2.2. Validationofthecalculationswithacalculator(manualcalculation)采用计算器对计算功能进行验证(人工计算)Usingtheprintedformulasfromthespreadsheet,allconcentrationsarecalculatedusingacalculator(seenextimage)andcomparedwiththeresultsgivenbythespreadsheet.使用从表格中打印出的公式，使用计算器计算所有浓度(见下图)，与表格中给出的结果进行比较。Sotware nme version IQOperatorDateXO0Cl3Form：H:"c>yc.Technician :Batc numberHgh( mmCalculated cetration (gml)Calculated concentration (gdose)CV%XAS工33Cd1.S32 .ti,d,l乙Afi.tmean1.d6SSyIS£5.22n30. Cd>/I. /33。meanLC. 33knc<- c S32o24/。S. 12Va以0Saentist03/06/2009Softwarenameversion10Asanalternative,thePCcalculatorcanbeusedanddocumentedinscreenshoots,asintheimagebelow.作为替代，可使用PC上的计算器并如下图截屏记录。融“和” “33M“36”3839404t42414445460Ifnodiscrepancyoccurs,thevalidationofthispartofthecalculationisconsideredfulfilled.Ifadiscrepancyisobserved,boththerevisionoftheformulasandthemanualcalculationsshouldberepeated(andthewholevalidationre-performed).如果未发现差异，则认为本部分计算的验证满足要求。如果发现有差异，则应修改公式，重复人工计算(并重新执行全部验证)。Moreover,calculationsinparagraph4.2.1and4.2.2shouldbere-performedwithotherdatasetsincludingexceptionalsituations,forexample:OOSresults,missingdata,ornonsensedata.Calculationsshouldalsobevalidatedundertheseconditions,asapplicable(datanotshown).另外，应使用其它数据系列包括例外情形重新执行段4.2.1和4.2.2中的计算，如，C)C)S结果、缺失数据、或无意义数据。适当时，在这些情形下的计算也需要进行验证(未展示数据)。4.2.3. Validationoftheprotections保护的验证Thefollowingpointsshallbeverifiedanddocumented:以下点应进行核查和记录：- Accessrightstothespreadsheet(e.g.onthenetworkshare)arecorrect:thefilecannotbemodifiedordeletedbyusers.- 表格进入权限(例如，在网络共享上)正确：用户不能修改或删除文件- Thedifferentsheetswithinthespreadsheetareproperlyprotected:onlyinputcellscanbeedited,allothercellsarelocked.- 工作表内不同表格受到适当保护：只有输入单元格可以进行编辑，所有其它单元格均锁定- Apassword(ifapplicable)isneededtoremovesheetprotectionandworkbookprotection.- 取消表格保护和工作薄保护时需要密码(适用时)