Recientemente escribí un artículo sobre cómo usar funciones de resumen en Excel para resumir fácilmente grandes cantidades de datos, pero este artículo consideró todos los datos en la hoja de trabajo. ¿Qué sucede si solo desea ver un subconjunto de los datos y resumir el subconjunto de los datos?
En Excel, puede crear filtros en columnas que ocultarán filas que no coinciden con su filtro. Además, también puede usar funciones especiales en Excel para resumir los datos usando solo los datos filtrados.
En este artículo, lo guiaré a través de los pasos para crear filtros en Excel y también usar funciones integradas para resumir estos datos filtrados.
Crea filtros simples en Excel
En Excel, puede crear filtros simples y filtros complejos. Comencemos con filtros simples. Cuando trabaje con filtros, siempre debe tener una fila en la parte superior que se usa para las etiquetas. No es obligatorio tener esta línea, pero facilita un poco el trabajo con filtros.
Arriba tengo algunos datos falsos y quiero crear un filtro en el Ciudad columna. En Excel, esto es realmente fácil de hacer. Continúe y haga clic en el Datos en la cinta, luego haga clic en el Filtrado botón. Tampoco es necesario que seleccione los datos en la hoja o haga clic en la primera fila.
Al hacer clic en Filtrar, cada columna de la primera fila tendrá automáticamente un pequeño botón desplegable en el extremo derecho.
Ahora siga adelante y haga clic en la flecha desplegable en la columna Ciudad. Verá algunas opciones diferentes, que explicaré a continuación.
En la parte superior, puede ordenar rápidamente todas las filas según los valores de la columna Ciudad. Tenga en cuenta que al ordenar los datos, se moverá toda la fila, no solo los valores de la columna Ciudad. Esto asegurará que sus datos permanezcan intactos como antes.
Además, es una buena idea agregar una columna al principio llamada ID y numerarla de una a tantas filas como tenga en su hoja de trabajo. De esta manera, aún puede ordenar por la columna de ID y recuperar sus datos en el mismo orden en que estaban originalmente, si eso es importante para usted.
Como puede ver, todos los datos de la hoja de trabajo ahora están ordenados según los valores de la columna Ciudad. Hasta ahora, no se oculta ninguna fila. Ahora echemos un vistazo a las casillas de verificación en la parte inferior del cuadro de diálogo del filtro. En mi ejemplo, solo tengo tres valores únicos en la columna Ciudad y esos tres aparecen en la lista.
Seguí adelante y desmarqué dos ciudades y dejé una marcada. Ahora solo tengo 8 filas de datos mostradas y el resto está oculto. Puede saber fácilmente que está viendo datos filtrados si marca los números de fila en el extremo izquierdo. Dependiendo del número de líneas ocultas, verá algunas líneas horizontales más y el color de los números será azul.
Ahora digamos que quiero filtrar en una segunda columna para reducir aún más el número de resultados. En la columna C tengo el número total de miembros en cada familia y quiero ver solo los resultados para familias con más de dos miembros.
Continúe y haga clic en la flecha desplegable en la columna C y verá las mismas casillas de verificación para cada valor único en la columna. Sin embargo, en este caso queremos hacer clic Filtros digitales luego haga clic en Mayor que. Como puede ver, también hay muchas otras opciones.
Aparecerá un nuevo cuadro de diálogo y puede ingresar el valor del filtro aquí. También puede agregar varios criterios con una función AND u OR. Puede decir que quiere filas con un valor mayor que 2 y no igual a 5, por ejemplo.
Ahora solo tengo 5 líneas de datos: familias solo de Nueva Orleans y con 3 o más miembros. ¿Suficientemente fácil? Tenga en cuenta que puede borrar fácilmente un filtro en una columna haciendo clic en la lista desplegable y luego haciendo clic en el botón Borrar el filtro de «Nombre de columna» Enlace.
Así que eso es todo para filtros simples en Excel. Son muy fáciles de usar y los resultados son bastante sencillos. Ahora echemos un vistazo a los filtros complejos usando el Avanzado cuadro de diálogo de filtros.
Crea filtros avanzados en Excel
Si desea crear filtros más avanzados, debe utilizar la Avanzado cuadro de diálogo de filtro. Por ejemplo, digamos que quería ver a todas las familias que viven en Nueva Orleans con más de 2 miembros de la familia. O todas las familias en Clarksville con más de 3 miembros de la familia Y solo aquellos con un .EDU dirección de correo electrónico final. Ahora no puede hacer esto con un filtro simple.
Para hacer esto, necesitamos configurar la hoja de Excel de manera un poco diferente. Continúe e inserte algunas filas sobre su conjunto de datos y copie las etiquetas del encabezado exactamente en la primera fila como se muestra a continuación.
Así es como funcionan los filtros avanzados. Primero debe escribir sus criterios en las columnas superiores, luego hacer clic en el botón Avanzado botón debajo Ordenar y filtrar sobre Datos lengua.
Entonces, ¿qué podemos capturar exactamente en estas células? Bien, comencemos con nuestro ejemplo. Solo queremos ver los datos de Nueva Orleans o Clarksville, así que introdúzcalos en las celdas E2 y E3.
Cuando escribe valores en diferentes líneas, significa OR. Ahora queremos familias de Nueva Orleans de más de dos miembros y familias de Clarksville de más de 3 miembros. Para hacer esto, escriba > 2 en C2 y > 3 en C3.
Dado que> 2 y Nueva Orleans están en la misma línea, será un operador AND. Lo mismo ocurre con la fila 3 anterior. Finalmente, solo queremos familias con una dirección de correo electrónico final .EDU. Para hacer esto, simplemente escriba * .edu tanto en D2 como en D3. El símbolo * significa cualquier número de caracteres.
Una vez que haya hecho eso, haga clic en cualquier parte de su conjunto de datos, luego haga clic en el Avanzado botón. la Lista de clasificaciónEl campo incluirá automáticamente su conjunto de datos, ya que hizo clic en él antes de hacer clic en el botón Avanzado. Ahora haga clic en el pequeño botón a la derecha de la Rango de criterios botón.
Seleccione todo, desde A1 hasta E3, luego haga clic en el mismo botón nuevamente para regresar al cuadro de diálogo Filtro avanzado. Haga clic en Aceptar y sus datos ahora deberían estar filtrados.
Como puede ver, solo tengo 3 resultados que coinciden con todos estos criterios. Tenga en cuenta que las etiquetas del rango de criterios deben coincidir exactamente con las etiquetas del conjunto de datos para que esto funcione.
Obviamente, puede crear consultas mucho más complicadas con este método, así que juegue con él para obtener los resultados que desea. Finalmente, hablemos de la aplicación de funciones de suma a los datos filtrados.
Resumen de datos filtrados
Ahora digamos que quiero resumir la cantidad de miembros de la familia en mis datos filtrados, ¿cómo podría hacer eso? Bueno, borremos nuestro filtro haciendo clic en el Despejar botón en la cinta. No se preocupe, es muy fácil volver a aplicar el filtro avanzado con solo hacer clic en el botón Avanzado y hacer clic en Aceptar nuevamente.
En la parte inferior de nuestro conjunto de datos, agreguemos una celda llamada Total luego agregue una función de suma para sumar el total de miembros de la familia. En mi ejemplo, acabo de escribir = SUMA (C7: C31).
Entonces, si miro a todas las familias, tengo 78 miembros en total. Ahora sigamos y volvamos a aplicar nuestro filtro avanzado y veamos qué sucede.
¡UPS! En lugar de mostrar el número correcto, 11, ¡todavía veo que el total es 78! ¿Por qué entonces? Bueno, la función SUM no ignora las filas ocultas, por lo que siempre hace el cálculo usando todas las filas. Afortunadamente, hay algunas funciones que puede utilizar para ignorar las líneas ocultas.
El primero es TOTAL. Antes de usar cualquiera de estas funciones especiales, querrá borrar su filtro y luego ingresar a la función.
Una vez que el filtro esté limpio, continúe e ingrese = SUBTOTAL ( y debería ver aparecer un cuadro desplegable con un montón de opciones. Al usar esta función, primero elige el tipo de función de suma que desea usar usando un número.
En nuestro ejemplo, quiero usar SUMA, así que escribiría el número 9 o simplemente haría clic en él en la lista desplegable. Luego escriba una coma y seleccione el rango de celdas.
Cuando presiona Enter, debería ver que el valor de 78 es el mismo que antes. Sin embargo, si vuelve a aplicar el filtro, ¡veremos 11!
¡Excelente! Esto es exactamente lo que queremos. Ahora puede ajustar sus filtros y el valor siempre reflejará solo las filas que se muestran actualmente.
La segunda función que funciona de manera muy similar a la función SUBTOTAL es AGREGAR. La única diferencia es que hay otro parámetro en la función AGREGAR donde debe especificar que desea ignorar las filas ocultas.
El primer parámetro es la función de suma que desea utilizar y, como con SUBTOTAL, 9 representa la función SUMA. La segunda opción es donde debe escribir 5 para ignorar las líneas ocultas. El último parámetro es el mismo y corresponde al rango de celdas.
También puede leer mi artículo sobre funciones RESUMEN para aprender a usar la función AGREGAR con más detalle y otras funciones como MODO, MEDIANO, PROMEDIO, etc.
Espero que este artículo le brinde un buen punto de partida para crear y usar filtros en Excel. Si tiene alguna pregunta, no dude en publicar un comentario. ¡Disfrutar!