Как удалить самую низкую оценку и получить среднее значение или сумму значений в Excel?
При работе со списком оценок или баллов в Excel вам может понадобиться рассчитать итоговую оценку студента, исключив его самый низкий балл, а возможно, и несколько самых низких баллов, прежде чем усреднять или суммировать оставшиеся значения. Это общее требование в образовательных учреждениях, где студентам может быть разрешено не учитывать свои худшие результаты для учета аномалий или обеспечения справедливости. Выполнение этой операции вручную может быть утомительным, особенно при работе с большими наборами данных или частыми изменениями расчетов. К счастью, Excel предоставляет несколько гибких способов достижения этой цели — от простых формул до автоматизации с помощью VBA для пакетных операций.
Исключение самой низкой оценки и получение среднего значения или суммы с использованием формул
Исключение самой низкой оценки и получение среднего значения или суммы с использованием формул
Если вы хотите исключить самое низкое или несколько низких значений из строки или списка данных, а затем выполнить вычисления, такие как усреднение или суммирование оставшихся чисел, встроенные формулы Excel предлагают практичный подход. Эти решения на основе формул особенно полезны, если у вас есть умеренное количество строк для обработки или вы предпочитаете использовать формулы для прозрачности и удобства корректировки.
Ниже вы найдете отдельные объяснения методов суммирования и усреднения, включая подробные инструкции и практические советы для гибкого применения.
Суммирование чисел, но исключение низшего или нескольких низших значений:
Чтобы вычислить сумму для каждой строки или списка, игнорируя самое низкое значение, используйте следующий метод:
1. Выберите пустую ячейку, где должен появиться результат суммы для первой строки (например, в ячейке I2, если ваши данные находятся в диапазоне B2:H2), затем введите следующую формулу:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Нажмите Enter для подтверждения, затем перетащите маркер заполнения вниз, чтобы применить эту формулу к другим строкам по мере необходимости. Это просуммирует все числа в каждой строке, исключая самое низкое значение.
Вот скриншот для справки:
Примечания и советы:
- Чтобы исключить два, три или более низших значения, можно расширить формулу, вычитая дополнительные результаты функции НАИМЕНЬШИЙ. Например:
=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 в зависимости от того, сколько низких оценок вы хотите исключить.
- Будьте осторожны, чтобы не установить n больше или равным общему количеству значений; иначе вы столкнетесь с ошибками или нежелательными результатами.
- Эти формулы работают независимо для строк. Если ваши данные охватывают столбцы вместо строк, соответственно скорректируйте диапазоны.
- Если ваш набор данных содержит дубликаты самого низкого числа, НАИМЕНЬШИЙ(B2:H2,1) будет исключать только одно вхождение за ссылку. Чтобы исключить несколько вхождений, повторите термин НАИМЕНЬШИЙ с увеличенными значениями k, как показано выше.
Усреднение чисел, но исключение низшего или нескольких низших значений:
Чтобы рассчитать среднее значение, игнорируя низшее или несколько низших значений, вы можете использовать формулы ниже. Этот расчет особенно полезен в системах оценивания, где выбросы низких результатов не учитываются при усреднении.
1. Выберите ячейку для результата среднего значения (например, J2, если ваши оценки находятся в диапазоне B2:H2), и введите формулу:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. После нажатия Enter перетащите формулу вниз по мере необходимости, чтобы усреднить дополнительные строки, каждый раз исключая самую низкую оценку в диапазоне для этой строки.
Примечания и важные рекомендации:
- Чтобы усреднить, исключая более одного низкого результата, расширьте формулу, вычитая дополнительные термины НАИМЕНЬШИЙ и соответственно уменьшая делитель:
=(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 самых низких оценок и вычисление сумм или средних значений по множеству строк, использование VBA может значительно упростить повторяющиеся задачи. С помощью макроса VBA вы можете указать диапазон данных и сколько самых низких оценок исключить, и код обработает все выбранные строки эффективно за один шаг.
Этот автоматический подход особенно полезен для преподавателей, управляющих таблицами для нескольких классов, или для тех, кто хочет минимизировать ручной ввод формул и потенциальные ошибки. Решение ниже позволяет вам легко настраивать как число исключений, так и целевую функцию (сумму или среднее значение).
Перед началом, пожалуйста, сохраните свою книгу, так как выполнение макросов нельзя отменить напрямую.
1. Нажмите Разработчик > Visual Basic. В окне Microsoft Visual Basic for Applications нажмите Вставить > Модуль, а затем введите следующий код:
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, чтобы получить пересчитанное среднее значение (исключая удаленные оценки).
Макрос обрабатывает каждую строку из указанного диапазона оценок и помещает либо сумму, либо среднее значение (в зависимости от выбора) в диапазон вывода. Если все оценки удалены в строке, результат помечается как N/A, чтобы избежать ошибок.
- Убедитесь, что входной диапазон соответствует структуре ваших данных (оценки одного студента в строке).
- Нечисловые ячейки (например, пустые или текстовые) будут игнорироваться по умолчанию.
- Этот код 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек