Функция СРЗНАЧ (AVERAGE) – Справочник

Синтаксис: СРЗНАЧ(число1; [число2]; …)

Описание

Возвращает среднее арифметическое аргументов. Например, если диапазон A1:A20 содержит числа, формула =СПБ(A1:A20) возвращает среднее из этих чисел.

Первый способ как рассчитать среднее значение в Excel.

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

Среднее значение. Функция СРЗНАЧ в MS Excel. Описание и примеры

Синтаксис формулы СРЗНАЧ

Формула СРЗНАЧ имеет следующий типичный для функций Excel синтаксис:

  • СРЗНАЧ(X1; X2; X3),
    где X1, X2, X3 — символическое обозначение аргументов функции.

Функция может принимать до 255-ти аргументов, хотя конечно на практике столько никто не использует. Обратите внимание, что если Вам требуется указать очень много аргументов, то скорее всего Вы неправильно используете формулу и следует использовать диапазоны (про это читайте ниже).

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

Самая простая функция для определения среднего значения – это функция «СРЗНАЧ».

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

Пример расчета: ставим курсор в ячейку, запускаем мастер функций, выбираем функцию «СРЗНАЧ».

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

Нажимаем «ENTER».

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

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

#4. СРЗНАЧ

Синтаксис: =СРЗНАЧ(число1;[число2];…)

число1 — обязательный аргумент.

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

Допустим, у нас есть диапазон из 6 ячеек: 4 из них заполнены числами, включая 0, одно значение — текстовое и еще одно — пустое. Функция просуммирует только числовые и поделит сумму на общее количество числовых — 4.

image4.png

В результате мы получим среднее, равное 4. Давайте проверим формулой:

(4 + 5 + ТЕКСТ + 7 + 0 + ПУСТОЕ) / 4 = 16 / 4 = 4

ТЕКСТ и ПУСТОЕ игнорируются.

Среднее по условию – функция СРЗНАЧЕСЛИ

СРЗНАЧЕСЛИ вычисляет среднее арифметическое всех ячеек, которые соответствуют заданным критериям.

СРЗНАЧЕСЛИ (диапазон; критерии; [диапазон_усреднения])

Она имеет следующие аргументы (первые 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)

#5. ОКРУГЛ

Синтаксис: =ОКРУГЛ(число; число_разрядов)

число — аргумент.

число_разрядов — до какого разряда округляется число.

Функция ОКРУГЛ применяется для округления действительных чисел до требуемого количества знаков после запятой и возвращает округленное значение согласно математическому правилу округления.

К примеру, для округления числа 2,57525 до 2-х символов после запятой можно ввести формулу =ОКРУГЛ(2,57525;2), которая вернет значение 2,58. Эта функция часто используется при построении балансовых и других видов отчетности.

Третий способ как рассчитать среднее значение в Excel.

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

Среднее значение. Функция СРЗНАЧ в MS Excel. Описание и примеры

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

Среднее значение. Функция СРЗНАЧ в MS Excel. Описание и примеры

Нажимаем ОК. Появляется диалоговое окно Аргументы функции.

Среднее значение. Функция СРЗНАЧ в MS Excel. Описание и примеры

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

Среднее значение. Функция СРЗНАЧ в MS Excel. Описание и примеры

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

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

Среднее значение. Функция СРЗНАЧ в MS Excel. Описание и примеры

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

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

Пример

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

Данные

10

15

32

7

9

27

2

Формула

Описание

Результат

=СРЗНАЧ(A2:A6)

Среднее значение чисел в ячейках A2–A6.

11

=СРЗНАЧ(A2:A6;5)

Среднее значение чисел в ячейках A2–A6 и числа 5.

10

=СРЗНАЧ(A2:C2)

Среднее значение чисел в ячейках A2–C2.

19

Нужна дополнительная помощь?

#7. ВПР

Синтаксис: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

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

таблица — ссылка на диапазон ячеек. В левом столбце выполняется поиск искомого значения, а из столбцов правее выводится соответствующее значение. Левый столбец еще называют ключевым. Если в таблице не окажется искомого значения, вернется ошибка #Н/Д.

номер_столбца — номер столбца таблицы, согласно которому нужно вывести результат.

[интервальный_просмотр] — необязательный аргумент. Принимает два значения: ИСТИНА и ЛОЖЬ. ИСТИНА устанавливается по умолчанию, и функция предполагает, что левый столбец таблицы отсортирован по возрастанию в алфавитном порядке. Если в этом аргументе — ИСТИНА, функция ищет ближайшее к искомому или совпадающее с ним значение, ЛОЖЬ — ищет стопроцентное совпадение с искомым значением.

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

Пример

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

image11.png

В ячейке G2 пишем формулу: =ВПР(A2;$L$2:$M$78;2;ЛОЖЬ). Обязательно ставим знаки фиксации диапазона $ — в случае их отсутствия при протягивании формулы вниз диапазон ячеек таблицы также будет смещаться вниз вместе с искомым значением.

Как прочитать формулу простым языком: найти значение из ячейки A2 в левом столбце таблицы L2:M78 и вывести соответствующее найденному искомому_значению выражение из столбца 2 этой таблицы, при этом искомое значение должно совпадать с данными в левом столбце таблицы (аргумент ЛОЖЬ).

image6.png

Протягиваем формулу вниз — и выводится результат.

image16.png

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

Функция AVERAGE (СРЗНАЧ) в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

#8. ЕСЛИОШИБКА

Синтаксис: =ЕСЛИОШИБКА(значение;значение_если_ошибка)

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

значение_если_ошибка — значение, которое возвращается, если произошла ошибка.

Эта функция проверяет аргумент на равенство значениям ошибок #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0, #ЧИСЛО!, #ИМЯ? или #ПУСТО!. Если выражение в ячейке, которая проверяется, содержит ошибку, функция вернет значение, которое определено в этом случае. Если ошибки нет — результат расчетов или данные ячейки. Часто используется при делении на ноль.

Пример

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

image1.png

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

image18.png

Есть вопросы по использованию формул в Excel?

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

Уникальная метка публикации: 5394C157-2DDA-4958-7201-8B04D93A78FC
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/formula-srznach-v-excel-znachenie-sintaksis-parametry-primery/

Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку “Назад” браузера.

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

#10. СЦЕПИТЬ

Синтаксис: =СЦЕПИТЬ(текст1;[текст2];[текст3];…)

текст1 — обязательный аргумент.

Для объединения значений из разных ячеек в одну используется функция СЦЕПИТЬ. Также можно применять аналог — & (амперсанд). Функция часто используется для объединения данных с нескольких столбцов.

Пример

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

image10.png

Хотите получать дайджест статей?

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

Спасибо за подписку!

Последние материалы

img-mascot-619faf0bec82a355501337.jpg

Как появилась идея говорящих драже M&M’s и в чем успех английского капитана Моргана.

img-liski-619f751aeadcc188867888.jpg

Рассказываем, как диверсифицировать риски в бизнесе.

img-alkhimovich-619e600e7da7f353639925.jpg

6 способов завоевать авторитет в команде — от топ-менеджера с 10-летним опытом (Luxoft).

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...