Excel – Auswerten bei Lücken

In einer Liste ist die Spalte A mit den Projektnummern nicht ganz gefüllt – so wird sie aus einem anderen System übergeben, also nicht auf den Ersteller der Tabelle schimpfen!

Das Wort “Ende” in Zelle A16 habe ich schon hingeschrieben, weil ich das zum Berechnen brauche. Das steht sonst nicht da.

Wir haben also eine Projektnummer in A2, dann drei Zellen, dann kommt in A6 die nächste Projektnummer, gefolgt von vier leeren Zellen und so fort. Jetzt sollen die Werte pro Projekt addiert werden, aber das Ausfüllen der Projektnummern ist wegen der tatsächlichen Länge der Tabelle nicht machbar.

Was nun?

Zwei Lösungen: eine mit Formel und eine mit PowerQuery (ab Excel 2016/Office 365). Heute stelle ich die Formel vor, morgen zeige ich, wie PowerQuery das löst.

Meine Tabelle braucht als erstes unter der letzten Zelle einen Stoppbegriff, bei mir ist es “Ende” in A16. Der letzte Datensatz ist in Zeile 15. Neben der Tabelle schreibe ich erst einmal die Projektnummer untereinander, zum Schluss mein  Stoppwort “Ende” (D2 bis D4).

Zuerst ermittle ich für jedes Projekt, in welcher Zeile es beginnt und in welcher es endet. Wenn ich weiß, dass Projekt 1 in Zeile 2 beginnt und in Zeile 5 endet, kann ich eine Summe bilden aus B2:B5. Genauso geht es mit den übrigen Projekten: Projekt 1 beginnt in Zeile 6 und endet in Zeile 10 – die Summe muss also von B6:B10 gehen.

Schritt 1 ist also das Ermitteln des Beginns. Dazu verwende ich einen  VERGLEICH.

=VERGLEICH(Suchbegriff;Suchmatrix;Vergleichstyp)

=VERGLEICH(D2;$A$1:$A$16;0)

Gesucht wird nach dem Wert aus D2 (der Projektnummer) im Bereich A1:A16 – hier muss die gesamte zu durchsuchende Spalte angegeben werden. Der Vergleichstyp 0 steht dafür, dass eine genaue Übereinstimmung gefunden werden muss.

Die Formel wird nach unten ausgezogen, so dass jetzt für alle drei Projektnummern sowie das Stoppwort “Ende” der Beginn ermittelt wurde.

Schritt 2 ist das Errechnen der Projektendes: in F2 wird ermittelt, wo das Projekt aufhört. Das ist immer eine Zeile oberhalb des nächstfolgenden Projektes.

In F2 steht

=E3-1

Auch diese Formel wird nach unten ausgezogen; für das Stoppwort “Ende” benötigen wir den Wert nicht, aufgehört wird mit der Formel in F4. Jetzt haben wir Beginn und Ende für jedes Projekt.

Schritt 3 ist der Zusammenbau der Zeilennummern in einen Bezug. Das Muster ist

SUMME(B+Zeilennummer aus E2:B+Zeilennummer aus F2)

Da man so nicht schreiben kann, benötige ich die Funktion INDIREKT. INDIREKT erlaubt die Angabe eines geschriebenen, festen Wertes (bei mir das “B” für die Spalte) in Kombination mit einem errechneten Wert (meine Zeilennummer).

Aus INDIREKT(“B”&E2) wird B2
Aus INDIREKT(“B”&F2) wird B6

Beides verwende ich mit der SUMME:

=SUMME(INDIREKT(“B”&E2):INDIREKT(“B”&F2)

Wer auf die Hilfsspalten Beginnt und Ende verzichten will, muss in der INDIREKT-Funktion den Zellinhalt von E2 ersetzen durch den VERGLEICH in dieser Zelle:

=SUMME(INDIREKT(“B”&VERGLEICH(D2;$A$1:$A$16;0) ….

Das Ende können wir alternativ auch ermitteln mit einer Suche nach dem nächstfolgenden Projekt, von dessen Zeilennummer 1 abgezogen werden muss: VERGLEICH(D2;$A$1:$A$16;0)-1. Das kann anstelle der F2 in INDIREKT eingesetzt werden. Komplett sieht die Formel dann so aus:

=SUMME(INDIREKT(“B”&VERGLEICH(D2;$A$1:$A$16;0):INDIREKT(“B”&VERGLEICH(D2;$A$1:$A$16;0)-1))

Die Beispieltabelle können Sie hier herunterladen. Zum Gucken und Ausprobieren.

In der kommenden Woche (30.10.2018) zeige ich in der gleichen Tabelle, wie man die Auswertung mit PowerQuery macht.