O guia do iniciante para funções aninhadas no Excel

O aninhamento no Microsoft Excel envolve o uso de uma função como argumento para outra, permitindo realizar vários cálculos ao mesmo tempo. Isso evita que você tenha que usar colunas auxiliares ou escrever várias fórmulas em várias células, mantendo sua planilha mais organizada e eficiente.

Principais definições

Este guia usa os três termos a seguir, então reserve um momento para entender o que eles significam:

• Fórmula: Uma combinação de funções, referências de células, valores, operadores e nomes que, quando usados ​​juntos seguindo o sinal de igual (=), criam um novo valor.
• Função: Uma fórmula predefinida que executa um cálculo usando argumentos em uma ordem específica.
• Argumento: Uma entrada que fornece informações para uma função.

Aninhando funções dentro de IF no Excel

Se você é novo nas funções aninhadas do Excel, deve começar usando a função SE:

=IF(a,b,c)

onde argumento um é um teste lógico, argumento b é o valor a ser retornado se o teste for atendido e o argumento c é o valor a ser retornado se o teste não for atendido. Para o teste lógico no argumento umvocê pode aninhar outra função.

Neste exemplo, os bônus são pagos se o lucro total da equipe (a soma dos valores nas células B2 a B6) exceder o limite (o valor na célula E1). Se o limite for atingido, você deseja que a célula E2 leia Sime se não for, você quer que leia Não. A fórmula para conseguir isso é a seguinte:

=IF(SUM(B2:B6)>=E1,"Yes","No")

Então, como isso funciona?

Ao criar ou ler fórmulas contendo funções aninhadas, o segredo é começar no centro e trabalhar para fora. No centro desta fórmula está a soma dos valores nas células B2 a B6, então é aqui que você precisa começar:

=SUM(B2:B6)

A seguir, você deseja que o Excel avalie se essa soma é igual ou maior que o valor na célula E1. Por padrão, o Excel retorna verdadeiro se a avaliação for positiva, ou FALSO se for negativo – também conhecido como valores booleanos:

=SUM(B2:B6)>=E1

Agora, em vez de retornar valores booleanos, você deseja que o Excel retorne Sim ou Não. Assim, você precisa envolver a fórmula SOMA dentro da função SE, para que o cálculo da soma seja o teste lógico (argumento um):

=IF(SUM(B2:B6)>=E1,"Yes","No")

Você não precisa usar a função SUM dentro de IF. Na verdade, você pode aninhar praticamente qualquer função como teste lógico. Aqui, a função MÉDIA calcula a média dos valores nas células B2 a F2, e se o resultado for superior a 5.000, a fórmula retorna Sim (ou Não se não for):

=IF(AVERAGE(B2:F2)>5000,"Yes","No")

Você pode então clicar duas vezes na alça de preenchimento no canto inferior direito da célula G2 para expandir a fórmula para as células restantes na coluna G.

Desta vez, a função OR abre duas opções para o teste lógico da função SE. Em outras palavras, se o valor em B2 for Encomendado ou Em trânsitoa fórmula retorna Em andamento. Caso contrário, ele retorna Concluído:

=IF(OR(B2="Ordered",B2="In transit"),"In progress","Completed")

Mais uma vez, clicar duas vezes na alça de preenchimento aplica a fórmula às linhas restantes do conjunto de dados.

Outros exemplos de aninhamento no Excel

Depois que você começa a experimentar outras funções, o poder do aninhamento rapidamente se torna claro.

Aninhando FILTER dentro de UNIQUE

Nesse caso, seu objetivo é extrair os nomes de todas as pessoas que ganharam algum prêmio em Nova York.

Em outras palavras, você deseja usar a função FILTER para reproduzir uma versão filtrada da coluna C. Para fazer isso, digite:

=FILTER(C2:C15,B2:B15=F1)

na célula E2 e pressione Enter. C2:C15 é a matriz que você deseja filtrar (nomes das pessoas) e B2:B15=F1 diz ao Excel para incluir apenas os valores onde o valor correspondente na coluna B é igual ao valor na célula F1 (Nova York).

Porém, como você pode ver na imagem acima, um dos nomes se repete porque essa pessoa ganhou duas vezes em Nova York. Então, você precisa aninhar a função FILTER dentro de UNIQUE para retornar cada nome apenas uma vez:

=UNIQUE(FILTER(C2:C15,B2:B15=F1))

A chave neste processo é forçar o Excel a realizar primeiro o cálculo principal e, em seguida, usar o resultado desta fórmula para conduzir outro.

Aninhando XMATCH dentro de INDEX

Um dos pares de funções mais populares do Excel é INDEX e XMATCH, que trabalham juntos para recuperar e extrair um valor de um conjunto de dados.

Aqui, depois de inserir o ID de um jogador na célula F2, você deseja retornar a pontuação na célula G2.

Para fazer isso, você pode usar a função INDEX:

=INDEX(a,b,c)

onde um é a matriz a ser varrida, b é o número da linha e c é o número da coluna.

Para argumento bcomo a linha muda dependendo do ID do jogador inserido na célula F2, você precisa aninhar a função XMATCH, que procura um item especificado e retorna sua posição relativa. E como a melhor abordagem para aninhar no Excel é começar com as funções aninhadas, é aqui que você deve começar. Então, na célula G2, digite:

=XMATCH(F2,A2:A6)

Quando você pressiona Enter, esta fórmula retorna corretamente 4porque o jogador D está na quarta linha do intervalo A2 a A6.

Agora, esta fórmula XMATCH pode atuar como argumento b da função INDEX, então agora você precisa agrupar o restante dos argumentos INDEX em torno de XMATCH:

=INDEX(a,XMATCH(F2,A2:A6),c)

Argumento c vai ser 4 porque você deseja retornar o valor da quarta coluna (total):

=INDEX(a,XMATCH(F2,A2:A6),4)

Então agora, tudo o que é necessário é inserir o argumento ume como esse é todo o array a ser verificado, serão as células A2 a D6:

=INDEX(A2:D6,XMATCH(F2,A2:A6),4)

Agora, quando você insere um player diferente na célula F2, o XMATCH aninhado procura por ele nas células A2 a A6 e retorna sua posição relativa para o argumento b da função ÍNDICE.

Se você nem sempre deseja retornar o total na quarta coluna, você pode dar um passo adiante e aninhar outra fórmula XMATCH para determinar o número da coluna:

=INDEX(A1:D6,XMATCH(F2,A1:A6),XMATCH(G2,A1:D1))

Neste caso, para argumentar cXMATCH pega o valor na célula G5, procura-o nas células A1 a D1 e retorna sua posição relativa.

Aninhamento MAX dentro de MÉDIA

Suponha que você tenha esta planilha, que contém as pontuações de cinco jogadores em três jogos, e seu objetivo seja encontrar a média das pontuações mais altas em cada um.

Em outras palavras, primeiro você precisa do Excel para calcular as pontuações máximas nas células B2 a B6, C2 a C6 e D2 a D6, respectivamente, antes de calcular a média do resultado. Portanto, a função MAX precisa ser aninhada dentro da função AVERAGE.

Primeiro, configure os três aninhamentos MAX, tendo em mente que os argumentos para a função AVERAGE são separados por vírgula:

MAX(B2:B6),MAX(C2:C6),MAX(D2:D6)

Agora, com o modo Enter ainda ativado, envolva a função AVERAGE em torno destas fórmulas MAX aninhadas:

=AVERAGE(MAX(B2:B6),MAX(C2:C6),MAX(D2:D6))

Funções de aninhamento dentro de si

Até agora, todos os exemplos aninharam uma função dentro de outra. No entanto, às vezes, pode ser necessário aninhar uma função dentro dela mesma. Confuso? Deixe-me mostrar como isso funciona usando a função SE.

Nesta planilha, o bônus de um funcionário é determinado pelo lucro que ele obtém. Especificamente, lucros acima de US$ 25.000 resultam em um bônus de 10%, lucros acima de US$ 20.000 retornam um bônus de 5%, lucros acima de US$ 10.000 geram um bônus de 2% e lucros acima de US$ 10.000 resultam em um bônus de 1%.

Nesse caso, você precisa aninhar o IF para criar um sistema de avaliação em camadas, do mais alto para o mais baixo. Isso ocorre porque as funções IF aninhadas são avaliadas da esquerda para a direita, parando na primeira condição que for verdadeira. Então, na célula C2, digite:

=IF(B2>25000,10%

mas não pressione Enter ainda. Esta primeira seção diz ao Excel para avaliar a célula B2 e se for maior que US$ 25.000 (em outras palavras, se avaliar o teste lógico como verdadeiro), retornar 10%. No entanto, você precisa informar ao Excel o que fazer se retornar FALSO. Nesse caso, você deseja verificar se o valor na célula B2 é maior que o valor do segundo nível, US$ 20.000, retornando 5% em caso afirmativo:

=IF(B2>25000,10%,IF(B2>20000,5%

Se esta função IF aninhada retornar FALSE, você deseja que o Excel passe para a próxima camada, então você deve aninhar outra função IF:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%

Agora, você precisa criar o nível mais baixo por meio de outra função IF aninhada:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%

Como não há mais camadas, você precisa informar ao Excel o que retornar se nenhuma das funções IF aninhadas for avaliada como TRUE. Neste caso, você deseja retornar 0%:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%,0%

Agora que todas as camadas foram criadas por meio de funções IF aninhadas, você pode fechar os parênteses. Existem quatro parênteses de abertura, então você precisa digitar quatro parênteses de fechamento antes de pressionar Enter:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%,0%))))

Agora você pode clicar duas vezes na alça de preenchimento para aplicar a fórmula às células restantes na coluna C.

O aninhamento de funções no Excel permite que você aproveite os recursos de mais de uma função ao mesmo tempo. Quanto mais você aproveitar ao máximo essa estrutura de fórmula, mais aprenderá quais combinações de funções funcionam bem para ajudar a acelerar o fluxo de trabalho da planilha.