Usar uma tabela dinâmica para disponibilizar os dados de tabela externa nos serviços do Excel

Observação: Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. As informações foram úteis? Dê sua opinião no final desta página. Aqui está o artigo em inglês para facilitar a referência.

Observação: Este artigo já cumpriu o seu papel, por isso o retiraremos em breve. Para evitar problemas de "Página não encontrada", removemos links que já sabemos que podem causar esse problema. Se você criou links para esta página, remova-os e juntos manteremos a Web conectada.

Se você quiser trabalhar com dados de tabela com base em uma conexão de dados externos no Serviços do Excel, você não pode usar um intervalo de dados externos (também chamado de uma tabela de consulta). Você deve criar uma tabela dinâmica que nivela multidimensionais, hierárquicas de dados em uma tabela ou duas dimensões. Veja aqui como fazer isso.

Comparando dados multidimensionais e bidimensionais

1. Converta uma tabela dinâmica multidimensional...

2.... até uma tabela dinâmica bidimensional...

3. …para que você possa nivelar os dados de tabela nos Serviços do Excel.

O que você deseja fazer?

Saber como os Serviços do Excel oferecem suporte a conexões com fontes de dados externas

Obter informações sobre as diferenças entre os relatórios de tabela dinâmica conectados aos dados externos e os intervalos de dados externo

Criar um relatório de tabela dinâmica bidimensional tabular

Crie um relatório de tabela dinâmica e conecte-o à fonte de dados externa

Adicionar, dispor e organizar os campos na área de rótulos de linha

Alterar as opções de tabela dinâmica e configurações de campo

Formatar o relatório de tabela dinâmica como uma tabela bidimensional

Personalizar o design do relatório de tabela dinâmica

Publicar a pasta de trabalho nos Serviços do Excel

Saber como os Serviços do Excel oferecem suporte a conexões com fontes de dados externas

Tabelas dinâmicas foram projetadas para agregar muitas dados numéricos em somas e totais e para trabalhar com dados multidimensionais que são organizados em uma hierarquia. Por outro lado, intervalos de dados externos são bidimensionais tabelas estruturadas como linhas e colunas, que exibem registros nonaggregated dos dados de origem.

Quando você usa o Assistente de Conexão de dados ou o Microsoft Query para se conectar aos dados externos, você normalmente crie um intervalo de dados externos. A única exceção é quando você cria uma tabela dinâmica que está conectada aos dados externos. Uma tabela dinâmica não criar um intervalo de dados externos. Essa diferença da maneira que as conexões são criadas é importante entender quando você publica uma pasta de trabalho nos serviços do Excel, pois os serviços do Excel apenas dá suporte a conexões de dados externos com base em tabelas dinâmicas e não suporta intervalos de dados externos.

Início da página

Saiba mais sobre as diferenças entre as tabelas dinâmicas que estão conectadas aos dados externos e intervalos de dados externos

Quando você trabalha com uma tabela dinâmica que está conectada aos dados externos, existem diferenças entre o comportamento de uma tabela dinâmica e um intervalo de dados externos que você deve estar ciente, incluindo o seguinte:

Dados de grupo e subgrupo de tabela dinâmica em uma ordem da esquerda para a direita

Em geral, dados agrupados baseia-se em uma classificação de dados por uma ou mais colunas. Por exemplo, se você quiser ver todos os funcionários agrupados por departamentos, você pode executar um primário tipo de dados por departamentos e uma classificação secundária por funcionários. Você também pode aninhar grupos, como linha de produtos, categoria, produto, ou geografia, país/região, estado/província e cidade. Por padrão, as tabelas dinâmicas são classificadas automaticamente em grupos e subgrupos em uma ordem da esquerda para a direita para tornar mais fácil ver dados relacionados, para exibir informações de cabeçalho e de resumo e para expandir ou recolher itens relacionados.

Dados agrupados por Linha de produtoe subgrouped dentro de cada Linha de produtos por categoria

Linha de Produto

Categoria

Produto

Motores

Avião

Montagem de asa

Montagem de cauda

Regulador de Tensão

Lavagem

269 HP

454 HP

