Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.
Например, средние значения ряда чисел в Excel считают с помощью статистических функций. Можно также вручную ввести собственную формулу. Рассмотрим различные варианты.
Как найти среднее арифметическое чисел?
Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.
Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:
Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).
Теперь посмотрим, что еще умеет функция СРЗНАЧ.
Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:
Среднее значение по условию
Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().
Найти среднее арифметическое чисел, которые больше или равны 10.
Функция: =СРЗНАЧЕСЛИ(A1:A8;">=10")
Результат использования функции СРЗНАЧЕСЛИ по условию ">=10":
Третий аргумент – «Диапазон усреднения» - опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.
Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.
Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».
Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово "столы"). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.
В результате вычисления функции получаем следующее значение:
Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.
Как посчитать средневзвешенную цену в Excel?
Как мы узнали средневзвешенную цену?
Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).
С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ - сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.
Среднее квадратическое отклонение: формула в Excel
Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.
Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.
Среднеквадратическое отклонение имеет привязку к масштабу исходных данных. Для образного представления о вариации анализируемого диапазона этого недостаточно. Чтобы получить относительный уровень разброса данных, рассчитывается коэффициент вариации:
среднеквадратическое отклонение / среднее арифметическое значение
Формула в Excel выглядит следующим образом:
СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).
Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.
17.02.2017
Excel предсталяет собой табличный процессор. Его можно использовать для создания разнообразных отчетов. В данной программе очень удобно производить разные вычисления. Многие не используют и половину возможностей Excel.
Найти средние значение чисел может понадобиться в школе, а также во время работы. Классическим способ определения среднего арифметического без использования программ заключается в складывании всех чисел, а затем полученную сумму нужно разделить на количество слагаемых. Если числа достаточно крупные или для отчетности необходимо выполнить операцию много раз, вычисления могут занять много времени. Это нерациональная трата сил и времени, намного лучше воспользоваться возможностями Excel.
Поиск среднего арифметического
Многие данные уже изначально фиксируются в Excel, если же этого не происходит необходимо перенести данные в таблицу. Каждая цифра для расчета должна находится в отдельной ячейке.
Способ 1: Рассчитать среднее значение через «Мастер функций»
В этом способе необходимо прописать формулу для расчета среднего арифметического и применить ее для указанных ячеек.
Основное неудобство этого способа в том, что приходится вручную вводить ячейки для каждого слагаемого. При наличии большого количества чисел это не слишком удобно.
Способ 2: Автоматический подсчет результата в выделенных ячейках
В этом способе расчет среднего арифметического осуществляется буквально за пару кликов мышью. Очень удобно для любого количества чисел.
Недостатком этого способа является расчет среднего значения только лишь для чисел, расположенных рядом. Если необходимые слагаемые разрознены, то их не получится выделить для расчета. Невозможно даже выделить два столбца, в таком случае результаты будут представлены отдельно для каждого из них.
Способ 3: Использование панели формул
Еще один способ перейти в окно функции:
Самый быстрый способ, при котором не нужно долго искать в меню необходимы пункты.
Способ 4: Ручной ввод
Не обязательно для высчитывания среднего значения использовать инструменты в меню Excel, можно вручную прописать необходимую функцию.
Быстрый и удобный способ для тех, кто предпочитает создавать формулы своими руками, а не искать готовые в меню программы.
Благодаря этим возможностям очень легко рассчитать среднее значение любых чисел, вне зависимости от их количества, можно также составлять статистические данные без подсчетов вручную. С помощью инструментов программы Excel любые расчеты сделать намного легче, чем в уме или же с помощью калькулятора.
В данной статье будет рассмотрено несколько статистических функций приложения Excel:
Функция МАКС
Возвращает максимальное числовое значение из списка аргументов.
Синтаксис: =МАКС
Пример использования:
=МАКС
({1;2;3;4;0;-5;5;"50"}) – возвращает результат 5, при этом строка «50» игнорируется, т.к. задана в массиве.
=МАКС
(1;2;3;4;0;-5;5;"50") – результатом функции будет 50, т.к. строка явно задана в виде отдельного аргумента и может быть преобразована в число.
=МАКС
(-2; ИСТИНА) – возвращает 1, т.к. логическое значение задано явно, поэтому не игнорируется и преобразуется в единицу.
Функция МИН
Возвращает минимальное числовое значение из списка аргументов.
Синтаксис: =МИН (число1; [число2]; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические значения в диапазонах и массивах игнорируются.
Пример использования:
=МИН
({1;2;3;4;0;-5;5;"-50"}) – возвращает результат -5, текстовая строка игнорируется.
=МИН
(1;2;3;4;0;-5;5;"-50") – результатам функции будет -50, так как строка «-50» задана в виде отдельного аргумента и может быть преобразована в число.
=МИН
(5; ИСТИНА) – возвращает 1, так как логическое значение задано явно в виде аргумента, поэтому не игнорируется и преобразуется в единицу.
Функция НАИБОЛЬШИЙ
Возвращает значение элемента, являвшегося n-ым наибольшим, из указанного множества элементов. Например, второй наибольший, четвертый наибольший.
Синтаксис: =НАИБОЛЬШИЙ (массив; n), где
- n – натуральное число (кроме нуля), указывающее позицию элемента в порядке убывания. Если задать дробное число, то оно округляется до целого в большую сторону (дробные числа меньше единицы возвращают ошибку). Если аргумент превышает количество элементов множества, то функция возвращает ошибку.
Пример использования:
На изображении приведено 2 диапазона. Они полностью совпадают, кроме того, что в первом столбце диапазон отсортирован по убыванию, он представлен для наглядности. Функция ссылается на диапазон ячеек во втором столбце и возвращает элемент, являющийся 3 наибольшим значением.
В данном примере используется диапазон с повторяющимися значениями. Видно, что ячейкам не назначаются одинаковые ранги, в случае их равенства.
Функция НАИМЕНЬШИЙ
Возвращает значение элемента, являвшегося n-ым наименьшим, из указанного множества элементов. Например, третий наименьший, шестой наименьший.
Синтаксис: =НАИМЕНЬШИЙ (массив; n), где
- массив – диапазон ячеек либо массив элементов, содержащий числовые значения. Текстовые и логические значения игнорируются.
- n – натуральное число (кроме нуля), указывающее позицию элемента в порядке возрастания. Если задать дробное число, то оно округляется до целого в меньшую сторону (дробные числа меньше единицы возвращают ошибку). Если аргумент превышает количество элементов множества, то функция возвращает ошибку.
Массив или диапазон НЕ обязательно должен быть отсортирован.
Пример использования:
Функция РАНГ
Возвращает позицию элемента в списке по его значению, относительно значений других элементов. Результатом функции будет не индекс (фактическое расположение) элемента, а число, указывающее, какую позицию занимал бы элемент, если список был отсортирован либо по возрастанию либо по убыванию.
По сути, функция РАНГ выполняет обратное действие функциям НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, т.к. первая находит ранг по значению, а последние находят значение по рангу.
Текстовые и логические значения игнорируются.
- число – обязательный аргумент. Числовое значение элемента, позицию которого необходимо найти.
- ссылка – обязательный аргумент, являющийся ссылкой на диапазон со списком элементов, содержащих числовые значения.
- порядок – необязательный аргумент. Логическое значение, отвечающее за тип сортировки:
- ЛОЖЬ – значение по умолчанию. Функция проверяет значения по убыванию.
- ИСТИНА – функция проверяет значения по возрастанию.
Если в списке отсутствует элемент с указанным значением, то функцией возвращается ошибка #Н/Д.
Если два элемента имеют одинаковое значение, то возвращается ранг первого обнаруженного.
Функция РАНГ присутствует в версиях Excel, начиная с 2010, только для совместимости с более ранними версиями. Вместо нее внедрены новые функции, обладающие тем же синтаксисом:
- РАНГ.РВ – полная идентичность функции РАНГ. Добавленное окончание «.РВ», сообщает о том, что, в случае обнаружения элементов с равными значениями, возвращается высший ранг, т.е. самого первого обнаруженного;
- РАНГ.СР – окончание «.СР», сообщает о том, что, в случае обнаружения элементов с равными значениями, возвращается их средний ранг.
Пример использования:
В данном случае используется возврат ранга при проверке диапазона значений по возрастанию.
На следующем изображении отображено использование функции с проверкой значений по убыванию. Так как в диапазоне имеется 2 ячейки со значением 2, то возвращается ранг первой обнаруженной в указанном порядке.
Функция СРЗНАЧ
Возвращает среднее арифметическое значение заданных аргументов.
Синтаксис: =СРЗНАЧ (число1; [число2]; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические значения в диапазонах и массивах игнорируются.
Пример использования:
Результатом выполнения функции из примера будет значение 4, т.к. логические и текстовые значения будут проигнорированы, а (5 + 7 + 0 + 4)/4 = 4.
Функция СРЗНАЧА
Аналогична функции СРЗНАЧ за исключением того, что истинные логические значения в диапазонах приравниваются к 1, а ложные значения и текст приравнивается к нулю.
Пример использования:
Возвращаемое значение в следующем примере 2,833333, так как текстовые и логические значения принимаются за ноль, а логическое ИСТИНА приравнивается к единице. Следовательно, (5 + 7 + 0 + 0 + 4 + 1)/6 = 2,833333.
Функция СРЗНАЧЕСЛИ
Вычисляет среднее арифметическое значение для ячеек, отвечающих заданному условию.
Синтаксис: =СРЗНАЧЕСЛИ (диапазон; условие; [диапазон_усреднения]), где
- диапазон – обязательный аргумент. Диапазон ячеек для проверки.
- условие – обязательный аргумент. Значение либо условие проверки. Для текстовых значений могут быть использованы подстановочные символы (* и?). Условия типа больше, меньше записываются в кавычках.
- диапазон_усреднения – необязательный аргумент. Ссылка на ячейки с числовыми значениями для определения среднего арифметического. Если данный аргумент опущен, то используется аргумент «диапазон».
Пример использования:
Необходимо узнать среднее арифметическое для чисел, которые больше 0. Так как для расчета представлено всего 3 числа, из которых 2 являются нулем, то остается только одно значение, которое и является результатом выполнения функция.
Также в функции не используется последний аргумент, поэтому вместо него принимается диапазон из первого.
В следующем примере рассматривается таблица с приведением заработной платы работников. Необходимо узнать среднюю заработную плату для каждой должности.
Функция СРЗНАЧЕСЛИМН
Возвращает среднее арифметическое для ячеек, отвечающих одному либо множеству условий.
Синтаксис: =СРЗНАЧЕСЛИМН (диапазон_усреднения; диапазон_условия1; условие1; [диапазон_условия2]; [условие2]; …), где
- диапазон_усреднения – обязательный аргумент. Ссылка на ячейки с числовыми значениями для определения среднего арифметического.
- диапазон_условия1 – обязательный аргумент. Диапазон ячеек для проверки.
- условие1 – обязательный аргумент. Значение либо условие проверки. Для текстовых значений могут быть использованы подстановочные символы (* и?). Условия типа больше, меньше заключаются в кавычки.
Все последующие аргументы от диапазон_условия2 и условие2 до диапазон_условия127 и условие127 являются необязательными.
Пример использования:
Используем таблицу из примера предыдущей функции с добавлением городов для сотрудников. Выведем среднюю заработную плату для электриков в городе Москва.
Результат выполнения функции 25 000.
Функция принимает в расчет только те значения, которые подходят под все условия.
Функция СЧЁТ
Подсчитывает количество числовых значений в диапазоне.
Синтаксис: =СЧЁТ (значение1; [значение2]; …), где значение1 – обязательный аргумент, принимающий значение, ссылку на ячейку, диапазон ячеек или массив. Аргументы от значение2 до значение255 являются необязательными и аналогичными значение1.
Логические значения в диапазонах и массивах игнорируются. Если такое значение задано явно в аргументе, то оно учитывается как число.
Пример использования:
=СЧЁТ
(1; 2; "5") – результат функции 3, т.к. строка «5» конвертируется в число.
=СЧЁТ
({1; 2; "5"}) – результатом выполнения функции будет значение 2, так как, в отличие от первого примера, число в виде строки записано в массиве, поэтому не будет преобразовано.
=СЧЁТ
(1; 2; ИСТИНА) – результат функции 3. Если бы логическое значение находилось бы в массиве, то оно не засчиталось как число.
Функция СЧЁТЕСЛИ
Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.
Синтаксис: =СЧЁТЕСЛИ (диапазон; критерий), где
- диапазон – обязательный аргумент. Принимает ссылку на диапазон ячеек для проверки на условие.
- критерий – обязательный аргумент. Критерий проверки, содержащий значение либо условия типа больше, меньше, которые необходимо заключать в кавычки. Для текстовых значений можно использовать подстановочные символы (* и?).
Пример использования:
Функция СЧЁТЕСЛИМН
Возвращает количество ячеек в диапазоне, удовлетворяющих условию либо множеству условий.
Функция аналогична функции СЧЁТЕСЛИ, за исключением того, что может содержать до 127 диапазонов и критериев, где первый является обязательным, а последующие – нет.
Синтаксис: =СЧЁТЕСЛИМН (диапазон1; критерий1; [диапазон2]; [критерий2]; …).
Пример использования:
На рисунке изображено использование функции СЧЁТЕСЛИМН, где подсчитывается количество человек, имеющих оклад свыше 4000 рублей и проживающих в Москве и Московской области. При этом для последнего условия используется подстановочный символ *.
Функция СЧЁТЗ
Подсчитывает непустые ячейки в указанном диапазоне.
Синтаксис: =СЧЁТЗ (значение1; [значение2]; …), где значение1 является обязательным аргумент, все последующие аргументы до значение255 необязательны. В качестве значения может содержаться ссылка на ячейку или диапазон ячеек.
Пример использования:
Функция возвращает значение 4, т.к. ячейка A3 содержит текстовую функцию, возвращающую пустую строку.
Функция СЧИТАТЬПУСТОТЫ
Подсчитывает пустые ячейки в указанном диапазоне.
Пустые строки (="") засчитываются как пустые.
У Вас недостаточно прав для комментирования.
Функции категории Статистические предназначены в первую очередь для анализа диапазонов ячеек в Excel. С помощью данных функций Вы можете вычислить наибольшее, наименьшее или среднее значение, подсчитать количество ячеек, содержащих заданную информацию, и т.д.
Данная категория содержит более 100 самых различных функций Excel, большая часть из которых предназначена исключительно для статистических расчетов и обычному рядовому пользователю покажется темным лесом. Мы же в рамках этого урока рассмотрим самые полезные и распространенные функции данной категории.
В рамках данной статьи мы не будем затрагивать такие популярные статистические функции Excel, как СЧЕТ и СЧЕТЕСЛИ , для них подготовлен отдельный урок .
СРЗНАЧ()
Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.
Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:
Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13
Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА . В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6) .
Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:
СРЗНАЧЕСЛИ()
Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ . Следующая формула вычисляет среднее чисел, которые больше нуля:
В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.
Допустим, в таблице ниже собрана статистика по стоимости лекарств в городе. В одной аптеке лекарство стоит дороже, в другой дешевле. Чтобы посчитать стоимость анальгина в среднем по городу, воспользуемся следующей формулой:
Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН , которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.
МАКС()
Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:
МИН()
Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:
НАИБОЛЬШИЙ()
Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.
Чтобы убедиться в этом, можно отсортировать числа в порядке возрастания:
НАИМЕНЬШИЙ()
Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.
Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:
МЕДИАНА()
Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.
Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.
Если отсортировать значения в порядке возрастания, то все становится на много понятней:
МОДА()
Возвращает наиболее часто встречающееся значение в массиве числовых данных.
Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:
Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН . Форма записи МОДА также поддерживается в Excel для совместимости.
Как известно, категория Статистические в Excel содержит более 100 самых разноплановых функций. Но, как показывает практика, львиная доля этих функций практически не применяется, а особенно начинающими пользователями. В этом уроке мы постарались познакомить Вас только с самыми популярными статистическими функциями Excel, которые Вы рано или поздно сможете применить на практике. Надеюсь, что данный урок был для Вас полезен. Удачи Вам и успехов в изучении Excel.
При подготовке к для успешного решения задачи 19 из части 3 необходимо знать некоторые функции Excel. Одна из таких функций — СРЗНАЧ . Рассмотрим ее подробнее.
Excel позволяет найти среднее арифметическое аргументов. Синтаксис этой функции такой:
СРЗНАЧ(число1, [число2],…)
Не забываем, что ввод формулы в ячейку начинается со знака «=».
В скобках мы можем перечислить числа, среднее значение которых хотим найти. К примеру, если мы напишем в ячейке =СРЗНАЧ(1; 2; -7; 10; 7; 5; 9) , то получим 3,857142857. Это легко проверить — если мы сложим все числа в скобках (1 + 2 + (-7) + 10 + 7 + 5 + 9 = 27) и разделим на их количество (7), то получим 3,857142857142857.
Обратите внимание — числа в скобках разделяются точкой с запятой (; ). Таким образом мы можем указать до 255 чисел.
Для примеров я использую Microsort Excel 2010.
Кроме того, с помощью функции СРЗНАЧ мы можем найти среднее значение диапазона ячеек . Предположим, что у нас в диапазоне A1:A7 хранятся некоторые числа, и мы хотим найти их среднее арифметическое.
Давайте поместим в ячейку B1 среднее арифметическое диапазона A1:A7. Для этого устанавливаем курсор в ячейку B1 и пишем =СРЗНАЧ(A1:A7) . В скобках я указал диапазон ячеек. Обратите внимание, что разделителем является символ двоеточие (: ). Можно было бы поступить еще проще — написать в ячейке B1 =СРЗНАЧ( , а далее мышкой выделить нужный диапазон.
В итоге в ячейке B1 мы получим число 15,85714286 — это и есть среднее арифметическое диапазона A1:A7.
В качестве разминки предлагаю найти среднее значение чисел от 1 до 100 (1, 2, 3 и т.д. до 100). Первый ответивший в комментариях правильно получит 50р на телефон Работаем.
