Как рассчитать среднее значение в Excel. Инструкция по расчету среднего значения в таблице Эксель

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

Сведения о строке состояния в Excel

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

6 способов расчета среднего значения в Excel

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

Ввод функции в ячейку ручным способом

А теперь настало время познакомиться с основной функцией калькуляции среднего значения – СРЗНАЧ. О ней мы будем еще очень много говорить. Она имеет такой синтаксис: =СРЗНАЧ(число1;2;3). Как видим, синтаксис этой функции очень простой. Нужно написать сам оператор, а потом через точку с запятой перечислить числа, ячейки или диапазоны, учитываемые для калькуляции среднего арифметического.

Определение среднего значения с помощью инструментов во вкладке «Формулы»

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

Расчет среднего значения через формулу СРЗНАЧ

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

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

Инструменты на ленте для расчета среднего значения

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

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

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

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

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

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

[expert_bq id=»1570″]В новом открывшемся окне необходимо найти поле Категория , указать Статические с помощью которого задается конкретная функция для расчета. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Перейти в меню во вкладку «Формулы», там выбрать в левом верхнем углу «Автосумма». При нажатии на стрелку, чтобы раскрыть все функции рядом с этой кнопкой, открываются несколько функций быстрого набора. Там следует выбрать «Среднее».

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

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

Как сделать среднее значение в excel график?

Чаще всего используются обычный линейный тренд и линия скользящего среднего. Линейный тренд – это прямая линия, расположенная таким образом, чтобы расстояние от неё до любой из точек графика было минимальным. Эта линия полезна в том случае, если есть уверенность, что последующие данные будут соответствовать тому же шаблону.

Линии тренда в Excel – это отличный способ получить больше информации об имеющемся наборе данных и о том, как они изменяются со временем. Линейный тренд и скользящее среднее – два типа линий тренда, наиболее распространённых и полезных для бизнеса.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

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

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

Как найти среднее арифметическое чисел?

Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.

Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:

1. Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» — кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.
2. В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
3. Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».

Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).

Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:

Среднее значение по условию

Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

Найти среднее арифметическое чисел, которые больше или равны 10.

Результат использования функции СРЗНАЧЕСЛИ по условию «>=10»:

Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.

Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

В результате вычисления функции получаем следующее значение:

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

Как посчитать средневзвешенную цену в Excel?

Как посчитать средний процент в Excel? Для этой цели подойдут функции СУММПРОИЗВ и СУММ. Таблица для примера:

С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ — сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Панель формул

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

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

Ручной ввод функции

Но, не забывайте, что всегда при желании можно ввести функцию «СРЗНАЧ» вручную. Она будет иметь следующий шаблон: «=СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число)).

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

[expert_bq id=»1570″]Чтобы определить вес узлов с помощью средства Поиск решения, при котором значение среднего абсолютных отклонений было бы минимально, выполните следующие действия. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Полученные результаты показывают, что оптимальное распределение весов таково, что весь вес сосредоточен на самом последнем наблюдении, при этом значение среднего абсолютных отклонений равно 7,56 (см. также Рисунок 59). Этот результат подтверждает предположение о том, что более поздние наблюдения должны иметь больший вес.

Метод скользящей средней в Microsoft Excel. Разработка прогноза с помощью метода скользящей средней. Пример решения задачи

1. Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» — кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.
2. В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
3. Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».

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

Как рассчитать скользящую среднюю в Google Таблицах

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

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

Что такое простая скользящая средняя (SMA)?

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

Простое скользящее среднее — это среднее значение цен акций (обычно цен закрытия акций) за выбранный период времени. Этот период времени обычно равен количеству торговых дней, в течение которых рассчитывается SMA. Например, SMA, рассчитанная за 5 дней, называется 5-дневной SMA. Точно так же у вас может быть 10-дневная SMA, 20-дневная SMA и так далее.

SMA может помочь определить, будет ли цена акции продолжать бычий или медвежий тренд или изменит направление.

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

Скользящее среднее в Google Таблицах и как его рассчитать

Если вы хотите рассчитать простую скользящую среднюю в Google Таблицах, вам необходимо сначала получить доступ к данным о запасах, которые обычно представляют собой цены закрытия ваших необходимых акций за определенный период времени.

В Google Таблицах вы можете напрямую импортировать эту информацию с помощью функции GOOGLEFINANCE. Как только вы получите необходимые данные о запасах, вы можете продолжить и использовать функцию AVERAGE для вычисления простой скользящей средней.

Давайте разберемся, как эти две функции — GOOGLEFINANCE и AVERAGE — работают в Google Таблицах.

Функция GOOGLEFINANCE

Функция GOOGLEFINANCE специально предназначена для получения данных о ценах на акции. Она в основном получает доступ к текущей или исторической информации об акциях с веб-сайта Google Finance.

• ticker — это символ или инициалы, соответствующие вашей акции. Он может состоять как из биржевого символа, так и из тикера акции. Например, «NASDAQ: GOOG» представляет акции «GOOG». Вы также можете просто использовать символ «GOOG».
• атрибут — это значение, к которому вы хотите получить доступ, относящийся к данному тикеру, например, «цена», «максимум», «минимум», «объем» и т. д. Если вы не укажете этот атрибут, функция получит цену относящиеся к данному тикеру.
• start_date — это начальная дата, с которой вы хотите получить атрибут
• end_date — это конечная дата, до которой вы хотите получить атрибут. num_days — количество дней, в течение которых вы хотите получить атрибут. Вы можете указать значение end_date или num_days для этого параметра.
• интервал определяет, как часто вы хотите возвращать данные. Это может быть «ежедневно» или «еженедельно».

Итак, чтобы получить цены закрытия акций GOOG, мы используем функцию GOOGLEFINANCE следующим образом:

Вышеупомянутая функция извлекает цены закрытия NASDAQ: GOOG, начиная с 27.02.2024 и в течение 10 дней после этого.

Функция AVERAGE

Эта функция находит среднее значение заданного набора значений. Синтаксис этой функции следующий:

• value1 — первое значение, которое следует учитывать при вычислении среднего
• value2,… — дополнительные значения, которые вы хотите учитывать при вычислении среднего значения. Эти значения не являются обязательными.

Итак, чтобы найти среднее значение в диапазоне A2: A6, мы используем функцию AVERAGE следующим образом:

Расчет скользящего среднего в Google Таблицах с использованием GOOGLEFINANCE и AVERAGE

Расчет скользящего среднего просто заключается в нахождении среднего значения за заданное количество дней. Допустим, мы хотим рассчитать 5-дневную SMA для активов GOOG за последний месяц.

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

Здесь TODAY () возвращает текущую дату, а TODAY () — 30 возвращает дату за 30 дней до текущей даты.

Обратите внимание, что количество отображаемых строк меньше 30. Это связано с тем, что функция GOOGLEFINANCE возвращает сведения только о торговых днях. Любые неторговые дни не указываются.

Теперь давайте рассчитаем 5-дневную скользящую среднюю в столбце C. Выберите 5-ю ячейку столбца и введите формулу, а затем нажмите клавишу возврата:

Будет отображено 5-дневное скользящее среднее с 24.08.2024 по 30.08.2024.

Теперь мы хотим рассчитать 5-дневную скользящую среднюю, начиная с 24.08.2024 и заканчивая 22.09.2024. Поэтому нам нужно скопировать формулу в остальные ячейки столбца, перетащив маркер заполнения ячейки C6.

Как видите, скользящее среднее за 5 дней за раз отображается в столбце C. Каждая ячейка столбца B отображает среднее значение за предыдущие 5 дней.

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

Расчет скользящего среднего в Google Таблицах с помощью GOOGLEFINANCE и QUERY

Более эффективным способом было бы прямое отображение SMA для каждой акции за последние N дней. Вы можете сделать формулу динамической, чтобы значение всегда обновлялось в зависимости от текущей даты и отображало SMA, начиная с N дней до текущей даты.

Поэтому, если вы хотите отобразить, скажем, 5-дневные SMA для следующих 6 активов, вы можете использовать комбинацию функций GOOGLEFINANCE и QUERY.

Вы можете использовать следующую формулу:

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

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

Объяснение формулы

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

Эта функция, как объяснялось в предыдущем разделе, просто извлекает цены акций на момент закрытия биржевого тикера, указанного в ячейке A2, за последние 5 дней до текущей даты.

Функция GOOGLEFINANCE возвращает и дату, и цены, но нам нужны только цены на акции, которые находятся в столбце 2. Поэтому мы используем функцию QUERY, чтобы извлечь только второй столбец.

Функция QUERY помогает нам запускать SQL-подобные запросы к данной таблице. Мы передаем диапазон или таблицу, к которой мы хотим применить запрос, в качестве первого параметра, а запрос, который мы хотим применить, в качестве второго параметра.

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

Функция QUERY возвращает значения второго столбца (которые состоят из цен на акции на момент закрытия за последние 5 торговых дней). Поэтому мы применяем функцию AVERAGE к этим возвращаемым значениям, чтобы получить 5-дневную SMA.

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

Заключение

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

Мы надеемся, что это руководство было для вас полезным и простым в использовании.

[expert_bq id=»1570″]При использовании метода для торговли по запаздывание на входе и на выходе из как правило очень значительно, поэтому в большинстве случаев теряется большая часть движения. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).

Как сделать среднее значение в excel график?

• ticker — это символ или инициалы, соответствующие вашей акции. Он может состоять как из биржевого символа, так и из тикера акции. Например, «NASDAQ: GOOG» представляет акции «GOOG». Вы также можете просто использовать символ «GOOG».
• атрибут — это значение, к которому вы хотите получить доступ, относящийся к данному тикеру, например, «цена», «максимум», «минимум», «объем» и т. д. Если вы не укажете этот атрибут, функция получит цену относящиеся к данному тикеру.
• start_date — это начальная дата, с которой вы хотите получить атрибут
• end_date — это конечная дата, до которой вы хотите получить атрибут. num_days — количество дней, в течение которых вы хотите получить атрибут. Вы можете указать значение end_date или num_days для этого параметра.
• интервал определяет, как часто вы хотите возвращать данные. Это может быть «ежедневно» или «еженедельно».

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A500:$A$12;A7;$B500:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.