Sortieren von Zellen einer Spalte in Excel ohne die Berücksichtigung von Lücken

Hallo

ich habe folgende Probleme (ich kenne mich mit excel leider nur wenig aus)

Problem 1

In Spalte A stehen unterschiedliche Zahlen,von Zelle A2 - Zelle A502. Es sollen jetzt in Spalte D alle Zellen aus Spalte A untereinander aufgelistet werden, die einen Wert enthalten. alle Leerzellen in Spalte A sollen ignoriert werden.

Problem 2

jetzt sollen alle Zellen von Spalte D in die Spalte E (von Groß nach klein sortiert werden)

Problem 3 Wenn die Zahlen sortiert in Spalte E geschrieben sind, dann wird es noch 9 andere Spalten wie Spalte E geben, (aber mit anderen Zahlen). Aus diesen insgesamt 10 Spalten sollen die aktuell 5 höchsten Werte in Spalte M Zelle M1 - M5 eingetragen werden.

Hinweis. In Spalte A kommen immer neue Zahlen hinzu, deswegen ändern sich die 5 höchsten Werte ständig, und alle anderen Spalte ändern sich auch ständig. Dies bitte Berücksichtigen.

Es wäre gut wenn ich für jedes Problem eine extra formel hätte.

Vielen Dank

Answer 1

[DeeDee07]

Ich überspringe mal Problem 1 und komm direkt von A nach E

In E2 schreibst du die Formel rein:

=KGRÖSSTE(A:A;ZEILE(A1)

Diese Formel kopierst du nach unten. Falls du weiter nach unten kopierst als aktuell nötig, weil du ja noch weitere Zahlen in Spalte A erwartest, wird der Fehler #ZAHL! erscheinen. Falls dich das stört, kannst du auch folgendes verwenden:

=WENN(ISTFEHLER(KGRÖSSTE(A:A;ZEILE(A1)));"";KGRÖSSTE(A:A;ZEILE(A1)))

In M1 trägst du dann ein:

=KGRÖSSTE(E:L;ZEILE(A1)

und kopierst sie bis M5 runter.

Comments

[Ortogonn]

Nachtrag/Hinweis ab Office 2007:

Funktion WENNFEHLER() statt WENN() und ISTFEHLER() verkürzt die Formel

=WENNFEHLER(KGRÖSSTE(A:A;ZEILE(A1);"")

Answer 2

[Ortogonn]

Da auf alle anderen Probleme bereits geantwortet wurde, hier nur der Vollständigkeit halber eine Antwort auf Problem 1

http://www.gutefrage.net/frage/excel-funktion-die-2-spalten-aus-2-unterschiedlichen-tabellen-vergleicht-und-werte-ausgibt#answer104704888

Nutze die letzte Formel in meiner verlinkten Antwort, nur angepasst von Spalte G auf deine Spalte A

Answer 3

[Galilei]

Hi Dani!

Ich fang mal hinten an:

Problem 3: Das funktioniert ganz einfach mit der Formel KGRÖSSTE. Nur reichen deine 10 Spalten bereits bis zum Buchstaben N, so dass die Spalte M schon belegt ist. In O1 kannst du aber eingeben:

=KGRÖSSTE($E$2:$N$502;1)

In O2 käme dann der zweitgrößte Wert der Matrix:

=KGRÖSSTE($E$2:$N$502;2)

Und so weiter.

Problem 2: Eine Sortierung per Formel ist ganz schön kompliert. Eine eigene Formel dafür gibt es nicht, daher muss man sich was zusammenbauen, was schon einige Leute gemacht haben. Wo ich allerdings gerad schwarz sehe, ich Problem 1. Das überspringe ich in dem Ganzen hier, weil ich dafür in der Tat keine Lösung weiß so spontan. Daher benutze ich Spalte D jetzt als Hilfsspalte. In D1 eintragen:

=SUMMENPRODUKT((A$2:A$502 < A1)+(A1="")*10)

Die Formel dann bis D502 herunterziehen. Die Formel ordnet jedem Wert in Spalte A seine "Position" in der Sortierung zu. Das durch einen kleinen Trick: Es wird gezählt, wie viele Werte kleiner sind. Das (A1="")*10 sortiert die Leerzellen aus, indem es für die Leerzellen immenshohe Werte produziert. (In dem Fall dürfte das 5010 sein. Da die Liste nur 501 Zeilen umspannt, werden Leerzellen also immer als Extremwerte gezählt.)

So, und jetzt kommt der eigentliche Formelbau für E1:

=INDEX(A:A;VERGLEICH(KKLEINSTE(D$2:D$502;ZEILE());D$2:D$502;0))

Von innen nach außen: KKLEINSTE gibt hier den x-kleinsten Wert unserer Spalte D aus. Den wievielt-kleinsten bestimmt das ZEILE(), das als Zahl die aktuelle Zeile ausgibt. In E1 (also 1. Zeile) wird der allerkleinste Wert aus Spalte D gesucht. In E2 der zweitkleinste Wert. Und so weiter. Zur Erinnerung: In Spalte D steht jeweils, wie viele kleinere Werte der jeweile Wert in Spalte A über und unter sich hat.

VERGLEICH sucht nun nach jenem x-kleinsten Wert von Spalte D innerhalb von Spalte D. Sprich, welchen Hierarchieplatz kriegt der Wert dort. Und INDEX wiederum schnappt sich dann den Wert aus Spalte A, der diesen Hierarchieplatz hat.

Voilà: Eine sortierte Spalte. Einziges Makel: Ganz unten werden die Leerzellen aus Spalte A als 0er angezeigt. Die können geflissentlich ignoriert werden. Wenn das ungünstig ist, dann noch eine WENN-Formel drumrumbauen, welche die 0er ausschließt. (Schaut dann allerdings etwas massig aus, solang man keine weitere Hilfsspalte benutzt.)

Soweit. Bei Fragen, gerne her damit. :-)

Viele Grüße!

Woher ich das weiß: Berufserfahrung – Data & BI Analyst

Comments

[Danii358]

Hallo,

ich habe da noch eine Rückfrage zu einer Formel

Diese Formel hat für mein Problem 1 funktioniert.

Jetzt will ich aber nur einen Teil einer Spalte lückenlos auflisten.

Diese Formel funktioniert für den Bereich A1-A502 und gibt das dann in Zelle D1-D502 wieder, soweit korrekt.

Hier ist die Formel du noch abzuändern wäre:

{=WENN(ZEILE(A1)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$1000<>"";ZEILE($1:$1000));ZEILE(A1))))}

Diese abgeänderte Formel brauche ich für den Bereich

FE 1062 - FE 1562 Ausgabe soll dann in IB 1062 - IB 1562 sein.

Aber ich habe schon versucht die genannte Formel so abzuändern das nur dieser Bereich betroffen ist. Excel zeigt jetzt aber nur leere Zellen an. Ich weiß nicht warum das so ist.

Könnten Sie mir vll sagen wie die formel sein muss, dass sie genau so funktioniert wie die in Spalte A ?

Gruß