Google 表格(Google Sheets)是一种流行的电子表格工具,它改变了当今人们的协作方式。这个基于 Web 的电子表格工具最适合作为Microsoft Excel的免费替代品,并允许在线创建和编辑电子表格数据。Excel比这个免费的在线工具具有更多的特性和内置功能,但它是首选,因为它是免费的,并且可以从任何设备在线访问。
Google 表格(Google Sheets)现在添加了这些强大的功能来自动执行单调的任务。在电子表格中工作涉及重复性任务,这些任务可能很无聊且令人厌烦。宏是提高生产力的恰当方式,可让您自动执行无聊单调的任务。处理具有相似图表、函数和数据的多张工作表的电子表格(Spreadsheet)用户将从宏中受益。宏(Macros)是节省时间的最佳方式,它可以让您专注于重要任务,而不是做乏味的乏味任务。
什么是宏?
宏(Macros)是允许您自动执行重复任务而无需编写代码的程序。宏(Macros)记录您的操作并保存它们,以便您在需要时只需单击一个按钮即可重复使用它们。当您想要自动化工作表中的繁琐工作时,宏会派上用场,例如添加格式、插入额外的行、插入额外的列、格式化表格、创建图表、插入棘手的公式、插入函数等等。(Macros)
简单来说,宏是一种节省时间的工具,它是记录重复性任务、保存任务并在将来随时运行任务而无需编写任何代码的三重奏。
创建宏以在(Create Macros)Google表格中自动执行任务
通过在浏览器URL中输入(URL)sheet.new(sheets.new)来启动 Google 表格,或者只需打开Google Drive文件夹并按Shift + S在该文件夹中创建一个新的Google 表格(Google Sheet)。
在工作表的任何单元格中键入一些数据。导航到工具(Tools)并从下拉菜单中选择宏。(Macros)
单击子菜单中的记录宏(Record macro)。这将在工作表底部打开一个录制新宏框。(Recording New Macro)您将被要求在两个选项之间进行选择。用户绝对引用或使用(User)相对引用。
当您想将格式化技术应用到所记录的确切位置时,请选择绝对引用。( Absolute references)假设(Say)您要格式化一系列单元格 A2:C1。选择这些范围单元格中的数据并将字体设为粗体。宏将仅应用于这些单元格,并且无论您单击哪个单元格,都将始终使这些单元格范围内的数据显示为粗体。当您想将格式应用到不同的单元格时,请选择相对引用,而不管记录的确切位置。(Relative references)它根据光标所在的位置应用宏,而不是宏被记录的确切位置。
当您想要在您选择的单元格及其附近的单元格上插入图表、函数或公式时,这很有用。假设您在单元格 B1 中记录粗体数据,该宏稍后可用于在 C1 中的单元格中加粗。
在这两个选项之间进行选择后,谷歌表格将开始记录。您想在单元格、列或行中自动执行的任何格式都将被记录下来。(Any)确保提前计划好要录制的内容。
在任何所需的单元格中应用格式,例如更改字体样式、颜色等。宏(Macro)记录会监视您的每一步。
完成后,单击“保存( Save)”按钮并输入宏(Macro)的名称。
您还可以设置自定义快捷(Short)键以快速访问您的宏(Macro)。
单击保存(Save)按钮以创建宏。
要访问宏,请导航到工具(Tools),然后选择宏。(Macros.)
从子菜单中单击(Click)要运行的宏文件夹。(Macro)
编辑你的宏
您可以更改宏的名称、(Macros)编辑宏(Macro)脚本、删除宏,甚至在创建后添加键盘快捷键。
导航到工具(Tools),然后从下拉菜单中单击宏。(Macros)
从子菜单中选择管理宏( Manage Macros)以编辑您的宏。
要删除宏或编辑其脚本,请转到选项宏(Macros)旁边的更多(More),然后单击更新。(Update.)
导入其他宏
在您的 Google 表格上,导航至工具。(Tools. )从下拉菜单中选择选项宏。( Macros)从子菜单中单击选项导入(Import)。
单击要导入的函数旁边的添加函数按钮。( Add function)
这里的所有都是它的。我希望你觉得这篇文章有用。(That’s all there is to it. I hope you find the post useful.)
How to automate tasks in Google Sheets with Macros
Google Sheets is a popular spreadsheet tool that has changed the way people collaborate today. This web-based spreadsheet tool serves best as a free alternative to the Microsoft Excel and allows to create and edit spreadsheets data online. Excel has more features and built-in functions than this free online tool, but it is preferred because it is free and due to its online accessibility from any device.
Google Sheets has now added these powerful features to automate monotonous tasks. Working in spreadsheets involves recurrent tasks that can be boring and tiresome. Macros are the apt way of being productive that lets you automate the boringly monotonous tasks. Spreadsheet users who deal with multiple sheets having similar charts, functions, and data would reap benefit from the macros. Macros are the best way to save your time that would allow you to focus on your important task rather than doing the deary round of bland tasks.
What are Macros?
Macros are the programs that will allow you to automate the recurring task without the need of you writing code. Macros record your action and save them so that you can reuse them when needed with a single click of a button. Macros come in handy when you want to automate the tedious work in sheets like add formatting, inserting additional rows, inserting additional columns, formatting tables, creating charts, inserting tricky formulas, inserting functions and more.
Macros in simple terms is a time-saving tool which is a trifecta of recording a repetitive task, saving the task and running the task in future whenever you want without writing any code.
Create Macros to automate tasks in Google Sheets
Launch Google Sheets by entering sheets.new in your browser URL or simply open Google Drive folder and press Shift + S to create a new Google Sheet in that folder.
Type some data in any cell of the sheet. Navigate to Tools and select Macros from the drop-down menu.
Click on the Record macro from the submenu. This will open a Recording New Macro box at the bottom of your sheets. You will be asked to choose between the two options. Either User absolute references or use relative references.
Select Absolute references when you want to apply formatting techniques to the exact location as recorded. Say you want to format a range of cells A2: C1. Select the data in those range cells and make the font bold. The macros will be applied to these cells only and will always make the data in those cells range appear bold regardless of which cell you clicked. Select Relative references when you want to apply formatting to the different cells, irrespective the exact location as recorded. It applies macros based on where your cursor is rather than the exact location where the macros were recorded.
This is useful when you want to insert chart, functions, or formulas on the cells you select and its nearby cells. Say if you record bolding data in cell B1, the macro can be later be used to bold cells in C1.
After you select between the two choices, the google sheets will start recording. Any formatting which you want to automate in the cell, column, or row will be recorded. Make sure to plan what you want to record well in advance.
Apply the formatting in any desired cell-like changing the font style, color, etc. The Macro recorded watches each of your step.
Once done, click the Save button and type the name for your Macro.
You can also set a custom Short key to have quick access to your Macro.
Click the Save button to create your macro.
To access the Macro, Navigate to Tools, and select Macros.
Click on the Macro folder you want to run from the submenu.
Edit your Macro
You can change the name of the Macros, edit the Macro scripts, remove macros or even add the keyboard shortcut after its created.
Navigate to Tools and click Macros from the drop-down menu.
Select Manage Macros from the submenu to edit your macro.
To delete a macro or edit its scripts, go to More beside the options Macros and click Update.
Import other macros
On your Google Sheets, navigate to Tools. Select the options Macros from the drop the menu. Click the option Import from the submenu.
Click Add function button next to the function you want to import.
That’s all there is to it. I hope you find the post useful.