Gerenciar intervalos de dados externos e suas propriedades

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

Um intervalo de dados externo é um nome ou nome de tabela definido que define o local dos dados que são trazidos para uma planilha. Quando você se conecta a dados externos, o Excel cria automaticamente um intervalo de dados externo. A única exceção a isso é 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 externo. No Excel, você pode formatar e estabelecer um intervalo de dados externo ou usá-lo em cálculos, como com qualquer outro dado.

Propriedades de intervalo de dados externos

Um intervalo de dados externo tem propriedades adicionais (não devem ser confundidas com propriedades de consulta ou conexão) que você pode usar para controlar os dados, como a preservação da formatação de células e a 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 suas alterações fazendo o seguinte:

  • Para intervalos de dados externos criados a partir da Consulta da Microsoft e do Assistente de Conexão de Dados, use a caixa de diálogo Propriedades de Dados Externos.

  • Para intervalos de dados externos criados a partir de um arquivo de texto importado ou consulta da Web que recupera dados HTML, use a caixa de diálogo Propriedades do Intervalo de Dados Externos.

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

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

  2. Na barra de fórmulas, clique na seta ao lado da Caixa de Nomes eclique no nome do intervalo de dados externo que você deseja.

  1. Selecione Fórmulas >Name Manager.

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

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

Observação: O botão Fechar fecha apenas a caixa de diálogo Gerenciador de Nomes. Não é preciso clicar em Fechar para que você já tenha feito alterações.

Você pode alterar a consulta subjacente para um intervalo de dados externo que foi criado a partir da Consulta da Microsoft, um arquivo de texto importado, uma consulta da Web ou o Assistente de Conexão de Dados.

  1. Clique em uma célula no intervalo de dados externo para o qual você deseja alterar a consulta subjacente.

  2. Selecione Propriedades >Dados.

  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 da Conexão, clique na guia Definição e clique em Editar Consulta.

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

  1. Clique em uma célula no intervalo de dados externo para o qual você deseja alterar a consulta subjacente.

  2. Selecione Conexões > Conexões & Consultas >, clique com o botão direito do mouse na conexão que deseja e selecione Propriedades.

  3. Na caixa de diálogo Propriedades da Conexão, clique na guia Definição e clique em Editar Consulta.

  4. Na caixa de diálogo Importar Arquivo de Texto, clique em Importar.

  5. 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 a importação de arquivos de texto, consulte Importar ou exportar arquivos de texto.

  1. Clique em uma célula no intervalo de dados externo para o qual você deseja alterar a consulta subjacente.

  2. Selecione Propriedades> Dados.

  3. Selecione Conexões > Conexões & Consultas >, clique com o botão direito do mouse na conexão que deseja e selecione Propriedades.

  4. Na caixa de diálogo Propriedades da Conexão, clique na guia Definição e clique em Editar Consulta.

  5. Faça alterações na consulta Da Web na caixa de diálogo Editar Consulta da Web e clique em Concluir.

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

  1. Clique em uma célula no intervalo de dados externo para o qual você deseja alterar a consulta subjacente.   

  2. Selecione Propriedades> Dados.

  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 da Conexão, clique na guia Definição.

  5. Siga um destes procedimentos:

    • Na caixa Tipo de comando, clique em Tabela e, na caixa de texto Comando, altere o valor para o nome de uma tabela, exibição ou consulta apropriada.

    • Na caixa Tipo de comando, clique em SQLou Padrão e, na caixa de texto Comando, edite SQL instrução.

      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 headers 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 fórmulas que você deseja copiar. Clique na seta ao lado da Caixa nome na barra de fórmulas e clique no nome do intervalo de dados externo que você deseja copiar.

  2. Selecione Home > Copy.

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

  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 sob ou à direita da célula que você clicar.

  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 externo, 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 ao Excel quando os dados são atualizados.

  1. Selecione Propriedades> Dados.

  2. Na caixa de diálogo Propriedades do Intervalo de Dados Externos, em Seo número de linhas no intervalo de dados for atualizado, clique em uma das seguintes opções:

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

      Observações: 

      • Quando uma ou mais linhas são adicionadas na fonte de dados, as células diretamente sob o intervalo de dados externos são movedas 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 na fonte de dados, as células diretamente sob o intervalo de dados externos são movimentadas 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ãousadas

      Observações: 

      • Quando uma ou mais linhas são adicionadas na fonte de dados, as células diretamente abaixo e à direita do intervalo de dados externos são 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 com novos dados, limpar células nãousadas

      Observações: 

      • Quando uma ou mais linhas são adicionadas na fonte de dados, as células diretamente sob o intervalo de dados externos são substituídas, 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. Selecione Propriedades> Dados.

  2. Em Formatação e layout de dados,faça um ou mais dos seguintes:

    • 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 externo que é convertido de um programa Microsoft Office 2003 ou para um intervalo de dados externo criado programaticamente.

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

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

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

      Observação: Para uma consulta da Web, essa opção é automaticamente desfeita quando você seleciona Formatação HTML Completa na caixa de diálogo Opções. Você acessa a caixa de diálogo Opções da caixa de diálogo Editar Consulta.

    • Para preservar as larguras de coluna definidas, marque a caixa de seleção Ajustar largura da coluna.

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

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

    • Para preservar a filtragem de colunas aplicada, marque a caixa de seleção Preservar filtro de coluna.

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

