Como os dados são jornadas pelo Excel

Aqui estão as principais coisas que você pode fazer enquanto os dados estão na estação de trem do Excel:

• Importar    Você pode importar dados de várias fontes de dados externas diferentes. Essas fontes de dados podem estar em seu computador, na nuvem ou no meio do mundo. Para obter mais informações, consulte Import data from external data sources.

• Consulta do Power    Você pode usar a Consulta do Power (anteriormente chamada de Get & Transform) para criar consultas para forma, transformar e combinar dados de várias maneiras. Você pode exportar seu trabalho como um Modelo de Consulta do Power para definir uma operação de fluxo de dados no Power Apps. Você pode até mesmo criar um tipo de dados para complementar tipos de dados vinculados. Para obter mais informações, consulte Power Query for Excel Help.

• Segurança    Privacidade, credenciais e autenticação de dados são sempre uma preocupação contínua. Para obter mais informações, consulte Manage data source settings and permissions and Set privacy levels.

• Atualizar    Os dados importados geralmente exigem uma operação de atualização para trazer alterações, como adições, atualizações e exclusões, para o Excel. Para obter mais informações, consulte Refresh an external data connection in Excel.

• Conexões/Propriedades    Cada fonte de dados externa tem informações de conexão e propriedade assortadas associadas a ela que, às vezes, exigem alterações, dependendo de suas circunstâncias. Para obter mais informações, consulte Manage external data ranges and their properties, Create, edit, and manage connections to external data, and Connection properties.

• Legacy    Métodos tradicionais, como Assistentes de Importação Herdou e MSQuery, ainda estão disponíveis para uso. Para obter mais informações, consulte Opções de importação e análise de dados e Usar a Consulta da Microsoft para recuperar dados externos.

As seções a seguir fornecem mais detalhes sobre o que está acontecendo nos bastidores nesta estação de trem do Excel.

Há propriedades de conexão, consulta e intervalo de dados externos. As propriedades de conexão e consulta contêm informações de conexão tradicionais. Em um título da caixa de diálogo, Propriedades de Conexão significa que não há consulta associada a ela, mas Propriedades de Consulta significa que há. As propriedades do intervalo de dados externos controlam o layout e o formato dos dados. Todas as fontes de dados têm uma caixa de diálogo Propriedades de Dados Externos, mas fontes de dados que têm informações de credencial e atualização associadas usam a caixa de diálogo Propriedades de Dados de Intervalo Externo maior.

As informações a seguir resumem as caixas de diálogo mais importantes, painéis, caminhos de comando e tópicos de ajuda correspondentes.

Caixa de Diálogo ou Painel Caminhos de comando	Guias e tuneis	Tópico da Ajuda Principal
Fontes recentes Dados > Fontes Recentes	(Sem guias) Caixa de diálogo Tunes to Connect> Navigator	Gerenciar configurações e permissões de fonte de dados
Propriedades de conexão OR Data Connection Wizard Dados > Consultas & Conexões > guia Conexões > (clique com o botão direito do mouse em uma conexão) > Propriedades	Guia Definição de guia de uso usada na guia	Propriedades de conexão
Propriedades de consulta Dados> Conexões Existentes > (clique com o botão direito do mouse em uma conexão) > Editar Propriedades de Conexão OR Data> Consultas & conexão| Guia Consultas > (clique com o botão direito do mouse em uma conexão) > Propriedades Or Query> Properties OU Dados> Atualizar Todas as> Conexões (quando posicionadas em uma planilha de consulta carregada)	Guia Definição de guia de uso usada na guia	Propriedades de conexão
Consultas & Conexões Consultas > de dados & Conexões	Guia Consultas Guia Conexões	Propriedades de conexão
Conexões existentes Dados > conexões existentes	Guia Tabelas da guia Conexões	Conectar-se a dados externos
Propriedades de dados externos OU Propriedades de intervalo de dados externos OU Propriedades> dados (Desabilitadas se não estiver posicionada em uma planilha de consulta)	Usada na guia (da caixa de diálogo Propriedades da Conexão)   Botão Atualizar nos tuneis à direita para Propriedades de Consulta	Gerenciar intervalos de dados externos e suas propriedades
Propriedades de conexão > guia Definição > Exportar Arquivo de Conexão OR Query> Exportar Arquivo de Conexão	(Sem guias) Pasta Fontes de dados da caixa de diálogo Túnel para Arquivo	Criar, editar e gerenciar conexões com dados externos

