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

Добавил пользователь Alex
Обновлено: 31.08.2024

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

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

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

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

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

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

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

Одна из самых интересных функций, позволяющих определить среднее значение – это возможность его вычислить с ориентировкой на предварительно указанное условие. То есть, брать в расчет программа будет только значения, соответствующие конкретному критерию. Например, нам следует получить среднее арифметическое лишь тех числовых значений, которые превышают ноль. В такой ситуации нужна функция СРЗНАЧЕСЛИ. Чтобы ее использовать, нужно совершить такую последовательность шагов:

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

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

Чтобы записать функцию СРЗНАЧ своими руками, необходимо записать в ячейке знак = (это универсальный символ ввода формулы), после чего написать большими буквами СРЗНАЧ (программа автоматически подскажет эту функцию в перечне, поэтому достаточно будет нажать по подсказке левой кнопкой мыши один раз), после чего открыть скобку и вводить аргументы. Ввод аргументов может осуществляться несколькими способами. Самый простой – это нажать на соответствующие ячейки или выделение соответствующих диапазонов. После этого нужно поставить разделитель – точку с запятой (;) и ввести следующее значение. Также можно просто написать в качестве аргумента число. Затем скобка закрывается, и нажимается клавиша ввода. Затем среднее арифметическое значений автоматически покажется в нужной ячейке. Например, так выглядит формула, которая находит среднее арифметическое чисел в таблице, которая находится на втором и третьем ряду.

Видим, что среднее арифметическое всех этих значений составляет 37,5. Для многих людей этот метод не покажется удобным. Тем не менее, если овладеть слепым методом набора, то ввод любых данных с клавиатуры будет значительно быстрее, чем ручной выбор всех необходимых данных. Кроме этого, будет значительно легче пользоваться ручным способом поиска среднего арифметического тем людям, которые уже знают эту функцию наизусть. Если же пользователь только учится пользоваться Excel, то ему подойдут другие методы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вариант 1: Стандартным способом

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

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

Вариант 2: Использование Мастера функций

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

При этом будет использована та же формула, что и в предыдущем варианте:

Вариант 3: Ввод функции вручную

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

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

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

Вариант 4: Расчёт среднего значения с заданными условиями

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

Попытаться реализовать задуманное можно по следующей инструкции:

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

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

Как ведёт себя Excel

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

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

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

Функций очень много и они сгруппированы по областям применения:

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

Что такое среднее значение?

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

Статистические (или просто учётные) таблицы могут быть совершенно разных форм и размеров. Собственно, форма, прямоугольник, но они бывают широкие, узкие, повторяющиеся (скажем, данные за неделю по дням), разбросанные на разных листах Вашей workbook — рабочей книги.

А то и вообще в других workbook (то есть в книгах, по-английски), а то и на других компьютерах в локальной сети, или, страшно сказать, в других концах нашего белого света, теперь объединённого всесильной сетью Internet. Много информации можно получать из весьма солидных источников в Интернет уже в готовом виде. После чего обрабатывать, анализировать, делать выводы, писать статьи, диссертации…

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

Пример поиска среднего значения

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

Исходные данные располагаются в левой части таблицы (например, один столбец — количество деталей, изготовленных одним работником А, которому соответствует в таблице отдельная строка, и второй столбец- цена одной детали), в последнем столбце проставляется выработка работника А в деньгах.

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

Простая таблица дневных заработков

Вот на картинке сумма заработка и вычисляется она по каждому работнику в столбце Е по формуле умножения количества деталей (столбец С) на цену деталей (столбец D).

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

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

Суммарные величины

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

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

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

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

Вот она, средняя (mean — по-английски)

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

Как видим, можно использовать уже рассчитанные в предыдущей строке суммы, просто разделить их на количество работников — 6 в данном случае.

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

Округление до копеек

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

А Excel — программа математическая. Она не испытывает трепета перед долей копейки — куда её девать. Excel просто хранит числа, как есть, со всеми знаками после запятой. И снова и снова будет проводить с такими числами вычисления. А уж, конечный результат может округлить (если мы дадим команду).

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

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

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

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

Среднее арифметическое значение – это отношение суммы элементов к их количеству. Для расчета этого показателя, в программе есть функция СРЗНАЧ(значение_1 ; Значение_2 ; … Значение n) .

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

Важно! Функция СРЗНАЧ (на английском — AVERAGE) игнорирует пустые и текстовые ячейки, а нулевые – участвуют в расчёте.

Среднее значение последних элементов

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

=СРЗНАЧ(СМЕЩ(первая_ячейка ; 0 ; СЧЁТ(вся_строка)-n ; 1 ; n))

Как работает формула? Функция СМЕЩ получает координаты первого столбца. Смещается относительно него в пятую с конца ячейку и возвращает массив размером в 5 колонок и 1 строку. СРЗНАЧ рассчитывает среднее арифметическое всех значений в этом диапазоне.

Среднее арифметическое трех наибольших значений

Теперь расскажу, как рассчитать среднее значение, например, трёх наибольших цифр в диапазоне. Запишем такую формулу: =СРЗНАЧ(НАИБОЛЬШИЙ( массив ;)) . Поскольку это формула массива, после ввода нажмите Ctrl+Shift+Enter .

Здесь функция НАИБОЛЬШИЙ выберет последовательно первый, второй и третий по величине элементы, а СРЗНАЧ рассчитает их среднее.

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

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