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

Функция СРЗНАЧ

Формула MAX IF с несколькими критериями

Как найти среднее арифметическое число в Excel

Расчет доли в процентах (удельного веса).

Среднее квадратическое отклонение: формула в Excel

Функция Excel СРЗНАЧЕСЛИ

Наблюдения и советы этой статьи мы подготовили на основании опыта команды СРЗНАЧЕСЛИ функция вычисляет среднее (среднее арифметическое) чисел в диапазоне, соответствующем заданным критериям.

аргументы

• Диапазон (обязательно): диапазон одной или нескольких ячеек, включая числа или имена, массивы или ссылки, содержащие числа.
• Критерии (обязательно): критерии, определяющие, какие ячейки усредняются. Это может быть число, выражение, ссылка на ячейку или текст.
• Средний_ диапазон (необязательно): Необязательно. Фактический диапазон ячеек усреднить. Если опущено, используется диапазон.

Примечания к функциям

1. Ячейки в диапазоне, содержащие Правда или ложь игнорируются.
2. Пустые ячейки в диапазоне и medium_range игнорируются.
3. После появления критерий — пустая ячейка, это будет рассматриваться как нулевое (0) значение.
4. Логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения можно включить в аргумент условия.
5. Наблюдения и советы этой статьи мы подготовили на основании опыта команды #ДЕЛ/0! ошибка произойдет, если:
   • ни одна ячейка в диапазоне не соответствует критериям
   • все усредняемые значения не являются числовыми
6. Average_range не обязательно должен быть того же размера, что и range. Фактические ячейки для усреднения начинаются с верхней левой ячейки в Average_range и являются ячейками, соответствующими ячейкам в диапазоне.
7. В функции СРЗНАЧЕСЛИ может применяться только одно условие. Если требуется несколько критериев, используйте функцию СРЗНАЧЕСЛИМН.

Примеры

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

1. Скопируйте приведенную ниже формулу в ячейку F5.

=СРЗНАЧЕСЛИ (B5: B14, E5, C5: C14)

2. Преобразуйте диапазон и средний_диапазон в приведенной выше формуле в абсолютный.

• Выберите диапазон B5:B14, нажмите клавишу F4, чтобы получить $B$5:$B$14.
• Выберите диапазон C5:C14, нажмите клавишу F4, чтобы получить $C$5:$C$14.

3. нажмите Enter кнопку, чтобы получить результат в F5.

4. затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу. Результаты показаны на следующем снимке экрана.

Заметки:

1. Аргумент критерия приведенной выше формулы предоставляется как ссылка на ячейку.
2. Мы можем непосредственно ввод значения в критерии. Три формулы в ячейках F5, F6 и F7 соответственно изменены на:

   =СРЗНАЧЕСЛИ (B5: B14, «Манго», C5: C14)

   =СРЗНАЧЕСЛИ (B5: B14, «А*», C5: C14)

   =СРЗНАЧЕСЛИ (B5: B14, «<>Лимон», C5: C14)

3. Критерии «Манго» означают усреднение продаж манго. Критерий «А*» означает усреднение продаж товаров, начинающихся с «А». Критерий «<>Лимон» означает усреднение продаж товаров, не являющихся лимоном.

Относительные функции:

Excel DEVSQ ФункцияФункция DEVSQ вычисляет сумму квадратов отклонений от выборочного среднего.

Excel DSTDEV ФункцияФункция DSTDEV возвращает оценочное значение стандартного отклонения генеральной совокупности на основе выборки.

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

Вычисление с помощью Мастера функций

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

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

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

Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.

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

Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.

После этого, жмите на кнопку «OK».

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

Подсчет определенных символов в диапазоне ячеек

СРЗНАЧСЛИМН с текстовыми критериями

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

В качестве примера найдем среднее значение продаж «Apple» в регионе «Север». Для этого составим формулу СРЗНАЧЕСЛИМН с двумя критериями:

• Average_range — это C3:C15 (ячейки усредняются).
• Критерий_диапазон1 — это A3:A15 (элементы для проверки), а критерий 1 — «яблоко».
• Критерий_диапазон2 — B3:B15 (регионы для проверки), а критерий2 — «север».

