Excel電子表格驗(yàn)證

1、Excel電子表格驗(yàn)證倒底是啥?我們談?wù)撊魏卧掝}都不能離開GMP這個(gè)根本，GMP的宗旨是什么？或者說(shuō)為什么要實(shí)行GMP建議大家再去查看一下GMP條款的第三條：“旨在最大限度地降低藥品生產(chǎn)過(guò)程中污染、交叉污染以及混淆、差錯(cuò)等風(fēng)險(xiǎn)，確保持續(xù)穩(wěn)定地生產(chǎn)出符合預(yù)定用途和注冊(cè)要求的藥品?！憋@然針對(duì)Excel電子表格的驗(yàn)證，目地是為了“防止出錯(cuò)”，這是我們今天分享內(nèi)容的核心原則?，F(xiàn)在回到Excel，是不是所有的Excel電子表格都需要驗(yàn)證？不是，只有涉及到產(chǎn)品放行、關(guān)鍵控制指標(biāo)的計(jì)算表格，必須經(jīng)過(guò)驗(yàn)證后才能使用。而平時(shí)方便自己簡(jiǎn)單計(jì)算、用于日常數(shù)據(jù)匯總的Excel并不需要經(jīng)過(guò)驗(yàn)證。根據(jù)計(jì)算機(jī)系統(tǒng)的三個(gè)部分：輸入、計(jì)算和輸出。下從數(shù)據(jù)輸入、公式核對(duì)和數(shù)據(jù)測(cè)試三個(gè)方面來(lái)介紹一下。—2、數(shù)據(jù)輸入是從源頭把關(guān)大多數(shù)人把Excel驗(yàn)證的重點(diǎn)放在公式的檢查、計(jì)算結(jié)果的復(fù)核，往往會(huì)忽略數(shù)據(jù)輸入的重要性，想一想，如果數(shù)據(jù)一開始就是錯(cuò)誤的，那后面的公式和結(jié)果又有什么意義呢？拿到一個(gè)Excel模板，第一件事是要分清哪些單元格是能夠輸入內(nèi)容的，哪些單元格是不需要輸入內(nèi)容，然后把這些單元格鎖定。具體的辦法是，在"單元格的格式“窗口中，選擇”鎖定“然后在”保護(hù)工作表“窗口中選擇”保護(hù)工作表及鎖定的單元格內(nèi)容“，再加上密碼。這樣一套組合拳下來(lái)，那些提示性單元格、計(jì)算公式、結(jié)果和結(jié)論，再也不能修改。除了輸入數(shù)據(jù)的單元格外，其它單元格均應(yīng)鎖定，包括空白單元格，也不能進(jìn)行修改、刪除、更改類型等操作，但可以進(jìn)行選擇和復(fù)制的操作。數(shù)據(jù)輸入驗(yàn)證時(shí)要用到一個(gè)重要的功能：“數(shù)據(jù)有效性”，在英文版的Excel中叫做“Datavalidation”，最新版的Excel2016中已經(jīng)改成"數(shù)據(jù)驗(yàn)證”。如果輸入文字內(nèi)容，比如輸入品名，這種相對(duì)固定的內(nèi)容，可以選擇“序列”的功能，提前輸入我們需涉及到的產(chǎn)品。如果輸入一個(gè)沒有的產(chǎn)品名稱，就會(huì)出現(xiàn)錯(cuò)誤提示。如果輸入的是數(shù)據(jù)，在輸入時(shí)要對(duì)數(shù)據(jù)的范圍進(jìn)行限制，對(duì)數(shù)據(jù)的正確性做一個(gè)初步的判斷。比如峰面積、稀釋倍數(shù)，一般來(lái)說(shuō)都會(huì)是正整數(shù)；檢驗(yàn)日期，這是一個(gè)日期，應(yīng)該在某個(gè)日期之后。稱樣量，應(yīng)該是一個(gè)小數(shù)，大于0；對(duì)照品含量，一定是個(gè)百分比數(shù)，介于0.9-1.0之間;做好了這些，數(shù)據(jù)驗(yàn)證工作就做好一半了。在驗(yàn)證也可以有意的輸入一些符號(hào)、亂碼，或者明顯不合邏輯的數(shù)值（比如負(fù)值），看一下是否能夠出現(xiàn)錯(cuò)誤提示信息，如果不改更，無(wú)法輸入下一個(gè)數(shù)據(jù)。3、公式審核—單元格的坐標(biāo)是用字母和數(shù)字組成的，比如：A1、C1:C10。公式就是用這樣的方式來(lái)引用單元格范圍，單元格的引用方式包括相對(duì)引用和絕對(duì)引用，用“$”表示，這兩種引用方式的區(qū)別是在進(jìn)行公式自動(dòng)填充時(shí)，單元格是否自動(dòng)變化，比如：1ABCDEF |=A12二A2二SA$13二A3=$A$14=A4=$A$15-A5邙A$16=A87二A78二A8二$A$19-A3=$A$110=A10二$小1也Jj'.I|mLb \11廣,r#Er1* 1LEI* 1L11 '這里給出一個(gè)小技巧，可以用“F4”這個(gè)快捷鍵來(lái)切換單元格的四種引用方式。順序是:A1-->$A$1-->A$1-->$A1驗(yàn)證時(shí)要顯示公式，在“公式”標(biāo)簽選擇“顯示公式”，這時(shí)所有的公式都以單元格引用的方式顯示，也可以打印到紙張上進(jìn)行核對(duì)，根據(jù)每個(gè)人的不同經(jīng)驗(yàn)和習(xí)慣。一個(gè)好的習(xí)慣是公式書寫的規(guī)范，可以提高驗(yàn)證的效率，減少出錯(cuò)的機(jī)會(huì)，比如下面這個(gè)公式：=$C18*$H$9/$B$6/$F$6*$H$6*100/$E18*$F$18 …….(a)是不是有一種掉到錢堆里的感覺，一時(shí)半會(huì)理不出頭緒，這種公式驗(yàn)證的難度大、錯(cuò)誤多。如果寫成下面這樣，將分子和分母用括號(hào)區(qū)分，計(jì)算結(jié)果是完全一樣的，公式的可讀性卻大大增加:=($C18*$H$9)/($B$6*$F$6)*($H$6*100*$F$18)/$E18 …….(b)更好的辦法是采用定義名稱的方式，將單元格或范圍定義成具體的名稱，在公式中可以直接引用名稱，這已經(jīng)和實(shí)際的公式?jīng)]有什么區(qū)別了，基本上可以避免低級(jí)錯(cuò)誤：=($^8*樣品稀釋)/(標(biāo)示量*對(duì)照品稀釋)*(F值*100*平均片重)/$E18 …….(c)4、數(shù)據(jù)測(cè)試—Excel計(jì)算模板使用前必須經(jīng)過(guò)數(shù)據(jù)測(cè)試，確認(rèn)沒有錯(cuò)誤，才能最終發(fā)布。這是驗(yàn)證中最重要、也是工作量最大的部分，主要目地是為了發(fā)現(xiàn)遺漏的錯(cuò)誤和一些特別的錯(cuò)誤，不能省略這項(xiàng)工作，小錯(cuò)誤害死人的事情發(fā)生太多。方法簡(jiǎn)單又枯燥，輸入數(shù)據(jù)，然后核對(duì)結(jié)果，測(cè)試過(guò)程要有記錄，要將每一個(gè)批次單獨(dú)命名存盤，打印存檔備查。數(shù)據(jù)量致少要在50批以上。不要只核對(duì)最終結(jié)果，要對(duì)每一個(gè)過(guò)程數(shù)據(jù)進(jìn)行復(fù)核。如果有條件，可以對(duì)結(jié)果進(jìn)行手工復(fù)核。要用大量的已有的檢驗(yàn)數(shù)據(jù)來(lái)進(jìn)行測(cè)試。經(jīng)過(guò)這樣的測(cè)試，一些數(shù)據(jù)類型錯(cuò)誤、格式錯(cuò)誤、有效性設(shè)置方面的錯(cuò)誤、公式的計(jì)算和顯示錯(cuò)誤都能發(fā)現(xiàn)。要選用一些特別的數(shù)據(jù)進(jìn)行測(cè)試。比如有意輸入一些不合格的數(shù)值，看看最終結(jié)果是否會(huì)體現(xiàn)為不合格。尤其要設(shè)計(jì)一些限度值邊緣的數(shù)據(jù)，看一下限度值上下數(shù)據(jù)的微小變化，能否導(dǎo)致結(jié)論的變化，這個(gè)測(cè)試很重要，因?yàn)槲⑿〉臄?shù)據(jù)變化就會(huì)涉及到合格與否的判斷?！?、模板的發(fā)布也很重要?！?jīng)過(guò)測(cè)試的模板應(yīng)該將輸入內(nèi)容清空，工作表經(jīng)保護(hù)，并加上密碼。正式發(fā)布的模板應(yīng)該按GMP文件管理，有相應(yīng)的審批流程，存檔備案。為了防止模板被隨意修改，模板編制完成后需要對(duì)文件進(jìn)行加密運(yùn)算，常用的Hash工具很多，計(jì)算結(jié)果為唯一值，只要模板內(nèi)容有任何的變動(dòng)，計(jì)算值都不一樣，驗(yàn)證開始時(shí)應(yīng)首先確認(rèn)文件的