Microsoft Excel

Excel und VBA: Beitrag aus Herbers Excel-Forumsarchiv

Text sortieren mit Formel

Betrifft: Text sortieren mit Formel
von: ransi
Geschrieben am: 28.07.2005 22:02:24

Hallo und guten Abend

Ich glaube ich sehe den Wald vor lauter Bäumen nicht.
Numerische Werte kann mit mit Kgrösste() bzw. Kkleinste() bequem sortieren.
Wie im Beispiel der bereich a1:a5 in b1:b5.
 
 AB
100
292
354
445
529
6  
7MüllerHinz
8MeierKunz
9SchulzeMeier
10HinzMüller
11KunzSchulze
Formeln der Tabelle
B1 : =KKLEINSTE($A$1:$A$5;ZEILE())
B2 : =KKLEINSTE($A$1:$A$5;ZEILE())
B3 : =KKLEINSTE($A$1:$A$5;ZEILE())
B4 : =KKLEINSTE($A$1:$A$5;ZEILE())
B5 : =KKLEINSTE($A$1:$A$5;ZEILE())
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Nun meine Frage:
Wie schaffe ich es den bereich A7:A11 aufsteigend sortiert mit einer Formel in
b7:b11 darzustellen ?

Danke schon mal an alle die sich damit beschäftigen.
ransi
  


Betrifft: AW: Text sortieren mit Formel
von: Leo
Geschrieben am: 28.07.2005 22:09:42

http://www.excelformeln.de/formeln.html?welcher=236

mfg Leo


  


Betrifft: danke fürs suchen...
von: ransi
Geschrieben am: 28.07.2005 22:13:26

hallo leo

habs auch grade gefunden.

ransi


  


Betrifft: AW: Text sortieren mit Formel
von: FP
Geschrieben am: 28.07.2005 22:24:40

Hallo ransi,

ich würde es ja so machen:

Tabelle1
 ABC
