基于Excel VBA的漁具選擇性分析SELECT模型實現(xiàn)

金宇鋒， 張 健

(上海海洋大學(xué) 大洋漁業(yè)資源可持續(xù)開發(fā)省部共建教育部重點實驗室;

國家遠洋工程技術(shù)研究中心;農(nóng)業(yè)部大洋漁業(yè)資源環(huán)境科學(xué)觀測實驗站，上海 201306)

0 引言

漁具的選擇性是指漁具在作業(yè)過程中只能捕獲特定種類或者具有特定生物特征捕撈對象的特性，它是漁具設(shè)計和漁業(yè)管理的重要依據(jù)。在拖網(wǎng)漁具選擇性研究中，早期對于比較試驗法實驗結(jié)果的處理方法因缺乏嚴謹統(tǒng)計模型而被質(zhì)疑［1-2］，而SELECT模型的建立和提出解決了這一難題，并被廣泛運用于各類漁具的選擇性研究中［3-4］。SELECT模型作為廣義線性模型，在具體的漁具選擇性分析中有多種實現(xiàn)手段，除了使用SAS、Splus等統(tǒng)計分析軟件的線性函數(shù)［5-6］、或者使用Fortran或者R語言編寫的處理程序包以外［7-8］，還有使用專門編寫的軟件，例如Constat的拖網(wǎng)漁具選擇性分析軟件CC2000［9-12］以及Herrmann等開發(fā)的分析軟件SELNET［13-14］。此外，基于表格處理軟件Excel的“規(guī)劃求解”(SOLVER Add-in)功能實現(xiàn)模型解析的方法自運用于拖網(wǎng)選擇性分析以來也得到了廣泛的運用［15-18］，在科研和教學(xué)實踐中發(fā)現(xiàn)，要充分利用這一方法實現(xiàn)拖網(wǎng)等漁具的選擇性分析不僅需要對SELECT模型的充分認識，還需要具備一定的Excel函數(shù)知識。本文根據(jù)對SELECT模型的解析，結(jié)合Excel內(nèi)置的VBA(Visual Basic for Application)程序語言，開展模型在拖網(wǎng)等過濾性漁具選擇性分析中的自動實現(xiàn)的研究和探討，為今后開展?jié)O具選擇性研究或教學(xué)提供參考。

1 模型簡介

1.1 SELECT 模型原理

SELECT 模型［3，19］從捕撈對象的分布特點出發(fā)，認為在漁具選擇性研究中，特定漁具i(研究對象)所捕獲的捕撈對象的數(shù)量Ci服從一個多項:

Ci～Multi(∑C;Φ1，…，Φi，…，ΦI)

式中:Φi為單元概率，表示一尾被捕獲的個體被i漁具捕獲的的期望概率，即Pi為i漁具的相對作業(yè)強度;Sij為i漁具的選擇率;θ為模型參數(shù)，即包含選擇性參數(shù)，也包括相對作業(yè)強度。

1.2 SELECT模型在拖網(wǎng)選擇性分析中的解析

在拖網(wǎng)選擇性試驗中，若使用比較試驗法，對比對照網(wǎng)和試驗網(wǎng)的漁獲進行分析，SELECT模型轉(zhuǎn)化為二項分布，即

式中:C1、C2分別為試驗網(wǎng)和對照網(wǎng)捕獲的漁獲數(shù)量;單元概率為試驗網(wǎng)的相對作業(yè)強度。

若使用套網(wǎng)法開展選擇性試驗，則C1、C2分別為網(wǎng)囊和套網(wǎng)中的漁獲數(shù)量，且Φ=S。

1.3 SELECT模型在拖網(wǎng)選擇性分析中實現(xiàn)的方法

在拖網(wǎng)選擇性分析中，通常使用極大似然估計法估算模型參數(shù)，似然函數(shù)為

對于選擇性曲線，除了Logistic曲線以外，考慮到特定情況下曲線的非對稱性，還可選擇Richards曲線(通過假設(shè)檢驗可驗證是否可以簡化為Logistic曲線)。在對比試驗法下，既可將相對作業(yè)強度假設(shè)為P=1/2(稱為等分模型)、也可將相對作業(yè)強度作為參數(shù)進行估算以此比較捕撈對象對實驗漁具和對照漁具的行為差異(稱為估計分隔模型)［19］，當(dāng)實驗網(wǎng)和對照網(wǎng)捕撈努力量(例如作業(yè)時間或者漁具數(shù)量)不同時，也可直接給定P(定義為固定分隔模型)［20］。那么，在拖網(wǎng)選擇性分析中，SELECT模型的拖網(wǎng)選擇性分析中的實現(xiàn)可歸納為8種模型，表1中分別用1～8來表示，模型簡化可歸納為7類，分別為表中的11-17。

