VLOOKUP en Excel, parte 2 Uso de VLOOKUP sin una base de datos
En un artículo reciente, presentamos la función de Excel llamada VLOOKUP y explicó cómo podría usarse para recuperar información de una base de datos en una celda en una hoja de cálculo local. En ese artículo mencionamos que había dos usos para VLOOKUP, y solo uno de ellos trataba con las bases de datos de consulta. 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 asumirá que el lector ya conoce muchos de los conceptos explicados en ese artículo..
Cuando se trabaja con bases de datos, a VLOOKUP se le pasa 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 existe 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 puede adoptar un enfoque "lo suficientemente bueno como suficiente" 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 cálculo de las comisiones que se generan en un conjunto de cifras de ventas. Comenzaremos con un escenario muy simple, y luego lo haremos progresivamente más complejo, hasta que la única solución racional al problema sea usar VLOOKUP. El escenario inicial en nuestra compañía ficticia funciona de la siguiente manera: si un vendedor genera más de $ 30,000 en ventas en un año determinado, la comisión que ganan por 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 calcular la 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 abajo el umbral de $ 30,000, o el encima el umbral. Esta fórmula hace uso de la función de Excel llamada SI. Para aquellos lectores que no están familiarizados con IF, funciona así:
SI(condición, valor si es verdadero, valor si es falso)
Donde el condición Es una expresión que evalúa a cualquiera cierto o falso. En el ejemplo anterior, condición es la expresión B1
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 tarifa de comisión diferente:
Así que nuestra hoja de cálculo está funcionando..
Vamos a hacerlo más complejo. Introduzcamos un segundo umbral: si el vendedor gana más de $ 40,000, entonces su tasa de comisión aumenta a 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) es ahora una función completa de FI por derecho propio. Esto se llama un 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 los detalles de cómo y por qué funciona esto, ni examinaremos los matices de las funciones anidadas. Este es un tutorial sobre VLOOKUP, no sobre Excel en general.
De todos modos, se pone peor! ¿Qué pasa cuando decidimos que si ganan más de $ 50,000 tienen derecho a una comisión del 50% y si ganan más de $ 60,000 tienen derecho a una comisión del 60%??
Ahora la fórmula en la celda B2, aunque es correcta, se ha vuelto virtualmente ilegible. ¡Nadie debería tener que escribir fórmulas donde las funciones estén anidadas a cuatro niveles de profundidad! Seguramente debe haber una forma más sencilla.?
Ciertamente hay VLOOKUP al rescate!
Vamos a rediseñar la hoja de cálculo un poco. Mantendremos todas las mismas cifras, pero organizaremos de una manera nueva, una más tabular camino:
Toma un momento y verifica por ti mismo que el nuevo Tabla de tarifas Funciona exactamente igual que la serie de umbrales arriba.
Conceptualmente, lo que estamos a punto de hacer es utilizar VLOOKUP para consultar el total de ventas del vendedor (de 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 son 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 hace no Aparecen en la tabla de tarifas. A ver si VLOOKUP puede resolver nuestro problema de todos modos ...
Seleccionamos la celda B2 (la ubicación donde queremos poner nuestra fórmula), y luego insertamos la función VLOOKUP desde la Fórmulas lengüeta:
los Argumentos de la función Aparece el cuadro para VLOOKUP. Completamos los argumentos (parámetros) uno por uno, comenzando con el Valor de búsqueda, que es, en este caso, el total de ventas de la celda B1. Colocamos el cursor en el Valor de búsqueda campo y luego haga clic una vez en la celda B1:
A continuación, debemos especificar en VLOOKUP en qué tabla se deben buscar estos datos. En este ejemplo, es la tabla de tasas, por supuesto. Colocamos el cursor en el Matriz de tabla campo, y luego resaltar toda la tabla de tasas - excluyendo los encabezados:
A continuación, debemos especificar qué columna de la tabla contiene la información que queremos que nos devuelva nuestra fórmula. 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 Col_index_num campo:
Finalmente ingresamos un valor en la Rango de búsqueda campo.
Importante: es el uso de este campo lo que diferencia las dos formas de usar VLOOKUP. Para usar VLOOKUP con una base de datos, este parámetro final, Rango de búsqueda, siempre se debe establecer en FALSO, pero con este otro uso de VLOOKUP, debemos dejarlo en blanco o ingresar un valor de CIERTO. Cuando use VLOOKUP, es vital que tome la decisión correcta para este parámetro final.
Para ser explícitos, introduciremos un valor de cierto en el Rango de búsqueda campo. 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 DE ACUERDO botón, y Excel construye nuestra fórmula VLOOKUP para nosotros:
Si experimentamos con unos pocos montos 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 Rango de búsqueda parámetro es FALSO, el valor pasado en el primer parámetro (Valor de búsqueda) debe Estar presente en la base de datos. En otras palabras, estamos buscando una coincidencia exacta.
Pero en este otro uso de VLOOKUP, no necesariamente estamos buscando una coincidencia exacta. En este caso, "lo suficientemente cerca es lo suficientemente bueno". Pero, ¿qué queremos decir con "lo suficientemente cerca"? Vamos a usar un ejemplo: al buscar 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%? De hecho, ¿qué significa "lo suficientemente cerca" en este caso? Seamos precisos:
Cuando Rango de búsqueda se establece en CIERTO (u omitido), VLOOKUP buscará en la columna 1 y coincidirá el valor más alto que no es mayor que la Valor de búsqueda parámetro.
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í..
