martes, enero 16, 2007

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:

26 comentarios:

  1. Se pueden tener dos autofiltros en una misma hoja? estuve intentandolo pero no lo logre.

    Es para poder imprimir los resultados en una sola hoja de papel, si tuviera ambas listas en hojas distintas tendria que mandar dos impresiones.
    No se si me hago entender.

    ResponderEliminar
  2. Jorge:
    En relacion a la nota de los filtros, de la figura 5 adjunta en tu nota, muestras una lista de precios, como te comentaba anteriormente requiero extraer esa lista(de la figura) que se genera del filtro aplicado, para ser copiada a otro a un lugar diferente del archivo.
    Esta lista es un resumen incluso de valores repetidos que se encuentran en la columna filtrada.
    Espero tu respuesta gracias.

    ResponderEliminar
  3. Hola
    no, no podés aplicar dos autofiltros en la misma hoja. Tengo la impresión que podés solucionar tu problema usando tablas dinámicas.
    Mandame una el archivo (o una muestra) y veré que se puede hacer.

    ResponderEliminar
  4. Sobre cómo generar una lista de valores únicos de un rango puedes leer mi nota sobre el tema

    ResponderEliminar
  5. Hola Jorge, ¿existe alguna posibilidad de eliminar la restriccion de que solo se muestren 1.000 registros en la lista desplegable del autofiltro o viene así configurado por defecto?

    Gracias

    ResponderEliminar
  6. No se puede, viene configurado por defecto.
    Si necesitás obtener una lista de registros únicos, podés usar Filtro Avanzado--Sólo registros únicos.

    ResponderEliminar
  7. no se si este es el lugar para preguntarlo, pero tengo una lista en donde una columna son nombres. hay varios que se repiten. lo que quiero hacer es que en una celda me muestre cuantos nombres diferentes hay. ej si tengo 3 celdas con "juan", 2 con "pedro" y 5 con "martin" : que la celda marque "3", que es la cantidad de personas diferentes que hay en la columna...

    te dejo mi mail aeromartin@gmail.com

    aprovecho para agradecerte por este blog que me ha servido muchisimo en mi aprendizaje de exel.

    ResponderEliminar
  8. Hola,
    gracias por leer el blog.

    Para contar cuantas ocurrencias de un determinado valor existen en una lista puedes usar la función CONTAR.SI.
    Para crear fácilmente una lista desplegable con los nombres que aparecen en un rango, puedes usar Validación de Datos con la opción Lista.
    El problema es que en esta lista desplegable los nombres aparecerán duplicados la misma cantidad de veces que aparecen en la lista.
    Sobre como lograr una lista desplegable sin duplicados, publicaré en los próximos días una nota.

    ResponderEliminar
  9. jorge gracias por la contestacion, pero el tema es que necesito un numero para despues poder usar ese numero para promediar cosas...
    gracias nuevamente
    martin

    ResponderEliminar
  10. Como te comantaba, usas CONTAR.SI para obtener el número de ocurrencia de cada nombre. Luego usas la celda que contiene el resultado como argumento en tu fórmula para promediar.
    También puedes enviarme un archivo con un ejemplo, para que me haga una idea más precisa de lo que quieres hacer.

    ResponderEliminar
  11. a que mail te puedo mandar un archivo de ejemplo para que lo veas?

    dede ya muchas gracias
    martin

    ResponderEliminar
  12. Puedes mandarlo a jorgedun@gmail.com

    ResponderEliminar
  13. Hola Amigos. tngo una consulta y les agradezco por adelantado si me pueden ayudar. ¿se puede aplicar el autofiltro a varias hojas de una sola vez.? es que tengo unas 25 hojas y quisiera sacar datos de estas. el filtro lo quiero aplicar en la misma fila en todas las hojas.
    gracias x adelantado.

    ResponderEliminar
  14. No se puede. Pero puedes usar tablas dinámicas como alternativa (el enlace te llevará a la entrada).

    ResponderEliminar
  15. Uso el autofiltro,y me gusta: pero resulta que este autofiltro necesito que sea permanente en otra hoja (y que se actualice)....como hago para copiar la funcion que armo con "personalizar" a uns formula?

    Se entiende?

    ResponderEliminar
  16. Hola Carlos,
    no entiendo tu consulta. Ten en cuenta que con funciones no puedes cambiar el "estado" de una hoja (como ocultar filas, por ejemplo).
    Si lo que quieres hacer es copiar una lilsta filtrada a otra hoja, puedes sencillamente copiarla. Si quieres algo dinámico tendrías que usar macros.
    De todas maneras puedes mandarme un archivo con el ejemplo para que me haga una idea más precisa del problema.

    ResponderEliminar
  17. hola jorge, una consulta, tengo una lista con valores y necesito utilizar autofiltro para saber los dos valores de menor costo...agradezco su ayuda gracias!

    ResponderEliminar
  18. Lo más fácil es crear una columna axuxiliar con la función JERARQUIA y luego filtrar de acuerdo a ella. Por ejemplo, suponiendo que tenemos una serie de valores en el rango A1:A11, usamos esta fórmula en el rango B2:B11 (en la fila 1 tenemos los encabezamientos)

    =JERARQUIA(A2,$A$2:$A$11,1)

    El menor de los valores en A recibe el número 1, el segundo el 2 y así sucesivamente. Entonces podemos filtrar con el criterio "menor que 3".

    ResponderEliminar
  19. Pregunta:
    en el filtro que aplico no aparece la todos los registros que deberian estar en la lista desplegable, Por que se debe eso?
    Importa la cantidad de registros deferentes en la columna?

    ResponderEliminar
  20. Excel tiene un límite de 1000 registros en la lista desplegable de Autofiltro.

    ResponderEliminar
  21. Hola Jorge... lo del autofiltro todo Ok... pero mi proximo requerimiento es concatenar los valores de texto localizados en una determinada columna filtrada. Te agradeceria me informes a rafael.garau arroba gmail.com
    Saludos desde el Norte Argentino...

    ResponderEliminar
  22. ¿Te referís a concatenar los valores de las celdas visibles en la columna?

    ResponderEliminar
  23. hola
    como puedo filtrar por estatus, por concepto de continua, y tambien por grupo a, copiar el resultado del filtro y pegarlo en la hoja grupo a

    ResponderEliminar
  24. Apreciable Jorge: Tengo una hoja de calculo con datos y formulas en el rango A1: AB70, entre este rango hay dos rangos A15:G25 y A40:E50, en cuyas celdas tengo formulas que arrojan como resultado que algunas celdas tengan valores y otras no(en fiferentes combinaciones segun el caso), por lo que desearia que en mi hoja se ocultaran las filas correspondientes a las celdas que no tengan un valor como resultado. Por jemplo en un caso (1): A15:G25 y A40:E50 las celdas tendrán valores, no asi en otros casos como (2) que solo tendrá valores en A15:G15 y A40:E40, por lo que desearía que en el caso (2) se ocultaran automáticamente las filas 16 a 25 y 41 a 50. Aclaro que luego de la fila 50, antes de la fila 15 y entre las filas 15 y 40 hay otros datos. Ojalá me puedas apoyar como siempre. Te agradezco de antemano y te saludo. Atentamente, ALEXX. PD. No me respondiste referente al formato de factura con base de datos.

    ResponderEliminar
  25. Para que algo suceda automáticamente en la hoja al cambiar algún resultado hay que usar macros. Más precisamente, eventos. Es decir, hay que programar un código que oculte las hojas y que éste se dispare cuando sucede algo, como el recálculo de la hoja.
    En el blog hay varias notas sobre el tema "eventos" que tepueden orientar hacia la solución.

    ResponderEliminar