HERBERS Excel-Forum - das Archiv
Thema: zählen hintereinader folgender gleicher Zahlen | Herbers Excel-Forum
zählen hintereinader folgender gleicher Zahlen
Inge
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!
AW: zählen hintereinader folgender gleicher Zahlen
Worti
Hallo Inge,
meinst du sowas:
Tabelle1
| A | B | |
| 1 | 3 | |
| 2 | 1 | |
| 3 | 1 | |
| 4 | ||
| 5 | ||
| 6 | -1 | |
| 7 | 1 |
| verwendete Formeln | |
| Zelle | Formel |
| B1 | =ZÄHLENWENN(A1:A100;1) |
Gruß Worti
AW: zählen hintereinader folgender gleicher Zahlen
welga
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
AW: zählen hintereinader folgender gleicher Zahlen
Inge
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
zählen bis zur ersten Abweichung - Arrayformel
WF
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
AW: zählen bis zur ersten Abweichung - Arrayformel
Inge
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
voilà - dann also länger
WF
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
AW: voilà - dann also länger
Inge
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
xlsx also Excel 2007 kann ich nicht öffnen
WF
speicher die Datei also unter xls ab (Excel 2003)
WF
AW: xlsx also Excel 2007 kann ich nicht öffnen
Inge
AW: zählen hintereinader folgender gleicher Zahlen
welga
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
AW: zählen hintereinader folgender gleicher Zahlen
firmus
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
AW: zählen hintereinader folgender gleicher Zahlen
Inge
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
AW: zählen hintereinader folgender gleicher Zahlen
Worti
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
Tabelle1
| A | B | |
| 105 | max 1 Mit UDF | max -1 mit Udf |
| 106 | 4 | 6 |
| 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
AW: zählen hintereinader folgender gleicher Zahlen
Inge
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
AW: zählen hintereinader folgender gleicher Zahlen
Josef
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
AW: zählen hintereinader folgender gleicher Zahlen
Inge
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
Excel-Beispiele