Excel 在计算群落生物多样性指数中的应用*
陈俊华1,2, 文吉富3, 王国良3
(1.成都理工大学, 四川 成都 610059; 2.四川省林业科学研究院，四川 成都 610081;
3. 富顺县国营林场, 四川 富顺 643200)
Chen Junhua1,2 , Wen Jifu3,Wang Guoliang3
(1.Chengdu University of Technology ,Chengdu 610059;2.Sichuan Academy of Forestry,Chengdu 610081;3.Fushun forestry farm,Fushun county ,Sichuan 643200)
摘 要：本文首先介绍了在Excel 中用于计算生物多样性指数的两个重要函数；然后举实例介绍了在Excel 中如何计算生物多样性指数；最后指出了Excel 在生态学统计计算中的优越性。
关键词：Excel; 群落; 重要值; 生物多样性指数; 函数
中图分类号： 文献标识码：A 文章编号：
On Application of Calculating Biodiversity Index in Communities with Excel
CHEN Jun-hua1,2 XIANG Cheng-hua2 LUO Zong-shi2
(1. Chengdu University of Technology ,Chengdu 610059,China;2. Sichuan Academy of Forestry,Chengdu 610081)
Abstract:In this paper,the two important function of calculating biodiversity index were presented with Excel at first;then pointet out how to calculate parameters on biodiversity with the example in Excel;at last,the advantage of Excel in ecology statistical evaluation was indicated.
Key words:Excel;communities;importan value; biodiversity index;function
生物多样性(Biodiversity)是指生命有机体及其赖以生存的生态综合体的多样化和变异性。具体讲，生物多样性既是指生命形式的多样化，也包括生命形式之间、生命形式与环境之间相互作用的多样性，还应涉及到生物群落、生态系统、生境、生态过程等的复杂性。生物多样性通常有3个层次：遗传多样性、物种多样性和生态系统多样性[1]。而在其生物多样性研究中，常用多样性指数来表征群落的多样性特征[2]。这时常需要对大量采样数据进行计算和分析，尤其是计算物种的频度时，看似简单的公式，计算起来却非常繁琐。当然可以用高级语言(如VB 、VC) 等编制计算程序，但对编程语言不熟悉的人来说是一件棘手的事情。 Microsoft Excel是美国微软公司研制开发的用于个人财务分析与规划、公司营运管理与目标设定和薪水管理等的一个出色的电子表格软件，它具有友好的界面、强大的数据计算与统计分析功能。Excel 进行数据计算与统计分析的一个显著特点是，它可以进行公式编辑和插人函数，而且具有连环链锁计算能力[3]。并且笔者发现Excel 中的函数[4]非常有用，有些函数组合起来可以说奥妙无穷，能起到意想不到的结果。下面笔者就详细介绍在Excel 中如何计算生物多样性指数，以此与读者分享。 * 收稿日期：
基金项目：国家林业科技支撑项目“四川盆地低山丘陵区农林复合系统可持续经营技术研究”。
(NO:2006BAD03A05-4)
作者简介：陈俊华(1972-),男, 工程师, 博士生。主要从事计算机及“3S ”技术在林业生态方面的应用研究。E-mail:[email protected]
1 生物多样性指数的计算公式[5]
(1) 种群的数量特征：相对密度(多度) 、相对频度、相对显著度、重要值。
相对密度=某物种的个体数⨯100% (1) 全部物种的个体数之和
相对频度=某物种的频度⨯100% (2) 全部物种的频度之和
某物种的胸高断面积⨯100% (3) 全部物种的胸高断面积之和相对显著度=
重要值=(相对密度+相对频度+相对显著度)/3 (4)
(2)生物多样性指数
最常用的生物多样性指数有Shannon-Wiener 多样性指数、Pielou 群落均匀度指数JSW
和Simpson 生态优势度C 。
Shannon-Wiener 多样性指数H ' =-
Pielou 群落均匀度指数Jsw =(-
Simpson 生态优势度C =i ∑P ln P (5) i i i i 2∑P ln P ) /ln S (6) (7) ∑(P /N )
式中N 为取样中所有种类的重要值总值，P i 为第i 种的相对重要值，S 为样地的物种总数。 2 Excel 中用于计算生物多样性指数的函数介绍[4]
2.1 VLOOKUP()函数介绍
(1)功能
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的V 代表垂直。
(2)语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或
文本字符串。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如
数据库或列表。
如果 range_lookup 为 TRUE ，则 table_array 的第一列中的数值必须按升序排列：…、
-2、-1、0、1、2、…、-Z 、FALSE 、TRUE ；否则，函数 VLOOKUP 不能返回正确的数值。
如果 range_lookup 为 FALSE ，table_array 不必进行排序。通过在“数据”菜单中的“排
序”中选择“升序”，可将数值按升序排列。
Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。
Col_index_num为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，
返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值值 #VALUE! ；如果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE ，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。
(3)说明
如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE ，则使用小于等于 lookup_value 的最大值。 如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE ，函数 VLOOKUP 返回错误值 #N/A。
2.2 TYPE()函数介绍
(1)功能
返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用函数
表1 TYPE 的Value 值及返回值 TYPE 。
value 值 返回值 (2)语法：TYPE(value)
Value 可以为任意 Microsoft Excel 数值，如数字、数字 1 文本以及逻辑值等等(表1) 。 文本 2 (3)说明 逻辑值 4
①当使用能接受不同类型数据的函数（例如函数 误差值 16 ARGUMENT 和函数 INPUT ）时，函数 TYPE 十分有数组 64 用。可以使用函数 TYPE 来查找函数或公式所返回的数
据是何种类型。
②可以使用 TYPE 来确定单元格中是否含有公式。TYPE 仅确定结果、显示或值的类型。如果某个值是一个单元格引用，它所引用的另一个单元格中含有公式，则 TYPE 将返回此公式结果值的类型。
此外，在Excel 计算生物多样性计算中还需两个函数即SUMIF()和COUNTIF()，具体用法可见参考文献[4]或Excel 中的相关帮助。
3 Excel 中用于计算生物多样性指数的实例介绍
下面以实例介绍在Excel 中如何计算多样性指数。采样数据地点在四川省宝兴县东拉山，群落为野桂花群落，见参考文献[6]。为节约篇幅，只取3个样地作为计算。如图1。
图1 用Excel 计算生物多样性例子
(1)频度及相对频度的计算
如图1，在Excel 表格的F3单元格中输入“=IF(TYPE(VLOOKUP(E3,$A$3:$B$44,1,FALSE))=2,1,0)”，意思是如果在A3～B44中出现了物种“桂花”，则为“1”，否则为“0”。这样就可以计算物种分别在样地1～样地3中是否出现。在单元格I3中输入“SUM(F3:H3)”即可求出物种“桂花”在大样方中的频度。按住鼠标左键不动，拉动F3～F20便可求出所有物种的频度。然后按公式(2)即可求出物种的相对频度。
(2)密度(株数) 及相对密度的计算
在J3单元格中键入“=COUNTIF($A$3:$A$132,E3)”求出“桂花”在大样方中的株数，同理可求出所有物种的相对密度。按公式(1)可求出物种的相对密度。
(3)相对显著度的计算
在K3单元格中键入“=SUMIF($A$3:$D$132,E3,$D$3:$D$132)”求出“桂花” 在大样方中的胸高断面积之和。按公式(3)可求出物种的相对显著度。
(4)重要值的计算
按公式(4)计算出各物种的重要值。
(5)物种多样性指数的计算
先算出P i 。具体算法为P i =重要值i /300。然后再算Ln (P i ) 、P i 2及物种数S 。然后按公式(5)～公式(7)即可求出Shannon-Wiener 多样性指数H’、 Pielou 群落均匀度指数JSW 以及Simpson 生态优势度C 。求出结果见表1。
4 结论及讨论
在进行群落生态学研究时，常需完成大量的数据统计分析处理。Excel 作为电子表格软件，具有许多优点。首先，只需将原始采样数据一次性输人工作簿，将相关公式或函数输入相应单元格，所需结果就能一次到位全部计算完毕并显示在相应位置。而且由于Excel 具有强大的连环链锁计算能力，修改数据非常方便。只要变动任何一个原始数据，其后相关的所有计算结果都会自动地进行修正，不必重新计算或检查，因此，只要校对Excel 工作簿中原始数据，就完成了所有的校对工作，极省时方便。其次，Excel 与Word 具有很好的兼容性，两个程序中的表格与数据可互相调用，Excel 中的表格可直接粘贴到Word 中去，形成Word 格式的表格，而Word 中的表格数据同样可直接粘贴到Excel 工作簿中进行计算。数据在两个程序之间互相调用后不需再进行校对。Excel 中的数据也可直接复制到SPSS 或其他统计软件中去，这样既减少了工作量，也减少了数据出错的机会。再次，Excel 具有许许多多非常有用的函数，利用函数的嵌套可以起到意想不到的结果。懂得编程的读者还可利用其自身带的编程高级语言VBA 进行编程计算。总之，Excel 在林业生态研究进行统计分析时非常有用。笔者就用Excel 完成了生态位相关指数的计算模板以及层次分析法(AHP)、物元分析法等统计方法的模板设计。
参考文献：
[1] 李慧蓉. 生物多样性和生态系统功能研究综述[J].生态学杂志, 2004, 23(3):109～114.
[2] 张金屯. 数量生态学[M].北京:科学出版社,2005.
[3] 桂红义, 樊荣, 张建森, 等.EXCE197中文版入门与提高[M].北京:清华大学出版社,1997.
[4] [美]John Walkenbach著. 路晓村等译.Excel 2002 公式与函数应用[M].北京:电子工业出版社,2002.
[5] 彭少麟, 周厚诚, 陈天杏, 等. 广东森林群落的组成结构数量特征[J].植物生态学与地植物学学报,1989,13(1):10～17.
[6] 陈俊华, 何飞, 李建彬, 等. 东拉山野桂花群落物种多样性及乔木优势种生态位研究[J].四川林业科技,2007,28(4):48～51,9.
[7] 刘雨芳.EXCEL 在群落生物多样性参数计算中的应用[J].湘潭师范学院学报(自然科学版),2003,25(2):80～82.
Excel 在计算群落生物多样性指数中的应用*
陈俊华1,2, 文吉富3, 王国良3
(1.成都理工大学, 四川 成都 610059; 2.四川省林业科学研究院，四川 成都 610081;
3. 富顺县国营林场, 四川 富顺 643200)
Chen Junhua1,2 , Wen Jifu3,Wang Guoliang3
(1.Chengdu University of Technology ,Chengdu 610059;2.Sichuan Academy of Forestry,Chengdu 610081;3.Fushun forestry farm,Fushun county ,Sichuan 643200)
摘 要：本文首先介绍了在Excel 中用于计算生物多样性指数的两个重要函数；然后举实例介绍了在Excel 中如何计算生物多样性指数；最后指出了Excel 在生态学统计计算中的优越性。
关键词：Excel; 群落; 重要值; 生物多样性指数; 函数
中图分类号： 文献标识码：A 文章编号：
On Application of Calculating Biodiversity Index in Communities with Excel
CHEN Jun-hua1,2 XIANG Cheng-hua2 LUO Zong-shi2
(1. Chengdu University of Technology ,Chengdu 610059,China;2. Sichuan Academy of Forestry,Chengdu 610081)
Abstract:In this paper,the two important function of calculating biodiversity index were presented with Excel at first;then pointet out how to calculate parameters on biodiversity with the example in Excel;at last,the advantage of Excel in ecology statistical evaluation was indicated.
Key words:Excel;communities;importan value; biodiversity index;function
生物多样性(Biodiversity)是指生命有机体及其赖以生存的生态综合体的多样化和变异性。具体讲，生物多样性既是指生命形式的多样化，也包括生命形式之间、生命形式与环境之间相互作用的多样性，还应涉及到生物群落、生态系统、生境、生态过程等的复杂性。生物多样性通常有3个层次：遗传多样性、物种多样性和生态系统多样性[1]。而在其生物多样性研究中，常用多样性指数来表征群落的多样性特征[2]。这时常需要对大量采样数据进行计算和分析，尤其是计算物种的频度时，看似简单的公式，计算起来却非常繁琐。当然可以用高级语言(如VB 、VC) 等编制计算程序，但对编程语言不熟悉的人来说是一件棘手的事情。 Microsoft Excel是美国微软公司研制开发的用于个人财务分析与规划、公司营运管理与目标设定和薪水管理等的一个出色的电子表格软件，它具有友好的界面、强大的数据计算与统计分析功能。Excel 进行数据计算与统计分析的一个显著特点是，它可以进行公式编辑和插人函数，而且具有连环链锁计算能力[3]。并且笔者发现Excel 中的函数[4]非常有用，有些函数组合起来可以说奥妙无穷，能起到意想不到的结果。下面笔者就详细介绍在Excel 中如何计算生物多样性指数，以此与读者分享。 * 收稿日期：
基金项目：国家林业科技支撑项目“四川盆地低山丘陵区农林复合系统可持续经营技术研究”。
(NO:2006BAD03A05-4)
作者简介：陈俊华(1972-),男, 工程师, 博士生。主要从事计算机及“3S ”技术在林业生态方面的应用研究。E-mail:[email protected]
1 生物多样性指数的计算公式[5]
(1) 种群的数量特征：相对密度(多度) 、相对频度、相对显著度、重要值。
相对密度=某物种的个体数⨯100% (1) 全部物种的个体数之和
相对频度=某物种的频度⨯100% (2) 全部物种的频度之和
某物种的胸高断面积⨯100% (3) 全部物种的胸高断面积之和相对显著度=
重要值=(相对密度+相对频度+相对显著度)/3 (4)
(2)生物多样性指数
最常用的生物多样性指数有Shannon-Wiener 多样性指数、Pielou 群落均匀度指数JSW
和Simpson 生态优势度C 。
Shannon-Wiener 多样性指数H ' =-
Pielou 群落均匀度指数Jsw =(-
Simpson 生态优势度C =i ∑P ln P (5) i i i i 2∑P ln P ) /ln S (6) (7) ∑(P /N )
式中N 为取样中所有种类的重要值总值，P i 为第i 种的相对重要值，S 为样地的物种总数。 2 Excel 中用于计算生物多样性指数的函数介绍[4]
2.1 VLOOKUP()函数介绍
(1)功能
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的V 代表垂直。
(2)语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或
文本字符串。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如
数据库或列表。
如果 range_lookup 为 TRUE ，则 table_array 的第一列中的数值必须按升序排列：…、
-2、-1、0、1、2、…、-Z 、FALSE 、TRUE ；否则，函数 VLOOKUP 不能返回正确的数值。
如果 range_lookup 为 FALSE ，table_array 不必进行排序。通过在“数据”菜单中的“排
序”中选择“升序”，可将数值按升序排列。
Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。
Col_index_num为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，
返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1，函数 VLOOKUP 返回错误值值 #VALUE! ；如果 col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE ，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。
(3)说明
如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE ，则使用小于等于 lookup_value 的最大值。 如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE ，函数 VLOOKUP 返回错误值 #N/A。
2.2 TYPE()函数介绍
(1)功能
返回数值的类型。当某一个函数的计算结果取决于特定单元格中数值的类型时，可使用函数
表1 TYPE 的Value 值及返回值 TYPE 。
value 值 返回值 (2)语法：TYPE(value)
Value 可以为任意 Microsoft Excel 数值，如数字、数字 1 文本以及逻辑值等等(表1) 。 文本 2 (3)说明 逻辑值 4
①当使用能接受不同类型数据的函数（例如函数 误差值 16 ARGUMENT 和函数 INPUT ）时，函数 TYPE 十分有数组 64 用。可以使用函数 TYPE 来查找函数或公式所返回的数
据是何种类型。
②可以使用 TYPE 来确定单元格中是否含有公式。TYPE 仅确定结果、显示或值的类型。如果某个值是一个单元格引用，它所引用的另一个单元格中含有公式，则 TYPE 将返回此公式结果值的类型。
此外，在Excel 计算生物多样性计算中还需两个函数即SUMIF()和COUNTIF()，具体用法可见参考文献[4]或Excel 中的相关帮助。
3 Excel 中用于计算生物多样性指数的实例介绍
下面以实例介绍在Excel 中如何计算多样性指数。采样数据地点在四川省宝兴县东拉山，群落为野桂花群落，见参考文献[6]。为节约篇幅，只取3个样地作为计算。如图1。
图1 用Excel 计算生物多样性例子
(1)频度及相对频度的计算
如图1，在Excel 表格的F3单元格中输入“=IF(TYPE(VLOOKUP(E3,$A$3:$B$44,1,FALSE))=2,1,0)”，意思是如果在A3～B44中出现了物种“桂花”，则为“1”，否则为“0”。这样就可以计算物种分别在样地1～样地3中是否出现。在单元格I3中输入“SUM(F3:H3)”即可求出物种“桂花”在大样方中的频度。按住鼠标左键不动，拉动F3～F20便可求出所有物种的频度。然后按公式(2)即可求出物种的相对频度。
(2)密度(株数) 及相对密度的计算
在J3单元格中键入“=COUNTIF($A$3:$A$132,E3)”求出“桂花”在大样方中的株数，同理可求出所有物种的相对密度。按公式(1)可求出物种的相对密度。
(3)相对显著度的计算
在K3单元格中键入“=SUMIF($A$3:$D$132,E3,$D$3:$D$132)”求出“桂花” 在大样方中的胸高断面积之和。按公式(3)可求出物种的相对显著度。
(4)重要值的计算
按公式(4)计算出各物种的重要值。
(5)物种多样性指数的计算
先算出P i 。具体算法为P i =重要值i /300。然后再算Ln (P i ) 、P i 2及物种数S 。然后按公式(5)～公式(7)即可求出Shannon-Wiener 多样性指数H’、 Pielou 群落均匀度指数JSW 以及Simpson 生态优势度C 。求出结果见表1。
4 结论及讨论
在进行群落生态学研究时，常需完成大量的数据统计分析处理。Excel 作为电子表格软件，具有许多优点。首先，只需将原始采样数据一次性输人工作簿，将相关公式或函数输入相应单元格，所需结果就能一次到位全部计算完毕并显示在相应位置。而且由于Excel 具有强大的连环链锁计算能力，修改数据非常方便。只要变动任何一个原始数据，其后相关的所有计算结果都会自动地进行修正，不必重新计算或检查，因此，只要校对Excel 工作簿中原始数据，就完成了所有的校对工作，极省时方便。其次，Excel 与Word 具有很好的兼容性，两个程序中的表格与数据可互相调用，Excel 中的表格可直接粘贴到Word 中去，形成Word 格式的表格，而Word 中的表格数据同样可直接粘贴到Excel 工作簿中进行计算。数据在两个程序之间互相调用后不需再进行校对。Excel 中的数据也可直接复制到SPSS 或其他统计软件中去，这样既减少了工作量，也减少了数据出错的机会。再次，Excel 具有许许多多非常有用的函数，利用函数的嵌套可以起到意想不到的结果。懂得编程的读者还可利用其自身带的编程高级语言VBA 进行编程计算。总之，Excel 在林业生态研究进行统计分析时非常有用。笔者就用Excel 完成了生态位相关指数的计算模板以及层次分析法(AHP)、物元分析法等统计方法的模板设计。
参考文献：
[1] 李慧蓉. 生物多样性和生态系统功能研究综述[J].生态学杂志, 2004, 23(3):109～114.
[2] 张金屯. 数量生态学[M].北京:科学出版社,2005.
[3] 桂红义, 樊荣, 张建森, 等.EXCE197中文版入门与提高[M].北京:清华大学出版社,1997.
[4] [美]John Walkenbach著. 路晓村等译.Excel 2002 公式与函数应用[M].北京:电子工业出版社,2002.
[5] 彭少麟, 周厚诚, 陈天杏, 等. 广东森林群落的组成结构数量特征[J].植物生态学与地植物学学报,1989,13(1):10～17.
[6] 陈俊华, 何飞, 李建彬, 等. 东拉山野桂花群落物种多样性及乔木优势种生态位研究[J].四川林业科技,2007,28(4):48～51,9.
[7] 刘雨芳.EXCEL 在群落生物多样性参数计算中的应用[J].湘潭师范学院学报(自然科学版),2003,25(2):80～82.