Master Excel Tables with AutoFilter and Subtotal Master Excel con tablas y AutoFilter Subtotal

Posted on April 9, 2008 at 5:23 am Publicado el 9 de abril de 2008 en 5:23

How much of Microsoft Excel are you using? ¿Cuánto de Microsoft Excel está usando? I thought so. He pensado así. Get ready because we’re stepping it up another notch. Excel is a powerful but often underutilized tool. Prepárate porque lo que pisar otra muesca. Excel es una poderosa herramienta, pero a menudo infrautilizados. Many of us use it every day to plan and track budgets, analyze marketing data, and occasionally to do more important things such as manage the office March Madness pool. Muchos de nosotros usamos todos los días para planificar y realizar el seguimiento de los presupuestos, el análisis de datos de marketing, y de vez en cuando para hacer cosas más importantes como la gestión de la oficina de Marzo Locura piscina. Several Online Tech Tips posts have already covered clever but unfamiliar functions like Varios Consejos técnicos puestos en línea ya han cubierto inteligente, pero desconocido en funciones, como VLOOKUP VLOOKUP and Y SUMIF SUMIF . Now it’s time for another round of Outrageous Excel Tips! Ahora es tiempo para otra ronda de Consejos de Excel Indignante!

I’m going to illustrate two Excel features which, used in concert, make tabular analysis a snap. Voy a ilustrar dos Excel características que, utilizados en concierto, hacer análisis tabular un broche de presión. Tabular data is what you get from a database extract or CSV file. Tabular los datos es lo que usted consigue extraer de una base de datos o archivo CSV. The AutoFilter and oft-overlooked Subtotal functions, like peanut butter and chocolate, are meant to be used together. They provide an easy way to “code” a spreadsheet to do subsets, sums, averages, counts, and more. El AutoFilter y frecuentemente pasado por alto Subtotal las funciones, como la crema de cacahuate y chocolate, están diseñadas para ser usadas juntas. Proporcionan una forma fácil de "código" una hoja de cálculo para hacer subconjuntos, sumas, promedios, cuenta, y mucho más.

The Excel table below is an extract of our company’s purchases by department. La tabla de Excel que figura a continuación es un extracto de nuestra empresa por el departamento de compras. We have a typical “header” row, followed by 11 data rows. Tenemos un típico "de cabecera" fila, seguido por 11 líneas de datos. You can see the functions I included for this table: Sum, Count, Min and Average. Usted puede ver las funciones que incluye para esta mesa: Sum, Count, Min y Promedio. More about them later. Más acerca de ellos más tarde.

Now, the boss comes in and needs to see just how much advertising airtime the Marketing people are purchasing, and how much we are paying. Ahora, el jefe viene en necesidades y para ver cuánto tiempo de emisión de publicidad de la compra de la gente de marketing, y cuánto estamos pagando. AutoFilter gives us the power to make entire rows visible or invisible based on cell contents. AutoFilter nos da el poder para hacer todo filas visibles o invisibles basada en el contenido de células. The Subtotal function updates the Sum, Count, Min and Average accordingly. El Subtotal se actualiza la función Sum, Count, Min y Promedio en consecuencia. Let’s quickly add an AutoFilter. Vamos a añadir un AutoFilter rápidamente.

Excel AutoFilter Excel AutoFilter

To add AutoFilter, select the entire range of cells, including the header row containing the column labels (A1:F12). Para añadir AutoFilter, seleccione todo el rango de celdas, incluyendo la cabecera de la fila que contiene la columna de las etiquetas (A1: F12). Then use Data -> Filter -> AutoFilter. Luego el uso de datos -> Filtro -> AutoFilter. Now you can see the filter indicator above each column in the table: Ahora puede ver el filtro de cada indicador por encima de la columna en la tabla:

The neat thing is this: click on any column heading, and AutoFilter gives you a drop-down list of values in the column for you to select from, and filters the entire table for the result: La cosa aseada es la siguiente: hacer clic en cualquier cabecera de la columna, y AutoFilter le ofrece una lista desplegable de valores en la columna para que usted seleccione, y los filtros de toda la tabla para el resultado:

The lists are dynamic based on table contents, so adding a row or changing an existing value means the drop-down list is updated also. Las listas son dinámicas basadas en el cuadro contenido, de modo que la adición de una fila o una modificación de un valor existente significa en la lista desplegable se actualiza también. You can show all values (default), select just one value, or create a custom filter based on boolean logic. Puede mostrar todos los valores (por defecto), selecciona sólo un valor, o crear un filtro personalizado basado en la lógica booleana. In this example we need to select the “Airtime” value. En este ejemplo tenemos que seleccionar el "tiempo aire" de valor.

The result is: El resultado es:

AutoFilter acts by making rows visible and invisible. AutoFilter actos haciendo filas visibles e invisibles. The original eleven data rows are still present, although some are invisible based on the selected filter. El original de once líneas de datos todavía están presentes, aunque algunos son invisibles basado en el filtro seleccionado. A2:F12 is still data range. A2: F12 sigue gama de datos.

We quickly see the two “Airtime” buys in this dataset, and the average price paid is $5000. Estamos ver rápidamente los dos "Tiempo Aire" en la compra de este conjunto de datos, y el precio medio pagado es de $ 5000. Use the drop down filters to select all values again, or select a different value, and do it on any number of columns. Use el menú desplegable de filtros para seleccionar todos los valores de nuevo, o seleccionar un valor diferente, y hacerlo en cualquier número de columnas. Depending on your table, some columns will make sense for filtering and others may not. Dependiendo de su mesa, algunas columnas tendrá sentido para el filtrado y otros no.

Excel Subtotals Subtotales Excel

In this example, the values for Sum, Count, Min, and Average update based on the filter automatically because of the powerful Subtotalfunction Subtotalfunction. En este ejemplo, los valores de Sum, Count, Min, y el promedio de actualización basado en el filtro automáticamente debido al poder de Subtotalfunction Subtotalfunction. Subtotal operates only on visible rows of data in a range, so it’s great in combination with AutoFilters. Subtotal opera sólo en las filas de los datos visibles en una serie, por lo que es grande en combinación con AutoFilters. Subtotal is also simple to set up. The syntax for Subtotal is: Subtotal también es sencillo. La sintaxis es para Subtotal:

=Subtotal(function#, range) = Subtotal (# función, rango)

Okay, you do have to know the “function #” which is a pain. Are you listening MicroSoft? Bueno, usted tiene que saber la "función #", que es un dolor. MicroSoft ¿Está usted escuchando? Here is the list of commonly used functions with Subtotal. Aquí está la lista de funciones de uso común con Subtotal. Others can be accessed by searching Excel Help: Otros se puede acceder mediante la búsqueda en Ayuda de Excel:

Function # Función #	Function Name Función Nombre
101	AVERAGE PROMEDIO
102	COUNT
104	MAX
105	MIN
109	SUM

In the example the Sum, Count, Min and Averages are obtained by these respective formulae: En el ejemplo de la Sum, Count, Min y promedios se obtienen por estas fórmulas respectivas:

SUM =Subtotal(109,F2:F12) Subtotal = SUMA (109, F2: F12)

COUNT =Subtotal(102,F2:F12) COUNT = Subtotal (102, F2: F12)

MIN =Subtotal(105,F2:F12) MIN = Subtotal (105, F2: F12)

AVERAGE =Subtotal(101,F2:F12) PROMEDIO = Subtotal (101, F2: F12)

Now that you have seen the power of AutoFilter and Subtotal , go give it a shot. Ahora que ha visto el poder de AutoFilter y Subtotal, vaya darle un tiro. After all, Fantasy-League baseball is just starting. Después de todo, Fantasía-Liga de béisbol acaba de empezar.

Pete Kushmeider, Writer and blogger Pete Kushmeider, escritor y blogger

Technorati Tags: Technorati Tags: Excel , Data Analysis Análisis de Datos