Recientemente escribí un artículo sobre cómo usar funciones de resumen en Excel para resumir fácilmente grandes cantidades de datos, pero ese artículo tuvo en cuenta todos los datos en la hoja de trabajo. ¿Qué sucede si solo desea ver un subconjunto de datos y resumir el subconjunto de datos?
En Excel, puede crear filtros en columnas que oculten filas que no coincidan con su filtro. Además, también puede usar funciones especiales en Excel para resumir datos usando solo los datos filtrados.
En este artículo, le guiaré por los pasos para crear filtros en Excel y también usando el built-in. funciones para resumir los datos filtrados.
Crear filtros simples en Excel
En Excel, puede crear filtros simples y complejos. Comencemos con filtros simples. Al trabajar con filtros, siempre debe tener una fila en la parte superior que se utiliza para las etiquetas. No es un requisito tener esta fila, pero hace que trabajar con filtros sea un poco más fácil.
Arriba, tengo algunos datos falsos y desea crear un filtro en la columna Ciudad. En Excel, esto es realmente fácil de hacer. Continúe y haga clic en la pestaña Datosen la cinta y luego haga clic en el botón Filtro. No tiene que seleccionar los datos en la hoja o hacer clic en la primera fila.
Al hacer clic en Filtro, cada columna en la primera fila se agregará automáticamente un pequeño botón desplegable a la derecha.
Ahora adelante y haz clic en la flecha desplegable en Columna de la ciudad Verá un par de opciones diferentes, que explicaré a continuación.
En la parte superior, puede ordenar rápidamente todas las filas por los valores en la columna Ciudad. Tenga en cuenta que cuando clasifique los datos, moverá toda la fila, no solo los valores en la columna Ciudad. Esto asegurará que sus datos permanezcan intactos tal como estaban antes.
Además, es una buena idea agregar una columna en el anverso llamada Id. Y numerarla de una a cuantas filas tenga en su hoja de cálculo. De esta forma, siempre puede ordenar por la columna ID y recuperar sus datos en el mismo orden en que se encontraba originalmente, si eso es importante para usted.
Como puede ver, todos los datos en la hoja de cálculo ahora están ordenados según los valores en la columna Ciudad. Hasta el momento, no hay filas ocultas. Ahora echemos un vistazo a las casillas de verificación en la parte inferior del diálogo de filtro. En mi ejemplo, tengo solo tres valores únicos en la columna Ciudad y esos tres aparecen en la lista.
Seguí adelante sin marcar dos ciudades y dejó una marcada. Ahora solo tengo 8 filas de datos que se muestran y el resto están ocultos. Puede ver fácilmente que está mirando los datos filtrados si comprueba los números de fila en el extremo izquierdo. Dependiendo de cuántas filas estén ocultas, verá algunas líneas horizontales adicionales 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 solo quiero ver los resultados para las 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 en Filtros de númeroy luego hacer clic en Mayor que. Como puede ver, hay un montón de otras opciones también.
Aparecerá un nuevo cuadro de diálogo y aquí puede escribir el valor para el filtro También puede agregar más de un criterio con una función AND u OR. Podría decir que quiere filas donde el valor sea mayor que 2 y no igual a 5, por ejemplo.
Ahora estoy de acuerdo solo 5 filas 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 el menú desplegable y luego haciendo clic en el enlace Borrar filtro de "Nombre de columna".
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 cuadro de diálogo de filtros Avanzado.
Cree filtros avanzados en Excel
Si desea crear filtros más avanzados, tiene que usar el cuadro de diálogo de filtro Avanzado. Por ejemplo, supongamos que quería ver a todas las familias que viven en Nueva Orleans con más de 2 miembros en su familia ORtodas las familias en Clarksville con más de 3 miembros en su familia Ysolo los que tienen una dirección de correo electrónico de finalización .EDU. Ahora no puedes hacer eso con un filtro simple.
Para hacer esto, necesitamos configurar la hoja de Excel de forma un poco diferente. Continúe e inserte un par de filas sobre su conjunto de datos y copie las etiquetas del encabezado exactamente en la primera fila, como se muestra a continuación.
ahora así es como funcionan los filtros avanzados. Primero debe ingresar sus criterios en las columnas de la parte superior y luego hacer clic en el botón Avanzadoen Ordenar y amp; Filtraen la pestaña Datos.
Entonces, ¿qué podemos escribir exactamente en esas celdas? OK, entonces comencemos con nuestro ejemplo. Solo queremos ver datos de Nueva Orleans o Clarksville, así que vamos a escribirlos en las celdas E2 y E3.
Cuando escribe valores en diferentes filas, significa OR. Ahora queremos tener familias de Nueva Orleans con más de dos miembros y familias de Clarksville con más de 3 miembros. Para ello, escriba & gt; 2en C2 y & gt; 3en C3.
Como & gt; 2 y New Orleans están en la misma fila, será un operador AND. Lo mismo es cierto para la fila 3 de arriba. Finalmente, solo queremos las familias con la dirección de correo electrónico final .EDU. Para hacer esto, simplemente escriba *. Eduen D2 y D3. El símbolo * significa cualquier número de caracteres.
Una vez que lo haga, haga clic en cualquier parte de su conjunto de datos y luego haga clic en Avanzadobotón. El campo List Range calculará automáticamente su conjunto de datos desde que hizo clic en él antes de hacer clic en el botón Avanzado. Ahora haga clic en el pequeño botón pequeño a la derecha del botón Rango de criterios.
Seleccione todo de A1 a E3 y luego haga clic nuevamente en el mismo botón para volver al cuadro de diálogo Filtro avanzado. ¡Haga clic en Aceptar y sus datos ahora deberían ser filtrados!
Como puede ver, ahora solo tengo 3 resultados que coinciden con todos esos criterios. Tenga en cuenta que las etiquetas para el 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 deseados Finalmente, hablemos sobre la aplicación de funciones de suma a los datos filtrados.
Resumiendo datos filtrados
Ahora digamos que quiero resumir el número de miembros de la familia en mis datos filtrados, ¿cómo lo haría? ¿Tienes ganas de hacer eso? Bien, limpiemos nuestro filtro haciendo clic en el botón Borraren la cinta. No se preocupe, es muy fácil volver a aplicar el filtro avanzado simplemente haciendo clic en el botón Avanzado y haciendo clic en Aceptar nuevamente.
En el En la parte inferior de nuestro conjunto de datos, agreguemos una celda llamada Totaly luego agreguemos una función suma para resumir el total de miembros de la familia. En mi ejemplo, acabo de escribir = SUMA (C7: C31).
Así que si miro todo familias, tengo 78 miembros en total. Ahora continuemos y volvamos a aplicar nuestro filtro avanzado y veamos qué sucede.
¡Vaya! En lugar de mostrar el número correcto, 11, ¡todavía veo que el total es 78! ¿Porqué es eso? Bueno, la función SUMA no ignora las filas ocultas, por lo que sigue haciendo el cálculo usando todas las filas. Afortunadamente, hay un par de funciones que puede usar para ignorar las filas ocultas.
La primera es SUBTOTAL. Antes de utilizar cualquiera de estas funciones especiales, deseará borrar su filtro y luego escribir la función.
Una vez que el filtro se borre, continúe y escriba = SUBTOTAL (y debería ver aparecer un cuadro desplegable con varias opciones. Al usar esta función, primero selecciona el tipo de función de suma que desea usar usando un número.
En nuestro ejemplo, quiero usar SUMAR, así que escribiría el número 9 o simplemente haría clic en él del menú desplegable. Luego, escriba una coma y seleccione el rango de celdas.
Cuando presione enter, debería ver que el valor de 78 es el mismo que el anterior. Sin embargo, si ahora aplica el filtro nuevamente, ¡veremos 11!
¡Excelente! Eso es exactamente lo que queremos. Ahora puede ajustar sus filtros y el valor siempre reflejará solo las filas que se muestran actualmente.
segunda función que funciona más o menos exactamente igual que la función SUBTOTAL es AGREGAR. La única diferencia es que hay otro parámetro en la función AGREGAR en el que debe especificar que desea ignorar las filas ocultas.
El primer parámetro es la función de suma que desea usar y, como con SUBTOTAL, 9 representa la función SUMA. La segunda opción es donde debe escribir 5 para ignorar las filas ocultas. El último parámetro es el mismo y es el rango de celdas.
También puede leer mi artículo sobre funciones de resumen para aprender cómo use la función AGREGAR y otras funciones como MODE, MEDIAN, AVERAGE, etc. con más detalle.
Con suerte, esto El artículo proporciona un buen punto de partida para crear y usar filtros en Excel. Si tiene alguna pregunta, no dude en publicar un comentario. ¡Disfruta!