Reversing a list in Excel within a formula

问题:

So there are a bunch of ways to reverse a list to turn it into a new list, using helper columns. I've written some code that does use helper columns to reverse a list and then use it.

I'm wondering how I would reverse a list without using a helper column for use in a sumproduct - for example,

=sumproduct(Reverse(A1:A3),B1:B3)

回答1:

This will do what you are asking:

=SUMPRODUCT(INDEX(A:A,N(IF(1,{3;2;1}))),B1:B3)

To make a little more dynamic you can use this array formula:

=SUM(INDEX(A:A,N(IF(1,LARGE(ROW(A1:A3),ROW(A1:A3)))))*B1:B3)

Being an array formula, it needs to be confirmed with Ctrl-Shift-Enter, instead of Enter when exiting Edit mode.

回答2:

This array formula will reverse the order of a vertical array:

= INDEX(B18:B21,N(IF({1},MAX(ROW(B18:B21))-ROW(B18:B21)+1)))

Also, this reverses a horizontal array:

= INDEX(A1:D1,N(IF({1},MAX(COLUMN(A1:D1))-COLUMN(A1:D1)+1)))

---

EDIT

More generally, to vertically flip a matrix instead of just an array (which is just a one-dimensional matrix), use this array formula: (e.g. for range A1:D2)

= INDEX(A1:D2,N(IF({1},MAX(ROW(A1:D2))-ROW(A1:D2)+1)),
              N(IF({1},COLUMN(A1:D2)-MIN(COLUMN(A1:D2))+1)))

And to horizontally flip a matrix, use this:

= INDEX(A1:D2,N(IF({1},ROW(A1:D2)-MIN(ROW(A1:D2))+1)),
              N(IF({1},MAX(COLUMN(A1:D2))-COLUMN(A1:D2)+1)))

And a bonus... to flip a matrix horizontally and vertically in one shot (i.e. rotate it 180 degrees):

= INDEX(A1:D2,N(IF({1},MAX(ROW(A1:D2))-ROW(A1:D2)+1)),
              N(IF({1},MAX(COLUMN(A1:D2))-COLUMN(A1:D2)+1)))

Actually this last one here could more generally be used to flip either a horizontal or vertical array.

回答3:

Actually you can make the formula in your Question work (with a small UDF()):

Pick a cell and enter:

=SUMPRODUCT(reverse(A1:A3),B1:B3)

with this in a standard module:

Public Function reverse(rng As Range)
    Dim ary(), N As Long, i As Long
    N = rng.Count
    ReDim ary(1 To N)
    i = N

    For Each r In rng
        ary(i) = r.Value
        i = i - 1
    Next r

    With Application.WorksheetFunction
        reverse = .Transpose(ary)
    End With
End Function

回答4:

For what it's worth, here's another completely different method to reverse an array. (I'm posting this as a separate answer just because it is apples and oranges to the other answer I already provided.)

Instead of reversing the order of the array by reversing the indexing, it is also possible to use matrix multiplication (MMULT) to accomplish this.

If your data in A1:A3 is {1;3;5} (for example) then the following matrix multiplication effectively reverses this array:

[0 0 1]   [1]   [5]
[0 1 0] * [3] = [3]
[1 0 0]   [5]   [1]

In order to generate that matrix of 1's and 0's above, you can do this (line break added for readability):

= (ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A3)))=
  (COLUMN(INDEX(1:1,ROWS(A1:A3)))-COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A1:A3)))+1))+0

So in the end, the formula to reverse this array would be:

= MMULT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A3)))=
  (COLUMN(INDEX(1:1,ROWS(A1:A3)))-COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A1:A3)))+1))+0,A1:A3)

This same line of thinking can be used to reverse a horizontal array. For example if A1:C1 is {1,3,5}, then:

          [0 0 1]
[1 3 5] * [0 1 0] = [5 3 1]
          [1 0 0]

Note how the matrix of 1's and 0's is the second argument this time instead of the first argument.

Using the same general line of reasoning, you can get to this formula to reverse a horizontal array.

= MMULT(A1:C1,(ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(A1:C1)))=
  (COLUMN(INDEX(1:1,COLUMNS(A1:C1)))-COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(A1:C1)))+1))+0)

This method has two major disadvantages as compared two the N(IF(...)) solution, namely:

1. It's way longer.

2. It only works for numbers since MMULT requires numbers, but the other method works if the cells contain anything (e.g. text).

I was using this solution to reverse arrays without helper columns until just recently when I learned about the N(IF(...)) alternative.