在 Excel 中创建自定义函数

尽管Excel包含大量内置工作表函数,但它可能没有适用于您执行的每种类型的计算的函数。 Excel设计人员不可能预测每个用户的计算需求。 相反,Excel提供创建自定义函数的功能,本文对此进行了说明。

自定义函数(如宏)使用 Visual Basic for Applications ( VBA) 编程语言。 它们在两个方面与宏不同。 首先,它们使用 函数 过程而不是 Sub 过程。 也就是说,它们从 Function 语句而不是 Sub 语句开始,以 End Function 而不是 End Sub 结尾。 其次,它们执行计算而不是操作。 某些类型的语句(例如选择和设置区域格式的语句)从自定义函数中排除。 本文将了解如何创建和使用自定义函数。 若要创建函数和宏,可以使用 VBE Visual Basic编辑器 (VBE) ,该编辑器在新窗口中打开,Excel。

假设贵公司在销售产品时提供 10% 的数量折扣,但订单数量超过 100 个单位。 以下段落将演示计算此折扣的函数。

以下示例显示了一个订单窗体,其中列出了每个项目、数量、价格、折扣 ((如果有) )以及生成的扩展价格。

没有自定义函数的示例订单窗体

若要在此工作簿中创建自定义 DISCOUNT 函数,请执行以下步骤:

  1. Alt+F11 Visual Basic Mac 上的 (编辑器,按FN+Alt+F11) ,然后单击"插入>模块"。 新模块窗口显示在编辑器的右侧Visual Basic窗口。

  2. 将以下代码复制并粘贴到新模块。

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

注意: 若要使代码更具可读性,可以使用 Tab 键缩进行。 缩进仅适用于你,并且是可选的,因为代码将随或不带它一起运行。 键入缩进行后,Visual Basic编辑器会假定下一行将采用类似的缩进方式。 若要在 (制表符左侧移动) ,请按Shift+Tab。

现在,可以使用新的 DISCOUNT 函数。 关闭Visual Basic编辑器",选择单元格 G7,然后键入以下内容:

=DISCOUNT (D7,E7)

Excel每单位 47.50 美元计算 200 个单位的 10% 折扣,并返回 $950.00。

在 VBA 代码的第一行函数 DISCOUNT (数量、价格) ,指示 DISCOUNT 函数需要两个参数 :数量价格。 在工作表单元格中调用函数时,必须包含这两个参数。 在公式 =DISCOUNT (D7,E7) ,D7 是 数量 参数,E7 是 price 参数。 现在,可以将 DISCOUNT 公式复制到 G8:G13,以获得如下所示的结果。

让我们考虑如何Excel此函数过程。 按Enter时,Excel在当前工作簿中查找名称DISCOUNT,并发现它是 VBA 模块中的自定义函数。 括在括号、数量和价格中的参数名称是计算折扣所基于的值的占位符。

具有自定义函数的示例订单窗体

以下代码块中的 If 语句检查 数量 参数,并确定销售的项数是否大于或等于 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

如果销售的项数大于或等于 100,VBA 将执行以下语句,该语句将数量值乘以价格值,然后将结果乘以 0.1:

Discount = quantity * price * 0.1

结果存储为变量 Discount。 将值存储在变量中的 VBA 语句称为 赋值 语句,因为它计算等号右侧表达式,并将结果分配给左侧的变量名称。 由于变量 Discount 与函数过程同名,因此变量中存储的值将返回到名为 DISCOUNT 函数的工作表公式。

如果 数量 小于 100,VBA 将执行以下语句:

Discount = 0

最后,以下语句将分配给 Discount 变量的值四周到两个小数位数:

Discount = Application.Round(Discount, 2)

VBA 没有 ROUND 函数,Excel ROUND 函数。 因此,若要在此语句中使用 ROUND,请告知 VBA 在 Application 对象 (中查找 round) 函数 (Excel) 。 为此,在 "Round"一词前添加"Application" 一词。 每当需要从 VBA 模块访问 Excel函数时,请使用此语法。

自定义函数必须以 Function 语句开始,以 End Function 语句结尾。 除了函数名称之外,Function 语句通常指定一个或多个参数。 但是,可以创建一个没有参数的函数。 Excel包括几个不使用参数的内置函数(例如 RAND 和 NOW)。

在 Function 语句之后,函数过程包括一个或多个 VBA 语句,这些语句使用传递给函数的参数做出决策和执行计算。 最后,在函数过程中,必须包含一个语句,该语句将值分配给与函数同名的变量。 此值将返回到调用函数的公式。

