如何使用indirect引用另一个选项卡的每个单元格_使用INDIRECT引用COUNTIFS/SUMIF(S)公式中的单元格范围(出错)_如何让Excel中的indirect()函数的单元格引用自动更新？ - 腾讯云开发者社区

到需要显示图表的工作表，本例中为工作表Sheet2，单击功能区“公式”选项卡“定义的名称”组中的“定义名称”命令，定义一个名为“获取图表”的名称，其引用位置为： =IF(Sheet2!...$D$2="东区",INDIRECT("Sheet1!F9"),IF(Sheet2!$D$2="西区",INDIRECT("Sheet1!F10"),INDIRECT("Sheet1!...图3 其中，工作表Sheet2的单元格D2中设置了选项列表。 3. 在工作表Sheet2中，单击功能区“插入”选项卡“插图”组中的“图片——此设备”命令，任选一个图片插入到工作表中。...图4 此时，Excel会自动使用单元格D2中的值代表的图表替换原来的图片，结果如下图5所示。 ? 图5 小结：在Excel中，可以对在工作表中插入的图像赋值命名区域。...因此，当调整工作表中的单元格大小以容纳图表，通过INDIRECT()函数的公式创建间接引用并在命名区域中使用时，Excel会获取单元格（图表）的内容，并用它替换原来的图片。

1.4K2 0

Excel公式技巧14：在主工作表中汇总多个工作表中满足条件的值

本文提供了一种方法，在给定一个或多个相同布局的工作表的情况下，可以创建另一个“主”工作表，该工作表仅由满足特定条件的所有工作表中的数据组成。并且，这里不使用VBA，仅使用公式。...D2:D10"),"Y")) 如果不熟悉跨多个工作表使用公式的技术，那么应记下使用INDIRECT的这种公式构造，因为它实际上是我们执行此类计算的唯一方法。...D2:D10"}),"Y")) 因为COUNTIF函数能够操作三维单元格区域，并且SUMPRODUCT函数提供了必要的强制转换，使得INDIRECT函数返回一组单元格引用，而不仅仅是一个，因此公式转换为...实际上，该技术的核心为：通过生成动态汇总小计数量的数组，该小计数量由来自每个工作表中符合条件（即在列D中的值为“Y”）的行数组成，然后将公式所在单元格相对行数与该数组相比较，以便有效地确定公式所在行中要指定的工作表...为此，这里使用： IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry1),ROWS($1:1)) 为理解这个公式构造是如何工作的，我们可暂时将其作为独立的数组公式

8.8K2 1

您找到你想要的搜索结果了吗？

是的

没有找到

位逻辑运算的Excel公式

例如，如果单元格A1中的值是13，B1中的值是7，则逻辑与（AND）操作结果如下（或十进制数5）：定义位数这些函数对固定的位数进行操作。...根据所需的位数，使用功能区“公式”选项卡“定义的名称”组中的“定义名称”来定义常量位。...例如： 8位：=2^(8-ROW(INDIRECT("1:8"))) 16位：=2^(16-ROW(INDIRECT("1:16"))) 32位：=2^(32-ROW(INDIRECT("1:32"))...) 定义的名称如下图2所示。...逻辑函数对于整数x和y，或者引用包含整数的单元格： NOT x：如果x位是0则结果位是1： =SUMPRODUCT(1-MOD(INT(x/Bits),2),Bits) x AND y：如果x位和

2121 0

Excel: 引用数据源发生移动时，如何不改变引用的单元格地址

文章背景：在Excel中，公式引用无效单元格时将显示 #REF! 错误。当公式所引用的单元格被删除或被粘贴覆盖时最常发生这种情况。...在编写单元格公式时，不推荐在函数中使用显式单元格的引用(如："=A2"，"=A3"等)。当引用的数据源发生移动时，为了确保引用的单元格地址不变，可以配合使用indirect函数和address函数。...语法：INDIRECT(ref_text, [a1]) （2）ADDRESS 函数可以使用 ADDRESS 函数，根据指定行号和列号获得工作表中的某个单元格的地址。...需要得到其行号的单元格或单元格区域。如果省略 reference，则假定是对函数 ROW 所在单元格的引用。参考资料： [1] 如何更正 #REF!...，如何使引用的单元格不变（https://club.excelhome.net/thread-1096201-1-1.html?

3.6K3 0

如何在合并单元格使用公式计算装车时间

提问今晚在学员群里看到一个很有挑战性的问题图片 [图片] 大概的数据案例如下 [在这里插入图片描述] 解答第一想法是使用INDIRECT函数,例如第一个合并单元格,可以用下面得出答案 =INDIRECT...("C7")-INDIRECT("B2") 如果对INDIRECT函数不太了解,看一下我之前写的3篇第一篇:INDIRET函数导言第二篇:INDIRECT函数的应用之跨表引用第三篇:间接引用INDIRECT...据此反推我们要得到每一个合并单元格的开始行行号以及结束行行号首先我们用ROW函数列出行号 =ROW() [在这里插入图片描述] 接下来如何获得每个单元格最开始的行号(例如2)和最末尾的行号(例如7...,所以会自动统计合并单元格数量 ==这里有个小技巧:注意最开始的单元格是固定的,这样下拉会使范围越来越大== 用INDEX和MATCH求开始行和结束行好了,我们根据这两列可以求到每个合并单元格最开始的行号和列号了...最开始的行号=第一个合并单元格分组号最末尾的行号=第一个合并单元格分组号+组员数-1 [在这里插入图片描述] 使用MATCH函数找到第一个分组号,返回对应的辅助列1的内容,就是合并单元格最开始的行号

1K0 0

Excel公式技巧25：使SUMIFSCOUNTIFS函数内的间接列引用变化

使用Excel朋友都知道，将包含相对列引用的公式复制到其他列时，这些引用也会相应地更新。...因此，我们有一个相对简单的方法，可以从连续的列中获得条件和。但是，如果我们希望增加的单元格区域是间接引用的，那该怎么办？...例如，如果我们使用上述公式版本，但所引用的工作表是动态的： =SUMIFS(INDIRECT("'"&$A$1&"'!C:C"),INDIRECT("'"&$A$1&"'!...A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y") 其中，A1包含要引用的工作表名称（例如“Sheet1”）。当我们向右拖动此公式时，间接引用的单元格区域不会改变。...A:A 而偏移的列数等于： COLUMNS($A:B) 即2，于是传递到OFFSET函数后得到： Sheet2!C:C 然而，如果间接引用的不是一个工作表，而是多个工作表，如何处理？

2.5K2 0

Excel公式练习41：获取非连续单元格区域中只出现一次的数字

本次的练习是：如下图1所示，有一组非连续的单元格区域，由任意数量的单列区域组成，每个区域中的值有数字、文本或空格。...要求从单元格A2开始，使用公式生成一个列表，这个列表由上述非连续单元格区域中所有只出现了一次的数字组成（如图1所示，1、2和9这三个数字在非连续的单元格区域中只出现了一次）。 ?...首先，看看单元格A1中返回满足要求的数字数量的公式： =SUMPRODUCT(--(FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)=1)) 这里的重点是使用...另一个函数是MAX函数，也可以操作多个、非连续的单元格区域，因此： MAX(RNG) 能够得到组成RNG的单元格区域中所有数值的最大值，忽略逻辑值、文本。很显然，其返回的结果是9。...小结本示例展示了解决涉及到非连续单元格区域的问题的技术，以及哪些函数能够处理非连续单元格区域。此外，也讲解了AGGREGATE函数的使用技术。

1.4K3 0

Excel: 通过Indirect函数和Address函数引用单元格数据

文章背景：公式引用无效单元格时将显示 #REF! 错误。当公式所引用的单元格被删除或被粘贴覆盖时最常发生这种情况。因此，不推荐在函数中使用显式单元格引用。...通过Indirect函数和Address函数，可以实现单元格的间接引用。...如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数 INDIRECT。 INDIRECT(ref_text, [a1]) Ref_text 必需。...对包含 A1 样式引用、R1C1 样式引用、定义为引用的名称或作为文本字符串对单元格的引用的单元格的引用。如果ref_text不是有效的单元格引用，则 INDIRECT 返回#REF！错误值。...一个数值，指定要在单元格引用中使用的行号。 column_num 必需。一个数值，指定要在单元格引用中使用的列号。 abs_num 可选。一个数值，指定要返回的引用类型。 A1 可选。

4.6K2 0

Excel公式技巧17：使用VLOOKUP函数在多个工作表中查找相匹配的值（2）

我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。下面是3个示例工作表： ?...图4：主工作表Master 解决方案1：使用辅助列可以适当修改上篇文章中给出的公式，使其可以处理这里的情形。首先在每个工作表数据区域的左侧插入一个辅助列，该列中的数据为连接要查找的两个列中数据。...解决方案2：不使用辅助列首先定义两个名称。注意，在定义名称时，将活动单元格放置在工作表Master的第11行。...C:C"),$B11)>0,0) 名称：Arry2 引用位置：=ROW(INDIRECT("1:10"))-1 在单元格C11中的数组公式如下： =INDEX(INDIRECT("'"&INDEX(Sheets...因此，在单元格C11的公式中的： INDIRECT("'"&INDEX(Sheets,Arry1)&"'!D1:D10") 转换为： INDIRECT("'"&INDEX(Sheets,3)&"'!

13.5K1 0

Excel实战技巧85：从下拉列表中选择并显示相关的图片

在《Excel实战技巧22：在工作表中查找图片(使用VBA代码)》中，使用VBA代码来达到根据名称显示相应图片的效果。本文实现的效果相同，实现的方法类似，但可能更简单些。...如下图1所示，工作表中显示了图片名称和对应的图片。注意，确保每张图片在单个单元格内，因为我们下面将会引用图片所在的单元格。 ?...图3 然后，选择单元格区域B3:C10。单击功能区“公式”选项卡“定义名称”组中的“根据所选内容创建”命令，根据左侧列创建名称，如下图4所示。 ? 图4 这里运用了一个技巧，一次性创建了8个名称。...再次选择单元格E3，使用公式定义名称：名称：卡通人物照片引用位置：=INDIRECT(Sheet1!E3) 如下图5所示。 ?...图5 最后，选择单元格E3附近的单元格，在列C中任选一幅图片粘贴到该单元格中，并在公式栏中将该图片的名称修改为：=卡通人物照片，如下图6所示。 ? 图6 看看最终的效果，如下图7所示。 ?

6.3K1 0

求字符串中数字之和的高级方法

标签：Excel公式与函数，FILTERXML函数如下图1所示，在单元格B2中包含由逗号分隔的数字组成的字符串。...图1 现在，需要求这些数字之和，即： 15+6+2022+9+606+89+2=2749 如何编写公式来获得结果？...如果我们使用传统的Excel函数来编写，公式如下： =SUM(--(MID(SUBSTITUTE(B2,",",REPT("",LEN(B2))),(ROW(INDIRECT("1:" &(LEN(B2...单击功能区“公式”选项卡中的“定义名称”，在新建名称对话框中，输入名称： GetSum 在引用位置中输入公式： =EVALUATE(SUBSTITUTE(B2,",","+")) 定义好的名称如下图4所示...图4 现在，只输在工作表单元格中输入： =GetSum 即可获得结果，如下图5所示。图5 注意，如果使用这种方法，需要将工作簿保存为.xlsm文件。

1.2K5 0

Power Query技巧：更强大的拆分

最简单的是使用“快速填充”。在单元格C1中输入B1中的数字1，然后单击功能区“数据”选项卡“数据工具”组中的“快速填充”命令，结果如下图2所示。...可以在单元格C1中输入公式： =MID(B1,MAX((--(MID(B1,ROW(INDIRECT("1:" & LEN(B1))),1)=" "))*ROW(INDIRECT("1:" & LEN(...将其下拉至数据单元格末尾，结果如下图3所示。图3 虽然在列B中的数字改变时列C中的数字同步变化，但公式复杂。下面使用Power Query来解决。...1.单击功能区“数据”选项卡“获取和转换数据”组中的“获取数据——来自文件——从工作簿”。 2.在“导入数据”对话框中，选择数据所在的工作簿，单击“导入”按钮。...图7 下面，再尝试使用Power Query将文本拆分成多行，如上图1中的示例工作表，列A中单元格A1的数据为Excel和Power BI，想将其拆分成两行。

1.8K5 0

VBA表单控件（二）

应用示例 1、结合indirect函数上节介绍过数值调节钮可以让数值依次的变化，可以联想到单元格地址中的“行”是数字，所以如果数字随着数值调节钮变动，单元格的地址也可以变动，那么可以引用的单元格的值就可以随着变动...首先看姓名所在的单元格区域是A2:A7区域，即单元格地址中数值的变动区域是从2到7，步长变化为1。以此来设置数组调节钮的控件格式，设置最小值和最大值以及步长，单元格引用区域暂时放在F2单元格。...那么在单元格中"A"&F2就可以表示单元格地址，随着数值调节钮的变动从A2至A7，然后借助indirect函数公式 =indirect("A"&F2)来引用对应单元格的值。即得到需要的文本。...2、结合vlookup函数以同样的思路，vookup函数的查找值可以从示例1中的单元格中来直接变化，vlookup函数的第三参数，可以直接用另一个数值调节钮来调节。...注意公式中查找值为A1单元格，查找区域为A1:C1区域，返回列的值使用F5单元格的值可变动，=VLOOKUP(A1,A1:C1,F5,0)。返回值随着F5单元格数值的变化，最后显示结果如图所示。

1.4K3 0

Excel公式技巧19：在方形区域内填充不重复的随机整数

本文分享一个基于公式生成n×n随机整数的解决方案，并且每个整数都是唯一的。例如，下图1显示了生成10行10列的不重复随机整数。 ?...通常，将此矩阵放置在工作表中的某位置，对于输出结果的最左上角单元格的公式，引用的两个单元格区域包括： 1）10×10的单元格区域从最左上角的单元格正下方的单元格开始，向下并向右延伸。...2）最左上角单元格右侧的1×10单行单元格数组这里都是相对/绝对混合引用。...工作原理考虑使用FREQUENCY函数，不仅可以生成通常使用COUNTIF函数能够获得的结果，而且还可以操作由多个单元格区域组成的引用。让我们从示例中随便选择一个公式，看看其是如何工作的。...由于数组中的数字元素数等于100减去所引用的区域的元素数，因此可以将其用于RANDBETWEEN函数的top参数： 100-COUNTA($A9:$J$11,D8:$K8) 使用了COUNTA函数，可用于处理多个单元格区域

1K2 0

Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）

在某个工作表单元格区域中查找值时，我们通常都会使用VLOOKUP函数。但是，如果在多个工作表中查找值并返回第一个相匹配的值时，可以使用VLOOKUP函数吗？本文将讲解这个技术。...最简单的解决方案是在每个相关的工作表中使用辅助列，即首先将相关的单元格值连接并放置在辅助列中。然而，有时候我们可能不能在工作表中使用辅助列，特别是要求在被查找的表左侧插入列时。...B1:D10"),3,0) 其中，Sheets是定义的名称：名称：Sheets 引用位置：={"Sheet1","Sheet2","Sheet3"} 在公式中使用的VLOOKUP函数与平常并没有什么不同...，我们首先需要确定在哪个工作表中进行查找，因此我们使用的函数应该能够操作三维单元格区域，而COUNTIF函数就可以。...B:B"}),$A3) INDIRECT函数指令Excel将这个文本字符串数组中的元素转换为单元格引用，然后传递给COUNTIF函数，同时单元格A3中的值作为其条件参数，这样上述公式转换成： {0,1,3

20.7K2 1

Excel公式练习36：找到和的加数

如下图1所示，在单元格A2中给出了目标值47，在单元格C1:K1中有9个值，现在我们想知道这些值中哪些值相加等于47，在这些值的下方单元格中使用“X”标记。 ?...图1 在单元格C2中输入公式，然后拖放至单元格K2，得到结果。那么，如何编写这个公式呢？先不看答案，自已动手试一试。...名称：Data 引用位置：=$C$1:$K$1 名称：Arry1 引用位置：=ROW(INDIRECT("1:" &COLUMNS(Data))) 名称：Arry2 引用位置：=ROW(INDIRECT...名称：Arry1 =ROW(INDIRECT("1:"& COLUMNS(Data))) 转换为： =ROW(INDIRECT("1:" & 9)) 得到的结果为： ={1;2;3;4;5;6;7;8;...另一个是9行1列的数组）的乘积，得到一个512行1列的数组： {0;10;14;24;20;30;34;44;2;12;16;26;…;50;54;64;60;70;74;84} 这个数组由C1:K1中的

7393 0

Excel公式练习40：从单元格区域的字符串中提取唯一值

现在，想从该区域中提取单词并创建唯一值列表，如列B中的数据所示。 ? 图1 可以在单元格B1中编写一个公式，向下拖拉以创建该唯一值列表。如何编写这个公式呢？先不看答案，自已动手试一试。...名称：Arry1 引用位置：=1+LEN(Data)-LEN(SUBSTITUTE(Data,"","")) 名称：Arry2 引用位置：=ROW(INDIRECT("1:" &(MAX(Arry1)*...Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1))))) 在上述名称中，使用了另一个定义的名称...Data中每个单元格内单个单词的数量，除了其中第3行和第9行为空但仍返回不正确的数字1外。...使用Arry3替换掉上文中使用FREQUENCY函数求唯一值的公式中的单元格区域，并进行适当的调整，得到单元格B2中的公式： =IF(ROWS($1:1)>$C$1,"",INDEX(Arry3,SMALL

2.1K3 0

让你的Excel表动起来！！！

本次是第二次分享广大网友向我提问的经典问题。本周问题，利用名称管理器完成二维表的匹配返回！让你的表格动起来！话说！本次解决方案相对复杂，能看明白并且自己会用的基本上函数使用没什么问题了！...二、如何结果部门不同时，如何变成另外一个部门的数据三、如何将某个单元格设置为不同部门可选（三个问题中最简单的一个）解决问题一：如何在确定A部门的情况，将产品列在下表！...如何方便快捷的更改选择的区域呢？且随着某个单元格的内容变化而变化呢？介绍两个小东西： 1、名称管理器 2、Indirect函数名称管理器是啥，就是将某个区域命名为一个名字！...Indirect函数啥意思呢？含义此函数立即对引用进行计算，并显示其内容。通俗的讲。如果将B2：B9命名为部门A，则这个区域作为参数的时候，可以输入B2:B9，也可以输入Indirect（部门A）。...部门所在的单元格是B12，所以进行引用 =IFERROR(INDEX($A$1:$A$9,SMALL(IF(INDIRECT($B$12)="","",ROW(INDIRECT($B$12))),ROW

1.6K4 0

Excel公式练习72：提取大写字母创建缩写

本次的练习是：如下图1所示，使用公式，提取列A每个单元格数据中的大写字母。 ?...图1 满足以下条件：只提取大写字母每个单词以大写字母开始每个单词仅有一个大写字母 单元格中的数据文本可能包含空格，也可能没有空格 单元格中的数据文本只包含字母和空格 单元格中的数据文本可包含任意类型的字符...解决方案首先，提取每一字符，使用CODE函数将其转换成对应的数字，如果数字大于等于65且小于等于90，将该数字再转换成对应的字母，将大写字母连在一起。公式中，MID(x!...A5,ROW(INDIRECT("1:"&LEN(x!A5))),1)用于提取单个字符，也可以使用MID(A5,ROW(A$1:INDEX(A:A,LEN(A5))),1)，但前者更短。...z 引用位置：=CODE(MID(x!

1.9K4 0

INDIRECT函数的应用之跨表引用

书归正传.今天的目的是希望能通过举例,让你们明白INDIRECT函数的使用方法和原理,这样才能一通百通....跨表引用这是最常用的例子.很多表很变态,喜欢把1-12月的数据分成12个工作表来做,到了年底的时候呢,老板突发奇想想要看看一年的分布.嗯,拆分一时爽,全家xxx.但是老板要啊,你不能说不吧,所以这时候...,你需要INDIRECT函数变态的表长下面这样每个月都是同样的简单枯燥的统计,长下面这样要完成的绿色部分,在下面这里让我们先来看看INDIRECT怎么写的无论在里面写文本,还是引用单元格在被引用的单元格里写文本...(注意第一个单引号不算,要多写一个单引号),与直接等于的结果都是一样的.所以可以这么理解↓ =INDIRECT("'1月'!...B"" 第4部分是ROW(A$2),这个公式返回的其实就是一个数字2.只是为了下拉让它跟着变成3,所以用它 ROW($A2) 最后使用连接字符&来连接就可以了 =INDIRECT("'"&B 所以,

2.2K1 0

点击加载更多

扫码

添加站长进交流群

领取专属 10元无门槛券

手把手带您无忧上云

Excel图表技巧05：自由选择想要查看的图表

Excel公式技巧14：在主工作表中汇总多个工作表中满足条件的值

位逻辑运算的Excel公式

Excel: 引用数据源发生移动时，如何不改变引用的单元格地址

如何在合并单元格使用公式计算装车时间

Excel公式技巧25：使SUMIFSCOUNTIFS函数内的间接列引用变化

Excel公式练习41：获取非连续单元格区域中只出现一次的数字

Excel: 通过Indirect函数和Address函数引用单元格数据

Excel公式技巧17：使用VLOOKUP函数在多个工作表中查找相匹配的值（2）

Excel实战技巧85：从下拉列表中选择并显示相关的图片

求字符串中数字之和的高级方法

Power Query技巧：更强大的拆分

VBA表单控件（二）

Excel公式技巧19：在方形区域内填充不重复的随机整数

Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）

Excel公式练习36：找到和的加数

Excel公式练习40：从单元格区域的字符串中提取唯一值

让你的Excel表动起来！！！

Excel公式练习72：提取大写字母创建缩写

INDIRECT函数的应用之跨表引用

扫码

相关资讯

热门标签

活动推荐

运营活动

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