Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1444to1448
1444to1448
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Auflisten ohne leere Zellen

Forumthread: Auflisten ohne leere Zellen

Auflisten ohne leere Zellen
02.09.2015 15:30:18
Marcel
Hallo,
habe eine Liste mit einigen Einträgen. Die Einträge in Spalte AD sind getrennt durch Leerzeilen.
Etwa so...
Eintrag 1
Eintrag 2
Eintrag 3 usw...
Das ganze soll in Spalte AE so aussehen
Eintrag 1
Eintrag 2
Eintrag 3
Habe jetzt in AE folgende Formel stehen
=INDEX(AD:AD;KKLEINSTE(WENN(AD$6:AD$90000;ZEILE($1:$99);101);ZEILE()))
Das ganze habe ich als Matrix Formel Strg + Shift + Enter eingegeben. Als Ausgabe bekomme ich #NV
Raffs net, wo der Fehler liegt, kann mir jemand auf die Sprünge helfen?
Danke LG
Marcel

Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Auflisten ohne leere Zellen
02.09.2015 15:44:43
SF
Hola,
ungetestet:
{=wennfehler(INDEX($AD$6:$AD$9000;KKLEINSTE(WENN($AD$6:$AD$90000;ZEILE($1:$8995));ZEILE(A1)));"") }
Gruß,
steve1da

AW: MATRIXformeln erfordern gleichgroße Bereiche
02.09.2015 16:20:52
...
Hallo Marcel,
... dass ist bei Dir mit AD$6:AD$9000 und ZEILE($1:$99) nicht der Fall.
Einer Matrixfunktion(alität)sformel auf Basis von AGGREGAT(#;6,...) ist das (fast) egal.
Diese lautet in AE1 so:
=WENNFEHLER(INDEX(AD:AD;AGGREGAT(15;6;ZEILE(Z$6:Z$9123)/(AD$6:AD$9000"");ZEILE()));"")
und zwar ohne {}-Eingabeabschluss!
Gruß Werner
.. , - ...

Anzeige
AW: MATRIXformeln erfordern gleichgroße Bereiche
03.09.2015 11:18:33
Marcel
Hallo Werner,
wenn ich die Formel so eingebe, bekomme ich WAHR als Ausgabe. Was bewirkt Aggregat denn? Und was bedeutet Z6:Z9123? Wieso beziehen wir uns hier auf Z? Spalte Z spielt doch in der Auswertung keine Rolle :-/

AW: MATRIXformeln erfordern gleichgroße Bereiche
03.09.2015 11:39:21
Daniel
Hi
die Formel braucht an dieser Stelle eine fortlaufende Zahl von 6 bis 9123.
diese kann man mit der Funktion ZEILE() erstellen.
auf welche Spalten man sich dabei referenziert, ist egal.
man sollte nur bedenken, dass Excel eine Formel immer dann neu berechnet, wenn sich im angegebenen Zellbereich eine Änderung ergibt, dazu gehört auch der verwendete Zellbereich in Zeile().
die Werte in dem dort angegebenen Zellbereich haben aber keinen Einfluss auf das Formelergebnis, daher wäre so eine Neuberechnung sinnlos.
Daher sollte man den Zellbereich, den man in Zeile() verwendet möglichst klein halten (eine Spalte) und in einen Zellbereich legen, der normalerweise nicht beschrieben wird oder sowieso schon bestandteil der Formel ist um unnötige Neuberechnungen der Formel zu vermeiden.
die Spalte Z wurde hier wahrscheinlich so als Standardspalte gewählt, weil Z an Zeile erinnert.
sinnvoller wäre hier aber, den sowieso schon referenzierten Zellbereich AD6:AD9000 zu verwenden.
Dann hat man auch weniger probleme, wenn man die Tabelle nachträglich durch Einfügen oder Löschen von Zellen bearbeitet.
=WENNFEHLER(INDEX(AD:AD;AGGREGAT(15;6;ZEILE(AD$6:AD$9000)/(AD$6:AD$9000"");ZEILE()));"")
Gruß Daniel

Anzeige
AW: MATRIXformeln erfordern gleichgroße Bereiche
03.09.2015 11:59:32
Marcel
Ei subber, jetzt klappts :)
Danke für die gute Erklärung

AW: MATRIXformeln erfordern gleichgroße Bereiche
03.09.2015 15:07:37
Marcel
Jetzt hab ich doch noch ein Problem festgestellt. Das Ding schreibt mir jetzt zwar einige Einträge untereinander, lässt aber ein paar außen vor. Verstehe net ganz, an was das hängt

AW: und welche werden nicht berücksichtigt? ...
03.09.2015 15:15:22
...
Hallo Marcel,
... sind das Texte die unterhalb von AD9000 stehen? Oder?
Gruß Werner
.. , - ...

Anzeige
AW: und welche werden nicht berücksichtigt? ...
03.09.2015 15:46:22
Marcel
Leider nein, stehen zwischen AD5 und AD1000.
Erster Eintrag in AD491 wird ignoriert
Zweiter Eintrag in AD536 wird ignoriert
Dritter Eintrag in AD551 wird ignoriert
Vierter Eintrag in AD603 wird ignoriert
Fünfter Eintrag in AD617 wird ignoriert
Sechster Eintrag in AD711 wird aufgelistet
Siebter Eintrag in AD729 wird aufgelistet
Achter Eintrag in AD750 wird aufgelistet
Wenn ich das ganze in einer anderen Spalte mache, ignoriert die Formel auch die ersten 5 Einträge und zeigt den Rest an.
Wenn ich die Liste Länger mache, also mehr Einträge hinzufüge, ignoriert die Formel weiter die ersten fünf Einträge, listet den Rest aber brav auf.

Anzeige
AW: so wie Du jetzt schreibst...
03.09.2015 16:45:40
...
Hallo Marcel,
... beginnst Du Dein ERrgebnislisting nicht in der 6. Zeile? Nicht wie ursprünglich von Dir angezeigt in der 1. Zeile?
Wenn Du in einer beliebigen Zeile beginnst, dann, anstelle ZEILE() z.B. ZEILE(A1)
=WENNFEHLER(INDEX(AD:AD;AGGREGAT(15;6;ZEILE(Z$6:Z$9123)/(AD$6:AD$9000"");ZEILE(A1)));"")
Gruß Werner
.. , - ...

Anzeige
AW: so wie Du jetzt schreibst...
04.09.2015 08:30:41
Marcel
Hmm mit Zeile(A1) funktionierts.
Wenn ich Zeile(A2) angebe, ignoriert er den ersten Eintrag und listet dann alle anderen. Verwirrend irgendwie :/

Forumthreads zu verwandten Themen

Nicht leere Zellen in einer Zeile finden
Andreas

Hallo Zusammen,
Bisher kam ich ohne VBA zurecht, aber ich denke nun ...
Ich habe eine Tabelle mit folgendem Aufbau
Tabelle1:
ABCDEFG
115789
2__z_z
3zz___
4z__z_
Zeile 1: enthält eine Bezeichnung (hier als Zahl dargestellt)
z=Zahl
_=leer
in einem n...
29.09.2008 14:20:42
Thread-ID: 1013111
5 Beiträge
Gültigkeitsliste ohne Zellenbereich
Reinhard

Hallo Wissende,
Sub Makro1() With Range("P2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="a;b;c" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput...
02.11.2008 16:48:00
Thread-ID: 1021179
4 Beiträge
Zählen nichtleerer Zellen
Stefan

Hallo zusammen,
ich erbitte erneut Eure Hilfe. Es geht darum Zellen mit Inhalt einer Liste der Fuhrparkverwaltung zu zählen. In Spalte C:J habe ich jeweils die verschiedenen Fahrzeuge. In den Zeilen 7:372 trage ich dann ein, wer welches Fahrzeug belegt hat. Geht eine Fahrt über mehrere Tage...
04.11.2008 08:20:00
Thread-ID: 1021461
3 Beiträge
Anzeige
Kopieren von Nichtleeren Zellen per Formel
Nichtleeren

Moin,
auf der Arbeit soll ich eine Tabelle anpassen:
In Tabellenblatt1 ist eine Matrix M6:P109, in einigen Zeilen (Artikelnummern in loser Reihenfolge) stehen Werte, (die Artikel, die im Bürobestand fehlen) in den meisten nicht.
Die Zeilen, in denen Werte stehen, sollen nun in Tabel...
10.01.2009 20:43:46
Thread-ID: 1038525
4 Beiträge
Anzeige
Matrix (mit leeren zellen) in Liste umwandeln
Wolfango

Hallo nochmal,
(diese Frage knüpft an die vorhin gestellte Frage an; nur diesmal mit einem anderen Ansatz)
Wie kann ich die in der angehängten Matrix (noch) vorhandenen Werte, über Formeln sauber in einer Liste (Spalte) zusammenführen?
Die Datei https://www.herber.de/bbs/user/61614...
05.05.2009 11:55:18
Thread-ID: 1073036
5 Beiträge
Leere und Nichtleere Zellen einer Spalte rechnen
Urmila

Hallo,
habe ien Problem, ich habe eine Liste, in Spalte A stehen Werte (Zahlen), in der Spalte B stehen Namen.
Nun will ich, dass er mir in der Zelle D1 alle Werte aus spalte A ausrechnet, wo die Zellen in Spalte B Inhalte haben, und in Zelle E1 wo kein Inhalt steht.
Ich hoffe ich k...
30.06.2009 12:41:41
Thread-ID: 1085323
4 Beiträge
UDF zum Verketten von nichtleeren Zellen mit Delim
nichtleeren

Hallo an alle,
ich würde gern eine benutzerdefinierte Funktion (UDF) erstellen, die alle Zahlen und Text in einer Zeile mit einander verkettet. Es geht um zwei Blätter: auf dem einen sind die Kommentare mit Zahlen detailliert dargestellt und jede Zahl steht in einer eigenen Zelle. Hier gibt...
02.07.2009 15:48:21
Thread-ID: 1085999
7 Beiträge
Anzeige
Alle roten zellen ohne Duplikate auflisten
Markus

Hallo zusammen,
ich habe in Tabelle 2 Spalte A, und in Tabelle 3 verstreut Zellen die eine Nummer enthalten.
Wie kann ich sämtliche Felder die rot markiert sind, in Tabelle 3 Spalte M absteigend sortiert ohne doppelte Eintäge auflisten?
Ich hoffe jemand kann helfen.
Gruß Markus<...
18.02.2010 13:54:14
Thread-ID: 1139505
6 Beiträge
Liste ohne Leerzellen
Harald

Hallo zusammen,
folgendes Problem:
In Spalte A:A habe ich in manche Leerzellen.
z.B.:
A1=1234abcd
A2= leer
A3= leer
A4= 4321dcba
A5= leer
usw.
Jetzt möchte ich gern alle gefüllten Zellen in Spalte B:B untereinander stehen haben, also :
B1= 1234abcd
24.02.2010 17:01:25
Thread-ID: 1141194
12 Beiträge
Gültigkeitsliste ohne Leerzellen
Thomas

Hallo,
ich benötige Hilfe bei der Erstellung einer Liste ohne Leerzellen.
In einem Tabellenblatt(2) habe ich eine Liste von Mitarbeitern mit verschiedenen Berechtigungen. Hier habe ich dann per =WENN(F2="x";A2&" ,"&B2) eine Liste mit MA erstellt welche jetzt in Frage kommen. Die...
30.06.2010 14:14:42
Thread-ID: 1164709
3 Beiträge
Kombiliste ohne leere Felder
Andreas

Problem: Der Inhalt der Kombiliste wird aus einer anderen Kombiliste gefüllt und enthält an variaben Stellen immer leere Bereiche, die ich gerne "ausgeblendet" haben möchte...

21.05.2002 07:56:48
Thread-ID: 122296
7 Beiträge
Anzeige
Anzahl nicht leerer Zellen nach Kriterium
Peter

Ich habe folgendes Problem:
Ich benötige in den Zellen D2:D6 die Anzahl der nicht leeren Zellen in D10:D19. Allerdings nur, wenn der Inhalt von C2:C6 mit dem Inhalt von B10:B20 übereinstimmt. (In dem unten stehenden Beispiel soll also in D2 eine 2 stehen. In D3 eine 1 usw.

Danke i...
02.07.2011 00:45:28
Thread-ID: 1220599
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Auflisten ohne leere Zellen in Excel

Schritt-für-Schritt-Anleitung

Um eine Liste in Excel ohne leere Zellen aufzulisten, kannst du folgende Schritte befolgen:

  1. Öffne deine Excel-Datei und stelle sicher, dass die Daten, die du auflisten möchtest, in einer Spalte vorhanden sind (z.B. Spalte AD).

  2. Wähle die Zelle aus, in der die aufgelisteten Werte erscheinen sollen (z.B. Zelle AE1).

  3. Gib die folgende Formel ein, um die nicht leeren Zellen aufzulisten:

    =WENNFEHLER(INDEX(AD:AD;AGGREGAT(15;6;ZEILE(AD$6:AD$9000)/(AD$6:AD$9000<>"");ZEILE()));"")

  4. Bestätige die Eingabe mit der Eingabetaste. Diese Formel ignoriert leere Zellen und listet nur die vorhandenen Werte auf.

  5. Ziehe die Formel nach unten, um weitere Einträge aufzulisten.

Häufige Fehler und Lösungen

Hier sind einige häufige Fehler und deren Lösungen:

  • Fehler: #NV – Dieser Fehler tritt auf, wenn die Formel keine gültigen Werte findet. Stelle sicher, dass der Zellbereich korrekt definiert ist und keine leeren Zellen dazwischen sind.

  • Fehler: WAHR – Wenn du diese Ausgabe erhältst, überprüfe die Formel auf korrekte Verweise und stelle sicher, dass die Werte in der angegebenen Spalte korrekt sind.

  • Einträge werden ignoriert – Möglicherweise beginnt die Ergebnisliste nicht an der richtigen Stelle. Stelle sicher, dass du die Zeile in der Formel korrekt angibst, z.B. ZEILE(A1) für die erste Zeile.

Alternative Methoden

Es gibt verschiedene Methoden, um Daten in Excel lückenlos aufzulisten:

  1. Filter verwenden: Du kannst einen Autofilter auf die Daten anwenden und nur die nicht leeren Zellen auswählen.

  2. Pivot-Tabellen: Erstelle eine Pivot-Tabelle, die nur die eindeutigen Werte anzeigt, ohne leere Zellen.

  3. Erweiterte Filter: Nutze die Funktion „Erweiterter Filter“, um ein neues Datenblatt zu erstellen, das nur die eindeutigen Werte ohne leere Zellen enthält.

Praktische Beispiele

  • Beispiel 1: Angenommen, du hast folgende Einträge in Spalte AD:

    Eintrag 1
(leer)
Eintrag 2
(leer)
Eintrag 3

    Mit der oben genannten Formel in Zelle AE1 erhältst du:

    Eintrag 1
Eintrag 2
Eintrag 3

  • Beispiel 2: Wenn du eine Liste mit Werten hast, die Nullen enthalten, und diese ignorieren möchtest, kannst du die Formel anpassen, um nur die Werte größer als Null aufzulisten:

    =WENNFEHLER(INDEX(AD:AD;AGGREGAT(15;6;ZEILE(AD$6:AD$9000)/(AD$6:AD$9000>0);ZEILE()));"")

Tipps für Profis

  • Verwende benannte Bereiche: Dies macht die Formeln übersichtlicher und einfacher zu verwalten.

  • Vermeide unnötige Berechnungen: Halte den Zellbereich in der ZEILE()-Funktion klein, um die Performance zu verbessern.

  • Nutze Datenvalidierung: Verhindere das Einfügen von leeren Zellen, indem du eine Datenvalidierung auf die Eingabespalten anwendest.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass nur eindeutige Werte aufgelistet werden?
Verwende die Funktion EINDEUTIGE(), um sicherzustellen, dass nur einzigartige Werte in deiner Liste erscheinen.

2. Was mache ich, wenn meine Liste immer noch leere Zellen anzeigt?
Überprüfe die Zellreferenzen in deiner Formel und stelle sicher, dass du den richtigen Bereich ohne leere Zellen verwendest.

Beliebteste Forumthreads (12 Monate)

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