Observação: O procedimento a seguir se aplica apenas a um intervalo de dados externo que é convertido de um programa Microsoft Office 2003 ou a um intervalo de dados externo criado programaticamente. Todos os intervalos de dados externos criados usando a interface do usuário são criados como tabelas do Excel. Essas tabelas se expandem automaticamente quando novas linhas são adicionadas e preenchem colunas calculadas.

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

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

  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 externo.

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

  4. Selecione Propriedades> Dados.

  5. Na caixa de diálogo Propriedades do Intervalo de Dados Externos, marque a caixa de seleção Preencher fórmulas em colunas adjacentes a dados.

    Observação: Se o intervalo de dados externo se expandir quando você atualizar, o Excel copia apenas as fórmulas imediatamente adjacentes a ou dentro do intervalo de dados externo.

Congelar um intervalo de dados externo 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 Nome eclique 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 de 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 se aplica apenas a um intervalo de dados externo que é convertido de um programa Microsoft Office 2003 ou a um intervalo de dados externo criado programaticamente. Todos os intervalos de dados externos criados usando a interface do usuário são criados como tabelas do Excel. Essas tabelas se expandem automaticamente quando novas linhas são adicionadas e preenchem colunas calculadas.

  1. Selecione Propriedades> Dados.

  2. Em Definição de consulta,desempure a caixa de seleção Salvar definição de consulta.

Observação: Quando você salva sua carteira de trabalho, a consulta subjacente é excluída da workbook. No entanto, os arquivos de consulta de banco de dados salvos (arquivos .dqy ou .odc) não são excluídos. Se você salvou a consulta quando a criou no Assistente de Consulta ou na Consulta da Microsoft, o arquivo de consulta será salvo em seu computador e poderá usar a consulta novamente para recuperar dados externos. Para obter mais informações, consulte Microsoft Query Help.

Observação: O procedimento a seguir aplica-se apenas a um intervalo de dados externo que é criado usando o Assistente de Conexão de Dados ou a Consulta da Microsoft (e não a um arquivo de texto importado ou consulta da Web), um intervalo de dados externo convertido de um programa Microsoft Office 2003 ou um intervalo de dados externo criado programaticamente. 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 fazendo um dos seguintes:

    • Selecione uma célula acima da qual você deseja inserir a nova linha. Em seguida, na guia Página Principal, no grupo Células, clique na seta ao lado de Inserire 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 Principal, 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. Selecione Propriedades> Dados.

  5. Na caixa de diálogo Propriedades do Intervalo de Dados Externos, em Formataçãoe layout de dados, desempure 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 externo e clique em Atualizar Dados Imagem do botão .

Observação: Quando você recupera dados de um banco de dados, quaisquer alterações nos nomes de coluna na Consulta da Microsoft são mantidas no intervalo de dados externo. Para obter informações sobre como alterar nomes de coluna na consulta, consulte Microsoft Query Help.

  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 Nomes eclique no nome do intervalo de dados externo que você deseja excluir.

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

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

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

Confira também

Consulta do Power para Ajuda do Excel

Precisa de mais ajuda?

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.

×