Usar ELEGIR (CHOOSE) en lugar de SI (IF) en MS Excel

martes, febrero 14, 2006

En esta nota he hablado sobre la capacidad de Excel de combinar funciones. Una de las combinaciones más usuales que veo, es con la función SI (IF en la versión inglesa). La función SI nos permite efectuar cálculos de acuerdo a ciertas condiciones. Y cuando hay una cadena de condiciones, entonces creamos una cadena de funciones SI en nuestra fórmula.
El problema con esta técnica es que terminamos creando "fórmulas chorizo" que, diez minutos después de haberlas escrito, no somos capaces de descifrar.
Esto es lo que le pasó a mi amigo Daniel. Describamos la situación de mi amigo de la siguiente manera:
Después de haber realizado unos experimentos anotamos los resultados en una hoja de Excel. Estos resultados pueden ser positivos o negativos. De acuerdo a esto debemos efectuar una operación de suma con los resultados. El cuadro es el siguiente, tres pruebas (test 1, test 2, test 3) y cinco posibilidades (pueden bajar el cuaderno con las fórmulas )





Mi amigo empezó a escribir una fórmula que combinaba (nesting) funciones SI (IF) y funciones Y(AND). La idea era escribir una única fórmula compacta, de acuerdo al famoso dicho de Gracián que lo bueno si breve, dos veces bueno.
Después de tres horas de enconada lucha con Excel y ya al borde de una crisis nerviosa, me llamó para que le ayudara.
Mi consejo fue, que si bien hay cierta sabiduría en lo de Gracián, mi abuelita también decía que si el atajo fuera bueno, no existirían los caminos. Así que le recomendé ir por el camino más largo que significa dividir la fórmula en varias fórmulas intermedias.
Paso a explicar. Para lograr en una única fórmula resolver el intríngulis de mi amigo debemos descerrajar semejante fórmula:

IF(AND(B2>0,B3>0,B4>0),B2-B3-B4,IF(AND(B2>0,B3<0,b4>0),B2+B3-B4,IF(AND(B2>0,B3>0,B4<0),b2-b3+b4,if(and(b2>0,B3<0,b4<0),b2+b3+b4,0))))

He aquí el resultado

Mi propuesta fue crear fórmulas que indicaran la situación de los resultados y usarlas como argumentos en una función ELEGIR (CHOOSE en inglés), la que muchas veces es un buen sustituto de la función SI (IF). En las celdas al lado del cuadro de condiciones hemos agregados unas fórmulas que nos indican la relación entre los resultados. Estas fórmulas combinan la función SI con la función Y (AND en la versión inglesa). Cada relación recibe un número de orden. Si la relación se cumple, la función muestra el número de orden (1, 2, 3, o 4), caso contrario el resultado será 0.

En la celda F8 escribimos la siguiente fórmula ELEGIR(SUMA(control),B2-B3-B4,B2+B3-B4,B2-B3+B4,B2+B3+B4,0) que como verán es mucho más compacta que la anterior. El resultado será, por supuesto, el mismo

Esta técnica nos permite un mayor control de la lógica de nuestras fórmulas y el consecuente ahorro de tiempo. Y como decíamos al principio, no siempre el camino más corto es el más rápido.



Categorías: Funciones&Formulas_, LOOKUPS_

15 comments:

Jaizki 30 junio, 2006 17:28  

No sería más fácil esta fórmula:

=SI(B2<0;0;B2-ABS(B3)-ABS(B4))

Jorge L. Dunkelman 03 julio, 2006 17:54  

Hola Jaizki,
si es más compacta. Gracias por el aporte.

CESARROBLES 29 agosto, 2006 05:31  

Hola soy Cesar ,
Gracias por todo el apórte q das en esta pagina , yo lo estoy aprovechando al maximo, por ahora soy principiante pero no por mucho tiempo, ya te enviare algunos problemitas q en el futuro los tenga, gracias.

javisegura 06 octubre, 2006 19:12  

Hola,soy Javier, está muy interesante tu página y estoy aplicando todos los aportes que das, desgraciadamente no he encontrado ningun post que me ayude con un problemilla que tengo, te lo dejo a ver si me puedes colaborar y si me logro hacer entender.

Debo condicionar una celda con ciertos valores predeterminados al resultado que me dé en otra celda así:

