Green-sell.info

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

Расчет среднего арифметического в 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 – все доступные способы: разбираем во всех подробностях

Табличный процессор Microsoft Excel лучше всего подходит в качестве программы для различных вычислений. Как правило, Excel поставляется вместе с «офисным» пакетом программ MS Office, который установлен почти на каждом компьютере. Но мало кто знает, насколько мощным функционалом обладает эта программа. Изучив основы Эксель, его можно применять практически в любой сфере деятельности. Эта программа очень пригодится школьникам для решения задач по математике, физике, химии, экономике и пр. Например, в 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). Результат:

Видео по расчету среднего значения

Как найти среднее значение в Excel?

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

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

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

Если теперь, например, изменить какое-то значение по одному из предметов (или вовсе его удалить и оставить поле пустым), то Эксель сразу же пересчитает формулу и выдаст новый результат.

Альтернативные способы расчета среднего значения

Еще один способ найти среднее значение в Excel — с помощью строки формул.

Пример использования функции СРЗНАЧ для расчета среднего значения диапазона ячеек

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вычисление среднего взвешенного значения

Для выполнения этой задачи используются функции СУММПРОИЗВ и сумм . Пример vThis вычисляет среднюю цену единицы измерения, оплаченная через три покупки, где находится каждый покупки для различное количество единиц измерения по различным ценам за единицу.

Скопируйте в приведенной ниже таблице на пустой лист.

Расчет среднего арифметического в excel


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Информация в строке состояния

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

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

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

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

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

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

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

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

    Инструменты на ленте

    Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:

    1. Выделяем диапазон ячеек с числовыми данными, для которых мы хотим определить среднее значение.
    2. Переходим во вкладку “Главная” (если находимся не в ней). В разделе инструментов “Редактирование” находим значок “Автосумма” и щелкаем по небольшой стрелке вниз рядом с ним. В раскрывшемся перечне кликаем по варианту “Среднее”.
    3. Сразу же под выделенным диапазоном отобразится результат, который и является средним значением по всем отмеченным ячейкам.
    4. Если мы перейдем в ячейку с результатом, то в строке формул увидим, какая функция была использована программой для расчетов – это оператор СРЗНАЧ, аргументами которого является выделенный нами диапазон ячеек.

    Примечание: Если вместо вертикального выделения (столбца целиком или его части) будет выполнено горизонтальное выделение, то результат отобразится не под областью выделения, а справа от нее.

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

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

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

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

    Альтернативный способ использования “Среднее” на ленте:

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

    Использование функции СРЗНАЧ

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

    1. Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
    2. В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
    3. На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
    4. Получаем результат в выбранной ячейке.
    5. Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
    6. Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
    7. Все готово. Теперь результат выглядит намного привлекательнее.

    Инструменты во вкладке “Формулы”

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

    1. Встаем в ячейку, в которой планируем выполнить расчеты. Переключаемся во вкладку “Формулы”. В разделе инструментов “Библиотека функций” щелкаем по значку “Другие функции”, в раскрывшемся перечне выбираем группу “Статистические”, затем – “СРЗНАЧ”.
    2. Откроется уже знакомое окно аргументов выбранной функци. Заполняем данные и жмем кнопку OK.

    Ввод функции в ячейку вручную

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

    В общем, синтаксис функции СРЗНАЧ выглядит так:

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

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

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

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

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

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

    Допустим, нам нужно посчитать среднее значение только по положительным числам, т.е. тем, которые больше нуля. В этом случае, нас выручит функция СРЗНАЧЕСЛИ.

    1. Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
    2. В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
    3. Откроются аргументы функции, после заполнения которых кликаем OK:
      • в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
      • в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
      • поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
    4. Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.

    Заключение

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

    Расчет средневзвешенного значения в 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.

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

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

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

    Читать еще:  Промежуточные итоги в excel для чайников
    Ссылка на основную публикацию
    Adblock
    detector