主题: 让你的工作事半功倍 ——浅谈index(match())组合函数

  • 上都驿站网络工作室
楼主回复
  • 阅读:12878
  • 回复:1
  • 发表于:2018/10/12 16:13:57
  1. 楼主
  2. 倒序看帖
  3. 只看该作者
马上注册,结交更多好友,享用更多功能,让你轻松玩转上都社区。

立即注册。已有帐号? 登录或使用QQ登录微信登录新浪微博登录

让你的工作事半功倍

——浅谈index(match())组合函数

 



摘要:Excel作为一款功能强大的表格软件,被广泛应用于各类办公领域,尤其对各位财务工作者来说,简直可以说是既熟悉又亲切。它大大提高了广大办公人员的工作效率,和以往的纸质记录相比,它让数字资料更加直观、便于修改、保存和携带,同时,由于它自带了很多方便的函数,可以轻松的对一系列数字进行运算操作,大大简化了办公人员的工作强度。另外,Excel的图表功能可以将数据表格转换为清晰明了的图形,便于分析数据变化趋势,找准业务关键点,以便决策者迅速做出反应。

而对于广大从事财务工作的人员来说,最实用的莫过于它的函数功能了。平时工作中用到的快速求和,求平均数以及稍微复杂一点的单元格引用四则运算都属于函数范畴。但是,笔者认为,仅仅知道一点运算功能,在目前的环境下,已经满足不了实际工作需要了。因此,经过了解和实际应用,笔者在此向大家科普一个更加强大的函数组合——index(match())函数。

一、index(match())组合函数的简介

    index(match())是index()和match()两个函数的组合,搭配使用可以从众多数据中提取需要的目标数据。

(一)index函数

定义:返回表或区域中的值或对值的引用。它有两种形式:数组形式和引用形式。数组形式通常返回数值或数值数组;引用形式通常返回引用。(由于实际操作中一般很少使用数组,本文只讲引用形式)

语法:index(引用区域,行号,列号)。返回引用中指定单元格或单元格区域的引用。其中,index代表函数名,括号(英文括号)内含三个参数,参数之间用英文小标逗号隔开,“行号“,“列号”两个参数可以根据情况只写一个。

参数:

“引用区域”是一个单元格区域。

“行号”用于选择要从中返回值的数组中的行。

“列号”用于选择要从中返回值的数组中的列。

(二)match函数

语法:match(查找单元格, 查找区域,匹配类型)。返回指定数值在指定数组区域中的位置。

同样包括三个参数:

“查找单元格”参数:需要在数据表(“查找区域”)中查找的值。可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。如果“查找单元格”参数为文本字符串,可以使用通配符星号 (*) 和问号 (?)。星号可以匹配任何字符序列;问号可以匹配单个字符。

“查找区域”参数:可能包含有所要查找数值的连续的单元格区域,区域必须是某一行或某一列,即必须为一维数据,引用的查找区域是一维数组。

“匹配类型”参数:表示查询的指定方式,用数字-1、0或者1表示,“匹配类型”省略相当于匹配类型为1的情况。

为1时,函数查找小于或等于“查找单元格”的最大数值,“查找区域”必须按升序排列

为0时,函数查找等于 “查找单元格”的第一个数值,“查找区域”可以按任何顺序排列

为-1时,函数查找大于或等于 “查找单元格” 的最小数值,“查找区域”必须按降序排列

如果省略 “匹配类型”,则假设为 1,由于实际使用中很少用到除0以外的两个参数,所以本文只讨论参数为“0”的情况。

二、函数的具体应用

以(图1)月报图为例:

 登录查看大图
登录/注册后可查看大图




                 图(1)

假设我们需要找到支出功能分类科目分别为201、204、205、210这四项对应的年累计金额,则需要做如下处理。

第一步:首先,在其他单元格中列出需要筛选的科目代码,如图(2)所示:

 登录查看大图
登录/注册后可查看大图




 

图(2)

其次,利用match()函数找到每个科目对应的行数。

如图(3)所示,我们在C17单元格输入以下公式:=match(B17,$A$6:$A$15,0)。该公式的意思是,在A6:A15单元格范围内查找B17的值,如果找到,返回A6:A15区域对应的行数。按回车键后将显示1。说明在A6:A15单元格区域的第1行找到了B17单元格的“201”分类科目。

利用Excel的自动完成功能,可以在C17单元格右下角当鼠标变成小黑十字框时向下拖动,可以迅速找到其他单元格对应的行数。如图(4)。

注意:引用A6:A15单元格区域时,为防止向下拖动时引用的单元格区域发生变化,所以在行标列标前都加了“$”符号,表示绝对引用。

 登录查看大图
登录/注册后可查看大图




                    图(3)

 登录查看大图
登录/注册后可查看大图




                    图(4)

第二步:利用index()函数找到相关科目对应年累计金额。

根据index()函数定义,在C17单元格目前的基础上做如下修改“=index($C$6:$C$15,MATCH(B17,$A$6:$A$15,0))”这条语句中“MATCH(B17,$A$6:$A$15,0)”部分目前的返回值是1。所以整句可以看做“=index($C$6:$C$15,1)”代表从C6:C15(同样使用了绝对引用)单元格区域中的第1行中取数。由于A6:A15和C6:C15区域是一一对应的关系,所以最终取到的数就是“201”科目的累计金额“10938277.73”,见图(5)。同理,利用自动完成功能拖动黑色十字完成其他部分(图(6))。至此,就利用index(match())函数完成了寻找部分科目对应数据的工作。

 登录查看大图
登录/注册后可查看大图




                  图(5)

 登录查看大图
登录/注册后可查看大图




                  图(6)

利用这两个函数组合可以轻松的筛选设定了条件的单元格对应内容。实际中遇到的表格往往有成百上千条记录,这时候就是这两个函数大显神威的时刻了。

三、函数使用的局限性

虽说这个组合函数功能强大,但是也有其固有的局限性。主要局限性有:

(一)只能筛选唯一科目。假如同一个科目对应有两个值,或者有科目有重复记录,则只能筛选出第一个值,如图(7)所示:

 登录查看大图
登录/注册后可查看大图




图(7)

(二)match()函数中,查找单元格内容和查找区域格式内容必须完全一致。图(8)中,我将查找单元格“201”、“204”、“205”、“210”设为了文本格式,因为在A6:A14单元格区域中的分类编码都是数字格式,所以无法匹配到对应数值,因此导致出现#N/A这种错误值的情况。

 登录查看大图
登录/注册后可查看大图


                           图(8)


(三)match()函数中,“查找单元格”参数不区分大小写。如图(9)所示,查找的内容虽然是大写的“ABC”对应金额,但是匹配到的数值却是小写“abc”对应的金额。查找的是“Efg”对应的金额,匹配到的是“EFG”对应的金额。这点在使用中要注意。

 登录查看大图
登录/注册后可查看大图




图(9)

 

 

 

 

正蓝旗财政局国库股

                                 王虹强



                                                       2018年10月9日

 
关注同城热点 获取最新资讯 点击查看更多本地热点话题
  
  • 上都驿站网络工作室
楼主回复
  • 发表于:2018/10/12 16:19:19
  1. 沙发
  2. 倒序看帖
  3. 只看该作者
  
二维码

下载APP 随时随地回帖

你需要登录后才可以回帖 登录 | 注册 QQ登陆 微信登陆 新浪微博登陆
加入签名
Ctrl + Enter 快速发布