Como os dados fluem no Excel

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

• Importar    Pode importar dados de várias origens de dados externas diferentes. Estas origens de dados podem estar no seu computador, na cloud ou a meio mundo. Para obter mais informações, veja Importar dados de origens de dados externas.

• Power Query    Pode utilizar Power Query (anteriormente denominado Obter & Transformar) para criar consultas para formatar, transformar e combinar dados de várias formas. Pode exportar o seu trabalho como modelo de Power Query para definir uma operação de fluxo de dados no Power Apps. Pode até criar um tipo de dados para complementar os tipos de dados ligados. Para mais informações, consulte Ajuda do Power Query para Excel.

• Segurança    A privacidade, as credenciais e a autenticação dos dados são sempre uma preocupação contínua. Para obter mais informações, veja Gerir definições e permissões da origem de dados e Definir níveis de privacidade.

• Atualizar    Normalmente, os dados importados requerem uma operação de atualização para introduzir alterações, como adições, atualizações e eliminações, no Excel. Para obter mais informações, consulte Atualizar uma ligação de dados externos no Excel.

• Connections/Propriedades    Cada origem de dados externa tem várias informações de ligação e propriedade associadas que, por vezes, requerem alterações consoante as suas circunstâncias. Para obter mais informações, veja Gerir intervalos de dados externos e as respetivas propriedades, Criar, editar e gerir ligações a dados externos e Propriedades de ligação.

• Legado    Os métodos tradicionais, como Os Assistentes de Importação Legados e o MSQuery, ainda estão disponíveis para utilização. Para obter mais informações, veja Opções de importação e análise de dados e Utilizar o Microsoft Query para obter dados externos.

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

Existem propriedades de ligação, consulta e intervalo de dados externos. As propriedades de ligação e consulta contêm informações de ligação tradicionais. Num título da caixa de diálogo, Propriedades da Ligação significa que não existe nenhuma consulta associada, mas as Propriedades da Consulta significam que existe. As propriedades do intervalo de dados externos controlam o esquema e o formato dos dados. Todas as origens de dados têm uma caixa de diálogo Propriedades de Dados Externos , mas as origens de dados que têm credenciais associadas e informações de atualização utilizam a caixa de diálogo Propriedades de Dados de Intervalo Externo maior.

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

Caixa de Diálogo ou Painel Caminhos de Comando	Separadores e túneis	Tópico de Ajuda Principal
Origens recentes Origens Recentes do> de Dados	(Sem separadores) Caixa de diálogo Túneis para Ligar > Navegador	Gerir definições e permissões da origem de dados
Propriedades da Ligação ASSISTENTE deLigação de Dados OU Data > Queries & Connections > Connections tab > (clique com o botão direito do rato numa ligação) > Propriedades	Separador Definição do separador UtilizaçãoUtilizado no separador	Propriedades da ligação
Propriedades da Consulta Dados > > de Connections Existente (clique com o botão direito do rato numa ligação) > Editar Propriedades da Ligação OU Consultas de> deDados & Ligações| Separador Consultas > (clique com o botão direito do rato numa ligação) > Propriedades PROPRIEDADES do>de Consulta OR OU Data > Refresh All > Connections (quando posicionado numa folha de cálculo de consulta carregada)	Separador Definição do separador UtilizaçãoUtilizado no separador	Propriedades da ligação
Consultas & Connections Consultas de> de Dados & Connections	Separador Consultas Connections  tab	Propriedades da ligação
Connections existente Dados > Connections Existentes	separador ConnectionsTabelas	Ligar a dados externos
Propriedades de dados externos OU Propriedades do intervalo de dados externos OU Propriedades do> de Dados ( Desativado se não estiver posicionado numa folha de cálculo de consulta)	Utilizado no separador (na caixa de diálogo Propriedades da Ligação )   Botão Atualizar nos túneis direitos para Propriedades da Consulta	Gerir intervalos de dados externos e respectivas propriedades
Propriedades da Ligação > separador Definição > Exportar Ficheiro de Ligação OR Consultar > Exportar Ficheiro de Ligação	(Sem separadores) Caixa de diálogo Túneis para Ficheiro Pastade origens de dados	Criar, editar e gerir ligações a dados externos

Os dados num livro do Excel podem ser provenientes de duas localizações diferentes. Os dados podem ser armazenados diretamente no livro ou podem ser armazenados numa origem de dados externa, como um ficheiro de texto, uma base de dados ou um cubo OLAP (Online Analytical Processing). Esta origem de dados externa está ligada ao livro através de uma ligação de dados, que é um conjunto de informações que descreve como localizar, iniciar sessão e aceder à origem de dados externa.

A principal vantagem de ligar 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 ligar a dados externos, também pode atualizar (ou atualizar) automaticamente os seus livros do Excel a partir da origem de dados original sempre que a origem 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 (ODC) (.odc) ou um ficheiro de Nome da Origem de Dados (.dsn).

Para trazer dados externos para o Excel, precisa de aceder aos dados. Se a origem de dados externa à qual pretende aceder não estiver no 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 origem de dados for uma base de dados, certifique-se de que a base de dados não está aberta em modo exclusivo. Se a origem 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 origens de dados também requerem um controlador ODBC ou um fornecedor OLE DB para coordenar o fluxo de dados entre o Excel, o ficheiro de ligação e a origem de dados.

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

1. Existem várias origens de dados às quais se pode ligar: Analysis Services, SQL Server, Microsoft Access, outras bases de dados OLAP e relacionais, folhas de cálculo e ficheiros de texto.

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

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

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

5. Os dados são copiados para um livro para que possa utilizá-lo tal como utiliza os dados armazenados diretamente no livro.

Para localizar ficheiros de ligação, utilize a caixa de diálogo Connections existente. (Selecione Dados > Connections Existentes.) Com esta caixa de diálogo, pode ver os seguintes tipos de ligações:

• Connections 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 através da caixa de diálogo Selecionar Origem de Dados do Assistente de Ligação de Dados ou de ligações que selecionou anteriormente como ligação a partir desta caixa de diálogo.

• Ficheiros de ligação no computador    

  Esta lista é criada a partir da pasta As Minhas Origens 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, cuja localização pode ser implementada na rede como parte da implementação de políticas de grupo do Microsoft Office ou de 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.

Nota    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 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 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 ficheiro 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 de Ligaçã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 BD de OLE, 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 obter informações sobre como instalar estes controladores ODBC ou fornecedores de OLE DB, consulte a documentação da base de dados ou contacte o fornecedor da sua base 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 Controlo. 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.

Origens 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. Pode utilizar origens de dados de computador apenas no computador em que são definidas. Existem dois tipos de origens de dados de computador: utilizador e sistema. As origens de dados de utilizador podem ser utilizadas apenas pelo utilizador atual e são visíveis apenas para esse utilizador. 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.

Origens de dados de ficheiro

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. Também pode colocar a origem de dados de ficheiro num servidor exclusivo, partilhá-la com vários computadores na rede e manter as informações de ligação facilmente numa localização.

Também é possível não permitir a partilha de uma origem de dados de ficheiro. Uma origem de dados de ficheiro não comutilizável reside num único computador e aponta para uma origem de dados de máquina. Pode utilizar origens de dados de ficheiro cuja partilha não é permitida para aceder a origens de dados de computador existentes a partir de origens de dados de ficheiro.

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 .