[Tips&Tricks] Отбор из таблицы по условию "не входит в другую таблицу"

[JohnSUN]

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

Код Выделить Развернуть

=COUNTIF(<второй список>;<проверяемое значение>)>0
или

Код Выделить Развернуть

=ISERROR(VLOOKUP(<проверяемое значение>;<второй список>;1;0))
или

Код Выделить Развернуть

=ISERROR(MATCH(<проверяемое значение>;<второй список>;0))
выясняется, встречается ли очередное значение первого списка во втором. Вариантов таких формул масса, но общий смысл сводится к одному — получить значение ИСТИНА или ЛОЖЬ, по которому затем список будет отфильтрован.

Иногда, для очень больших наборов данных, на которых Calc из-за обилия формул начинает жутко тормозить, используют макросы.

Реже некоторые энтузиасты Base предлагают импортировать оба списка в базу данных и одним SELECT'ом получить результат.

Хочу показать еще один способ, о котором редко вспоминают.
Есть очень старый, но тем не менее, эффектный трюк.

Шаг 1. Преобразовать данные второго списка (фильтра) в строки с дописанным оператором сравнения "не равно". Для этого используется формула

Код Выделить Развернуть

= TRANSPOSE ( "<>" & <второй список>)
Ввод этой формулы нужно завершить нажатием Ctrl+Shift+Enter, чтобы получить «формулу массива»

Шаг 2. Повторить заголовок первого списка (столбца сортируемых данных) для каждой ячейки в строке условий

Шаг 3. Применить расширенный фильтр

Смотрите приложенный анимированный GIF

Разумеется, этот прием может быть полезен только для случая, когда количество значений во втором списке (списке фильтра) меньше числа столбцов в таблице. Однако на практике, к счастью,  редко встречаются наборы более тысячи записей.

[вложение удалено Администратором]