Frage von Slevi89, 52

Wie realisiere ich eine Blockgrößenerkennung in Excel?

Hallo Leute, ich habe eine Spalte in Excel die aus ca. 14K Einträgen besteht und nur Blöcke von Nullen und Einsen unterschiedlicher Länge enthält. Mich interessieren nur 1er Blöcke. Sagen wir es gibt 20 Stück (hypothetisch), dann möchte ich einer neuen Spalte (die entsprechend 20 Zellen besitzt) in jeder Zelle die Länge bzw. Summe, was hier das Gleiche ist, von jedem 1er Block stehen haben.

Bin mit meinen bisherigen Ideen nicht weiter gekommen und mit VB habe ich so gut wie keine Erfahrung. Habe schon viel rechachiert, aber nie das gefunden, was ich tatsächlich brauchen kann.

Freue mich über jede Hilfe :)

Slevi

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 20

Wenn Spalte A die Spalte mit den Einsen- und Nullenblöcken ist, dann kannst du direkt daneben abfragen, ob links davon ein Einserblock steht und mit LÄNGE die Länge abfragen.

=WENN(LINKS(A1;1)="1";LÄNGE(A1);"")

Das ziehst du herunter.

Kommentar von Slevi89 ,

Leider bringt das nichts.

Hier mal ein Ausschnitt meiner Spalte A mit gewünschtem Ergebnis.

A                         B
0                        Länge
0                            5                  
0                            3
1                            :
1
1
1
1
0
0
1
1
1
0
0
:
:

Kommentar von Suboptimierer ,

Achso, jetzt habe ich zumindest schon einmal das Problem verstanden...

Kommentar von Suboptimierer ,

Wenn du keine Hilfsspaltenallergie hast, kannst du es wie folgt machen:

B1: 0
B2: =WENN(A2=1;B1+1;0) → herunter ziehen
C1: =WENN(B3<B2;ZEILE();"") → herunter ziehen
F2: =WENNFEHLER(INDEX($B$1:$B$15;KKLEINSTE($C$1:$C$15;ZEILE(A1)));"") → herunter ziehen

In Spalte B werden die aufeinander folgenden Einsen gezählt.
In Spalte C wird die Zeile der maximalen Anzahl pro Block notiert.
In Spalte F werden die Werte zu den notierten Zeilen sequenziell ausgelesen.

Kommentar von Slevi89 ,

Ich danke dir! Es funktioniert, allerdings wird immer 1 zu wenig gezählt. Kein Problem ich addiere einfach +1 :)

Kommentar von Suboptimierer ,

Bitteschön! Bei mir passt es (gerade nochmal geschaut).

Aber da kann man sich ja zu helfen wissen. ;)

Expertenantwort
von Oubyi, Community-Experte für Excel, 2

Ich beziehe
mich auf Deinen Kommentar:

Also als Ergebnis sowas wie:

Block     Länge

1               10
2               50
3               30 

Angenommen Dein 1en und 0en stehen in Spalte A ab A1.
Dann mache Dir eine Hilfsspalte (die ausgeblendet werde kann), im Beispiel Spalte B.
In B1:

=A1

In B2 und runterkopieren, soweit wie nötig:

=WENN(UND(A1=1;A2=0);"#"&B1;SUMME(B1;A2))

Jetzt hast Du am Ende der 1er-Reihen immer ein #-Zeichen und die Zahl für den Block.
Schreibe jetzt z.B. in D1: "Block"
und darunter die laufenden Zahlen 1 bis Ultimo.
In E1 dann "Länge" und diese Formel in E2 und runterkopieren:

{=(WENNFEHLER(WECHSELN(INDEX($B$1:$B$15000;KKLEINSTE(WENN(ISTZAHL(SUCHEN("#";$B$1:$B$15000));ZEILE($B$1:$B$15000));ZEILE(A1)));"#";"")*1;""))}

**ACHTUNG!**
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.

Eine Matrixformel für so einen großen Bereich könnte etwas auf die Performance gehen, sollte aber noch erträglich sein.
Klappt es?

P.S.: Das ganze ist so ein bisschen getrickst und geht wahrscheinlich eleganter, aber im Moment komme ich nicht drauf.

Antwort
von iMPerFekTioN, 30

So :), hallo erst mal :P,

Ich hab rum getüftelt und bin (Ohne VBA) zu einer Lösung gekommen...

