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.

M Max CyrekShopDepot Opublikowany Czas czytania9 min czytania

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)

KolumnaCo wpisujeszWpisujesz czy liczy formuła
SKUUnikalny kod produktu lub wariantu, np. KUB-CZ-01Wpisujesz
NazwaNazwa produktuWpisujesz
WariantKolor, rozmiar, pojemnośćWpisujesz
EANKod kreskowy, jeśli produkt go maWpisujesz
LokalizacjaRegał i półka, np. R2-P3Wpisujesz
Stan początkowyIlość z liczenia w dniu startuWpisujesz raz
Stan minimalnyPróg, przy którym zamawiasz towarWpisujesz
Cena zakupu nettoCena z ostatniej faktury zakupuWpisujesz
StanAktualna ilośćFormuła
Wydano 30 dniSprzedaż z ostatnich 30 dniFormuła
Dni zapasuNa ile dni wystarczy towarFormuła
WartośćStan razy cena zakupuFormuła
StatusOK albo ZAMÓWFormuł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)

KolumnaCo wpisujeszWpisujesz czy liczy formuła
DataDzień ruchu towaruWpisujesz
DokumentNumer faktury zakupu, numer zamówienia, „SPIS”Wpisujesz
TypPrzyjęcie, Wydanie, Zwrot, Korekta+, Korekta-Wybierasz z listy
SKUKod produktuWybierasz z listy
NazwaPodgląd nazwy, żeby wyłapać pomyłkę w SKUFormuła
IlośćZawsze liczba dodatniaWpisujesz
KanałSklep, Allegro, sprzedaż osobistaWybierasz z listy
ZmianaIlość ze znakiem plus lub minusFormuła
KtoInicjały osoby, która wpisała ruchWpisujesz

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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).
  5. 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])
  6. 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")
  7. 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.

SKUStan początkowySuma zmianStanWydano 30 dniDni zapasuStan minimalnyStatus
KUB-CZ-0140+41811813515OK
KUB-BI-0125-1691616,910ZAMÓW
POD-DR-0212-939105ZAMÓ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.

ObjawNajczęstsza przyczynaCo zrobić
W kolumnie Nazwa pojawia się „BRAK SKU”Literówka w kodzie albo produktu nie ma w tabeli ProduktyPopraw SKU w ruchu lub dodaj produkt do kartoteki; włącz listę wyboru dla SKU
Stan nie zmienia się po dopisaniu ruchuWiersz 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ńcuTyp tylko z listy rozwijanej
Ujemny stanWydanie wpisane dwa razy albo dostawa niewpisanaPrzefiltruj 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 tekstSprawdź, czy kolumna Data ma format daty, a nie tekstu
Formuła z X.WYSZUKAJ nie działa u współpracownikaExcel 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.

  1. 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.
  2. 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.
  3. 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.
  4. Plik edytuje kilka osób. Pojawiają się kopie pliku, nadpisane komórki i pytanie „kto to zmienił?”.
  5. Po każdym liczeniu półek dopisujesz wiele korekt. To znak, że część ruchów nie trafia do arkusza w ogóle.
  6. 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.
  7. 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.

Gotowy, by sprzedawać wszędzie?

Otwórz sklep w kilka minut i podłącz platformy sprzedażowe - 14 dni za darmo.

14 dni za darmo Załóż sklep