Motores a Pistão

Passageiro

Superaquecimento

Padrão

SUV

Pequeno

Ao colocar uma coluna com valores exclusivos na área Rótulos de Linha à esquerda, você mesclará automaticamente os dados.

Os mesmos dados de Linha de produto nivelados adicionando a coluna ID-Num

ID

Linha de Produto

Categoria

Produto

WM-345

Motores

Avião

Montagem de asa

TM-231

Motores

Avião

Montagem de cauda

BSTR-567

Motores

Avião

Regulador de Tensão

6C-734

Motores

Lavagem

269 HP

8C-121

Motores

Lavagem

454 HP

MF-202

Motores a Pistão

Passageiro

Superaquecimento

MF-321

Motores a Pistão

Passageiro

Padrão

MF-211

Motores a Pistão

SUV

Pequeno

Se você não quiser exibir a coluna, depois de adicionar a coluna da tabela dinâmica, você pode ocultar a coluna. (Selecione a coluna e, em seguida, na guia página inicial, no grupo células, clique na seta ao lado de formato, aponte para Ocultar e reexibir e clique em Ocultar colunas.)

Importante: Se uma tabela dinâmica detectar duas ou mais linhas duplicadas de dados da fonte de dados, tabela dinâmica exibe apenas uma linha. Se desejar que todas as linhas, até mesmo duplicar linhas, exibidas em uma tabela dinâmica, é necessário adicionar uma coluna que contenha valores exclusivos para a tabela, consulta ou modo de exibição que você importa da fonte de dados. Se a fonte de dados não tiver uma coluna exclusiva, você pode adicionar uma de cada fonte de dados. Por exemplo, você pode adicionar uma coluna que tem o tipo de dados de AutoNum a uma tabela do Microsoft Office Access, ou uma coluna que tem o tipo de dados de identidade a uma tabela do Microsoft SQL Server ou uma coluna calculada que exibe um número exclusivo para cada registro a uma consulta de banco de dados ou xibir w.

Editando dados de célula e adicionando colunas calculadas

Antes de publicar a pasta de trabalho nos Serviços do Excel, é possível editar os dados em um intervalo de dados externo, mas os dados da tabela dinâmica serão sempre somente leitura.

Se você clicar duas vezes em uma célula em um intervalo de dados externo, entrará no modo de edição e poderá alterar o valor ou até mesmo a fórmula. No entanto, observe que você não está alterando o valor original na fonte de dados e, na próxima vez que atualizar os dados, o novo valor poderá ser substituído, dependendo da configuração da propriedade do intervalo de dados externo Se o número de linhas no intervalo de dados for alterado após atualização.

Se você clicar duas vezes uma célula de rótulo de linha em uma tabela dinâmica, a célula expande ou recolhe os itens em que rótulo de linha. Quando você Nivelar uma tabela dinâmica em uma tabela bidimensional, o nivelamento tem o efeito de tornar todos os valores de linha em um grupo para a esquerda da célula desaparecem ou repetir.

Você pode adicionar colunas calculadas em qualquer lugar em uma tabela do Microsoft Office Excel com base em um intervalo de dados externos, ou você pode inserir colunas e preencher fórmulas em um intervalo de dados externos. Você não pode inserir colunas calculadas dentro de uma tabela dinâmica (embora você possa inserir campos calculados). No entanto, você pode adicionar uma coluna que preenche dados ou fórmulas para a imediatamente à esquerda ou direita da tabela dinâmica e dentro de uma fórmula de busca preenchida, você pode usar referências de célula dentro da tabela dinâmica.

Criando gráficos

Se você quiser criar um gráfico com base nos dados da tabela dinâmica, você pode criar um gráfico dinâmico, que se comporta de maneira semelhante aos gráficos padrão, mas há alguns diferenças, incluindo o seguinte:

Interação     Com gráficos padrão, você cria um gráfico para cada modo de exibição de dados que você deseja ver, mas os modos de exibição não são interativos. Com gráficos dinâmicos, você pode criar um único gráfico e interativamente exibir os dados de maneiras diferentes, alterando o layout do relatório ou os detalhes que é exibido. Serviços do Excel, tanto um gráfico padrão e gráfico dinâmico podem ser atualizados com base na interação do usuário com a pasta de trabalho, mas os dois gráficos são exibidos como imagens estáticas.

Tipos de gráfico     O tipo de gráfico padrão para um gráfico padrão é um gráfico de colunas agrupadas, que compara valores entre categorias. O tipo de gráfico padrão para um gráfico dinâmico é um gráfico de colunas empilhadas, que compara a contribuição de cada valor para um total entre categorias. Você pode alterar um gráfico dinâmico para qualquer tipo de gráfico, exceto xy (dispersão), ações ou de bolhas.

Elementos do gráfico     Gráficos dinâmicos contêm os mesmos elementos gráficos padrão, mas eles também contêm campos e itens que podem ser adicionados a, girados ou removidos, para exibir diferentes modos de exibição dos seus dados. Categorias, séries e dados em gráficos padrão são campos de categoria, campos de série e campos de valor em gráficos dinâmicos. Gráficos dinâmicos também podem conter filtros de relatório. Cada um desses campos contém itens, que em gráficos padrão são exibidos como categoria rótulos ou nomes de séries em legendas.

Formatação     Maior parte da formatação, incluindo elementos, layout e estilo, é preservado quando você atualizar um gráfico dinâmico. No entanto, as linhas de tendência, rótulos de dados, barras de erros e outras alterações a série de dados não são preservadas. Gráficos padrão não perderá essa formatação aplicada uma vez.

Movendo ou redimensionando itens     Em um gráfico dinâmico, você não pode mover ou redimensionar a área de plotagem, legenda, títulos de gráfico ou títulos de eixo, embora você pode selecionar uma das diversas posições predefinidas para a legenda, e você pode alterar o tamanho da fonte de títulos. Em um gráfico padrão, você pode mover e redimensionar todos esses elementos.

Depois de criar um gráfico dinâmico, se você preferir, você pode convertê-la em um gráfico padrão. Para obter mais informações, consulte o artigo criar uma tabela dinâmica anaylze para dados de planilha, ou criar um gráfico dinâmico.

Comparando as propriedades do intervalo de dados externo e as opções de tabela dinâmica

Intervalos de dados externos e tabelas dinâmicas têm propriedades que você pode usar para controlar os formatação de dados e layout e o comportamento de atualização de dados.

Intervalos de dados externos e tabelas dinâmicas permitem ajustar largura da coluna, preservar a coluna de classificação, filtragem, formatação de célula e o layout dos dados se os dados externos for atualizados.

Em um intervalo de dados externos, você também tem a opção de incluir números de linha como a primeira coluna de dados. Em uma tabela dinâmica, não há nenhuma opção semelhante. Consulte a seção dados de grupo e subgrupo de tabela dinâmica em uma ordem da esquerda para a direita, para uma discussão sobre como adicionar uma coluna exclusiva na fonte de dados.

Quando você atualiza um intervalo de dados externo, pode controlar o que acontece com os dados existentes, caso o número de linhas do intervalo de dados seja alterado. Para isso, defina uma das opções a seguir:

  • Insira células para novos dados, exclua células não utilizadas (o padrão)

  • Insira linhas inteiras para novos dados, limpe as células não utilizadas

  • Sobrescreva células existentes com novos dados, limpe as não utilizadas

Quando você atualiza uma tabela dinâmica, basta traz novos dados. No entanto, você pode ser solicitado e solicitado a decidir se deseja substituir as células existentes abaixo do relatório atual.

Filtrando dados

Filtrar dados em um dados externos intervalo e tabela dinâmica são muito semelhantes, mas há algumas diferenças, incluindo o seguinte:

  • Intervalos de dados externos usam o Filtro de texto, Filtro de número e comandos de Filtro de data, enquanto as tabelas dinâmicas usam os comandos de Filtro de rótulo, Filtro de valor e Filtro de data.

  • O menu de filtro de um intervalo de dados externos sempre remove itens que não são mais válidos, e o menu do filtro nunca inclui novos itens com um filtro aplicado após uma operação de atualização, mas uma tabela dinâmica tem opções para controlar esse comportamento. Para obter mais informações, consulte a seção Opções de tabela dinâmica de alteração e configurações de campo.

  • Uma tabela dinâmica tem um relatório recurso de filtro (não disponível em um intervalo de dados externos) que permite aos usuários trabalhar com um subconjunto de dados da tabela dinâmica.

