Обща формула
=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.
Режимът на съвпадение е зададен на -1 за точно съвпадение или следващия най-малък елемент.
XLOOKUP търси в масива за търсене максималната стойност на датата. Тъй като масивът вече е филтриран за изключване на дати, които не са свързани с „Пояс“, XLOOKUP просто намира най-доброто съвпадение (или точната дата, или следващата най-малка дата), която съответства на последната дата.
Крайният резултат е цената, свързана с последната дата. Формулата ще продължи да работи, когато данните са сортирани в произволен ред.