1ab ab
2anton anton
3zeppelin doris
4doris michael
5rainer rainer
6michael zeppelin
7   
8   
9   
10   
Formeln der Tabelle
C1 : {=INDEX(A:A;VERGLEICH(ZEILE(A1)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
C2 : {=INDEX(A:A;VERGLEICH(ZEILE(A2)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
C3 : {=INDEX(A:A;VERGLEICH(ZEILE(A3)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
C4 : {=INDEX(A:A;VERGLEICH(ZEILE(A4)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
C5 : {=INDEX(A:A;VERGLEICH(ZEILE(A5)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
C6 : {=INDEX(A:A;VERGLEICH(ZEILE(A6)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}

Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  


Servus aus dem Salzkammergut
Franz


  


Betrifft: Das ist doch krank :-)
von: Ramses
Geschrieben am: 28.07.2005 22:36:46

Hallo Franz

kannst du die Formel mal erklären :-)

Ich kann schon die Formel auf EXCEL-Formeln nicht nachzuvollziehen, aber hier muss ich echt sagen: Krank :-)

Welchen Knacks in den Hirnwindungen muss man denn haben, um das zu verstehen oder besser zu konstruieren :-)

2 Sachen kriege ich nicht auf die Reihe:

1. Wieso wird als Suchkriterium 0 verwendet
2. Wieso wird ZÀHLENWENN als Such-Matrix akzeptiert

Gruss Rainer


  


Betrifft: AW: Das ist doch krank :-)
von: K.Rola
Geschrieben am: 28.07.2005 22:45:10

Hallo Ramses,

ist doch mein Reden seit dem ersten Mondflug, dass Excelformel krank sind. Das ändert aber nichts daran, dass die Kranken, die auf solche Formeln kommen, ganz nette Typen
sein können(siehe z.B. Boris unf JensF).

Gruß K.Rola


  


Betrifft: AW: Das ist doch krank :-)
von: Ramses
Geschrieben am: 28.07.2005 23:43:07

Hallo K.Rola

Schön mal wieder was von dir zu lesen/hören.
Alles klar bei Dir ?

Klar sind das nette Jungs,....aber ich versteh manches trotzdem nicht, obwohl sich Boris sehr bemüht hat :-)

Gruss Rainer


  


Betrifft: AW: Das ist doch krank :-)
von: K.Rola
Geschrieben am: 29.07.2005 00:05:21

Hallo Ramses,

danke der Nachfrage, alles tutti bei mir. Steige so allmählich hinter die Geheimnisse
meiner software, macht auch unglaublich viel Laune. Daraus resultiert, dass ich kaum
noch Zeit für Excel/VBA habe. Ab und zu lese ich ein wenig in verschiedenen Foren,
quasi zum Entspannen.
Du weißt soviel über Windows/Office/VBA und Excel, da musst du nicht auch noch Formel-
experte sein.

Gruß K.Rola


  


Betrifft: AW: Das ist doch krank :-)
von: Erich G.
Geschrieben am: 29.07.2005 00:35:46

Hallo Rainer,
bin zwar nicht der Franz, kann aber vielleicht trotzdem auf deine Fragen antworten.

1.
Die "0" in der Formel ist nicht das Suchkriterium, sondern der Vergleichstyp.
"0" bewirkt den Vergleich mit "gleich" (nicht kleiner/gleich oder größer/gleich), dabei dürfen die Werte der Suchmatrix in beliebiger Reihenfolge stehen. (Ganz praktisch, denn hier ja erst noch sortiert werden.)

2.
Das Ergebnis von

ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100)

ist ein Array mit Hundert werten, in dem Beispiel beginnend mit 0;1;5;2;4;3; - danach kommen nur noch Nullen. In diesem Array wird (etwa in C6) die ZEILE(A6)-1 gesucht, die fünf wird an der 3. Stelle des Arrays gefunden, damit ist C6=A3.

Ich finds auch kr.., aber nicht krank, eher kryptisch. Aber ist nicht jede Verschlüsselung mehr oder weniger leicht knackbar?

Grüße aus Kamp-Lintfort
Erich


  


Betrifft: AW: Das ist doch krank :-)
von: Ramses
Geschrieben am: 29.07.2005 18:19:01

Hallo Erich

Der Vergleichstyp 0 ist mir schon klar, aber das Suchkriterium "Zeile(A1)-1" ist 0, und damit bin ich nicht klar gekommen.

Vielen Dank für den Typ mit dem Array-Ergebnis, ich hab das hier fürs Archiv nochmal reingebaut

 
 ABCD
1Müller3Hinz 
2Meier2Kunz 
3Schulze4Meier 
4Hinz0Müller 
5Kunz1Schulze 
6 #NV  
Formeln der Tabelle
B1 : {=ZÄHLENWENN($A$1:$A$5;"<"&$A$1:$A$5)}
C1 : {=INDEX(A:A;VERGLEICH(ZEILE(A1)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
B2 : {=ZÄHLENWENN($A$1:$A$5;"<"&$A$1:$A$5)}
C2 : {=INDEX(A:A;VERGLEICH(ZEILE(A2)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
B3 : {=ZÄHLENWENN($A$1:$A$5;"<"&$A$1:$A$5)}
C3 : {=INDEX(A:A;VERGLEICH(ZEILE(A3)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
B4 : {=ZÄHLENWENN($A$1:$A$5;"<"&$A$1:$A$5)}
C4 : {=INDEX(A:A;VERGLEICH(ZEILE(A4)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
B5 : {=ZÄHLENWENN($A$1:$A$5;"<"&$A$1:$A$5)}
C5 : {=INDEX(A:A;VERGLEICH(ZEILE(A5)-1;ZÄHLENWENN(A$1:A$100;"<"&A$1:A$100);0))}
B6 : {=ZÄHLENWENN($A$1:$A$5;"<"&$A$1:$A$5)}
 




Die Formel in B1:B6 funktioniert NUR, wenn man den Bereich B1:B6 zuerst markiert, die Formel OHNE Auflösung der Markierung in B1 einträgt und "Ctrl"+"Shift"+"Enter" abschliesst.

Nun ist mir auch das "Zeile(A1)-1" klar, und das ganze ist nun auch nicht mehr so schwer zu verstehen,.... aber wie jemand darauf kommt, ist mir schleierhaft :-)

Das mit "krank" ist übrigens nicht negativ gemeint, sondern eher ausgesprochen positiv.

Vielen Dank für deine Erklärung.

Gruss Rainer


  


Betrifft: AW: Text sortieren mit Formel
von: ransi
Geschrieben am: 29.07.2005 16:38:30

Hallo

Prima vorschlag vom Franz.
Klappt aber nur wenn keine zahlen und keine leerzellen im bereich sind.
denke aber das kriege ich noch eingebaut.

besten dank nochmal
ransi


  


Betrifft: AW: Text sortieren mit Formel
von: Rolf Beißner
Geschrieben am: 30.07.2005 09:26:41

Hallo ransi,

warum komplizierte Formeln, statt ein paar simpler Codezeilen?
Gibt's da einen tieferen Sinn oder ist das eher Hobby?????

Herzliche Grüße
Rolf