Classificando dados

Em um intervalo de dados externos, você pode executar uma classificação de várias colunas, até 64 colunas, você pode escolher qualquer coluna como de classificação principal e você pode escolher qualquer outras colunas como classifica secundária. No entanto, os processos de classificar dados em uma tabela dinâmica e classificar um intervalo de dados externos são diferentes. Ao contrário de um intervalo de dados externos, em uma tabela dinâmica que você pode executar apenas uma classificação primária na coluna mais à esquerda. Todas as outras colunas executam classificações secundárias com base na coluna imediatamente à esquerda. Em resumo, você só pode executar uma classificação primária e secundária em uma ordem da esquerda para a direita.

Além disso, quando você insere uma coluna com valores exclusivos como primeira coluna à esquerda, pode realizar a classificação somente por essa coluna, pois, na verdade, está criando um grupo principal para cada linha. Dependendo das necessidades dos usuários, talvez seja necessário classificar previamente os dados no Excel e, após publicar a pasta de trabalho nos Serviços do Excel, desativar a classificação desmarcando a caixa de seleção Classificação no painel de ferramentas Web Part do Microsoft Office Excel Web Access.

Usando hiperlinks

Diferente dos intervalos de dados externos, não há suporte para hiperlinks ativos nas células da tabela dinâmica. O hiperlink é tratado como texto, mas você não pode seguir o hiperlink até uma página da Web ou documento, no Excel ou nos Serviços do Excel.

Início da página

Tornar uma tabela dinâmica bidimensional, tabular

Há várias etapas que são necessárias para nivelar dados multidimensionais em uma tabela dinâmica bidimensional, tabular. Para obter melhores resultados, siga os procedimentos a seguir em sequência.

Início da página

Criar a tabela dinâmica e conectá-lo à fonte de dados externa

  1. Selecione uma célula em branco.

  2. Na guia Inserir, no grupo Tabelas, clique em Tabela Dinâmica e, em seguida, clique em Tabela Dinâmica novamente.

    Imagem da Faixa de Opções do Excel

    A caixa de diálogo Criar Tabela Dinâmica é exibida.

  3. Clique em Usar uma fonte de dados externa.

  4. Clique em Escolher Conexão.

    O Caixa de diálogo Conexões existentes é exibida.

  5. Na lista Mostrar na parte superior da caixa de diálogo, selecione a categoria de conexões para a qual deseja escolher uma conexão ou selecione Todas as Conexões Existentes (que é o padrão).

  6. Selecione uma conexão na lista Selecione uma Conexão e clique em Abrir.

  7. Digite um local e siga um destes procedimentos:

    • Para colocar a tabela dinâmica em uma nova planilha, começando na célula A1, clique em Nova planilha.

    • Para colocar a tabela dinâmica em uma planilha existente, selecione Planilha existente e, em seguida, digite a primeira célula do intervalo de células onde você deseja colocar a tabela dinâmica.

      Como alternativa, clique em Recolher caixa de diálogo Imagem do botão para ocultar temporariamente a caixa de diálogo, selecione a célula começo da planilha e clique em Expandir caixa de diálogo Imagem do botão .

  8. Clique em OK.

Uma tabela dinâmica vazia, com a lista de campos de tabela dinâmica exibida, é adicionada para o local que você inseriu.

Início da página

Adicionar, criar o layout e organizar os campos na área Rótulos de Linha

Use a Lista de Campos da Tabela Dinâmica para adicionar, criar o layout e organizar os campos. Além disso, use-a para verificar se todos os campos foram adicionados à área Rótulos de Linha.

  1. Clique na Tabela Dinâmica.

  2. Siga um ou mais dos procedimentos abaixo:

    • Para adicionar campos ao relatório, siga um ou mais deste procedimentos:

      • Marque a caixa de seleção ao lado de cada nome de campo na seção de campo da Lista de Campos. Cada campo é colocado em uma área padrão da seção de layout, mas você pode reorganizar os campos.

        Por padrão, os campos não-numéricos são adicionados à área Rótulos de Linha, os campos numéricos são adicionados à área Valores e os campos de hora/data são adicionados à área Rótulos de Coluna.

      • Para mover um campo para a área Rótulos de Linha, clique com o botão direito do mouse no nome do campo e selecione Adicionar aos Rótulos de Linha no menu de atalho.

        Dica: Você também pode clicar em um nome de campo e, sem soltar o botão do mouse, arrastar o campo da seção de campo para uma área na seção de layout.

    • Para reorganizar os campos, clique no nome de campo em uma das áreas e selecione um dos seguintes comandos:

Mover para Cima   

Move o campo uma posição acima na área.

Mover para Baixo   

Move o campo uma posição para baixo na área.

Mover para o Início   

Move o campo para o início da área.

Mover para o Fim   

Move o campo para o fim da área.

Mover para Rótulos de Linha   

Move o campo para a área Rótulos de Linha.

  • Para remover um campo, clique no campo nome em um das áreas de layout e clique em Remover campo ou desmarque a caixa de seleção ao lado de cada nome de campo que você deseja remover, na seção de campos.

    Dica: Você também pode clicar em um nome de campo na seção de layout e, sem soltar o botão do mouse, arrastar o nome de campo para fora da Lista de Campos da Tabela Dinâmica.

    Observação: Ao desmarcar uma caixa de seleção na seção de campo, você removerá todas as instâncias do campo no relatório.

Início da página

Alterar as opções de tabela dinâmica e configurações de campo

Para garantir que uma tabela dinâmica se comporta de maneira semelhante a um intervalo de externo, faça o seguinte:

Altere as opções de tabela dinâmica   

  1. Clique na Tabela Dinâmica.

  2. Na guia Opções, no grupo Tabela Dinâmica, clique em Opções.

    A caixa de diálogo Opções de Tabela Dinâmica é exibida.

  3. Para fazer o ajuste automático de uma largura de coluna após uma operação de atualização, clique na guia Layout e Formato e, na seção Exibir, marque a caixa de seleção Ajustar automaticamente a largura das colunas ao atualizar.

  4. Para manter a formatação cada vez que os dados são atualizados, clique na guia Layout e Formato e, na seção Exibir, marque a caixa de seleção Preservar a formatação da célula ao atualizar.

  5. Para verificar se o menu Filtrar remove os itens que não são mais válidos, após uma operação de atualização, clique na guia Dados e, na caixa Número de itens retidos por campo, selecione Nenhum.

Altere as configurações de campo   

  1. Clique na Tabela Dinâmica.

  2. Para incluir novos itens em uma tabela dinâmica com um filtro aplicado, para cada campo, uma célula de atalho, clique em Configurações do campo no menu de atalho, clique na guia subtotais & filtros e em seção filtros, selecione o Incluir novos itens no filtro manual caixa de seleção.

Início da página

Layout de tabela dinâmica como uma tabela bidimensional

Para formatar a tabela dinâmica como uma tabela bidimensional, faça o seguinte:

  1. Clique na Tabela Dinâmica.

  2. Na guia Design, no grupo Layout, clique em Subtotais e, em seguida, clique em Não Mostrar Subtotais.

  3. Na guia Design, no grupo Layout, clique em Totais Gerais e, em seguida, clique em Desativado para Linhas e Colunas.

  4. Na guia Design, no grupo Layout, clique em Layout do Relatório e, em seguida, clique em Mostrar em Formato de Tabela.

  5. Na guia Opções, no grupo Mostrar/Ocultar, clique em Cabeçalhos de Campos.

Início da página

Personalizar o design da tabela dinâmica

