En varias de mis notas anteriores he mencionado el uso de "nombres" en la composición de fórmulas. En este contexto he utilizado "nombres" para señalar rangos o celdas (en definitiva, una celda es un rango de una sola celda).
En cuanto descubrí esta funcionalidad que ofrece Excel comencé a utilizarla profusamente, y siempre me ha llamado la atención que usuarios de nivel intermedio e inclusive alto, no la empleen. Así que vamos a hablar un poco sobre el uso de nombres en Excel.
Por lo general hablamos de rangos nominados ("named ranges" en inglés) pero enseguida veremos que los nombres en Excel son en realidad otra cosa.
El ejemplo más sencillo es de este tipo: en una 
hoja de Excel tenemos una tabla con las ventas mensuales de un agente; en alguna otra celda tenemos el porcentaje de comisión que le corresponde:
En lugar de calcular la comisión que le corresponde a nuestro agente con la fórmula =SUMA(B2:B13), podemos nominar el rango B2:B13 como "ventas" y la celda B15 "porcentaje". Nuestra fórmula se verá entonces: =SUMA(ventas)*porcentaje. En este ejemplo sencillo las ventajas de usar nombres no parecen gran cosa, pero imagínense una fórmula complicada con referencias a otras hojas. Esta forma "auto-explicatoria" de escribir las fórmulas puede ser de gran ayuda. Y esto no es todo, pero las demás ventajas las veremos más adelante.
Hay varias formas de crear nombres. La más sencilla es usar el "cuadro de nombres"
La técnica es sencilla: primero marcamos el rango, luego escribimos el nombre en el cuadro de nombre y pulsamos Enter.
Hacemos lo mismo con la celda B15, y ya podemos escribir nuestra fórmula con nombres:
Para ver cómo interpreta Excel los nombres, abrimos el menú Insertar--->Nombre--->Definir
Aquí podemos ver que la definición del rango va precedida por un signo "=": =Hoja1!$B$2:$B$13, lo que nos dice que Excel interpreta los nombres como fórmulas y no precisamente como rangos. Por ejemplo, podemos crear un nombre que se llame "total_de_ventas" cuya definición es =SUMA(Hoja1!$B$2:$B$13). Podemos entonces rescribir la fórmula en la celda B16 de la siguiente manera: =total_de_ventas*porcentaje
Esto es muy importante porque podemos definir no solo rangos sino también fórmula los que nos permitirá hacer algunos "pases mágicos". Estos serán el tema de mi próxima entrada.
Por ahora me queda sólo agregar que no se pueden borrar nombres en el cuadro de nombres, como parecería lógico pensar. Para borrar nombres hay que activar el menú Insertar--->Nombre--->Definir, elegir el nombre que queremos borrar y pulsar el botón "Eliminar".
En la próxima entrada veremos cómo usar nombre para crear rangos dinámicos y como crear listas para validación de datos.
Categorías: Funciones&Formulas_, Varios _
Technorati Tags: NAMES, Nombres en Excel
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_
