Master Excel Tables with AutoFilter and Subtotal Master Excel tabelas com AutoFilter e Subtotal

Posted on April 9, 2008 at 5:23 am Postado em 9 de abril de 2008, 5:23 am

How much of Microsoft Excel are you using? Como muito do Microsoft Excel que você está usando? I thought so. Eu pensava assim. Get ready because we’re stepping it up another notch. Excel is a powerful but often underutilized tool. Prepare-se, porque estamos intensificando it up outro entalhe. Excel é uma ferramenta poderosa, mas por vezes subutilizados. 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. Muitos de nós usá-lo todos os dias para planejar e monitorar orçamentos, marketing analisar os dados, e ocasionalmente para fazer coisas mais importantes, como gerenciar o escritório Março Madness pool. Several Online Tech Tips posts have already covered clever but unfamiliar functions like Vários Online Tech Dicas cargos já abrangidos inteligente mas desconhece funções como VLOOKUP VLOOKUP and E SUMIF SUMIF . Now it’s time for another round of Outrageous Excel Tips! Agora é a hora de uma outra ronda de Outrageous Excel Dicas!

I’m going to illustrate two Excel features which, used in concert, make tabular analysis a snap. Eu vou para ilustrar Excel duas características que, utilizados no concerto, fazer análises tabulares num piscar de olhos. Tabular data is what you get from a database extract or CSV file. Dados tabulares é o que você começa a partir de uma base de dados extrato ou 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. O AutoFilter e oft-esquecido Subtotal funções, como o amendoim e chocolate, são destinadas a ser utilizadas em conjunto. Elas fornecem uma maneira fácil de "código" de uma planilha para fazer subconjuntos, somas, médias, contagem, e muito mais.

The Excel table below is an extract of our company’s purchases by department. O Excel tabela abaixo é um extrato do nosso departamento de compras da empresa. We have a typical “header” row, followed by 11 data rows. Temos um típico "cabeçalho" linha, seguido por 11 linhas de dados. You can see the functions I included for this table: Sum, Count, Min and Average. Você pode ver as funções eu incluído para esta tabela: Soma, Contar, Min e Média. More about them later. Mais sobre eles mais 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. Agora, o patrão chega em e necessita de ver o quanto a publicidade airtime Marketing pessoas estão compra, e quanto estamos a pagar. AutoFilter gives us the power to make entire rows visible or invisible based on cell contents. AutoFilter nos dá o poder de fazer todo linhas visíveis ou invisíveis baseadas em células conteúdo. The Subtotal function updates the Sum, Count, Min and Average accordingly. A Subtotal função atualiza o Sum, Count, Min e médio em conformidade. Let’s quickly add an AutoFilter. Vamos rapidamente adicionar um AutoFilter.

Excel AutoFilter Excel AutoFilter

To add AutoFilter, select the entire range of cells, including the header row containing the column labels (A1:F12). Para adicionar AutoFilter, selecione o conjunto de células, incluindo o cabeçalho da linha que contém a coluna etiquetas (A1: F12). Then use Data -> Filter -> AutoFilter. Então use Dados -> Filter -> AutoFilter. Now you can see the filter indicator above each column in the table: Agora você pode ver o filtro indicador acima de cada coluna na tabela:

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: O esmerado coisa é o seguinte: clique em qualquer coluna, e AutoFilter dá-lhe uma lista suspensa dos valores da coluna para você escolher a partir de, e filtros toda a mesa para o 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. As listas são dinâmicas baseadas na tabela conteúdos, de forma adicionando uma linha ou alterar um existente valor significa a lista suspensa está atualizado também. You can show all values (default), select just one value, or create a custom filter based on boolean logic. Você pode mostrar todos os valores (padrão), selecione apenas um valor, ou criar um filtro personalizado baseados em lógica booleana. In this example we need to select the “Airtime” value. Neste exemplo, precisamos de seleccionar o "Crédito" valor.

The result is: O resultado é:

AutoFilter acts by making rows visible and invisible. AutoFilter actos fazendo linhas visíveis e invisíveis. The original eleven data rows are still present, although some are invisible based on the selected filter. Os dados originais onze linhas ainda estão presentes, embora alguns são invisíveis baseado no filtro selecionado. A2:F12 is still data range. A2: F12 é ainda dados intervalo.

We quickly see the two “Airtime” buys in this dataset, and the average price paid is $5000. Nós ver rapidamente os dois "Crédito" adquire neste dados, bem como o preço médio pago é de R $ 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 o menu suspenso de filtros para selecionar todos os valores novamente, ou selecione um valor diferente, e fazê-lo em qualquer número de colunas. Depending on your table, some columns will make sense for filtering and others may not. Dependendo da sua mesa, algumas colunas fará sentido para filtragem e outros não.

Excel Subtotals Excel subtotais

In this example, the values for Sum, Count, Min, and Average update based on the filter automatically because of the powerful Subtotalfunction Subtotalfunction. Neste exemplo, os valores de Sum, Count, Min, e média actualização baseia-se no filtro automaticamente por causa do poderoso Subtotalfunction Subtotalfunction. Subtotal operates only on visible rows of data in a range, so it’s great in combination with AutoFilters. Subtotal opera somente nas linhas visíveis de dados em um intervalo, por isso é grande em combinação com AutoFilters. Subtotal is also simple to set up. The syntax for Subtotal is: Subtotal também é fácil de configurar. A sintaxe para Subtotal é:

=Subtotal(function#, range) = Subtotal (# função, intervalo)

Okay, you do have to know the “function #” which is a pain. Are you listening MicroSoft? Ok, você tem de conhecer a "função #", que é uma dor. Tem ouvir MicroSoft? Here is the list of commonly used functions with Subtotal. Aqui está a lista de funções comumente usadas com Subtotal. Others can be accessed by searching Excel Help: Outros podem ser acessados através de pesquisa Excel Ajuda:

Function # Função #	Function Name Função Nome
101	AVERAGE MÉDIA
102	COUNT
104	MAX
105	MIN
109	SUM

In the example the Sum, Count, Min and Averages are obtained by these respective formulae: No exemplo do Soma, Contar, Min e médias são obtidas por estes respectivas fórmulas:

SUM =Subtotal(109,F2:F12) SUM = Subtotal (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) MÉDIA = Subtotal (101, F2: F12)

Now that you have seen the power of AutoFilter and Subtotal , go give it a shot. Agora que você tem visto o poder de AutoFilter e Subtotal, ir dar-lhe um tiro. After all, Fantasy-League baseball is just starting. Afinal, Fantasy-League baseball está apenas começando.

Pete Kushmeider, Writer and blogger Pete Kushmeider, escritor e blogger

Technorati Tags: Technorati Tags: Excel , Data Analysis Data Analysis