Skip to main content

了解如何使用Excel宏自動完成繁瑣的任務

了解如何使用Excel宏自動完成繁瑣的任務

Geoffrey Carr

Excel中較強大但很少使用的功能之一是能夠在宏中輕鬆創建自動化任務和自定義邏輯。宏提供了一種理想的方法,可以節省可預測的重複性任務的時間,並標準化文檔格式 - 多次無需編寫單行代碼。

如果您很好奇宏是什麼或如何實際創建它們,沒問題 - 我們將引導您完成整個過程。

注意:相同的過程應該適用於大多數版本的Microsoft Office。屏幕截圖可能略有不同。

什麼是宏?

Microsoft Office宏(因為此功能適用於多個MS Office應用程序)只是保存在文檔中的Visual Basic for Applications(VBA)代碼。對於類似的類比,將文檔視為HTML,將宏視為Javascript。與Javascript可以在網頁上操作HTML的方式大致相同,宏可以操作文檔。

宏是非常強大的,可以做你想像中可以想到的任何東西。作為(非常)簡短的函數列表,您可以使用宏:

  • 應用樣式和格式。
  • 處理數據和文本。
  • 與數據源(數據庫,文本文件等)通信。
  • 創建全新的文檔。
  • 任何上述任何順序的任何組合。

創建宏:按示例說明

我們從您的花園種類CSV文件開始。這裡沒什麼特別的,只有一個10到20的數字,介於0和100之間,同時有一個行和列標題。我們的目標是生成格式良好,可顯示的數據表,其中包括每行的匯總總數。

如上所述,宏是VBA代碼,但Excel的一個好處是你可以創建/記錄它們,只需要編碼 - 就像我們在這裡做的那樣。

要創建宏,請轉到視圖>宏>記錄宏。

為宏指定一個名稱(無空格),然後單擊“確定”。

完成後, 所有 記錄您的操作 - 每個單元格更改,滾動操作,窗口調整大小,您為其命名。

有幾個地方表明Excel是記錄模式。一種是通過查看“宏”菜單並註意“停止錄製”已替換“錄製宏”選項。

另一個是在右下角。 “停止”圖標表示它處於微距模式,按此處將停止錄製(同樣,當不處於錄製模式時,此圖標將是“錄製宏”按鈕,您可以使用該按鈕而不是進入“宏”菜單)。

現在我們正在記錄我們的宏,讓我們應用我們的匯總計算。首先添加標題。

接下來,分別應用適當的公式:

  • = SUM(B2:K2)
  • = AVERAGE(B2:K2)
  • = MIN(B2:K2)
  • = MAX(B2:K2)
  • = MEDIAN(B2:K2)

現在,突出顯示所有計算單元格並拖動所有數據行的長度,以將計算應用於每一行。

完成此操作後,每行應顯示其各自的摘要。

現在,我們想要獲取整個工作表的摘要數據,因此我們再應用一些計算:

分別:

  • = SUM(L2:L21)
  • =平均(B2:K21)*這必須在所有數據中計算,因為行平均值的平均值不一定等於所有值的平均值。
  • = MIN(N2:N21)
  • = MAX(O2:O21)
  • = MEDIAN(B2:K21) *出於與上述相同的原因計算所有數據。

現在計算已完成,我們將應用樣式和格式。首先通過執行全選(Ctrl + A或單擊行標題和列標題之間的單元格)在所有單元格中應用常規數字格式,然後選擇主菜單下的“逗號樣式”圖標。

接下來,對行標題和列標題應用一些可視格式:

  • 膽大。
  • 居中。
  • 背景填充顏色。

最後,將一些樣式應用於總計。

完成所有操作後,這就是我們的數據表:

由於我們對結果感到滿意,因此請停止錄製宏。

恭喜 - 您剛剛創建了一個Excel宏。

為了使用我們新錄製的宏,我們必須以支持宏的文件格式保存我們的Excel工作簿。但是,在我們這樣做之前,我們首先需要清除所有現有數據,以便它不會嵌入到我們的模板中(我們每次使用此模板時都會想到,我們會導入最新的數據)。

為此,請選擇所有單元格並將其刪除。

現在數據已清除(但宏仍包含在Excel文件中),我們希望將文件另存為宏啟用模板(XLTM)文件。重要的是要注意,如果將其保存為標準模板(XLTX)文件,那麼宏將會 能夠從它運行。或者,您可以將文件另存為舊模板(XLT)文件,這將允許運行宏。

將文件另存為模板後,請繼續並關閉Excel。

使用Excel宏

在介紹我們如何應用這個新錄製的宏之前,重要的是要涵蓋一般關於宏的幾點:

  • 宏可能是惡意的。
  • 見上文。

VBA代碼實際上非常強大,可以處理當前文檔範圍之外的文件。例如,宏可以更改或刪除“我的文檔”文件夾中的隨機文件。因此,確保您這一點非常重要 只要 從受信任的來源運行宏。

要使用我們的數據格式宏,請打開上面創建的Excel模板文件。執行此操作時,假設您啟用了標準安全設置,您將在工作簿的頂部看到一條警告,指出已禁用宏。因為我們信任自己創建的宏,所以單擊“啟用內容”按鈕。

接下來,我們將從CSV導入最新的數據集(這是用於創建宏的工作表的源)。

要完成CSV文件的導入,您可能必須設置一些選項,以便Excel正確解釋它(例如分隔符,標題存在等)。

導入數據後,只需轉到“宏”菜單(在“視圖”選項卡下),然後選擇“查看宏”。

在結果對話框中,我們看到上面記錄的“FormatData”宏。選擇它並單擊“運行”。

一旦運行,您可能會看到光標跳了一會兒,但是就像它一樣,您將看到正在操縱的數據 究竟 正如我們錄製的那樣。完成所有操作後,它應該看起來就像我們的原始 - 除了不同的數據。

尋找引擎蓋:什麼使宏觀工作

正如我們已經多次提到的,宏是由Visual Basic for Applications(VBA)代碼驅動的。當您“記錄”宏時,Excel實際上將您執行的所有操作轉換為其各自的VBA指令。簡單地說 - 您不必編寫任何代碼,因為Excel正在為您編寫代碼。

要查看使宏運行的代碼,請在“宏”對話框中單擊“編輯”按鈕。

打開的窗口顯示創建宏時從我們的操作中記錄的源代碼。當然,您可以編輯此代碼,甚至可以在代碼窗口內完全創建新的宏。雖然本文中使用的錄製操作可能適合大多數需求,但更高度自定義的操作或條件操作將要求您編輯源代碼。

把我們的榜樣更進一步......

假設我們的源數據文件data.csv是由自動進程生成的,該進程始終將文件保存到同一位置(例如C: Data data.csv始終是最新的數據)。打開此文件並導入它的過程也可以很容易地變成一個宏:

  1. 打開包含“FormatData”宏的Excel模板文件。
  2. 記錄名為“LoadData”的新宏。
  3. 使用宏錄製,像往常一樣導入數據文件。
  4. 導入數據後,停止錄製宏。
  5. 刪除所有單元格數據(全選然後刪除)。
  6. 保存更新的模板(請記住使用啟用宏的模板格式)。

完成此操作後,無論何時打開模板,都會有兩個宏 - 一個用於加載我們的數據,另一個用於格式化。

如果您真的想通過一些代碼編輯來解決問題,可以通過複製“LoadData”生成的代碼並將其插入“FormatData”代碼的開頭,輕鬆地將這些操作組合到一個宏中。

下載此模板

為方便起見,我們已經包含了本文中生成的Excel模板以及您可以使用的示例數據文件。

從How-To Geek下載Excel宏模板

Link
Plus
Send
Send
Pin