Excel宏入门教程：从基础到实战

Excel宏入门教程：从基础到实战

这个Excel宏实战教程将向您展示什么是宏，如何使用VBA宏以及丰富的示例：

在工作中，我们大多数人都会遇到需要每天重复执行的任务。想象一下，如果这些任务只需单击一下就能完成，是不是很令人兴奋？Excel宏就是答案。

在本教程中，我们将学习什么是宏，如何使用绝对引用和相对引用录制宏，并通过一些实际示例进行演示。

什么是Excel宏

宏是一系列可以运行以执行所需任务的操作。例如，假设您每月创建一个报告，需要将逾期金额的用户账户标记为粗体红色。那么您可以创建并运行一个宏，每次需要时都应用这些格式更改。

如何在Excel中启用宏

开发人员选项卡使我们能够使用宏、加载项等功能，并允许我们编写自己的VBA代码，以帮助我们自动化任何想要的任务。此选项卡默认是隐藏的。

按照以下步骤取消隐藏开发人员选项卡。这适用于所有Windows版本的Excel（Excel 2007、2010、2013、2016、2019）。

注意：这是一个一次性过程。一旦启用开发人员选项卡，它将在每个打开的Excel实例的自定义功能区中显示，除非您显式禁用它。

启用开发人员选项卡

1. 单击"文件"选项卡
2. 单击"选项"
3. 单击"自定义功能区"
4. 在"自定义功能区"下启用"开发人员"

启用开发人员选项卡后，它将显示在功能区列表中。

开发人员选项卡的选项

以下是开发人员选项卡下可用的选项：

• Visual Basic：提供编辑器以编写或编辑VBA代码。也可以使用Alt+F11打开。
• Macros：显示所有已录制的宏列表，并用于录制新宏。Alt+F8将直接打开宏列表。
• Add-ins：允许插入加载项并管理它们。
• Controls：帮助使用表单控件和ActiveX控件。查看和编辑控件属性。设计模式的开启/关闭控制在这里。
• XML：帮助导入/导出XML数据文件，管理XML扩展包，以及打开XML源任务窗格。

如何录制宏

考虑一个示例，您的公司有一个工具，用于为各个部门生成Excel考勤表。作为经理，您有责任每周审核并将表格发送给财务团队。

但在发送之前，您需要进行一些格式设置：

1. 在每个工作表中插入标题，包括团队名称和周数，将其加粗并设置背景色为黄色。
2. 绘制边框
3. 将列标题加粗
4. 将工作表名称重命名为团队名称

与其每周手动执行这些操作，不如创建一个宏，只需单击一下即可完成所有这些操作。

录制宏非常简单。导航到开发人员选项卡，然后点击"录制宏"。

这将打开一个窗口，您需要输入：

1. 宏名称：名称不应包含空格。必须以字母或下划线开头。
2. 快捷键：这在运行宏时很有用。如果您按下快捷键，宏将被执行。确保给出未被占用的键，否则宏将覆盖该功能。例如，如果将Ctrl+S设置为快捷键，那么每次按下Ctrl+S时，您的宏将被执行，从而忽略保存文件选项。因此，建议添加Shift，如Ctrl+Shift+D
3. 存储宏位置：有3个选项

• This Workbook：所有创建的宏仅对当前工作簿可用。如果打开新Excel，则之前创建的宏不可用，因此无法使用。
• Personal Macro Workbook：如果选择此选项，那么创建的宏将被存储，并在打开新Excel工作表时显示。
• New Workbook：此选项将打开一个新的工作簿，您在该工作簿中执行的所有操作都将被记录。

4. 描述：这将描述宏的目的。建议给出详细描述，以便使用该宏的任何人都能了解其具体用途。

填写上述字段的详细信息后，您可以继续在Excel工作簿中执行所需的操作，所有操作都将被记录。完成后，返回开发人员选项卡并点击"停止录制"。

保存带有宏的Excel工作簿

选择存储宏位置为"This Workbook"：

假设您在录制时选择了存储宏位置为"This Workbook"。保存文件时，您需要选择"Excel启用宏的工作簿"。您不需要显式保存宏，它会自动保存。

选择存储宏位置为"Personal Macro workbook"：

现在假设在录制时选择了存储宏位置为"Personal Macro workbook"。您需要显式保存宏。如果您只是保存Excel文件然后尝试关闭文件，那么您将收到如下所示的弹出对话框。

注意：如果您不保存，那么宏将被删除。

运行宏

现在我们已经完成了录制和保存文件，让我们尝试运行它并获得预期的结果。我们已经提前录制了一个宏，包含了考勤表示例中所需的所有步骤，并将其保存为当前工作簿，快捷键为Ctrl+Shift+B。

因此，每周当您从软件工具接收新的Excel时，只需打开该Excel文件并按下快捷键（Ctrl+Shift+B），所有预期的更改都将被应用。生成的Excel如下所示。

注意：

1. 如果您忘记了快捷键，可以转到开发人员->宏，选择宏并点击选项。
2. 如果存储在个人存储中的宏在宏选项卡中不可见。转到视图->取消隐藏，这将显示所有宏的列表。

单元格引用

有两种方式来录制宏，如下所示：

1. 绝对单元格引用
2. 相对单元格引用

绝对单元格引用：

绝对引用将始终指向录制时的特定单元格。例如，如果您在A10单元格中录制文本，那么下次在另一个工作簿中使用该宏时，它将把文本放在A10。

考虑我们的考勤表示例。我们总是希望标题在每个工作表的第一行。我们不希望单元格引用在复制到其他工作表或工作簿时发生变化。在这种情况下，绝对单元格引用非常有用。

相对单元格引用：

假设您需要在工作表的多个位置重复相同的操作。相对引用在需要在多行或多列中重复相同计算或步骤时很方便。

例如，假设您有一个包含1000名员工的Excel表格，包括全名、电话号码和出生日期。（格式如下）
Emp ID Emp FullName Phone Number DOB
1 John Jeson 1111111111 10-01-1987
2 Tom Matis 2222222222 01-02-1988
3 Jesper Cluster 3333333333 22-02-1989
4 Tim Joseph 4444444444 16-03-1990
5 Vijay abc 5555555555 07-04-1991

您的经理期望您：

1. 分离名字和姓氏
2. 在电话号码前添加国家代码（例如+91）
3. 将出生日期显示为dd-mon-yy格式，例如10 Jan 87

由于有1000条记录，手动操作将耗时。因此，您决定创建一个宏。但是使用绝对引用无法解决问题，因为您希望它在多行多列中工作。在这种情况下，相对引用非常有用。

使用相对引用录制Excel宏

要使用相对引用录制，首先选择您要开始录制的单元格。
转到开发人员->点击使用相对引用->录制宏。录制您想要的任何内容，然后点击停止录制。

对于上述示例，请按照以下步骤操作：

1. 首先，我们需要在Emp FullName旁边插入一列，并将列标题更改为FirstName和LastName。
2. 选择B2单元格->转到开发人员->使用相对引用->录制宏。
3. 使用文本分隔符分离名字和姓氏。完成后停止录制。
4. 同样，为电话号码和出生日期创建2个更多宏。
5. 保存文件。
6. 要执行，选择所有Emp FullName，即B3到最后一个员工B1001，并执行第一个宏。
7. 对电话号码和出生日期重复相同步骤。生成的Excel如下所示。
   Emp ID Emp FirstName Emp LastName Phone Number DOB
   1 John Jeson (+91) 1111111111 10-Jan-87
   2 Tom Matis (+91) 2222222222 01-Feb-88
   3 Jesper Cluster (+91) 3333333333 22-Feb-89
   4 Tim Joseph (+91) 4444444444 16-Mar-90
   5 Vijay abc (+91) 5555555555 07-Apr-91

常见问题解答

Q #1) Excel中宏的示例是什么？
答案：宏是一系列可以运行以执行所需任务的操作。例如，假设您每月创建一个报告，需要将逾期金额的用户账户标记为粗体红色。您可以创建并运行一个宏，每次需要时都应用这些格式更改。

Q #2) Excel中的宏在哪里？
答案：所有已录制的宏都可在开发人员选项卡->宏中找到。如果找不到个人宏，那么转到视图->取消隐藏。

Q #3) Excel中有哪几种单元格引用类型？
答案：

• 绝对：绝对引用将始终指向录制时的特定单元格。例如，如果您在D10单元格中录制文本，那么每次使用宏时它始终指向D10。
• 相对：这些在需要在多行或多列中重复相同计算或步骤时很方便。

Q #4) 如何将宏保存到所有工作簿？
答案：在录制宏时选择个人宏工作簿下的存储宏位置，这将使您的宏对所有工作簿可用。如果仍然看不到选项，转到视图->取消隐藏。

结论

在本教程中，我们学习了Excel宏，它可以帮助我们在Excel中自动化常规任务。我们了解了什么是宏，如何在Excel中启用宏。我们还探讨了如何使用绝对引用和相对引用录制宏，并通过示例进行了演示。