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

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

Среднее Среднее арифметическое и вычисляется путем добавления ряда чисел и затем деления количества этих чисел. Например среднее значение 2, 3, 3, 5, 7 и 10 имеет 30, разделенных на 6, 5;

Медиана Средний номер ряда чисел. Половина чисел имеют значения, которые больше, чем Медиана, а половина чисел имеют значения, которые меньше, чем Медиана. Например медиана 2, 3, 3, 5, 7 и 10 - 4.

Режим Наиболее часто встречающееся число в группе чисел. Например режим 2, 3, 3, 5, 7 и 10 - 3.

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

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

Выполните следующие действия.

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

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

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

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

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

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

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

Функции категории Статистические предназначены в первую очередь для анализа диапазонов ячеек в 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.

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

Шаги

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

Введите числа, среднее арифметическое которых вы хотите найти. Например, рассмотрим множество из десяти чисел.

• В большинстве случаев числа вводятся в столбцах (как и в нашем примере), поэтому введите числа в ячейки A1 – A10.
• Числа для ввода: 2, 3, 5, 5, 7, 7, 7, 9, 16, и 19.
• Если хотите, найдите сумму чисел, введя формулу «=СУММ(A1:A10)» в ячейке A11 (без кавычек).

1. Найти среднее арифметическое можно при помощи функции СРЗНАЧ.

   • Нажмите на пустую ячейку, например, А12, а затем введите «=СРЗНАЧ(A1:10)» (без кавычек).
   • Нажмите на пустую ячейку, а затем нажмите на кнопку «f x » (в строке формул непосредственно над рабочим листом Excel). В открывшемся окне в списке «Выберите функцию» найдите и выделите «СРЗНАЧ», а затем нажмите OK. Введите диапазон «A1:A10» в строке «Число 1» открывшегося окна и нажмите ОК.
   • Введите знак равенства (=) в строке формул. Слева от строки формул в раскрывающемся списке «Функции» найдите и выберите «СРЗНАЧ». Введите диапазон «A1:A10» в строке «Число 1» открывшегося окна и нажмите ОК.

2. В ячейке, в которую вы вели формулу, отобразится среднее арифметическое, равное отношению суммы чисел в заданном диапазоне ячеек (80) к общему количеству чисел в диапазоне (10): 80/10 = 8.

   • Для проверки правильности ответа в любой пустой ячейке введите формулу «=A11/10».
   • Среднее арифметическое хорошо применимо для центрирования распределения, когда отдельные числа в некотором множестве чисел не очень отличаются друг от друга.

   Медиана

   1. Введите числа, медиану которых вы хотите найти.

      Найти среднее арифметическое можно при помощи функции МЕДИАНА. Вы можете ввести формулу одним из трех способов:

      • Нажмите на пустую ячейку, например, А13, а затем введите «=МЕДИАНА(A1:10)» (без кавычек).
      • Нажмите на пустую ячейку, а затем нажмите на кнопку «f x » (в строке формул непосредственно над рабочим листом Excel). В открывшемся окне в списке «Выберите функцию» найдите и выделите «МЕДИАНА», а затем нажмите OK. Введите диапазон «A1:A10» в строке «Число 1» открывшегося окна и нажмите ОК.
      • Введите знак равенства (=) в строке формул. Слева от строки формул в раскрывающемся списке «Функции» найдите и выберите «МЕДИАНА». Введите диапазон «A1:A10» в строке «Число 1» открывшегося окна и нажмите ОК.

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

   Мода

   Введите числа, моду которых вы хотите найти. Например, рассмотрим множество из десяти чисел (2, 3, 5, 5, 7, 7, 7, 9, 16 и 19). Введите эти числа в ячейки А1 – А10.

   В разных версиях Excel функция нахождения моды различна.

   1. Вы можете ввести формулу одним из трех способов:

      • Нажмите на пустую ячейку, например, А14, а затем введите «=МОДА(A1:10)» или «=МОДА.ОДН(A1:10)» (без кавычек).
      • Нажмите на пустую ячейку, а затем нажмите на кнопку «f x » (в строке формул непосредственно над рабочим листом Excel). В открывшемся окне в списке «Выберите функцию» найдите и выделите «МОДА» или «МОДА.ОДН», а затем нажмите OK. Введите диапазон «A1:A10» в строке «Число 1» открывшегося окна и нажмите ОК.
      • Введите знак равенства (=) в строке формул. Слева от строки формул в раскрывающемся списке «Функции» найдите и выберите «МЕДИАНА» или «МОДА.ОДН». Введите диапазон «A1:A10» в строке «Число 1» открывшегося окна и нажмите ОК.

   2. В ячейке, в которую вы вели формулу, отобразится значение моды, равное числу, которое встречается в некотором множестве чисел наиболее часто. В нашем примере мода равна 7, так как число 7 встречается в нашем множестве чисел три раза.

      • Если два числа встречаются в диапазоне чисел одинаковое количество раз, функция МОДА или МОДА.ОДН отобразит первое встреченное число. Если в нашем примере вы измените 3 на 5, то мода будет равна 5, а не 7, потому что 5 встречается в первую очередь.

   Среднее взвешенное

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

      • В нашем примере введем заголовки столбцов. Введите «Цена за единицу» в ячейке A1, и «Число отгруженных единиц» в ячейке B1.
      • Первая партия состояла из 10 единиц по $20 за единицу. Введите «$20» в ячейке A2 и «10» в ячейке B2.
      • Спрос на тоник увеличился, поэтому вторая партия состояла из 40 единиц по $30 за единицу. Введите «$30» в ячейке A3 и «40» в ячейке B3.
      • Так как цена выросла, спрос на тоник упал и третья партия состояла из 20 единиц по $25 за единицу. Введите «$25» в ячейке A4 и «20» в ячейке B4.

   2. Для вычисления среднего взвешенного в Excel необходимо использовать две функции:

      • СУММПРОИЗВ. Функция СУММПРОИЗВ перемножает числа в одной строке и складывает произведения чисел во всех строках. Вы указываете диапазон каждого столбца; в нашем примере: «СУММПРОИЗВ=(A2:A4,B2:B4)». В результате вы получите общую стоимость всего отгруженного тоника.
      • СУММ. Функция СУММ складывает числа в одной строке или столбце. Так как вы хотите найти среднюю цену единицы отгруженного тоника, необходимо найти общее количество отгруженного тоника. В нашем примере: «=СУММ(B2:B4)».
      Так как среднее значение определяется отношением суммы всех чисел к количеству чисел, вы можете объединить эти две функции в одну формулу: «СУММПРОИЗВ=(A2:A4,B2:B4)/СУММ(B2:B4)».

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

      • Общая стоимость отгруженного тоника: 20 х 10 + 30 х 40 + 25 х 20 = 200 + 1200 + 500 = $1900.
      • Общее количество отгруженного тоника: 10 + 40 + 20 = 70.
      • Средняя цена: 1900/70 = $27,14.
   • Вам не обязательно вводить все числа подряд (в столбец или строку), но вы должны убедиться, что Excel понимает, какие числа вы хотите включить и исключить. В нашем примере, если вы хотите найти средние значения пяти первых чисел, введите формулу так: «=СРЗНАЧ(A1:A5,A10)».

В данной статье будет рассмотрено несколько статистических функций приложения 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 содержит текстовую функцию, возвращающую пустую строку.

Функция СЧИТАТЬПУСТОТЫ

Подсчитывает пустые ячейки в указанном диапазоне.

Пустые строки (="") засчитываются как пустые.

У Вас недостаточно прав для комментирования.

Функции СУММ, СРЗНАЧ, МАКС, МИН, предназначены для работы с наборами значений, содержащихся в некотором диапазоне ячеек. В диалоговом окне Мастер функций - шаг 1 из 2 эти функции можно найти в категории Статистические. Функция СУММ относится к математическим и предназначена для подсчета суммы в некотором диапазоне ячеек. Несмотря на то что эти функции относятся к разным категориям и вычисляют различные параметры для некоторого набора числовых значений, между ними много общего. Они имеют похожий синтаксис и часто используются в вычислениях. Excel, как видно из всего сказанного выше очень мощный инструмент для решения задач с использованием различных функций.

Функция СУММ

Функция СУММ, вне всяких сомнений, является наиболее используемой функцией Excel. Функция СУММ имеет следующий синтаксис:

СУММ(число1;число2; ...;число255)

где число1, число2 -- количество аргументов (от 1 до 255), сумму которых необходимо вычислить.

Аргументами функции СУММ должны быть числа, заданные в виде конкретных значений, ссылок на ячейки или диапазоны ячеек, массивов констант. Вместо ссылок можно использовать имена ячеек или диапазонов ячеек. Массив констант представляет собой массив чисел, заключенных в фигурные скобки, например {1;2;3} или {1:2:3}. Числа в фигурных скобках должны разделяться точкой с запятой либо двоеточием.

Предположим, что в ячейках A1, A2 и A3 содержатся значения 1, 2 и 3. Тогда формулы =СУММ(1;2;3), =СУММ(A1;A2;A3), =СУММ(A1:A3), =СУММ(Данные) и =СУММ({1;2;3}) возвращают один и тот же результат -- 6. Здесь имя Данные присвоено диапазону A1:A3. В первой формуле в качестве аргументов функции СУММ используются ссылки на ячейки, во второй формуле аргументом функции СУММ является массив значений.

• § Сумму чисел, содержащихся в одном диапазоне ячеек. В этом случае достаточно задать один аргумент (число1). Для указания ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например, на рис. 6.25 формула =СУММ(C5:E5) в ячейке F5 возвращает сумму чисел, содержащихся в диапазоне ячеек C5:E5.
• · Сумму чисел, содержащихся в нескольких диапазонах (как смежных, так и несмежных). В этом случае можно задать до 255 аргументов. Для создания ссылки на два несмежных диапазона используется оператор объединения диапазонов, обозначаемый точкой с запятой (;). Например, формула =СУММ(C5:C7;C9:C11; C13:C15;C17:C19) в ячейке C24 (рис. 6.25) возвращает сумму чисел, которые находятся в диапазонах C5:C7, C9:C11, C13:C15 и C17:C19.
• § Сумму чисел, содержащихся в диапазоне, который является пересечением диапазонов, заданных в качестве аргументов. Для создания ссылки на такой диапазон используется оператор пересечения диапазонов -- пробел. Например, формула в ячейке C21=СУММ(C$5:C$7 $C5:$E5;C$9:C$11 $C9:$E9;C$13:C$15 $C13:$E13;C$17:C$19 $C17:$E17) возвращает сумму чисел, которые содержатся на пересечении диапазонов: C5:C7 и C5:E5 (ячейка C5), C9:C11 и C9:E9 (ячейка C9), C13:C15 и C13:E13 (ячейка C13), C17:C19 и C17:E17 (ячейка C17), т.е. заданная таким образом функция вычисляет сумму чисел, содержащихся в ячейках C5, C9, C13 и C17 (см. рис. 6.25).

В последней формуле использованы смешанные ссылки на диапазоны. Использование смешанных ссылок позволяет сократить время на ввод достаточно громоздких формул в диапазон C21:E23. Достаточно ввести только одну формулу в ячейку C21 и затем скопировать ее в остальные ячейки диапазона C21:E23.

Если на рабочем листе определены имена, то использование имен в качестве аргументов функции СУММ делает формулы если не менее громоздкими, то, по крайней мере, более осмысленными (рис. 6.26).

Особенности использования функции СУММ

При суммировании значений с помощью функции СУММ учитываются только числовые значения, представленные в одном из стандартных числовых форматов Excel, а также числа, представленные как текст. Аргументы, которые ссылаются на пустые ячейки, логические или текстовые значения, игнорируются (рис. 6.27).

Отметим, что логическое значение ИСТИНА может влиять на результат вычислений функции СУММ. Например, формула =СУММ(1;3;4;ИСТИНА), в которой логическое значение ИСТИНА указано явно, вернет значение 9. Формула =СУММ ({1;3;4;ИСТИНА}), в которой в качестве аргумента функции СУММ используется массив констант, возвращает тот же результат, что и формула =СУММ(E2:E6).

Если логические значения непосредственно используются в качестве аргументов функции СУММ, то они преобразуются в числа. Логическое значение ИСТИНА преобразуется в число 1, а значение ЛОЖЬ -- в 0. Вот почему формула =СУММ (1;3;4;ИСТИНА) (см. рис. 6.27) возвращает другой результат по сравнению с формулой =СУММ(E2:E6).

Если хотя бы один из аргументов функции СУММ является значением ошибки, функция СУММ возвращает значение ошибки (рис. 6.28).

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

Функция СРЗНАЧ вычисляет среднее арифметическое своих аргументов. Если заданы n действительных чисел a1, a2, ..., an, то число1 2...nna a aAn+ + +=называется средним арифметическим чисел a1, a2, ..., an.

В формуле для среднего арифметического сначала вычисляется сумма n чисел, затем полученный результат делится на количество слагаемых. Для вычисления среднего арифметического n чисел в Excel можно использовать одну из следующих формул:

СРЗНАЧ(Диапазон1)

СУММ(Диапазон1)/СЧЁТ(Диапазон1)

Пример использования этих формул показан на рис. 6.31. В качестве аргументов функций СРЗНАЧ, СУММ и СЧЁТ используются имена Диапазон1 и Диапазон2, содержащие ссылку на диапазоны A2:C6 и E2:G6 соответственно. Всего функция СРЗНАЧ может иметь до 255 аргументов.

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

Если при определении среднего арифметического необходимо учитывать пустые ячейки, логические значения и текст, используйте функцию СРЗНАЧА. При вычислении средне го арифметического с помощью функции СРЗНАЧА логическое значение ИСТИНА полагается равным 1, логическое значение ЛОЖЬ и текст (кроме чисел, представленных в виде текста) -- равными 0 (нулю). На рис. 6.31 хорошо видно, что функции СРЗНАЧ и СРЗНАЧА возвращают разные значения для одного и того же диапазона данных. Кроме того, если исходный диапазон содержит логическое значение ИСТИНА, формулы =СРЗНАЧА(Диапазон2) и =СУММ(Диапазон2)/СЧЁТЗ(Диапазон2) возвращают разные значения.

Функции МАКС и МИН

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

При определении максимального и минимального значений с помощью функций МАКС и МИН учитываются только те ячейки, которые содержат числа; пустые строки, текст (за исключением чисел, представленных в виде текста) и логические значения игнорируются (рис. 6.32).

Функции МИН и МАКС возвращают значение ошибки, если хотя бы одна ячейка диапазона содержит значение ошибки. Если диапазон не содержит ячеек с числовыми значениями, функции МАКС и МИН возвращают значение 0 (нуль).

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