зміст
Усі класичні функції пошуку та підстановки типів VPR (ВПР), GPR (HLOOKUP), БІЛЬШ ЕКСПОЗИЦІЙНИЙ (МАТЧ) і такі, як вони, мають одну важливу особливість – вони шукають від початку до кінця, тобто зліва направо або зверху вниз у вихідних даних. Як тільки буде знайдено перший відповідний збіг, пошук припиняється і буде знайдено лише перше входження потрібного нам елемента.
Що робити, якщо потрібно знайти не перше, а останнє входження? Наприклад, остання операція для клієнта, останній платіж, останнє замовлення тощо?
Спосіб 1: Пошук останнього рядка за допомогою формули масиву
Якщо у вихідній таблиці немає стовпця з датою або порядковим номером рядка (замовлення, платежу…), то наше завдання, власне, полягає в тому, щоб знайти останній рядок, який задовольняє задану умову. Це можна зробити за такою формулою масиву:
тут:
- функція IF (ЯКЩО) перевіряє одну за одною всі клітинки в стовпці Клієнт і відображає номер рядка, якщо він містить потрібну нам назву. Номер рядка на аркуші дає нам функція ЛІНІЯ (РЯДОК), але оскільки нам потрібен номер рядка в таблиці, ми додатково повинні відняти 1, оскільки у нас є заголовок у таблиці.
- Потім функція MAX (МАКС.) вибирає максимальне значення зі сформованого набору номерів рядків, тобто номер останнього рядка клієнта.
- функція ІНДЕКС (ІНДЕКС) повертає вміст комірки зі знайденим останнім числом з будь-якого іншого необхідного стовпця таблиці (Код замовлення).
Все це потрібно ввести як формула масиву, тобто:
- В Office 365 із встановленими останніми оновленнями та підтримкою динамічних масивів можна просто натиснути Що натомість? Створіть віртуальну версію себе у .
- У всіх інших версіях після введення формули доведеться натискати комбінацію клавіш Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у , який автоматично додасть до нього фігурні дужки в рядку формул.
Спосіб 2: Зворотний пошук за допомогою нової функції LOOKUP
Я вже написав велику статтю з відео про нову функцію Поглянути (XLOOKUP), який з’явився в останніх версіях Office замість старого VLOOKUP (ВПР). За допомогою BROWSE наше завдання вирішується досить елементарно, т.к. для цієї функції (на відміну від VLOOKUP) можна явно задати напрямок пошуку: зверху вниз або знизу вгору – за це відповідає її останній аргумент (-1):
Спосіб 3. Пошук рядка з останньою датою
Якщо у вихідних даних у нас є стовпець із порядковим номером або датою, які відіграють подібну роль, то завдання модифікується – потрібно знайти не останній (найнижчий) рядок із збігом, а рядок із останнім ( максимум) дата.
Я вже детально обговорив, як це зробити за допомогою класичних функцій, а тепер давайте спробуємо використати потужність нових функцій динамічного масиву. Для більшої краси та зручності ми також перетворюємо оригінальну таблицю в «розумну» таблицю за допомогою комбінації клавіш Ctrl+T або команди Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю).
З їх допомогою ця «парочка-вбивця» дуже витончено вирішує нашу проблему:
тут:
- Функція спочатку ФІЛЬТР (ФІЛЬТР) вибирає лише ті рядки нашої таблиці, де в стовпці Клієнт – ім’я, яке нам потрібно.
- Потім функція GRADE (СОРТ) сортує вибрані рядки за датою в порядку спадання, з останньою угодою вгорі.
- функція ІНДЕКС (ІНДЕКС) витягує перший рядок, тобто повертає останню потрібну угоду.
- І, нарешті, зовнішня функція FILTER видаляє зайві 1-й і 3-й стовпці з результатів (Код замовлення и Клієнт) і залишає лише дату та суму. Для цього використовується масив констант. {0;1;0;1}, визначаючи, які стовпці ми хочемо (1) або не хочемо (0) відображати.
Спосіб 4: Пошук останнього збігу в Power Query
Що ж, для повноти картини давайте розглянемо рішення нашої проблеми зворотного пошуку за допомогою надбудови Power Query. З її допомогою все вирішується дуже швидко і красиво.
1. Давайте перетворимо нашу оригінальну таблицю в «розумну» за допомогою комбінації клавіш Ctrl+T або команди Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю).
2. Завантажте його в Power Query за допомогою кнопки З таблиці/діапазону таб дані (Дані — з таблиці/діапазону).
3. Ми сортуємо (через розкривний список фільтра в заголовку) нашу таблицю в порядку спадання дати, щоб останні транзакції були вгорі.
4… У вкладці Перетворення вибрати команду Групувати за (Трансформувати — Групувати за) і встановіть групування за клієнтами, а в якості функції агрегування виберіть опцію Всі лінії (Всі рядки). Ви можете назвати новий стовпець як завгодно – наприклад ПОДРОБИЦІ.
Після групування ми отримаємо список унікальних імен наших клієнтів і в стовпці ПОДРОБИЦІ – таблиці з усіма транзакціями кожного з них, де першим рядком буде остання транзакція, що нам і потрібно:
5. Додайте новий обчислюваний стовпець за допомогою кнопки Спеціальний стовпець таб Додати стовпець (Додати стовпець — додати спеціальний стовпець)і введіть таку формулу:
тут ПОДРОБИЦІ – це стовпець, з якого ми беремо таблиці по клієнтам, і 0 {} це номер рядка, який ми хочемо витягти (нумерація рядків у Power Query починається з нуля). Отримуємо стовпець із записами (Запис), де кожен запис є першим рядком кожної таблиці:
Залишається розгорнути вміст усіх записів кнопкою з подвійними стрілками в заголовку стовпця Остання угода вибір потрібних стовпців:
… а потім видаліть стовпець, який більше не потрібен ПОДРОБИЦІ клацнувши правою кнопкою миші на його заголовку – Видалити стовпці (Видалити стовпці).
Після завантаження результатів на аркуш через Головна — Закрийте та завантажте — Закрийте та завантажте (Додому — Закрити та завантажити — Закрити та завантажити до…) ми отримаємо таку гарну таблицю зі списком останніх транзакцій, як ми хотіли:
Коли ви змінюєте вихідні дані, не забудьте оновити результати, клацнувши на них правою кнопкою миші – команда Update & Save (Оновити) або комбінацію клавіш Ctrl+інший+F5.
- Функція LOOKUP є нащадком функції VLOOKUP
- Як використовувати нові функції динамічного масиву SORT, FILTER і UNIC
- Пошук останньої непорожньої комірки в рядку чи стовпці за допомогою функції LOOKUP