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 las columnas que ocultarán las 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, lo guiaré a través de los pasos para crear filtros en Excel y también usar funciones integradas para resumir los datos filtrados.

Crear 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 use 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 quiero crear un filtro en la columna Ciudad . ^(City)En Excel , esto es muy fácil de hacer. Continúe y haga clic en la pestaña Datos^(Data) en la cinta y luego haga clic en el botón Filtro^(Filter) . No tienes que seleccionar los datos en la hoja o hacer clic^{(sheet or click)} en la primera fila tampoco.

Cuando hace clic en Filtrar^(Filter) , cada columna en la primera fila tendrá automáticamente un pequeño botón desplegable agregado a la derecha.

Ahora siga adelante y haga clic en la flecha desplegable en la columna Ciudad^{(City column)} . 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^{(City column)} . Tenga en cuenta que cuando ordena los datos, moverá toda la fila, no solo los valores en la columna Ciudad^{(City column)} . Esto asegurará que sus datos permanezcan intactos tal como estaban antes.

Además, es una buena idea agregar una columna al frente llamada ID y numerarla^{(ID and number)} de una a todas las filas que tenga en su hoja de trabajo. De esta manera, siempre puede ordenar por la columna de ID^{(ID column)} y recuperar sus datos en el mismo orden en que estaban 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^{(City column)} . Hasta ahora, no hay filas ocultas. Ahora echemos un vistazo a las casillas de verificación en la parte inferior del cuadro de diálogo de filtro^{(filter dialog)} . En mi ejemplo, solo tengo tres valores únicos en la columna Ciudad^{(City column)} y esos tres aparecen en la lista.

Seguí adelante y desmarqué dos ciudades y dejé una marcada. Ahora solo se muestran 8 filas de datos y el resto está oculto. Puede darse cuenta fácilmente de que está viendo 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 la cantidad de resultados. En la columna C, tengo el número total de miembros en cada familia y solo quiero ver los resultados de las familias con más de dos miembros.

Continúe y haga clic en la flecha desplegable en la Columna C^{(Column 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 numéricos^{(Number Filters)} y luego hacer clic en Mayor que^{( Greater Than)} . Como puede ver, también hay muchas otras opciones.

Aparecerá un nuevo cuadro de diálogo y aquí puede escribir el valor del filtro. También puede agregar más de un criterio con una función AND u OR^{(AND or OR function)} . Podría decir que desea filas donde el valor sea mayor que 2 y no igual a 5, por ejemplo.

Ahora tengo solo 5 filas de datos: familias solo de Nueva Orleans^{(New Orleans)} y con 3 o más miembros. ¿Bastante fácil ? ^(Easy)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" .^{(Clear Filter From “Column Name”)}

Así que eso es todo para los filtros simples en Excel . Son muy fáciles de usar y los resultados son bastante sencillos. Ahora echemos un vistazo a los filtros complejos utilizando el cuadro de diálogo Filtros avanzados .^(Advanced)

Crear filtros avanzados en Excel

Si desea crear filtros más avanzados, debe utilizar el cuadro de diálogo Filtro ^{(filter dialog)}avanzado^(Advanced) . Por ejemplo, digamos que quiero ver todas las familias que viven en Nueva Orleans^{(New Orleans)} con más de 2 miembros en su familia O^(OR) todas las familias en Clarksville con más de 3 miembros en su familia Y^(AND) solo las que tienen una dirección de correo electrónico final .EDU . Ahora no puedes hacer eso con un simple filtro.

Para hacer esto, necesitamos configurar la hoja de Excel de^{(Excel sheet)} manera un poco diferente. Continúe e inserte un par de filas sobre su conjunto de datos y copie las etiquetas de encabezado exactamente en la primera fila como se muestra a continuación.

Ahora así es como funcionan los filtros avanzados. Primero debe escribir sus criterios en las columnas en la parte superior y luego hacer clic en el botón Avanzado^(Advanced) en Ordenar y filtrar^{( Sort & Filter)} en la pestaña Datos .^(Data)

Entonces, ¿qué podemos escribir exactamente en esas celdas? Bien, empecemos con nuestro ejemplo. Solo queremos ver los datos de Nueva Orleans o Clarksville^{(New Orleans or Clarksville)} , así que introdúzcalos en las celdas E2 y E3^{(E2 and E3)} .

Cuando escribe valores en diferentes filas, significa O. Ahora queremos familias de Nueva Orleans^{(New Orleans)} con más de dos miembros y familias de Clarksville con más de 3 miembros. Para ello, escriba >2 en C2 y >3 en C3.

Dado que >2 y Nueva Orleans^{(New Orleans)} están en la misma fila, será un operador AND^{(AND operator)} . Lo mismo es cierto para la fila 3^{(row 3)} anterior. Finalmente, solo queremos las familias con dirección de correo electrónico final .EDU. Para hacer esto, simplemente escriba *.edu tanto en D2 como en D3^{(D2 and D3)} . El símbolo * significa cualquier número de caracteres.

Una vez que haga eso, haga clic en cualquier parte de su conjunto de datos y luego haga clic en el botón Avanzado^(Advanced) . El campo Rango de lista^{(List Rang)} calculará automáticamente su conjunto de datos desde que hizo clic en él antes de hacer clic en el botón Avanzado^{(Advanced button)} . Ahora haga clic en el pequeño botón pequeño a la derecha del botón de rango de Criterios^{(Criteria range)} .

Seleccione todo^{(Select everything)} , desde A1 hasta E3 y luego haga clic en el mismo botón nuevamente para volver al cuadro de diálogo Filtro avanzado^{(Advanced Filter dialog)} . ¡ Haga clic en Aceptar^{(Click OK)} y sus datos ahora deberían estar 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 para el 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.

Resumen de datos filtrados

Ahora digamos que quiero resumir el número de miembros de la familia en mis datos filtrados, ¿cómo podría hacer eso? Bueno, borre nuestro filtro haciendo clic en el botón Borrar^(Clear) en la cinta. No se preocupe, es muy fácil volver a aplicar el filtro avanzado simplemente haciendo clic en el botón Avanzado^{(Advanced button)} y haciendo clic en Aceptar nuevamente.

En la parte inferior de nuestro conjunto de datos, agreguemos una celda llamada Total y luego agreguemos una función de suma para resumir el total de miembros de la familia. En mi ejemplo, acabo de escribir =SUM(C7:C31) .

Entonces, si miro a todas las familias, tengo 78 miembros en total. Ahora sigamos adelante y volvamos a aplicar nuestro filtro Avanzado^{(Advanced filter)} 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^{(SUM function)} 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.

El primero es SUBTOTAL . Antes de que usemos cualquiera de estas funciones especiales, querrá borrar su filtro y luego escribir la función.

Una vez que se borre el filtro, continúe y escriba =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^{(summation function)} que desea usar usando un número.

En nuestro ejemplo, quiero usar SUM , así que escribiría el número 9^{(number 9)} o simplemente haría clic en él desde el 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 antes. Sin embargo, si ahora vuelves a aplicar el filtro, ¡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.

La segunda función que funciona prácticamente igual que la función SUBTOTAL^{(SUBTOTAL function)} es AGGREGATE . La única diferencia es que hay otro parámetro en la función AGREGAR^{(AGGREGATE function)} donde debe especificar que desea ignorar las filas ocultas.

El primer parámetro es la función de suma^{(summation function)} que desea usar y, al igual que con SUBTOTAL , 9 representa la función SUMA^{(SUM function)} . 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 a usar la función AGREGADO^{(AGGREGATE function)}  y otras funciones como MODA^(MODE) , MEDIANA^(MEDIAN) , PROMEDIO^(AVERAGE) , etc. con más detalle.

Con suerte, este artículo le brinda un buen punto de partida^{(starting point)} para crear y usar filtros en Excel . Si tiene alguna pregunta, no dude en publicar un comentario. ¡Disfrutar!

How to Filter Data in Excel

I recently wrоte an article on how to use summary functions in Excel to easily summarize large amounts of data, but that article took into account all data on the worksheet. What if you only want to look at a subset of data and summarize the subset of data?

In Excel, you can create filters on columns that will hide rows that do not match your filter. In addition, you can also use special functions in Excel to summarize data using only the filtered data.

In this article, I’ll walk you through the steps for creating filters in Excel and also using built-in functions to summarize that filtered data.

Create Simple Filters in Excel

In Excel, you can create simple filters and complex filters. Let’s start off with simple filters. When working with filters, you should always have one row at the top that is used for labels. It’s not a requirement to have this row, but it makes working with filters a bit easier.

Above, I have some fake data and I want to create a filter on the City column. In Excel, this is really easy to do. Go ahead and click on the Data tab in the ribbon and then click on the Filter button. You don’t have to select the data on the sheet or click in the first row either.

When you click on Filter, each column in the first row will automatically have a small dropdown button added at the very right.

Now go ahead and click on the dropdown arrow in the City column. You’ll see a couple of different options, which I will explain below.

At the top, you can quickly sort all the rows by the values in the City column. Note that when you sort the data, it will move the entire row, not just the values in the City column. This will ensure that your data remains intact just as it was before.

Also, it’s a good idea to add a column at the very front called ID and number it from one to however many rows you have in your worksheet. This way, you can always sort by the ID column and get your data back in the same order it was originally, if that is important to you.

As you can see, all the data in the spreadsheet is now sorted based on the values in the City column. So far, no rows are hidden. Now let’s take a look at the checkboxes at the bottom of filter dialog. In my example, I have only three unique values in the City column and those three show up in the list.

I went ahead and unchecked two cities and left one checked. Now I only have 8 rows of data showing and the rest are hidden. You can easily tell you are looking at filtered data if you check the row numbers at the far left. Depending on how many rows are hidden, you’ll see a few extra horizontal lines and the color of the numbers will be blue.

Now let’s say I want to filter on a second column to further reduce the number of results. In column C, I have the total number of members in each family and I want to only see the results for families with more than two members.

Go ahead and click on the dropdown arrow in Column C and you’ll see the same checkboxes for each unique value in the column. However, in this case, we want to click on Number Filters and then click on Greater Than. As you can see, there are a bunch of other options too.

A new dialog will pop up and here you can type in the value for the filter. You can also add more than one criteria with an AND or OR function. You could say you want rows where the value is greater than 2 and not equal to 5, for example.

Now I’m down to just 5 rows of data: families only from New Orleans and with 3 or more members. Easy enough? Note that you can easily clear a filter on a column by clicking on the dropdown and then clicking the Clear Filter From “Column Name” link.

So that’s about it for simple filters in Excel. They are very easy to use and the results are pretty straight-forward. Now let’s take a look at complex filters using the Advanced filters dialog.

Create Advanced Filters in Excel

If you want to create more advanced filters, you have to use the Advanced filter dialog. For example, let’s say I wanted to see all families that live in New Orleans with more than 2 members in their family OR all families in Clarksville with more than 3 members in their family AND only the ones with a .EDU ending email address. Now you can’t do that with a simple filter.

To do this, we need to setup the Excel sheet a little differently. Go ahead and insert a couple of rows above your set of data and copy the heading labels exactly into the first row like shown below.

Now here is how advanced filters work. You have to first type your criteria into the columns at the top and then click the Advanced button under Sort & Filter on the Data tab.

So what exactly can we type into those cells? OK, so let’s start with our example. We only want to see data from New Orleans or Clarksville, so let’s type those into cells E2 and E3.

When you type values on different rows, it means OR. Now we want New Orleans families with more than two members and Clarksville families with more than 3 members. To do this, type in >2 in C2 and >3 in C3.

Since >2 and New Orleans are on the same row, it will be an AND operator. The same is true for row 3 above. Finally, we want only the families with .EDU ending email address. To do this, just type in *.edu into both D2 and D3. The * symbol means any number of characters.

Once you do that, click anywhere in your dataset and then click on the Advanced button. The List Range field will automatically figure out your dataset since you clicked into it before clicking the Advanced button. Now click on the small little button at the right of the Criteria range button.

Select everything from A1 to E3 and then click on the same button again to get back to the Advanced Filter dialog. Click OK and your data should now be filtered!

As you can see, now I have only 3 results that match all of those criteria. Note that the labels for the criteria range have to match exactly with the labels for the dataset in order for this to work.

You can obviously create a lot more complicated queries using this method, so play around with it to get your desired results. Finally, let’s talk about applying summation functions to filtered data.

Summarizing Filtered Data

Now let’s say I want to sum up the number of family members on my filtered data, how would I got about doing that? Well, let’s clear our filter by clicking on the Clear button in the ribbon. Don’t worry, it’s very easy to apply the advanced filter again by simply clicking on the Advanced button and clicking OK again.

At the bottom of our dataset, let’s add a cell called Total and then add a sum function to sum up the total family members. In my example, I just typed =SUM(C7:C31).

So if I look at all families, I have 78 members total. Now let’s go ahead and reapply our Advanced filter and see what happens.

Whoops! Instead of showing the correct number, 11, I still see the total is 78! Why is that? Well, the SUM function does not ignore hidden rows, so it is still doing the calculation using all the rows. Luckily, there are a couple of functions you can use to ignore hidden rows.

The first is SUBTOTAL. Before we use any of these special functions, you will want to clear your filter and then type in the function.

Once the filter is cleared, go ahead and type in =SUBTOTAL( and you should see a dropdown box appear with a bunch of options. Using this function, you first pick the type of summation function you want to use using a number.

In our example, I want to use SUM, so I would type in the number 9 or just click on it from the dropdown. Then type a comma and select the range of cells.

When you press enter, you should see the value of 78 is the same as previously. However, if you now apply the filter again, we will see 11!

Excellent! That’s exactly what we want. Now you can adjust your filters and the value will always reflect only the rows that are currently showing.

The second function that works pretty much exactly the same as the SUBTOTAL function is AGGREGATE. The only difference is that there is another parameter in the AGGREGATE function where you have to specify that you want to ignore hidden rows.

The first parameter is the summation function you want to use and as with SUBTOTAL, 9 represents the SUM function. The second option is where you have to type in 5 to ignore hidden rows. The last parameter is the same and is the range of cells.

You can also read my article on summary functions to learn how to use the AGGREGATE function and other functions like MODE, MEDIAN, AVERAGE, etc. in more detail.

Hopefully, this article gives you a good starting point for creating and using filters in Excel. If you have any questions, feel free to post a comment. Enjoy!

Related posts

• Trazando sus datos de Excel

• Use la nueva función "Insertar datos de la imagen" de Excel Mobile

• Usar funciones de resumen para resumir datos en Excel

• Use Excel como una herramienta para copiar datos de la Web

• Añadir un Regression Trendline lineal a un Excel Scatter Plot

• Cómo encontrar y calcular el rango en Excel

• Cómo encontrar valores coincidentes en Excel

• Cómo crear un Distribution List en Outlook

• Vincular celdas entre hojas y libros de trabajo en Excel

• Cómo restar fechas en Excel

• Los 40 mejores atajos de teclado de Microsoft Excel

• Cómo crear un Flowchart en Word and Excel

• Cómo calcular Variance en Excel

• 13 OneNote Tips & Tricks para organizar mejor sus notas

• Use Excel para calcular una tasa de interés efectiva a partir de una tasa de interés nominal

• Autoajustar anchos de columna y alturas de fila en Excel

• Cómo entender el análisis hipotético en Microsoft Excel

• Formatear celdas usando formato condicional en Excel

• Cómo de forma segura Password Protect un Excel File

• Cómo hacer una lista de verificación en Excel