Criar uma consulta parâmetro

Quando você consulta dados no Excel, talvez queira usar um valor de entrada-um parâmetro-para especificar algo sobre a consulta. Para fazer isso, crie uma consulta parâmetro. A maneira como você cria consultas de parâmetro e como elas se comportam depende se você usa o Microsoft Query ou o Power Query.

Dica: Os parâmetros do Power Query são muito diferentes dos parâmetros usados em consultas baseadas em SQL. Além disso, você pode usar uma consulta em vez de um parâmetro real se tudo o que precisar é filtrar dados. Considere a leitura das seções de exemplo do Power Query antes de criar parâmetros no Power Query.

Microsoft Query

Power Query

Como os parâmetros afetam as consultas

Os parâmetros são usados na cláusula WHERE da consulta – eles sempre funcionam como um filtro para dados recuperados.

Os parâmetros podem ser usados em qualquer etapa de consulta. Além de funcionar como um filtro de dados, os parâmetros podem ser usados para especificar itens como um caminho de arquivo ou um nome de servidor.

Opções de entrada de parâmetro

Os parâmetros podem solicitar ao usuário um valor de entrada quando a consulta é executada ou atualizada, usar uma constante como o valor de entrada ou usar o conteúdo de uma célula especificada como o valor de entrada.

Os parâmetros não solicitam entrada. Em vez disso, você pode alterar o valor usando o editor do Power Query. Ou, em vez de um parâmetro de sua fé, você pode usar uma consulta que se refere a um local externo com um valor que você pode editar facilmente.

Escopo do parâmetro

Um parâmetro faz parte da consulta que ele modifica e não pode ser reutilizado em outras consultas.

Os parâmetros são separados das consultas, uma vez criadas, você pode adicionar um parâmetro às consultas conforme necessário.

  1. Clique em dados > obter & dados de transformação > obter dados > de outras fontes > do Microsoft Query.

  2. Siga as etapas do assistente de consulta. No Assistente de consulta – tela concluir , selecione exibir dados ou editar consulta no Microsoft Query e, em seguida, clique em concluir. A janela Microsoft Query é aberta e exibe a consulta.

  3. Clique em exibir> SQL. Na caixa de diálogo SQL que aparece, localize a cláusula WHERE – uma linha começando com a palavra WHERE, geralmente no final do código SQL. Se não houver uma cláusula WHERE, adicione uma digitando onde em uma nova linha no final da consulta.

  4. Depois de onde, digite o nome do campo, um operador de comparação (=, <, >, como etc.) e um dos seguintes:

    • Para um prompt de parâmetro genérico, digite um ponto de interrogação (?). Nenhuma frase útil é exibida na solicitação que aparece quando a consulta é executada.

      Modo SQL de consulta MS enfatizando a cláusula WHERE

    • Para um prompt de parâmetro que ajude as pessoas a fornecer uma entrada válida, digite uma frase entre colchetes. A frase é exibida no prompt de parâmetro quando a consulta é executada.

      Modo SQL de consulta MS enfatizando a cláusula WHERE

  5. Após terminar de adicionar condições com parâmetros à cláusula WHERE, clique em OK para executar a consulta. O Excel solicitará que você forneça um valor para cada parâmetro e, em seguida, o Microsoft Query exibirá os resultados.

  6. Quando estiver pronto para carregar os dados, feche a janela do Microsoft Query para retornar os resultados para o Excel. A caixa de diálogo Importar dados é aberta.

    Caixa de diálogo Importar dados no Excel

  7. Para revisar seus parâmetros, clique em Propriedades. Em seguida, na caixa de diálogo Propriedades da conexão, na guia definição , clique em parâmetros.

    Caixa de diálogo Propriedades da conexão

  8. A caixa de diálogo parâmetros exibe os parâmetros usados na consulta. Selecione um parâmetro em nome do parâmetro para revisar ou alterar como o valor do parâmetro é obtido. Você pode alterar o prompt do parâmetro, inserir um valor específico ou especificar uma referência de célula.

    Caixa de diálogo parâmetro do MS Query

  9. Clique em OK para salvar as alterações e fechar a caixa de diálogo parâmetros e, na caixa de diálogo Importar dados, clique em OK para exibir os resultados da consulta no Excel.

Agora a sua pasta de trabalho tem uma consulta parâmetro. Sempre que você executar a consulta ou atualizar a conexão de dados, o Excel verificará o parâmetro para concluir a cláusula WHERE da consulta. Se o parâmetro solicitar um valor, o Excel exibirá a caixa de diálogo Inserir valor do parâmetro para coletar a entrada – você pode digitar um valor ou clicar em uma célula que contém o valor. Você também pode especificar que o valor ou a referência fornecida sempre deve ser usado e, se usar uma referência de célula, você pode especificar que o Excel atualize automaticamente a conexão de dados (ou seja, execute a consulta novamente) sempre que o valor da célula especificada for alterado.

Observação: Este tópico pressupõe que você saiba como criar uma conexão com um banco de dados do Access usando o Power Query. Para obter mais informações, consulte conectar a um banco de dados do Access.

Você pode usar parâmetros em mais cenários do Power Query do que apenas filtrar dados – qualquer etapa de uma consulta do Power Query pode ter parâmetros. Por exemplo, você pode usar um parâmetro para especificar partes da cadeia de conexão na etapa de origem, como um nome de arquivo.

Parâmetros do Power Query têm nomes. Para usar um parâmetro, faça referência a ele por nome na fórmula de uma etapa. Por exemplo, suponha que você queira revisar os dados de páginas da Web que mantém e deseja filtrar os dados por data de publicação. Embora você sempre possa usar os filtros internos na visualização da consulta, usar um parâmetro para fornecer uma data para filtragem economizará tempo e dará a você mais flexibilidade. Vamos examinar este exemplo.

Em uma pasta de trabalho vazia, criamos uma conexão com o banco de dados do Access que tem os registros de tráfego da Web que queremos, incluindo os campos que indicam quando cada página foi originalmente publicada. Carregado no Power Query, ele tem a seguinte aparência:

Editor do Power Query que mostra dados carregados

Como queremos filtrar por data, alteramos o tipo de dados da coluna que estamos usando, FirstPublishDate. Eles são dados de data/hora na origem, mas não nos preocupamos a ocorrência da publicação de hora do dia e a necessidade de especificá-lo, portanto, o alteraremos para o tipo de dados de data.

Editor do Power Query exibindo resultados

Em seguida, criamos um parâmetro para limitar os resultados pela data em que a página foi originalmente publicada. Clique em> parâmetros de > gerenciar parâmetros para abrir a caixa de diálogo parâmetros.

Caixa de diálogo parâmetros do Power Query

Clique em novo, e o formulário exibirá um novo parâmetro chamado parâmetro1 sem outras informações.

Alteramos algumas propriedades de parâmetro:

  • Alterar nome para FirstPubD

  • Altere a Descrição para a data em que a página foi publicada pela primeira vez.

  • Alterar o tipo para Data para que o parâmetro aceite apenas valores de data

  • Defina o valor atual para que o parâmetro não filtre todas as linhas quando não fornecermos entradas-usamos 1/1/2010.

Dica: O nome e a descrição devem fornecer contexto suficiente para ajudar as pessoas a entender como e por que usar o parâmetro. Mesmo que você seja a única pessoa que usará o parâmetro, talvez seja necessário um lembrete periodicamente.

Clicamos em OK para criar o parâmetro e visualizá-lo no editor do Power Query.

Editor do Power Query exibindo um parâmetro

Agora, o nosso parâmetro está listado no painel consultas – podemos selecioná-lo para exibi-lo no painel principal, ou clique nele com o botão direito do mouse para ver mais opções. Quando um parâmetro é selecionado, podemos editar o valor atual no painel principal ou clicar em gerenciar parâmetro para alterar suas outras configurações.

Agora podemos usar esse parâmetro na nossa consulta original. Nós clicamos na consulta original no painel consultas para exibi-lo. Queremos usar o parâmetro para filtrar os resultados com base na data da primeira publicação, portanto, vamos selecionar a coluna FirstPublishDate , clique na seta Filtrar/classificar na margem direita do título da coluna, aponte para filtros de datae clique em depois....

Editor do Power Query exibindo um menu de filtro de data

Na caixa de diálogo filtrar linhas, selecionamos parâmetro na lista de opções do filtro.

Caixa de diálogo filtrar linhas

Insira ou selecione um valor é substituído por uma lista de parâmetros disponíveis. Há apenas uma, a que acabamos de criar, FirstPubD.

Caixa de diálogo filtrar linhas exibindo um parâmetro selecionado

Selecione-o e clique em OK. O editor do Power Query carrega a consulta usando o novo parâmetro como um filtro.

Editor do Power Query exibindo resultados filtrados

Para testar o parâmetro, alteramos seu valor para 1/1/2018.

Editor do Power Query exibindo um parâmetro

Atualizamos a consulta, que agora mostra apenas as linhas com FirstPublishDate após 1/1/2018.

Editor do Power Query exibindo resultados filtrados

Agora temos uma consulta que filtra por data usando um parâmetro. Para filtrar os resultados por FirstPublishDate, não precisamos mais encontrar o campo, clique na seta Filtrar/classificar, escolha a seta após... tipo de filtro e insira um valor de data – podemos simplesmente alterar o valor de FirstPubD e atualizar nossa consulta. Além disso, podemos reutilizar o novo parâmetro, por exemplo, se decidirmos extrair um conjunto diferente de campos da fonte de dados original para uma nova planilha, mas ainda assim desejam incluir FirstPubDate e usá-lo para filtrar resultados.

Os parâmetros são muito úteis, mas ainda devemos usar o editor do Power Query para alterar o valor do parâmetro. Gostaríamos de poder alterar o valor do filtro sem abrir o editor do Power Query. Para isso, criaremos uma tabela na planilha onde a consulta é carregada e uma nova conexão do Power Query para a tabela e, em seguida, usamos a nova consulta para filtrar nossa consulta principal.

Na planilha em que a consulta é carregada, inserimos algumas linhas acima dos dados importados. Em seguida, criamos uma tabela do Excel com uma linha para armazenar o valor do parâmetro.

Pasta de trabalho do Excel exibindo uma tabela de parâmetros e dados carregados do Power Query

Para usar a nova tabela para filtrar consultas, precisamos conectar-se a ela no Power Query. Criamos uma conexão com a tabela selecionando-a e clicando em de tabela/intervalo na guia dados . A nova conexão abre e exibe a nova tabela no editor do Power Query.

Dados de tabela do Excel carregados no editor do Power Query

Como os dados são carregados como o tipo de dados data/hora, precisamos alterá-lo para o tipo de dados de data para que ele corresponda ao nosso parâmetro, então, clicamos em Home > transformar > tipo de dados > Data.

Passe o mouse sobre o comando tipo de dados no grupo transformar na guia página inicial da faixa de opções do editor do Power Query.

Também renomemos nossa consulta para algo mais significativo do que tabela2. Para torná-lo claro o que é para, nós o nomeamos FirstPubDate.

Editor do Power Query com a caixa nome realçada

Como queremos passar um valor, não a tabela em si, precisamos fazer uma busca detalhada até o valor de data. Para fazer isso, clico com o botão direito do mouse no valor dos dados visualizados e, em seguida, clico em fazer busca detalhada.

Menu de contexto do editor do Power Query para um valor de campo

A visualização agora exibe o valor em vez da tabela.

Editor do Power Query exibindo um valor de data simples

Não precisamos que os dados da nova consulta sejam carregados em qualquer lugar – seus dados já estão na planilha onde queremos. Precisamos apenas da conexão para que o Power Query possa obter o valor do parâmetro. Portanto, clicamos em arquivo> fechar & carregar para... para abrir a caixa de diálogo Importar dados e, em seguida, selecionamos somente criar conexão.

Caixa de diálogo Importar dados com apenas a opção criar conexão selecionada

Agora temos uma consulta chamada "FirstPubDate" que obtém um valor de data único de uma tabela na planilha acima de onde a nossa consulta principal é carregada. Agora precisamos usar esta consulta como parâmetro para filtrar nossa consulta principal. Portanto, abrimos a consulta principal e edito a etapa que filtra as linhas usando a coluna FirstPublishDate. Expandimos a barra de fórmulas e selecionamos o parâmetro que criamos anteriormente (FirstPubD). Em seguida, digitamos um "a" após FirstPubD , porque o nome da nova consulta começa com as mesmas letras que o parâmetro, o Power Query exibe-o como uma opção para selecionar.

Barra de fórmulas do editor do Power Query expandida

Selecione-o e, em seguida, clique fora da barra de fórmulas para aplicar a etapa.

Editor do Power Query com dados carregados

Tudo parece correto, portanto, encerramos o editor do Power Query e salvamos nossas alterações. Para testar o parâmetro, na planilha de relatório, alteramos o valor da célula na tabela na parte superior para 5/4/2019, atualizamos a conexão para ver os dados filtrados.

Dados filtrados no Excel

Nosso novo filtro funciona! Então, salvamos e fechamos a pasta de trabalho. Agora, qualquer pessoa que usa a pasta de trabalho pode especificar uma data da primeira publicação para usar como um filtro de consulta – diretamente na mesma planilha onde a consulta é carregada.

  1. Clique em dados > obter & transformar dados > obter dados > iniciar o editor do Power Query.

  2. No editor do Power Query, clique em parâmetros > residenciais > gerenciar parâmetros.

  3. Na caixa de diálogo parâmetros, clique em novo.

  4. Defina as seguintes opções conforme necessário:

    • Name -deve refletir a função do parâmetro, mas é o mais curto possível.

    • Descrição -pode conter todos os detalhes que ajudarão as pessoas a usarem o parâmetro de forma correta.

    • Obrigatório -selecione para fazer com que esse parâmetro exija um valor.

    • Tipo – especifica o tipo de dados que o parâmetro requer.

    • Valores sugeridos -se desejar, adicione uma lista de valores ou especifique uma consulta para fornecer sugestões de entrada.

    • Valor padrão -isso só será exibido se a lista de valores sugeridos estiver definida como lista de valores e especifica qual item da lista é o padrão.

    • Valor atual -dependendo de onde você usa o parâmetro, se estiver em branco, a consulta pode não retornar nenhum resultado. Se necessário estiver selecionado, o valor atual não poderá estar vazio.

  5. Clique em OK para criar o parâmetro.

  1. Abra uma consulta no editor do Power Query.

  2. Clique na seta na borda direita do cabeçalho de uma coluna que você deseja usar para filtrar seus dados e escolha um filtro no menu exibido.

  3. Na caixa de diálogo filtrar linhas, clique no botão à direita da condição de filtro e siga um destes procedimentos:

    • Para usar um parâmetro existente, clique em parâmetroe, em seguida, selecione o parâmetro desejado na lista que aparece à direita.

    • Para usar um novo parâmetro, clique em novo parâmetro...e crie um parâmetro.

  1. Na planilha em que a consulta que você deseja filtrar está carregada, crie uma tabela com duas células: um cabeçalho e um valor.

  2. Clique no valor e, em seguida, clique em dados > obter & dados de transformação > de tabela/intervalo.

  3. No editor do Power Query, faça todos os ajustes para a conexão de tabela (por exemplo, alterar o tipo de dados ou o nome) e, em seguida, clique em página inicial > fechar > & fechar > carregarfechar & carregar para....

  4. Na caixa de diálogo Importar dados, clique em somente criar conexão, opcionalmente, selecione Adicionar ao modelo de dadose clique em OK.

  5. Abra a consulta que você deseja filtrar no editor do Power Query.

  6. Clique na seta na borda direita do cabeçalho da coluna que você deseja usar para filtrar seus dados e escolha um filtro no menu exibido.

  7. Siga um destes procedimentos:

    • Selecione um valor na lista suspensa de valores (eles vêm dos dados consultados).

    • Selecione um valor usando o botão na borda direita da condição do filtro.

  8. Clique na seta na borda direita da barra de fórmulas para exibir a consulta inteira.

  9. A condição de filtro segue a palavra cada:

    • O nome da coluna que está sendo filtrada aparece entre colchetes.

    • O operador de comparação acompanha imediatamente o nome da coluna.

    • O valor do filtro acompanha imediatamente o operador de comparação e termina no parêntese de fechamento. Selecione o valor inteiro.

  10. Comece a digitar o nome da conexão de tabela que você acabou de criar e, em seguida, selecione-o na lista exibida.

  11. Clique em página inicial > fechar > fechar carga &.

    Sua consulta agora usa o valor na tabela que você criou para filtrar os resultados da consulta. Para usar um novo valor, edite o conteúdo da célula e atualize a consulta.

Confira também

Criar uma lista suspensa

Observação:  Esta página foi traduzida automaticamente e pode apresentar erros gramaticais ou imprecisões. Nosso objetivo é que este conteúdo seja útil para você. Você pode nos dizer se as informações foram úteis? Use o artigo em inglês como referência.​

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×