Как посчитать средневзвешенное значение в excel

Обновлено: 06.07.2024

Если у вас есть диапазон значений на листе, и вы хотите подсчитать ячейки выше / ниже определенного значения или выше / ниже среднего значения диапазона, как вы можете быстро их подсчитать? В Excel для этого можно использовать формулу СЧЁТЕСЛИ.

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Подсчитайте ячейки выше / ниже среднего

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Здесь у вас есть диапазон значений на листе, как показано на снимке экрана, и теперь вам нужно подсчитать значения выше или ниже среднего в этом диапазоне.

Выберите пустую ячейку, например Ячейку C6, введите эту формулу = СЧЁТЕСЛИ (A1: C5; ">" & СРЕДНЕЕ (A1: C5)) (диапазон A1: C5 указывает диапазон, в котором вы хотите подсчитать количество ячеек выше среднего, вы можете изменить его по своему усмотрению) и нажмите Enter кнопку на клавиатуре. Смотрите скриншот:

Чаевые: Если вы хотите подсчитать количество ячеек ниже среднего, введите эту формулу = СЧЁТЕСЛИ (A1: C5; "<" & СРЕДНЕЕ (A1: C5)) (диапазон A1: C5 указывает диапазон, в котором вы хотите подсчитывать ячейки ниже среднего, вы можете изменить его по своему усмотрению).

Подсчитайте ячейки выше / ниже определенного значения

Если вы просто хотите подсчитать ячейки выше или ниже определенного значения, в этом случае я хочу подсчитать ячейки выше числа 50.

Выберите пустую ячейку, например Ячейку C6, введите эту формулу = СЧЁТЕСЛИ (A1: C5; "> 50") (диапазон A1: C5 указывает диапазон, в котором вы хотите подсчитать ячейки выше указанного значения 50, число 50 соответствует критерию, вы можете изменить их по своему усмотрению) и нажмите Enter кнопку на клавиатуре. Смотрите скриншот:

Совет: если вы хотите подсчитать количество ячеек ниже определенного значения, введите эту формулу = СЧЁТЕСЛИ (A1: C5; «<50») (диапазон A1: C5 указывает диапазон, в котором вы хотите посчитать ячейки ниже указанного значения, число 50 соответствует критерию, вы можете изменить их по своему усмотрению).

В Excel существует несколько способов найти среднее для набора чисел. Например, можно воспользоваться функцией для расчета простого среднего, взвешенного среднего или среднего, исключающего определенные значения.

Чтобы научиться вычислять средние значения, используйте предоставленные образцы данных и описанные ниже процедуры.

Копирование примера данных

Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.

Создайте пустую книгу или лист.

Выделите приведенный ниже образец данных.

Примечание: Не выделяйте заголовки строк или столбцов (1, 2, 3. A, B, C. ) при копировании данных примера на пустой лист.

Выбор примеров данных в справке

Качество изделия

Цена за единицу

Количество заказанных изделий

Среднее качество изделий

Средняя цена изделия

Среднее качество всех изделий с оценкой качества выше 5

Нажмите клавиши +C.

Выделите на листе ячейку A1, а затем нажмите клавиши +V.

Расчет простого среднего значения

Выделите ячейки с A2 по A7 (значения в столбце "Качество изделия").

На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма и выберитесреднее значение .

Расчет среднего для несмежных ячеек

Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом "Среднее качество изделия" в примере данных.

На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма кнопку Среднее инажмите клавишу RETURN.

Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере).

Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).

В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).

Удерживая нажатой клавишу , щелкните ячейки, для чего нужно вычесть среднее значение, и нажмите клавишу RETURN. Например, выберите A2, A4 и A7 и нажмите клавишу RETURN.

Выделенная ссылка на диапазон в функции СРЗНАЧ заменится ссылками на выделенные ячейки. В приведенном примере результат будет равен 8.

Расчет среднего взвешенного значения

В приведенном ниже примере рассчитывается средняя цена за изделие по всем заказам, каждый из которых содержит различное количество изделий по разной цене.

Выделите ячейку A9, расположенную слева от ячейки с текстом "Средняя цена изделия".

На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.

В списке построителя формул дважды щелкните функцию СУММПРОИЗВ.

Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СУММПРОИЗВ.

Щелкните поле рядом с надписью массив1 и выделите на листе ячейки с B2 по B7 (значения в столбце "Цена за единицу").

Щелкните поле рядом с надписью массив2 и выделите на листе ячейки с C2 по C7 (значения в столбце "Количество заказанных изделий").

В строке формул установите курсор справа от закрывающей скобки формулы и введите /

Если строка формул не отображается, в меню Вид выберите пункт Строка формул.

В списке построителя формул дважды щелкните функцию СУММ.

Выделите диапазон в поле число1, нажмите кнопку DELETE и выделите на листе ячейки с C2 по C7 (значения в столбце "Количество изделий").

Теперь в строке формул должна содержаться следующая формула: =СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7).

Нажмите клавишу RETURN.

В этой формуле общая стоимость всех заказов делится на общее количество заказанных изделий, в результате чего получается средневзвешенная стоимость за единицу — 29,38297872.

Расчет среднего, исключающего определенные значения

Вы можете создать формулу, которая исключает определенные значения. В приведенном ниже примере создается формула для расчета среднего качества всех изделий, у которых оценка качества выше 5.

Выделите ячейку A10, расположенную слева от ячейки с текстом "Среднее качество всех изделий с оценкой качества выше 5".

На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.

В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.

Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СРЗНАЧЕСЛИ.

Щелкните поле рядом с надписью диапазон и выделите на листе ячейки с A2 по A7 (значения в столбце "Цена за единицу").

Щелкните поле рядом с надписью условие и введите выражение ">5".

Нажмите клавишу RETURN.

Такая формула исключит значение в ячейке A7 из расчета. В результате будет получено среднее качество изделий, равное 8,8.

Совет: Чтобы использовать функцию СРЗНАЧЕСЛИ для расчета среднего без нулевых значений, введите выражение "<>0" в поле условие.

Копирование примера данных

Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.

Создайте пустую книгу или лист.

Выделите приведенный ниже образец данных.

Выбор примеров данных в справке

Качество изделия

Цена за единицу

Количество заказанных изделий

Среднее качество изделий

Средняя цена изделия

Среднее качество всех изделий с оценкой качества выше 5

Нажмите клавиши +C.

Выделите на листе ячейку A1, а затем нажмите клавиши +V.

Расчет простого среднего значения

Рассчитаем среднее качество изделий двумя разными способами. Первый способ позволяет быстро узнать среднее значение, не вводя формулу. Второй способ предполагает использование функции "Автосумма" для расчета среднего значения и позволяет вывести его на листе.

Быстрый расчет среднего

Выделите ячейки с A2 по A7 (значения в столбце "Качество изделия").

На строка состояния щелкните стрелку всплывающее меню (если вы используете образец данных, вероятно, область содержит текст Sum=49),а затем выберите среднее .

Примечание: Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.

Расчет среднего с отображением на листе

На панели инструментов Стандартная под названием книги щелкните стрелку рядом с кнопкой кнопку Среднее и нажмите клавишу RETURN.

Результат составляет 8,166666667 — это средняя оценка качества всех изделий.

Совет: Если вы работаете с данными, в которые перечислены числа в строке, выберите первую пустую ячейку в конце строки, а затем щелкните стрелку рядом с кнопкой .

Расчет среднего для несмежных ячеек

Существует два способа расчета среднего для ячеек, которые не следуют одна за другой. Первый способ позволяет быстро узнать среднее значение, не вводя формулу. Второй способ предполагает использование функции СРЗНАЧ для расчета среднего значения и позволяет вывести его на листе.

Быстрый расчет среднего

Выделите ячейки, для которых вы хотите найти среднее значение. Например, выделите ячейки A2, A4 и A7.

Совет: Чтобы выбрать несмещные ячейки, щелкните их, удерживая клавишу.

На строка состояния щелкните стрелку всплывающее меню и выберите среднеезначение .

В приведенном примере результат будет равен 8.

Примечание: Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.

Расчет среднего с отображением на листе

Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере).

Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).

В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).

Расчет среднего взвешенного значения

Выделите ячейку A9, расположенную слева от ячейки с текстом "Средняя цена изделия".

На вкладке Формулы в разделе Функция выберите пункт Построитель формул.

В списке построителя формул дважды щелкните функцию СУММПРОИЗВ.

В разделе Аргументы щелкните поле рядом с надписью массив1 и выделите на листе ячейки с B2 по B7 (значения в столбце "Цена за единицу").

В разделе Аргументы щелкните поле рядом с надписью массив2 и выделите на листе ячейки с C2 по C7 (значения в столбце "Количество заказанных изделий").

В строке формул установите курсор справа от закрывающей скобки формулы и введите /

Если строка формул не отображается, в меню Вид выберите пункт Строка формул.

В списке построителя формул дважды щелкните функцию СУММ.

В разделе Аргументы щелкните диапазон в поле число1, нажмите кнопку DELETE и выделите на листе ячейки с C2 по C7 (значения в столбце "Количество изделий").

Теперь в строке формул должна содержаться следующая формула: =СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7).

Нажмите клавишу RETURN.

Расчет среднего, исключающего определенные значения

На вкладке Формулы в разделе Функция выберите пункт Построитель формул.

В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.

В разделе Аргументы щелкните поле рядом с надписью диапазон и выделите на листе ячейки с A2 по A7 (значения в столбце "Цена за единицу").

В разделе Аргументы щелкните поле рядом с надписью условие и введите выражение ">5".

Нажмите клавишу RETURN.

Предположим, вам нужно найти среднее количество дней для выполнения задач разными сотрудниками. Или вы хотите вычислить среднюю температуру для определенного дня на основе 10-летнего промежутка времени. Существует несколько способов расчета среднего для группы чисел.

Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения:

Среднее значение Это арифметическое и вычисляется путем с добавления группы чисел и деления на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана Среднее число числа. Половина чисел имеют значения больше медианой, а половина чисел имеют значения меньше медианой. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода Наиболее часто встречается число в группе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. В акосимном распределении группы чисел они могут быть другими.

Расчет среднего значения чисел в подрядной строке или столбце

Выполните действия, описанные ниже.

Щелкните ячейку снизу или справа от чисел, для которых необходимо найти среднее.

На вкладке "Главная" в группе "Редактирование" щелкните стрелку рядом с кнопкой " ", выберите "Среднее" и нажмите клавишу ВВОД.

Расчет среднего значения чисел, которые не являются частью строки или столбца

Для этого используйте функцию С AVERAGE. Скопируйте приведенную ниже таблицу на пустой лист.

Описание (результат)

Среднее значение всех чисел в списке выше (9,5).

Среднее значение 3-го и последнего числа в списке (7,5).

Среднее значение чисел в списке за исключением тех, которые содержат нулевые значения, например ячейка A6 (11,4).

Для этой задачи используются функции СУММПРОИВ ИСУММ. В этом примере вычисляется средняя цена за единицу для трех покупок, при которой каждая покупка приобретает различное количество единиц по разной цене.

В целом, легко вычислить средневзвешенное значение, комбинируя функции СУММПРОИЗВ и СУММ в Excel. Однако вычисляемые поля, похоже, не поддерживают функции в сводной таблице. Следовательно, как можно рассчитать средневзвешенное значение в сводной таблице? В этой статье будет представлено решение.

Вычислить средневзвешенное значение в сводной таблице Excel

Предположим, вы создали сводную таблицу, как показано на скриншоте ниже. И я возьму сводную таблицу в качестве примера, чтобы вычислить средневзвешенную цену каждого фрукта в сводной таблице.

1. Прежде всего, добавьте вспомогательный столбец Сумма в исходных данных.
Вставьте пустой столбец в исходные данные, введите Сумма как имя столбца, следующий тип = D2 * E2 в первой ячейке этого вспомогательного столбца и, наконец, перетащите маркер автозаполнения, чтобы заполнить весь столбец. Смотрите скриншот:

2. Выберите любую ячейку в сводной таблице, чтобы активировать Инструменты сводной таблицы, А затем нажмите Анализировать (или Параметры)> обновление. Смотрите скриншот:

3. Продолжайте нажимать Анализировать > Поля, предметы и наборы > Расчетное поле. Смотрите скриншот:

4. В диалоговом окне «Вставить вычисляемое поле» введите Средний вес в Имя поле, введите = Сумма / Вес (измените формулу в соответствии с именами полей) в Формула поле, а затем щелкните OK кнопка. Смотрите скриншот:

Теперь вы вернетесь к сводной таблице и получите средневзвешенную цену каждого фрукта в строках промежуточных итогов. Смотрите скриншот:

Например, у вас есть список покупок с ценами, весом и суммой. Вы можете легко рассчитать среднюю цену с помощью функции СРЕДНИЙ в Excel. А что если средневзвешенная цена? В этой статье я представлю метод расчета средневзвешенного значения, а также метод расчета средневзвешенного значения при соблюдении определенных критериев в Excel.

Легко группируйте столбцы среднего / сумм / подсчета, если они соответствуют критериям в другом столбце

Kutools for Excel гордится мощной полезностью Расширенные ряды комбинирования который может помочь вам быстро объединить строки с критериями в одном столбце и одновременно усреднить / сумму / количество / макс / мин / продукт в других столбцах. Полнофункциональная бесплатная 30-дневная пробная версия!

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Рассчитать средневзвешенное значение в Excel

Предположим, ваш список покупок выглядит так, как показано на скриншоте ниже. Вы можете легко рассчитать средневзвешенную цену, объединив функции СУММПРОИЗВ и СУММ в Excel, и сделать это можно следующим образом:

Шаг 1. Выберите пустую ячейку, говорит «Ячейка F2», введите формулу =SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18) в него и нажмите Enter ключ.

Внимание: В формуле = СУММПРОИЗВ (C2: C18, D2: D18) / СУММ (C2: C18), C2: C18 столбец "Вес", D2: D18 - это столбец «Цена», и вы можете изменить оба значения в зависимости от ваших потребностей.

Шаг 2. Средневзвешенная цена может включать слишком много десятичных знаков. Чтобы изменить десятичные разряды, вы можете выбрать ячейку, а затем щелкнуть Увеличить десятичную дробь кнопка or Уменьшить десятичный кнопка на Главная меню.

Рассчитать средневзвешенное значение при соответствии заданным критериям в Excel

Приведенная выше формула рассчитает средневзвешенную цену всех фруктов. Но иногда вам может потребоваться рассчитать средневзвешенное значение, если оно соответствует заданным критериям, таким как средневзвешенная цена Apple, и вы можете попробовать этот способ.

Шаг 1. Выберите пустую ячейку, например «Ячейка F8», введите формулу =SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18) в него и нажмите Enter ключ.

Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем!
Подробнее . Бесплатная пробная версия

Внимание: В формуле = СУММПРОИЗВ ((B2: B18 = «Яблоко») * C2: C18 * D2: D18) / СУММЕСЛИ (B2: B18, «Яблоко», C2: C18), B2: B18 столбец "Фрукты", C2: C18 столбец "Вес", D2: D18 столбец "Цена" "Apple"- это конкретные критерии, по которым вы хотите рассчитать средневзвешенное значение, и вы можете изменить их в зависимости от ваших потребностей.

Шаг 2. Выберите ячейку с этой формулой, а затем щелкните значок Увеличить десятичную дробь кнопка or Уменьшить десятичный кнопка на Главная Вкладка для изменения знаков после запятой средневзвешенной цены яблока.

Читайте также: