Microsoft Excel

Herbers Excel/VBA-Archiv

zählen hintereinader folgender gleicher Zahlen | Herbers Excel-Forum


Betrifft: zählen hintereinader folgender gleicher Zahlen von: Inge
Geschrieben am: 05.01.2010 09:29:18

Hallo,
innerhalb einer Spalte werden Werte aus verschiedenen Arbeitsblättern zwecks einer Auswertung zusammengefasst. Diese Auswertungsdaten wechseln ständig. Ich möchte nun erreichen, dass ich in der Auswertung erkenne, wie oft hintereinader der eine oder andere Zustand erreicht wird. Das Problem sind die immer wiederkehrenden Leerzellen. - Beispiel -

A2=1
A3=leer
A4=leer
A5=1
A6=1
A7=-1
A8=leer
.
.
A100

Nun benötige ich eine Formel um zu erkennen wie oft dieser Zustand (hier 1) trotzt Leerzellen hintereinader erfolgt . Denn in A2 bis einschließlich A6 erfolgt der Zustand genau 3 mal hintereinander. Sieht einfach aus, weiß dennoch nicht weiter!

Vielen Dank!

  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Worti
Geschrieben am: 05.01.2010 10:11:39

Hallo Inge,
meinst du sowas:

Tabelle1

 AB
1  3
21 
31 
4   
5   
6-1 
71 

verwendete Formeln
Zelle Formel
B1 =ZÄHLENWENN(A1:A100;1)


Gruß Worti


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: welga
Geschrieben am: 05.01.2010 10:14:46

Hallo Inge,

wenn ich dich richtig verstanden habe, dann willst du wissen, wie oft in deinem Beispiel die 1 auftaucht.

Das sollte doch eigenlich mit =zählenwenn(A2:A100;1) funktionieren.


Gruß
welga


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Inge
Geschrieben am: 05.01.2010 10:35:45

Hallo Worti und Welga,

die Zählenwenn-Funktion erlaubt nur die Feststellung der gesamten Anzahl der vorkommenden 1en innerhalb der Spalte. Da die 1 in der Spalte aber mehrmals hintereinader in unterschiedlichen Zellen vorkommt ist es mir wichtig wann der Zustand 1 innerhalb der Zelle wie oft hinterheinader vorkommt. Auch mit MAX und MIN funktioniert es nicht weil immer wieder die Leerzellen dazwischen sind. Auch nicht mit der Funktion z.B. =WENN(A7=1;WENN(UND(B6>0;A7=DA6);B6+1;WENN(A6=A7;1;2))) . Die leeren Zellen machen mir immer wieder eine Strich durch die Rechnung. Es funktioniert nur mi der Filterfunktion. Nur das ist für die Vielzahl der Tabellen zu umständlich und zu zeitaufwndig.

Danke


  

Betrifft: zählen bis zur ersten Abweichung - Arrayformel von: WF
Geschrieben am: 05.01.2010 10:28:37

Hi Inge,

folgende Arrayformel:
{=ZÄHLENWENN(INDIREKT("A2:A"&MIN(WENN(A2:A99<>A2;WENN(A2:A99<>"";ZEILE(2:99)))));A2)}

ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluß der Formel mit gleichzeitig Strg / Shift / Enter (statt Enter allein); - das erzeugt sie.

Obige Formel bedingt, daß der gesuchte Zustand immer in A2 beginnt - kann A2 auch leer sein, melde Dich: dann wird's etwas länger.

Salut WF


  

Betrifft: AW: zählen bis zur ersten Abweichung - Arrayformel von: Inge
Geschrieben am: 05.01.2010 10:41:58

Hallo WF,

vielen Dank für die schnelle Antwort.

Auch A2 kann leer sein. Kommt sogar vor, dass erst ab A11 ein Wert vorhanden ist.

Inge


  

Betrifft: voilà - dann also länger von: WF
Geschrieben am: 05.01.2010 10:47:58

Hi Inge,

statt fix A2 gibst Du dann zwei mal
INDEX(A1:A99;VERGLEICH(WAHR;A1:A99<>"";0))
ein - insgesamt also:

{=ZÄHLENWENN(INDIREKT("A2:A"&MIN(WENN(A2:A99<>INDEX(A1:A99;VERGLEICH(WAHR;A1:A99<>"";0)); WENN(A2:A99<>"";ZEILE(2:99)))));INDEX(A1:A99;VERGLEICH(WAHR;A1:A99<>"";0))) }

trapp, trapp, trapp, trapp, trapp, brrrrr
WF



http://www.excelformeln.de/
die ultimative Formelseite


  

Betrifft: AW: voilà - dann also länger von: Inge
Geschrieben am: 05.01.2010 11:31:10

Hallo WF,

der Ansatz scheint der richtige zu sein. Entspricht allerdings nicht ganz meinen Vorstellungen. Habe eine Excel - Tabelle hochgeladen. Näheres in der Tabelle

https://www.herber.de/bbs/user/66988.xlsx


  

Betrifft: xlsx also Excel 2007 kann ich nicht öffnen von: WF
Geschrieben am: 05.01.2010 11:45:21

speicher die Datei also unter xls ab (Excel 2003)

WF


  

Betrifft: AW: xlsx also Excel 2007 kann ich nicht öffnen von: Inge
Geschrieben am: 05.01.2010 11:59:37

Dann versuchen wir es mal hiermit.

https://www.herber.de/bbs/user/66989.xls

Inge


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: welga
Geschrieben am: 05.01.2010 10:48:26

Hallo Inge,

so ganz habe ich noch nicht nicht verstanden was du genau willst.

Willst du vielleicht sowas?
A2=1 B2=1
A3=leer B3=leer
A4=leer B4=leer
A5=1 B5=2
A6=1 B6=leer
A7=-1 B7=leer
A8=leer B8=leer

Oder willst du nur wissen, wie oft die 1 ab dem ersten auftreten direkt hintereinander vorkommt?


Gruß
welga


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: firmus
Geschrieben am: 05.01.2010 17:53:03

Hallo Inge,

nimm eine freie Spalte und trage in die 2. Zeile folgendes ein.
Beispiel: Spalte D
Zelle D1: wert "1"
Zelle D2: "=if(A2=A1;D1+1;1)
Zelle D3 bis Ende: Zelle D2 runterkopieren.

Über Autofilter kannst Du den maximalen Wert sehen: Filter auf Sp.A "-1" oder "1" und Filter in Sp.D ansehen.

Trivialer aber für mich prima funktionierender Ansatz.

Gruß,
Firmus


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Inge
Geschrieben am: 05.01.2010 20:53:41

Hallo Firmus,

vielen Dank, auch darüber komme ich leider nicht zum gewünschten Erfolg. Das sortieren ist aufgrund der Menge der Tabelllen und dem Vergleich der Daten aus unterschiedlichen Arbeitsmappen immer noch zu Zeitaufwendig und nicht richtig nachzuhalten. Ich benötige eine Zählfunktion ähnlich dieser =WENN(A7=1;WENN(UND(D6>0;A7=D6);D6+1;WENN(A6=A7;1;2)))*1. Diese zählt, vorausgesetzt es sind keine Leerzellen vorhanden, schön der Reihe nach wie oft eine 1 vorhanden ist. Doch die Leerzellen machen mir leider einen Strich durch die Rechnung. Hier eine Tabelle die meine Anforderungen in etwa darstellt.

https://www.herber.de/bbs/user/66989.xls

Vielen Dank für die Unterstützung

Gruß Inge


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Worti
Geschrieben am: 05.01.2010 21:43:40

Hallo Inge,
hab mal eine UDF gebastelt. Wenn ich es richtig verstanden habe, sollte es so gehen:
Folgenden Code in ein allgemeines Modul kopieren:

Function MaxAnzahl(rngBereich As Range, strZeichen As String) As Long

      Dim lngMerkMax As Long, lngZeile As Long
      
      MaxAnzahl = 0
   
      If rngBereich.Columns.Count > 1 Then
         MaxAnzahl = "#Wert"
      Else
         If rngBereich.Count = 1 And CStr(rngBereich.Cells(1, 1).Value) = strZeichen Then
            MaxAnzahl = 1
         Else
            lngMerkMax = 0
            lngZeile = 1
            Do
               
               Do While (CStr(rngBereich.Cells(lngZeile, 1).Value) = strZeichen Or rngBereich. _
Cells(lngZeile, 1).Value = "") And _
                  lngZeile <= rngBereich.Count
                  If CStr(rngBereich.Cells(lngZeile, 1).Value) = strZeichen Then
                     lngMerkMax = lngMerkMax + 1
                  End If
                  lngZeile = lngZeile + 1
               Loop
               If lngMerkMax > MaxAnzahl Then
                  MaxAnzahl = lngMerkMax
               End If
               lngMerkMax = 0
               lngZeile = lngZeile + 1
            Loop Until lngZeile > rngBereich.Count
         End If
      End If
      
End Function
Das ergibt dann bei Eingabe der Formel im Tabellenblatt:

Tabelle1
 AB
105max 1 Mit UDF max -1 mit Udf
10646

verwendete Formeln
Zelle Formel
A106 =MaxAnzahl($A$2:$A$100;1)
B106 =MaxAnzahl($A$2:$A$100;-1)

http://c.excelhost.de/c_beverly/getfile.php?id=122 Tabellendarstellung in Foren Version 5.3



Gruß Worti


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Inge
Geschrieben am: 06.01.2010 01:52:09

Hallo Worti,

Danke für Deine Antwort. Bin in VBA leider nicht so bewandert. Werde mich trotzdem umgehend mit Deinem Lösungsvorschlag befassen. Vielleicht komme ich auf Dich nochmal zurück. Habe heute noch eine andere Lösung in Excel erhalten. Einfach zu händeln und beinhaltet auch den Lösungsansatz, den ich gesucht habe.

Vielen Dank für Deine Bemühungen

Gruß Inge


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Josef B
Geschrieben am: 05.01.2010 21:38:41

Hallo Inge

Ich würde das mit zwei Hilsfsspalten machen. Diese kannst du ja nachträglich ausblenden.

Ich habe die Formeln einmal in deine Tabelle eingetragen.

https://www.herber.de/bbs/user/66996.xls

Gruss Sepp


  

Betrifft: AW: zählen hintereinader folgender gleicher Zahlen von: Inge
Geschrieben am: 06.01.2010 01:43:27

Hallo Josef B,

vielen, vielen Dank. Das funktioniert prima. Habe bereits wochenlang daran gearbeitet und kam nicht auf die richtige Lösung.

Bis zum nächsten Mal

Inge


Beiträge aus den Excel-Beispielen zum Thema "zählen hintereinader folgender gleicher Zahlen"