Si una celda tiene un valor X y ese valor está en un rango entre 0 y 1 debe poner 100, pero si X está en un rango de 1,1 a 2 debe poner 200, pero si está entre 2,1 y 3 debe poner 300, y así sucesivamente.

Agradecería que me enviaras un correo electrónico si me puedes ayudar con la respuesta a este problema.

Jorge L. Dunkelman 07 octubre, 2006 00:57  

Hola Javier

te estoy mandando una solución por correo electrónico. Si hay dudas, no dejes de preguntarme

Silas77 07 noviembre, 2007 17:32  

Hola amigo Jorge. Soy David.
Podrías poner a nuestra disposición la solución que le remitiste javisegura? Me parece interesante. Por cierto, no puedo descargar el cuaderno de trabajo con las fórmulas de tu ejemplo del ELEGIR de esta pag.
Gracias

Jorge L. Dunkelman 07 noviembre, 2007 22:17  

Hola David

acabo de corregir el enlace.
El archivo con la solución a la consulta de Javier se puede descargar aquí

ALFREDO 03 marzo, 2008 15:31  

hola mi nombre es ALFREDO RIVAS me gustaria saber como hago para comparar dos fechas y lelegir la ultima 0 mejor la mayor

Jorge L. Dunkelman 03 marzo, 2008 18:02  

Hola Alfredo

las fechas en excel son números. Por lo tanto, comparar fechas es como comparar números.
Puedes ver mi nota sobre fechas y tiempo en Excel.

Anónimo,  30 abril, 2008 17:26  

Hola Jorge..mi pregutna es la siguiente...tengo 3 columnas, en las dos primeras tengo numeros y en la tercera tengo una lista despegable a traves de validacion de datos. Lo que quiero hacer es por ejemplo si en la tercera columna escojo multiplicar, en una 4 columna, se multiplicara la primera columna con la segunda, si escogo dividir, se dividira la primera con la segunda y asi sucesivamente. Obviamente se podria utilizar la funcion SI con este ejemplo sencillo, pero con la limitante de que solo puedo anidar 7 SI. Te lo pregunto con un ejemplo sencillo, porque en un ejemplo mucho mas complejo, tengo 15 opciones y de acuerdo a la escogencia de una de las 15 opciones, hara operaciones diferentes. Trate de hacerlo con la opcion Elegir, pero no pude, tambien colocando nombres a formulas, pero no se como hacer que si se escoge una opcion, aplique una determinada formula...Me podrias colaborar?..Muchismias gracias

Jorge L. Dunkelman 30 abril, 2008 19:50  

Una posibilidad sería crear una función definida por el usuario (UDF), que de acuerdo a a un número (una de las variables de la función), use la función deseada. Posiblemente publique algo al respecto.

Anónimo,  14 julio, 2008 22:10  

Jorge buen dia...
Tengo un programa de excel que me pasaron para hacer calculos de recargos pero observe que al escojer un nombre de una lista desplegable en otro renglon diferente aparece un numero pero ese renglon no tiene formula, y elijo otro nombre y se cambia automaticamente pero insisto no veo ninguna formula como se puede realizar por favor me puedes ayudar, ya que me serviria mucho para utilizarlo en otros archivos.

Gracias.
Gael

Jorge L. Dunkelman 14 julio, 2008 22:50  

Hola Gael
tienes que usar Validación de datos.
En el blog hay varias notas sobre el tema. Puedes empezar leyendo ésta.

Marcos Cavagnaro 20 marzo, 2012 21:46  

Excelente Jorge y saludos desde Guayaquil - Ecuador

gerson joel alvarez martinez 20 marzo, 2013 15:14  

hola quisiera saber si me puedes ayudar con un problema q tengo en excel estoy haciendo una facturacion de energia electrica de diferentes distribuidoras las e puesto en una lista y ademas si el consumo es abajo del 99kwh me hace el calculo del subsiiidio para todas las distribuidoras pero no secomo hacer esa parte para mostrarlo en la facturacion aca dejounaidea maas clara:

acaestan los calculos
caess clesa eeo
subsidio 1.01 2.5 1.36


los nombres caess clesa eeo los e puesto en forma delista en mi factura pero lo q quierohacer esq si el elconsumo fue menos de 99 kwh y yo sellecciono de mi lista me ponga el subsidio de esa compañia y si elconsumo es >99kwh q ponga nohay subsidio.

gracias por el aporte de antemano Jorge L . Dunkelman

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP