Ich habe eine Zeile, die ich mit Formeln wie
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
aber das Ziehen der Formel ändert nur den alphabetischen Teil des Index. Gibt es eine Möglichkeit, Excel dazu zu bringen, die Array-Indizes korrekt zu ändern?
Antwort1
So wie ich es verstehe, haben Sie eine Zelle mit einer Formel =SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)und möchten diese so ausfüllen, dass die Zelle in der nächsten Spalte, aber in derselben Zeile, die Formel hat =SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48). Das heißt, Sie möchten, dass die Spalte wie üblich um 1 erhöht wird, die Zeile jedoch um 1 verringert wird.
Dies können Sie mithilfe der OFFSETFunktion erreichen. Damit können Sie einen Bereich konstruieren, der einen bestimmten Abstand zu einem bekannten Bereich aufweist. Mithilfe der COLUMNFunktion können wir angeben, wie groß der gewünschte Versatz sein soll.
Die gewünschten Bereiche sind B93:B96und B46:B49, versetzt um -1 Zeile und +1 Spalte für jede Spalte nach der ersten. Daher OFFSET(B93:B96, -1, 1)gibt eine Referenz auf zurück C92:C95. Unsere Referenz aktualisiert jedoch automatisch die Spalte, sodass wir den Spaltenversatz weglassen können.
Für den Bereich, der B93:B96in der ursprünglichen Formel enthalten ist, möchten wir in der ersten Zelle OFFSET(B93:B96, 0, 0), in der zweiten OFFSET(C93:C96, -1, 0), in der dritten OFFSET(D93:D96, -2, 0)und so weiter. Wir können verwenden, =COLUMN(A1)um ein Ergebnis zu erhalten, das die Nummer der aktuellen Spalte ist (weil der Verweis auf B1 in der zweiten Spalte aktualisiert wird usw.). Wenn wir diese kombinieren, können wir verwenden,
OFFSET(B93:B96, 1 - COLUMN(A1), 0)
was wir durch Ändern des Verweises vereinfachen können:
OFFSET(B94:B97, -COLUMN(A1), 0)
In der ersten Zelle wird dies zu übersetzt, OFFSET(B94:B97, -1, 0)was ist B93:B96. In der zweiten Zelle wird die Formel direkt als ausgefüllt, OFFSET(C94:C97, -COLUMN(B1), 0)was ist C92:C95.
Die beiden Referenzen, die wir brauchen, sind also OFFSET(B94:B97, -COLUMN(A1), 0)und OFFSET(B47:B50, -COLUMN(A1), 0). Daher lautet die Formel
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
Es gibt noch eine letzte Komplikation. Als ich dies auf meiner Kopie von Excel 2007 testete, stellte ich fest, dass innerhalb der SUMPRODUCTFormel COLUMN(A1)einAnordnungenthält den Wert 1, statt nur den Wert 1. (Sie können dies im Dialogfeld „Formel auswerten“ sehen: Bei der Auswertung COLUMN(A1)ist das Ergebnis {1}statt nur 1, und der SUMPRODUCTAusdruck erhält zwei #VALUE!Argumente und ergibt am Ende 0.) Abhängig von Ihrer Version passiert dies bei Ihnen möglicherweise nicht. Wenn dies der Fall ist, umschließen Sie das mit COLUMN(A1)einem SUM(), wie folgt:
=SUMPRODUCT(OFFSET(B94:B97, -SUM(COLUMN(A1)), 0), OFFSET(B47:B50, -SUM(COLUMN(A1)), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
COLUMN(A1)Sie werden bemerken, dass ich das Finale nicht in ein eingeschlossen habe SUM(); das liegt daran, dass dieses wie üblich eine Zahl zurückgegeben hat, keinen Array-Verweis. Ich gehe davon aus, dass SUMExcel erkannt hat, dass wir in diesem Fall kein Array von haben wollten, da die Funktion keine Array-Verweise akzeptiert COLUMN.