agwebrunner.com

El nou XLOOKUP d'Excel substituirà VLOOKUP i proporcionarà un poderós reemplaçament a una de les funcions més populars d'Excel. Aquesta nova funció resol algunes de les limitacions de VLOOKUP i té una funcionalitat addicional. Això és el que heu de saber.

Què és XLOOKUP?

La nova funció XLOOKUP té solucions per a algunes de les majors limitacions de VLOOKUP. A més, també substitueix HLOOKUP. Per exemple, XLOOKUP pot mirar a l'esquerra, per defecte coincideix exactament i us permet especificar un interval de cel·les en lloc d'un número de columna. VLOOKUP no és tan fàcil d'utilitzar ni tan versàtil. Us mostrarem com funciona tot.

De moment, XLOOKUP només està disponible per als usuaris del programa Insiders. Tothom pot unir-se al programa Insiders per accedir a les funcions més recents d'Excel tan aviat com estiguin disponibles. Aviat Microsoft començarà a publicar-lo a tots els usuaris de l'Office 365.

Com s'utilitza la funció XLOOKUP

Anem a submergir-nos directament amb un exemple de XLOOKUP en acció. Agafeu els exemples de dades següents. Volem retornar el departament de la columna F per a cada identificador de la columna A.

Aquest és un exemple clàssic de cerca de coincidències exactes. La funció XLOOKUP requereix només tres dades.

La imatge següent mostra XLOOKUP amb sis arguments, però només els tres primers són necessaris per a una coincidència exacta. Centrem-nos en ells:

• Valor_cerca: El que busqueu.
• Lookup_array: On mirar.
• Matriu_retorn: l'interval que conté el valor a retornar.

La fórmula següent funcionarà per a aquest exemple: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ F $ 2: $ F $ 8)

Ara explorem un parell d’avantatges que XLOOKUP té sobre VLOOKUP aquí.

No hi ha més número d'índex de columna

El cèlebre tercer argument de VLOOKUP era especificar el número de columna de la informació a retornar d'una matriu de taula. Això ja no és un problema perquè XLOOKUP us permet seleccionar l'interval des del qual voleu tornar (columna F en aquest exemple).

I no ho oblideu, XLOOKUP pot veure les dades que queden a la cel·la seleccionada, a diferència de VLOOKUP. Més informació a continuació.

A més, ja no teniu el problema d’una fórmula trencada quan s’insereixen columnes noves. Si això passés al full de càlcul, l'interval de retorn s'ajustaria automàticament.

La coincidència exacta és la predeterminada

Sempre va resultar confús a l’hora d’aprendre VLOOKUP per què calia especificar una coincidència exacta.

Afortunadament, XLOOKUP predetermina una coincidència exacta (la raó molt més comuna per utilitzar una fórmula de cerca). D’aquesta manera es redueix la necessitat de respondre a aquest cinquè argument i es garanteixen menys errors per part dels usuaris nous de la fórmula.

En resum, XLOOKUP fa menys preguntes que VLOOKUP, és més fàcil d'utilitzar i també és més durador.

XLOOKUP pot mirar cap a l’esquerra

Poder seleccionar un interval de cerca fa que XLOOKUP sigui més versàtil que VLOOKUP. Amb XLOOKUP, l’ordre de les columnes de la taula no té importància.

VLOOKUP es va limitar buscant a la columna més esquerra d’una taula i després tornant d’un nombre especificat de columnes a la dreta.

A l’exemple següent, hem de buscar un identificador (columna E) i retornar el nom de la persona (columna D).

La fórmula següent pot aconseguir-ho: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8)

Què fer si no es troba

Els usuaris de les funcions de cerca estan molt familiaritzats amb el missatge d’error # N / A que els saluda quan la seva funció VLOOKUP o la seva funció MATCH no troben el que necessiten. I sovint hi ha una raó lògica per a això.

Per tant, els usuaris investiguen ràpidament com amagar aquest error perquè no és correcte ni útil. I, per descomptat, hi ha maneres de fer-ho.

XLOOKUP ve amb el seu propi argument integrat "si no es troba" per gestionar aquests errors. Vegem-ho en acció amb l'exemple anterior, però amb un identificador mal escrit.

La fórmula següent mostrarà el text "ID incorrecte" en lloc del missatge d'error:= XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8, "Identificador incorrecte")

Ús de XLOOKUP per a una cerca d’interval

