Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel formula - Reference for a dynamic range

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?

enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

enter image description here

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...