Microsoft Excel

Herbers Excel/VBA-Archiv

Häufigkeiten ermitteln

Betrifft: Häufigkeiten ermitteln von: erichm
Geschrieben am: 02.10.2014 07:06:20

Hallo,

ich möchte aus einer Reihe von Zahlen immer deren Häufigkeit ermitteln, da einige Zahlen immer gleich oft vorkommen, soll bei gleicher Anzahl immer die größere Zahl ermittelt werden. In dem Beispiel habe ich die Zahlenreihen in den Zeilen 1 bis 10 zur besseren Übersichtlichkeit "vorsortiert" in Wirklichkeit ist das alles unsortiert:

Tabelle1

 ABC
1 24
2 24
3 24
4 24
5 22
6 12
7 11
8 11
9 00
10 00
11   
12am häufigsten24
13am zweithäufigsten12
14am dritthäufigsten01


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

DANKE!
mfg

  

Betrifft: AW: Häufigkeiten ermitteln von: Hajo_Zi
Geschrieben am: 02.10.2014 07:41:23

warum ist in Spalte C die am dritthäufigsten 1 es gibt nur 4* und 2*

GrußformelHomepage


  

Betrifft: AW: Häufigkeiten ermitteln von: erichm
Geschrieben am: 02.10.2014 08:12:33

hallO Hajo,

vielleicht hat mich da ungenau ausgedrückt; wenn zwei oder drei Zahlen gleichoft vorkommen, dann soll quasi immer die größere Zahl als "nächste Häufigkeit" erkannt werden. In dem Beispiel also kommen in Spalte C die 2 / 1 / 0 je zweimal vor (=zweithäufigst); jetzt soll daraus
die 1 als "dritthäufigst" und
die 0 als "vierthäufigst" erkannt werden.

Danke.
mfg


  

Betrifft: mit einer komplexen MATRIXformel ... von: neopa C (paneo)
Geschrieben am: 02.10.2014 09:22:21

Hallo Erich,

... einfacher wäre sicherlich eine Hilfsspaltenlösung. Aber die Lösung ohne solche hat mich nun mal gereizt.

Entstanden ist im ersten Wurf eine kleine Monsterformel, die aber nach meinen ersten groben Testungen alle Deine Bedingungen erfüllt. Doch momentan noch vorausgesetzt, dass keine leeren Zellen im auszuwertenden Bereich vorhanden sind. (wäre auch noch einbaubar).

Die Formel ist auch nur zusammengebaut und noch nicht optimiert und ist nach unten und rechts zu kopieren.


 ABC
1 14
2 24
3 04
4 15
5 24
6 15
7 25
8 14
9 05
10 25
11   
12am häufigsten25
13am zweithäufigsten14
14am dritthäufigsten0 

Formeln der Tabelle
ZelleFormel
B12{=WENN(ZEILE(A1)>SUMME(N(HÄUFIGKEIT(B$1:B$10;B$1:B$10)>0)); "";INDEX(B$1:B$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B11;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10)); HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B11;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10); )))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...


  

Betrifft: AW: mit einer komplexen MATRIXformel ... von: erichm
Geschrieben am: 03.10.2014 06:47:12

Hallo Werner,

DANKE! Es ist sichergestellt, dass in den Zahlenreihen keine Leerzellen bestehen.

Die Formel passt noch nicht ganz, wie an dem Beispiel ersichtlich:

Tabelle1

 AB
1 4
2 4
3 4
4 4
5 2
6 2
7 3
8 1
9 0
10 0
11  
12am häufigsten4
13am zweithäufigsten3
14am dritthäufigsten2

Formeln der Tabelle
ZelleFormel
B12{=WENN(ZEILE(A1)>SUMME(N(HÄUFIGKEIT(B$1:B$10;B$1:B$10)>0)); "";INDEX(B$1:B$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B11;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10)); HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B11;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10); )))}
B13{=WENN(ZEILE(A2)>SUMME(N(HÄUFIGKEIT(B$1:B$10;B$1:B$10)>0)); "";INDEX(B$1:B$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B12;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10)); HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B12;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10); )))}
B14{=WENN(ZEILE(A3)>SUMME(N(HÄUFIGKEIT(B$1:B$10;B$1:B$10)>0)); "";INDEX(B$1:B$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B13;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10)); HÄUFIGKEIT(WENN(MAX(ISTNV(VERGLEICH(B$1:B$10;B$11:B13;))*B$1:B$10)=B$1:B$10;B$1:B$10); B$1:B$10); )))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Ich habe keine Chance die Formel so zu optimieren. Ggfls. könnte ich aber auch mit einer Hilfsspalte leben. Danke nochmal.

mfg


  

Betrifft: eine Zwischenlösung ... von: neopa C (paneo)
Geschrieben am: 03.10.2014 08:27:25

Hallo Erich,

... da unterlag ich gestern einen Trugschluss.
Dieses (falsche) Ergebnis hätte ich auch mit einer kürzeren Formel erzielen können ;-)

Jetzt nur auf die Schnelle, wenn Du sicherstellen könntest, dass Deine Daten zuvor immer abwärts sortiert sind (dazu sind nur drei Mausklicks notwendig), dann sollte mit folgender verkürzter Matrixformel Dein restliches Ziel schon realisierbar sein (siehe Spalte C).
Teste bitte mal.
Ich schau dann später (evtl. erst die nächsten Tage) noch einmal, wie es auch ohne vorherige Sortierung mit nur einer Formel gehen könnte.

 BC
104
214
343
422
522
622
722
831
940
1000
11  
1222
1304
1440
1513
1631

Formeln der Tabelle
ZelleFormel
B12{=WENN(ZEILE(A1)>SUMME(N(HÄUFIGKEIT(B$1:B$10;B$1:B$10)>0)); "";INDEX(B$1:B$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(B$1:B$10;B$11:B11;)); B$1:B$10); B$1:B$10)); HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(B$1:B$10;B$11:B11;)); B$1:B$10); B$1:B$10); )))}
C12{=WENN(ZEILE(C1)>SUMME(N(HÄUFIGKEIT(C$1:C$10;C$1:C$10)>0)); "";INDEX(C$1:C$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(C$1:C$10;C$11:C11;)); C$1:C$10); C$1:C$10)); HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(C$1:C$10;C$11:C11;)); C$1:C$10); C$1:C$10); )))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...


  

Betrifft: AW: eine Zwischenlösung ... von: erichm
Geschrieben am: 03.10.2014 08:36:47

Danke für die schnelle Rückmeldung; das mit dem abwärts sortieren lässt sich innerhalb des gesamten EXCEL-Tools leider nicht umsetzen.

mfg


  

Betrifft: dann auf die Schnelle mit einer Hilfsspalte .... von: neopa C (paneo)
Geschrieben am: 03.10.2014 09:04:14

Hallo Erich,

... Formel C1 und B12 nach unten kopieren:

 ABC
1 04
2 14
3 43
4 22
5 22
6 12
7 21
8 31
9 40
10 00
11   
12am häufigsten2 
13am zweithäufigsten4 
14am dritthäufigsten1 
15 0 
16 3 

Formeln der Tabelle
ZelleFormel
C1=KGRÖSSTE(B$1:B$10;ZEILE(A1))
B12{=WENN(ZEILE(C1)>SUMME(N(HÄUFIGKEIT(C$1:C$10;C$1:C$10)>0)); "";INDEX(C$1:C$10;VERGLEICH(MAX(HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(C$1:C$10;B$11:B11;)); C$1:C$10); C$1:C$10)); HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(C$1:C$10;B$11:B11;)); C$1:C$10); C$1:C$10); )))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: dann auf die Schnelle mit einer Hilfsspalte .... von: erichm
Geschrieben am: 03.10.2014 09:08:23

OK, Danke; so könnte das klappen - ich teste das und melde mich dann sofort!

mfg


  

Betrifft: DANKE - das klappt wunderbar; PERFEKT von: erichm
Geschrieben am: 03.10.2014 09:22:49

......


  

Betrifft: der Vollständigkeit halber ...ohne Hilfsspalte.... von: neopa C (paneo)
Geschrieben am: 03.10.2014 19:37:06

Hallo Erich,

... auf vorliegender Basis, lässt sich natürlich auch eine hilfsspaltenfreie Lösungsformel generieren und damit auch gleichzeitig evtl. Leerzellen und Textwerte korrekt berücksichtigen. Siehe Formel in B101. Da diese unüberschaubar lang ist, empfiehlt sich der Einsatz eines Bereichsnamens. Dazu siehe Formel C101. Wichtig ist lediglich dass die Zelle vor der ersten Formel leer ist bzw. ="":

 ABC
1Zahlen Anzahl auswerten; bei gleicher Anz.: zuerst höchster Wert
2
3 00
4 44
5 22
6 22
7 11
8 22
9   
10 33
11 44
12 00
13   
14   
15   
16   
97 22
98 11
99 00
100   
101am häufigsten22
102am 2.häufigsten00
103usw.44
104 11
105 33
106   

Formeln der Tabelle
ZelleFormel
B101{=WENN(ZEILE(A1)>SUMME(N(HÄUFIGKEIT(B$1:B$99;B$1:B$99)>0)); "";INDEX(KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99)))); VERGLEICH(MAX(HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99)))); B$100:B100;)); KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99))))); KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99)))))); HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99)))); B$100:B100;)); KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99))))); KGRÖSSTE(B$1:B$99;ZEILE(A$1:INDEX(B:B;ANZAHL(B$1:B$99))))); )))}
C101{=WENN(ZEILE(C1)>SUMME(N(HÄUFIGKEIT(ZW_S;ZW_S)>0)); "";INDEX(ZW_S;VERGLEICH(MAX(HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(ZW_S;C$100:C100;))*(ZW_S<>""); ZW_S); ZW_S)); HÄUFIGKEIT(WENN(ISTNV(VERGLEICH(ZW_S;C$100:C100;))*(ZW_S<>""); ZW_S); ZW_S); )))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
C101ZW_S=KGRÖSSTE(Tabelle1!C$1:C$99;ZEILE(Tabelle1!C$1:INDEX(Tabelle1!C:C;ANZAHL(Tabelle1!C$1:C$99))))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...


  

Betrifft: AW: der Vollständigkeit halber ...ohne Hilfsspalte.... von: erichm
Geschrieben am: 05.10.2014 12:27:10

DANKE!!
Die Formel mit dem Namen ist natürlich optimal!!
(hatte leider erst heute wieder Zeit)

mfg


 

Beiträge aus den Excel-Beispielen zum Thema "Häufigkeiten ermitteln"