今天來教大家動手打造自己的專用函數,別以為自定義函數離你很遠,其實你也可以的,而且,今天介紹的知識不複雜,僅僅使用Vlookup而已。
在工作中很多人都遇到這樣的情況,有一些固定的或者不經常更新的基礎信息表,需要使用Vlookup來查找數據。通常做法是,先打開基礎信息表,然後使用Vlookup函數開始查找。其實我們還有一種更簡便的方法,想知道是什麼嗎?Follow me!
案例
有一份產品分類結構表,把不同的產品分成三級,一級分類是最大的分類,二級分類是一級分類的子分類,三級分類是二級分類的子分類。有時候我們經常會根據三級分類名稱來查找二級分類或者一級分類。為了簡化這個查找工作,我們來定義一個P函數。

下面給大家詳述一下操作步驟:
1、首先把這份明細表單獨存放到一個文件中,然後按Alt+F11打開VBA編輯器。在左側的“工程資源管理器”中雙擊存放產品結構的工作表,在下面的“屬性”窗口中將名稱命名為“shProduct”。
如果你沒有看到“工程資源管理器”和“屬性”窗口,可以在頂部的【視圖】菜單中點擊“工程資源管理器”和“屬性窗口”即可將其顯示出來。

2、接下來在ThisWorkbook上點擊右鍵菜單中的“插入”、“模塊”。

然後輸入以下代碼。

對這段代碼稍微做一下解釋:
自定義函數名稱為“P”,也可以改成其他便於記憶的名稱;
Application.Volatile 是為了聲明為易失性函數,當查找值變化時可以重新計算;
P =
Application.WorksheetFunction.VLookup(Product, shProduct.Columns(“A:C”), 4 – Level, 0)本質上還是使用了工作表的Vlookup查找函數,也就是在A:C列查找Product,返回指定列的結果。Level=1表示返回第一級分類的內容,這是因為表格中一級分類在第3列,4-Level=4-1=3,這樣也就返回了第三列的內容,也就是一級分類。具體返回哪一列的信息需要根據表格設置來做相應的調整。
3、將文件另存為“Excel加載宏(*.xlam)”格式,選擇這個格式時會彈出來對話框詢問保存地址,默認情況下會保存到以下路徑中。
C:Users你的用戶名AppDataRoamingMicrosoftAddIns
我們將文件保存為“產品結構.xlam”

4、點擊【開發工具】選項卡中的“Excel加載項”,在打開的對話框中勾選“產品結構”,點擊“確定”按鈕返回。

這樣就完成了全部的設置,在我們自己的本地電腦的任何Excel文件中都可以使用這個P函數了。
新建一個工作表,輸入以下“三級分類”內容,輸入公式=P(A2,1)可以返回一級分類內容,輸入=P(A2,2)可以返回二級分類內容,如果找不到的話就會返回錯誤值。

當我們需要更新產品分類時就使用源文件更改並另存為xlam格式,覆蓋之前的文件即可。
做這個自定義函數免去了每次打開文件的麻煩,而且大大地縮短了公式,輸入寥寥幾個公式字符即可完成查詢工作。
原創文章,作者:投稿專員,如若轉載,請註明出處:https://www.506064.com/zh-hant/n/216756.html