Дата публикации: 14.05.2026

Формула СУММЕСЛИМН для расчёта остатков по товару + складу

Хочу себе такие же кнопки
2a75c51f

Что вы получите от этого урока

Вы научитесь быстро и точно рассчитывать остатки товаров на разных складах с помощью формулы СУММЕСЛИМН в Excel/Google Sheets. Поймёте, как собрать данные о приходах и расходах, как построить единую таблицу‑источник и как адаптировать формулу под любые бизнес‑ситуации (много складов, несколько номенклатур, разные типы операций). После урока сможете построить автоматический отчёт, который будет обновляться в реальном времени, экономя часы ручного подсчёта.

1. Почему именно СУММЕСЛИМН?

  • Мульти‑условие: позволяет суммировать только те строки, которые удовлетворяют сразу нескольким критериям (например, товар = «А», склад = «Москва»).
  • Гибкость: работает как с числами, так и с датами, текстом, логическими выражениями.
  • Производительность: в больших таблицах она быстрее, чем вложенные СУММЕСЛИ + СУММ.

Аналогия: представьте, что у вас есть огромный ящик с деталями, а вам нужны только детали определённого цвета и размера. СУММЕСЛИМН — это как фильтр, который сразу отбирает нужные детали и считает их количество.

2. Структура данных, с которой будем работать

Дата Товар Склад Тип операции Кол‑во
2024‑01‑01 А Москва Приход 120
2024‑01‑03 Б Питер Расход 30
2024‑01‑05 А Москва Расход 45
2024‑01‑07 А Питер Приход 80
… … … … …
  • Дата – когда произошла операция.
  • Товар – артикул/название.
  • Склад – географическое место хранения.
  • Тип операции – «Приход» (поступление) или «Расход» (отгрузка).
  • Кол‑во – количество единиц в операции.

Важно: в колонке Тип операции будем использовать два фиксированных текста: «Приход» и «Расход». Это упростит построение условий.

3. Принцип расчёта остатка

Остаток = Сумма всех приходов – Сумма всех расходов

Для конкретного товара Т и склада С:

Остаток(T, S) = Σ(Кол‑во, где Товар = T И Склад = S И Тип операции = "Приход")
                – Σ(Кол‑во, где Товар = T И Склад = S И Тип операции = "Расход")

Эту формулу можно записать в одной ячейке, используя СУММЕСЛИМН дважды (один раз для приходов, один раз для расходов) или объединить в одну функцию с массивом‑условием.

4. Синтаксис СУММЕСЛИМН

СУММЕСЛИМН(диапазон_сумм; диапазон_критерия1; критерий1; [диапазон_критерия2; критерий2]; …)
  • диапазон_сумм – столбец, из которого берутся числа (в нашем случае Кол‑во).
  • диапазон_критерияX – столбец, к которому применяется условие (Товар, Склад, Тип операции).
  • критерийX – условие в виде текста, числа, ссылки на ячейку или логического выражения.

Пример: СУММЕСЛИМН(D2:D100; B2:B100; "А"; C2:C100; "Москва"; E2:E100; "Приход")
Здесь D – Кол‑во, B – Товар, C – Склад, E – Тип операции.

5. Построение динамического отчёта

5.1. Таблица‑параметры

Товар Склад
А Москва
А Питер
Б Москва
Б Питер

Эти два столбца можно разместить в отдельном листе Отчёт (например, ячейки A2:B5).

5.2. Формула остатка

В ячейке C2 (рядок для первого сочетания) вводим:

=СУММЕСЛИМН(Данные!$D$2:$D$1000;
            Данные!$B$2:$B$1000; A2;
            Данные!$C$2:$C$1000; B2;
            Данные!$E$2:$E$1000; "Приход")
 -
 СУММЕСЛИМН(Данные!$D$2:$D$1000;
            Данные!$B$2:$B$1000; A2;
            Данные!$C$2:$C$1000; B2;
            Данные!$E$2:$E$1000; "Расход")
  • $ фиксирует диапазоны, чтобы при копировании формулы они оставались одинаковыми.
  • A2 и B2 – ссылки на текущий товар и склад в таблице‑параметров.

5.3. Упрощённый вариант с массивом‑условием

В новых версиях Excel (Office 365) и Google Sheets можно написать одну функцию:

=СУММЕСЛИМН(Данные!$D$2:$D$1000;
            Данные!$B$2:$B$1000; A2;
            Данные!$C$2:$C$1000; B2;
            Данные!$E$2:$E$1000; {"Приход";"Расход"})

Эта формула вернёт массив из двух чисел: [СуммаПриходов; СуммаРасходов]. Чтобы получить остаток, просто вычитаем:

=ИНДЕКС(
    СУММЕСЛИМН(Данные!$D$2:$D$1000;
               Данные!$B$2:$B$1000; A2;
               Данные!$C$2:$C$1000; B2;
               Данные!$E$2:$E$1000; {"Приход";"Расход"});
    1) - ИНДЕКС(
    СУММЕСЛИМН(Данные!$D$2:$D$1000;
               Данные!$B$2:$B$1000; A2;
               Данные!$C$2:$C$1000; B2;
               Данные!$E$2:$E$1000; {"Приход";"Расход"});
    2)

Но для большинства пользователей более читаемым остаётся первый вариант с двумя отдельными вызовами.

5.4. Копирование формулы

  • Выделите ячейку C2.
  • Перетяните маркер заполнения вниз до C5.
  • Excel автоматически подстроит ссылки A3, B3 и т.д., оставляя диапазоны фиксированными.

Получите готовый отчёт остатка по каждому сочетанию «Товар‑Склад».

6. Расширенные сценарии

Сценарий Как адаптировать формулу
Фильтрация по дате (например, остаток на 31 март) Добавьте ещё один диапазон‑критерий: Данные!$A$2:$A$1000; "<=31.03.2024"
Только активные товары (по списку в отдельном листе) Вместо фиксированного текста используйте ссылку: Товары!$A$2:$A$50 и функцию СЧЁТЕСЛИ для проверки наличия.
Разные типы операций (например, «Возврат», «Перемещение») Добавьте ещё один критерий: Данные!$E$2:$E$1000; {"Приход";"Возврат"} и суммируйте их.
Вычисление средней цены (если в таблице есть столбец «Цена») Вместо СУММЕСЛИМН используйте СРЗНАЧЕСЛИМН с теми же условиями.

7. Частые ошибки и как их избежать

Ошибка Как проявляется Как исправить
Неправильные ссылки на диапазоны Формула возвращает #ЗНАЧ! или неправильные суммы. Убедитесь, что все диапазоны одинаковой длины и начинаются/заканчиваются в одинаковых строках.
Текстовые пробелы в колонке Тип операции Условие "Приход" не совпадает с "Приход " (с пробелом). Примените ОБРЕЗАТЬ к колонке или используйте СУММЕСЛИМН(...; "*Приход*").
Регистронезависимость "приход" vs "Приход" В Excel условия регистронезависимы, но в Google Sheets — нет. Используйте СИМВОЛ или НижнийРегистр.
Слишком большие диапазоны Замедление работы. Ограничьте диапазон реальными данными, например $D$2:$D$5000 вместо $D:$D.

8. Как проверять правильность расчётов

  1. Сводная таблица – построите её по тем же полям (Товар, Склад, Тип операции) и сравните итоги.
  2. Контрольные суммы – суммируйте все приходы и все расходы отдельно, затем вычислите общий остаток. Должно совпадать с суммой всех ячеек в колонке «Остаток».
  3. Тестовый набор данных – создайте небольшую таблицу (5‑10 строк) с известными результатами и проверьте формулу вручную.

9. Лучшие практики оформления

  • Именуйте листы: Данные, Отчёт, Товары.
  • Используйте таблицы Excel (Ctrl + T) – тогда диапазоны автоматически расширяются при добавлении новых строк.
  • Оформляйте заголовки жирным шрифтом и фиксируйте их (View → Freeze).
  • Добавляйте комментарии к ячейкам с формулами, чтобы коллеги быстро понимали логику.

Практика для закрепления

  1. Базовый расчёт

    • Вставьте в новую книгу данные из таблицы в пункте 2 (10‑15 строк).
    • Создайте лист Отчёт с перечнем товаров и складов (по 3‑4 комбинации).
    • Напишите формулу остатка, используя два вызова СУММЕСЛИМН.
    • Сравните результаты с вручную подсчитанными значениями.

  2. Фильтрация по дате

    • Добавьте в таблицу колонку Дата (разные даты в 2024 году).
    • Поставьте задачу: посчитать остаток на 30 января 2024 для товара «А» на складе «Москва».
    • Как изменится формула? Добавьте условие по дате.

  3. Учет возвратов

    • В колонку Тип операции добавьте новые значения «Возврат» (поступление) и «Перемещение» (отгрузка).
    • Требуется считать остаток, учитывая, что Возврат считается как приход, а Перемещение — как расход.
    • Какой набор критериев нужно указать в СУММЕСЛИМН?

  4. Динамический список товаров

    • На листе Товары разместите список актуальных артикулов (5‑6 штук).
    • На листе Отчёт используйте проверку «допустимое значение» (Data Validation), чтобы пользователь мог выбирать только из этого списка.
    • Проверьте, что при выборе товара, которого нет в списке, формула возвращает 0.

  5. Оптимизация диапазонов

    • Преобразуйте обычный диапазон в Таблицу Excel (назовите её Транзакции).
    • Перепишите формулу, используя структурированные ссылки (Транзакции[Кол‑во], Транзакции[Товар] и т.д.).
    • Сравните, как изменилось время расчёта при добавлении 10 000 новых строк.

Поздравляем! Вы теперь владеете мощным инструментом для автоматизации расчётов остатков на складах. Применяйте полученные навыки в реальных проектах, а при необходимости адаптируйте формулу под любые дополнительные условия – от фильтрации по датам до учёта специальных типов операций. Удачной работы с данными!


Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения