1. 首頁 > 生活 > >

excel怎么自定義函數公式集 在excel中,怎樣自定義公式函數?

小知識在excel中



文章插圖
excel怎么自定義函數公式集 在excel中,怎樣自定義公式函數?

文章插圖
【excel怎么自定義函數公式集 在excel中,怎樣自定義公式函數?】今天來教大家動手打造自己的專用函數，別以為自定義函數離你很遠，其實你也可以的，而且，今天介紹的知識不復雜，僅僅使用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格式，覆蓋之前的文件即可 。
做這個自定義函數免去了每次打開文件的麻煩，而且大大地縮短了公式，輸入寥寥幾個公式字符即可完成查詢工作 。