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