Gerenciar intervalos de dados externos e suas propriedades

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 definir um intervalo de dados externo ou usá-lo em cálculos, como acontece com qualquer outro dado.

Propriedades externas do intervalo de dados

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

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

• Para intervalos de dados externos que são criados a partir de um arquivo de texto importado ou consulta 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 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. No barra de fórmulas, clique na seta ao lado da Caixa de Nomes e clique no nome do intervalo de dados externo desejado.

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 para a referência na caixa Nome .

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

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

2. Selecione Home > Copy.

3. Alterne 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, verifique se a planilha não tem dados em ou à direita da célula clicada.

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

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

1. Selecione Propriedades de> de Dados .

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 usadas

     Observações: 

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

     Observações: 

     • Quando uma ou mais linhas são adicionadas na fonte de dados, as células diretamente sob e à direita do intervalo de dados externos se movem 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 externo não se movem.

   • Substituir células existentes com novos dados, limpar células não utilizados

     Observações: 

     • Quando uma ou mais linhas são adicionadas na fonte de dados, as células diretamente sob o intervalo de dados externo 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 externo não se movem.

1. Selecione Propriedades de> de Dados .

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

   • Para incluir nomes de campo como a primeira linha, selecione a caixa Incluir nomes de campo marcar.

     Observação: Essa caixa de marcar só está disponível para um intervalo de dados externo que é convertido de um programa do Microsoft Office 2003 ou para um intervalo de dados externo criado programaticamente.

   • Para adicionar uma coluna de números de linha, selecione a caixa Incluir números de linha marcar.

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

   • Para preservar a formatação de célula que você aplica, selecione a caixa Preservar formatação de célula marcar.

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

   • Para preservar as larguras de coluna definidas, selecione a caixa Ajustar largura da coluna marcar.

   • Para preservar a classificação, a filtragem e o layout da coluna que você aplica, selecione a caixa Preservar classificação/filtro/layout da coluna marcar.

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

   • Para preservar a filtragem de coluna que você aplica, selecione a caixa Preservar filtro de coluna marcar.

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

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 segunda linha no intervalo de dados externo, dependendo se a primeira linha contém cabeçalhos.

2. Selecione a célula e clique duas vezes no 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 de> de Dados .

5. Na caixa de diálogo Propriedades do Intervalo de Dados Externo, selecione a caixa Preencher fórmulas em colunas adjacentes à caixa de marcar de dados.

   Observação: Se o intervalo de dados externo se expandir quando você atualizar, o Excel copiará apenas as fórmulas que são 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 externo congelado não pode ser atualizado.

1. Insira uma linha em branco acima do intervalo de dados externos fazendo um dos seguintes 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 Inserir e 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 desejados nas células na linha em branco.

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

4. Selecione Propriedades de> de Dados .

5. Na caixa de diálogo Propriedades do Intervalo de Dados Externos, em Formatação e layout de dados, desmarque a caixa Incluir nomes de campo marcar e clique em OK.

6. Para remover os nomes de campo existentes e atualizar o intervalo de dados externo, clique em uma célula no intervalo de dados externo e clique em Atualizar Dados .

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

2. Na barra de fórmulas, clique na seta ao lado da Caixa de Nomes e clique 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 solicitar.