☰

Extrahieren von Werten aus einem Bereich, die in einem anderen fehlen

tag-icon • tag-icon microsoft-excel vlookup array
Extrahieren von Werten aus einem Bereich, die in einem anderen fehlen

Ich habe in Excel zwei Spalten, „ROSTER“ und „PRESENT“, die unten angezeigt werden:

Spaltenbild

Gibt es eine Formel, um die Spalte „NICHT HIER“ zu erhalten? Ich habe es mit VLOOKUP()und versucht.https://superuser.com/a/289653/135912ohne Erfolg =(

Jede Hilfe wäre willkommen!

Danke!

Antwort1

Es gibt keine integrierte Funktion, die diese Aufgabe im Alleingang erledigen kann.

Sie können diese Matrixformel in der Spalte „Nicht hier“ ausprobieren (MS Excel 2007+).

=IFERROR(INDEX(roster,SMALL(IF(COUNTIF(present,roster)=0,ROW()-1,""),ROW()-1),1),"")

Wobei (in meinem Beispiel)
rosterein benannter Bereich ist, der sich bezieht auf $A$2:$A$21
presentist ein benannter Bereich, der sich bezieht auf$B$2:$B$21

Um die Formel einzugeben, wählen Sie die Zellen in der Spalte „Nicht hier“ aus (in meinem Fall ist esC2bis zu21. Jahrhundert), geben Sie die Formel ein und drücken Sie dann Ctrl+ Shift+Enter

Bildbeschreibung hier eingeben

Antwort2

Das ist vielleicht etwas übertrieben, aber es funktioniert. Hoffentlich macht es Ihnen nichts aus, eine Zwischenspalte „Nicht hier“ mit Leerzeichen zu haben, bevor Sie zum Endergebnis (Nicht hier 2) gelangen.

Bild der funktionierenden Lösung

Hinter den Kulissen:

Verwendete benannte Bereiche:

  • Dienstplan: (B3:B19)
  • Gegenwart: (C3:C19)
  • Nicht hier: (F3:F19)

Array-Formel in Bereich (D3:D19) eingegeben …

{=IF(ISERROR(MATCH(Roster,Present,0)),Roster,"")}

In Zellen eingegebene Formeln anordnen (E3:E19) …

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A1)),COLUMN(A1)),"")}

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A2)),COLUMN(A2)),"")}

usw...

Obwohl dies eine langwierige Lösung zu sein scheint, funktioniert sie unabhängig davon, wo die Tabelle im Arbeitsblatt platziert wird. Außerdem werden dadurch #numFehler in Excel 2007 behoben, falls Sie diese Version verwenden.

verwandte Informationen