Instruções

Abra um documento Excel contendo os dados que deseja resumir mesa. Clique com o ponteiro do mouse em uma das células da tabela; um pré-requisito é que algum valor seja escrito nesta célula; O mais conveniente é atribuir à coluna um nome apropriado, por exemplo, “nome”, “quantidade”, etc.

Na barra de menu na parte superior da janela, encontre o botão “Dados” (para Excel 2000, XP, 2003) ou a guia “Inserir” (para Excel 2007) e clique nele. Será aberta uma lista, na qual ative o botão “Tabela Dinâmica”. O “Assistente de criação de tabela dinâmica” será aberto e o ajudará a configurar todos os parâmetros necessários.

Selecione a fonte de dados da tabela dinâmica e o tipo de documento. A janela do assistente está dividida em duas partes. Na metade superior, indique de onde você deseja que as informações venham, como um arquivo Excel ou um banco de dados separado. Na parte inferior, o item “Tabela Dinâmica” está marcado por padrão, então você não precisa alterar nada. Clique no botão “Avançar” e prossiga para a segunda etapa do assistente.

Use o mouse para selecionar o intervalo de dados a ser processado. Por padrão, toda a tabela da planilha Excel original é selecionada, você verá isso na tela como um quadro pontilhado piscando. Se necessário, você pode selecionar apenas parte dos dados. Clique no botão “Avançar” quando terminar de selecionar as informações.

Clique no botão "Concluído" na tela de configuração da mesa final. Você pode escolher onde sua tabela dinâmica estará localizada. O padrão é Nova Planilha e esta é a melhor opção. Você também pode selecionar o item “Planilha existente”, mas neste caso pode haver dificuldades com links circulares e exibição.

Configure um resumo mesa. Após clicar no botão Concluir, você verá o layout da tabela e os campos de controle. Cada um deles é assinado para facilitar a organização dos dados e a configuração da saída final das informações.

Selecione a fonte de dados desejada na janela à esquerda e arraste-a para o local apropriado no layout. A tabela será preenchida imediatamente com valores das colunas ou linhas especificadas. Tudo depende do propósito de criar uma tabela dinâmica - é uma ferramenta muito flexível. Para obter este ou aquele relatório, você precisa selecionar as informações de interesse da lista no campo da tabela de resumo, desmarcar as fontes de dados desnecessárias e marcar as necessárias.

Conselhos úteis

Se as tabelas de dados originais estiverem localizadas em várias planilhas, é melhor movê-las para uma e criar um intervalo contínuo de valores para processamento.

Tente trabalhar primeiro com uma pequena quantidade de dados, então você será capaz de entender rapidamente a lógica de construção de tabelas dinâmicas.

Excel é um dos principais Programas do Windows. Muitos usuários sabem de sua existência, mas desconhecem os recursos que ele contém. Um deles pode ser considerado uma mesa. Quem aprendeu isso pela primeira vez pode perguntar: “Como criar uma tabela no Excel?”

Instruções

Abra o programa e escreva o nome da tabela em qualquer célula. Abaixo dela, em cada célula individual, escreva o nome das colunas. Neste caso, cada nome deve estar sob uma letra específica, localizada acima do campo de trabalho.

Em cada coluna, preencha as informações desejadas. Selecione as informações resultantes com o botão esquerdo do mouse e clique com o botão direito em qualquer uma das células. Se o nome da tabela e as designações das colunas não forem exibidos completamente, clique com o botão esquerdo na borda da célula e arraste lado direito. Da mesma forma, você pode encurtar o comprimento da célula, bastando arrastar o cursor na direção oposta.

Você já deve estar familiarizado com o conceito de tabelas dinâmicas do Excel, que são criadas a partir de uma lista. Mas e se você quiser fazer a operação oposta? Neste tutorial, você aprenderá como criar uma lista a partir de uma tabela dinâmica simples com duas variáveis.

A folha mostrada na Fig. 167.1 exibe o tipo de conversão de que estou falando. O intervalo A1:E4 contém a tabela de resumo original: 48 pontos de dados. As colunas G:I mostram a parte da tabela de 48 linhas derivada da tabela dinâmica. Em outras palavras, cada valor na tabela dinâmica original é convertido em uma linha que também contém o nome do produto e o mês correspondentes. Este tipo de lista é útil porque pode ser classificada e manipulada de outras maneiras.

O truque para criar essa lista é usar uma tabela dinâmica. Mas antes de aplicar este método, você deve adicionar o comando Assistente de Tabela Dinâmica para o painel acesso rápido. Excel 2007, Excel 2010 e Excel 2013 ainda suportam Assistentes de tabela dinâmica, mas não está disponível em fita. Para acessar o assistente, siga estas etapas:

1. Clique com o botão direito na barra de ferramentas de acesso rápido e selecione menu de contexto parágrafo Personalizando a barra de ferramentas de acesso rápido.
2. Na seção Barra de ferramentas de acesso rápido da caixa de diálogo Opções do Excel selecione Comandos na faixa de opções na lista suspensa à esquerda.
3. Role a lista e selecione .
4. Clique no botão Adicionar.
5. Clique 0K para fechar a caixa de diálogo Opções do Excel.

Após concluir essas etapas, sua barra de ferramentas de acesso rápido conterá um novo ícone.

Agora é hora de converter a tabela dinâmica em uma lista. Lembre-se de que, embora as etapas a seguir sejam específicas dos dados de amostra mostrados aqui, você pode modificá-las facilmente para trabalhar com seus dados.

1. Ative qualquer célula da Tabela Dinâmica.
2. Clique no ícone Assistente de Tabela Dinâmica e Gráfico, que você adicionou à barra de ferramentas de acesso rápido.
3. Na caixa de diálogo Assistente de Tabela Dinâmica e Gráfico defina o primeiro botão de opção para consolidação de vários intervalos e clique no botão Próximo.
4. Na etapa 2a, coloque a chave em Criar campos de página e pressione o botão Próximo.
5. Na etapa 2b no campo Faixa especifique o intervalo da tabela dinâmica (A1:E4 para a seleção de exemplo) e clique no botão Adicionar; então clique no botão Próximo para ir para a etapa 3.
6. Na etapa 3, selecione um local para a tabela dinâmica e clique em Preparar. O Excel criará uma tabela dinâmica com os dados e mostrará a área Lista de campos da tabela dinâmica.
7. Na área Lista de campos da tabela dinâmica desmarque as caixas Linha E Coluna.

Então, a tabela dinâmica permanece apenas com o campo de dados Soma em todo o campo Significado. Sobre no momento uma pequena tabela resumida mostra apenas a soma de todos os valores (Fig. 167.2).

Para finalizar, clique duas vezes na célula que contém o valor total (2851 neste exemplo). O Excel criará uma nova planilha que conterá os dados originais em forma de tabela (Fig. 167.3). Os títulos das colunas representam descrição geral (Linha, Coluna E Significado), então você provavelmente desejará alterá-los para serem mais descritivos.

Depois de algum tempo você desejará converter a Tabela Dinâmica projetada em uma Lista no Excel, agora você pode ler este tutorial para concluir a conversão.

Ferramentas de produtividade recomendadas para Excel/Office

Converter uma tabela dinâmica em uma lista

Para converter uma Tabela Dinâmica em uma Lista, você deve garantir que a Tabela Dinâmica foi configurada conforme sua necessidade. Por exemplo, eu converto a tabela dinâmica abaixo em uma lista.

1. Clique em qualquer célula da tabela dinâmica e vá para projeto aba e clique resumos > Não mostrar legendas.

2. Clique Totais gerais >Desligado Para linhas e colunas sob projeto Guia. Veja a captura de tela:

3. Clique Layout do relatório >Repita todos os rótulos dos elementos sob projeto Guia. Veja a captura de tela:

4. Clique Layout do relatório novamente e pressione Mostrar em forma tabular Veja a captura de tela:

Agora a tabela dinâmica é mostrada abaixo:

5. Clique Opções guia (ou Analisar guia) e desmarque Botões E Cabeçalhos de campo, que se refere a Mostrar grupo.

Agora a tabela dinâmica mostrada abaixo:

5. Agora vou te mostrar células vazias como zero. Clique em qualquer célula da tabela dinâmica e clique com o botão direito para selecionar Opções de tabela dinâmica e, na caixa de diálogo suspensa, em Layout e formato guia, desmarque Para células vazias E clique OK fechar Opções de tabela dinâmica Diálogo. Veja a captura de tela:

Se quiser saber mais sobre como mostrar células vazias como zero, clique em .

6. Selecione a Tabela Dinâmica e clique em Ctrl+C naquele momento para copiá-lo, coloque o cursor na célula onde deseja colar a tabela dinâmica como uma lista e clique com o botão direito para selecionar Inserção especial >Valor (V) Veja a captura de tela:

Atenção: No Excel 2007 você precisa clicar Lar > macarrão > Colar valores para inserir uma tabela dinâmica como uma lista.

