環境空氣質量評價中利用Excel進行百分位數的計算及百分位數矩形圖的繪制

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

：Excel??百分位數??環境空氣質量??制圖

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

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

百分位數是用于表示空氣污染物有關統計指標分布狀態的一種較常用的方法。環境質量報告書中一般給出第5、第10、第25、第50（中位數）、第75、第90和第95的百分位數的數據，以及平均值。

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

?

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

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

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

制圖數據模板由初始數據表和X、Y坐標數據表兩部分構成，在Excel工作簿中建立第六個工作表為制圖數據表。

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

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

的X、Y坐標值，具體引用如下。

2016年組數據的X坐標一欄中，在單元格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年組數據X坐標全部設置完畢，2017—2020這4組數據的X坐標分別取前一組數據的X坐標值加一個圖間距，在單元格E16中輸入“=C16+$C$12”，在單元格G16中輸入“=E16+$C$12”，在單元格I16中輸入“=G16+$C$12”，在單元格K16中輸入“=I16+$C$12”，然后用單元格拷貝的方式將相同的公式輸入其他相應的單元格。

五組數據的Y坐標值均利用LOOKUP工作表函數從初始數據表單中自動提取，在單元格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）”，然后用單元格拷貝的方式將相同的公式輸入其他相應單元格內。至此，百分位數矩形圖的制圖數據表模板也就形成了。

在工作簿中再建立第七個工作表“百分位數矩形圖”，按以下順序進行操作。

（1）插入→帶直線的散點圖。

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

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

（3）最后添加系列“平均值”，Y值選定工作表制圖數據表中區域C10：G10的圖形，X值不填寫，留空白即可。并對其進行設置，將數據標志選項中選擇“內置”，選擇“×”，線條寬度選擇“2.25磅”，并添加數據標簽。

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

至此，一個5組數據的百分位數矩形圖的基本模板已經完成。

在矩形圖中繼續添加數據系列，可以更直觀地比較。例如：本文中，可以添加PM的日均值標準、年均值標準，以及24h平均第98百分位數對某市的PM進行年平均，此圖常用于5年環境質量報告書中環境空氣質量的評價，具有一定的實用價值。

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

[1] 環境保護部.環境空氣質量評價技術規范（試行）[M].北京：環境保護部，2013.

[2] 環境保護部.環境質量報告書編寫技術規范[M].北京：環境保護部，2012.

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

[4] 李日升.某建設項目區域環境現狀調查與評價[J].科技創新與應用，2021，11（35）：48-51.

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

[6]毛光瑞，李宛真，汪杰.商洛市“十三五”環境空氣質量評價[J].黑龍江環境通報，2020，33（2）：4-6.

[7]查木哈.赤峰市取暖期環境空氣質量評價[J].科技創新與應用，2018（27）：68-69.

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

[9]付浩，付飛娥.利用Excel函數快速計算環境空氣監測數據[J].廣東化工，2019，46（10）：132，127.

吳彬（1986—），男，本科，助理工程師，研究方向為環境監測。