Summenprodukt mit Multiplikation mit erster * letzter statt erster * erster

Question

Ich suche nach einer Funktion oder Formel in Excel 2007, um Folgendes zu tun:

Ich habe zwei Zahlenreihen:

1 -1  2 5 10 
1  2 -1 2  5 

Ich möchte etwas Ähnliches tun wie sumproduct, also die Einträge in jeder Spalte miteinander multiplizieren und dann die Summen addieren. Allerdings möchte ich den ersten Eintrag in einer der Zeilen mit dem letzten Eintrag in der anderen Zeile multiplizieren und dann den zweiten mit dem vorletzten und so weiter.

Also:

1 * 5 + -1 * 2 + 2 * -1 + 5 * 2 + 10 * 1

statt nur

1 * 1 + -1 * 2 + ...

Gibt es eine sinnvolle Möglichkeit, diese Art der Berechnung der Summenprodukte in umgekehrter Reihenfolge durchzuführen?

Ich möchte nicht für jedes dieser Summenprodukte, die ich erstellen möchte, eine zusätzliche Zeile in umgekehrter Reihenfolge erstellen müssen.

Answer 1

Sie können mit dieser Formel eine weitere Zeile hinzufügen (beispielsweise Zeile 3):

=INDEX($A$2:$J$2,COUNTA($A$2:$J$2)-COLUMN()+1)

Und dann machen Sie das Gleiche SUMPRODUCTmit der ersten und dritten Reihe.

Ich sehe keine Möglichkeit, dies in nur einer Anweisung zu tun

Answer 2

Vorausgesetzt, Ihre Beispiele sind in A1:E1und platziert A2:E2, erledigt das Folgende das Gewünschte in einer einzigen Formel, indem es einfach die Art und Weise ändert, wie der zweite Bereich an weitergeleitet wird SUMPRODUCT():
(Zeilenumbrüche zur besseren Übersicht und um Bildlaufleisten zu vermeiden)

=SUMPRODUCT(A1:E1,
            N(OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1))
           )

---

Ein kurzer Blick auf die Komponenten:

OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1)

Dies iteriert effektiv (da es von als Array verwendet wird SUMPRODUCT()) über den Bereich A2:E2. Dies geschieht in derselben Zeile (dem 0), verwendet dann aber den von berechneten horizontalen Offset COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1, der bei der Anzahl der Spalten in minus eins (also dem letzten Eintrag) beginnt A2:E2und auf null (also dem ersten Eintrag) reduziert wird. Daher wird es beim Lesen als Array in umgekehrter Reihenfolge gelesen.

Dies wird in einen N()Aufruf eingebettet, um sicherzustellen, dass alle leeren Zellen, Texte usw. als Null gelesen werden und #VALUE!in diesen Fällen ein Fehler vermieden wird. Wenn SiewollenDies führt zu Fehlern. Verwenden Sie es dann nicht N().

SUMPRODUCT()Dies wird dann einfach als zweites zu verwendendes Array eingefügt .

Answer 3

Hier ist eine UDF, um Ihnen den Einstieg zu erleichtern.

Function SUMPRODREV(rForward As Range, rBackward As Range) As Double

    Dim i As Long, j As Long
    Dim vaForw As Variant
    Dim vaBack As Variant
    Dim dReturn As Double

    'put range values into arrays
    vaForw = rForward.Value: vaBack = rBackward.Value

    'if only 1 row, multiply columns
    If UBound(vaForw, 1) = 1 Then
        For i = LBound(vaForw, 2) To UBound(vaForw, 2)
            dReturn = dReturn + (vaForw(1, i) * vaBack(1, UBound(vaForw, 2) - (i - 1)))
        Next i
    Else 'if only 1 column, multiply rows
        For i = LBound(vaForw, 1) To UBound(vaForw, 1)
            dReturn = dReturn + (vaForw(i, 1) * vaBack(UBound(vaForw, 1) - (i - 1), 1))
        Next i
    End If

    SUMPRODREV = dReturn

End Function

Answer 4

Hier ist eine einzelne Formel, die das tut, was Sie brauchen, aber bei der Eingabe gibt es einen Trick.

Angenommen, Ihr Zellbereich sieht so aus. Ich habe die Bereiche „oben“ und „unten“ genannt.

1
Markieren Sie zwei beliebige leere Zellen. In meinem Fall habe ich die Zellen B4:C4 ausgewählt, und zwar wie folgt:

2
Tragen Sie diese in dasBearbeitungsleisteals Array-Formel (drücken Sie Ctrl+ Shift+ Enter):

=SUM(top*INDEX(bottom,1,LARGE(COLUMN(bottom),COLUMN(top))))

Oder dies (ohne benannte Bereiche zu verwenden):

=SUM(A1:J1*INDEX(A2:J2,1,LARGE(COLUMN(A2:J2),COLUMN(A1:J1))))

Sie erhalten ungefähr das obige Ergebnis; regulär SUMPRODUCTergibt 220.

Der Nachteil ist, dass Sie die zusätzliche Zelle einfach ausblenden müssen, indem Sie ihre Schriftfarbe ändern oder ihre Spalte oder Zeile ausblenden. Die zusätzliche Zelle ist erforderlich, um den zweiten Teil der Formel (den INDEX-Teil) zum „Verschieben“ zu zwingen oder ein Wertearray zurückzugeben.