Загальна формула
=XLOOKUP(max,dates,results,,-1) // latest match by date
Резюме
Щоб отримати останню відповідність у наборі даних за датою, ви можете використовувати XLOOKUP в режимі приблизного збігу, встановивши для match_mode значення -1. У наведеному прикладі формула в G5, скопійована вниз, має вигляд:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
де дата (C5: C15), товар (B5: B15) і ціна (D5: D15) називаються діапазонами.
Пояснення
XLOOKUP пропонує кілька функцій, які роблять його надзвичайно хорошим для більш складних пошуків. У цьому прикладі ми хочемо встановити останню ціну товару за датою. Якби дані були відсортовані за датою у порядку зростання, це було б дуже просто. Однак у цьому випадку дані не сортуються.
За замовчуванням XLOOKUP поверне перший збіг у наборі даних. Щоб отримати останню відповідність, ми можемо встановити для необов’язкового аргументу search_mode значення -1, щоб XLOOKUP шукав «останній до першого». Однак ми не можемо використовувати цей підхід тут, оскільки немає гарантії, що остання ціна на товар з’являється останньою.
Натомість ми можемо встановити для необов’язкового аргументу match_mode значення -1, щоб змусити приблизний збіг "точного або наступного найменшого", і налаштувати значення пошуку та масив пошуку, як пояснено нижче. Формула в G5, скопійована вниз, така:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Обробляючи аргументи по одному, lookup_value є найбільшою (останньою) датою в даних:
MAX(date) // get max date value
Lookup_array виводиться з логічним виразом логіки:
(item=F5)*date
Порівнюючи кожен предмет зі значенням у F5, "Пояс", ми отримуємо масив значень TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
де значення TRUE представляють записи для "Пояса". Цей масив діє як фільтр. Коли він помножується на значення в названій даті діапазону , значення TRUE / FALSE обчислюються як 1 і 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Результат - масив, який містить лише нулі та дати для поясів:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Примітка: серійні номери є дійсними датами Excel.
Цей масив доставляється безпосередньо до XLOOKUP як аргумент lookup_array.
Return_array - це названа ціна діапазону (D5: D15)
Необов’язковий аргумент not_found не надається.
Режим Match_mode встановлений на -1, для точної відповідності або наступного найменшого елемента.
XLOOKUP шукає в масиві підстановки максимальне значення дати. Оскільки масив уже відфільтровано для виключення дат, не пов’язаних із "Поясом", XLOOKUP просто знаходить найкращий збіг (або точну дату, або наступну найменшу дату), яка відповідає останній даті.
Кінцевим результатом є ціна, пов’язана з останньою датою. Формула буде продовжувати працювати, коли дані сортуються в будь-якому порядку.
