Hace mucho tiempo, trabajé para el vicepresidente de ventas de una empresa. Siempre estaba modelando algún nuevo programa de bonificación o plan de comisiones. Me acostumbré bastante a encargar planes con todo tipo de condiciones. El que se muestra en este consejo es bastante dócil.
El enfoque normal es comenzar a construir una fórmula IF anidada. Siempre comienzas en el extremo superior o inferior del rango. “Si las ventas superan los 500.000 dólares, el descuento es del 20%; de lo contrario, … " El tercer argumento de la función SI es una función SI completamente nueva que prueba el segundo nivel: "Si las ventas superan los $ 250K, el descuento es del 15%; de lo contrario, …"
Estas fórmulas se hacen cada vez más largas a medida que hay más niveles. La parte más difícil de tal fórmula es recordar cuántos paréntesis de cierre poner al final de la fórmula.
Si está utilizando Excel 2003, su fórmula ya se está acercando al límite. Con esa versión, no puede anidar más de 7 funciones IF. Fue un día feo cuando los poderes cambiaron el plan de comisiones y se necesitaba una forma de agregar una octava función IF. Hoy, puede anidar 64 funciones IF. Nunca debe anidar tantos, pero es bueno saber que no hay problema para anidar 8 o 9.
En lugar de utilizar la función SI anidada, intente utilizar la función BUSCARV. Cuando el cuarto argumento de BUSCARV cambia de Falso a Verdadero, la función ya no busca una coincidencia exacta. Bueno, primero BUSCARV intenta encontrar una coincidencia exacta. Pero si no se encuentra una coincidencia exacta, Excel se instala en la fila un poco menos de lo que está buscando.
Considere la siguiente tabla. En la celda C13, Excel buscará una coincidencia por $ 28,355 en la tabla. Cuando no puede encontrar 28355, Excel devolverá el descuento asociado con el valor que es simplemente menor; en este caso, el 1% de descuento para el nivel de $ 10K.
Cuando convierte las reglas a la tabla en E13: F18, debe comenzar desde el nivel más pequeño y pasar al nivel más alto. Aunque no está establecido en las reglas, si alguien tiene menos de $ 10,000 en ventas, el descuento será del 0%. Debe agregar esto como la primera fila de la tabla.
Precaución
Cuando utiliza la versión "Verdadera" de BUSCARV, su tabla debe ordenarse de forma ascendente. Mucha gente cree que todas las tablas de búsqueda deben ordenarse. Pero una tabla debe ordenarse solo para obtener una coincidencia aproximada.
¿Qué pasa si su gerente quiere una fórmula completamente autónoma y no quiere ver la tabla de bonificaciones a la derecha? Después de crear la fórmula, puede incrustar la tabla directamente en la fórmula. Ponga la fórmula en modo Edición haciendo doble clic en la celda o seleccionando la celda y presionando F2. Use el cursor para seleccionar el segundo argumento completo: $ E $ 13: $ F $ 18.
Presione la tecla F9. Excel incrusta la tabla de búsqueda como una constante de matriz. En la constante de matriz, un punto y coma indica una nueva fila y una coma indica una nueva columna. Consulte Comprensión de las constantes de matriz.
Presione Entrar. Copie la fórmula en las otras celdas.
Ahora puede eliminar la tabla. La fórmula final se muestra a continuación.
Gracias a Mike Girvin por enseñarme sobre los paréntesis coincidentes. La técnica VLOOKUP fue sugerida por Danny Mac, Boriana Petrova, Andreas Thehos y @mvmcos.
