Fruitsekta.ru

Мир ПК
11 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как рассчитать среднюю цену в excel

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

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

Допустим, вам нужно найти среднее количество дней для выполнения задач разными сотрудниками. Кроме того, вы хотите вычислить среднюю температуру на определенный день в течение 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 . в ВСИС примере рассчитываются средние цены, оплаченные за единицу в трех покупках, где каждая из них предназначена для разных единиц товара на разных единицах.

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

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

Во время работы с числовыми данными в Эксель нередко перед пользователями встает такая задача, как подсчет среднего значения. Математически данное действие выполняется путем деления суммы всех чисел на их количество. А как это сделать в Excel? Давайте разбираться.

Информация в строке состояния

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

Если данная информация недоступна, скорее всего, соответствующий пункт выключен в настройках. Чтобы обратно его включить, щелкаем правой кнопкой мыши по строке состояния, в открывшемся списке проверяем наличие флажка напротив строки “Среднее”. Поставить его в случае необходимости можно простым щелчком левой кнопки мыши.

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

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

Использование арифметического выражения

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

  1. Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
    =(Число1+Число2+Число3. )/Количество_слагаемых .
    Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
    Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 .
  2. Когда все готово, жмем Enter, чтобы получить результат.

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

Инструменты на ленте

Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:

  1. Выделяем диапазон ячеек с числовыми данными, для которых мы хотим определить среднее значение.
  2. Переходим во вкладку “Главная” (если находимся не в ней). В разделе инструментов “Редактирование” находим значок “Автосумма” и щелкаем по небольшой стрелке вниз рядом с ним. В раскрывшемся перечне кликаем по варианту “Среднее”.
  3. Сразу же под выделенным диапазоном отобразится результат, который и является средним значением по всем отмеченным ячейкам.
  4. Если мы перейдем в ячейку с результатом, то в строке формул увидим, какая функция была использована программой для расчетов – это оператор СРЗНАЧ, аргументами которого является выделенный нами диапазон ячеек.
Читать еще:  Построение градуировочного графика в excel

Примечание: Если вместо вертикального выделения (столбца целиком или его части) будет выполнено горизонтальное выделение, то результат отобразится не под областью выделения, а справа от нее.

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

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

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

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

Альтернативный способ использования “Среднее” на ленте:

  1. Переходим в первую же свободную ячейку после столбца или строки (в зависимости от структуры данных) и жмем кнопку расчета среднего значения.
  2. Вместо моментального вывода результата на этот раз программа предложит нам предварительно проверить диапазон ячеек, по которому будет считаться среднее значение, и в случае необходимости скорректировать его координаты.
  3. По готовности жмем клавишу Enter и получаем результат в заданной ячейке.

Использование функции СРЗНАЧ

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

  1. Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
  2. В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
  3. На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
  4. Получаем результат в выбранной ячейке.
  5. Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
  6. Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
  7. Все готово. Теперь результат выглядит намного привлекательнее.

Инструменты во вкладке “Формулы”

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

  1. Встаем в ячейку, в которой планируем выполнить расчеты. Переключаемся во вкладку “Формулы”. В разделе инструментов “Библиотека функций” щелкаем по значку “Другие функции”, в раскрывшемся перечне выбираем группу “Статистические”, затем – “СРЗНАЧ”.
  2. Откроется уже знакомое окно аргументов выбранной функци. Заполняем данные и жмем кнопку OK.

Ввод функции в ячейку вручную

Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.

В общем, синтаксис функции СРЗНАЧ выглядит так:

В качестве аргументов могут выступать как ссылки на отельные ячейки (диапазоны ячеек), так и конкретные числовые значения.

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

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

Безусловно, такой метод нельзя назвать удобным, но иногда, при небольшом объеме данных, и он вполне может использоваться.

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

Помимо перечисленных выше методов, в Эксель также предусмотрена возможность расчета среднего значения по заданному пользователем условию. Как следует из описания, участвовать в общем подсчете будут только числа (ячейки с числовыми данными), соответствующие какому-то конкретному условию.

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

  1. Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
  2. В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
  3. Откроются аргументы функции, после заполнения которых кликаем OK:
    • в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
    • в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
    • поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
  4. Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.
