Dynamische Array-Erweiterung für SUMMENPRODUKT

[lkdcc]

Hallo zusammen,

ich habe folgendes Problem:

Ich habe in einem Tabellenblatt eine Pivot-Tabelle, in der sich nach und nach mehr Zeilen ergeben.
In einem weiteren Tabellenblatt möchte ich das Summenprodukt der 1. Spalte immer gepaart mit einer der restlichen Spalten bilden:
Bspw:
=SUMMENPRODUKT(Array1;Array2)
=SUMMENPRODUKT(Array1;Array3)
=SUMMENPRODUKT(Array1;Array4)
...usw.

Da sich wie beschrieben die Pivottabelle auf die sich die Summenprodukte beziehen nach unten nach und nach erweitert, möchte ich die Argumente der Funktion dynamisch gestalten.

Im 1. Schritt habe ich bereits das 1. Argument (also die Spalte, die in allen Summenprodukten vorkommt)  per Namensmanager und Bezug auf:
=$A$5:BEREICH.VERSCHIEBEN($A$5;VERGLEICH("";$A:$A;-1)-6;0)

dynamisch gemacht. Es erweitert sich also, sobald die Pivottabelle länger wird.

Nun möchte ich nich für jede Spalte einen eigenen Namen im Namensmanager erstellen müssen. Gibt es eine Möglichkeit, das 2. Argument ebenso dynamisch zu machen.

[schauan]

Hallöchen,

versuch Dich mal statt an BEREICH.VERSCHIEBEN an INDEX.
Mit INDEX kannst Du auch Bereiche zurückgeben und nicht nur bestimmte Inhalte von Bereichen.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	1	4		21
2	2	5
3	3	6

Zelle	Formel
D1	=SUMME(INDEX(A1:A3;;1):INDEX(B1:B3;;1))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Das Beispiel ist jetzt mal nur für die Bereichsrückgabe. Du kannst statt A1:A3 bzw. B1:B3 auch größere und mehrspaltige Bereiche nehmen und die Spalte z.B. mit VERGLEICH ermitteln oder anhand eines Zellinhaltes o.a. festlegen.

Mehr dazu siehe dort:
INDEX & VERGLEICH

[lkdcc]

Hallo Schauan,

erst einmal vielen Dank für deine Antwort. Leider kam ich bis jetzt zeitlich nicht dazu deinen Vorschlag auszuprobieren, bzw. mich mit der Verwendung der INDEX-Funktion für diesen Fall zu beschäftigen.
Nachdem ich mir deinen verlinkten Artikel durchgelesen habe, habe ich allerdings auch keine Klarheit, ob ich die Index-Funktion so nutzen kann wie ich es mir vorstelle.

Zur besseren Veranschaulichung meines anfangs genannten Problems habe ich mal eine Bsp-Datei gebastelt.

In Sheet1 befindet sich die Datenbasis, woraus ich die Pivot in Tabelle1 erstellt habe. Hier habe ich nun in Spalte A die unterschiedlich auftretenden Lieferzeiten und in der darauffolgenden Matrix die Mengen pro Monat, die die entsprechenden Lieferzeiten haben.
Soweit so gut, in Sheet 2 komme ich nun zu meinem eigentlichen Anliegen:
Hier bilde ich zunächst wie Ihr sehen könnt =SUMMENPRODUKT(Tabelle1!$A$5:$A$66;Tabelle1!B5:B66), woraus ich anschließend die durchschnittliche Lieferzeit für den jeweiligen Monat (s. Zeile 4) berechne.
Da sich die Datenbasis in Sheet1 nun monatlich erweitert, erweitert sich in den meisten Fällen auch die Pivottabelle um eine bzw. mehrere Zeilen, da sich Lieferzeiten ergeben, die es vorher nicht gab.
Damit ich nicht alle Summenprodukte in Sheet2 manuell anpassen muss, würde ich nun gerne die Arrays des =SUMMENPRODUKT(Tabelle1!$A$5:$A$66;Tabelle1!B5:B66) dynamisch erweitern lassen.
Sprich: Falls in der Pivot-Tabelle der letzte relevante Wert für das Summenprodukt nicht mehr in Zeile 66 sondern 68 steht, soll sich dies automatisch in den Arrays des Summenproduktes entsprechend erweitern.

Ich hoffe ich konnte es etwas veranschaulichen und bin für Eure Hilfe vorab dankbar.

Viele Grüße

  Excel-Testdatei.xlsx (Größe: 34,59 KB / Downloads: 3)

[LCohen]

Zu schauans 

=SUMME(INDEX(A1:A3;;1):INDEX(B1:B3;;1)) :

Das geht auch als

=SUMME(INDEX(A1:A3;1):INDEX(B1:B3;1))

oder als

=SUMME(A1:INDEX(B1:B3;1))

(hinterer Teil ebenso).

Ansonsten kann man auch

=SUMME(A1:INDEX(B:B;ANZAHL2(A:A)))

nehmen für die Tünahmick.

[snb]

Warum fügst du kein Average Lieferzeit als field im Pivottable zu ?

[lkdcc]

Danke für Eure Antworten.

Zu LCohen:

Das hat geklappt, vielen Dank!

Jetzt versuche ich noch den Zellbezug in Zeile 3, Sheet2 ebenfalls dynamisch zu machen mit:
=INDIREKT("Tabelle1!B"&ANZAHL2(Tabelle1!$A:$A)+2)

Dies funktioniert auch soweit, ist allerdings nicht sonderlich handlich, da sich das "Tabelle1!B" ja nicht auf bspw. "Tabelle1!C" ändert, wenn ich dies nach rechts in der Tabelle ziehe.

Habt ihr hier auch noch eine Lösung?