Excel–Bereich ermitteln (Teil 2)

Gestern sind wir bis zum Ermitteln des Bereiches B2:B12 gekommen – den zweiten Bereich C2:C12 haben Sie selber herausgefunden. Oder?

Das ist jetzt unsere Basis für das weitere Rechnen: Bereich ermitteln_Basis

Der Schwerpunkt heute ist das Zusammenführen von Hilfsrechnungen in eine einzige Formel – das SUMMENPRODUKT ist ein Beispiel dafür. Aber Sie können auch bei ganz anderen Rechenvorgängen so verfahren.

15.03.2019_0008

In den Formeln in den Spalten E und F wird jeweils auf Zellen in den Spalten D und E Bezug genommen.

In F2 =WENN(A2<>A1;“B“&D2&“:B“&E2;““)

In G2 =WENN(A2<>A1;“C“&D2&“:C“&E2;““)

Unsere erste Aufgabe wird sein, auf die beiden Hilfsberechnungen in den Spalten D und E zu verzichten. Dafür muss ein Teil der Formel aus D2 in beiden Formeln anstelle von D2 eingefügt werden. In D2 steht:

=WENN(A2<>A1;ZEILE(A2);““)

DAs WENN(A2<>A1 …) haben wir in F2 schon verwendet. Das können wir uns also sparen und müssen es aus der Hilfsformel nicht mitnehmen. Genauso hat unsere WENN-Formel in F2 schon eine “Sonst tue nichts”-Anweisung für alles; auch das brauchen wir nicht aus den Hilfsformeln mitnehmen. Kopieren Sie deswegen nur den mittleren Teil … ZEILE(A2)… und fügen Sie es in die Formel von F2 anstelle des Bezuges D2 ein.

=WENN(A2<>A1;“B“&ZEILE(A2)&“:B“&E2;““)

Genauso verfahren Sie mit der Formel aus E2. Auch aus dieser Formel benötigen Sie den mittleren Teil:

=WENN(A2<>A1;D2+ZÄHLENWENN(A:A;A2)-1;““)

Das muss anstelle von E2 in die Formel von F2 eingefügt werden. In F2 steht zum Schluss:

=WENN(A2<>A1;“B“&ZEILE(A2)&“:B“&ZEILE(A2)+ZÄHLENWENN(A:A;A2)-1;““)

Fett markiert sind die Teile, die wir anfangs in einer eigenen Spalte berechnet haben. Füllen Sie die Formel nach unten aus und machen Sie es genauso für die Spalte G (also den Bereich für C). Anschließend können Sie die Spalten D und E löschen.

15.03.2019_0011

Im Beispiel oben sieht die Formel ein klein wenig anders aus, weil ich die Spalten mit dem $ fixiert habe. Also $A2<>$A1 oder ZEILE($A2) oder $A:$A. Dann ist es einfacher, die Formeln nach rechts zu kopieren, ohne dass ich nachträglich die Spalten wieder anpassen muss.

Jetzt haben wir die Bezüge ohne Zwischenrechnungen.

Nächster Schritt: ich will die ermittelten Bezüge B2:B12 und C2:C12 in der Funktion SUMMENPRODUKT verwenden, um damit zu rechnen. Zuerst verweise ich auf die beiden Hilfszellen D2 und E2. Leider kann man diese Bezüge nicht direkt so verwenden, Excel versteht den Zellinhalt als Text. Mit der Funktion INDIREKT wird daraus wieder ein Bezug. Da Excel für die Multiplikation leerer Zellen einen Bezugsfehler anzeigt, setze ich vor die Rechnung WENNFEHLER. Es folgt die Rechenanweisung SUMMENPRODUKT und als zweites Argument die Anweisung, falls ein Fehler auftaucht: tue nichts. Das sind die zwei Anführungszeichen:

15.03.2019_0012

=WENNFEHLER(SUMMENPRODUKT(INDIREKT($D2);INDIREKT($E2));““)

Zum Schluss will ich natürlich wieder auf die Spalten D und E verzichten. Es müssen also die Formeln aus D2 und E2 kopiert werden und anstelle dieser Bezüge in die Formel kopiert werden. Ich habe die beiden Stellen oben fett markiert.

Anstelle von …INDIREKT($D2)… muss stehen
INDIREKT(WENN($A2<>$A1;“B“&ZEILE($A2)&“:B“&ZEILE($A2)+ZÄHLENWENN($A$2:$A$64;$A2)-1;““))

Anstelle von …INDIREKT($E2)… muss stehen
INDIREKT(WENN($A2<>$A1;“C“&ZEILE($A2)&“:C“&ZEILE($A2)+ZÄHLENWENN($A$2:$A$64;$A2)-1;““)))

Kopieren Sie die gesamte Formel ohne das = aus D2 und fügen Sie es in die Formel anstatt $D2 ein; genauso kopieren Sie die Formel (wieder ohne =) aus E2 und fügen es anstatt $E2 ein.

Die komplette Formel in F2 lautet jetzt:

=WENNFEHLER(SUMMENPRODUKT(INDIREKT(WENN($A2<>$A1;“B“&ZEILE($A2)&“:B“&ZEILE($A2)+ZÄHLENWENN($A$2:$A$64;$A2)-1;““));INDIREKT(WENN($A2<>$A1;“C“&ZEILE($A2)&“:C“&ZEILE($A2)+ZÄHLENWENN($A$2:$A$64;$A2)-1;““)));““)

Nach unten ausfüllen und die Spalten D und E löschen.

Komplexe Berechnungen lassen sich immer so in Einzelschritte aufteilen:

  • Berechnen Sie einen Schritt nach dem anderen
  • Scheuen Sie sich nicht vor Hilfsspalten
  • Beziehen Sie den nächsten Rechenschritt auf die Ergebnisse des vorhergehenden
  • Kopieren Sie dann die Hilfsformeln ohne das = in den folgenden Rechenschritt
  • Beziehen Sie sich wieder auf dieses Ergebnis, kopieren Sie auch die Formel … und so fort

15.03.2019_0013

 

Die komplette Tabelle mit allen Schritten gibt es hier: Bereich ermitteln

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