Tots els secrets de la funció BUSCAR V d'Excel per trobar dades en una taula i extreure-les en una altra

2024 Autora: Malcolm Clapton | [email protected]. Última modificació: 2023-12-17 03:49

Després de llegir l'article, no només aprendràs a trobar dades en un full de càlcul Excel i extreure'ls en un altre, sinó també tècniques que es poden utilitzar juntament amb la funció BUSCAR V.

Quan es treballa en Excel, molt sovint cal trobar dades en una taula i extreure-les en una altra. Si encara no saps com fer-ho, després de llegir l'article, no només aprendràs a fer-ho, sinó que també descobriràs en quines condicions pots treure el màxim rendiment del sistema. Es tenen en compte la majoria de les tècniques molt efectives que s'han d'utilitzar juntament amb la funció BUSCARV.

Fins i tot si fa anys que utilitzeu la funció BUSCARV, amb un alt grau de probabilitat, aquest article us serà útil i no us deixarà indiferent. Per exemple, com a especialista en informàtica i després cap d'informàtica, fa 15 anys que utilitzo VLOOKUP, però només he aconseguit fer front a tots els matisos ara, quan vaig començar a ensenyar Excel a la gent de manera professional.

CERCA V és una abreviatura de vvertical NSinspecció. De la mateixa manera, BUSCAR V - CERCA Vertical. El mateix nom de la funció ens indica que cerca a les files de la taula (verticalment - iterant sobre les files i fixant la columna) i no a les columnes (horitzontalment - iterant sobre les columnes i fixant la fila). Cal tenir en compte que VLOOKUP té una germana, un aneguet lleig que mai es convertirà en cigne, aquesta és la funció HLOOKUP. HLOOKUP, a diferència de VLOOKUP, realitza cerques horitzontals, però el concepte d'Excel (i de fet el concepte d'organització de dades) implica que les vostres taules tenen menys columnes i moltes més files. És per això que hem de cercar per files moltes vegades més sovint que per columnes. Si utilitzeu la funció HLO massa sovint a Excel, és probable que no hàgiu entès alguna cosa en aquesta vida.

Sintaxi

La funció BUSCARV té quatre paràmetres:

= BUSCAR V (;; [;]), aquí:

- el valor desitjat (poques vegades) o una referència a una cel·la que conté el valor desitjat (la gran majoria dels casos);

- referència a un rang de cel·les (matriu bidimensional), a la columna PRIMERA (!) de la qual es cercarà el valor del paràmetre;

- número de columna en l'interval des del qual es retornarà el valor;

- aquest és un paràmetre molt important que respon a la pregunta de si la primera columna de l'interval està ordenada en ordre ascendent. Si la matriu està ordenada, especifiquem el valor TRUE o 1, en cas contrari - FALSE o 0. Si s'omet aquest paràmetre, el valor predeterminat és 1.

Aposto que molts dels que coneixen la funció de BUSCAR V com a escamosa, després de llegir la descripció del quart paràmetre, poden sentir-se incòmodes, ja que estan acostumats a veure-ho d'una forma una mica diferent: normalment parlen d'una coincidència exacta quan cerca (FALSE o 0) o una exploració d'interval (VERTADER o 1).

Ara cal esforçar-se i llegir el paràgraf següent diverses vegades fins que sentiu el significat del que es va dir fins al final. Cada paraula és important allà. Els exemples us ajudaran a esbrinar-ho.

Com funciona exactament la fórmula VLOOKUP?

• Tipus de fórmula I. Si s'omet l'últim paràmetre o s'especifica igual a 1, la BUSCAR V suposa que la primera columna s'ordena en ordre ascendent, de manera que la cerca s'atura a la fila que immediatament precedeix la línia que conté el valor més gran que el desitjat … Si no es troba aquesta cadena, es retorna l'última fila de l'interval.

  

• Fórmula II. Si l'últim paràmetre s'especifica com a 0, VLOOKUP escaneja la primera columna de la matriu seqüencialment i immediatament atura la cerca quan es troba la primera coincidència exacta amb el paràmetre, en cas contrari el codi d'error # N / A (# N / A) es retorna.

  

Fluxos de treball de fórmules

VPR tipus I

VPR tipus II

Corol·laris per a fórmules de la forma I

1. Les fórmules es poden utilitzar per distribuir valors entre intervals.
2. Si la primera columna conté valors duplicats i s'ordena correctament, es retornarà l'última de les files amb valors duplicats.
3. Si cerqueu un valor que òbviament és més gran que el que pot contenir la primera columna, podeu trobar fàcilment l'última fila de la taula, que pot ser força valuosa.
4. Aquesta vista retornarà l'error # N / A només si no troba un valor inferior o igual al desitjat.
5. És bastant difícil entendre que la fórmula retorna els valors incorrectes si la vostra matriu no està ordenada.

Corol·laris per a fórmules de tipus II

Si el valor desitjat apareix diverses vegades a la primera columna de la matriu, la fórmula seleccionarà la primera fila per a la recuperació de dades posterior.

Rendiment VLOOKUP

Has arribat al clímax de l'article. Sembla, bé, quina diferència hi ha si especifico zero o un com a darrer paràmetre? Bàsicament, tothom indica, per descomptat, zero, ja que això és força pràctic: no cal que us preocupeu per ordenar la primera columna de la matriu, podeu veure immediatament si el valor s'ha trobat o no. Però si teniu diversos milers de fórmules BUSCAR V al vostre full, notareu que BUSCAR V II és lenta. Al mateix temps, normalment tothom comença a pensar:

• Necessito un ordinador més potent;
• Necessito una fórmula més ràpida, per exemple, molta gent sap sobre INDEX + MATCH, que suposadament és més ràpida entre un 5-10%.

I poca gent pensa que tan bon punt comenceu a utilitzar VLOOKUP del tipus I i us assegureu que la primera columna s'ordena per qualsevol mitjà, la velocitat de VLOOKUP augmentarà 57 vegades. Escric amb paraules: CINQUANTA-SET VEGADES! No el 57%, sinó el 5.700%. Vaig comprovar aquest fet de manera bastant fiable.

El secret d'un treball tan ràpid rau en el fet que es pot aplicar un algorisme de cerca extremadament eficient en una matriu ordenada, que s'anomena cerca binària (mètode de la meitat, mètode de la dicotomia). Per tant, BUSCAR V del tipus I l'aplica, i BUSCAR V del tipus II cerca sense cap optimització. El mateix passa amb la funció MATCH, que inclou un paràmetre similar, i la funció LOOKUP, que només funciona amb matrius ordenades i s'inclou a Excel per a la compatibilitat amb Lotus 1-2-3.

Inconvenients de la fórmula

Els desavantatges de VLOOKUP són evidents: en primer lloc, cerca només a la primera columna de la matriu especificada i, en segon lloc, només a la dreta d'aquesta columna. I com enteneu, pot passar que la columna que conté la informació necessària estigui a l'esquerra de la columna en la qual mirarem. La ja esmentada combinació de fórmules INDEX + MATCH no té aquest inconvenient, la qual cosa la converteix en la solució més flexible per extreure dades de taules en comparació amb VLOOKUP (VLOOKUP).

Alguns aspectes de l'aplicació de la fórmula a la vida real

Cerca d'intervals

Una il·lustració clàssica d'una cerca de rang és la tasca de determinar un descompte per mida de la comanda.

Cerca cadenes de text

Per descomptat, VLOOKUP no només busca números, sinó també text. Cal tenir en compte que la fórmula no distingeix entre cas de caràcters. Si utilitzeu comodins, podeu organitzar una cerca difusa. Hi ha dos comodins: "?" - substitueix qualsevol caràcter en una cadena de text, "*" - substitueix qualsevol nombre de caràcters.

Espais de lluita

