Excel–Beginn und Ende eines Bereiches

Eine Kostenübersicht über mehrere Städte ändert sich ständig, es kommen neue Werte dazu, es können auch Zeilen entfallen. Die Tabelle ist immer nach dem Stadtnamen sortiert.

13.03.2019_0001Die Anforderung ist, dass immer am Beginn der Stadt eine Gesamtsumme über alle Kosten angezeigt wird.

Ich habe in die Zellen momentan das Wort “Summe” geschrieben: es soll also in D2 die Summe aller Münchner Ausgaben, in D60 die Ausgaben aus Düsseldorf und so fort. Wichtig ist dabei, dass nach dem Umsortieren (nach dem Ort alphabetisch auf- oder absteigend) oder beim Einfügen neuer Zeilen oder sogar neuer Orte die Summe immer automatisch neben dem ersten Vorkommen des Ortes steht.

Das geht mit einer Kombination aus den beiden Funktionen SUMMEWENN und WENN.

Die erste Frage, die sich stellt: Woran erkenne ich, dass ein neuer Ort beginnt? Wenn der Eintrag in A2 nicht identisch ist mit dem Eintrag in A1 oder wenn der Eintrag in A60 nicht identisch ist mit A59. Im Screenshot sind einige Zeilen ausgeblendet, damit Sie mehrere Ortsanfänge sehen können.

 

 

 

 

 

13.03.2019_0002Wir ermitteln zuerst, in der Zeile ein neuer Ort beginnt – in D2 kommt die Formel =A2<>A1. Mehr nicht. Nach unten ausfüllen.

Das <> bedeutet “ist nicht gleich”.
In Alltagssprache also “ist A2 nicht gleich A1?”.

Excel guckt jetzt, ob diese Bedingung wahr oder falsch ist. Wir erhalten WAHR für jeden Ortsbeginn und FALSCH, wenn der Ort gleich bleibt.

Damit geht es weiter.

 

 

Für jedes WAHR soll Excel die Summe für den Ort dieser Zeile rechnen. Das geht mit SUMMEWENN.

Die Funktion hat drei Argumente:

=SUMMEWENN(zu durchsuchender Bereich; Suchkriterium; zu addierende Zahlen)

SUMMEWENN für München in Zelle D2 wäre

=SUMMEWENN(A2:A200;A2;C2:C200)

Durchsuche die Spalte A von A2 bis A200 nach dem Begriff aus A2 und wenn der Begriff gefunden wurde, addiere die Zahlen aus Spalte C von C2 bis C200.

Aber – das soll Excel ja nur machen, wenn ein neuer Ort anfängt – sonst soll nicht gerechnet werden. In Alltagssprache ungefähr so:

Wenn A2 nicht gleich A1 ist, dann sollst Du SUMMEWENN rechnen; sonst sollst Du die Zellen leer lassen. Angefangen wird mit WENN

Wenn A2 nicht gleich A1 ist =WENN(A2<>A1;
dann rechne SUMMEWENN
Aber Achtung! A2:A200 und C2:C200 müssen absolut gesetzt werden!
=SUMMEWENN($A$2:$A$200;A2;$C$2:$C$200);
Sonst lasse die Zelle leer (zwei Anführungszeichen stehen für ”Nichts”) “”)

So sieht die Formel dann gesamt aus:

=WENN(A2<>A1;SUMMEWENN($A$2:$A$200;A2;$C$2:$C$200);““)

13.03.2019_0003

Nein, nicht im Kopf nachrechnen – da sind immer noch eine Menge Zeilen ausgeblendet!

Wie muss die WENN-Funktion aussehen, wenn eine Beschriftung ausgegeben werden soll?

So wie im ersten Screenshot das Wort “Summe”. Excel soll in Spalte D bei jedem neuen Ort “Summe” schreiben.

=WENN(A2<>A1;”Summe”;””)

 

Diese Berechnung geht mit SUMMEWENN, ZÄHLENWENN, MINWENNS und MAXWENNS. Ersetzen Sie die Funktion SUMMEWENN(….) durch die eine der anderen Funktionen. Für MINWENNS lautet die Funktion beispielsweise:

=WENN(A2<>A1;MINWENNS($C$2:$C$200;$A$2:$A$200;A2);““)

 

Beispieltabelle zum Angucken gibt es hier: Bereich die erste

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