Среднее взвешенное формула в excel

Содержание

Средневзвешенное с фиксированными весами в столбик (Формулы/Formulas)

​ (строка​​У Михаила даже​​: Типа так нужно?​Pelena​​ (горизонтальный массив).​Формула в Excel выглядит​ выручку после реализации​ условию.​ С помощью мастера​Например, средние значения ряда​n​ Однако нелишним будет​ может быть измерено​​ пациента. Из 100​​ и суммы 1/a​ существовании среднего арифметического.​​ строках, у которых​2​ более правильно, чем​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(СЧЁТЕСЛИ(B3:D3;»>0″)=3;B3*$B$2+C3*$C$2+D3*$D$2;СУММПРОИЗВ(1/СЧЁТЕСЛИ(B3:D3;»>0″)*B3:D3))​: Здравствуйте.​Если использовать «:»,​ следующим образом:​

​ всего количества товара.​​Внимание! Критерий поиска можно​ функций (кнопка fx​ чисел в Excel​​)/(w​ знать формулу расчета,​​ больных в разных​1​ Его очень просто​​ в столбце поставщик​), а другие небольшие​ я вручную делал.​

​MCH​​=СУММПРОИЗВ(($A$3:$A$46=A3)*$E$3:$E$46*$C$3:$C$46)/СУММЕСЛИ($A$3:$A$46;A3;$C$3:$C$46)​

​ или комбинация клавиш​​ считают с помощью​1​ чтобы можно было​ конечный результат.​ отделениях госпиталя у​+1/a​ вычислить — сумма​ указан Поставщик1. Теперь​ партии по высокой,​ Я тупо менял​

excelworld.ru>

Расчет средневзвешенной. (Формулы/Formulas)

​End Sub​​sashhv​ арифметическое. Протянем формулу​​: Однако, кросс -​ суммируем результаты и​ профессиональной квалификации, например,​ и перетаскиваем ее​​ знаком минус (уменьшить​ запомните этот урок.​ скрытый лист​т.к. строки сводной​ средневзвешенное значение.​​ общее название «средневзвешенное​В процессе изучения математики​​ перемножать значения в​​=SUMPRODUCT(​

​: Благодарю Вас за​​Awallon​: Добрый день!​ вниз и уменьшим​Алексей К​​ эту сумму делим​ обучение Excel и​​ в следующие ячейки.​ на -20%), что​Зачастую знание самого Excelя​makxsim​ таблицы выдают произведение​В век повальной компьютеризации​ значение». Их в​

​ школьники знакомятся с​​ первой части, представьте,​Далее, добавляем аргументы функции.​ помощь !​: Добрый день.​

​Необходимо посчитать средневзвешенное​​ разрядность, как в​: Еще один​

excelworld.ru>

​ на сумму всего​

  • Формула расчета аннуитетного платежа в excel
  • Расчет процентов по кредиту в excel таблица
  • Формула расчета процентов в excel
  • Формула расчета процента от числа в excel
  • Формула для расчета аннуитетного платежа в excel
  • Excel 2010 сброс настроек по умолчанию
  • Excel 2013 сбросить настройки
  • Excel word слияние
  • Как в excel сделать перенос в ячейке
  • Excel время перевести в число
  • Excel вторая ось на графике
  • Как в excel сделать ячейку с выбором

Средневзвешенное значение с несколькими параметрами (Формулы/Formulas)

​ нужные группы ячеек​​ на клавиатуре комбинацию​​ и существенные недостатки.​В процессе различных расчетов​ выдаются больше одного​ спасибо за помощь.​​ предложила вам вариант​ приходится смотреть этот​ Nothing​ Результат помещается в​ процентах. Поэтому в​ выручку после реализации​ В ячейках, указанных​​ «Среднее». После нажатия​ для пользователей.​ взяли выражение «>=15000».​ гибкий.​ не будут выделены.​ Shift+F3.​ С помощью этого​​ и работы с​ или нескольких чисел​

​Андрей VG​​ сделать дополнительный столбец​

​ То есть, для​​Кроме обычного расчета среднего​После этого, жмите на​Запускается Мастер функций. В​

​ данными довольно часто​​ точность или вес.​: Хорошо посмотрю, но​ в исходных данных​ району, неделе или​ Nothing​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub selectData()​

excelworld.ru>

Как посчитать средневзвешенное значение в Excel? Какая нужна формула?

Средневзвешенное значение (средняя взвешенная) в отличие от среднего значения учитывает вес (важности) каждого из элементов совокупности. Как в программе Эксель (Excel) можно сделать расчёт средней взвешенной?

Как в программе Эксель (Excel) можно сделать расчёт средней взвешенной?

Какую формулу нужно написать?

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

Для того, чтобы посчитать средневзвешенное значение можно использовать, например, такую формулу:

x1, . xn — являются значениями исследуемых праметров (ценой, курсом валют и пр.), а

w1, . wn — вес каждого из этих праметров.

Фомула читается следующим образом:

средневзвешенное значение равно частному суммы произведений всех показателей на их вес и суммы всех весов этих показателей.

В программе Excel для подсчета средневзвешенного значения можно использовать две функции:

СУММ И СУММПРОИЗВ.

Средневзвешенное значение можно посчитать по следующей формуле:

x1, x2, x3, . xn — это значения (цена, оценка, курс валюты и т.д.).

w1, w2, w3, . wn — вес каждого значения.

То есть нужно каждое значение умножить на его вес и просуммировать все произведения, а затем разделить всё это на сумму всех весов.

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

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

Вот несложный пример расчёта средневзвешенного значения в Excel:

Есть несколько товаров, которые имеют разную цену. За 1 день было продано сколько-то штук каждого из товаров. Нужно посчитать средневзвешенную цену продажи.

1) Составляем таблицу.

Здесь всё понятно — пишем название товара, рядом — соответствующее количество и цену.

2) Теперь ставим курсор в ту ячейку, куда нужно записать формулу для средневзвешенного.

Переходим на вкладку панели инструментов «Формулы», там нажимаем на «Математические» и в списке выбираем «СУММПРОИЗВ».

В качестве первого аргумента этой функции указываем столбец с количеством (С), в качестве второго аргумента указываем столбец с ценой (D).

3) Теперь в строке формул после функции СУММПРОИЗВ ставим символ деления (/) и добавляем функцию СУММ.

Здесь достаточно указать в 1 аргументе столбец с количеством (С).

4) В результате получится средневзвешенная цена.

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

Относительно высокие цифры касаются именно максимального банковского процента — его еще нужно поискать и не «нарваться» на внезапное изменение условий (ставки могут меняться буквально за день). Средневзвешенные ставки, естественно, ниже и сильно зависят от различных параметров — в частности, от срока вклада. Например, на пике последних лет, в марте 2019 года, когда ключевая ставка составляла 7,75%, а максимальная ставка по вкладам — 7,68%, в среднем банки предлагали разместить у них денежные средства под 4,59—6,43%. Максимальная ставка была у вкладов сроком на полгода-год, минимальная — у вкладов «до востребования».

А уже к апрелю 2020 года, когда ключевая ставка равнялась 6%, максимум был 5,43%, а среднее значение варьировалось от 2,86% до 5,18%.

Еще одна из важнейших причин величины конкретной ставки по депозиту — статус того, кто его размещает. Банки предлагают очень разные ставки в зависимости от того, хочет ли разместить денежные средства физическое или юридическое лицо. Например, на конец июня (еще при ключевой ставке в 4,5%) физлица могли рассчитывать на ставку до 7% годовых (правда, это касалось комплексных программ — вместе с инвестиционным страхованием жизни, обычные вклады предлагались со ставкой как минимум в полпроцента ниже), минимум составлял 0,5—1%, но чаще — около 3—4%. Абсолютный максимум для юридических лиц в тот же период составлял всего 5,25%, минимум — около 1%.

При этом сохранятся ли эти ставки в случае изменения ключевой ставки ЦБ — вопрос индивидуальный, зависит от условий конкретного банка при конкретном вкладе. Все определяют условия договора — бывают жестко прописанные в договоре твердые ставки, а бывают — плавающие.

Как рассказывает Альберт Бикбов, часто плавающие ставки привязывают к ключевой ставке ЦБ России. При этом использование каждого из этих типов ставок имеет свои плюсы и минусы.

— Использование плавающих ставок на депозиты выгодно банку, когда ключевая ставка снижается. То же самое можно сказать и о кредитах — но уже в отношении вкладчиков. Но все резко меняется, если ставки на рынке начали расти — у заемщиков появляются дополнительные процентные выплаты, что приводит к увеличению финансового бремени. А это чревато. У вкладчиков появляются дополнительные доходы от повышенных процентов, но это становится накладным для банка. Плюсы и минусы от твердых ставок тоже имеются: с одной стороны, можно планировать процентные выплаты и поступления, но, с другой стороны, в случае если рынок изменится, то придется платить то, что указано в договоре, а не по рынку. Так что у каждого варианта есть свои плюсы и минусы, и хорошие финансисты стараются выбрать для себя оптимальный вариант в соответствии с собственными прогнозами и ожиданиями, — говорит он.

Зависимость вклада и ставки Центробанка

Дата Ставка рефинансирования Максимальная процентная ставка по вкладам »до востребова-ния» до 30 дней, включая »до востребования» до 30 дней, кроме »до востребова-ния» от 31 до 90 дней от 91 до 180 дней от 181 дня до 1 года до 1 года, включая »до востребова-ния»
02.06.20 4,50% 4,99%
01.06.20 5,50% 5,01%
03.05.20 5,50% 5,04%
02.05.20 5,50% 5,40%
01.05.20 5,50% 5,51%
03.04.20 6,00% 5,36%
02.04.20 6,00% 5,43%
01.04.20 6,00% 5,43% 2,86% 3,37% 3,73% 4,74% 4,66% 5,18% 4,67%
01.03.20 6,00% 5,15% 2,67% 3,13% 3,63% 3,59% 4,37% 4,62% 4,21%
01.02.20 6,25% 5,49% 2,88% 3,26% 3,59% 3,61% 4,55% 4,58% 4,23%
01.01.20 6,25% 5,93% 3,01% 3,38% 3,79% 3,73% 4,58% 4,84% 4,46%
01.12.19 6,50% 6,13% 3,66% 3,67% 3,68% 3,81% 4,71% 5,08% 4,66%
01.11.19 6,50% 6,17% 3,52% 3,60% 3,67% 3,85% 4,73% 4,97% 4,48%
01.10.19 7,00% 6,48% 3,73% 3,95% 4,21% 4,22% 5,02% 5,42% 4,90%
01.09.19 7,25% 6,71% 3,75% 4,07% 4,29% 4,58% 5,18% 5,49% 5,03%
01.08.19 7,25% 6,82% 3,86% 4,28% 4,68% 4,66% 5,33% 5,64% 5,18%
01.07.19 7,50% 7,14% 4,03% 4,35% 4,61% 4,89% 5,50% 5,71% 5,33%
01.06.19 7,75% 7,31% 3,92% 4,45% 4,75% 5,35% 5,78% 6,02% 5,72%
01.05.19 7,75% 7,52% 3,81% 4,41% 4,82% 5,22% 5,81% 6,05% 5,63%
01.04.19 7,75% 7,54% 4,50% 4,64% 4,75% 5,37% 6,03% 6,19% 5,72%
01.03.19 7,75% 7,68% 4,59% 4,73% 4,88% 5,24% 6,40% 6,43% 6,03%
01.02.19 7,75% 7,50% 4,32% 4,57% 4,77% 5,38% 6,61% 6,43% 6,15%
01.01.19 7,75% 7,53% 4,62% 4,80% 4,99% 5,18% 6,72% 5,90% 6,02%
01.12.18 7,50% 7,38% 5,35% 5,09% 4,70% 5,14% 5,83% 5,80% 5,62%
01.11.18 7,50% 7,18% 4,24% 4,62% 4,90% 5,01% 5,87% 6,36% 5,89%
01.10.18 7,50% 6,80% 3,69% 4,13% 4,57% 4,69% 5,51% 6,06% 5,54%
01.09.18 7,25% 6,61% 4,17% 4,26% 4,35% 4,59% 5,21% 5,52% 5,14%
02.08.18 7,25% 6,32% 4,18% 4,31% 4,74% 4,40% 5,25% 5,13% 4,98%

Вычисление среднего значения ряда чисел

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

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

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

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

Вычисляет среднее значение всех чисел в приведенном выше списке (9,5)

= СРЗНАЧ (A2: A4; A7)

Вычисление среднего значения первых трех значений и последнего числа в списке (7,5)

= СРЗНАЧЕСЛИ (a2: A7; “0″)

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

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

Скопируйте таблицу, расположенную ниже, на пустой лист.

Средневзвешенное значение с несколькими параметрами (Формулы/Formulas)

​условие​​Выделите ячейку A10, расположенную​​ (значения в столбце​ скобками (при использовании​5​ вам, с помощью​​ (​ более 1 раза,​: Подскажите, пожалуйста, как​ & «FROM ​ ответ.​В разделе​щелкните диапазон в​​в разделе​ текстом «Среднее качество​Совет:​ Первый способ позволяет​.​ слева от ячейки​ «Цена за единицу»).​​ примера данных —​20​

​ кнопок внизу страницы.​​А​

​ хотя бы подобрать​​ «​китин​Аргументы​ поле​Функция​ изделия» в примере​

​ При работе с данными,​​ быстро узнать среднее​Чтобы научиться вычислять средние​ с текстом «Среднее​

​A2:A7​​200​ Для удобства также​) будут содержаться одинаковые​ количества и цены,​ среднюю температуру для​sql = sql​

excelworld.ru>

Пример формулы для расчета средневзвешенной процентной ставки в Excel

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

С целью вычисления средневзвешенной процентной ставки доля для каждого инвестиционного объекта в общей стоимости портфеля умножается на процентную ставку доходности. Функция СУММПРОИЗВ идеально подходит для перемножения двух наборов данных (массивов) с последующим суммированием результатов. Функция может иметь максимальное количество аргументом до 255, разделенных точкой с запятой. Но в данной формуле необходимо использовать только лишь 2 аргумента.

В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.

Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.

Синтаксис и аргументы SUMPRODUCT

Синтаксис функции относится к компоновке функции и включает имя функции, скобки и аргументы. Синтаксис для SUMPRODUCT функция:

= SUMPRODUCT (array1, array2, array3, … array255)

Аргументами для функции SUMPRODUCT являются:

  • Array1: Первый аргумент массива (обязательно)
  • Array2, array3, … array255: Дополнительные (необязательные) массивы, до 255. С двумя или несколькими массивами функция умножает элементы каждого массива вместе, а затем добавляет результаты.

Элементы массива могут быть сотовыми ссылками на местоположение данных на листе или номерами, разделенными арифметическими операторами, такими как плюс (+) или минус (-) приметы. Если вы вводите числа, не разделенные операторами, Excel рассматривает их как текстовые данные.

  • Все аргументы массива должны быть одного размера. Другими словами, одинаковое количество элементов должно быть в каждом массиве. Если не, SUMPRODUCT возвращает #ЗНАЧЕНИЕ! значение ошибки.
  • Если какие-либо элементы массива не являются номерами, такими как текстовые данные, SUMPRODUCT рассматривает их как нули.

Вычисляем средневзвешенные значения при помощи СУММПРОИЗВ

Excel превратил вычисление среднего арифметического нескольких ячеек в очень простую задачу – просто используйте функцию СРЗНАЧ (AVERAGE). Но что делать, если некоторые значения имеют больший вес, чем другие? Например, на многих курсах тесты имеют больший вес, чем задания. Для таких случаев необходимо рассчитывать среднее взвешенное.

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

В эксель среднее значение

Excel предсталяет собой табличный процессор. Его можно использовать для создания разнообразных отчетов. В данной программе очень удобно производить разные вычисления. Многие не используют и половину возможностей Excel.

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

Стандартный способ вычисления

Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».

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

Этот способ хорош простотой и удобством. Но, у него имеются и существенные недостатки. С помощью этого способа можно произвести подсчет среднего значения только тех чисел, которые располагаются в ряд в одном столбце, или в одной строке. А вот, с массивом ячеек, или с разрозненными ячейками на листе, с помощью этого способа работать нельзя.

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

Среднее квадратическое отклонение: формула в Excel

Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.

Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.

Среднеквадратическое отклонение имеет привязку к масштабу исходных данных. Для образного представления о вариации анализируемого диапазона этого недостаточно. Чтобы получить относительный уровень разброса данных, рассчитывается коэффициент вариации:

среднеквадратическое отклонение / среднее арифметическое значение

Формула в Excel выглядит следующим образом:

СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).

Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.

Посчитать средневзвешенное значение на определенную дату

​ из них обладает​​: Уважаемый​​ In ActiveSheet.ListObjects(«tabl148»).ListColumns(1).Range​ Do​ сторону Power Pivot​ и введите туда​ либо добавлять в​ на вкладке​ у каждой из​ чем на 20​ Здорово, правда?​ обычного среднеарифметического, каждое​=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(​

​ будет подсчитывать все​​ получилось!​​ количество единиц товара,​

​ функцию: =СРЗНАЧЕСЛИ().​​ своими особенностями и​​abtextime​Found = False​Loop​​ и DAX -​ формулу для расчета​

​ сводную вычисляемое поле​​Конструктор (Design)​ них своя ценность/рисковость​

planetaexcel.ru>

​ копейки. Получить правильный​

  • Excel если значение ячейки то значение
  • Как в excel найти значения в таблице
  • Как в excel 2010 убрать нулевые значения
  • Как выявить в excel повторяющиеся значения
  • Выбор значения из списка в excel
  • Excel значение ячейки
  • Найти среднее значение в excel формула
  • Форматирование ячейки по значению другой ячейки excel
  • Как в excel сравнить значения в столбцах excel
  • Excel как найти значение в таблице
  • Excel поиск значения по нескольким условиям в excel
  • Excel последнее значение в столбце

Формула среднего значения в excel

Excel предсталяет собой табличный процессор. Его можно использовать для создания разнообразных отчетов. В данной программе очень удобно производить разные вычисления. Многие не используют и половину возможностей Excel.

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

В чем проблема?

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

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

В какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).

Средним значением в данном случае будет 195 150 рублей, что согласитесь, неправильно.

Как найти среднее арифметическое по условию

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

Наша задача вычислить среднее значение продаж ручек. Для этого проделаем следующие шаги:

  • В ячейке A13 напишем название товара “Ручки”;
  • В ячейке B13 введем формулу:

Диапазон ячеек “ А2:A10 ” указывает на список товаров, в котором мы будем искать слово “Ручки”. Аргумент A13 это ссылка на ячейку с текстом, который мы будем искать среди всего списка товаров. Диапазон ячеек “ B2:B10 ” это диапазон с данными продаж товаров, среди которых функция найдет “Ручки” и вычислит среднее значение.

Расчет среднего значения по условию

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

Для этих целей, используется функция «СРЗНАЧЕСЛИ». Как и функцию «СРЗНАЧ», запустить её можно через Мастер функций, из панели формул, или при помощи ручного ввода в ячейку. После того, как открылось окно аргументов функции, нужно ввести её параметры. В поле «Диапазон» вводим диапазон ячеек, значения которых будут участвовать в определении среднего арифметического числа. Делаем это тем же способом, как и с функцией «СРЗНАЧ».

А вот, в поле «Условие» мы должны указать конкретное значение, числа больше или меньше которого будут участвовать в расчете. Это можно сделать при помощи знаков сравнения. Например, мы взяли выражение «>=15000». То есть, для расчета будут браться только ячейки диапазона, в которых находятся числа большие или равные 15000. При необходимости, вместо конкретного числа, тут можно указать адрес ячейки, в которой расположено соответствующее число.

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

Когда все данные введены, жмем на кнопку «OK».

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

Как видим, в программе Microsoft Excel существует целый ряд инструментов, с помощью которых можно рассчитать среднее значение выбранного ряда чисел. Более того, существует функция, которая автоматически отбирает числа из диапазона, не соответствующие заранее установленному пользователем критерию. Это делает вычисления в приложении Microsoft Excel ещё более удобными для пользователей.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Как это работает

Давайте разберем каждую часть формулы, начиная с функции СУММПРОИЗВ (SUMPRODUCT), чтобы понять, как она работает. Функция СУММПРОИЗВ (SUMPRODUCT) вычисляет произведение оценки каждого задания на его вес, а затем суммирует все полученные произведения. Другими словами, функция находит сумму произведений (sum of the products), отсюда она и получила своё название. Итак, для Задания 1 умножаем 85 на 5, а для Теста умножаем 83 на 25.

Если Вас удивляет, зачем перемножать значения в первой части, представьте, что чем больше вес у задания, тем большее число раз мы должны учитывать оценку за него. Например, Задание 2 посчитано 5 раз, а Итоговый экзамен – 45 раз. Вот почему Итоговый экзамен имеет большее влияние на итоговую оценку.

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

Если бы Вы могли заглянуть под капот функции СУММПРОИЗВ (SUMPRODUCT), то увидели, что на самом деле она считает вот что:

К счастью, нам не нужно писать такую длинную формулу, поскольку СУММПРОИЗВ (SUMPRODUCT) делает всё это автоматически.

Сама по себе функция СУММПРОИЗВ (SUMPRODUCT) возвращает нам огромное число – 10450. В этот момент включается вторая часть формулы: /СУММ(C2:C9) или /SUM(C2:C9), которая возвращает результат в нормальный диапазон оценок, давая ответ 83,6.

Вторая часть формулы очень важна, т.к. позволяет автоматически корректировать вычисления. Помните, что веса не обязаны складываться в сумму 100%? Все это благодаря второй части формулы. Например, если мы увеличиваем одно или несколько значений весов, вторая часть формулы просто выполнит деление на большее значение, вновь приводя к правильному ответу. Или же мы можем сделать веса намного меньше, например, указать такие значения как 0,5, 2,5, 3 или 4,5, и формула по-прежнему будет работать правильно. Здорово, правда?

Подготавливаем таблицу

Если Вы собираетесь вычислять среднее взвешенное, Вам потребуется минимум два столбца. Первый столбец (в нашем примере – столбец B) содержит оценки для каждого задания или теста. Второй столбец (столбец C) содержит веса. Больший вес означает большее влияние задания или теста на итоговую оценку.

Чтобы понять, что такое вес, Вы можете представить его, как процент от итоговой оценки. На самом деле это не так, поскольку в таком случае веса в сумме должны составлять 100%. Формула, которую мы разберем в этом уроке, будет подсчитывать все правильно и не зависеть от суммы, в которую складываются веса.

Вычисление с помощью Мастера функций

Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.

Кликаем по ячейке, где хотим, чтобы выводился результат подсчета среднего значения. Жмем на кнопку «Вставить функцию», которая размещена слева от строки формул. Либо же, набираем на клавиатуре комбинацию Shift+F3.

Запускается Мастер функций. В списке представленных функций ищем «СРЗНАЧ». Выделяем его, и жмем на кнопку «OK».

Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.

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

Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.

После этого, жмите на кнопку «OK».

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

Оцените статью
Рейтинг автора
5
Материал подготовил
Андрей Измаилов
Наш эксперт
Написано статей
116
Добавить комментарий