☰

Holen Sie sich die Referenzen aus den Zellen, die nicht leer sind

tag-icon • tag-icon microsoft-excel worksheet-function
Holen Sie sich die Referenzen aus den Zellen, die nicht leer sind

Ich brauche Hilfe bei einer Funktion. In Spalte A steht eine Liste von Namen, die aber durch leere Zellen getrennt sind. Gibt es eine Möglichkeit, diese Namen in Spalte B anzuzeigen, aber ohne die leeren Zellen?

Das Ergebnis sollte wie folgt aussehen:

Bildbeschreibung hier eingeben

Antwort1

Die folgenden Arrayformeln funktionieren auch, wenn einige der Namen identisch sind. Geben Sie sie B2mit Ctrl+ Shift+ ein Enterund füllen Sie sie anschließend nach unten aus.

Dieses verwendet einen festen Bereich basierend auf Ihren Beispieldaten:

{=IFERROR(INDEX(A:A,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10),""),ROW()-ROW(A$1)+1)),"")}

Dieses verwendet einen dynamischen Bereich, der sich automatisch anpasst, wenn Sie der Spalte weitere Namen hinzufügen A:A:

=IFERROR(INDEX(A:A,SMALL(IF(A$1:INDEX(A:A,MATCH("*",A:A,-1))<>"",ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1))),""),ROW()-ROW(A$1)+1)),"")

Dieselbe dynamische Formel wie oben in erweiterter Form:

=
IFERROR(
  INDEX(
    A:A
  , SMALL(
      IF(
        A$1:INDEX(A:A,MATCH("*",A:A,-1))<>""
      , ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1)))
      , ""
      )
    , ROW()-ROW(A$1)+1
    )
  )
, ""
)

Wie Sie sehen, ist diese zweite Formel einfach die erste, wobei alle A$10durch ersetzt wurden INDEX(A:A,MATCH("*",A:A,-1)).

Erläuterung:

Die IF()Funktion ist äquivalent zu:

IF(
  {"";"Camilo Georgi";"";"Carla Suarez Navarro";"";"Belinda Bencic";"";"Grace Min";"";"Johanna Larsson"}<>""
, {1;2;3;4;5;6;7;8;9;10}
, ""
)

Da Excel Konstanten automatisch in Konstanten-Arrays mit passender Länge erweitert, ergibt sich daraus:

IF(
  {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
, {1;2;3;4;5;6;7;8;9;10}
, {"";"";"";"";"";"";"";"";"";""}
)

Das Ergebnis lautet:

{"";2;"";4;"";6;"";8;"";10}

Die SMALL()Funktion wird dann:

SMALL({"";2;"";4;"";6;"";8;"";10},ROW()-1+1)

und da SMALL()Zeichenfolgen ignoriert werden, ist dies gleichbedeutend mit:

SMALL({2;4;6;8;10},ROW())

Beachten Sie, dass die Zahlen die Indizes der nicht leeren Namen sind. Für Zellen B1 SMALL()wird zurückgegeben 2, für B2wird 4 zurückgegeben usw. Für B6und darunter wird der #NUM!Fehler zurückgegeben. (Aus diesem Grund gibt es eine IFERROR()Funktion. Sie konvertiert diese Fehler in Leerzeichen.)

Schließlich INDEX()zieht die Funktion die Namen mithilfe der Indizes.

Antwort2

Hier ist eine Möglichkeit, Werte aufzulisten und dabei leere Zellen auszuschließen. Siehediese Hilfeseitefür mehr Details.

Die Formel hierfür erfordert eine Zeile oberhalb der ersten Datenzeile. In der folgenden Tabelle dienen dazu die Überschriften.

Bildbeschreibung hier eingeben

In B2 wird folgende Matrixformel eingetragen:

=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10&"")+IF($A$2:$A$10=",1,0),0)),"")

Da es sich um eine Arrayformel handelt, muss sie mit eingegeben werden CtrlShiftEnter, nicht nur mit Enter. Bei korrekter Eingabe umschließt Excel die Formel in der Formelleiste mit geschweiften Klammern {}.

Nachdem die Formel eingegeben wurde, klicken Sie auf Zelle B2 und füllen Sie die Formel nach unten aus.

Falls doppelte Namen aufgelistet werden müssen, entfernt diese Array-Formel nur Leerzeichen, aber keine Duplikate:

=IFERROR(INDEX(A:A,SMALL(INDEX(NOT(ISBLANK($A$2:$A$10))*ROW($A$2:$A$10),0),COUNTBLANK($A$2:$A$10)+ROW(C1))),"")

verwandte Informationen