Como os dados viajam pelo Excel

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

• Importação    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 Importar dados de fontes de dados externas.

• Power Query    Você pode usar Power Query (anteriormente chamada de Get & Transform) para criar consultas para moldar, transformar e combinar dados de várias maneiras. Você pode exportar seu trabalho como um modelo de Power Query 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, confira Ajuda do Power Query para Excel.

• Segurança    Privacidade, credenciais e autenticação de dados são sempre uma preocupação contínua. Para obter mais informações, consulte Gerenciar configurações e permissões de fonte de dados e Definir níveis de privacidade.

• 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 Atualizar uma conexão de dados externa no Excel.

• Connections/Propriedades    Cada fonte de dados externa tem informações de conexão e propriedade variadas associadas a ela que às vezes exigem alterações dependendo de suas circunstâncias. Para obter mais informações, consulte Gerenciar intervalos de dados externos e suas propriedades, Criar, editar e gerenciar conexões com dados externos e propriedades de conexão.

• Legado    Métodos tradicionais, como Assistentes de Importação Herdado 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 microsoft para recuperar dados externos.

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

Há propriedades de conexão, consulta e intervalo de dados externo. 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 as Propriedades de Consulta significam que há. As propriedades externas do intervalo de dados controlam o layout e o formato dos dados. Todas as fontes de dados têm uma caixa de diálogo Propriedades de Dados Externas , mas fontes de dados que associaram informações de credencial e atualização usam a caixa de diálogo Propriedades de Dados de Intervalo Externo maior.

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

Caixa de diálogo ou painel Caminhos de Comando	Guias e túneis	Tópico Principal da Ajuda
Fontes recentes Dados > fontes recentes	(Sem guias) Caixa de diálogo Túneis para Conectar > Navegador	Gerenciar configurações e permissões de fonte de dados
Propriedades de conexão Assistente de conexão de dados or > consultas de dados & Connections > Connections guia > (clique com o botão direito do mouse em uma conexão) > Propriedades	Guiadefinição de guia de uso Usada na guia	Propriedades da conexão
Propriedades da Consulta Dados > > de Connections existentes (clique com o botão direito do mouse em uma conexão) > Editar Propriedades de Conexão Consultas de> dedados ou & conexões | Guia consultas > (clique com o botão direito do mouse em uma conexão) > Propriedades Propriedades de>de consulta ou Or Data > Atualizar Todos os > Connections (quando posicionado em uma planilha de consulta carregada)	Guiadefinição de guia de uso Usada na guia	Propriedades da conexão
Consultas & Connections Consultas de> de dados & Connections	Guia Consultas Connections  tab	Propriedades da conexão
Connections existente Dados > Connections existentes	guia Connections Tabelas	Conectar a dados externos
Propriedades de dados externos Propriedades de intervalo de dados externas do OR Propriedades de> dedados or (desabilitadas se não estiverem posicionadas em uma planilha de consulta)	Usado na guia (da caixa de diálogo Propriedades de Conexão )   Botão Atualizar nos túneis direitos para Propriedades de Consulta	Gerenciar intervalos de dados externos e suas propriedades
Propriedades de conexão > guia Definição > Arquivo de conexão de exportação Arquivo de conexão de exportação de> de consulta ou	(Sem guias) Caixa de diálogo Túneis para Arquivo Pasta Fontes de dados	Criar, editar e gerenciar as conexões aos dados externos

Os dados em uma pasta de trabalho 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 à pasta de trabalho por meio de uma conexão de dados, que é um conjunto de informações que descreve como localizar, fazer logon e acessar a fonte de dados externa.

O main benefício de se conectar a dados externos é que você pode analisar periodicamente esses dados sem copiar repetidamente os dados para sua pasta de trabalho, que é uma operação que pode ser demorada e propensa a erros. Depois de se conectar a dados externos, você também pode atualizar (ou atualizar) automaticamente suas pastas de trabalho 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 de acesso aos 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, verifique se o banco de dados não está aberto no modo exclusivo. Se a fonte de dados for um arquivo de texto ou uma planilha, verifique se outro usuário não o tem aberto 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 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 facilmente editadas.

5. Os dados são copiados em uma pasta de trabalho para que você possa usá-los assim como você usa dados armazenados diretamente na pasta de trabalho.

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

• Connections na pasta de trabalho    

  Esta lista exibe todas as conexões atuais na pasta 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 que você selecionou anteriormente como uma conexão nesta 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, cujo local pode ser implantado em toda a rede como parte da implantação de políticas de grupo do Microsoft Office ou uma biblioteca do SharePoint. 

Também pode utilizar o Excel como editor de ficheiros de ligação para criar e editar ligações a origens de dados externas armazenadas num livro ou num ficheiro de ligação. Se não encontrar a ligação pretendida, pode criar uma ligação ao clicar em Procurar Mais para apresentar a caixa de diálogo Selecionar Origem de Dados e, em seguida, clicar em Nova Origem para iniciar o Assistente de Ligação de Dados.

Depois de criar a ligação, pode utilizar a caixa de diálogo Propriedades da Ligação (Selecionar Dados > Consultas & Connections > separador Connections > (clique com o botão direito do rato numa ligação) > Propriedades) para controlar várias definições de ligações a origens de dados externas e para utilizar, reutilizar ou mudar de ficheiros de ligação.

Observação    Por vezes, a caixa de diálogo Propriedades da Ligação tem o nome da caixa de diálogo Propriedades da Consulta quando existe uma consulta criada no Power Query (anteriormente denominada Obter transformação &) associada à mesma.

Se utilizar um ficheiro de ligação para ligar a uma origem de dados, o Excel copia as informações de ligação do ficheiro de ligação para o livro do Excel. Quando efetua alterações utilizando a caixa de diálogo Propriedades da Ligação , está a editar as informações de ligação de dados armazenadas no livro atual do Excel e não o ficheiro de ligação de dados original que pode ter sido utilizado para criar a ligação (indicado pelo nome de ficheiro que é apresentado na propriedade Ficheiro de Ligação no separador Definição ). Depois de editar as informações de ligação (à exceção das propriedades Nome da Ligação e Descrição da Ligação ), a ligação para o ficheiro de ligação é removida e a propriedade Ficheiro de Ligação é desmarcada.

Para garantir que o ficheiro de ligação é sempre utilizado quando uma origem de dados é atualizada, clique em Tentar sempre utilizar este ficheiro para atualizar estes dados no separador Definição . Selecionar esta caixa de marcar garante que as atualizações ao ficheiro de ligação serão sempre utilizadas por todos os livros que utilizam esse ficheiro de ligação, que também tem de ter esta propriedade definida.

Ao utilizar a caixa de diálogo Connections, pode gerir facilmente estas ligações, incluindo a criação, edição e eliminação das mesmas (Selecionar Consultas> de Dados & Connections > Connections separador > (clique com o botão direito do rato numa ligação) > Propriedades.) Pode utilizar esta caixa de diálogo para fazer o seguinte: 

• Crie, edite, atualize e elimine ligações que estão a ser utilizadas no livro.

• Verifique a origem dos dados externos. Poderá querer fazê-lo caso a ligação tenha sido definida por outro utilizador.

• Mostrar onde cada ligação é utilizada no livro atual.

• Diagnostique uma mensagem de erro sobre ligações a dados externos.

• Redirecione uma ligação para um servidor ou origem de dados diferente ou substitua o ficheiro de ligação para uma ligação existente.

• Facilite a criação e partilha de ficheiros de ligação com os utilizadores.

Os ficheiros de ligação são particularmente úteis para partilhar ligações de forma consistente, tornando as ligações mais detetáveis, ajudando a melhorar a segurança das ligações e facilitando a administração de origens de dados. A melhor forma de partilhar ficheiros de ligação é colocá-los numa localização segura e fidedigna, como uma pasta de rede ou biblioteca do SharePoint, onde os utilizadores podem ler o ficheiro, mas apenas os utilizadores designados podem modificar o ficheiro. Para obter mais informações, veja Partilhar dados com o ODC.

Utilizar ficheiros ODC

Pode criar ficheiros ODC (Ligação de Dados do Office) (.odc) ao ligar a dados externos através da caixa de diálogo Selecionar Origem de Dados ou através do Assistente de Ligação de Dados para ligar a novas origens de dados. Um ficheiro ODC utiliza etiquetas HTML e XML personalizadas para armazenar as informações de ligação. Pode ver ou editar facilmente os conteúdos do ficheiro no Excel.

Pode partilhar ficheiros de ligação com outras pessoas para lhes conceder o mesmo acesso que tem a uma origem de dados externa. Os outros utilizadores não precisam de configurar uma origem de dados para abrir o ficheiro de ligação, mas poderão ter de instalar o controlador ODBC ou o fornecedor OLE DB necessário para aceder aos dados externos no computador.

Os ficheiros ODC são o método recomendado para ligar a dados e partilhar dados. Pode converter facilmente outros ficheiros de ligação tradicionais (DSN, UDL e ficheiros de consulta) num ficheiro ODC ao abrir o ficheiro de ligação e, em seguida, clicar no botão Exportar Ficheiro de Ligação no separador Definição da caixa de diálogo Propriedades da Ligação .

Utilizar ficheiros de consulta

Os ficheiros de consulta são ficheiros de texto que contêm informações de origem de dados, incluindo o nome do servidor onde os dados estão localizados e as informações de ligação que fornece quando cria uma origem de dados. Os ficheiros de consulta são uma forma tradicional de partilhar consultas com outros utilizadores do Excel.

Utilizar ficheiros de consulta .dqy    Pode utilizar o Microsoft Query para guardar ficheiros .dqy que contenham consultas de dados de bases de dados relacionais ou ficheiros de texto. Quando abre estes ficheiros no Microsoft Query, pode ver os dados devolvidos pela consulta e modificar a consulta para obter resultados diferentes. Pode guardar um ficheiro .dqy para qualquer consulta que criar, seja através do Assistente de Consultas ou diretamente no Microsoft Query.

Utilizar ficheiros de consulta .oqy    Pode guardar ficheiros .oqy para ligar a dados numa base de dados OLAP, num servidor ou num arquivo de cubo offline (.cub). Quando utiliza o Assistente de Ligação Multidimensional no Microsoft Query para criar uma origem de dados para uma base de dados ou cubo OLAP, é criado automaticamente um ficheiro .oqy. Uma vez que as bases de dados OLAP não estão organizadas em registos ou tabelas, não pode criar consultas ou ficheiros .dqy para aceder a estas bases de dados.

Utilizar ficheiros de consulta .rqy    O Excel pode abrir ficheiros de consulta no formato .rqy para suportar controladores de origem de dados OLE DB que utilizam este formato. Para obter mais informações, veja a documentação do controlador.

Utilizar ficheiros de consulta .qry    O Microsoft Query pode abrir e guardar ficheiros de consulta no formato .qry para utilização com versões anteriores do Microsoft Query que não conseguem abrir ficheiros .dqy. Se tiver um ficheiro de consulta no formato .qry que pretende utilizar no Excel, abra o ficheiro no Microsoft Query e, em seguida, guarde-o como um ficheiro .dqy. Para obter informações sobre como guardar ficheiros .dqy, consulte a Ajuda do Microsoft Query.

Utilizar ficheiros de consulta Web .iqy    O Excel pode abrir ficheiros de consulta Web .iqy para obter dados da Web. Para obter mais informações, consulte Exportar para o Excel a partir do SharePoint.

Um intervalo de dados externos (também denominado tabela de consulta) é um nome definido ou nome de tabela que define a localização dos dados trazidos para uma folha de cálculo. Quando se liga a dados externos, o Excel cria automaticamente um intervalo de dados externos. A única exceção é um relatório de tabela dinâmica ligado a uma origem de dados, que não cria um intervalo de dados externo. No Excel, pode formatar e definir um intervalo de dados externos ou utilizá-lo em cálculos, tal como acontece com outros dados.

O Excel atribui automaticamente nomes a um intervalo de dados externos da seguinte forma:

• Os intervalos de dados externos dos ficheiros ODC (Ligação de Dados do Office) recebem o mesmo nome que o nome do ficheiro.