可在自定义函数中使用的 VBA 关键字数小于可在宏中使用的数量。 不允许自定义函数执行除将值返回到工作表中的公式,或返回到其他 VBA 宏或函数中使用的表达式外的任何操作。 例如,自定义函数无法调整窗口大小、编辑单元格中的公式或更改单元格中文本的字体、颜色或图案选项。 如果在函数过程中包含此类的"action"代码,函数将返回#VALUE! 错误。

函数过程可以执行一个操作 (执行计算,) 显示一个对话框。 可以使用自定义函数 中的 InputBox 语句作为从执行函数的用户获取输入的方式。 可以使用 MsgBox 语句作为向用户传达信息的方式。 也可使用自定义对话框或 UserForms,但这是一个超出此简介范围的主题。

即使是简单的宏和自定义函数也难以阅读。 可以通过以批注形式键入说明性文本,使其更易于理解。 添加注释时,在说明性文本前面加上撇号。 例如,以下示例显示了带注释的 DISCOUNT 函数。 添加此类注释可让你或其他人在经过一段时间后更轻松地维护 VBA 代码。 如果将来需要更改代码,可以更轻松地了解最初执行哪些操作。

带注释的 VBA 函数示例

撇号Excel忽略同一行中右侧所有内容,以便可以自行在行上或包含 VBA 代码的行右侧创建注释。 你可能会从一段相对较长的代码块开始,其中注释解释了其整体用途,然后使用内联注释来记录各个语句。

另一种记录宏和自定义函数的方法就是为宏和自定义函数提供描述性名称。 例如,你可以将它命名为MonthLabels,而不是为宏命名标签,以更具体地描述宏的用途。 创建许多过程时,对宏和自定义函数使用描述性名称尤其有用,尤其是在创建具有类似但目的不相同的过程时。

宏和自定义函数的文档记录方式由个人偏好决定。 重要的是采用某种文档方法,并一致地使用它。

若要使用自定义函数,必须打开包含创建函数的模块的工作簿。 如果该工作簿未打开,你收到#NAME? 错误。 如果在不同的工作簿中引用函数,则必须在函数名称的之前,使用函数所在的工作簿的名称。 例如,如果在名为 Personal.xlsb 的工作簿中创建了名为 DISCOUNT 的函数,并且从另一个工作簿调用该函数,则必须键入=personal.xlsb!discount () ,而不只是=discount () 。

可以通过从"插入函数" (选择自定义函数,为自己保存一些按键和) 键入错误。 自定义函数显示在"用户定义的"类别中:

“插入函数”对话框

使自定义函数在一切时刻都可用一种更简单的方式是将它们存储在单独的工作簿中,然后将该工作簿另存为加载项。 然后,每次运行运行时,都可以使该加载项Excel。 下面将了解如何执行此操作:

  1. 创建所需的函数后,单击"文件">另存为"。

    在 Excel 2007 中,单击"Microsoft Office按钮",然后单击"另存为"

  2. 在"另存为"对话框中,打开"另存为类型"下拉列表,然后选择"Excel加载项"。 将工作簿保存在AddIns文件夹中的可识别名称下,例如MyFunctions。 " 另存 为"对话框将建议该文件夹,因此只需接受默认位置。

  3. 保存工作簿后,单击"文件"> Excel选项"。

    在 Excel 2007 中,单击"Microsoft Office按钮",然后单击"Excel选项"。

  4. "Excel选项"对话框中,单击"加载项"类别。

  5. 在"管理"下拉列表中,选择"Excel加载项"。 然后单击" 转到" 按钮。

  6. "加载项"对话框中 ,选中用于保存工作簿的名称旁边的复选框,如下所示。

    加载项对话框

  1. 创建所需的函数后,单击"文件">另存为"。

  2. 在"另存为"对话框中,打开"另存为类型"下拉列表,然后选择"Excel加载项"。 将工作簿保存在可识别的名称下,例如MyFunctions。

  3. 保存工作簿后,单击"工具> Excel加载项"。

  4. 在"加载项"对话框中,选择"浏览"按钮以查找加载项,单击"打开",然后选中"可用加载项"框中Add-In旁边的框。

执行这些步骤后,每次运行自定义函数时,都会Excel。 如果要添加到函数库,请返回到"Visual Basic编辑器"。 如果在 VBAProject Visual Basic资源管理器Project,会看到一个名为 的模块,该模块以加载项文件命名。 加载项将具有扩展名 .xlam。

vbe 中的命名模块

在资源管理器中双击该Project会导致Visual Basic编辑器显示函数代码。 若要添加新函数,将插入点定位到终止代码窗口中最后一个函数的 End Function 语句的后,然后开始键入。 你可按照此方式创建所需的任何数量的函数,并且它们始终在"插入函数"对话框中的"用户定义的 "类别中可用

此内容最初由 MarkCrason 和2007Inside Out Microsoft Office Excel的一部分创作。 此后,它已更新为也适用于较新版本Excel版本。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

×