今天来教大家动手打造自己的专用函数,别以为自定义函数离你很远,其实你也可以的,而且,今天介绍的知识不复杂,仅仅使用Vlookup而已。

在工作中很多人都遇到这样的情况,有一些固定的或者不经常更新的基础信息表,需要使用Vlookup来查找数据。通常做法是,先打开基础信息表,然后使用Vlookup函数开始查找。其实我们还有一种更简便的方法,想知道是什么吗?Follow me!

案例

有一份产品分类结构表,把不同的产品分成三级,一级分类是最大的分类,二级分类是一级分类的子分类,三级分类是二级分类的子分类。有时候我们经常会根据三级分类名称来查找二级分类或者一级分类。为了简化这个查找工作,我们来定义一个P函数。

excel怎么自定义函数公式(excel表格函数公式大全)(1)

下面给大家详述一下操作步骤:

1、首先把这份明细表单独存放到一个文件中,然后按Alt+F11打开VBA编辑器。在左侧的“工程资源管理器”中双击存放产品结构的工作表,在下面的“属性”窗口中将名称命名为“shProduct”。

如果你没有看到“工程资源管理器”和“属性”窗口,可以在顶部的【视图】菜单中点击“工程资源管理器”和“属性窗口”即可将其显示出来。

excel怎么自定义函数公式(excel表格函数公式大全)(2)

2、接下来在ThisWorkbook上点击右键菜单中的“插入”、“模块”。

excel怎么自定义函数公式(excel表格函数公式大全)(3)

然后输入以下代码。

excel怎么自定义函数公式(excel表格函数公式大全)(4)

对这段代码稍微做一下解释:

自定义函数名称为“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”

excel怎么自定义函数公式(excel表格函数公式大全)(5)

4、点击【开发工具】选项卡中的“Excel加载项”,在打开的对话框中勾选“产品结构”,点击“确定”按钮返回。

excel怎么自定义函数公式(excel表格函数公式大全)(6)

这样就完成了全部的设置,在我们自己的本地电脑的任何Excel文件中都可以使用这个P函数了。

新建一个工作表,输入以下“三级分类”内容,输入公式=P(A2,1)可以返回一级分类内容,输入=P(A2,2)可以返回二级分类内容,如果找不到的话就会返回错误值。

excel怎么自定义函数公式(excel表格函数公式大全)(7)

当我们需要更新产品分类时就使用源文件更改并另存为xlam格式,覆盖之前的文件即可。

做这个自定义函数免去了每次打开文件的麻烦,而且大大地缩短了公式,输入寥寥几个公式字符即可完成查询工作。