一遍又一遍地执行相同的操作不仅无聊,而且还可能浪费时间并降低您的工作效率(productivity)。对于Excel 初学者来说(beginner Excel users)尤其如此,他们可能没有意识到通过录制宏来自动执行常见任务很容易。但是什么是Excel宏?
Excel宏允许您自动执行从插入公式到格式化数据的许多常见任务。您无需成为程序员即可创建它们,因为Excel可以在您执行操作时记录您的操作。如果您想知道如何在Excel中录制宏,您需要按照以下步骤操作。
什么是 Excel 宏?(What Is an Excel Macro?)
Excel宏是一组记录的指令,在 VBA(created manually in VBA)(Visual Basic for Applications)中手动创建或使用 Excel 的宏记录器(Macro Recorder )工具自动记录。宏允许您保存任意数量的常用操作,例如删除空白列、更改文本格式或将新公式插入工作簿。
然后可以通过单击按钮或使用特定的键盘快捷键来运行这些保存的操作。每个宏都有一个特定的快捷方式,您可以使用它来快速重复操作。
如果您使用 Excel 记录宏,您将减少创建或编辑电子表格所需的时间,从而加快您在此过程中的工作流程。
如何在 Excel 中录制宏(How to Record a Macro in Excel)
在Microsoft Excel(Microsoft Excel)中创建新宏的最简单方法是使用宏记录器(Macro Recorder )工具进行记录,您可以在Windows或Mac上的(Mac)Excel中使用该工具。遗憾的是,无法在Excel Online中录制宏。
- 要在Excel(Excel)中录制新宏,请打开一个新的或现有的工作簿。在功能区栏的“开发人员”选项卡中,选择“(Developer)录制宏”(Record Macro)按钮。或者,按键盘上的Alt + T + M + R键。
- 在“录制宏”(Record Macro)窗口中,您可以在“宏名称”框中为宏设置名称以标识其用途,并在“(Macro name)描述(Description )”框中为其他用户提供描述。您还可以添加快捷键(例如Ctrl + T)以将新宏分配给键盘快捷键。
- 在将宏存储在(Store macro in)下拉菜单中,您可以设置录制宏后要保存的位置,例如此工作簿(This Workbook )(将其保存到打开的工作簿)、新工作簿(New Workbook )(将其保存到新工作簿) ) 或个人宏工作簿(Personal Macro Workbook )(允许您在多个工作簿中使用它)。确认选择后,选择OK按钮开始录制。
- 选择OK后,Excel 宏录制器(Excel Macro Recorder)将处于录制模式。您执行的每个操作,从单击按钮到编辑单元格,都会被记录下来。此时执行您希望录制为宏的操作,然后在完成后选择“开发人员(Developer)”选项卡中的“停止录制”(Stop Recording )按钮。
运行、编辑或删除现有 Microsoft Excel 宏(Running, Editing or Deleting Existing Microsoft Excel Macros)
根据您在“录制宏”(Record Macro)窗口中选择的保存位置,您录制的宏(Macro)将准备好在您打开的工作簿或新打开的工作簿中运行。
- 要运行、编辑或删除您创建的宏,请从功能区栏中选择Developer > Macros
- 在宏(Macro )窗口中,将列出可在打开的工作簿中运行的可用宏列表。要运行宏,请从列表中选择它,然后选择“运行”(Run)按钮。您也可以使用创建宏时选择的键盘快捷键来运行它。
- 要改为更改录制的宏,请选择“编辑(Edit )”按钮。这将打开Excel VBA编辑器,允许高级用户编辑 VBA 代码(edit the VBA code)并更改宏的运行方式。VBA 初学者(VBA beginners)可能会发现简单地删除宏并重新记录它更容易。
- 要删除宏,请从列表中选择它,然后选择删除(Delete )按钮。
- Excel将要求您确认是否要删除宏。选择是(Yes)确认这一点。删除后,您需要使用上面列出的步骤再次录制宏。
使用录制的宏保存 Excel 文件(Saving Excel Files with Recorded Macros)
Excel工作簿通常保存为XLSX 文件格式(XLSX file format )(或XLS用于在Excel 2007和更早版本中创建的工作簿)。此文件类型支持大多数Excel数据,但不包括任何已保存的Excel宏。
要保存带有录制宏的Excel文件,您需要改用XLSM文件格式。
- 要将Excel文件另存为启用宏的工作簿,请选择File > Save As > Browse。
- 在“另存为(Save As )”窗口中,从下拉菜单中选择Excel Macro-Enabled Workbook (*.xlsm) 选择一个位置来保存文件,然后选择保存(Save)按钮来保存它。
- 当您在另一台 PC 或Mac上打开启用宏的Excel文件时,您可能需要先授权Excel运行任何包含的宏。选择启用宏(Enable Macros)按钮来执行此操作。
使用录制的宏共享 Excel 文件(Sharing Excel Files with Recorded Macros)
Excel宏很有用,但它们也包含潜在的安全风险。如果您从您不认识或不信任的来源打开包含宏的Excel文件,则您允许该文件在您的 PC 上运行危险代码。(Excel)
因此,Gmail等在线电子邮件提供商会自动阻止用户向其他用户发送XLSM(启用宏的Excel工作簿)文件。如果发生这种情况,您可以使用Google Drive等文件共享服务与其他用户在线共享。
如果您想与附近的其他用户共享启用宏的Excel文件,那么您还可以查看本地文件传输方法(local file transfer methods)以与另一台 PC 或Mac或其他设备(如智能手机)共享。
启用宏的Excel文件可能与病毒或其他恶意软件一起打包,因此如果您打开一个您不信任的Excel文件,您可能需要事后(Excel)扫描恶意软件(scan for malware)以确保您的 PC 没有受到损害。或者在打开文件之前扫描文件本身。(scan the file itself)
高级 Excel 提示和技巧(Advanced Excel Tips and Tricks)
录制宏只是可以帮助您节省时间的一种Excel 技巧(Excel trick),但还有其他Excel功能也可以提高您的工作效率。高级(Power)用户可能对使用 Excel 在线抓取数据感兴趣,而数据分析师可能对(using Excel to scrape data online)如何使用 COUNTIFS、SUMIFS 和 AVERAGEIFS(how to use COUNTIFS, SUMIFS and AVERAGEIFS)进行广泛的数据分析感到好奇。
如果您更喜欢团队合作,或者您正在为复杂的Excel公式而苦恼,您还可以使用共享工作簿在 Excel 电子表格上进行协作。(collaborate on Excel spreadsheets)您可能会发现Google 表格是在线协作的更好选择(Google Sheets is a better option),但它具有许多相同的功能和特性。
How to Record a Macro in Excel
Performing the same actіons over and over again isn’t juѕt boring, but it can also be a waste of time and a drain on your productivity. This is especially true for beginner Excel users, who might not realise that it’s easy to automate common tasks by recording a macro. But what is an Excel macro?
Excel macros allow you to automate many of the common tasks you’ll perform, from inserting formulas to formatting data. You don’t need to be a programmer to create them, as Excel can record your actions as you perform them. If you want to know how to record a macro in Excel, you’ll need to follow the steps below.
What Is an Excel Macro?
An Excel macro is a recorded set of instructions, created manually in VBA (Visual Basic for Applications) or recorded automatically using Excel’s Macro Recorder tool. A macro allows you to save any number of common actions, such as deleting blank columns, changing text formatting, or inserting new formulae into a workbook.
These saved actions can then be run with the click of a button or by using a specific keyboard shortcut. Each macro has a specific shortcut that you can use to quickly repeat the action.
If you use Excel to record a macro, you’ll reduce the time it takes to create or edit a spreadsheet, speeding up your workflow in the process.
How to Record a Macro in Excel
The easiest way to create a new macro in Microsoft Excel is to record it using the Macro Recorder tool, which you can use in Excel on Windows or Mac. Unfortunately, it isn’t possible to record a macro in Excel Online.
- To record a new macro in Excel, open a new or existing workbook. In the Developer tab on the ribbon bar, select the Record Macro button. Alternatively, press the Alt + T + M + R keys on your keyboard.
- In the Record Macro window, you can set a name for your macro to identify its purpose in the Macro name box, as well as provide a description for other users in the Description box. You can also add a shortcut key (such as Ctrl + T) to assign the new macro to a keyboard shortcut.
- In the Store macro in drop-down menu, you can set where you’d like to save the macro once it’s recorded, such as This Workbook (to save it to the open workbook), New Workbook (to save it to a new workbook), or Personal Macro Workbook (to allow you to use it in multiple workbooks). Select the OK button to begin recording once you’ve confirmed your choices.
- Once you select OK, the Excel Macro Recorder will be in recording mode. Each action you perform, from clicking a button to editing a cell, will be recorded. Perform the actions you wish to record as a macro at this point, then select the Stop Recording button in the Developer tab once you’re done.
Running, Editing or Deleting Existing Microsoft Excel Macros
Depending on the save location you selected in the Record Macro window, your recorded Macro will be ready to run in your open workbook or in a newly opened workbook.
- To run, edit, or delete the macro you’ve created, select Developer > Macros from the ribbon bar.
- In the Macro window, a list of available macros to run in your open workbook will be listed. To run the macro, select it from the list, then select the Run button. You can also use the keyboard shortcut you selected when you created the macro to run it instead.
- To make changes to the recorded macro instead, select the Edit button. This will open the Excel VBA editor, allowing advanced users to edit the VBA code and change how the macro runs. VBA beginners may find it easier to simply delete the macro and record it again.
- To delete the macro, select it from the list, then select the Delete button.
- Excel will ask you to confirm that you want to delete the macro. Select Yes to confirm this. Once deleted, you’ll need to record the macro again using the steps listed above.
Saving Excel Files with Recorded Macros
Excel workbooks are typically saved in the XLSX file format (or XLS for workbooks created in Excel 2007 and older). This file type supports most Excel data, but excludes any saved Excel macros.
To save an Excel file with recorded macros, you’ll need to use the XLSM file format instead.
- To save an Excel file as a macro-enabled workbook, select File > Save As > Browse.
- In the Save As window, select Excel Macro-Enabled Workbook (*.xlsm) from the drop-down menu. Choose a location to save the file, then select the Save button to save it.
- When you open the macro-enabled Excel file on another PC or Mac, you may need to authorize Excel to run any included macros first. Select the Enable Macros button to do this.
Sharing Excel Files with Recorded Macros
Excel macros are useful, but they also include potential security risks. If you open an Excel file containing macros from a source that you don’t recognize or trust, then you’re allowing that file to run dangerous code on your PC.
Because of this, online email providers like Gmail automatically block users from sending XLSM (macro-enabled Excel workbook) files to other users. If this happens, you may be able to use file sharing services like Google Drive to share it online with other users.
If you want to share your macro-enabled Excel file with another user nearby, then you could also look at local file transfer methods to share it with another PC or Mac, or with other devices like smartphones.
Macro-enabled Excel files can be packaged with viruses or other malware, so if you open an Excel file that you don’t trust, you may need to scan for malware afterwards to ensure that your PC isn’t compromised. Or scan the file itself before opening it.
Advanced Excel Tips and Tricks
Recording a macro is just one Excel trick that can help you save time, but there are other Excel features that can make you more productive as well. Power users might be interested in using Excel to scrape data online, while data analysts might be curious about how to use COUNTIFS, SUMIFS and AVERAGEIFS for extensive data analysis.
If you prefer to work as a team, or you’re struggling with a complicated Excel formula, you can also collaborate on Excel spreadsheets using a shared workbook. You may find that Google Sheets is a better option for online collaboration, however, with many of the same functions and features.