As opções de estilo e estilos de tabela dinâmica internos foram projetadas para dados multidimensionais, não uma tabela bidimensional. Quando você nivelar o relatório em uma tabela bidimensional, esses estilos Não exibir bordas de célula e as caixas de seleção de Colunas em Tiras no grupo Opções de estilo de tabela dinâmica da guia Design e Linhas em Tiras não afetam o formato das células. No entanto, você pode personalizar um estilo de tabela dinâmica para que cada célula tem uma borda de célula e a tabela dinâmica usa formatação condicional para exibir linhas ou colunas listadas.

Personalize o estilo de tabela dinâmica   

  1. Clique na Tabela Dinâmica.

  2. Na guia Design, no grupo Estilos de Tabela Dinâmica, selecione um estilo desejado e faça o seguinte:

    1. Clique com o botão direito do mouse em um estilo visível, percorra a galeria ou, para ver todos os estilos disponíveis, clique em Mais na parte inferior da barra de rolagem.

    2. Clique em Duplicar.

      A caixa de diálogo Modificar Estilo Rápido de Tabela Dinâmica é exibida.

    3. Se desejar, digite um novo nome na caixa Nome.

    4. Na caixa Elemento de tabela, selecione Tabela Inteira e clique em Formatar.

      A caixa de diálogo Formatar Células é exibida.

    5. Clique na guia Borda e crie uma borda de célula.

    6. Se desejar, clique nas guias Preenchimento e Fonte, e faça outras alterações.

    7. Clique duas vezes em OK.

    8. Clique no botão Mais na parte inferior da barra de rolagem e, na categoria Personalizado, clique no estilo de tabela dinâmica que você acabou de criar.

Exiba as linhas ou colunas em tiras usando a formatação condicional   

  1. Selecione todas as colunas da tabela dinâmica.

  2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação Condicional e clique em Gerenciar Regras.

    Imagem da Faixa de Opções do Excel

    A caixa de diálogo Gerenciador de Regras de Formatação Condicional é exibida.

  3. Para adicionar uma formato condicional, clique em Nova Regra.

    A caixa de diálogo Nova Regra de Formatação será exibida.

  4. Em Selecione um Tipo de Regra, clique em Usar uma fórmula para determinar quais células devem ser formatadas.

    1. Em Edite a Descrição da Regra, na caixa Formatar valores em que esta fórmula é verdadeira, digite uma fórmula.

    2. Siga um destes procedimentos:

      • Para aplicar tiras às linhas, digite a seguinte fórmula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

  • Para aplicar tiras às colunas, digite a seguinte fórmula:

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  • Para aplicar tiras às linhas e colunas, digite a seguinte fórmula:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  1. Clique em Formato para exibir a caixa de diálogo Formatar Células.

  2. Selecione o formato de número, fonte, borda ou preenchimento que deseja aplicar quando o valor da célula satisfizer à condição e, em seguida, clique em OK.

    Você pode escolher mais de um formato. Os formatos que você selecionar serão exibidos na caixa Visualização.

Início da Página

Publicar a pasta de trabalho nos Serviços do Excel

  1. Clique o Botão Microsoft Office Imagem do botão Office , clique na seta ao lado de publicação e clique em Serviços do Excel em distribuir o documento para outras pessoas.

  2. Na caixa Nome do arquivo, digite o caminho que levará ao servidor e aceite o nome sugerido para a pasta de trabalho ou digite um novo nome, se necessário.

    Em um computador que esteja executando o Windows Vista    

    • Na Barra de endereço, selecione um local na rede que possa ser acessado pelos usuários desejados e clique em Salvar.

      Em um computador que esteja executando o Microsoft Windows XP    

    • Na caixa Salvar em, selecione um local na rede que possa ser acessado pelos usuários desejados e clique em Salvar.

      Por exemplo, digite onome do arquivo dosite/deservidor/http://

      Observação: O Excel pode publicar uma pasta de trabalho no servidor somente no formato de arquivo XML (.xlsx) do Microsoft Office Excel 2007 ou no formato de arquivo binário (.xlsb) do Office Excel 2007.

  3. Clique em OK.

  4. Para verificar se as áreas visualizáveis da pasta de trabalho estão exibidas corretamente no navegador, marque a caixa de seleção Abrir esta pasta de trabalho no meu navegador após salvá-la.

  5. Clique em Salvar.

Início da página

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×