Как отбросить наименьшую оценку и рассчитать среднее значение или сумму остальных значений в Excel?
При работе со списком оценок или баллов в Excel вам может понадобиться рассчитать итоговую оценку студента, исключив его самый низкий результат или даже *n* самых низких результатов перед вычислением среднего значения или суммы оставшихся значений. Это распространённая практика в образовательной сфере, где студентам разрешают не учитывать худшие результаты — чтобы сгладить случайные сбои или обеспечить большую справедливость. Выполнение такой операции вручную быстро становится утомительным, особенно при работе с большими объёмами данных или при частой корректировке расчётов. К счастью, Excel предлагает несколько гибких решений: от простых формул до автоматизации с помощью VBA для пакетной обработки.
Отбросьте наименьшую оценку и получите среднее значение или сумму с помощью формул
Отбросьте наименьшую оценку и получите среднее значение или сумму с помощью формул
Если вы хотите исключить наименьшее или *n* наименьших значений из строки данных или списка, а затем выполнить расчёты — например, найти среднее значение или сумму оставшихся чисел, — встроенные формулы Excel обеспечивают практичное и гибкое решение. Такой подход особенно удобен при работе с умеренным объёмом данных или когда вы цените прозрачность и простоту настройки, которые дают формулы.
Ниже приведены отдельные пояснения методов суммирования и вычисления среднего значения, включая подробные инструкции и практические советы для их гибкого применения.
Просуммируйте числа, но отбросьте наименьшее или N наименьших чисел:
Чтобы вычислить сумму для каждой строки или списка с исключением наименьшего значения, используйте следующий метод:
1. Выберите пустую ячейку, в которую хотите поместить сумму по первой строке (например, I2, если ваши данные находятся в диапазоне B2:H2), и введите следующую формулу:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Нажмите клавишу Enter, чтобы подтвердить ввод, затем перетащите маркер заполнения вниз — так формула автоматически применится ко всем нужным строкам. В результате в каждой строке будут просуммированы все числа, за исключением наименьшего значения.
Скриншот для справки:
Примечания и советы:
- Чтобы исключить два, три или даже больше наименьших значений, просто расширьте формулу, вычитая дополнительные результаты функции SMALL. Например:
=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)- В этих формулах B2:H2 — это диапазон, который вы хотите просуммировать, а числа 1, 2, 3 и т.д. указывают, сколько наименьших значений следует исключить. Настройте значение n в зависимости от количества наименьших оценок, которые вы хотите отбросить.
- Будьте внимательны: не устанавливайте значение n больше или равным общему количеству значений — в противном случае вы получите ошибку или нежелательные результаты.
- Эти формулы работают независимо для каждой строки. Если ваши данные организованы по столбцам, а не по строкам, скорректируйте диапазоны соответствующим образом.
- Если в вашем наборе данных есть дубликаты наименьшего числа, функция SMALL(B2:H2,1) исключит только одно вхождение за раз. Чтобы исключить несколько вхождений, повторите функцию SMALL, увеличивая значение k, как показано выше.
Вычислите среднее значение чисел, но отбросьте наименьшее или N наименьших чисел:
Чтобы вычислить среднее значение, исключив наименьшее или n наименьших значений, воспользуйтесь приведёнными ниже формулами. Этот приём особенно полезен в системах оценивания, где низкие выбросы не должны влиять на итоговое среднее.
1. Выберите ячейку для отображения среднего значения (например, J2, если ваши оценки находятся в диапазоне B2:H2) и введите формулу:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. После нажатия клавиши Enter перетащите формулу вниз столько, сколько нужно, чтобы рассчитать среднее значение для дополнительных строк, каждый раз исключая наименьшую оценку из диапазона соответствующей строки.
Примечания и важные рекомендации:
- Чтобы вычислить среднее значение с отбрасыванием более чем одной наименьшей оценки, расширьте формулу, вычитая дополнительные члены SMALLи соответственно уменьшая делитель:
=(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)- Напоминаем ещё раз: B2:H2 — это диапазон для вычисления среднего значения, а n указывает, сколько наименьших значений будет исключено из расчёта.
- Если вы попытаетесь исключить из расчёта больше чисел, чем содержится в диапазоне, формулы вернут ошибку #ЧИСЛО!, сигнализирующую о недостаточном количестве значений для вычисления среднего. Всегда убедитесь, что значение n меньше общего количества чисел.
- Перед исключением наименьших значений обязательно убедитесь, что они не являются критически важными или необходимыми для вашего расчёта — это может повлиять на окончательный результат.
- Для очень больших наборов данных или динамического отбрасывания n наименьших значений рекомендуем использовать автоматизированные решения или формулы с массивами.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Код VBA — отбросьте наименьшую или n наименьших оценок и автоматически вычислите сумму или среднее значение
В ситуациях с большими или часто обновляемыми наборами данных, а также при необходимости автоматизировать отбрасывание n наименьших значений и расчёт сумм или средних по множеству строк, VBA существенно упрощает рутинные задачи. С помощью макроса вы задаёте диапазон данных и количество наименьших значений, подлежащих исключению, — и код мгновенно обрабатывает все выбранные строки за один проход.
Такой автоматизированный подход особенно полезен преподавателям, управляющим таблицами нескольких классов, а также всем, кто стремится свести к минимуму ручной ввод формул и возможные ошибки. Представленное ниже решение позволяет легко настраивать как количество исключаемых оценок, так и целевую функцию — сумму или среднее значение.
Прежде чем начать, обязательно сохраните книгу — макросы нельзя отменить напрямую.
1. Щёлкните Разработчик > Visual Basic. В окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль и введите следующий код:
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. После добавления кода нажмите кнопку 
или клавишу F5, чтобы выполнить его.
3. Следуйте появившимся подсказкам:
- Выберите диапазон оценок для обработки (убедитесь, что оценки каждого студента расположены в одной строке).
- Выберите верхнюю левую ячейку в области размещения списка (результаты будут заполняться вниз в зависимости от количества строк).
- Укажите количество самых низких оценок, которые нужно отбросить (например,)1, чтобы исключить только одну самую низкую оценку в каждой строке).
- Введите SUM, чтобы получить сумму (исключая отброшенные оценки), или AVG, чтобы получить пересчитанное среднее (исключая отброшенные оценки).
Макрос обрабатывает каждую строку из указанной области оценок и размещает в вашей Области размещения списка либо сумму, либо среднее значение (в зависимости от вашего выбора). Если все оценки в строке исключаются, результат помечается как Н/Д, чтобы избежать ошибок.
- Убедитесь, что структура входного диапазона соответствует вашим данным: оценки одного студента должны находиться в одной строке.
- Нечисловые ячейки (например, пустые или содержащие текст) по умолчанию игнорируются.
- Этот код VBA значительно ускоряет повторяющиеся расчёты оценок для целых классов и обеспечивает гибкую настройку количества отбрасываемых оценок.
- Если вы часто выполняете подобные операции, мы рекомендуем назначить этот макрос кнопке на листе — так вы получите ещё более быстрый доступ!
Если возникают проблемы, такие как некорректные результаты или ошибки, дважды проверьте правильность указания диапазонов и убедитесь, что значение «n» не Больше или равно общего количества доступных оценок в каждой строке.
Для аналогичных задач автоматизации, например отбрасывания как наивысших, так и наименьших оценок или обработки столбцов вместо строк, можно внести небольшие изменения в логику кода VBA.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек