環(huán)境空氣質(zhì)量評價(jià)中利用Excel進(jìn)行百分位數(shù)的計(jì)算及百分位數(shù)矩形圖的繪制

：通過百分位數(shù)矩形圖的繪制，可以直觀了解某項(xiàng)指標(biāo)在某一時(shí)間段中的污染物分布特征，并可以對污染變化趨勢進(jìn)行比較、分析。本文介紹了在Excel中制作百分位數(shù)矩形圖模板，只需輸入原始數(shù)據(jù)即可自動(dòng)生成百分位數(shù)矩形圖，實(shí)現(xiàn)數(shù)據(jù)與圖表的自動(dòng)鏈接，確保圖形的準(zhǔn)確。制成的模版可以反復(fù)使用，并且可以根據(jù)不同的情況靈活變換，可以提高工作效率，具有實(shí)用價(jià)值。

：Excel??百分位數(shù)??環(huán)境空氣質(zhì)量??制圖

The Calculation of Percentile and the Drawing of Percentile Rectangle Chart in Environmental Air Quality Assessment by Using Microsoft Excel

WU?Bin

Abstract： By drawing the percentile rectangle， we can intuitively understand a pollution distribution feature of a certain indicator in a specific time period， and we can compare and analyze the changing trends of pollution status.?This paper introduces the creation of percentile rectangular templates in Excel to automatically input the percentiles， to realize the automatic link between data and charts， to ensure the accuracy of the graph.?The made template can be used repeatedly， and can be flexibly transformed according to different situations， improve the work efficiency， has a certain practical value.

Key?Words： Excel; Percentile; Ambient air quality; Mapping

百分位數(shù)法是環(huán)境質(zhì)量分析中的一種常用方法，一般用于分析空氣中污染物的濃度和相關(guān)指標(biāo)的分布狀況，通過繪制百分位數(shù)圖，可以直觀了解某項(xiàng)指標(biāo)在某一時(shí)間段中的污染物分布特征，并可以對污染變化趨勢進(jìn)行比較、分析。本文介紹了利用Excel現(xiàn)有公式來計(jì)算各百分位數(shù)的濃度值，再將這些各百分位數(shù)的濃度值通過Excel中的簡單圖標(biāo)—X、Y散點(diǎn)圖來制作百分位數(shù)矩形圖，使用該方法能實(shí)現(xiàn)數(shù)據(jù)與圖表自動(dòng)鏈接。

百分位數(shù)是用于表示空氣污染物有關(guān)統(tǒng)計(jì)指標(biāo)分布狀態(tài)的一種較常用的方法。環(huán)境質(zhì)量報(bào)告書中一般給出第5、第10、第25、第50（中位數(shù)）、第75、第90和第95的百分位數(shù)的數(shù)據(jù)，以及平均值。

含量為的數(shù)據(jù)例按大小順序排列好，則第百分位的值等于個(gè)數(shù)的值，中位數(shù)即為第50個(gè)百分位數(shù)。例數(shù)為偶數(shù)，取兩個(gè)中間值的算術(shù)平均值作中位數(shù)。當(dāng)不為整數(shù)時(shí)，用線性插入法計(jì)算出第百分位數(shù)的值。

?

Excel內(nèi)置工作表函數(shù)PERCENTILE可以用于計(jì)算一組數(shù)據(jù)的百分位數(shù)的數(shù)值，其計(jì)算結(jié)果與1.2中公式計(jì)算的結(jié)果是完全一致的，其表達(dá)式為：PERCENTILE（array，），array定義為相對位置的數(shù)據(jù)區(qū)域，為0到1之間的百分點(diǎn)值（即值）。例：計(jì)算某市2020年P(guān)M24h平均第95百分位數(shù)，將2020年1月1日至12月31日的PM24h平均值分別輸入Excel表的A1至A366的單元格內(nèi)，再使用公式PERCENTILE（A1：A366，0.95），即求取A1：A366單元格區(qū)域內(nèi)數(shù)據(jù)的第95百分位數(shù)，所得值即為該市2020年P(guān)M24h平均第95百分位數(shù)。用此方法可以求得百分位矩形圖中的第5、10、25、50、75、90、95百分位數(shù)。

采用分段組合的原理制作百分位數(shù)矩形圖，即將一個(gè)百分位數(shù)矩形圖分解為5個(gè)圖段，用工具Excel內(nèi)置的X、Y散點(diǎn)圖（帶直線的散點(diǎn)圖）分段制圖，再將制成的分段圖形相互銜接形成一個(gè)完整美觀的百分位數(shù)矩形圖。

首先，新建一個(gè)Excel工作簿，在此工作簿先建立5個(gè)工作表。第一至第五個(gè)工作表分別利用2016—2020年P(guān)M24h平均的原始數(shù)據(jù)（按照《數(shù)值修約規(guī)則與極限數(shù)值的表示與判定》（GB/T 8170-2008）修約后），通過公式自動(dòng)生成第5、10、25、50、75、90、95百分位數(shù)的各數(shù)值。以第一個(gè)工作表2016為例：在第一個(gè)工作表2016的單元格A1至A366內(nèi)輸入2016年1月1日至12月31日的原始數(shù)據(jù)，然后再將此數(shù)據(jù)分別復(fù)制至單元格B1至B366、C1至C366、D1至D366、E1至E366、F1至F366、G1至G366，共7組相同的原數(shù)數(shù)據(jù)。在單元格A376至G367分別做標(biāo)記百分之5、百分之10、百分之25、百分之50、百分之75、百分之90、百分之95，分別代表每一列生成的百分位數(shù)。最后再在單元格A368至G368分別輸入公式=PERCENTILE（A1：A366，a）（a=0.05、0.1、0.25、0.5、0.75、0.9、0.95）至此，2016年P(guān)M24h平均第5、第10、第25、第50、第75、第90、第95百分位數(shù)分別生成在單元格A368至G368。依照工作表2016，工作表2017—2020也同理生成各年份的第5、第10、第25、第50、第75、第90、第95百分位數(shù)。

制圖數(shù)據(jù)模板由初始數(shù)據(jù)表和X、Y坐標(biāo)數(shù)據(jù)表兩部分構(gòu)成，在Excel工作簿中建立第六個(gè)工作表為制圖數(shù)據(jù)表。

在此工作表的A1：G12單元格內(nèi)建立原始數(shù)據(jù)表，在單元格A13：L42內(nèi)建立作圖數(shù)據(jù)表單。其中，單元格C2：G2為時(shí)段代碼，本文舉例為2016—2020年;單元格C3：G10為第5、10、25、50、75、90、95百分位對應(yīng)的數(shù)值和平均值;單元格A11：G12為圖形形狀控制參數(shù)，用于調(diào)整分段圖形的寬度和各組數(shù)據(jù)百分位數(shù)矩形圖的間距，可以根據(jù)需要進(jìn)行調(diào)整，其中，單元格E11數(shù)值小于單元格D11數(shù)值，單元格D11數(shù)值小于單元格C11數(shù)值，單元格C11數(shù)值小于1。

單元格C16：L42為5組數(shù)據(jù)（2016—2020年）百分位數(shù)矩形圖各分段

的X、Y坐標(biāo)值，具體引用如下。

2016年組數(shù)據(jù)的X坐標(biāo)一欄中，在單元格C16、C17、C20、C36、C37、C40中輸入“=C11”;在單元格C21、C22、C25、C31、C32、C35中輸入“=D11”;在單元格C26、C27、C30、C41中輸入“=E11”;在單元格C18、C19、C38、C39中輸入“=2- C11”;在單元格C23、C24、C33、C34中輸入“=2-D11”;在單元格C28、C29中輸入“=2-E11”，至此2016年組數(shù)據(jù)X坐標(biāo)全部設(shè)置完畢，2017—2020這4組數(shù)據(jù)的X坐標(biāo)分別取前一組數(shù)據(jù)的X坐標(biāo)值加一個(gè)圖間距，在單元格E16中輸入“=C16+$C$12”，在單元格G16中輸入“=E16+$C$12”，在單元格I16中輸入“=G16+$C$12”，在單元格K16中輸入“=I16+$C$12”，然后用單元格拷貝的方式將相同的公式輸入其他相應(yīng)的單元格。

五組數(shù)據(jù)的Y坐標(biāo)值均利用LOOKUP工作表函數(shù)從初始數(shù)據(jù)表單中自動(dòng)提取，在單元格D16中輸入“=LOOKUP（B16，$B$3：$C$9）”;在單元格F16中輸入“=LOOKUP（B16，$B$3：$D$9）”;在單元格H16中輸入“=LOOKUP（B16，$B$3：$E$9）”;在單元格J16中輸入“=LOOKUP（B16，$B$3：$F$9）”;在單元格L16中輸入“=LOOKUP（B16，$B$3：$G$9）”，然后用單元格拷貝的方式將相同的公式輸入其他相應(yīng)單元格內(nèi)。至此，百分位數(shù)矩形圖的制圖數(shù)據(jù)表模板也就形成了。

