La funció BUSCAR V sempre ha estat una de les funcions més potents d'Excel. Us permet cercar valors a la primera columna d'una taula i retornar valors dels camps de la dreta. Però Excel també té una funció anomenada XLOOKUP, que us permet cercar un valor a qualsevol columna o fila i retornar dades de qualsevol altra columna.
Com funciona XLOOKUP
La funció XLOOKUP és molt més fàcil d'utilitzar que la funció VLOOKUP, perquè en comptes d'especificar un valor per a la columna de resultats, podeu especificar tot l'interval.
La funció també us permet cercar tant una columna com una fila, localitzant el valor a la cel·la que es creua.
Els paràmetres de la funció XLOOKUP són els següents:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: el valor que voleu cercar
- lookup_array: la matriu (columna) que voleu cercar
- return_array: el resultat (columna) del qual voleu recuperar un valor de
- match_mode (opcional): seleccioneu una coincidència exacta (0), una coincidència exacta o el valor més petit següent (-1) o una coincidència amb comodí (2).
- search_mode (opcional): seleccioneu si voleu cercar començant pel primer element de la columna (1), l'últim element de la columna (-1), cerca binària ascendent (2) o cerca binària descendent (-2).
Les següents són algunes de les cerques més habituals que podeu fer amb la funció XLOOKUP.
Com cercar un únic resultat amb XLOOKUP
La manera més fàcil d'utilitzar XLOOKUP és cercar un únic resultat mitjançant un punt de dades d'una columna.
-
Aquest full de càlcul d'exemple és una llista de comandes enviades pels representants de vendes, que inclou l'article, el nombre d'unitats, el cost i la venda total.
Image -
Si voleu trobar la primera venda a la llista enviada per un representant de vendes específic, podeu crear una funció XLOOKUP que cerqui un nom a la columna Representant. La funció retornarà el resultat de la columna Total. La funció XLOOKUP per a això és:
=RECUPA XL(I2, C2:C44, G2:G44, 0, 1)
- I2: apunta a la Nom del representant cel·la de cerca
- C2:C44: aquesta és la columna Representant, que és la matriu de cerca
- G2:G33: aquesta és la columna Total, que és la matriu de retorn
- 0: selecciona una coincidència exacta
- 1: selecciona la primera coincidència als resultats
-
Quan premeu Introduïu i escriviu el nom d'un representant de vendes, la cel·la del resultat total us mostrarà el primer resultat de la taula per a aquest representant de vendes.
Image -
Si voleu cercar la venda més recent (ja que la taula està ordenada per data en ordre invers), canvieu l'últim argument XLOOKUP a - 1, que començarà cerqueu des de l'última cel·la de la matriu de cerca i us proporcioneu aquest resultat.
Image -
Aquest exemple mostra una cerca similar que podríeu realitzar amb una funció BUSCAR V utilitzant la columna Rep com a primera columna de la taula de cerca. Tanmateix, XLOOKUP us permet cercar qualsevol columna en qualsevol direcció. Per exemple, si voleu trobar el representant de vendes que va vendre la primera comanda de Binder de l'any, haureu d'utilitzar la funció XLOOKUP següent:
=RECUPA XL(I2, D2:D44, C2:C44, 0, 1)
- D2: apunta a la cel·la de cerca d'elements
- D2:D44: aquesta és la columna Element, que és la matriu de cerca
- C2:C44: aquesta és la columna Rep, que és la matriu de retorn a l'esquerra de la matriu de cerca
- 0: selecciona una coincidència exacta
- 1: selecciona la primera coincidència als resultats
-
Aquesta vegada, el resultat serà el nom del representant de vendes que va vendre la primera comanda d'enquadernador de l'any.
Image
Realitza una concordança vertical i horitzontal amb XLOOKUP
Una altra capacitat de XLOOKUP que no és capaç de VLOOKUP és la capacitat de realitzar una cerca tant vertical com horitzontal, el que significa que podeu cercar un element per una columna i també per una fila.
Aquesta funció de cerca dual és un reemplaçament eficaç d' altres funcions d'Excel com INDEX, MATCH o HLOOKUP.
-
A l'exemple de full de càlcul següent, les vendes de cada representant de vendes es divideixen per trimestre. Si volguéssiu veure les vendes del tercer trimestre d'un representant de vendes específic, sense la funció XLOOKUP, aquest tipus de cerca seria difícil.
Image -
Amb la funció XLOOKUP, aquest tipus de cerca és fàcil. Mitjançant la següent funció XLOOKUP, podeu cercar les vendes del tercer trimestre per a un representant de vendes específic:
=CERCA XL(J2, B2:B42, CERCA XL(K2, C1:H1, C2:H42))
- J2: apunta a la cel·la de cerca del representant
- B2:B42: aquesta és la columna Element, que és la matriu de cerca de columnes
- K2: apunta a la cel·la de cerca Quarter
- C1:H1: aquesta és la matriu de cerca de files
- C2:H42: aquesta és la matriu de cerca per a l'import en dòlars de cada trimestre
Aquesta funció XLOOKUP imbricada identifica primer el representant de vendes, i la següent funció XLOOKUP identifica el trimestre desitjat. El valor de retorn will és la cel·la on s'intercepten aquests dos.
-
El resultat d'aquesta fórmula són els guanys del primer trimestre del representant amb el nom de Thompson.
Image
Ús de la funció XLOOKUP
La funció XLOOKUP només està disponible per als subscriptors d'Office Insider, però aviat s'implementarà a tots els subscriptors de Microsoft 365.
Si voleu provar la funció vos altres mateixos, podeu convertir-vos en un Office Insider. Seleccioneu Fitxer > Compte i, a continuació, seleccioneu el menú desplegable Office Insider per subscriure-us.
Un cop us uniu al programa Office Insider, la vostra versió instal·lada d'Excel rebrà totes les últimes actualitzacions i podreu començar a utilitzar la funció XLOOKUP.
