excel自定义函数编写方法

Excel自定义函数基础教程

1.总述、编写环境与设置

自定义函数功能是Excel中一个强大的扩展功能，它可以实现不同用户的特定计算要求。以使得Excel计算自主化、专业化，可以大大简化人工劳动（查表、专业公式计算、统计等）。所以编写与使用自定义函数应是每个工程计算人员必不可少的技能。本教程为零基础教程，特为呼和浩特职业技术学院编写，如需引用或咨询请联系526949738@https://www.360docs.net/doc/4f5787657.html,。

本教程以Excel2003为平台讲解，使用其他版本Excel的同学请参照学习。自定义函数需要应用宏编写，请进行如下设置。

工具—>宏—>安全性

写的宏了。

器，在弹出的窗口点击插入—>模块，即进入了编写环境。

选中相应的模块点击文件—>导出文件，就可以存储.bas的源文件。这样就可以方便存储整理，以便日后引用。

2.宏的概念与Visual Basic基础

有VB编程基础的同学可跳过此章。

宏实际上就是一段用以完成某些功能的源代码，Excel中的

宏以Visual Basic（以后简称VB）编写，存储后的.bas文件可以用记事本打开，里面记录的就是编写的代码。

VB是一门高级编程语言，其语法简单易于掌握，适合初学者学习。本教程中只对VB进行初步讲解，如果需要深入学习请参照VB相关的专门书籍（Visual Basic编辑器中的帮助也有详细介绍）。

顺序、选择、循环是编程的三种结构，合理组合嵌套这三种结构就可以解决所有编程问题。默认情况下，代码按照顺序结构执行，所以下面只介绍选择和循环结构的语句表达。

选择结构：

选择结构非常常见，它的作用是使程序根据条件的不同执行不同的语句。这里我们只介绍最简单的一种语句。

If condition Then [statements] [Else elsestatements]

或者

If condition Then

[statements]

