Excel – SUMMEWENN mit dynamischen Spalten

Aus einer Zeiterfassungssoftware wird wöchentlich eine Liste mit Mitarbeiternamen, Projektnamen und Stunden geliefert. Daraus wird für einen Kunden ein Übersichtsblatt nach Maßgabe des Kunden “gebastelt”.

22.10.2019_0000

Der Wunsch ist, das zweite Blatt mit Formeln zu automatisieren.

tiger_by_the_tail_1600_clr_11300

Nicht verzagen, auch wenn die Beschreibung ziemlich lang ist und die Formel auch ein ziemliches Monstrum wird.
Schritt für Schritt packen Sie’s! Und zum Schluss gibt es natürlich wieder einen Link mit der Arbeitsmappe zum Download. Auch die Schritt-für-Schritt-Berechnungen sind dabei.

Die Liste aus der Zeiterfassung steht auf dem Tabellenblatt “Basis”, die Übersicht auf dem Tabellenblatt “Übersicht”.

Zum Einsatz kommt momentan SUMMEWENN. In D2 der Übersicht der Übersicht steht:

=SUMMEWENN(Bereich;Suchkriterium;zu addierender Bereich)
=SUMMEWENN(Basis!$B$2:$B$500;$A2;Basis!$D$2:$D$500)

Der zu durchsuchende Bereich ist vom Blatt Basis der Bereich B2 bis B500 (die Spalte “Bearbeiter”), das Suchkriterium ist vom Blatt “Übersicht” die Zelle A2 (der konkrete Mitarbeitername), der zu addierende Bereich ist wiederum auf dem Blatt “Basis” von D2 bis D500 (die Spalte Immobilien KG).

Das hat den Nachteil, dass der zu addierende Bereich für jeden Projektname angepasst werden muss – die Holding KG aus Spalte F, die Beteiligung aus Spalte H. Kommt ein Projekt hinzu oder taucht eines in dieser Woche nicht auf, stimmt gar nichts mehr. Es ist also sehr fehleranfällig.

Wie kann ich SUMMEWENN dazu bewegen, den Spaltennamen selbständig zu suchen? Der SVERWEIS kann das ja auch (siehe SVERWEIS sucht Spaltennummer selber). Bei SUMMEWENN ist das leider nicht ganz so einfach, weil hier keine Spaltennummer, sondern die Adresse gebraucht wird. Ich brauche also nicht Spalte 4, sondern D.

Der Übersichtlichkeit halber erkläre ich zuerst das Vorgehen schrittweise auf dem gleichen Blatt “Basis”.

1. Schritt: Ermitteln der Spaltennummer

Die Spaltennummer kann ich mit VERGLEICH ermitteln. Von H1 bis J1 sind die Firmennamen wiederholt.

In H2 steht die Formel:
=VERGLEICH(Suchkriterium;Suchmatrix;Übereinstimmung)
=VERGLEICH(H$1;$A$1:$F$1;0)

22.10.2019_0001

Das Suchkriterium ist H1, die Zeile wird absolut und die Spalte relativ gesetzt, damit die Formel nach unten und rechts kopiert werden kann.
Die Suchmatrix ist die Überschrift von A1 bis F1 – alles absolut gesetzt.
Der Vergleichstyp ist 0 – eine exakte Übereinstimmung ist gefragt.

Das Ergebnis ist z. B. 6 für die Beteiligung KG in Spalte F.

2. Schritt: Spaltenbuchstabe aus der Nummer ermitteln

Da ich mit “4” nichts anfangen kann, muss die ermittelte Nummer in den Spaltenbuchstaben umgesetzt werden. Das geht mit ADRESSE.

In H3 steht die Formel:
=ADRESSE(Zeile;Spalte)
=ADRESSE(2;H2)

22.10.2019_0002

Als erstes wird eine Zeile eingetragen – es ist im Moment egal, was ich das verwende, aber später beginnt mein Bezug mit B2, also starte ich auch mit Zeile 2.

Das zweite Argument ist die Spalte. Sie wird in Form einer Ziffer angegeben, die ich in H2 ermittelt habe.

Das Ergebnis ist z. B. $F$2 für die Beteiligung KG in Spalte F. Der Bezug ist automatisch absolut – was ich auch brauche. Jetzt habe ich den Beginn meines Bereiches, aber ich brauche noch das Ende. Das geht genauso:
=ADRESSE(500;H2)

3. Schritt: ADRESSE und VERGLEICH zusammenbauen

Statt H2 für die Spaltennummer wird die Funktion VERGLEICH eingesetzt:

=ADRESSE(2;H2)
=ADRESSE(2;VERGLEICH(H$1;$A$1:$F$1;0))

22.10.2019_0003

Ich habe im Beispiel oben die beiden Bezüge für den Beginn und das Ende zusammengestellt. Das Ergebnis ist jeweils $D$2 und $D$500.

4. Schritt: Umwandeln des Ergebnisses in einen Bezug

Leider kann ich diese Bezüge nicht so ohne weiteres in SUMMEWENN einbauen, denn das Ergebnis ist bloß ein Text. Also unbrauchbar. Das muss zuerst in einen echten Bezug umgewandelt werden – mit INDIREKT. Diese Funktion kann man nicht “standalone” ausprobieren, ich kann sie also nicht vorführen. Sie wird aber nur um die Funktion aus dem 3. Schritt herumgebaut:

=INDIREKT(ADRESSE(2;VERGLEICH(H$1;$A$1:$F$1;0))

5. Schritt: Zusammenbau mit SUMMEWENN

So sah die Formel anfangs aus:
=SUMMEWENN(Bereich;Suchkriterium;zu addierender Bereich)
=SUMMEWENN(Basis!$B$2:$B$500;Übersicht!$A2;Basis!$D$2:$D$500)

Der zu ändernde Teil der Funktion ist rot markiert: der zu addierende Bereich. Momentan kümmere ich mich noch nicht um den Blattnamen – der bleibt noch außen vor. Wir ergänzen das am Ende. Also $D$2 muss ersetzt werden durch den ganzen Rattenschwanz aus Schritt 4, das gleiche für $D$500. Zwei Rattenschwänze.

=SUMMEWENN($B$2:$B$500;$A2;$D$2:$D$500)

Statt $D$2 und $D$500 setze ich die Zellbezüge auf H3 und H4 ein:
=SUMMEWENN($B$2:$B$500;G5;INDIREKT(H3):INDIREKT(H4))

Mein Suchbegriff steht probeweise in Zelle G5.

22.10.2019_0004

Da das klappt, kopiere ich die Formeln aus H3 und H4 an die Stellen in der SUMMEWENNFORMEL.

=SUMMEWENN($B$2:$B$500;$G6;INDIREKT(ADRESSE(2;VERGLEICH(H$1;$A$1:$F$1;0))):INDIREKT(ADRESSE(500;VERGLEICH(H$1;$A$1:$F$1;0))))

Noch alle da? Dann geht es zum Endspurt!

6. Schritt: Blattnamen ergänzen

Da die Auswertung auf einem anderen Tabellenblatt passiert, müssen die Blattnamen noch ergänzt werden. Das war in der ersten Formel =SUMMEWENN(Basis!$B$2:$B$500;$A2;Basis!$D$2:$D$500) schon zu sehen.

Die erste Ergänzungen sind unproblematisch, weil das ja normale, reine Bezüge sind.
=SUMMEWENN(Basis!$B$2:$B$500;$A2; …

Aber innerhalb von “INDIREKT” kann ich nicht einfach den Blattnamen dazutippen. Text muss in Anführungszeichen gesetzt und mit & aneinander gehängt werden. Blattname und Zellbezug werden durch ein Ausrufezeichen getrennt. Warum beides getrennt angegeben werden muss, weiß ich nicht – schreibt man Basis! zusammen in die Anführungszeichen, funktioniert es nicht. Also getrennt:

“Basis”&”!”&…

=SUMMEWENN(Basis!$B$2:$B$241;$A2;INDIREKT(„Basis“&“!“&ADRESSE(8;VERGLEICH(D$1;Basis!$A$1:$J$1;0))):INDIREKT(„Basis“&“!“&ADRESSE(500;VERGLEICH(D$1;Basis!$A$1:$J$1;0))))

In der Funktion VERGLEICH gibt es auch nochmal den Blattnamen, dort muss er nicht gesondert behandelt werden. In der Funktion kann er einfach dazugeschrieben werden.

Ein Hinweis noch: wenn der Blattname einen Leerschritt beinhaltet, muss der gesamte Name in einfache Anführungszeichen gesetzt sein: ‘Übersicht Gesamt’!
Mitsamt den Anführungszeichen für INDIREKT hat man dann drei Striche hintereinander: “’Übersicht Gesamt’”&”!”

In der Beispieltabelle ist die gesamte Formel noch in WENNFEHLER eingepackt, damit es keine Fehler wie BEZUG oder ähnliches gibt, wenn ein Projektname nicht gefunden wird.

Und hier ist es, das Prachtstück: SUMMEWENN dyn

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s