在 Excel 中建立自訂函數

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

您正在尋找如何建立可在 Windows 版 Excel、Mac 版 excel 或 Excel 網頁版上執行的 JAVAscript 自訂函數的相關資訊嗎? 如果您是的話, 請參閱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 支援專員連絡以深入了解您的意見。

×