在特定点之后输出列中的单元格的数组 [英] Array that outputs cells in a column AFTER a certain point

问题描述

我有一个数组公式，可以输出非0或文本范围内的所有值.

I have an array formula that outputs all values in a range that are not 0 or text.

此范围主要是数字和一个文本单元格，即描述性标题.

This range is mainly numbers and one cell of text which is the descriptive header.

但是，我需要数组仅在检测到列中存在的文本时才开始输出(这可能在AA4单元格中或可能在AA40中，每次都不同)，并且仅在此点之后输出数字

However, I need the array to start outputting only when it detects the text that is present in the column (This could be in cell AA4 or could be AA40, it is different each time) and only output the numbers AFTER this point.

例如，在列中给出以下内容:

For example, given the following in a column:

525
0
22
464
Type
233
0
453
665

该数组只能输出数字233、453、665.

Only the numbers 233, 453, 665 should be output by the array.

此刻，它仅查看范围并输出非0或文本的任何内容，而不管其在标题文本的上方还是下方.因此，此时将全部输出525,22,464,233,453,665.

At the moment it just looks at the range and outputs anything that isn't a 0 or text, regardless of it being above or below the header text. So 525,22,464,233,453,665 would all be output at the moment.

=IFERROR(INDEX($AA$4:$AA$978, SMALL(IF($AA$4:$AA$978<>0, ROW($AA$4:$AA$978)-MIN(ROW($AA$4:$AA$978))+1, ""), ROW(A1))),"")

执行此任务需要更改/添加什么?

What do I need to alter/add to perform this task?

推荐答案

IF语句中的 value_if_false 子句不是严格必需的.另外，对于SMALL的 k 参数，ROWS比ROW更为严格(请参见

A value_if_false clause within the IF statement is not strictly necessary. Also, ROWS is more rigorous than ROW for SMALL's k parameter (see here for an explanation as to why).

您需要:

=IFERROR(INDEX($AA$4:$AA$978,SMALL(IF($AA$4:$AA$978<>0,IF(ROW($AA$4:$AA$978)-MIN(ROW($AA$4:$AA$978))+1>MATCH("Type",$AA$4:$AA$978,0),ROW($AA$4:$AA$978)-MIN(ROW($AA$4:$AA$978))+1)),ROWS(A$1:A1))),"")

除非您实际上使用的是Excel表，否则我可能会修改上面的一些引用，以使其更有效，即:

Unless you're actually using an Excel Table, I'd probably amend some of the references in the above so as to make it more efficient, viz:

=IFERROR(INDEX($AA:$AA,SMALL(IF($AA$4:$AA$978<>0,IF(ROW($AA$4:$AA$978)>MATCH("Type",$AA:$AA,0),ROW($AA$4:$AA$978))),ROWS(A$1:A1))),"")

范围不到一千个单元格，继续使用IFERROR设置可能会很好，尽管请注意，对于较大的范围，这可能是非常低效的-说明和替代设置-在此处 a>.

With a range of just under a thousand cells, you're probably fine to continue to use an IFERROR set-up, though note that, for larger ranges, this can be extremely inefficient - an explanation and alternative set-up is given here.

致谢

这篇关于在特定点之后输出列中的单元格的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！