Поиск заданных строк в таблице EXCEL

Найдем заданные строки в таблице. Строки состоят из 2-х столбцов, значения могут быть любыми.

Пусть имеется перечень из 5 строк, которые требуется найти в таблице MS EXCEL. Предполагается, что строки не повторяются.

Будем искать эти строки в большой таблице (см. файл примера ).

Строки считаются совпадающими, когда их значения в обоих столбцах попарно совпадают .

Для поиска совпадений будем использовать формулу = СУММПРОИЗВ((B17=B$8:B$12)*(C17=C$8:C$12)) Формулу разместим в столбце D.

Формула работает следующим образом. Выражение B17=B$8:B$12 сравнивает текущее значение первого столбца большой таблицы со всеми значениями первого столбца искомых строк, и возвращает массив логических значений {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}. 5 значений ЛОЖЬ означает, что значение 1,2 из В17 не найдено в диапазоне B$8:B$12, состоящем из 5 значений, т.к. значение не встречается в первом столбце искомых строк.

Аналогично, второе выражение ищет совпадения во втором столбце. Далее, значения попарно перемножаются (при этом ЛОЖЬ преобразуется в 0, а ИСТИНА в 1). Знак умножения эквивалентен в данном случае функции И() , т.е. строки совпадают, когда значения из обоих столбцов совпадают с соответствующими значениями искомых строк. Функция СУММПРОИЗВ() складывает результат перемножения и использована вместо СУММ() лишь для того, чтобы не использовать формулы массива .

Для примера рассмотрим строку 18 (2-я строка в большой таблице). Первое и второе выражения вернут массивы {ЛОЖЬ:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ} и {ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ} соответственно. После попарного перемножения элементов массивов, он будет преобразован в {0:0:1:0:0}. Т.е. строка 18 совпадает с третьей искомой строкой. Функция СУММПРОИЗВ() вернет 1.

СОВЕТ : Для просмотра промежуточных вычислений воспользуйтесь клавишей F9 .

С помощью формулы массива =ПОИСКПОЗ(1;(B17=B$8:B$12)*(C17=C$8:C$12);0) найдем номер искомой строки. Формулу разместим в столбце Е.

Также для каждой искомой строки можно определить, если она в большой таблице. Это можно сделать с помощью функции = ЕСЛИ(ЕНД(ПОИСКПОЗ(A8;$E$17:$E$28;0));"";"Да!")

Найденные строки можно выделить с помощью Условного форматирования .