[Excel]如何創建自定義函數

在 Excel 內建函式中找不到自已想要的函式怎麼辦?事實上我們可以自已建一個，以下是一個簡單的Excel自定義函數示例，用於將民國年轉為西元：

Function 民國轉西元(x As Double) As Double

    民國轉西元 = x + 1911

End Function

 

要使用此函數，您需要在Excel中按 `Alt + F11` 打開Visual Basic編輯器。然後，通過在項目窗口中右鍵單擊工作簿名稱並選擇 `插入 > 模塊` 來插入新模塊。將上面的代碼粘貼到新模塊中，如下圖所示。

然後關閉Visual Basic編輯器。現在您可以像使用任何其他內置函數一樣在工作表中使用 `民國轉西元` 函數。例如，如果您在單元格中輸入 `=民國轉西元(100)`，它將返回 `2011`，如下圖所示。

使用限制

使用 VBA 在 Excel 中創建自定義函數有一些限制。自定義函數不能執行更改 Excel 環境的操作。這意味著這樣的函數不能執行以下任何操作：

• 在電子表格上插入、刪除或格式化單元格。
• 更改另一個單元格的值。
• 移動、重命名、刪除或向工作簿添加工作表。
• 更改任何環境選項，例如計算模式或屏幕視圖。
• 向工作簿添加名稱。
• 設置屬性或執行大多數方法。

使用情境

使用 VBA 在 Excel 中創建自定義函數可以滿足許多不同的需求。例如，您可以創建一個函數來執行特定的計算，該計算不包含在 Excel 的內置函數中。這樣，您就可以在工作簿中重複使用該函數，而不必每次都複製和粘貼相同的公式。

此外，您還可以創建一個函數來自動化某些任務，例如從網絡上抓取數據或從其他文件中提取信息。這樣，您就可以更快地完成工作，並減少出錯的可能性。

總之，使用 VBA 在 Excel 中創建自定義函數可以讓您更有效地使用 Excel，並滿足您特定的計算需求。

[ Excel ] 如何使用函式篩選出唯一值

 在 Google 試算表中有提供 UNIQUE 函式，於所指定範圍列中，傳回範圍中的不重複資料列，如同下圖示例。

但在 Excel 雖有可篩選的操作，可是並沒有相對應的函式，如果要用函式的方式處理，就需要繞些遠路。首先要先統計各種類的數量，在此用到的是 COUNTIF 函式，此函式可統計我們指定範圍內，符合我們所要項目的數量，在本例我們應用來取得各別項目的數量計算，語法與執行結果如下圖。

為了要統計各類的各別有的數量，利用錢的符號『 $ 』來定住 COUNTIF 函式作用區間的上緣，而下緣則為目前在的列數，這樣下面如有新增的資料時，只要在函式列下拉即可計算，COUNTIF 計算標的，為當前列的項目於我們指定的上緣到該列時的數量，例如上圖範列的第7例為黑色，在 [ J$2:J7 ] 的範圍內共有2個黑色，而用  COUNTIF (J$2:J7 , J7) 計算出來的結果也為 2 ，這樣做的結果我們會發現，我們相當於在對各個不同的項目各自排序，由上圖可以看到，紅色有3個，分別在第2、3、11列，而統計出來的結果在 K2、K3、K11 為 1、2、3 ，剛好為其排序。

至於為什麼要排序，有了排序我們就可以捉 K 欄中顯示為 1 的列，對應到同列的 J 欄就為不重複的唯一值，在本例 K 欄為 1 的，在 J 欄分別為 紅色、黑色、紫色、黃色、藍色，共有五種顏色，所以接下來的動作，我們依此統計種類數，在N欄中如上面所講的，在 K 欄值為 1時，才進行計數，從而統計出種類數為 5 種顏色。

接下來利用 VLOOKUP 函式來將上述唯一值在 Excel 篩選出，但 VLOOKUP 要求查閱值需在資料範圍的最前列，為此，需將 M 欄的結果，放到 K欄 之前，做為 VLOOKUP 的鍵值，我們直接指定 H 欄 等為 M 欄值，這樣就完成資料範圍的整備。 

接下來要用 VLOOKUP 來搜尋不重複的值，首先要建立查閱值，在前面我們已經知道有 5 種顏色，所以 O 欄的查閱值，原本可以直接輸 1、2、3、4、5，但為了擴充和通用性，我們用 「=IF(O2<MAX(M:M),O2+1,"")」，來進行查閱值的產生，查閱值產生後就可以用 VLOOKUP 函式來找出唯一的值了，如 下圖 Q 欄所示。

為什麼要這樣做

以上為用函式求找唯一值的方式，就如同前面所說，繞了很大一段路，才求得結果，但為什麼要這樣做，應用的情境為何? 就一般來說  Excel 雖有可篩選的操作，但還是需要我們點選操作，才可達成我們所要的目的，用此方法只要將資料貼到你指定的欄位，就會自動求得所以的唯一值，而後我們可以用這結果套入其他函式進行運算，從而得到自動化的結果。

另外  Excel 的篩選操作過程中都會有縮減列數的效果，如果你的欄位是在一個大資料集中，這樣做可能會將其他欄位的資料給刪除，此時要不是複制該欄貼到其他 Sheet 進行操作，就比較適合用此方式進行操作。

結語