Agora você pode ver a lista mostrada abaixo:

Guia Escritório

Traga guias convenientes para o Excel e muito mais programas Office, como Chrome, Firefox e o novo Internet Explorer.

Hoje falaremos sobre TABELAS. Não sobre tabelas, mas especificamente sobre TABELAS. Foi exatamente assim que a Microsoft propôs chamar aquelas tabelas maravilhosas que serão discutidas a seguir. Eles apareceram ainda na infância no Excel 2003 e eram chamados de “listas” lá. No Excel 2007, eles foram trazidos à mente e renomeados como TABLES, e o que todas as pessoas normais costumavam chamar de tabelas agora é proposto para ser chamado de RANGE. Na Rússia, essa abordagem não pegou, e por que as pessoas deveriam alterar retroativamente os termos estabelecidos, então chamaremos de TABELAS " mesas inteligentes", e vamos deixar as tabelas em seu sentido comumente usado.

Tabelas inteligentes

Deve-se admitir que o termo mesas inteligentes capta muito bem a essência do fenômeno. Na verdade, lidar com tabelas inteligentes no Excel é muito mais fácil e agradável do que com tabelas normais. No entanto, nem todo mundo sabe sobre eles. Vamos preencher essa lacuna porque as planilhas inteligentes realmente mudarão sua vida e você não vai querer voltar às planilhas normais depois disso.

Por que eles são necessários?

Aquelas pessoas que trabalham muito no Excel acabam entendendo que ao criar suas tabelas devem seguir certas regras rígidas.

1. Dê a todas as colunas nomes de colunas exclusivos.
2. Evite colunas e linhas vazias na tabela.
3. Não permita dados heterogêneos em uma coluna. Se você já decidiu que, por exemplo, a coluna E deve armazenar o volume de vendas em peças, não será necessário inserir o volume de vendas ali, digamos, em dinheiro para algumas linhas da tabela.
4. Não mescle células, a menos que seja absolutamente necessário.
5. Formate a tabela para que ela tenha a mesma aparência em todas as suas partes. Ou seja, é fácil desenhar uma grade e destacar os títulos das colunas com cores.
6. Congele áreas para que o título fique sempre visível na tela.
7. Defina o filtro padrão.
8. Insira uma linha de subtotal.
9. Use absolutos e links relativos nas fórmulas para que possam ser ampliadas sem a necessidade de alterações.
10. Ao trabalhar com uma tabela, não destaque linhas/colunas fora da tabela. Essa moda, aliás, é muito comum - selecione a linha inteira ou a coluna inteira com um clique do mouse e pinte. E não importa que haja 100 linhas na tabela e, além delas, mais 1.000.000 de linhas tenham sido pintadas. E então me pergunto inocentemente: “Por que meus arquivos pesam tanto?”

Conformidade com estes regras simples irá ajudá-lo, se não a sair mais cedo do trabalho, pelo menos a trabalhar de forma mais produtiva e significativa, dominando coisas realmente interessantes e complexas, e não lutando contra as consequências do seu descuido a cada passo.

Assim, para a 13ª versão (Excel 2007), seus desenvolvedores observaram mais de perto as ações típicas de usuários qualificados do Excel e nos deram a funcionalidade de tabelas inteligentes, pelas quais muito lhes agradecemos. Porque a maior parte do que acabei de listar nas tabelas inteligentes é feita automaticamente ou torna a configuração muito fácil.

Então, vamos nos familiarizar com como as tabelas inteligentes são criadas e o que propriedades benéficas possuir.

1. Criando uma mesa inteligente

Você pode criar uma tabela inteligente com apenas alguns cliques do mouse. Fique em qualquer cela da sua mesa,

1. Método 1 - na faixa HOME selecione Formatar como tabela, escolha o design que você gosta (você tem 60 disponíveis maneira padrão formatação)
2. Método 2 – Pressione Ctrl-T
3. Método 3 – Na faixa INSERT, selecione Tabela

2.Formatação

Se, antes de você ficar mais inteligente, sua tabela tinha formatação (molduras, letras e cores de fundo, etc.), então talvez você deva redefinir essa formatação para que ela não “conflita” com a formatação da tabela inteligente. Para fazer isso:

1. Selecione a tabela inteira - a maneira mais fácil é pressionar Ctrl-A (latim "A"!) duas vezes
2. Na faixa HOME, clique em Estilos de célula e depois no estilo Normal

