环境空气质量评价中利用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—），男，本科，助理工程师，研究方向为环境监测。