Clever-Excel-Forum

Hallo zusammen,

ich hätte ein Luxusproblem an dem ich schon länger überlege wie ich dies schöner (mit einfachen) Formeln abbilden kann.
Bislang ist mir nichts adequates eingefallen.

Ich habe diverse Dateien in denen ich Summen ziehen muss. Soweit kein Problem ;). Auch wenn die Summen gewisse Kriterien haben, wie bspw. das die einzelnen Kostenstellen zu Kostenstellengruppen zugeordnet werden (Beispieldatei: Spalte D & C). Alles ganz normal per SummeWennS. Die Formel kann ich auch "nach rechts ziehen" wenn ich dies über einen Zeitraum haben möchte und in der Datentabelle auch die MOnate nebeneinander dargestellt sind.
Allerdings bricht das System etwas in sich zusammen, wenn ich die SummeWennS über eine Tabelle mit einem Zeitraum ziehen will und sich die Datentabelle in der Spaltenstruktur gegenüber der gewünschten Darstellung ändert. Beispielsweise wenn in der Datentabelle sich zusätzliche Spalten wie Gesamtsummen befinden oder andere zusätzliche Spalten.
Hier passe ich bislang immer den Bereich manuell an, was aber ein wirklicher zeitfresser ist.

Gibt es ggfs. Denkanstöße wie man dieses Problem lösen kann?
In der Tabelle habe ich die bisherige Lösung mit dem manuellen Bereich grün umrahmt und eine (offensichtlich nicht praktikable) Lösung unten rot umrahmt

Hi,

füge in deinem roten Bereich in Zeile 38 Formeln analog zu Zeile 3 ein (also mit den Ergebnissen JanuarPreis, JanuarMenge, FebruarPreis, etc).

Dann kommt in E41 die Formel =SUMMENPRODUKT($D$6:$U$22*($B$6:$B$22=$C41)*($D$3:$U$3=E$38))
Nach rechts und runter ziehen.

Allerdings wäre es für solche Auswertungen erheblich geschickter du würdest deine Kreuztabelle auflösen. Eine Tabelle mit den Spalten Kostenstellengruppe, Kostenstelle, Monat (oder besser: echtes Datum), Preis, Gesamt würde sich ganz wunderbar mit einer Pivot-Tabelle oder einfachen SUMMEWENNS-Formeln auswerten lassen.

Vielen Dank für die schnelle Hilfe.

Summenprodukt ist bekannt, wurde aber bislang nur extrem selten angewandt (aufgrund des komplexen Aufbaus habe ich es bisher vermieden, auch weil dann ständig Rückfragen der Kollegen kommen würden. Man selbst würde sich an die Syntax gewöhnen).

Der Begriff Kreuztabelle ist mir für unleserliche Tabelle neu, aber zeigt das Problem auf.
"Leider" lässt es sich in diesem Falle nicht so einfach auflösen da die Tabelle ein fertiger Bericht ist. Ggfs. muss man das ganze neu aufbauen und erst mal eine richtige (!!) Tabelle aufbauen aus der dieser Bericht gespeist wird.
Mal schauen was die Zeit zu lässt.

Meinem Problem wurde geholfen, danke für die schnelle Unterstützung :)

Hi,

zum schnellen Auflösen von Kreuztabellen: mach dich mal zu PowerQuery und dem Stichwort "entpivotisieren" bzw. "entpivotieren" schlau.

Und sooo kompliziert ist Summenprodukt gar nicht:
=SUMMENPRODUKT(Summenbereich*(Filterbedingung1)*(Filterbedingung2)*(...))

Wobei die einzelnen Filterbedingungen jeweils entweder
- eine Zeile und gleich viele Spalten
oder
- eine Spalte und gleich viele Zeilen
oder
- gleichviele Zeilen und Spalten
wie der Summenbereich haben müssen.

Sinnvollerweise ergeben die Filterbed. immer 0 oder 1 und niemals andere Zahlenwerte.
Dies ist gewährleistet sobald man Vergleichsbedingungen (>,<,=,>=,<=) bzw. Funktionen verwendet, die Wahrheitswerte zurück liefern (wie z.B. ISTTEXT() etc.). Nicht erlaubt sind Funktionen wie UND(), ODER(), XODER(), SUMME(), kurz alle Funktionen, die selbst zusammenfassen.

PowerPivot, Powerquery stehen ganz oben auf meiner Liste. Das wäre definitiv hilfreich.

Danke für die schnelle Hilfe ihr beiden