☰

Извлечь значения из диапазона, отсутствующие в другом

tag-icon • tag-icon microsoft-excel vlookup array
Извлечь значения из диапазона, отсутствующие в другом

У меня есть два столбца в Excel: «СОСТАВ» и «НАСТОЯЩЕЕ», показанные ниже:

изображение столбца

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

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

Спасибо!

решение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

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

введите описание изображения здесь

решение2

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

Фотография рабочего раствора

За кулисами:

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

  • Состав: (B3:B19)
  • Присутствует: (C3:C19)
  • Не здесь: (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, если вы используете эту версию.

Связанный контент