夏炳訓(xùn)　寧璇璇　宮欽周　紀(jì)殿勝　王 芹

Excel在海洋生物資料處理中的應(yīng)用——以種名錄統(tǒng)計為例

夏炳訓(xùn)寧璇璇宮欽周紀(jì)殿勝王 芹

（國家海洋局煙臺海洋環(huán)境監(jiān)測中心站，山東 煙臺 264006）

以實例方式介紹了利用Excel軟件統(tǒng)計海洋生物種名錄的方法。該方法能夠簡單快捷、準(zhǔn)確可靠地獲取生物種名錄，具有很強(qiáng)的實用性。

Excel；海洋生物；種名錄；統(tǒng)計

1　引言

近年來，隨著人們對海洋生態(tài)環(huán)境的日益重視，海洋生物調(diào)查已成為海洋環(huán)境調(diào)查中的一項重要內(nèi)容。眾所周知，在環(huán)境監(jiān)測領(lǐng)域，數(shù)據(jù)處理是監(jiān)測工作必不可少的一個環(huán)節(jié)。因此，海洋生物資料的統(tǒng)計分析就成為監(jiān)測人員必須面對的一項工作，然而由于生物資料需要統(tǒng)計的內(nèi)容繁多、過程復(fù)雜，一直是困擾基層海洋工作者的一個難題。譬如，在實際工作中，經(jīng)常需要對調(diào)查海域的種名錄進(jìn)行統(tǒng)計，傳統(tǒng)的方法多為人工逐一核對，剔除各站位間重復(fù)出現(xiàn)的物種，最終形成整個調(diào)查海域的種名錄。這項工作在物種種類少的情況下還相對容易完成，但如果在調(diào)查站位多、物種豐富情況下，采用人工挑選的方法不僅費(fèi)力耗時，而且容易出錯。因此，如何使海洋生物數(shù)據(jù)處理工作變得快速、有效，是一個亟需解決的問題。Excel是一款具有強(qiáng)大數(shù)據(jù)統(tǒng)計分析功能的應(yīng)用軟件，并且操作簡單、易學(xué)易懂，已在環(huán)境監(jiān)測領(lǐng)域各個方面得到了廣泛應(yīng)用［1-21］。鑒于此，本文提出利用Excel統(tǒng)計海洋生物種名錄的方法，供同行借鑒和參考。

2　種名錄統(tǒng)計工作介紹

種名錄統(tǒng)計的目的就是要提取所有調(diào)查站位間不重復(fù)的、唯一的種類名稱，也就是統(tǒng)計出整個調(diào)查海域所出現(xiàn)的生物種類。例如，在萊州灣海域生態(tài)環(huán)境調(diào)查中，筆者布設(shè)了10個調(diào)查站位，采集了10個站位的浮游植物樣品，每個站位鑒定出的浮游植物種類從20～30種不等，并且不同站位相互間的種類有相同的，也有不同的。為了了解整個調(diào)查海域的生物種類狀況，筆者就需要剔除不同站位間重復(fù)出現(xiàn)的種類，只保留沒有重復(fù)出現(xiàn)的種類，從而形成整個調(diào)查海域的種名錄。

3　功能的實現(xiàn)

3.1高級篩選法

3.1.1數(shù)據(jù)準(zhǔn)備

本文以浮游植物調(diào)查數(shù)據(jù)為例。在進(jìn)行種名錄提取之前，首先要將各調(diào)查站位的原始數(shù)據(jù)全部匯總到一起，數(shù)據(jù)的匯總不必人工逐個錄入，可以運(yùn)用文獻(xiàn)［21］提供的方法來獲取原始記錄，然后通過復(fù)制粘貼來進(jìn)行匯總。本文為了便于講解，筆者假設(shè)只有3個站位20個數(shù)據(jù)，數(shù)據(jù)區(qū)域為B3：D22，如圖1所示。

圖1　數(shù)據(jù)匯總整理圖表

3.1.2操作程序

點(diǎn)擊“數(shù)據(jù)”→“篩選”→“高級篩選”打開高級篩選對話框（圖2）。在“方式”中選擇“將篩選結(jié)果復(fù)制到其他位置”，以免覆蓋原有數(shù)據(jù)；在“列表區(qū)域”中選擇要篩選的數(shù)據(jù)區(qū)域（$B$2：$D$22）；“條件區(qū)域”可不填；在“復(fù)制到”框中選取用于存放篩選結(jié)果的第一個單元格地址（$F$2）；勾選“選擇不重復(fù)的記錄”復(fù)選框，單擊確定。篩選結(jié)果如圖3所示。

圖2　高級篩選對話框

圖3　篩選結(jié)果

3.2公式法

3.2.1數(shù)據(jù)準(zhǔn)備

公式法的數(shù)據(jù)準(zhǔn)備與篩選法（3.1.1）相同，這里不再贅述。

3.2.2公式編制

在數(shù)據(jù)匯總工作表的F～H列輸入公式，各單元格中的公式如圖4所示。

圖4　公式的編制

圖4中有關(guān)公式的編制過程如下：

（1）在F3單元格中輸入=INDEX（B：B，MIN（IF（$C$3：$C$ 22=G3，ROW（$C$3：$C$22），4^8））），按“Shift+Ctrl+Enter”組合鍵完成公式的輸入，下拉復(fù)制公式，直到出現(xiàn)#N/A錯誤值。

（2）在G3單元格中輸入=INDEX（C$3：C$22，MATCH（0，COUNTIF（G$2：G2，C$3：C$22），0）），然后同時按“Shift+Ctrl+Ent er”組合鍵完成公式的輸入，下拉復(fù)制公式，直到出現(xiàn)#N/A錯誤值。

（3）將G列中的公式向右拖拉復(fù)制到H列中，至此完成所有公式的編制。

3.2.3公式解析

上述公式主要調(diào)用了INDEX、MATCH、COUNTIF、MIN、IF、ROW等6個函數(shù)，其單個函數(shù)的基本語法含義和功能比較容易理解，請參考excel幫助，在此不進(jìn)行詳細(xì)敘述。本文主要對公式的構(gòu)建過程進(jìn)行一下解析。

（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）是一個簡單的條件函數(shù)，其含義為：當(dāng)$C$3：$C$22區(qū)域中有等于G3單元格中的值時，返回其所在的行號，否則返回4^8，即65536。這里寫成4^8是因為Excel 2003最大行數(shù)為65536（4^8）行，考慮到條件區(qū)域可能無限大，為了防止出現(xiàn)錯誤，才寫成4^8，也可以寫成其他比較大的數(shù)，但一定要大于數(shù)據(jù)源的行數(shù)，否則將會出現(xiàn)錯誤。

本例中，G3單元格為“星臍圓篩藻”，$C$3：$C$22區(qū)域中只有C3和C10單元格的值為“星臍圓篩藻”，因此IF（$C$3：$C $22=G3，ROW（$C$3：$C$22），4^8）返回的結(jié)果為：｛3；65536；655 36；65536；65536；65536；65536；10；65536；65536；65536；65536；655 36；65536；65536；65536；65536；65536；65536；65536｝）），然后利用MIN函數(shù)提取上述數(shù)組中的最小值“3”，最終公式變成=INDE X（B：B，3），即提取B列中第3行的值（硅藻門）。當(dāng)向下復(fù)制公式時，$C$3：$C$22=G3條件等式中G3將依次變?yōu)镚4、G5、G6、……，這樣將數(shù)據(jù)源$C$3：$C$22中等于G4、G5、G6、……的行號提取出來，然后由INDEX函數(shù)提取B列中相對應(yīng)行的值。

（2）=INDEX（C$3：C$22，MATCH（0，COUNTIF（G$2：G2，C $3：C$22），0）），這是G3單元格中的一個公式，調(diào)用了INDEX、MATCH、COUNTIF 3個函數(shù)，相互嵌套，是一個看起來比較復(fù)雜的公式。公式的核心部分是COUNTIF（G$2：G2，C$3：C$ 22），其中C$3：C$22為數(shù)據(jù)源，G$2：G2為結(jié)果公式起始位置（G3單元格）的上一位置，需要根據(jù)公式的實際位置進(jìn)行調(diào)整，并且G$2：G2是動態(tài)變化的，當(dāng)公式運(yùn)行到G4單元格時變?yōu)镚$2：G3，運(yùn)行到G5單元格時變?yōu)镚$2：G4，……。

