Якщо потрібно отримати інформацію з таблиці Excel, існує декілька корисних функцій. Наприклад, можна застосовувати функцію ВПР в поєднанні з ІНДЕКС та ПОШУКПОЗ.
Ці функції здійснюють пошук заданого фрагмента в таблиці і зупиняються, коли знаходять перше відповідність. Однак, що робити, якщо потрібно знайти не лише перше збіг, а всі?
Отже, розпочнемо!
У даній статті я покажу вам, як це можна реалізувати.
Знаходження елемента в усій таблиці: другий, третій та N-ий випадок збігу
Через включення нового стовпця
Припустимо, ви маєте ось таку таблицю:
Наприклад, вам потрібно підрахувати всі «Training», які пройшла особа, у клітинках після її імені.
Таким чином, ми можемо використовувати функцію ВПР або ІНДЕКС (разом з ПОШУКПОЗ), але існує незначна проблема: коли функція виявить першу відповідність (наприклад, для Джона), вона припинить пошук і видасть результат.
Наприклад, Джон завершив усі курси, але якщо ми застосуємо згадані вище функції, ми отримаємо лише слово «Excel». Ця функція виявила перший збіг імені Джона, зупинилася і надала нам результат. Але що робити, щоб вона не зупинялася на першій знахідці?
Можна вставити новий стовпець і виконати з ним прості дії.
Алгоритм дій етап за етапом:
- Додамо стовпець «Помічник» відразу після імені;
У першому вільному місці запишемо:
=A2&ПІДРАХУВАЛИ($A$2:$A2;A2)
- Тепер у іншому стовпчику запишемо таку функцію:
=ЕСНД(ВПР($E2&ЧИСЛСТОЛБ($F$1:F1);$B$2:$C$14;2;0);””)
У цих стовпцях буде відображено, які тренінги пройшла особа. Якщо ж якусь із програм вона не завершила, це місце залишиться порожнім.
Яка роль цієї функції?
Ми застосовуємо певну хитрість. Функція РАХУНКИ забезпечує унікальність кожного нового імені людини. Як це вдається? Дуже просто: вона додає номер до імені, наприклад, Джон1, Джон2 і так далі.
Отже, виходить, що тепер функція ВПР не зупиниться при першому збігу, адже збігів більше не буде. Тепер усі імена, навіть якщо вони однакові, є унікальними завдяки цифрам, що додаються в кінці.
$E2&КІЛЬКСТОЛБ ($F$1:F1) є частиною, що використовується для виконання пошуку. Функція КІЛЬКСТОЛБ додає число, враховуючи номер рядка, до кінця назви, після чого здійснюється пошук за цим фрагментом.
З використанням масиву
Якщо з певних причин вам не подобається або ви не маєте можливості скористатися методом з додаванням стовпця, існує ще один спосіб.
Припустимо, у нас є аналогічна таблиця:
Ось переформульований текст зберігаючи первісний зміст:
Ця функція також видасть коректний результат:
=ЯКЩОПОМИЛКА(ІНДЕКС($B$2:$B$14;НАЙМЕНШИЙ(ЯКЩО($A$2:$A$14=$D2;РЯД($A$2:$A$14)-1;””);ЧИСЛЕТЕР($E$1:E1)));””) Щоб додати дані, вам слід вибрати комірки, в які потрібно внести інформацію (в даному випадку від E2 до G9).
Одним із ключових моментів є те, що, коли ви будете вводити функцію, потрібно нажати CTRL+SHIFT+ENTER, а не просто ENTER. Це необхідно, оскільки ми маємо справу з масивом даних.
Яка роль цієї функції?
Отже, давайте проаналізуємо:
$A$2:$A$14=$D2
У цьому сегменті нашої функції відбувається порівняння значення з коміркою D2.
Вихід або ІСТИНА, або обман.
Пример результата выполнения:
{ІСТИНА;неправда;неправда;неправда;неправда;неправда;ІСТИНА;неправда;неправда;неправда;ІСТИНА;неправда;неправда}
Продовжимо та проаналізуємо наступний елемент:
ЯКЩО($A$2:$A$14=$D2;РЯД($A$2:$A$14)-1;””)
У цьому розділі нашої функції ми приймаємо масив даних (істина або неправда) і замінюємо істину на порядковий номер рядка з таблиці, а неправду — на пусте значення.
Ось приклад реалізації цього елемента: {1;””;””;””;””;””;7;””;””;””;11;””;””}
Продовжуючи:
МІНІМАЛЬНИЙ(ЯКЩО($A$2:$A$14=$D2;РЯД($A$2:$A$14)-1;”);КІЛЬКІСТЬ.СТОВПЦІВ($E$1:E1))
Тепер функція НАЙМЕНШИЙ створить список усіх найменших порядкових чисел: перше, друге, третє і так далі. А функція ЧИСЛСТОЛБ надасть їм номера, згідно з порядком рядка.
ІНДЕКС ($ B $ 2: $ B $ 14; НАЙМЕНШИЙ (ЯКІ ($ A $ 2: $ A $ 14 = $ D2; РЯДОК ($ A $ 2: $ A $ 14)-1;”)); ЧИСЛСТОЛБ ($ E $ 1: E1) ))
Отже, функція ІНДЕКС, використовуючи порядкові номери, які надає функція ЧИСЛСТОЛБ, видасть відповідні значення. Це означає, що при першому збігу Excel поверне перший результат, і так далі.
Проте, якщо станеться помилка, а це неминуче, оскільки не всі учасники пройшли три тренінги, функція ЕСЛИПОМИЛКА замінить всі помилки на порожні значення.
Отже, в цій частині статті ми застосували функцію масиву. Це дуже зручно, оскільки її можна легко копіювати без жодних труднощів. На мою думку, це оптимальний підхід, хоча він може бути трохи більш складним, проте його легко масштабувати.
Я думаю, що це найефективніші способи виявлення всіх збігів. Якщо у вас є свої «зручні» методи, будь ласка, залиште їх у коментарях.
