Im vorhergehenden Blog habe ich gezeigt, wie man mit SUMMEWENN eine Summe immer am Beginn eines neuen Eintrages erstellt. Heute geht es darum, wie man allgemein Anfang und Ende eines Bereiches ermittelt.
Und es geht darum, wie man komplexe Formeln Schritt für Schritt aufbaut und zusammenstellt. Ich erstelle ganz bewusst mehrere Zwischenrechnungen, die wir dann Stück für Stück zusammenführen zu einer einzigen Funktion. Die Zwischenspalten können dann genauso Schritt für Schritt entfernt werden, bis eine einzige Berechnung überbleibt. Nehmen wir den Stier bei den Hörnern!
Die noch nicht berechnete Tabelle zum Mitmachen gibt es hier:
Als Beispiel dient die Funktionen SUMMENPRODUKT. Damit werden Spalten zeilenweise multipliziert. Im Beispiel rechts ist die Berechnung des Gesamtgewichtes pro Palette von Hand so:
=SUMMENPRODUKT(B2:B12;C2:C12)
Damit werden alle Zeilen multipliziert (B2*C2, B3*C3 …) und die Ergebnisse addiert.
Ich benötige also zwei Bereiche: B2:B12 und C2:C12. Für die nächste Palette ist es dann B13:B26 bzw. C13:C26 und so fort.
Allerdings will ich nicht jedes Mal ermitteln, wo ein neuer Bereich beginnt und aufhört. Ich will eine Formel erstellen, die eigenständig das Anfang und das Ende der Palette ermittelt.
Wie wir den Anfang herausfinden, haben wir gestern schon gesehen. Der Beginn einer Palette ist dort, wo die Werte aus Spalte unterschiedlich sind. In Spalte D beginnend mit D2 steht
=A2<>A1
Bekomme ich hier ein “WAHR”, beginnt eine neue Palette.
Mit WAHR und FALSCH kann ich aber nicht so viel anfang, ich brauche die Zeilennummer für alle Stellen, die WAHR sind.
Die Zeilennummer (zum Beispiel die 2) erhalte ich mit der Funktion ZEILE().
=Zeile(A2) gibt als Wert 2 aus
Da ich die Zeilennummern nur für die erste Fundstelle brauche, berechne ich in D2:
=WENN(A2<>A1;ZEILE(A2);””)
Nach unten ausfüllen. Jetzt gibt es für jede neue Palette die Zeilennummer.
Wie kann das Ende herausgefunden werden? Setzen Sie voraus, dass immer nach Spalte A sortiert ist. Die Palettennummern stehen immer hintereinander, kein M1 kommt irgendwo am Ende oder zwischendrin vor.
Mit dieser Voraussetzung gibt es folgende Überlegung:
- M1 kommt in Spalte A elfmal vor
- Beginn von M1 ist in Zeile 2
- Ende ist dann Zeile 2 + 11 – 1 – Zeile 12 (-1, weil ich ja schon in A2 stehe)
Wie oft ein Eintrag in einer Spalte vorkommt, lässt sich mit ZÄHLENWENN herausfinden.
=ZÄHLENWENN(A:A;A2)
Formel beginnt in E2 und wird nach unten ausgefüllt. Jetzt steht überall die Zeilennummer.
Jetzt muss ich aus der Startzeile plus der Anzahl der Zeilen die letzte Zeile berechnen. Und wieder nur für das erste Vorkommen! Beginnend in Zelle D2:
=WENN(A2<>A1;D2+ZÄHLENWENN(A:A;A2)-1;””)
Nach unten ausfüllen.
Aus dem notwendigen Bezug B2:B12 habe ich jetzt also die Zeilen. Daraus muss ein Bezug mit den Spaltenbuchstaben zusammengesetzt werden. Im ersten Schritt verwenden wir die Zellen D2 mit der Anfangszeile und E2 mit der Endzeile.
Die Formel dazu:
=”B”&D2&”:B”&E2
Das & reiht Einträge und Zellinhalte aneinander. Texte (wie das B für den Spaltenbuchstaben) müssen in Anführungszeichen angegeben werden. Die Formel nach unten ausfüllen
Aber wieder: ich will das nur für die erste Fundstelle haben. Die Formel in F2 muss also lauten:
=WENN(A2<>A1;”B”&D2&”:B”&E2;””)
Nach unten ausfüllen
Noch alle da?
Dann gibt es eine Hausaufgabe für morgen. In Spalte G brauche ich den gleichen Bezug für Spalte C. Wir brauchen also als Basis die beiden Bezüge: In Spalte F B2:B12 und in Spalte G C2:C12
Erstellen Sie die Formel in Spalte und füllen Sie die Formel nach unten aus.
Morgen geht es weiter mit zwei Themen:
- Wie kann man aus dem Bezug (der jetzt in Textform vorliegt) einen Bezug machen, mit dem man weiterrechnen kann?
- Wie reduziert man Formeln mit vielen Zwischenschritten zu einer einzigen Formel?
- Wie berechnet man schlussendlich das SUMMENPRODUKT in einer Formel?
Bork.Blog 