Os dados em uma planilha do Excel podem vir de dois locais diferentes. Os dados podem ser armazenados diretamente na pasta de trabalho, ou podem ser armazenados em uma fonte de dados externa, como um arquivo de texto, um banco de dados ou um cubo OLAP (Processamento Analítico Online). Essa fonte de dados externa está conectada à agenda de trabalho por meio de uma conexão de dados, que é um conjunto de informações que descreve como localizar, fazer logoff e acessar a fonte de dados externa.

O principal benefício de se conectar a dados externos é que você pode analisar periodicamente esses dados sem copiar repetidamente os dados para sua workbook, que é uma operação que pode levar tempo e propensa a erros. Depois de se conectar a dados externos, você também pode atualizar automaticamente (ou atualizar) suas planilhas do Excel da fonte de dados original sempre que a fonte de dados for atualizada com novas informações.

As informações de conexão são armazenadas na pasta de trabalho e também podem ser armazenadas em um arquivo de conexão, como um arquivo ODC (Conexão de Dados do Office) (.odc) ou um arquivo Nome da Fonte de Dados (.dsn).

Para trazer dados externos para o Excel, você precisa acessar os dados. Se a fonte de dados externa que você deseja acessar não estiver no computador local, talvez seja necessário entrar em contato com o administrador do banco de dados para obter uma senha, permissões de usuário ou outras informações de conexão. Se a fonte de dados for um banco de dados, certifique-se de que o banco de dados não está aberto no modo exclusivo. Se a fonte de dados for um arquivo de texto ou uma planilha, certifique-se de que outro usuário não a tenha aberta para acesso exclusivo.

Muitas fontes de dados também exigem um driver ODBC ou um provedor OLE DB para coordenar o fluxo de dados entre o Excel, o arquivo de conexão e a fonte de dados.

O diagrama a seguir resume os pontos-chave sobre as conexões de dados.

1. Há uma variedade de fontes de dados às quais você pode se conectar: Analysis Services, SQL Server, Microsoft Access, outros bancos de dados OLAP e relacionais, planilhas e arquivos de texto.

2. Muitas fontes de dados têm um driver ODBC associado ou um provedor OLE DB.

3. Um arquivo de conexão define todas as informações necessárias para acessar e recuperar dados de uma fonte de dados.

4. As informações de conexão são copiadas de um arquivo de conexão para uma pasta de trabalho e as informações de conexão podem ser editadas facilmente.

5. Os dados são copiados em uma manual de trabalho para que você possa usá-los da mesma forma que você usa dados armazenados diretamente na workbook.

Para encontrar arquivos de conexão, use a caixa de diálogo Conexões Existentes. (Selecione Dados >Conexões Existentes.) Usando essa caixa de diálogo, você pode ver os seguintes tipos de conexões:

• Conexões na workbook    

  Esta lista exibe todas as conexões atuais na lista de trabalho. A lista é criada a partir de conexões que você já definiu, que você criou usando a caixa de diálogo Selecionar Fonte de Dados do Assistente de Conexão de Dados ou de conexões selecionadas anteriormente como uma conexão dessa caixa de diálogo.

• Arquivos de conexão em seu computador    

  Essa lista é criada a partir da pasta Minhas Fontes de Dados que geralmente é armazenada na pasta Documentos.

• Arquivos de conexão na rede    

  Essa lista pode ser criada a partir de um conjunto de pastas em sua rede local, onde o local pode ser implantado na rede como parte da implantação de políticas de grupo Microsoft Office ou uma biblioteca do SharePoint. 

