Supportnet / Forum / Tabellenkalkulation
Am häufigsten vorkommenden Werte
Frage
Hallo zusammen,
kann mir bitte jemand sagen, wie ich die in eine Spalte am häufigsten vorkommenden Werte ermitteln kann!
Ich habe eine Spalte mit mehrmals vorkommenden Ländernamen und möchte die Top 5 Länder ermitteln.
Danke im Voraus!
Rami
Antwort 1 von Rami
Mit Formeln bitte und nicht mit AutoFilter!
Antwort 2 von TeX
Hallo Rami,
der Weg der mir spontan einfällt führt zum Ziel ist jedoch sicherlich anders eleganter realisierbar. Nichts desto trotz hier mein Lösungsvorschlag.
In einer Hilfsspalte mittels:
ermitteln, wie oft Deutschland im Bereich A1 bis A100 vorkommt. Für das Land Frankreich dann entsprechend:
Die Hilfsspalte kann dann noch mit der einfachen Sortierfunktion aus der Standard-Symbolleiste geordnet werden.
Gruß TeX
der Weg der mir spontan einfällt führt zum Ziel ist jedoch sicherlich anders eleganter realisierbar. Nichts desto trotz hier mein Lösungsvorschlag.
In einer Hilfsspalte mittels:
=Zählenwenn(A1:A100;"Deuschland")
ermitteln, wie oft Deutschland im Bereich A1 bis A100 vorkommt. Für das Land Frankreich dann entsprechend:
=Zählenwenn(A1:A100;"Frankreich")
in einer anderen Zelle der Hilfsspalte.Die Hilfsspalte kann dann noch mit der einfachen Sortierfunktion aus der Standard-Symbolleiste geordnet werden.
Gruß TeX
Antwort 3 von Rami
Hallo TeX,
wie es eleganter geht, genau das möchte ich wissen. Und danke für den Vorschlag!
Habe die Funktion MODALWERT gefunden. Die ist aber nur mit Zahlen verwendbar und nicht mit Texte.
wie es eleganter geht, genau das möchte ich wissen. Und danke für den Vorschlag!
Habe die Funktion MODALWERT gefunden. Die ist aber nur mit Zahlen verwendbar und nicht mit Texte.
Antwort 4 von CaroS
Hallo Rami,
ich hätte da mal einen Lösungsvorschlag mit Formeln, allerdings benötigt der auch eine Hilfsspalte. Da aber seit einer Woche nichts besseres im Angebot ist ...
Spalte A: Ländernamen (A1:A100 oder weniger, leere Zellen zwischendurch möglich)
Spalte B (oder andere): Hilfsspalte - kann nach Eingabe der Formeln verborgen werden Formel B1: =WENN(ZÄHLENWENN(A$1:A11; A11) = 1; ZÄHLENWENN(A$1:A$100; A11) * ANZAHL2(A$1:A$100) - ZEILE(); "")
Formeln ab B2: Zelle B1 so weit wie nötig runterkopieren/-ziehen
bel. Spalte (z. B. C): Ergebnisse
Formel C1: =WENN(ZEILE() > 5; ""; INDIREKT("A" & VERGLEICH(KGRÖSSTE(B$1:B$100; ZEILE()); B$1:B$100; 0)))
(Falls Hilfsspalte nicht B, dann entsprechend anpassen.)
Formeln C2:C5: Zelle C1 bis in 5. Zeile runterkopieren/-ziehen
- Die Bereiche A$1:A$100 und B$1:B$100 können größer gewält werden, als Werte vorhanden sind.
- Werden die 6 häufigsten Ländernamen benötigt, Formeln in C anpassen: =WENN(ZEILE() > 6; ...
- Sind mehrere Ländernamen gleich oft vorhanden, werden die zuerst angezeigt, die in Spalte A weiter oben stehen (kleinere Zeilennummer) und die anderen danach bzw. gar nicht angezeigt. Um innerhalb von gleich vielen die untersten zuerst anzuzeigen, die Formel der Hilfsspalte ändern: =WENN(ZÄHLENWENN(A$1:A11; A11) = 1; ZÄHLENWENN(A$1:A$100; A11) * ANZAHL2(A$1:A$100) + ZEILE(); "")
Gruß,
CaroS
ich hätte da mal einen Lösungsvorschlag mit Formeln, allerdings benötigt der auch eine Hilfsspalte. Da aber seit einer Woche nichts besseres im Angebot ist ...
Spalte A: Ländernamen (A1:A100 oder weniger, leere Zellen zwischendurch möglich)
Spalte B (oder andere): Hilfsspalte - kann nach Eingabe der Formeln verborgen werden Formel B1: =WENN(ZÄHLENWENN(A$1:A11; A11) = 1; ZÄHLENWENN(A$1:A$100; A11) * ANZAHL2(A$1:A$100) - ZEILE(); "")
Formeln ab B2: Zelle B1 so weit wie nötig runterkopieren/-ziehen
bel. Spalte (z. B. C): Ergebnisse
Formel C1: =WENN(ZEILE() > 5; ""; INDIREKT("A" & VERGLEICH(KGRÖSSTE(B$1:B$100; ZEILE()); B$1:B$100; 0)))
(Falls Hilfsspalte nicht B, dann entsprechend anpassen.)
Formeln C2:C5: Zelle C1 bis in 5. Zeile runterkopieren/-ziehen
- Die Bereiche A$1:A$100 und B$1:B$100 können größer gewält werden, als Werte vorhanden sind.
- Werden die 6 häufigsten Ländernamen benötigt, Formeln in C anpassen: =WENN(ZEILE() > 6; ...
- Sind mehrere Ländernamen gleich oft vorhanden, werden die zuerst angezeigt, die in Spalte A weiter oben stehen (kleinere Zeilennummer) und die anderen danach bzw. gar nicht angezeigt. Um innerhalb von gleich vielen die untersten zuerst anzuzeigen, die Formel der Hilfsspalte ändern: =WENN(ZÄHLENWENN(A$1:A11; A11) = 1; ZÄHLENWENN(A$1:A$100; A11) * ANZAHL2(A$1:A$100) + ZEILE(); "")
Gruß,
CaroS
Antwort 5 von Rami
Hallo CaroS,
danke für deinen Vorschlag, ich dachte hier antwortet keiner mehr.
Leider muss ich sagen, es hat nicht funktioniert, vor allem die Hilfsspalte, die hat immer falsche Zahlen geliefert.
Ideal wäre es für mich, wenn ich eine Funktion wie MODALWERT finde, die auch mit Texte und nicht nur mit Zahlen umgehen kann.
Gruß
Rami
danke für deinen Vorschlag, ich dachte hier antwortet keiner mehr.
Leider muss ich sagen, es hat nicht funktioniert, vor allem die Hilfsspalte, die hat immer falsche Zahlen geliefert.
Ideal wäre es für mich, wenn ich eine Funktion wie MODALWERT finde, die auch mit Texte und nicht nur mit Zahlen umgehen kann.
Gruß
Rami
Antwort 6 von Primut
Hi Rami,
ich würd spontan eine benutzerdefinierte, d.h. vba-technsich selbst geschriebene Funktion vorschlagen, die halt ähnlich wie Modalwert funktioniert, bloß, daß man noch zusätzlich den Rang der Suche bestimmen können müßte.
Habe aber frühestens Dienstag Zeit etwas zu machen, aber vielleicht ist ja ein anderer schneller.
Besten Gruß
ich würd spontan eine benutzerdefinierte, d.h. vba-technsich selbst geschriebene Funktion vorschlagen, die halt ähnlich wie Modalwert funktioniert, bloß, daß man noch zusätzlich den Rang der Suche bestimmen können müßte.
Habe aber frühestens Dienstag Zeit etwas zu machen, aber vielleicht ist ja ein anderer schneller.
Besten Gruß
- Primut
Antwort 7 von nighty
hi all :)
wie gewünscht :))
gruss nighty
Rem gibt den 3 rang an
Rem RangWahl(A2:A12;3)
Rem einzufuegen alt f11/allgemeines modul
Option Explicit
Function RangWahl(zellen As Range, zaehlerindex As Integer) As String
Application.Volatile
ReDim wort(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As String
ReDim wert(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As Integer
Dim rangindex As Integer
Dim zaehler1 As Integer
Dim zaehler As Integer
Dim werte As Integer
Dim rangzuweisung As Integer
Dim auswahl As Integer
Dim wortzuweisung As String
Dim zelle As Range
Dim fest As Boolean
zaehler1 = 0
For Each zelle In zellen
For zaehler = 0 To zaehler1
If wort(zaehler) = zelle Then
wert(zaehler) = wert(zaehler) + 1
fest = 1
Exit For
End If
Next zaehler
If fest = 0 Then
wort(zaehler1) = zelle
wert(zaehler1) = wert(zaehler1) + 1
zaehler1 = zaehler1 + 1
End If
fest = 0
Next zelle
For rangindex = 1 To zaehlerindex
For werte = 0 To zaehler1
If wert(werte) > rangzuweisung Then
rangzuweisung = wert(werte)
wortzuweisung = wort(werte)
auswahl = werte
End If
Next werte
If rangindex < zaehlerindex Then
wert(auswahl) = 0
rangzuweisung = 0
End If
Next rangindex
RangWahl = wortzuweisung
End Function
wie gewünscht :))
gruss nighty
Rem gibt den 3 rang an
Rem RangWahl(A2:A12;3)
Rem einzufuegen alt f11/allgemeines modul
Option Explicit
Function RangWahl(zellen As Range, zaehlerindex As Integer) As String
Application.Volatile
ReDim wort(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As String
ReDim wert(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As Integer
Dim rangindex As Integer
Dim zaehler1 As Integer
Dim zaehler As Integer
Dim werte As Integer
Dim rangzuweisung As Integer
Dim auswahl As Integer
Dim wortzuweisung As String
Dim zelle As Range
Dim fest As Boolean
zaehler1 = 0
For Each zelle In zellen
For zaehler = 0 To zaehler1
If wort(zaehler) = zelle Then
wert(zaehler) = wert(zaehler) + 1
fest = 1
Exit For
End If
Next zaehler
If fest = 0 Then
wort(zaehler1) = zelle
wert(zaehler1) = wert(zaehler1) + 1
zaehler1 = zaehler1 + 1
End If
fest = 0
Next zelle
For rangindex = 1 To zaehlerindex
For werte = 0 To zaehler1
If wert(werte) > rangzuweisung Then
rangzuweisung = wert(werte)
wortzuweisung = wort(werte)
auswahl = werte
End If
Next werte
If rangindex < zaehlerindex Then
wert(auswahl) = 0
rangzuweisung = 0
End If
Next rangindex
RangWahl = wortzuweisung
End Function
Antwort 8 von nighty
hi all :)
noch ein wenig verbessert :)
gruss nighty
Rem gibt den 3 rang an
Rem RangWahl(A2:A12;3;0)
Rem Anzahl RangWahl(A2:A12;3;1)
Rem einzufuegen alt f11/allgemeines modul
Option Explicit
Function RangWahl(zellen As Range, zaehlerindex As Integer, anz As Integer) As String
Application.Volatile
ReDim wort(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As String
ReDim wert(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As Integer
Dim rangindex As Integer
Dim zaehler1 As Integer
Dim zaehler As Integer
Dim werte As Integer
Dim rangzuweisung As Integer
Dim auswahl As Integer
Dim wortzuweisung As String
Dim zelle As Range
Dim fest As Boolean
zaehler1 = 0
For Each zelle In zellen
For zaehler = 0 To zaehler1
If wort(zaehler) = zelle Then
wert(zaehler) = wert(zaehler) + 1
fest = 1
Exit For
End If
Next zaehler
If fest = 0 Then
wort(zaehler1) = zelle
wert(zaehler1) = wert(zaehler1) + 1
zaehler1 = zaehler1 + 1
End If
fest = 0
Next zelle
For rangindex = 1 To zaehlerindex
For werte = 0 To zaehler1
If wert(werte) > rangzuweisung Then
rangzuweisung = wert(werte)
wortzuweisung = wort(werte)
auswahl = werte
End If
Next werte
If rangindex < zaehlerindex Then
wert(auswahl) = 0
rangzuweisung = 0
End If
Next rangindex
If anz = 0 Then RangWahl = wortzuweisung
If anz = 1 Then RangWahl = rangzuweisung
End Function
noch ein wenig verbessert :)
gruss nighty
Rem gibt den 3 rang an
Rem RangWahl(A2:A12;3;0)
Rem Anzahl RangWahl(A2:A12;3;1)
Rem einzufuegen alt f11/allgemeines modul
Option Explicit
Function RangWahl(zellen As Range, zaehlerindex As Integer, anz As Integer) As String
Application.Volatile
ReDim wort(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As String
ReDim wert(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 1) As Integer
Dim rangindex As Integer
Dim zaehler1 As Integer
Dim zaehler As Integer
Dim werte As Integer
Dim rangzuweisung As Integer
Dim auswahl As Integer
Dim wortzuweisung As String
Dim zelle As Range
Dim fest As Boolean
zaehler1 = 0
For Each zelle In zellen
For zaehler = 0 To zaehler1
If wort(zaehler) = zelle Then
wert(zaehler) = wert(zaehler) + 1
fest = 1
Exit For
End If
Next zaehler
If fest = 0 Then
wort(zaehler1) = zelle
wert(zaehler1) = wert(zaehler1) + 1
zaehler1 = zaehler1 + 1
End If
fest = 0
Next zelle
For rangindex = 1 To zaehlerindex
For werte = 0 To zaehler1
If wert(werte) > rangzuweisung Then
rangzuweisung = wert(werte)
wortzuweisung = wort(werte)
auswahl = werte
End If
Next werte
If rangindex < zaehlerindex Then
wert(auswahl) = 0
rangzuweisung = 0
End If
Next rangindex
If anz = 0 Then RangWahl = wortzuweisung
If anz = 1 Then RangWahl = rangzuweisung
End Function
Antwort 9 von nighty
hi all :)
net so perfekt aber geht scho mal :)
gruss nighty
net so perfekt aber geht scho mal :)
gruss nighty
Antwort 10 von Rami
Hi alle,
Ich habe eine bitte an euch: schickt mir bitte nur Excel-Formeln, weil ich mit Formeln viel lieber arbeite und außerdem kenne ich mich mit VB sehr schlecht aus.
Vielen Dank!
Rami
Ich habe eine bitte an euch: schickt mir bitte nur Excel-Formeln, weil ich mit Formeln viel lieber arbeite und außerdem kenne ich mich mit VB sehr schlecht aus.
Vielen Dank!
Rami
Antwort 11 von nighty
hi all :)
das ist eine benutzerdefinierte formel :)
die dein prob loest
gruss nighty
das ist eine benutzerdefinierte formel :)
die dein prob loest
gruss nighty
Antwort 12 von nighty
hi rami :)
nochmal :)
einzufuegen alt f11/allgemeines modul
dann unter funktion einfuegen zur verfügung steht :)
parameter wie folgt :)
RangWahl(A2:A12;3;0)
bedeutet
rang 3
RangWahl(A2:A12;3;1)
bedeutet
rang 3 anzahl
gruss nighty
nochmal :)
einzufuegen alt f11/allgemeines modul
dann unter funktion einfuegen zur verfügung steht :)
parameter wie folgt :)
RangWahl(A2:A12;3;0)
bedeutet
rang 3
RangWahl(A2:A12;3;1)
bedeutet
rang 3 anzahl
gruss nighty
Antwort 13 von CaroS
Hallo Rami,
ich habe mir die Formeln in AW4 noch einmal angesehen und leider erst jetzt einen Fehler gefunden. Die Formel für B1 enthielt mehrmals einen Bezug auf A11, was natürlich Unsinn ist. Da hätte A1 stehen müssen.
=WENN(ZÄHLENWENN(A$1:A11; A11) = 1; ZÄHLENWENN(A$1:A$100; A11) * ANZAHL2(A$1:A$100) - ZEILE(); "")
ändern in
=WENN(ZÄHLENWENN(A$1:A1; A1) = 1; ZÄHLENWENN(A$1:A$100; A1) * ANZAHL2(A$1:A$100) - ZEILE(); "")
Damit funktioniert es dann auch, das habe ich noch einmal überprüft. (Der Fehler könnte bei mir auch einfach nur durch Kopieren aus der falschen Zeile entstanden sein, denn für B11 sieht die Formel ja genau so aus.) Also, das Ganze hier noch einmal:
Spalte A: Ländernamen (A1:A100 oder weniger, leere Zellen zwischendurch möglich)
Spalte B (oder andere): Hilfsspalte - kann nach Eingabe der Formeln verborgen werden Formel B1 (Variante 1): =WENN(ZÄHLENWENN(A$1:A1; A1) = 1; ZÄHLENWENN(A$1:A$100; A1) * ANZAHL2(A$1:A$100) - ZEILE(); "")
Formel B1 (Variante 2):
=WENN(ZÄHLENWENN(A$1:A1; A1) = 1; ZÄHLENWENN(A$1:A$100; A1) * ANZAHL2(A$1:A$100) + ZEILE(); "")
Unterschied zwischen Variante 1 und Variante 2:
Sind mehrere Ländernamen gleich oft vorhanden, werden in Variante 1 die zuerst angezeigt, die in Spalte A am weitesten oben stehen (kleinere Zeilennummer) und in Variante 2 die, die in Spalte A am weitesten unten stehen (größere Zeilennummer). Ländernamen, die deswegen nicht in die Plätze 1 bis 5 passen, werden gar nicht angezeigt.
Formeln ab B2: Zelle B1 so weit wie nötig runterkopieren/-ziehen
bel. Spalte (z. B. C): Ergebnisse
Formel C1: =WENN(ZEILE() > 5; ""; INDIREKT("A" & VERGLEICH(KGRÖSSTE(B$1:B$100; ZEILE()); B$1:B$100; 0)))
(Falls Hilfsspalte nicht B, dann entsprechend anpassen.)
Formeln C2:C5: Zelle C1 bis in 5. Zeile runterkopieren/-ziehen
Die Bereiche A$1:A$100 und B$1:B$100 können größer gewählt werden, als Werte vorhanden sind.
Werden die 6 häufigsten Ländernamen oder mehr benötigt, Formeln in C anpassen: =WENN(ZEILE() > 6; ... oder =WENN(ZEILE() > 7; ...
Gruß,
CaroS
ich habe mir die Formeln in AW4 noch einmal angesehen und leider erst jetzt einen Fehler gefunden. Die Formel für B1 enthielt mehrmals einen Bezug auf A11, was natürlich Unsinn ist. Da hätte A1 stehen müssen.
=WENN(ZÄHLENWENN(A$1:A11; A11) = 1; ZÄHLENWENN(A$1:A$100; A11) * ANZAHL2(A$1:A$100) - ZEILE(); "")
ändern in
=WENN(ZÄHLENWENN(A$1:A1; A1) = 1; ZÄHLENWENN(A$1:A$100; A1) * ANZAHL2(A$1:A$100) - ZEILE(); "")
Damit funktioniert es dann auch, das habe ich noch einmal überprüft. (Der Fehler könnte bei mir auch einfach nur durch Kopieren aus der falschen Zeile entstanden sein, denn für B11 sieht die Formel ja genau so aus.) Also, das Ganze hier noch einmal:
Spalte A: Ländernamen (A1:A100 oder weniger, leere Zellen zwischendurch möglich)
Spalte B (oder andere): Hilfsspalte - kann nach Eingabe der Formeln verborgen werden Formel B1 (Variante 1): =WENN(ZÄHLENWENN(A$1:A1; A1) = 1; ZÄHLENWENN(A$1:A$100; A1) * ANZAHL2(A$1:A$100) - ZEILE(); "")
Formel B1 (Variante 2):
=WENN(ZÄHLENWENN(A$1:A1; A1) = 1; ZÄHLENWENN(A$1:A$100; A1) * ANZAHL2(A$1:A$100) + ZEILE(); "")
Unterschied zwischen Variante 1 und Variante 2:
Sind mehrere Ländernamen gleich oft vorhanden, werden in Variante 1 die zuerst angezeigt, die in Spalte A am weitesten oben stehen (kleinere Zeilennummer) und in Variante 2 die, die in Spalte A am weitesten unten stehen (größere Zeilennummer). Ländernamen, die deswegen nicht in die Plätze 1 bis 5 passen, werden gar nicht angezeigt.
Formeln ab B2: Zelle B1 so weit wie nötig runterkopieren/-ziehen
bel. Spalte (z. B. C): Ergebnisse
Formel C1: =WENN(ZEILE() > 5; ""; INDIREKT("A" & VERGLEICH(KGRÖSSTE(B$1:B$100; ZEILE()); B$1:B$100; 0)))
(Falls Hilfsspalte nicht B, dann entsprechend anpassen.)
Formeln C2:C5: Zelle C1 bis in 5. Zeile runterkopieren/-ziehen
Die Bereiche A$1:A$100 und B$1:B$100 können größer gewählt werden, als Werte vorhanden sind.
Werden die 6 häufigsten Ländernamen oder mehr benötigt, Formeln in C anpassen: =WENN(ZEILE() > 6; ... oder =WENN(ZEILE() > 7; ...
Gruß,
CaroS
Antwort 14 von Rami
Funktioniert einwandfrei. Vielen Dank CaroS!
Rami
Rami
Antwort 15 von Rami
Hallo nighty,
Leider hat es mit der RangWahl nicht funktioniert. Mir fehlen aber auch die Einzelheiten zum Einfügen einer neuen Funktion. So eine Funktion müsste eigentlich standardmäßig drin sein, denn sinnvoll ist sie ja auch.
Gruß
Rami
Leider hat es mit der RangWahl nicht funktioniert. Mir fehlen aber auch die Einzelheiten zum Einfügen einer neuen Funktion. So eine Funktion müsste eigentlich standardmäßig drin sein, denn sinnvoll ist sie ja auch.
Gruß
Rami