Gerenciar intervalos de dados externos e suas propriedades

Você pode alterar as propriedades de um intervalo de dados externos para controlar dados que foram importados em uma planilha por meio de uma conexão com uma fonte de dados externa, como um banco de dados, uma consulta à Web ou um arquivo de texto.

Um intervalo de dados externos (também chamado de tabela de consulta) é um nome definido ou um nome de tabela que define a localização dos dados que são colocados em uma planilha. Quando você se conecta a dados externos, o Excel cria automaticamente um intervalo de dados externos. A única exceção é um relatório de tabela dinâmica que está conectado a uma fonte de dados — um relatório de tabela dinâmica não usa um intervalo de dados externos. No Excel, você pode formatar e dispor um intervalo de dados externos ou usá-lo em cálculos, como em qualquer outro dado.

Propriedades do intervalo de dados externos

Um intervalo de dados externos tem propriedades adicionais (não deve ser confundidas com as propriedades de conexão) que você pode usar para controlar os dados, como a preservação da formatação da célula e da largura da coluna. Você pode alterar essas propriedades de intervalo de dados externos clicando em Propriedades no grupo conexões , na guia dados . Em seguida, faça as alterações fazendo o seguinte:

  • Para intervalos de dados externos criados do Microsoft Query e do assistente para conexão de dados, use a caixa de diálogo Propriedades de dados externos .

  • Para intervalos de dados externos que são criados a partir de um arquivo de texto importado ou de uma consulta à Web que recupera dados HTML, use a caixa de diálogo Propriedades do intervalo de dados externos .

  • Para intervalos de dados externos que são criados a partir de uma consulta da Web que recupera dados XML, use a caixa de diálogo Propriedades do mapa XML .

Usando modelos com intervalos de dados externos

Se você quiser compartilhar um resumo ou um relatório baseado em dados externos, poderá dar a outros usuários uma pasta de trabalho que contenha um intervalo de dados externos ou criar um modelo de relatório. Um modelo de relatório permite que você salve o resumo ou relatório sem salvar os dados externos para que o arquivo seja menor. Os dados externos são recuperados quando um usuário abre o modelo de relatório.

  1. Selecione a planilha na qual você deseja pesquisar uma intervalo de dados externos.

  2. Na barra de fórmulas, clique na seta ao lado da caixa de nomee, em seguida, clique no nome do intervalo de dados externos desejado.

  1. Na guia Fórmulas, no grupo Nomes Definidos, clique em Gerenciador de Nomes.

    Imagem da Faixa de Opções do Excel

  2. Na caixa de diálogo Gerenciador de nomes , clique no nome do intervalo de dados externos e, em seguida, clique em Editar. Você também pode clicar duas vezes no nome.

  3. Na caixa de diálogo Editar nome , digite o novo nome para a referência na caixa nome .

Observação: O botão fechar fecha apenas a caixa de diálogo Gerenciador de nomes . Você não precisa clicar em fechar para confirmar as alterações que já fez.

Você pode alterar a consulta subjacente para um intervalo de dados externos criado a partir do Microsoft Query, um arquivo de texto importado, uma consulta à Web ou o assistente para conexão de dados.

  1. Clique em uma célula no intervalo de dados externos cuja consulta subjacente você deseja alterar.

  2. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  3. Na caixa de diálogo Propriedades de dados externos , clique em propriedades de conexão Imagem do botão .

  4. Na caixa de diálogo Propriedades de conexão , clique na guia definição e, em seguida, clique em Editar consulta.

Para obter mais informações sobre como criar e editar consultas no Microsoft Query, consulte ajuda do Microsoft Query.

  1. Clique em uma célula no intervalo de dados externos cuja consulta subjacente você deseja alterar.

  2. Na guia Dados , no grupo Conexões , clique em Conexões.

    Imagem da Faixa de Opções do Excel

  3. Na caixa de diálogo conexões da pasta de trabalho , clique em Propriedades.

  4. Na caixa de diálogo Propriedades de conexão , clique na guia definição e, em seguida, clique em Editar consulta.

  5. Na caixa de diálogo Importar arquivo de texto , clique em importar.

  6. Faça alterações no arquivo de texto importado no assistente de importação de texto e clique em concluir.

    Para obter mais informações sobre como importar arquivos de texto, consulte importar ou exportar arquivos de texto.

  1. Clique em uma célula no intervalo de dados externos cuja consulta subjacente você deseja alterar.

  2. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  3. Na caixa de diálogo conexões da pasta de trabalho , clique em Propriedades.

  4. Na caixa de diálogo Propriedades de conexão , clique na guia definição e, em seguida, clique em Editar consulta.

  5. Faça alterações na consulta à Web na caixa de diálogo Editar consulta à Web e, em seguida, clique em concluir.

Para obter mais informações sobre como criar e editar consultas à Web, consulte conectar-se a uma página da Web.

  1. Clique em uma célula no intervalo de dados externos cuja consulta subjacente você deseja alterar.

  2. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  3. Na caixa de diálogo Propriedades de dados externos , clique em propriedades de conexão Imagem do botão .

  4. Na caixa de diálogo Propriedades de conexão , clique na guia definição .

  5. Siga um destes procedimentos:

    • Na caixa tipo de comando , clique em tabela e, em seguida, na caixa texto do comando , altere o valor para o nome de uma tabela, modo de exibição ou consulta apropriada.

    • Na caixa tipo de comando , clique em SQL ou em padrão e, em seguida, na caixa texto do comando , edite a instrução SQL.

      Observação: Dependendo de como a conexão foi definida, a caixa tipo de comando pode estar indisponível (ela aparece esmaecida).

  1. Na barra de fórmulas, clique na seta ao lado da caixa nomee selecione o nome do intervalo de dados externo que você deseja copiar.

    Para uma tabela do Excel, selecione o nome do intervalo e pressione CTRL + A para selecionar os cabeçalhos da tabela.

    Se você quiser incluir rótulos de coluna ou fórmulas que não fazem parte do intervalo de dados externos, selecione as células que contêm os rótulos de coluna ou as fórmulas que você deseja copiar. Clique na seta ao lado da caixa de nome na barra de fórmulas e clique no nome do intervalo de dados externos que você deseja copiar.

  2. Na guia Página Inicial, no grupo Área de transferência, clique em Copiar.

    Imagem da Faixa de Opções do Excel

  3. Alterne para a pasta de trabalho na qual você deseja colar o intervalo de dados externos.

  4. Clique na célula superior esquerda do área de colar.

    Para garantir que o dados externos não substitua os dados existentes, certifique-se de que a planilha não tenha dados abaixo ou à direita da célula em que você clicou.

  5. Na guia Página Inicial, no grupo Área de Transferência, clique em Colar.

Observação: Se você copiar apenas parte de um intervalo de dados externos, o consulta subjacente não será copiado, e os dados copiados não poderão ser atualizados.

Você pode controlar como manipular um conjunto de dados menor ou maior que é retornado para o Excel quando os dados são atualizados.

  1. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  2. Na caixa de diálogo Propriedades do intervalo de dados externos , em se o número de linhas no intervalo de dados for alterado após a atualização, clique em uma das seguintes opções:

    • Inserir células para novos dados, excluir células não utilizadas

      Observações: 

      • Quando uma ou mais linhas são adicionadas à fonte de dados, as células diretamente abaixo do intervalo de dados externos são movidas para baixo, mas as células à direita do intervalo de dados externos não se movem.

      • Quando uma ou mais linhas são excluídas da fonte de dados, as células diretamente sob o intervalo de dados externos se movem para cima, mas as células à direita do intervalo de dados externos não se movem.

    • Inserir linhas inteiras para novos dados, limpar células não utilizadas

      Observações: 

      • Quando uma ou mais linhas são adicionadas à fonte de dados, as células diretamente abaixo e à direita do intervalo de dados externos são movidas para baixo.

      • Quando uma ou mais linhas são excluídas na fonte de dados, as células diretamente abaixo e à direita do intervalo de dados externos não se movem.

    • Substituir células existentes por novos dados, limpar células não utilizadas

      Observações: 

      • Quando uma ou mais linhas são adicionadas à fonte de dados, as células diretamente abaixo do intervalo de dados externos são sobrescritas, mas as células à direita do intervalo de dados externos não se movem.

      • Quando uma ou mais linhas são excluídas na fonte de dados, as células diretamente abaixo e à direita do intervalo de dados externos não se movem.

  1. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  2. Em formatação e layout de dados, siga um ou mais destes procedimentos:

    • Para incluir nomes de campo como a primeira linha, marque a caixa de seleção incluir nomes de campo .

      Observação: Essa caixa de seleção só está disponível para um intervalo de dados externos convertido de um programa do Microsoft Office 2003 ou para um intervalo de dados externos criado de forma programática.

    • Para adicionar uma coluna de números de linha, marque a caixa de seleção incluir números de linha .

      Observação: Essa caixa de seleção não está disponível para um arquivo de texto, arquivo XML ou consulta à Web importado.

    • Para preservar a formatação da célula aplicada, marque a caixa de seleção Preservar formatação da célula .

      Observação: Para uma consulta à Web, essa opção é desmarcada automaticamente quando você seleciona formatação HTML completa na caixa de diálogo Opções . Acesse a caixa de diálogo Opções na caixa de diálogo Editar consulta .

    • Para preservar as larguras de coluna que você definiu, marque a caixa de seleção ajustar largura da coluna .

    • Para preservar a classificação, filtragem e layout de coluna que você aplicar, marque a caixa de seleção Preservar classificação/filtro/layout de coluna .

      Observação: Esta caixa de seleção não está disponível para uma consulta à Web.

    • Para preservar a filtragem de coluna que você aplica, marque a caixa de seleção preservar filtro de coluna .

      Observação: Essa caixa de seleção está disponível apenas para uma consulta à Web baseada em dados XML.

Observação: O procedimento a seguir aplica-se apenas a um intervalo de dados externos convertido a partir de um programa do Microsoft Office 2003 ou a um intervalo de dados externo criado de forma programática. Todos os intervalos de dados externos criados usando a interface do usuário são criados como tabelas do Excel. Essas tabelas são automaticamente expandidas quando novas linhas são adicionadas e preenche as colunas calculadas.

  1. Insira uma fórmula em uma célula que seja adjacente à primeira linha de dados no intervalo de dados externos.

    Observação: A primeira linha de dados pode ser a primeira ou a segunda linha no intervalo de dados externos, dependendo se a primeira linha contém cabeçalhos.

  2. Selecione a célula e clique duas vezes na alça de preenchimento para copiar a fórmula para todas as linhas no intervalo de dados externos.

  3. Clique em uma célula no intervalo de dados externos.

  4. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  5. Na caixa de diálogo Propriedades do intervalo de dados externos , marque a caixa de seleção preencher fórmulas em colunas adjacentes aos dados .

    Observação: Se o intervalo de dados externos se expandir quando você atualizar, o Excel copiará somente as fórmulas imediatamente adjacentes ou dentro do intervalo de dados externos.

Congelar um intervalo de dados externos mantém os dados, mas não sua consulta subjacente, portanto, um intervalo de dados externos congelado não pode ser atualizado.

  1. Clique na planilha que contém a tabela do Excel da qual você deseja remover a conexão de dados.

  2. Na barra de fórmulas, clique na seta ao lado da caixa de nomee, em seguida, clique no nome do intervalo de dados externo do qual você deseja remover a conexão de dados. O intervalo de dados é selecionado.

  3. Na guia ferramentas , no grupo dados da tabela externa , clique em desvincular. O intervalo de dados permanece e ainda tem o mesmo nome, mas a conexão é excluída.

Observação: O procedimento a seguir aplica-se apenas a um intervalo de dados externos convertido a partir de um programa do Microsoft Office 2003 ou a um intervalo de dados externo criado de forma programática. Todos os intervalos de dados externos criados usando a interface do usuário são criados como tabelas do Excel. Essas tabelas são automaticamente expandidas quando novas linhas são adicionadas e preenche as colunas calculadas.

  1. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  2. Em definição de consulta, desmarque a caixa de seleção salvar definição de consulta .

Observação:  Quando você salva sua pasta de trabalho, a consulta subjacente é excluída da pasta de trabalho. No entanto, os arquivos de consulta do banco de dados salvo (arquivos. dqy ou. odc) não são excluídos. Se você salvou a consulta quando a criou no assistente de consulta ou no Microsoft Query, o arquivo de consulta é salvo no computador e você pode usar a consulta novamente para recuperar dados externos. Para obter mais informações, consulte ajuda do Microsoft Query.

Observação: O procedimento a seguir aplica-se apenas a um intervalo de dados externos que é criado usando o assistente para conexão de dados ou o Microsoft Query (e não para um arquivo de texto ou consulta à Web importado), um intervalo de dados externos convertido de um programa do Microsoft Office 2003 ou um arquivo de texto externo intervalo de dados criado de forma programática. Todos os intervalos de dados externos criados usando a interface do usuário são criados como tabelas do Excel.

  1. Insira uma linha em branco acima do intervalo de dados externos seguindo um destes procedimentos:

    • Selecione uma célula acima da qual você deseja inserir a nova linha. Em seguida, na guia página inicial , no grupo células , clique na seta ao lado de Inserire, em seguida, clique em inserir linhas de planilha. Como você selecionou uma célula, uma nova linha é inserida acima da linha atual.

    • Clique no seletor de linha da linha acima da qual você deseja inserir uma nova linha. Em seguida, na guia página inicial , no grupo células , clique em Inserir. Como você selecionou uma linha inteira, uma nova linha é inserida acima da linha selecionada.

  2. Digite os rótulos que você deseja nas células na linha em branco.

  3. Clique em uma célula no intervalo de dados externos.

  4. Na guia dados , no grupo conexões , clique em Propriedades.

    Imagem da Faixa de Opções do Excel

  5. Na caixa de diálogo Propriedades do intervalo de dados externos , em formatação e layout de dados, desmarque a caixa de seleção incluir nomes de campo e clique em OK.

  6. Para remover os nomes de campo existentes e atualizar o intervalo de dados externos, clique em uma célula no intervalo de dados externos e, em seguida, clique em atualizar dados Imagem do botão .

Observação: Quando você recupera dados de um banco de dados, todas as alterações feitas em nomes de coluna no Microsoft Query são mantidas no intervalo de dados externos. Para obter informações sobre como alterar nomes de coluna na consulta, consulte ajuda do Microsoft Query.

  1. Clique na planilha que contém o intervalo de dados externos que você deseja excluir.

  2. Na barra de fórmulas, clique na seta ao lado da caixa de nomee, em seguida, clique no nome do intervalo de dados externos que você deseja excluir.

    Se o intervalo de dados externos for uma tabela do Excel, pressione CTRL + A para selecionar a tabela inteira.

  3. Para excluir o intervalo de dados externos, pressione DELETE.

  4. Para excluir a consulta subjacente, clique em Sim quando o Excel solicitar.

Precisa de mais ajuda?

Você pode sempre consultar um especialista na Excel Tech Community, obter suporte na Comunidade de respostas ou sugerir um novo recurso ou melhoria no User Voice do Excel.

Observação:  Esta página foi traduzida automaticamente e pode apresentar erros gramaticais ou imprecisões. Nosso objetivo é que este conteúdo seja útil para você. Você pode nos dizer se as informações foram úteis? Use o artigo em inglês como referência.​

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.

×