使用公式为整个列创建可用的引用

Question

我有一个电子表格，需要引用整个列，但是列上的位置不是提前确定的，所以我使用了索引匹配、地址和单元格函数来查找。一旦找到正确的列，就需要在其中引用单元格。

有两个问题阻止了我：

• 我有以单词或字母作为数据的列和以数字作为数据的列。
• 我需要简单地引用整个列，因为行的数量也在变化。
• ，我尝试过的公式不起作用，它只允许我查看位置。

我试着使用下面的公式，得到了我想要的输出，但是它只是显示地址，当我把它放在公式中时，它就不工作了。我需要一些我可以在公式中作为参考的东西。

国家公式

=LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Ship"&"*"&"State"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1,"Extracted Sales Data"),28)&":"&LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Ship"&"*"&"State"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1),1)

收入金额公式

=LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1,"Extracted Sales Data"),28)&":"&LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1),1)

上面的公式返回以下值，但我不能将其用作引用(请注意，列不是预先确定的)

状态列

'Extracted Sales Data'!G:G

收入数额栏：

'Extracted Sales Data'!AQ:AQ

我试着在SUMIF和COUNTIF函数中使用它。

=SUMIF('Extracted Sales Data'!G:G,M16,'Extracted Sales Data'!AQ:AQ)

或

=COUNTIF('Extracted Sales Data'!G:G,M16)

样本数据：

一种类型的数据集：

Ship-to-State          Trx Type     RC Revenue Amount
Alabama                INVOICE      50000 
California             INVOICE      3000

另一种类型的数据集

Customer          Ship to State          Trx Type     Expense Amount     Revenue Amount
Customer A        Alabama                INVOICE      21000              50000 
Customer B        California             INVOICE      1680               3000

我需要两个数据集的一个公式，我如何实现这一点？(根据查找/匹配引用整列。)

Answer 1

您只需要索引/匹配部分：

INDEX('Extracted Sales Data (2)'!B7:AI200000,0,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0))

使用第二个条件中的0，它将返回完整的列。

因此，您只需在公式中使用这些：

=SUMIF(INDEX('Extracted Sales Data (2)'!B7:AI200000,0,MATCH("*"&"Ship"&"*"&"State"&"*",'Extracted Sales Data (2)'!B7:AI7,0)),M16,INDEX('Extracted Sales Data (2)'!B7:AI200000,0,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0)))

对COUNTIF做同样的事情