1Aug
VLOOKUP es una de las funciones más útiles de Excel, y también es una de las menos entendidas. En este artículo, desmitificamos VLOOKUP por medio de un ejemplo de la vida real. Crearemos una plantilla de factura utilizable para una empresa ficticia.
VLOOKUP es una función de de Excel .Este artículo supondrá que el lector ya tiene una comprensión pasajera de las funciones de Excel y puede usar funciones básicas como SUMA, PROMEDIO y HOY.En su uso más común, VLOOKUP es una función de la base de datos , lo que significa que funciona con tablas de bases de datos, o más simplemente, enumera de las cosas en una hoja de cálculo de Excel.¿Que tipo de cosas? Bueno, algo de .Puede tener una hoja de trabajo que contenga una lista de empleados, o productos, o clientes, o CD en su colección de CD, o estrellas en el cielo nocturno. Realmente no importa.
Aquí hay un ejemplo de una lista o base de datos. En este caso se trata de una lista de productos que vende nuestra compañía ficticia:
Por lo general, las listas como esta tienen algún tipo de identificador único para cada elemento de la lista. En este caso, el identificador único se encuentra en la columna "Código de artículo".Nota: Para que la función VLOOKUP funcione con una base de datos / lista, esa lista debe tener una columna que contiene el identificador único( o "clave" o "ID"), y esa columna debe ser la primera columna de la tabla.Nuestra base de datos de muestra anterior satisface este criterio.
La parte más difícil de usar VLOOKUP es entender exactamente para qué sirve. Entonces, veamos si podemos aclarar eso primero:
VLOOKUP recupera información de una base de datos / lista basada en una instancia suministrada del identificador único.
En el ejemplo anterior, insertaría la función VLOOKUP en otra hoja de cálculo con un código de artículo, y le devolvería la descripción del artículo correspondiente, su precio o su disponibilidad( su cantidad "In stock") como se describe en sulista original¿Cuál de estas piezas de información te hará retroceder? Bueno, tienes que decidir esto cuando estás creando la fórmula.
Si todo lo que necesita es una pieza de información de la base de datos, sería un gran problema ir a construir una fórmula con una función VLOOKUP.Normalmente, utilizaría este tipo de funcionalidad en una hoja de cálculo reutilizable, como una plantilla. Cada vez que alguien ingresa un código de artículo válido, el sistema recuperaría toda la información necesaria sobre el artículo correspondiente.
Vamos a crear un ejemplo de esto: una plantilla de factura que podemos reutilizar una y otra vez en nuestra empresa ficticia.
Primero comenzamos Excel, y nos creamos una factura en blanco:
Así es como va a funcionar: la persona que usa la plantilla de factura completará una serie de códigos de artículo en la columna "A", y el sistema recuperará cada artículodescripción y precio de nuestra base de datos de productos. Esa información se usará para calcular el total de la línea para cada artículo( suponiendo que ingresemos una cantidad válida).
A los efectos de mantener este ejemplo simple, ubicaremos la base de datos del producto en una hoja separada en el mismo libro de trabajo:
En realidad, es más probable que la base de datos del producto esté ubicada en un libro de trabajo separado. No tiene mucha importancia la función VLOOKUP, que realmente no se preocupa si la base de datos se encuentra en la misma hoja, en una hoja diferente o en un libro de trabajo completamente diferente.
Por lo tanto, hemos creado nuestra base de datos de productos, que se ve así:
Para probar la fórmula VLOOKUP que vamos a escribir, primero ingresamos un código de artículo válido en la celda A11 de nuestra factura en blanco:
Luego,mover la celda activa a la celda en la que queremos que se almacene la información recuperada de la base de datos por VLOOKUP.Curiosamente, este es el paso que la mayoría de las personas se equivocan. Para seguir explicando: estamos a punto de crear una fórmula VLOOKUP que recuperará la descripción que corresponde al código del elemento en la celda A11.¿Dónde queremos poner esta descripción cuando la obtengamos? En la celda B11, por supuesto. Entonces ahí es donde escribimos la fórmula VLOOKUP: en la celda B11.Seleccione la celda B11 ahora.
Necesitamos ubicar la lista de todas las funciones disponibles que Excel tiene para ofrecer, para que podamos elegir VLOOKUP y obtener ayuda para completar la fórmula. Esto se encuentra haciendo clic primero en la pestaña Fórmulas y luego haciendo clic en Insertar función :
Aparece un cuadro que nos permite seleccionar cualquiera de las funciones disponibles en Excel.
Para encontrar el que estamos buscando, podríamos escribir un término de búsqueda como "búsqueda"( porque la función que nos interesa es búsqueda función).El sistema nos devolvería una lista de todas las funciones relacionadas con la búsqueda en Excel. VLOOKUP es el segundo en la lista. Seleccione un clic OK .
Aparece el recuadro Argumentos de función , que nos solicita todos los argumentos ( o parámetros ) necesarios para completar la función VLOOKUP.Puede considerar esta casilla como la función que nos hace las siguientes preguntas:
- ¿Qué identificador único está buscando en la base de datos?
- ¿Dónde está la base de datos?
- ¿Qué parte de la información de la base de datos, asociada con el identificador único, desea recuperar para usted?
Los primeros tres argumentos se muestran en negrita , lo que indica que son obligatorios argumentos( la función VLOOKUP está incompleta sin ellos y no devolverá un valor válido).El cuarto argumento no es negrita, lo que significa que es opcional:
Completaremos los argumentos en orden, de arriba abajo.
El primer argumento que debemos completar es el argumento Lookup_value .La función necesita que le digamos dónde encontrar el identificador único( el código de artículo en este caso) del que debería devolver la descripción. Debemos seleccionar el código del artículo que ingresamos anteriormente( en A11).
Haga clic en el icono del selector a la derecha del primer argumento:
Luego haga clic una vez en la celda que contiene el código del elemento( A11) y presione Ingrese :
El valor de "A11" se inserta en el primer argumento.
Ahora necesitamos ingresar un valor para el argumento Table_array .En otras palabras, debemos decirle a VLOOKUP dónde encontrar la base de datos / lista. Haga clic en el ícono selector al lado del segundo argumento:
Ahora ubique la base de datos / lista y seleccione toda la lista - sin incluir la línea de encabezado .En nuestro ejemplo, la base de datos se encuentra en una hoja de trabajo separada, por lo que primero hacemos clic en esa pestaña de la hoja de cálculo:
A continuación, seleccionamos toda la base de datos, sin incluir la línea de encabezado:
. .. y presionamos Ingrese .El rango de celdas que representa la base de datos( en este caso "'Base de datos de producto!' A2: D7") se ingresa automáticamente en el segundo argumento.
Ahora tenemos que ingresar el tercer argumento, Col_index_num .Utilizamos este argumento para especificar a VLOOKUP qué parte de la información de la base de datos, asociar con nuestro código de elemento en A11, deseamos que nos sea devuelta. En este ejemplo particular, deseamos que la descripción de del artículo nos sea devuelta. Si observa la hoja de trabajo de la base de datos, verá que la columna "Descripción" es la segunda columna de en la base de datos. Esto significa que debemos ingresar un valor de "2" en la casilla Col_index_num :
Es importante tener en cuenta que no estamos ingresando un "2" aquí porque la columna "Descripción" está en la columna B enesa hoja de trabajo. Si la base de datos comenzaba en la columna K de la hoja de trabajo, todavía ingresaríamos un "2" en este campo porque la columna "Descripción" es la segunda columna en el conjunto de celdas que seleccionamos al especificar "Table_array".
Finalmente, debemos decidir si ingresamos un valor en el argumento VLOOKUP final, Range_lookup .Este argumento requiere un valor verdadero o falso , o debe dejarse en blanco. Al usar VLOOKUP con bases de datos( como es cierto el 90% del tiempo), la forma de decidir qué poner en este argumento se puede pensar de la siguiente manera:
Si la primera columna de la base de datos( la columna que contiene los identificadores únicos)se ordena alfabéticamente / numéricamente en orden ascendente, luego es posible ingresar un valor de verdadero en este argumento, o dejarlo en blanco.
Si la primera columna de la base de datos es no clasificada, o está ordenada en orden descendente, entonces debe ingresar un valor de falso en este argumento
como la primera columna de nuestra base de datos es no ordenada,ingresamos falso en este argumento:
¡Eso es todo! Hemos ingresado toda la información requerida para VLOOKUP para devolver el valor que necesitamos. Haga clic en el botón OK y observe que la descripción correspondiente al código de elemento "R99245" se ha ingresado correctamente en la celda B11:
La fórmula que se creó para nosotros tiene este aspecto:
Si ingresamos un diferente código de elemento en la celdaA11, comenzaremos a ver el poder de la función VLOOKUP: la celda de descripción cambia para coincidir con el nuevo código de elemento:
Podemos realizar un conjunto similar de pasos para obtener precio del artículo devuelto a la celda E11.Tenga en cuenta que la nueva fórmula debe crearse en la celda E11.El resultado será así:
. .. y la fórmula se verá así:
Tenga en cuenta que la única diferencia entre las dos fórmulas es el tercer argumento( Col_index_num ) ha cambiado de un "2" a un "3"( porquequeremos datos recuperados de la tercera columna en la base de datos).
Si decidimos comprar 2 de estos artículos, ingresaríamos un "2" en la celda D11.Luego ingresamos una fórmula simple en la celda F11 para obtener el total de la línea:
= D11 * E11
. .. que se ve así. ..
Completando la plantilla de factura
Hasta ahora hemos aprendido mucho sobre VLOOKUP.De hecho, aprendimos todo lo que vamos a aprender en este artículo. Es importante tener en cuenta que VLOOKUP se puede usar en otras circunstancias además de las bases de datos. Esto es menos común y puede estar cubierto en futuros artículos How-To Geek.
Nuestra plantilla de factura aún no está completa. Para completarlo, haríamos lo siguiente:
- Quitaríamos el código del elemento de muestra de la celda A11 y el "2" de la celda D11.Esto hará que nuestras fórmulas VLOOKUP recién creadas muestren mensajes de error:
Podemos remediar esto mediante el uso juicioso de las funciones IF() y ISBLANK() de Excel. Cambiamos nuestra fórmula de esta. .. = VLOOKUP( A11, 'Base de datos de productos'! A2: D7,2, FALSO) . .. a esto. .. = IF( ISBLANK( A11), "", BUSCARV( A11, 'Base de datos de productos'! A2: D7,2, FALSE))
- Copiaremos las fórmulas en las celdas B11, E11 y F11 hasta el resto de las filas de los elementos de la factura. Tenga en cuenta que si hacemos esto, las fórmulas resultantes ya no se referirán correctamente a la tabla de la base de datos. Podríamos solucionar esto cambiando las referencias de celda para la base de datos a referencias de celda absoluta .Alternativamente, e incluso mejor, podríamos crear un con el nombre de rango para toda la base de datos del producto( como "Productos") y usar este nombre de rango en lugar de las referencias de celda. La fórmula cambiaría de esta. .. = IF( ISBLANK( A11), "", BUSCARV( A11, 'Base de datos de productos'! A2: D7,2, FALSO)) . .. a esto. .. = IF( ISBLANK( A11), "", BUSCARV( A11, Productos, 2, FALSO)) . .. y luego copian las fórmulas al resto de las filas de elementos de la factura.
- Probablemente "bloquearemos" las celdas que contienen nuestras fórmulas( o más bien desbloquee las otras celdas ), y luego protejamos la hoja de trabajo, para asegurarnos de que nuestras fórmulas cuidadosamente construidas no se sobrescriban accidentalmente cuando alguien viene a llenaren la factura
- Guardamos el archivo como plantilla , para que pueda ser reutilizado por todos en nuestra compañía
Si nos sintiéramos realmente inteligente, crearíamos una base de datos de todos nuestros clientes en otra hoja de trabajo, y luego usaríamos elID de cliente ingresado en la celda F5 para completar automáticamente el nombre y la dirección del cliente en las celdas B6, B7 y B8.
Si desea practicar con VLOOKUP, o simplemente ver nuestra Plantilla de Factura resultante, puede descargarla desde aquí.
