Устали считать вручную? Автоматизируйте расчеты с СУММЕСЛИ и СУММЕСЛИМН в Excel
Работа с большими таблицами в Excel часто требует выборочного суммирования данных по определенным критериям. Вместо того чтобы вручную складывать нужные ячейки, можно воспользоваться мощными функциями СУММЕСЛИ и СУММЕСЛИМН. Разберемся, как они работают и когда какую использовать.
Сравнительная таблица функций
Чтобы выбрать оптимальный инструмент для вашей задачи, сравните ключевые характеристики трех основных функций суммирования с условиями.
| Характеристика | СУММЕСЛИ | СУММЕСЛИМН | СУММПРОИЗВ |
|---|---|---|---|
| Количество условий | 1 | До 127 | Неограниченно |
| Сложность синтаксиса | Простой | Средний | Сложный |
| Скорость работы | Быстрая | Быстрая | Медленная |
| Гибкость условий | Низкая | Средняя | Высокая |
| Логика ИЛИ | Нужно складывать формулы | Нужно складывать формулы | Встроена |
Для большинства повседневных задач оптимальным выбором станет СУММЕСЛИМН, ведь функция в себе сочетает высокую скорость работы и достаточную функциональность.
Кстати, упростить поиск и сопоставление данных в Excel можно с помощью формулы ВПР, а просмотр с помощью закрепления шабки таблицы.
Базовые способы суммирования в Excel
Прежде чем изучать сложные формулы, давайте вспомним, как сложить числа самыми простыми способами в Эксель.
- Автосумма — абсолютный фаворит
Выделяете любой столбец или строку с цифрами → жмёте Alt + = — и всё! Excel сам вставляет =СУММ() и сразу показывает результат. На русской раскладке срабатывает Alt + Shift + 0.
Где искать кнопку: вкладка «Главная», большая греческая буква Σ (сигма). Один клик — и готово.
- Строка состояния внизу экрана — для ленивых
Просто выделите нужные ячейки мышкой — и посмотрите в самый низ окна Excel. Там сразу появляется сумма, количество и среднее. Формул никаких, удобно когда нужно быстро прикинуть.
- Классическая СУММ
Если автосумма по какой-то причине не сработала, просто пишем:
=СУММ(A1:A100) — и весь столбец посчитан.
Можно даже складывать отдельные ячейки, для этого вводим =СУММ(A5; B12; C3).
Чаще всего подсчеты в Excel делают именно этими тремя способами. Если надо «посчитать только по Москве» или «только заказы дороже 50 тысяч» — вот тогда уже достаем тяжёлую артиллерию в виде СУММЕСЛИ и СУММЕСЛИМН.
Что такое СУММЕСЛИ и как ее использовать каждый день
СУММЕСЛИ — это та самая волшебная функция, которая берет огромную таблицу и складывает то, что нужно, а остальное игнорирует. По сути, это фильтр + сумма в одной формуле.
Пишется она так:
=СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования])
Первый диапазон — ячейки, в которых ищем совпадения с условием, критерием выступает условие отбора, например, число, дата, текст или выражение. Диапазон_суммирования — ячейки, которые надо просуммировать.
По-английски это SUMIF, но в русской версии Excel пишем именно СУММЕСЛИ.
Практические примеры
- Рассмотрим как рассчитать общую сумму продаж по г. Москва:
| Город | Сумма продаж |
|---|---|
| Москва | 45 000 |
| СПб | 32 000 |
| Москва | 28 000 |
| Казань | 19 000 |
| Москва | 51 000 |
Пишем в любую свободную ячейку:
=СУММЕСЛИ(A2:A6;»Москва»;B2:B6)
Жму Enter → сразу 124 000.
Всё. Никаких фильтров, никаких промежуточных столбцов. Excel сам прошёлся по столбцу A, нашел все строки с Москвой и сложил соответствующие цифры из столбца B.
- Продажи выше определенной суммы
Можно найти суммы продаж, которые превышают 30 000 рублей.
= СУММЕСЛИ (B2:B6;»>30000″)
В формуле не ставим третий аргумент, ведь суммировать будем диапазон, в котором ищем совпадения.
Что такое функция СУММЕСЛИМН
Функция СУММЕСЛИМН — это, по сути, старшая сестра обычной СУММЕСЛИ, но намного умнее. Она умеет суммировать числа сразу по нескольким условиям, и без неё в серьёзных отчетах уже никуда.
Как она пишется:
=СУММЕСЛИМН(диапазон_который_суммируем; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
На английском это SUMIFS.
Самая частая ошибка новичков (и не только) — путаница в порядке аргументов.
Запомните раз и навсегда:
- В СУММЕСЛИ сначала идёт диапазон условия, потом то, что суммируем;
- В SUMIFS всё наоборот: сначала то, что суммируем, а уже потом все условия.
Из-за этой разницы люди регулярно получают ноль или какую-то ерунду, хотя формула вроде бы правильная.
Короче, если нужно отобрать продажи, допустим, только по определенному менеджеру, региону или за март — без функции будете мучаться с вспомогательными столбцами или сводными таблицами. А с ней — одна строчка, и всё считается само.
Очень выручает, если данных много и условия сложные. Рекомендую подружиться.
Пример
Надо найти сколько продал Иванов в Москве.
| Город | Менеджер | Сумма |
|---|---|---|
| Москва | Иванов | 45 000 |
| СПб | Петрова | 32 000 |
| Москва | Петрова | 28 000 |
| Казань | Иванов | 19 000 |
| Москва | Иванов | 51 000 |
Алгоритм решения:
=СУММЕСЛИМН(C2:C6;A2:A6;»Москва»;B2:B6;»Иванов»)
Получаем 96 000 рублей. Т.е. функция проверяет одновременно два условия: ищем г. Москва и цифры только по Иванову.
СУММПРОИЗВ — универсальный инструмент с альтернативными функциями суммирования
Кто бы мог подумать, что функция СУММПРОИЗВ способна почти полностью вытеснить предыдущие функции, особенно если условия становятся действительно запутанными.
=СУММПРОИЗВ((A2:A10=»Москва»)*(B2:B10>50000)*C2:C10)
Эта штука делает ровно то же самое, что и привычная всем формула:
=СУММЕСЛИМН(C2:C10; A2:A10; «Москва»; B2:B10; «>50000»)
Но при этом даёт гораздо больше свободы.
А вообще, если вам постоянно нужно рассчитывать одно и то же по куче разных срезов (по регионам, менеджерам, месяцам и т.д.), то сразу делайте сводную таблицу.
Делается это за 10 секунд:
- Выделяете всю таблицу с шапкой.
- Вставка → Сводная таблица → ОК.
Перетаскиваете нужные поля в строки, столбцы, значения — и всё, Excel сам все посчитает.
В каких случаях точно стоит выбрать сводки:
- данные обновляются каждый день/неделю;
- хочется покрутить-повертеть цифры, профильтровать, свернуть-развернуть;
- нужно сразу и суммы, и количество, и средние чеки, и процент выполнения;
- планируете потом строить на этих данных дашборды.
Короче, для разовых подсчетов — СУММПРОИЗВ отличный вариант, а для регулярной аналитики — используйте сводные таблицы. Выбирайте то, что удобнее вам в конкретной ситуации.
В каких случаях использовать каждую функцию?
Давайте подытожим, какая функция для каких расчетов подходит:
| СУММЕСЛИ | СУММЕСЛИМН | СУММПРОИЗВ |
|---|---|---|
| Нужно применить только одно условие | Надо проверить два и больше условий | Условия сложны для SUMIFS |
| Требуется быстрый и простой расчет | Строите сложные аналитические отчеты | Нужна особая логика вычислений |
| Таблица небольшая | Работаете с многомерными данными | Требуется учет регистра |
| Нужна фильтрация по диапазону значений |
Полезные советы по работе с функциями
Теперь несколько советов, которые существенно облегчат работу с функциями:
- Как ссылаться на ячейки вместо жестких критериев
Гораздо удобнее не вбивать город или название прямо в формулу, а держать его в отдельной ячейке. Меняешь значение — всё сразу пересчитывается.
Например, в E1 у тебя сейчас «Москва»:
=СУММЕСЛИ(A2:A6;E1;B2:B6)
Поменял E1 на «Казань» — и сумма моментально встала по Казани.
- Подстановочные знаки, которые реально спасают
В критериях можно использовать символы:
? — заменяет один символ
***** — заменяет любое количество символов
~ — экранирует спецсимволы (для поиска самих знаков ? и *)
Примеры из жизни:
- =СУММЕСЛИ(B2:B6;»Ноут*»;C2:C6) — подхватит и «Ноутбук», и «Ноут Asus», и «Ноутбук-игровой»
- =СУММЕСЛИ(A2:A10;»Иван?в»;B2:B10) — найдёт и Иванов, и Иванев, и Иваныч (если вдруг такое есть)
- =СУММЕСЛИ(A2:A10;»*петербург»;B2:B10) — сработает и на «Санкт-Петербург», и на «Санктпетербург», и даже на «г. Санкт-Петербург»
- А если надо найти знак вопроса в тексте:
- =СУММЕСЛИ(A2:A10;»~?»;B2:B10)
- Использование функций в критериях
Иногда нужно что-то поумнее простого текста. Например, посчитать продажи только за текущий месяц:
=СУММЕСЛИМН(C:C; A:A; «>=»&ДАТАМЕС(СЕГОДНЯ();0); A:A; «<«&ДАТАМЕС(СЕГОДНЯ();1))
Первое условие — дата не раньше первого числа текущего месяца, второе — строго меньше первого числа следующего.
- Абсолютные ссылки — чтобы при протяжке ничего не поехало
Когда копируешь формулу вниз или вправо, диапазоны любят «убегать». Фиксируем их долларами:
=СУММЕСЛИ($A$2:$A$100; E2; $B$2:$B$100)
Теперь при протяжке вниз будут меняться только ячейки с критериями (E2 → E3 → E4…), а сами столбцы A и B останутся на месте.
- Как сделать «ИЛИ» из нескольких СУММЕСЛИ
СУММЕСЛИМН — это всегда «И» (все условия сразу). А если надо «Москва» ИЛИ «Санкт-Петербург»? Просто складываем два обычных СУММЕСЛИ:
=СУММЕСЛИ(A2:A10;»Москва»;B2:B10) + СУММЕСЛИ(A2:A10;»Санкт-Петербург»;B2:B10)
Работает идеально и понятнее, чем массивные формулы.
- Поиск с учётом регистра (когда «москва» и «Москва» — разные вещи)
Обычные СУММЕСЛИ и СУММЕСЛИМН регистр не видят вообще. Если вдруг критично — берём СУММПРОИЗВ и функцию СОВПАД:
=СУММПРОИЗВ((СОВПАД(A2:A10;»МОСКВА»))*(B2:B10))
Или более современный вариант через СУММЕСЛИ + ТОЧНОТЕКСТ, но это уже в зависимости от версии Excel.
- Используйте две формулы Эксель — СЧЕТЕСЛИ и СУММЕСЛИ вместе для полного анализа данных
Первая функция покажет количество записей по условию, а вторая посчитает их сумму, что дает полную картину ваших данных.
Заключение
Функции СУММЕСЛИ и СУММЕСЛИМН превращают Excel в мощный инструмент анализа данных. Освоив их, вы сможете за секунды получать нужные итоги из огромных таблиц, экономя часы ручной работы.
Главное запомнить:
- СУММЕСЛИ — когда условие одно. Например, «посчитай все продажи по Москве».
- СУММЕСЛИМН — когда условий несколько и первым идет диапазон суммирования.
Как только таблица перевалила за пару тысяч строк и условий становится больше трех-четырех — бросайте всё это и делайте сводную. Реально быстрее и надёжнее.
Начните с простых примеров и постепенно переходите к более сложным комбинациям условий — и ваша работа с данными выйдет на новый уровень эффективности.
