Formula generică
=XLOOKUP(max,dates,results,,-1) // latest match by date
rezumat
Pentru a obține cea mai recentă potrivire dintr-un set de date după dată, puteți utiliza XLOOKUP în modul de potrivire aproximativ setând match_mode la -1. În exemplul prezentat, formula din G5, copiată, este:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
unde data (C5: C15), articolul (B5: B15) și prețul (D5: D15) sunt denumite intervale.
Explicaţie
XLOOKUP oferă mai multe funcții care îl fac excepțional de bun pentru căutări mai complicate. În acest exemplu, dorim cel mai recent preț pentru un articol după dată. Dacă datele ar fi sortate după dată în ordine crescătoare, acest lucru ar fi foarte simplu. Cu toate acestea, în acest caz, datele sunt nesortate.
În mod implicit, XLOOKUP va returna primul meci dintr-un set de date. Pentru a obține ultima potrivire, putem seta argumentul opțional search_mode, la -1 pentru a determina XLOOKUP să caute „ultimul până la primul”. Cu toate acestea, nu putem folosi această abordare aici, deoarece nu există nicio garanție că ultimul preț pentru un articol apare ultima.
În schimb, putem seta argumentul opțional match_mode la -1 pentru a forța o potrivire aproximativă de „exact sau următorul cel mai mic” și putem ajusta valoarea de căutare și matricea de căutare așa cum se explică mai jos. Formula din G5, copiată în jos, este:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Lucrând prin argumente unul câte unul, lookup_value este cea mai mare (cea mai recentă) dată din date:
MAX(date) // get max date value
Lookup_array este derivat cu o expresie logică booleană:
(item=F5)*date
Comparând fiecare articol cu valoarea din F5, „Centură”, obținem o serie de valori ADEVĂRATE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
unde valorile TRUE reprezintă intrări pentru „Belt”. Această matrice acționează ca un filtru. Când este înmulțit cu valorile din data intervalului denumit , valorile TRUE / FALSE sunt evaluate la 1 și 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Rezultatul este un tablou care conține doar zerouri și date pentru curele:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Notă: numerele de serie sunt date Excel valabile.
Această matrice este livrată direct către XLOOKUP ca argument lookup_array.
Return_array este prețul intervalului denumit (D5: D15)
Argumentul opțional not_found nu este furnizat.
Match_mode este setat la -1, pentru potrivirea exactă sau următorul cel mai mic element.
XLOOKUP caută prin matricea de căutare valoarea maximă a datei. Deoarece matricea a fost deja filtrată pentru a exclude datele care nu sunt asociate cu „Belt”, XLOOKUP găsește pur și simplu cea mai bună potrivire (fie data exactă, fie următoarea cea mai mică dată) care corespunde celei mai recente date.
Rezultatul final este prețul asociat cu cea mai recentă dată. Formula va continua să funcționeze atunci când datele sunt sortate în orice ordine.