COUNTIF（G$2：G2，C$3：C$22）用于計算C$3：C$22中每一個元素在G$2：G2中出現(xiàn)的次數(shù)，相當(dāng)于：COUNTIF（G$2：G2，C3）、COUNTIF（G$2：G2，C4）、……、COUNTIF（G$2：G2，C22）。因為是數(shù)組公式，所以該公式返回的結(jié)果是一個與C$3：C$22大小相同的數(shù)組。然后利用MATCH（0，COUNTIF（G$2：G2，C$ 3：C$22），0）去查找該數(shù)組中第一個“0”的位置，即在G$2：G2中沒出現(xiàn)的元素的位置。

在本例中，由于G2=“中文名”，而C$3：C$22中不存在“中文名”，因此COUNTIF（G$2：G2，C$3：C$22）結(jié)果是：｛0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0｝，即公式相當(dāng)于：MATCH（0，｛0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0；0｝，0），返回結(jié)果為1。由此，整個公式變?yōu)椋?INDEX（C$3：C$22，1），即提取C$3：C$22第1個單元格C3中的值（星臍圓篩藻）。當(dāng)G3單元格中的公式向下復(fù)制到G4單元格時，公式COUNTIF（G$2：G2，C$3：C$22）中的G$2：G2發(fā)生了變化，變成了G$2：G3，由于G3單元格中的值為“星臍圓篩藻”，而C3和C10單元格中的值也為“星臍圓篩藻”，因此COUNTIF（G$2：G3，C$3：C$22）的計算結(jié)果為：｛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）的查找結(jié)果為2，然后用INDEX函數(shù)提取C$3：C$22第2個單元格C4中的值（柔弱根管藻）。依此類推，通過采用COUNTIF函數(shù)計算數(shù)據(jù)源中的每個值是否在公式所在單元格前面的區(qū)域出現(xiàn)過，然后通過MATCH函數(shù)查找沒有出現(xiàn)過的值所在的位置，最后利用INDEX函數(shù)完成不重復(fù)值的提取，從而生成調(diào)查海區(qū)的種名錄。

4　討論

4.1篩選法和公式法的比較

高級篩選法操作簡單實用，易于掌握，不便之處是當(dāng)工作表中的數(shù)據(jù)源更新后，篩選結(jié)果不會自動更新，每次都要重復(fù)執(zhí)行高級篩選的操作步驟，自動化程度不高。適合在資料統(tǒng)計頻次低（偶爾統(tǒng)計一、兩次資料）或無法編制公式的情況下使用。

公式法可一次性建立公式永久使用，以后只需更改數(shù)據(jù)源即可，不用像篩選法那樣每次都要進(jìn)行重復(fù)的操作，可達(dá)到一勞永逸、事倍功半的效果。但公式法要達(dá)到運(yùn)用自如的程度，需要對公式進(jìn)行充分地理解和掌握，否則一旦公式損壞，或是在缺少公式模板的場合下，使用起來可能就不會得心應(yīng)手。公式法適合在資料統(tǒng)計頻率高的情況下使用，這樣可以大大減少工作量，提高工作效率。

4.2使用中應(yīng)注意的問題

（1）不論高級篩選法還是公式法的提取結(jié)果，“類群”一欄都不是分門別類排序的，如果要將同一類群的放在一起，可進(jìn)一步地采用“自動篩選”，選擇“升序”或“降序”排列即可。

（2）使用高級篩選法時，選擇“列表區(qū)域”時一定要包括數(shù)據(jù)列標(biāo)題在內(nèi)的單元格區(qū)域，不能只選擇包含數(shù)據(jù)的單元格區(qū)域，否則篩選結(jié)果會出現(xiàn)錯誤。

（3）本文中的公式均為數(shù)組公式，公式錄入完畢后，必須同時按“Shift+Ctrl+Enter”組合鍵完成公式的輸入，否則公式將出現(xiàn)錯誤。

5　結(jié)語

相比人工挑選而言，運(yùn)用Excel統(tǒng)計海洋生物種名錄，不論是篩選法還是公式法，都省去了大量繁雜、機(jī)械的人工統(tǒng)計過程，把分析人員從繁瑣耗時的資料統(tǒng)計中解放出來，極大地提高了工作效率。實際應(yīng)用表明，本文提出的方法簡單實用、準(zhǔn)確可靠、自動化程度高、通用性強(qiáng)，可廣泛應(yīng)用于浮游植物、浮游動物、底棲生物等海洋生物的種名錄統(tǒng)計中，具有較高的實用價值和推廣價值。

［1］ 丘國華.EXCEL在環(huán)境監(jiān)測數(shù)據(jù)管理中的應(yīng)用［J］.環(huán)境監(jiān)測管理與技術(shù)，1998，10（2）：43-43.

