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

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

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

Можно один раз создать такой документ — собственно, сразу целое семейство документов, которое по терминологии Excel называется «рабочая книга» (английский вариант workbook).

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

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

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

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

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

  1. математические (арифметические),
  2. тригонометрические,
  3. логические,
  4. текстовые
  5. финансовые
  6. инженерные и так далее.

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

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

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

Ну что ж, будем считать, что тому числу страшно повезло в этом случае, но обычно арифметическое среднее бывает не только не совпадающим ни с одним из чисел своего ряда, но даже, как говорится, «не лезущим ни в какие ворота» в этом ряду. Например, среднее количество человек, живущих в квартирах какого-то города N-ска, может оказаться 5,216 человек. Это как? Живут 5 человек и ещё довесок в 216 тысячных долей одного из них? Знающий только ухмыльнётся: да Вы что! Это же статистика!

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать выпадающий календарь в ячейке excel?

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

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

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

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

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

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

В формулах делить на константы, постоянные числа, это дурной тон. А вдруг у нас произойдёт нечто из ряда вон, и количество работников станет меньше? Тогда нужно будет лазать по всем формулам и везде менять цифру семь на какую-то другую. Можно, например, «обмануть» табличку так:

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

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

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

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

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

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

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

  1. Откроется панель, которая называется «Мастер функций». Это такой многошаговый диалог (Wizard, по-английски), с помощью которого программа помогает при конструировании сложных формул. И, заметим, что помощь уже начата: в строке формул программа вбила за нас знак =.
  2. Теперь можно быть спокойным, программа проведёт нас через все сложности (хоть по-русски, хоть по-английски) и в результате будет построена правильная формула для вычисления.

В верхнем окошке («Поиск функции:») написано, что мы можем тут искать и находить. То есть здесь можно написать «среднее» и нажать кнопку «Найти» (Find, по-английски). Но можно поступить иначе. Мы знаем, что это функция — из категории статистических. Вот и найдём эту категорию во втором окошке. А в открывшемся ниже списке, найдём функцию «СРЗНАЧ».

Заодно и увидим, какое там великое множество функций в категории статистических, одних только средних там 7 штук. И по каждой из функций, если перемещать по ним указатель, ниже можно увидеть краткую аннотацию по этой функции. А если кликнуть ещё ниже, по надписи «Справка по этой функции», то можно получить и совсем подробное её описание.

А сейчас мы просто просчитаем среднее значение. Кликаем «ОК» (это так по-английски выражается согласие, хотя, скорее, это по-американски) на кнопочке внизу.

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

Читать еще:  Рейтинг в excel как сделать

Как высчитать среднее арифметическое в Excel

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

Способ 1: стандартный

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

  1. Выделите ячейки столбца или строки, в которых находятся числовые значения для расчета.
  2. Перейдите во вкладку «Главная».
  3. На панели инструментов в категории «Редактирование» нажмите по кнопке «Автосумма», однако жать необходимо на стрелочку рядом с ней, чтобы появился выпадающий список.
  4. В нем вам необходимо кликнуть по пункту «Среднее».

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

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

Способ 2: с помощью Мастера функций

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

  1. Нажав левую кнопку мыши, выделите ячейку, в которой хотите видеть результат вычислений.
  2. Откройте окно Мастера функций, нажав по кнопке «Вставить функцию», расположенной слева от строки формул либо использовав горячие клавиши Shift+F3.
  3. В появившемся окне отыщите в списке строку «СРЗНАЧ», выделите ее и нажмите кнопку «ОК».
  4. Появится новое окно для ввода аргументов функции. В нем вы увидите два поля: «Число1» и «Число2».
  5. В первое поле введите адреса ячеек, в которых расположены числовые значения для расчета. Сделать это можно как вручную, так и с помощью специального инструмента. Во втором случае нажмите по кнопке, расположенной в правой части поля для ввода. Окно Мастера свернется и вам необходимо будет выделить мышкой ячейки для расчета.
  6. Если другой диапазон ячеек с данными находится в другом месте листа, тогда укажите его в поле «Число2».
  7. Проделайте ввод данных, пока не укажете все необходимые.
  8. Нажмите кнопку «ОК».

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

Способ 3: через панель формул

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

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

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

Способ 4: ручной ввод функции

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

Чтобы разобраться во всех нюансах, необходимо посмотреть на синтаксис формулы, выглядит он следующим образом:

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

Способ 5: расчет по условию

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

  • выделите ячейку, в которой будет совершаться расчет;
  • нажмите кнопку «вставить функцию»;
  • в появившемся окне мастера в списке выделите строку «срзначесли»;
  • нажмите «ОК».

После этого появится окно для ввода аргументов функции. Оно очень похоже на то, что было продемонстрировано ранее, только теперь появилось дополнительное поле – «Условие». Именно в него необходимо вписывать условие. Так, введя «>1500», в расчет будут браться только те значения, которые больше указанного.

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

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

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

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

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

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

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

Вводим формулу

Теперь, когда наша таблица готова, мы добавляем формулу в ячейку B10 (подойдёт любая пустая ячейка). Как и с любой другой формулой в Excel, начинаем со знака равенства (=).

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

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

Вторым аргументом будет диапазон ячеек C2:C9, в котором содержатся веса. Между этими аргументами должен стоять разделитель точка с запятой (запятая). Когда все будет готово, закрываем скобки:

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

Чтобы выполнить операцию деления, продолжаем уже введённую формулу символом / (прямой слеш), а далее записываем функцию СУММ (SUM):

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

Для функции SUM (СУММ) мы укажем только один аргумент – диапазон ячеек C2:C9. Не забудьте после ввода аргумента закрыть скобки:

=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Готово! После нажатия клавиши Enter, Excel рассчитает среднее взвешенное значение. В нашем примере итоговый результат будет равен 83,6.

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

Давайте разберем каждую часть формулы, начиная с функции СУММПРОИЗВ (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, и формула по-прежнему будет работать правильно. Здорово, правда?

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