• Os intervalos de dados externos das bases de dados têm o nome da consulta. Por predefinição, Query_from_ origem é o nome da origem de dados que utilizou para criar a consulta.

• Os intervalos de dados externos dos ficheiros de texto têm o nome do ficheiro de texto.

• Os intervalos de dados externos de consultas Web são denominados com o nome da página Web a partir da qual os dados foram obtidos.

Se a sua folha de cálculo tiver mais do que um intervalo de dados externos da mesma origem, os intervalos são numerados. Por exemplo, MyText, MyText_1, MyText_2, etc.

Um intervalo de dados externo tem propriedades adicionais (não confundir com propriedades de ligação) que pode utilizar para controlar os dados, como a preservação da formatação das células e a largura das colunas. Pode alterar estas propriedades do intervalo de dados externos ao clicar em Propriedades no grupo Connections no separador Dados e, em seguida, efetuar as alterações nas caixas de diálogo Propriedades do Intervalo de Dados Externos ou Propriedades de Dados Externos.

Existem vários objetos de dados (como um intervalo de dados externos e um relatório de tabela dinâmica) que pode utilizar para ligar a diferentes origens de dados. No entanto, o tipo de origem de dados a que se pode ligar é diferente entre cada objeto de dados.

Pode utilizar e atualizar dados ligados no Serviços do Excel. Tal como acontece com qualquer origem de dados externa, poderá ter de autenticar o seu acesso. Para obter mais informações, consulte Atualizar uma ligação de dados externos no Excel. Fou mais informações sobre credenciais, veja Serviços do Excel Authentication Settings (Definições de Autenticação).

A tabela seguinte resume que origens de dados são suportadas para cada objeto de dados no Excel.

Excel dados objeto	Cria Externo dados intervalo?		OLE BD	ODBC	Texto ficheiro	HTML ficheiro	XML ficheiro	SharePoint lista
Assistente de Importação de Texto	Sim		Não	Não	Sim	Não	Não	Não
Relatório de tabela dinâmica (não 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 do 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 Web	Sim		Não	Não	Não	Sim	Sim	Não
Assistente para Conexão de Dados	Sim		Sim	Sim	Sim	Sim	Sim	Sim
Microsoft Query	Sim		Não	Sim	Sim	Não	Não	Não

Serviços do Excel solução para tabelas do Excel e intervalos com nome

Se quiser apresentar um livro do Excel no Serviços do Excel, pode ligar-se e atualizar dados, mas tem de utilizar um relatório de tabela dinâmica. Serviços do Excel não suporta intervalos de dados externos, o que significa que Serviços do Excel não suporta uma Tabela do Excel ligada a uma origem de dados, a uma consulta Web, a um mapa XML ou ao Microsoft Query.

No entanto, pode contornar esta limitação ao utilizar uma Tabela Dinâmica para ligar à origem de dados e, em seguida, estruturar e esquematizar a Tabela Dinâmica como uma tabela bidimensional sem níveis, grupos ou subtotais para que todos os valores de linha e coluna pretendidos sejam apresentados. 

Vamos fazer uma viagem pela faixa de memória da base de dados.

Acerca do MDAC, OLE DB e OBC

Em primeiro lugar, peço desculpa por todos os acrónimos. O Microsoft Data Access Components (MDAC) 2.8 está incluído no Microsoft Windows. Com o MDAC, pode ligar e utilizar dados de uma grande variedade de origens de dados relacionais e não relacionais. Pode ligar a muitas origens de dados diferentes através de controladores ODBC (Open Database Connectivity) ou fornecedores de OLE DB, criados e enviados pela Microsoft ou desenvolvidos por vários terceiros. Quando instala o Microsoft Office, são adicionados ao seu computador controladores ODBC e fornecedores OLE DB adicionais.

Para ver uma lista completa dos fornecedores OLE DB instalados no seu computador, apresente a caixa de diálogo Propriedades da Ligação de Dados a partir de um ficheiro de Ligação de Dados e, em seguida, clique no separador Fornecedor .

Para ver uma lista completa dos fornecedores ODBC instalados no seu computador, apresente a caixa de diálogo Administrador da Base de Dados ODBC e, em seguida, clique no separador Controladores .

Também pode utilizar controladores ODBC e fornecedores OLE DB de outros fabricantes para obter informações de origens diferentes das origens de dados da Microsoft, incluindo outros tipos de bases de dados ODBC e OLE DB. Para saber mais sobre como instalar esses drivers ODBC ou provedores OLE DB, verifique a documentação do banco de dados ou contate o fornecedor do banco de dados.

Utilizar o ODBC para ligar a origens de dados

Na arquitetura ODBC, uma aplicação (como o Excel) liga-se ao Gestor de Controladores ODBC, que por sua vez utiliza um controlador ODBC específico (como o controlador ODBC do Microsoft SQL) para ligar a uma origem de dados (como uma base de dados do Microsoft SQL Server).

Para ligar a origens de dados ODBC, faça o seguinte:

1. Certifique-se de que o controlador ODBC adequado está instalado no computador que contém a origem de dados.

2. Defina um nome de origem de dados (DSN) utilizando o Administrador de Origem de Dados ODBC para armazenar as informações de ligação no registo ou num ficheiro DSN ou uma cadeia de ligação no código do Microsoft Visual Basic para transmitir as informações de ligação diretamente ao Gestor de Controladores ODBC.

   Para definir uma origem de dados, no Windows, clique no botão Iniciar e, em seguida, clique em Painel de Controle. Clique em Sistema e Manutenção e, em seguida, clique em Ferramentas Administrativas. Clique em Desempenho e Manutenção, clique em Ferramentas Administrativas. e, em seguida, clique em Origens 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 de computador

As origens de dados do computador armazenam informações de ligação no registo, num computador específico, com um nome definido pelo utilizador. É possível usar fontes de dados de computador somente no computador em que elas estão definidas. Há dois tipos de fontes de dados de computador: usuário e sistema. Fontes de dados do usuário podem ser usadas somente pelo usuário atual e são visíveis apenas para esse usuário. As origens de dados do sistema podem ser utilizadas por todos os utilizadores num computador e são visíveis para todos os utilizadores no computador.

Uma origem de dados de computador é especialmente útil quando quer fornecer segurança adicional, uma vez que ajuda a garantir que apenas os utilizadores com sessão iniciada podem ver uma origem de dados do computador e que uma origem de dados do computador não pode ser copiada por um utilizador remoto para outro computador.

Fontes de dados de arquivo

As origens de dados de ficheiros (também denominadas ficheiros DSN) armazenam informações de ligação num ficheiro de texto, não no registo, e são geralmente mais flexíveis de utilizar do que as origens de dados do computador. Por exemplo, pode copiar uma origem de dados de ficheiro para qualquer computador com o controlador ODBC correto, para que a sua aplicação possa depender de informações de ligação consistentes e precisas para todos os computadores que utiliza. Ou você pode colocar a fonte de dados de arquivo em um único servidor, compartilhá-la entre vários 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 compartilhável. Uma origem de dados de ficheiro não comutilizável reside num único computador e aponta para uma origem de dados de máquina. É possível usar fontes de dados de arquivo compartilháveis para acessar fontes de dados de computador existentes de fontes de dados de arquivo.

Utilizar o OLE DB para ligar a origens de dados

Na arquitetura OLE DB, a aplicação que acede aos dados é denominada consumidor de dados (como o Excel) e o programa que permite o acesso nativo aos dados é denominado fornecedor de base de dados (como o Fornecedor OLE DB da Microsoft para SQL Server).

Um ficheiro de Ligação de Dados Universal (.udl) contém as informações de ligação que um consumidor de dados utiliza para aceder a uma origem de dados através do fornecedor OLE DB dessa origem de dados. Pode criar as informações de ligação ao efetuar um dos seguintes procedimentos:

• No Assistente de Ligação de Dados, utilize a caixa de diálogo Propriedades da Ligação de Dados para definir uma ligação de dados para um fornecedor OLE DB. 

• Crie um ficheiro de texto em branco com uma extensão de nome de ficheiro .udl e, em seguida, edite o ficheiro, que apresenta a caixa de diálogo Propriedades da Ligação de Dados .