Kennen Sie das Problem? Sie haben eine Excel-Tabelle mit Daten, die in Zeilen angeordnet sind. Jede Zeile enthält eine Abfolge von Werten, von denen einige ausgefüllt und andere leer sind. Ihre Aufgabe ist es, die Anzahl der zusammenhängenden, ausgefüllten Blöcke in jeder Zeile automatisch zu zählen. Eine manuelle Zählung kann zeitaufwändig und fehleranfällig sein, insbesondere bei großen Datensätzen. Glücklicherweise gibt es einen cleveren Excel-Formel-Hack, mit dem Sie diese Aufgabe im Handumdrehen erledigen können!
Das Problem verstehen: Was sind zusammenhängende, ausgefüllte Blöcke?
Bevor wir uns der Lösung zuwenden, definieren wir klar, was wir unter einem „zusammenhängenden, ausgefüllten Block” verstehen. Stellen Sie sich eine Zeile in Ihrer Excel-Tabelle vor, die so aussieht:
| LEER | WERT | WERT | LEER | WERT | LEER | LEER | WERT | WERT | WERT | LEER |In dieser Zeile gibt es drei zusammenhängende, ausgefüllte Blöcke:
- Block 1: Zelle 2 & 3
- Block 2: Zelle 5
- Block 3: Zelle 8, 9 & 10
Unser Ziel ist es, eine Excel-Formel zu entwickeln, die für jede Zeile automatisch die Anzahl dieser Blöcke ermittelt.
Die Lösung: Eine Kombination aus Funktionen
Die Excel-Formel, die wir verwenden werden, kombiniert mehrere Funktionen, um das gewünschte Ergebnis zu erzielen. Wir werden die Funktionen SUMME, PRODUKT und ISTLEER verwenden. Lassen Sie uns die Logik hinter der Formel Schritt für Schritt aufschlüsseln:
- ISTLEER: Diese Funktion prüft, ob eine Zelle leer ist. Sie gibt WAHR zurück, wenn die Zelle leer ist, und FALSCH, wenn sie nicht leer ist.
- Vergleich mit der vorherigen Zelle: Wir vergleichen den ISTLEER-Wert der aktuellen Zelle mit dem ISTLEER-Wert der vorherigen Zelle. Wenn die aktuelle Zelle NICHT leer ist (ISTLEER gibt FALSCH zurück) UND die vorherige Zelle LEER war (ISTLEER gibt WAHR zurück), dann beginnt ein neuer Block.
- Konvertierung in numerische Werte: WAHR und FALSCH werden in Excel intern als 1 und 0 behandelt. Wir nutzen das aus.
- PRODUKT: Wir verwenden die Funktion PRODUKT, um zu prüfen, ob BEIDE Bedingungen (aktuelle Zelle NICHT leer UND vorherige Zelle LEER) erfüllt sind. PRODUKT(0,1) oder PRODUKT(1,0) ergeben 0, während PRODUKT(1,1) 1 ergibt.
- SUMME: Wir summieren die Ergebnisse der PRODUKT-Funktion über die gesamte Zeile, um die Gesamtzahl der Blöcke zu erhalten.
Die konkrete Formel
Angenommen, Ihre Daten beginnen in Spalte B und enden in Spalte G, und Sie möchten die Anzahl der Blöcke in Zeile 2 zählen. Die Formel, die Sie in eine beliebige leere Zelle (z.B. H2) eingeben würden, lautet:
=SUMME(PRODUKT(--(ISTLEER(B2:G2)),--(NICHT(ISTLEER(A2:F2)))))Wichtig: Beachten Sie, dass der Bereich in der zweiten ISTLEER-Funktion (A2:F2) um eine Spalte nach links verschoben ist (A2 statt B2, F2 statt G2). Außerdem gehen wir davon aus, dass die Spalte LINKS von Ihrem Datenbereich IMMER leer ist. Wenn das nicht der Fall ist, passen Sie die Formel entsprechend an, indem Sie eine leere Zelle referenzieren (z.B. „=SUMME(PRODUKT(–(ISTLEER(B2:G2)),–(NICHT(ISTLEER($A$1:F2)))))”, wenn A1 leer ist).
Die doppelten Minuszeichen (–): Die doppelten Minuszeichen werden verwendet, um die booleschen Werte (WAHR/FALSCH) in numerische Werte (1/0) umzuwandeln. Dies ist notwendig, damit die PRODUKT-Funktion korrekt funktioniert.
Schritt-für-Schritt-Erklärung der Formel am Beispiel
Lassen Sie uns die Formel an einem konkreten Beispiel durchgehen. Angenommen, Zeile 2 enthält folgende Werte:
| A2 (leer) | B2 (10) | C2 (20) | D2 (leer) | E2 (30) | F2 (leer) | G2 (40) |So funktioniert die Formel:
- ISTLEER(B2:G2): Gibt ein Array von booleschen Werten zurück: {FALSCH, FALSCH, WAHR, FALSCH, WAHR, FALSCH}
- NICHT(ISTLEER(A2:F2)): Gibt ein Array von booleschen Werten zurück: {WAHR, WAHR, FALSCH, WAHR, FALSCH, WAHR}
- –(ISTLEER(B2:G2)): Wandelt die booleschen Werte in numerische Werte um: {0, 0, 1, 0, 1, 0}
- –(NICHT(ISTLEER(A2:F2))): Wandelt die booleschen Werte in numerische Werte um: {1, 1, 0, 1, 0, 1}
- PRODUKT({0, 0, 1, 0, 1, 0}, {1, 1, 0, 1, 0, 1}): Multipliziert die entsprechenden Elemente der Arrays: {0, 0, 0, 0, 0, 0}
*Genauer: PRODUKT(0,1), PRODUKT(0,1), PRODUKT(1,0), PRODUKT(0,1), PRODUKT(1,0), PRODUKT(0,1)* - SUMME({0, 0, 0, 0, 0, 0}): Summiert die Werte im Array: 0
In diesem Fall gibt die Formel 0 zurück. Warum? Die Formel funktioniert, ABER sie zählt nur den Beginn jedes Blocks. Unsere Annahme, dass die erste Spalte leer ist, ist hier nicht korrekt, weil die erste Spalte (A2) zwar leer ist, aber direkt daneben (B2) bereits ein Wert steht. Es gibt also KEINEN Übergang von leer zu nicht leer ZU BEGINN des Bereiches. Um das Problem zu lösen, müssen wir eine Hilfsspalte HINZUFÜGEN, die immer leer ist, ODER die Formel leicht anpassen. Wir korrigieren die Formel unten:
=SUMME(PRODUKT(--(ISTLEER(B2:G2)),--(NICHT(ISTLEER(A2:F2)))))+(NICHT(ISTLEER(B2)))*1Der zusätzliche Term am Ende berücksichtigt den Fall, in dem der erste Wert im Bereich NICHT leer ist und daher als Block gezählt werden muss. NICHT(ISTLEER(B2)) gibt WAHR (also 1) zurück, wenn B2 nicht leer ist, und FALSCH (also 0), wenn B2 leer ist. Dieses Ergebnis wird dann mit 1 multipliziert und zur Gesamtsumme addiert.
Mit der korrigierten Formel und den gleichen Daten:
=SUMME(PRODUKT(--(ISTLEER(B2:G2)),--(NICHT(ISTLEER(A2:F2)))))+(NICHT(ISTLEER(B2)))*1Erhalten wir:
- Das Ergebnis von SUMME(PRODUKT(–(ISTLEER(B2:G2)),–(NICHT(ISTLEER(A2:F2))))) bleibt 0 (wie oben).
- NICHT(ISTLEER(B2)): Gibt WAHR zurück (weil B2 nicht leer ist).
- WAHR * 1: Ergibt 1.
- 0 + 1: Ergibt 1.
Diese Formel muss jetzt angepasst werden, falls Spalten INNEN des zu prüfenden Bereichs von LEER zu NICHT LEER übergehen!
Nun ein zweites Beispiel mit einer Anpassung:
| A2 (leer) | B2 (10) | C2 (20) | D2 (leer) | E2 (leer) | F2 (30) | G2 (leer) |Die korrekte Formel, wie sie oben schon steht:
=SUMME(PRODUKT(--(ISTLEER(B2:G2)),--(NICHT(ISTLEER(A2:F2)))))+(NICHT(ISTLEER(B2)))*1Die Auswertung:
- ISTLEER(B2:G2): {FALSCH, FALSCH, WAHR, WAHR, FALSCH, WAHR}
- NICHT(ISTLEER(A2:F2)): {WAHR, WAHR, FALSCH, FALSCH, WAHR, FALSCH}
- –(ISTLEER(B2:G2)): {0, 0, 1, 1, 0, 1}
- –(NICHT(ISTLEER(A2:F2))): {1, 1, 0, 0, 1, 0}
- PRODUKT({0, 0, 1, 1, 0, 1}, {1, 1, 0, 0, 1, 0}): {0, 0, 0, 0, 0, 0}
- SUMME({0, 0, 0, 0, 0, 0}): 0
- NICHT(ISTLEER(B2)): WAHR (weil B2 nicht leer ist)
- WAHR * 1: 1
- 0 + 1: 1
Die Anzahl der Blöcke (1) wird korrekt berechnet.
Nun das dritte Beispiel:
| A2 (leer) | B2 (leer) | C2 (leer) | D2 (leer) | E2 (leer) | F2 (leer) | G2 (leer) |Die Auswertung:
- ISTLEER(B2:G2): {WAHR, WAHR, WAHR, WAHR, WAHR, WAHR}
- NICHT(ISTLEER(A2:F2)): {FALSCH, FALSCH, FALSCH, FALSCH, FALSCH, FALSCH}
- –(ISTLEER(B2:G2)): {1, 1, 1, 1, 1, 1}
- –(NICHT(ISTLEER(A2:F2))): {0, 0, 0, 0, 0, 0}
- PRODUKT({1, 1, 1, 1, 1, 1}, {0, 0, 0, 0, 0, 0}): {0, 0, 0, 0, 0, 0}
- SUMME({0, 0, 0, 0, 0, 0}): 0
- NICHT(ISTLEER(B2)): FALSCH (weil B2 leer ist)
- FALSCH * 1: 0
- 0 + 0: 0
Kein Block, also korrekt!
Alternativer Ansatz mit HILFSSPALTE
Ein anderer Ansatz ist die Nutzung einer Hilfsspalte VOR Ihren Daten. Wenn Sie sicherstellen, dass Spalte A immer leer ist, wird die Formel etwas einfacher:
=SUMME(PRODUKT(--(ISTLEER(B2:G2)),--(NICHT(ISTLEER(A2:F2)))))Da die Hilfsspalte IMMER leer ist, können Sie sich auf die reinen Übergänge verlassen. Das macht die Formel etwas robuster und leichter verständlich.
Anwendung auf mehrere Zeilen
Sobald Sie die Formel für die erste Zeile (z.B. Zeile 2) erstellt haben, können Sie sie einfach auf die anderen Zeilen anwenden, indem Sie die Zelle mit der Formel (H2) auswählen und den kleinen Quadrat-Griff in der unteren rechten Ecke nach unten ziehen. Excel passt die Zellbezüge automatisch an die jeweilige Zeile an.
Vorteile dieses Hacks
- Automatisierung: Spart Zeit und reduziert das Fehlerrisiko im Vergleich zur manuellen Zählung.
- Dynamisch: Die Formel passt sich automatisch an Änderungen in den Daten an.
- Flexibel: Kann leicht an verschiedene Datenbereiche angepasst werden.
Fazit
Mit diesem Excel-Formel-Hack können Sie die Anzahl der zusammenhängenden, ausgefüllten Blöcke in einer Zeile schnell und einfach zählen. Die Kombination aus SUMME, PRODUKT und ISTLEER ermöglicht eine elegante Lösung für dieses häufige Problem. Experimentieren Sie mit der Formel und passen Sie sie an Ihre spezifischen Bedürfnisse an, um Ihre Excel-Arbeit noch effizienter zu gestalten. Denken Sie daran, die Formel sorgfältig zu testen, um sicherzustellen, dass sie in allen Fällen korrekt funktioniert, insbesondere wenn Ihre Daten komplexe Muster aufweisen.