[ElseIf condition-n Then

[elseifstatements] ...

[Else

[elsestatements]]

End If

注释：condition：条件表达式；statements：执行语句；[]中的内容为可选内容。

第一句为写在一行的选择语句，其表达的意思如英文一样就是“如果……就……否则……”，黑体的“If”“Then”是关键词必不可少，后面“[]”中的“Else”为可选项。下面的语句为多行表达的形式，要注意回车的位置和结尾处加上“End If”。还需要注意的是关键字后面一定要加空格或回车。具体用法将在后文的实例中介绍。

循环结构：

循环结构适合嵌套使用，这里只介绍固定次数循环语句，如需了解条件循环，请参考VB相关书籍。

For counter=start To end [Step step]

[statements]

[Exit For]

[statements]

Next [counter]

例：

For i = 1 To 10 Step 1

…

Next i

“For”“To”“Step”“Exit For”“Next”都为关键字，其中“For”“To”“Next”必不可少。以上面例子做为讲解。例中语句的意思为“使得i首先等于1，然后执行…的内容，接着使i 自身增加1，再执行…的内容，一直到i等于10，最后执行…的

内容后，停止循环，进入下一条语句”。本例中…的内容一共执行了10次。“Step”后面的数为每次i增加多少，如果不写“Step”则默认每次加1。最后i等于11。这里的i实际上就是人为定义的一个变量，下面介绍变量的概念。

变量：

变量实际上就是用字母汉字等标识代表一个“数”，而这个“数”在计算过程中也是可以变化的（与常量相反），命名的原则和“数”的类型，参考VB相关书籍。这里推荐用字母串或汉字（不要与VB关键字相同）命名变量。下面讲解变量基础知识。

1）变量的声明：

Dim abc

虽然VB容许不用事先声明变量，但是为了程序易读，在我们使用变量前都要对它进行声明。上面的语句的意思就是“声明了一个叫做abc的变量”。

2）变量的赋值：

abc = 10

abc = abc + 5

变量赋值不同于方程计算，它的顺序是先计算等号右边表达式的值，然后再赋给等号左边的变量（等号左边一般情况下只允许是一个变量）。上式第一句的意思是“把10赋予变量abc”。执行完这句后变量abc的值就是10。下一句的意思是“先计算等号

右边变量abc加5的值，然后再把值赋给变量abc”。先计算变量abc加5为15，然后再把15赋给变量abc。所以最后abc的值是15。

3）变量的计算：

上面已经提到变量的“+”运算，需要说明的是等号右边的表达式不仅可以是变量和数的运算，也可以是变量和变量的运算，且变量和数的个数理论上没有限制。所以我们学过的四则运算“+”“-”“*”“/”，还有括号“()”乘方“^”等都可以运用（括号必须是半角英文()，乘方3^5表示35）。当然还有其他运算需要大家自学。

4）变量的比较：

在选择语句中，“If”后的条件表达式需要用到比较，简单的有大于“>”、小于“<”、等于“=”、大于等于“>=”、小于等于“<=”、不等于“<>”。具体应用见下一章实例。

有了以上的知识我们就可以进行实例的讲解了，但是由于省略了大量VB的内容，所以这就要求大家在以后的学习实践中循序渐进地补充VB编程知识。

3．自定义函数实例讲解

例1.

如图我们已知矩形截面的宽和高，求其惯性矩。由于矩形惯性矩计算公式为：

12h b 3

＝

I

由于没有直接的Excel 公式计算，所以我们需要自定义函数。在第一章介绍的模块1的编写环境中输入以下代码，并保存。

Function I(b, h) I = (b * h ^ 3) / 12 End Function

注意所有符号均为半角英文，实际如上图。返回Excel界面，鼠标点击C3单元格，点击插入—>函数，下拉菜单中选择用户定义里面就有刚刚编写的I函数了。

选择后，第一个参量选单元格A3，第二个参量选单元格B3，确定后即可计算出结果，如图。

下拉C3单元格右下角即可得出所有惯性矩。如图。

下面我们详细讲解一下例1中的代码。

Function I(b, h)

I = (b * h ^ 3) / 12

End Function

一、三句是声明函数的必要格式，其通用格式为：

Function 函数名(参数变量1, 参数变量2,…,参数变量n)

……

函数内部语句

……

End Function

其中函数名就是我们在Excel中引用这个函数的名字，如上例中用户定义里面的I函数。参数变量就是我们编写这个函数需要的参数，其名字可以随意起，为了方便上例中就用“b”和“h”表示宽和高了。参数变量可以有多个，也可以没有，类型可以是一个单元格，也可以是多个单元格。之后会讲到多个单元格参数变量。

第二句“I = (b * h ^ 3) / 12”函数的运算语句，这里的I虽然是函数名，但它也是代表函数值的变量，它的作用就是存储函数值。等号左边是把从Excel表格中传递来的宽“b”和高“h”进行计算得到惯性矩，然后赋值给函数值变量。这样最后的函数值就是惯性矩了。

例2.

这个例子研究如何判断计算出的惯性矩是否大于500。新函数可以在原来函数下面书写，代码如下：

Function over(q)

If q > 500 Then

over = "是"

Else

over = "否"

End If

End Function

应用此函数计算。在D3处插入函数，取值范围选择C3单元格。确定后，右下角拉下。

确定后，右下角拉下。

代码分析。函数名为over，只有一个参数变量q，它用来存储上一个函数计算出来的惯性矩（直接从Excel表中读取）。中间的判断语句意思为“如果q大于500则函数值over为‘是’，

否则函数值over为‘否’”。从中可以看到函数值和变量还可以存储字符，但是在代码中一定要用””标出（英文半角）。If和then 中间的部分是判断条件，其中经常为逻辑表达式（请参照VB课程）。

例3.

统计选中区域内大于500的个数。代码如下：

Function num(a)

Dim r

Dim i

num = 0

r = a.Rows.Count

For i = 1 To r

If a(i, 1) > 500 Then num = num + 1

Next i

End Function

代码分析。函数名为num，参数变量为a，但是这里的a是一个范围，它代表了多个单元格，本例中它代表了一列的单元格（上图虚线框）。Dim r和Dim i定义了两个变量，num=0使得函数值变量初始值为零。r = a.Rows.Count等号右边表示参数变量a 的行个数，表达式表示将a的行数赋予r。

For i = 1 To r

If a(i, 1) > 500 Then num = num + 1

Next i

为一个循环语句，从i=1一直循环到i=r，循环体中是一个判断语句，a(i,1)表示a的第i行第1列单元格的值，这个语句表示“判断从a第1行第1列单元格到a最末行第1列单元格的值是否大于500，若大于500，每次循环num自加1。”

由于a的范围我们只考虑了单列的情况，如果a去为多行多

列那么就需要再进行一次循环，代码如下：

Function num(a)

Dim r

Dim i

Dim j

Dim c

num = 0

c = a.Columns.Count

r = a.Rows.Count

For i = 1 To r

For j = 1 To c

If a(i, j) > 500 Then num = num + 1 Next j

Next i

End Function

基础内容已经讲完，今后还需大家自学提高。