Jak prowadzić magazyn w Excelu: układ skoroszytu z rejestrami i formuła stanu. Gdzie kończą się możliwości arkusza i kiedy warto przejść na system WMS.
Wiele firm zaczyna od arkusza i na początku to rozsądna decyzja. Ta strona pokazuje, jak zbudować w Excelu prosty magazyn, w którym stan liczy się sam, i gdzie dokładnie kończą się możliwości arkusza. Program magazynowy w Excelu to w praktyce jeden skoroszyt z kilkoma powiązanymi arkuszami, a nie system, który zna lokalizacje i partie. Strona nie zniechęca do arkusza i nie zachęca z góry do systemu: daje wzór, który można odtworzyć samodzielnie, oraz uczciwą listę sytuacji, w których ten wzór przestaje wystarczać.
Sąsiednie strony odpowiadają na inne pytania. Budowę samej karty towaru opisuje strona o kartotece magazynowej w Excelu, a argumenty za zmianą arkusza na system zebrano na stronie Zamień Excel na WMS. Tutaj chodzi o cały skoroszyt: rejestr ruchów, formułę stanu i granice arkusza. Tabela poniżej pomaga wybrać stronę do dalszej lektury.
| Strona | Na jakie pytanie odpowiada |
|---|---|
| Ta strona | Jak zbudować skoroszyt z przyjęciami i wydaniami, jak policzyć stan i gdzie arkusz przestaje wystarczać |
| Kartoteka magazynowa w Excelu | Jakie dane ma mieć karta towaru w arkuszu i jak ją zbudować |
| Zamień Excel na WMS | Dlaczego i jak zamienić arkusz na system WMS |
| Magazyn Excel czy system WMS | Co potrafi arkusz, a co system, w ujęciu porównawczym |
| WMS i Excel | Jak używać Excela obok systemu, na przykład do zestawień i analiz |
| Program magazynowy | Czym jest program magazynowy Studio WMS.net i jak wygląda wdrożenie |
| Ewidencja magazynowa | Jak zapisuje się ruchy towaru dokumentami PZ i WZ, niezależnie od narzędzia |
| Import danych z Excela | Jak wczytać kartoteki i kontrahentów z arkusza do Studio WMS.net |
O czym piszemy na tej stronie?
- Excel w zarządzaniu magazynem - podstawy
- Jak stworzyć prosty magazyn w Excelu?
- Jakie formuły liczą stan magazynowy?
- Excel jako program magazynowy - kiedy działa, a kiedy zawodzi?
- Dlaczego małe firmy wybierają Excel?
- Kiedy Excel staje się przeszkodą
- Jak przenieść dane z arkusza do WMS?
- Excel vs WMS - najczęstsze pytania
- Kiedy arkusz Excel przestaje wystarczać w profesjonalnym zarządzaniu magazynem?
- Jakie zagrożenia dla danych niesie ewidencja w Excelu?
- Czym Studio WMS.net różni się od arkusza w codziennej pracy?
- Czy migracja danych z arkuszy Excel do Studio WMS.net jest trudna?
- Jaką formułą policzyć stan magazynowy w Excelu?
Excel w zarządzaniu magazynem - podstawy
Czym jest magazyn prowadzony w arkuszu
Zarządzanie magazynem w Excelu to ręczne wpisywanie ruchów towarowych do arkusza, który pełni funkcję centralnego rejestru zapasów. Takie rozwiązanie obejmuje listę towarów, prostą kontrolę stanów ilościowych oraz rejestrację przychodów i rozchodów za pomocą formuł. Pasuje przede wszystkim do mikroprzedsiębiorstw i start-upów, a także do firm o bardzo niskiej dynamice obrotu, w których liczba indeksów nie przekracza kilkudziesięciu pozycji. Zaletą jest niemal zerowy koszt wejścia i powszechna dostępność pakietu biurowego, a do tego pełna swoboda w tworzeniu szablonów dopasowanych do własnych potrzeb.
Prostota arkusza pozwala zacząć szybko, bez infrastruktury serwerowej i bez kolektorów danych z Androidem. Użytkownik sam projektuje kolumny: nazwy towarów, kody EAN, jednostki miary i ceny zakupu, więc ewidencję można zacząć od razu. Excel nie jest jednak systemem klasy WMS i nie wymusza kolejności operacji, a poprawność danych sprawdza tylko tam, gdzie sam ustawisz regułę. Elastyczność ma drugą stronę: bez narzuconych standardów logistycznych dokumentacja zaczyna się rozjeżdżać, gdy rośnie skala działalności.
Firmy, które zamieniają Excel na WMS, czyli przechodzą z arkuszy na system Studio WMS.net, zwykle wskazują te same powody. Wraz z rozwojem firmy ryzyko nadpisania danych albo utraty formuły staje się podstawowym zagrożeniem dla ciągłości pracy. Dlatego arkusz najlepiej traktować jako rozwiązanie przejściowe: pozwala zrozumieć przepływ towarów, zanim firma będzie gotowa na mobilność i skanowanie. Do raportowania i prostych zestawień arkusz zostaje przydatny także po wdrożeniu systemu.
Co daje arkusz w małej skali
Zasady zapisu ruchów opisuje strona o ewidencji magazynowej, a planowanie zapasu - strona o zarządzaniu zapasami. Porównanie arkusza z systemem znajdziesz na stronie Excel w magazynie, a pełną listę artykułów w spisie treści.
Jak stworzyć prosty magazyn w Excelu?
Budowa własnego narzędzia do kontroli zapasów wymaga przemyślanej struktury tabel i kilku podstawowych automatyzmów obliczeniowych. Najprostsza wersja to jeden arkusz z kolumnami [Indeks] [Nazwa] [J.m.] [Ilość] [Cena] [Wartość], w którym ilość poprawia się ręcznie po każdej dostawie i każdym wydaniu. Działa to w bardzo małej skali, ale zostawia po sobie tylko ostatnią liczbę, bez historii operacji. Dlatego lepiej rozdzielić dane na kilka arkuszy.
Cztery arkusze zamiast jednej tabeli
Układ, który polecamy na start, składa się z czterech arkuszy. Kartoteka przechowuje dane towaru i zmienia się rzadko. Przyjęcia i Wydania to dwa rejestry, do których dopisuje się jeden wiersz na jedną operację. Arkusz Stany niczego nie przechowuje, tylko liczy: dla każdego indeksu z kartoteki sumuje przyjęcia i wydania. Schemat pokazuje, skąd bierze się każda liczba.
Każdy arkusz ma stały zestaw kolumn. Tabela zestawia je razem z zasadą, której warto pilnować od pierwszego dnia.
| Arkusz | Kolumny | Zasada |
|---|---|---|
| Kartoteka | Indeks, Nazwa, Jednostka, EAN, Minimum, Cena | Indeks jest unikalny i nigdy się nie zmienia |
| Przyjęcia | Data, Numer dokumentu, Indeks, Ilość, Dostawca | Ilość jest zawsze dodatnia, jedna dostawa to jeden wiersz |
| Wydania | Data, Numer dokumentu, Indeks, Ilość, Odbiorca | Ilość też jest dodatnia, a kierunek wynika z nazwy arkusza |
| Stany | Indeks, Nazwa, Przyjęto, Wydano, Stan, Minimum, Status | Same formuły, bez wpisów ręcznych |
Kolumny kartoteki są tu celowo skrócone. Pełny zestaw danych, jakie warto trzymać przy towarze, opisuje strona o kartotece magazynowej w Excelu. Do kartoteki można dopisać kolumnę z lokalizacją, ale będzie to zwykły opis tekstowy: arkusz nie wie, ile mieści półka, i nie sprawdzi, czy paleta nie stoi w dwóch miejscach naraz. Prawdziwe adresy miejsc składowania opisuje strona o lokalizacjach w magazynie.
Etapy budowy prostego arkusza
Zasady wpisywania, które chronią dane
Dwie decyzje projektowe oszczędzają najwięcej kłopotów.
- Rejestry Przyjęcia i Wydania zamień na tabele Excela (polecenie „Formatuj jako tabelę”). Zakresy rozszerzają się wtedy same, a filtry pozwalają posortować asortyment według dowolnego kryterium.
- Istniejących wierszy rejestru nie poprawiaj. Pomyłkę koryguje się nowym wierszem z numerem dokumentu korygującego, dzięki czemu historia każdej operacji zostaje czytelna.
Do tego dochodzi sprawdzanie poprawności danych z listy: kolumna Indeks w rejestrach przyjmuje tylko wartości z kartoteki, więc literówka nie utworzy nowego, nieistniejącego towaru. To ten sam mechanizm, który ogranicza ryzyko pomyłek w nazwach lokalizacji i kategorii produktowych.
Jakie formuły liczą stan magazynowy?
Stan magazynowy w arkuszu jest wynikiem, a nie wpisem. Formuły sumują zarejestrowane dostawy i wydania towaru, więc po każdej nowej operacji liczba w arkuszu Stany odświeża się sama.
stan = suma przyjęć indeksu - suma wydań indeksu
Obie sumy liczy funkcja SUMA.JEŻELI po kolumnie Indeks, więc kolejność wierszy w rejestrach nie ma znaczenia.
Podstawowe znaczenie ma funkcja SUMA.JEŻELI, która agreguje ruchy dla konkretnego indeksu. Funkcja WYSZUKAJ.PIONOWO pobiera opis artykułu z kartoteki po wpisaniu jego unikalnego kodu. Przy kilku warunkach naraz, na przykład indeksie i zakresie dat, używa się funkcji SUMA.WARUNKÓW. Jej argumenty ułożone są inaczej niż w SUMA.JEŻELI: najpierw zakres sumowany, potem pary zakres i kryterium, o czym mówi dokumentacja Microsoft.
| Zadanie | Formuła | Uwaga |
|---|---|---|
| Przyjęto | =SUMA.JEŻELI(tPrzyjecia[Indeks];A2;tPrzyjecia[Ilość]) | tPrzyjecia to nazwa tabeli z rejestrem przyjęć |
| Wydano | =SUMA.JEŻELI(tWydania[Indeks];A2;tWydania[Ilość]) | Ta sama konstrukcja dla rejestru wydań |
| Stan | =C2-D2 | Różnica dwóch sum z tego samego wiersza |
| Nazwa z kartoteki | =WYSZUKAJ.PIONOWO(A2;tKartoteka;2;FAŁSZ) | Argument FAŁSZ wymusza dokładne dopasowanie indeksu |
| Brak indeksu w kartotece | =JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;tKartoteka;2;FAŁSZ);"brak w kartotece") | Zamiast komunikatu błędu pojawia się czytelny opis |
| Status | =JEŻELI(E2<=F2;"Zamów";"OK") | F2 to minimum pobrane z kartoteki |
| Wydania w okresie | =SUMA.WARUNKÓW(tWydania[Ilość];tWydania[Indeks];A2;tWydania[Data];">="&I1;tWydania[Data];"<="&I2) | I1 i I2 to daty początku i końca okresu |
Formatowanie warunkowe z regułą opartą na formule =$E2<=$F2 podświetla na czerwono wiersze, w których stan spadł do minimum, co ułatwia terminowe zamawianie zapasów. W angielskiej wersji Excela te same funkcje nazywają się SUMIF, SUMIFS, VLOOKUP, IFERROR i IF, a argumenty rozdziela przecinek.
Przykład: arkusz Stany z danymi ilustracyjnymi
Towar A-100 przyszedł dwiema dostawami po 200 i 100 sztuk, a wyszedł dwoma wydaniami po 120 i 40 sztuk. Formuły dają przyjęto 300, wydano 160 i stan 140. Minimum wynosi 50, więc status to OK.
| Indeks | Nazwa | Przyjęto | Wydano | Stan | Minimum | Status |
|---|---|---|---|---|---|---|
| A-100 | Karton 300x200 | 300 | 160 | 140 | 50 | OK |
| B-200 | Taśma pakowa | 60 | 25 | 35 | 40 | Zamów |
| C-300 | Folia stretch | 24 | 24 | 0 | 6 | Zamów |
Przy bardzo niewielkim asortymencie taki skoroszyt wystarcza do codziennej pracy. Wartość magazynu liczy się mnożeniem stanu przez cenę z kartoteki, a zestawienia dla księgowości powstają z filtrów i prostych tabel przestawnych. Nie zastępuje to jednak dokumentów magazynowych: numer dokumentu w rejestrze jest tylko tekstem, który ktoś musi wystawić gdzie indziej.
Excel jako program magazynowy - kiedy działa, a kiedy zawodzi?
Granice arkusza wynikają z jego budowy: Excel zapisuje komórki, a nie operacje magazynowe. Skoroszyt z poprzednich sekcji wystarcza zwykle w czterech sytuacjach.
- Ruchy wpisują jedna albo dwie osoby, a pozostali tylko czytają zestawienie.
- Każdy towar leży w jednym miejscu i nikt nie musi pytać, na której półce.
- Asortyment nie ma partii ani terminów ważności, które decydują o kolejności wydań.
- Ruchów jest na tyle mało, że ręczny wpis nie opóźnia wydań.
Gdy któryś z tych warunków przestaje być prawdziwy, arkusz zaczyna wymagać obejść. Tabela zbiera miejsca, w których to widać najwcześniej, razem z tym, co da się zrobić, dopóki firma zostaje przy Excelu.
| Obszar | Co dzieje się w arkuszu | Co zrobić, zostając przy Excelu |
|---|---|---|
| Kilku użytkowników | Współedytowanie pliku w OneDrive lub SharePoint (Microsoft 365) pozwala pracować kilku osobom naraz, ale bez blokady dokumentu i kontroli kolejności zapisów. Dwie osoby mogą wydać tę samą sztukę | Wyznacz jedną osobę wpisującą ruchy, resztę ogranicz do odczytu |
| Lokalizacje | Adres miejsca to tekst w komórce, bez pojemności półki i bez rozbicia stanu na kilka miejsc | Jeden indeks to jedno miejsce, a kolumna lokalizacji służy tylko jako podpowiedź |
| Partie i terminy ważności | Stan liczy się dla indeksu, nie dla partii, więc reguły FIFO i FEFO nie mają na czym działać | Osobny wiersz kartoteki dla każdej partii, co szybko rozrasta listę |
| Historia zmian | Rejestry zachowują operacje, ale nadpisana komórka lub usunięty wiersz nie zostawiają śladu, kto i kiedy to zrobił | Chroń arkusze Stany i Kartoteka, a plik trzymaj w miejscu z historią wersji |
| Skanowanie | Skaner podłączony do komputera działa jak klawiatura i wpisze kod do komórki, ale magazynier nie ma w ręku terminala z listą zadań | Wydruk listy i wpis przy biurku po powrocie z hali |
| Kopie i dostępność | Cała ewidencja to jeden plik, więc jego usunięcie lub uszkodzenie zatrzymuje pracę | Kopia codzienna w osobnej lokalizacji, zgodnie z zasadami opisanymi w bezpieczeństwie danych w chmurze |
| Wymiana danych | Wymiana z ERP lub sklepem odbywa się przez ręczny eksport i import plików CSV albo XLSX | Stały termin eksportu i jedna osoba odpowiedzialna za wczytanie |
W systemie WMS te same obszary mają osobne mechanizmy. Na przykład formularz Szukaj w Studio WMS.net pozwala znaleźć dokument po dacie, rodzaju, kontrahencie, asortymencie, numerze partii i użytkowniku. W arkuszu odpowiada temu filtr po kolumnach, który działa tylko na tym, co wpisano do rejestru.
Uprawnienia pokazują podobną różnicę. W Excelu można zablokować arkusz hasłem, ale hasło chroni plik, a nie konkretną operację. W Studio WMS.net administrator ustawia dostęp do transakcji osobno dla każdego użytkownika, co opisuje strona o uprawnieniach per użytkownik.
Dlaczego małe firmy wybierają Excel?
Atuty arkusza w małej logistyce
Dla małych podmiotów gospodarczych arkusz kalkulacyjny to najbardziej przystępny punkt wejścia w cyfrową ewidencję zasobów.
Zerowy koszt licencji jest istotnym argumentem dla start-upów, które już mają pakiet biurowy. Brak potrzeby zakupu serwerów i opłacania miesięcznych subskrypcji pozwala przeznaczyć kapitał na towar albo marketing, co ma znaczenie na wczesnym etapie rozwoju firmy. Prostą tabelę obsłuży każdy pracownik biurowy bez szkolenia specjalistycznego, więc bariery wejścia są niskie. Przygotowanie szablonu może zająć kilka godzin, a po nim można od razu zacząć ewidencję.
Excel daje pełną kontrolę nad strukturą danych, więc kolumny łatwo dopasować do własnego asortymentu. W przeciwieństwie do systemów pudełkowych arkusz pozwala dopisać parametry takie jak numery seryjne, daty ważności czy kody kolorystyczne bez udziału programisty. Trzeba tylko pamiętać, że to kolumny opisowe: stan nadal liczy się dla indeksu, a nie dla partii. Łatwy eksport do formatów CSV i XLSX przydaje się przy szybkich zestawieniach dla księgowości i analizach sprzedaży oraz przy późniejszym przeniesieniu danych do systemu.
Firmy, które wyrastają z rozwiązań arkuszowych, mogą przenieść zgromadzoną historię operacji do bazy SQL, na której działa Studio WMS.net, a szczegóły opisuje strona o imporcie danych z Excela. Arkusz uczy też dyscypliny w dokumentowaniu przychodów i rozchodów, co ułatwia późniejszą pracę z kolektorami Android. Wykresy i proste dashboardy wewnątrz arkusza dają właścicielowi małej firmy poczucie kontroli bez kosztów systemów Business Intelligence.
Jeśli szukasz darmowego narzędzia, zanim zdecydujesz się na arkusz, możesz najpierw przetestować program magazynowy online. To, jak Excel współpracuje z systemem już po wdrożeniu, opisuje strona WMS i Excel, a kierunek rozwoju małej firmy w ujęciu ogólnym omawia strona o logistyce magazynowej.
Kiedy Excel staje się przeszkodą
Sygnały, że czas zmienić arkusz na system WMS
Moment, w którym arkusz staje się barierą dla rozwoju firmy, widać po błędach operacyjnych i po tym, ile czasu zespół poświęca na pilnowanie pliku.
Orientacyjnie od około tysiąca indeksów ręczne przeszukiwanie wierszy i prowadzenie kartotek zaczyna być czasochłonne, ale liczba pozycji to tylko wskaźnik pomocniczy, a nie próg. Większe znaczenie ma to, kto wpisuje dane. Brak pracy wielostanowiskowej w czasie rzeczywistym prowadzi do powstawania wielu wersji tego samego pliku i problemów z synchronizacją między działem sprzedaży a magazynem. Częste błędy przy ręcznym wpisywaniu ilości dają rozbieżności w stanach, a ich wykrycie wymaga żmudnej inwentaryzacji. W rezultacie firma traci kontrolę nad zasobami, co przekłada się na opóźnienia w realizacji zamówień.
Drugą grupę sygnałów tworzą ograniczenia samego narzędzia. Excel nie obsługuje bezpośrednio skanowania kodów kreskowych, więc bez kolektorów danych Android pracownicy weryfikują towar „na oko” i tracą czas. Nie śledzi też lokalizacji w czasie rzeczywistym, więc odnalezienie konkretnej palety na magazynie wysokiego składowania zależy od pamięci magazyniera. Arkusz nie wyda też towaru według metod FIFO ani FEFO, co w branżach z krótkim terminem ważności może przynieść straty finansowe, a o ich skali decydują dane z konkretnej firmy, nie ogólne wskaźniki.
Tabela proponuje cztery sprawdzenia, które każda firma może wykonać na własnych danych, zanim zdecyduje o zmianie.
| Sygnał | Jak to sprawdzić u siebie | Co z tego wynika |
|---|---|---|
| Kilka wersji pliku | Przejrzyj folder i policz pliki typu „magazyn_nowy” oraz „magazyn_final” | Brak jednego źródła prawdy o stanie |
| Stan z arkusza różni się od półki | Po inwentaryzacji porównaj liczby i zapisz, jak często rozbieżność się powtarza | Błędy wpisu albo wydania bez dokumentu |
| Towar szuka się z pamięci | Zapytaj magazynierów, ile razy w tygodniu ktoś pyta, gdzie coś leży | Potrzebne są lokalizacje, a nie kolumna tekstowa |
| Kolejność wydań zależy od daty | Sprawdź, czy ktoś ręcznie pilnuje, która dostawa wychodzi pierwsza | Potrzebna ewidencja partii i reguła FEFO |
Kiedy czas poświęcany na naprawianie błędów w Excelu przewyższa czas realnej pracy logistycznej, zmiana narzędzia przestaje być kwestią wygody. Wybór ułatwia testowanie systemu WMS przed wdrożeniem. Awizacja dostaw i obsługa reklamacji wymagają wspólnej bazy, do której piszą różne osoby, a w arkuszu można je tylko odwzorować ręcznie. Dlatego dla firm rozwijających się szybciej niż ich ewidencja przejście na system staje się praktyczną koniecznością.
Jak przenieść dane z arkusza do WMS?
Skoroszyt zbudowany według opisanego wzoru przenosi się łatwo, bo dane są już rozdzielone na kartotekę i rejestry. W Studio WMS.net rolę arkuszy Przyjęcia i Wydania przejmują dokumenty magazynowe PZ, PZB, WZ, WZB, PW i RW zapisane w bazie MS SQL Server. Dane z arkusza trafiają do niej przez wbudowane narzędzia importu, o czym piszą strony o imporcie danych z Excela i o migracji danych. Import przenosi kartoteki towarowe razem ze stanami na moment startu, a program sprawdza pola obowiązkowe i duplikaty przed zapisem.
Przygotowanie arkusza do przeniesienia zajmuje zwykle mniej czasu niż sam import, o ile kilka rzeczy zrobi się wcześniej.
- Usuń zdublowane indeksy i ujednolij nazwy towarów.
- Ujednolij jednostki miary oraz zapis wag i wymiarów.
- Zrób inwentaryzację w dniu startu i wpisz stan początkowy.
- Zapisz kopię skoroszytu przed jakimikolwiek poprawkami.
Zakres przenoszonej historii ustala się przed startem. Do pracy na nowym systemie zwykle wystarcza kartoteka i stan początkowy, a stare rejestry zostają w archiwum jako plik. Dalszą lekturę o samej decyzji o zmianie narzędzia znajdziesz na stronie Zamień Excel na WMS, a działanie systemu można zobaczyć w bezpłatnym demo.