Supportnet Computer
Planet of Tech

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:
=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.

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

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

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ß
    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

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

Antwort 9 von nighty

hi all :)

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

Antwort 11 von nighty

hi all :)

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

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

Antwort 14 von Rami

Funktioniert einwandfrei. Vielen Dank CaroS!

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

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: