El tema de hoy: sumar y contar con más de una condición. Ya he escrito sobre este tema, pero mi intención hoy es dar una idea general del método.
Excel ofrece dos funciones para sumar o contar condicionadamente: SUMAR.SI (SUMIF en la versión inglesa) y CONTAR.SI (COUNTIF). Estas funciones aceptan sólo una condición y su uso es sencillo.
La cuestión es cómo sumar o contar cuándo deben cumplirse varias condiciones.
Veamos un ejemplo sencillo de contar con una condición. Tenemos una lista de nombres, sexo y edades, y queremos saber cuántas personas hay en la lista mayores de 30 años.
Como pueden ver, la formula en la celda C13 es bien sencilla: =CONTAR.SI(C2:C11,">30")
Pero digamos que ahora queremos saber cuántas personas mayores de 30 años y de sexo masculino hay en la lista. La función CONTAR.SI no acepta dos condiciones. La solución es utilizar fórmulas matriciales o SUMAPRODUCTO como ya he mostrado.
Cómo funciona esto? Cuando Excel evalúa una expresión de tipo A1= B1 puede dar uno de dos resultados: FALSO (FALSE en inglés) o VERDADERO (TRUE). Falso está asociado con la cifra 0 (cero) y VERDADERO con el 1 (uno).
Cuando establecemos la condición "mayor de 30" Y "masculino", la palabra Y está asociada a la operación de multiplicar.
Como ejemplo, he escrito en el rango de celdas D2:D11 la fórmula =Y(C2>30,B2="masculino")
Ahora he agregado en el rango E2:E11 la formula =(C2>30)*(B2="masculino")
Como se puede ver, si sumamos todo el rango E2:E11 obtendremos el resultado 2, que es el número de personas que cumplen con las dos condiciones.
En lugar de efectuar la operación en dos etapas, primero evaluar cada miembro y luego sumar los resultados, podemos hacerla de una vez usando la función SUMAPRODUCTO de esta manera:
=SUMAPRODUCTO(((C2:C11)>30),((B2:B11)="masculino"))
la multiplicación por 1 dentro del paréntesis se hace para convertir los valores FALSO y VERDADERO a valores numéricos (0 y 1).
Otra alternativa es usar la función SUMA en una fórmula matricial, esto es, ingresándola apretando al mismo tiempo Control+Shift+Enter.
La fórmula es: ={SUMA(((C2:C11)>30)*((B2:B11)="masculino")*1)}
De esta manera podemos combinar varias condiciones que deben cumplirse simultáneamente.
En el caso de condiciones incluyentes, digamos, personas mayores de 40 o menores de 30, usaremos el operador "+" (sumar) en ligar de "*" (multiplicar):
={SUMA((((C2:C11)<30)+((c2:c11)>40))*1)}
Categorías: Funciones&Formulas_, Formulas Matriciales_
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_
La función ALEATORIO (RAND en la versión inglesa) de Excel genera números aleatorios. Esta función no tiene argumentos y es recalculada nuevamente cada vez que se produce un cambio en la hoja, excepto que el modo de cálculo sea "manual".
Los números que produce ALEATORIO (RAND) van de del 0 al 1.
En esta hoja usamos la fórmula =ALEATORIO() en el rango A2:A11
Si queremos producir una serie de números aleatorios enteros, debemos combinar la función ALEATORIO con la función REDONDEAR (ROUND), o con alguna otra función de este tipo como REDONDEAR.MAS o REDONDEAR.MENOS.
En esta hoja, usamos la fórmula combinada =REDONDEAR(ALEATORIO()*100,0) para producir números aleatorios entre 0 y 100
Si queremos producir números aleatorios que se encuentren en un rango entre dos números, digamos entre 12 y 88, podemos usar la fórmula
=REDONDEAR(ALEATORIO()*(88-12)+12,0)
Con esta técnica podemos generar, por ejemplo, números aleatorios para la lotería. Aquí, por ejemplo, el Loto sortea 6 números entre el 1 y el 36. El problema de esta fórmula es que puede generar números duplicados, como en este ejemplo
Supongo que hay más de una técnica para superar este problema. Mencionaré aquí dos de ellas.
La primera es, simplemente, recalcular la hoja (presionar F9) hasta lograr una serie sin números repetidos.
La otra consiste en condicionar la creación de los números aleatorios. Esta técnica consiste en generar una referencia circular intencional, para lo cual es necesario que la opción Iteración en Opciones/Cálculo esté activada (pueden bajar el archivo del ejemplo con las fórmulas 
aqui)
La fórmula que usamos en el rango A2:A11 es
=SI(SUMA($B$2:$B$11)<>10,REDONDEAR(ALEATORIO()*(36-1)+1,0),REDONDEAR(ALEATORIO()*(36-1)+1,0))
en el rango B2:B11 usamos la fórmula =CONTAR.SI($A$2:$A$11,A2). Esta fórmula controla si hay números duplicados. Si el resultado es 1, el número aparece una sola vez.
La fórmula en el rango A2:A11 controla si todos los resultados en el rango B2:B11 no suman 10, es decir que hay algún numero duplicado; en ese caso calcula un número aleatorio. Si la condición lógica no se cumple, vuelve a calcular. Como ven hay aquí una referencia circular. Como hemos activado la opción "Iteración", Excel intentará encontrar la serie de 10 números que cumplan con la condición. Si no la encuentra, deberemos pulsar nuevamente F9 (recalcular) hasta encontrar la solución.
Esta técnica fue sugerida por John Walkenbach en su libro "Microsoft Excel 2000 Formulas".
Categorías:
Funciones&Formulas_, Varios_
