Как найти среднее значение на графике в excel
Найти среднее значение довольно просто. Все зависит от Ваших целей. В одном случае, можно определить его через формулу, а в другом, посмотреть в специальной строке. Я предлагаю на примере рассмотреть возможные варианты.
Вводные данные.
Допустим, у нас есть некий диапазон значений:
Способ 1. Пишем формулу вручную.
Выделяем любую свободную ячейку и вводим формулу " =СУММ(A1:A7)/7 ":
Разберем формулу подробнее:
- Значок " равно " - означает, что в ячейке вводится формула
- Слово " СУММ " - осуществляет сложение диапазона ячеек, указанных в скобках. В нашем случае начиная с A1 до A7 .
- Слово " /7 " - осуществляет обычное математическое деление на цифру 7. Число 7 соответствует количеству ячеек.
Нажимаем " Enter ". Проверяем результат:
Мы выяснили, что среднее значение будет равно 150.
Способ 2. Строка состояния.
Если не нужно в Excel отображать результат среднего значения, но необходимо его вычислить, то можно обратиться к строке состояния.
Выделяем диапазон значений и смотрим вниз документа:
Среднее значение равно 150, все верно.
По умолчанию, данное действие должно отображаться в строке состояния. Если его нет, то достаточно его включить. Для этого, наводим курсор на строку состояния, нажимаем правую кнопку мыши и ставим галочку в поле " Среднее ":
Способ 3. Функции.
Есть еще один способ узнать среднее значение. Он хорош тем, что формируется автоматически. Вам не нужно даже разбираться в формуле, хотя на выходе она и получается.
Выделяем диапазон значений, на вкладке " Главная " находим кнопку функции и выбираем " Среднее ":
Посмотрим на результат:
Программа автоматически сформировала ячейку с формулой . Я мог её использовать еще в первом способе, но решил, что лучше сделать другую формулу, чтобы не повторяться.
Функция " СРЗНАЧ " как Вы догадались, отвечает за определение среднего значения в диапазоне, указанного в скобках.
Во время работы с числовыми данными в Эксель нередко перед пользователями встает такая задача, как подсчет среднего значения. Математически данное действие выполняется путем деления суммы всех чисел на их количество. А как это сделать в Excel? Давайте разбираться.
Информация в строке состояния
Пожалуй, это самый легкий и быстрый способ определения среднего значения. Для этого достаточно выделить диапазон, содержащий от двух ячеек и более, и среднее значение по ним сразу же отобразится в строке состояния программы.
Если данная информация недоступна, скорее всего, соответствующий пункт выключен в настройках. Чтобы обратно его включить, щелкаем правой кнопкой мыши по строке состояния, в открывшемся списке проверяем наличие флажка напротив строки “Среднее”. Поставить его в случае необходимости можно простым щелчком левой кнопки мыши.
Расчет среднего значения
Когда среднее значение нужно не только определить, но и зафиксировать в отдельной выбранной для этого ячейке, можно использовать несколько методов. Ниже мы подробно рассмотрим каждый из них.
Использование арифметического выражения
Как мы знаем, среднее значение равняется сумме чисел, разделенных на их количество. Данную формулу можно использовать и в Экселе.
- Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
=(Число1+Число2+Число3. )/Количество_слагаемых .
Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 . - Когда все готово, жмем Enter, чтобы получить результат.
Данный метод, безусловно хорош, но удобство его использования существенно ограничено объемом обрабатываемых данных, ведь на перечисление всех чисел или координат ячеек в большом массиве уйдет немало времени, к тому же, в этом случае не исключена вероятность допущения ошибки.
Инструменты на ленте
Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:
Примечание: Если вместо вертикального выделения (столбца целиком или его части) будет выполнено горизонтальное выделение, то результат отобразится не под областью выделения, а справа от нее.
Данный метод, достаточно прост и позволяет быстро получить нужный результат. Однако помимо очевидных плюсов, есть у него и минус. Дело в том, что он позволяет вычислить усредненное значение только по ячейкам, расположенными подряд, причем, только в одном столбце или строке.
Чтобы было нагляднее, разберем следующую ситуацию. Допустим, у нас есть две заполненные данными строки. Мы хотим получить среднее значение сразу по двум строкам, следовательно, выделяем их и применяем рассмотренный инструмент.
В результате, мы получим средние значения под каждым столбцом, что тоже неплохо, если преследовалась именно такая цель.
Но если, все же, требуется определить среднее значение по нескольким строкам/столбцам или разбросанным в разных местах таблицы ячейкам, пригодятся методы, описанные далее.
Альтернативный способ использования “Среднее” на ленте:
Использование функции СРЗНАЧ
С данной функцией мы уже успели познакомиться, когда перешли в ячейку с результатом расчета среднего значения. Теперь давайте научимся полноценно ею пользоваться.
- Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
- В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
- На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
- Получаем результат в выбранной ячейке.
- Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
- Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
- Все готово. Теперь результат выглядит намного привлекательнее.
Инструменты во вкладке “Формулы”
В программе Excel есть специальная вкладка, отвечающая за работу с формулами. В случае с расчетом среднего значения, она тоже может пригодиться.
Ввод функции в ячейку вручную
Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.
В общем, синтаксис функции СРЗНАЧ выглядит так:
В качестве аргументов могут выступать как ссылки на отельные ячейки (диапазоны ячеек), так и конкретные числовые значения.
Просто встаем в нужную ячейку и, поставив знак “равно”, пишем формулу, перечислив аргументы через символ “точка с запятой”. Вот, как это выглядит со ссылками на ячейки в нашем случае. Допустим, мы решили включить в подсчет всю первую строку и только три значения из второй:
Когда формула полностью готова, нажимаем клавишу Enter и получаем готовый результат.
Безусловно, такой метод нельзя назвать удобным, но иногда, при небольшом объеме данных, и он вполне может использоваться.
Определение среднего значения по условию
Помимо перечисленных выше методов, в Эксель также предусмотрена возможность расчета среднего значения по заданному пользователем условию. Как следует из описания, участвовать в общем подсчете будут только числа (ячейки с числовыми данными), соответствующие какому-то конкретному условию.
Допустим, нам нужно посчитать среднее значение только по положительным числам, т.е. тем, которые больше нуля. В этом случае, нас выручит функция СРЗНАЧЕСЛИ.
- Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
- В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
- Откроются аргументы функции, после заполнения которых кликаем OK:
- в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
- в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
- поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
- Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.
Заключение
Таким образом, в Экселе существует немало способов для нахождения среднего значения как по отдельным строкам и столбцам, так и по целым диапазонам ячеек, которые, к тому же, могут быть разбросаны по таблице. А использование того или иного метода определяется удобством и целесообразностью его использования в каждом конкретном случае.
В данной статье рассмотрим на примерах, самые популярные способы расчёта среднего значения в MS Excel. Предположим, что у нас есть столбец с числами и нам нужно рассчитать среднее значение этих чисел.
Первый способ как рассчитать среднее значение в Excel.
Если выделить диапазон ячеек, по которым нужно рассчитать среднее значение, то Excel, автоматически рассчитает среднее значение по этому диапазону и покажет его в правом нижнем углу окна программы. Ехсel сделает это по умолчанию, без каких-либо дополнительных действий со стороны пользователя ПО.
Второй способ как рассчитать среднее значение в Excel.
Рассмотрим возможные варианты рассчитать среднее значения с помощью кнопки вызова функции СРЗНАЧ, которая находиться в закладке Главная.
Выбираем диапазон ячеек, ко которому нужно рассчитать среднее значение. Нажимаем на кнопку Среднее. Функция рассчитает среднее значение чисел в выделенном диапазоне ячеек, а само среднее значение отобразиться в ячейки под выделенным диапазоном. В нашем примере, это С13.
Тоже самое произойдет, если вы сначала станете на ячейку под нужным Вам диапазоном (в нашем случае это С13), затем нажмете на кнопку Среднее. Функция автоматически определит диапазон ячеек, по которым рассчитает сведение значение.
Сама формула функции, в ячейки, будет выглядеть вот так: =СРЗНАЧ(C3:C12).
С3:С12, это диапазон ячеек в столбце, по которым считается среднее значение.
Нужный нам диапазон можно изменить, поменяв адреса ячеек диапазона прямо в формуле функции. Например, заменить С3 на С6, тогда среднее значение будет рассчитано только по ячейкам столбца, начиная с С6 по С12.
Предположим, что нам необходимо рассчитать среднее значение не по всему столбцу, а по конкретным ячейкам в самом столбце. Для этого нужно в самой формуле функции через точку с запятой (;) прописать адреса ячеек, по которым мы хотим рассчитать среднее значение. Например, это будут ячейки С6, С8 и С12.
Выглядит это вот так:
Третий способ как рассчитать среднее значение в Excel.
Воспользуемся кнопкой вызова функции: Вставить функцию.
Выбираем ячейку, в которой должно появиться среднее значение, которое нам нужно рассчитать. Например, в ячейки Н3. После нажатия на кнопку Вставить функцию, появиться диалоговое окно Вставка функции, в котором будет перечень 10 недавно использовавшихся функций, в числе которых может быть интересующее нас функция: СРЗНАЧ. Если данной функции в перечне не будет, нужно воспользоваться поиском. В поле поиска нужно прописать СРЗНАЧ и нажать Найти.
Нажимаем ОК. Появляется диалоговое окно Аргументы функции.
В поле Число 1, нужно внести диапазон ячеек, по которым нужно рассчитать среднее значение. Становимся курсором на поле Число 1 и выделяем нужный нам диапазон ячеек.
Нажимаем ОК. Получаем среднее значение по заданному диапазону ячеек.
В поле Число 2, можно добавить еще один диапазон ячеек. И тогда функция будет считать среднее по двум диапазонам. Причем, если добавить диапазон в поле Число 2, то в диалоговом окне Аргументы функции появиться еще одно поле: Число 3. В которое так же можно добавить диапазон ячеек. Соответственно, если добавить диапазон в поле Число 3, появиться поле Число 4 и так далее.
В поля Число 1, Число 2, Число 3 и т.д., можно, в место диапазона ячеек, указывать отдельные ячейки и рассчитывать по ним среднее значение. Ячейки при этом могут находится в разных частях Листа.
Диапазоны ячеек или отдельные ячейки, для подсчета среднего значения, могут находиться на разных Листах книги. Само искомое среднее значение, так же может находиться на отдельном листе от исходных данных. Алгоритм работы с функцией СРЗНАЧ в током случае не будет отличаться от описанного выше.
Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. Вычислим скользящее среднее с помощью надстройки MS EXCEL Пакет анализа, формулами и с помощью линии тренда на диаграмме.
Метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда.
В случае усреднения за 3 периода скользящее среднее равно:
Y скол.i =(Y i + Y i-1 + Y i-2 )/3
На картинке ниже показано как вычислить в MS EXCEL скользящее среднее путем усреднения значений за три периода (за два предыдущих и один текущий).
Примечание : В англоязычной литературе для скользящего среднего используется термин Moving Average (MA) или Simple Moving Average (SMA) , а также rolling average, running average, moving mean.
Получить ряд, сглаженный методом скользящего среднего, можно с помощью надстройки MS EXCEL Пакет анализа (Analysis ToolPak) . Надстройка доступна из вкладки Данные, группа Анализ (EXCEL 2010).
В поле Интервал установим значение 3 – будем усреднять значения ряда за 3 периода. В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
Диаграмма
Вычисление погрешности
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
=КОРЕНЬ(СУММКВРАЗН(B9:B11;D9:D11)/3)
Т.е. данная погрешность вычисляется по формуле:
Значения y i – это значения исходного ряда в период i. Значения «yi с крышечкой» - значения ряда, полученного методом скользящего среднего, в тот же в период i. Значение n равно 3, т.к. в нашем случае усреднение производится 3 периода.
Формула погрешности совпадает с выражением среднеквадратичной ошибки (англ. RMSE – Root Mean Squared Errors, квадратный корень из среднего значения квадратов ошибок), но вычисленной не для всей выборки (ряда), а только на интервале сглаживания (в нашем случае за 3 периода).
Обычно рассчитывается 2 типа ошибок: ошибка сглаживания (ошибка подгонки модели; англ. fitting errors или residuals) и ошибка прогнозирования (forecast errors).
Погрешности, вычисленные надстройкой Пакет анализа, являются ошибками прогнозирования. Эту погрешность можно использовать, чтобы рассчитать интервал предсказания (prediction interval). Про вычисление прогнозного значения и его интервала предсказания см. статью Прогнозирование методом скользящего среднего .
Отметим, что MS EXCEL вычисляет целый массив погрешностей (столбец Е), но для построения интервала предсказания необходимо только последнее значение.
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении количества периодов усреднения приходится перезапускать расчет, вызывая Надстройку заново.
В файле примера на листе Формулы создана форма для автоматического пересчета скользящего среднего в зависимости от количества периодов.
Значения сглаженного ряда вычисляются с помощью формулы:
= ЕСЛИ(A11
в ячейке D8 содержится количество периодов усреднения. Про функцию СМЕЩ() можно прочитать в этой статье .
Погрешности вычисляются по формуле:
= КОРЕНЬ(СУММКВРАЗН(СМЕЩ(B11;-$D$8+1;;$D$8);СМЕЩ(C11;-$D$8+1;;$D$8))/$D$8)
Выбор количества периодов усреднения для удобства осуществляется с помощью элемента управления Счетчик .
На диаграмме с помощью линии тренда можно построить график Скользящего среднего с заданным количеством периодов усреднения.
Используем данные файла примера на листе Формулы . Сначала построим ряд скользящего среднего с 5-ю периодами усреднения с помощью формул.
Теперь построим линию тренда, которая совпадет с красным графиком «Сглаженный ряд». Для этого:
• Щелкните диаграмму, чтобы выделить ее.
• Выберите ряд данных, к которому нужно добавить график скользящего среднего (синий график).
• На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите пункт Дополнительные параметры линии тренда.
• В открывшемся окне выберите Линейная фильтрация и установите в поле Точки значение 5.
После закрытия окна будет выведен график скользящего среднего, полностью совпадающий с красным графиком, ранее построенным с помощью формул.
Примечание : У метода Скользящее среднее есть несколько модификаций, которые рассмотрены в одноименной статье.
Читайте также: