Excel在海洋生物资料处理中的应用——以种名录统计为例

夏炳训　宁璇璇　宫钦周　纪殿胜　王 芹

Excel在海洋生物资料处理中的应用——以种名录统计为例

夏炳训宁璇璇宫钦周纪殿胜王 芹

（国家海洋局烟台海洋环境监测中心站，山东 烟台 264006）

以实例方式介绍了利用Excel软件统计海洋生物种名录的方法。该方法能够简单快捷、准确可靠地获取生物种名录，具有很强的实用性。

Excel；海洋生物；种名录；统计

1　引言

近年来，随着人们对海洋生态环境的日益重视，海洋生物调查已成为海洋环境调查中的一项重要内容。众所周知，在环境监测领域，数据处理是监测工作必不可少的一个环节。因此，海洋生物资料的统计分析就成为监测人员必须面对的一项工作，然而由于生物资料需要统计的内容繁多、过程复杂，一直是困扰基层海洋工作者的一个难题。譬如，在实际工作中，经常需要对调查海域的种名录进行统计，传统的方法多为人工逐一核对，剔除各站位间重复出现的物种，最终形成整个调查海域的种名录。这项工作在物种种类少的情况下还相对容易完成，但如果在调查站位多、物种丰富情况下，采用人工挑选的方法不仅费力耗时，而且容易出错。因此，如何使海洋生物数据处理工作变得快速、有效，是一个亟需解决的问题。Excel是一款具有强大数据统计分析功能的应用软件，并且操作简单、易学易懂，已在环境监测领域各个方面得到了广泛应用［1-21］。鉴于此，本文提出利用Excel统计海洋生物种名录的方法，供同行借鉴和参考。

2　种名录统计工作介绍

种名录统计的目的就是要提取所有调查站位间不重复的、唯一的种类名称，也就是统计出整个调查海域所出现的生物种类。例如，在莱州湾海域生态环境调查中，笔者布设了10个调查站位，采集了10个站位的浮游植物样品，每个站位鉴定出的浮游植物种类从20～30种不等，并且不同站位相互间的种类有相同的，也有不同的。为了了解整个调查海域的生物种类状况，笔者就需要剔除不同站位间重复出现的种类，只保留没有重复出现的种类，从而形成整个调查海域的种名录。

3　功能的实现

3.1高级筛选法

3.1.1数据准备

本文以浮游植物调查数据为例。在进行种名录提取之前，首先要将各调查站位的原始数据全部汇总到一起，数据的汇总不必人工逐个录入，可以运用文献［21］提供的方法来获取原始记录，然后通过复制粘贴来进行汇总。本文为了便于讲解，笔者假设只有3个站位20个数据，数据区域为B3：D22，如图1所示。

图1　数据汇总整理图表

3.1.2操作程序

点击“数据”→“筛选”→“高级筛选”打开高级筛选对话框（图2）。在“方式”中选择“将筛选结果复制到其他位置”，以免覆盖原有数据；在“列表区域”中选择要筛选的数据区域（$B$2：$D$22）；“条件区域”可不填；在“复制到”框中选取用于存放筛选结果的第一个单元格地址（$F$2）；勾选“选择不重复的记录”复选框，单击确定。筛选结果如图3所示。

图2　高级筛选对话框

图3　筛选结果

3.2公式法

3.2.1数据准备

公式法的数据准备与筛选法（3.1.1）相同，这里不再赘述。

3.2.2公式编制

在数据汇总工作表的F～H列输入公式，各单元格中的公式如图4所示。

图4　公式的编制

图4中有关公式的编制过程如下：

（1）在F3单元格中输入=INDEX（B：B，MIN（IF（$C$3：$C$ 22=G3，ROW（$C$3：$C$22），4^8））），按“Shift+Ctrl+Enter”组合键完成公式的输入，下拉复制公式，直到出现#N/A错误值。

（2）在G3单元格中输入=INDEX（C$3：C$22，MATCH（0，COUNTIF（G$2：G2，C$3：C$22），0）），然后同时按“Shift+Ctrl+Ent er”组合键完成公式的输入，下拉复制公式，直到出现#N/A错误值。

（3）将G列中的公式向右拖拉复制到H列中，至此完成所有公式的编制。

3.2.3公式解析

上述公式主要调用了INDEX、MATCH、COUNTIF、MIN、IF、ROW等6个函数，其单个函数的基本语法含义和功能比较容易理解，请参考excel帮助，在此不进行详细叙述。本文主要对公式的构建过程进行一下解析。

（1）=INDEX（B：B，MIN（IF（$C$3：$C$22=G3，ROW（$C$3：$C$22），4^8））），这是F3单元格中的一个公式。其中，IF（$C$ 3：$C$22=G3，ROW（$C$3：$C$22），4^8）是一个简单的条件函数，其含义为：当$C$3：$C$22区域中有等于G3单元格中的值时，返回其所在的行号，否则返回4^8，即65536。这里写成4^8是因为Excel 2003最大行数为65536（4^8）行，考虑到条件区域可能无限大，为了防止出现错误，才写成4^8，也可以写成其他比较大的数，但一定要大于数据源的行数，否则将会出现错误。

本例中，G3单元格为“星脐圆筛藻”，$C$3：$C$22区域中只有C3和C10单元格的值为“星脐圆筛藻”，因此IF（$C$3：$C $22=G3，ROW（$C$3：$C$22），4^8）返回的结果为：｛3；65536；655 36；65536；65536；65536；65536；10；65536；65536；65536；65536；655 36；65536；65536；65536；65536；65536；65536；65536｝）），然后利用MIN函数提取上述数组中的最小值“3”，最终公式变成=INDE X（B：B，3），即提取B列中第3行的值（硅藻门）。当向下复制公式时，$C$3：$C$22=G3条件等式中G3将依次变为G4、G5、G6、……，这样将数据源$C$3：$C$22中等于G4、G5、G6、……的行号提取出来，然后由INDEX函数提取B列中相对应行的值。

（2）=INDEX（C$3：C$22，MATCH（0，COUNTIF（G$2：G2，C $3：C$22），0）），这是G3单元格中的一个公式，调用了INDEX、MATCH、COUNTIF 3个函数，相互嵌套，是一个看起来比较复杂的公式。公式的核心部分是COUNTIF（G$2：G2，C$3：C$ 22），其中C$3：C$22为数据源，G$2：G2为结果公式起始位置（G3单元格）的上一位置，需要根据公式的实际位置进行调整，并且G$2：G2是动态变化的，当公式运行到G4单元格时变为G$2：G3，运行到G5单元格时变为G$2：G4，……。

COUNTIF（G$2：G2，C$3：C$22）用于计算C$3：C$22中每一个元素在G$2：G2中出现的次数，相当于：COUNTIF（G$2：G2，C3）、COUNTIF（G$2：G2，C4）、……、COUNTIF（G$2：G2，C22）。因为是数组公式，所以该公式返回的结果是一个与C$3：C$22大小相同的数组。然后利用MATCH（0，COUNTIF（G$2：G2，C$ 3：C$22），0）去查找该数组中第一个“0”的位置，即在G$2：G2中没出现的元素的位置。

在本例中，由于G2=“中文名”，而C$3：C$22中不存在“中文名”，因此COUNTIF（G$2：G2，C$3：C$22）结果是：｛0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0｝，即公式相当于：MATCH（0，｛0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0｝，0），返回结果为1。由此，整个公式变为：=INDEX（C$3：C$22，1），即提取C$3：C$22第1个单元格C3中的值（星脐圆筛藻）。当G3单元格中的公式向下复制到G4单元格时，公式COUNTIF（G$2：G2，C$3：C$22）中的G$2：G2发生了变化，变成了G$2：G3，由于G3单元格中的值为“星脐圆筛藻”，而C3和C10单元格中的值也为“星脐圆筛藻”，因此COUNTIF（G$2：G3，C$3：C$22）的计算结果为：｛1；0；0；0；0；0；0；1；0；0；0；0；0；0；0；0；0；0；0；0｝。显然MATCH（0，COUNTI F（G$2：G3，C$3：C$22），0）的查找结果为2，然后用INDEX函数提取C$3：C$22第2个单元格C4中的值（柔弱根管藻）。依此类推，通过采用COUNTIF函数计算数据源中的每个值是否在公式所在单元格前面的区域出现过，然后通过MATCH函数查找没有出现过的值所在的位置，最后利用INDEX函数完成不重复值的提取，从而生成调查海区的种名录。

4　讨论

4.1筛选法和公式法的比较

高级筛选法操作简单实用，易于掌握，不便之处是当工作表中的数据源更新后，筛选结果不会自动更新，每次都要重复执行高级筛选的操作步骤，自动化程度不高。适合在资料统计频次低（偶尔统计一、两次资料）或无法编制公式的情况下使用。

公式法可一次性建立公式永久使用，以后只需更改数据源即可，不用像筛选法那样每次都要进行重复的操作，可达到一劳永逸、事倍功半的效果。但公式法要达到运用自如的程度，需要对公式进行充分地理解和掌握，否则一旦公式损坏，或是在缺少公式模板的场合下，使用起来可能就不会得心应手。公式法适合在资料统计频率高的情况下使用，这样可以大大减少工作量，提高工作效率。

4.2使用中应注意的问题

（1）不论高级筛选法还是公式法的提取结果，“类群”一栏都不是分门别类排序的，如果要将同一类群的放在一起，可进一步地采用“自动筛选”，选择“升序”或“降序”排列即可。

（2）使用高级筛选法时，选择“列表区域”时一定要包括数据列标题在内的单元格区域，不能只选择包含数据的单元格区域，否则筛选结果会出现错误。

（3）本文中的公式均为数组公式，公式录入完毕后，必须同时按“Shift+Ctrl+Enter”组合键完成公式的输入，否则公式将出现错误。

5　结语

相比人工挑选而言，运用Excel统计海洋生物种名录，不论是筛选法还是公式法，都省去了大量繁杂、机械的人工统计过程，把分析人员从繁琐耗时的资料统计中解放出来，极大地提高了工作效率。实际应用表明，本文提出的方法简单实用、准确可靠、自动化程度高、通用性强，可广泛应用于浮游植物、浮游动物、底栖生物等海洋生物的种名录统计中，具有较高的实用价值和推广价值。

［1］ 丘国华.EXCEL在环境监测数据管理中的应用［J］.环境监测管理与技术，1998，10（2）：43-43.

［2］ 李刚.Excel数组公式在环境统计中的应用［J］.中国环境监测，2006，22（6）：54-56.

［3］ 向晓钧，李杨，王鸿.Excel在处理环境监测数据中的应用［J］.北方环境，2011，23（6）：160-162.

［4］ 林淑珠.Excel在环境监测计算中的应用［J］.福建分析测试，2004，13（2）：1976-1978.

［5］ 王仙红，朱文钦.水质资料整编工具技巧探讨—运用Excel2007完成水质资料整编［J］.甘肃科技，2011，27（11）： 22-23，32.

［6］ 庞文博，黄朝英.浅谈EXCEL在鉴定工作的数值自动修约中的应用［J］.大众科技，2011，（8）：16-17.

［7］ 代堂刚.Excel函数在水质特征值年统计表中的运用［J］.水资源研究，2009，30（2）：6-7.

［8］ 高晓林.Excel在实验数据回归分析中的应用［J］.实验技术与管理，2005，22（5）：28-31.

［9］ 许涛，朱文昌.环境监测数值修约在Excel中的实现［J］.四川环境，2008，27（1）：42-45，60.

［10］ 杨湘山，吕焱，李景舜，等.Excel软件在环境监测质量控制中的应用［J］.四川环境，2005，24（3）：101-103.

［11］ 徐建平.Excel在确定监测方法重复性与再现性中的运用［J］.环境监测管理与技术，2010，22（1）：54-58.

［12］ 付友宝，杨利，遇方彪.MicrosoftExcel在环境监测数据处理方面的应用-Grubbs检验离群数据［J］.四川环境，2004，23（6）：107-109.

［13］ 陈军，赵安洁，何蓉.利用Excel快速建立t检验模板［J］.光谱实验室，2006，23（5）：1096-1098.

［14］ 邱训平.EXCEL在水质现状评价中的应用与技巧［J］.水资源研究，2011，32（3）：33-34，40.

［15］ 白爱民.湖库富营养化状态的快速评价［J］.环境科学导刊，2014，33（1）： 80-83.

［16］ 房玮.EXCEL软件在污染物扩散模式计算中的应用［J］.广州环境科学，2001，16（3）：43-45.

［17］ 许涛，穆卫亮，袁荣根.利用Excel绘制监测数据百分位数矩形图［J］.四川环境，2007，26（6）：112-115.

［18］ 董瑞.利用EXCEL制作质量控制图［J］.四川环境，2001，20（1）： 67-69.

［19］ 孔凡洲，于仁成，徐子钧，等.应用Excel软件计算生物多样性指数［J］.海洋科学， 2012，36（4）： 57-62.

［20］ 刘玉龙，王苏明，刘菲，等.EXCEL在气相色谱-质谱检测中的应用［J］.岩矿测试，2011，30（1）：116-120.

［21］ 夏炳训，宁璇璇，陈权文，等.Excel在海洋浮游植物数据自动化处理中的应用研究［J］.四川环境，2015，34（3）：43-48.

Application of Excel in data processing for marine organism——Taking the statistics of species list as an example

A statistical method of marine species list using excel software is introduced by an example. With this method， a species list can be easily and accurately obtained. Hence， it will be strongly recommended and widely applied in practice.

Excel； marine organism； species list； statistics

TP317.3；Q179.1

A

1008-1151（2016）02-0053-03

2016-01-12

海洋公益性行业科研专项（201305003）。

夏炳训（1970－），男，山东烟台人，国家海洋局烟台海洋环境监测中心站高级工程师，从事海洋环境监测与评价工作。