Neste caso, todos os problemas de formatação são resolvidos imediatamente. No entanto, você terá que restaurar os formatos das colunas das células: formato de data, formato de hora, nuances do formato numérico (como o número de caracteres após um ponto), mas isso não é muito difícil. Em qualquer caso, cabe a você decidir se deseja redefinir a formatação desta forma, ou de alguma outra forma menos “destrutiva”, mas você precisa saber disso.

3. Visualização do estilo da tabela

Através do cardápio Formatar como tabela você pode ver como ficará sua mesa usando qualquer estilo padrão. Muito conveniente e visual!

4. Outras guloseimas e coisas úteis...

1. Alternando cor de linha ou coluna! Você sabia que antes para isso você tinha que conjurar por 10 minutos com formatação condicional com pandeiro e ossos de rato!. "E agora? Olhe ao redor - que tipo de prédios eles construíram para você, que tipo de gramados eles criaram, água encanada, TV, cozinha a gás, estufas, canteiros de flores..."
2. Ligue a linha total com um clique!
3. Filtro padrão
4. A primeira e a última coluna podem estar em negrito
5. Ao rolar a tabela, as colunas ficam visíveis SEM áreas congeladas! Por que você precisa de mais?!

5. Seleção simplificada de tabelas, colunas, linhas

6.A tabela inteligente tem um nome e pode ser alterada

Embora uma tabela inteligente apareça no Gerenciador de Nomes, ela não é completamente equivalente a um intervalo nomeado. Por exemplo, não é possível usar diretamente uma coluna de tabela inteligente como fonte de linhas para a lista suspensa da função de validação de dados. Você tem que criar um intervalo nomeado que já faça referência a uma tabela inteligente, então funciona. E neste caso, esse intervalo nomeado, se novas linhas forem adicionadas à tabela inteligente, ele se expande automaticamente, o que eu gosto muito de usar e recomendo fortemente para vocês.

7.Inserindo fatias.

No Excel 2010 existe isso recurso útil como fatias. Esses são filtros visuais que podem ser adicionados a tabelas dinâmicas, bem como a tabelas inteligentes. Vamos ver como funciona:

8.Fórmulas estruturadas.

As fórmulas estruturadas não operam com endereços de células e intervalos, mas com colunas de tabelas inteligentes, intervalos de colunas e áreas especiais de tabelas (como cabeçalhos, total de linhas, toda a área de dados da tabela). Vejamos:

Maneira "inteligente" de abordar	A que se refere?	A fórmula retorna	Gama padrão
=SOMA(Resultados)	Por padrão, uma tabela inteligente chamada "Resultados" refere-se à sua área de dados	87	B3:E7
=SOMA(Resultados[#Dados])	Esta fórmula retornará o mesmo resultado, onde a área de dados é especificada explicitamente.	87	B3:E7
=SOMA(Resultados[Vendas])	Vamos resumir a área de dados da coluna “Vendas”. Se você precisar criar um intervalo nomeado que fará referência a uma coluna de tabela inteligente, será necessário usar a sintaxe Results[Sales].	54	D3:D7
=Resultados[@Lucro]	Inserimos esta fórmula na linha 3. @ significa a linha atual e Lucro é a coluna da qual os dados são retornados.	6	E3
=SOMA(Resultados[Vendas]:Resultados[Lucro])	Link para o intervalo de colunas: da coluna "Vendas" até a coluna "Lucro", inclusive. Observe o operador ":", que cria um intervalo.	87	D3:E7
=SOMA(Resultados[@])	A fórmula foi inserida na linha 3. Ela retornou toda a linha da tabela.	11	B3:E3
=COUNT(Resultados[#Cabeçalhos])	Contando o número de elementos em #Headers.	4	B2:E2
=Resultados[[#Resultados];[Vendas]]	A fórmula retorna a linha total da coluna Vendas. Não é a mesma coisa que Resultados[Vendas], pois a função resultante pode ser diferente, por exemplo, um valor médio.	54	D8

Aviso para quem gosta de navegar em sites estrangeiros: camaradas, levem em consideração as diferenças nas configurações regionais da Rússia e dos países ocidentais (países de língua inglesa, com certeza). Nas configurações regionais existe um parâmetro como " Separador de elemento de lista". Então no Ocidente é vírgula, mas temos ponto e vírgula. Portanto, quando escrevem fórmulas no Excel, os parâmetros são separados por vírgulas, e quando escrevemos, então - ponto e vírgula. A mesma coisa se aplica aos livros traduzidos, em todos os lugares.

Problemas