Maiores e menores com LARGE e SMALL

MAX fornece o maior valor individual, e MIN, o menor. Mas e se você quiser o 2º maior ou o 3º menor?

É exatamente isso que LARGE e SMALL fazem. Elas permitem obter o enésimo maior ou o enésimo menor valor de um intervalo, o que constitui a base dos relatórios dos N maiores e dos N menores valores.

LARGE(range, k) retorna o k-ésimo maior valor. O segundo argumento, k, escolhe a posição: 1 é o maior, 2 é o segundo maior e assim por diante.

Assim, LARGE(A2:A20,1) equivale a MAX(A2:A20), enquanto LARGE(A2:A20,2) fornece o segundo colocado.

SMALL(range, k) funciona como LARGE, mas de baixo para cima. SMALL(A2:A20,1) equivale a MIN(A2:A20), e SMALL(A2:A20,3) retorna o terceiro menor valor.

Use SMALL para destacar os piores desempenhos, as opções mais baratas ou as datas mais antigas de um conjunto de dados.

Para listar os três maiores valores, use três fórmulas LARGE com k = 1, 2 e 3. Uma abordagem mais prática é fazer referência a um número auxiliar, para que você possa preencher a fórmula para baixo.

Se a célula D2 contiver 1, D3 contiver 2 e D4 contiver 3, esta única fórmula, preenchida para baixo, produzirá a lista completa dos três maiores valores.

Você pode gerar k automaticamente com a função ROW, portanto não precisa de uma coluna auxiliar. ROW()-1 produz 1 na primeira linha, 2 na seguinte e assim por diante.

Ao preenchê-la para baixo, ela cria uma lista ordenada sem nenhuma configuração adicional. Ajuste o deslocamento para corresponder ao ponto em que sua lista começa.

No Excel e no Google Sheets modernos, você pode gerar uma lista completa dos cinco maiores valores em uma única fórmula, usando SEQUENCE como argumento k. SEQUENCE(5) produz 1, 2, 3, 4, 5.

LARGE então retorna cinco valores de uma vez, expandindo-os para baixo automaticamente. Uma fórmula, uma lista completa ordenada.

Uma pergunta comum nos negócios é: qual é a contribuição dos meus três principais clientes? Envolva LARGE em SUM com uma SEQUENCE (ou uma constante de matriz) para totalizar os maiores valores.

A fórmula abaixo soma os três maiores valores do intervalo, fornecendo a contribuição combinada dos três maiores em uma única célula.

Se k for menor que 1 ou maior que a quantidade de números no intervalo, LARGE e SMALL retornarão o erro #NUM!.

Por exemplo, solicitar o 30º maior valor em uma lista com apenas 19 números falha. Proteja seus relatórios contra isso envolvendo a chamada com IFERROR.

Um valor sozinho é menos útil do que saber quem o obteve. Combine LARGE com INDEX e MATCH para buscar o nome associado ao maior valor.

Isso encontra o maior valor de vendas, localiza sua linha e retorna o nome correspondente da coluna A, para que seu relatório mostre "Vendedor líder: Maria."

Assim como as outras funções estatísticas, LARGE e SMALL contam apenas números. Células de texto e vazias no intervalo são ignoradas, portanto não afetam qual valor é o enésimo maior.

Isso significa que você pode apontá-las com segurança para uma coluna que inclua uma linha de cabeçalho. Apenas fique atento a números armazenados acidentalmente como texto, pois eles são ignorados e podem alterar o valor retornado por LARGE ou SMALL.

Você tem vendas mensais em B2:B20. A gerência quer os três melhores e os três piores meses.

• Melhores três: =LARGE($B$2:$B$20,{1,2,3})
• Piores três: =SMALL($B$2:$B$20,{1,2,3})

Coloque cada fórmula em uma célula que possa expandi-la, e você terá instantaneamente um panorama dos melhores e piores resultados alimentando o painel.

Teste sua compreensão de LARGE e SMALL.

Agora você pode ir além de MAX e MIN:

• LARGE(range,k) retorna o k-ésimo maior; SMALL(range,k), o k-ésimo menor.
• k = 1 corresponde a MAX / MIN; aumente k para obter os valores seguintes.
• Passe SEQUENCE ou uma matriz como {1,2,3} para criar ou somar uma lista dos N maiores com uma única fórmula.
• Associe a INDEX-MATCH para rotular o valor e envolva-o em IFERROR para lidar com k fora do intervalo.