UMIFS/COUNTIFS函数内的间接列引用变化
发布时间:2022-02-26 04:47:13
使用Excel朋友都知道,将包含相对列引用的公式复制到其他列时,这些引用也会相应地更新。例如,公式:
=SUMIFS(C:C,$A:$A,”X”,$B:$B,”X”)
向右拖放时,将会变成:
=SUMIFS(D:D,$A:$A,”X”,$B:$B,”X”)
=SUMIFS(E:E,$A:$A,”X”,$B:$B,”X”)
等等。
因此,我们有一个相对简单的方法,可以从连续的列中获得条件和。
但是,如果我们希望增加的单元格区域是间接引用的,那该怎么办?例如,如果我们使用上述公式版本,但所引用的工作表是动态的:
=SUMIFS(INDIRECT(“‘”&$A$1&”‘!C:C”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
其中,A1包含要引用的工作表名称(例如“Sheet1”)。
当我们向右拖动此公式时,间接引用的单元格区域不会改变。当然,这是完全可以预料的,因为那些“单元格区域”根本不是真正的单元格区域,而只是伪装成单元格区域的文本字符串,只有通过将它们传递给INDIRECT函数才能将其解释为实际的单元格区域。
现在的问题是:我们如何修改第一个公式,以便将其向右复制后,依次获得以下等价的公式:
=SUMIFS(INDIRECT(“‘”&$A$1&”‘!D:D”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
=SUMIFS(INDIRECT(“‘”&$A$1&”‘!E:E”),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
等等。
可以使用INDEX函数来解决:
=SUMIFS(INDEX(INDIRECT(“‘”&$A$1&”‘!A:XFD”),,COLUMNS($A:C)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
注意,当省略INDEX函数的参数row_num时,会返回整列引用,对于参数column_num也是如此。
如果A1中的值是“Sheet2”,则:
INDEX(INDIRECT(“‘”&$A$1&”‘!A:XFD”),,COLUMNS($A:C))
转换为:
=INDEX(Sheet2!A:XFD,,3)
即:
Sheet2!C:C
向右复制,公式成为:
=SUMIFS(INDEX(INDIRECT(“‘”&$A$1&”‘!A:XFD”),,COLUMNS($A:D)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
转换为:
=SUMIFS(INDEX(Sheet2!A:XFD,,COLUMNS($A:D)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
转换为:
=SUMIFS(INDEX(Sheet2!A:XFD,,4),Sheet2!A:A,”X”,Sheet2!B:B,”Y”)
转换为:
=SUMIFS(Sheet2!D:D,Sheet2!A:A,”X”,Sheet2!B:B,”Y”)
……
也可以使用OFFSET函数:
=SUMIFS(OFFSET(INDIRECT(“‘”&$A$1&”‘!A:A”),,COLUMNS($A:B)),INDIRECT(“‘”&$A$1&”‘!A:A”),”X”,INDIRECT(“‘”&$A$1&”‘!B:B”),”Y”)
其中:
INDIRECT(“‘”&$A$1&”‘!A:A”)
转换为:
Sheet2!A:A
而偏移的列数等于:
COLUMNS($A:B)
即2,于是传递到OFFSET函数后得到:
Sheet2!C:C
然而,如果间接引用的不是一个工作表,而是多个工作表,如何处理?例如公式:
=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&Sheets&”‘!C:C”),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))
其中,“Sheets”是定义的名称,引用位置为:
={“Sheet1″,”Sheet2″,”Sheet3″,”Sheet4”}
像前面一样,我们希望向右拖拉时,公式变化为:
=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&Sheets&”‘!D:D”),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))
=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&Sheets&”‘!E:E”),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))
等等。
仍然可以使用OFFSET函数:
=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT(“‘”&Sheets&”‘!A:A”),,COLUMNS($A:B)),INDIRECT(“‘”&Sheets&”‘!A:A”),”X”,INDIRECT(“‘”&Sheets&”‘!B:B”),”Y”))
其原理与上文所解的单个工作表的版本相同。
猜你喜欢
- 使用Word文档时我们不仅可以编辑和处理文字内容,我们还可以在其中插入表格,图片,形状等内容并使用自动出现的相应工具对其进行编辑和处理。比如
- office 2013 打不开旧版PPT文件一直报错怎么办?安装新版 office 2013后,发现很多旧版的 ppt 文件打不开,显示报错
- 相信许多对WORD不是很了解或是使用很少的朋友,当换了新工作,或学习新的事物的时候,会需要使用WORD插入图片,那么word怎么插入漂亮的图
- 许多文稿,特别是比较正式的文稿都需要设置页码。得体的页眉和页脚,会使文稿显得更加规范,也会给阅读带来方便。那么下面就由小编给大家分享下wor
- word2013中输入上下标的步骤如下:步骤一:首先,打开Word2013,点击菜单“插入”-“公式”。 步骤二:
- 考试结束,教育部门要求全市学生的成绩都汇总在如图1的同一个Excel工作表中,并要求求出各学校、各专业、各学科的平均分。 &n
- 1、找到一篇 PDF 文档,选择用 Word 打开。 如果手头没有现成的 PDF 文档,可以百度一篇,或到zf类、
- Excel2016怎么计算重复次数?Excel2016表格中想要看看某几个文字或者数字出现的次数,该怎么统计呢?下面我们就来看看excel统
- excel怎么做共词矩阵?excel表格中想要制作共词矩阵,该怎么制作呢?下面我们就来看看制作简单数字的4列矩阵矩阵做共词矩阵演示的方法,需
- 在看视屏时,默认情况下我们都会全屏观看,想必这一是很多人的习惯了,那么如果看一篇文章该如何全屏呢?其实Word2013中可以通过隐藏阅读工具
- 在使用win10系统过程中,有的朋友会碰到无法打开左下角的情况,如果碰到此类问题改如何解决呢,其实只要简单几步就可以解决了.问题表现:1、左
- word中的替换功能不仅可以批量替换内容,还可以批量删除内容。一、批量删除空格1.点击【替换】
- 选中文档,点击视图--显示比例面板---点单排即可!
- 你试着按如下操作调整一下,看能否解决你提出的问题。1、文件-页面设置-文档网络-网格-改成每行40个字符2、工具-选项-中文版式-控制字符间
- 方法如下:选项>高级>显示>硬件图形加速office 2013 卡顿解决办法1office 2013 卡顿解决办法2off
- 1、百度文库的下载机制是有的需要积分,有的不需要积分,而复制是完全免费的,复制方法也很简单。只需要选中文库中需要的文字,复制就可以了。 &n
- ①首先是在Word2003中绘制一张表格,打开Word,点击菜单栏表格--插入--表格。 ②在插入表格界面中选择行
- 在EXCEL中如何建立华丽的分割线?很多朋友都不是很清楚,其实方法很简单的,下面小编就为大家详细介绍一下,大家一起来看看吧EXCEL统计数据
- 在Excel文档的编辑过程中,有时候钱的金额会有大写来表示,有可能遇到需要输入【壹、贰、叁】等大写中文数字的情况,其实在excel2007直
- 相信很多人都遇到过Excel表格中的数据有很多行,想要从这些数据中筛选出符合特定规则的数据但不知道如何下手,其实非常简单,下面小编就为大家详