В статье показано, как определить среднее значение в Excel для разных типов данных с помощью формул СРЗНАЧ или СРЗНАЧА. Вы также узнаете, как использовать функции СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН для усреднения ячеек, соответствующих определенным критериям.
Средняя зарплата… Средняя продолжительность жизни… Ежедневно мы с вами слышим эти словосочетания, служащие для описания множества одним единственным числом. Но, как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека. Например, в какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).
В математике среднее называется средним арифметическим, и оно рассчитывается путем сложения группы чисел, а затем деления на количество этих чисел.
В приведенном выше примере средняя зарплата составляет: 195 000 рублей =(50000*9+1500000)/10. Вряд ли эта цифра соответствует реальной ситуации, но таковы расчёты среднего арифметического значения.
И всё же в большинстве случаев знать среднее весьма полезно.
Table Of Contents
- Функция СРЗНАЧ.
- Пример 1. Расчет среднего из нескольких чисел.
- Пример 2. Расчет среднего процента.
- Пример 3. Расчет среднего времени.
- Чем отличается функция СРЗНАЧА?
- Среднее по условию — функция СРЗНАЧЕСЛИ
- Среднее, если пусто.
- Среднее, если не пустое.
- Среднее по нескольким условиям — функция СРЗНАЧЕСЛИМН
- Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)
- Пример 2. Среднеарифметическое на основе критерия даты
- Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев
- Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения
- Пример 3. Среднее с логикой ИЛИ на основе пустых/непустых ячеек
Как посчитать среднее арифметическое в Экселе? Вам не нужно писать сложные математические выражения. Есть несколько функций «на все случаи жизни».
- СРЗНАЧ (AVERAGE- а английском варианте) — рассчитать среднее арифметическое ячеек с числами.
- СРЗНАЧА (или AVERAGEA) — найти среднее арифметическое ячеек с любыми данными (числа, логические и текстовые значения).
- СРЗНАЧЕСЛИ (или AVERAGEIF) – среднее по заданному критерию.
- СРЗНАЧЕСЛИМН (или AVERAGEIFS) – среднее ячеек, удовлетворяющих нескольким условиям.
Средняя арифметическая как оценка математического ожидания
Теория вероятностей занимается изучением случайных величин. Для этого строятся различные характеристики, описывающие их поведение. Одной из основных характеристик случайной величины является математическое ожидание, являющееся своего рода центром, вокруг которого группируются остальные значения.
Формула матожидания имеет следующий вид:
где M(X) – математическое ожидание
xi – это случайные величины
pi – их вероятности.
То есть, математическое ожидание случайной величины — это взвешенная сумма значений случайной величины, где веса равны соответствующим вероятностям.
Математическое ожидание суммы выпавших очков при бросании двух игральных костей равно 7. Это легко подсчитать, зная вероятности. А как рассчитать матожидание, если вероятности не известны? Есть только результат наблюдений. В дело вступает статистика, которая позволяет получить приблизительное значение матожидания по фактическим данным наблюдений.
Математическая статистика предоставляет несколько вариантов оценки математического ожидания. Основное среди них – среднее арифметическое.
Среднее арифметическое значение рассчитывается по формуле, которая известна любому школьнику.
где xi – значения переменной, n – количество значений.
Среднее арифметическое – это соотношение суммы значений некоторого показателя с количеством таких значений (наблюдений).
Другие способы
Максимальное, минимальное и среднее можно узнать и другими способами.
- Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
- Поставьте курсор в любую ячейку.
- Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
- Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
- Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
- Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.
Свойства средней арифметической (математического ожидания)
Теперь рассмотрим свойства средней арифметической, которые часто используются при алгебраических манипуляциях. Правильней будет вновь вернутся к термину математического ожидания, т.к. именно его свойства приводят в учебниках.
Матожидание в русскоязычной литературе обычно обозначают как M(X), в иностранных учебниках можно увидеть E(X). Встречается обозначение греческой буквой μ (читается «мю»). Для удобства предлагаю вариант M(X).
Итак, свойство 1. Если имеются переменные X, Y, Z, то математическое ожидание их суммы равно сумме их математических ожиданий.
M(X+Y+Z) = M(X) + M(Y) + M(Z)
Допустим, среднее время, затрачиваемое на мойку автомобиля M(X) равно 20 минут, а на подкачку колес M(Y) – 5 минут. Тогда общее среднее арифметическое время на мойку и подкачку составит M(X+Y) = M(X) + M(Y) = 20 + 5 = 25 минут.
Свойство 2. Если переменную (т.е. каждое значение переменной) умножить на постоянную величину (a), то математическое ожидание такой величины равно произведению матожидания переменной и этой константы.
M(aX) = aM(X)
К примеру, среднее время мойки одной машины M(X) 20 минут. Тогда среднее время мойки двух машин составит M(aX) = aM(X) = 2*20 = 40 минут.
Свойство 3. Математическое ожидание постоянной величины (а) есть сама эта величина (а).
M(a) = a
Если установленная стоимость мойки легкового автомобиля равна 100 рублей, то средняя стоимость мойки нескольких автомобилей также равна 100 рублей.
Свойство 4. Математическое ожидание произведения независимых случайных величин равно произведению их математических ожиданий.
M(XY) = M(X)M(Y)
Автомойка за день в среднем обслуживает 50 автомобилей (X). Средний чек – 100 рублей (Y). Тогда средняя выручка автомойки в день M(XY) равна произведению среднего количества M(X) на средний тариф M(Y), т.е. 50*100 = 500 рублей.
Среднее по нескольким условиям — функция СРЗНАЧЕСЛИМН
СРЗНАЧЕСЛИМН учитывает сразу несколько условий и возвращает среднеарифметическое ячеек, которые соответствуют всем указанным критериям.
СРЗНАЧЕСЛИМН(диапазон подсчёта, диапазон_критерия_1, критерий_1, [диапазон_критерия_2, критерий_2],…)
Она имеет следующие аргументы:
- Диапазон_подсчёта — область ячеек, которые вы хотите усреднить.
- диапазон_критерия_1, 2 ,… — от 1 до 127 диапазонов для проверки на соответствие указанным параметрам. Первый из них обязателен, последующие – по желанию.
- Критерий_1, 2,… — от 1 до 127 критериев, определяющих, какие именно клетки следует усреднить. Они могут быть в виде числа, логического выражения, текста или ссылки. Первый обязателен, дополнительные – по необходимости.
Функция СРЗНАЧЕСЛИМН доступна в Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010 и 2007.
Как уже упоминалось, СРЗНАЧЕСЛИМН находит среднее ячеек, которые соответствуют всем указанным вами критериям (логика «И»). По сути, вы применяете его совершенно аналогично СРЗНАЧЕСЛИ, за исключением того, что вы можете указать в ней несколько областей проверки и соответствующих условий.
Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)
Предположим, что у вас есть список товаров в столбце A и суммы продаж в столбце B. Давайте выясним среднее продаж бананов, превышающее 100 единиц.
Здесь:
- С2:С8 — ячейки, которые вы хотите усреднить, если выполняются оба условия;
- Первое условие: A2:A8 (столбец «Товар»), а критерий_1 — «бананы»;
- Второе условие — это С2:С8 (столбец «Продажи»), а критерий_2 — «> 100».
Собираем вышеперечисленные компоненты вместе. И если вы замените условия ссылками, вы получите нечто похожее на это:
=СРЗНАЧЕСЛИМН($C$2:$C$8; $A$2:$A$8;$C$10; $C$2:$C$8; «>»&$C$11)
Как видите, только две ячейки (С3 и С5) удовлетворяют обоим условиям, и поэтому только они и берутся для расчета.
Пример 2. Среднеарифметическое на основе критерия даты
В этом примере давайте посчитаем среднее количество товаров, доставленных до 22 июля 2022 года, статус которых определен (позиция в соответствующем столбце не пустая). С количеством, указанным в столбце D, датами в колонке В (критерий_1) и статусом в С (критерий_2), формула выглядит следующим образом:
=СРЗНАЧЕСЛИМН($D$2:$D$8; $B$2:$B$8; «<=22-07-2020»; $C$2:$C$8; «<>»)
В критерии1 вы вводите дату, которой предшествует оператор сравнения. В критерии2 вы вводите «<>», который указывает включать только непустые ячейки в пределах столбца «Статус».
Примечание. Когда вы используете число или дату в сочетании с логическим оператором в указании условия, то заключайте эту комбинацию в двойные кавычки — “<=22.07.2020”.
Что нужно запомнить!
СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН имеют много общего, в частности:
- При подсчёте пустые области, логические ИСТИНА/ЛОЖЬ и текст — игнорируются.
- При оценке заданных условий пустые ячейки рассматриваются как нулевые (0).
- Если область, по которой считаем среднее, содержит только пустые клетки или текст, то обе функции возвращают ошибку #ДЕЛ/0!.
- Если ничего не соответствует требованиям, то также получаем ошибку #ДЕЛ/0!.
ОСОБЕННОСТИ
- Диапазон для подсчёта СРЗНАЧЕСЛИ не обязательно должен быть того же размера, что и область условий. Однако фактические данные, с которыми работает функция, определяются размером аргумента усреднения. Другими словами, если записать выражение
=СРЗНАЧЕСЛИ($B$2:$B$10; «<>»; $C$2:$C$8)
то в любом случае находить среднее мы будем по $C$2:$C$8.
- В отличие от СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН требует, чтобы каждая область критериев была того же размера, что и область для подсчета. Иначе получаем ошибку #ЗНАЧ!.
Как усреднить числа по нескольким параметрам с помощью логики ИЛИ?
Поскольку СРЗНАЧЕСЛИМН работает с логикой «И», а СРЗНАЧЕСЛИ допускает только 1 критерий, нам придется составить нашу собственную формулу для усреднения с помощью логики «ИЛИ».
Другими словами, мы составим выражение для вычисления среднего арифметического в Excel, если выполняется любое из указанных требований.
Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев
Предположим, вы хотите получить средние продажи (C2: C8) как для бананов, так и для яблок (A2: A8). Чтобы рассчитать это, вам понадобится формула массива, включающая несколько функций Excel:
{=СРЗНАЧ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2:A8;{«бананы»;»яблоки»};0));B2:B8))}
Пожалуйста, помните, что формулы массива должны быть введены через Ctrl + Shift + Enter, а не просто ENTER.
Если товары, которые мы ищем, записать отдельно (скажем, в E1 и E2), то наш расчет будет выглядеть так:
{=СРЗНАЧ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2:A8;E1:E2;0));B2:B8))}
Из всех формул усреднения Excel, которые обсуждались до сих пор, это самая хитрая (хотя осталось еще два примера
Как это работает?
Для наших любопытных и вдумчивых читателей, которые хотят не только механически применять рекомендации, но и понимать, что же они делают, даем подробное объяснение логики расчетов.
В начале функция ЕСЛИ определяет, какие значения в исходном диапазоне соответствуют любому из указанных параметров, и передает их в СРЗНАЧ. Вот как:
ПОИСКПОЗ берёт исходные данные в A2:A8 в качестве условий поиска и сравнивает каждое из них с массивом поиска, представленным критериями в E1: E2.
3- й аргумент, установленный в 0, указывает искать точные совпадения:
ПОИСКПОЗ(A2:A8,E1:E2,0)
Когда совпадение найдено, программа возвращает относительную позицию в массиве поиска, в противном случае ошибку #Н/Д!.
{#Н/Д!; 1; 2; 1; 2; #Н/Д!; 1}
ЕЧИСЛО преобразует любые числа в ИСТИНА, а ошибки в ЛОЖЬ:
{ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА}
Этот массив отправляется на логическую проверку ЕСЛИ.
Она заменяет позиции, соответствующие ИСТИНА в приведённом выше массиве, фактическими значениями из B2: B8:
{ЛОЖЬ; 80; 130; 120; 70; ЛОЖЬ; 90}
Этот окончательный массив передается в СРЗНАЧ, которая вычисляет, игнорируя логические значения.
Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения
Если вы хотите найти среднее арифметическое на основе нескольких числовых критериев и условий больше/меньше, чем в сочетании с логикой ИЛИ, то формула, рассмотренная в предыдущем примере, не будет работать. Ведь вы не можете поместить эти логические выражения в массив. Решение заключается в применении функции СУММ в формуле массива.
Предположим, у вас есть количество в столбце B и сумма в столбце C, и вы хотите усреднить суммы, которые больше 50 в столбце B или C. При этом вы хотите избежать дубликатов, то есть не считать строку дважды, если число больше 50 в обоих столбцах.
Вот рабочий пример:
{=СУММ(ЕСЛИ((C2:C8+D2:D8>50);D2:D8;0))/СУММ( —((C2:C8+D2:D8)>50))}
Помните, что это формула массива, и поэтому вы должны нажать Ctrl + Shift + Enter и ввести ее правильно.
Как видите, выражение состоит из 2 частей. В первой части вы применяете функцию ЕСЛИ с оператором ИЛИ в аргументе (C2:C8> 50) + (D2:D8> 50). Как вы, вероятно, знаете, в формулах массива плюс (+) действует как оператор ИЛИ.
Итак, первая часть складывает числа в столбце C, если выполняется любое из условий.
Вторая часть возвращает количество таких ячеек, а затем вы делите сумму на количество, чтобы найти среднеарифметическое.
И, естественно, вы можете указать разные условия для каждого диапазона. Например, чтобы усреднить продажи, если столбец C больше 50 или столбец D больше 100, используйте следующие выражения: (C2: C8> 50) + (D2: D8> 100). Всё целиком будет выглядеть следующим образом:
=СУММ(ЕСЛИ((C2:C8>50)+(D2:D8>100);D2:D8;0))/СУММ(—((C2:C8>50)+(D2:D8>100)))
Формула среднего значения в Excel
Среднее арифметическое чисел в Excel рассчитывают с помощью функции СРЗНАЧ. Выглядит примерно так.
У этой формулы есть замечательное свойство. Если в диапазоне, по которому рассчитывается формула, присутствуют пустые ячейки (не нулевые, а именно пустые), то они исключается из расчета.
Вызвать функцию можно разными способами. Например, воспользоваться командой автосуммы во вкладке Главная:
После вызова формулы нужно указать диапазон данных, по которому рассчитывается среднее значение.
Есть и стандартный способ для всех функций. Нужно нажать на кнопку fx в начале строки формул. Затем либо с помощью поиска, либо просто по списку выбрать функцию СРЗНАЧ (в категории «Статистические»).
Среднее по условию — функция СРЗНАЧЕСЛИ
СРЗНАЧЕСЛИ вычисляет среднее арифметическое всех ячеек, которые соответствуют заданным критериям.
СРЗНАЧЕСЛИ (диапазон; критерии; [диапазон_усреднения])
Она имеет следующие аргументы (первые 2 обязательны, последний – нет):
- Диапазон — диапазон ячеек, которые будут проверены по заданным параметрам.
- Критерии — условие, применяемое для определения, какие ячейки усреднять. Они могут быть представлены в виде числа, логического выражения, текста или ссылки, например, 5,> 5, «кот» или A2.
- Диапазон усреднения — что вы хотите усреднить (он необязательный). Если опущено, функция будет считать по диапазону условия.
А теперь давайте посмотрим, как вы можете применить СРЗНАЧЕСЛИ на реальных рабочих листах, чтобы найти среднее ячеек, соответствующих вашим требованиям.
Точное соответствие критериям
Классическое применение СРЗНАЧЕСЛИ — это нахождение среднего арифметического ячеек, которые точно соответствуют заданному условию.
В этом примере давайте усредним только продажи (B2: B8) для заказов на бананы (A2: A8):
=СРЗНАЧЕСЛИ($A$2:$A$8; $B$10; $B$2:$B$8)
Вместо того, чтобы вводить условие непосредственно в формулу, вы можете ввести его в отдельную ячейку ($B$10) и затем обратиться к ней. Это очень известный и всеми применяемый приём.
Примечание. Чтобы округлить возвращаемое число до определенного количества десятичных знаков, примените одну из функций округления Excel, или диалоговое окно «Формат ячеек», чтобы изменить только отображение числа на экране.
Например, чтобы округлить полученный выше результат до 2 десятичных знаков, вы можете заключить его в функцию ОКРУГЛ следующим образом:
=ОКРУГЛ(СРЗНАЧЕСЛИ(A2:A8;»бананы»; B2:B8);2)
Кроме того, вы можете выбрать ячейку с формулой (E1 в этом примере), нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Затем перейдите на вкладку «Числовой» или «Денежный » и выберите количество десятичных разрядов, которые вы хотите показать. Помните, что в этом случае фактическое сохраненное значение не изменится, и точное неокругленное число будет использоваться во всех вычислениях, когда вы ссылаетесь на него в других формулах.
Частичное соответствие критериям (с символами подстановки).
Вы можете применить символы подстановки в аргументе критерия для выбора ячеек на основе частичного совпадения:
- Вставьте знак вопроса (?), чтобы заменить любой отдельный символ.
- Вставьте звездочку (*), чтобы заменить ею любую последовательность символов.
- Чтобы найти реальный знак вопроса или звездочку, введите тильду (~) перед этим символом (то есть, ~? или ~*).
Предположим, что у вас есть 3 разных вида бананов, и вы хотите найти среднюю величину их продаж:
=СРЗНАЧЕСЛИ($A$2:$A$8; «*бананы»; $B$2:$B$8)
Если вашему ключевому слову может предшествовать и/или последовать какой-либо другой символ, добавьте звездочку * как перед словом, так и после него.
=СРЗНАЧЕСЛИ($A$2:$A$8; «*бананы*»; $B$2:$B$8)
Чтобы найти среднее по всем товарам, за исключением каких-либо бананов, запишите следующую формулу:
=СРЗНАЧЕСЛИ($A$2:$A$8; «<>*бананы*»; $B$2:$B$8)
Как применить числовые условия и логические операторы.
Довольно часто вам может потребоваться усреднить ячейки, в которых количество больше или меньше определенного числа. Например, есть перечень чисел в столбце A, и мы хотим найти среднее из тех из них, которые больше 10.
Правильный подход — заключить логический оператор и число в двойные кавычки.
Итак, мы получили вот что:
=СРЗНАЧЕСЛИ($A$2:$A$7;»>10″)
Другая весьма распространенная проблема – как найти среднее чисел, которые не равны нулю. Для этого вам понадобится оператор «не равно» в аргументе критерия:
=СРЗНАЧЕСЛИ($A$2:$A$7;»<>0″)
Как вы, возможно, заметили, мы не задействуем третий аргумент [диапазон усреднения] ни в одном из приведенных выше выражений, поскольку мы хотим считать по начальному диапазону.
Для пустых или непустых ячеек
При выполнении анализа данных в Excel часто может потребоваться найти среднее чисел, которые соответствуют либо пустым, либо непустым областям.
Среднее, если пусто.
Чтобы включить пустые ячейки, которые не содержат абсолютно ничего (без формулы, без строки нулевой длины), введите «=» в аргументе.
Например, вычислим среднее ячеек C2: C8, если клетка в столбце B в той же строке абсолютно пуста:
=СРЗНАЧЕСЛИ($B$2:$B$8; «=»; $C$2:$C$8)
Чтобы усреднить значения, соответствующие визуально пустым ячейкам, включая те, которые содержат пустые строки, возвращаемые другими функциями (например, с формулой вроде = «»), запишите «». Например:
=СРЗНАЧЕСЛИ($B$2:$B$8; «»; $C$2:$C$8)
Среднее, если не пустое.
Чтобы найти среднее, соответствующее непустым ячейкам, введите «<>».
Например, так вычисляем среднеарифметическое C2:C8, если соответствующая позиция в столбце B не пуста:
=СРЗНАЧЕСЛИ($B$2:$B$8; «<>»; $C$2:$C$8)
Использование ссылок и других функций в описании условий.
Вместо того, чтобы записывать параметры подсчета непосредственно в формуле, вы можете обратиться к определенной ячейке, в которую можно вводить различные значения, не изменяя саму формулу. Так значительно уменьшается риск случайной ошибки при корректировке.
Если ссылка является критерием точного соответствия, то просто укажите ее в аргументе:
=СРЗНАЧЕСЛИ($B$2:$B$8;E1; $C$2:$C$8)
Если в качестве условия вы берёте логическое выражение со ссылкой или c другой функцией, нужно заключить логический оператор в «двойные кавычки» и добавить амперсанд (&) для их объединения.
Например, чтобы рассчитать средние продажи (C2: C8), превышающие указанное в E4:
=СРЗНАЧЕСЛИ($C$2:$C$8; «>»&$B$13)
С датами в B2: B8, приведенное ниже возвращает среднюю величину продаж (C2: C8), которые произошли до текущей даты:
=СРЗНАЧЕСЛИ($B$2:$B$8; «<=»&»22-07-2020»; $C$2:$C$8)
Вместо прямого указания даты можно взять соответствующую функцию. К примеру,
=СРЗНАЧЕСЛИ($B$2:$B$8; «<=»&СЕГОДНЯ(); $C$2:$C$8)
Средняя арифметическая взвешенная
Рассмотрим следующую простую задачу. Между пунктами А и Б расстояние S, которые автомобиль проехал со скоростью 50 км/ч. В обратную сторону – со скоростью 100 км/ч.
Какова была средняя скорость движения из А в Б и обратно? Большинство людей ответят 75 км/ч (среднее из 50 и 100) и это неправильный ответ. Средняя скорость – это все пройденное расстояние, деленное на все потраченное время. В нашем случае все расстояние – это S + S = 2*S (туда и обратно), все время складывается из времени из А в Б и из Б в А. Зная скорость и расстояние, время найти элементарно. Исходная формула для нахождения средней скорости имеет вид:
Теперь преобразуем формулу до удобного вида.
Подставим значения.
Правильный ответ: средняя скорость автомобиля составила 66,7 км/ч.
Средняя скорость – это на самом деле среднее расстояние в единицу времени. Поэтому для расчета средней скорости (среднего расстояния в единицу времени) используется средняя арифметическая взвешенная по следующей формуле.
где x – анализируемый показатель; f – вес.
Аналогичным образом по формуле средневзвешенной средней рассчитывается средняя цена (средняя стоимость на единицу продукции), средний процент и т.д. То есть если средняя считается по другим усредненным значениям, нужно применить среднюю взвешенную, а не простую.
Минимальное значение без учёта нулей
Задача, чтобы вВ конструкторе сводной таблицы (Database) необходимо найти максимальный
МАКСЕСЛИПоиск максимального и минимального
BТоварпоявились только начинаяна предмет равенства значения среди неповторяющихся, если значение <>или с Именованными диапазонами: А в столбцев область строк,Как видите, зеленые ячейки с 2016 версииДля будущего удобства, конвертируем
текущему товару ( значений приведен в с результатами транспонированы Excel. Если у исходный диапазон смы подсчитываем количество или отдельно заполненные формула массива), т.е.Бумага максимальное значение+1, т.е.Часть формулы Текст=E6, вернет сумма» — отображалосьЦену ценами в «умную нулей в диапазоне ячейки. Для удобства можно вводить и). Если это так, ячейку нашей «умной» минимальное среди неповторяющихся критериев представляет собой просто ввести в
excelworld.ru>
Формула средневзвешенного значение в Excel
Обычная функция среднего значения в Excel СРЗНАЧ, к сожалению, считает только среднюю простую. Готовой формулы для среднего взвешенного значения в Excel нет. Однако расчет несложно сделать подручными средствами.
Самый понятный вариант создать дополнительный столбец. Выглядит примерно так.
Имеется возможность сократить количество расчетов. Есть функция СУММПРОИЗВ. С ее помощью можно рассчитать числитель одним действием. Разделить на сумму весов можно в этой же ячейке. Вся формула для расчета среднего взвешенного значения в Excel выглядит так:
=СУММПРОИЗВ(B3:B5;C3:C5)/СУММ(C3:C5)
Интерпретация средней взвешенной такая же, как и у средней простой. Средняя простая – это частный случай взвешенной, когда все веса равны 1.
Через мастер формул
При необходимости нужно выделить нужную ячейку и кликнуть на функции.
Далее в категории «Полный алфавитный перечень» выбираем СРЗНАЧ.
Теперь можно выделить столбец или строку, где нужно произвести вычисление.
Физический смысл средней арифметической
Представим, что имеется спица, на которой в разных местах нанизаны грузики различной массы.
Как отыскать центр тяжести? Центр тяжести – это такая точка, за которую можно ухватиться, и спица при этом останется в горизонтальном положении и не будет переворачиваться под действием силы тяжести. Она должна быть в центре всех масс, чтобы силы слева равнялись силам справа. Для нахождения точки равновесия следует рассчитать среднее арифметическое взвешенное расстояний от начала спицы до каждого грузика. Весами будут являться массы грузиков (mi), что в прямом смысле слова соответствует понятию веса. Таким образом, среднее арифметическое расстояние – это центр равновесия системы, когда силы с одной стороны точки уравновешивают силы с другой стороны.
И последнее. В русском языке так сложилось, что под словом «средний» обычно понимают именно среднее арифметическое. То есть моду и медиану как-то не принято называть средним значением. А вот на английском языке слово «средний» (average) может трактоваться и как среднее арифметическое (mean), и как мода (mode), и как медиана (median). Так что при чтении иностранной литературы следует быть бдительным.
Онлайн курс
Статистика в MS Excel
Если в массиве есть числа в текстовом формате
В случае, если число введено в ячейку с текстовым форматом (см. ячейку А6 ), то это значение воспринимается функцией СРЗНАЧ() как текст и игнорируется. Поэтому, не смотря на то, что в диапазоне A 5: A 15 11 значений, для функции СРЗНАЧ() их всего 10, т.е. n в этом случае =10. Среднее будет равно 20,4 (см. файл примера ).
Функция СРЗНАЧA() ведет себя по другому: числа в текстовом формате интерпретируются ею как 0, впрочем, как и все остальные текстовые значения, включая значение Пустой текст “” . Поэтому, для функции СРЗНАЧА() n =11, а среднее будет равно 18,545454.
Чтобы числа в текстовом формате также учитывались при вычислении среднего, то нужно записать формулу массива =СРЗНАЧ(A5:A15+0) . В этом случае среднее будет равно 18,818181, т.к. n=11, а число в текстовом формате будет интерпретировано как 3.
Примечание : Чтобы перевести числа из текстового формата в числовой см. статью Преобразование ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами) .
Примечание : о вычислении средневзвешенного значения см. статью Средневзвешенная цена в MS EXCEL
Синтаксис формулы СРЗНАЧ
Формула СРЗНАЧ имеет следующий типичный для функций Excel синтаксис:
- СРЗНАЧ(X1; X2; X3), где X1, X2, X3 — символическое обозначение аргументов функции.
Функция может принимать до 255-ти аргументов, хотя конечно на практике столько никто не использует. Обратите внимание, что если Вам требуется указать очень много аргументов, то скорее всего Вы неправильно используете формулу и следует использовать диапазоны (про это читайте ниже).
Аргументы в формуле отделяются друг от друга символом «точка с запятой». После последнего аргумента разделитель не требуется.
Поиск максимального значения с условием
2
автомобилях найти значение извиняюсь, но в станет 4 ВсёПостарайтесь в следующий: Ээээ, сорри, этоПомогите, пжл, разобратьсяНу вообще-то в присваивается соответственный формат формулу для определения же диапазону должно и Минимальный по
функция ДМАКС() вернет
G5:G6
Предположим, что в диапазоне
удовлетворяющее заданным условиям.72 максимального пробега для
чем великая сермяжная
Так что ли? раз прикреплять пример вкладка Значение. )))) с формулой: примере (которого нет)
(цвет заливки)
Важно форматируемых ячеек». быть применено 2 условию в MS 0. Это может– ссылка на
A5:D11
Функция ДМАКС() относится к
Формула
3 черных Мерседесов. правда этого попрыгунства?Sub www() xls (2003 Excel)viktturНеобходимо найти в не так отметить что вВ поле ввода введите правила условного форматирования.
EXCEL).
ввести в заблуждение, табличку критериев (см.имеется таблица продаж той же группе
=НАИБОЛЬШИЙ (A2:B6;3)
4СтасиДИдем в 1ps = Selection.RowDV: Что Вы хотели диапазоне максимальное значение,200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОДСТАВИТЬ(АДРЕС(1;ПОИСКПОЗ(МАКС(A1:E1);A1:E1;0);4);1;)&МАКС(A1:E1) формуле для просмотра формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и Чтобы проверить выберитеПозицию максимального значения в т.к. не понятно:
рисунок выше).
( функций, что иТретье по величине5
CyberForum.ru>
