内容字号:默认大号超大号

段落设置:取消段首缩进段首缩进

字体设置:切换到微软雅黑切换到宋体

业界资讯软件之家
Win10之家WP之家
iPhone之家iPad之家
安卓之家数码之家
评测中心智能设备
精准搜索请尝试:精确搜索

小白课堂:必备的Excel报表技巧,除了VLOOKUP,还有INDEX+MATCH

2018-7-19 14:29:23来源:IT之家作者:溪风/实习责编:溪风/实习评论:

IT之家小白课堂,小白也能看懂的教程,喜欢就点收藏 :)

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)

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

销售报表案例表格:点此下载

相关文章

关键词:业界动态

IT之家,软媒旗下科技门户网站 - 爱科技,爱这里。

Copyright (C)RuanMei.com, All Rights Reserved.

软媒公司版权所有