Etwas muss man schon verändern, also, du brauchst z.B. mehrere Hilfsspalten, vielleicht geht es auch mit weniger aber ich bin mit meiner Lösung mehr als zufrieden wenn ich mir die anderen Lösungen so anschaue...

So, fangen wir mit der Formatierung der Tabelle an:

1. Formatierung:

Zelle C1 benennst du mit "Zahl", Zelle D1 mit "Block", Zelle E1 mit "Länge"

Deine 1en und 0en schreibst du dann in die Spalte C.

2. Formeln:

So, in Zelle B2 kommt folgende Formel:

=WENN(C2=1;1;0)

In Zelle A2 kommt diese Formel:

=WENN(UND(NICHT(B2=0);C2=0);VERKETTEN(B2;"s");B2)

Zelle B3 diese Formel:

=WENN(UND(B2=0;C2=0;C3=1);B2+1;B2)

Und in Zelle B4 diese Formel:

=WENN(UND(NICHT(B3=0);C3=0;C4=1);B3+1;B3)

Die Formel von Zelle B4 kannst du jetzt ganz runter ziehen.

So, die Spalte D (Block) kannst du ja einfach durch nummerieren, schreib eine 1 in D2 und zieh diese Runter das sich die anderen Zahlen von selbst erstellen.

In E2 kommt jetzt diese Formel:

=ZÄHLENWENN($A$1:$A$900;D2)

Die Formel ziehst du auch runter und du hast deinen gewünschten Effekt!

Grüße,

iMPerFekTioN

Kommentar von Slevi89 ,

Ich danke dir für die Tüftelei! Allerdings bekomme ich so nur die insgesammte Anzahl meiner 1en. Mein Ziel ist es aber diese nur blockweise zu trennen. Habe dein Beispiel angewendet, aber der zählt mir nur alle 1en zusammen

Kommentar von iMPerFekTioN ,

Das kann aber nicht sein =D
Ich habs ja genau so und es funktioniert :D

Kommentar von iMPerFekTioN ,

Dann einfacher:

Neue Formatierung, ... folgende Formeln eingeben und runter ziehen:

A2:

=WENN(UND(NICHT(B2=0);C2=0);VERKETTEN(B2;"s");B2)

B2:

=WENN(UND(B1>0;C1=1;C2=1);B1;WENN(UND(B1>0;C1=1;C2=0);B1;WENN(UND(B1>0;C1=0;C2=0;B1+1);B1;WENN(UND(B1=0;C1=0;C2=0);B1;B1+1))))

C2:

Deine Binären Zahlen wie du lustig bist

D2:

Da gibst du die Zahl von deinem Block an den du ausfiltern willst (Wenn du z.B. 2 eingibst wird dir nur die Anzahl vom 2. Block angegeben)

E2:

=WENN(ZÄHLENWENN($A$1:$A$900;D2)<1;"";ZÄHLENWENN($A$1:$A$900;D2))


Grüße,


iMPerFekTioN


Kommentar von iMPerFekTioN ,

Bzw, wir machen das noch einfacher...

Hier haste den Link zu meiner DropBox, da ist die Datei fertig drin:

https://www.dropbox.com/s/v6rtnuulj8tuvkl/Blockberechnung.xlsx?dl=0

Antwort
von Jackie251, 32

Kannst du das näher erklären?
Sagen wir es gibt 20 x 1er Blöcke je 10 mit 0 und 1

Jetzt möchstest du diese 20 Blöcken untereinander in einer neuen Spalte.
Ok soweit ist das eine Aufgabe.

Jetzt möchtest du in jede Zelle die Info haben wie Lang der Block ist, bzw seine Summe.
Ähm naja, ich hätte eine Vermutung wie lang so ein 1ner Block wohl sein wird...

Da passt also was nicht zusammen.

Kommentar von Slevi89 ,

Nicht ganz ;)

Ein Block sind mehrere Zellen. Sprich du hast 10 Zellen in denen eine 1 steht, dann ist das ein Block der länge 10. Danach kommen 50 Nullen, dann wiederum 30 Zellen mit 1en, ergo ein neuer Block der länge 30 usw. Die Anzahl der 1er Blöcke die bei mir vorkommen ist mit bei 14000 Einträgen nicht bekannt. Ich möchte aber in einer neuen Spalte stehen haben wie groß ein jeder solcher 1er Block ist.

Also als Ergbenis sowas wie:

Block     Länge

1               10

2               50

3               30

.                 .

.                 .

.                 .

Keine passende Antwort gefunden?

Fragen Sie die Community