L'Excel 2016 per als subscriptors d'Office 365 a Windows i Mac ara admet una nova funció XLOOKUP, presentada com un reemplaçament considerablement més senzill i més versàtil per a la funció de cerca vertical molt popular (encara que sovint difamada), VLOOKUP (no sé quina és la X a XLOOKUP). significa; extensiu, potser?).
Per a aquells de vosaltres que encara no esteu familiaritzats amb BUSCAR V (considerada la tercera funció més utilitzada just després de SUMA i MITJANA), aquesta funció cerca verticalment per fila a la columna més a l'esquerra d'una taula de cerca designada de dalt a baix fins que troba un valor en un columna de cerca designada per un número de compensació que coincideixi o superi el que esteu buscant. Tot i que és molt útil per localitzar elements concrets en una llista llarga o columna d'una taula de dades del vostre full de treball, la funció BUSCARV té diverses limitacions que no comparteixen aquesta nova funció de cerca, com BUSCAR XL:
La funció XLOOKUP té cinc arguments possibles, els tres primers són obligatoris i els dos últims opcionals, utilitzant la sintaxi següent:
XLOOKUP( valor_cerca , matriu_cerca , matriu_retorn ,[ mode_concorda ],[ mode_cerca ])
L' argument lookup_value obligatori designa el valor o l'element que esteu cercant. L' argument de matriu de cerca_requerit designa l'interval de cel·les que s'ha de cercar per aquest valor de cerca, i l' argument de matriu_retorn designa l'interval de cel·les que contenen el valor que voleu retornar quan Excel trobi una coincidència exacta.
* Tingueu en compte que quan designeu els arguments lookup_array i return_array a la vostra funció XLOOKUP, tots dos intervals han de ser de la mateixa longitud, en cas contrari Excel retornarà el #VALOR! error a la teva fórmula. Aquest és més el motiu pel qual utilitzeu noms d'interval o noms de columnes d'una taula de dades designada quan definiu aquests arguments en lloc d'assenyalar-los o escriure les seves referències de cel·les .
L' argument match_mode opcional pot contenir qualsevol dels quatre valors següents:
L' argument search_mode opcional pot contenir qualsevol dels quatre valors següents:
La millor manera d'entendre el poder i la versatilitat de la nova funció XLOOKUP és veure-la en acció en un full de treball d'Excel. A la figura següent, tinc un full de treball amb una taula senzilla de dades de vendes del 2019 organitzada per país. Per utilitzar XLOOKUP per retornar les vendes totals d'aquesta taula a la cel·la E4 en funció del país que introduïu a la cel·la D4 del full de treball, feu aquests passos:
Col·loqueu el cursor de la cel·la a la cel·la E4 del full de treball
Feu clic a l'opció Cerca i referència a la pestanya Fórmules seguida de CERCA XL a la part inferior del menú desplegable per obrir el seu quadre de diàleg Arguments de funció.
Feu clic a la cel·la D4 del full de treball per introduir la seva referència de cel·la al quadre de text de l'argument Lookup_value.
Premeu Tab per seleccionar el quadre de text de l'argument Lookup_array i, a continuació, feu clic a la cel·la A4 i manteniu premuda la tecla Maj mentre premeu Ctrl-fletxa cap avall per seleccionar A4:A8 com a interval per cercar (perquè l'interval A3:B8 es defineix com una taula de dades d'Excel, La taula 1[País] apareix al quadre de text en lloc de l'interval A4:A8).
Premeu Tab per seleccionar el quadre de text de l'argument Return_array i, a continuació, feu clic a la cel·la B4 i manteniu premuda la tecla Maj mentre premeu Ctrl-fletxa cap avall per seleccionar B4:B8 com a interval que conté els valors que s'han de tornar en funció dels resultats de la cerca (que apareix com a Taula 1[Vendes totals] al quadre de text).
Feu clic a D'acord per introduir la fórmula XLOOKUP a la cel·la E4.
Creació d'una fórmula amb XLOOKUP a la cel·la E4 que retorni les vendes en funció del país introduït a la cel·la D4.
Excel introdueix la fórmula XLOOKUP a la cel·la E4 del full de treball i retorna 4900 com a resultat perquè Costa Rica s'introdueix actualment a la cel·la de cerca D4 i, com podeu veure a la taula de vendes de 2019, aquestes són realment les vendes totals realitzades per a aquest país.
Com que XLOOKUP funciona de dreta a esquerra igual de bé que d'esquerra a dreta, podeu utilitzar aquesta funció igual de bé per retornar el país d'aquesta taula de vendes en funció d'una xifra de vendes concreta. La figura següent us mostra com ho feu. Aquesta vegada, creeu la fórmula XLOOKUP a la cel·la D4 i designeu el valor introduït a la cel·la E4 (11.000, en aquest cas) com a argument cerca_valor.
A més, introduïu -1 com a argument match_mode per anul·lar la concordança exacta predeterminada de la funció, de manera que Excel retorni el país amb una coincidència exacta amb el valor de vendes introduït a la cel·la de cerca E4 o el que té les vendes totals més baixes (Mèxic amb 10.000 dòlars en aquest cas, ja que no hi ha cap país en aquesta taula amb 11.000 dòlars de vendes totals). Sense designar un argument match_mode per a aquesta fórmula, Excel retornaria #NA com a resultat, perquè no hi ha una coincidència exacta amb 11.000 dòlars en aquesta taula de vendes.
Creació d'una fórmula amb XLOOKUP a la cel·la D4 que retorni el país en funció de les vendes introduïdes a la cel·la E4
Com que la funció XLOOKUP és igual de còmoda cercant horitzontalment per columna com en vertical per fila, podeu utilitzar-la per crear una fórmula que realitza una cerca bidireccional (substituint la necessitat de crear una fórmula que combini les funcions INDEX i MATCH com en el passat). La figura següent, que conté la taula de programació de producció de 2019 per als números de peça, AB-100 a AB-103 per als mesos d'abril a desembre, us mostra com es fa.
Creació d'una fórmula amb funcions XLOOKUP imbricades per retornar el nombre d'unitats produïdes per a una peça en un mes concret
A la cel·la B12, vaig crear la fórmula següent:
=XLOOKUP(cerca_part,$A$3:$A$6,XLOOKUP(cerca_data,$B$2:$J$2,$B$3:$J$6))
Aquesta fórmula comença definint una funció XLOOKUP que cerca verticalment per fila una coincidència exacta amb l'entrada de part feta a la cel·la anomenada part_lookup (cel·la B10, en aquest cas) a l'interval de cel·les $A$3:$A$6 de la taula de producció. . Tingueu en compte, però, que l'argument return_array per a aquesta funció LOOKUP original és en si mateixa una segona funció XLOOKUP.
Aquesta segona funció XLOOKUP imbricada cerca l'interval de cel·les $B$2:$J$2 horitzontalment per columna per trobar una coincidència exacta amb l'entrada de data feta a la cel·la anomenada data_lookup (cel·la B11, en aquest cas). L'argument return_array per a aquesta segona funció XLOOKUP imbricada és $B$3:$J$6, l'interval de cel·les de tots els valors de producció de la taula.
La manera com funciona aquesta fórmula és que Excel calcula primer el resultat de la segona funció XLOOKUP imbricada realitzant una cerca horitzontal que, en aquest cas, retorna la matriu a l'interval de cel·les D3: D6 de la columna Jun-19 (amb els valors: 438, 153, 306 i 779) com a resultat. Aquest resultat, al seu torn, es converteix en l'argument return_array per a la funció XLOOKUP original que realitza una cerca vertical per fila d'una coincidència exacta amb l'entrada del número de peça feta a la cel·la B11 (anomenada part_lookup). Com que, en aquest exemple, aquesta cel·la part_lookup conté AB-102, la fórmula retorna només el valor de producció del 19 de juny, 306, a partir del resultat de la segona funció XLOOKUP següent.
Aquí ho tens! Un primer cop d'ull a XLOOKUP, una nova funció de cerca potent, versàtil i bastant fàcil d'utilitzar que no només pot fer les cerques d'un sol valor realitzades per les funcions BUSCARV i HLOOKUP, sinó també les cerques de valors bidireccionals realitzades combinant el També les funcions INDEX i MATCH.
* Malauradament, la funció XLOOKUP no és compatible amb versions anteriors de Microsoft Excel que només admeten les funcions VLOOKUP i HLOOKUP o compatible amb les versions actuals que encara no l'inclouen com una de les seves funcions de cerca, com ara Excel 2019 i Excel Online . Això vol dir que si compartiu un llibre de treball que conté fórmules XLOOKUP amb companys de feina o clients que utilitzen una versió d'Excel que no inclou aquesta nova funció de cerca, totes aquestes fórmules tornaran #NAME? valors d'error quan obren el seu full de treball.
La funció XLOOKUP cerca un interval o una matriu i, a continuació, retorna l'element corresponent a la primera concordança que troba. Si no hi ha cap coincidència, llavors XLOOKUP pot tornar la concordança (aproximada) més propera.
=XLOOKUP(valor_de_cerca, matriu_de_cerca, matriu_de_retorn, [if_not_found], [mode_concordança], [mode_cerca])
|
Argumentació |
Descripció |
|---|---|
|
valor_de_cerca Obligatori* |
El valor que cal cercar |
|
matriu_cerques Obligatori |
La matriu o l'interval a cercar |
|
matriu_retorn Obligatori |
La matriu o l'interval a retornar |
|
[si_no_trobat] Opcional |
Quan no es trobi cap coincidència vàlida, retorneu el text [if_not_found] que proporcioneu. Si no es troba cap coincidència vàlida i falta [if_not_found], #N/A es retorna. |
|
[match_mode] Opcional |
Especifiqueu el tipus de concordança: 0 - Coincidència exacta. Si no se'n troba cap, retorneu #N/A. Aquesta és la predeterminada. -1 - Coincidència exacta. Si no n'hi ha cap, retorneu el següent element més petit. 1 - Coincidència exacta. Si no n'hi ha cap, retorneu el següent element més gran. 2 - Una concordança de comodins on *, ? i ~ tenen un significat especial. |
|
[mode_cerca] Opcional |
Especifiqueu el mode de cerca que voleu utilitzar: 1 - Feu una cerca començant pel primer element. Aquesta és la predeterminada. -1 - Feu una cerca inversa començant per l'últim element. 2: feu una cerca binària que es basa en que lookup_array s'ordena en ordre creixent. Si no s'ordena, es retornaran resultats no vàlids. -2 - Feu una cerca binària que es basa en que lookup_array s'ordena en ordre descendente. Si no s'ordena, es retornaran resultats no vàlids. |
Exemple 1 fa servir XLOOKUP per cercar el nom d'un país en un interval i, a continuació, retornar el seu codi telefònic de país. Inclou el valor_de_cerca (cel·la F2), matriu_de_cerca (interval B2: B11) i arguments return_array (rang D2:D11). No inclou l'argument match_mode , ja que XLOOKUP produeix una coincidència exacta de manera predeterminada.
Nota: XLOOKUP utilitza una matriu de cerca i una matriu de retorn, mentre que VLOOKUP utilitza una matriu de taula única seguida d'un número d'índex de columna. La fórmula equivalent de CERCA V en aquest cas seria: =CercaV(F2,B2:D11,3,FALSE)
————————————————————————————
Exemple 2 cerca la informació dels empleats a partir d'un número d'identificació d'empleat. A diferència de BUSCARV, BUSCAR XL pot retornar una matriu amb diversos elements, de manera que una única fórmula pot retornar tant el nom de l'empleat com el departament des de les cel·les C5:D14.
————————————————————————————
Exemple 3 afegeix un argument if_not_found a l'exemple anterior.
————————————————————————————
Exemple 4 cerca a la columna C els ingressos personals introduïts a la cel·la E2 i troba un tipus impositiu coincident a la columna B. Estableix el if_not_found argument per tornar 0 (zero) si no es troba res. L'argument match_mode s'estableix en 1, la qual cosa significa que la funció buscarà una coincidència exacta i, si no la troba, retornarà el següent element més gran. Finalment, l'argument search_mode s'estableix en 1, el que significa la funció cercarà des del primer element fins a l'últim.
Nota: la columna XARRAY lookup_array es troba a la dreta de columna, mentre que BUSCAR V només pot mirar d'esquerra a dreta.return_array
————————————————————————————
Exemple 5 utilitza una funció XLOOKUP imbricada per fer una coincidència vertical i horitzontal. Primer cerca Guany brut a la columna B i després cerca Qtr1 a la fila superior de la taula (interval C5:F5) i finalment retorna el valor a la intersecció dels dos. Això és semblant a utilitzar les funcions INDEX i CONFIGURACIÓ juntes.
Consell: També podeu fer servir XLOOKUP per substituir la funció HLOOKUP .
Nota: La fórmula de les cel·les D3:F3 és: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3, $C5:$G5,$C6:$G17)).
————————————————————————————
L'Exemple 6 fa servir la funció SUMA i dues funcions XLOOKUP imbricades per sumar tots els valors entre dos intervals. En aquest cas, volem sumar els valors del raïm, els plàtans i incloure les peres, que estan entre els dos.
La fórmula de la cel·la E3 és: =SUMA(CERCAXL(B3;B6:B10;E6:E10):CERCAXL(C3;B6:B10,E6:E10))
Com funciona? XLOOKUP retorna un interval, de manera que quan es calcula, la fórmula acaba semblant així: =SUMA($E$7:$E$9). Podeu veure com funciona això pel vostre compte seleccionant una cel·la amb una fórmula XLOOKUP similar a aquesta i, a continuació, seleccioneu Fórmules > Auditoria de fórmules > Avalua la fórmula i, a continuació, selecciona Avalua per passar pel càlcul.
Com bloquejar Microsoft Word d'obrir fitxers en mode de només lectura a Windows Microsoft Word obre fitxers en mode de només lectura, cosa que fa que sigui impossible editar-los? No us preocupeu, els mètodes es mostren a continuació
Com corregir errors en imprimir documents incorrectes de Microsoft Word Els errors en imprimir documents de Word amb tipus de lletra canviats, paràgrafs desordenats, falta de text o contingut perdut són força habituals. No obstant això, no
Si heu utilitzat el llapis o el marcador per dibuixar a les vostres diapositives de PowerPoint durant una presentació, podeu desar els dibuixos per a la següent presentació o esborrar-los de manera que la propera vegada que el mostreu, començareu amb diapositives de PowerPoint netes. Seguiu aquestes instruccions per esborrar dibuixos a llapis i ressaltadors: Esborrant línies una a […]
La biblioteca d'estils conté fitxers CSS, fitxers de llenguatge de full d'estil extensible (XSL) i imatges utilitzades per pàgines mestres predefinides, dissenys de pàgina i controls a SharePoint 2010. Per localitzar fitxers CSS a la biblioteca d'estils d'un lloc de publicació: Trieu Accions del lloc→Visualitza Tot el contingut del lloc. Apareix el contingut del lloc. La biblioteca d'estil es troba a […]
No desbordeu el vostre públic amb números gigantesques. A Microsoft Excel, podeu millorar la llegibilitat dels vostres taulers i informes formatant els vostres números perquè apareguin en milers o milions.
Apreneu a utilitzar les eines de xarxes socials de SharePoints que permeten a persones i grups comunicar-se, col·laborar, compartir i connectar-se.
Les dates julianes s'utilitzen sovint en entorns de fabricació com a marca de temps i referència ràpida per a un número de lot. Aquest tipus de codificació de dates permet als minoristes, consumidors i agents de serveis identificar quan es va fabricar un producte i, per tant, l'antiguitat del producte. Les dates julianes també s'utilitzen en programació, militars i astronomia. Diferents […]
Podeu crear una aplicació web a Access 2016. Què és, de totes maneres, una aplicació web? Bé, web vol dir que està en línia i l'aplicació és només una abreviatura de "aplicació". Una aplicació web personalitzada és una aplicació de base de dades en línia a la qual s'accedeix des del núvol mitjançant un navegador. Creeu i manteniu l'aplicació web a la versió d'escriptori […]
La majoria de les pàgines del SharePoint 2010 mostren una llista d'enllaços de navegació a la barra d'inici ràpid al costat esquerre de la pàgina. La barra d'inici ràpid mostra enllaços al contingut del lloc destacat, com ara llistes, biblioteques, llocs i pàgines de publicació. La barra d'inici ràpid inclou dos enllaços molt importants: Enllaç de tot el contingut del lloc: el […]
Per a problemes senzills, Solver a Excel sol trobar ràpidament els valors òptims de la variable Solver per a la funció objectiu. Però, en alguns casos, Solver té problemes per trobar els valors de la variable Solver que optimitzen la funció objectiu. En aquests casos, Solver normalment mostra un missatge o un missatge d'error que descriu o discuteix el problema que […]
