有人喜欢函数，赞美其和声之美；有人看见函数就头疼，这是众多读者的反馈。但是当你真正走进了函数的世界，在使用中可以省下很多的时间，进而享受到期间的乐趣，你会对函数刮目相看。

今天讲MATCH()函数和INDEX()函数结合，实现双重条件的查询。其实这类问题最好用VBA代码来解决，这里我还是不遗余力的写函数，只是让大家明白一种VBA的逻辑思路。好了，闲话少叙，看情景。

如下：1、2、3月的出勤如下表，

如果想知道某人1、3月的出勤天数，如何去处理呢？当然如果只是一条数据，轻松地就可以实现，如果数据较多，怎么办呢？在大数据时代，上千条上万条数据呢？不急，函数来帮忙。

如上图，在蓝色区域分别录入上面公式：以B14为例公式讲解：

=INDEX($A$1:$D$10,MATCH($A14,$A$1:$A$10,),MATCH($B$13,$A$1:$D$1,))

$A$1:$D$10是指数值的区域范围；

MATCH($A14,$A$1:$A$10,)是在$A$1:$A$10区域内查找$A14的值，返回行值。

MATCH($B$13,$A$1:$D$1,)是在$A$1:$D$1区域内查找$B$13值，返回列值。

这样在$A$1:$D$10区域内的行列值有了，就可以返回对应的VALUE了。看下面的返回结果：

这样就输出了需要的结果，是不是很麻烦呢？不要紧，你只要跟着上面的公式，在录入的时候琢磨一下就可以了，不是很难的。上面的公式中还用到了绝对引用和相对引用，就不再多说了。

需要注意的是：上面的方法适用于人员是唯一值；出勤月份为唯一值的条件。

总之，函数就是输入和输出的运算，是一种对应关系。关系不乱，函数就不会乱，通过各种关系的组合得到不同的想要得到的值。

分享成果，随喜正能量

在我们在工作或者学习中，使用Excel进行数据统计的时候，经常会用到各类的Excel相关函数，类似Sum、sumif、countif、Average、Averageif等等的统计函数。

实际上在Excel中有一个更加简单又方便的操作，那就是数据透视表。你只需要拖动你要统计的内容，数据即可展现出来。

一、引用路径方法：

设置方法：

1、点击菜单栏：插入—数据透视表，可以新建或者在当前页面插入数据透视表区域；

2、将名称字段拖动到行标签或者列标签，数据字段需要拖动到数值。通过数据透视表的操作，我们可以进行各类的求和、计数、平均以及各类占比的计算。

二、案列介绍

场景1：求出人员明细中，每个部门的销售总量

操作方法：

1、点击菜单栏：插入—数据透视表，在当前页面插入数据透视表区域；

2、将部门字段拖动到行标签，销售额的字段拖动到数值区域。这样默认的数据求和就出来了。

场景2：求出每个部门的平均销售量

操作方法：

1、点击菜单栏：插入—数据透视表，在当前页面插入数据透视表区域；

2、将部门字段拖动到行标签，销售额的字段拖动到数值区域；

3、点击数值字段中，销售额右边的小倒三角形，值字段设置，显示数值选择为平均值。

场景3：求出各部门人数

操作方法：

1、点击菜单栏：插入—数据透视表，在当前页面插入数据透视表区域；

2、将部门字段拖动到行标签，姓名字段拖动到数值区域；当移动的是非数值的标签时，默认为统计个数。

场景4：求出各部门人数的占比

操作方法：

1、点击菜单栏：插入—数据透视表，在当前页面插入数据透视表区域；

2、将部门字段拖动到行标签，姓名字段拖动到数值区域；

3、点击数值字段中，姓名右边的小倒三角形，值字段设置—值显示方式—占总和的百分比。

场景5：求出各部门销售额占比

操作方法：

1、点击菜单栏：插入—数据透视表，在当前页面插入数据透视表区域；

2、将部门字段拖动到行标签，销售额字段拖动到数值区域；

3、点击数值字段中，销售额右边的小倒三角形，值字段设置—值显示方式—占总和的百分比。

现在你学会了这个拖一下就可以统计数据的操作了吗？

注意问题：

1、在制作数据透视表的过程中，表头也就是标题行不能有空白单元格，这样透视表将无法透析；

2、为保证数据的准确性，每列的数据格式必须要保持一致。

喜欢、有用就点点关注

函数COUNT家族有5个成员，今天先介绍其中的三个家庭成员，它们分别是COUNT、COUNTA、COUNTBLANK。

一、函数定义

COUNT：计算区域中数字的单元格个数。COUNTA：计算区域中非空单元格的个数。COUNTBLANK：计算区域中空单元格的个数。

二、函数实例

▲

计算有数字的单元格个数

01

公式：=COUNT(A3:A11)不能转换为数字的文本、空白单元格、逻辑值、错误值都不计算在内。

计算非空单元格的个数

02

公式：=COUNTA(A17:A25)参数值可以是任何类型,包括空字符(""),但不包括空白单元格。

计算空单元格的个数

03

公式：=COUNTBLANK(A31:A39)空白单元格和空文本（""）会被计算在内。

求成绩大于等于60的个数

04

公式：=COUNT(0^(B45:B51>=60))按三键结束(B45:B51>=60)部分成立的返回TRUE,不成立的返回FALSE。发生四则运算时TRUE相当于1，FALSE相当于0，利用0的任何次方等于0，0的1次方返回错误值的特性，将(B45:B51>=60)部分作为0的次方，得到{0;#NUM!;#NUM!;0;#NUM!;0;#NUM!}，COUNT函数忽略错误值只统计有数字的单元格个数。

求缺勤率

05

公式：=COUNTBLANK(B57:B63)/COUNTA(B57:B63&"")，记得带上花括号哦！先用COUNTBLANK函数算出未打卡的人数为2，因为有两个空白单元格，所以用COUNTA计算时加"",算出总人数为7，然后相除。

添加序号

06

在A69单元格输入公式：=IF(B69="","",COUNTA(B$69:B69))，向下填充。COUNTA(B$69:B69)部分是统计不为空的单元格个数，注意引用方式；然后用IF函数判断是不是空，为空就返回空，否则返回COUNTA函数计算的个数。

合并单元格添加序号

07

COUNTA法:选中区域A81:A87输入公式=COUNTA(A$80:A80)按Ctrl+Enter键结束

COUNT法:选中区域B81:B87输入公式=COUNT(B$80:B80)+1按Ctrl+Enter键结束

求平均销售额

08

在F93单元格输入公式：=SUM(B93:E93)/COUNT(B93:E93)，向下填充。先用SUM函数求和算出总销售额，然后用COUNT算出有销售额的数目，再用SUM/COUNT得到平均销售额。

三、函数总结

1、COUNT：计算区域中数字的单元格个数。

①如果参数为数字、日期或者代表数字的文本，则将被计算在内；②逻辑值和直接键入到参数列表中代表数字的文本被计算在内；③如果参数为错误值或不能转换为数字的文本，则不会被计算在内；④如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。

2、COUNTA：计算区域中非空单元格的个数。

①参数值可以是任何类型,可以包括空字符(""),但不包括空白单元格；②如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略；③如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。

3、COUNTBLANK：计算区域中空单元格的个数。

①包含返回 ""（空文本）的公式的单元格会计算在内；②包含零值的单元格不计算在内。

爱上Excel合伙人2017出品

我们一直秉承简洁、优雅、高效的为读者分享工作中遇到的每一个Excel问题，不论是Excel技巧、函数、图表、VBA，甚至是有关于Excel的开发，只要你能提出来问题，我们总能给你一个满意的答案！

每天准时来一篇Excel在职场中的案例

在工作过程中，有时为了方便区分不同的类别，一般都会选用给单元格标注颜色，这种方法简单快捷。那如果后续想根据单元格颜色来进行汇总怎么办呢？我们都知道可以按单元格颜色进行筛选，那除了最简单的筛选，还有什么其他办法呢？今天给大家介绍几个按不同颜色来进行单元格求和的方法。

如图，根据下列案例分别按不同的四个颜色对订单数进行求和。

一、查找求和

查找这个功能大家都经常用，但是根据颜色来查找大家都会用吗？具体方法如下：

点击开始选项卡下，【编辑】组里的“查找和选择”下方的“查找”或者按Ctrl+F就可以打开“查找和替换”窗口。

在“查找和替换”窗口点击“选项”。选项上方就会出现“格式”下拉框，在下拉框选择“从单元格选择格式”。也可以直接选择格式进行设置，不过从单元格选择当然更方便了。

鼠标就会变成一个吸管，点击黄色的单元格之后，格式旁边的预览窗格就是黄色的。点击“查找全部”下方就会出现所有黄色的单元格。

点击下方查找到的任一条记录，按住Ctrl+A，所有黄色的单元格就被选中了。工作表右下角就出现了所有黄色的求和。

然后再利用这种方法再依次把其他颜色的单元格求和值获取出来就可以了。

这种方法简单易操作，缺点就是只能根据颜色一个个进行操作。

二、宏表函数求和

Excel中可以使用宏表函数get.cell来得到单元格的填充色。但宏表函数必须自定义名称才能使用，具体方法如下：

点击公式选项卡下【定义的名称】组里的“定义名称”。

在“编辑名称”窗口，名称输入“color”，引用位置输入“=GET.CELL(63,宏函数!B2)”。“宏表函数”是所在工作表的名称，由于首先在C2单元格输入公式获取颜色值，所以这里选用带颜色的单元格B2。不加绝对引用就可以方便在其他单元格同样也能获取到左侧单元格的颜色值。

然后在C2:C10单元格里输入“=color”。这列的值就是颜色值。

同理，在颜色这一列F2:F5旁边也输入颜色值“=color”。

最后根据一一对应的颜色值，使用SUMIF函数“=SUMIF(C:C,F2,B:B)”即可。

利用宏表函数获取颜色的值 ，然后通过SUMIF函数进行求和。这种获取颜色值的方法除了可以使用SUMIF函数之外，还可以使用其他不同的函数来对颜色进行多角度分析，非常方便实用。

三、VBA求和

获取单元格颜色最方便最快捷的方式当然是使用VBA。Excel本身包含的函数无法实现按颜色求和，我们通过VBA自己构建一个自定义函数来帮助实现按颜色求和。

按住Alt+F11或者在工作表标签上右键“查看代码”打开VBA编辑器。

在VBA编辑器里点击插入下方的“模块”。

点击新创建的模块--模块1，在右侧窗口输入以下代码。

Function SumColor(col As Range, sumrange As Range) As Long

Dim icell As Range

Application.Volatile

For Each icell In sumrange

If icell.Interior.ColorIndex = col.Interior.ColorIndex Then

SumColor = Application.Sum(icell) + SumColor

End If

Next icell

End Function

解析：

SumColor是自定义的函数名称，里面包括两个参数，第一参数col是要获取颜色的单元格，第二参数sumrange是求和区域。

（这里相当于我们自己创建一个函数SumColor，并且自己定义函数的2个参数的含义。对于初学者来说，暂时可以不用理解这段代码的意思，只需要保存下来，作为模板套用即可）

点击“文件”-“保存”，然后直接关闭VBA编辑器即可。

自定义函数定义好之后，直接在工作表进行使用就可以了。在F2:F5单元格输入“=SumColor(E2,$A$2:$B$10)”就可以了。

注意：宏表函数和VBA用法由于使用了宏，在EXCEL2003版本可以直接保存，但2003以上版本需要保存为“xlsm”格式才能正常使用。

对于标记颜色的单元格来说，查找这个方法容易使用但适用场景不多，VBA功能很强大，但是要想彻底弄懂还需要更深层次的学习。宏表函数这个方法比较简单，而且也比较实用，觉得有用的话赶紧收藏吧！

＊＊＊＊部落窝教育－excel按颜色求和＊＊＊＊

原创：夏雪/部落窝教育（未经同意，请勿转载）

您好，这里是“E图表述”为您讲述的Excel各种知识。

有很多时候，我们需要在数据中插入图片，这对于信息的可视化是很有帮助的。今天我们就来看一个用函数来自动引用图片的过程。

先看一下数据源：

在这里说一些题外话，这些图片是手动插入的。如果是生产行业的零件图，一张一张的手工插入也确实是一个挺浪费时间的事情。那样的话，我们就需要用vba来解决了。这个代码作者以后会和大家分享的，敬请期待。

言归正传，今天我们要达到的效果是，根据改变名称来自动调用上图中对应的图片，看一下效果吧。

现在我们看一下具体的操作是怎样的。

1、Ctrl+F3调出“名称管理器”，新建一个“编辑名称”：

名称：图片（或者其他的名字，随你喜欢）

引用位置：=INDEX($B:$B,MATCH($D$1, $A:$A,0))

2、复制其中一个图片，粘贴到引用图片需要展示的位置，选中图片，在地址栏输入=图片，按回车。

完工了，就是如此简单吧。

值得说两句的是，很多同学都表示，再引用图片的时候出现“引用无效”的提示。这种时候，检查一下名称管理器中，引用位置的函数，切记一定要用绝对引用（手工输入$，或者按F4），这样才能保证引用位置的固定不变。往往大部分的“引用无效”都是因为没有绝对引用位置造成的。

作者云：

动态引用图片是很有用的技巧，比如：简历管理中的个人寸照，零件性能对比，足球比赛技术统计对比，同类商品销售数据对比等等，很多都可以用到这个方法。

编后语：

名称管理器是一个值得深入了解的内容，很多时候我们都需要，我们可以理解它是一个辅助数据，可以很方便的存进内容，也可以很方便的提取内容。而且它是内存中的计算，效率也会有保障。

如果上面的内容对您还有帮助，或者觉得作者比较用心。可以关注、评论、留言、转发“E图表述”，便于您继续观阅和浏览往期的“Excel干货分享”。

在平时工作学习中，我们几乎每天都要使用EXCEL制作表单，保存数据，制作图表等。

除了这些功能，你会用哪些函数呢，仅仅是SUM，AVERAGE，COUNT，MAX，MIN等这些简单的函数。对于EXCEL函数博大精深，会这些基础类函数仅是皮毛而已，小编只能是略微熟练。若能精通EXCEL高级函数使用，月薪超过6千、甚至1万也完全有可能。下文小编介绍一些函数复杂用法，提高大家工作和学习效率。

本文主要介绍函数，如果你已精通请略过此文。

查找类函数：VLOOKUP,VLOOKUP和IF、CHOOSE组合，INDEX等

字符截取函数：LEFT,WRIGHT,MID

条件类函数：SUMIF,COUNTIF，SUMPRODUCT，IF和ISERROR等组合

图1-某美女工作照

1、查找类函数VLOOKUP

例子如图2，如何从表二中，A厂、D厂、C厂、F厂一月份的产量，使用函数=VLOOKUP(C12:C15,C2:D8,2,0)。

参数解释：

第一参数：C12:C15即需要查找的对象，

第二参数：C2:D8,查找的原始数据，工厂必须在第一列如果不在第一列后边讲解。

第三个参数：2，查找数据的第几列，因为我们查找产量在第二列，所以这里为2。

第四个参数：0，代表模糊查找。

各位读者可以打开EXCEL练习vlookup函数。

图2-vlookup

在上个实例提到，如果查找工厂列不在产量前面如何解决呢？

见图3，使用函数为：={VLOOKUP(C12,IF({1,0},D2:D8,C2:C8),2,0)}

讲解：这里大括号需要使用数组函数，输入函数后同时按下Ctrl+Enter

参数解释：

第一个参数：C12，需要查找的对象

第二参数：IF({1,0},D2:D8,C2:C8)，是对数组判断，意思就是如果为真对应D2:D8,否则对样C2:C8列。

第三个参数：2，查找的对样对应的第几列，这里是对2列。

第四个参数：0，模糊查找

读者可以练习vlookup/IF组合使用，是不是突然恍悟，原来可以这样用。

图3-VLOOKUP/IF

图2实例中，也可以使用VLOOKUP/CHOOSE组合。

函数为：=VLOOKUP(C12,CHOOSE({1,2},D2:D8,C2:C8),2,0)

CHOOSE函数属于数组函数，这里可以不用按Ctrl+Enter使用。

参数解释：

这里不再解释第一三四参数，具体解释见图1和图2中解释

第二参数，CHOOSE({1,2},D2:D8,C2:C8)，就是把D2:D8和C2:C8组成数组，D列为第一列，C列为第二列。因此，我这里第三个参数使用的是2，就是按照C列中的值查找对应的D列中的值。

图4-VLOOKUP/CHOOSE组合

多条件查找，图4实例这里知道年份、月份、工厂信息，查找对应产量信息。

使用函数如下：

=VLOOKUP(B12&C12&D12,IF({1,0},$B$2:$B$8&$C$2:$C$8&$D$2:$D$8,$E$2:$E$8),2,0)

参数解释：

参数一：B12&C12&D12，使用&符把多条件连接起来，组合起来就是，201611月A厂，这里是不使用$符号，我们需要相对引用，向拉时，对象跟着变化，使用$符号下拉不会变化，这里需要注意。

参数二：IF({1,0},$B$2:$B$8&$C$2:$C$8&$D$2:$D$8,$E$2:$E$8)，使用if函数组成新的数组，第一列是B列C列D列，第二列是E列，这里使用的绝对引用，下拉不需要数组发生变化。因此第三个参数我们使用参数2，第四个参数就不做解释，请参考上文。

图4-多条件查找

2、字符串截取函数

已知E列年月工厂信息，需要知道年份、月份、工厂信息

使用函数如下：

B列：=LEFT(E2,5)，意思为取E2左边5个字符

C列：=MID(E2,7,LEN(E2)-6-3)，这里使用组合函数，因为月份长度不确定，LEN(E2)-6-3，意思是E2总长度减去年份长度加一和工厂长度加一。函数整体意思，从第7个字符开始取长度为LEN(E2)-6-3，即月份。

D列：=RIGHT(E2,2)，右边取E2长度为2的字符

图5-字符串截取

3、多条件求和

实例如图6，求2016年1月份，A厂和B厂总产量。

使用函数：=SUMIF(C$2:C$8,C12,D$2:D$8)

函数解释：

在D$2:D$8中求和，其中C$2:C$8工厂为C12的数字之和。这里是绝对引用，下拉即为B厂总产量，下拉后函数为是：=SUMIF(C$2:C$8,C13,D$2:D$8)，这里不在上图，读者这里可以练习一下。

图6-条件求和

4、条件计数

已知表一信息，求A厂和B厂记录数。

函数如下：=COUNTIF(C$2:C$8,C12)

解释，记录C$2:C$8为C12的数量，下拉即得B厂记录数。

图7-条件计数

5、多条件求和，SUMPRODUCT函数

已知表一信息，求A厂和B厂2016年1月份总产量。

函数如下：=SUMPRODUCT((C$2:C$8=C12)*(D$2:D$8))，下拉即得B厂总产量，

函数是：=SUMPRODUCT((C$2:C$8=C13)*(D$2:D$8))

解释，求D$2:D$8中之和，其中C$2:C$8为C12数值之和。

图8-多条件求和SUMPRODUCT

6、IF/ISERROR组合判断

在表一中，由于信息不规律，我们筛选出，2017年的产量，并且标记为Y,否则标记为N。

函数为：=IF(ISERROR(FIND("2017",B2,1)),"N","Y")

解释，在B2列中查找2017字符串并取反，如果为真取标记N,否则标记Y。

图9-IF/ISERROR判断

7、条件格式

我们继续使用图9的信息，见图10

使用函数=INDEX($D:$D,ROW(),0)="Y"，此函数解释，判断D列中是否为Y，是标记为TRUE，否则标记FLASE。

条件函数如何设置，以EXCEL2013为例，开始->条件格式->新建格式规则-使用公式确实设置的单元格。

图10-条件格式

总结使用心得

EXCEL函数就介绍到这里，函数使用靠平时积累，多用多练，熟能生巧。如果平时对于不懂的函数，可以多上网搜索。小编认为过不多久你也会成为EXCEL高手，身边会有很多人羡慕你。EXCEL函数可以提高你工作效率，但不一定能解决更复杂的问题，多种重复性的操作，需要VBA来实现，小编以后会介绍如何使用VBA实现自动操作EXCEL。

好了，今天就介绍到这里，祝你工作学习愉快，月薪早日超过6000吧，我们一起加油！

加油努力！

1 自定义函数的代码存放位置

一般存放在模块中。

2 自定义函数代码的格式

'函数功能：

'在第一参数“区域”所代表的列中查找第二参数“查找值”的值，然后根据第三参数“列”的值确定返回值所在列的值

'如果有找到多个值，那么由第四参数决定返回第几个值

'忽略第三参数时表示默认值是为2，即返回“区域”右边一列的值

'忽略第四参数时表示默认值1，即返回第一个值

'使用函数可参考以下公式：

'=look(E$2,B$1:C$12,2,ROW(A1))

'确定函数Look，类型为String。包括四个参数，前两个为必选参数，后两个为可选参数

Function look(查找值 As String, 区域 As Range, Optional 列 As Integer = 2, Optional 索引号 As Integer = 1) As String

Dim i As Long, cell As Range, Str As String

With 区域.Columns(1) '引用区域的第一列

'如果引用区域第一个单元格等于查找的对象，那么将该单元格赋予变量Cell。否则使用Find方法查找，将找到的单元格赋予变量Cell

If .Cells(1) = 查找值 Then Set cell = .Cells(1) Else Set cell = .Find(查找值, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then '如果找到

Str = cell.Address '记录单元格地址

Do '通过循环语句继续查找

i = i + 1 '累加变量，表示符合条件的个数

'如果变量等于最后一个参数，那么将查找到的单元格右边的值赋予Look函数

If i = 索引号 Then look = cell.Offset(0, 列 - 1): Exit Function

Set cell = 区域.Find(查找值, cell, , xlWhole) '查找下一个

'如果找到的目标单元格地址不等于第一次找到的单元格的地址就继续查找

Loop While cell.Address <>Str

Else

look = "" '如果找不到则直接返回空白

End If

End With

End Function

3 添加自定义函数的说明

开发工具→宏→宏名：look→选项→说明：输入说明内容。

4 为自定义函数指定类别

在打开的“插入函数”对话框的“函数分类”列表中，函数的类别包括全部、财务、日期与时间等分类，而创建的自定义函数会自动分配到“

用户定义”类别中。

也可以把自定义函数添加到特定的类别中，运行如下的一个过程即可：

Sub 指定函数类别()

Application.MacroOptions "look", Category:=5

End Sub

函数类别编号如下所示：

0全部

1财务

2 日期和时间

3 数学和三角

4 统计

5 查找和引用

6 数据库

7 文本

8 逻辑

9 信息

5 自定义函数的使用

5.1 被其它VBA程序调用

5.2 在工作表公式中使用

6 自定义函数的公用

自定义函数一般情况下只能在含有函数代码的工作簿内使用。如果需要让该自定义函数在所有打开的Excel工作簿中使用，需要保存为加载宏文件并进行加载。

6.1 保存为加载宏文件：包含自定义函数的文件→另存为→类型：加载宏xla。

6.2 添加加载宏：office按钮→Excel选项→加载项→转到→勾选：加载宏文件名→确定。

VLOOKUP函数是一个纵向查找函数，它是按列查找，最终返回该列所需查询列序所对应的值。

用VLOOKUP函数来查找很方便，不过它的缺点很明显：

1、速度慢，特别是在数据量大的情况下。

2、每个单元格你都要维护好公式，如果对应不到会出现#N/A，不是很美观，当然你可以用别的公式来消除，不过这又增加了公式的复杂度。

用VBA代替VLOOKUP函数，不仅速度快，而且把它单独做成模版，下次有类似对应操作的需求时，可以直接复制粘贴进去来使用，不用再维护调整公式数量了，通用性强。

举个例子：把表1学号信息填到表2学号里面

VBA代替VLOOKUP函数

方法一、最笨的方法就是按照姓名筛选手工填或者CTRL+F批量替代，数据量大了根本不好使。

方法二、在表2学号列填写VLOOKUP函数，比如G2=VLOOKUP(F2,A1:B4,2,FALSE), G3=VLOOKUP(F3,A1:B4,2,FALSE),以此类推。

方法三、用VBA代码，按ALT+F11进入工程界面，输入右侧代码，运行就可以了。

下次遇到类似的需求只要把相应的数据复制粘贴到表1和表2，运行一下就可以了。

附上截图代码

Sub 引用()

Dim i%, r%

Dim arr1, arr2

arr1 = Sheets("sheet1").[a1].CurrentRegion '表1数据赋值给数组arr1

arr2 = Sheets("sheet1").[f1].CurrentRegion '表2数据赋值给数组arr2

r = 1

For r = 1 To UBound(arr2) '可以看成表2的行数

For i = 1 To UBound(arr1) '可以看成表1的行数

If arr2(r, 1) = arr1(i, 1) Then '可以看成如果表1和表2各自的第1列数据有一样的

arr2(r, 2) = arr1(i, 2) '那么把表1对应的第2列数据赋值给表2的第2列数据

Exit For '结束循环遍历

End If

Next

Sheets("sheet1").[f1].Resize(UBound(arr2), 2) = arr2 '把更新后的数组arr2复制到表2

End Sub

VLOOKUP函数在EXCEL是个很实用很流行的公式，使用VLOOKUP函数来对应数据很方便简洁，不过缺点也很明显：

1、在数据量偏大，数据行偏多的情况下速度反应慢。

2、有多少行单元格就要填多少公式，一不小心容易出错，如果对应不到的单元格会出现#N/A，这样很不美观，用判断公式来消除#N/A，无形中又增加了公式复杂度。

用VBA实现VLOOKUP函数，把它单独做成模版，不用再维护调整公式，下次有需求时，可以直接复制粘贴进去来使用，不仅速度快，而且通用性强。

下面说个例子：把表格1里的学号信息填到表格2对应学号列里面

VBA实现VLOOKUP函数

下面说说三种方法：

一、最原始的方法就是手工填写，可以按照姓名筛选手工填也可以CTRL+F批量替代，数据小可以用用。

二、在表格2里的学号列填写VLOOKUP函数，比如G2=VLOOKUP(F2,A1:B4,2,FALSE), G3=VLOOKUP(F3,A1:B4,2,FALSE),G4=VLOOKUP(F4,A1:B4,2,FALSE)……有多少填多少。

三、就是本文说的用VBA代码，按ALT+F11进入工程界面，输入代码，运行。

VBA实现VLOOKUP函数

以后遇到类似的任务可以直接把相应的数据复制粘贴到表格1和表格2，运行一下就OK了。

以下是截图代码

Sub 引用()

Dim i%, r%

Dim arr1, arr2

arr1 = Sheets("sheet1").[a1].CurrentRegion

arr2 = Sheets("sheet1").[f1].CurrentRegion

r = 1

For r = 1 To UBound(arr2)

For i = 1 To UBound(arr1)

If arr2(r, 1) = arr1(i, 1) Then

arr2(r, 2) = arr1(i, 2)

Exit For

End If

Next

Next

Sheets("sheet1").[f1].Resize(UBound(arr2), 2) = arr2

End Sub

上面要注意表2中G1单元格不能留空，如果有什么运行问题请留言。

之前零散开发过一些自定义函数获取Excel对象属性，此次再细细地把有价值的属性都一一给开发完成，某些场景下，有这些小函数还是可以比较方便地实现一些通过Excel界面没法轻松获取到的信息。

函数清单

可在公式=》插入函数里找到此类的函数清单

大部分函数取的是单元格的一些属性。

函数清单

同时也做了个示例的文件，方便使用和查阅。

函数示例工作薄

具体函数功能

GetHyperlinksAddress函数

从网页上复制内容到Excel中比较有用，可以提取网页的超链接

GetRowHeight函数

GetColumnWidth函数

GetCellFormular函数

GetCellCommentText函数

GetCellText函数

GetCellNumberFormat函数

获取单元格的数字格式设置内容

GetCellInteriorColor函数

GetCellFontColor函数

获取单元格的字体颜色

GetRangeAddress函数

获取单元格的地址，不同参数下可获得相应的绝对、相对引用的地址格式

GetCurrent相关函数

获取工作表、工作薄的名称信息

总结

万丈高楼平地起，任何一个精彩的Excel应用，都是多方的知识和功能联合造就的，这些小小的自定义函数，某些时候会是某个数据应用里一个很不错的功能落地点。积累多一些知识，真正应用时就可以有丰富的智囊可供使用。