Excel报表技巧Index+Match函数用法

Excel Match 函数用法

1、match 函数

= MATCH ( lookup_value , lookup_array , match_type)

lookup_value 为需要在数据表中查找的数值。可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用

lookup_array 应为数组或数组引用，可能包含所要查找的数值的连续单元格区域。

match_type，为数字 -1、0 或 1， 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。

示例：

2、index 函数

= INDEX ( array , row_num , column_num )

array 为单元格区域或数组常量

row_num 数组中某行的行序号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。

数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 row_num。

多与MATCH函数连用

示例：

Index + Match 函数用法

Excel 函数公式里，VLOOKUP是经典的查找引用函数。

CP组合INDEX+MATCH，操作上更灵活，很多时候能替代VLOOKUP。

举例，你有一份客户资料表，包括客户名称、区域、省份等。

现在需要在销售明细表里，填入客户名称，自动输出对应的区域、省份。

效果图如下：

怎么操作？可以通过VLOOKUP函数实现，不过IT之家下面讲解的案例，主要采用INDEX+MATCH。

首先看下各个函数的基本含义，以及通俗语法。

1、INDEX函数：返回表中的值。

=INDEX（在哪儿找，第几行）

2、MATCH函数：返回指定数值在指定区域中的位置。

=MATCH（找谁，在哪儿找，匹配方式）

3、VLOOKUP函数：纵向查找返回表中的值。

缺点：查阅值需要位于查找区域的第一列。

=VLOOKUP（找谁，在哪儿找，第几列，匹配方式）

下面看看销售报表案例里，INDEX+MATCH嵌套函数的写法。

A3处的公式如下

=INDEX(G:G,MATCH(C3,H:H,0))

这是一个嵌套函数，先计算MATCH函数，再将结果作为参数，计算INDEX函数。

首先，MATCH在H列查找匹配，结果为4，即C3的值在H列里位于第4行。

然后将MATCH的结果4作为参数，INDEX在G列查找返回结果G4“广东”。

在销售明细表里，把这个公式往下拉，那么每次输入客户名称，就会自动输出客户所在省份。这样可以避免手动输错的问题，也能节省一些时间。

同样的案例，用VLOOKUP函数怎么写？需要将案例中的G列、H列顺序对调，保证要查找的客户名称列，在查找区域首列。

A3处的公式如下。

=VLOOKUP(C3,G:H,2,0)

如果销售表、客户表不在同一张表里呢？很简单，公式前加入“工作表名称!”即可，如下。

=INDEX(客户!C:C,MATCH(D2,客户!D:D,0))

报表里还有一个非常重要的功能：数据验证（早期Office版本里叫数据有效性）。有什么作用？可以规范输入的数值格式，同时，还能提供下拉框来选择输入数值。

如果输入的数值不符合规定，则弹窗提示无法输入。本案例中，就能避免出现“中国联通”“联通”“联通公司”这样的多种写法。当多人维护同一份表格，再也不怕大家乱输数值了。同理，品名规格列也可以做数据验证。

操作路径：销售表里全选D列，菜单栏-数据-数据验证，允许选择“序列”，来源输入下面的公式。

=OFFSET(客户!$D$2,,,COUNTA(客户!$D:$D)-1)

公式含义：销售表里的客户名称列，只能输入客户表里已记录的客户名称。注意要加入绝对引用符号“$”，保证参数数值不变。

参考推荐：

Excel $符号等用法详解

Excel 常见函数的用法详解

Excel 多条件判断函数 IF、IFS、Choose 用法详解

MySQL 数据类型

MySQL 查询语句取整数或小数

MySQL 中的 NULL 和 空字符(”) 区别

MySQL 正则表达式查询

MySQL 时间函数加减计算

MySQL 日期和时间函数详解

MySQL 连接查询join详解

MySQL 联合查询union详解

MySQL 函数 group_concat

MySQL 中 case when 语句用法

MySQL 中 distinct 和 group by 性能比较

MySQL中distinct和group by过滤删除重复行

MySQL 数据库 User表权限以及用户授权详解

MySQL 字段中区分字符串大小写的解决方法

MySQL 字段的添加前缀、去掉前缀等字符串操作

MySQL中大于小于，IN，OR，BETWEEN性能比较

PHP MySQL中 uft-8中文编码乱码的解决办法

MySQL 中 insert ignore into, replace into 用法总结