教你用Excel繪制實驗室質量控制圖
2018-08-20
作者：
瀏覽數：2019
檢(jian)測過程中(zhong)(zhong)不(bu)可避免地(di)存在(zai)波(bo)(bo)動情(qing)況。其(qi)(qi)異(yi)常波(bo)(bo)動對檢(jian)測質(zhi)(zhi)(zhi)量(liang)有較大影(ying)響，質(zhi)(zhi)(zhi)量(liang)控(kong)(kong)(kong)(kong)(kong)制(zhi)圖是(shi)消除異(yi)常波(bo)(bo)動的(de)有效方法(fa)，因(yin)此質(zhi)(zhi)(zhi)量(liang)控(kong)(kong)(kong)(kong)(kong)制(zhi)圖是(shi)實(shi)驗(yan)室內部質(zhi)(zhi)(zhi)量(liang)控(kong)(kong)(kong)(kong)(kong)制(zhi)中(zhong)(zhong)常用(yong)的(de)質(zhi)(zhi)(zhi)量(liang)控(kong)(kong)(kong)(kong)(kong)制(zhi)方法(fa)。許多儀器的(de)工(gong)作(zuo)站是(shi)不(bu)具有質(zhi)(zhi)(zhi)控(kong)(kong)(kong)(kong)(kong)圖的(de)建立的(de)，在(zai)數(shu)據(ju)的(de)整(zheng)理過程中(zhong)(zhong)常常是(shi)手工(gong)繪制(zhi)較多，且每(mei)次有新(xin)的(de)數(shu)據(ju)編入時(shi)，其(qi)(qi)上(shang)(下)控(kong)(kong)(kong)(kong)(kong)制(zhi)限、上(shang)(下)警(jing)戒限及中(zhong)(zhong)心線就發生變(bian)化，需要重(zhong)新(xin)繪制(zhi)新(xin)的(de)質(zhi)(zhi)(zhi)控(kong)(kong)(kong)(kong)(kong)圖才能對分析質(zhi)(zhi)(zhi)量(liang)進行控(kong)(kong)(kong)(kong)(kong)制(zhi)，這樣費(fei)時(shi)費(fei)力。怎樣不(bu)依賴質(zhi)(zhi)(zhi)控(kong)(kong)(kong)(kong)(kong)圖軟件而只應(ying)用(yong)普通的(de)辦公軟件制(zhi)作(zuo)質(zhi)(zhi)(zhi)量(liang)控(kong)(kong)(kong)(kong)(kong)制(zhi)圖是(shi)質(zhi)(zhi)(zhi)控(kong)(kong)(kong)(kong)(kong)圖能否廣泛應(ying)用(yong)于各級檢(jian)測實(shi)驗(yan)室的(de)關鍵。
質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)一般(ban)分為:平(ping)均(jun)值質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)，極差質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)和(he)標(biao)準(zhun)差質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)三種。無論(lun)哪種控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)都離不開(kai)控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)限、警戒限及中心線的繪制(zhi)(zhi)(zhi)(zhi)(zhi)，而且現(xian)在實驗室常用(yong)的質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)一般(ban)為平(ping)均(jun)值質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)，那么(me)該如何僅應用(yong)辦公軟件Excel就能(neng)繪制(zhi)(zhi)(zhi)(zhi)(zhi)出平(ping)均(jun)值質(zhi)(zhi)(zhi)量控(kong)(kong)(kong)(kong)制(zhi)(zhi)(zhi)(zhi)(zhi)圖(tu)(tu)呢?
1.在檢測過程中進行分析質量控(kong)制
在(zai)分(fen)析某一(yi)項目時(shi)，把質控(kong)樣在(zai)不同日期按照與分(fen)析實測(ce)樣品相(xiang)同進(jin)行分(fen)析，每次(ci)(ci)至少平行分(fen)析兩(liang)次(ci)(ci)。分(fen)析結(jie)果的相(xiang)對相(xiang)差(cha)符合要求者(zhe)保留，否則(ze)報廢重(zhong)新進(jin)行分(fen)析。當(dang)控(kong)制樣的結(jie)果累(lei)積(ji)達(da)20次(ci)(ci)以(yi)上時(shi)，計算均值及標準偏(pian)差(cha)，進(jin)行控(kong)制樣質控(kong)圖的建立。
2。質(zhi)量控制工作表的建(jian)立(li)2.1基本信息填寫
基本信息包括:質控項目、質控方法、控制樣名(ming)稱及編號、儀(yi)器型號、試劑名(ming)稱及批號、質控起止時間等。
2.2質量控制工作表的構成(cheng)
新建Excel表(biao)，見表(biao)1所列，按日期、質(zhi)控樣測定數據(ju)、平均值
排(pai)列(lie)(lie)，表列(lie)(lie)按日(ri)(ri)(ri)期(qi)(qi)順序排(pai)列(lie)(lie)。為(wei)使質(zhi)控(kong)(kong)(kong)圖的(de)質(zhi)量控(kong)(kong)(kong)制數(shu)據(ju)點能(neng)更清楚地(di)顯示在(zai)圖的(de)正(zheng)中(zhong)(zhong)(zhong)央，需將第一(yi)個日(ri)(ri)(ri)期(qi)(qi)向上延伸幾(ji)(ji)日(ri)(ri)(ri)，最后一(yi)個日(ri)(ri)(ri)期(qi)(qi)向下延伸幾(ji)(ji)日(ri)(ri)(ri)，同時測定日(ri)(ri)(ri)期(qi)(qi)按"6-5”表示2011年6月(yue)5日(ri)(ri)(ri)輸人(ren)，為(wei)了在(zai)質(zhi)控(kong)(kong)(kong)圖中(zhong)(zhong)(zhong)更簡便地(di)顯示出來(lai)將Excel默認的(de)“2011年6月(yue)5日(ri)(ri)(ri)”在(zai)選定的(de)情況下，右鍵菜單選擇(ze)“設置單元(yuan)格格式(shi)”中(zhong)(zhong)(zhong)的(de)“數(shu)字”欄(lan)中(zhong)(zhong)(zhong)“分類”中(zhong)(zhong)(zhong)“日(ri)(ri)(ri)期(qi)(qi)”選擇(ze)“3-14"，日(ri)(ri)(ri)期(qi)(qi)表達即(ji)為(wei)“6-5”的(de)形式(shi)。在(zai)日(ri)(ri)(ri)期(qi)(qi)輸人(ren)完成后，最后一(yi)行計算平均(jun)值x、標(biao)準偏差(cha)s、相對標(biao)準偏差(cha)RSD及質(zhi)控(kong)(kong)(kong)次數(shu)n。
2.3質控表(biao)公式編輯(ji)
在(zai)最后一行(xing)的(de)計(ji)算平均值(zhi)(zhi)(zhi)x“=AVERAGE(測(ce)定值(zhi)(zhi)(zhi)起(qi)始(shi)(shi)位(wei)置(zhi):測(ce)定值(zhi)(zhi)(zhi)終止(zhi)位(wei)置(zhi))”;標(biao)準(zhun)偏差(cha)s“=STDEV(質(zhi)(zhi)控數(shu)(shu)(shu)據起(qi)始(shi)(shi)位(wei)置(zhi):質(zhi)(zhi)控數(shu)(shu)(shu)據終止(zhi)位(wei)置(zhi))”;相對標(biao)準(zhun)偏差(cha)RSD=s/x*100;質(zhi)(zhi)控次數(shu)(shu)(shu)n“=COUNT(質(zhi)(zhi)控數(shu)(shu)(shu)據起(qi)始(shi)(shi)位(wei)置(zhi):質(zhi)(zhi)控數(shu)(shu)(shu)據終止(zhi)位(wei)置(zhi))”。在(zai)平均值(zhi)(zhi)(zhi)二列(lie)下輸(shu)人“=$列(lie)$行(xing)”，如(ru)表1中計(ji)算平均值(zhi)(zhi)(zhi)x值(zhi)(zhi)(zhi)位(wei)置(zhi)在(zai)B列(lie)27行(xing)，則在(zai)平均值(zhi)(zhi)(zhi)x列(lie)輸(shu)人“=$B$27",x±s列(lie)輸(shu)人“=$B$27±$D$27"，以(yi)此類推，然后將(jiang)鼠標(biao)光(guang)標(biao)移(yi)動至變為黑色(se)十字(zi)下拉至覆蓋(gai)各日(ri)期列(lie)。
3.質控圖繪制
質控(kong)數據(ju)表建成(cheng)后(hou)，即可進行質控(kong)圖的繪制。
3.1折線圖的繪制(zhi)
選(xuan)定(ding)質控數(shu)據(ju)和對應的日期(qi)，如表(biao)1中A2:B26全部選(xuan)擇，單擊菜單中的“圖表(biao)向(xiang)導(dao)”，選(xuan)擇散點(dian)(dian)(dian)圖中“折線散點(dian)(dian)(dian)圖”，單擊下(xia)一步(bu);在(zai)此(ci)處單擊圖表(biao)向(xiang)導(dao)左上(shang)角的“系(xi)列”標(biao)簽，然后點(dian)(dian)(dian)“添加”進行(xing)系(xi)列添加，添加系(xi)列后點(dian)(dian)(dian)擊“分類(lei)X軸(zhou)標(biao)志(zhi)(T):”右側長(chang)方形(xing)最右側的按(an)鈕(niu)(niu)，選(xuan)定(ding)日期(qi)欄中的所有(you)日期(qi)，包括(kuo)上(shang)下(xia)延伸的日期(qi)，點(dian)(dian)(dian)擊“值(V):”右側長(chang)方形(xing)最右側的按(an)鈕(niu)(niu)選(xuan)定(ding)數(shu)據(ju)，如表(biao)1中選(xuan)定(ding)H2:H26。再點(dian)(dian)(dian)“添加”按(an)鈕(niu)(niu)，將控制(zhi)限、警戒(jie)限和中心線按(an)
的(de)順序(xu)進行逐次添加(jia)，同時將相應(ying)的(de)系(xi)列名(ming)稱更(geng)改成相對應(ying)的(de)名(ming)稱，相對應(ying)系(xi)列名(ming)稱分別為“質控數據、上控制(zhi)(zhi)限(xian)(UCL)、上警(jing)戒限(xian)(UWL)、中心線(xian)(CL)、下(xia)警(jing)戒限(xian)(LWL)、下(xia)控制(zhi)(zhi)限(xian)(LCL)"，添加(jia)完畢后單(dan)擊下(xia)一步;在(zai)“標題”標簽(qian)中分別為“圖(tu)表標題”、“分類(X)軸”、“分類(Y)軸”命名(ming)，在(zai)“網格線(xian)”標簽(qian)中將“數值(Y)軸主(zhu)要網格線(xian)”點成“無”;單(dan)擊完成。見圖(tu)1。
3.2質控圖修(xiu)飾
圖(tu)1的折(zhe)(zhe)線圖(tu)基本上是質(zhi)控圖(tu)的雛形(xing)了，此(ci)時很(hen)有必要對折(zhe)(zhe)線圖(tu)進(jin)行修飾，主(zhu)要分(fen)為以下幾步:
3.2.1繪圖區的修飾
單擊繪(hui)(hui)圖(tu)區(qu)右(you)鍵(jian)，選擇“繪(hui)(hui)圖(tu)區(qu)格式”對繪(hui)(hui)圖(tu)區(qu)的“邊框”和“區(qu)域”均選擇“無”。
3.2.2對坐(zuo)標軸的修(xiu)飾(shi)
選(xuan)定Y軸(zhou)(zhou)(zhou)，對(dui)“坐(zuo)(zuo)標(biao)軸(zhou)(zhou)(zhou)格(ge)式(shi)”選(xuan)擇“刻度”標(biao)簽(qian)(qian)進(jin)(jin)行(xing)編(bian)(bian)輯，對(dui)坐(zuo)(zuo)標(biao)軸(zhou)(zhou)(zhou)最大(da)值(zhi)(zhi)(zhi)(zhi)和最小(xiao)(xiao)值(zhi)(zhi)(zhi)(zhi)進(jin)(jin)行(xing)調(diao)整，盡(jin)量將上(下(xia))控制(zhi)限、上(下(xia))警戒限和中心(xin)線(xian)平均分配于(yu)縱列，且在“數(shu)(shu)字”標(biao)簽(qian)(qian)對(dui)小(xiao)(xiao)數(shu)(shu)點位(wei)數(shu)(shu)調(diao)整為質控數(shu)(shu)據的(de)有效位(wei)數(shu)(shu);再選(xuan)定X軸(zhou)(zhou)(zhou)，對(dui)“坐(zuo)(zuo)標(biao)軸(zhou)(zhou)(zhou)格(ge)式(shi)”選(xuan)擇“刻度”標(biao)簽(qian)(qian)進(jin)(jin)行(xing)編(bian)(bian)輯，最小(xiao)(xiao)值(zhi)(zhi)(zhi)(zhi)和最大(da)值(zhi)(zhi)(zhi)(zhi)更(geng)改為上下(xia)延(yan)伸日期(qi)(qi)。如果X軸(zhou)(zhou)(zhou)的(de)日期(qi)(qi)無法直接(jie)進(jin)(jin)行(xing)最大(da)、最小(xiao)(xiao)值(zhi)(zhi)(zhi)(zhi)的(de)更(geng)改，就需要將所選(xuan)最小(xiao)(xiao)值(zhi)(zhi)(zhi)(zhi)的(de)日期(qi)(qi)和最大(da)值(zhi)(zhi)(zhi)(zhi)的(de)日期(qi)(qi)選(xuan)定單(dan)擊(ji)右鍵(jian)，選(xuan)擇“設(she)置單(dan)元(yuan)格(ge)格(ge)式(shi)”，在數(shu)(shu)字標(biao)簽(qian)(qian)下(xia)進(jin)(jin)行(xing)常規顯示(shi)出的(de)數(shu)(shu)據填(tian)入(ru)即可(ke)，如2011-6-5的(de)常規顯示(shi)為40699
3.2.3對控制(zhi)限、警戒限和中(zhong)心線的(de)修飾(shi)
分別選中(zhong)(zhong)上(shang)(下(xia))控制限(xian)、上(shang)(下(xia))警(jing)戒限(xian)和中(zhong)(zhong)心(xin)線，單擊右鍵，選擇“數(shu)據系列格式(shi)”中(zhong)(zhong)的(de)“數(shu)據標(biao)記”選為“無(wu)”，前(qian)景色和背景色選為“無(wu)顏色”;根據需要對各自“線形”進行編(bian)輯顏色和樣式(shi)，讓控制限(xian)、警(jing)戒限(xian)和中(zhong)(zhong)心(xin)線成為一(yi)條(tiao)直線或有規律的(de)虛線即可(ke)。
制成后(hou)的圖表無論是添加(jia)或刪減數(shu)據(ju)，整(zheng)個(ge)質(zhi)控(kong)圖都會跟著數(shu)據(ju)的變化而(er)改變，同時(shi)也可以對(dui)整(zheng)個(ge)質(zhi)控(kong)表進行(xing)復制作為模板用于其(qi)他檢測項目(mu)質(zhi)控(kong)圖的繪制。
