Como os dados viajam através do Excel

Aqui estão as principais coisas que pode fazer enquanto os dados estão na estação de comboios Excel:

• Importação    Pode importar dados de origem de diferentes fontes de dados externas. Estas fontes de dados podem estar na sua máquina, na nuvem, ou a meio caminho do mundo. Para obter mais informações, consulte dados de importação de fontes de dados externas.

• Consulta de energia    Pode utilizar a Power Query (anteriormente chamada Get & Transform) para criar consultas para moldar, transformar e combinar dados de várias maneiras. Pode exportar o seu trabalho como modelo de consulta de energia para definir uma operação de fluxo de dados em Power Apps. Pode até criar um tipo de dados para complementar tipos de dados ligados. Para obter mais informações, consulte a Consulta de Energia para a Ajuda do Excel.

• Segurança    A privacidade dos dados, credenciais e autenticação são sempre uma preocupação permanente. Para obter mais informações, consulte Gerir as definições e permissões de fontes de dados e definir os níveis de privacidade.

• Refrescar    Os dados importados geralmente requerem uma operação de atualização para trazer alterações, tais como adições, atualizações e eliminações, para o Excel. Para mais informações, consulte Refrescar uma ligação de dados externa no Excel.

• Conexões/Propriedades    Cada fonte de dados externos tem uma ligação variada e informações de propriedade associadas a ela que às vezes requer alterações dependendo das suas circunstâncias. Para obter mais informações, consulte Gerir as gamas de dados externos e as suas propriedades,criar, editar e gerir ligações a dados externose propriedades de conexão.

• Legado    Os métodos tradicionais, como os Assistentes de Importação Legacy e o MSQuery, ainda estão disponíveis para uso. Para obter mais informações, consulte as opções de importação e análise de dados e utilize a Consulta da Microsoft para obter dados externos.

As seguintes secções fornecem mais detalhes sobre o que se passa nos bastidores desta movimentada estação de comboios Excel.

Existem propriedades de conexão, consulta e gama de dados externos. As propriedades de ligação e consulta contêm informações tradicionais de ligação. Num título de caixa de diálogo, Connection Properties significa que não há nenhuma consulta associada a ele, mas a Query Properties significa que existe. As propriedades da gama de dados externos controlam o layout e o formato dos dados. Todas as fontes de dados têm uma caixa de diálogo external Data Properties, mas fontes de dados que têm informações de credenciais e de atualização associadas utilizam a maior caixa de diálogo external Range Data Properties.

As seguintes informações 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	Separadores e túneis	Tópico de Ajuda Principal
Fontes recentes Dados > Fontes Recentes	(Sem separadores) Túneis para ligar> caixa de diálogo do navegador	Gerir definições e permissões de fontes de dados
Propriedades de conexão Ou Assistente de Ligação de Dados Data > Queries & Connections > Separador > (clique à direita numa ligação) > Propriedades	Separador de definição de separador de utilização Usado no separador	Propriedades de conexão
Propriedades de consulta Data> As ligações existentes > (clique à direita numa ligação) > Editar Propriedades de Conexão OU Data> Consultas & Ligação| Separador de consultas > (clique à direita numa ligação) > Propriedades OU Consulta> Propriedades OU Dados> Refrescar todas asligações > (quando posicionado numa folha de cálculo de consulta carregada)	Separador de definição de separador de utilização Usado no separador	Propriedades de conexão
Consultas & Conexões Data > Consultas & Conexões	Separador de consultas Separador de Ligações	Propriedades de conexão
Conexões existentes Data > Conexões Existentes	Separador tabelas de ligações	Ligar a dados externos
Propriedades de dados externos Ou propriedades de gama de dados externas OU Data> Propriedades (Desativado se não estiver posicionado numa folha de cálculo de consulta)	Usado no separador (da caixa de diálogo Connection Properties)   Atualização botão nos túneis certos para Propriedades de Consulta	Gerir gamas de dados externos e suas propriedades
Aba de conexão de propriedades > > ficheiro de conexão de exportação OU Consulta> Ficheiro de Ligação à Exportação	(Sem separadores) Túneis para arquivar caixa de diálogo Pasta dados pasta	Criar, editar e gerir ligações a dados externos

Os dados num livro do Excel podem vir de dois locais diferentes. Os dados podem ser armazenados diretamente no livro, ou podem ser armazenados numa fonte de dados externa, como um ficheiro de texto, uma base de dados ou um cubo de Processamento Analítico Online (OLAP). Esta fonte de dados externa está ligada ao livro através de uma ligação de dados, que é um conjunto de informações que descrevem como localizar, iniciar sessão e aceder à fonte de dados externa.

O principal benefício da ligação a dados externos é que pode analisar periodicamente estes dados sem copiar repetidamente os dados para o seu livro, que é uma operação que pode ser morosa e propensa a erros. Depois de se ligar a dados externos, também pode atualizar (ou atualizar) automaticamente os seus livros do Excel a partir da fonte de dados original sempre que a fonte de dados for atualizada com novas informações.

As informações de ligação são armazenadas no livro e também podem ser armazenadas num ficheiro de ligação, como um ficheiro de ligação de dados do Office Data Connection (ODC) ou um ficheiro Data Source Name (.dsn).

Para trazer dados externos para o Excel, precisa de ter acesso aos dados. Se a fonte de dados externa a que pretende aceder não estiver no seu computador local, poderá ter de contactar o administrador da base de dados para obter uma palavra-passe, permissões de utilizador ou outras informações de ligação. Se a fonte de dados for uma base de dados, certifique-se de que a base de dados não é aberta em modo exclusivo. Se a fonte de dados for um ficheiro de texto ou uma folha de cálculo, certifique-se de que outro utilizador não o tem aberto para acesso exclusivo.

Muitas fontes de dados também requerem que um controlador ODBC ou um fornecedor de DB OLE coordenem o fluxo de dados entre o Excel, o ficheiro de ligação e a fonte de dados.

O diagrama seguinte resume os pontos-chave sobre as ligações de dados.

1. Existem uma variedade de fontes de dados a que pode ligar: Serviços de Análise, SQL Server, Microsoft Access, outras bases de dados OLAP e relacionais, folhas de cálculo e ficheiros de texto.

2. Muitas fontes de dados têm um controlador ODBC associado ou um fornecedor de DB OLE.

3. Um ficheiro de ligação define todas as informações necessárias para aceder e obter dados a partir de uma fonte de dados.

4. As informações de ligação são copiadas de um ficheiro de ligação para um livro de trabalho, e as informações de ligação podem ser facilmente editadas.

5. Os dados são copiados num livro de trabalho para que possa usá-lo tal como utiliza os dados armazenados diretamente no livro.

Para encontrar ficheiros de ligação, utilize a caixa de diálogo 'Ligações Existentes'. (Selecione dados > ligações existentes.) Utilizando esta caixa de diálogo, pode ver os seguintes tipos de ligações:

• Conexões no livro    

  Esta lista apresenta todas as ligações atuais no livro. A lista é criada a partir de ligações que já definiu, que criou utilizando a caixa de diálogo Select Data Source do Assistente de Ligação de Dados, ou a partir de ligações que selecionou anteriormente como ligação a partir desta caixa de diálogo.

• Ficheiros de ligação no seu computador    

  Esta lista é criada a partir da pasta 'As Minhas Fontes de Dados' que normalmente é armazenada na pasta Documentos.

• Ficheiros de ligação na rede    

  Esta lista pode ser criada a partir de um conjunto de pastas na sua rede local, sendo a localização que pode ser implementada em toda a rede como parte da implementação das políticas do grupo Microsoft Office, ou de uma biblioteca SharePoint. 

Também pode utilizar o Excel como editor de ficheiros de ligação para criar e editar ligações a fontes de dados externas que são armazenadas num livro ou num ficheiro de ligação. Se não encontrar a ligação que pretende, pode criar uma ligação clicando em procurar mais para exibir a caixa de diálogo Select Data Source e, em seguida, clicar em Nova Fonte para iniciar o Assistente de Ligação de Dados.

Depois de criar a ligação, pode utilizar a caixa de diálogo Connection Properties (Select Data > Queries & Separador >Ligações > (clique à direita numa ligação) > Propriedades) para controlar várias definições de ligações a fontes de dados externas e para utilizar, reutilizar ou mudar ficheiros de ligação.

Nota    Por vezes, a caixa de diálogo Connection Properties é nomeada a caixa de diálogo Desíduas Desempenas quando existe uma consulta criada em Power Query (anteriormente chamada Get & Transform) associada a ela.

Se utilizar um ficheiro de ligação para ligar a uma fonte de dados, o Excel copia as informações de ligação do ficheiro de ligação no livro do Excel. Quando escoda alterações utilizando a caixa de diálogo Connection Properties, está a editar as informações de ligação de dados que são armazenadas no livro atual do Excel e não o ficheiro original de ligação de dados que pode ter sido utilizado para criar a ligação (indicada 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 (com exceção das propriedades De Nome de Ligação e Descrição de Conexão), a ligação ao ficheiro de ligação é removida e a propriedade 'Ficheiro de Ligação' é limpa.

Para garantir que o ficheiro de ligação é sempre utilizado quando uma fonte de dados é atualizada, clique em Sempre tentar utilizar este ficheiro para atualizar estes dados no separador Definição. A seleção desta caixa de verificação 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 deve ter este conjunto de propriedades.

Ao utilizar a caixa de diálogo Connections, pode gerir facilmente estas ligações, incluindo a criação, edição e eliminação (Selecione Data > Queries & Connections > Separador > (clique à direita numa ligação) > Propriedades.) Pode utilizar esta caixa de diálogo para fazer o seguinte: 

• Criar, editar, refrescar e apagar ligações que estão a ser utilizadas no livro.

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

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

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

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

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

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

Utilização de ficheiros ODC

Pode criar ficheiros de Ligação de Dados de Office (ODC) (.odc) ligando-os a dados externos através da caixa de diálogo Select Data Source ou utilizando o Assistente de Ligação de Dados para se ligar a novas fontes de dados. Um ficheiro ODC utiliza tags HTML e XML personalizadas para armazenar as informações de ligação. Pode facilmente visualizar ou editar o conteúdo do ficheiro no Excel.

Pode partilhar ficheiros de ligação com outras pessoas para lhes dar o mesmo acesso que tem a uma fonte de dados externa. Outros utilizadores não precisam de configurar uma fonte de dados para abrir o ficheiro de ligação, mas podem precisar de instalar o controlador ODBC ou o fornecedor OLE DB necessário para aceder aos dados externos no seu computador.

Os ficheiros ODC são o método recomendado para a ligação aos dados e a partilha de dados. Pode converter facilmente outros ficheiros de ligação tradicionais (ficheiros DSN, UDL e ficheiros de consulta) para um ficheiro ODC, abrindo o ficheiro de ligação e clicando no botão 'Ficheiro de Ligação de Exportação' no separador Definição da caixa de diálogo Connection Properties.

Utilização de 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 fonte de dados. Os ficheiros de consulta são uma forma tradicional de partilhar consultas com outros utilizadores do Excel.

Usando ficheiros de consulta .dqy    Pode utilizar a Microsoft Query para guardar ficheiros .dqy que contenham consultas de dados de bases de dados relacionais ou ficheiros de texto. Quando abrir estes ficheiros na Microsoft Query, pode visualizar os dados devolvidos pela consulta e modificar a consulta para obter resultados diferentes. Pode guardar um ficheiro .dqy para qualquer consulta que crie, utilizando o Assistente de Consulta ou diretamente na Microsoft Query.

Usando ficheiros de consulta .oqy    Pode guardar ficheiros .oqy para se ligar a dados numa base de dados OLAP, seja num servidor ou num ficheiro de cubo offline (.cub). Quando utiliza o Assistente de Conexão Multidimensional na Microsoft Para criar uma fonte de dados para uma base de dados OLAP ou cubo, um ficheiro .oqy é criado automaticamente. Como as bases de dados do OLAP não estão organizadas em registos ou tabelas, não é possível criar consultas ou ficheiros .dqy para aceder a estas bases de dados.

Usando ficheiros de consulta .rqy    O Excel pode abrir ficheiros de consulta em formato .rqy para suportar os controladores de fonte de dados OLE DB que utilizam este formato. Para mais informações, consulte a documentação do seu motorista.

Utilizando ficheiros de consulta .qry    A Microsoft Query pode abrir e guardar ficheiros de consulta em formato .qry para utilização com versões anteriores da 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 em Microsoft Query e guarde-o como ficheiro .dqy. Para obter informações sobre a poupança de ficheiros .dqy, consulte a Ajuda de Consulta da Microsoft.

Usando ficheiros de consulta web .iqy    O Excel pode abrir ficheiros de consulta web .iqy para obter dados da Web. Para mais informações, consulte Export to Excel da SharePoint.

Uma gama de dados externos (também chamada tabela de consultas) é um nome ou nome de tabela definido que define a localização dos dados trazidos para uma folha de cálculo. Quando se conecta a dados externos, o Excel cria automaticamente uma gama de dados externos. A única exceção a isso é um relatório da Tabela Dinâmica ligado a uma fonte de dados, que não cria uma gama de dados externa. No Excel, pode formatar e definir uma gama de dados externos ou usá-lo em cálculos, como em qualquer outro dado.

O Excel nomeia automaticamente uma gama de dados externo da seguinte forma:

• Os dados externos variam a partir dos ficheiros Office Data Connection (ODC) recebem o mesmo nome que o nome do ficheiro.

• Os dados externos variam a partir de bases de dados com o nome da consulta. Por predefinição Query_from_fonte é o nome da fonte de dados que usou para criar a consulta.

• Os dados externos variam a partir de ficheiros de texto com o nome do ficheiro de texto.

• Os dados externos variam de consultas web são nomeados com o nome da página Web a partir da qual os dados foram recuperados.

Se a sua folha de cálculo tiver mais de um intervalo de dados externos da mesma fonte, os intervalos estão numerados. Por exemplo, MyText, MyText_1, MyText_2, e assim por diante.

Uma gama de dados externos tem propriedades adicionais (não confundir com propriedades de ligação) que pode usar para controlar os dados, como a preservação da formatação celular e largura da coluna. Pode alterar estas propriedades de gama de dados externas clicando em Propriedades no grupo De Ligações no separador Dados e, em seguida, escondo as suas alterações nas propriedades de gama de dados externas ou caixas de diálogo de propriedades de dados externas.

Existem vários objetos de dados (como uma gama de dados externo e relatório PivotTable) que pode utilizar para se conectar a diferentes fontes de dados. No entanto, o tipo de fonte de dados a que se pode ligar é diferente entre cada objeto de dados.

Pode utilizar e atualizar dados conectados nos Serviços Excel. Tal como acontece com qualquer fonte de dados externo, poderá necessitar de autenticar o seu acesso. Para mais informações, consulte Refresh uma ligação de dados externa no Excel. Fou mais informações sobre credenciais, consulte As Definições de Autenticação de Serviços excel.

O quadro seguinte resume quais as fontes de dados suportadas por cada objeto de dados no Excel.

Excel dados objeto	Cria Externo dados alcance?		OLE DB	ODBC	Texto arquivo	HTML arquivo	XML arquivo	SharePoint lista
Assistente de texto de importação	Sim		Não	Não	Sim	Não	Não	Não
Relatório de mesa (não-OLAP)	Não		Sim	Sim	Sim	Não	Não	Sim
Relatório de mesa (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 web	Sim		Não	Não	Não	Sim	Sim	Não
Assistente de Ligação de Dados	Sim		Sim	Sim	Sim	Sim	Sim	Sim
Consulta da Microsoft	Sim		Não	Sim	Sim	Não	Não	Não

Solução de Excel Services para tabelas Excel e gamas nomeadas

Se pretender exibir um livro do Excel nos Serviços Excel, pode ligar e atualizar dados, mas tem de utilizar um relatório Da Tabela Dinâmica. O Excel Services não suporta gamas de dados externas, o que significa que os Excel Services não suportam uma Tabela Excel ligada a uma fonte de dados, uma consulta Web, um mapa XML ou consulta da Microsoft.

No entanto, pode trabalhar em torno desta limitação utilizando uma Tabela Dinâmica para ligar à fonte de dados e, em seguida, projetar e estruturar a Tabela Dinâmica como uma tabela bidimensional sem níveis, grupos ou subtotales para que todos os valores de linha e coluna desejados sejam exibidos. 

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

Sobre MDAC, OLE DB e OBC

Em primeiro lugar, desculpas por todas as siglas. Os componentes de acesso a dados do Microsoft (MDAC) 2.8 estão incluídos no Microsoft Windows . Com o MDAC, pode conectar-se e utilizar dados de uma grande variedade de fontes de dados relacionais e não-culturais. Pode conectar-se a muitas fontes de dados diferentes utilizando controladores de conectividade open database (ODBC) ou fornecedores de BD de OLE, que são construídos e enviados pela Microsoft ou desenvolvidos por vários terceiros. Quando instala o Microsoft Office, são adicionados ao computador controladores ODBC adicionais e fornecedores de DB OLE.

Para ver uma lista completa de fornecedores OLE DB instalados no seu computador, exiba a caixa de diálogo Data Link Properties a partir de um ficheiro Data Link e, em seguida, clique no separador 'Fornecedor'.

Para ver uma lista completa de fornecedores ODBC instalados no seu computador, apresente a caixa de diálogo do administrador de base de dados ODBC e, em seguida, clique no separador Condutores.

Também pode utilizar controladores ODBC e fornecedores de DB OLE de outros fabricantes para obter informações de outras fontes que não as fontes de dados da Microsoft, incluindo outros tipos de bases de dados ODBC e OLE DB. Para obter informações sobre a instalação destes controladores ODBC ou fornecedores de DB OLE, consulte a documentação da base de dados ou contacte o seu fornecedor de bases de dados.

Utilizar o ODBC para ligar a fontes de dados

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

Para ligar às fontes de dados da ODBC, faça o seguinte:

1. Certifique-se de que o controlador ODBC apropriado é instalado no computador que contém a fonte de dados.

2. Defina um nome de origem de dados (DSN) utilizando o Administrador de Fonte 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 Base Visual do Microsoft para passar as informações de ligação diretamente ao Gestor de Controladores ODBC.

   Para definir uma fonte de dados, clique no botão Iniciar e, em seguida, clique em Painel de Controlo. Clique em Sistema e Manutençãoe, em seguida, clique em Ferramentas Administrativas. Clique em Performance e Manutenção,clique em Ferramentas Administrativas. e, em seguida, 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 da máquina

As fontes de dados da máquina armazenam informações de ligação no registo, num computador específico, com um nome definido pelo utilizador. Só pode utilizar fontes de dados de máquinas no computador em que estão definidas. Existem dois tipos de fontes de dados de máquinas — utilizador e sistema. As fontes de dados do utilizador só podem ser utilizadas pelo utilizador atual e são visíveis apenas para esse utilizador. As fontes 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 fonte de dados de máquina é especialmente útil quando pretende fornecer segurança adicional, pois ajuda a garantir que apenas os utilizadores que estão ligados podem ver uma fonte de dados da máquina, e uma fonte de dados da máquina não pode ser copiada por um utilizador remoto para outro computador.

Fontes de dados de ficheiros

As fontes de dados de ficheiros (também chamadas ficheiros DSN) armazenam informações de ligação num ficheiro de texto, não no registo, e são geralmente mais flexíveis de usar do que as fontes de dados da máquina. Por exemplo, pode copiar uma fonte de dados de ficheiro para qualquer computador com o controlador ODBC correto, para que a sua aplicação possa contar com informações de ligação consistentes e precisas a todos os computadores que utiliza. Ou pode colocar a fonte de dados do ficheiro num único servidor, partilhá-lo entre muitos computadores na rede e manter facilmente as informações de ligação num único local.

Uma fonte de dados de ficheiro também pode ser inapartável. Uma fonte de dados de ficheiros inapartiável reside num único computador e aponta para uma fonte de dados de máquina. Pode utilizar fontes de dados de ficheiros não partilhadas para aceder às fontes de dados de máquinas existentes a partir de fontes de dados de ficheiros.

Utilização do OLE DB para ligar a fontes de dados

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

Um ficheiro Universal Data Link (.udl) contém a informação de ligação que um consumidor de dados utiliza para aceder a uma fonte de dados através do fornecedor OLE DB dessa fonte de dados. Pode criar as informações de ligação fazendo uma das seguintes:

• No Assistente de Ligação de Dados, utilize a caixa de diálogo Data Link Properties 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 exibe a caixa de diálogo Data Link Properties.