基于Excel VBA的渔具选择性分析SELECT模型实现

金宇锋， 张 健

(上海海洋大学 大洋渔业资源可持续开发省部共建教育部重点实验室;

国家远洋工程技术研究中心;农业部大洋渔业资源环境科学观测实验站，上海 201306)

0 引言

渔具的选择性是指渔具在作业过程中只能捕获特定种类或者具有特定生物特征捕捞对象的特性，它是渔具设计和渔业管理的重要依据。在拖网渔具选择性研究中，早期对于比较试验法实验结果的处理方法因缺乏严谨统计模型而被质疑［1-2］，而SELECT模型的建立和提出解决了这一难题，并被广泛运用于各类渔具的选择性研究中［3-4］。SELECT模型作为广义线性模型，在具体的渔具选择性分析中有多种实现手段，除了使用SAS、Splus等统计分析软件的线性函数［5-6］、或者使用Fortran或者R语言编写的处理程序包以外［7-8］，还有使用专门编写的软件，例如Constat的拖网渔具选择性分析软件CC2000［9-12］以及Herrmann等开发的分析软件SELNET［13-14］。此外，基于表格处理软件Excel的“规划求解”(SOLVER Add-in)功能实现模型解析的方法自运用于拖网选择性分析以来也得到了广泛的运用［15-18］，在科研和教学实践中发现，要充分利用这一方法实现拖网等渔具的选择性分析不仅需要对SELECT模型的充分认识，还需要具备一定的Excel函数知识。本文根据对SELECT模型的解析，结合Excel内置的VBA(Visual Basic for Application)程序语言，开展模型在拖网等过滤性渔具选择性分析中的自动实现的研究和探讨，为今后开展渔具选择性研究或教学提供参考。

1 模型简介

1.1 SELECT 模型原理

SELECT 模型［3，19］从捕捞对象的分布特点出发，认为在渔具选择性研究中，特定渔具i(研究对象)所捕获的捕捞对象的数量Ci服从一个多项:

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

式中:Φi为单元概率，表示一尾被捕获的个体被i渔具捕获的的期望概率，即Pi为i渔具的相对作业强度;Sij为i渔具的选择率;θ为模型参数，即包含选择性参数，也包括相对作业强度。

1.2 SELECT模型在拖网选择性分析中的解析

在拖网选择性试验中，若使用比较试验法，对比对照网和试验网的渔获进行分析，SELECT模型转化为二项分布，即

式中:C1、C2分别为试验网和对照网捕获的渔获数量;单元概率为试验网的相对作业强度。

若使用套网法开展选择性试验，则C1、C2分别为网囊和套网中的渔获数量，且Φ=S。

1.3 SELECT模型在拖网选择性分析中实现的方法

在拖网选择性分析中，通常使用极大似然估计法估算模型参数，似然函数为

对于选择性曲线，除了Logistic曲线以外，考虑到特定情况下曲线的非对称性，还可选择Richards曲线(通过假设检验可验证是否可以简化为Logistic曲线)。在对比试验法下，既可将相对作业强度假设为P=1/2(称为等分模型)、也可将相对作业强度作为参数进行估算以此比较捕捞对象对实验渔具和对照渔具的行为差异(称为估计分隔模型)［19］，当实验网和对照网捕捞努力量(例如作业时间或者渔具数量)不同时，也可直接给定P(定义为固定分隔模型)［20］。那么，在拖网选择性分析中，SELECT模型的拖网选择性分析中的实现可归纳为8种模型，表1中分别用1～8来表示，模型简化可归纳为7类，分别为表中的11-17。

表1 拖网选择性分析中SELECT模型实现方法和简化方法

2 拖网选择性分析的Excel VBA自动实现

2.1 自动实现流程图

在不同的拖网选择性分析模型和模型简化，建立如图1所示的主程序流程图。

2.2 交互界面

2.2.1 实验参数交互界面

图1 主程序流程图

图2 实验参数录入和曲线选择交互界面

实验参数录入和曲线选择交互界面如图2所示。除可设置网次数量、选择试验方法、选择性曲线以外，为了后续可以开展网次间差异的分析，同时设置了试验参数(既包括定性，也包含定量参数)。通过不同的设置方法和参数选择，向主程序传递参数。

2.2.2 选择性分析参数选择交互界面

选择性分析中的模型简化、初始值设定、标准差计算选择交互界面如图3所示。其中，嵌套模型(Nested Model)功能是通过假设检验以对模型进行简化，包括对选择性曲线和对估算分隔模型的简化判断(具体简化的方法见表1)。

2.3 选择性分析程序和调用

图3 选择性分析参数选择交互界面

从功能上程序可分为2类，其中选择率、单元概率和似然值计算、模型选择和曲线简化以及使用“规划求解”功能求解模型参数为主程序，其他部分包括参数标准差计算、图表显示等为辅助程序。模型实现的完 整 程 序 可 从http://hyxy.shou.edu.cn/keylab/admin/UploadFiles/2013581059115.rar处下载并查看。

程序采用了RibbonX代码的方式，在功能区制作了调用控件以实现程序调用。而主程序则采用加载宏的方式调用，当然直接程序打开亦可使用。

3 实例分析

