Magazyn w Excelu: szablon, formuły i kiedy przestaje wystarczać
Magazyn w Excelu krok po kroku: szablon kolumn, formuły SUMA.JEŻELI i X.WYSZUKAJ, przykład z liczbami oraz 7 sygnałów, że czas przejść na program magazynowy.
Magazyn w Excelu działa dobrze, gdy masz kilkadziesiąt do kilkuset produktów, jeden kanał sprzedaży i jedną osobę, która wpisuje ruchy towaru. Wystarczą dwie tabele: kartoteka produktów i dziennik ruchów, a stan liczy jedna formuła SUMA.JEŻELI. Arkusz przestaje wystarczać, gdy sprzedajesz w kilku kanałach naraz, plik edytuje kilka osób albo co tydzień poprawiasz stany po liczeniu półek.
Poniżej znajdziesz gotowy układ kolumn, formuły w polskiej wersji Excela (nazwy funkcji sprawdzone w dokumentacji Microsoft), przykład z liczbami, najczęstsze błędy i listę sygnałów, że czas przejść na system.
Zanim zaczniesz: czego potrzebujesz do magazynu w Excelu
Do prowadzenia magazynu w Excelu potrzebujesz programu Excel, listy produktów z unikalnymi kodami i stanów początkowych z policzenia półek. Poziom trudności jest podstawowy: wystarczy umieć wpisać formułę i utworzyć tabelę. Zbudowanie szablonu według tej instrukcji to szacunkowo jedno popołudnie, a najwięcej czasu zajmuje uporządkowanie listy produktów.
- Excel - w wersji Microsoft 365, 2021 lub 2024 zadziała funkcja X.WYSZUKAJ; w Excelu 2016 i 2019 użyj WYSZUKAJ.PIONOWO (podaję obie wersje formuły).
- Unikalny kod każdego produktu i wariantu - najlepiej kod sku, bo nazwa produktu zmienia się częściej, niż myślisz.
- Stan początkowy - policzony fizycznie w dniu startu arkusza, nie przepisany ze starego pliku.
- Jedna zasada wpisywania - każdy ruch towaru to nowy wiersz w dzienniku; nikt nie nadpisuje stanu ręcznie.
Szablon arkusza magazynowego: kolumny
Dobry szablon magazynu w Excelu ma dwie tabele. W tabeli Produkty jest jeden wiersz na SKU i wszystkie wyliczenia. W tabeli Ruchy jest jeden wiersz na każde przyjęcie, wydanie, zwrot i korektę. Stanu nigdy nie wpisujesz, tylko go liczysz z ruchów, dzięki czemu każda liczba ma swoje źródło.
Obie tabele utwórz przez Narzędzia główne > Formatuj jako tabelę i nadaj im nazwy Produkty i Ruchy. Tabela Excela sama się rozszerza, gdy zaczniesz pisać w wierszu bezpośrednio pod nią, więc formuły obejmą nowe wpisy bez poprawiania zakresów.
Tabela Produkty (kartoteka)
| Kolumna | Co wpisujesz | Wpisujesz czy liczy formuła |
|---|---|---|
| SKU | Unikalny kod produktu lub wariantu, np. KUB-CZ-01 | Wpisujesz |
| Nazwa | Nazwa produktu | Wpisujesz |
| Wariant | Kolor, rozmiar, pojemność | Wpisujesz |
| EAN | Kod kreskowy, jeśli produkt go ma | Wpisujesz |
| Lokalizacja | Regał i półka, np. R2-P3 | Wpisujesz |
| Stan początkowy | Ilość z liczenia w dniu startu | Wpisujesz raz |
| Stan minimalny | Próg, przy którym zamawiasz towar | Wpisujesz |
| Cena zakupu netto | Cena z ostatniej faktury zakupu | Wpisujesz |
| Stan | Aktualna ilość | Formuła |
| Wydano 30 dni | Sprzedaż z ostatnich 30 dni | Formuła |
| Dni zapasu | Na ile dni wystarczy towar | Formuła |
| Wartość | Stan razy cena zakupu | Formuła |
| Status | OK albo ZAMÓW | Formuła |
Kolumna Lokalizacja to zwykły tekst, ale nawet w małym magazynie oszczędza szukania. Jak nadać sensowne lokalizacje magazynowe regałom i półkom, opisujemy osobno.
Tabela Ruchy (dziennik)
| Kolumna | Co wpisujesz | Wpisujesz czy liczy formuła |
|---|---|---|
| Data | Dzień ruchu towaru | Wpisujesz |
| Dokument | Numer faktury zakupu, numer zamówienia, „SPIS” | Wpisujesz |
| Typ | Przyjęcie, Wydanie, Zwrot, Korekta+, Korekta- | Wybierasz z listy |
| SKU | Kod produktu | Wybierasz z listy |
| Nazwa | Podgląd nazwy, żeby wyłapać pomyłkę w SKU | Formuła |
| Ilość | Zawsze liczba dodatnia | Wpisujesz |
| Kanał | Sklep, Allegro, sprzedaż osobista | Wybierasz z listy |
| Zmiana | Ilość ze znakiem plus lub minus | Formuła |
| Kto | Inicjały osoby, która wpisała ruch | Wpisujesz |
Jak zrobić magazyn w Excelu krok po kroku
Magazyn w Excelu zbudujesz w siedmiu krokach: dwie tabele, listy wyboru, kilka formuł i formatowanie warunkowe. Formuły używają odwołań strukturalnych, np. Ruchy[SKU] to cała kolumna SKU w tabeli Ruchy, a [@SKU] to komórka SKU w tym samym wierszu. Separatorem argumentów w polskim Excelu jest średnik.
- Utwórz tabelę Produkty z kolumnami z pierwszej tabeli i wpisz stany początkowe z liczenia. Każdy wariant (rozmiar, kolor) to osobny wiersz z własnym SKU.
- Utwórz tabelę Ruchy z kolumnami z drugiej tabeli. Od tej chwili każda dostawa, każde wysłane zamówienie i każdy zwrot to nowy wiersz.
- Dodaj listy wyboru. Zaznacz kolumnę Typ, wybierz Dane > Sprawdzanie poprawności danych, w polu Zezwalaj ustaw Lista i wpisz źródło: Przyjęcie;Wydanie;Zwrot;Korekta+;Korekta-. Tak samo ustaw kolumnę SKU, jako źródło zaznaczając kolumnę SKU w arkuszu z produktami (np. =Produkty!$A$2:$A$2000). Na karcie Alert o błędzie wpisz komunikat, który pojawi się przy nieznanym kodzie.
- Wpisz formuły w tabeli Ruchy. Kolumna Zmiana zamienia wydania i korekty minus na liczbę ujemną:
=JEŻELI(LUB([@Typ]="Wydanie";[@Typ]="Korekta-");-[@Ilość];[@Ilość])
Kolumna Nazwa pokazuje nazwę produktu albo ostrzeżenie:
=X.WYSZUKAJ([@SKU];Produkty[SKU];Produkty[Nazwa];"BRAK SKU")
W Excelu 2016 i 2019: =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO([@SKU];Produkty;2;FAŁSZ);"BRAK SKU") (działa, gdy SKU jest pierwszą, a Nazwa drugą kolumną tabeli Produkty). - Wpisz formułę stanu w tabeli Produkty. Stan to stan początkowy plus suma wszystkich zmian dla danego SKU:
=[@[Stan początkowy]]+SUMA.JEŻELI(Ruchy[SKU];[@SKU];Ruchy[Zmiana]) - Dodaj kolumny do decyzji zakupowych.
Wydano 30 dni: =SUMA.WARUNKÓW(Ruchy[Ilość];Ruchy[SKU];[@SKU];Ruchy[Typ];"Wydanie";Ruchy[Data];">="&DZIŚ()-30)
Dni zapasu: =JEŻELI.BŁĄD([@Stan]/([@[Wydano 30 dni]]/30);"brak sprzedaży")
Wartość: =[@Stan]*[@[Cena zakupu netto]]
Status: =JEŻELI([@Stan]<=[@[Stan minimalny]];"ZAMÓW";"OK") - Włącz ostrzeżenia kolorem. Zaznacz dane tabeli Produkty, wybierz Narzędzia główne > Formatowanie warunkowe > Nowa reguła > Użyj formuły do określenia komórek, które należy sformatować i wpisz np. =$I2<=$G2 (kolumna I to Stan, G to Stan minimalny). Znak $ tylko przed literą kolumny sprawia, że reguła sprawdza każdy wiersz osobno. Drugą regułą =LICZ.JEŻELI($A:$A;$A2)>1 podświetlisz zdublowane SKU.
Funkcja SUMA.WARUNKÓW przyjmuje do 127 par zakres-kryterium, więc kolumnę Wydano 30 dni łatwo rozbudujesz, np. o warunek Ruchy[Kanał];"Allegro", żeby zobaczyć sprzedaż z jednego kanału. Jeśli chcesz podzielić produkty według udziału w sprzedaży, ta sama tabela jest punktem wyjścia, a analiza abc excel zajmuje wtedy kilka dodatkowych kolumn.
Przykład: jak liczy się stan w Excelu
Przykład pokazuje trzy produkty fikcyjnego sklepu z ceramiką po miesiącu prowadzenia arkusza (formuły liczone 31 października, więc okno 30 dni obejmuje cały październik). Kubek czarny dostał dostawę 60 sztuk, miał 18 sztuk wydanych, 1 zwrot i korektę minus 2 sztuki po liczeniu półki.
| SKU | Stan początkowy | Suma zmian | Stan | Wydano 30 dni | Dni zapasu | Stan minimalny | Status |
|---|---|---|---|---|---|---|---|
| KUB-CZ-01 | 40 | +41 | 81 | 18 | 135 | 15 | OK |
| KUB-BI-01 | 25 | -16 | 9 | 16 | 16,9 | 10 | ZAMÓW |
| POD-DR-02 | 12 | -9 | 3 | 9 | 10 | 5 | ZAMÓW |
Dane przykładowe (fikcyjny sklep), policzone formułami z instrukcji powyżej.
Z przykładu widać, po co jest kolumna Dni zapasu. Kubek biały ma jeszcze 9 sztuk, ale przy obecnym tempie sprzedaży wystarczy go na około 17 dni, więc zamówienie trzeba złożyć teraz, a nie po wyprzedaniu. Kubek czarny ma zapas na 135 dni, więc druga dostawa 60 sztuk byłaby zamrożeniem gotówki, które zwiększa koszty magazynowania.
Najczęstsze błędy w magazynie w Excelu
Większość problemów z magazynem w Excelu nie wynika z formuł, tylko z tego, jak ludzie wpisują dane. Oto błędy, które najczęściej psują arkusz, i sposób na każdy z nich.
- Ręczne nadpisywanie stanu. Ktoś zmienia „12” na „11” zamiast dopisać wydanie. Po miesiącu nie wiadomo, skąd wzięła się liczba. Rozwiązanie: stan tylko z formuły, a korekty po liczeniu jako wiersz Korekta+ lub Korekta- z dokumentem „SPIS”.
- Nazwa zamiast kodu. „Kubek czarny 300 ml” i „kubek czarny 300ml” to dla Excela dwa różne produkty. Rozwiązanie: łącz wszystko po SKU i wybieraj go z listy.
- Usuwanie wierszy z dziennika. Skasowany wiersz zmienia stan bez śladu. Rozwiązanie: błędny wpis koryguj nowym wierszem z odwrotną zmianą.
- Wpisywanie „na koniec dnia”. Arkusz pokazuje stan z wczoraj, a sklep sprzedaje dziś. Rozwiązanie: wpis zaraz po spakowaniu zamówienia albo przyjęciu dostawy.
- Kopie pliku. Plik „magazyn_nowy_FINAL2.xlsx” na pulpicie i drugi na dysku. Rozwiązanie: jeden plik w OneDrive lub SharePoint i współtworzenie zamiast przesyłania kopii.
- Stara funkcja „skoroszyt udostępniony”. Microsoft zastąpił ją współtworzeniem, a skoroszytu z tabelami Excela i tak nie da się w ten sposób udostępnić. Rozwiązanie: współtworzenie pliku zapisanego w chmurze.
Co zrobić, gdy arkusz pokazuje złe stany
Gdy stan w Excelu nie zgadza się z półką, przyczyna prawie zawsze leży w jednym z czterech miejsc: błędny kod, brakujący wpis, zła kolumna w formule albo zakres, który nie objął nowych wierszy. Tabela pomaga znaleźć ją szybko.
| Objaw | Najczęstsza przyczyna | Co zrobić |
|---|---|---|
| W kolumnie Nazwa pojawia się „BRAK SKU” | Literówka w kodzie albo produktu nie ma w tabeli Produkty | Popraw SKU w ruchu lub dodaj produkt do kartoteki; włącz listę wyboru dla SKU |
| Stan nie zmienia się po dopisaniu ruchu | Wiersz dopisany poza tabelą (np. po pustym wierszu) | Przenieś wpis bezpośrednio pod ostatni wiersz tabeli - wtedy tabela sama się rozszerzy |
| Wydanie zwiększa stan zamiast go zmniejszać | Inna pisownia typu, np. „wydanie ” ze spacją na końcu | Typ tylko z listy rozwijanej |
| Ujemny stan | Wydanie wpisane dwa razy albo dostawa niewpisana | Przefiltruj tabelę Ruchy po SKU i porównaj z zamówieniami i fakturami |
| Dni zapasu pokazują „brak sprzedaży” | Brak wydań w 30 dniach albo daty zapisane jako tekst | Sprawdź, czy kolumna Data ma format daty, a nie tekstu |
| Formuła z X.WYSZUKAJ nie działa u współpracownika | Excel 2016 lub 2019, w których nie utworzysz formuły z tą funkcją | Użyj wersji z WYSZUKAJ.PIONOWO i JEŻELI.BŁĄD |
Excel magazyn: kiedy przestaje wystarczać
Excel jako magazyn przestaje wystarczać nie przy konkretnej liczbie produktów, tylko wtedy, gdy arkusz nie nadąża za sprzedażą. Technicznie arkusz mieści 1 048 576 wierszy, więc limit Excela rzadko jest problemem. Problemem jest to, że stan w pliku zmienia się dopiero wtedy, gdy ktoś go wpisze. Jeśli rozpoznajesz u siebie dwa lub więcej sygnałów z listy, czas na system.
- Sprzedajesz w więcej niż jednym kanale. Sklep i sprzedaż na allegro z jednego zapasu oznaczają, że każde zamówienie trzeba wpisać do arkusza, a potem przepisać stan w drugim kanale. Każde opóźnienie przy ostatniej sztuce kończy się sprzedażą towaru, którego już nie ma.
- Wpisy nie nadążają za zamówieniami. Gdy dziennik ruchów ma zaległość dłuższą niż dzień, arkusz pokazuje historię, a nie stan.
- Arkusz zjada godziny. Przykład: 30 zamówień dziennie po półtorej minuty na wpis to 45 minut dziennie, czyli przy 22 dniach roboczych 990 minut, około 16,5 godziny miesięcznie samego przepisywania.
- Plik edytuje kilka osób. Pojawiają się kopie pliku, nadpisane komórki i pytanie „kto to zmienił?”.
- Po każdym liczeniu półek dopisujesz wiele korekt. To znak, że część ruchów nie trafia do arkusza w ogóle.
- Warianty mnożą wiersze. Koszulka w 5 rozmiarach i 4 kolorach to 20 wierszy, a każdy nowy kolor to 5 kolejnych SKU do założenia ręcznie.
- Potrzebujesz dokumentów, partii albo dat ważności. Arkusz z tego poradnika prowadzi ilości, a nie wycenę rozchodu ani terminy przydatności. Jeśli księgowość wymaga dokumentów magazynowych, potrzebny jest program, który je wystawia.
Dostawcy oprogramowania magazynowego podają w swoich poradnikach progi rzędu 500 albo około 1000 indeksów, ale to szacunki bez danych źródłowych. Bardziej wiarygodnym miernikiem jest czas, jaki zajmuje Ci przepisywanie, i liczba korekt po liczeniu półek.
Program magazynowy excel czy prawdziwy system: co wybrać
Po Excelu masz dwie drogi. Osobny program magazynowy wybierz, gdy potrzebujesz dokumentów magazynowych, inwentaryzacji, lokalizacji i partii. Platformę sprzedażową z wbudowanym magazynem wybierz, gdy głównym problemem jest to, że sprzedajesz w kilku kanałach, a stan żyje w osobnym pliku. Gotowy „program magazynowy w Excelu” z makrami rozwiązuje część problemów z wpisywaniem, ale nadal nie łączy się sam ze sklepem ani z marketplace'ami.
W ShopDepot magazyn jest częścią panelu, w którym przyjmujesz zamówienia. Według opisu na stronie magazyn w ShopDepot prowadzisz stan dla każdego produktu i wariantu, sam ustalasz, które statusy zamówienia zdejmują towar ze stanu, a które go przywracają (np. przy anulowaniu), a zamówienia ze sklepu i marketplace'ów korzystają z tego samego stanu. Zmiana stanu wychodzi automatycznie do podłączonych kanałów: Allegro, Amazona, Kauflandu, Erli i marketplace'ów działających na Mirakl. Oznacza to, że to, co w arkuszu robią kolumny Zmiana i Stan, dzieje się samo przy każdym zamówieniu, bez przepisywania.
Plany ShopDepot zaczynają się od 49 zł netto miesięcznie (100 zamówień w cenie), platforma nie pobiera prowizji od sprzedaży, a 14-dniowy okres próbny nie wymaga karty. Jeśli dziś masz sklep na Shoperze, WooCommerce, IdoSell, PrestaShop lub Shopify albo stany w BaseLinkerze, kreator przeniesienia sklepu przenosi produkty z SKU i GTIN oraz aktualne stany. Uporządkowana tabela Produkty z tego poradnika (SKU, wariant, stan) przyda się przy każdej zmianie systemu.