Você pode usar o Microsoft Query para recuperar dados de fontes externas. Ao usar o Microsoft Query para recuperar dados de seus bancos de dados e arquivos corporativos, você não precisa digitar novamente os dados que deseja analisar no Excel. Você também pode atualizar seus relatórios e resumos do Excel automaticamente do banco de dados de origem original sempre que o banco de dados for atualizado com novas informações.
Saiba mais sobre o Microsoft Query
Usando o Microsoft Query, você pode se conectar a fontes de dados externas, selecionar dados dessas fontes externas, importar esses dados para a planilha e atualizar os dados conforme necessário para manter os dados da planilha sincronizados com os dados das fontes externas.
Tipos de bancos de dados que você pode acessar Você pode recuperar dados de vários tipos de bancos de dados, incluindo Microsoft Office Access, Microsoft SQL Server e Microsoft SQL Server OLAP Services. Você também pode recuperar dados de pastas de trabalho do Excel e de arquivos de texto.
O Microsoft Office fornece drivers que você pode usar para recuperar dados das seguintes fontes de dados:
- Microsoft SQL Server Analysis Services (provedor OLAP)
- Microsoft Office Access
- dBASE
- Microsoft FoxPro
- Microsoft Office Excel
- Oracle
- Paradoxo
- Bancos de dados de arquivos de texto
Você também pode usar drivers ODBC ou drivers de fonte de dados de outros fabricantes para recuperar informações de fontes de dados que não estão listadas aqui, incluindo outros tipos de bancos de dados OLAP. Para obter informações sobre como instalar um driver ODBC ou driver de fonte de dados que não esteja listado aqui, marque a documentação do banco de dados ou entre em contato com o fornecedor do banco de dados.
Selecionando dados de um banco de dados Você recupera dados de um banco de dados criando uma consulta, que é uma pergunta que você faz sobre dados armazenados em um banco de dados externo. Por exemplo, se seus dados estiverem armazenados em um banco de dados do Access, talvez você queira saber os números de vendas de um produto específico por região. Você pode recuperar uma parte dos dados selecionando apenas os dados do produto e da região que deseja analisar.
Com o Microsoft Query, você pode selecionar as colunas de dados desejadas e importar apenas esses dados para o Excel.
Atualizando sua planilha em uma operação Depois de ter dados externos em uma pasta de trabalho do Excel, sempre que seu banco de dados for alterado, você poderá atualizar os dados para atualizar sua análise — sem precisar recriar seus relatórios de resumo e gráficos. Por exemplo, você pode criar um resumo de vendas mensal e atualizá-lo todos os meses quando os novos números de vendas chegarem.
Como o Microsoft Query usa fontes de dados Depois de configurar uma fonte de dados para um banco de dados específico, você pode usá-la sempre que quiser criar uma consulta para selecionar e recuperar dados desse banco de dados, sem precisar digitar novamente todas as informações de conexão. O Microsoft Query usa a fonte de dados para se conectar ao banco de dados externo e mostrar quais dados estão disponíveis. Depois de criar sua consulta e retornar os dados ao Excel, o Microsoft Query fornece à pasta de trabalho do Excel as informações da consulta e da fonte de dados para que você possa se reconectar ao banco de dados quando quiser atualizar os dados.
Usando o Microsoft Query para importar dados para importar dados externos para o Excel com o Microsoft Query, siga estas etapas básicas, cada uma das quais é descrita em mais detalhes nas seções a seguir.
Conectar-se a uma fonte de dados
O que é uma fonte de dados? Uma fonte de dados é um conjunto armazenado de informações que permite que o Excel e o Microsoft Query se conectem a um banco de dados externo. Ao usar o Microsoft Query para configurar uma fonte de dados, você dá um nome à fonte de dados e, em seguida, fornece o nome e o local do banco de dados ou servidor, o tipo de banco de dados e suas informações de logon e senha. As informações também incluem o nome de um driver OBDC ou driver de fonte de dados, que é um programa que faz conexões com um tipo específico de banco de dados.
Para configurar uma fonte de dados usando o Microsoft Query:
Na guia Dados , no grupo Obter Dados Externos , clique em De Outras Fontes e clique em Do Microsoft Query.
Observação
O Excel 365 moveu o Microsoft Query para o grupo de menus Assistentes Herdados . Este menu não é mostrado por padrão. Para habilitar, vá para Arquivo, Opções, Dados e habilite na seção Mostrar assistentes de importação de dados herdados .
Siga um destes procedimentos:
- Para especificar uma fonte de dados para um banco de dados, arquivo de texto ou pasta de trabalho do Excel, clique na guia Bancos de dados.
- Para especificar uma fonte de dados de cubo OLAP, clique na guia Cubos OLAP . Essa guia estará disponível somente se você tiver executado o Microsoft Query no Excel.
Clique duas vezes em <Nova Fonte> de Dados.
–ou–
Clique em <Nova Fonte de> Dados e clique em OK.
A caixa de diálogo Criar Nova Fonte de Dados é exibida.Na etapa 1, digite um nome para identificar a fonte de dados.
Na etapa 2, clique em um driver para o tipo de banco de dados que você está usando como fonte de dados.
Observação
- Se o banco de dados externo que você deseja acessar não tiver suporte nos drivers ODBC instalados com o Microsoft Query, você precisará obter e instalar um driver ODBC compatível com o Microsoft Office de um fornecedor de terceiros, como o fabricante do banco de dados. Entre em contato com o fornecedor do banco de dados para obter instruções de instalação.
- Os bancos de dados OLAP não exigem drivers ODBC. Quando você instala o Microsoft Query, os drivers são instalados para bancos de dados que foram criados usando o Microsoft SQL Server Analysis Services. Para se conectar a outros bancos de dados OLAP, você precisa instalar um driver de fonte de dados e um software cliente.
Clique em Conectar e forneça as informações necessárias para se conectar à fonte de dados. Para bancos de dados, pastas de trabalho do Excel e arquivos de texto, as informações fornecidas dependem do tipo de fonte de dados selecionada. Talvez seja solicitado que você forneça um nome de logon, uma senha, a versão do banco de dados que está usando, o local do banco de dados ou outras informações específicas do tipo de banco de dados.
Importante
- Use senhas fortes que combinem letras maiúsculas e minúsculas, números e símbolos. Senhas fracas não misturam esses elementos. Um exemplo de senha forte é Y6dh!et5. Um exemplo de senha fraca é Casa27. As senhas devem ter 8 ou mais caracteres. Uma frase secreta com 14 ou mais caracteres é melhor.
- É fundamental que você se lembre da sua senha. Caso você a esqueça, a Microsoft não poderá recuperá-la. Guarde as senhas que anotar por escrito em um lugar seguro, longe das informações que elas ajudam a proteger.
Depois de inserir as informações necessárias, clique em OK ou em Concluir para retornar à caixa de diálogo Criar Nova Fonte de Dados .
Se o seu banco de dados tiver tabelas e você quiser que uma tabela específica seja exibida automaticamente no Assistente de Consultas, clique na caixa da etapa 4 e clique na tabela desejada.
Se não quiser digitar o nome e a senha de logon ao usar a fonte de dados, marque a caixa Salvar minha ID de usuário e senha na definição da fonte de dados marcar. A senha salva não é criptografada. Se a caixa de marcação não estiver disponível, consulte o administrador do banco de dados para determinar se essa opção pode ser disponibilizada.
Observação
Evite salvar informações de logon ao conectar-se a fontes de dados. Essas informações podem ser armazenadas como texto sem formatação e um usuário mal-intencionado pode acessá-las para comprometer a segurança da fonte de dados.
Depois de concluir essas etapas, o nome da fonte de dados será exibido na caixa de diálogo Escolher Fonte de Dados .
Usar o Assistente de Consulta para definir uma consulta
Usar o Assistente de Consulta para a maioria das consultas O Assistente de Consulta facilita a seleção e a junção de dados de diferentes tabelas e campos do banco de dados. Usando o Assistente de Consulta, você pode selecionar as tabelas e os campos que deseja incluir. Uma junção interna (uma operação de consulta que especifica que linhas de duas tabelas são combinadas com base em valores de campo idênticos) é criada automaticamente quando o assistente reconhece um campo de chave primária em uma tabela e um campo com o mesmo nome em uma segunda tabela.
Você também pode usar o assistente para classificar o conjunto de resultados e fazer uma filtragem simples. Na etapa final do assistente, você pode optar por retornar os dados para o Excel ou refinar ainda mais a consulta no Microsoft Query. Depois de criar a consulta, você pode executá-la no Excel ou no Microsoft Query.
Para iniciar o Assistente de Consulta, execute as etapas a seguir.
- Na guia Dados , no grupo Obter Dados Externos , clique em De Outras Fontes e clique em Do Microsoft Query.
- Na caixa de diálogo Escolher Fonte de Dados, verifique se a caixa de marca Usar o Assistente de Consulta para criar/editar consultas está marcada.
- Clique duas vezes na fonte de dados que você deseja usar.
–ou–
Clique na fonte de dados que você deseja usar e clique em OK.
Trabalhar diretamente no Microsoft Query para outros tipos de consultas Se você quiser criar uma consulta mais complexa do que o permitido pelo Assistente de Consulta, poderá trabalhar diretamente no Microsoft Query. Você pode usar o Microsoft Query para exibir e alterar as consultas que começa a criar no Assistente de Consultas ou pode criar novas consultas sem usar o assistente. Trabalhe diretamente no Microsoft Query quando quiser criar consultas que fazem o seguinte:
- Selecione dados específicos de um campo Em um banco de dados grande, você pode escolher alguns dos dados em um campo e omitir dados que não são necessários. Por exemplo, se você precisar de dados para dois dos produtos em um campo que contém informações para muitos produtos, poderá usar critérios para selecionar dados para apenas dois produtos que você deseja.
- Recupere dados com base em critérios diferentes cada vez que executar a consulta Se você precisar criar o mesmo relatório ou resumo do Excel para várias áreas nos mesmos dados externos, como um relatório de vendas separado para cada região, poderá criar uma consulta de parâmetro. Ao executar uma consulta de parâmetro, você é solicitado a fornecer um valor a ser usado como critério quando a consulta seleciona registros. Por exemplo, uma consulta parâmetro pode solicitar que você insira uma região específica e você pode reutilizar essa consulta para criar cada um de seus relatórios de vendas regionais.
- Unir dados de maneiras diferentes As junções internas criadas pelo Assistente de Consulta são o tipo mais comum de junção usada na criação de consultas. Às vezes, no entanto, você deseja usar um tipo diferente de junção. Por exemplo, se você tiver uma tabela de informações de vendas de produtos e uma tabela de informações do cliente, uma junção interna (o tipo criado pelo Assistente de Consulta) impedirá a recuperação de registros de clientes que não fizeram uma compra. Usando o Microsoft Query, você pode unir essas tabelas para que todos os registros de clientes sejam recuperados, juntamente com os dados de vendas dos clientes que fizeram compras.
Para iniciar o Microsoft Query, execute as etapas a seguir.
- Na guia Dados , no grupo Obter Dados Externos , clique em De Outras Fontes e clique em Do Microsoft Query.
- Na caixa de diálogo Escolher Fonte de Dados, verifique se a caixa Usar o Assistente de Consulta para criar/editar consultas marcar está desmarcada.
- Clique duas vezes na fonte de dados que você deseja usar.
–ou–
Clique na fonte de dados que você deseja usar e clique em OK.
Reutilizar e compartilhar consultas Tanto no Assistente de Consulta quanto no Microsoft Query, você pode salvar suas consultas como um arquivo .dqy que pode modificar, reutilizar e compartilhar. O Excel pode abrir arquivos .dqy diretamente, o que permite que você ou outros usuários criem intervalos de dados externos adicionais a partir da mesma consulta.
Para abrir uma consulta salva no Excel:
- Na guia Dados , no grupo Obter Dados Externos , clique em De Outras Fontes e clique em Do Microsoft Query. A caixa de diálogo Escolher Fonte de Dados é exibida.
- Na caixa de diálogo Escolher Fonte de Dados , clique na guia Consultas .
- Clique duas vezes na consulta salva que você deseja abrir. A consulta é exibida no Microsoft Query.
Se você quiser abrir uma consulta salva e o Microsoft Query já estiver aberto, clique no menu Arquivo do Microsoft Query e clique em Abrir.
Se você clicar duas vezes em um arquivo .dqy, o Excel será aberto, executará a consulta e inserirá os resultados em uma nova planilha.
Se você quiser compartilhar um resumo ou relatório do Excel baseado em dados externos, poderá fornecer a outros usuários uma pasta de trabalho que contenha um intervalo de dados externos ou poderá criar um modelo. Um modelo permite salvar o resumo ou relatório sem salvar os dados externos, para que o arquivo seja menor. Os dados externos são recuperados quando um usuário abre o modelo de relatório.
Trabalhar com os dados no Excel
Depois de criar uma consulta no Assistente de Consulta ou no Microsoft Query, você pode retornar os dados para uma planilha do Excel. Os dados se tornam um intervalo de dados externos ou um relatório de Tabela Dinâmica que você pode formatar e atualizar.
Formatação dos dados recuperados No Excel, você pode usar ferramentas, como gráficos ou subtotais automáticos, para apresentar e resumir os dados recuperados pelo Microsoft Query. Você pode formatar os dados e a formatação será mantida quando você atualizar os dados externos. Você pode usar seus próprios rótulos de coluna em vez dos nomes de campo e adicionar números de linha automaticamente.
O Excel pode formatar automaticamente os novos dados digitados no final de um intervalo para corresponder às linhas anteriores. O Excel também pode copiar automaticamente fórmulas que foram repetidas nas linhas anteriores e estendê-las para linhas adicionais.
Observação
Para serem estendidos para novas linhas no intervalo, os formatos e fórmulas devem aparecer em pelo menos três das cinco linhas anteriores.
Você pode ativar essa opção (ou desativá-la novamente) a qualquer momento:
- Clique emOpções>de arquivo>avançadas.
- Na seção Opções de edição, marque a opção Estender formatos e fórmulas de intervalo de dados marcar. Para desativar novamente a formatação automática de intervalo de dados, desmarque essa caixa de marcação.
Atualizando dados externos Ao atualizar dados externos, execute a consulta para recuperar quaisquer dados novos ou alterados que correspondam às suas especificações. Você pode atualizar uma consulta no Microsoft Query e no Excel. O Excel fornece várias opções para atualizar consultas, incluindo atualizar os dados sempre que você abrir a pasta de trabalho e atualizá-la automaticamente em intervalos cronometrados. Você pode continuar a trabalhar no Excel enquanto os dados estão sendo atualizados e também pode marcar o status enquanto os dados estão sendo atualizados. Para obter mais informações, consulte Atualizar uma conexão de dados externa no Excel.