Fórmulas con condiciones múltiples en Excel

martes, febrero 15, 2011

Supongamos un formulario en Excel donde controlamos un proceso de aprobación de créditos compuesto de tres partes: presentación, proceso y aprobación



Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a “En proceso” y finalmente al introducir una fecha en C4, Estatus muestra “Aprobado”



La solución más inmediata es crear una fórmula con la función SI

=SI(A4<>"",SI(B4<>"",SI(C4<>"","Aprobado","En proceso"),"Presentado"),"")

Esta fórmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o 2010 podemos anidar hasta 64 niveles de SI (en comparación a los 7 niveles en Excel Clásico), por encima de las 3 o 4 condiciones la fórmula se vuelve compleja y difícil de manejar.

Podemos superar este problema creando una fórmula con la función ELEGIR

=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")



Otro problema con estas fórmulas es que no toman en cuenta errores. Por ejemplo, que introduzcamos una fecha en “Proceso” sin que hayamos puesta una fecha anterior en “Presentación”.

Para superar este inconveniente podemos usar esta fórmula que combina ELEGIR con MMULT y una matriz de constantes

=ELEGIR(MMULT((A4:C4<>0)*1,{4;2;1})+1,"","Error 3","Error 2","Error 2","Presentado","Error 1","En proceso","Aprobado")



MMULT multiplica los miembros de la matriz (A4:C4<>0) por los valores de la matriz {4;2;1} determinando así el valor de la variable de ELEGIR. Este video muestra el proceso de cálculo de la fórmula cuando sólo la celda A4 contiene una fecha



También esta fórmula tiene un inconveniente. Es difícil de mantener, por ejemplo, si queremos agregar condiciones o cambiar valores.

La mejor solución, y que está inspirada en una respuesta de Ron Coderre en uno de los foros en los que participo, es usar una tabla auxiliar para determinar los valores y SUMAPRODUCTO con BUSCARV (CONSULTAV en Excel 2010)



La fórmula es (o parece!) sencilla

=CONSULTAV(SUMAPRODUCTO(((A4:C4)<>"")*{1,10,100}),$I$4:$J$11,2,0)

Como en la fórmula anterior, multiplicamos dos matrices para obtener un valor que usamos en CONSULTAV (o BUSCARV en versiones anteriores a Excel 2010) para obtener el texto correspondiente en Estatus



Para determinar los valores de la columna Indicador, usamos un sistema “pseudo-binario”. Esta imagen explica el método



Cada fila en la tabla es una de las posibilidades; en cada posibilidad indicamos con un 1 si la celda contiene una fecha; luego sumamos las celdas de la fila donde la columna Fecha 1 le asignamos el valor 1 (unidades), la columna Fecha 2 el valor 10 (decenas) y a la columna Fecha 3 el valor 100 (centenas).

El archivo con los ejemplos se puede descargar aquí.

10 comments:

Anónimo,  16 febrero, 2011 14:11  

Interesante... voy a probarlo.
Gracias,como siempre, por compartir con nosotros :)

Blaine

carolsurz 17 febrero, 2011 21:08  

Muy buen post Jorge,
Estas funciones te hacen discurrir bastante; ya que estoy 2 dudas:
1) He intentado hacer la fórmula del útimo método por separado y al hacer A4:C4<>""*{1,10,100}, quiero que me salga una matriz de 1 fila y 3 columnas...me sale sin problema, lo que pasa que en vez de darme número me sale Verdadero ó Falso...

2) El vector que pones (1, 10, 100)...supongo que es 1 fila y 3 columnas (para así poder multiplicar al vector A4:C4; sin embargo, en Excel 2007, las columnas se separaban por ";".

Un Saludo

Jorge L. Dunkelman 17 febrero, 2011 21:41  

Hola

1 - Para que te salgan los resultados numéricos tienes que introducir la fórmula en forma matricial (Ctrl+Mayúsculas+Enter)

2 - Los separadores dependen de las definiciones regionales de Windows, no de la versión de Excel.

carolsurz 19 febrero, 2011 17:08  

Hola Jorge de nuevo y perdona la insistencia:
Tengo instalado en el mismo ordenador Excel 2003 y Excel 2010; para introducir una matriz manualmente:
-En 2003 (y en 2007): Para separar columnas (1 2 3), utiliza ";" y para filas "\"
-En 2010: Columnas "\" y filas ";"....
¿Entonces, sabes si cambio eso en Excel 2010?

- Yo miré lo que me dijiste de la configuración de Windows, pero ni ninguna opción

Saludos

Jorge L. Dunkelman 19 febrero, 2011 18:51  

Hola,

no, no ha habido ningún cambio en la sintaxis, pero encontré este artículo de Microsoft donde explican que "Constantes de matriz inesperadamente puede contener la barra invertida (\) si se cambia el carácter del símbolo decimal a una coma (,) o punto y coma (;) en el equipo". El enfasis puesto en la palabra "inesperadamente".

carolsurz 19 febrero, 2011 19:56  

Muchas Gracias Jorge, eficacia 100%...
Seguiré mirando porque lo que me extraña es que pasa en Excel 2003 y 2010 el cambio, pero ambos los tengo en el mismo ordenador, por tanto, con la misma configuración.

Gracias de nuevo y un saludo

Anónimo,  21 mayo, 2013 23:48  

Felicidades me han sacado de un apuro con estas formulas sigan adelante

Anónimo,  23 junio, 2013 09:51  

Muy buenas Jorge necesito ayuda (soy nuevo en esto)
en una hoja de excel con 10 columnas donde se ponen fechas, necesito saber los días Naturales sumando todas las fechas de las columnas pero quitando el periodo de vacaciones de agosto
Ejemplo Fecha inicio 01/05/13 Final 15/05/13; Inicio 15/06/13 Final 15/09/13 y así varias fechas
Un saludo y muchas gracias

Jorge Dunkelman 23 junio, 2013 17:27  
Este comentario ha sido eliminado por el autor.
Jorge Dunkelman 23 junio, 2013 17:29  

Fijate en esta nota.
También te sugiero ver todas las notas sobre el tema pulsando el enlace "Fechas y hora" en la nube de etiquetas.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP