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格式,覆盖之前的文件即可 。
做这个自定义函数免去了每次打开文件的麻烦,而且大大地缩短了公式,输入寥寥几个公式字符即可完成查询工作 。