Tablas dinámicas en lugar de Autofiltro.

Uno de mis lectores me pregunta si se puede aplicar autofiltro simultáneamente a varias hojas. En sus propias palabras:

¿se puede aplicar autofiltro a varias hojas de una sola vez? Tengo 25 hojas y
quiero filtrar en todas desde una fecha determinada. ej: desde ej 01/06/2007.

La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".

Supongamos un cuaderno Excel (que se puede descargar aquí) con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.

Construimos la tabla dinámica con rangos de consolidación múltiples








Agregamos las listas de las distintas hojas



Y ponemos la tablas en una hoja aparte (Reporte)



Obtenemos



Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y movemos el elemento Página1 a la zona de campos de columna



Reemplazamos los rótulos de la tabla por otros más significativos



Para filtrar los datos según fechas abrimos el menú de ítems del campo



Y seleccionamos los elementos deseados



Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos elementos (fechas) en nuestras listas el método puede ser muy trabajoso.

Technorati Tags: MS Excel

Filtro Avanzado – otros usos

Continuando al nota anterior sobre Filtro Avanzado, en esta veremos otro uso interesante de esta herramienta.
Supongamos esta lista que muestra la fecha de recepción de las órdenes de nuestros clientes y la fecha en que se efectuó el despacho




Si queremos obtener una lista de los despachos que han tardado más de 7 días en salir, aplicamos Filtro Avanzado usando esta fórmula como criterio

=(C5-B5)>7



Como hemos explicado en la nota anterior, el encabezamiento distinto del rango de los criterios debe ser distinto al de las columnas de la tabla. También podemos prescindir de encabezamiento para el criterio.



El resultado es



También podemos usar funciones más avanzadas. Por ejemplo, si queremos ver todas las órdenes que se despacharon por lo menos 5 días hábiles después de recibidas, usamos la función NETWORKDAYS

Technorati Tags: MS Excel

Usando Filtro Avanzado en Excel

Autofiltro permite filtrar una lista de acuerdo a una serie de criterios. Por cada columna podemos elegir hasta dos criterios, combinándolos con los operadores Y (AND) u O (OR).
Autofiltro es una gran herramienta para filtrar datos, pero en ciertas ocasiones resulta limitada. En muchos de esos casos podemos utilizar Filtro Avanzado.




En una nota anterior en este blog he mostrado uno de los usos de Filtro Avanzado: extraer una lista de valores únicos.

En esta nota veremos otros usos de Filtro Avanzado. Empecemos por considerar esta lista de nombres con sus respectivas edades



Como pueden ver hemos dejado algunas líneas en blanco por encima de la lista. Esto es necesario para permitir introducir los criterios con los cuales queremos filtrar la lista.

Supongamos que queremos filtrar la lista de manera que vemos todas las personas mayores de 40 años o menores de 30 años. El primer paso consiste en copiar los encabezamientos de las columnas en la fila 1; luego escribimos los criterios en las celdas B2 y B3



Ahora abrimos el menú Datos—Filtro--Filtro Avanzado y señalamos la tabla de datos y el rango que contiene los criterios



Apretamos aceptar y Excel filtrará la lista



Para volver a mostrar todas las líneas usamos el menú Datos—Filtro—Mostrar todo



Al escribir los criterios en distintas líneas estamos indicando a Excel que queremos usar el operador O (OR). Si queremos usar el operador Y (AND) tenemos que poner los criterios en la misma línea. Por ejemplo, si queremos mostrar solo aquellos cuyo nombre empieza con la letra R y son mayores de 30 años






Nótese que usamos R* como criterio.

También podemos combinar operadores Y (AND) y O (OR)






También podemos usar fórmulas como criterio. Para esto deben cumplirse dos condiciones:
1 - que den como resultado un valor VERDADERO o FALSO.
2 – el encabezamiento del rango de criterios debe ser distinto del encabezamiento de la columna de datos

Por ejemplo, queremos mostrar sólo aquellos nombres cuya edad sea menor que la edad promedio de la lista. En el rango de criterios ponemos esta fórmula

=B6<PROMEDIO($B$6:$B$15)






Otro ejemplo sería mostrar aquellos nombres que aparecen más de una vez. Para eso usaremos la fórmula

=CONTAR.SI($A$6:$A$15,A6)>1






Otra gran ventaja de Filtro Avanzado es que nos permite copiar la lista filtrada a otra ubicación en la hoja. Volviendo sobre el ejemplo anterior, marcamos la posibilidad "Copiar a otro lugar", por ejemplo a D1






