Формула «остаток = приход − расход» в Excel

Формулу «остаток = приход − расход» в Excel обычно записывают в каждую строку и протягивают вниз. Пока товара мало, это работает. Но когда список растёт, добавляются строки, таблицу начинают сортировать, протянутые формулы рвутся, и остаток вдруг меняется «сам по себе». Есть более надёжный путь: движения записываются в один длинный журнал, а остаток считает формула на отдельном листе.

Два листа: журнал и остатки

Первый лист назовите «Движение». Каждый приход и расход идёт отдельной строкой, столбцы такие:

• A: дата;
• B: код товара (например, LMP-09);
• C: тип (только «приход» или «расход»);
• D: количество (всегда положительное число);
• E: примечание (продажа, бой, возврат, подарок).

Второй лист «Остаток». Здесь каждый товар стоит один раз: в A код, в B название, в C рассчитанный остаток, в D минимальный остаток. В начале работы впишите фактически пересчитанное количество каждого товара первой строкой журнала: тип «приход», примечание «начальный».

Плюс такого устройства в том, что формула остаётся в одном месте, даже если в журнал добавлена тысяча строк. Удаление строки или сортировка таблицы не ломают остаток, а всю историю одного товара вы видите через фильтр.

Основная формула

В ячейку C2 листа «Остаток» запишите:

=SUMIFS(Движение!D:D; Движение!B:B; A2; Движение!C:C; "приход") - SUMIFS(Движение!D:D; Движение!B:B; A2; Движение!C:C; "расход")

Формула складывает из журнала все строки «приход» с этим кодом и вычитает все строки «расход». Протяните её вниз, и всё: по мере добавления записей остаток обновляется сам. В русскоязычном Excel функция называется СУММЕСЛИМН, а аргументы разделяются точкой с запятой; в других настройках вместо неё нужна обычная запятая.

Пример: магазин электротоваров, светодиодная лампа 9 Вт, код LMP-09.

• 2 марта, начальный пересчёт: приход 120;
• 5 марта, от поставщика: приход 200;
• 5 марта, продажи за день: расход 35;
• 6 марта, продажи за день: расход 48;
• 7 марта, бой: расход 2.

Расчёт: 120 + 200 − 35 − 48 − 2 = 235 штук. Ровно это число и выдаст формула.

Столбцы контроля

Добавьте на лист «Остаток» ещё три столбца:

• E, «Статус»: =IF(C2<=D2; "Заказать"; ""). Если остаток не больше минимального, появится «Заказать». Закрасьте ячейку красным через условное форматирование, и хватит одного взгляда;
• F, «Пересчитано»: число, которое вы реально насчитали на полке, вводится вручную;
• G, «Разница»: =F2-C2. Строки с ненулевым значением показывают недостачу или излишек.

После еженедельного пересчёта отфильтруйте столбец G: останутся только товары с разницей, с них и начинайте искать причину.

Например, по LMP-09 на полке насчитали 231 штуку, а формула показывает 235. Разница −4: возможно, две продажи не записаны, а две лампы недовезли при поставке. Ищите причину сразу, а когда найдёте, добавьте в журнал строку-исправление с примечанием. Старые строки не удаляйте: исправление тоже часть истории.

Новый товар и резервная копия

Когда появляется новый товар, добавьте его в конец листа «Остаток»: впишите код, название и минимальный остаток, протяните до этой строки формулы из столбцов C, E и G, а в журнал внесите начальную строку. Раз в неделю копируйте файл журнала с датой в названии (например, остаток-07-марта) и сохраняйте копию в облаке или в личном чате Telegram. Один испорченный файл может лишить вас учёта за весь месяц.

Что чаще всего мешает в Excel

1. Одинаковое написание кодов. «LMP-09» и «LMP-09 » (с пробелом на конце) для Excel два разных товара.
2. Дробные числа. Если кабель ведётся в метрах, используйте запятую (12,5). Число с точкой «12.5» может превратиться в дату или текст, и формула его не сложит.
3. Тип выбирается из списка. Через «Данные → Проверка данных → Список» оставьте в столбце C только варианты «приход, расход». Строка с опечаткой «расхд» просто не попадёт в расчёт.
4. Одна единица измерения. Лампа в штуках, кабель в метрах: решите это один раз и не пишите в журнале иначе.
5. Не используйте объединённые ячейки, они ломают формулы.

Вывод

Журнал, лист остатков и одна формула: для небольшого магазина этого достаточно, переходить на программу не обязательно. Важно лишь, чтобы начальная цифра была настоящей. Если на пересчёт не хватает времени, команда Sanab пересчитает товар и передаст результат в таблице Excel: её можно прямо вставить в журнал строками «начальный».