Tot i que no és tan comú com la concordança exacta, un ús molt eficaç d’una fórmula de cerca és buscar un valor en intervals. Preneu l’exemple següent. Volem retornar el descompte en funció de la quantitat invertida.

Aquesta vegada no busquem un valor específic. Hem de saber on s’inclouen els valors de la columna B dins dels intervals de la columna E. Això determinarà el descompte obtingut.

XLOOKUP té un cinquè argument opcional (recordeu, per defecte la concordança exacta) anomenat mode de concordança.

Podeu veure que XLOOKUP té capacitats més grans amb coincidències aproximades que la de VLOOKUP.

Hi ha l'opció de trobar la coincidència més propera menor que (-1) o més propera superior a (1) el valor cercat. També hi ha una opció per utilitzar caràcters comodins (2) com ara el? o el *. Aquesta configuració no està activada de manera predeterminada, tal com es feia amb VLOOKUP.

La fórmula d’aquest exemple retorna el valor més proper al valor cercat si no es troba una coincidència exacta: = XLOOKUP (B2, $ E $ 3: $ E $ 7, $ F $ 3: $ F $ 7 ,, - 1)

Tot i això, hi ha un error a la cel·la C7 on es retorna l’error # N / A (no s’ha utilitzat l’argument "si no es troba"). Això hauria d’haver retornat un descompte del 0% perquè la despesa de 64 no arriba als criteris per a cap descompte.

Un altre avantatge de la funció XLOOKUP és que no requereix que l'interval de cerca estigui en ordre ascendent com ho fa VLOOKUP.

Introduïu una fila nova a la part inferior de la taula de cerca i obriu la fórmula. Amplieu l'interval utilitzat fent clic i arrossegant les cantonades.

La fórmula corregeix immediatament l’error. No és un problema tenir el "0" a la part inferior de l'interval.

Personalment, encara ordenaria la taula per la columna de cerca. Tenir “0” a la part inferior em tornaria boig. Però el fet que la fórmula no es trenqui és genial.

XLOOKUP Substitueix també la funció HLOOKUP

Com es va esmentar, la funció XLOOKUP també és aquí per substituir HLOOKUP. Una funció per substituir-ne dues. Excel · lent!

La funció HLOOKUP és la cerca horitzontal, que s’utilitza per cercar al llarg de files.

No tan conegut com el seu germà VLOOKUP, però útil per a exemples com ara a continuació, on hi ha les capçaleres de la columna A i les dades es troben al llarg de les files 4 i 5.

XLOOKUP pot mirar en les dues direccions: columnes cap avall i també al llarg de files. Ja no necessitem dues funcions diferents.

En aquest exemple, la fórmula s'utilitza per retornar el valor de vendes relatiu al nom de la cel·la A2. Es veu al llarg de la fila 4 per trobar el nom i retorna el valor de la fila 5: = XLOOKUP (A2, B4: E4, B5: E5)

XLOOKUP es pot mirar des de baix

Normalment, cal buscar una llista per trobar la primera (sovint només) ocurrència d'un valor. XLOOKUP té un sisè argument anomenat mode de cerca. Això ens permet canviar la cerca per començar a la part inferior i buscar una llista per trobar l'última aparició d'un valor.

A l’exemple següent, ens agradaria trobar el nivell d’estoc de cada producte a la columna A.

La taula de cerca està ordenada per data i hi ha diverses comprovacions d’estoc per producte. Volem retornar el nivell d’estoc des de la darrera vegada que es va comprovar (última ocurrència de l’identificador de producte).

El sisè argument de la funció XLOOKUP proporciona quatre opcions. Estem interessats en fer servir l'opció "Cerca del primer al primer".

La fórmula completa es mostra aquí: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,,, - 1)

En aquesta fórmula, el quart i el cinquè argument van ser ignorats. És opcional i volíem que es coincidís exactament per defecte.

Round-Up

La funció XLOOKUP és l’esperada successora de les funcions VLOOKUP i HLOOKUP.

En aquest article es van utilitzar diversos exemples per demostrar els avantatges de XLOOKUP. Una d’elles és que XLOOKUP es pot utilitzar en fulls, llibres de treball i també amb taules. Els exemples es van mantenir senzills a l'article per ajudar-nos a entendre millor.

Degut a que aviat s’introduiran matrius dinàmics a Excel, també pot retornar un interval de valors. Sens dubte, això val la pena explorar-lo més.

Els dies de VLOOKUP estan numerats. XLOOKUP és aquí i aviat serà la fórmula de cerca de facto.