Funções condicionais em planilhas

Quando alguém aprende praticamente qualquer linguagem de programação, uma das primeiras coisas que aparecem são as condicionais. Ou seja, as instruções clássicas IF-ELSEIF-ELSE, que permitem executar operações diferentes com base em condições lógicas. No Excel isso pode não ser a primeira coisa que vem à mente. Mesmo assim, elas existem e são bem importantes quando você trabalha em relatórios no Excel com um certo nível de complexidade. Neste tutorial, vamos ver como as instruções IF-ELSE são estruturadas no Excel e, também, algumas funções mais avançadas, como COUNTIF(), que são muito úteis em determinadas situações.

IF básicas

Para começar, vamos dar uma olhada em como escrever uma instrução IF básica no Excel. A sintaxe é a seguinte:

IF(condition, value_if_true, value_if_false)

Onde:

• condition: valor ou operação lógica que resulta em TRUE ou FALSE
• value_if_true: o valor a retornar se a condição for TRUE
• value_if_false: o valor a retornar se a condição for FALSE

Dito isso, vamos direto a um exemplo simples de como usar uma condicional básica em uma tabela de demonstração:

Simples, né? Para quem já conhece R, você provavelmente percebeu que essa sintaxe é essencialmente a mesma da função ifelse(). Porém, há algumas diferenças. Por exemplo, os operadores lógicos. No exemplo acima, usei o operador lógico \">\" para indicar maior que, que é bem padrão. Já outros não são tão óbvios. É o caso do operador diferente de, que é escrito como \"<>\"

Veja abaixo a lista completa de operadores lógicos usados no Excel:

OR(), AND() e NOT()

Os operadores de comparação não são os únicos elementos das condicionais no Excel que podem diferir do que você usa em Python, R ou Matlab. Na verdade, a forma de definir operações booleanas como OR, AND e NOT é diferente. Por exemplo, no Excel você escreveria uma expressão OR assim:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Para ver todos os operadores booleanos em ação no Excel, confira o exemplo abaixo:

IF aninhadas

Assim como você pode aninhar instruções IF-ELSEIF-ELSE em Python, R ou Matlab, também pode fazer isso no Excel. É tão simples quanto colocar outra IF dentro de uma IF anterior quando ela avaliar para TRUE ou FALSE. Isso permite testar mais condições e retornar mais resultados com base nelas. O Excel permite aninhar até 64 IFs. No entanto, eu desaconselho aninhar IFs demais, pois é preciso muito cuidado com a lógica, e depurar ou alterar depois pode virar um pesadelo.

Dito isso, aqui vai um exemplo simples de IFs aninhadas no próprio Excel:

COUNTIF() e COUNTIFS()

Além das IF tradicionais, o Excel tem outras funções que executam operações com base em um conjunto de condições. Nesta seção, vou focar nas funções de contagem condicional, mais especificamente em COUNTIF() e COUNTIFS().

COUNTIF() permite contar as células que atendem a um único critério. Sua sintaxe é:

COUNTIF(cell_range_to_count, criteria)

Onde:

• cell_range_to_count: o intervalo específico de células que você deseja contar
• criteria: o critério que define quais células devem ser contadas. Eles podem ser bem diversos, por exemplo:
  • COUNTIF(A1:A10, 10) - Conta quantas células no intervalo de A1 a A10 são iguais a 10
  • COUNTIF(A1:A10, B5) - Conta quantas células no intervalo de A1 a A10 são iguais ao valor em B5
  • COUNTIF(A1:A10, \"Gandalf\") - Conta quantas células no intervalo de A1 a A10 são iguais a \"Gandalf\"
  • COUNTIF(A1:A10, \">20\") - Conta quantas células no intervalo de A1 a A10 são maiores que 20
  • COUNTIF(A1:A10, \"<>Gandalf\") - Conta quantas células no intervalo de A1 a A10 são diferentes de \"Gandalf\"

COUNTIFS, como você deve imaginar, difere de COUNTIF por permitir contar células que atendem a múltiplos critérios. Sua sintaxe é:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

É importante observar que, para cada intervalo adicional especificado em COUNTIFS, você deve ter o mesmo número de linhas e colunas do intervalo original (cell_range_to_count_1), caso contrário ocorrerá erro.

Essas duas funções são ótimas para relatórios de negócio. Um exemplo clássico é como fica fácil contar o número de reuniões que nossos representantes de vendas fazem por mês e em que etapa do funil elas estão.

Nos vídeos abaixo, eu mostro essas duas funções em ação. Primeiro, vamos contar o total de reuniões de um grupo fictício de vendedores:

Viu como é fácil? Você acabou de somar quantas reuniões cada um dos nossos vendedores fictícios teve no total. Em teoria, daria para fazer isso também usando apenas IF básicas, mas exigiria bem mais trabalho para chegar exatamente ao mesmo resultado.

Agora, digamos que você também queira somar as reuniões que cada vendedor teve em cada mês. Nesse caso, COUNTIFS() é a resposta; veja no vídeo abaixo:

Observação: você deve ter percebido que, no último vídeo, usei sinais de $ no intervalo que eu queria contar. Isso serve para fixá-lo e evitar que mude quando eu arrastar a fórmula para baixo.

Pronto: somamos as reuniões dos nossos vendedores fictícios por mês sem esforço. Vale mencionar que dá para fazer isso de forma mais \"eficiente\" se você brincar com os sinais de $ para travar certas células e intervalos ao arrastar as fórmulas (por exemplo, eu poderia ter escrito a fórmula em F2 e depois arrastado para o restante). Preferi ser mais explícito escrevendo as fórmulas de COUNTIFS para janeiro e fevereiro para dar mais visibilidade. Se você ficou curioso, recomendo reescrever a fórmula de COUNTIFS() em F2 de modo que possa arrastá-la para as demais células e obter o mesmo resultado. Esse é um bom exercício, já que é muito útil fixar intervalos e células para arrastar fórmulas quando seus relatórios no Excel ficam grandes. Aqui está a tabela de dados usada neste tutorial.

SUMIF() e SUMIFS()

Vimos como contar células que atendem a um ou a vários critérios com COUNTIF() e COUNTIFS(), mas e se você precisar somar as células em vez de apenas contá-las? Aí entram SUMIF() e SUMIFS(). Essas funções são especialmente úteis quando você precisa somar a receita que um vendedor gerou no total ou em um determinado mês, por exemplo. A sintaxe das duas funções é a seguinte:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Onde:

• criteria_range: o intervalo de células no qual o critério será verificado.
• criteria: o critério condicional usado para determinar quais células serão somadas.
• sum_range: as células que serão somadas. Se não for informado em COUNTIF(), as células em criteria_range serão somadas.

Agora, vamos a um exemplo usando SUMIF() e SUMIFS() para calcular a receita total e a receita por mês gerada pelos nossos representantes de vendas fictícios:

Curiosamente, no caso de SUMIFS(), sum_range é um argumento obrigatório e vem primeiro, enquanto em SUMIF() ele é opcional e aparece por último. Isso pode confundir quando você usa as duas fórmulas, como deu para ver quando digitei a função SUMIFS para fevereiro. Portanto, fique atento ao trabalhar com ambas. Sempre confira se você está somando os intervalos corretos.

IFERROR()

Se você conhece alguma linguagem de programação/script, sabe que existem blocos try/catch para tratamento de erros. No Excel, temos a função IFERROR() para fazer exatamente isso. A IFERROR permite \"capturar\" erros como #N/A, #VALUE! ou #REF! e exibir uma saída mais amigável. A sintaxe da função IFERROR é a seguinte:

IFERROR(value, value_if_error)

Onde:

• value: o valor ou fórmula a ser verificado quanto a erro
• value_if_error: o valor a exibir quando a função encontrar um erro.

Veja como funciona na prática no vídeo a seguir:

Ao trabalhar com relatórios de vendas, é uma boa prática envolver certos cálculos em IFERROR. Às vezes, você terá situações em que novos vendedores terão pouquíssimas reuniões, oportunidades qualificadas ou negócios fechados, o que pode causar divisões por 0 ao calcular, por exemplo, o percentual de reuniões que viram oportunidades qualificadas para um determinado vendedor. Isso gera erros feios, que deixam o relatório com aparência suja e mais difícil de ler para quem toma decisão. Porém, se você envolver esses cálculos com IFERROR(), dá para substituir esses erros por algo mais agradável, como um traço, deixando o relatório bem mais apresentável. Além disso, IFERROR() vai além de apenas exibir mensagens de erro mais amigáveis. Você também pode aninhar essas instruções para fazer, por exemplo, VLOOKUPs encadeados.

Conclusão

Parabéns! Agora você conhece várias maneiras de usar condicionais no Excel. Neste tutorial, procurei cobrir as funções que mais uso quando preciso montar relatórios de negócio no Excel. Mas, se você quiser ir além, há outras funções como SWITCH(), AVERAGEIF() e AVERAGEIFS() que não tratei aqui e que podem ser do seu interesse.

Além disso, dá para usar curingas (wildcards) em funções como COUNTIFS() para contar correspondências parciais de texto. Por exemplo, imagine que você tem uma planilha com itens do cardápio de um restaurante e quer contar quantos mencionam a palavra \"fish\". Nesse caso, você poderia escrever COUNTIF(cell_range,\"*fish\"), e essa função contaria itens como swordfish ou dolphinfish. Recomendo fortemente aprender mais sobre curingas: eles são muito úteis e podem salvar seu próximo projeto no Excel.

Por fim, vale dar uma olhada no curso Data Analysis with Spreadsheets da DataCamp, que tem uma seção dedicada a funções condicionais e pode servir de referência adicional.

Confira também nosso tutorial de if…elif…else em Python.