agwebrunner.com

VLOOKUP és una de les funcions més útils d'Excel i també és una de les que menys s'entenen. En aquest article, desmitificem VLOOKUP a través d’un exemple de la vida real. Crearem un element útil Plantilla de factura per a una empresa fictícia.

VLOOKUP és un Excel funció. Aquest article suposarà que el lector ja té una bona comprensió de les funcions d'Excel i pot utilitzar funcions bàsiques com SUM, AVERAGE i AVUI. En el seu ús més comú, VLOOKUP és un base de dades funció, és a dir, que funciona amb taules de bases de dades o, més simplement, llistes de les coses en un full de treball d'Excel. Quin tipus de coses? Bé, cap una mena de coses. És possible que tingueu un full de treball que contingui una llista d’empleats, productes, clients o CD a la vostra col·lecció de CD o estrelles al cel nocturn. Realment no importa.

Aquí teniu un exemple de llista o base de dades. En aquest cas, es tracta d’una llista de productes que ven la nostra empresa fictícia:

Normalment, aquestes llistes tenen algun tipus d’identificador únic per a cada element de la llista. En aquest cas, l'identificador únic es troba a la columna "Codi de l'article". Nota: perquè la funció VLOOKUP funcioni amb una base de dades / llista, aquesta llista ha de tenir una columna que contingui l'identificador únic (o "clau" o "ID") i aquesta columna ha de ser la primera columna de la taula. La nostra base de dades de mostra anterior compleix aquest criteri.

El més difícil d’utilitzar VLOOKUP és entendre exactament per a què serveix. A continuació, vegem si primer ho podem deixar clar:

VLOOKUP recupera informació d’una base de dades / llista basada en una instància subministrada de l’identificador únic.

A l'exemple anterior, inseriríeu la funció VLOOKUP en un altre full de càlcul amb un codi d'article i us retornaria la descripció de l'article corresponent, el seu preu o la seva disponibilitat (la quantitat "En estoc") tal com es descriu a l'original llista. Quina d'aquestes informacions us retornarà? Bé, heu de decidir-ho quan creeu la fórmula.

Si tot el que necessiteu és una informació de la base de dades, seria molt difícil anar a construir una fórmula amb una funció VLOOKUP. Normalment, utilitzeu aquest tipus de funcionalitat en un full de càlcul reutilitzable, com ara una plantilla. Cada vegada que algú introdueix un codi d'article vàlid, el sistema recuperaria tota la informació necessària sobre l'article corresponent.

Creem un exemple d'això: An Plantilla de factura que podem reutilitzar una vegada i una altra a la nostra empresa fictícia.

Primer iniciem Excel i ens creem una factura en blanc:

Així funcionarà: la persona que faci servir la plantilla de factura emplenarà una sèrie de codis d'article a la columna "A" i el sistema recuperarà la descripció i el preu de cada article de la nostra base de dades de productes. Aquesta informació s'utilitzarà per calcular el total de la línia de cada article (suposant que introduïm una quantitat vàlida).

Per tal de facilitar aquest exemple, localitzarem la base de dades de productes en un full separat del mateix llibre:

En realitat, és més probable que la base de dades de productes estigui ubicada en un llibre de treball independent. Té poca diferència en la funció VLOOKUP, que realment no té importància si la base de dades es troba al mateix full, a un full diferent o a un llibre completament diferent.

Per tant, hem creat la nostra base de dades de productes, que té aquest aspecte:

Per provar la fórmula VLOOKUP que estem a punt d’escriure, primer introduïm un codi d’article vàlid a la cel·la A11 de la nostra factura en blanc:

A continuació, traslladem la cel·la activa a la cel on volem que s’emmagatzemi la informació recuperada de la base de dades per VLOOKUP. Curiosament, aquest és el pas en què la majoria de la gent s’equivoca. Per explicar-ho més: Estem a punt de crear una fórmula VLOOKUP que recuperarà la descripció que correspon al codi de l'article de la cel·la A11. On volem que es posi aquesta descripció quan la rebem? A la cel·la B11, és clar. Per tant, aquí escrivim la fórmula VLOOKUP: a la cel·la B11. Seleccioneu la cel·la B11 ara.

Hem de localitzar la llista de totes les funcions disponibles que ofereix Excel, de manera que puguem triar VLOOKUP i obtenir ajuda per completar la fórmula. Això es troba fent primer clic a Fórmules i, a continuació, feu clic a Funció d'inserció:

Apareix un quadre que ens permet seleccionar qualsevol de les funcions disponibles a Excel.

Per trobar el que busquem, podríem escriure un terme de cerca com ara "cerca" (perquè la funció que ens interessa és una Cercar funció). El sistema ens proporcionaria una llista de totes les funcions relacionades amb la cerca a Excel. BÚSQUEDA V és el segon de la llista. Seleccioneu-lo amb un clic D'acord.

El Arguments de la funció apareix el quadre, que ens demana tots els fitxers arguments (o paràmetres) necessaris per completar la funció VLOOKUP. Podeu pensar en aquest quadre com la funció que ens fa les següents preguntes:

1. Quin identificador únic busqueu a la base de dades?
2. On és la base de dades?
3. Quina informació de la base de dades, associada a l'identificador únic, voleu recuperar per a vosaltres?

Es mostren els tres primers arguments en negreta, indicant que ho són obligatori arguments (la funció VLOOKUP és incompleta sense ells i no retornarà un valor vàlid). El quart argument no és en negreta, és a dir, és opcional:

Completarem els arguments en ordre, de dalt a baix.

El primer argument que hem de completar és el Valor_cerca argument. La funció necessita que li indiquem on trobar l’identificador únic (el codi de l'ítem en aquest cas) que hauria de retornar la descripció de. Hem de seleccionar el codi d’element que hem introduït anteriorment (a A11).

Feu clic a la icona del selector a la dreta del primer argument:

A continuació, feu clic una vegada a la cel·la que conté el codi de l'element (A11) i premeu Entra:

El valor de "A11" s'insereix al primer argument.

Ara hem d'introduir un valor per a Matriu_tabla argument. En altres paraules, hem d’indicar a VLOOKUP on trobar la base de dades / llista. Feu clic a la icona del selector al costat del segon argument:

Ara localitzeu la base de dades / llista i seleccioneu tota la llista, sense incloure la línia de capçalera. En el nostre exemple, la base de dades es troba en un full de treball independent, de manera que primer fem clic a la pestanya del full de treball:

A continuació, seleccionem tota la base de dades, sense incloure la línia de capçalera:

... i premeu Entra. L’interval de cel·les que representa la base de dades (en aquest cas, “‘ Base de dades de productes ’! A2: D7”) s’introdueix automàticament al segon argument.

Ara cal introduir el tercer argument, Col_index_num. Utilitzem aquest argument per especificar a VLOOKUP quina informació de la base de dades, associada amb el nostre codi d'article a A11, volem que ens hagi retornat. En aquest exemple concret, desitgem tenir l’element descripció ens va tornar. Si mireu el full de treball de la base de dades, notareu que la columna "Descripció" és segon a la base de dades. Això significa que hem d'introduir un valor de "2" al fitxer Col_index_num Caixa:

És important tenir en compte que aquí no introduïm un "2" perquè la columna "Descripció" es troba a B columna del full de treball. Si la base de dades va començar a la columna K del full de treball, encara introduiríem un "2" en aquest camp perquè la columna "Descripció" és la segona columna del conjunt de cel·les que hem seleccionat quan s'especifica la "taula_marca".

Finalment, hem de decidir si voleu introduir un valor a l'argument VLOOKUP final, Range_lookup. Aquest argument requereix un cert o bé fals o s’ha de deixar en blanc. Quan s’utilitza VLOOKUP amb bases de dades (com és cert el 90% del temps), la manera de decidir què posar en aquest argument es pot pensar de la següent manera:

Si la primera columna de la base de dades (la columna que conté els identificadors únics) s’ordena alfabèticament / numèricament en ordre ascendent, és possible introduir un valor de cert en aquest argument o deixeu-lo en blanc.

Si la primera columna de la base de dades és no ordenats o ordenats en ordre descendent, llavors vosaltres haver de introduïu un valor de fals en aquest argument

Com és la primera columna de la nostra base de dades no ordenats, entrem fals en aquest argument:

Això és! Hem introduït tota la informació necessària perquè VLOOKUP torni el valor que necessitem. Feu clic a D'acord i observeu que la descripció corresponent al codi de l'article "R99245" s'ha introduït correctament a la cel·la B11:

La fórmula que es va crear per a nosaltres té aquest aspecte:

Si entrem a diferent codi de l’article a la cel·la A11, començarem a veure la potència de la funció VLOOKUP: la cel·la de descripció canvia per coincidir amb el nou codi de l’article:

Podem realitzar un conjunt de passos similars per obtenir l’element preu va tornar a la cel·la E11. Tingueu en compte que la nova fórmula s'ha de crear a la cel·la E11. El resultat serà així:

... i la fórmula serà així:

Tingueu en compte que l’única diferència entre les dues fórmules és el tercer argument (Col_index_num) ha canviat de "2" a "3" (perquè volem que es recuperin dades de la 3a columna de la base de dades).

Si decidíssim comprar 2 d'aquests articles, introduiríem un "2" a la cel·la D11. A continuació, introduiríem una fórmula simple a la cel·la F11 per obtenir el total de la línia:

= D11 * E1

... que sembla així ...

Completar la plantilla de factura

Fins ara hem après moltes coses sobre VLOOKUP. De fet, hem après tot el que aprendrem en aquest article. És important tenir en compte que VLOOKUP es pot utilitzar en altres circumstàncies, a part de les bases de dades. Això és menys freqüent i es pot tractar en futurs articles de How-To Geek.

La nostra plantilla de factures encara no està completa. Per completar-lo, faríem el següent:

1. Eliminaríem el codi de mostra de la cel·la A11 i el "2" de la cel·la D11. Això farà que les nostres fórmules VLOOKUP de nova creació mostrin missatges d'error:

   
   

   
   Podem solucionar-ho mitjançant un ús prudent dels Excel SI() i ISBLANK () funcions. Canviem la nostra fórmula a partir d’aquesta ... = VLOOKUP (A11, "Base de dades de productes"! A2: D7,2, FALS)... a això ...= IF (ISBLANK (A11) "," VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE))

   
   

2. Copiaríem les fórmules de les cel·les B11, E11 i F11 fins a la resta de files de l'article de la factura. Tingueu en compte que si fem això, les fórmules resultants ja no es refereixen correctament a la taula de base de dades. Podríem solucionar-ho canviant les referències de cel·la de la base de dades absolut referències de cel·les. De manera alternativa, i encara millor, podríem crear un fitxer nom de l'interval per a tota la base de dades de productes (com ara "Productes") i utilitzeu aquest nom d'interval en lloc de les referències de cel·les. La fórmula canviaria a partir d’aquesta ... = IF (ISBLANK (A11) "," VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE))... a això ... = IF (ISBLANK (A11) ", VLOOKUP (A11, Productes, 2, FALS))... i llavors copieu les fórmules a la resta de files de l'element de la factura.
3. Probablement "bloquejaríem" les cel·les que contenen les nostres fórmules (o millor dit descobrir el altres cel·les) i, a continuació, protegir el full de càlcul, per tal de garantir que les nostres fórmules acuradament construïdes no se sobreescriuen accidentalment quan algú ve a emplenar la factura.
4. Desaríem el fitxer com a plantilla, de manera que tothom de la nostra empresa pugui reutilitzar-lo

Si ens sentíssim realment intel·ligent, crearíem una base de dades de tots els nostres clients en un altre full de càlcul i, a continuació, utilitzaríem l’identificador de client introduït a la cel·la F5 per emplenar automàticament el nom i l’adreça del client a les cel·les B6, B7 i B8.

Si voleu practicar amb VLOOKUP o simplement veure la nostra plantilla de factura resultant, es pot descarregar des d’aquí.