How to make nested array computations with INDEX()

问题:

Imagine I have several (i.e. > 100) column vectors of numbers. Vectors are large with equal length (e.g. 20k items). The vectors are not adjacent, so they don't make a matrix.

What I want, is to get some row-wise computation with the vectors, for instance

For each row what is the first non zero value among all vectors?

or

For each row what is the maximal value among all vectors?

See this simplified example, that should get the maximal value for all vectors, which would be 3 for all row (in reality the displayed value is 1):

It would be easy, if I could copy the vectors as a matrix and get the column of row ranges that spans all vectors for a given row, instead of the column ranges. But that is not the option due to the size of the data. I think it is related to other SO question: Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array?.

回答1:

You can use CHOOSE to combine equal sized columns into a single range, e.g. for your 3 range example:

=CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6)

Then use that directly in a formula, e.g. in G2 copied down to get the MAX in each row for your example

=MAX(INDEX(CHOOSE({1,2,3},$B$1:$B$4,$B$5:$B$8,$A$3:$A$6),F2,0))

or you can define the CHOOSE part as a named range [especially useful if you have 100 ranges], e.g. name that Matrix and use

=MAX(INDEX(Matrix,F2,0))

You need to modify the {1,2,3} part based on the number of ranges, to shortcut when you have 100 ranges you can use

=CHOOSE(TRANSPOSE(ROW(INDIRECT("1:100"))),Range1, Range2.....Range100)

Now needs to be confirmed with CTRL+SHIFT+ENTER

To get the first non-zero value you can use this version

=INDEX(INDEX(Matrix,F2,0),MATCH(TRUE,INDEX(Matrix,F2,0)<>0,0))

also confirmed with CTRL+SHIFT+ENTER

回答2:

I've found that you actually "can" return an array from INDIRECT().

However it must be in "R1C1" syntax AND you cannot create your R1C1 syntax with a formula (not with something like "R" & ROW() & "C" & COLUMN()".

You have to enter the ROW & COLUMN numbers as absolute and then it works.

Apparently excel puts {} around the numbers when they are returned by ROW() or COLUMN() function, and I guess that's why it doesn't work (try debugging, you'll see).