【Excel】查找与引用函数知识点总结及图文详解

本文转载自公众号：数据产品之道，作者：马王杰。感谢作者辛勤劳动和无私分亨！

Excel查找与引用函数主要框架如下：

01
—
ROW与COLUMN
ROW(reference)      返回一个引用的行号
ROWS(array)    返回数组或单元格区域中的行数COLUMN(reference)     返回一个引用的列号COLIMNS(array)    返回数组或单元格区域中的列数例一：生成如下图数列



例二：



02
—
VLOOKUP函数
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=VLOOKUP ( 查找值 , 查找的区域 , 返回位置(列) , 查找方式 )

设置这种上下三角的方法：
文件>选项>自定义功能区>开发工具


精确查找(FALSE):
例一：


绝对引用（$E$2:$G$6）快捷键：F4
例二：

其中，下拉三角选项的设置方法：数据>数据有效性>设置>系列>（选择区域）

但是，当C4为空时，就会出现下面这种情况：比较难看



例三：
要求：根据不同的职务底薪和全勤金额填入左表


模糊查找(TRUE)：
例一：


模糊查找，找出比它本身数字小的最大值。
例二：


VLOOKUP之通配符、向左查找：


IF({1,0},$E$8:$E$16,$D$8:$D$16)    选中，按F9
{'张三林','NED001';'李启','NED002';'王生军','NED003';'陈秀峰','NED004';'张梅','NED005';'胡苹','NED006';'陈青','NED007';'刘思燕','NED008';'高妞儿','NED009'}
即，如果是1，则执行$D$8:$D$16)，如果是0，则执行$E$8:$E$16 ，
从而使得两列前后顺序互换。
VLOOKUP进阶：



03
—
HLOOKUP函数
=HLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
= HLOOKUP ( 查找的值 ， 查找的区域 ， 返回的行号 ， 查找方式 )


04
—
LOOKUP函数


向量用法：既能行查询，又能列查询，但只能是单行或者单列。


不管序列升序与否,LOOKUP都以为是升序。
9E 307，为EXCEL里面的最大值。

数组用法：



05
—
CHOOSE函数
CHOOSE(index_num, value1, [value2], ...)   从参数列表中选择并返回一个值

1.第一参数的值不能小于1或者大于29
2.第一参数是小数,则在使用前将被截尾取整

例一：




例二：

06
—
MATCH函数
返回的是look_value在lookup_array中的位置，是一个数值。



07
—
INDEX函数

例一：

例二：


为去掉无效值，使用IF函数。

例三：


例四：

08
—
OFFSET函数
=OFFSET ( reference , rows , cols , height , width)


例一：

例二：


备注：
使用逗号分隔横向数组元素，使用分号分隔纵向数组元素。