Суммирование в Excel

Приветствую любителей статистического анализа данных и табличного процессора Excel. В этой статье мы приступим к изучению возможностей Excel. Начнем, по Дарвину, с простого – c суммирования.

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

Четыре года Светику,

Он любит арифметику.

Светик радостную весть

Объявляет всем:

— Если к двум прибавить шесть,-

Это будет семь! –

Услыхав его слова,

Юра стал считать:

— Нет, к шести прибавить два —

Это будет пять! —

А.Барто.

А.Барто убедительно доказывает, что даже у детей есть интерес к сложению чисел. По мере взросления этот интерес увеличивается еще больше. Короче, без сложения – никуда. Поэтому первая функция, которую мы рассмотрим в Excel, будет суммирование.

Просматривая список функций в Excel 2010, я для себя открыл много нового – разработчики явно поработали над расширением функционала. Тем не менее, многое осталось из Excel 2003, поэтому несчастным пользователям старой версии все-таки будет, что почитать ниже.

«+» — плюс

«+» – оператор суммирования. Пояснять здесь нечего. Плюс — он и в Африке плюс. На клавиатуре даже есть горячая клавиша (в двух местах!) — обозначена крестиком. Чтобы сплюсовать числа в двух или более ячейках, нужно вначале нажать «=», выделить первую ячейку, нажать «+», выделить вторую ячейку и т.д., затем после выделения последней ячейки, которую нужно добавить, нажимаем «ввод» – самая большая кнопка на клавиатуре с иностранной надписью «Enter». Надеюсь, доступно объяснил. Данная функция является наследием микрокалькуляторов, когда нужно было набирать и плюсовать каждое значение в отдельности. Не думаю, что у кого-то могут возникнуть вопросы по использованию калькулятора.

На мониторе суммирование плюсиком выглядит примерно так:

Суммирование плюсикоа

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

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

СУММ (SUM)

СУММ – самая простая и часто используемая функция. В скобках указывается диапазон суммирования. Если в диапазоне суммирования оказывается ячейка с текстом, то она при расчете игнорируется и ошибку не выдает, что, как бы хорошо, так как иногда вместо числа нужно что-то написать, хотя бы «данные отсутствуют». Но я бы не рекомендовал злоупотреблять текстом среди числовых значений. Рано или поздно появится ошибка.

Диапазон суммирования можно прописать в скобках вручную, что делается редко, либо выделить мышкой, как делается в большинстве случаев. Если диапазон суммирования имеет разрывы, то нужные участки следует выделять, удерживая клавишу Ctrl.

Суммирование функцией СУММ

Так как функция суммирования используется очень часто, то в конструкторском бюро Excel разработали несколько способов осуществить ее запуск. Пользователь сам выбирает, какой способ ему милее. Стандартный вариант – через Мастер функций. Для вызова Мастера функций нужно стать в ячейку (то есть сделать ее активной), где будет прописана формула, затем нажать кнопку fx (слева от строки формул) и выбрать нужную функцию. На рисунке видно, где что нажимать:

Мастер функций

Кому такой способ не нравится, в панели инструментов на закладках «Главная» и «Формулы» предусмотрена специальная кнопочка — автосумма.

Кнопка автосуммы

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

Однако истинные мастера Excel выбирают горячие клавиши. Автосумму можно вызвать комбинацией клавиш Alt + = (Альт и равно). Комбинация достаточно практичная: левым большим пальцем нажимаем Alt, правым указательным, или любым другим, – «равно» (если, конечно, все пальцы на месте). Я лично горячими клавишами пользуюсь часто, и другим рекомендую – это здорово ускоряет работу, особенно при постоянном использовании. Суммирование – как раз часто используемая функция.

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

Автосумма

Стали в ячейку B21, нажали Alt + = (или кнопку на панели). Диапазон автоматически выделился – это видно по контуру моргающей рамочки. Формула суммирования появилась как в ячейке, так и в строке формул (вверху рисунка). Теперь осталось нажать ввод и все – сумма готова.

СУММЕСЛИ (SUMIF)

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

Суммесли в Мастере функций

Поле «Диапазон» – это диапазон данных, где будет осуществляться поиск нашего условия.

Поле «Критерий» – это то условие, которое будет проверяться в массиве данных, заданных в поле «Диапазон». Мы задали значение больше 10 . Прошу обратить внимание на правило написания подобного условия – в кавычках. Иначе Эксель не поймет, что от него требуется. Хотя, если написать такое условие в Мастере функций без кавычек, Эксель их сам добавит после нажатия ввода, можете проверить.

«Диапазон_суммирования» – диапазон, из которого отбираются значения для суммирования, соответствующие заданному критерию. В примере выше оба диапазона (первое и третье поле) совпадают, т.к. и наличие критерия, и суммируемые числа расположены в одном диапазоне. Поэтому данное поле можно не заполнять.

Поясню «на пальцах», как работает формула. Берется заданный критерий (второе поле) и просматриваются все ячейки, указанные в поле «Диапазон». Если в нем присутствуют значения, удовлетворяющие заданному критерию, Excel берет значения по соответствующим строкам из поля «Диапазон_суммирования» и все складывает.

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

Не раз еще повторю, что я лично предпочитаю пользоваться клавиатурой, поэтому многие формулы прописываю вручную. Получается гораздо быстрее, чем вызывать Мастер, тыкать в эти поля, мышкой гонять по всему монитору… А так, стал на ячейку, написал =суммесли (регистр не имеет значения), открыл скобку, выделил диапазон, поставил «;», написал или выделил критерий, снова поставил «;», выделил диапазон для суммирования, закрыл скобку, нажал «Ввод». Все!

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

Набору формулы вручную

Вот так выглядит формула, если ее прописать вручную.

Функцию СУММЕСЛИ используют также для связки разных таблиц. К примеру, в одной таблице есть список уникальных товаров, а во второй таблице есть объемы продаж данного товара за разные периоды. Данные о продажах во второй таблице расположены в одном столбце, то есть одни и те же товары повторяются. Формула суммесли легко суммирует и подтягивает продажи к соответствующим товарам в первой таблице с уникальным списком. Это один из примеров, а их в практике встречаются сотни. Впервые с СУММЕСЛИ я столкнулся именно при решении задачи связки данных из разных таблиц.

Без знания и умелого использования функции СУММЕСЛИ стать хорошим аналитиком и мастером Эксель невозможно. Изучить в обязательном порядке. Лучший способ изучения – практика.

Это были основные функции суммирования, которые используются в Excel. Есть и другие, но они требуются реже. В большинстве случаев формул СУММ и СУММЕСЛИ вполне достаточно.

Предлагаю посмотреть трюк Excel, как ловко применять автосуммирование.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •