摘要： 使用Excel朋友都知道，将包含相对列引用的公式复制到其他列时，这些引用也会相应地更新。例如，公式： =SUMIFS(C:C,$A:$A,”X”,$B:$B,”X”)
向右拖放时，将会变成：
=...
使用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”)
转换为：
Excel公式中的降维技术
看过前面一系列文章的朋友，一定会熟悉“重新定义数组维度”的概念。这是一项非常有用且非常重要的技术，使我们可以接受二维数组并将其转换为一维数组，同时将元素保留在该数组中。 如果希望进一步操纵某二维数组的元素，则需要使用这种技术。例如，由于某种原因，在某种情形下，需要将二维数组中的每个元素传递给一个或多个参数进行进一步处
=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”))
其原理与上文所解的单个工作表的版本相同。
excel怎么同时定位字符串中的第一个和最后一个数字
在很多情况下，我们都面临着需要确定字符串中第一个和最后一个数字的位置的问题，这可能是为了提取包围在这两个边界内的子字符串。然而，通常的公式都是针对所需提取的子字符串完全由数字组成，如果要提取的数字中有分隔符（例如电话号码）则无法使用。当然，可以先执行替换操作来去掉字符串中的分隔符，这可能会更复杂些。 本文仅涉及被提取
