Excel:查找行和列的交集 [英] Excel: Find intersection of a row and a column

问题描述

我的问题是如何找到特定列和行号的相交单元格?

My question is how can I find an intersecting cell of a specific column and row number?

我的情况是这样的:通过一些计算，我发现了两个单元格，例如 B6 和 E1 .我知道我需要第一个的行和第二个的列.因此，我可以只使用 ROW 和 COLUMN 函数来获取数字.之后，我需要找到一个相交的单元格.在此示例中为 E6 .

My situation is this: with some calculations I find two cells, lets say B6 and E1. I know that I need a row of the first one and a column of the second one. So I could just use ROW and COLUMN functions to get the numbers. After that, I need to find an intersecting cell. Which would be E6 in this example.

我只使用 INDEX(A1:Z100; ROW; COLUMN)，但我不知道我需要的确切区域-它取决于其他内容.我可以使用类似 A1:XFG65000 的方法，但这太la脚了.我也可以使用 INDIRECT(ADDRESS())的组合，但是我要从已关闭的工作簿中提取数据，因此 INDIRECT 将不起作用.

I would just use INDEX(A1:Z100;ROW;COLUMN) but I don't know the exact area that I'm going to need - it depends on other stuff. I could use something like A1:XFG65000 but that is way too lame. I could also use a combination of INDIRECT(ADDRESS()) but I'm pulling data from a closed workbook so INDIRECT will not work.

如果这将有助于您了解全部用途-这是一个具体示例: 我需要找到要处理的工作表部分的限制.我知道它从 B 列开始，一直到该列的最后一个非空单元格.该范围以最后一列结尾，该列在第一行中具有任何值.所以要定义它-我需要找到最后一列和最后一行与B列中的值的交集. 我使用此数组公式查找最后一列:

If this would help to know what is this all for - here's a concrete example: I need to find limits of a section of a sheet that I would work with. I know that it starts from the column B and goes all the way down to the last non-empty cell in this column. This range ends with a last column that has any value in first row. So to define it - I need to find the intersection of this last column and the last row with values in B column. I use this array formula to find the last column:

INDEX(1:1; MAX((1:1<>")*(COLUMN(1:1))))

这个数组公式可以找到最后一行:

And this array formula to find the last row:

INDEX(B:B; MAX((B:B<>")*(ROW(B:B)))

最后一列为 E1 ，最后一行为 B6 .现在，我需要将范围定义为 B1:E6 ，如何从所有这些中获取 E6 并放入生成的公式中?我已经思考了一段时间，没有成为Excel专家-我什么都没想.因此，任何帮助将不胜感激.谢谢！

Last column results in E1 and last row results in B6. Now I need to define my range as B1:E6, how can I get E6 out of this all to put into the resulting formula? I've been thinking for a while now and not being and Excel expert - I couldn't come up with anything. So any help would really be appreciated. Thanks!

推荐答案

您可以使用索引/匹配"组合并使用匹配"来找到相关的单元格.对行使用一个Match()，对列使用一个Match().

You can use an Index/Match combination and use the Match to find the relevant cell. Use one Match() for the row and one Match() for the column.

索引/匹配函数，用于查找工作表中的最后一个单元格

The index/match function to find the last cell in a sheet where

• 列B是最左侧的表列
• 第1行是最上面的表行
• B列和第1行中的数据可以是文本和数字的组合
• B列和第1行中可以有空单元格
• B列中最后一个填充的单元格标记表的最后一行
• 第1行中最后一个填充的单元格标记表的最后一列

在这些前提下，以下将返回正确的结果，将其用于Sum()中，以A1作为起始单元格，并使用Index返回范围的右下方单元格:

With these premises, the following will return correct results, used in a Sum() with A1 as the starting cell and Index to return the lower right cell of the range:

=SUM(A1:INDEX(1:1048576,MAX(IFERROR(MATCH(99^99,B:B,1),0),IFERROR(MATCH("zzzz",B:B,1),0)),MAX(IFERROR(MATCH(99^99,1:1,1),0),IFERROR(MATCH("zzzz",1:1,1),0))))

由于您似乎在使用分号作为列表定界符的系统上，因此下面是使用分号的公式:

Since you seem to be on a system with the semicolon as the list delimiter, here is the formula with semicolons:

=SUM(A1:INDEX(1:1048576;MAX(IFERROR(MATCH(99^99;B:B;1);0);IFERROR(MATCH("zzzz";B:B;1);0));MAX(IFERROR(MATCH(99^99;1:1;1);0);IFERROR(MATCH("zzzz";1:1;1);0))))

这篇关于Excel:查找行和列的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！