Você também pode usar o Excel como editor de arquivos de conexão para criar e editar conexões a fontes de dados externas armazenadas em uma pasta de trabalho ou em um arquivo de conexão. Se você não encontrar a conexão que deseja, poderá criar uma conexão clicando em Procurar mais para exibir a caixa de diálogo Selecionar Fonte de Dados e clicando em Nova Fonte para iniciar o Assistente de Conexão de Dados.

Depois de criar a conexão, você pode usar a caixa de diálogo Propriedades da Conexão > (Selecione Consultas de dados & Conexões > > guia Conexões > (clique com o botão direito do mouse em uma conexão) > Propriedades) para controlar várias configurações para conexões com fontes de dados externas e para usar, reutilizar ou alternar arquivos de conexão.

Observação    Às vezes, a caixa de diálogo Propriedades de Conexão é chamada de caixa de diálogo Propriedades de Consulta quando há uma consulta criada no Power Query (anteriormente chamada get & Transform) associada a ela.

Se você usar um arquivo de conexão para se conectar a uma fonte de dados, o Excel copia as informações de conexão do arquivo de conexão para a pasta de trabalho do Excel. Ao fazer alterações usando a caixa de diálogo Propriedades de Conexão, você está editando as informações de conexão de dados armazenadas na pasta de trabalho atual do Excel e não o arquivo de conexão de dados original que pode ter sido usado para criar a conexão (indicado pelo nome do arquivo exibido na propriedade Connection File na guia Definição). Depois de editar as informações de conexão (com exceção das propriedades Nome da Conexão e Descrição da Conexão), o link para o arquivo de conexão é removido e a propriedade Connection File é limpa.

Para garantir que o arquivo de conexão seja sempre usado quando uma fonte de dados é atualizada, clique em Sempre tente usar esse arquivo para atualizar esses dados na guia Definição. Selecionar essa caixa de seleção garante que as atualizações para o arquivo de conexão sempre serão usadas por todas as pasta de trabalho que usam esse arquivo de conexão, que também deve ter essa propriedade definida.

Usando a caixa de diálogo Conexões, você pode gerenciar facilmente essas conexões, incluindo a criação, edição e exclusão delas (Selecione Dados > Consultas & Conexões > guia Conexões > (clique com o botão direito do mouse em uma conexão) > Propriedades.) Você pode usar essa caixa de diálogo para fazer o seguinte: 

• Crie, edite, atualize e exclua conexões que estão em uso na workbook.

• Verifique a origem dos dados externos. Talvez você queira fazer isso caso a conexão tenha sido definida por outro usuário.

• Mostrar onde cada conexão é usada na agenda atual.

• Diagnosticar uma mensagem de erro sobre conexões com dados externos.

• Redirecione uma conexão para um servidor ou fonte de dados diferente ou substitua o arquivo de conexão por uma conexão existente.

• Facilita a criação e o compartilhamento de arquivos de conexão com os usuários.

Os arquivos de conexão são particularmente úteis para compartilhar conexões de forma consistente, tornando as conexões mais descobertas, ajudando a melhorar a segurança das conexões e facilitando a administração da fonte de dados. A melhor maneira de compartilhar arquivos de conexão é colocá-los em um local seguro e confiável, como uma pasta de rede ou biblioteca do SharePoint, onde os usuários podem ler o arquivo, mas somente os usuários designados podem modificar o arquivo. Para obter mais informações, consulte Compartilhar dados com o ODC.

Usando arquivos ODC

Você pode criar arquivos ODC (Conexão de Dados do Office) conectando-se a dados externos por meio da caixa de diálogo Selecionar Fonte de Dados ou usando o Assistente de Conexão de Dados para se conectar a novas fontes de dados. Um arquivo ODC usa marcas HTML e XML personalizadas para armazenar as informações de conexão. Você pode exibir ou editar facilmente o conteúdo do arquivo no Excel.

Você pode compartilhar arquivos de conexão com outras pessoas para dar a elas o mesmo acesso que você tem a uma fonte de dados externa. Outros usuários não precisam configurar uma fonte de dados para abrir o arquivo de conexão, mas podem precisar instalar o driver ODBC ou o provedor OLE DB necessário para acessar os dados externos em seu computador.

Os arquivos ODC são o método recomendado para se conectar a dados e compartilhar dados. Você pode facilmente converter outros arquivos de conexão tradicionais (arquivos DSN, UDL e consulta) em um arquivo ODC abrindo o arquivo de conexão e clicando no botão Exportar Arquivo de Conexão na guia Definição da caixa de diálogo Propriedades da Conexão.

Usando arquivos de consulta

Os arquivos de consulta são arquivos de texto que contêm informações de fonte de dados, incluindo o nome do servidor onde os dados estão localizados e as informações de conexão que você fornece ao criar uma fonte de dados. Os arquivos de consulta são uma maneira tradicional de compartilhar consultas com outros usuários do Excel.

Usando arquivos de consulta .dqy    Você pode usar a Consulta da Microsoft para salvar arquivos .dqy que contenham consultas para dados de bancos de dados relacionais ou arquivos de texto. Ao abrir esses arquivos no Microsoft Query, você pode exibir os dados retornados pela consulta e modificar a consulta para recuperar resultados diferentes. Você pode salvar um arquivo .dqy para qualquer consulta que você criar, usando o Assistente de Consulta ou diretamente na Consulta da Microsoft.

Usando arquivos de consulta .oqy    Você pode salvar arquivos .oqy para se conectar a dados em um banco de dados OLAP, em um servidor ou em um arquivo de cubo offline (.cub). Quando você usa o Assistente de Conexão Multidimensional na Consulta da Microsoft para criar uma fonte de dados para um banco de dados OLAP ou cubo, um arquivo .oqy é criado automaticamente. Como os bancos de dados OLAP não são organizados em registros ou tabelas, você não pode criar consultas ou arquivos .dqy para acessar esses bancos de dados.

Usando arquivos de consulta .rqy    O Excel pode abrir arquivos de consulta no formato .rqy para dar suporte a drivers de fonte de dados OLE DB que usam esse formato. Para obter mais informações, consulte a documentação do seu driver.

Usando arquivos de consulta .qry    A Consulta da Microsoft pode abrir e salvar arquivos de consulta no formato .qry para uso com versões anteriores da Consulta da Microsoft que não podem abrir arquivos .dqy. Se você tiver um arquivo de consulta no formato .qry que deseja usar no Excel, abra o arquivo na Consulta da Microsoft e salve-o como um arquivo .dqy. Para obter informações sobre como salvar arquivos .dqy, consulte Microsoft Query Help.

Usando arquivos de consulta da Web .iqy    O Excel pode abrir arquivos de consulta da Web .iqy para recuperar dados da Web. Para obter mais informações, consulte Exportar para o Excel do SharePoint.

Um intervalo de dados externo (também chamado de tabela de consulta) é um nome ou nome de tabela definido que define o local dos dados 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 conectado a uma fonte de dados, que não cria 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.

O Excel nomeia automaticamente um intervalo de dados externos da seguinte forma:

• Os intervalos de dados externos de arquivos ODC (Conexão de Dados do Office) têm o mesmo nome que o nome do arquivo.

• Intervalos de dados externos de bancos de dados são nomeados com o nome da consulta. Por padrão Query_from_fonte é o nome da fonte de dados que você usou para criar a consulta.

• Intervalos de dados externos de arquivos de texto são nomeados com o nome do arquivo de texto.

• Intervalos de dados externos de consultas da Web são nomeados com o nome da página da Web da qual os dados foram recuperados.

Se sua planilha tiver mais de um intervalo de dados externos da mesma fonte, os intervalos serão numerados. Por exemplo, MyText, MyText_1, MyText_2 e assim por diante.

Um intervalo de dados externo tem propriedades adicionais (não devem ser confundidas com propriedades de 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 e fazendo suas alterações nas caixas de diálogo Propriedades do Intervalo de Dados Externos ou Propriedades de Dados Externos.

Há vários objetos de dados (como um intervalo de dados externo e um relatório de tabela dinâmica) que você pode usar para se conectar a diferentes fontes de dados. No entanto, o tipo de fonte de dados à qual você pode se conectar é diferente entre cada objeto de dados.

Você pode usar e atualizar dados conectados Serviços do Excel. Assim como em qualquer fonte de dados externa, talvez seja necessário autenticar seu acesso. Para obter mais informações, consulte Refresh an external data connection in Excel. Fou mais informações sobre credenciais, consulte Serviços do Excel Configurações de Autenticação.

A tabela a seguir resume quais fontes de dados são suportadas para cada objeto de dados no Excel.

Excel data object	Cria Externo data range?		OLE DB	ODBC	Texto file	HTML file	XML file	SharePoint list
Assistente de Importação de Texto	Sim		Não	Não	Sim	Não	Não	Não
Relatório de tabela dinâmica (non-OLAP)	Não		Sim	Sim	Sim	Não	Não	Sim
Relatório de tabela dinâmica (OLAP)	Não		Sim	Não	Não	Não	Não	Não
Tabela excel	Sim		Sim	Sim	Não	Não	Sim	Sim
Mapa XML	Sim		Não	Não	Não	Não	Sim	Não
Consulta da Web	Sim		Não	Não	Não	Sim	Sim	Não
Assistente de Conexão de Dados	Sim		Sim	Sim	Sim	Sim	Sim	Sim
Consulta da Microsoft	Sim		Não	Sim	Sim	Não	Não	Não

Serviços do Excel solução alternativa para tabelas do Excel e intervalos nomeados

Se quiser exibir uma planilha do Excel no Serviços do Excel, você pode se conectar e atualizar dados, mas deve usar um relatório de tabela dinâmica. Serviços do Excel não dá suporte a intervalos de dados externos, o que significa que o Serviços do Excel não dá suporte a uma Tabela do Excel conectada a uma fonte de dados, uma consulta Web, um mapa XML ou Uma Consulta microsoft.

No entanto, você pode resolver essa limitação usando uma Tabela Dinâmica para se conectar à fonte de dados e, em seguida, projetar e layout da Tabela Dinâmica como uma tabela bidimensional sem níveis, grupos ou subtotais para que todos os valores de linha e coluna desejados sejam exibidos. 

Vamos fazer uma viagem até a faixa de memória do banco de dados.

Sobre MDAC, OLE DB e OBC

Em primeiro lugar, desculpe-se por todos os acrônimos. O Microsoft Data Access Components (MDAC) 2.8 está incluído no Microsoft Windows . Com o MDAC, você pode se conectar e usar dados de uma ampla variedade de fontes de dados relacionais e não relacionadas. Você pode se conectar a várias fontes de dados diferentes usando drivers ODBC (Open Database Connectivity) ou provedores OLE DB, que são construídos e enviados pela Microsoft ou desenvolvidos por vários terceiros. Quando você instala o Microsoft Office, drivers ODBC adicionais e provedores OLE DB são adicionados ao computador.

Para ver uma lista completa de provedores OLE DB instalados em seu computador, exibe a caixa de diálogo Propriedades do Link de Dados de um arquivo de Link de Dados e clique na guia Provedor.

Para ver uma lista completa de provedores ODBC instalados em seu computador, exibe a caixa de diálogo Administrador de Banco de Dados ODBC e clique na guia Drivers.

Você também pode usar drivers ODBC e provedores OLE DB de outros fabricantes para obter informações de fontes diferentes de fontes de dados da Microsoft, incluindo outros tipos de bancos de dados ODBC e OLE DB. Para obter informações sobre como instalar esses drivers ODBC ou provedores OLE DB, verifique a documentação do banco de dados ou entre em contato com o fornecedor do banco de dados.

Usando o ODBC para se conectar a fontes de dados

Na arquitetura ODBC, um aplicativo (como o Excel) se conecta ao Gerenciador de Driver ODBC, que, por sua vez, usa um driver ODBC específico (como o driver ODBC do Microsoft SQL) para se conectar a uma fonte de dados (como um banco de dados Microsoft SQL Server).

Para se conectar às fontes de dados ODBC, faça o seguinte:

1. Verifique se o driver ODBC apropriado está instalado no computador que contém a fonte de dados.

2. Defina um nome de fonte de dados (DSN) usando o Administrador de Fonte de Dados ODBC para armazenar as informações de conexão no Registro ou em um arquivo DSN ou uma cadeia de caracteres de conexão no código do Microsoft Visual Basic para passar as informações de conexão diretamente para o Gerenciador de Driver ODBC.

   Para definir uma fonte de dados, no Windows, clique no botão Iniciar e clique em Painel de Controle. Clique em Sistema e Manutençãoe clique em Ferramentas Administrativas. Clique em Desempenho e Manutenção,clique em Ferramentas Administrativas. e clique em Fontes de Dados (ODBC). Para obter mais informações sobre as diferentes opções, clique no botão Ajuda em cada caixa de diálogo.

Fontes de dados do computador

As fontes de dados do computador armazenam informações de conexão no Registro, em um computador específico, com um nome definido pelo usuário. Você só pode usar fontes de dados do computador no computador em que elas estão definidas. Há dois tipos de fontes de dados de máquina : usuário e sistema. As fontes de dados do usuário só podem ser usadas pelo usuário atual e ficam visíveis apenas para esse usuário. As fontes de dados do sistema podem ser usadas por todos os usuários em um computador e ficam visíveis para todos os usuários no computador.

Uma fonte de dados de máquina é especialmente útil quando você deseja fornecer segurança adicionada, pois ajuda a garantir que somente usuários conectados possam exibir uma fonte de dados do computador, e uma fonte de dados de máquina não pode ser copiada por um usuário remoto para outro computador.

Fontes de dados de arquivo

Fontes de dados de arquivo (também chamadas de arquivos DSN) armazenam informações de conexão em um arquivo de texto, não no Registro, e são geralmente mais flexíveis de usar do que as fontes de dados do computador. Por exemplo, você pode copiar uma fonte de dados de arquivo para qualquer computador com o driver ODBC correto, para que seu aplicativo possa contar com informações de conexão consistentes e precisas para todos os computadores que ele usa. Ou você pode colocar a fonte de dados de arquivo em um único servidor, compartilhá-la entre muitos computadores na rede e manter facilmente as informações de conexão em um único local.

Uma fonte de dados de arquivo também pode ser não compartilhamento. Uma fonte de dados de arquivo não compartilhamento reside em um único computador e aponta para uma fonte de dados do computador. Você pode usar fontes de dados de arquivo não compartilhadas para acessar fontes de dados de máquina existentes de fontes de dados de arquivo.

Usando o OLE DB para se conectar a fontes de dados

Na arquitetura OLE DB, o aplicativo que acessa os dados é chamado de consumidor de dados (como o Excel), e o programa que permite o acesso nativo aos dados é chamado de provedor de banco de dados (como o Microsoft OLE DB Provider for SQL Server).

Um arquivo link de dados universal (.udl) contém as informações de conexão que um consumidor de dados usa para acessar uma fonte de dados por meio do provedor OLE DB dessa fonte de dados. Você pode criar as informações de conexão fazendo um dos seguintes:

• No Assistente de Conexão de Dados, use a caixa de diálogo Propriedades do Link de Dados para definir um link de dados para um provedor OLE DB. 

• Crie um arquivo de texto em branco com uma extensão de nome de arquivo .udl e edite o arquivo, que exibe a caixa de diálogo Propriedades do Link de Dados.