表1 拖網(wǎng)選擇性分析中SELECT模型實現(xiàn)方法和簡化方法

2 拖網(wǎng)選擇性分析的Excel VBA自動實現(xiàn)

2.1 自動實現(xiàn)流程圖

在不同的拖網(wǎng)選擇性分析模型和模型簡化，建立如圖1所示的主程序流程圖。

2.2 交互界面

2.2.1 實驗參數(shù)交互界面

圖1 主程序流程圖

圖2 實驗參數(shù)錄入和曲線選擇交互界面

實驗參數(shù)錄入和曲線選擇交互界面如圖2所示。除可設(shè)置網(wǎng)次數(shù)量、選擇試驗方法、選擇性曲線以外，為了后續(xù)可以開展網(wǎng)次間差異的分析，同時設(shè)置了試驗參數(shù)(既包括定性，也包含定量參數(shù))。通過不同的設(shè)置方法和參數(shù)選擇，向主程序傳遞參數(shù)。

2.2.2 選擇性分析參數(shù)選擇交互界面

選擇性分析中的模型簡化、初始值設(shè)定、標(biāo)準(zhǔn)差計算選擇交互界面如圖3所示。其中，嵌套模型(Nested Model)功能是通過假設(shè)檢驗以對模型進行簡化，包括對選擇性曲線和對估算分隔模型的簡化判斷(具體簡化的方法見表1)。

2.3 選擇性分析程序和調(diào)用

圖3 選擇性分析參數(shù)選擇交互界面

從功能上程序可分為2類，其中選擇率、單元概率和似然值計算、模型選擇和曲線簡化以及使用“規(guī)劃求解”功能求解模型參數(shù)為主程序，其他部分包括參數(shù)標(biāo)準(zhǔn)差計算、圖表顯示等為輔助程序。模型實現(xiàn)的完 整 程 序 可 從http://hyxy.shou.edu.cn/keylab/admin/UploadFiles/2013581059115.rar處下載并查看。

程序采用了RibbonX代碼的方式，在功能區(qū)制作了調(diào)用控件以實現(xiàn)程序調(diào)用。而主程序則采用加載宏的方式調(diào)用，當(dāng)然直接程序打開亦可使用。

3 實例分析

分別選用文獻Fryer(1991)中套網(wǎng)法拖網(wǎng)選擇性試驗數(shù)據(jù)［21］(僅以第1種漁具的第2網(wǎng)次數(shù)據(jù))和文獻Millar(1992)中對比試驗拖網(wǎng)選擇性試驗數(shù)據(jù)［3］為例，使用上述代碼(結(jié)合相應(yīng)的輔助代碼)，選擇不同分析模型并選擇模型簡化，進行數(shù)據(jù)擬合，所得結(jié)果如表2所示。

對比原文獻中的實驗數(shù)據(jù)擬合結(jié)果發(fā)現(xiàn)，在相同的模型下，擬合結(jié)果是完全一致的。不僅如此，在程序中，我們內(nèi)置了不同模型的比較和模型簡化的方法，根據(jù)優(yōu)劣性指標(biāo)選擇合適的模型，例如在表2中，根據(jù)套網(wǎng)法的擬合和簡化結(jié)果，可使用 Logistic曲線代替Richards曲線以優(yōu)化模型(P＞0.05);而在對比試驗法中，使用等分模型的假設(shè)不能被接受(P＜0.01)，但可接受Logistic代替Richards曲線的假設(shè)(P＞0.05)。

4 討論

目前SELECT模型在拖網(wǎng)漁具選擇性分析可通過多種途徑實現(xiàn)，但是無論是使用大型統(tǒng)計軟件的相關(guān)模塊、還是使用專門的選擇性分析軟件，除了需要具備一定的軟件(或語言)基礎(chǔ)外，商業(yè)軟件本身價格不菲。通過使用VBA實現(xiàn)SELECT模型在漁具選擇性分析雖然受到Excel平臺和VBA語言自身條件限制，例如無法定義單元格的數(shù)據(jù)類型、在浮點計算、矩陣計算等方面的先天不足、代碼執(zhí)行效率低和計算時間較長等，但是在漁具選擇性分析中，因為計算量不大，這些缺點并不突出。此外，使用VBA實現(xiàn)的方法與其它幾種實現(xiàn)方法相比，仍存在一些優(yōu)勢:

表2 不同試驗方法下模型擬合和簡化結(jié)果

(1)繼承Excel平臺和VBA語言的優(yōu)點，例如無需運行庫，無需配置環(huán)境參數(shù)，僅依靠Excel平臺而忽略操作系統(tǒng)和數(shù)據(jù)格式;可調(diào)用Excel內(nèi)置的函數(shù);Excel自帶的打印、文件處理、格式化或者文本編輯功能，且VBA支持調(diào)用Windows底層API;VBA與VB(VisalBasci)代碼格式一致，可調(diào)用一切基于VB的函數(shù)等;Excel的加載項“規(guī)劃求解”功能強大，除可設(shè)置迭代算法以外，還可設(shè)置精度等條件;此外，可對Excel自帶的圖表格式進行修改以滿足不同的需求;Excel自帶的宏錄制功能，不僅方便處理一些不熟悉的實現(xiàn)步驟，也便于對程序代碼的理解;這些都為今后的繼續(xù)開展?jié)O具選擇性分析的VBA自動實現(xiàn)提供了便利。

(2)Excel軟件普及度較高，通用性強，而且其中“規(guī)劃求解”功能也屬于免費加載項;此外，多數(shù)情況下前期的漁獲數(shù)據(jù)處理會使用Excel軟件，因此前后期數(shù)據(jù)銜接較好，便于對數(shù)據(jù)的統(tǒng)計分析和分類保存，數(shù)據(jù)格式比較統(tǒng)一;

(3)因為需要使用“規(guī)劃求解”功能求模型參數(shù)，因此在SELECT模型的實現(xiàn)過程中，使用了單元格公式編輯(.formula或者.formulaR1C1語法)的方法，而不是直接計算單元格的值，這一方法不僅有助于了解模型實現(xiàn)的步驟進而掌握模型，而且有助于利用Excel自帶的查錯功能迅速定位代碼中的邏輯錯誤或者計算過程中的數(shù)值錯誤，一方面可方便對模型的修改或者完善補充;另一方面也有助于在教學(xué)中的理解和應(yīng)用。

表1基本涵蓋了拖網(wǎng)等過濾性漁具選擇性分析中對套網(wǎng)法和對比試驗法的試驗數(shù)據(jù)分析的基本模型，但在此次SELECT模型的實現(xiàn)過程中，仍對部分模型和試驗條件進行了限制。例如，雖然過濾性漁具的選擇性曲線模型有多種，但是從目前多數(shù)研究來看，基本使用Logistic和Richards曲線，因此在此次VBA實現(xiàn)中僅考慮了這2個曲線模型;考慮到分析結(jié)果與后續(xù)使用混合影響模型進行網(wǎng)次間差異分析的相互銜接，限定了定性的試驗漁具種類不超過3種，定量的試驗參數(shù)不超過4種(實際中這些限定條件能滿足絕大部分分析要求)。除了對模型和試驗條件限制以外也應(yīng)該看到，模型的實現(xiàn)的主要流程也可被應(yīng)用于其它漁具、其它試驗方法或者其它選擇性模型，張健等就利用了類似的實現(xiàn)手段對分隔裝置的分隔性能進行了分析［22］，因此利用Excel完成實現(xiàn)模型這一思路具有較為廣泛的擴展性。

在模型的擬合中也應(yīng)注意，“規(guī)劃求解”加載項功能雖然強大，但其對初始值的選取比較敏感，當(dāng)始值選取不合理的時候，迭代過程會出現(xiàn)不收斂的情況，進而無法得出合理的試驗結(jié)果，特別是在使用Richards曲線作為選擇性曲線(存在指數(shù)計算)的時候。因此在進行自動化求解選擇性參數(shù)之前，須根據(jù)經(jīng)驗或者制作漁獲分布圖對漁獲數(shù)據(jù)進行簡單分析判斷出選擇性參數(shù)的大致范圍，并可在選擇性分析參數(shù)選擇交互界面(見圖3)中預(yù)先設(shè)定。

過濾性漁具作業(yè)效率較高，因此會出現(xiàn)對捕獲的漁獲進行取樣的情況。此次模型實現(xiàn)中，根據(jù)有關(guān)的理論［23］也將這一因素考慮到模型之中，因此模型的適用性得到了進一步的加強。

［1］CADIGAN N G，MILLAR R B.Reliability of selection curves obtained from trouser trawl or alternate hual experiments［J］.Canadian Journal of Fisheries and Aquatic Sciences，1992，49:1625-1632.

［2］梁振林，葛長字，劉英光.國外漁具選擇性研究進展［J］.青島海洋大學(xué)學(xué)報(自然科學(xué)版)，2001，31(6):835-841.

LIANG Zhen-lin，GE Chang-zi，LIU Ying-guang.Review of study on grid separators for beam trawl the gear selectivityabroad［J］.Journal of ocean university of china(Natural Science)，2001，31(6):835-841.