Читать еще:  Как в excel отключить округление чисел

Заключение

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

Как рассчитать среднюю цену в excel

Модератор форума: _Boroda_, Manyasha, SLAVICK, китин
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Расчёт средней стоимости в зависимости от количества (Формулы/Formulas)

Расчёт средней стоимости в зависимости от количества

yanpriestДата: Четверг, 14.11.2019, 09:40 | Сообщение № 1

Прошу помочь в решении следующей задачи.

В таблице всего четыре колонки с числами:
— Просмотры 1
— CPM 1
— Просмотры 2
— CPM 2

Просмотры — количество просмотров рекламы.
CPM — стоимость тысячи просмотров.
Цифры 1 и 2 означают рекламный канал.

Получается:
Просмотры 1 — кол-во просмотров с первого канала.
CPM 1 — стоимость тысячи просмотров на первом канале.
Просмотры 2 — кол-во просмотров на втором канале.
CPM 2 — стоимость тысячи просмотров на втором канале.

Нужно рассчитать средний CPM (среднюю стоимость тысячи просмотров) между двумя рекламными каналами на каждый день (см. столбец «Средний CPM»).

Находить среднее значение между CPM 1 и CPM 2 некорректно, т.к. количество просмотров у каждого канала разное и результат получается некорректным.

Я пробовал находить среднюю стоимость одного просмотра на каждом канале, потом считал среднее и умножал на общее кол-во просмотров.
Вот так:

Это тоже неправильно.

Пожалуйста, помогите решить задачу.
Пример прикрепляю.

P.S. Если где-то нули, формула должна учитывать это.

Прошу помочь в решении следующей задачи.

В таблице всего четыре колонки с числами:
— Просмотры 1
— CPM 1
— Просмотры 2
— CPM 2

Просмотры — количество просмотров рекламы.
CPM — стоимость тысячи просмотров.
Цифры 1 и 2 означают рекламный канал.

Получается:
Просмотры 1 — кол-во просмотров с первого канала.
CPM 1 — стоимость тысячи просмотров на первом канале.
Просмотры 2 — кол-во просмотров на втором канале.
CPM 2 — стоимость тысячи просмотров на втором канале.

Нужно рассчитать средний CPM (среднюю стоимость тысячи просмотров) между двумя рекламными каналами на каждый день (см. столбец «Средний CPM»).

Находить среднее значение между CPM 1 и CPM 2 некорректно, т.к. количество просмотров у каждого канала разное и результат получается некорректным.

Я пробовал находить среднюю стоимость одного просмотра на каждом канале, потом считал среднее и умножал на общее кол-во просмотров.
Вот так:

Это тоже неправильно.

Пожалуйста, помогите решить задачу.
Пример прикрепляю.

P.S. Если где-то нули, формула должна учитывать это. yanpriest

Прошу помочь в решении следующей задачи.

В таблице всего четыре колонки с числами:
— Просмотры 1
— CPM 1
— Просмотры 2
— CPM 2

Просмотры — количество просмотров рекламы.
CPM — стоимость тысячи просмотров.
Цифры 1 и 2 означают рекламный канал.

Получается:
Просмотры 1 — кол-во просмотров с первого канала.
CPM 1 — стоимость тысячи просмотров на первом канале.
Просмотры 2 — кол-во просмотров на втором канале.
CPM 2 — стоимость тысячи просмотров на втором канале.

Нужно рассчитать средний CPM (среднюю стоимость тысячи просмотров) между двумя рекламными каналами на каждый день (см. столбец «Средний CPM»).

Находить среднее значение между CPM 1 и CPM 2 некорректно, т.к. количество просмотров у каждого канала разное и результат получается некорректным.

Я пробовал находить среднюю стоимость одного просмотра на каждом канале, потом считал среднее и умножал на общее кол-во просмотров.
Вот так:

Это тоже неправильно.

Пожалуйста, помогите решить задачу.
Пример прикрепляю.

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

Основная идея

Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:

Свободное место, допустим, только одно, и наша задача — выбрать достойного.

Первое, что обычно приходит в голову — это рассчитать классический средний балл с помощью стандартной функции Excel СРЗНАЧ (AVERAGE).

На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется «Программирование», а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.

Средневзвешенное — это среднее с учетом различной ценности (веса, важности) каждого из элементов.

В бизнесе средневзвешенное часто используется в таких задачах, как:

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

Расчет средневзвешенного формулами

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

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

Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉

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

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

Обратите внимание, что я преобразовал ее в «умную» таблицу с помощью команды Главная — Форматировать как таблицу (Home — Format as Table) и дал ей на вкладке Конструктор (Design) имя Data.

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

=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33

То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.

Правда, с реализацией этой нехитрой логики именно в сводной таблице нас ждет небольшой облом. Если вы работали со сводными раньше, то, наверное, помните, что можно легко переключить поле значений сводной в нужную нам функцию, щелкнув по нему правой кнопкой мыши и выбрав команду Итоги по (Summarize Values By) :

В этом списке есть среднее, но нет средневзвешенного 🙁

Можно частично решить проблему, если добавить в исходную таблицу вспомогательный столбец, где будет считаться стоимость каждой сделки:

Теперь можно рядом закинуть в область значений стоимость и количество — и мы получим почти то, что требуется:

Останется поделить одно на другое, но сделать это, вроде бы, простое математическое действие внутри сводной не так просто. Придется либо добавлять в сводную вычисляемое поле (вкладка Анализ — Поля, элементы, наборы — Вычисляемое поле), либо считать обычной формулой в соседних ячейках или привлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , о которой я уже писал. А если завтра изменятся размеры сводной (ассортимент товаров), то все эти формулы придется вручную корректировать.

В общем, как-то все неудобно, трудоемко и нагоняет тоску. Да еще и дополнительный столбец в исходных данных нужно руками делать. Но красивое решение есть.

Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX

Если у вас Excel 2013-2016, то в него встроен супермощный инструмент для анализа данных — надстройка Power Pivot, по сравнению с которой сводные таблицы с их возможностями — как счеты против калькулятора. Если у вас Excel 2010, то эту надстройку можно совершенно бесплатно скачать с сайта Microsoft и тоже себе установить. С помощью Power Pivot расчет средневзвешенного (и других невозможных в обычных сводных штук) очень сильно упрощается.

1. Для начала, загрузим нашу таблицу в Power Pivot. Это можно сделать на вкладке Power Pivot кнопкой Добавить в модель данных (Add to Data Model) . Откроется окно Power Pivot и в нем появится наша таблица.

2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Несколько нюансов по формуле:

  • В Power Pivot есть свой встроенный язык с набором функций, инструментов и определенным синтаксисом, который называется DAX. Так что можно сказать, что эта формула — на языке DAX.
  • Здесь WA — это название вычисляемого поля (в Power Pivot они еще называются меры), которое вы придумываете сами (я называл WA, имея ввиду Weighted Average — «средневзвешенное» по-английски).
  • Обратите внимание, что после WA идет не равно, как в обычном Excel, а двоеточие и равно.
  • При вводе формулы будут выпадать подсказки — используйте их.
  • После завершения ввода формулы нужно нажать Enter , как и в обычном Excel.

3. Теперь строим сводную. Для этого в окне Power Pivot выберите на вкладке Главная — Сводная таблица (Home — Pivot Table). Вы автоматически вернетесь в окно Excel и увидите привычный интерфейс построения сводной таблицы и список полей на панели справа. Осталось закинуть поле Наименование в область строк, а нашу созданную формулой меру WA в область значений — и задача решена:

Вот так — красиво и изящно.

Общая мораль: если вы много и часто работаете со сводными таблицами и вам их возможности «тесноваты» — копайте в сторону Power Pivot и DAX — и будет вам счастье!

Ссылка на основную публикацию
Adblock
detector