Excel 查找数据,这个简单高效的函数好用到爆 |
您所在的位置:网站首页 › vba查找值返回位置 › Excel 查找数据,这个简单高效的函数好用到爆 |
原文标题:《再见 VLOOKUP!查找数据,这个简单高效的函数好用到爆!》 小伙伴们,大家好。我是农夫,专治「疑难杂数」的农夫~ 相信大家在日常学习和工作中,常常会根据相应的条件来提取工作表中的数据~ 比如: 从销售资料表,包括商品名称、型号、省份、销量、单价等,根据商品名称和型号提取出相应的销量; 从成绩单,包括学生名称、课程名称、分数等,根据学生名称和课程名称提取相应的分数; 从员工资料表,包括姓名、职务、性别、户籍等,根据员工姓名提取相应职务等等。 应对这种需求,我猜很多小伙伴会想到 VLOOKUP 函数对不? 但其实还有一个查找引用函数,也可以轻松搞定这类数据需求,这就是今天要介绍的 INDEX 函数~ INDEX 函数自身能解决一些基本的数据查找引用需求,然而,一旦与其他函数合体,那么它的功能就会被成倍放大! 为了更好更灵活地使用 INDEX 函数,本篇我们先来学习下 INDEX 函数的基本用法~ 01、INDEX 函数INDEX 函数会返回行列或区域中的值或值的引用。其具体语法如下: =INDEX (查询的单行单列或区域,行号,列号) 其中,如果数据区域为单行或单列,则相对应的参数行号或列号为可选参数。 ❶ 单个区域提取数据。以下表为例,如果你想查找在 A2:G21 这个区域内,第 2 行第 3 列单元格数据,可以输入: =INDEX(A2:G21,2,3)即可输出单元格数据:秋二兵。 如果你想查找在 E2:E21 这一列内,第 2 行的数据,可以输入: =INDEX(E2:E21,2)即可输出单元格数据:中国近现代史纲要。 如果你想查找在 A2:G5 这一行内,第 3 列的数据,可以输入: =INDEX(A5:G5,3)即可输出单元格数据:王一虎。 如果你想在两个不连续的区域中查询数据,就需要用到 INDEX 函数的多区域提取~ 比如,查询 A1:C11 和 D1:E11 两个区域中,第二个区域中的第 4 行第 1 列的数据。 具体公式如下: =INDEX((A1:C11,D1:E11),4,1,2)其中,INDEX 函数, 第一个参数为选择的两片数据区域,将两片区域放在括号内,并用逗号分开; 第二个参数为行号; 第三个参数为列号; 第四个参数是查询数据所在的那一片区域(这里我们选择的是第二片区域)。 返回结果:新媒体与网络安全。 PS:逗号:联合运算符,将多个引用区域合并为一个引用区域;空格:交叉运算符,生成两个引用区域共同部分的引用区域。 ❺ 隔行提取数据。很多时候,我们也会遇到,隔固定数量的行提取数据的需求。 对于这种数据,我们也可以使用 INDEX 函数处理。 其关键就在于,将 INDEX 函数第二个参数构建公差为 2 的等差数列,具体公式如下: =INDEX(B:B,2+2*(ROW(A1)-1))&其中,2+2*(ROW (A1)-1) 为等差数列。 ROW 函数返回单元格所在行号,如 ROW (A1) 返加 A1 所在的行数,则 2+2*(ROW (A1)-1)=2+2*0=2,随着下拉则会生成 2,4,6,8,10...... 等差数列。 最终,通过 INDEX 函数提取出相应单元格的数据。 "" 的作用是让 INDEX 函数引用至空单元格时,不会返回 0 而是返回空。 好,下面我们更进一步,构建姓名和科目相互对应的数据表。 我们只需将第 1 个 2 替换为随着下拉需变动的函数:COLUMNS (A1)(横向拉动不变,纵向拉动变化 + 1),这样就能构建两个等差数列,分别提取姓名列和科目列。 具体公式如下: =INDEX($B:$B,COLUMN(A1)+2*(ROW(A1)-1))&当然了,还有更为简单方式,这里不再细说,仅用动图演示: 从上面的基础应用来看,INDEX 函数用法其实很简单,如果想要发挥其威力,还是在于与其他函数的组合使用,尤其是它的「黄金搭档」——MATCH 函数。 之所以 MATCH 函数成为它的「黄金搭档」,主要是因为 MATCH 函数的结果可以返回数据的相对位置,完美充当 INDEX 函数的参数。 那么,下面我们先来介绍下 MATCH 函数的基本应用。 02、MATCH 函数MATCH 函数结果,会返回内容在单行或单列中的相对位置(指查找内容在查找区域中所处的位置,而非工作表中的位置)。其具体语法如下: =MATCH (查找对象,查找的范围,指定匹配方式) 有的小伙伴可能就要问了,如果在多行或多列的区域中查找呢? 这个就要借助 & 连字符了。 先用连接符 &,将多个条件组成的查找对象合并成一个新的条件; 再使用连接符将条件所在行或列的信息合并成一个新的查询区域。具休公式如下: =MATCH (组合后的查找对象,查找的范围 1 & 查找的范围 2, 指定匹配方式) 匹配方式: 1,表示函数会查找小于或等于「查询值」的最大值; 0,表示函数会查找等于「要查询值」的第一个值,即精确匹配; -1,表示函数会查找大于或等于「要查询值」的最小值。 例如,要查找符合「王一虎」和「中国近代史纲要」两个条件的数据相对位置,需要用 & 将两个条件合成一个新的查找对象。 具体公式如下: =MATCH(I11&J11,C2:C21&E2:E21,0)即可输出数据在所在区域内的相对位置:5。 注意: ❶ 如果在查询的区域中有多个查找值,则 MATCH 函数只会返回查找值第一次出现的位置; ❷ 查找文本值时,MATCH 函数不区分大小写字母。 03、总结回顾INDEX 和 MATCH 函数基本使用方法并不复杂,只是需要我们深刻的理解其语法,才能与其他函数灵活组合,解决日常所遇到的各种数据需求。 下面,我们再来回顾下相关知识点: ❶ INDEX 函数① 单个区域提取数据: INDEX (查询区域,行号,列号) ② 单行提取数据: INDEX (查询行,行号) ③ 单列提取数据: INDEX (查询列,列号) ④ 多区域中提取数据: INDEX ((区域 1, 区域 2), 行号,列号,所选则的第几个区域) ⑤ 隔行提数 ⑥ 构建等差数列 ❷ MATCH 函数。① 基本应用 MATCH (查找对象,查找的范围,指定匹配方式) ② 多条件应用 MATCH (组合后的查找对象,查找的范围 1 & 查找的范围 2, 指定匹配方式) 学会了 INDEX 和 MATCH 函数的基本用法,我们就能够解决工作与学习中,一些简单数据查找与提取的需求了。 我们在开篇时说过,要想 INDEX 函数实现更为复杂的需求。 如反向查找、多条件查找和双向查找,就需要将 INDEX 与其他函数结合起来使用。 本文来自微信公众号:秋叶 Excel (ID:excel100),作者:农夫,编辑:雅梨子、竺兰 广告声明:本文含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考。IT之家所有文章均包含本声明。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |