Green-sell.info

Новые технологии
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Подсчет среднего значения в excel

Подсчет среднего значения в excel


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

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

Поиск среднего арифметического

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

Способ 1: Рассчитать среднее значение через «Мастер функций»

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

    Выделить любую ячейку в таблице нажать кнопку «Вставить функция».

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

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

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

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

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

Способ 2: Автоматический подсчет результата в выделенных ячейках

В этом способе расчет среднего арифметического осуществляется буквально за пару кликов мышью. Очень удобно для любого количества чисел.

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

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

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

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

Еще один способ перейти в окно функции:

    Выбрать вкладку «Формулы», навести курсор мыши на «Другие функции», выбрать «Статические» и «СРЗНАЧ».

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

  • Нажать «ОК» и увидеть результат в свободной ячейке.
  • Самый быстрый способ, при котором не нужно долго искать в меню необходимы пункты.

    Способ 4: Ручной ввод

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

      Найти строку для ввода разнообразных формул.

    Ввести необходимые числа согласно шаблону:
    =СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число))

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

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

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

    Средняя зарплата… Средняя продолжительность жизни… Практически каждый день мы с вами слышим эти словосочетания, используемые для описания множества одним единственным числом. Но как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека.

    В чем проблема?

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

    Давайте обратимся к классическому примеру со средней зарплатой.

    В какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).

    Средним значением в данном случае будет 195 150 рублей, что согласитесь, неправильно.

    Какие способы вычисления среднего бывают?

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

    • x – среднее арифметическое;
    • xn – конкретное значение;
    • n – количество значений .
    • Хорошо работает при нормальном распределении значений в выборке;
    • Легко вычислить;
    • Интуитивно понятно.
    • Не дает реального представления о распределении значений;
    • Неустойчивая величина легко поддающаяся выбросам (как в случае с генеральным директором).

    Вторым способом является вычисление моды, то есть наиболее часто встречающегося значения.

    • M – мода;
    • x – нижняя граница интервала, который содержит моду;
    • n – величина интервала;
    • fm– частота (сколько раз в ряду встречается то или иное значение);
    • fm-1 – частота интервала предшествующего модальному;
    • fm+1 – частота интервала следующего за модальным.
    • Прекрасно подходит для получения представления об общественном мнении;
    • Хорошо подходит для нечисловых данных (цвета сезона, хиты продаж, рейтинги);
    • Проста для понимания.
    • Моды может просто не быть (нет повторов);
    • Мод может быть несколько (многомодальное распределение).
    Читать еще:  Как поставить округление в excel

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

    • Me – медиана;
    • x – нижняя граница интервала, который содержит медиану;
    • h – величина интервала;
    • f i – частота (сколько раз в ряду встречается то или иное значение);
    • Sm-1 – сумма частот интервалов предшествующих медианному;
    • fm – число значений в медианном интервале (его частота).
    • Дает самую реалистичную и репрезентативную оценку;
    • Устойчива к выбросам.
    • Сложнее вычислить, так как перед вычислением выборку нужно упорядочить.

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

    А теперь давайте вернемся к нашему примеру и посчитаем все три варианта среднего при помощи специальных функций Excel:

    • СРЗНАЧ(число1;[число2];…) — функция для определения среднего арифметического;
    • МОДА.ОДН(число1;[число2];. ) — функция моды (в более старых версиях Excel использовалась МОДА(число1;[число2];. ) );
    • МЕДИАНА(число1;[число2];. ) — функция для поиска медианы.

    И вот какие значения у нас получились:

    В данном случае мода и медиана гораздо лучше характеризуют среднюю зарплату в компании.

    Но что делать, когда в выборке не 10 значений, как в примере, а миллионы? В Excel это не посчитать, а вот в базе данных где хранятся ваши данные, без проблем.

    Вычисляем среднее арифметическое на SQL

    Тут все достаточно просто, так как в SQL предусмотрена специальная агрегатная функция AVG .

    И чтобы ее использовать достаточно написать вот такой запрос:

    Вычисляем моду на SQL

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

    Вычисляем медиану на SQL

    Как и в случае с модой, в SQL нет встроенной функции для вычисления медианы, зато есть универсальная функция для вычисления процентилей PERCENTILE_CONT .

    Выглядит все это так:

    Подробнее о работе функции PERCENTILE_CONT лучше почитать в справке Microsoft и Google BigQuery.

    Какой способ все-таки использовать?

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

    Но это не всегда так. Если вы работаете со средним, то остерегайтесь многомодального распределения:

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

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

    А еще лучше разделить выборку на две группы и собрать статистические данные для каждой.

    Вывод:

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

    Окончательный выбор меры центральной тенденции всегда лежит на аналитике.

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

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

    Сейчас же речь пойдет о том, как посчитать среднее значение. Статья будет актуальна для всех современных версий Excel, включая Excel 2007, 2010, 2013 и 2016.

    Как посчитать среднее значение столбца, строки или отдельных ячеек

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

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

    Если вам нужно, чтобы среднее значение появилось в какой-то другой ячейке, то вы можете перенести результат просто вырезав его (CTRL-X), а потом вставив (CTRL-V). Либо вы можете сначала выбрать ячейку, где должен находиться результат, а потом нажать на кнопку «Авто сумма – Среднее» и выбрать ряд числел.

    Если вам нужно посчитать среднее значение каких-то отдельных или конкретных ячеек, то это также можно сделать с помощью кнопки «Авто сумма – Среднее». В этом случае нужно сначала выбрать ячейку, в которой будет находится результат, после чего нажать «Авто сумма – Среднее» и выбрать ячейки для которых нужно рассчитать среднее значение. Для выбора отдельных ячеек нужно удерживать клавишу CTRL на клавиатуре.

    Кроме этого, вы можете вписать формулу для расчета среднего значения определенных ячеек вручную. Для этого нужно поставить курсор там, где должен находиться результат, а потом ввести формулу в формате: = СРЗНАЧ (D3; D5; D7). Где вместо D3, D5 и D7 нужно указать адреса нужных вам ячеек с данными.

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

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

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

    Читать еще:  Создание и форматирование таблиц в excel

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

    Расчет средневзвешенного значения в 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.
    Читать еще:  Как сделать нумерацию ячеек в excel

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

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

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

    Вычисление минимального, максимального и среднего значения в Microsoft Excel

    В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

    Это особенно актуально в таблицах с большим объёмом данных. Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

    В этой статье мы расскажем, как вычислить различные значения в Эксель

    Максимальное и минимальное

    Вот как найти максимальное значение в Excel:

    1. Поставьте курсор-ячейку в любое место.
    2. Перейдите в меню «Формулы».
    3. Нажмите «Вставить функцию».
    4. В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
    5. В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона — это первая и последняя ячейки, которые в него входят.
    6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
    7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

    Следующий шаг – укажите диапазон значений

    Теперь будет легче разобраться, как найти минимальное значение в Excel. Алгоритм действий полностью идентичен. Просто вместо «МАКС» выберите «МИН».

    Среднее

    Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

    Вот как найти среднее значение в Excel:

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

    Нажмите ОК для подсчета

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

    Другие способы

    Максимальное, минимальное и среднее можно узнать и другими способами.

    1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
    2. Поставьте курсор в любую ячейку.
    3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
    4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
    5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
    6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

    Впишите диапазон и нажмите Enter

    Найти и вычислить поможет Excel-инструмент «Автофункции».

    1. Поставьте курсор в ячейку.
    2. Перейдите в раздел «Формулы».
    3. Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
    4. Нажмите на чёрную стрелочку под ней.
    5. Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
    6. В отмеченной клетке появится формула. Кликните на любую другую ячейку — она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
    7. Когда закончите, нажмите Enter. Результат отобразится в клетке.

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

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×