Autofiltro de campos en tablas dinámicas

Supongamos esta tabla dinámica (basada en la tabla de facturas de la base de datos Northwind que viene con Office)

Si queremos mostrar sólo los datos de un determinado intervalo (digamos entre el 15/08/2008 y el 15/09/2008) podemos desplegar la lista de fechas y elegir las fechas una por una



Pero como estamos usando Excel 2007, la tarea es mucho más fácil. Sencillamente usamos la opción Filtros de fecha-Entre…



Como podemos ver, las posibilidades de filtrar por criterios de fechas son múltiples

Lo mismo es válido si los valores del campo de filas son texto

En Excel 97-2003 no existen, aparentemente, estas posibilidades.

Digo aparentemente porque con un pequeño truco podemos replicar la funcionalidad de autofiltro en tablas dinámicas que existe en Excel 2007.

Seleccionamos la celda adyacente al borde derecho de la tabla dinámica en la fila de los encabezamientos (en nuestro ejemplo C4) y aplicamos Autofiltro con el menú Datos-Filtro-Autofiltro. El resultado es que el Autofiltro es aplicado a las columnas de la tabla dinámica

Ahora disponemos de la funcionalidad "Personalizar", con la cual podemos aplicar el filtro con criterios personalizados

Mostrar los criterios de Autofiltro en una celda

Una de las funcionalidades más populares de Excel es el Autofiltro. Con esta herramienta podemos filtrar una tabla de acuerdo criterios lógicos aplicados a los valores de una o más columnas de la tabla.
Un lector me consulta cómo se puede hacer para que el criterio aplicado aparezca en una celda por encima del encabezamiento de la columna. Por ejemplo, partiendo de esta lista

ver en las celdas correspondientes de fila 2 los criterios aplicados para filtrar la lista

Como puede verse, estamos usando una función definida por el usuario (UDF).

Esta función fue desarrollada por el guru de Excel Stephen Bullen.

Para poder usar esta función tenemos que poner este código en un módulo común del editor de Vba, preferentemente en el Personal.xlsb (Personal.xls en Excel 97-2003)

Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen
    Dim Filter As String
   
    Application.Volatile True
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " y " & .Criteria2
                Case xlOr
                    Filter = Filter & " o " & .Criteria2
            End Select
        End With
    End With
Finish:
    FilterCriteria = Filter
End Function

Esta función utiliza un único argumento, "Rng", que es cualquier celda de la columna sobre se aplica el Autofiltro.

Hay que tener en cuenta que esta función fue desarrollada antes de la aparición de Excel 2007. Por ejemplo, si elegimos más de dos criterios en una misma columna, por ejemplo ver las zonas Norte, Sur y Oeste, la función no podrá mostrar el criterio aplicado

Tip: en Excel 2007, al apuntar con el mouse al icono del autofiltro podemos ver una ventanilla que nos muestra los criterios aplicados (como en la imagen de arriba).

Filtro Avanzado – valores únicos en otra hoja

Ya hemos mencionado en el pasado la posibilidad de extraer valores únicos de una lista con Filtro Avanzado.

En Excel 2007 usamos Datos-Ordenar y Filtrar-Avanzadas

El problema con este método, tanto con Excel Clásico como con Excel 2007, es que aparentemente podemos extraer la lista de valores únicos sólo a la hoja que contiene la lista

Pero si leemos con atención el mensaje de Excel, vemos que dice "Sólo se puede copiar los datos filtrados a la hoja activa". Y remarco "hoja activa", ya que esta es la clave para resolver el "problema".

Cuando queremos copiar valores únicos de una lista a una hoja distinta de la hoja que contiene los datos, sencillamente activamos el Filtro Avanzado desde la hoja que recibirá la lista de valores únicos. De esta manera la hoja activa es la que recibirá los valores únicos.

Veamos el caso en nuestro ejemplo. La lista de nombres, con varios valores repetidos, está en la hoja "lista"; nuestro objetivo es copiar la lista de valores únicos en la hoja "únicos".

Elegimos la celda de la hoja "únicos" donde queremos poner los valores únicos (A1 en nuestro caso) y activamos Filtro Avanzado y ponemos todas las definiciones excepto "Rango de la lista"

Ahora señalamos el rango de la lista

Al apretar "Aceptar" los valores únicos serán copiados al rango deseado.
Antes de descubrir esta solución (y supongo que no soy el primero en descubrirla) me había embarcado a escribir una macro para hacer la tarea. Moraleja: dedicar siempre unos segundos para evaluar si entendemos lo que nos dicen.

Fórmulas con resultados múltiples

Hay situaciones en las que una fórmula puede dar más de un resultado. El caso clásico es con las funciones de búsqueda como BUSCARV o INDICE.


La alternativa más práctica en estos casos es usar Autofiltro (o Filtro Avanzado). Pero hay situaciones, por ejemplo cuando construimos un tablero de comandos (dashboard), donde queremos que los resultados aparezcan en un rango determinado de la hoja o en otra hoja.

Supongamos que tenemos esta tabla (la misma que usamos en la nota sobre el uso de BUSCARV en listas con valores repetidos):

En otra hoja queremos poner el nombre de un producto y que se desplieguen todas las órdenes de compra del producto.

Si usamos BUSCARV (o INDICE con COINCIDIR) para obtener todas las órdenes de compra de tornillos, sólo podemos obtener un resultado

Sólo podemos obtener un resultado por celda. No hay ninguna forma de escribir resultados de una fórmula en otra celda que no contenga la fórmula, al igual que no podemos cambiar la estructura de la hoja con fórmulas.
Si queremos poner en una hoja de Excel todos los resultados posibles de una fórmula de búsqueda tendremos que usar otras técnicas.

En esta nota mostraré dos técnicas para obtener todos los resultados. Una estrategia posible es usar múltiples fórmulas.


Establecemos por adelantado cuál pueda ser el número máximo de resultados a obtener y en un rango de celdas contiguas introducimos esta fórmula matricial:

=INDICE(od_compra;K.ESIMO.MENOR(SI(DESREF(od_compra;0;0;FILAS(od_compra);1)=$B$2;FILA(DESREF(od_compra;0;0;FILAS(od_compra);1))-FILA(DESREF(od_compra;0;0;1;1))+1;FILA(DESREF(od_compra;FILAS(od_compra)-1;0;1;1))+1);FILA()-3);2)

donde el nombre "od_compras" define el rango A2:B10 de nuestro ejemplo

Por ejemplo, en una nueva hoja ("formulas") introducimos la fórmula en la celda A4 y la copiamos en todo el rango hasta la celda A9 (estamos suponiendo que el número máximo de órdenes posibles es seis)

Dado que sólo hay tres órdenes de compras para tornillos, el resultado en las celdas A18:A20 es #¡REF!

Para ocultar estos resultados podemos usar formato condicional

Esta técnica tiene varias desventajas: el uso de fórmulas matriciales, el uso de DESREF que es volátil, y además es complicada.

Una técnica alternativa es usar la cámara junto con Autofiltro.

Activamos el Autofiltro en la tabla de datos (en la hoja "datos"). Luego seleccionamos todo el rango de la tabla sin incluir los encabezamientos y activamos la cámara; creamos una nueva hoja ("cámara") y pegamos la imagen en el lugar deseado

En la hoja Datos filtramos la tabla de acuerdo al producto deseado

La imagen en la hoja "cámara" reflejará el cambio instantáneamente

El inconveniente con este modelo es que debemos ir a la hoja Datos para hacer el filtrado. Si queremos manejar los resultados desde la hoja "cámara", tendremos que utilizar una macro para manejar el filtrado por "control remoto".

Empezamos por agregar una lista desplegables, con validación de datos, en la celda B2 de la hoja "cámara" para poder controlar el filtrado

Luego ponemos este código en un módulo común del editor de Vb

Sub filtrado_ordenes()
    Dim strCrit As String
    Dim rngTablaDatos As Range
  
    strCrit = Sheets("camara").Range("B2")
  
    Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
  
    If strCrit <> "Todos" Then
        rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit
    Else
        rngTablaDatos.AutoFilter
    End If
  
End Sub

Para activar el código cuando elegimos el producto programamos este evento en el módulo de la hoja "cámara"

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Address = "$B$2" Then Call filtrado_ordenes

End Sub

Con esto hemos completado nuestro modelo. Podemos ocultar la hoja "datos" de manera que nuestro usuario sólo vea la hoja "cámara" desde la cual puede manejar las que órdenes serán exhibidas en la hoja.

El archivo con el ejemplo se puede descargar gratuitamente aquí.

Filtro Avanzado en hojas protegidas – una rareza de Excel

Aplicamos Proteger a una hoja de Excel para impedir que el usuario haga cambios o modificaciones en la hoja. Excel nos permite definir excepciones. Por ejemplo, permitir que el usuario pueda usar Autofiltro (a condición que el autofiltro haya sido aplicado antes de proteger la hoja) u Ordenar

Si aplicamos Proteger Hoja sin excepciones, la opción Filtro y Mostrar Todo del menú Datos aparecen deshabilitadas



Si nos fijamos bien veremos que la opción Filtro Avanzado sigue activa. Los programadores de Excel se han olvidado de bloquear esta opción.

Así podemos aplicar Filtro avanzado para filtrar todas las filas donde el valor de la celda en la columna A sea mayor de 2500 a pesar de que la hoja está protegida



Uno de los problemas que surgen es que si bien la opción Filtro Avanzado sigue activa, la opción Mostrar Todo está bloqueada. Es decir, podemos filtrar, pero no podemos quitar el filtro.

Para mostrar las filas ocultas sin tener que cancelar la protección basta con abril el diálogo del Filtro Avanzado y borrar el rango de los criterios.

Microsoft conoce este problema desde la versión 2000, y en la última nota publicado en la base de conocimientos de Excel , Advanced Filter May Function on Protected Worksheet, mencionan que por el momento (noviembre del 2003!) no hay forma de impedir la aplicación de Filtro Avanzado en una hoja protegida.

Una solución posible es proteger todas las celdas y no permitir seleccionar todas las celdas donde pudieran definirse criterios para filtro avanzado. De esta manera, si bien el diálogo del filtro avanzado se abrirá, no habrá posibilidad de definir un rango de criterios, lo que hace que todas las filas queden visibles.

Technorati Tags: MS Excel

Como crear un icono para quitar Autofiltro

En la nota sobre los comandos personalizados para Autofiltro, mostré como agregar ciertos iconos que nos permitan ahorrar tiempo a quienes usamos Autofiltro con frecuencia.

Uno de estos iconos nos permite agregar el autofiltro con un solo clic

Para poner autofiltro a una tabla apretamos este icono. Sería muy conveniente que al apretar nuevamente este icono, quitemos el autofiltro. Es decir, que funcione como un interruptor. Eso es lo que sucede en Excel 2007, pero no en las versiones anteriores. Si queremos quitar el autofiltro tenemos que ir al menú Datos y hacer clic a Filtro.

En esta nota mostraré como crear una macro para cancelar el autofiltro y cómo ligarla a un icono, que pondremos en la misma barra de herramientas del icono de autofiltro.
Empecemos por la macro, que es muy sencilla. En un módulo, preferentemente del cuaderno Personal.xls, ponemos este código

Sub filter_off()
ActiveSheet.AutoFilterMode = False
End Sub

Ahora lo ligaremos a un icono, que luego pondremos en la barra de herramientas correspodiente. Empezamos por abrir el menú Herramientas-Personalizar y en el formulario ir a la pestaña Comandos y elegir la opción Macros

Arrastramos el botón con el “smiley” y lo ubicamos al lado del icono de Autofiltro (el embudo con el símbolo “=” a la izquierda)



Señalamos el icono con el mouse y pulsamos el botón derecho para abrir el menú



Le asignamos la macro, en la ventanilla Nombre ponemos “Cerrar Autofiltro” y seleccionamos la opción “Sólo texto (siempre)”



El resultado es un icono de texto. Al apretar este icono, si en la hoja hay una tabla con Autofiltro, éste será quitado.



Pero sería muy bueno si en lugar del icono de texto apareciera uno similar al de Autofiltro, por ejemplo, un embudo con un signo “=” pero cruzado por una X, es decir, esto



Excel nos provee con las herramientas necesarias para crear el icono. Empezamos por abrir el menú Herramientas-Personalizar y seleccionar (un solo clic) el icono del embudo. Abrimos el menú y apretamos Copiar imagen del botón



Ahora seleccionamos el icono de texto “Cerrar Autofiltro” y en el menú del botón apretamos Pegar imagen



A esta altura del partido tenemos la imagen del embudo junto al texto. Para deshacernos del texto usamos la opción “Estilo predeterminado” y luego elegimos la opción “Modificar imagen del botón”



Esto abre la esta ventanilla, donde podemos modificar la imagen.

En nuestro caso seleccionamos el color rojo (o cualquier otro que encuentren conveniente) en el cuadro de colores (negro es la opción por defecto) y marcamos una X señalando con el mouse los cuadrados correspondientes a la diagonal del cuadro de la imagen



Una vez concluida la tarea apretamos Aceptar. Ahora tenemos dos iconos, uno para poner el Autofiltro y otro para quitarlo



Esta nota tiene un cierto tinte de nostalgia anticipada, ya que las barras de herramientas y los botones han desaparecido de Excel 2007. Supongo que en no mucho tiempo, esta nota será caduca, pero mientras tanto podemos seguir disfrutando de la funcionalidad de las barras de herramientas y la posibilidad de crear botones personalizados.

Technorati Tags: MS Excel

Filtrado de fechas en Excel con Autofiltro o Filtro Avanzado

Supongamos que queremos filtrar una lista de fechas de manera de dejar visibles sólo las fechas que caen en miércoles. Podemos hacer esto con Autofiltro o con Filtro Avanzado, pero cuál sería el criterio?
Si usamos Autofiltro necesitamos crear una columna auxiliar que nos dé el día de la semana para cada una de las fechas del rango. Luego filtramos de acuerdo a esta columna auxiliar.



¿Cómo calculamos el día de la semana para cada fecha? Dos posibilidades, una sencilla y otra complicada:

1 - La complicada, con funciones. Con la función DIASEM, por ejemplo

=DIASEM(A2,2) da 3.

Para transformar el resultado 3 en "miércoles" usamos la función ELEGIR de esta manera

=ELEGIR(DIASEM(A2,2),"lunes","martes","miércoles","jueves","viernes","sábado","domingo")

O con la función INDICE:
=INDICE(semana,DIASEM(A2,2)),

donde "semana" es un nombre que contiene un rango con los días de la semana o directamente los días de la semana.

2 - La posibilidad sencilla. Creamos en la columna auxiliar una referencia a la celda con la fecha (en B2 ponemos =A2) y cambiando el formato de B con el formato personalizado "dddd".

Si queremos usar Filtro Avanzado, tenemos que usar una fórmula lógica, es decir, que dé como resultado VERDADERO o FALSO. Agregamos algunas filas en blanco para poner las filas de criterios y en la celda A2 ponemos esta fórmula

=DIASEM(A5,2)=3



Luego usamos el menú de Filtro avanzado






La ventaja de usar Filtro Avanzado es que nos permite copiar los resultados a otro rango de la hoja o usar más de dos criterios para filtrar la lista.

Ahora veamos otros casos que se presentan.

Para dejar visibles sólo fechas que sean el primer día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)

Si la fecha en A2 coincide con el primer día del mes el resultado será VERDADERO. En caso contrario, FALSO. Luego filtramos la lista usando como criterio VERDADERO en la columna auxiliar.



Si queremos usar Filtro Avanzado, usamos la misma fórmula como criterio






Para filtrar las fechas que sean el último día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)

Para filtrar el primer día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)+ELEGIR(DIASEM(FECHA(AÑO(A2),MES(A2),1),2),0,0,0,0,0,2,21

La primer parte de esta fórmula calcula la fecha del primer día del mes. En la segunda parte de la fórmula DIASEM calcula el número de día de la semana de esta fecha. Este resultado es usado como parámetro en la función ELEGIR, que agrega 0 (cero) si el día de semana cae entre lunes y viernes, suma 2 si el día de semana del primer día del mes cae un sábado o 1 si es domingo.

Para filtrar el último día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)-(MAX(0,DIASEM(FECHA(AÑO(A2),MES(A2)+1,0),2)-5))

Como en los casos anteriores, si queremos usar Autofiltro usamos la fórmula para construir una columna auxiliar. Si queremos usar Filtro Avanzado usamos la fórmula en la celda de criterio.

Technorati Tags: MS Excel

Autofiltro - comandos personalizados

Si utilizan con cierta frecuencia Autofiltro para analizar datos de una tabla en Excel, seguramente sabrán apreciar esta sugerencia.
Para activar Autofiltro lo que hacemos normalmente es abrir el menú Datos-Filtro-Autofiltro



Si quisiéramos filtrar la lista para ver sólo los clientes de la Argentina, pulsamos la fecha del campo (Country, en nuestro ejemplo) y marcamos "Argentina"



Contemos la cantidad de operaciones que hemos efectuado:
1 - Clic al menú Datos
2 - Clic al menú Filtro
3 - Clic al menú Autofiltro
4 - Clic a la flecha del filtro en el campo Country
5 - Clic a la opción "Argentina".

En total cinco operaciones. Todo esto se puede hacer en un solo clic, si instalamos previamente el comando Autofiltro en alguna de la barras de herramientas.

Para instalar el comando, abrimos el menú Herramientas-Personalizar (o clic con el botón derecho del Mouse apuntando al área de las barras de herramientas)



En la pestaña Categorías del diálogo Personalizar, elegimos la categoría Datos; en la ventanilla Comandos seleccionamos Autofiltro



y lo arrastramos a alguna de las barras de herramientas



Una vez instalado, el comando quedará a nuestra disposición cada vez que usemos Excel.

Volviendo a nuestro ejemplo, en el cual hemos quitado el Autofiltro, seleccionamos la celda A13 que contiene el valor "Argentina" (el criterio con el cual queremos filtrar la lista).



Todo lo que tenemos que hacer es pulsar una vez el icono de Autofiltro. Excel activa
el autofiltro y realiza el filtrado de acuerdo al valor de la celda activa



Lo que antes hicimos con cinco operaciones. Hemos hecho ahora con un único clic.

Como podrán notar, he agregado otro comando muy útil, "Mostrar todo". Un clic a este comando elimina todos los filtros de la lista.

Technorati Tags: MS Excel

Algo más sobre nombres en Excel

Supongamos una hoja de un cuaderno Excel con la siguiente tabla



Si abrimos el diálogo de nombres (Insertar-Nombres-Definir), veremos que no hay ningún nombre definido



Empezamos por definir un nombre que contiene el rango B2:B13 (que llamamos "ventas")



Si abrimos ahora el diálogo Insertar-Nombres-Definir veremos el nombre que acabamos de crear



Digamos que queremos ver en qué meses las ventas fueron inferiores a 70.000, para lo cual aplicamos Autofiltro



Ahora queremos imprimir nuestro reporte, para lo cual definimos algunos parámetros con el menú Configurar Página-Hoja



Ahora volvemos a abrir el menú Insertar-Nombres-Definir



Vemos ahora que Excel a creado dos nombres que corresponden a las definiciones de impresión que hemos establecido: Área_de_impresión y Títulos_a_imprimir.

Estos nombres han sido creados como nombres "locales", es decir, son válidos sólo para la Hoja1 (en nuestro caso). Si abrimos el diálogo Insertar-Nombes-Definir en la Hoja2, sólo veremos "ventas".

¿Son estos todos los nombres en nuestro cuadernos? Excel no tiene un método nativo para exhibir todos los nombres definidos en el cuaderno. Para ver todos los nombres podemos usar una macro como la siguiente (sugerida por John Walkenbach)

Sub ListAllNames()
Row = 1
For Each n In ActiveWorkbook.names
Cells(Row, 4) = n.Name
Cells(Row, 5) = " " & n.RefersTo
Row = Row + 1
Next n
End Sub

o usar el excelente complemento Name Manager, creado por Jan Karle Pieterse,.



Como vemos, Excel ha creado el nombre Hoja1!_FilterDatabase que contiene el rango de la tabla a la que hemos aplicado Autofiltro. Este es un nombre "oculto", es decir, no parece en la lista de nombres (Insertar-Nombres-Pegar). Excel crea nombres ocultos en todo tipo de tareas, por ejemplo impresión como hemos visto, cuando usamos el Solver, etc.

Volviendo a Hoja1!_FilterDatabase, si agregamos datos a la tabla y volvemos a aplicar Autofiltro, veremos que Excel cambia el rango del nombre para adecuarlo a la nueva situación.

Un uso interesante, pero que hay que ejercer con cautela, consiste en reemplazar manualmente el rango de estos nombres. Esto es relevante, por supuesto, a los nombres que aparecen en el diálogo de Nombres-Definir.

Por ejemplo, el lector Carlos Clemente me envía una hoja para calcular tablas de amortización (que les recomiendo descargar y analizar). En esta hoja, Carlos reemplaza el rango del nombre Área_de_impresión por una fórmula, lo que le permite definir el área de impresión en forma dinámica de acuerdo a la cantidad de pagos del préstamo. Esto es necesario, ya que si reducimos el número de pagos y por ende el número de filas a imprimir, Excel seguirá imprimiendo el área mayor que haya sido impresa con anterioridad.
Otro característica interesante en el modelo del amigo Carlos es el uso de Formato Condicional para ocultar las filas sin datos.

Como vemos, el uso de nombres no sólo nos permite simplificar la interpretación de las fórmulas y crear rangos dinámicos, sino también afectar el comportamiento de Excel, como en el caso del área de impresión (o de Títulos a Imprimir).




Technorati Tags: MS Excel