Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1440to1444
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1440to1444
1440to1444
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

nach Häufigkeit absteigend auflisten

nach Häufigkeit absteigend auflisten
11.08.2015 16:07:33
Jenny
Hallo an alle,
bitte helft mir, ich habe eine Liste die folgendermaßen aussieht, Zellformat ist Text damit die führenden Nullen nicht gelöscht werden.
0007
0706
0609
0002
0005
0710
0905
0616
1311
1110
1216
1221
1313
0807
0410
0904
0606
9906
usw.
die zahlen bedeuten soviel, die ersten beiden Stellen ist das Baujahr die letzten beiden Stellen eine laufende Nummer.
Gibt es eine Formel, die es schafft diese Liste folgendermaßen umzusortieren.
1. Kriterium Häufigkeit wie oft ein Modell in dieser Liste steht, absteigend.
2. Kriterium, für den Fall dass 2 oder mehrere Modelle gleich häufig in der Liste stehen Baujahr absteigend, wobei natürlich das 99er Baujahr am Schluss stehen soll, noch ältere Modelle gibt es nicht.
Jede Modellnummer soll natürlich nur noch dann einmal in der Liste auftauchen.
Hat da jemand eine Idee ob sich das umsetzen lässt?
LG
Jenny

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt, nur einmalige Angelegenheit? ...
11.08.2015 16:36:25
...
Hallo Jenny,
... dann würde ich es in einer Kombination von PIVOTauswertung, Datenübernahme und =ZÄHLENWENN() sowie abschließender Sortierung lösen.
Für eine oft zu wiederholende oder dynamische Lösung, sollte man noch wissen, wie viele Datensätze denn max. auszuwerten sind.
Gruß Werner
.. , - ...

unpassendes Beispiel
11.08.2015 17:21:03
WF
Hi,
in obiger Liste sind nur Solisten - also Häufigkeit 1.
Dann brauchst Du ja nur eine Sortierformel ?
WF

AW: war mir zuerst auch aufgefallen, allerdings...
11.08.2015 17:57:15
...
Hallo WF,
... steht ja unter der Liste noch "usw.", wo die angekündigten Wiederholungen ja noch kommen könnten.
Bevor ich mir jedoch weitere Gedanken zu einer Formellösung machen wollte, habe ich erst einmal weitere Fragen an Jenny gerichtet. Falls Jenny noch heute antwortet, würde ich das nun erst Morgen tun können,
da ich gleich offline gehen werde.
Gruß Werner
.. , - ...

Anzeige
AW: war mir zuerst auch aufgefallen, allerdings...
11.08.2015 19:51:51
Jenny
Hallo ihr beiden,
danke erstmal für eure Mühe.
das ist die derzeitige Tabelle, aus mehr besteht sie noch nicht. Aber es werden in Zukunft auch Mehrfacheinträge kommen, daher das usw.
Zu der Frage der Größenordnung, es werden ähnlich viele Zeilen über dem Zeitraum von einem Jahr 5x die Woche zusammenkommen, also ca. 4500, viellleicht auch 5000.
Zu dem was mir so vorschwebt,
stellt euch vor, ich gehe hin, filtere die Tabelle erstmal dass Duplikate ausgeblendet werden, damit ich eine Liste der Modelle hab, diese Liste in der dann jedes Modell nur noch einmal steht kopiere ich dann in ein neues Blatt und zähle dann mit =ZÄHLENWENN(Tabelle1:A:A;A1) wie oft das jeweilige Modell in der Liste steht und sortiere dann wie gewünscht.
Nur das müsste ich dann jeden Tag machen nachdem die Liste erweitert wurde.
Daher suche ich eine Formel, die das automatisch macht, dass ich nur noch die neuen Modelle eintragen muss.
Das Problem ist nur, ich hab keine vollständige Liste der Modelle auf die die Formel zugreifen kann, ich hab nur die Liste die ich hier angefangen habe.
VG
Jenny

Anzeige
AW: war mir zuerst auch aufgefallen, allerdings...
11.08.2015 19:55:46
Jenny
ach so, mit einer Hilfsspalte zum zählen hab ich keine Probleme, die hätt ich mir sowieso gemacht

die Häufigkeitsrangliste ist ...
11.08.2015 20:11:04
WF
Hi,
... folgende Arrayformel (Daten in Spalte A ) beginnend in B2 (B1 ist leer):
{=WENN(ANZAHL2(A:A)=SUMME(ZÄHLENWENN(A$1:A$9999;B$1:B1));ZEICHEN(160);INDEX(A:A; VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$9999;A$1:A$9999);1+SUMME(ZÄHLENWENN(A$1:A$9999;B$1:B1))); WENN(ZÄHLENWENN(B$1:B1;A$1:A$9999)=0;ZÄHLENWENN(A$1:A$9999;A$1:A$9999));0))) }
runterkopieren
Sortieren willst Du ja selber.
Salut WF
p.s.: bei 9.999 Zeilen dauert das etwas - hängt von der power Deines Rechners ab.

Anzeige
AW: die Häufigkeitsrangliste ist ...
11.08.2015 20:15:59
Jenny
Hallo WF,
das funktioniert. Dankeschön
zu meinem Laptop, I7 4500, 8 GB RAM, Windows 10
hoffe das reicht für so ne Formel.
LG
Jenny

AW: die Häufigkeitsrangliste ist ...
11.08.2015 20:43:22
Jenny
Hallo WF,
es funktioniert leider doch nicht zu 100%
er tut die zwar den 99er Jahrgang beim Sortieren an den Schluss aber den 00er Jahrgang an den Anfang.
LG
Jenny

AW: die Häufigkeitsrangliste ist ...
11.08.2015 20:48:37
Jenny
obwohl ich hab Mist geschrieben, er sortiert gar nicht,
du hattest wohl was missverstanden, ich hatte gesagt dass mir eine Hilffspalte die zählt nichts ausmacht, da ich die sowieso erstellt hätte aber davon dass die Formel nicht auch nach dem zweiten Kriterium sortieren soll hatte ich nichts gesagt.
LG
Jenny

Anzeige
Deine Sortierformel kenne ich nicht
11.08.2015 20:57:18
WF
ich hab Dir nur die Rangfolge der Häufigkeiten geliefert.
Sortieren wolltest Du selber.
WF

AW: Deine Sortierformel kenne ich nicht
11.08.2015 21:13:37
Jenny
Hallo WF,
das war meine Bitte im Eröffnungsbeitrag, der erste Punkt ist abgehakt
Sortierkriterien:
1. Kriterium Häufigkeit wie oft ein Modell in dieser Liste steht, absteigend.
2. Kriterium, für den Fall dass 2 oder mehrere Modelle gleich häufig in der Liste stehen Baujahr absteigend, wobei natürlich das 99er Baujahr am Schluss stehen soll, noch ältere Modelle gibt es nicht.
LG
Jenny

ganz anders, einfacher, ohne Arrayformeln
12.08.2015 06:10:39
WF
Moin Jenny,
Deine 4-stelligen Textzahlen stehen in Spalte A beginnend mit A2.
In B2 (Hilfsspalte) steht:
=WENN(ODER(A2="";ZÄHLENWENN(A$1:A1;A2)>0);0;ZÄHLENWENN(A:A;A2)*1000+WENN(LINKS(A2;2)="99";-1; LINKS(A2;2)*10)+RECHTS(A2;2)/100)
runterkopieren
Das Ergebnis dann in irgendeiner Zelle:
=WENN(ZEILE(A1)>ZÄHLENWENN(B:B;">0");"";INDEX(A:A;VERGLEICH(KGRÖSSTE(B:B;ZEILE(A1));B:B;0)))
runterkopieren
Salut WF

Anzeige
AW: ganz anders, einfacher, ohne Arrayformeln
12.08.2015 07:52:24
Jenny
Hallo WF,
super danke für deine Hilfe, das funktioniert.
LG
Jenny