分别选用文献Fryer(1991)中套网法拖网选择性试验数据［21］(仅以第1种渔具的第2网次数据)和文献Millar(1992)中对比试验拖网选择性试验数据［3］为例，使用上述代码(结合相应的辅助代码)，选择不同分析模型并选择模型简化，进行数据拟合，所得结果如表2所示。

对比原文献中的实验数据拟合结果发现，在相同的模型下，拟合结果是完全一致的。不仅如此，在程序中，我们内置了不同模型的比较和模型简化的方法，根据优劣性指标选择合适的模型，例如在表2中，根据套网法的拟合和简化结果，可使用 Logistic曲线代替Richards曲线以优化模型(P＞0.05);而在对比试验法中，使用等分模型的假设不能被接受(P＜0.01)，但可接受Logistic代替Richards曲线的假设(P＞0.05)。

4 讨论

目前SELECT模型在拖网渔具选择性分析可通过多种途径实现，但是无论是使用大型统计软件的相关模块、还是使用专门的选择性分析软件，除了需要具备一定的软件(或语言)基础外，商业软件本身价格不菲。通过使用VBA实现SELECT模型在渔具选择性分析虽然受到Excel平台和VBA语言自身条件限制，例如无法定义单元格的数据类型、在浮点计算、矩阵计算等方面的先天不足、代码执行效率低和计算时间较长等，但是在渔具选择性分析中，因为计算量不大，这些缺点并不突出。此外，使用VBA实现的方法与其它几种实现方法相比，仍存在一些优势:

表2 不同试验方法下模型拟合和简化结果

(1)继承Excel平台和VBA语言的优点，例如无需运行库，无需配置环境参数，仅依靠Excel平台而忽略操作系统和数据格式;可调用Excel内置的函数;Excel自带的打印、文件处理、格式化或者文本编辑功能，且VBA支持调用Windows底层API;VBA与VB(VisalBasci)代码格式一致，可调用一切基于VB的函数等;Excel的加载项“规划求解”功能强大，除可设置迭代算法以外，还可设置精度等条件;此外，可对Excel自带的图表格式进行修改以满足不同的需求;Excel自带的宏录制功能，不仅方便处理一些不熟悉的实现步骤，也便于对程序代码的理解;这些都为今后的继续开展渔具选择性分析的VBA自动实现提供了便利。

(2)Excel软件普及度较高，通用性强，而且其中“规划求解”功能也属于免费加载项;此外，多数情况下前期的渔获数据处理会使用Excel软件，因此前后期数据衔接较好，便于对数据的统计分析和分类保存，数据格式比较统一;

(3)因为需要使用“规划求解”功能求模型参数，因此在SELECT模型的实现过程中，使用了单元格公式编辑(.formula或者.formulaR1C1语法)的方法，而不是直接计算单元格的值，这一方法不仅有助于了解模型实现的步骤进而掌握模型，而且有助于利用Excel自带的查错功能迅速定位代码中的逻辑错误或者计算过程中的数值错误，一方面可方便对模型的修改或者完善补充;另一方面也有助于在教学中的理解和应用。

表1基本涵盖了拖网等过滤性渔具选择性分析中对套网法和对比试验法的试验数据分析的基本模型，但在此次SELECT模型的实现过程中，仍对部分模型和试验条件进行了限制。例如，虽然过滤性渔具的选择性曲线模型有多种，但是从目前多数研究来看，基本使用Logistic和Richards曲线，因此在此次VBA实现中仅考虑了这2个曲线模型;考虑到分析结果与后续使用混合影响模型进行网次间差异分析的相互衔接，限定了定性的试验渔具种类不超过3种，定量的试验参数不超过4种(实际中这些限定条件能满足绝大部分分析要求)。除了对模型和试验条件限制以外也应该看到，模型的实现的主要流程也可被应用于其它渔具、其它试验方法或者其它选择性模型，张健等就利用了类似的实现手段对分隔装置的分隔性能进行了分析［22］，因此利用Excel完成实现模型这一思路具有较为广泛的扩展性。

在模型的拟合中也应注意，“规划求解”加载项功能虽然强大，但其对初始值的选取比较敏感，当始值选取不合理的时候，迭代过程会出现不收敛的情况，进而无法得出合理的试验结果，特别是在使用Richards曲线作为选择性曲线(存在指数计算)的时候。因此在进行自动化求解选择性参数之前，须根据经验或者制作渔获分布图对渔获数据进行简单分析判断出选择性参数的大致范围，并可在选择性分析参数选择交互界面(见图3)中预先设定。

过滤性渔具作业效率较高，因此会出现对捕获的渔获进行取样的情况。此次模型实现中，根据有关的理论［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］.青岛海洋大学学报(自然科学版)，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，FRYER 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，FRYER R J，FERRO 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，FERRO R S T，FONTEYNE R，et al.Manual of Methods of Measuring the Selectivity of Towed Fishing Gears［R］.Copenhagen Denmark:ICES，1996.No.215.

［18］孙满昌，张 健，许柳雄，等.渔具渔法选择性［M］.北京:中国农业出版社，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］张 健，石建高，张 鹏，等.分隔式桁拖网分隔装置的分隔效率模型研究［J］.水产学报，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.