Como podemos ver, Filtro Avanzado permite filtrar listas de maneras que no serían posibles con Autofiltro.



Technorati Tags: MS Excel

Gráficos Dinámicos con Autofiltro

Una característica interesante de Autofiltro es que si creamos una gráfico a partir de una lista, al aplicar Autofiltro, el gráfico se irá adaptando a los datos visibles (filtrados).

El archivo con el ejemplo se puede descargar aqui


Por ejemplo, a partir de esta lista



creamos este gráfico (que no tiene mucho sentido, en esta etapa del ejemplo)



Si filtramos la lista para mostrar sólo las ventas de José, el gráfico se adaptará



En cuanto empezamos a trabajar con este gráfico descubrimos que hay un problema: el gráfico se "encoge" cuando navega sobre líneas ocultas. Por ejemplo, nuestro gráfico puede llegar a verse así



Podemos sobreponernos a este problema y, mejor aún, hacer que en el título de nuestro gráfico aparezca el nombre del agente cuyas ventas estamos mostrando. Todo esto puede hacerse sin macros y usando las técnicas que mostramos en la nota anterior sobre cómo ocultar gráficos en Excel.

Como en ese ejemplo, usaremos dos hojas. En la Hoja1 tendremos la tabla de datos y mostraremos el gráfico (de hecho, una imagen del gráfico).
En la hoja dos reside el "motor" del modelo: el gráfico y la lista de los agentes. En la celda contigua al agente ponemos una fórmula que totaliza las ventas de cada agente. Esto nos servirá para identificar el nombre del agente cuyo gráfico queremos exhibir en la Hoja1.

Empezamos por cortar y pegar el gráfico que acabamos de crear a otra hoja (en nuestro ejemplo a la Hoja2).

Seleccionamos el rango "sobre" el cual se encuentra el gráfico y lo definimos con un nombre. En nuestro caso definimos el nombre "grafico"

grafico: =Hoja2!$A$2:$G$17

En la Hoja2 elegimos una celda vacía, por ejemplo $I$1 y la llamamos sin_graf

sin_graf =Hoja2!$I$2

En el rango A20:A24 ponemos la lista de los agentes; en la celda B20 ponemos la fórmula =SUMAR.SI(agente,A20,ventas) y la copiamos al rango B20:B24.

Los nombres usados en la fórmula se refieren a rangos en la Hoja1

agente: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

ventas: =DESREF(Hoja1!$C$2,0,0,CONTARA(Hoja1!$C:$C)-1,1)

En la Hoja1 ponemos en la celda E1 la siguiente fórmula

=INDICE(Hoja2!A20:A24,COINCIDIR(Hoja1!F1,Hoja2!B20:B24,0))

En la celda F1 ponemos la fórmula

=SUBTOTALES(9,ventas)

Esta fórmula nos da el total de ventas del agente elegido con Autofiltro. La fórmula en la celda E1 nos da el nombre del agente, basado en el resultado de la celda F1.

Volvemos por un instante a la Hoja2 y ligamos el cuadro de texto del título del gráfico a la celda E1 en la Hoja1



Ahora tenemos que crear una imagen vinculada al gráfico en la Hoja3.
Elegimos una celda vacía, y la copiamos (Ctrl+C). Seleccionamos la celda E3, abrimos el menú Edición pulsando Mayúsculas (Shift) y elegimos Pegar--Vínculos Imagen. El resultado es una imagen vacía del tamaño de la celda.

Ahora creamos el nombre

mostrar_graf =SI(ESNOD(Hoja1!$E$1),sin_graf,grafico)

Esta fórmula nos permite mostrar u ocultar el gráfico basado en su resultado. Para qu esto sucede ligamos la imagen a la fórmula de la siguiente manera:

Seleccionamos la imagen que creamos con Pegar Vínculos—Imagen y en la barra de fórmulas reemplazamos la referencia por "=mostrar_graf"



Ahora podemos filtrarla lista con Autofiltro y veremos aparecer el gráfico. Por ejemplo, si elegimos Miguel



Este modelo tiene un serio problema potencial: si dos agentes tienen exactamente el mismo volumen de ventas, no se mostrará el gráfico.
Las soluciones posibles son:

1 – usar macros para determinar el nombre de la primera fila visible en la columna A a partir de la celda A2; ver el ejemplo desarrollado en el sitio Contextures;

