码迷,mamicode.com
                                                                              首页 > 其他好文 > 详细

                                                                              Excel高手必备的最为灵活实用的引用函数:Indirect

                                                                              时间:2019-05-09 13:53:12      阅读:30      评论:0      收藏:0      [点我收藏+]

                                                                              标签:body   图片   bin   har   填充   完全   一点   工资   公式   

                                                                               
                                                                               
                                                                               

                                                                              技术图片

                                                                               

                                                                              一、什么是Indirect函数?

                                                                              Indirect函数称之为间接引用函数,间接引用当然是直接引用而言的。直接引用非常简单,我在?#25105;?#19968;个单元格输入公式:“=a1”,那么将得到A1单元格的值。而间接引?#36855;?#23436;全不同。如果我想通过间接引用函数返回a1单元格的值,我输入的公式为:=indirect(‘a1‘,1),这样才能得到A1的值。那么间接引用的优势是是什么呢?文章开头我已经说了答案了,那就是非常灵活,大家注意到indirect函数的参数加了引号,表示是文本。既然是文本,我们就可以灵活地编辑引用地址。这一点是直接引用做不到的。下面简单来看一下这个函数吧:

                                                                              技术图片

                                                                              indirect函数

                                                                              例如:在下图中,我们输入“=indirect(‘d1‘)”,公式将会直接计算d1单元格的值,也就是a1;而如果我们输入“=indirect(d1)”,公式将直接引用D1单元格的内容“a1”,然后再返回‘a1‘单元格的值,最后得到的结果就是“indirect函数”(简单来说计算了2次,第一次:由“=indirect(d1)”得到“=indirect(‘a1‘)”;第二次:由“=indirect(‘a1‘)”得到最终的值“indirect函数”)

                                                                              技术图片

                                                                              indirect函数案例

                                                                              indirect函数如此灵活,在工作中我们如何去应用,下面我们先来看一个简单案例。

                                                                              二、利用Indirect函数跨表统计工资

                                                                              如下图所示,如何汇总不同sheet中的工?#39318;?#39069;汇总到下表中呢?2010年到2012年三个sheet中的表格结构完全一样,如下图中右下角表格所示。

                                                                              技术图片

                                                                              源数据

                                                                              很多同学可能会想到把这三张表全部复制?#31243;?#21040;一个sheet里在进行统计,然而在sheet很多的情况下,通过这样的办法效率实在是太低。毫不夸张地说,你有可能花上一个小时都干不完的活,Excel高手一条公式一分钟?#23478;?#19981;了就搞定了。下面我们来看看利用Indirect函数来做。

                                                                              思路:首先得到每个sheet的B列,然后外层嵌套一个sum函数求?#22270;?#21487;得到每年的汇总了。公式如下:

                                                                              =SUM(INDIRECT(A12&‘!B:B‘))

                                                                              向下拖拽公式,轻松完成个sheet的统计。

                                                                              indirect函数,直接引用a12得到sheet的名称,使用“&”链接上“!b:b”,引用的是X表格B列的区域,外加一个sum函数即可汇总每年的销售总额。

                                                                              三、利用indirect函数快速做数据表转置

                                                                              还是先来看看咱们的需求,如何将左侧表格快速转化?#20063;?#34920;格的样式呢?方法有很多,包括利?#36855;?#20204;之前介绍过的index函数都可以轻松做出来,下面我们来介绍如何利用Indirect函数来做。

                                                                              技术图片

                                                                              从左边的样式?#25509;?#36793;的样式

                                                                              思路:

                                                                              1.要得?#25509;也?#30340;表格的样式,我希望将左侧表格的每一个单元格名称直接放在?#20063;?#34920;中,如下图所示:

                                                                              技术图片

                                                                              首先我们想办法得到这样的引用样式

                                                                              如何得到这样的样式呢?由于我们是由一列变成多行多列,因此列号不变,都是A,唯一变的是行号。我们要思考的是当我们向右填充公式时,行号能够从左向右?#26469;?#22686;大(1,2,3,4……),向下填充公式时,行号能够以等差数列的方式增大(0,4,8,12)?针对第一个需求,我们可以用公式“=column(a1)”来做,针对第二个需求,我们可以用这样一个公式“=(row(a1)-1)*4”来实现,因此要得到上图中的地址,我们可以利用如下的公式得到:

                                                                              =‘A‘&33+column(a1)+(row(a1)-1)*4

                                                                              2.在我们得到单元格地址外层嵌套一个indirect函数即可得到咱们?#20063;?#22270;表的样式了。最终公式为:

                                                                              =indirect(=‘A‘&33+column(a1)+(row(a1)-1)*4)

                                                                              技术图片

                                                                              一条公式搞定

                                                                              轻松搞定了一列变多列的数据转置了吧?

                                                                              四、利用Indirect函数做多表数据条件汇总

                                                                              如下图所示,如何快速统计:2010年到2012年这三年中,业绩超过150万的人数?

                                                                              技术图片

                                                                              每个sheet的表格结构都和上图右下角的一致

                                                                              思路:

                                                                              首先利用indirect函数得到所有sheetB列的数据,下一步利用countif函数进行人数统计就可以了。

                                                                              1.这个引用唯一的变量是表格(年份数字是变量)名称,我们可以利用函数row得到:=row(2010:2012)。因此我们的indirect函数可以这样写:

                                                                              =indirect(row(2010:2012)&‘年!b:b‘)

                                                                              2.接下来就简单了,在完成嵌套一个countif函数就可以完成条件计数的汇总了。最终公式如下:

                                                                              {=COUNTIF(INDIRECT(ROW(2010:2012)&‘年!b:b‘),‘>150‘)}

                                                                              Excel高手必备的最为灵活实用的引用函数:Indirect

                                                                              标签:body   图片   bin   har   填充   完全   一点   工资   公式   

                                                                              原文地址:https://www.cnblogs.com/wanzhongjun/p/10837811.html

                                                                              (0)
                                                                              (0)
                                                                                 
                                                                              举报
                                                                              评论 一句话评论(0
                                                                              0条  
                                                                              登录后才能评论!
                                                                              ? 2014 mamicode.com 版权所有 京ICP备13008772号-2
                                                                              迷上了代码!
                                                                              宁夏11选5开奖查询