La funció XLOOKUP a Excel

La funció XLOOKUP és una fórmula de Cerca i Referència que, si més no a dia d’avui, no ha estat traduïda al català. (si s’haguès seguit el mateix creiteri de traducció de CONSULV entenc que s’hauria d’haver anomenat CONSULX). En castellà ha estat traduïda com a BUSCARX

Aquesta funció s’incorpora a Office 365 per milorar les fórmules CONSULV i CONSULH.

Punts de millora de XLOOKUP respecte CONSULV I CONSULH

Pot buscar en rangs tant horitzontals com verticals: El que abans feiem amb 2 funcions, ara en tenim prou amb 1.

-Permet trobar valors que estiguin abans i després del valor buscat: Amb CONSULV, per exemple,el resultat sempre havia d’estar a la columna del valor trobat, o posterior. Mai en una columna anterior.

-Permet determinar un resultat en cas que el valor buscat no es trobi: Si CONSULV O CONSULH no troven el valor cercat, generen un error #ND. Per evitar-ho calia convinar-ho amb la funció SIERROR. En aquest post explico com fer-ho.

-Podem trobar el darrer valor coincident: En comptes de limitar-nos al primer, com en CONSULV o CONSULH

-El resultat pot ser de més d’una cel·la: Imaginem una taula on a una columna hi tenim diferents productes i en les altres columnes diferents caratacterístiques del producte com marca, model, preu, materials, etc. A partir dun XLOOKUP del producte podríem obtenir com a resultat la marca, el model i el preu, per exemple.

-Podem buscar per 2 criteris, a una columna i una fila: Retornant el resultat de la intersecció dels 2

Paràmetres de la funció XLOOKUP

La funció XLOOKUP té els següents arguments:

-Valor buscat: El valor que volem buscar (o la cel·la que conté aquest valor)

-Matriu a on buscar: Rang de cel·les en el qual busquem el valor

-Matriu retornada: Rang de cel·les en el qual hi ha el resultat que busquem i que tindrà la mateixa fila o columna (segons busquem) que el valor trobat

-Valor si no es troba (opcional): Podem indicar el valor (o la cel·la que conté aquest valor) que donarà com a resultat en cas de no trobar el valor buscat

-Tipus de coincidència (opcional) : Si no indiquem cap valor, el predeterminat és el 0.

0: Coincidència exacte. Valor per defecte

-1: Coincidència exacte. Si no trova el valor, agafa el proper més petit.

1: Coincidència exacte. Si no trova el valor, agafa el proper més gran.

2: Coincidència comodí. Per cercar amb caràcters comodí (* , ? ~ )

-Tipus de cerca: (opcional): Si no s’indica cap valor, el predeterminat és 1.

1: La cerca es realitza començant pel primer element.

-1: La cerca es realitza en ordre invers, començant pel darrer element.

2: Realitza una cerca binària. Cal que les dades estiguin ordenades de manera ascendent.

-2: Realitza una cerca binària. Cal que les dades estiguin ordenades de manera descendent

Què vol dir que Excel fa una cerca binària a XLOOKUP?

La cerca binària és un algoritme de cerca més eficient (i per tant ràpid), però que no té trasccendència en el resultat final (a no ser que les dades no estiguin correctament ordenades que aleshores pot oferir resultats erronis).

En l’algoritme de cerca habitual, Excel recorre tots els elements 1 a 1, fins a trobar el valor cercat. En canvi a la cerca binària Excel fa trams del 50% dels valors. Mirem un exemple:

Òbviament en cerques petites no s’aprecien millores però si el volum de dades és gran, el temps de resposta pot millorar significativament.

Exemple de de cerca vertical

Destaquem que la fórmula XLOOKUP a la cel·la H5 en el seu tercer argument (resultat retornat), conté un rang de més d’una columna (de la B a la E) i per tant la fórmula desborda i ofereix com a resultat 4 cel·les diferents.

Recordar que XLOOKUP, a diferència de CONSULV, permet oferir resultats en columnes anteriors (cap a l’esquerra) a la columna en la que busquem el resultat. En aquest exemple busquem els valors a la columna B i retornem com a resultat la columna A

Exemple de de cerca vertical amb valor no trobat

A l’exemple anterior, si el valor cercat no és troba XLOOKUP genera un error #N/D

Exemple de de cerca vertical amb valor no trobat i control d’error

Per evitar el cas anterior, cal utilitzar el quart argument de la funció XLOOKUP posant el resultat que volem que ofereixi en cas de no trobar el valor cercat

En aquest cas, però, el resultat només es dona a la cel·la H5, mentre que les cel·les I5, J5 i K5, queden en blanc. En cas que volguéssim que en aquestes cel·les també aparegués algun resultat, ho podem fer indicant en el quart argument de XLOOKUP, una matriu (pots trobar informació al respecte en aquest post). En aquest exemple, farem que en cas de no trobar resultat aparegui un guió “-“

Exemple de cerca horitzontal XLOOKUP

Exemple de cerca amb 2 criteris (fila i columna)

Aquí podem veure que el potencial de XLOOKUP és brutal. Antigament havíem de convinar les funcions INDEX i COINCIDIR per tal de tenir aquests resultats.

Aquesta imatge té l'atribut alt buit; el seu nom és image-15-1024x265.png

Observació: En el darrer parámetre del segon XLOOKUP en comptes del rang B2:H13 i apareix B2# que és la manera en que Excel fa referència a un rang desbordat. (en aquests post explico com fer referència a un rang desbordat). Això és perquè els números de la taula s’han generat automàticament amb la funció MATRIUALEAT que ha generat aquest rang desbordat amb els valors de venda ficticis.

Feu un comentari