Tabela de resumo do Excel: como criar e trabalhar? Trabalhando com o Excel PivotTables

Muitos usuários de escritório geralmente enfrentam uma série de problemas ao tentar criar e editar qualquer documento do Office. Muitas vezes isso é devido ao fato de que as empresas usam vários programas de escritório, os princípios de trabalho em que podem variar muito. Especialmente muitas dificuldades podem criar uma planilha do Excel.

Felizmente, o MS Office 2010-2013 apareceu recentemente, que não só inclui uma série de programas atualizados para o processamento de arquivos de texto, tabelas, bancos de dados e apresentações, mas também permite que vários funcionários trabalhem com eles simultaneamente, o que não tem preço em um ambiente corporativo.

Por que novas versões?

Para entender esse problema, você precisa imaginar todas as mudanças mais significativas que ocorreram com este pacote de escritório.

Como antes, o Excel é o segundo programa mais popular, que permite que você não apenas crie tabelas simples, mas até crie bancos de dados bastante complexos. Tal como acontece com outros componentes, o botão "Office" foi adicionado, clicando no qual você pode salvar o documento no formato exigido por você, alterá-lo ou especifique as medidas necessárias para protegê-lo. O módulo de segurança do documento foi significativamente atualizado.

Quanto às mudanças mais significativas no Excel, devemos notar a correção de muitos erros nas fórmulas, razão pela qual, nas versões anteriores, houve bastante erro grosseiro nos cálculos.

Devo dizer que a nova versão do pacote não é apenas um conjunto de programas de escritório, mas também uma ferramenta poderosa, adequada para resolver tarefas muito complexas. Como você pode entender, hoje consideramos uma planilha do Excel.

O que é e para que serve?

A mudança mais significativa nas novas versões do "Office" é sua interface completamente redesenhada, que foi chamada de criadores de fita (fita). Nas fitas, todas as 1500 equipes são convenientemente agrupadas em categorias, então você não precisa procurá-las por muito tempo. Para cumprir completamente esses parâmetros, os desenvolvedores adicionados ao Excel também melhoraram as tabelas de resumo.

Hoje vamos considerar o Excel "para bules". As tabelas dinâmicas são uma ferramenta especial na qual os resultados de um processo são agrupados visualmente. Simplificando, com a ajuda deles, você pode ver quantos produtos vendeu cada vendedor para o turno de trabalho. Além disso, são utilizados nos casos em que é necessário:

• Prepare cálculos analíticos para escrever relatórios finais.
• Calcule cada um de seus indicadores separadamente.
• Faça um agrupamento de dados por seus tipos.
• Realize uma filtragem e análise aprofundada dos dados obtidos.

Neste artigo, veremos maneiras simples de criá-las. Você aprenderá como uma planilha do Excel ajuda a criar e analisar séries temporais, criar e desmontar uma previsão mais detalhada.

Crie o documento desejado

Então, como criar uma tabela dinâmica no Excel? Primeiro, precisamos compilar um documento simples no qual inserimos dados para sua análise subseqüente. Uma coluna deve ter um parâmetro. Vamos dar o exemplo mais simples:

• Tempo de venda.
• Mercadorias vendidas.
• O custo de todas as vendas.

Assim, entre todos os parâmetros em cada coluna particular, uma ligação é formada: suponha que as tênis fossem vendidas às 9 da manhã, com o lucro sendo n-rubles.

Formatando uma planilha

Tendo preparado todas as informações iniciais, coloque o cursor na primeira célula da primeira coluna, abra a aba "Inserir" e, em seguida, clique no botão "Tabela de resumo". Uma caixa de diálogo aparece imediatamente , na qual você pode fazer o seguinte:

• Se você clicar imediatamente no botão "OK", a planilha do Excel será exibida imediatamente em uma folha separada.
• Personalize completamente a inferência.

No último caso, os desenvolvedores nos dão a capacidade de determinar o intervalo de células nas quais a informação desejada será exibida. Depois disso, o usuário deve determinar exatamente onde a nova tabela será criada: em um existente ou em uma folha recém-criada. Ao clicar em "OK", você vê imediatamente uma mesa pronta à sua frente. É assim que a criação de tabelas dinâmicas no Excel está quase concluída.

No lado direito da folha estão as áreas com as quais você trabalhará. Os campos podem ser arrastados para áreas separadas, após o que os dados deles serão exibidos na tabela. Consequentemente, a própria tabela pivô estará localizada na parte esquerda do espaço de trabalho.

Mantendo pressionado o campo "Mercadorias" com o botão esquerdo do mouse pressionado, enviamos para "Nome da Linha" e o item "Soma de todas as vendas" da mesma maneira é encaminhado para "Valores" (no lado esquerdo da folha). É assim que você pode obter a quantidade de vendas para todo o período analisado.

Criação e agrupamento de séries temporais

Para analisar as vendas para intervalos de tempo específicos, você precisa inserir os itens relevantes na própria tabela. Para fazer isso, vá para a folha "Dados" e, em seguida, insira três novas barras imediatamente após a data. Selecione a coluna com o nome do produto e clique no botão "Colar".

É muito importante que todas as colunas recém-criadas estejam dentro de uma tabela já existente com os dados originais. Nesse caso, você não precisa criar as tabelas dinâmicas no Excel novamente. Você simplesmente adiciona novos campos com os dados necessários.

As fórmulas utilizadas

Por exemplo, as colunas recém-criadas podem ser chamadas de "Ano", "Mês", "Meses-Anos". Para obter os dados que nos interessam, cada um deles terá que escrever uma fórmula separada para os cálculos:

• No "Anual" inserimos uma fórmula do formulário: "= ANO" (referente à data).
• O mês deve ser complementado com a expressão: "= MÊS" (também com referência à data).
• Na terceira coluna, inserimos uma fórmula do formulário: "= CLIQUE" (referente a isso por um ano e um mês).

