Знайдіть останній випадок значення пошуку в списку в Excel

У цьому посібнику ви дізнаєтесь, як знайти останнє входження елемента у списку за допомогою формул Excel.

Нещодавно я працював над визначенням порядку денного наради.

У мене був список в Excel, де я мав список людей і дати, коли вони виступали в ролі «Голови зборів».

Оскільки у списку було повторення (це означає, що людина неодноразово була на засіданні голови), мені також потрібно було знати, коли востаннє особа виступала як «голова зборів».

Це було тому, що мені довелося переконатися, що того, хто нещодавно очолював, не призначили знову.

Тому я вирішив використати магію функцій Excel, щоб це зробити.

Нижче наведено кінцевий результат, де я можу вибрати ім’я зі спадного меню, і він дає мені дату останнього введення цього імені у списку.

Якщо ви добре розумієте функції Excel, ви б знали, що немає жодної функції Excel, яка б це могла зробити.

Але ви знаходитесь у розділі «Зламання формул», і тут ми робимо чарівну подію.

У цьому уроці я покажу вам три способи зробити це.

Знайдіть останній випадок - за допомогою функції MAX

Завдяки цій техніці заслуговує стаття керівника програми Excel Чарлі Кайда.

Ось формула Excel, яка поверне останнє значення зі списку:

= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14))*($ D $ 3 = $ A $ 2: $ A $ 14))-1))

Ось як працює ця формула:

• Функція MAX використовується для пошуку номера рядка останнього відповідного імені. Наприклад, якщо ім’я Глен, воно поверне 11, так як воно знаходиться у 11 -му рядку. Оскільки наш список починається з другого рядка, 1 було віднято. Отже, позиція останнього входження Глена - 10 у нашому списку.
• SUMPRODUCT використовується, щоб гарантувати, що вам не доведеться використовувати Control + Shift + Enter, оскільки SUMPRODUCT може обробляти формули масиву.
• Тепер функція INDEX використовується для пошуку дати останнього відповідного імені.

Знайдіть останній випадок - за допомогою функції LOOKUP

Ось ще одна формула для виконання тієї ж роботи:

= ПОГЛЯД (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)

Ось як працює ця формула:

• Значення пошуку - 2 (ви зрозумієте чому … продовжуйте читати)
• Діапазон пошуку 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Це повертає 1, коли він знаходить відповідне ім'я, і ​​помилку, коли це не так. Таким чином, ви отримаєте масив. Наприклад, із значення пошуку Glen, масив буде {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
• Третій аргумент ([result_vector]) - це діапазон, з якого він дає результат, які в даному випадку є датами.

Причина цієї формули полягає в тому, що функція LOOKUP використовує техніку наближеної відповідності. Це означає, що якщо він зможе знайти точне відповідне значення, він поверне це, але якщо не зможе, він відсканує весь масив до кінця і поверне наступне найбільше значення, нижче від значення пошуку.

У цьому випадку значення пошуку становить 2, і в нашому масиві ми отримаємо лише 1 або помилки. Таким чином, він сканує весь масив і повертає позицію останнього 1 - це останнє відповідне значення імені.

Знайдіть останній випадок - використання користувацької функції (VBA)

Дозвольте мені також показати вам інший спосіб зробити це.

Ми можемо створити власну функцію (також звану функцією, визначеною користувачем) за допомогою VBA.

Перевага створення користувацької функції в тому, що вона проста у використанні. Вам не потрібно кожен раз турбуватися про створення складної формули, оскільки більшість робіт відбувається у бекенді VBA.

Я створив просту формулу (яка багато в чому нагадує формулу VLOOKUP).

Щоб створити власну функцію, вам потрібно мати код VBA у редакторі VB. Я дам вам код і кроки, щоб розмістити його в редакторі VB через деякий час, але дозвольте мені спочатку показати вам, як він працює:

Ось формула, яка дасть вам результат:

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

Формула бере три аргументи:

• Значення пошуку (це буде ім'я у клітинці D3)
• Діапазон пошуку (це діапазон із назвами та датами - A2: B14)
• Номер стовпця (це стовпець, з якого ми хочемо отримати результат)

Після того як ви створили формулу та помістили код у редактор VB, ви можете використовувати її так само, як і будь -які інші звичайні функції робочого аркуша Excel.

Ось код формули:

"Це код функції, яка знаходить останнє входження значення пошуку та повертає відповідне значення із зазначеного стовпця" Код, створений Sumit Bansal (https://trumpexcel.com) Функція LastItemLookup (Lookupvalue As String, LookupRange As Діапазон, номер стовпця як ціле число) Dim i As Long For i = LookupRange.Columns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Тоді LastItemLookup = LookupRange.Cells (i, ColumnNumber) Вийти з функції "Завершити", якщо "Далі" i "Завершити функцію"

Ось кроки, щоб розмістити цей код у редакторі VB:

1. Перейдіть на вкладку Розробник.
2. Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
3. На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код. Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
4. Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
5. Скопіюйте та вставте код у вікно модуля.

Тепер формула буде доступна на всіх аркушах робочої книги.

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