Sovint es planteja la qüestió de com resoldre el problema dels espais addicionals en cercar. Si encara es pot esborrar la taula de cerca, el primer paràmetre de la fórmula BUSCARV no sempre depèn de vostè. Per tant, si hi ha el risc d'obstruir les cel·les amb espais addicionals, podeu utilitzar la funció TRIM per esborrar-lo.

Format de dades diferent

Si el primer paràmetre de la funció BUSCAR V fa referència a una cel·la que conté un número, però que s'emmagatzema a la cel·la com a text, i la primera columna de la matriu conté números en el format correcte, la cerca fallarà. La situació contrària també és possible. El problema es pot resoldre fàcilment traduint el paràmetre 1 al format requerit:

= CERCA V (−− D7; Productes! $ A $ 2: $ C $ 5; 3; 0) - si D7 conté text i la taula conté números;

= CERCA V (D7 i ""); Productes! $ A $ 2: $ C $ 5; 3; 0) - i viceversa.

Per cert, podeu traduir text a un número de diverses maneres alhora, trieu:

• Doble negació -D7.
• Multiplicació per un D7 * 1.
• Suma zero D7 + 0.
• Pujada a la primera potència D7 ^ 1.

La conversió d'un número a text es fa mitjançant la concatenació amb una cadena buida, que obliga Excel a convertir el tipus de dades.

Com suprimir # N / A

Això és molt convenient fer-ho amb la funció IFERROR.

Per exemple: = IFERROR (VLOOKUP (D7; Productes! $ A $ 2: $ C $ 5; 3; 0); "").

Si BUSCARV retorna el codi d'error # N / A, aleshores IFERROR l'interceptarà i substituirà el paràmetre 2 (en aquest cas, una cadena buida), i si no es produeix cap error, aquesta funció fingirà que no existeix en absolut, però només hi ha VLOOKUP que ha retornat un resultat normal.

Matriu

Sovint s'obliden de fer la referència de la matriu absoluta, i quan estiren la matriu "flota". Recordeu utilitzar $ A $ 2: $ C $ 5 en lloc d'A2: C5.

És una bona idea col·locar la matriu de referència en un full separat del llibre de treball. No es posa sota els peus, i serà més segur.

Una idea encara millor seria declarar aquesta matriu com a rang amb nom.

Molts usuaris, quan especifiquen una matriu, utilitzen una construcció com A: C, especificant columnes senceres. Aquest enfocament té dret a existir, ja que us estalvieu d'haver de fer un seguiment del fet que la vostra matriu inclou totes les cadenes necessàries. Si afegiu files al full amb la matriu original, no cal ajustar l'interval especificat com a A: C. Per descomptat, aquesta construcció sintàctica obliga Excel a fer una mica més de feina que especificar el rang exactament, però aquesta sobrecàrrega es pot descuidar. Estem parlant de centèsimes de segon.

Bé, a la vora del geni - per organitzar la matriu en el formulari.

Utilitzant la funció COLUMN per especificar la columna a extreure

Si la taula a la qual esteu recuperant dades mitjançant BUSCARV té la mateixa estructura que la taula de cerca, però simplement conté menys files, podeu utilitzar la funció COLUMN () a BUSCARV per calcular automàticament els números de les columnes que cal recuperar. En aquest cas, totes les fórmules VLOOKUP seran iguals (ajustades per al primer paràmetre, que canvia automàticament)! Tingueu en compte que el primer paràmetre té una coordenada de columna absoluta.

Creació d'una clau composta amb & "|" &

Si es fa necessari cercar per diverses columnes alhora, cal fer una clau composta per a la cerca. Si el valor de retorn no fos textual (com és el cas del camp "Codi"), sinó numèric, llavors la fórmula SUMIFS més convenient seria adequada per a això i la clau de columna composta no seria necessària en absolut.

Aquest és el meu primer article per a un Lifehacker. Si us ha agradat, us convido a visitar-lo i també llegir amb molt de gust als comentaris els vostres secrets d'utilitzar la funció BUSCARV i similars. Gràcies.:)