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

5495
Kevin Hua

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

column image

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

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

Спасибо!

6
Как насчет этого? http://superuser.com/questions/81498/excel-find-items-in-one-column-that-are-not-in-another-column bfhd 11 лет назад 1
Это работает, но у меня не может быть пустых мест http://i.imgur.com/ETpb1.png Kevin Hua 11 лет назад 0
посмотрите решение `jeeped` в этой ссылке - это не даст вам пустых мест - это предполагает Excel 2007 или более позднюю версию, какая у вас версия? barry houdini 11 лет назад 0

2 ответа на вопрос

9
Ellesa

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

Вы можете попробовать эту формулу массива в столбце «Не здесь» (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

enter image description here

0
Iakovosian

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

Picture of working solution

За кулисами:

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

  • Реестр: (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, если вы используете эту версию.

Похожие вопросы