2 – agregar una columna auxiliar que agregue a cada total de ventas del mes de cada agente un número identificatorio suficientemente pequeño como para no alterar los resultados. Por ejemplo, a Roberto le damos el número 1; a las ventas de Roberto le sumamos 1/1000000. Lo mismo con los restantes (Pedro 2/1000000, etc). Luego basamos las fórmulas y el gráfico en la columna auxiliar.

Technorati Tags: MS Excel

Usos de Autofiltro – Copiar a rangos no continuos

Hace unos días atrás, Rob van Gelder plantea el siguiente problema en Daily Dose of Excel:

tenemos una tabla con líneas en blanco que separan distintos grupos de datos. Queremos agregar una columna que contiene una fórmula, pero al copiar la fórmula a todo el rango se producen resultado "error". Esta imagen ejemplifica el problema:



La solución más inmediata es seleccionar las celdas con resultados erróneos y borrar el contenido.
Pero supongamos que se trata de una tabla con 1000 filas. Evidentemente la tarea llevaría muchísimo tiempo (1).
La misión es crear una selección lo continua de celdas en una columna dónde copiaríamos la fórmula.
Rob van Gelder propone una macro como solución. Nosotros mostraremo como hacer esto con Autofiltro.

Partimos de la tabla original, sin fórmulas en la columna E. Si aplicamos Autofiltro sin más trámite, Excel mostrará solamente los valores hasta la primer fila en blanco.
Para "obligar" a Excel a considerar toda la tabla, empezamos por seleccionar todo el rango de las columnas A hasta E y luego aplicamos Autofiltro



Ahora filtramos la tabla de acuerdo a la columna B, "no vacías"



Excel oculta las filas vacías



Seleccionamos el rango relevante en la columna E y aplicamos la fórmula



Todo lo que nos queda por hacer es anular el autofiltro



Obtenemos el mismo resultado, sin aplicar macros.

(1) En realidad podríamos seleccionar de una vez todas las celdas con resultado #DIV/0! usando Ir A --Especial, como ya hemos mostrado, y borrar el contenido en una sola operación. Si usamos alguna fórmula que no divida por una celda vacía no recibiremos resultado #DIV/0 y el método mostrado en esta nota sería el ideal.

Technorati Tags: MS Excel

Autofiltro avanzado en Excel

Supongo que la gran mayoría de mis lectores conocen la funcionalidad Autofiltro de Excel. Autofiltro nos permite "filtrar" una lista de acuerdo a uno o más criterios que podemos aplicar a una o más columnas de una lista.
Para ejemplificar el uso he importado una de las tablas de la base de datos Northwind que viene con el paquete Office de Microsoft.



Si queremos filtrar la lista para que se vean sólo los productos clasificados como bebidas (Beverage), seleccionamos una de las celdas de la tabla, y en menú Datos seleccionamos Autofiltro



Para filtrar la lista seleccionamos de la lista desplegable el criterio requerido



El resultado es



También podemos filtrar usando criterios "personalizados". Por ejemplo, todos los productos cuyo precio por unidad sea superior a 20. Abrimos la lista desplegable y elegimos Personalizar



En el diálogo que se abre elegimos "es mayor que" y fijamos el valor 20 en la ventanilla correspondiente



y pulsamos Aceptar



Podemos extender el poder de esta funcionalidad combinando filtro de varias columnas. Por ejemplo todas las bebidas cuyo precio sea mayor a 20




Para eliminar la selección podemos pulsar la flecha de la columna filtrada (que aparece en azul) o usar el menú Datos—Autofiltro.

La posibilidad de personalizar el filtro nos provee de una gran flexibilidad para filtrar nuestras listas.
Supongamos que queremos, por algún oscuro motivo, filtrar la lista para que muestre sólo las filas donde la cantidad por unidad (la columna QuantityPerUnit) empieza con "12". Usamos Personalizar con este criterio



El resultado



Como ven, Personalizar en Autofiltro ofrece muchas posibilidades. De



hasta



También podemos aplicar "wildcards" como * o ?. Por ejemplo, mostrar sólo las filas que tengan el número 12 en la columna QuantityPerUnit en la posición 6 y7. Usamos Personalizar con 5 signos ?, el numero 12 y un asterisco



También podemos usar



Obtenemos



Por último (en lo que respecta a esta nota) podemos combinar criterios dentro de una misma columna usando las posibilidades Y y O del diálogo. Es de notar que sólo podemos combinar dos condiciones.
Otra limitación de Autofiltro es que sólo puede mostrar 1000 registros en las listas desplegables de los encabezamientos.





Technorati Tags: MS Excel