Как рассчитать средневзвешенное значение в Excel: 3 простых способа в 2026 году

Чтобы рассчитать средневзвешенное значение в Excel, умножьте каждое значение на его вес, сложите результаты и разделите полученную сумму на сумму весов. Самая быстрая формула — это =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4), где в столбце B находятся значения, а в столбце C — веса. Если сумма ваших весов уже равна 100%, то одна только функция SUMPRODUCT даст правильный ответ.
В этом руководстве объясняется, что такое средневзвешенное значение и когда его использовать. Затем рассматриваются три способа расчета средневзвешенного значения в Excel: SUMPRODUCT с SUM, вспомогательный столбец и SUMPRODUCT с условиями. Каждый метод включает в себя практический пример. Сведения о функциях взяты со страниц поддержки Microsoft по состоянию на 17 ноября 2026 года.
Что такое средневзвешенное значение
Обычное среднее значение относится ко всем величинам одинаково. Microsoft описывает функцию AVERAGE как возвращающую «среднее (арифметическое) своих аргументов». Каждое число учитывается один раз.
Средневзвешенное значение дает одним величинам больше влияния, чем другим. Каждое значение имеет вес, и значения с большим весом притягивают результат к себе.
Формула проста:
Средневзвешенное значение = (значение 1 × вес 1 + значение 2 × вес 2 + …) ÷ (вес 1 + вес 2 + …)
Вот быстрый пример. Оценка за курс складывается из домашних заданий, промежуточного экзамена и финального экзамена. Финальный экзамен составляет половину оценки, поэтому обычное среднее значение даст неверный результат.
| Компонент | Балл | Вес |
|---|---|---|
| Домашнее задание | 85 | 20% |
| Промежуточный экзамен | 78 | 30% |
| Финальный экзамен | 92 | 50% |
Простое среднее значение трех оценок равно 85. Средневзвешенный результат составляет 86.4, так как высокая оценка за финальный экзамен имеет наибольший вес. Эта разница и объясняет, почему важно использовать средневзвешенное значение.
Когда использовать средневзвешенное значение
Используйте средневзвешенное значение всегда, когда одни элементы должны иметь большее значение, чем другие. Типичные случаи включают:
- Оценки. Экзамены, проекты и домашние задания имеют разный вес.
- Цены. Средняя цена, уплаченная по заказам разного объема.
- Портфели. Средняя доходность активов разной стоимости.
- Опросы. Ответы, взвешенные по размеру группы или весам выборки.
- Запасы. Средневзвешенная стоимость запасов, закупленных по разным ценам.
- KPI. Оценка, формируемая на основе нескольких показателей разной степени важности.
Веса могут браться из разных источников. Иногда они задаются правилом, например, учебной программой, в которой указано, что финальный экзамен оценивается в 50%. Иногда они берутся из самих данных, например, количество проданных единиц или стоимость каждого актива. В обоих случаях математический расчет одинаков.
Если каждый элемент имеет одинаковое значение, достаточно обычного среднего. Если же один элемент явно должен сильнее влиять на результат, вам понадобятся веса.
Подготовка данных
Прежде чем писать формулу, поместите данные в простую таблицу. Используйте по одной строке для каждого элемента и следующие столбцы:
| Столбец | Что в нем содержится |
|---|---|
| A | Название элемента, например, Домашнее задание |
| B | Значение, например, оценка |
| C | Вес в виде числа или процента |
Веса могут быть выражены в процентах (например, 20%) или обычными числами (например, количество проданных единиц). Оба варианта подходят для приведенных ниже формул, если вы делите результат на сумму весов.
Две проверки помогут избежать большинства ошибок. Во-первых, убедитесь, что у каждого значения есть вес в той же строке. Во-вторых, следите за тем, чтобы диапазоны значений и весов были одинакового размера, иначе формула вернет ошибку.
Также полезно преобразовать диапазон в таблицу Excel. Выделите его, затем выберите Главная > Форматировать как таблицу. Новые строки будут автоматически расширять таблицу, и формулы, использующие ссылки на таблицу, будут их учитывать.
Метод 1: SUMPRODUCT и SUM
Это стандартный способ расчета средневзвешенного значения в Excel. Он использует две функции в одной формуле.
Microsoft заявляет, что SUMPRODUCT «возвращает сумму произведений соответствующих диапазонов или массивов». Простыми словами, она умножает каждое значение на его вес и складывает результаты.
Синтаксис выглядит так: =SUMPRODUCT(array1, [array2], [array3], ...). В нашем примере с оценками значения находятся в диапазоне B2:B4, а веса — в C2:C4. Введите:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
Вот что делает Excel:
- SUMPRODUCT умножает 85 на 20%, 78 на 30% и 92 на 50%.
- Она складывает три произведения: 17 + 23.4 + 46 = 86.4.
- SUM складывает веса: 20% + 30% + 50% = 100%.
- Формула делит 86.4 на 100%, что дает 86.4.
Когда сумма весов равна 100%, деление ничего не меняет. Тем не менее, оставьте его. Если кто-то позже изменит вес и сумма станет равной 90%, формула все равно выдаст правильный результат.
Microsoft указывает два важных здесь правила для SUMPRODUCT. Диапазоны «должны иметь одинаковые размерности», иначе функция вернет ошибку #VALUE!. Кроме того, SUMPRODUCT «трактует нечисловые элементы массива как нулевые». Пустой вес или вес в виде текста будет незаметно принят за ноль, поэтому проверяйте свои данные.
Microsoft также предостерегает от использования ссылок на целые столбцы. Для лучшей производительности SUMPRODUCT «не следует использовать со ссылками на целые столбцы». Используйте B2:B500, а не B:B.
Метод 2: Вспомогательный столбец
Некоторым людям нравится видеть каждый шаг. Вспомогательный столбец показывает каждое произведение, что позволяет легко проверить и объяснить средневзвешенное значение.
- В столбце D введите заголовок, например Значение × Вес.
- В ячейке D2 введите
=B2*C2и скопируйте формулу вниз. - Под последней строкой подведите итог по столбцу D с помощью
=SUM(D2:D4). - Просуммируйте веса с помощью
=SUM(C2:C4). - Разделите первый итог на второй.
В примере с оценками в столбце D будут показаны значения 17, 23.4 и 46. В сумме они дают 86.4. Сумма весов составляет 100%, поэтому средневзвешенное значение равно 86.4.
Это те же самые математические вычисления, которые SUMPRODUCT выполняет за один шаг. На странице самой компании Microsoft показано, что более длинная формула, такая как =C2*D2+C3*D3+C4*D4+C5*D5, дает «тот же результат», что и SUMPRODUCT.
Используйте вспомогательный столбец для обучения, аудита или в любых таблицах, где другим пользователям нужно проследить логику расчетов. Используйте Метод 1, если вам нужен компактный лист.
Метод 3: SUMPRODUCT с условиями
Иногда расчет требуется выполнить только для части данных. Например, вам может понадобиться средняя цена, уплаченная в одном регионе, взвешенная по количеству проданных единиц.
SUMPRODUCT может проверять условия прямо внутри формулы. В примере от Microsoft подсчитывается количество вишни, проданной в одном регионе, с помощью =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). Сравнения возвращают значения TRUE или FALSE, которые при умножении работают как 1 или 0.
Применим ту же идею здесь. Допустим, в столбце A указан регион, в столбце B — цена, а в столбце C — количество единиц. Чтобы получить средневзвешенную цену для региона East:
=SUMPRODUCT((A2:A100="East")*B2:B100*C2:C100)/SUMIF(A2:A100,"East",C2:C100)
Верхняя часть складывает произведения цены на количество единиц, но только для строк с регионом East. Нижняя часть использует функцию SUMIF для суммирования единиц только для строк с регионом East. Microsoft объясняет, что SUMIF суммирует «ячейки, указанные заданным критерием».
Поместите название региона в ячейку вместо того, чтобы вводить вручную «East». Тогда вы сможете менять значение в ячейке и видеть результаты по каждому региону без редактирования формулы.
Для двух условий, таких как регион и месяц, добавьте второе сравнение в верхнюю часть. В нижней части используйте функцию SUMIFS вместо SUMIF.
Практический пример: средневзвешенная цена
Оценки — классический пример. Но на работе не менее часто приходится сталкиваться с ценами. Вот пример из сферы закупок.
Магазин трижды закупал один и тот же товар по разным ценам. Цены указаны за единицу товара.
| Заказ | Единицы | Цена за единицу | Единицы × цена |
|---|---|---|---|
| Заказ 1 | 10 | 4.00 | 40.00 |
| Заказ 2 | 25 | 3.20 | 80.00 |
| Заказ 3 | 15 | 5.00 | 75.00 |
| Итого | 50 | 195.00 |
Простое среднее трех цен составляет 4.07. Это число вводит в заблуждение, поскольку большая часть единиц товара была закуплена по самой низкой цене.
Для расчета средневзвешенного значения в качестве веса используется количество единиц:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)
Верхняя часть равна 195. Нижняя часть равна 50. Средневзвешенная цена составляет 3.90 за единицу. Это истинная средняя стоимость запасов, и именно этот показатель следует использовать для расчета маржи или планирования повторных заказов.
Обратите внимание, что весами здесь выступают единицы товара, а не проценты. Это нормально. Деление на сумму весов позволяет работать с любым типом веса.
Средневзвешенное значение в сводной таблице
Сводные таблицы позволяют быстро обобщать большие объемы данных, но среди их встроенных параметров нет средневзвешенного значения. Параметр «Среднее» в сводной таблице рассчитывает обычное среднее. Однако есть простой обходной путь.
- В исходной таблице добавьте вспомогательный столбец, который умножает значение на вес, например
=B2*C2. Назовите его Произведение. - Обновите сводную таблицу, чтобы она увидела новый столбец.
- Выделите сводную таблицу, затем выберите Анализ сводной таблицы > Поля, элементы и наборы > Вычисляемое поле.
- Назовите поле, например Средневзвешенная цена, и введите формулу
=Произведение/Единицы. - Добавьте новое поле в область Значения.
Это работает, потому что вычисляемое поле делит итоговые значения, а не отдельные строки. Для каждой группы оно делит сумму произведений на сумму весов. Это абсолютно верный математический подход.
Задайте для нового поля нужный числовой формат, например, два знака после запятой. Теперь сводная таблица будет показывать правильное значение для каждого региона, продукта или месяца, которые вы перетащите в строки.
Средневзвешенное значение с процентами
Многие читатели спрашивают, как работать с весами, заданными в процентах. Здесь возможны два случая.
Сумма весов равна 100%. Вы можете использовать формулу =SUMPRODUCT(B2:B4,C2:C4) без деления. Результат уже будет правильным. Тем не менее, деление на SUM(C2:C4) ничего не стоит и защищает вас от ошибок округления или ввода.
Сумма весов не равна 100%. Всегда делите на сумму весов. В противном случае результат окажется слишком завышенным или заниженным.
Форматируйте ячейки с весами как проценты. Выделите их, затем выберите Процентный в списке числовых форматов на вкладке Главная. Введите 20 в ячейку с процентным форматом, и Excel сохранит это значение как 0.2.
Распространенная ошибка — смешивание стилей, например, 20% в одной строке и 30 в следующей. В этом случае Excel воспримет второй вес как 3 000%. Сохраняйте единый формат для всех весов.
Расчет средневзвешенного значения в Excel с помощью ИИ
Приведенные выше формулы отлично подходят для одной таблицы. Но когда ваши данные распределены по множеству строк или нескольким файлам, настройка диапазонов требует времени. Инструмент на базе ИИ может прочитать файл и выполнить все расчеты за вас.
Вот этот метод в три шага с использованием Powerdrill Bloom.
Шаг 1: Загрузите файл Excel
Загрузите рабочую книгу или файл CSV, содержащий ваши значения и веса. На странице расчетов в Excel вас попросят убедиться, что ваши данные «правильно отформатированы для обеспечения беспрепятственных расчетов».
Вы можете загрузить несколько файлов одновременно. На странице указано, что вы можете загрузить «до 10 файлов одновременно в один набор данных».
Шаг 2: Запросите средневзвешенное значение на естественном языке
Введите нужный вам расчет. Например: «Рассчитай среднюю цену за единицу по регионам, взвешенную по количеству проданных единиц».
На странице указано, что инструмент «распознает и применит правильную формулу». Попросите его показать использованную формулу, чтобы вы могли воссоздать ее на своем листе.
Шаг 3: Проверьте результат и используйте формулу повторно
Сравните результат с быстрой ручной проверкой по одной группе. Затем скопируйте формулу в свою рабочую книгу или экспортируйте сводную таблицу.
На странице указано, что инструмент «быстро сгенерирует точные результаты на основе вашего текстового ввода». Сохраните формулу на своем листе, чтобы результат обновлялся при изменении данных.
Распространенные ошибки, которых следует избегать
Вот проблемы, которые чаще всего возникают при расчете средневзвешенного значения в Excel.
- Использование AVERAGE. Функция AVERAGE дает каждому значению равный вес. Вместо нее используйте SUMPRODUCT и SUM.
- Отсутствие деления на веса. Если сумма весов не равна точно 100%, обязательно делите на их сумму.
- Несовпадение диапазонов. Для работы SUMPRODUCT требуются диапазоны одинакового размера, иначе она возвращает ошибку #VALUE!.
- Пустые веса. Пустой вес считается равным нулю, из-за чего эта строка исключается из расчета без предупреждения.
- Смешанные форматы процентов. Задавайте все веса либо в процентах, либо в виде обычных чисел.
- Ссылки на целые столбцы. Для ускорения работы используйте ограниченные диапазоны, например B2:B500.
- Жестко заданные условия. Помещайте условия в ячейку, чтобы формулу было легко использовать повторно.
Информацию о других распространенных расчетах вы найдете в наших руководствах по темам: как сделать сводную таблицу в Excel и как сделать ABC-анализ в Excel.
Если ваши значения и веса находятся в большом экспортированном файле, попробуйте Powerdrill Bloom и запросите средневзвешенное значение одним предложением.
Часто задаваемые вопросы
Какова формула для расчета средневзвешенного значения в Excel?
Используйте формулу =SUMPRODUCT(значения,веса)/SUM(веса). Например, =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) умножает каждое значение на его вес, складывает результаты и делит полученную сумму на общий вес.
Можно ли рассчитать средневзвешенное значение без SUMPRODUCT?
Да. Добавьте вспомогательный столбец, который умножает каждое значение на его вес. Затем разделите сумму этого столбца на сумму весов.
Как рассчитать средневзвешенное значение с процентами в Excel?
Если сумма процентов равна 100%, то функция SUMPRODUCT для значений и весов сразу даст правильный ответ. Если нет, разделите результат на сумму весов.
Как рассчитать средневзвешенное значение с условием?
Умножьте условие внутри SUMPRODUCT, например (A2:A100="East"), на значения и веса. Затем разделите результат на функцию SUMIF для весов с тем же условием.
В чем разница между средним и средневзвешенным значением?
Простое среднее значение учитывает все величины одинаково. Средневзвешенное значение присваивает каждому значению вес, поэтому более важные значения сильнее влияют на результат.
Источники: Служба поддержки Microsoft, функция SUMPRODUCT · Служба поддержки Microsoft, функция AVERAGE · Служба поддержки Microsoft, функция SUMIF.