Облік товарів в Excel
Автоматично розраховує залишок за рухом надходжень і видатків, попередження про критичний рівень і загальну вартість запасів.
Як вести облік запасів в Excel?
Облік запасів в Excel ґрунтується на тому, щоб відкрити по одному рядку на кожен товар, записувати кількості надходжень і видач, розраховувати залишок за формулою =Надійшло−Видано та підсвічувати умовним форматуванням товари, що опустилися нижче критичного рівня. Правильно налаштована таблиця до кількох сотень товарів виконує роботу, близьку до платних програм.
Які стовпці справді потрібні?
Зайві стовпці роблять таблицю нездійсненною для заповнення. У таблиці обліку залишків мають бути щонайменше такі поля: код товару, назва товару, одиниця виміру, надійшло, вибуло, залишок, критичний рівень і закупівельна ціна. Стелаж/місце зберігання та дата останнього руху допомагають швидше знаходити товар і помічати помилки.
Код товару — критично важливий: він дає змогу зіставляти інвентаризацію, прайс-листи та таблиці замовлень. Без коду один і той самий товар розмножується під різними написаннями, наприклад «Папір A4» і «Папір A4 80г». Як побудувати код (префікс категорії + порядковий номер, код варіанта, формула автоматичного коду в Excel) читайте в посібнику як присвоїти код товару.
Основні формули
Для залишку: =E2-F2; щоб не бачити нулів у порожніх рядках, краще =IF(A2="";"";E2-F2). Для стовпця попередження достатньо конструкції =IF(G2<=0;"ВИЧЕРПАНО";IF(G2<=H2;"КРИТИЧНО";"ДОСТАТНЬО")). Вартість запасу рахується як =G2*J2, загальна вартість — як =SUM(L2:L300).
Щоб порахувати кількість критичних товарів, використовують =COUNTIF(I2:I300;"КРИТИЧНО"), а для суми по одній категорії — =SUMIF(C2:C300;"Побутова хімія";G2:G300). У локалізованому Excel ці функції мають свої місцеві назви — завантажений шаблон відкривається без проблем в обох варіантах.
Як налаштувати сповіщення про критичний рівень
Для кожного товару запишіть у стовпець критичного рівня відповідь на питання «скільки щонайменше має бути в наявності». Щоб визначити це число, перемножте середній денний продаж, термін поставки та запас міцності: денний продаж × термін поставки (дні) × 1,2 — практична відправна точка.
Потім через Головна → Умовне форматування додайте до стовпця статусу правило кольорів. У шаблонах, які можна завантажити, ці правила вже налаштовані.
Поширені помилки
Один файл відкривають усі: коли двоє вносять зміни одночасно, дані губляться. Для спільного редагування тримайте файл на OneDrive/Google Drive.
Формули закінчуються: додаючи новий рядок, не забудьте протягнути формулу вниз.
Відсутність резервних копій: наприкінці місяця зберігайте копію файлу з датою в назві.
Запис залишку замість руху: написати «залишок 40» і піти далі — означає унеможливити пошук помилки згодом; вносьте кожен рух окремим рядком.
Коли Excel уже не вистачає?
Таблиця починає вас гальмувати, коли з’являються такі ознаки: товарів стало понад кілька тисяч, у файл одночасно заходить кілька людей, побільшало філій/складів, потрібне швидке введення за штрихкодом або продажі й залишки ведуться в різних місцях.
У цей момент програма, яка тримає залишки, контрагентів і рахунки в одному місці, і заощаджує час, і знижує ризик помилок. Під час переходу ваш файл Excel не пропадає: більшість програм підтримують імпорт .xlsx.
Поширені запитання
Чи достатньо Excel для обліку залишків?
Якщо товарів до кількох сотень і працює одна людина — з великим запасом достатньо. Для кількох користувачів, кількох складів або там, де важлива швидкість роботи зі штрихкодами, його замало.
Google Sheets чи Excel?
Якщо працюватимуть одночасно кілька людей, зручніше Google Sheets. Якщо йдеться про складні формули й великі обсяги даних, Excel швидший.
З чого почати облік залишків?
Проведіть інвентаризацію й визначте початкові залишки, а потім регулярно вносьте кожне надходження і вибуття. Якщо початкові дані неправильні, таблиця ніколи не зійдеться.
Шаблони, що використовуються з цим посібником
Excel для інвентаризації складу
Порівнює кількість у системі з фактичним підрахунком і автоматично формує звіт про нестачу, надлишки та суму втрат.