在 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 上建議新功能或增強功能。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×