Як обчислити дату останнього збігу текстового значення в Excel

Якось, коли я працював у Excel переді мною стояло завдання — обчислити коли востаннє на конференції виступала кожна людина. Мені потрібно було зробити це, щоб при складанні розкладу, не допустити повторного виступу однієї людини. Таке завдання може зустрітися вам у різних ситуаціях.

Для цього я використав сукупність функцій.

Результат, якого ми досягнемо, виглядатиме так:

Як обчислити дату останнього збігу текстового значення в 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)

Як обчислити дату останнього збігу текстового значення в Excel

Як це працює:

  • Отже, спочатку всі рядки порівнюються з вибраним ім'ям та створюється масив даних. Якщо рядок не збігається – масив потрапляє помилка, а якщо збігається, то туди потрапляє значення «1»;
  • Останній аргумент – діапазон осередків, де міститься те, що потрібно повернути в результаті (у нашому випадку це дати).

Ну, а функція ПЕРЕГЛЯД просто сканує створений масив на збіг і видає осередок з датою для того рядка, в якому ім'я зустрічається останній раз.

Так як функція ПЕРЕГЛЯД шукає часткові збіги, при скануванні отриманого на попередніх етапах масиву даних, в ньому будуть тільки помилки та значення 1. І функція перегляд поверне те, що ближче до 2, тобто останнє значення 1.

За допомогою Visual Basic

Ну і насамкінець, розглянемо як зробити те саме завдання через Visual Basic.

Ми створимо власну функцію, за допомогою якої те саме завдання вирішуватиметься за пару кліків мишки. Щоб зробити це потрібно трохи підготуватися, зате потім ви зможете виконувати такі завдання дуже швидко.

Отже, зрештою, щоб отримати той самий результат, нам потрібно буде просто написати таку формулу і все буде готове!

Ось вона:

=LastItemLookup($D$3,$A$2:$B$14,2)

Як обчислити дату останнього збігу текстового значення в Excel

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

Як це зробити?

Покрокова інструкція:

  • Клацніть на “Розробник”;

Як обчислити дату останнього збігу текстового значення в Excel

  • Відкрийте Visual Basic;

Як обчислити дату останнього збігу текстового значення в Excel

  • Правою кнопкою мишки на будь-який аркуш -> “Insert” -> “Module”;

Як обчислити дату останнього збігу текстового значення в Excel

  • І просто помістіть код у вікно, що відкрилося.

Як обчислити дату останнього збігу текстового значення в Excel

Готово!

Однак не забудьте, що при використанні макросів Visual Basic потрібно зберігати вашу табличку з відповідним розширенням (.XLSM).

nBook
Додати коментар