Formel für lückenloses Auflisten einer Spalte in Excel 2007
Hallo,
ich habe folgende Situation Excel 2007
In der Spalte FE stehen Zahlen von der Zelle FE 1062 - FE 1562 in diesem Bereich stehen etwa 30 verschiedene Zahlen. D.h es gibt viele leere Zellen dazwischen.
Um das zu verhindern, hätte ich gerne eine Formel die die Zahlen lückenlos auflistet in die Spalte IB von Zelle IB 1062 - IB 1562.
Hier ist meine Formel wie ich sie mir überlegt habe:
{=WENN(ZEILE(A1)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$1000<>"";ZEILE($1:$1000));ZEILE(A1))))}
Diese Formel funktioniert auch eigentlich. Aber nur wenn die Zahlen, welche sortiert werden sollen z.B in der Zelle A1 oder B1, C1.... stehen. Ich habe auch schon versucht einfach diese Formel auf die Spalte FE anzupassen, aber Excel zeigt dann nur in der Spalte IB leere Zellen an, also keine aufgelisteten Zahlen.
Kann mir jemand von euch helfen, damit die Formel richtig arbeitet ?
am besten gleich eine korrekte Formel hier reinstellen.
Gruß Danii358
2 Antworten
Hier ist meine Formel wie ich sie mir überlegt habe:
Du meinst wie sie sich Walter, Jens und Boris überlegt haben: http://www.excelformeln.de/formeln.html?welcher=236
Man soll sich nicht mit fremden Federn schmücken, besonders dann nicht, wenn man das "geklaute" nicht versteht.
Aber um dir zu helfen: So wird die Formel auf Spalte FE angepasst.
Ohne geschweifte Klammern eingeben und dann mit Strg+Shift+Enter abschliessen.
{=WENN(ZEILE(A1)>ANZAHL2(FE:FE);"";INDEX(FE:FE;KKLEINSTE(WENN(FE$1:FE$1000<>"";ZEILE($1:$1000));ZEILE(A1))))}
1000 durch 1562 ersetzen. Ich geh aber davon aus, dass auch vor Zeile 1062 noch was in FE steht. Wenn man jetzt noch
FE$1:FE$1000<>"";ZEILE($1:$1000)
durch
FE$1062:FE$1562<>"";ZEILE($1062:$1562)
ersetzt, könnte es genau zutreffen. Oder müsste man auch noch ZEILE(A1)>ANZAHL2(FE:FE) anpassen? (kann mich grad nicht konzentrieren!)
Vielen Dank hat super funktioniert (endlich)
Hab noch ein kleines Schönheitsproblem in der Darstellung ;)
Wenn in meiner Spalte FE nur 10 Zahlen sind und ich in der Spalte IB diese Formel bis zur 15 Zeile herunterziehe dann kommt folgendes in der Zelle: #Zahl!
Könnte man jetzt vll noch die Formel so machen, dass anstatt #Zahl! ,Excel eine leere Zelle macht ?
Vielen Dank
Hallo, könnt Ihr mir mal helfen weil es bei mir so nicht funktioniert.
In Reihe A den Inhalt einer beliebigen Zelle löschen. Der Inhalt der restlichen A-Zellen sollen dann nachgerutscht kommen.
Selenblock
Also wenn die Zahlen auch sortiert werden sollen, kannst du folgende Formel benutzen:
=KGRÖSSTE(FE$1062:FE$1562;ZEILE(A1))
Diese Formel dann nach unten kopieren. Kannst natürlich auch KKLEINSTE verwenden, wenn du sie aufsteigend sortiert haben willst.
DH für die kürzeste Formel und für Zeile(A1), das wird viel zu selten genutzt!
"Zahlen auch sortiert werden sollen": ist wohl zwingend, ansonsten wirds das Mostrum (für nicht allzu Fortgeschrittene), das AChris oben hat. Das hätt ich mir vor 2 Jahren trotz 20Jahren Tabkalk selbst noch nicht zugetraut!
KKleinste bringt meiner Erinnerung nach die Leerzellen oben, die Werte tauchen also dann unten im Bereich auf!
Rückwärts kann man die Werte auch wieder zuordnen (sofern sie nur einmal auftreten und -2.Formel- zB für den Eintrag in Sp. A):
Zeile =Vergleich(ib1062;ib:iB;0)
Wert in A =index(A:A;Vergleich(ib1062;ib:iB;0);1)
Eine andere Möglichkeit, die Einträge in der richtigen Reihenfolge zu extrahieren, ist die Erstellung eines Autofilters mit Alt+n, f, f, sortieren nach FE: Nichtleere, kopieren und Einfügen auf ein neues Blatt. (möglicherweise in 2003 noch InhalteEinfügen, Werte, irgendwann scheint sich da mal was geändert zu haben)
Tipp zu Autofilter: Alt+n, f, a zeigt aus allen Spalten alle an, da kann man keine weiteren übersehen.
Alt+n, f, f erstellt das Autofilter und löscht es auch wieder
Sorry, hab das mit den Zeilen überlesen. Muss natürlich dann so heissen:
IB1062: {=WENN(ZEILE(A1)>ANZAHL2($FE$1062:$FE$1562);"";INDEX(FE:FE;KKLEINSTE(WENN(FE$1062:FE$1562<>"";ZEILE($1062:$1562));ZEILE(A1))))}
dann runterziehen.