В этом разделе представлены основные принципы суммирования содержимого ячеек, а также рассмотрены примеры суммирования: как простейшие формулы суммирования, так и сложные формулы массивов, которые вычисляют значения на основе нескольких критериев.
Суммирование всех ячеек диапазона
Суммировать все ячейки диапазона несложно. Например, следующая формула возвращает сумму всех значений диапазона Данные:
=СУММ(Данные)
Функция СУММ может принимать до 255 аргументов. Следующая формула возвращает сумму значений в пяти несмежных диапазонах:
=СУММ(А1:А9;Cl:С9;El:Е9;Gl:G9;I1:I9)
В качестве аргументов функции СУММ можно использоваться строки или столбцы электронной таблицы. Например, формула, приведенная ниже, возвращает сумму всех значений в столбце А. Если данную формулу ввести в ячейку столбца А, это вызовет ошибку циклической ссылки.
=СУММ(А:А)
Следующая формула возвращает сумму всех значений, содержащихся на рабочем листе Лист1. Чтобы при ее создании избежать циклической зависимости, формула должна использоваться в другом рабочем листе, отличном от Лист1.
=СУММ(Лист1!1:1048576)
Функция СУММ достаточно универсальна. В качестве ее аргументов можно использовать числовые значения, ячейки, диапазоны, текстовые представления чисел (которые интерпретируются как значения), логические значения и даже вложенные функции. Например, рассмотрим следующую формулу:
=СУММ(В1;5;“6”; ;КОРЕНЬ(4);{1;2;3};А1:А5;ИСТИНА)
Она содержит типы аргументов, которые перечислены ниже в порядке их представления:
• простая ссылка на одну ячейку;
• значение, представленное константой;
• строка, похожая на числовое значение;
• аргумент отсутствует;
• выражение, использующее другую функцию;
• константа массива;
• ссылка на диапазон значений;
• логическое значение ИСТИНА.
Предупреждение
Несмотря на всю универсальность, функция СУММ в отношении логических значений (ИСТИНА или ЛОЖЬ) ведет себя достаточно противоречиво. Например, логические значения, сохраненные в ячейках, всегда трактуются как нуль, однако при использовании логического значения ИСТИНА в качестве аргумента функции СУММ оно трактуется как единица.
Накопительная сумма
При необходимости можно вычислить общую сумму значений заданного диапазона, которая в этом случае называется накопительной. На рисунке приведен пример такого вычисления. В столбце В представлены суммы по месяцам, а в столбце С отображается накопительная сумма за все прошедшие месяцы.
В ячейку С2 введена следующая формула:
=СУММ(В$2:В2)
Обратите внимание, что данная формула использует смешанную ссылку. Первая ячейка диапазона, на которую ссылается формула, всегда располагается в строке 2. При копировании этой формулы вниз по столбцу данный диапазон изменится, но таким образом, что суммирование ячеек всегда будет начинаться со строки 2 и заканчиваться текущей строкой. Если скопировать эту формулу вниз по столбцу С, ячейка С8 отобразит следующую формулу:
=СУММ(В$2:В8)
Чтобы скрыть общие суммы для строк, данные в которые еще не введены, используйте функцию ЕСЛИ (например, как в формуле, введенной в ячейку С2 и скопированной вниз по столбцу).
=ЕСЛИ(ЕПУСТО(В2);“”;СУММ(В$2:В2))
На рисунке показано, как работает эта формула.
Суммирование заданного количества наибольших или наименьших значений
В отдельных случаях может возникнуть необходимость суммирования определенного количества максимальных значений заданного диапазона, например, десяти максимальных значений. Один из способов решения этой проблемы – сортировка диапазона ячеек в порядке убывания с последующим применением функции СУММ к первым n значениям в сортируемом диапазоне. Представленная далее формула массива выполняет эту задачу, не требуя предварительной сортировки значений.
{=СУММ(НАИБОЛЬШИЙ(Данные;{1;2;3;4;5;6;7;8;9;10}))}
Эта формула суммирует 10 наибольших значений в диапазоне Данные. Для того, чтобы просуммировать 10 наименьших значений, вместо функции НАИБОЛЬШИЙ просто используйте функцию НАИМЕНЬШИЙ.
{=СУММ(НАИМЕНЬШИЙ(Данные;{1;2;3;4;5;6;7;8;9;10}))}
Обе эти формулы используют константу массива, представляющую собой аргумент функций НАИБОЛЬШИЙ или НАИМЕНЬШИЙ. Если нужно обработать диапазон, намного больший, чем запрашиваемое количество наибольших значений, воспользуйтесь формулой, приведенной ниже (она возвращает сумму наибольших 30 значений диапазона Данные). При необходимости значение 30 может быть заменено на любое другое значение.
{=СУММ(НАИБОЛЬШИЙ(Данные;СТРОКА(ДВССЫЛ(“1:30”))))}