Якось, коли я працював у Excel переді мною стояло завдання — обчислити коли востаннє на конференції виступала кожна людина. Мені потрібно було зробити це, щоб при складанні розкладу, не допустити повторного виступу однієї людини. Таке завдання може зустрітися вам у різних ситуаціях.
Для цього я використав сукупність функцій.
Результат, якого ми досягнемо, виглядатиме так:
При виборі потрібного імені зі списку Excel автоматично виділяє нам той рядок, де зустрічається ім'я людини останній раз (щодо дати).
Тож почнемо!
Використовуємо функцію МАКС
Формула Excel, для нашого прикладу, виглядатиме так:
=ІНДЕКС($B$2:$B$14;СУМПРОИЗВ(МАКС(РЯДОК($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Як це працює:
- Функція МАКС знаходить номер того рядка, який є останнім. Наприкінці формули ми віднімаємо один, тому що в нашій таблиці є заголовок;
- Функція СУММПРОІЗВ використовується тільки для того, щоб ви без проблем та зайвих кнопок працювали з масивом даних;
- А функція ІНДЕКС просто виводить дату того рядка, в якому востаннє зустрічається ім'я.
Використовуємо функцію ПЕРЕГЛЯД
Формула виглядатиме так, а результат буде такий самий:
=ПРОГЛЯД(2;1/($A$2:$A$14=$D$3);$B$2:$B$14)
Як це працює:
- Отже, спочатку всі рядки порівнюються з вибраним ім'ям та створюється масив даних. Якщо рядок не збігається – масив потрапляє помилка, а якщо збігається, то туди потрапляє значення «1»;
- Останній аргумент – діапазон осередків, де міститься те, що потрібно повернути в результаті (у нашому випадку це дати).
Ну, а функція ПЕРЕГЛЯД просто сканує створений масив на збіг і видає осередок з датою для того рядка, в якому ім'я зустрічається останній раз.
Так як функція ПЕРЕГЛЯД шукає часткові збіги, при скануванні отриманого на попередніх етапах масиву даних, в ньому будуть тільки помилки та значення 1. І функція перегляд поверне те, що ближче до 2, тобто останнє значення 1.
За допомогою Visual Basic
Ну і насамкінець, розглянемо як зробити те саме завдання через Visual Basic.
Ми створимо власну функцію, за допомогою якої те саме завдання вирішуватиметься за пару кліків мишки. Щоб зробити це потрібно трохи підготуватися, зате потім ви зможете виконувати такі завдання дуже швидко.
Отже, зрештою, щоб отримати той самий результат, нам потрібно буде просто написати таку формулу і все буде готове!
Ось вона:
=LastItemLookup($D$3,$A$2:$B$14,2)
3 аргументи у функції:
- Перший – текстове значення, яке ми шукаємо;
- Другий – діапазон осередків, серед яких виконується пошук;
- Третій – діапазон осередків, значення яких повертатиметься при збігу.
Код Visual Basic:
| Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count До 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Exit Function End If Next i End Function |
Але для початку нам потрібно додати цю функцію до Excel
Як це зробити?
Покрокова інструкція:
- Клацніть на “Розробник”;
- Відкрийте Visual Basic;
- Правою кнопкою мишки на будь-який аркуш -> “Insert” -> “Module”;
- І просто помістіть код у вікно, що відкрилося.
Готово!
Однак не забудьте, що при використанні макросів Visual Basic потрібно зберігати вашу табличку з відповідним розширенням (.XLSM).
