在 Excel 中建立自訂函數

附註: 我們想要以您的語言,用最快的速度為您提供最新的說明內容。 本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 希望您能在本頁底部告訴我們這項資訊是否有幫助。 此為英文文章出處,以供參考。

雖然 Excel 包含眾多的內建的工作表函數,可能是,它不需要執行的計算的每個類型的函式。 Excel 的設計工具可能無法將會計算每位使用者的需求。 相反地,Excel 會為您提供建立自訂的函式,這篇文章中所說明的能力。

您是否正在尋找如何建立能在 Windows 版 Excel,Excel for Mac],或 Excel Online 執行 Javascript 自訂函數的詳細資訊? 如果您是,請參閱Excel 自訂函數概觀

自訂函數,例如巨集],使用Visual Basic for Applications (VBA)程式設計語言。 兩個重要的方式有所不同巨集。 首先,他們可以使用函數程序,而不是Sub程序。 也就是說,以開始與結束函式,而End Sub,而非Sub陳述式] 和 [結束的函式陳述式。 第二,它們會執行計算,而不是採取動作。 從自訂函式中排除特定類型的陳述式,例如選取和設定格式的範圍,陳述式。 在本文中,您將學習如何建立及使用自訂的函式。 若要建立函式和巨集,您使用Visual Basic 編輯器 (VBE),獨立於 Excel 在新視窗中開啟。

假設貴公司提供適用於多不要超過 100 單位的數量的產品,提供順序促銷 10%的折扣。 在下列段落,我們會示範函數來計算這個折扣。

下列範例顯示非訂購表單會列出每個項目、 數量、 價格、 折扣 (如果有的話),並產生單項產品總價。

沒有自訂函數的範例訂單表單

若要建立自訂的折扣函數此活頁簿中,請遵循下列步驟:

  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 會計算上 200 單位 $47.50 每單位 10%的折扣,並傳回 $950.00。

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

我們來看看 Excel 如何解譯此函式程序。 當您按下Enter時,Excel 會尋找折扣目前的活頁簿中的名稱,並會找出它是在 VBA 模組中的自訂函數。 括號內, quantityprice括住的引數名稱都是折扣的預留位置計算所根據的值。

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

如果在下列程式碼區塊中的陳述式會檢查數量引數,並判斷售出的項目數字是否大於或等於 100:

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

如果賣出的項目數量是大於或等於 100,VBA 將會執行下列陳述式,這將數量值乘以price值,然後再將結果乘以 0.1:

Discount = quantity * price * 0.1

結果會儲存為折扣變數。 將值儲存在變數中的 VBA 陳述式會呼叫作業陳述式,因為它評估的運算式右側的等號,並將結果指派變數名稱的左側。 折扣變數具有相同名稱的函式程序,因為儲存在變數中的值會傳回呼叫折扣函式的工作表公式中。

如果數量小於 100,VBA 將會執行下列陳述式:

Discount = 0

最後,將下列陳述式會四捨五入至兩位小數的折扣變數指派的值:

Discount = Application.Round(Discount, 2)

VBA 有沒有 ROUND 函數,但 Excel。 因此,若要使用 ROUND 此陳述式中,您會告訴 VBA 尋找應用程式物件 (Excel) 中的圓形方法 (函數)。 您藉由新增該字之前先 word應用程式執行的圓形。 每當您需要從在 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 支援專員連絡以深入了解您的意見。

×