Consequentemente, obtemos três colunas com todos os dados iniciais. Agora você precisa ir ao menu "Resumo", clicar em qualquer espaço livre na tabela com o botão direito do mouse e, em seguida, no menu de contexto aberto, selecione "Atualizar". Preste atenção! Escreva cuidadosamente as fórmulas nas planilhas do Excel, pois qualquer erro levará à previsão imprecisa.

Como analisar as vendas por ano?

Mais uma vez, mantemos o item "Ano" com o botão esquerdo do mouse e, em seguida, arraste-o para o "Nome da coluna", após o que a tabela de resumo começa a exibir os resultados da venda de um determinado tipo de produto para todos os anos. E se você também precisa analisar as vendas mensais? Da mesma forma, esprememos o "Mês" da LMC, arrastando-o sob a coluna anual.

Para obter a dinâmica visual de mudanças mensais nas vendas por anos, o campo "Mês" deve ser arrastado diretamente para a coluna anual que foi movida anteriormente para "Nome da Coluna". Depois de concluir esta operação, você verá a tabela na seguinte visão:

• A soma das vendas de um produto específico para todo o ano.
• Dinâmica das vendas de cada um deles por anos.

Nós removemos dados do problema

Suponha que, a partir da emissão, precisamos remover os dados para outubro, uma vez que não temos estatísticas completas sobre isso. Na tabela de resumo, estamos procurando o "Report Filter", depois arraste o "Year-Month" para ele.

Acima da tabela aparece um filtro no qual você deseja verificar a caixa ao lado de "Selecionar vários itens". Depois disso, na lista que aparece, desmarque a caixa de outubro e clique em "OK".

Assim, você pode adicionar e remover elementos do filtro, gerando análises realmente relevantes e necessárias. O programa calcula automaticamente os indicadores dos meses que o interessam, mostrando visualmente a dinâmica de mudanças nos indicadores.

Calculamos as previsões

Vamos tentar prever os resultados das vendas por algum tempo por vir. Para fazer isso, você deve primeiro desativar os totais para que eles não introduzam incorreções na previsão gerada.

Para fazer isso, coloque o cursor no campo "total total" e, em seguida, clique no botão "Excluir o total geral". Depois disso, realizamos uma atualização automática da planilha do Excel da maneira descrita acima (clique no botão direito do mouse, selecione "Atualizar").

Como resultado, esses dados desaparecem da nossa tabela de resumo. Para construir uma representação visual das previsões de vendas, colocamos o cursor no ano a partir do qual planejamos empurrar e, em seguida, clique no botão "Gráfico do modelo de previsão" com o botão esquerdo do mouse.

Depois disso, aparece uma programação bonita e informativa, na qual os resultados previstos da atividade de compra são apresentados em forma visual na previsão que você está interessado. Isso economizará energia e economizará dinheiro, o que pode ser usado para comprar bens realmente necessários.

Usamos outras fontes de dados para criar uma tabela de resumo

É hora de considerar lições de Excel mais complexas. Uma tabela de resumo pode ser criada com base em documentos que foram editados por outros usuários.

A função "Merge" nos ajudará neste. Este programa permite que você combine automaticamente o texto do documento e os endereços, nomes ou outras informações necessárias para criar previsões personalizadas, o que, em nosso caso, é necessário.

A função de mesclagem nas versões posteriores do "Office" permaneceu praticamente inalterada em comparação com o Office 2003. No entanto, as consultas aos bancos de dados para selecionar a fonte de dados podem ser realizadas usando o Microsoft Query. Para obter dados de uma tabela dinâmica do Excel, você precisa de dois objetos:

• O documento principal que contém as informações que você precisa, bem como os campos que indicam o programa do Excel, quais dados devem ser inseridos no documento. A tabela principal é um documento do processador de tabela padrão, mas você deve especificar na caixa de diálogo Ajustador de mala direta que deve ser usada como a tabela principal.
• Uma fonte de dados contendo a informação necessária para compilar a previsão. Você pode criar uma nova tabela da maneira que mencionamos acima.

Quando o processo de criação de documentos de resumo ocorre, o programa cria uma cópia para cada um dos registros contidos no arquivo de fonte de dados. Esses arquivos podem ser exibidos como seções separadas de um novo documento ou usadas como seções de sua tabela dinâmica.

Basta clicar no campo que você precisa com o botão esquerdo do mouse e você pode ver os dados de origem em vez dos nomes dos campos que aparecem no documento principal. Os campos podem ser inseridos em qualquer parte do documento.

Categorias usadas

Também aqui você pode usar a categoria ASK, que significa "solicitação", e FILLIN, que significa "preencher", NEXT e NEXTIF. Você também pode configurar os parâmetros desses itens de previsão que serão exibidos na tabela concluída. Você pode encontrar o registro necessário usando um filtro especial ou simplesmente classificando os dados, como mencionamos acima.

Quando você está pronto para criar um documento mestre, a caixa de diálogo Mesclar fornece ao usuário a capacidade de identificar e corrigir erros individuais antes que eles ocorram. Quando tudo estiver marcado, você pode pressionar com segurança o botão "Mesclar". Se a mesclagem for feita via e-mail, você será solicitado a instalar o programa de email (se você não tiver instalado).

No caso de ocorrer um erro durante o processo de mesclagem, isso indica um campo incorretamente especificado no documento externo, ou os dados usados são apresentados em um formato não suportado. Portanto, esse procedimento deve ser revisado novamente.

Em geral, o Excel (trabalhando com tabelas dinâmicas em que estamos considerando) permite não só realizar análises comparativamente complexas de poder de compra, mas também criar previsões com base nelas.