谷歌表格

Google Sheets lets you automate repetitive tasks with macros, and you can then bind them to keyboard shortcuts to execute them quickly. They work using Google Apps Script to capture your actions for later use.

Google表格可以讓您使用宏自動執(zhí)行重復(fù)性任務(wù)，然后可以將其綁定到鍵盤快捷鍵以快速執(zhí)行它們。 他們使用Google Apps腳本來捕獲您的操作以供以后使用。

什么是宏？ (What are Macros?)

A macro—or macroinstruction—is a specific sequence of actions that let you automate a series of steps to increase productivity. They work by recording your actions and saving them into a file that’s bound to the spreadsheet in which they were recorded.

宏(或宏指令)是特定的操作序列，可讓您自動化一系列步驟以提高生產(chǎn)率。 它們的工作方式是記錄您的操作并將其保存到與記錄了這些操作的電子表格綁定的文件中。

When you record a macro in Google Sheets, it automatically creates an Apps Script?with all the code to replicate your actions for you. This means you can create complex macros without knowing how to write code. The next time you run it, Sheets will do everything you did when you recorded the macro. Essentially, you’re teaching Google Sheets how to manipulate a document to your liking with a single command.

當您在Google表格中記錄宏時，它會自動創(chuàng)建一個包含所有代碼的Apps腳本，以為您復(fù)制操作。 這意味著您可以在不知道如何編寫代碼的情況下創(chuàng)建復(fù)雜的宏。 下次運行它時，Sheets將完成您在錄制宏時所做的所有操作。 本質(zhì)上，您是在教Google表格如何通過單個命令來按自己的喜好操縱文檔。

Macros are a powerful feature that can do basically anything Sheets is capable of doing. Here are just a few examples of its functionality:

宏是一項強大的功能，基本上可以完成Sheets能夠執(zhí)行的任何操作。 以下是其功能的一些示例：

• Apply formatting and styles.
  應(yīng)用格式和樣式。
• Create completely new spreadsheets.
  創(chuàng)建全新的電子表格。
• Use any Google Sheets function, toolbar, menu, or feature.
  使用任何Google表格功能，工具欄，菜單或功能。

The sky is the limit.

天空才是極限。

如何在Google表格中記錄宏 (How to Record a Macro in Google Sheets)

Fire up a Google Sheet and click Tools > Macros > Record Macro.

啟動Google表格，然后單擊工具>宏>記錄宏。

This opens the recording menu at the bottom of the window, with two options for capturing your actions:

這將打開窗口底部的“錄制”菜單，其中有兩個用于捕獲動作的選項：

• Absolute References:?The macro will do tasks only on the exact cells you record. If you italicize cell B1, the macro will only ever italicize B1 regardless of what cell you clicked on.

  絕對引用：宏將僅在您記錄的確切單元格上執(zhí)行任務(wù)。 如果您將單元格B1變?yōu)樾斌w，則無論您單擊哪個單元格，宏都只會將其斜體為B1。

• Relative References:??The macro will do tasks on the selected cells, regardless of where they are in the sheet. If you italicize B1 and C1, you can re-use the same macro to italicize cells D1 and E1 later.

  相關(guān)參考：宏將在選定的單元格上執(zhí)行任務(wù)，無論它們在表中的位置如何。 如果將B1和C1斜體化，則可以在以后重新使用同一宏來斜體化單元格D1和E1。

Select whether you want an absolute or relative reference, then you can start to click, format, and teach Sheets what order you want these actions to replicate.

選擇您要的是絕對參考還是相對參考，然后可以單擊，格式化和教導(dǎo)Sheets您希望這些操作復(fù)制的順序。

After you’ve captured all the actions for this macro, click “Save.”

在捕獲了該宏的所有動作之后，單擊“保存”。

Enter a name for your macro.?Google also lets you create shortcuts for up to?ten?macros. If?you want to bind a macro to a keyboard shortcut, enter a number from 0-9 in the space provided. When you finish, click “Save.”

輸入宏的名稱。 Google還允許您創(chuàng)建最多?十個宏如果要將宏綁定到鍵盤快捷鍵，請在提供的空格中輸入0-9之間的數(shù)字。 完成后，單擊“保存”。

If you need to tweak your macro’s name or shortcut, you can edit a macro by clicking Tools > Macros > Manage Macros.

如果需要調(diào)整宏的名稱或快捷方式，可以通過單擊工具>宏>管理宏來編輯宏。

From the window that opens, tweak as desired and then click “Update.”

在打開的窗口中，根據(jù)需要進行調(diào)整，然后單擊“更新”。

The next time you press the shortcut associated with the macro, it will run without having to open the macro menu from the toolbar.

下次您按下與宏關(guān)聯(lián)的快捷方式時，它將運行，而無需從工具欄打開宏菜單。

如何在Google表格中運行宏 (How to Run a Macro in Google Sheets)

If your macro is an absolute reference, you can run the macro by pressing the keyboard shortcut or go to?Tools > Macros > Your Macro and then click the appropriate option.

如果您的宏是絕對引用，則可以通過按鍵盤快捷鍵或轉(zhuǎn)到“工具”>“宏”>“您的宏”，然后單擊適當?shù)倪x項來運行宏。

Otherwise, if your macro is a relative reference, highlight the cells in your spreadsheet on which you want the macro to run and then press the corresponding shortcut, or click on it from Tools > Macros > Your Macro.

否則，如果您的宏是相對引用，請突出顯示電子表格中您要在其上運行宏的單元格，然后按相應(yīng)的快捷方式，或者從“工具”>“宏”>“您的宏”中單擊它。

如何導(dǎo)入宏 (How to Import Macros)

As mentioned earlier, when you record a macro, it gets bound to the spreadsheet on which you recorded it. But what if you want to import a macro from another spreadsheet??While it’s not a straightforward and simple task, you can do it using this little workaround.

如前所述，當您記錄宏時，它會綁定到記錄宏的電子表格。 但是，如果要從另一個電子表格導(dǎo)入宏該怎么辦？ 盡管這不是一項簡單明了的任務(wù)，但是您可以使用此小解決方法來完成。

Because recorded macros are stored as functions in Google Apps Script, to import a macro, you need to copy the function and then paste it in the new sheet’s macro file.

由于錄制的宏是作為函數(shù)存儲在Google Apps腳本中的，因此要導(dǎo)入宏，您需要復(fù)制該函數(shù)，然后將其粘貼到新工作表的宏文件中。

Open the Google Sheet with the macro you want to copy and then click on Tools > Macros > Manage Macros.

使用您要復(fù)制的宏打開Goog??le表格，然后單擊工具>宏>管理宏。

Next, click the “More” icon next to the macro you’d like to copy and then click “Edit Script.”

接下來，點擊您要復(fù)制的宏旁邊的“更多”圖標，然后點擊“編輯腳本”。

All macros save to the same file, so if you have a couple of macros saved, you may have to sift through them.?The function’s name is the same one you gave it when you created it.

所有宏都保存到同一個文件中，因此，如果您保存了幾個宏，則可能必須在它們之間進行篩選。 該函數(shù)的名稱與您創(chuàng)建函數(shù)時使用的名稱相同。

Highlight the macro(s) you want to copy, then press Ctrl + C.?Be sure to copy everything up to and including the closing semi-colon.

突出顯示要復(fù)制的宏，然后按Ctrl +C。確保將所有內(nèi)容復(fù)制到包括分號在內(nèi)的所有內(nèi)容。

Now, open the other spreadsheet you’ll be importing the macro to and click Tools > Macros > Record Macro.

現(xiàn)在，打開要將宏導(dǎo)入到的其他電子表格，然后單擊工具>宏>記錄宏。

Immediately click “Save” without recording any actions to create a placeholder function in the sheet’s macro file for us. You’ll be deleting this a little later.

立即單擊“保存”，而無需進行任何操作即可在工作表的宏文件中為我們創(chuàng)建占位符功能。 您稍后會刪除它。

Click “Save” again.

再次單擊“保存”。

Open Google Apps Script by clicking Tools > Script Editor, and then open the macros.gs file from the left pane. Delete the existing function and then press Ctrl + V to paste in the macro from your other Sheet.

通過單擊工具>腳本編輯器，打開Goog??le Apps腳本，然后從左窗格中打開macros.gs文件。 刪除現(xiàn)有功能，然后按Ctrl + V從其他工作表粘貼到宏中。

Press Ctrl + S to save the script, close the tab, and return to your spreadsheet.

按Ctrl + S保存腳本，關(guān)閉選項卡，然后返回到電子表格。

Your spreadsheet reads the macros.gs file and looks for changes made to it. If a new function is detected, you can use the Import feature to add a macro from another sheet.

您的電子表格將讀取macros.gs文件并查找對其所做的更改。 如果檢測到新功能，則可以使用“導(dǎo)入”功能從另一個工作表添加宏。

Next, click Tools > Macros > Import.

接下來，單擊工具>宏>導(dǎo)入。

Finally, click “Add Function” under the macro you want to add.

最后，在要添加的宏下單擊“添加功能”。

Unfortunately, you will have to bind the macro manually to a keyboard shortcut again. Just follow the instruction previously mentioned, and you’ll be all set to use this macro across multiple sheets.

不幸的是，您將不得不再次將宏手動綁定到鍵盤快捷鍵。 只要按照前面提到的說明進行操作，就可以在多個工作表上使用此宏。

---

---

That’s all there is to creating and using macros in Google Sheets. There’s no limit to the routines you can create for your spreadsheets to limit the amount of time you spend doing these repetitive tasks.

這就是在Google表格中創(chuàng)建和使用宏的全部內(nèi)容。 您可以為電子表格創(chuàng)建的例程沒有限制，以限制您花費在執(zhí)行這些重復(fù)任務(wù)上的時間。

翻譯自: https://www.howtogeek.com/413334/how-to-automate-google-sheets-with-macros/

谷歌表格

總結(jié)

以上是生活随笔為你收集整理的谷歌表格_如何使用宏自动执行Google表格的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。