Функции СУММ, СРЗНАЧ, МИН, МАКС

Функции СУММ, СРЗНАЧ, МАКС, МИН, предназначены для работы с наборами значений, содержащихся в некотором диапазоне ячеек. В диалоговом окне Мастер функций — шаг 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 хорошо видно, что функции МИН и МИНА, МАКС и МАКСА возвращают разные значения для одного и того же диапазона.