¿Tiene una fórmula de Excel que anida múltiples funciones SI? Cuando llegue al punto con demasiadas declaraciones IF anidadas, debe ver si todo se vuelve más simple con una simple función VLOOKUP. He visto fórmulas de 1000 caracteres simplificadas a una fórmula VLOOKUP de 30 caracteres. Es fácil de mantener cuando tiene que agregar nuevos valores más adelante.
Hace mucho tiempo, trabajé para el vicepresidente de ventas en el trabajo. 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 de abajo 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, entonces 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.
Mini consejo de bonificación
Emparejar los paréntesis
Excel recorre una variedad de colores para cada nuevo nivel de paréntesis. Si bien Excel reutiliza los colores, usa negro solo para el paréntesis de apertura y para el paréntesis de cierre correspondiente. A medida que termine la fórmula siguiente, siga escribiendo paréntesis de cierre hasta que escriba un paréntesis negro.
Volver a la sugerencia de fórmula anidada
Si está utilizando Excel 2003, su fórmula ya se está acercando al límite. En aquel entonces, no se podían 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 hacer esto, pero es bueno saber que no hay problema para anidar 8 o 9.
En lugar de utilizar la función IF anidada, intente utilizar el uso inusual de 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 en el caso de hacer 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.
Con el cursor, seleccione el segundo argumento completo: $ E $ 13: $ F $ 18.
Presione la tecla F9. Excel incrustará 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.
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.
Ver video
- Con un programa escalonado de comisiones, bonificaciones o descuentos, a menudo tiene que anidar sus funciones IF
- El límite de Excel 2003 fue de 7 declaraciones IF anidadas.
- Ahora puede anidar 32, pero no creo que deba anidar 32
- ¿Cuándo usaría alguna vez la versión de coincidencia aproximada de BUSCARV? Este es el tiempo.
- Traducir el programa de descuentos en una tabla de búsqueda
- VLOOKUP no encontrará la respuesta en la mayoría de los casos.
- Poniendo, True al final le dirá a BUSCARV que encuentre el valor un poco menos.
- Esta es la única vez que se debe ordenar la tabla VLOOKUP.
- ¿No quiere que la mesa VLOOKUP esté a un lado? Incrustarlo en la fórmula.
- F2 para editar la fórmula. Seleccione la tabla de búsqueda. Presione F9. Entrar.
Transcripción del video
Aprenda Excel del podcast, episodio 2030 - ¡Reemplazo de IF anidado con BUSCARV!
Estaré publicando un podcast de este libro completo, haga clic en la "i" en la esquina superior derecha para acceder a la lista de reproducción.
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. Muy bien, esto es muy común con un programa de comisiones, un programa de descuentos o un programa de bonificación, donde tenemos los niveles, diferentes niveles, ¿verdad? Si supera los $ 10000, obtiene 1% de descuento, $ 50000 5% de descuento. Debe tener cuidado cuando construya esta instrucción IF anidada, comience en el extremo superior si está buscando mayor que, o en el extremo inferior si busca menos que. Entonces B10> 50000, 20%, de lo contrario otro IF, B10> 250000, 25%, y así sucesivamente. Esta es solo una fórmula larga y complicada, y si su tabla es más grande, en Excel 2003, no podía anidar más de 7 declaraciones IF, estamos casi cerca del límite aquí. Puedes ir al 32 ahora, no creo que nunca debas ir al 32, e incluso si estás gritando "Oye, espera,¿Qué pasa con la nueva función que acaba de aparecer en Excel 2016, la versión de febrero de 2016, con la función IFS? " Sí, claro, aquí hay menos paréntesis, y 87 caracteres en lugar de 97 caracteres, sigue siendo un desastre, ¡deshagámonos de esto y hagámoslo con VLOOKUP!
Muy bien, aquí están los pasos, sigues esas reglas y las inviertes. Lo primero que tenemos que decir es, si tuviste $ 0 en ventas, obtienes un 0% de descuento, si tuviste $ 10000 en ventas, obtienes un 1% de descuento, si tienes $ 50000 en ventas, obtienes un 5% de descuento . $ 100000, 10% de descuento, $ 250000, 15% de descuento y, finalmente, cualquier cosa> $ 500000 obtiene el 20% de descuento, está bien. Ahora, muchas veces, cada vez que hablo de VLOOKUP, digo que cada VLOOKUP que cree terminará en FALSE, y la gente dirá "Bueno, espera un segundo, ¿para qué sirve la versión VERDADERA?" Es para este mismo caso en el que estamos buscando un rango, por lo que estamos buscando este valor, una VLOOKUP regular, por supuesto, 2, FALSE no encontrará 550000, ¡devolverá el # N / A!Entonces, en este caso muy especial, cambiaremos ese FALSO a VERDADERO, y eso le dirá a Excel "Busque el 550000, si no puede encontrarlo, dénos el valor que es menos". Entonces nos da el 20%, eso es lo que hace, ¿de acuerdo? Y esta es la única vez que su tabla VLOOKUP tiene que ser ordenada, todas las demás veces con, FALSE, puede ser como usted quiera. Y sí, podría dejar el, TRUE desactivado, pero siempre lo pongo allí solo para recordarme a mí mismo que este es uno de esos VLOOKUP extraños increíblemente peligrosos, y no quiero copiar esta fórmula en otro lugar. De acuerdo, entonces copiaremos y pegaremos fórmulas especiales, de acuerdo.¿bien? Y esta es la única vez que su tabla VLOOKUP tiene que ser ordenada, todas las demás veces con, FALSE, puede ser como usted quiera. Y sí, podría dejar el, TRUE desactivado, pero siempre lo pongo allí solo para recordarme a mí mismo que este es uno de esos VLOOKUP extraños increíblemente peligrosos, y no quiero copiar esta fórmula en otro lugar. De acuerdo, copiaremos y pegaremos fórmulas especiales, de acuerdo.¿bien? Y esta es la única vez que su tabla VLOOKUP tiene que ser ordenada, todas las demás veces con, FALSE, puede ser como usted quiera. Y sí, podría dejar el, TRUE desactivado, pero siempre lo pongo allí solo para recordarme a mí mismo que este es uno de esos VLOOKUP extraños increíblemente peligrosos, y no quiero copiar esta fórmula en otro lugar. De acuerdo, copiaremos y pegaremos fórmulas especiales, de acuerdo.
Próxima queja: su gerente no quiere ver la tabla de búsqueda, la quiere "Deshazte de esa tabla de búsqueda". Muy bien, podemos hacer eso insertando la tabla de búsqueda, mira esto, gran truco que obtuve de Mike Girvin en ExcelIsFun. F2 para poner la fórmula en el modo de edición, y luego seleccione con mucho cuidado solo el rango para la tabla de búsqueda. Presione F9, y toma esta tabla y la pone en nomenclatura de matriz, y luego simplemente presiono Enter así. Copie eso, pegue fórmulas especiales, y luego soy libre de deshacerme de la tabla de búsqueda, que todo continúa funcionando en la fila. Esto es una gran molestia si tienes que volver y editar esto, tienes que mirarlo con mucha claridad. La coma significa que vamos a la siguiente columna, el punto y coma significa que vamos a la siguiente fila, está bien,pero, en teoría, podría volver allí y cambiar esa fórmula si cambia uno de los números de la cadena.
Muy bien, usar una VLOOKUP en lugar de una instrucción IF anidada es el consejo # 32 en nuestro camino a 40, "40 mejores consejos de Excel de todos los tiempos", puede tener este libro completo. Haga clic en esa "i" en la esquina superior derecha, $ 10 por un libro electrónico, $ 25 por el libro impreso, de acuerdo. Así que hoy estamos tratando de resolver un bono de comisión escalonado o un programa de descuento. Los IF anidados son muy comunes, cuidado, 7 es todo lo que puede tener en Excel 2003, hoy puede tener 32, pero nunca debería tener 32. Entonces, cuándo usar la versión aproximada MATCH de BUSCARV, esto es todo. Tome ese programa de descuento, déle la vuelta, conviértalo en una tabla de búsqueda que comience con el número más pequeño y vaya al número más grande. VLOOKUP, por supuesto, no encontrará la respuesta, pero al cambiar VLOOKUP a TRUE al final, le dirá que encuentre el valor un poco menos,esta es la única vez que se debe ordenar la tabla. Si no desea ver esa tabla, puede incrustarla en la fórmula usando el truco de Mike Girvin, F2 para editar la fórmula, seleccione la tabla de búsqueda, presione F9 y luego Enter.
Muy bien oye, quiero agradecerte por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2030.xlsx
