Извлечение значений из диапазона, который отсутствует в другом

Question

У меня есть две колонки в Excel, "ROSTER" и "PRESENT", показанные ниже:

Есть ли формула для достижения столбца "НЕ ЗДЕСЬ"? Я пытался использовать VLOOKUP() и https://superuser.com/a/289653/135912 безрезультатно =(

Любая помощь будет оценена!

Спасибо!

Answer 1

Нет встроенной функции, которая могла бы в одиночку выполнить эту задачу.

Вы можете попробовать эту формулу массива в столбце "Не здесь" (MS Excel 2007+)

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

Где (в моем примере)
roster именованных диапазонов, который относится к $A$2:$A$21
present является именованный диапазон, который относится к $B$2:$B$21

Чтобы ввести формулу, выберите ячейки в столбце «Не здесь» (в моем случае это C2 до C21), введите формулу и нажмите Ctrl+Shift+Enter

Answer 2

Это может быть немного излишним, но это работает. Надеюсь, вы не против иметь промежуточный столбец «Не здесь» с пробелами, прежде чем достигнуть конечного результата (Не здесь 2).

---

За кулисами:

Именованные диапазоны в использовании:

• Реестр: (B3: B19)
• Настоящее время: (C3: C19)
• NotHere: (F3: F19)

---

Формула массива введена в диапазон (D3:D19)...

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

---

Формулы массивов, введенные в ячейки (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)),"")}

так далее...

---

Хотя это решение выглядит сложным, оно будет работать независимо от того, где таблица размещена на рабочем листе. Он также удаляет ошибки #num в Excel 2007, если вы используете эту версию.