［2］ 李剛.Excel數(shù)組公式在環(huán)境統(tǒng)計中的應(yīng)用［J］.中國環(huán)境監(jiān)測，2006，22（6）：54-56.

［3］ 向曉鈞，李楊，王鴻.Excel在處理環(huán)境監(jiān)測數(shù)據(jù)中的應(yīng)用［J］.北方環(huán)境，2011，23（6）：160-162.

［4］ 林淑珠.Excel在環(huán)境監(jiān)測計算中的應(yīng)用［J］.福建分析測試，2004，13（2）：1976-1978.

［5］ 王仙紅，朱文欽.水質(zhì)資料整編工具技巧探討—運(yùn)用Excel2007完成水質(zhì)資料整編［J］.甘肅科技，2011，27（11）： 22-23，32.

［6］ 龐文博，黃朝英.淺談EXCEL在鑒定工作的數(shù)值自動修約中的應(yīng)用［J］.大眾科技，2011，（8）：16-17.

［7］ 代堂剛.Excel函數(shù)在水質(zhì)特征值年統(tǒng)計表中的運(yùn)用［J］.水資源研究，2009，30（2）：6-7.

［8］ 高曉林.Excel在實驗數(shù)據(jù)回歸分析中的應(yīng)用［J］.實驗技術(shù)與管理，2005，22（5）：28-31.

［9］ 許濤，朱文昌.環(huán)境監(jiān)測數(shù)值修約在Excel中的實現(xiàn)［J］.四川環(huán)境，2008，27（1）：42-45，60.

［10］ 楊湘山，呂焱，李景舜，等.Excel軟件在環(huán)境監(jiān)測質(zhì)量控制中的應(yīng)用［J］.四川環(huán)境，2005，24（3）：101-103.

［11］ 徐建平.Excel在確定監(jiān)測方法重復(fù)性與再現(xiàn)性中的運(yùn)用［J］.環(huán)境監(jiān)測管理與技術(shù)，2010，22（1）：54-58.

［12］ 付友寶，楊利，遇方彪.MicrosoftExcel在環(huán)境監(jiān)測數(shù)據(jù)處理方面的應(yīng)用-Grubbs檢驗離群數(shù)據(jù)［J］.四川環(huán)境，2004，23（6）：107-109.

［13］ 陳軍，趙安潔，何蓉.利用Excel快速建立t檢驗?zāi)０澹跩］.光譜實驗室，2006，23（5）：1096-1098.

［14］ 邱訓(xùn)平.EXCEL在水質(zhì)現(xiàn)狀評價中的應(yīng)用與技巧［J］.水資源研究，2011，32（3）：33-34，40.

［15］ 白愛民.湖庫富營養(yǎng)化狀態(tài)的快速評價［J］.環(huán)境科學(xué)導(dǎo)刊，2014，33（1）： 80-83.

［16］ 房瑋.EXCEL軟件在污染物擴(kuò)散模式計算中的應(yīng)用［J］.廣州環(huán)境科學(xué)，2001，16（3）：43-45.

［17］ 許濤，穆衛(wèi)亮，袁榮根.利用Excel繪制監(jiān)測數(shù)據(jù)百分位數(shù)矩形圖［J］.四川環(huán)境，2007，26（6）：112-115.

［18］ 董瑞.利用EXCEL制作質(zhì)量控制圖［J］.四川環(huán)境，2001，20（1）： 67-69.

［19］ 孔凡洲，于仁成，徐子鈞，等.應(yīng)用Excel軟件計算生物多樣性指數(shù)［J］.海洋科學(xué)， 2012，36（4）： 57-62.

［20］ 劉玉龍，王蘇明，劉菲，等.EXCEL在氣相色譜-質(zhì)譜檢測中的應(yīng)用［J］.巖礦測試，2011，30（1）：116-120.

［21］ 夏炳訓(xùn)，寧璇璇，陳權(quán)文，等.Excel在海洋浮游植物數(shù)據(jù)自動化處理中的應(yīng)用研究［J］.四川環(huán)境，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

海洋公益性行業(yè)科研專項（201305003）。

夏炳訓(xùn)（1970－），男，山東煙臺人，國家海洋局煙臺海洋環(huán)境監(jiān)測中心站高級工程師，從事海洋環(huán)境監(jiān)測與評價工作。