动态范围参考 [英] Reference for a dynamic range

问题描述

我有一个表 A2:E7，其中每一列都来自一个动态数组公式.结果，=A2#、=B2# 等工作并溢出.

现在，我想在每一行上使用 TEXTJOIN，预期结果在 F 列中.但是 =TEXTJOIN(",",TRUE,A2:E2#) 不起作用.我不知道如何编写范围的动态引用.

有人可以帮忙吗?

解决方案

上一个答案

这个有一个缺陷:它假设 1 个字符的单元格值，但我把它留在这里以供参考.下面的新答案.

你可以这样做:

=LET( m, A2:E7,rSeq，序列(行(米))，L, MMULT( LEN(m),SIGN( SEQUENCE( COLUMNS(m) ) ) )*2-1,i, MMULT(--( TRANSPOSE( rSeq ) 

修改后的答案

这可以采用可变大小的单元格值:

=LET( m, A2:E7,rSeq，序列(行(米))，L，MMULT(LEN(m)--(m")，SIGN(序列(列(m))))-1，i, MMULT(--( TRANSPOSE( rSeq ) 

<块引用>

添加了@P.b 的 IFERROR 包装器以防止空白行抛出值错误.- 谢谢 P.b！不错的收获.

可能还有一些地方需要优化.基本上，它在最后做了一个巨大的 TEXTJOIN，将所有内容与，"连接起来.分隔符.考虑到这一点，它准备了一些用于分解巨大文本 blob 的数组.L 创建每个单元格值及其分隔符(小于 1)的长度数组.i 是一个索引，它简单地将 L 的值连续相加到一个列式数组中，以告诉 MID 函数在哪里中断，而 L 则告诉 MID 巨型文本块的每个块的大小.

<块引用>

注意:如果分隔符超过 1 个字符，则失败.

扩根法

如果要求是:

• 输入必须是一行单元格，每个单元格都包含下面溢出的动态数组.
• 列数是可变的，但是连续的.
• 分隔符是一个字符.
• 所有输入单元格都是一列维度的动态数组.
• 所有动态数组的大小相同.§

那么这个公式应该有效:

=LET( root, A2:E2,c，列(根)，m, IFERROR( INDEX(root,1,1):INDEX(root,1,c)#, "" ),rSeq，序列(行(米))，L，MMULT(LEN(m)--(m")，SIGN(序列(c)))-1，i, MMULT(--( TRANSPOSE( rSeq ) 

其中 root (A2:E2) 是包含每个动态数组根的输入范围.

<块引用>

§ - 如果它们的大小不同，则下重叠将包含 0.这可以通过用"替换 0 来解决，但如果您的输入通常包含有效的 0，这将是一个糟糕的方法，所以我离开了满足并保持对等长动态数组的要求作为输入代替.如果您需要带零的可变长度数组，这是可能的，但会增加更多可能减慢速度的步骤.

I have a table A2:E7, where each column comes from a dynamic array formula. As a result, =A2#, =B2#, etc. work and spill.

Now, I would like to use TEXTJOIN over each row, the expected results are in column F. But =TEXTJOIN(",",TRUE,A2:E2#) does not work. I don't know how to write the dynamic reference for a range.

Could anyone help?

解决方案

Previous Answer

this one has a flaw: it assumes 1 char cell values, but I am leaving it here for reference. New answer below.

You could do:

=LET( m, A2:E7,
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m),SIGN( SEQUENCE( COLUMNS(m) ) ) )*2-1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq - 1,
       MID( TEXTJOIN( ",", TRUE, m ), i+1, L ) )

Revised Answer

This can take variably sized cell values:

=LET( m, A2:E7,
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - 1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
       IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) )

added @P.b's IFERROR wrapper to prevent blank rows from throwing a VALUE error. - Thanks P.b! Nice catch.

There may still be some places for optimization. Basically, it does a giant TEXTJOIN at the the end that concatenates everything with "," delimiters. With that in mind, it prepares some arrays that will be used to break up the giant text blob. L creates an array of lengths of each cell value as well as its delimiter (less 1). i is an index that simply adds up L's values consecutively into a columnar array to tell the MID function where to break while L tells MID the size of each chunk of the giant text blob.

NB: If the delimiter is more than 1 character, this fails.

Expanding Roots Method

If the requirements are:

• The input must be a row of cells that each contain dynamic arrays that are spilled below.
• The number of columns is variable, but contiguous.
• The delimiter is one character.
• All input cells are dynamic arrays of one column dimension.
• All dynamic arrays are equally sized.§

Then this formula should work:

=LET( root, A2:E2,
       c, COLUMNS(root),
       m, IFERROR( INDEX(root,1,1):INDEX(root,1,c)#, "" ),
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( c ) ) ) - 1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
       IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) )

where root (A2:E2) is the input range that contains the roots of each dynamic array.

§ - If they are not equally sized, the underlaps will contain 0's. This can be fixed by replacing 0 with "", but if your inputs would normally contain valid 0's, this would be a bad approach, so I left that out and maintained a requirement of equal length dynamic arrays as inputs instead. If you require variable length arrays, with zeros, it's possible, but will add more steps that could slow it down.

这篇关于动态范围参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！