雖然 Excel 包含多種內建工作表函數,但它很可能沒有您執行之每種計算類型的函數。 Excel 的設計者不可能預期每個用戶的計算需求。 Excel 反而提供您建立自定義函數的功能,如本文所述。
自定義函數,例如宏,會使用 Visual Basic for Applications (VBA) 程式設計語言。 它們與宏有兩種顯著的方式不同。 首先,他們使用 函數 程式,而非 子 程式。 也就是說,它們的開頭是 函數 語句,而不是 Sub 語句,結尾是 End 函 數,而不是 End Sub。 其次,他們會執行計算,而不是採取動作。 某些類型的語句,例如選取範圍和格式化範圍的語句,會排除在自定義函數之外。 在本文中,您將瞭解如何建立和使用自定義函數。 若要建立函數和宏,您可以使用 Visual Basic 編輯器 (VBE) ,此編輯器會在與 Excel 分開的新視窗中開啟。
假設您的公司在產品銷售時提供 10% 的數量折扣,但訂單超過 100 份。 在下列段落中,我們將示範計算此折扣的函數。
以下範例顯示訂單表單,其中列出每個專案、數量、價格、折扣 (如果有) ,以及產生的延伸價格。
若要在此活頁簿中建立自定義 DISCOUNT 函數,請遵循下列步驟:
-
按 Alt+F11 以在 Mac 上開啟 Visual Basic 編輯器 (、按 FN+ALT+F11) ,然後按兩下 [ 插入 > 模組]。 新的模組視窗會顯示在 Visual Basic 編輯器的右側。
-
將下列程式代碼複製並貼到新模組。
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。
現在您已準備好使用新的 DISCOUNT 函數。 關閉 Visual Basic 編輯器,選取儲存格 G7,然後輸入下列內容:
=DISCOUNT (D7,E7)
Excel 會將 200 單位的 10% 折扣計算為每單位 $47.50,並傳回 $950.00。
在 VBA 程式代碼的第一行中,函數 DISCOUNT (數量、價格) ,表示 DISCOUNT 函數需要兩個自 變數:數量 和 價格。 當您在工作表單元格中呼叫函數時,必須包含這兩個自變數。 在公式 =DISCOUNT (D7,E7) 中,D7 是 quantity 自變數,而 E7 是 price 自變數。 現在您可以將 DISCOUNT 公式複製到 G8:G13,以取得以下所示的結果。
讓我們考慮一下 Excel 如何解譯此函數程式。 當您按 Enter 時,Excel 會尋找目前活頁 簿中的 DISCOUNT 名稱,並發現它是 VBA 模組中的自定義函數。 以括弧、 數量 和 價格括住的自變數名稱,是折扣計算依據之值的佔位符。
下列程式代碼區塊中的 If 語句會檢查 quantity 自變數,並判斷售出的項目數量是否大於或等於 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 語句稱為 工作分派 語句,因為它會評估等號右側的運算式,並將結果指派給左邊的變數名稱。 由於變數 Discount 與函數程式的名稱相同,因此儲存在變數中的值會回到稱為 DISCOUNT 函數的工作表公式。
如果 quantity 小於 100,VBA 會執行下列語句:
Discount = 0
最後,下列語句會將指派給 折扣 變數的值四捨五入至兩位小數字數:
Discount = Application.Round(Discount, 2)
VBA 沒有 ROUND 函數,但 Excel 沒有。 因此,若要在本語句中使用 ROUND,您必須要求 VBA 在 Excel) (Application 物件中尋找 Round 方法 (函數) 。 您可以在 [四捨五入] 文字前新增 [ 應用程式 ] 這個字。 每當您需要從 VBA 模組存取 Excel 函數時,請使用此語法。
自定義函數必須以函數語句開頭,並以 End 函數語句做為結尾。 除了函數名稱之外,函數語句通常也會指定一或多個自變數。 不過,您可以建立沒有自變數的函數。 Excel 包含數個不使用自變數的內建函數,例如 RAND 和 NOW。
在函數語句之後,函數程式包含一或多個 VBA 語句,可使用傳遞至函數的自變數來做出決策並執行計算。 最後,在函數程式的某個位置,您必須包含一個語句,將值指派給與函數同名的變數。 此值會傳回呼叫函數的公式。
您可以在自訂函數中使用的 VBA 關鍵詞數目小於可在宏中使用的數位。 自定義函數除了傳回值至工作表中的公式,或傳回其他 VBA 宏或函數中使用的運算式之外,不得執行其他動作。 例如,自定義函數無法調整視窗大小、編輯儲存格中的公式,或變更儲存格中文字的字型、色彩或圖樣選項。 如果您在函數程式中包含這類「動作」程序代碼,函數會傳回 #VALUE! 錯誤。
函數程式除了執行計算之外, (可以執行的動作) 顯示對話方塊。 您可以在自定義函數中使用 InputBox 語句,做為從執行函數的使用者取得輸入的一種方式。 您可以使用 MsgBox 語句來向使用者傳達資訊。 您也可以使用自定義對話框或 UserForm,但這是本簡介範圍之外的主題。
即使是簡單的宏和自定義函數也很難閱讀。 您可以以批註的形式輸入說明文字,讓它們更容易理解。 您可以在說明文字前面加上單引號來新增批注。 例如,下列範例顯示含有批注的 DISCOUNT 函數。 新增類似批注,可讓您或其他人在時間經過時更輕鬆地維護您的 VBA 程序代碼。 如果您未來需要變更程式碼,您將能更輕鬆地瞭解您原本所做的內容。
單引號可讓 Excel 忽略同一行右邊的所有專案,因此您可以在包含 VBA 程式代碼的行右側,自行建立批注。 您可能會以解釋其整體用途的批注開始一個很長的程式代碼區塊,然後使用內嵌批注來記錄個別的陳述。
另一個記錄宏和自定義函數的方式是為它們提供描述性的名稱。 例如,您可以將 MonthLabels 命名為 MonthLabels,以更明確描述宏所提供的目的,而不是將宏命名為 [捲標]。 當您建立許多程式時,尤其是當您建立具有相似但不完全相同的程式時,對宏和自定義函數使用描述性名稱特別有説明。
記錄宏和自定義函數的方式是個人喜好設定的問題。 重要的是採用一些檔方法,並持續使用。
若要使用自定義函數,必須開啟包含您建立函數之模組的活頁簿。 如果該活頁簿未開啟,您會收到 #NAME? 錯誤。 如果您在其他活頁簿中參照函數,則必須在函數名稱前面加上函數所在活頁簿的名稱。 例如,如果您在名為 Personal.xlsb 的活頁簿中建立名為 DISCOUNT 的函數,而您從另一個活頁簿呼叫該函數,則必須輸入 =personal.xlsb!discount () ,而不只是 =discount () 。
您可以從 [插入函數] 對話框中選取您的自定義函數, (以及可能的輸入錯誤) 儲存您自己。 您的自訂函數會顯示在 [使用者定義] 類別中:
將自定義函數儲存在個別的活頁簿中,然後將該活頁簿另存為載入宏,是讓您隨時都能使用自定義函數的簡單方法。 您可以在每次執行 Excel 時提供載入宏。 方法如下:
-
建立所需的函數之後,請按兩下 [ 檔案 > 另存新檔]。
在 Excel 2007中,按兩下 [Microsoft Office 按鈕],然後按兩下 [ 另存新檔]
-
在 [ 另存新 檔] 對話框中,開啟 [ 存盤類型 ] 下拉式清單,然後選取 [Excel 載入宏]。 將活頁簿儲存在可辨識的名稱底下,例如 [MyFunctions],並儲存在 [AddIns ] 資料夾中。 [ 另存新 檔] 對話框會建議該資料夾,因此您只需要接受預設位置即可。
-
儲存活頁簿之後,按兩下 [ 檔案 > Excel選項]。
在 Excel 2007中,按兩下 [Microsoft Office 按鈕],然後按兩下 [Excel 選項]。
-
在 [ Excel 選項] 對話框中,按兩下 [載入宏] 類別。
-
在 [ 管理 ] 下拉式清單中,選取 [Excel 載入宏]。 然後按下 [ 前往] 按鈕。
-
在 [ 載入宏 ] 對話框中,選取您用來儲存活頁簿之名稱旁邊的複選框,如下所示。
-
建立所需的函數之後,請按兩下 [ 檔案 > 另存新檔]。
-
在 [ 另存新 檔] 對話框中,開啟 [ 存盤類型 ] 下拉式清單,然後選取 [Excel 載入宏]。 將活頁簿儲存為可辨識的名稱,例如 MyFunctions。
-
儲存活頁簿之後,按兩下 [ 工具 ] > Excel載入宏]。
-
在 [ 載入 宏] 對話框中,選取 [流覽] 按鈕以尋找您的載入宏,按兩下 [ 開啟],然後在 [ 可用的載入 宏] 方塊中核取 Add-In 旁邊的方塊。
遵循這些步驟之後,每次執行 Excel 時,您都可以使用自定義函數。 如果您想要新增到函數庫,請返回 Visual Basic 編輯器。 如果您在 [Visual Basic 編輯器專案總管] 的 VBAProject標題底下查看,您會看到以載入宏檔案命名的模組。 您的載入宏將具有擴展名 .xlam。
按兩下專案總管中的該模組,可讓Visual Basic編輯器顯示您的功能代碼。 若要新增函數,請將插入點置於終止程式碼視窗中最後一個函數的 End 函數語句後面,然後開始輸入。 您可以視需要以這種方式建立任意數量的函數,這些函數一律可在 [ 插入函 數] 對話方塊的 [使用者定義] 類別中使用。
此內容原本是由 Mark Defendere 和權貴使用者為 Microsoft Office Excel 2007 Inside Out 書籍的一部分所撰寫。 自此之後,也已更新為套用至較新版本的Excel。
需要更多協助嗎?
您可以隨時詢問 Excel 技術社群中的專家,或在社群中取得支援。