Power Query: Instrução If - ifs aninhados e múltiplas condições
No Excel Power Query, a instrução IF é uma das funções mais populares para verificar uma condição e retornar um valor específico dependendo se o resultado é VERDADEIRO ou FALSO. Existem algumas diferenças entre esta instrução if e a função IF do Excel. Neste tutorial, apresentarei a sintaxe desta instrução if e alguns exemplos simples e complexos para você.
Sintaxe básica da instrução if de Power Query
Power Query instrução if usando uma coluna condicional
Power Query instrução if escrevendo o código M
Sintaxe básica da instrução if de Power Query
In Power Query, a sintaxe é:
- teste lógico: A condição que você deseja testar.
- valor_se_verdadeiro: O valor a ser retornado se o resultado for TRUE.
- valor_se_falso: O valor a ser retornado se o resultado for FALSE.
No Excel Power Query, existem duas maneiras de criar esse tipo de lógica condicional:
- Usando o recurso Coluna Condicional para alguns cenários básicos;
- Escrevendo código M para cenários mais avançados.
Na seção a seguir, falarei sobre alguns exemplos de uso dessa instrução if.
Power Query instrução if usando uma coluna condicional
Exemplo 1: instrução if básica
Aqui, apresentarei como usar esta instrução if em Power Query. Por exemplo, tenho o seguinte relatório de produto, se o status do produto for Antigo, exibindo um desconto de 50%; se o status do produto for Novo, exibindo um desconto de 20% conforme as capturas de tela mostradas abaixo.
1. Selecione a tabela de dados da planilha e, no Excel 2019 e no Excel 365, clique em Data > Da tabela/intervalo, veja a captura de tela:
Note: No Excel 2016 e no Excel 2021, clique em Data > Da tabela, veja a captura de tela:
2. Então, no aberto Power Query editor janela, clique em Adicionar coluna > Coluna Condicional, veja a captura de tela:
3. No estalou para fora Adicionar coluna condicional caixa de diálogo, faça as seguintes operações:
- Nome da nova coluna: Digite um nome para a nova coluna;
- Em seguida, especifique os critérios necessários. Por exemplo, vou especificar Se Status for igual a Antigo, então 50% senão 20%;
- Nome da coluna: a coluna para avaliar sua condição if. Aqui, seleciono Status.
- operador: Lógica condicional a ser usada. As opções serão diferentes dependendo do tipo de dados do Nome da coluna selecionado.
- Texto: começa com, não começa com, iguala, contém, etc.
- Números: igual, diferente, maior ou igual, etc.
- Data: é antes, é depois, é igual a, não é igual, etc.
- Valor: o valor específico com o qual comparar sua avaliação. Juntamente com o nome da coluna e o operador, compõe uma condição.
- saída: O valor a ser retornado se a condição for atendida.
- Outro: Outro valor a ser retornado quando a condição for falsa.
4. Então clique OK botão para voltar ao Power Query editor janela. Agora, um novo Desconto coluna é adicionada, veja a captura de tela:
5. Caso queira formatar os números para porcentagem, basta clicar ABC123 ícone do Desconto cabeçalho da coluna e selecione Percentagem conforme necessário, veja a captura de tela:
6. Finalmente, por favor clique Home page > Fechar e carregar > Fechar e carregar para carregar esses dados em uma nova planilha.
Exemplo 2: instrução if complexa
Com esta opção de coluna condicional, você também pode inserir duas ou mais condições na Adicionar coluna condicional diálogo. Por favor, faça assim:
1. Selecione a tabela de dados e vá para a Power Query editor janela clicando Data > Da tabela/intervalo. Na nova janela, clique em Adicionar coluna > Coluna Condicional.
2. No estalou para fora Adicionar coluna condicional caixa de diálogo, execute as seguintes operações:
- Insira um nome para a nova coluna no campo Nome da nova coluna caixa de texto;
- Especifique os primeiros critérios no primeiro campo de critérios e clique em Adicionar Cláusula botão para adicionar outros campos de critérios conforme necessário.
3. Após finalizar os critérios, clique em OK botão para voltar ao Power Query editor janela. Agora, você obterá uma nova coluna com o resultado correspondente que você precisa. Veja a captura de tela:
4. Por fim, clique Home page > Fechar e carregar > Fechar e carregar para carregar esses dados em uma nova planilha.
Power Query instrução if escrevendo o código M
Normalmente, a coluna condicional é útil para alguns cenários básicos. Às vezes, pode ser necessário usar várias condições com a lógica AND ou OR. Nesse caso, você deve escrever o código M dentro de uma coluna personalizada para cenários mais complexos.
Exemplo 1: instrução if básica
Tomemos como exemplo os primeiros dados, se o status do produto for Antigo, apresentando 50% de desconto; se o status do produto for Novo, exibindo um desconto de 20%. Para escrever o código M, faça o seguinte:
1. Selecione a tabela e clique em Data > Da tabela/intervalo para ir para o Power Query editor janela.
2. Na janela aberta, clique em Adicionar coluna > Coluna personalizada, veja a captura de tela:
3. No estalou para fora Coluna personalizada caixa de diálogo, execute as seguintes operações:
- Insira um nome para a nova coluna no campo Nome da nova coluna caixa de texto;
- Em seguida, insira esta fórmula: if [Status] = "Antigo" então "50%" senão "20%" no Coluna personalizada Fórmula caixa.
4. Então clique OK para fechar esta caixa de diálogo. Agora, você obterá o seguinte resultado conforme necessário:
5. Finalmente, clique em Home page > Fechar e carregar > Fechar e carregar para carregar esses dados em uma nova planilha.
Exemplo 2: instrução if complexa
Normalmente, para testar as subcondições, você pode aninhar várias instruções if. Por exemplo, eu tenho a tabela de dados abaixo. Se o produto for “Vestido”, dê 50% de desconto sobre o preço original; se o produto for “Sweater” ou “Hoodie”, dê um desconto de 20% sobre o preço original; e outros produtos mantêm o preço original.
1. Selecione a tabela de dados e clique em Data > Da tabela/intervalo para ir para o Power Query editor janela.
2. Na janela aberta, clique em Adicionar coluna > Coluna personalizada. No aberto Coluna personalizada caixa de diálogo, execute as seguintes operações:
- Insira um nome para a nova coluna no campo Nome da nova coluna caixa de texto;
- Em seguida, insira a fórmula abaixo no Coluna personalizada Fórmula caixa.
- = if [Produto] = "Vestido" então [Preço] * 0.5 senão
if [Produto] = "Suéter" então [Preço] * 0.8 senão
se [Produto] = "Moletom" então [Preço] * 0.8
senão [Preço]
3. E então, clique OK botão para voltar ao Power Query editor janela e você obterá uma nova coluna com os dados necessários, veja a captura de tela:
4. Finalmente, clique em Home page > Fechar e carregar > Fechar e carregar para carregar esses dados em uma nova planilha.
A lógica OR executa vários testes lógicos e o resultado verdadeiro retornará se algum dos testes lógicos for correto. A sintaxe é:
Supondo que eu tenha a tabela abaixo, agora, eu quero uma nova exibição de coluna como: se o produto for “Vestido” ou “T-shirt”, então a marca é “AAA”, a marca dos outros produtos é “BBB”.
1. Selecione a tabela de dados e clique em Data > Da tabela/intervalo para ir para o Power Query editor janela.
2. Na janela aberta, clique em Adicionar coluna > Coluna personalizada, no aberto Coluna personalizada caixa de diálogo, execute as seguintes operações:
- Insira um nome para a nova coluna no campo Nome da nova coluna caixa de texto;
- Em seguida, insira a fórmula abaixo no Fórmula de coluna personalizada caixa.
- = if [Produto] = "Vestido" ou [Produto] = "Camiseta" então "AAA"
mais "BBB"
3. E então, clique OK botão para voltar ao Power Query editor janela e você obterá uma nova coluna com os dados necessários, veja a captura de tela:
4. Finalmente, clique em Home page > Fechar e carregar > Fechar e carregar para carregar esses dados em uma nova planilha.
A lógica AND executa vários testes lógicos dentro de uma única instrução if. Todos os testes devem ser verdadeiros para que o resultado verdadeiro seja retornado. Se algum dos testes for falso, o resultado falso é retornado. A sintaxe é:
Pegue os dados acima, por exemplo, quero que uma nova coluna seja exibida como: se o produto for “Vestido” e o pedido for maior que 300, dê um desconto de 50% sobre o preço original; caso contrário, mantenha o preço original.
1. Selecione a tabela de dados e clique em Data > Da tabela/intervalo para ir para o Power Query editor janela.
2. Na janela aberta, clique em Adicionar coluna > Coluna personalizada. No aberto Coluna personalizada caixa de diálogo, execute as seguintes operações:
- Insira um nome para a nova coluna no campo Nome da nova coluna caixa de texto;
- Em seguida, insira a fórmula abaixo no Fórmula de coluna personalizada caixa.
- = se [Produto] ="Vestido" e [Pedido] > 300 então [Preço]*0.5
senão [Preço]
3. Então clique OK botão para voltar ao Power Query editor janela e você obterá uma nova coluna com os dados necessários, veja a captura de tela:
4. Por fim, você deve carregar esses dados em uma nova planilha clicando em Home page > Fechar e carregar > Fechar e carregar.
Instrução if com lógica OR e AND
Tudo bem, os exemplos anteriores são fáceis de entender. Agora, vamos tornar mais difícil. Você pode combinar AND e OR para formar qualquer condição que possa imaginar. Nesse tipo, você pode usar colchetes na fórmula para definir regras complexas.
Considere também os dados acima como exemplo, suponha que eu queira que uma nova coluna seja exibida como: se o produto for “Vestido” e seu pedido for maior que 300, ou o produto for “Calças” e seu pedido for maior que 300, mostre “A+”, caso contrário, exibe “Outro”.
1. Selecione a tabela de dados e clique em Data > Da tabela/intervalo para ir para o Power Query editor janela.
2. Na janela aberta, clique em Adicionar coluna > Coluna personalizada. No aberto Coluna personalizada caixa de diálogo, execute as seguintes operações:
- Insira um nome para a nova coluna no campo Nome da nova coluna caixa de texto;
- Em seguida, insira a fórmula abaixo no Fórmula de coluna personalizada caixa.
- =if ([Produto] = "Vestido" e [Pedido] > 300 ) ou
([Produto] = "Calças" e [Pedido] > 300 )
então "A+"
senão "Outro"
3. Então clique OK botão para voltar ao Power Query editor janela e você obterá uma nova coluna com os dados necessários, veja a captura de tela:
4. Por fim, você deve carregar esses dados em uma nova planilha clicando em Home page > Fechar e carregar > Fechar e carregar.
Na caixa Fórmula de coluna personalizada, você pode usar os seguintes operadores lógicos:
- = : Igual
- <> : Diferente de
- > : Maior que
- >= : Maior ou igual a
- < : Menos de
- <= : Menor ou igual a
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
