Как рассчитать среднее значение в столбце по заданным критериям из другого столбца в Excel?
Во многих практических сценариях в Excel часто требуется рассчитать среднее значение в одном столбце, сгруппированное или отфильтрованное по совпадающим записям в другом столбце. Например, вы можете определить средние продажи для каждого продавца или региона, как показано на рисунке ниже. Такой расчёт широко применяется в сводных отчётах, анализе эффективности и обзорах данных. В этой статье мы продемонстрируем несколько эффективных методов достижения этого результата, чтобы вы могли выбрать подходящий способ в зависимости от своих потребностей и уровня владения инструментами.
Расчёт среднего значения в столбце на основе одинаковых значений в другом столбце с помощью формул
Расчёт среднего значения в столбце на основе одинаковых значений в другом столбце с помощью Kutools для Excel
Расчёт среднего значения по группам с использованием Сводная таблица
Автоматизация расчёта групповых средних значений с помощью макроса VBA
Расчёт среднего значения в столбце на основе одинаковых значений в другом столбце с помощью формул
Один из самых простых способов вычислить среднее значение для группы на основе другого столбца в Excel — использовать условные формулы, такие как AVERAGEIF или AVERAGEIFS. Этот подход особенно полезен, когда нужны точные результаты по конкретным критериям, например расчёт средних продаж по определённому городу или продавцу.
1. Выберите пустую ячейку для отображения результата, введите следующую формулу и нажмите Enter:
=AVERAGEIF(B2:B13,E2,C2:C13)
Объяснение параметров: В приведённой выше формуле B2:B13 — это диапазон, содержащий критерии для проверки (например, город или продавца), E2 — конкретное значение, с которым выполняется сравнение (например, «Owenton»), а C2:C13 — диапазон, содержащий числовые значения, для которых необходимо рассчитать среднее.
После нажатия клавиши Enter вы мгновенно получите среднее значение для группы, указанной в ячейке E2 (например, средние продажи по «Owenton»).
Чтобы рассчитать среднее значение для каждого уникального значения в столбце критериев, просто измените значение в ячейке критерия (E2) нужным образом или скопируйте формулу вниз, если у вас уже есть список уникальных записей.
Практический совет: Для больших наборов данных или множества уникальных групп объедините эту формулу со списком уникальных значений (полученным с помощью таких инструментов, как «Удалить дубликаты» или функция UNIQUE в Office 365 и Excel 2021) — так вы мгновенно рассчитаете средние значения по всем группам. Всегда проверяйте, чтобы диапазоны в формуле охватывали все нужные данные и оставались согласованными при копировании.
Типичные ошибки и способы устранения:
- Если вы получаете ошибку #DIV/0!, убедитесь, что значение критерия действительно присутствует в выбранном диапазоне.
- Убедитесь, что ваш числовой диапазон содержит только корректные числа — текст или пустые ячейки могут исказить результаты вычислений.
Расчёт среднего значения в столбце на основе одинаковых значений в другом столбце с помощью Kutools для Excel
Если вы хотите автоматически рассчитать среднее значение для всех уникальных значений в столбце — без многократного ввода формул или ручной фильтрации, — Kutools для Excel предлагает удобное решение. Это особенно полезно при работе с большими списками и сложными наборами данных.
1. Выделите весь диапазон данных, включающий как столбец группировки, так и числовой столбец, для которого нужно рассчитать среднее значение. Затем перейдите в меню Kutools > Merge & Split > Расширенное объединение строк.
2. В диалоговом окне Объединить строки на основе столбца выполните следующие действия:
- Выберите столбец, по которому нужно выполнить группировку (например, «Город» или «Продавец»), и нажмите кнопку Primary Key, чтобы назначить его полем группировки.
- Выберите числовой столбец, для которого нужно рассчитать среднее значение, затем нажмите Calculate > Average.
Подсказка: Для остальных столбцов (например, с датами) можно указать способ объединения их значений — например, через запятую. - Нажмите OK, чтобы выполнить операцию.
Kutools немедленно сгруппирует данные по выбранному ключу и выведет среднее значение для каждой группы в числовом столбце.
Пакетная обработка в Kutools — идеальное решение для тех, кто регулярно анализирует групповую статистику: ежемесячные отчёты, сводки по отделам или другие многогрупповые расчёты. Как надстройка, Kutools сохраняет исходную структуру данных, а функция предварительного просмотра позволяет убедиться в корректности группировки до применения изменений.
Примечания и советы:
- Перед запуском инструмента убедитесь, что в выбранном диапазоне отсутствуют пустые строки.
- Если вы хотите использовать рассчитанные групповые средние значения в другом месте, просто скопируйте и вставьте результаты после завершения вычислений.
- В очень больших наборах данных обязательно правильно назначайте столбцы для группировки и числовые столбцы, чтобы избежать путаницы.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Расчёт среднего значения по группам с использованием Сводная таблица
Сводная таблица предоставляют мощный встроенный способ сводить, группировать и анализировать данные, включая расчёт средних значений по группам, без использования формул или сторонних надстроек. Этот метод идеален, если требуется интерактивное представление средних значений и итогов по различным категориям, и подходит как для небольших, так и для очень крупных наборов данных.
Как настроить Сводная таблица для расчёта групповых средних значений:
- Выберите любую ячейку в наборе данных, затем перейдите в меню Insert > PivotTable. В диалоговом окне укажите, где должна появиться сводная таблица (на новом листе или на существующем листе), и нажмите OK.
- В области полей сводной таблицы перетащите столбец, по которому нужно выполнить группировку, в область Rows (например, «Город» или «Продавец»).
- Перетащите числовой столбец, для которого нужно рассчитать среднее значение (например, «Продажи»), в область Values. По умолчанию Excel может вычислять сумму; чтобы изменить это, щелкните поле со значениями, выберите Value Настройки полей и укажите Average.
Ваша сводная таблица немедленно покажет среднее значение по каждой группе. При необходимости вы сможете быстро фильтровать, сортировать и форматировать отчёт. Этот метод прост в использовании и исключает риск ошибок в формулах.
Преимущества: интерактивность, эффективная работа с большими объёмами данных и возможность одновременного расчёта нескольких статистик.
Недостатки: Результат отображается в виде сводной таблицы, а не простого списка; при изменении исходных данных требуется периодически обновлять отчёт.
Совет: Дважды щёлкнув по любой ячейке итогового значения в сводной таблице, вы откроете новый лист с исходными данными для этой группы — это упрощает проверку деталей или устранение расхождений.
Типичные проблемы:
- Если среднее значение не отображается, убедитесь, что в настройках полей для поля значений (Value) выбрано значение «Average».
- Проверьте свои исходные данные на наличие лишних пустых строк или столбцов, которые могут нарушить макет сводной таблицы.
Автоматизация расчёта групповых средних значений с помощью макроса VBA
Пользователям, которым часто приходится рассчитывать средние значения для множества групп или автоматически создавать сводки, написание макроса на VBA может существенно сократить рутинную работу. VBA особенно эффективен, когда структура данных остаётся неизменной или требуется регулярно генерировать сводные отчёты одним щелчком мыши.
Обязательно сохраните книгу и включите макросы перед началом работы. Вот как приступить:
1. Нажмите Средства разработчика > Visual Basic, чтобы открыть редактор VBA. В редакторе выберите Вставка > Модуль, чтобы создать новый модуль кода. Вставьте приведённый ниже код в модуль:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. После вставки кода закройте редактор VBA. Вернитесь в Excel, нажмите Alt+F8, выберите в списке GroupAverageSummary и нажмите Выполнить. Макрос предложит выбрать столбец с группами (критериями) и столбец со значениями (числовыми данными). После выбора он автоматически создаст новый лист с именем «Group Average Summary», содержащий каждую уникальную группу и соответствующие средние значения.
Примечания по параметрам и работе:
- Убедитесь, что столбцы для группировки и значения имеют одинаковую длину и содержат корректные данные — избегайте частичного выделения.
- При необходимости этот макрос можно адаптировать для более сложных группировок или расширенной сводной статистики.
- Если на вашем листе уже существует сводка «Group Average Summary» с именем листа, макрос создаст новый лист с именем по умолчанию — «Имя листа».
Устранение неполадок:
- Если появляется сообщение об ошибке «индекс за пределами диапазона» или аналогичное, убедитесь, что выделенные диапазоны правильно совмещены и расположены на одном листе.
- Для наилучших результатов убедитесь, что столбец значений содержит исключительно числовые данные: текст или пустые ячейки внутри числового диапазона будут пропущены макросом.
Этот макрос идеально подходит для пакетной обработки, автоматической генерации отчётов или ситуаций, когда часто требуется сводить новые или обновлённые наборы данных.
Демонстрация: расчёт среднего значения в столбце на основе одинаковых значений в другом столбце с помощью Kutools для 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек