Dynamic Array – XLOOKUP

Ceea ce ne doream în articolul trecut (https://bit.ly/2sQCWvL), s-a realizat din luna ianuarie: Microsoft a activat funcțiile Dynamic Array pentru toți utilizatorii de Office 365, nu numai pentru “Insideri”. Vom descrie în următoarele articole aceste noi funcții. Pentru astăzi, XLOOKUP.

Ca de obicei, plecăm de la o problemă și încercăm să identificăm elementele de rezolvare.

Problemă: Tabel de date și extragere informații în diverse situații.



Acces la fișierul de lucru: http://bit.ly/2UwxzO6



Problema 1. Avem un formular de date, unde la alegerea codului de produs (Cod_P) din F2 trebuie să obținem denumirea de produs (Den_P) și valoarea produsului (Valoare). După cum sunt poziționate datele în tabel vom folosi clasica funcție VLOOKUP. Astfel, în F3, respectiv F4, vom avea:

=VLOOKUP(F2;A2:C11;2;0), respectiv =VLOOKUP(F2;A2:C11;3;0)

Funcția VLOOKUP este limitată prin restricțiile pe care le are:
- informația cunoscută (codul de produs) trebuie să fie întotdeauna în prima coloană, deci cerința va fi întotdeauna la dreapta (denumire produs și valoare);
- formula trebuie să conțină FALSE sau 0 pentru o căutare exactă.
În acest caz, ne punem o a doua problemă.

Problema 2. Dacă informația cunoscută este Den_P, cum extragem cerința Cod_P, aflată la stânga?



Până acum foloseam funcțiile INDEX și MATCH (rezolvarea din F8).

=INDEX(A2:C11;MATCH(F7;B2:B11;0);MATCH(E8;A1:C1;0))

Să vedem în continuare utilizarea funcției XLOOKUP. Reluăm datele problemei într-o foaie nouă de calcul (XLOOKUP). Tabelul de date l-am trasformat prin Format as Table. Nu este doar un element de design. Vreau doar să folosesc avantajele referințelor structurate.



Să le luăm pe rând:
Lookup_value – elementul cunoscut (Cod_P sau Den_P)
Lookup_array – aria de date. Dacă la funcțiile VLOOKUP și HLOOKUP aria cuprindea coloana ce conținea elementul cunoscut (obligatoriu prima coloană) precum și celelalte coloane de unde doream să obținem rezultatul (obligatoriu în dreapta) acum lookup_array face referire doar la coloana ce conține elementul cunoscut.
Return_array – coloana de unde vrem să obținem informația (nu ne mai interesează poziția acesteia – stânga sau dreapta)
=XLOOKUP(F2;Table3[Cod_P];Table3[Den_P]) – rezolvarea la Den_P
=XLOOKUP(F2;Table3[Cod_P];Table3[Valoare]) – rezolvarea la Valoare

Pentru cea de a doua problemă, unde inițial am folosit INDEX și MATCH, rezolvarea vine tot din XLOOKUP, deoarece odată cu această funcție au dispărut elementele restrictive.
=XLOOKUP(F7;Table3[Den_P];Table3[Cod_P]) – rezolvarea la problema a doua, Cod_P

Următoarele elemente nu sunt obligatorii.
[if_not_found] – dacă elementul cunoscut nu se regăsește în listă, o să returneze #N/A sau puteți afișa un mesaj: “Nu sunt date!” (Atenție! Textul se trece între ghilimele sau nu, dacă utilizați interfața grafică a funcției – fx).



[match_mode] – așa cum spuneam anterior, o restricție la VLOOKUP era căutarea exactă (0 sau FALSE), implicit fiind valoarea 1 (TRUE), valoare aproximativă. La XLOOKUP, valoarea implicită este, în sfârșit, pe căutare exactă.
[search_mode] – se poate folosi pentru tipul și direcția căutării
Pentru partea de opționale ar fi bună rezolvarea unei probleme legate de: elementul cunoscut este aproximativ. Nu știm exact valoarea. O aproximăm la 500. Vrem să aflăm codul pentru această valoare.



=XLOOKUP(F18;Table3[Valoare];Table3[Cod_P];0;1;1)

Pentru data următoare funcția UNIQUE (alegerea valorilor unice dintr-o listă) și funcția SORT (sortarea elementelor extrase).