在 Excel 中建立自訂的函數

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

雖然 Excel 包含各種內建的工作表函數,有可能是計算的沒有您所執行的每個類型的函數。Excel 的設計人員可能無法將會計算每位使用者的需求。不過,Excel 會為您提供建立自訂的函數,本文所述的功能。

自訂的函數,例如巨集],使用Visual Basic for Applications (VBA)程式設計語言。兩個有效的方式有所不同巨集。首先,這些使用函數程序,而不是Sub程序。就是以開始使用,而不是End Sub結束函數,而非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,並輸入下列內容:

=DISCOUNT(D7,E7)

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

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

我們來看看 Excel 如何解譯此函數程序。當您按下enter 鍵時,Excel 就會看起來的折扣中目前的活頁簿的名稱,並找到它是在 VBA 模組中的自訂函數。引數名稱的括號內,數量價格,是折扣的版面配置區計算所根據的值。

使用自訂的函數的範例訂單表單

如果在下列程式碼區塊陳述式檢查數量引數,並決定是否大於或等於 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 此陳述式中,您會告訴您要的應用程式物件 (Excel) 中的圓形方法 (函數) 的 VBA。您新增字詞之前的單字應用程式執行的圓形。每當您需要從 VBA 模組存取的 Excel 函數,請使用下列語法。

自訂函數必須開始的函數陳述式,並以結束函數陳述式結尾。除了在函數名稱函數陳述式通常會指定一個或多個引數。不過,您就可以建立函數沒有引數。Excel 中包含數個內建的函數,RAND 現在,例如的不是使用引數。

下列函數陳述式,function 程序包含一或多個 VBA 陳述式的決策,以及執行計算使用傳遞給函數的引數。最後,位置中的函數程序,您必須包含將值設定為使用函數的相同名稱變數的陳述式。此值會傳回通話函數的公式。

您可以使用自訂函數中的 VBA 關鍵字的數字小於數目您可以使用巨集。如何在工作表中的公式或其他 VBA 巨集或函數中使用運算式的值傳回以外的任何項目不允許自訂函數。例如,自訂函數無法調整視窗大小、 編輯在儲存格中,公式或變更字型、 色彩或圖樣選項,在儲存格中的文字。如果您在 function 程序 」 動作 」 這類的程式碼,此函數會傳回 # #VALUE !錯誤。

Function 程序可以執行 (除了執行計算) 上一個動作是顯示的對話方塊。您可以自訂函數中使用InputBox陳述式,做為使用者執行函式的開始輸入。您可以使用MsgBox陳述式的傳達給使用者的資訊。您也可以使用自訂對話方塊或Userform,但這是簡介的範圍之外的主旨。

更簡單的巨集和自訂函數時,可能難以閱讀。您可以使其更容易理解輸入說明文字形式的註解。您可以新增註解藉由使用單引號說明性文字。例如,下列範例顯示註解的折扣函數。新增註解,如下讓您更方便您或其他人維護經過一段時間的 VBA 程式碼。如果您需要在未來的程式碼進行變更,您必須瞭解處理您並未原本更容易時間。

註解的 VBA 函數的範例

單引號會告知 Excel 略過在同一行中,向右的所有項目,讓您可以建立註解行單獨或右側的行包含 VBA 程式碼。您可能會開始超長區塊說明整體用途的註解的程式碼,然後使用 [文件的個別陳述式的內置註解。

您的巨集和自訂函數的文件的另一種方法是賦予其描述性的名稱。例如,而不是標籤巨集的名稱,您可能將其命名MonthLabels描述更明確地說目的巨集是。使用巨集及自訂函數的描述性名稱是非常有用當您建立多個程序,特別是如果您建立具有類似但不是相同的目的的程序。

您如何您的巨集和自訂函數的文件是個人的喜好設定。重要的是採用文件的方法,並使用一致的方式。

若要使用自訂的函數,必須開啟活頁簿包含您所建立之函數的模組。如果無法開啟該活頁簿,您會收到 #NAME 嗎?當您嘗試使用函數的錯誤。如果您參考不同的活頁簿中的函數,您必須在函數存在於活頁簿的函數名稱。例如,如果您建立稱為 Personal.xlsb 活頁簿中名為折扣函數呼叫該函數從另一個活頁簿,您必須輸入=personal.xlsb!discount(),而不是直接=discount()

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

[插入函數] 對話方塊

更簡單的方法,讓您自訂的函數在所有的時間就是將其儲存在不同的活頁簿,然後再將該活頁簿儲存為增益集。您可以使增益集時執行 Excel。以下是如何進行此動作:

  1. 建立您所需要的函數之後,按一下 [檔案>另存新檔

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

  2. 在 [另存新檔] 對話方塊中,開啟 [檔案類型] 下拉式清單中,然後選取 [ Excel 增益集。儲存活頁簿以可辨識的名稱,例如MyFunctions,在 [增益集] 資料夾。[另存新檔] 對話方塊會建議該資料夾,因此您只需要接受預設的位置。

  3. 在您儲存活頁簿後,按一下 [檔案> Excel 選項]

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

  4. 在 [ Excel 選項] 對話方塊中,按一下增益集]類別。

  5. 在 [管理] 下拉式清單中,選取[Excel 增益集。然後按一下 [移至] 按鈕。

  6. 在 [增益集] 對話方塊中,選取您用來儲存活頁簿,如下所示的名稱旁邊的核取方塊。

    [增益集] 對話方塊

  1. 建立您所需要的函數之後,按一下 [檔案>另存新檔

  2. 在 [另存新檔] 對話方塊中,開啟 [檔案類型] 下拉式清單中,然後選取 [ Excel 增益集。儲存在可辨識的名稱,例如MyFunctions活頁簿。

  3. 在您儲存活頁簿後,按一下 [工具] > [Excel 增益集

  4. 在 [增益集] 對話方塊中,選取 [瀏覽] 尋找增益集,按一下 [開啟],然後核取 [增益集] 方塊中您增益集旁的方塊。

請遵循下列步驟之後,您的自訂函數將可供每次啟動 Excel 時。如果您想要新增至您的函數程式庫,會傳回 Visual Basic 編輯器。如果您看 vba 專案] 標題下的 [Visual Basic 編輯器專案總管] 中,您會看到增益集檔案命名模組。增益集會有分機.xlam。

VBE 中的命名模組

按兩下 [專案總管] 中的模組會使 Visual Basic 編輯器,以顯示 [函數程式碼。若要新增新的函數,請結束程式碼] 視窗中的最後一個函數可結束函數陳述式之後將插入點,然後開始輸入。您可以建立許多函數必須以這種方式,以及他們一律會可在 [插入函數] 對話方塊中的 [使用者定義] 類別。

此內容原本是由標記閃避和Microsoft Office Excel 2007 Inside Out其活頁簿的一部分 Craig Stinson 撰寫。由於已套用至較新版本的 Excel 也。

需要更多協助嗎?

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

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×