使用 Microsoft 登入
登入或建立帳戶。
您好:
選取其他帳戶。
您有多個帳戶
選擇您要用來登入的帳戶。

雖然 Excel 包含多種內建工作表函數,但它很可能沒有您執行之每種計算類型的函數。 Excel 的設計者不可能預期每個用戶的計算需求。 Excel 反而提供您建立自定義函數的功能,如本文所述。

自定義函數,例如宏,會使用 Visual Basic for Applications (VBA) 程式設計語言。 它們與宏有兩種顯著的方式不同。 首先,他們使用 函數 程式,而非 程式。 也就是說,它們的開頭是 函數 語句,而不是 Sub 語句,結尾是 End 函 數,而不是 End Sub。 其次,他們會執行計算,而不是採取動作。 某些類型的語句,例如選取範圍和格式化範圍的語句,會排除在自定義函數之外。 在本文中,您將瞭解如何建立和使用自定義函數。 若要建立函數和宏,您可以使用 Visual Basic 編輯器 (VBE) ,此編輯器會在與 Excel 分開的新視窗中開啟。

假設您的公司在產品銷售時提供 10% 的數量折扣,但訂單超過 100 份。 在下列段落中,我們將示範計算此折扣的函數。

以下範例顯示訂單表單,其中列出每個專案、數量、價格、折扣 (如果有) ,以及產生的延伸價格。

沒有自定義函數的範例順序窗體

若要在此活頁簿中建立自定義 DISCOUNT 函數,請遵循下列步驟:

  1. Alt+F11 以在 Mac 上開啟 Visual Basic 編輯器 (、按 FN+ALT+F11) ,然後按兩下 [ 插入 > 模組]。 新的模組視窗會顯示在 Visual Basic 編輯器的右側。

  2. 將下列程式代碼複製並貼到新模組。

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

附註: 若要讓程式代碼更易於閱讀,您可以使用 Tab 鍵來縮排行。 縮排僅適用於您的權益,且為選用,因為程式代碼會搭配或不隨之執行。 輸入縮排線條之後,Visual Basic 編輯器會假設您的下一行會同樣縮排。 若要移出 (,請在左) 一個製表符,按 Shift+Tab

現在您已準備好使用新的 DISCOUNT 函數。 關閉 Visual Basic 編輯器,選取儲存格 G7,然後輸入下列內容:

=DISCOUNT (D7,E7)

Excel 會將 200 單位的 10% 折扣計算為每單位 $47.50,並傳回 $950.00。

在 VBA 程式代碼的第一行中,函數 DISCOUNT (數量、價格) ,表示 DISCOUNT 函數需要兩個自 變數:數量價格。 當您在工作表單元格中呼叫函數時,必須包含這兩個自變數。 在公式 =DISCOUNT (D7,E7) 中,D7 是 quantity 自變數,而 E7 是 price 自變數。 現在您可以將 DISCOUNT 公式複製到 G8:G13,以取得以下所示的結果。

讓我們考慮一下 Excel 如何解譯此函數程式。 當您按 Enter 時,Excel 會尋找目前活頁 簿中的 DISCOUNT 名稱,並發現它是 VBA 模組中的自定義函數。 以括弧、 數量價格括住的自變數名稱,是折扣計算依據之值的佔位符。

含自定義函數的訂單窗體範例

下列程式代碼區塊中的 If 語句會檢查 quantity 自變數,並判斷售出的項目數量是否大於或等於 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

如果售出的項目數量大於或等於 100,VBA 會執行下列語句,將 數量 值乘以 價格 值,然後將結果乘以 0.1:

Discount = quantity * price * 0.1

結果會儲存為變數 折扣。 將值儲存在變數中的 VBA 語句稱為 工作分派 語句,因為它會評估等號右側的運算式,並將結果指派給左邊的變數名稱。 由於變數 Discount 與函數程式的名稱相同,因此儲存在變數中的值會回到稱為 DISCOUNT 函數的工作表公式。

如果 quantity 小於 100,VBA 會執行下列語句:

Discount = 0

最後,下列語句會將指派給 折扣 變數的值四捨五入至兩位小數字數:

Discount = Application.Round(Discount, 2)

VBA 沒有 ROUND 函數,但 Excel 沒有。 因此,若要在本語句中使用 ROUND,您必須要求 VBA 在 Excel) (Application 物件中尋找 Round 方法 (函數) 。 您可以在 [四捨五入] 文字前新增 [ 應用程式 ] 這個字。 每當您需要從 VBA 模組存取 Excel 函數時,請使用此語法。

自定義函數必須以函數語句開頭,並以 End 函數語句做為結尾。 除了函數名稱之外,函數語句通常也會指定一或多個自變數。 不過,您可以建立沒有自變數的函數。 Excel 包含數個不使用自變數的內建函數,例如 RAND 和 NOW。

在函數語句之後,函數程式包含一或多個 VBA 語句,可使用傳遞至函數的自變數來做出決策並執行計算。 最後,在函數程式的某個位置,您必須包含一個語句,將值指派給與函數同名的變數。 此值會傳回呼叫函數的公式。

您可以在自訂函數中使用的 VBA 關鍵詞數目小於可在宏中使用的數位。 自定義函數除了傳回值至工作表中的公式,或傳回其他 VBA 宏或函數中使用的運算式之外,不得執行其他動作。 例如,自定義函數無法調整視窗大小、編輯儲存格中的公式,或變更儲存格中文字的字型、色彩或圖樣選項。 如果您在函數程式中包含這類「動作」程序代碼,函數會傳回 #VALUE! 錯誤。

函數程式除了執行計算之外, (可以執行的動作) 顯示對話方塊。 您可以在自定義函數中使用 InputBox 語句,做為從執行函數的使用者取得輸入的一種方式。 您可以使用 MsgBox 語句來向使用者傳達資訊。 您也可以使用自定義對話框或 UserForm,但這是本簡介範圍之外的主題。

即使是簡單的宏和自定義函數也很難閱讀。 您可以以批註的形式輸入說明文字,讓它們更容易理解。 您可以在說明文字前面加上單引號來新增批注。 例如,下列範例顯示含有批注的 DISCOUNT 函數。 新增類似批注,可讓您或其他人在時間經過時更輕鬆地維護您的 VBA 程序代碼。 如果您未來需要變更程式碼,您將能更輕鬆地瞭解您原本所做的內容。

含批注的 VBA 函數範例

單引號可讓 Excel 忽略同一行右邊的所有專案,因此您可以在包含 VBA 程式代碼的行右側,自行建立批注。 您可能會以解釋其整體用途的批注開始一個很長的程式代碼區塊,然後使用內嵌批注來記錄個別的陳述。

另一個記錄宏和自定義函數的方式是為它們提供描述性的名稱。 例如,您可以將 MonthLabels 命名為 MonthLabels,以更明確描述宏所提供的目的,而不是將宏命名為 [捲]。 當您建立許多程式時,尤其是當您建立具有相似但不完全相同的程式時,對宏和自定義函數使用描述性名稱特別有説明。

記錄宏和自定義函數的方式是個人喜好設定的問題。 重要的是採用一些檔方法,並持續使用。

若要使用自定義函數,必須開啟包含您建立函數之模組的活頁簿。 如果該活頁簿未開啟,您會收到 #NAME? 錯誤。 如果您在其他活頁簿中參照函數,則必須在函數名稱前面加上函數所在活頁簿的名稱。 例如,如果您在名為 Personal.xlsb 的活頁簿中建立名為 DISCOUNT 的函數,而您從另一個活頁簿呼叫該函數,則必須輸入 =personal.xlsb!discount () ,而不只是 =discount ()

您可以從 [插入函數] 對話框中選取您的自定義函數, (以及可能的輸入錯誤) 儲存您自己。 您的自訂函數會顯示在 [使用者定義] 類別中:

[插入函數] 對話方塊

將自定義函數儲存在個別的活頁簿中,然後將該活頁簿另存為載入宏,是讓您隨時都能使用自定義函數的簡單方法。 您可以在每次執行 Excel 時提供載入宏。 方法如下:

  1. 建立所需的函數之後,請按兩下 [ 檔案 > 另存新檔]

    在 Excel 2007中,按兩下 [Microsoft Office 按鈕],然後按兩下 [ 另存新檔]

  2. 在 [ 另存新 檔] 對話框中,開啟 [ 存盤類型 ] 下拉式清單,然後選取 [Excel 載入宏]。 將活頁簿儲存在可辨識的名稱底下,例如 [MyFunctions],並儲存在 [AddIns ] 資料夾中。 [ 另存新 檔] 對話框會建議該資料夾,因此您只需要接受預設位置即可。

  3. 儲存活頁簿之後,按兩下 [ 檔案 > Excel選項]

    在 Excel 2007中,按兩下 [Microsoft Office 按鈕],然後按兩下 [Excel 選項]

  4. 在 [ Excel 選項] 對話框中,按兩下 [載入宏] 類別。

  5. 在 [ 管理 ] 下拉式清單中,選取 [Excel 載入宏]。 然後按下 [ 前往] 按鈕。

  6. 在 [ 載入宏 ] 對話框中,選取您用來儲存活頁簿之名稱旁邊的複選框,如下所示。

    [增益集] 對話方塊

  1. 建立所需的函數之後,請按兩下 [ 檔案 > 另存新檔]

  2. 在 [ 另存新 檔] 對話框中,開啟 [ 存盤類型 ] 下拉式清單,然後選取 [Excel 載入宏]。 將活頁簿儲存為可辨識的名稱,例如 MyFunctions

  3. 儲存活頁簿之後,按兩下 [ 工具 ] > Excel載入宏]

  4. 在 [ 載入 宏] 對話框中,選取 [流覽] 按鈕以尋找您的載入宏,按兩下 [ 啟],然後在 [ 可用的載入 宏] 方塊中核取 Add-In 旁邊的方塊。

遵循這些步驟之後,每次執行 Excel 時,您都可以使用自定義函數。 如果您想要新增到函數庫,請返回 Visual Basic 編輯器。 如果您在 [Visual Basic 編輯器專案總管] 的 VBAProject標題底下查看,您會看到以載入宏檔案命名的模組。 您的載入宏將具有擴展名 .xlam。

VBE 中的命名模組

按兩下專案總管中的該模組,可讓Visual Basic編輯器顯示您的功能代碼。 若要新增函數,請將插入點置於終止程式碼視窗中最後一個函數的 End 函數語句後面,然後開始輸入。 您可以視需要以這種方式建立任意數量的函數,這些函數一律可在 [ 插入函 數] 對話方塊的 [使用者定義] 類別中使用。

此內容原本是由 Mark Defendere 和權貴使用者為 Microsoft Office Excel 2007 Inside Out 書籍的一部分所撰寫。 自此之後,也已更新為套用至較新版本的Excel。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家,或在社群中取得支援。

需要更多協助嗎?

想要其他選項嗎?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。

這項資訊有幫助嗎?

您對語言品質的滿意度如何?
以下何者是您會在意的事項?
按下 [提交] 後,您的意見反應將用來改善 Microsoft 產品與服務。 您的 IT 管理員將能夠收集這些資料。 隱私權聲明。

感謝您的意見反應!

×