AW: für mich sind da jedoch Widersprüche ...
12.08.2015 09:19:37
...
Hallo Jenny,
... Deine Aussagen zur Sortierung und zu den Ergebnissen mit den von WF vorgeschlagen Formeln sind im Laufe des threads aus meiner Sicht widersprüchlich.
Vor allem Deine Aussagen zur Sortierung. Wenn die Daten beginnend mit 99 als letztes einsortiert werden sollen, ist die Sortierung zumindest für mich auf- und nicht absteigend. Oder?
Wie sind denn Daten beginnend mit z.B. 95 oder 91 einzuordnen? Welche Logik steht dahinter, dass diese wieder nach dem "normalen" Sortiermodus eingeordnet werden sollen, so wie Du es bezogen auf die Hilfsspaltenformellösung von WF jetzt als richtig angesehen hast?
Ich hab hier mal für (m)ein Beispiel die bisherigen Lösungsformeln meiner Interpretation (in E2) für aufsteigende Sortierung (bei gleicher Häufigkeit der Daten) gegenübergestellt.
In Spalte F hab ich eine analoge Formel für eine grundsätzlich absteigende Sortierung vorgestellt.
Natürlich könnte ich auch eine Ausnahme mit in der Formel berücksichtigen. Aber dazu würde ich schon gern vorher "Klarheit" haben, was genau die Ausnahme ist und gerne auch warum das so sein soll.
 ABCDEF
1 1.HS2.N1N2
2000706091000122106091221
3070612211070060912210609
4060913114060910713119107
5000213131000131313131313
6000591071000131191071311
7071000071070930500029906
8090500071090910600059305
9061600071060910500079106
10131107062130121604109105
11111007061110111006061216
12121607061120090506161110
13122107064120090407060905
14131307102130080707100904
15080709051080071008070807
16041009051040070609040710
17090409051090061609050706
18060611101060060611100616
1999061216999,1041012160606
20060999060000791050410
21131399060000591060007
22122199060000293050005
23060999060990699060002
240609 0   
251221#ZAHL!0   
261221#ZAHL!0   
271311#ZAHL!0   
289305#ZAHL!1930   
299107#ZAHL!2910   
309106#ZAHL!1910   
319107#ZAHL!0   
329105#ZAHL!1910   
33      

Formeln der Tabelle
ZelleFormel
B2{=WENN(ANZAHL2(A:A)=SUMME(ZÄHLENWENN(A$1:A$9999;E$1:E1)); ZEICHEN(160); INDEX(A:A; VERGLEICH(KGRÖSSTE(ZÄHLENWENN(A$1:A$9999;A$1:A$9999); 1+SUMME(ZÄHLENWENN(A$1:A$9999;E$1:E1))); WENN(ZÄHLENWENN(E$1:E1;A$1:A$9999)=0;ZÄHLENWENN(A$1:A$9999;A$1:A$9999)); 0)))}
C2=WENN(ODER(A2="";ZÄHLENWENN(A$1:A1;A2)>0); 0;ZÄHLENWENN(A:A;A2)*1000+WENN(LINKS(A2;2)="99";-1; LINKS(A2;2)*10)+RECHTS(A2;2)/100)
D2=WENN(ZEILE(A1)>ZÄHLENWENN(C:C;">0"); "";INDEX(A:A;VERGLEICH(KGRÖSSTE(C:C;ZEILE(A1)); C:C;0)))
E2=WENNFEHLER(TEXT(REST(AGGREGAT(15;6;(KÜRZEN(10^9/ZÄHLENWENN(A$1:A$9999;A$1:A$9999); )+A$1:A$9999%%)/(VERGLEICH(A$1:A$9999;A$1:A$9999;)=ZEILE(A$1:A$9999)); ZEILE(A1)); 1)*10000;"0000"); "")
F2=WENNFEHLER(TEXT(REST(AGGREGAT(14;6;(ZÄHLENWENN(A$1:A$9999;A$1:A$9999)+A$1:A$9999%%)/(VERGLEICH(A$1:A$9999;A$1:A$9999;)=ZEILE(A$1:A$9999)); ZEILE(A1)); 1)*10000;"0000"); "")
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
.. , - ...

Anzeige
lesen hilft (oben 20:57 Uhr
12.08.2015 09:37:08
WF
Zitat
"2. Kriterium, für den Fall dass 2 oder mehrere Modelle gleich häufig in der Liste stehen Baujahr absteigend, wobei natürlich das 99er Baujahr am Schluss stehen soll, noch ältere Modelle gibt es nicht."
WF

AW: das hatte ich wirklich überlesen ...
12.08.2015 10:24:01
...
Hallo WF,
... dann ergänze ich meine AGGREGAT()-Formel noch mit WECHSELN().
Deine Hilfsspaltenlösung ergibt nun die gleichen Werte wie meine entsprechend angepasste Matrixfunktion(alitäts)formel in G2. Die hab ich jetzt aber nur der Vollständigkeit halber noch eingestellt. Diese ist zwar schneller als eine echte MATRIXformel aber natürlich langsamer als Deine Hilfsspaltenformel auch wenn meine Formel noch etwas optimiert werden würde.
 ACDG
1 HS2.N3
20007100012211221
30706107006090609
40609406013131313
50002100013111311
60005100012161216
70710107011101110
80905109009050905
90616106009040904
101311213008070807
111110111007100710
121216112007060706
131221412006160616
141313213006060606
150807108004100410
160410104000070007
170904109000050005
180606106000020002
199906999,199079907
200609099069906
211313099059905
2212210  
2306090  
2406090  
2512210  
2612210  
2713110  
289905999,1  
299907999,1  
30    

Formeln der Tabelle
ZelleFormel
C2=WENN(ODER(A2="";ZÄHLENWENN(A$1:A1;A2)>0); 0;ZÄHLENWENN(A:A;A2)*1000+WENN(LINKS(A2;2)="99";-1; LINKS(A2;2)*10)+RECHTS(A2;2)/100)
D2=WENN(ZEILE(A1)>ZÄHLENWENN(C:C;">0"); "";INDEX(A:A;VERGLEICH(KGRÖSSTE(C:C;ZEILE(A1)); C:C;0)))
G2=WENNFEHLER(RECHTS(TEXT(REST(AGGREGAT(14;6;(WECHSELN(ZÄHLENWENN(A$1:A$5000;A$1:A$5000)+0,2+A$1:A$5000%%%;2099;1999))/(VERGLEICH(A$1:A$5000;A$1:A$5000;)=ZEILE(A$1:A$5000)); ZEILE(A1)); 1)*1000000;"000000"); 4); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: das hatte ich wirklich überlesen ...
12.08.2015 14:00:39
Jenny
sorry ihr beiden,
jetzt blicke ich gar nicht mehr durch, damit schon angefangen was überhaupt an WF's Vorschlag von gestern falsch sein sollte.
LG
Jenny

AW: richtig sind die Formel von Heute ...
12.08.2015 14:16:13
Heute
Hallo Jenny,
... und zwar die Hilfsspaltenformellösung von WF und meine hilfsspaltenfreie Lösung (als N3 gekennzeichnet) ergibt zwar die gleichen Ergebnisse beansprucht aber mehr Rechenpower.
Nutze also die Hilfsspaltenformellösung von WF wenn Du sehr viele Datensätze auszuwerten hast
Gruß Werner
.. , - ...

AW: für mich sind da jedoch Widersprüche ...
12.08.2015 14:07:43
Jenny
erstmal zur Aufklärung bei gleicher Häufigkeit soll die Sortierung von neu nach alt sein, also das 14er Baujahr als erstes, das 99er Baujahr als letztes.
somit 14, 13, ...., 00, 99
Aber darf ich mal einen anderen Vorschlag zur Lösung des Problems machen,
1. man kann doch sicher auch das Zellformat so ändern, dass auch im Format Zahl die führenden Nullen stehen bleiben oder?
2. dazu eine Formel die die Duplikate löscht
3. zu den Ergebnissen eine Hilfsspalte die 10000 zu allen Zahlen kleiner 9901 addiert um die gewünschte Sortierung zu erreichen
4. eine Formel mit Zählenwenn
5. ein Mechanismus der automatisch sortiert, sobald etwas in der Ursprungsspalte eingegeben wird, z.B. ein kleines Makro das das sortieren auslöst und zwar erstes Kriterium die Spalte mit Zählenwenn, 2. Kriterium die Spalte in der 10000 addiert wird.
So hätt ich es zumindest als Laiin gemacht.
LG
Jenny

Anzeige
AW: für mich sind da jedoch Widersprüche ...
12.08.2015 14:15:00
Jenny
https://www.herber.de/bbs/user/99509.xlsx
habe jetzt heute nochmal Daten hinzugefügt, jetzt gibts auch einen Doppelten (1311 in A10 und A29)
für mich funktionieren die Formeln in C und D einwandfrei, das wird genauso sortiert wie ich es wollte.
Gruß
Jenny

AW: siehe mein Beitrag von eben owT ...
12.08.2015 14:18:25
eben
Gruß Werner
.. , - ...

Du solltest das von neopa ignorieren.
12.08.2015 14:19:44
neopa
.

AW: lesen würde aber auch hier helfen ...
12.08.2015 14:27:58
...
Hallo WF,
... ich hab vor Dir zwei Beiträge an Jenny geschrieben, wo ich ihr geschrieben habe, dass die Formeln von Heute (auch meine) richtig sind (Deine Matrixformel von gestern war falsch) und ich ihr zu Deiner Hilfsspaltenformel geraten habe. Mit Deinem hiesigen Betreff würdest Du das negieren.
Gruß Werner
.. , - ...

Anzeige
was willst Du ?
12.08.2015 14:44:44
WF
Es gibt eine Lösung.
Es wird sich bedankt.
Jetzt kommst Du - warum ?

AW: was willst Du ?
12.08.2015 14:48:51
Jenny
ich verstehs auch nicht so ganz,
also wenn ichs richtig sehe gehen beide Formeln, aber WF's Formel also die in meiner Beispieldatei benötigt weniger Rechenzeit?
dann hab ich doch das was ich will
LG
Jenny

AW: ich schrieb ich Dir ja auch ...
12.08.2015 15:20:24
...
Hallo Jenny,
... dass die hilfsspaltenfreie Lösungsformel von WF weniger Rechner-Ressourcen (bei vielen Daten) benötigt und wiederholte dies zuletzt nochmal.
Zur Erklärung noch:
Heute früh glaubte ich noch Widersprüche in Deiner Aufgabenstellung festgestellt zu haben. Ich hatte jedoch Deine Aussage: "...noch ältere Modelle gibt es nicht" überlesen, worauf mich WF hinwies. Ich hatte danach meinen diesbzgl. Versehen eingeräumt. Im gleichen Beitrag hatte ich auch eine alternative Formellösungsmöglichkeit vorgestellt. Doch schon da hatte ich zum Ausdruck gebracht, dass diese ".. aber nur der Vollständigkeit halber noch eingestellt" habe und auf den Auswertungsgeschwindigkeitsnachteil hingewiesen.
Ich hoffe nun sind die bisherigen noch verbliebenen Unklarheiten bereinigt.
Gruß Werner
.. , - ...

AW: ich schrieb ich Dir ja auch ...
12.08.2015 15:54:21
Jenny
Hallo Werner,
ja das sind sie jetzt, ich war selbst total verwirrt und wusste nicht merh wirklich was jetzt Stand der Dinge ist, ich wusste nur das ich eine funktionierende Lösung hab.
Daher hab ich selbst offensichtlich Dinge geschrieben die unpassend waren.
Aber danke euch beiden für Eure Hilfe.
LG
Jenny

AW: freut mich nun auch, Danke! owT
12.08.2015 16:00:33
...
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige