Funciones SI (IF) en Excel – Como construir fórmulas anidadas compactas

Una tarea bastante corriente en Excel es construir fórmulas que combinan varias funciones. Una de las combinaciones "clásicas" es con la función SI (IF en la versión inglesa).
La función SI analiza una propuesta (Jorge mide más de 1.80 metros) y de acuerdo al resultado (verdadero o falso) da una respuesta (Jorge es alto). Si la propuesta resulta "falsa" el resultado será distinto (Jorge es bajo).
Por supuesto se pueden combinar (anidar) varias funciones SI, de manera que cada una sirva de argumento para la otra. Excel acepta hasta 7 funciones SI en una misma fórmula. El problema es que también con 3 o 4 funciones SI combinadas, la fórmula resulta larga y complicada. Sobre esto he escrito en la entrada sobre el uso de la función ELEGIR (Choose, en inglés).
En este ejemplo (les recomiendo descargar el archivo del ejercicio) veremos cómo construir fórmulas que anidan varias funciones SI, en forma compacta.
Para esto es necesario conocer el concepto de "nombres" (NAMES) en Excel. Si no están familiarizados con este concepto, pueden leer mis entradas sobre el tema:
Uso de Nombres (NAMES) en MS Excel
Usar Nombres en Excel para Formato Condicional
Uso de NOMBRES (NAMES) en Excel – Validación de Datos

En nuestro ejemplo suponemos la existencia de una tienda que tiene un club de clientes. Cada cliente puede pertenecer a una de las categorías: Platinum, Gold o Regular. De acuerdo a la categoría el miembro puede recibir determinados descuentos e acuerdo a esta tabla:


Además la tienda vende con el sistema "1+1", al comprar dos productos se recibe un descuento del 50% sobre el más barato.
Este ejercicio fue parte de un examen en un curso sobre Excel. Los alumnos tenían que proponer una fórmula que calculara el descuento adicional a los clientes.


Las fórmulas propuestas por los alumnos variaron entre 4 y 5 funciones SI combinadas. Si bajaron el archivo, pueden ver en la columna G la fórmula con 5 funciones SI

=SI(C2="Platinum",SI(Y(CONTAR(D2:E2)=2,F2>10000),F2*0.2,SI(Y(CONTAR(D2:E2)=2,F2<=10000),F2*0.1,SI(Y(CONTAR(D2:E2)=1,F2>10000),F2*0.1))),SI(Y(C2="Gold",CONTAR(D2:E2)=2,F2>15000),F2*0.1," "))

Si bien esta fórmula resuelve el problema, se puede escribir de otra manera usando solamente 2 funciones SI. La regla a seguir dice que la cantidad de funciones SI a combinar equivale a la cantidad de casos menos 1. Si bien hay cinco casos de descuento, como podemos ver en la tabla arriba, que de hecho hay tres casos: los que reciben 20% de descuento adicional; los que reciben 10% y los que no reciben descuento adicional.

Para construir la fórmula, primero defineremos los nombres que contengan las fórmulas para determinar el tipo de descuento.
Para esto escribimos las fórmulas en celdas laterales, lo que nos permitirá verificar su funcionamiento.

Fíjense en las fórmulas en el rango J2:M7. Los nombres y sus fórmulas son:
P_2_10 --- =Y(C2="Platinum",CONTARA(D2:E2)=2,F2>10000)
P_2 -------- =Y(C2="Platinum",CONTARA(D2:E2)=2)
P_10 ------- =Y(C2="Platinum",F2>10000)
G_2_15 --- =Y(C3="Gold",CONTARA(D3:E3)=2,F3>15000)

La función CONTARA nos permite contar la cantidad de productos comprados en cada compra. Usamos esta función y no CONTAR para evitara resultados erróneos cuando una de las celdas del rango está en blanco.
Los nombres los definimos usando el menú Insertar---Nombre---Definir

La forma más práctica de construir los nombres, es copiando las fórmulas de la barra de fórmulas y pegándolas en la ventanilla "Se refiere a" del diálogo Definir Nombre.
MUY IMPORTANTE: al construir los nombres la celda activa debe ser la primer celda en el rango relevante, en nuestro caso H2. Esto se debe a que en nuestras fórmulas usamos direcciones relativas (sin los símbolos $).

Dado que las condiciones P_2, P_10 y G_2_15 dan como resultado el mismo descuento (10%), las combinaremos en una sola condición usando la función O (OR en su versión inglesa).
Finalmente podemos escribir nuestra fórmula de la siguiente manera:

=SI(P_2_10,F2*0.2,(SI(O(P_2,P_10,G_2_15),F2*0.1,"")))

Como pueden ver (en la columna H), no sólo la fórmula es mucho más compacta, si no que el uso de nombres la convierte en explícita.


Categorías: Funciones&Formulas_, Manejo de Datos_


Technorati Tags: Nombres, Funcion SI en Excel, IF