Uma das coisas mais interessantes no Excel, é saber como achar o maior valor de um item repetido na planilha. Quando trabalhamos com Excel temos várias possibilidades de obter dados e referências de dados. E para começar, agora eu quero achar o maior valor de um item repetido na planilha. Isso é importante, por exemplo, em uma planilha de vendas na qual precisamos saber qual foi o maior valor de venda um determinado item que foi vendido, mas este mesmo produto obteve 5 vendas e estas vendas estão lançadas na planilha.
Isso é possível se usarmos a função SOMARPRODUTO em conjunto com a função MAIOR e buscar a referência de uma determinada célula. Também pode-se usar a função MAIOR com a função SE.
Eu estive lendo em vários blogs e achei várias fórmulas interessantes, mas elas apenas encontram os maiores e menores valores em um intervalo de células. Eu tinha que encontrar o maior valor de um dado repetido, que na minha planilha específica seria o preço mais alto de venda de um produto onde o cliente fez várias vendas.
Quer saber? Eu juntei duas funções, testei com sucesso e quero compartilhar com você. Siga lendo!
É por isso que eu vou mostrar também como encontrar e somar o valor total de determinado item repetido em um intervalo de células. E finalmente vou mostrar quantas vendas este mesmo item obteve. Então, vamos achar o maior valor de um item repetido na planilha.
Encontrando o maior valor de um item repetido em um intervalo de células
Vamos continuar trabalhando sobre um exemplo de uma planilha de vendas. Neste exemplo, obterei o maior ou o segundo maior ou o enésimo maior valor com base no produto da linha X da mesma planilha, basta fazer o seguinte:
=SOMARPRODUTO(MAIOR(($A$3:$C$14=E3)*($B$3:$B$14);1))
O intervalo de células que eu quero calcular está entre A3 e C14. Note que algumas referências da fórmula estão entre o sinal $ - cifrão. E por que isso? Simplesmente para copiar as mesmas referências sem alterá-las.
Você ainda pode usar outra fórmula para encontrar o maior valor de um item ou de um dado repetido em um intervalo de células.
Use as funções MAIOR e SE aninhadas para o mesmo exemplo da planilha e encontre o mesmo valor. A diferença é que com a função SOMAPRODUTO é possível acrescentar mais critérios.
=MAIOR(($A$3:$C$14=E3)*($B$3:$B$14);1)
Você pode copiar o exemplo da tabela e colar na célula A1 de uma nova planilha. Pode ser necessário ajustar as células e colunas para achar o maior valor do item.
| Venda de produtos | Valor mais alto de venda de cada produto | |||
|---|---|---|---|---|
| Nome do Produto | Preço | Data | Produto | Maior Valor de Venda |
| Água Mineral de 500ML | R$ 2,00 | 11/jul | Água Mineral de 500ML | =SOMARPRODUTO(MAIOR(($A$3:$C$14=E3)*($B$3:$B$14);1)) |
| Salgadinho pacote 100G | R$ 3,00 | 12/jul | Salgadinho pacote 100G | =SOMARPRODUTO(MAIOR(($A$3:$C$14=E4)*($B$3:$B$14);1)) |
| Biscoito | R$ 5,00 | 13/jul | Biscoito | =SOMARPRODUTO(MAIOR(($A$3:$C$14=E5)*($B$3:$B$14);1)) |
| Farinha de Milho 1KG | R$ 2,00 | 14/jul | Farinha de Milho 1KG | =SOMARPRODUTO(MAIOR(($A$3:$C$14=E6)*($B$3:$B$14);1)) |
| Água Mineral de 500ML | R$ 2,20 | 15/jul | ||
| Biscoito | R$ 5,00 | 16/jul | Soma total de cada produto vendido | |
| Biscoito | R$ 4,00 | 17/jul | Produto | Maior Valor de Venda |
| Salgadinho pacote 100G | R$ 3,50 | 18/jul | Água Mineral de 500ML | =SOMASE($A$3:$C$14;E10;$B$3:$B$14) |
| Farinha de Milho 1KG | R$ 2,00 | 19/jul | Salgadinho pacote 100G | =SOMASE($A$3:$C$14;E11;$B$3:$B$14) |
| Água Mineral de 500ML | R$ 1,90 | 20/jul | Biscoito | =SOMASE($A$3:$C$14;E12;$B$3:$B$14) |
| Biscoito | R$ 4,00 | 21/jul | Farinha de Milho 1KG | =SOMASE($A$3:$C$14;E13;$B$3:$B$14) |
| Salgadinho pacote 100G | R$ 3,00 | 22/jul |
Se você copiar este exemplo e colar no Excel, não esqueça de ajustar as fórmulas excluindo o eventual sinal apóstrofo antes do sinal de igual.
Vamos somar valores totais agora.
Somar o total de valores de um item repetido dentro do intervalo de células
Use o mesmo exemplo da planilha para calcular o valor total de vendas de um mesmo produto em um intervalo de células.
=SOMASE($A$3:$C$14;E10;$B$3:$B$14)
Por exemplo: essa fórmula busca apenas o item de referência, quantas vezes ele apareceu na planilha e soma os valores referentes a ele. Fantástico, não é?
Somar quantas vezes o mesmo item está sendo exibido
Já sabemos como achar o maior valor de um item repetido na planilha, ou em um intervalo de células. Agora é a hora de saber quantas vezes este item se repetiu. Em nosso exemplo, queremos saber quantas vezes ele foi vendido.
=CONT.SE($A$3:$A$14;E10)
Esta última dica é um bônus e não aparecerá no exemplo da planilha acima. No entanto, você pode copiar a fórmula e colar em qualquer lugar da planilha e vai obter o resultado correto do total das vendas do item referido.
E ainda pode criar uma formatação condicional para destacar.
Veja a planilha de Estoque e vendas com estes recursos.
É possível baixar a versão demonstrativa para testar.
| Compre Aqui o seu pacote do Microsoft 365 - com Excel, Word, PowerPoint e outros programas. |
| Escolha sua versão: Microsoft 365 Family, Microsoft 365 Personal, Office 365 Home, ou Office Professional Plus. |
