1Aug
En un artículo reciente, presentamos la función de Excel llamada VLOOKUP y explicamos cómo se podría usar para recuperar información de una base de datos en una celda en una hoja de trabajo local. En ese artículo mencionamos que había dos usos para VLOOKUP, y solo uno de ellos se ocupaba de consultar bases de datos. En este artículo, el segundo y último de la serie VLOOKUP, examinamos este otro uso menos conocido para la función VLOOKUP.
Si aún no lo ha hecho, lea el primer artículo de VLOOKUP: este artículo supondrá que muchos de los conceptos explicados en ese artículo ya son conocidos por el lector.
Al trabajar con bases de datos, se le pasa a VLOOKUP un "identificador único" que sirve para identificar qué registro de datos queremos encontrar en la base de datos( por ejemplo, un código de producto o ID de cliente).Este identificador único debe existir en la base de datos, de lo contrario VLOOKUP nos devuelve un error. En este artículo, examinaremos una forma de usar VLOOKUP donde el identificador no necesita existir en la base de datos. Es casi como si VLOOKUP pudiera adoptar un enfoque "lo suficientemente cerca como para devolver los datos que estamos buscando".En ciertas circunstancias, esto es
exactamente lo que necesitamos.Ilustraremos este artículo con un ejemplo del mundo real: el de calcular las comisiones que se generan en un conjunto de cifras de ventas. Comenzaremos con un escenario muy simple y luego lo haremos más complejo, hasta que la única solución racional al problema sea usar VLOOKUP.El escenario inicial de nuestra empresa ficticia funciona así: si un vendedor genera más de $ 30,000 en ventas en un año determinado, la comisión que gana con esas ventas es del 30%.De lo contrario, su comisión es solo del 20%.Hasta ahora, esta es una hoja de trabajo bastante simple:
Para usar esta hoja de trabajo, el vendedor ingresa sus cifras de ventas en la celda B1 y la fórmula en la celda B2 calcula la tasa de comisión correcta que tienen derecho a recibir, que se usa en la celda B3 para calcularla comisión total que se le debe al vendedor( que es una simple multiplicación de B1 y B2).
La celda B2 contiene la única parte interesante de esta hoja de trabajo: la fórmula para decidir qué tasa de comisión usar: la de un por debajo de el umbral de $ 30,000, o la de un por encima de el umbral. Esta fórmula hace uso de la función de Excel llamada IF .Para aquellos lectores que no están familiarizados con IF, funciona así:
IF( condición, valor si es verdadero, valor si es falso)
Donde condición es una expresión que evalúa verdadero o falso .En el ejemplo anterior, la condición es la expresión B1 & lt; B5 , que se puede leer como "¿B1 es menor que B5?", O dicho de otra forma, "¿Las ventas totales son menores que el umbral"?Si la respuesta a esta pregunta es "sí"( verdadero), entonces usamos el valor si el verdadero parámetro de la función, es decir B6 en este caso - la tasa de comisión si el total de ventas fue debajo del umbral .Si la respuesta a la pregunta es "no"( falso), entonces usamos el valor si el parámetro falso de la función, es decir B7 en este caso - la tasa de comisión si el total de ventas fue por encima del umbral .
Como puede ver, el uso de un total de ventas de $ 20,000 nos da una tasa de comisión del 20% en la celda B2.Si ingresamos un valor de $ 40,000, obtenemos una tasa de comisión diferente:
Entonces, nuestra hoja de cálculo funciona.
Vamos a hacerlo más complejo. Vamos a introducir un segundo umbral: si el vendedor gana más de $ 40,000, entonces su tasa de comisión aumenta al 40%:
Lo suficientemente fácil de entender en el mundo real, pero en la celda B2 nuestra fórmula es cada vez más compleja. Si observa detenidamente la fórmula, verá que el tercer parámetro de la función IF original( el valor si es falso ) ahora es una función IF completa por derecho propio. Esto se denomina función anidada ( una función dentro de una función).Es perfectamente válido en Excel( ¡incluso funciona!), Pero es más difícil de leer y entender.
No vamos a entrar en detalles sobre cómo y por qué funciona esto, ni vamos a examinar los matices de las funciones anidadas. Este es un tutorial sobre VLOOKUP, no en Excel en general.
¡De todos modos, empeora!¿Qué pasa cuando decidimos que si ganan más de $ 50,000 tienen derecho al 50% de comisión y, si ganan más de $ 60,000, tienen derecho al 60% de comisión?
Ahora la fórmula en la celda B2, si bien es correcta, se ha vuelto prácticamente ilegible.¡Nadie debería tener que escribir fórmulas donde las funciones están anidadas a cuatro niveles de profundidad! Seguramente debe haber una manera más simple?
Ciertamente existe. VLOOKUP al rescate!
Vamos a rediseñar la hoja de trabajo un poco. Mantendremos todas las mismas cifras, pero organícela de una manera nueva, una forma tabular más :
Tómese un momento y compruebe por sí mismo que la nueva Rate Table funciona exactamente igual que la serie de umbrales anterior.
Conceptualmente, lo que vamos a hacer es usar VLOOKUP para buscar el total de ventas del vendedor( desde B1) en la tabla de tarifas y devolvernos la tasa de comisión correspondiente. Tenga en cuenta que el vendedor puede haber creado ventas que sean y no , uno de los cinco valores en la tabla de tarifas( $ 0, $ 30,000, $ 40,000, $ 50,000 o $ 60,000).Es posible que hayan creado ventas de $ 34,988.Es importante tener en cuenta que $ 34,988 aparece y no en la tabla de tasas. Veamos si VLOOKUP puede resolver nuestro problema de todos modos. ..
Seleccionamos la celda B2( la ubicación en la que queremos poner nuestra fórmula) y luego insertamos la función VLOOKUP de la pestaña Fórmulas :
Aparece la caja de Function Arguments para VLOOKUP.Completamos los argumentos( parámetros) uno por uno, comenzando con Lookup_value , que es, en este caso, el total de ventas de la celda B1.Colocamos el cursor en el campo Lookup_value y luego hacemos clic una vez en la celda B1:
A continuación, debemos especificar a VLOOKUP en qué tabla buscar estos datos. En este ejemplo, es la tabla de tasas, por supuesto. Colocamos el cursor en el campo Table_array , y luego resaltamos la tabla de tasas completa - excluyendo los títulos :
A continuación debemos especificar qué columna de la tabla contiene la información que queremos que nuestra fórmula nos devuelva. En este caso, queremos la tasa de comisión, que se encuentra en la segunda columna de la tabla, por lo tanto, ingresamos un 2 en el campo Col_index_num :
Finalmente, ingresamos un valor en el campo Range_lookup .
Importante: es el uso de este campo el que diferencia las dos formas de usar VLOOKUP.Para usar VLOOKUP con una base de datos, este parámetro final, Range_lookup , siempre debe establecerse en FALSE , pero con este otro uso de VLOOKUP, debemos dejarlo en blanco o ingresar un valor de TRUE .Al usar VLOOKUP, es vital que realice la elección correcta para este parámetro final.
Para ser explícitos, ingresaremos un valor de verdadero en el campo Range_lookup .También estaría bien dejarlo en blanco, ya que este es el valor predeterminado:
Hemos completado todos los parámetros. Ahora hacemos clic en el botón OK , y Excel crea nuestra fórmula VLOOKUP para nosotros:
Si experimentamos con algunas cantidades totales de ventas diferentes, podemos asegurarnos de que la fórmula está funcionando.
Conclusión
En la versión de "base de datos" de VLOOKUP, donde el Range_lookup parámetro es FALSO , el valor pasado en el primer parámetro( Lookup_value ) debe estar presente en la base de datos. En otras palabras, estamos buscando una coincidencia exacta.
Pero en este otro uso de VLOOKUP, no estamos necesariamente buscando una coincidencia exacta. En este caso, "lo suficientemente cerca es lo suficientemente bueno".Pero, ¿qué queremos decir con "lo suficientemente cerca"?Usemos un ejemplo: cuando busquemos una tasa de comisión sobre un total de ventas de $ 34,988, nuestra fórmula VLOOKUP nos devolverá un valor del 30%, que es la respuesta correcta.¿Por qué eligió la fila en la tabla que contiene 30%?¿Qué significa "lo suficientemente cerca" en este caso? Seamos precisos:
Cuando Range_lookup se establece en VERDADERO ( u omitido), VLOOKUP buscará en la columna 1 y hará coincidir el valor más alto que no sea mayor que el parámetro Lookup_value .
También es importante tener en cuenta que para que este sistema funcione, la tabla debe ordenarse en orden ascendente en la columna 1 .
Si desea practicar con VLOOKUP, el archivo de muestra ilustrado en este artículo se puede descargar desde aquí.