Ewidencja magazynowa w Excelu: przyjęcia, wydania i stany

Mały sklep, warsztat czy biuro często wiedzą o magazynie tyle, ile pamięta jedna osoba. Kończy się papier, brakuje kluczowej części, a w pudle leży trzydzieści sztuk czegoś, czego nikt nie używa. Nie trzeba od razu kupować systemu magazynowego. Dobrze zaprojektowany arkusz wystarczy, jeśli trzymasz się kilku zasad.

Poniżej opisuję strukturę, formuły i typowe pułapki ewidencji magazynowej w Excelu.

Trzy tabele zamiast jednej

Najczęstszy błąd to jedna wielka tabela, w której stan zmienia się przez ręczne poprawianie liczb. Zamiast tego rozdziel dane:

1. Asortyment: kod, nazwa, grupa, jednostka, cena, stan minimalny.
2. Przyjęcia: data, kod, ilość, ewentualnie dostawca.
3. Wydania: data, kod, ilość, odbiorca lub cel.

Stan nie jest wpisywany, tylko liczony z przyjęć i wydań. Dzięki temu masz pełną historię i możesz sprawdzić, skąd wziął się dany wynik.

Kod towaru jako klucz

Każda pozycja musi mieć unikalny kod, np. SR-001. W przyjęciach i wydaniach kod wybierasz z listy rozwijanej, a nazwę podstawiasz formułą:

=WYSZUKAJ.PIONOWO(B2;Asortyment!$A:$B;2;FAŁSZ)

Nigdy nie identyfikuj towaru po nazwie. „Śruba M6” i „Śruba m6 ” to dla Excela dwa różne towary.

Formuły stanu

Stan to suma przyjęć minus suma wydań dla danego kodu:

=SUMA.JEŻELI(Przyjęcia!$B:$B;A2;Przyjęcia!$C:$C)-SUMA.JEŻELI(Wydania!$B:$B;A2;Wydania!$C:$C)

Wartość stanu to stan razy cena:

=E2*D2

Sygnał zamówienia:

=JEŻELI(E2<=F2;"Zamówić";"OK")

Przykład

Śruby M6: cena 0,80 zł, stan minimalny 50 sztuk.

Stan: 300 − 260 = 40 sztuk. To mniej niż minimum 50, więc pojawia się status „Zamówić”. Wartość stanu wynosi 40 × 0,80 zł = 32 zł.

Kontrola wydania ponad stan

Najgroźniejsza pomyłka to wydanie większe niż to, co jest w magazynie. Wynika zwykle z literówki w ilości lub wydania przed zapisaniem przyjęcia. Dodaj formatowanie warunkowe, które podświetla wiersz, gdy wydanie przekracza dostępny stan. Wtedy błąd widać od razu, a nie przy inwentaryzacji.

Jak ustalić stan minimalny

Policz średnie dzienne zużycie, pomnóż przez czas dostawy w dniach i dodaj rezerwę. Jeśli dziennie schodzi 5 sztuk, dostawa trwa 7 dni, a rezerwa to 15 sztuk, minimum wynosi 5 × 7 + 15 = 50 sztuk.

Typowe błędy

• Poprawianie stanu ręcznie zamiast dopisywania przyjęcia lub wydania.
• Brak unikalnych kodów.
• Wpisywanie ilości z jednostkami („5 szt.”), przez co formuły nie działają.
• Mieszanie jednostek: sztuki, kilogramy i opakowania w jednej kolumnie bez wyraźnego opisu.
• Brak okresowej inwentaryzacji. Nawet najlepszy arkusz warto sprawdzić z fizycznym stanem raz na kilka miesięcy.

Nawyki, które utrzymują porządek

Wpisuj operacje od razu, a nie raz w tygodniu z pamięci. Jedna osoba odpowiada za przyjęcia, druga może zatwierdzać wydania, jeśli to możliwe. Raz w miesiącu przefiltruj towary ze statusem „Zamówić” i złóż zamówienie za jednym razem, zamiast jeździć po pojedyncze sztuki. Pozycje, które nie miały ruchu od pół roku, są kandydatami do wyprzedaży lub likwidacji.

Gotowy szablon ewidencji magazynowej

Jeśli nie chcesz budować wszystkiego od podstaw, możesz użyć szablonu. Jest płatny, a cenę zobaczysz na stronie produktu. Arkusz „Parametry” zawiera grupy towarów, jednostki miary i dane organizacji.

W arkuszu „Asortyment” prowadzisz do 300 towarów z minimalnym stanem, ceną, stanem, wartością i statusem. „Przyjęcia” i „Wydania” mają po 1000 wierszy. Kod wybierasz z listy, nazwa podstawia się sama, a wydanie ponad stan jest podświetlane na czerwono z uwagą, w asortymencie pojawia się wtedy status „Brak w magazynie”. Likwidację towaru wpisujesz jako wiersz wydania.

„Podsumowanie” pokazuje wartość stanów według grup, liczbę pozycji do zamówienia, obroty według miesięcy i wykres. W zestawie jest pusty arkusz i wypełniony przykład z fikcyjnymi danymi. Plik działa w Excelu 2007 i nowszym oraz w LibreOffice, bez makr. Obliczenia mają charakter orientacyjny.

FAQ

Czy Excel wystarczy do prowadzenia magazynu?

Dla małego asortymentu i niewielkiej liczby operacji dziennie tak. Gdy pracuje kilka osób jednocześnie albo potrzebujesz kodów kreskowych, lepszy będzie wyspecjalizowany program.

Jak często robić inwentaryzację?

Przynajmniej raz w roku, a przy droższym towarze częściej. Różnice między arkuszem a stanem fizycznym wyjaśnij, zanim dopiszesz korektę.

Czy ewidencja w Excelu jest dokumentem księgowym?

Nie. To narzędzie do bieżącej kontroli stanów. Wymogi dotyczące dokumentacji sprawdź w obowiązujących przepisach.

Co zrobić z towarem uszkodzonym?

Wpisz go jako wydanie z odpowiednim celem, na przykład „likwidacja”. Stan się zmniejszy, a historia zostanie w tabeli.