Сложив аргументы вместе, получим следующую формулу:

=СРЗНАЧЕСЛИ(C3:C15, A3:A15, «яблоко», B3:B15, «север»)

С критериями в предопределенных ячейках (F3 и F4) формула принимает следующий вид:

=СРЗНАЧЕСЛИ(C3:C15, A3:A15, F3, B3:B15, F4)

Функция СРЗНАЧЕСЛИ в Excel

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

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

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

А вот, в поле «Условие» мы должны указать конкретное значение, числа больше или меньше которого будут участвовать в расчете. Это можно сделать при помощи знаков сравнения. Например, мы взяли выражение «>=15000». То есть, для расчета будут браться только ячейки диапазона, в которых находятся числа большие или равные 15000. При необходимости, вместо конкретного числа, тут можно указать адрес ячейки, в которой расположено соответствующее число.

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

Когда все данные введены, жмем на кнопку «OK».

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

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

Помогла ли вам эта статья?

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

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

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

Дополнительная информация

• Пустые ячейки игнорируются при вычислении;
• Если в качестве критерия указана пустая ячейка, то функция воспринимает ее значение как “0”;
• Если ни одна ячейка из заданного диапазона не соответствует критерию, функция выдаст ошибку;
• Если диапазон ячеек пуст или содержит данные в текстовом формате, формула выдаст ошибку;
• Критерием может выступать число, выражение, ссылка на ячейку, текст или формула;
• Критерий, указанный в формате текста или логического/математического символа (=,+,-,/,*) следует указывать в двойных кавычках;
• Подстановочные знаки могут использоваться в качестве критерия.

Используем логические функции Excel для задания сложных условий

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

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

Логическая функция И()

Возвращает ИСТИНА, если все используемые условия истинны, или ЛОЖЬ, если хотя бы одно из них ложное.

=И(A1>B1; A2<>25)

Данная формула проверяет сразу два условия и возвращает ИСТИНА, если оба из них выполняются. В следующем примере оба условия истинны, поэтому и функция возвращает ИСТИНА:

В данном примере первое условие ложно, а второе истинно, поэтому функция вернула ЛОЖЬ:

Логическая функция ИЛИ()

Возвращает ИСТИНА, если хотя бы одно из условий истинно, или ЛОЖЬ, если все условия ложны.

=ИЛИ(A1>B1; A2>B2; A3>B3)

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

Поскольку в этом примере все условия ложны, то и функция вернула ЛОЖЬ.

Логическая функция НЕ()

Меняет логическое значение своего аргумента на противоположное. Если аргумент имеет значение ИСТИНА, функция меняет его на ЛОЖЬ. Если же значение аргумента ЛОЖЬ, то функция меняет его на ИСТИНУ.

=НЕ(A1>B1)

Например, на следующем рисунке видно, что число в ячейке A1 больше, чем в A2. Соответственно, выражение A1>B1 – имеет логическое значение ИСТИНА. Применив функцию НЕ в формуле, мы изменили его на противоположное.

Логическая функция ИСКЛИЛИ()

Возвращает логическое «исключающее или» всех аргументов. Функция вернет ИСТИНА, если число истинных условий нечетное, и ЛОЖЬ, если число истинных условий четное. Если все аргументы функции ИСКЛИЛИ имеют значение ЛОЖЬ, то и функция возвратит ЛОЖЬ.

=ИСКЛИЛИ(A1>B1; A2>B2; A3>B3; A4>B4)

В следующем примере формула вернет ИСТИНА, поскольку три условия из четырех истинны (нечетное количество):

В данном примере формула вернет ЛОЖЬ, поскольку все 4 условия истинны (четное количество):

В данном примере формула вернет ЛОЖЬ, поскольку не одно из условий не выполняется:

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

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

Оцените качество статьи

Нам важно ваше мнение:

Средняя арифметическая как оценка математического ожидания

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

Формула матожидания имеет следующий вид:

где M(X) – математическое ожидание

x_i – это случайные величины

p_i – их вероятности.

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

Математическое ожидание суммы выпавших очков при бросании двух игральных костей равно 7. Это легко подсчитать, зная вероятности. А как рассчитать матожидание, если вероятности не известны? Есть только результат наблюдений. В дело вступает статистика, которая позволяет получить приблизительное значение матожидания по фактическим данным наблюдений.

Математическая статистика предоставляет несколько вариантов оценки математического ожидания. Основное среди них – среднее арифметическое.

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

где x_i – значения переменной,n – количество значений.

Среднее арифметическое – это соотношение суммы значений некоторого показателя с количеством таких значений (наблюдений). 

СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)

Анализ НОРМАЛЬНОСТИ РАСПРЕДЕЛЕНИЯ | АНАЛИЗ ДАННЫХ #4

Table of Contents:

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

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

Четыре группы, их средства, стандартные отклонения и стандартные ошибки. На графике показаны групповые средства.

Для каждой группы вы можете использовать AVERAGE для вычисления среднего и STDEV. S для вычисления стандартного отклонения. Вы можете рассчитать стандартную ошибку каждого среднего. Выберите ячейку B12, поэтому в поле формулы показано, что вы вычислили стандартную ошибку для столбца B по этой формуле:

= B11 / SQRT (COUNT (B2: B9))

Фокус в том, чтобы получить каждую стандартную ошибку в графике. В Excel 2016 это легко сделать, и оно отличается от предыдущих версий Excel. Начните с выбора графика. Это приведет к появлению вкладок Design and Format. Выберите

Дизайн | Добавить элемент диаграммы | Ошибка баров | Дополнительные параметры ошибок.

Путь к вставке баров ошибок.

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

Дополнительные параметры панели ошибок являются подходящим выбором. Откроется панель «Формат ошибок».

Панель «Ошибки формата».

В области «Направление» панели выберите переключатель рядом с «Оба», а в области «Стиль конца» выберите переключатель рядом с «Кап».

Один выбор в области «Сумма ошибки» — это стандартная ошибка. Избегайте этого. Это не означает, что Excel помещает стандартную ошибку каждого среднего на график.

Прокрутите вниз до области «Сумма ошибки» и выберите переключатель рядом с «Пользовательский». Это активирует кнопку «Укажите значение». Нажмите эту кнопку, чтобы открыть диалоговое окно «Пользовательские ошибки». С помощью курсора в поле «Положительное значение ошибки» выберите диапазон ячеек, который содержит стандартные ошибки ($ B $ 12: $ E $ 12). Вставьте вкладку «Отрицательная ошибка» и сделайте то же самое.

Диалоговое окно «Нестандартные ошибки».

Это поле Negative Error Value может дать вам небольшую проблему. Перед тем, как вводить диапазон ячеек, убедитесь, что он очищен от значений по умолчанию.

Нажмите «ОК» в диалоговом окне «Нестандартные ошибки» и закройте диалоговое окно «Формат ошибок», и график будет выглядеть следующим образом.

График группы означает, включая стандартную ошибку каждого среднего.

Формула средневзвешенного значение в Excel

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

3-ий метод как высчитать среднее значение в Excel.

Воспользуемся клавишей вызова функции: Вставить функцию.

Избираем ячейку, в которой обязано показаться среднее значение, которое нам необходимо высчитать. К примеру, в ячейки Н3. Опосля нажатия на клавишу Вставить функцию, показаться диалоговое окно Вставка функции, в котором будет список 10 не так давно использовавшихся функций, в числе которых быть может интересующее нас функция: СРЗНАЧ. Если данной функции в списке не будет, необходимо пользоваться поиском. В поле поиска необходимо прописать СРЗНАЧ и надавить Отыскать.

Жмем ОК. Возникает диалоговое окно Аргументы функции.

В поле Число 1, необходимо внести спектр ячеек, по которым необходимо высчитать среднее значение. Становимся курсором на поле Число 1 и выделяем подходящий нам спектр ячеек.

Жмем ОК. Получаем среднее значение по данному спектру ячеек.

В поле Число 2, можно добавить очередной спектр ячеек. Тогда и функция будет считать среднее по двум спектрам. При этом, если добавить спектр в поле Число 2, то в диалоговом окне Аргументы функции показаться очередное поле: Число 3. В которое так же можно добавить спектр ячеек. Соответственно, если добавить спектр в поле Число 3, показаться поле Число 4 и так дальше.

В поля Число 1, Число 2, Число 3 и т.д., можно, в пространство спектра ячеек, указывать отдельные ячейки и рассчитывать по ним среднее значение. Ячейки при всем этом могут находится в различных частях Листа.

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

Функция СЧЕТ в Excel

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

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

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

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

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

Вычислить наибольшую разницу в сумме.

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

 Ниже представлена ​​таблица с небольшой выборкой данных за 4 месяца.

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

Но с помощью функции MAX можно значительно упростить расчет и не использовать такую ​​длинную и сложную структуру.

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

Как видно из таблицы ниже, этот вариант намного проще и возвращает те же результаты.

Объединяем несколько условий.

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

​Поиск функции​ должно отображаться среднее​Расчет среднего с отображением​Примечание:​ формула для расчета​Вставить функцию​Выделите ячейки с A2​ вам полезна. Просим​ в Excel. В​ и с помощью​ появился выпадающий список.​

​Описание (результат)​ распределение ряда чисел​ Мы стараемся как можно​ и для другой​ общее количество значений​ качество всех изделий​. Например, начните вводить​

​ значение, например ячейку​​ на листе​ Не выделяйте заголовки строк​ среднего качества всех​, чтобы открыть панель​ по A7 (значения​ вас уделить пару​ некоторых ситуациях ручной​ специального инструмента. Во​В нем вам необходимо​=СУММПРОИЗВ(A2:A4;B2:B4)/СУММ(B2:B4)​

​ они могут быть​​ оперативнее обеспечивать вас​ строки? Чтобы ее​ х.​ с оценкой качества​СУММПРОИЗВ​ A8, которая находится​Выберите ячейку, в которой​ или столбцов (1, 2, 3. A,​ изделий, у которых​Построитель формул​ в столбце «Качество​

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

​ можно было скопировать-вставить​Если значения расположены в​ выше 5″.​.​ слева ячейки с​ должно отображаться среднее​ B, C. ) при​ оценка качества выше​

​ изделия»).​ помогла ли она​ много раз ускорит​ по кнопке, расположенной​

​ «Среднее».​​ трех покупок на​​Выполните следующие действия.​ на вашем языке.​​ в нужную ячейку​​ диапазоне​На вкладке​​В разделе​

​ значение, например ячейку​

Вычисление среднего значения ячеек, расположенных вразброс

​В списке​На вкладке​​ вам, с помощью​​ процесс расчета.​ в правой части​Как только вы это​

​ общее количество приобретенных​

​Щелкните ячейку ниже или​

​ Эта страница переведена​

​ на другой строке?​A5:A15​

​Аргументы​ изделия» в примере​ A8, которая находится​

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

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

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

​щелкните стрелку рядом​ Для удобства также​ нюансах, необходимо посмотреть​

​ Окно Мастера свернется​

​ рядом появится результат​

​Для выполнения этой задачи​

​ нужно найти среднее​

​ текст может содержать​

​ ниже) Видел где-то​

​ вычисления среднего (среднего​

​На панели инструментов​

​ текстом «Среднее качество​

​ с текстом «Среднее​

​ приводим ссылку на​

​ на синтаксис формулы,​

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

Вычисление среднего значения чисел, без учета нулевых значений

​ неточности и грамматические​ такие функции вроде​​ арифметического) будет выглядеть​​выберите пункт​​массив1​​Стандартная​ изделия» в примере​Качество изделия​ качество всех изделий​.​Автосумма​ оригинал (на английском​ выглядит он следующим​

​ будет выделить мышкой​

​ выделенных значений. Его​

​На вкладке «​ ошибки. Для нас​ бы со знаками​ так:​Построитель формул​

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

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

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

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

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

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

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

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

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

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

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Стоимость имущества	Комиссионные
100000	7000
200000	14000
300000	21000
400000	28000
Формула	Описание	Результат
=СРЗНАЧЕСЛИ(B2:B5;”<23000″)	Среднее значение всех комиссионных меньше 23 000. Этому условию удовлетворяют три из четырех значений, сумма которых составляет 42 000.	14000
=СРЗНАЧЕСЛИ(A2:A5;”<250000″)	Среднее среди всех значений стоимости имущества меньше 250 000. Этому условию удовлетворяет два из четырех значений, их сумма составляет 300 000.	150000
=СРЗНАЧЕСЛИ(A2:A5;”<95000″)	Среднее среди всех значений стоимости имущества меньше 95 000. Так как количество значений стоимости имущества, удовлетворяющих этому условию, равно 0, функция СРЗНАЧЕСЛИ вернет ошибку #ДЕЛ/0! из-за попытки деления на 0.	#ДЕЛ/0!
=СРЗНАЧЕСЛИ(A2:A5;”>250000″;B2:B5)	Среднее значение всех комиссионных для стоимости имущества более 250 000. Этому условию удовлетворяют два значения, сумма которых составляет 49 000.	24500

Пример 2

Регион	Доходы (в тысячах)
Восточный	45678
Западный	23789
Северный	-4789
Южная (новое представительство)
Средний Запад	9678
Формула	Описание	Результат
=СРЗНАЧЕСЛИ(A2:A6;”=*Западная”;B2:B6)	Среднее значение всех доходов для Западной и Средне-Западной областей.	16733,5
=СРЗНАЧЕСЛИ(A2:A6;”<>*(Новое представительство)”;B2:B6)	Среднее значение всех доходов для всех областей, за исключением новых представительств.	18589

Примеры суммеслимн

Аргументы (параметры) формулы СРЗНАЧ

Синтаксис

СРЗНАЧЕСЛИ(диапазон, условия, )

Аргументы функции СРЗНАЧЕСЛИ указаны ниже.

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

  

  

  

  

  

  

  

  

  

  

• Условие.    Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, “32”, “>32”, “яблоки” или B4.

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

Синтаксис

СРЗНАЧЕСЛИ(диапазон, условия, )

Аргументы функции СРЗНАЧЕСЛИ указаны ниже.

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

Условие. Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.

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

Свойства средней арифметической (математического ожидания)

Средняя арифметическая взвешенная

Рассмотрим следующую простую задачу. Между пунктами А и Б расстояние S, которые автомобиль проехал со скоростью 50 км/ч. В обратную сторону – со скоростью 100 км/ч. 

Какова была средняя скорость движения из А в Б и обратно? Большинство людей ответят 75 км/ч (среднее из 50 и 100) и это неправильный ответ. Средняя скорость – это все пройденное расстояние, деленное на все потраченное время. В нашем случае все расстояние – это S + S = 2*S (туда и обратно), все время складывается из времени из А в Б и из Б в А. Зная скорость и расстояние, время найти элементарно. Исходная формула для нахождения средней скорости имеет вид:

Теперь преобразуем формулу до удобного вида.

Подставим значения.

Правильный ответ: средняя скорость автомобиля составила 66,7 км/ч.

Средняя скорость – это на самом деле среднее расстояние в единицу времени. Поэтому для расчета средней скорости (среднего расстояния в единицу времени) используется средняя арифметическая взвешенная по следующей формуле.

где x – анализируемый показатель; f – вес.

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

Функция СЧЕТЕСЛИ в Excel и примеры ее использования

Формула ранжирования в Excel для выборки наибольших значений

В данном примере принципиальным является использование функции НАИБОЛЬШИЙ, так как мы анализируем результаты игры в боулинг. А в боулинге чем больше число пунктов, тем лучше результат. Если же бы мы анализировали другой вид спорта где вместо балов был список результатов времени в турнирной таблице, то в таком случае победителем будет тот участник, у которого наименьшее время. Турнирная таблица с временными результатами изображена ниже на рисунке. Таблица содержит дополнительный столбец с номером места каждого участника, а в нем использована формула следующая:

Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.

В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.

Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:

Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно: