Semenalidery.com

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

Вычисление среднего в excel

Вычисление среднего в excel


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Основная идея

    Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:

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

    Первое, что обычно приходит в голову — это рассчитать классический средний балл с помощью стандартной функции Excel СРЗНАЧ (AVERAGE).

    На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется «Программирование», а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.

    Средневзвешенное — это среднее с учетом различной ценности (веса, важности) каждого из элементов.

    В бизнесе средневзвешенное часто используется в таких задачах, как:

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

    Расчет средневзвешенного формулами

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

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

    Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉

    Расчет средневзвешенного в сводной таблице

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

    Читать еще:  If excel как пользоваться

    Обратите внимание, что я преобразовал ее в «умную» таблицу с помощью команды Главная — Форматировать как таблицу (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.

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

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

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

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

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

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

    Соединить текст из разных ячеек

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

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

    Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

    Подобрать значения для нужного результата

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

    Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

    Как следить за бюджетом

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

    Читать еще:  Как в excel посчитать разность столбцов

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

    Обновить курс валют

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

    Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

    Планировать действия

    Теперь, когда курсы валют и стоимость акций подгружаются автоматически, эксель становится динамичным рабочим инструментом. Но можно пойти дальше и требовать от него реакций — и даже советов! — при определенных изменениях. Для этого понадобится функция «Если». Она заполняет ячейки заданными значениями в зависимости от того, что происходит в остальной таблице.

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

    Вот так: =ЕСЛИ (ячейка с ценой акции >= цена выгодной продажи; «продавать»; ЕСЛИ (ячейка с ценой акции Это самый простой пример. Формула «Если» может совмещать данные из разных ячеек и таблиц и быть частью сложных формул, когда при нужном условии происходит умножение или другое действие

    Выделить цветом нужные данные

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

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

    Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

    Суммировать только нужное

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

    Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

    В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

    Расставить по порядку

    В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

    Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

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

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

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

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

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

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

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

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

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

    Читать еще:  Графики ганта в excel

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

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

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

    Кроме всего вышеописанного в 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
    ×
    ×