martes, febrero 14, 2006

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

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_

13 comentarios:

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

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

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

    ResponderBorrar
  3. Hola Javier

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

    ResponderBorrar
  4. 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

    ResponderBorrar
  5. Hola David

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

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

    ResponderBorrar
  7. 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.

    ResponderBorrar
  8. 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

    ResponderBorrar
  9. 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.

    ResponderBorrar
  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

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

    ResponderBorrar
  12. Excelente Jorge y saludos desde Guayaquil - Ecuador

    ResponderBorrar
  13. 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

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.