在 Excel 中建立自訂函數

雖然 Excel 包含許多內建的工作表函數,但對於您執行的每一種計算類型而言,可能不會有任何函數。 Excel 的設計工具可能無法預見到每個使用者的計算需求。 相反地,Excel 會提供建立自訂函數的功能,本文將說明這些功能。

自訂函數(例如宏),請使用Visual Basic For Applications (VBA)程式設計語言。 它們與宏有兩個顯著的不同。 首先,它們會使用函數程式,而不是Sub程式。 也就是說,它們的開頭是函數語句,而不是Sub語句,而是 end函數,而不是end Sub。 其次,它們會執行計算,而不是採取動作。 某些類型的語句(例如選取及格式化範圍的語句)會從自訂函數中排除。 在本文中,您將學習如何建立及使用自訂函數。 若要建立函數和宏,您可以使用 [ Visual Basic 編輯器] (VBE),在新視窗中開啟並從 Excel 分離。

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

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

不含自訂函數的 [範例訂單] 表單

若要在此活頁簿中建立自訂折扣功能,請執行下列步驟:

  1. Alt + F11以開啟 [Visual Basic 編輯器] (在 Mac 上,按下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

現在,您可以開始使用新的折扣函式了。 關閉 [Visual Basic 編輯器],選取 [儲存格 G7],然後輸入下列內容:

= 折扣(D7,E7)

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

在您 VBA 程式碼的第一行中,[函數折扣(數量,價格)] 表示折扣函式需要兩個引數、數量價格。 當您在工作表儲存格中呼叫函數時,必須包含這兩個引數。 在公式 = 折扣(D7,E7),D7 是 [數量] 引數,而 E7 是 [ price ] 引數。 現在,您可以將折扣公式複製至 G8: G13,以取得如下所示的結果。

讓我們來考慮 Excel 如何轉譯此函數程式。 當您按下enter時,Excel 會在目前的活頁簿中尋找 [名稱折扣],然後發現它是 VBA 模組中的自訂函數。 以括弧、[數量] 和 [價格] 括住的引數名稱,是打折計算所依據之值的預留位置。

含自訂函數的 [範例訂單] 表單

下列程式碼塊中的 If 語句會檢查數量引數,並判斷售出的專案數是否大於或等於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 語句稱為賦值語句,因為它會評估等號右側的運算式,並將結果指派給左側的變數名稱。 因為可變折扣與函數程式的名稱相同,所以儲存在變數中的值會傳回給呼叫折扣函式的工作表公式。

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA 沒有 ROUND 函數,但 Excel 卻如此。 因此,若要在此語句中使用 ROUND,您可以告訴 VBA 在 Application 物件(Excel)中尋找 Round 方法(函數)。 您可以在輪斷字之前新增 word應用程式來執行此動作。 每當您需要從 VBA 模組存取 Excel 函數時,請使用此語法。

自訂函數必須以函數語句開頭,並以 End 函數語句結束。 除了函數名稱,函數語句通常指定一個或多個引數。 不過,您可以建立不含引數的函數。 Excel 包含數個內建函數(例如 RAND 和 NOW),不使用引數。

依照函數語句,函數程式會包含一或多個 VBA 語句,這些語句使用傳遞至函數的引數進行決策並執行計算。 最後,函數程式中的某個位置,您必須加入一個語句,該語句會將值指派給與函數名稱相同的變數。 此值會傳回給呼叫函數的公式。

您可以在自訂函數中使用的 VBA 關鍵字數小於您可以在宏中使用的數位。 除了將值傳回工作表中的公式,或是在另一個 VBA 宏或函數中使用的運算式之外,不允許自訂函數執行任何其他動作。 例如,自訂函數無法調整視窗大小、編輯儲存格中的公式,或變更儲存格中文字的字型、色彩或模式選項。 如果您在 function 程式中包含這種類型的 [action] 代碼,函數會傳回 #VALUE! 錯誤。

函數程式可以執行的一個動作(除了執行計算之外)會顯示一個對話方塊。 您可以在自訂函數中使用InputBox語句,作為從執行函數的使用者取得輸入的方式。 您可以使用MsgBox語句作為傳達資訊給使用者的一種方式。 您也可以使用自訂對話方塊,或userform,但這是除了本簡介的範疇以外。

甚至是簡單的宏和自訂函數,都可能難以閱讀。 您可以在批註形式中輸入說明性文字,讓它們更容易理解。 您可以在說明性文字前面加上撇號來新增批註。 例如,下列範例顯示含批註的折扣函數。 新增批註,如此一來,您或其他人就能讓您更容易地在時間階段維持 VBA 程式碼。 如果您需要在將來變更程式碼,您就能更輕鬆地瞭解您最初所做的工作。

含有批註的 VBA 函數範例

撇號會告訴 Excel 忽略同一行右側的所有專案,因此您可以自行或在包含 VBA 程式碼的行右側建立批註。 您可能會開始一個相對較長的程式碼區塊,其中包含說明其整體用途的批註,然後使用內嵌批註來記錄個別語句。

另一種檔宏與自訂函數的方式是提供描述性名稱。 例如,您可以將它命名為MonthLabels ,以更明確地描述宏的用途,而不是為宏標籤命名。 如果您已建立許多程式,尤其是當您建立的程式具有相似但不完全相同的程式時,使用宏和自訂函數的描述性名稱就特別有用。

您如何記錄宏和自訂函數是個人喜好的考慮。 重要的是採用一些檔方法,並始終如一地使用。

若要使用自訂函數,包含您在其中建立函數之模組的活頁簿必須是開啟的。 如果該活頁簿沒有開啟,您會收到 #NAME 嗎? 當您嘗試使用函數時,發生錯誤。 如果您在不同的活頁簿中參照該函數,您必須在函數名稱前面加上函數所駐留的活頁簿名稱。 例如,如果您在名為 personal.xlsb 的活頁簿中建立名為 [打折] 的函數,而且您是從另一個活頁簿呼叫該函數,您必須輸入= personal.xlsb!折扣(),而不只是= [折扣] ()

您可以從 [插入函數] 對話方塊中選取您的自訂函數,儲存一些擊鍵(以及可能的輸入錯誤)。 您的自訂函數會出現在 [使用者定義] 類別中:

[插入函數] 對話方塊

若要讓自訂函數在任何時候都能使用,更簡單的方法就是將它們儲存在不同的活頁簿中,然後將該活頁簿另存為增益集。 然後,您就可以在每次執行 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. 在 [增益集] 對話方塊中,選取 [流覽] 按鈕來尋找您的增益集,按一下 [開啟],然後選取 [現有的增益集] 方塊中增益集旁的方塊。

完成這些步驟後,您的自訂函數就會在您每次執行 Excel 時使用。 如果您想要新增至函式程式庫,請返回 [Visual Basic 編輯器]。 如果您在 VBAProject 標題底下看到 [Visual Basic 編輯器] 專案資源管理器,您會在增益集檔案後看到一個名為的模組。 您的增益集將擁有副檔名 xlam。

VBE 中的命名模組

在專案資源管理器中按兩下該模組,就會使 [Visual Basic 編輯器] 顯示您的函數代碼。 若要新增函數,請將插入點放在終止程式碼視窗中最後一個函數的 End 函數語句之後,然後開始輸入。 您可以以這種方式建立您所需的許多函數,且在 [插入函數] 對話方塊中的 [使用者定義] 類別中,這些函數永遠都可以使用。

此內容原本是透過標記減淡及 Craig Stinson,將其作為書籍中Microsoft Office Excel 2007的一部分來撰寫。 已更新為適用于更新版本的 Excel。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×