就個人的使用習慣上，如果遇到此類操作，會將資料移到 Google 試算表操作，但有的時侯可能是只能用 Excel 操作時，且需要較自動化的操作，個人就會用前面所述的方式進行操作，看起來設定繁瑣，但設定好後，後續使用非常自動化，提供有需求的朋友進行參考。

善用 Excel 進階函數 vlookup，解決工作上大小事。

Excel 我們知道提供很多函數，從數學、財務、統計、日期...等等有上百個函數，但要在這些函數選一個最常用的函數，我想非 vlookup 莫屬，這個函數主要用途是在您指定的資料範圍內，透過「查閱值」，去尋找在所指定資料範圍內，你所要的欄位資料，其語法如下:

VLOOKUP(查閱值 , 資料範圍 , 指定欄位索引, [已排序])

其參數說明如下:

• 查閱值是我們所要搜尋的值，使用上有二個限制:

1. 必需是資料範圍內第一欄所會出現的值。
2. 在資料範圍第一欄內，該值最好在第一欄內是唯一值，否則結果可能不如預期。

• 資料範圍係我們所要搜尋的範圍，系統會在該範圍的第一欄尋找搜尋詞中指定查閱值。
• 指定欄位索引，代表要傳回的值所屬的欄索引。資料範圍中的第一欄編號為 1，也就是說，如果你想要傳回資料範圍內第3欄的資料，指定欄位索引就設為3，如指定欄位索引並非介於 1 和資料範圍的欄數之間，系統會傳回 #VALUE!。
• [已排序]有二種情形:
1. 絕大多數情況下，請設為 FALSE，該設定會使系統會傳回完全相符的值。如果有多個相符值，系統會傳回與找到的資料範圍第一欄中第一個值對應的儲存格內容；如果找不到相符值，則會傳回  #N/A。
2. 設為 TRUE 或省略，系統會傳回最接近的相符值 (小於或等於搜尋詞)。如果進行搜尋的欄中所有的值均大於搜尋詞，則會傳回 #N/A。

以下舉一實際範例來說明如何應用，現假設我們想從下面範例的股票股利股息表，捉出特定公司現金股息及股價，算出其現金殖利率，在此因為股票代號是唯一的，當然我們就選用股票代號為查閱值，去查出該股票本年度的現金股息及股價。

因此在下面現金殖利率表中，股票代號由我們手動輸入所要查詢的股票代號，股價欄位則應用 vlookup 捉取，語法如下:

=vlookup(B2,'股票股利股息表'!A:F,6,false)

在此查閱值為現金殖利率表的B2，也就是股票代號欄位;資料範圍為股票股利股息表的A到F欄;指定欄位索引在此我們要的是股價，也就是在股票股利股息表由股票代號起算的第6個欄，因此設定為6;[已排序]一般則設為 false 。同樣的現金股息的部分也是相同的方式，語法如下:

=vlookup(B2,'股票股利股息表'!A:F,3,false)

得到股價及現金股息，就可以計算出現金股息殖利率了，在下表的範例1，我們展示計算泰詠現金股息殖利率的結果;在範例2，我們展示如果我們沒輸入股票代號或輸入股票代號，不在股票股利股息表中時，會傳回  #N/A，如果要避免出現，可加上 iferror 函數，將 #N/A轉為我指定的值，因此前面兩個函數可修改如下:

=iferror(vlookup(B4,'股票股利股息表'!A:F,6,false),) 

=iferror(vlookup(B4,'股票股利股息表'!A:F,3,false),)

修改後的結果如範例3所示，在此我們將 #N/A 改為空白 。

前面介紹完 vlookup 語法，粗略來看該語法所做的事情，事實上很簡單，就是將我們所要的資料，由指定資料範圍找出來，有點像是簡單查詢程式，但其應用變化多端存乎一心，用的好的話可以解決工作上的很多問題，例如可用在下面場合:

1. 如同前面的範例，將其中一個表當作資料庫，將我們所要欄位資料捉到另一個表做計算使用。
2. 比對兩個表或欄位資料是否有差異，在文書作業中，我們常常會製做很多表，有的時侯版本控管沒控管好，時間久了我們會忘了那一版為最終版，這時可用 vlookup 函數來做比對，方法是在其中一個表中捉取另一個表中的資料，看是全部都會捉到，還是會出現#N/A，由此可知兩個表的差異，當然如果要更細緻一點，可以在加上 if , and ,or 等判斷式，做更細部的確認。
3. 可以用於將多張不同的表，匯整成一張大表使用。
4. 將適用於試算表運算的表，改為適合的閱讀或文書作業的表，就如同在如何將紙本表單轉為 Google 表單後，讓填寫人填寫完後轉為紙本表單格式印出。 所做的，將表單填表人所填的資訊，轉為紙本表單格式印出。
5. 跟 4 的應用反過來，是將適合的閱讀或文書作業的表改為試用試算表計算用的表，在此時單用 vlookup 可能沒辦法完全處理好，需要搭配使用 index 、 column、row 等函數才有辦法處理。

以上為 vlookup 使用方式及其應用的介紹，如果能應用的好，事實上可以解決工作上的很多問題，故我一直認為 vlookup 為 Excel 最重要的函數，不過如果你問我在 Google 試算表是否也是如此，那我會說不一定，原因為在 Google 試算表有提供另一個同類型的函數 query ，其使用限制更少，功能更強，有興趣朋友可以參考我這篇文章。但 query 使用語法較為複雜，需下如 SQL 的語法才能使用，而且限制在Google 試算表才能用，因此 vlookup 還是一值得學習的函數。