［3］MILLAR R B.Estimation the size-selectivity of fishing gear by conditioning on the total catch［J］.Journal of the American Statistical Association，1992，87:962-968.

［4］MILLAR R B，F(xiàn)RYER R J.Estimating the size-selection curves of towed gears，traps，nets and hooks［J］.Reviews in Fish Biology and Fisheries，1999，9(1):89-116.

［5］MILLAR R B.Analysis of trawl selectivity studies(addendum):implementation in SAS［J］.Fisheries Research，1993，17(3-4):373-377.

［6］MILLAR R B，Holst R.Estimation of gillnet and hook selectivity using log-linear models［J］.ICES Journal of Marine Science，1997，54:471-477.

［7］MILLAR R B，CADIGAN N G.A FORTRAN program for fitting selectivity curves to trouser trawl data［M］.St.John’s:Dept.of Fisheries and Oceans， Newfoundland Region， Science Branch，1990.

［8］MILLAR R B.Selectivity software and documentation［EB/OL］.The University of Auckland，New Zealand，2010 ［2013-02-03］;http://www.stat.auckland.ac.nz/～ millar/selectware/code.html.

［9］AYDIN C，TOSUNOGLU Z.Selectivity of square and hexagonal mesh codendsforthe deep waterroseshrimp，Parapenaeus longirostris(Lucas，1846)(Decapoda，Penaeidae)in the Aegean Sea［J］.Crustaceana，2009，82(1):89-98.

［10］AYDIN C，TSUNGLU Z，TOKAC A.Sorting grid trials to improve size selectivity of red mullet(Mullus barbatus)and annular sea bream(Diplodus annularis)in Turkish bottom trawl fishery［J］.Journal of Applied Ichthyology，2008，24(3):306-310.

［11］CONSTAT，CC2000Selectivityprogram［CP］， ConStatDK，Hjorring，Denmark，2000.

［12］FRANDSEN R P，MADSEN N，KRAG L A.Selectivity and escapement behaviour of five commercial fishery species in standard square-and diamond-mesh codends［J］.ICES Journal of Marine Science:Journal du Conseil，2010，67(8):1721-1731.

［13］HERRMANN B，MADSEN N，SISTIAGA M，et al.Modelling escapement during the fishing process as a dual sequence-Introducing SELNE.Poster session presented at ICES Working Group on Fishing Technology and Fish Behaviour(WGFTFB).Ancona，Italy:ICES;2009.

［14］SISTIAGA M，HERRMANN B，GRIMALDO E，et al.Assessment of dual selection in grid based selectivity systems［J］.Fisheries Research，2010，105(3):187-199.

［15］TOKAI T.Maximum likelihood parameter estimates of a mesh selectivity logistic model through SOLVER on MS-Excel［J］.Bulletin of The Japanese Society of Fisheries Oceanography，1997，61(3):288-298.

［16］ZUUR G，F(xiàn)RYER R J，F(xiàn)ERRO R S T，et al.Modelling the size selectivities of a trawl codend and an associated square mesh panel［J］.ICES Journal of Marine Science，2001，58(3):657-671.

［17］WILEMAN D A，F(xiàn)ERRO R S T，F(xiàn)ONTEYNE R，et al.Manual of Methods of Measuring the Selectivity of Towed Fishing Gears［R］.Copenhagen Denmark:ICES，1996.No.215.

［18］孫滿昌，張 健，許柳雄，等.漁具漁法選擇性［M］.北京:中國農(nóng)業(yè)出版社，2004.

［19］MILLAR R B，WALSH S J.Analysis of trawl selectivity studies with an application to trouser trawl［J］.Fisheries Research，1992，13(3):205-220.

［20］XU X，MILLAR R B.Estimation of trap selectivity for male snow crab(Chionoecetes opilio)using the SELECT modeling approach with unequal sampling effort［J］.Canadian Journal of Fisheries and Aquatic Sciences，1993，50(11):2485-2490.

［21］FRYER R J.A model of between-haul variation in selectivity［J］.ICES J Mar Sci，1991，48(1):281-290.

［22］張 健，石建高，張 鵬，等.分隔式桁拖網(wǎng)分隔裝置的分隔效率模型研究［J］.水產(chǎn)學(xué)報，2009，33(4):697-703.

ZHANG Jian， SHI Jian-gao， ZHANG Peng， et al.Model for separation effeciency assessment of separator devices rigged in multilevel beam trawls［J］.Journal of Fsheries of China，2009，33(4):697-703.

［23］MILLAR R B.Sampling from trawl gears used in size selectivity experiments［J］.ICES Journal of Marine Science，1994，51(3):293-298.