Contenido
Recientemente dedicamos un artículo a una de las funciones de Excel más útiles llamada VPR y mostró cómo se puede utilizar para extraer la información requerida de una base de datos en una celda de la hoja de cálculo. También mencionamos que hay dos casos de uso para la función VPR y solo uno de ellos se ocupa de las consultas de la base de datos. En este artículo, aprenderá otra forma menos conocida de usar la función VPR en Excel
Si aún no lo ha hecho, asegúrese de leer el último artículo sobre la función. VPR, porque toda la información a continuación asume que ya está familiarizado con los principios descritos en el primer artículo.
Al trabajar con bases de datos, las funciones VPR se pasa un identificador único, que se utiliza para identificar la información que queremos encontrar (por ejemplo, un código de producto o un número de identificación de cliente). Este código único debe estar presente en la base de datos, de lo contrario VPR informará de un error. En este artículo, veremos esta forma de usar la función VPRcuando la identificación no existe en la base de datos en absoluto. Como si la función VPR cambia al modo aproximado, y elige qué datos nos proporciona cuando queremos encontrar algo. En ciertas circunstancias, esto es exactamente lo que se necesita.
Un ejemplo de vida. fijamos la tarea
Ilustremos este artículo con un ejemplo de la vida real: calcular comisiones en función de una amplia gama de métricas de ventas. Empezaremos con una opción muy simple, y luego la iremos complicando gradualmente hasta que la única solución racional al problema sea usar la función VPR. El escenario inicial de nuestra tarea ficticia es el siguiente: si un vendedor gana más de $30000 30 en ventas en un año, entonces su comisión es del 20 %. De lo contrario, la comisión es solo del XNUMX%. Pongámoslo en forma de tabla:
El vendedor ingresa sus datos de ventas en la celda B1 y la fórmula en la celda B2 determina la tasa de comisión correcta que el vendedor puede esperar. A su vez, la tasa resultante se utiliza en la celda B3 para calcular la comisión total que debe recibir el vendedor (simplemente multiplicando las celdas B1 y B2).
La parte más interesante de la tabla se encuentra en la celda B2: esta es la fórmula para determinar la tasa de comisión. Esta fórmula contiene una función de Excel llamada IF (SI). Para aquellos lectores que no estén familiarizados con esta función, les explico cómo funciona:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Estado es un argumento de función que toma el valor de CÓDIGO VERDADERO (Verdad o FALSO (FALSO). En el ejemplo anterior, la expresión B1
¿Es cierto que B1 es menor que B5?
O puedes decirlo de otra manera:
¿Es cierto que el importe total de las ventas del año es inferior al valor umbral?
Si respondemos a esta pregunta Si (VERDADERO), entonces la función devuelve valor si es cierto (valor si es VERDADERO). En nuestro caso, este será el valor de la celda B6, es decir, la tasa de comisión cuando las ventas totales están por debajo del umbral. Si respondemos a la pregunta NO (FALSO) luego regresa valor si es falso (valor si es FALSO). En nuestro caso, este es el valor de la celda B7, es decir, la tasa de comisión cuando las ventas totales superan el umbral.
Como puede ver, si tomamos las ventas totales de $20000, obtenemos una tasa de comisión del 2 % en la celda B20. Si ingresamos un valor de $ 40000, entonces la tasa de comisión cambiará en un 30%:
Así funciona nuestra mesa.
Complicamos la tarea
Hagamos las cosas un poco más difíciles. Establezcamos otro umbral: si el vendedor gana más de $40000 40, la tasa de comisión aumenta al XNUMX %:
Todo parece ser simple y claro, pero nuestra fórmula en la celda B2 se vuelve notablemente más complicada. Si observa detenidamente la fórmula, verá que el tercer argumento de la función IF (SI) se convirtió en otra función completa IF (SI). Esta construcción se llama anidamiento de funciones entre sí. Excel felizmente permite estas construcciones, e incluso funcionan, pero son mucho más difíciles de leer y comprender.
No profundizaremos en los detalles técnicos: por qué y cómo funciona, y no entraremos en los matices de escribir funciones anidadas. Después de todo, este es un artículo dedicado a la función. VPR, no es una guía completa de Excel.
En cualquier caso, ¡la fórmula se vuelve más complicada! ¿Qué pasa si introducimos otra opción para una tasa de comisión del 50% para aquellos vendedores que ganan más de $50000 en ventas? Y si alguien ha vendido más de $60000, ¿pagará el 60% de comisión?
Ahora la fórmula en la celda B2, incluso si se escribió sin errores, se ha vuelto completamente ilegible. Creo que son pocos los que quieren usar fórmulas con 4 niveles de anidamiento en sus proyectos. Debe haber una manera más fácil?!
¡Y hay tal manera! La función nos ayudará. VPR.
Aplicamos la función BUSCARV para resolver el problema
Cambiemos un poco el diseño de nuestra mesa. Mantendremos todos los mismos campos y datos, pero los organizaremos de una manera nueva y más compacta:
Tómese un momento y asegúrese de que la nueva mesa Tabla de tarifas incluye los mismos datos que la tabla de umbral anterior.
La idea principal es usar la función VPR para determinar la tasa de tarifa deseada de acuerdo con la tabla Tabla de tarifas dependiendo del volumen de ventas. Tenga en cuenta que el vendedor puede vender bienes por una cantidad que no es igual a uno de los cinco umbrales de la tabla. Por ejemplo, podría vender por $34988, pero no existe tal cantidad. Veamos cómo funciona la función. VPR puede hacer frente a tal situación.
Insertar una función BUSCARV
Seleccione la celda B2 (donde queremos insertar nuestra fórmula) y busque VLOOKUP (BUSCARV) en la biblioteca de funciones de Excel: Fórmulas (fórmulas) > Biblioteca de funciones (Biblioteca de funciones) > Búsqueda y referencia (Referencias y arreglos).
Aparece un cuadro de diálogo Argumentos de función (Argumentos de la función). Rellenamos los valores de los argumentos uno a uno, empezando por Valor de búsqueda (Valor de búsqueda). En este ejemplo, esta es la cantidad total de ventas de la celda B1. Poner el cursor en el campo Valor de búsqueda (valor_buscado) y seleccione la celda B1.
A continuación, debe especificar las funciones. VPRdónde buscar datos. En nuestro ejemplo, esta es una tabla Tabla de tarifas. Poner el cursor en el campo Matriz de tabla (Tabla) y seleccione toda la tabla Tabla de tarifasexcepto los encabezados.
A continuación, debemos especificar de qué columna extraer datos usando nuestra fórmula. Nos interesa la tasa de comisión, que se encuentra en la segunda columna de la tabla. Por lo tanto, para el argumento col_index_num (Column_number) ingrese el valor 2.
Y finalmente, presentamos el último argumento: Rango de búsqueda (intervalo_búsqueda).
Importante: es el uso de este argumento lo que marca la diferencia entre las dos formas de aplicar la función VPR. Cuando se trabaja con bases de datos, el argumento Rango de búsqueda (range_lookup) siempre debe tener un valor FALSO (FALSO) para buscar una coincidencia exacta. En nuestro uso de la función VPR, debemos dejar este campo en blanco, o ingresar un valor CÓDIGO VERDADERO (CIERTO). Es extremadamente importante elegir esta opción correctamente.
Para que quede más claro, vamos a introducir CÓDIGO VERDADERO (VERDADERO) en el campo Rango de búsqueda (intervalo_búsqueda). Aunque, si dejas el campo en blanco, esto no será un error, ya que CÓDIGO VERDADERO es su valor predeterminado:
Hemos rellenado todos los parámetros. Ahora presionamos OKy Excel crea una fórmula para nosotros con una función VPR.
Si experimentamos con varios valores diferentes para el monto total de las ventas, nos aseguraremos de que la fórmula funcione correctamente.
Conclusión
Cuando la función VPR trabaja con bases de datos, argumento Rango de búsqueda (range_lookup) debe aceptar FALSO (FALSO). Y el valor ingresado como Valor de búsqueda (Lookup_value) debe existir en la base de datos. En otras palabras, está buscando una coincidencia exacta.
En el ejemplo que hemos visto en este artículo, no es necesario obtener una coincidencia exacta. Este es el caso cuando la función VPR debe cambiar al modo aproximado para devolver el resultado deseado.
Por ejemplo: Queremos determinar qué tasa usar en el cálculo de la comisión para un vendedor con un volumen de ventas de $34988. Función VPR nos devuelve un valor del 30%, que es absolutamente correcto. Pero, ¿por qué la fórmula seleccionó la fila que contenía exactamente el 30 % y no el 20 % o el 40 %? ¿Qué se entiende por búsqueda aproximada? Seamos claros.
Cuando el argumento Rango de búsqueda (interval_lookup) tiene un valor CÓDIGO VERDADERO (VERDADERO) u omitido, función VPR itera a través de la primera columna y selecciona el valor más grande que no exceda el valor de búsqueda.
Punto importante: Para que este esquema funcione, la primera columna de la tabla debe ordenarse en orden ascendente.