在工作簿中再建立第七個(gè)工作表“百分位數(shù)矩形圖”，按以下順序進(jìn)行操作。

（1）插入→帶直線的散點(diǎn)圖。

（2）右擊出現(xiàn)的圖標(biāo)源對話框單擊“選擇數(shù)據(jù)”，單擊“添加”，系列名稱輸入“2016第一段”，在X、Y軸系列值分別選定工作表制圖數(shù)據(jù)表中區(qū)域C16：C20和D16：D20，再用相同方法添加2016第二段至第六段。

用以上方法，繼續(xù)添加2017年的第一段至第六段，作出2017年的PM24h平均百分位數(shù)矩形圖;同樣再做出2018—2020年的百分位數(shù)矩形圖。為了美觀，將各系列選項(xiàng)中線條顏色設(shè)置為“黑色”，寬度為“1磅”。

（3）最后添加系列“平均值”，Y值選定工作表制圖數(shù)據(jù)表中區(qū)域C10：G10的圖形，X值不填寫，留空白即可。并對其進(jìn)行設(shè)置，將數(shù)據(jù)標(biāo)志選項(xiàng)中選擇“內(nèi)置”，選擇“×”，線條寬度選擇“2.25磅”，并添加數(shù)據(jù)標(biāo)簽。

（4）利用插入→形狀的功能用“2016年”“2017年”“2018年”“2019年”“2020年”及空白分別將X軸的“1”“2”“3”“4”“5”“6”替換，添加坐標(biāo)軸標(biāo)題（刪除X軸標(biāo)題，Y軸標(biāo)題命名為細(xì)顆粒物：ug/m）。

至此，一個(gè)5組數(shù)據(jù)的百分位數(shù)矩形圖的基本模板已經(jīng)完成。

在矩形圖中繼續(xù)添加數(shù)據(jù)系列，可以更直觀地比較。例如：本文中，可以添加PM的日均值標(biāo)準(zhǔn)、年均值標(biāo)準(zhǔn)，以及24h平均第98百分位數(shù)對某市的PM進(jìn)行年平均，此圖常用于5年環(huán)境質(zhì)量報(bào)告書中環(huán)境空氣質(zhì)量的評價(jià)，具有一定的實(shí)用價(jià)值。

上述利用Excel的表格、內(nèi)置函數(shù)和一些繪圖技巧，通過原始數(shù)據(jù)直接生成百分位數(shù)矩形圖，具有一定的實(shí)用價(jià)值，制成的模板可以反復(fù)使用，并且可以根據(jù)不同的情況靈活變換，提高了工作效率。

[1] 環(huán)境保護(hù)部.環(huán)境空氣質(zhì)量評價(jià)技術(shù)規(guī)范（試行）[M].北京：環(huán)境保護(hù)部，2013.

[2] 環(huán)境保護(hù)部.環(huán)境質(zhì)量報(bào)告書編寫技術(shù)規(guī)范[M].北京：環(huán)境保護(hù)部，2012.

[3] （日）藤井直彌，大山啓介，著.Excel最強(qiáng)教科書[M].（中）王娜，李利，祁芳芳，譯.北京：中國青年出版社，2019.

[4] 李日升.某建設(shè)項(xiàng)目區(qū)域環(huán)境現(xiàn)狀調(diào)查與評價(jià)[J].科技創(chuàng)新與應(yīng)用，2021，11（35）：48-51.

[5] 謝新宇，閆妍.2017～2019年秦皇島市昌黎縣空氣質(zhì)量評價(jià)與分析[J].河北科技師范學(xué)院學(xué)報(bào)，2021，35（2）：72-77.

[6]毛光瑞，李宛真，汪杰.商洛市“十三五”環(huán)境空氣質(zhì)量評價(jià)[J].黑龍江環(huán)境通報(bào)，2020，33（2）：4-6.

[7]查木哈.赤峰市取暖期環(huán)境空氣質(zhì)量評價(jià)[J].科技創(chuàng)新與應(yīng)用，2018（27）：68-69.

[8]潘碧靈，周國治，尤翔宇，等.環(huán)境空氣質(zhì)量評價(jià)方法改進(jìn)研究[J].中國環(huán)境管理，2020，12（4）：13-19.

[9]付浩，付飛娥.利用Excel函數(shù)快速計(jì)算環(huán)境空氣監(jiān)測數(shù)據(jù)[J].廣東化工，2019，46（10）：132，127.

吳彬（1986—），男，本科，助理工程師，研究方向?yàn)榄h(huán)境監(jiān)測。