Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Хочу себе такие же кнопки
Что вы узнаете и зачем это нужно
СУММЕСЛИ (англ. SUMIFS) — это мощный инструмент Excel, позволяющий быстро подсчитывать остатки по каждому товару, даже если данных несколько тысяч строк. Вы научитесь:
- формировать запросы, которые «выбирают» только нужные строки;
- комбинировать несколько условий (дата, склад, тип операции);
- проверять правильность расчётов с помощью простых проверок.
Эти навыки позволяют в реальном времени видеть, сколько единиц конкретного продукта находится на складе, планировать закупки и избегать «переполнения» или «недостачи».
1. Основные понятия
| Термин | Описание | Пример |
|---|---|---|
| СУММЕСЛИ | Функция, суммирующая значения, удовлетворяющие одному или нескольким условиям. | =СУММЕСЛИ(A2:A100; "Товар 1"; B2:B100) |
| Диапазон_критерия | Столбец, в котором проверяется условие. | A2:A100 — названия товаров |
| Критерий | Текст, число или ссылка, определяющие, какие строки брать. | "Товар 1" |
| Суммируемый диапазон | Столбец, значения которого будут складываться. | B2:B100 — количества |
| СУММЕСЛИМН | Расширенная версия, принимающая множество пар «диапазон‑критерий». | =СУММЕСЛИМН(C2:C100; A2:A100; "Товар 1"; D2:D100; "Поступление") |
Аналогия: представьте, что у вас есть огромный склад с ящиками (строками). СУММЕСЛИ — это как робот‑сортировщик, который проходит по каждому ящику, открывает его и, если на этиике написано нужное слово, кладёт содержимое в общую коробку (сумму).
2. Структура данных для расчёта остатков
| Столбец | Содержание | Примечание |
|---|---|---|
| A | Товар | Наименование (например, «Молоко 1 л») |
| B | Дата | Дата операции (формат ДД.ММ.ГГГГ) |
| C | Тип операции | «Поступление», «Продажа», «Возврат», «Списание» |
| D | Кол-во | Положительные числа — поступления, отрицательные — отгрузки |
| E | Склад | Идентификатор склада (например, «Склад A») |
Важно: Чтобы формула работала корректно, тип операции может быть уже учтён в столбце D (положительные/отрицательные), либо вы будете отфильтровывать её отдельным условием.
3. Простейший случай: один критерий
Если нужно узнать остаток только по одному товару, без учёта дат и складов:
=СУММЕСЛИ(A2:A1000; "Молоко 1 л"; D2:D1000)- A2:A1000 — диапазон, где ищем название товара;
- "Молоко 1 л" — критерий;
- D2:D1000 — диапазон, где суммируем количества.
Результат — общий остаток на всех складах за весь период.
4. Добавляем второй критерий: тип операции
Обычно в D уже хранится знак (плюс/минус), но если вы хотите учитывать только «Поступление», используйте СУММЕСЛИМН:
=СУММЕСЛИМН(D2:D1000; A2:A1000; "Молоко 1 л"; C2:C1000; "Поступление")Здесь:
- D2:D1000 — суммируемый диапазон;
- A2:A1000 — критерий №1 (товар);
- C2:C1000 — критерий №2 (тип операции).
Чтобы получить чистый остаток (поступления – отгрузки), достаточно вычесть из суммы поступлений сумму отгрузок:
=СУММЕСЛИМН(D2:D1000; A2:A1000; "Молоко 1 л"; C2:C1000; "Поступление")
- СУММЕСЛИМН(D2:D1000; A2:A1000; "Молоко 1 л"; C2:C1000; "Продажа")5. Третий критерий: дата (период)
Для анализа остатков за конкретный месяц или квартал добавляем условие по дате:
=СУММЕСЛИМН(D2:D1000;
A2:A1000; "Молоко 1 л";
C2:C1000; "Поступление";
B2:B1000; ">=01.03.2024";
B2:B1000; "<=31.03.2024")- Даты сравниваются как текстовые строки в формате
ДД.ММ.ГГГГтолько если ячейки действительно отформатированы как даты. - Для гибкости можно вынести даты в отдельные ячейки (например,
G1— начало,H1— конец) и использовать ссылки:
=СУММЕСЛИМН(D2:D1000; A2:A1000; "Молоко 1 л"; C2:C1000; "Поступление"; B2:B1000; ">="&G1; B2:B1000; "<="&H1)6. Четвёртый критерий: склад
Если у вас несколько складов, добавьте их в список условий:
=СУММЕСЛИМН(D2:D1000;
A2:A1000; "Молоко 1 л";
C2:C1000; "Поступление";
B2:B1000; ">=01.03.2024";
B2:B1000; "<=31.03.2024";
E2:E1000; "Склад A")Таким образом, вы получаете остаток только на Складе A за март 2024.
7. Как построить «универсальную» формулу для любого товара
Чтобы не писать отдельную формулу для каждого на, используйте ссылки на ячейки, где пользователь вводит параметры:
| Ячейка | Описание |
|---|---|
| G2 | Товар (например, =Молоко 1 л) |
| H2 | Склад (например, Склад A) |
| I2 | Начальная дата (01.03.2024) |
| J2 | Конечная дата (31.03.2024) |
Формула:
=СУММЕСЛИМН(D2:D1000;
A2:A1000; G2;
C2:C1000; "Поступление";
B2:B1000; ">="&I2;
B2:B1000; "<="&J2;
E2:E1000; H2)
-
СУММЕСЛИМН(D2:D1000;
A2:A1000; G2;
C2:C1000; "Продажа";
B2:B1000; ">="&I2;
B2:B1000; "<="&J2;
E2:E1000; H2)Теперь достаточно изменить значения в G2:J2, и остаток пересчитается автоматически.
8. Проверка корректности расчётов
- Сводная таблица – быстро построить и сравнить с результатами формулы.
- Контрольные суммы – добавить столбец «Итого», где
=СУММ(D2:D1000). Сумма всех «Поступление» – «Продажа» должна совпадать с остатком, полученным черезSUMIFS. - Фильтр – отфильтровать по выбранному товару и складу, вручную просуммировать столбец D.
Если результаты различаются, проверьте:
- правильный формат дат;
- отсутствие скрытых пробелов в названиях товаров;
- согласованность знаков (+/‑) в столбце D.
9. Часто задаваемые вопросы
| Вопрос | Ответ |
|---|---|
Можно ли использовать СУММЕСЛИ вместо СУММЕСЛИМН? |
Да, если нужен один критерий. При более двух условиях SUMIFS (англ.) или СУММЕСЛИМН обязательны. |
| Что делать, если в столбце «Тип операции» записано не «Поступление», а «+»? | Замените критерий на "+" или используйте отдельный столбец с признаками (1 — поступление, -1 — отгрузка) и умножайте на него. |
| Можно ли суммировать только положительные числа? | Да, добавьте условие ">0" к диапазону D: =СУММЕСЛИМН(D2:D1000; A2:A1000; G2; D2:D1000; ">0"). |
| Как ускорить расчёт при 100 000 строк? | Применяйте таблицу Excel (Ctrl+T) – диапазоны автоматически расширяются, а формулы работают быстрее. Также можно использовать Power Query для предварительной агрегации. |
10. Пример полного расчёта в реальном листе
A B C D E
1 Товар Дата Тип Кол-во Склад
2 Молоко 1л 01.03.2024 Поступление 100 Склад A
3 Молоко 1л 02.03.2024 Продажа -30 Склад A
4 Молоко 1л 05.03.2024 Поступление 50 Склад B
5 Хлеб 500г 01.03.2024 Поступление 200 Склад A
...Параметры в ячейках G2:J2:
G2 = Молоко 1л
H2 = Склад A
I2 = 01.03.2024
J2 = 31.03.2024Формула в K2 (остаток):
=СУММЕСЛИМН(D2:D1000; A2:A1000; G2; C2:C1000; "Поступление"; B2:B1000; ">="&I2; B2:B1000; "<="&J2; E2:E1000; H2)
-
СУММЕСЛИМН(D2:D1000; A2:A1000; G2; C2:C1000; "Продажа"; B2:B1000; ">="&I2; B2:B1000; "<="&J2; E2:E1000; H2)Результат: 70 — остаток «Молоко 1 л» на «Складе A» за март 2024.
Практика для закрепления
-
Упражнение 1
В таблице есть столбцы Товар, Дата, Тип, Кол‑во, Склад. Составьте формулу, которая выдаёт остаток по товару «Хлеб 500 г» на складе «Склад B» за период с 01.04.2024 по 30.04.2024. -
Упражнение 2
Добавьте в таблицу столбец Состояние (значения «Новый», «Брак», «Продан»). С помощьюСУММЕСЛИМНпосчитайте, сколько единиц «Товар X» в состоянии «Брак» было получено в течение текущего года. -
Упражнение 3
С помощью одной формулы найдите наибольший остаток среди всех товаров на складе «Склад C» за весь период. (Подсказка: используйтеМАКСв сочетании сСУММЕСЛИМНв массивной формуле.) -
Упражнение 4
В ячейкахM2иN2введите даты начала и конца периода. Составьте формулу, которая будет автоматически менять диапазон дат, используя ссылки наM2иN2. Проверьте её работу, изменяя даты. -
Упражнение 5
Сравните результат, полученный черезСУММЕСЛИМН, с результатом, полученным через Сводную таблицу. Оцените, в каких случаях удобнее использовать каждый из методов.
Поздравляем! Вы теперь уверенно работаете с СУММЕСЛИМН и можете быстро получать точные остатки по любому товару, складу и периоду. Применяйте полученные навыки в своих бизнес‑процессах, и ваш учет запасов станет прозрачным и надёжным.
Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения
