Jak stworzyć prosty system inwentaryzacji w Excelu przy użyciu kodów kreskowych
Autor Tomasz Lichosik
Gotowy na wygenerowanie kodów?
Twórz wysokiej jakości etykiety w kilka sekund.
Jeśli nadal wklepujesz numery SKU do arkusza ręcznie, płacisz za to dwa razy — raz czasem, a drugi raz literówkami, które pojawiają się gdzieś koło czterechsetnej pozycji. Nie potrzeba do tego drogiego ERP-a. Zwykły arkusz plus czytnik kodów dowozi większość tego, czym jest prawdziwy system magazynowy.
Jest jednak krok, który pomija niemal każdy poradnik, i to właśnie on zamienia działający system w taki, który po cichu gubi dane: Excel przepisuje część Twoich kodów w chwili, gdy trafią do komórki, i nie uprzedza o tym. Dlatego stoi tutaj na drugim miejscu, zaraz po strukturze — zanim wydrukujesz choć jedną etykietę.
1. Najpierw zaplanuj strukturę arkusza
Zanim wygenerujesz pierwszy kod, przygotuj w Excelu czyste źródło prawdy: jedna tabela, jeden wiersz na pozycję.
- ID produktu (SKU) — unikalna wartość dla każdej pozycji, np.
PROD-001. - Nazwa produktu — czytelne oznaczenie dla człowieka.
- Stan bieżący — ilość na stanie.
- Lokalizacja — gdzie leży, np.
Regał A-1. - Poziom ponownego zamówienia — próg, przy którym uzupełniasz zapas.
Trzymaj ID czysto alfanumeryczne, bez spacji i dziwnych znaków — starsze czytniki się na nich potykają.
2. Powstrzymaj Excela przed zjadaniem kodów
Komórka w formacie Ogólny — czyli domyślnym — próbuje potraktować wszystko, co do niej trafi, jak liczbę. Przy kodach kreskowych psuje się to na trzy różne sposoby.
Zera wiodące znikają. Zeskanuj 007123 do komórki Ogólnej, a dostaniesz 7123. Etykieta i arkusz przestają się zgadzać, a każde wyszukiwanie po tym SKU chybia.
Długie kody przechodzą na notację naukową. Trzynastocyfrowy EAN wyświetla się jako 5,90123E+12. Uciążliwe, ale przynajmniej widoczne.
I ten sposób, który naprawdę kosztuje: cyfry powyżej piętnastej zostają zastąpione zerami. Excel przechowuje tylko 15 znaczących cyfr dziesiętnych. Wszystko dłuższe jest po cichu obcinane — trwale, nie tylko na ekranie.
| Typ kodu | Cyfr | Jako tekst | W komórce liczbowej | Nienaruszony? |
|---|---|---|---|---|
| EAN-13 | 13 | 5901234123457 | 5901234123457 | tak |
| GTIN-14 (ITF-14) | 14 | 15901234123454 | 15901234123454 | tak |
| SSCC-18 | 18 | 340123450000000014 | 340123450000000000 | nie |
| SSCC-18 | 18 | 106141411234567897 | 106141411234567000 | nie |
Kody detaliczne przechodzą bez szwanku. Etykiety paletowe nie. Jeśli masz do czynienia z SSCC-18 — a masz w chwili, gdy wysyłasz na paletach albo przyjmujesz od dystrybutora — każdy z nich traci trzy ostatnie cyfry.
I tu zaczyna się rzecz naprawdę groźna, a nie tylko uciążliwa. Można by rozsądnie założyć, że kontrola cyfry kontrolnej wychwyci uszkodzenie. Wygenerowaliśmy 100 000 poprawnych SSCC, przepuściliśmy każdy przez komórkę liczbową i sprawdziliśmy ponownie: 9,9% uszkodzonych kodów nadal przechodzi test cyfry kontrolnej. Walidacja łapie mniej więcej dziewięć na dziesięć — a ten dziesiąty wygląda całkowicie poprawnie aż do palety.
Poprawka, zanim cokolwiek zeskanujesz:
- Zaznacz kolumnę SKU.
- Formatuj komórki → Tekstowe. Zrób to przed pojawieniem się danych; zmiana formatu później nie przywróci cyfr, których już nie ma.
- Importujesz istniejący plik? Idź przez Dane → Z tekstu/CSV i ustaw kolumnę z kodami na Tekst w oknie importu, a nie po fakcie.
Szybki sposób na sprawdzenie: wpisy tekstowe domyślnie wyrównują się do lewej, liczby do prawej. Jeśli Twoje kody przyklejają się do prawej krawędzi komórki, Excel wciąż uważa je za liczby.
3. Wygeneruj kody kreskowe
Teraz trzeba zamienić te identyfikatory w coś, co maszyna umie odczytać. Wiele osób sięga po „font kodów kreskowych" w Excelu — wygląda to amatorsko i przestaje się skanować, gdy tylko rozmiar albo marginesy trochę się zmienią. Font nie policzy też cyfry kontrolnej ani nie zostawi strefy ciszy, więc bez oporu wyrenderuje symbol nieprawidłowy.
Pewna droga to wygenerowanie prawdziwych kodów SVG dla każdego SKU — zgodnych z normą i ostrych. Zrób po jednym na SKU w BarcodeReady i pobierz je jako SVG, żeby krawędzie przetrwały druk. Przy katalogu realnej wielkości generowanie hurtowe z pliku CSV bije robienie tego pojedynczo.
4. Wydrukuj i oznacz
Przejście z ekranu na regał:
- Drukuj na arkuszach etykiet samoprzylepnych, na przykład Avery.
- Naklej po jednej etykiecie na każdą pozycję albo na miejsce regałowe, w którym leży.
- Zeskanuj próbkę przed zrobieniem tysiąca i sprawdź, czy czytnik wrzuca do Excela właściwe ID.
Krok 3 nie jest opcjonalny i warto zrobić go na drukarce, której faktycznie użyjesz — siatka punktów zmienia wymiary wydruku w sposób łatwy do przeoczenia. Typowe błędy w druku kodów opisują, co zmierzyć.
5. Zbuduj stanowisko skanowania
Tu zaczyna się automat. Czytnik kodów zachowuje się jak klawiatura: „wpisuje" wartość, a potem wysyła klawisz kończący.
To, który to klawisz, decyduje o tym, jak wypełnia się arkusz:
| Sufiks | Kursor przechodzi | Do czego |
|---|---|---|
| Enter (domyślnie) | wiersz niżej | jeden skan na wiersz — dziennik skanów |
| Tab | kolumnę w prawo | kilka skanów w wierszu — SKU, potem lokalizacja, potem pojemnik |
Niemal każdy czytnik pozwala to przełączyć zeskanowaniem kodu konfiguracyjnego z instrukcji. Dwie sekundy ustawień, które przesądzają, czy dane wpadną w oczekiwanym kształcie.
Załóż drugi arkusz o nazwie Dziennik skanów, sformatuj kolumnę ID jako Tekst (patrz krok 2), kliknij w komórkę i skanuj — ID ląduje, a kursor rusza dalej.
6. Waliduj każdy skan cyfrą kontrolną
Czytnik myli się znacznie rzadziej, niż człowiek się myli przy klawiaturze, ale „znacznie rzadziej" to nie „nigdy", a wilgotna albo porysowana etykieta podnosi szanse. Ostatnia cyfra GTIN istnieje właśnie po to, żeby sprawdzić resztę, a Excel potrafi ten rachunek wykonać:
=MOD(10-MOD(SUMA.ILOCZYNÓW(FRAGMENT.TEKSTU(A2;WIERSZ(ADR.POŚR("1:"&DŁ(A2)));1)
*(3-2*MOD(DŁ(A2)-WIERSZ(ADR.POŚR("1:"&DŁ(A2)));2)));10);10)
Wstaw kod bez ostatniej cyfry do A2; formuła zwróci, jaka ta ostatnia cyfra powinna być. Porównaj z tym, co faktycznie zeskanowałeś.
Excel tłumaczy nazwy funkcji zależnie od wersji językowej. Jeśli Twoja odrzuci formułę, oto oryginał angielski do przerobienia: =MOD(10-MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*(3-2*MOD(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),2))),10),10)
Dlaczego ta formuła wygląda na bardziej zawiłą niż te, które można znaleźć gdzie indziej: GS1 waży cyfry od prawej, nie od lewej — ostatnia cyfra korpusu dostaje wagę 3, potem 1, 3, 1 i tak dalej. Licząc od lewej, trafiasz poprawnie tylko wtedy, gdy korpus ma parzystą liczbę cyfr. To obejmuje EAN-13 (12 cyfr), więc prostsza formuła sprawia wrażenie działającej — a potem po cichu zawodzi na UPC-A (11) i GTIN-14 (13). To człon DŁ(A2) sprawia, że jedna formuła obsługuje wszystkie.
Dwa uczciwe ograniczenia. Cyfra kontrolna wychwytuje 91% przestawień sąsiednich cyfr, czyli klasycznego błędu ręcznego wpisu. Te, które umykają, to dokładnie pary różniące się o 5, i wynika to z natury wag 1/3, a nie z wady: zamiana dwóch cyfr przesuwa sumę ważoną o podwojoną ich różnicę, a 2 × 5 = 10 znika modulo 10. Poza tym, jak pokazuje krok 2, cyfra kontrolna nie powie Ci wiarygodnie, że Excel obciął kod — napraw format komórki, zamiast polegać na walidacji.
7. Podciągaj dane automatycznie
Żeby obok każdego skanu pojawiła się nazwa produktu:
=X.WYSZUKAJ(A2; Produkty!$A$2:$A$5000; Produkty!$B$2:$B$5000)
Zwróć uwagę na ograniczone zakresy. Kusi, żeby napisać Produkty!A:A — jest krócej i działa — ale odwołanie do całej kolumny wskazuje na wszystkie 1 048 576 wierszy arkusza, i to dla każdego wiersza Twojego dziennika:
| Wierszy skanów | Komórek przez A:A |
Przez A2:A5000 |
|---|---|---|
| 100 | 104 857 600 | 499 900 |
| 1 000 | 1 048 576 000 | 4 999 000 |
| 5 000 | 5 242 880 000 | 24 995 000 |
To 210 razy więcej komórek na tę samą odpowiedź. Nowoczesny Excel sporo z tego optymalizuje, ale jest to najczęstszy powód, dla którego dziennik skanów natychmiastowy w pierwszym tygodniu w szóstym zaczyna zauważalnie przystawać. Jeszcze lepiej: zamień listę produktów w prawdziwą tabelę (Ctrl+T) i odwołuj się przez Produkty[SKU] — zakres będzie wtedy rósł razem z danymi.
8. Rytm codzienny
Gdy to stoi, bieżąca praca jest szybka:
- Przyjęcie: zeskanuj pozycję, dodaj do stanu.
- Wydanie: zeskanuj pozycję, odejmij od stanu.
- Inwentaryzacja: zeskanuj to, co jest na regale, i porównaj sumy, żeby wyłapać rozbieżności. Pełna inwentaryzacja ma własny przebieg.
Kiedy wyrastasz z Excela
Arkusz to świetny punkt startu, ale ma sufit. Uczciwe sygnały, że właśnie o niego uderzyłeś:
- Dwie osoby muszą skanować jednocześnie. To jest prawdziwa granica i nie chodzi w niej o rozmiar: współdzielony skoroszyt kiepsko znosi równoczesne zapisy i czyjeś skany po prostu przepadają.
- Regularnie obsługujesz etykiety paletowe SSCC-18. Krok 2 je zabezpiecza, ale wystarczy, że ktoś otworzy plik i przeformatuje kolumnę, żeby to po cichu unieważnić.
- Katalog przekracza kilka tysięcy SKU, a wyszukiwania zwalniają nawet przy ograniczonych zakresach.
- Potrzebujesz synchronizacji na żywo ze sklepem — Shopify, WooCommerce lub podobnym. Zobacz przewodnik po kodach dla Shopify i WooCommerce.
Do tego czasu Excel plus kody kreskowe to najszybszy i najtańszy sposób, żeby nadać zapasom realną strukturę — pod warunkiem, że komórki są sformatowane jako tekst przed pierwszym skanem. Potrzebne kody wygenerujesz w BarcodeReady i możesz zaczynać.
Powiązane artykuły
- Śledzenie majątku: zarządzanie sprzętem firmowym przez kody
- Jak okodować magazyn: przewodnik po oznaczaniu lokalizacji
- Przewodnik po kodach dla Shopify i WooCommerce
- Jak generować kody kreskowe hurtowo
- Jak drukować kody kreskowe w domu
- Jak zrobić inwentaryzację czytnikiem i Excelem
- Etykiety paletowe SSCC-18: kompletny przewodnik