Пошук заданих рядків в таблиці ms excel - сумісно з microsoft excel 2018, excel 2018

Знайдемо задані рядки в таблиці. Рядки складаються з 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-й рядок у великій таблиці). Перше і друге вираження повернуть масиви і відповідно. Після попарного перемноження елементів масивів, він буде перетворений в. Тобто рядок 18 збігається з третьою шуканої рядком. Функція СУММПРОИЗВ () поверне 1.

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