Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Zählfunktionen

Beitrag: Anzahlen zusammenhängender Blöcke *

Aufgabe
In Spalte A stehen wild durcheinander aufeinanderfolgende durch Leerzellen oder andere Zeichen unterbrochene Zeichenfolgen (im Beispiel XY).
Es soll die jeweilige Anzahl der Einer-, Zweier-, Dreier-, Viererblöcke usw. bestimmt werden.

       A               B               C               D               E               F       
1 XY           
2 XY           
3 c           
4     3    Einer  2  
5 XY    4    Zweier  3  
6     6    Dreier   
7 XY    9    Vierer   
8 XY    15    Fünfer  1  
9     17    Sechser   
10 XY    20    Siebener   
11 XY    21    Achter   
12 XY    22    usw.   
13 XY    23       
14 XY    24       
15 wf    25       
16 XY    26       
17     27       
18 XY    28       
19 XY    29       
20 1    30       



Lösung
In C4 (Hilfsspalte - ausblenden) steht:
{=KKLEINSTE(WENN((A$1:A$999<>"XY");ZEILE(X$1:X$999));ZEILE(X1))}
runterkopieren

Die Bezeichnungen Einer, Zweier, Dreier usw. ab E4 manuell eingeben.

Das Ergebnis ab F4:
=SUMMENPRODUKT((C$4:C$99-C$3:C$98-1=ZEILE(A1))*1)
runterkopieren

Zusammengefasst (ohne Hilfsspalte) von neopa:
=SUMMENPRODUKT((KGRÖSSTE((A$1:A$99<>"XY")*ZEILE(X$1:X$99);ZEILE(X$1:X$98))-KGRÖSSTE((A$1:A$99<>"XY")*ZEILE(X$1:X$99);ZEILE(X$2:X$99))-1=ZEILE(A1))*1)
Ist allerdings schnarchlangsam - also nur für kleinere Bereiche praktikabel.

Erläuterung
Stehen die Zeichenfolgen nicht in Spalte A sondern in Zeile 1, lautet C4:
{=KKLEINSTE(WENN((A$1:IQ$1<>"XY");SPALTE(A$1:IQ$1));ZEILE(A1))}
bzw. für die Zusammenfassung:
=SUMMENPRODUKT((KGRÖSSTE((A$1:IQ$1<>"XY")*SPALTE(A$1:IQ$1);SPALTE(A$1:HQ$1))-KGRÖSSTE((A$1:IQ$1<>"XY")*SPALTE(A$1:IQ$1);SPALTE(B$1:IQ$1))-1=ZEILE(A1))*1)