在 Excel 中创建自定义的函数

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

虽然 Excel 包含大量内置的工作表函数,很可能是没有您执行的计算的每种类型的函数。设计器的 Excel 可能无法预计计算每个用户的需求。相反,Excel 提供了创建自定义的函数,本文中介绍的功能。

自定义的函数,例如宏,使用Visual Basic for Applications (VBA)编程语言。它们不同宏从两个重要的方式。首先,这些使用函数过程,而不是Sub过程。也就是说,启动时出现,而不是Sub语句和结束,而不是End Sub结束函数函数语句。第二步,它们会执行计算,而不是执行操作。从自定义的函数中排除某些类型的语句,例如选择和设置格式的范围,语句。在本文中,您将了解如何创建和使用自定义的函数。若要创建函数和宏,可以使用Visual Basic 编辑器 (VBE),它可以独立于 Excel 在新窗口中打开。

假设您的公司提供的 10%的产品,提供顺序的销售数量折扣适用于超过 100 个单位。在下面的段落,我们将演示函数来计算此折扣。

下面的示例显示列出了每个项目、 数量、 价格、 折扣 (如果有),订单窗体和生成总价。

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

若要在此工作簿中创建自定义折扣函数,请按照下列步骤:

  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

现在,您就可以使用新的折扣函数。关闭 Visual Basic 编辑器中,选择单元格 G7,并键入以下命令:

=DISCOUNT(D7,E7)

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

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

让我们来设想 Excel 如何解释该 function 过程。时按Enter,Excel 查找折扣的当前工作簿中的名称,并找到它是在 VBA 模块中的自定义的函数。参数名称括在括号中数量价格,是折扣的计算所基于的值的占位符。

使用自定义的函数示例订单窗体

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

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

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

Discount = quantity * price * 0.1

结果将存储为折扣的变量。存储在变量的值的 VBA 语句被称为工作分配语句中,因为它在等号右侧的表达式的计算结果,并将结果分配给左侧的变量名。折扣变量具有相同名称的函数过程,因为是名为折扣函数的工作表公式返回存储在变量的值。

如果数量少于 100,VBA 执行下面的语句:

Discount = 0

最后,以下语句舍入到两个小数位数折扣变量分配的值:

Discount = Application.Round(Discount, 2)

VBA 具有无 ROUND 函数,但 Excel。因此,若要在此语句中使用 ROUND,您可以辨别 VBA 查找应用程序对象 (Excel) 中的圆方法 (函数)。执行该操作通过添加字词之前的应用程序word Round。当您需要从 VBA 模块访问 Excel 函数,请使用此语法。

自定义的函数必须函数语句的开头和结尾结束函数语句。除了在函数名称的函数语句通常指定一个或多个参数。但是,您可以不带任何参数创建一个函数。Excel 中包含多个内置函数 — RAND,现在,例如 —,不要使用参数。

以下函数语句 function 过程包括一个或多个做出决策和使用传递给函数的参数进行计算的 VBA 语句。最后,位置中的函数过程中,您必须包含语句分配给具有相同名称的函数的变量的值。此值将返回到调用函数的公式。

您可以使用自定义的函数中的 VBA 关键字数小于数您可以使用的宏。自定义的函数不可以执行任何操作以外返回到工作表中,在公式或其他 VBA 宏或函数中使用的表达式的值。例如,自定义的函数不能调整窗口的大小、 中某个单元格,编辑公式或更改字体、 颜色或图案的单元格中的文本的选项。如果 function 过程中包括"操作"这种类型的代码,该函数将返回 # #VALUE !错误。

Function 过程可以执行的操作 (除了执行计算) 的一个操作是显示一个对话框。您可以作为一种从执行函数的用户获取输入自定义的函数中使用InputBox语句。您可以使用MsgBox语句作为一种传输给用户的信息。您也可以使用自定义对话框或用户窗体,但这是此简介范围以外的主题。

更简单的宏和自定义函数很难阅读。您可以让它们易于理解的批注的窗体中键入说明文字。您可以通过使用撇号说明文字前面添加注释。例如,下面的示例显示带批注的折扣函数。添加以下类似的注释便于您或其他人随着时间的推移维护 VBA 代码。如果您需要在将来的代码进行更改,您将有更轻松地了解您最初执行的操作。

带批注的 VBA 函数的示例

撇号告诉 Excel 忽略同一行中,在右侧的所有内容,以便您可以单独行上创建批注或右侧的行包含 VBA 代码。您可能会开始相对较长的代码块有说明了整体用途的注释,然后使用文档各个语句到内嵌批注。

您的宏和自定义的函数的文档另一种方法是可授予他们描述性名称。例如,而不是名称宏标签,您可能会其命名为MonthLabels描述更具体地说宏提供服务的目的。使用宏和自定义的函数的描述性名称将非常有用创建后许多过程,特别是当您创建具有相似但不是相同用途的过程。

您如何记录您的宏和自定义的函数是一种个人首选项。什么是文档的重要是文档的采用一些方法,并使用它一致。

若要使用自定义的函数,必须打开包含在其中创建该函数的模块中的工作簿。如果该工作簿没有打开,您将获得 # #NAME?当您尝试使用函数的错误。如果引用其他工作簿中的函数,您必须在函数名称前面加函数所在的工作簿的名称。例如,如果您创建函数调用 Personal.xlsb 工作簿中名为折扣,该函数调用从另一个工作簿,你必须键入=personal.xlsb!discount(),而不只是=discount()

您可以省去一些键击 (和可能的键入错误) 通过从插入函数对话框中选择您的自定义函数。在用户定义的类别中显示您自定义的函数:

“插入函数”对话框

更简单的方法在提供您的自定义函数始终是将其存储在单独的工作簿,然后将该工作簿另存为外接程序。您可以然后提供该加载项时运行 Excel。下面介绍了如何执行此操作:

  1. 在创建所需的功能后,单击文件>另存为

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

  2. 另存为对话框中,打开保存类型下拉列表中,,然后选择Excel 加载项加载项文件夹中保存工作簿可以识别的名称,如MyFunctions下。另存为对话框中会建议该文件夹,因此您需要执行所有接受默认位置。

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

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

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

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

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

    加载项对话框

  1. 在创建所需的功能后,单击文件>另存为

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

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

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

请按照下列步骤后,您的自定义函数可每次运行 Excel。如果您想要添加到您的函数库,返回到 Visual Basic 编辑器中。如果您看标题下的 vba 项目 Visual Basic 编辑器项目资源管理器中,您将看到用加载项文件的名称命名模块。加载项将具有扩展名.xlam。

vbe 中的命名模块

双击该项目资源管理器模块导致显示函数代码,Visual Basic 编辑器。若要添加新的函数,将插入点放置在终止代码窗口中的最后一个函数的结束函数语句后,然后开始键入。您可以创建许多函数这种方式,您需要他们将始终是在插入函数对话框中的用户定义的类别中可用。

通过标记减淡和Microsoft Office Excel 2007 Inside Out其簿 Craig Stinson 最初创作此内容。已将其由于更新到较新版本的 Excel 也将应用。

需要更多帮助吗?

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

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

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×