Как усреднить каждые 5 строк или столбцов в Excel?
При работе с большими наборами данных в Excel часто требуется выполнять вычисления средних значений для каждой группы строк или столбцов, например, каждых 5 строк или каждых 5 столбцов. Хотя можно вручную вставлять формулы, такие как =AVERAGE(A1:A5), =AVERAGE(A6:A10), =AVERAGE(A11:A15) и так далее, это быстро становится непрактичным, если ваш список содержит сотни или тысячи ячеек. Повторение этих операций вручную занимает много времени и подвержено ошибкам. К счастью, Excel предлагает несколько способов автоматизировать эту задачу, что делает анализ данных намного эффективнее и менее утомительным. В этой статье будут представлены несколько практических методов расчета среднего значения каждых 5 строк или столбцов, включая подходы на основе формул, надстройки Excel, автоматизацию с помощью VBA и техники сводных таблиц, чтобы помочь вам выбрать лучшее решение для вашего сценария.
Усреднение каждых 5 строк или столбцов с помощью формул
Усреднение каждых 5 строк с помощью Kutools для Excel
Усреднение каждых 5 строк или столбцов с помощью кода VBA
Усреднение каждых 5 строк с помощью сводной таблицы
Усреднение каждых 5 строк или столбцов с помощью формул
Если вы предпочитаете использовать стандартные формулы Excel, вы можете автоматизировать расчет для каждых 5 строк или столбцов без необходимости использования надстроек или скриптов. Этот подход особенно подходит для статических наборов данных, где вам просто нужно сгенерировать наборы средних значений для поддержки анализа. Однако следует быть внимательным при правильном указании ссылок на данные и обработке любых пустых или нерегулярных интервалов.
В следующем примере показано, как рассчитать среднее значение каждых 5 строк в столбце:
1. Введите следующую формулу в первую ячейку, где вы хотите получить результат (например, C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Здесь A2 — это начальная ячейка вашего столбца данных, C2 — это ячейка вывода формулы, а 5 — это интервал (количество строк для усреднения). Обязательно скорректируйте эти ссылки в соответствии с вашим фактическим набором данных.
После ввода формулы нажмите Enter. Первое усредненное значение будет отображено. См. скриншот:
2. Выберите ячейку с формулой, затем перетащите маркер заполнения вниз до появления значения ошибки (например, #DIV/0!, если осталось меньше 5 значений). Это автоматически извлечет средние значения для каждой группы из 5 строк. См. скриншот:
Советы и примечания: Вы можете использовать функции обработки ошибок, такие как IFERROR(), чтобы подавить значения ошибок, если ваши данные не делятся на идеально равные группы, например:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Чтобы вычислить среднее значение каждых 5 столбцов в строке, примените следующую формулу (разместите в A3 и протяните вправо):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Здесь A1 — это начальная ячейка, A3 — это ячейка вывода формулы, а 5 — количество столбцов в каждой группе. Настройте ссылки на ячейки в зависимости от макета ваших данных.
После ввода формулы и нажатия Enter, перетащите маркер заполнения вправо до появления значения ошибки. См. скриншот:
Этот метод на основе формул идеален для быстрых разовых расчетов или когда вы не хотите использовать дополнительные инструменты. Однако, при изменении размера или формы ваших данных может потребоваться корректировка формул или ручное обновление диапазонов ячеек, а обработка неполных групп может потребовать дополнительной осторожности.
Усреднение каждых 5 строк с помощью Kutools для Excel
Kutools для Excel предоставляет удобное графическое решение, если вам часто нужно усреднять группы строк без управления сложными формулами. Через функции «Вставить разрыв страницы через каждую другую строку» и «Статистика страницы данных», вы можете быстро разделить свои данные и рассчитать партию средних значений всего за несколько кликов. Этот метод особенно полезен, если вы хотите применять усреднение через повторяющиеся интервалы и визуализировать группировку непосредственно на листе.
После загрузки и установки Kutools для Excel выполните следующие шаги:
1. Нажмите Kutools Plus > Печать > Вставить разрыв страницы через каждую другую строку. См. скриншот:
2. В диалоговом окне «Вставить разрыв страницы через каждую другую строку» укажите интервал (например, 5), чтобы вставить разрыв страницы после каждых 5 строк. Это позволит Kutools автоматически сегментировать ваши данные. См. скриншот:
3. Далее нажмите Kutools Plus > Печать > Статистика страницы данных. См. скриншот:
4. В диалоговом окне «Статистика страницы данных» выберите столбец(ы), которые вы хотите усреднять, затем выберите Среднее как метод расчета. См. скриншот:
5. Нажмите OK, и Kutools мгновенно вставит строки итогов со средними значениями через каждые 5 строк. См. скриншот:
Загрузите и попробуйте Kutools для Excel прямо сейчас бесплатно!
Kutools делает повторяющуюся группировку данных и анализ легкими для завершения без необходимости корректировки формул или написания скриптов. Однако учтите, что вставленные разрывы страниц могут повлиять на макет печати и виды, поэтому вы можете захотеть удалить их после использования, если они не нужны для вашего отчета.
Усреднение каждых 5 строк или столбцов с помощью кода VBA
Если вам нужно многократно вычислять среднее значение для фиксированного количества строк или столбцов в больших или постоянно меняющихся наборах данных, автоматизация этого процесса с помощью VBA может сэкономить значительное количество ручного труда. VBA позволяет вам циклически проходить через ваши данные, группировать их по необходимости и выводить средний результат для каждой партии. Этот метод особенно подходит для продвинутых пользователей или тех, кто работает с динамическими блоками данных, и избегает захламления рабочего листа формулами. Ниже приведен универсальный макрос VBA, который вы легко можете адаптировать.
Автоматизируйте усреднение каждых 5 строк:
1. Нажмите Разработчик > Visual Basic, чтобы открыть окно Microsoft Visual Basic for Applications. Затем нажмите Вставка > Модуль и вставьте приведенный ниже код в модуль:
Sub AverageEvery5Rows()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Integer, i As Integer, j As Integer
Dim LastRow As Long, StartRow As Long
Dim SumValue As Double, CountValue As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
LastRow = DataRange.Rows.Count
StartRow = 1
i = 0
Do While StartRow <= LastRow
SumValue = 0
CountValue = 0
For j = 0 To GroupSize - 1
If (StartRow + j) <= LastRow Then
SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
CountValue = CountValue + 1
End If
Next j
If CountValue > 0 Then
OutputCell.Offset(i, 0).Value = SumValue / CountValue
Else
OutputCell.Offset(i, 0).Value = ""
End If
StartRow = StartRow + GroupSize
i = i + 1
Loop
End Sub2. Для выполнения кода нажмите кнопку 
или нажмите F5Выберите диапазон своих данных (один столбец), затем выберите начальную ячейку для вывода и укажите размер группы (например, 5). Макрос выведет среднее значение для каждого набора из 5 строк один под другим в указанном выходном столбце.
Вы можете использовать аналогичный макрос для усреднения каждых 5 столбцов в строке.
Автоматизируйте усреднение каждых 5 столбцов:
Sub AverageEveryNColumns()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Long
Dim totalCols As Long, totalRows As Long
Dim startCol As Long, endCol As Long, outCol As Long
Dim v As Variant
Dim r As Long, c As Long
Dim sumVal As Double, cntVal As Long
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set DataRange = Application.InputBox("Select the data range (single rows)", _
xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
If GroupSize < 1 Then
MsgBox "Group size must be >= 1.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim prevCalc As XlCalculation
prevCalc = Application.Calculation
Application.Calculation = xlCalculationManual
totalCols = DataRange.Columns.Count
totalRows = DataRange.Rows.Count
v = DataRange.Value
outCol = 0
For startCol = 1 To totalCols Step GroupSize
endCol = startCol + GroupSize - 1
If endCol > totalCols Then endCol = totalCols
sumVal = 0
cntVal = 0
For r = 1 To totalRows
For c = startCol To endCol
If Not IsEmpty(v(r, c)) Then
If IsNumeric(v(r, c)) Then
sumVal = sumVal + CDbl(v(r, c))
cntVal = cntVal + 1
End If
End If
Next c
Next r
If cntVal > 0 Then
OutputCell.Offset(0, outCol).Value = sumVal / cntVal
Else
OutputCell.Offset(0, outCol).Value = ""
End If
outCol = outCol + 1
Next startCol
CleanExit:
Application.Calculation = prevCalc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Усреднение каждых 5 строк с помощью сводной таблицы
Другой практический способ расчета групповых средних каждых 5 строк — использование сводной таблицы в сочетании с нумерацией последовательностей или столбцом индекса для группировки данных. Этот метод особенно полезен для пользователей, работающих со структурированными, основанными на таблицах данными, которым нужна быстрая, интерактивная сводка без написания формул или использования надстроек. Сводная таблица динамически обрабатывает изменения данных и поддерживает гибкую группировку — отлично подходит для больших наборов данных или регулярных задач отчетности.
Вот как выполнить эту операцию с помощью вспомогательного столбца и сводной таблицы:
1. Добавьте столбец «Индекс» или «Группа» рядом с вашими данными, чтобы отметить каждую группу из 5 строк. В первой строке данных (B2) введите:
=INT((ROW()-ROW($A$2))/5)+1Эта формула маркирует каждую строку в последовательности, присваивая одно и то же номер группы каждым 5 строкам. Протяните эту формулу вниз вдоль вашего набора данных.
2. Выберите свои данные и новый столбец Индекс, затем нажмите Вставка > Сводная таблица. В диалоговом окне создания сводной таблицы подтвердите диапазон данных и выберите место для размещения сводной таблицы.
3. В списке полей только что созданной сводной таблицы перетащите поле «Группа» в область Строки и поле значений (например, «Продажи») в область Значения.
4. Щелкните раскрывающийся список в области Значения, выберите Настройки поля значений и выберите Среднее.
Теперь ваша сводная таблица показывает среднее значение для каждых 5 строк исходных данных, удобно сгруппированных с помощью вспомогательного столбца.
Ключевые преимущества метода сводной таблицы заключаются в его гибкости и легком обновлении при изменении исходных данных. Однако он требует добавления вспомогательного столбца и может не подходить для ситуаций, где данные должны оставаться точно отформатированными или неизмененными.
Связанные статьи:
Как усреднять последние 5 значений столбца при добавлении новых чисел?
Как усреднять верхние или нижние 3 значения в Excel?
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек