Como descartar a nota mais baixa e obter a média ou o total de valores no Excel?
Ao trabalhar com uma lista de notas ou pontuações no Excel, você pode precisar calcular a nota final de um aluno excluindo sua pontuação mais baixa, ou até mesmo as n pontuações mais baixas, antes de calcular a média ou somar os valores restantes. Essa é uma exigência comum em ambientes educacionais, onde os alunos podem ter permissão para descartar seus piores desempenhos para considerar anomalias ou garantir justiça. Realizar essa operação manualmente pode ser tedioso, especialmente com grandes conjuntos de dados ou ajustes frequentes de cálculos. Felizmente, o Excel oferece várias maneiras flexíveis de alcançar isso, desde fórmulas simples até automação com VBA para operações em lote.
Descartar a nota mais baixa e obter a média ou soma com fórmulas
Descartar a nota mais baixa e obter a média ou soma com fórmulas
Se você deseja excluir o valor mais baixo ou os n valores mais baixos de uma linha ou lista de dados e depois realizar cálculos — como média ou soma dos números restantes — as fórmulas embutidas do Excel oferecem uma abordagem prática. Essas soluções de fórmula são especialmente úteis se você tiver um número moderado de linhas para processar ou preferir uma abordagem baseada em fórmulas para maior transparência e facilidade de ajuste.
A seguir, você encontrará explicações separadas para os métodos de soma e média, incluindo instruções detalhadas e dicas práticas para aplicação flexível.
Somar os números, mas descartar o menor ou os menores N números:
Para calcular a soma de cada linha ou lista enquanto omite o valor mais baixo, use o seguinte método:
1. Selecione uma célula em branco onde deseja que o resultado da soma da primeira linha apareça (por exemplo, na célula I2 se seus dados estiverem em B2:H2), depois insira a seguinte fórmula:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Pressione Enter para confirmar, então arraste a alça de preenchimento para baixo para aplicar essa fórmula a outras linhas conforme necessário. Isso somará todos os números em cada linha, excluindo o valor mais baixo.
Aqui está uma captura de tela para referência:
Notas e dicas:
- Para excluir os dois, três ou mais valores mais baixos, você pode estender a fórmula subtraindo resultados adicionais da função MENOR. Por exemplo:
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- Nessas fórmulas, B2:H2 é o intervalo que você deseja somar, e os números 1, 2, 3, etc., especificam os n menores números a excluir. Ajuste n com base em quantas das notas mais baixas você deseja descartar.
- Tenha cuidado para não definir n maior ou igual ao número total de valores; caso contrário, você encontrará erros ou resultados indesejados.
- Essas fórmulas funcionam de forma independente para linhas. Se seus dados se estendem por colunas em vez de linhas, ajuste os intervalos de acordo.
- Se seu conjunto de dados contém duplicatas do menor número, MENOR(B2:H2,1) descartará apenas uma ocorrência por referência. Para descartar múltiplas ocorrências, repita o termo MENOR com números k incrementados conforme mostrado acima.
Tirar a média dos números, mas descartar o menor ou os menores N números:
Para calcular a média ignorando o menor ou os n menores valores, você pode usar as fórmulas abaixo. Esse cálculo é especialmente útil em esquemas de avaliação onde desempenhos muito baixos não devem ser considerados na média.
1. Selecione uma célula para o resultado da média (por exemplo, J2 se suas pontuações estiverem em B2:H2) e insira a fórmula:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Após pressionar Enter, arraste a fórmula para baixo conforme necessário para calcular a média de linhas adicionais, omitindo sempre a pontuação mais baixa no intervalo de cada linha.
Notas e orientações importantes:
- Para calcular a média enquanto descarta mais de uma pontuação mais baixa, expanda a fórmula subtraindo termos adicionais MENOR e reduzindo o divisor de acordo:
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- Novamente, B2:H2 é o intervalo para calcular a média, e n representa quantos dos menores valores serão excluídos do cálculo.
- Se você tentar subtrair mais números do que existem no intervalo, as fórmulas retornarão um erro #NÚM!, indicando valores insuficientes para calcular a média. Sempre garanta que n seja menor que a contagem de números.
- É recomendável verificar duas vezes se os valores mais baixos não são críticos ou necessários para o seu cálculo antes de excluí-los, pois isso pode afetar os resultados finais.
- Para conjuntos de dados extremamente grandes ou exclusão dinâmica das n notas mais baixas, considere uma solução automatizada ou baseada em matriz.
Desbloqueie a Magia do Excel com o Kutools AI
- Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
- Codificação VBA: Escreva e implemente código VBA sem esforço.
- Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
- Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.
Código VBA - Descartar a nota mais baixa ou as n notas mais baixas e calcular a soma ou média automaticamente
Para situações envolvendo grandes conjuntos de dados ou dados que mudam frequentemente, ou onde você precisa automatizar a exclusão das n notas mais baixas e calcular somas ou médias em muitas linhas, o uso de VBA pode simplificar muito o trabalho repetitivo. Com uma macro VBA, você pode especificar o intervalo de dados e quantas notas mais baixas deseja excluir, e o código processará todas as linhas selecionadas de forma eficiente em uma única etapa.
Essa abordagem automática é especialmente útil para professores gerenciando planilhas para várias turmas, ou qualquer pessoa que deseje minimizar a entrada manual de fórmulas e possíveis erros. A solução abaixo permite que você ajuste facilmente tanto o número de exclusões quanto a função-alvo (soma ou média).
Antes de começar, salve sua pasta de trabalho, pois a execução de macros não pode ser desfeita diretamente.
1. Clique em Desenvolvedor > Visual Basic. Na janela Microsoft Visual Basic for Applications, clique em Inserir > Módulo e, em seguida, insira o seguinte código:
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. Após adicionar o código, clique no 
botão ou pressione F5 para executar.
3. Siga os prompts que aparecerão:
- Selecione o intervalo de notas que deseja processar (certifique-se de que as notas de cada aluno estejam em uma linha).
- Escolha a célula superior esquerda do intervalo de saída (a saída será preenchida para baixo com base na contagem de linhas).
- Insira o número de notas mais baixas para descartar (por exemplo, 1 para excluir apenas a nota mais baixa em cada linha).
- Digite SOMA para obter o total (excluindo as notas descartadas) ou MÉDIA para obter a média recalculada (excluindo as notas descartadas).
A macro processa cada linha da área de notas especificada e coloca a soma ou média (conforme escolhido) no intervalo de saída. Se todas as notas forem excluídas em uma linha, o resultado será marcado como N/D para evitar erros.
- Certifique-se de que o intervalo de entrada corresponda à estrutura dos seus dados (as notas de um aluno por linha).
- Células não numéricas (por exemplo, em branco ou texto) serão ignoradas por padrão.
- Esse código VBA acelera enormemente os cálculos de notas repetitivos para turmas inteiras e suporta ajustes flexíveis no número de notas a descartar.
- Se você realiza essas operações frequentemente, pode atribuir essa macro a um botão na sua planilha para acesso ainda mais rápido.
Se você encontrar problemas como saídas incorretas ou erros, verifique novamente se os intervalos estão especificados corretamente e se "n" não é maior ou igual ao número total de notas disponíveis para cada linha.
Para requisitos semelhantes de automação, como descartar as notas mais altas e mais baixas ou lidar com colunas em vez de linhas, pequenos ajustes podem ser feitos na lógica do código VBA.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins