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

sábado, marzo 11, 2006

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: , ,

18 comments:

Anónimo,  31 diciembre, 2007 05:59  

Hola Jorge,

Felicidades por su blog, parece haber un problema con la liga, del archivo, agradecerìa su ayuda para corregirlo.
Saludos. Arturo

Jorge L. Dunkelman 01 enero, 2008 17:38  

Hola Arturo

hat algunas notas sobre tablas para liga de fútbol. A cuál te refieres?

Anónimo,  01 febrero, 2010 19:12  

Jorge
Muchas gracias por la información brindada en su blog.
intenté descargar el archivo pero el link aparentemente no funciona.
Que continuén los éxitos
Un afectuoso abrazo desde Ramos Mejía Bs As Arg.
Walter Colque E mail walter_cq@hotmail.com

Jorge L. Dunkelman 01 febrero, 2010 21:53  

Hola Walter,

acabo de actualizar el enlace.

Anónimo,  04 febrero, 2010 13:11  

Estimado Jorge

Muchas gracias por su pronta respuesta. Ya he descargado ok el archivo .xls

Un abrazo

Walter Colque

Juan R Garces 04 agosto, 2012 01:37  

Buenas tardes. Necesito ayuda para elaborar una fórmula con la función condicional SI.
Necesito pagar una comisión de acuerdo al valor vendido. Si el valor es inferior a 18 multiplicaré ese valor por 7,5%. Si el valor es de 18 o más pero inferior a 21, multiplicar por 8,5%. Si el valor es de 21 en adelante multiplicarlo por 9,5%. Muchas gracias por su valiosa ayuda.

Jorge L. Dunkelman 06 agosto, 2012 19:03  

Juan,
¿por qué usar SI y no la técnica que muestro en esta nota, o en esta otra?

Anónimo,  27 marzo, 2013 01:08  

Buenas tardes Jorge,
espero que te encuentres muy bien. Tengo una duda acerca de como hacer una formula en excel la cual describa que del 1 al 3 es color verde, de 3 a 4 es color amarillo y del 4 al 5 de color rojo, no se si me entiendas, pero espero que me puedas ayudar.

Atte

Rodrigo

Jorge L. Dunkelman 27 marzo, 2013 06:40  

Rodrigo, no me queda claro. ¿Lo que buscas es que aparezca la palabra "amarillo" (o verde o rojo) en la celda que contiene la fórmula?

Anónimo,  02 mayo, 2013 15:10  

BUEN DIA necesito ayuda, yo tengo que llevar datos en un cuadro que lleva 4 hojas y tengo que meter los datos uno por uno en cada hoja. la ayuda es como hago para cuando meta ej; en nombre. cedula. me aparescan tambien en las otras ojas y no volverlo a copiar? le agradezco la ayuda. gracias

Jorge Dunkelman 03 mayo, 2013 16:35  

Si los datos están en las mismas celdas de cada hoja, podrías seleccionar las hojas relevantes (apretando la tecla Ctrl); cada dato ingresará en la misma celda de las hojas seleccionadas.

Anónimo,  03 septiembre, 2013 07:19  

cOMO PUEDO DAR COLOR A UNA CELDA SI CUMPLE UNA CONDICIÓN

Anónimo,  03 septiembre, 2013 08:23  

Gracias Jorge: me puedes indicar como funciona el formato condicional?

Jorge Dunkelman 03 septiembre, 2013 08:53  

En mi blog hay una serie de notas sobre el tema a la que puedes acceder apretando el enlce Formato Condicional en la nube de etiquetas *en la parte superior de la plantilla).
También puedes hacer una búsqueda en Google o, mejor aún, usar la ayuda en línea de Microsoft.
En resumen, no faltan fuentes.

Bernardino Hernandez 07 febrero, 2014 00:22  

SE PUEDE CAMBIAR EL COLOR DE TEXTO CON LA FUNCION DE IF ANIDADO?

Jorge Dunkelman 07 febrero, 2014 10:07  

No se puede modificar ningún objeto con fórmulas, pero si con Formato Condicional que puede usar fórmulas. Se puede construir cualquier fórmula válida, incluida SI anidado, pero el resultado debe ser FALSO o VERDADERO.


Anónimo,  26 junio, 2014 22:43  

Muchas Gracias!!! Me sirvió demasiado para mi trabajo!!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP