Mostrar y ocultar gráficos con Autofiltro

Un pequeño truco que vi en el excelente blog DPH y que puede ser útil en ciertas circunstancias (o por lo menos dar la impresión que somos maestros en esto del Excel).
Supongamos que queremos representar estos datos




Una posibilidad es organizarlos en un “dashboard” como este



Pero con este truco podemos crear una lista desplegable de gráficos. El primer paso consiste en crear los gráficos, preferentemente en una nueva hoja (de acuerdo a nuestro principio de separar los datos de los reportes).
En esta nueva hoja, elegimos tantas celdas contiguas de una columna como gráficos tenemos (en nuestro caso cuatro). Ampliamos el alto de la fina y el ancho de la columna de manera la celda se acomode al tamaño del gráfico



En cada celda escribimos el título del gráfico que contendrá (o cualquier otro texto relevante). Movemos los gráficos de manera que ocupen los límites de cada celda, lo que ocultará el teto de la celda.
En la celda por encima del primer gráfico ponemos un texto como “Elegir gráfico a mostrar”



El último paso es activar Autofiltro



Como pueden ver, podemos elegir el gráfico a mostrar. Por ejemplo, elegimos Departamento 1



Todos los demás gráficos quedan ocultos.
Esta técnica puede ser muy útil cuando queremos mostrar datos relacionados cuyas escalas son distintas (por ejemplo Ventas, costos y ganancias).

Technorati Tags: MS Excel

Contar condicional distinguiendo entre mayúsculas y minúsculas

La función CONTAR.SI no distingue entre minúsculas y mayúsculas cuando contamos las apariciones de una palabra o cadena de texto en un rango.

Por ejemplo, en el rango A1.A12 aparece la palabra “manzana”, siete veces escrita con mayúsculas (MANZANA) y cinco veces en minúsculas (manzana)

Si queremos contar cuantas veces aparece MANZANA en el rango, estaríamos tentados a usar esta fórmula

=CONTAR.SI(A1:A12,"MANZANA")

El problema es que el resultado es 12, es decir, CONTAR.SI no distingue entre minúsculas y mayúsculas



La solución consiste en usar la función IGUAL(). Esta función compara dos textos y da como resultado VERDADERO o FALSO, tomando en cuenta minúsculas y mayúsculas.

Como ya hemos explicado en el pasado, podemos forzar a Excel a convertir VERDADERO en 1 y FALSO en 0, multiplicando estos valores lógicos por 1 (o usándolos en alguna operación aritmética). Sobre esta base podemos escribir esta fórmula matricial

={SUMA(--IGUAL("MANZANA",A1:A12))}

El doble signo “-“ a la izquierda de IGUAL tiene la función de convertir la matriz de VERDADERO y FALSO generada por la función en un vector de 1 y 0, que son sumados por la función SUMA.

Como toda fórmula matricial, ésta debe ser introducida apretando simultáneamente Ctrl+Mayusculas+Enter (lo que hace que aparezcan los corchetes).

Si reemplazamos “MANZANA” por “manzana”, veremos que el resultado es 5

Technorati Tags: MS Excel

Rangos dinámicos con Listas

Una tarea frecuente en Excel es crear rangos dinámicos. La técnica más difundida es crear un nombre (Insertar-Nombres-Definir) con una fórmula que combine DESREF y CONTARA.

Una técnica alternativa más sencilla es usar Listas (Excel 2003) o Tablas (Excel 2007). Esta funcionalidad es muy útil y permite simplificar nuestros modelos en Excel.

En una nota anterior mostramos como crear con facilidad un gráfico dinámico usando Listas. En esta nota mostraremos cómo crear un modelo dinámico.

Como ejemplo construiremos un modelo para manejar el inventario de un almacén/depósito. En un cuaderno Excel creamos dos hojas: “movimientos” y “saldos”. En la primera anotamos los movimientos de los productos en el almacén (entradas – salidas); en mostramos los saldos actualizados de los productos.





En la hoja “movimientos” tenemos ahora un cuadro de datos en el rango A1:D31. Para transformar este rango en Lista, usamos el menú Datos-Lista (o Ctrl+Q)






Al apretar Aceptar veremos que Excel selecciona todo el rango, activa Autofiltro y en la primer fila libre aparece un asterisco azul. A partir de este momento, cada vez que agreguemos datos a la lista, ésta se expandirá automáticamente.

En la celda A1 de la hoja “saldos” combinamos texto y funciones para crear un título dinámico


="Saldos a la fecha "&TEXTO(MAX(movimientos!C2:C31),"dd/mm/yyyy")

Como pueden ver usamos una referencia estática al rango de las fechas en la hoja “movimientos”.
Para calcular los saldos actualizados usamos la fórmula

=SUMAR.SI(movimientos!$A$2:$A$31,saldos!A4,movimientos!$D$2:$D$31)


También aquí usamos rangos “normales”.

Ahora agregamos los movimientos del día 08/01/2009



Cuando pasamos a la hoja “saldos” vemos que tanto el título como los saldos se han actualizados. Así de simple!



En Excel 2007, el mecanismo es similar, pero la funcionalidad Lista ha pasado a llamarse Tabla. Para convertir un rango en Tabla usamos el icono Tabla en la pestaña Insertar



Tanto en Excel 2003 como en Excel 2007, la forma más cómoda y eficiente de agregar datos en la lista/tabla, es usando Tab.



Technorati Tags: MS Excel