Tydzień wielkiej wyprzedażyClaude Skills — 20% RABATU
Tips

Jak stworzyć arkusz do śledzenia zapasów w Excelu: Krok po kroku

Powerdrill Bloom·
Jak stworzyć arkusz do śledzenia zapasów w Excelu: Krok po kroku

Aby stworzyć arkusz do śledzenia zapasów w Excel, zbuduj dwie tabele. Pierwsza zawiera listę przedmiotów z kolumnami takimi jak ID przedmiotu, nazwa, koszt jednostkowy, stan początkowy i punkt ponownego zamówienia. Druga rejestruje każdy ruch, zawierając datę, ID przedmiotu, typ (Przyjęcie lub Wydanie) oraz ilość. Następnie użyj funkcji SUMIFS, aby zsumować przyjęcia i wydania towaru, a potem odejmij je od siebie, aby uzyskać aktualny stan zapasów.

Dodaj kolumnę statusu, która oznacza przedmioty na poziomie lub poniżej punktu ponownego zamówienia, i wyróżnij te wiersze za pomocą formatowania warunkowego. Krótkie podsumowanie na górze pokaże całkowitą wartość zapasów oraz liczbę przedmiotów wymagających ponownego zamówienia.

Ten przewodnik obejmuje każdy krok, od struktury tabeli po formuły i alerty. Przedstawia również pełny przykład – magazyn materiałów biurowych – oraz szybszy sposób na zbudowanie arkusza na podstawie eksportu danych. Kroki pochodzą ze stron pomocy technicznej Microsoft, stan na 18 listopada 2026 r.

Co robi arkusz do śledzenia zapasów

Arkusz do śledzenia zapasów odpowiada na trzy pytania w dowolnym momencie:

  • Ile mamy każdego przedmiotu? Jest to stan zapasów pod ręką.
  • Ile to jest warte? Jest to wartość zapasów, oparta na koszcie jednostkowym.
  • Co musimy zamówić? Są to przedmioty na poziomie lub poniżej ich punktu ponownego zamówienia.

Dobry arkusz zachowuje również historię. Gdy liczba wydaje się błędna, możesz przewinąć dziennik ruchów i sprawdzić, kiedy towar został przyjęty lub wydany.

Arkusz do śledzenia zapasów w Excel sprawdza się dobrze w przypadku małych i średnich zasobów. Nadaje się do magazynków, małych sklepów, materiałów biurowych, części zamiennych i sprzętu eventowego. Gdy śledzisz tysiące przedmiotów w kilku lokalizacjach, dedykowany system zarządzania zapasami jest zazwyczaj lepszym rozwiązaniem.

Zaplanuj arkusz: dwie tabele

Zanim wpiszesz jakiekolwiek formuły, zdecyduj, jak będzie skonstruowany arkusz do śledzenia zapasów. Wiele prostych arkuszy używa jednej tabeli z pojedynczą kolumną ilości, którą użytkownicy nadpisują. To rozwiązanie szybko zawodzi. Nikt nie wie, kiedy i dlaczego liczba uległa zmianie.

Lepszy projekt oddziela listę przedmiotów od ruchów magazynowych:

Tabela Jeden wiersz na Główne kolumny
Przedmioty Produkt lub część ID przedmiotu, Nazwa, Kategoria, Koszt jednostkowy, Stan początkowy, Punkt ponownego zamówienia
Ruchy Zmiana stanu Data, ID przedmiotu, Typ (Przyjęcie lub Wydanie), Ilość, Notatka

Tabela Przedmioty zawiera fakty, które rzadko się zmieniają. Tabela Ruchy rośnie każdego dnia. Formuły w tabeli Przedmioty odczytują tabelę Ruchy i obliczają bieżący stan zapasów.

Taki projekt ma trzy zalety. Nigdy nie nadpisujesz liczby. Każda zmiana ma datę. Możesz też później odpowiedzieć na pytania, np. ile towaru wydano w marcu.

Sprawia to również, że arkusz do śledzenia zapasów jest bezpieczniejszy przy udostępnianiu. Personel dodaje tylko wiersze do tabeli Ruchy. Tabela Przedmioty, wraz z jej formułami, może pozostać zablokowana.

Umieść każdą tabelę na osobnym arkuszu. Nazwij arkusze „Przedmioty” i „Ruchy”, aby formuły były łatwe do odczytania.

Zbuduj listę przedmiotów jako tabelę

Zacznij od arkusza Przedmioty. Wpisz nagłówki w wierszu 1: ID przedmiotu, Nazwa, Kategoria, Koszt jednostkowy, Stan początkowy i Punkt ponownego zamówienia. Następnie wprowadź po jednym wierszu dla każdego przedmiotu.

Następnie zamień ten zakres w tabelę Excel. Microsoft wyjaśnia dlaczego: „Aby ułatwić zarządzanie grupą powiązanych danych i ich analizowanie, możesz zamienić zakres komórek w tabelę Excel”. Aby ją utworzyć:

  1. Zaznacz dowolną komórkę w danych.
  2. Wybierz Narzędzia główne > Formatuj jako tabelę.
  3. Wybierz styl tabeli.
  4. Upewnij się, że pole wyboru nagłówków jest zaznaczone, a następnie wybierz OK.

Nazwij tabelę „Przedmioty” na karcie Projektowanie tabeli. Zrób to samo dla dziennika ruchów na drugim arkuszu i nazwij go „Ruchy”.

Tabele pomagają na dwa sposoby. Rosną automatycznie po dodaniu wierszy. Formuły w kolumnie tabeli wypełniają się samoczynnie w dół. Microsoft twierdzi, że formuła wprowadzona w jednej komórce „jest natychmiast stosowana do wszystkich pozostałych komórek w tej kolumnie tabeli”.

Używaj krótkich i unikalnych ID przedmiotów, takich jak A-101. Używaj ich wszędzie zamiast nazw, ponieważ nazwy bywają zapisywane z błędami.

Dodaj listy rozwijane do dziennika ruchów

Błędy w pisowni są głównym powodem rozbieżności w stanach magazynowych. Lista rozwijana eliminuje większość z nich.

Microsoft wyjaśnia cel w jednym zdaniu: „Użyj sprawdzania poprawności danych, aby ograniczyć typ danych lub wartości wprowadzane przez użytkowników do komórki, na przykład do listy rozwijanej”. Dla kolumny Typ:

  1. Zaznacz kolumnę Typ w tabeli Ruchy.
  2. Wybierz Dane > Poprawność danych.
  3. Na karcie Ustawienia, w polu Dozwolone, wybierz Lista.
  4. W polu Źródło wpisz Przyjęcie,Wydanie.
  5. Upewnij się, że opcja Rozwijana lista w komórce jest zaznaczona, a następnie wybierz OK.

Zrób to samo dla kolumny ID przedmiotu, ale jako źródło wskaż kolumnę ID przedmiotu z tabeli Przedmioty. Teraz pracownicy mogą wybrać tylko przedmiot, który istnieje.

Listy rozwijane przyspieszają również wprowadzanie danych. Pracownik magazynu może zarejestrować dostawę w kilka sekund, wybierając przedmiot i typ, a następnie wpisując ilość.

Możesz również ograniczyć kolumnę Ilość. W polu Dozwolone wybierz Liczba całkowita i ustaw minimum na 1. To zablokuje puste i ujemne wpisy.

Oblicz stan zapasów za pomocą SUMIFS

Teraz dodaj kolumny obliczeniowe do tabeli Przedmioty. Funkcja SUMIFS dodaje liczby spełniające więcej niż jeden warunek. Składnia Microsoft to:

SUMIFS(suma_zakres, kryteria_zakres1, kryteria1, [kryteria_zakres2, kryteria2], ...)

Dodaj kolumnę o nazwie „Przyjęcie towaru”. W jej pierwszej komórce wpisz:

=SUMIFS(Ruchy[Ilość],Ruchy[ID przedmiotu],[@[ID przedmiotu]],Ruchy[Typ],"Przyjęcie")

To zsumuje każdą ilość w tabeli Ruchy, gdzie ID przedmiotu zgadza się z wierszem, a typem jest Przyjęcie. Tabela automatycznie skopiuje formułę dla każdego przedmiotu.

Dodaj kolumnę „Wydanie towaru” z tą samą formułą, zmieniając „Przyjęcie” na „Wydanie”.

Następnie dodaj „Stan pod ręką”:

=[@[Stan początkowy]]+[@[Przyjęcie towaru]]-[@[Wydanie towaru]]

Na koniec dodaj „Wartość zapasów”:

=[@[Stan pod ręką]]*[@[Koszt jednostkowy]]

Po zbudowaniu formuł sprawdź ręcznie jeden przedmiot. Przefiltruj tabelę Ruchy dla tego przedmiotu, zsumuj wiersze Przyjęcie i Wydanie, a następnie porównaj wynik z kolumną Stan pod ręką.

Oznacz przedmioty do ponownego zamówienia

Arkusz do śledzenia zapasów jest najbardziej użyteczny, gdy mówi Ci, co masz zrobić. Dodaj kolumnę „Status” z tą formułą:

=IF([@[Stan pod ręką]]<=[@[Punkt ponownego zamówienia]],"Zamów","OK")

Teraz każdy wiersz wyświetla Zamów lub OK. Ustal punkty ponownego zamówienia na podstawie czasu oczekiwania na dostawę i zużycia w tym okresie.

Wyróżnij wiersze Zamów za pomocą formatowania warunkowego:

  1. Zaznacz kolumnę Status.
  2. Wybierz Narzędzia główne > Formatowanie warunkowe > Reguły wyróżniania komórek > Tekst zawierający.
  3. Wpisz Zamów i wybierz kolor wypełnienia, np. jasnoczerwony.
  4. Wybierz OK.

Microsoft zauważa, że gdy pojawi się okno dialogowe Tekst zawierający, „wprowadzasz opcję formatowania, którą chcesz zastosować, i klikasz OK”. Wyróżnienie aktualizuje się za każdym razem, gdy zmienia się stan zapasów.

Możesz również sortować lub filtrować kolumnę Status, aby zobaczyć tylko wiersze Zamów przed złożeniem zamówienia.

Wyszukuj szczegóły przedmiotu za pomocą XLOOKUP

Dziennik ruchów pokazuje ID przedmiotów, które są trudne do odczytania. Kolumna wyszukiwania dodaje nazwę przedmiotu obok każdego ruchu.

Microsoft opisuje XLOOKUP jako sposób na „znalezienie rzeczy w tabeli lub zakresie według wiersza”. Jego składnia to:

XLOOKUP(szukana_wartość, przeszukiwana_tablica, zwracana_tablica, [jeśli_nie_znaleziono], [tryb_dopasowania], [tryb_wyszukiwania])

Dodaj kolumnę „Nazwa” do tabeli Ruchy i wpisz:

=XLOOKUP([@[ID przedmiotu]],Przedmioty[ID przedmiotu],Przedmioty[Nazwa],"Nieznane ID")

Jeśli ktoś wprowadzi ID, które nie istnieje, komórka pokaże „Nieznane ID”. Dzięki temu błędy są łatwe do zauważenia.

Microsoft zauważa, że „funkcja XLOOKUP nie jest dostępna w Excel 2016 i Excel 2019”. W tych wersjach użyj funkcji INDEKS i PODAJ.POZYCJĘ lub WYSZUKAJ.PIONOWO z ID przedmiotu w pierwszej kolumnie.

Dodaj podsumowanie na górze

Ostatnim elementem arkusza do śledzenia zapasów jest podsumowanie. Krótkie zestawienie pomaga każdemu zobaczyć stan zapasów bez przewijania. Dodaj kilka komórek nad tabelą Przedmioty lub na osobnym arkuszu „Podsumowanie”.

Użyj tych formuł:

  • Całkowita wartość zapasów: =SUM(Przedmioty[Wartość zapasów])
  • Przedmioty do zamówienia: =COUNTIF(Przedmioty[Status],"Zamów")
  • Jednostki wydane w tym miesiącu: =SUMIFS(Ruchy[Ilość],Ruchy[Typ],"Wydanie",Ruchy[Data],">="&DATE(2026,11,1))

Funkcja COUNTIF działa według prostego schematu. Według słów Microsoft, pyta ona: „Gdzie chcesz szukać?” oraz „Czego chcesz szukać?”.

Możesz również dodać wykres słupkowy wartości zapasów według kategorii. Zaznacz zakres podsumowania i wybierz Wstawianie > Wykres. Podsumowanie powinno być małe i zawierać od trzech do pięciu kluczowych liczb.

Praktyczny przykład: magazyn materiałów biurowych

Oto pełny przykład użycia arkusza do śledzenia zapasów. Małe biuro śledzi pięć rodzajów materiałów. Tabela Przedmioty przed listopadem wygląda tak:

ID przedmiotu Nazwa Koszt jednostkowy Stan początkowy Punkt ponownego zamówienia
A-101 Notes A5 $2.50 120 50
A-102 Długopisy (pudełko) $6.00 40 20
B-201 Papier do drukarki (ryza) $4.20 80 30
B-202 Toner $45.00 12 5
C-301 Pudełko wysyłkowe (średnie) $0.80 300 100

W listopadzie tabela Ruchy rejestruje następujące zmiany. Notesy: 85 wydano i 50 przyjęto. Długopisy: 25 wydano. Papier: 60 wydano i 40 przyjęto. Toner: 9 wydano. Pudełka: 180 wydano.

Po przeliczeniu formuł kolumny obliczeniowe pokazują:

ID przedmiotu Przyjęcie Wydanie Stan pod ręką Wartość zapasów Status
A-101 50 85 85 $212.50 OK
A-102 0 25 15 $90.00 Zamów
B-201 40 60 60 $252.00 OK
B-202 0 9 3 $135.00 Zamów
C-301 0 180 120 $96.00 OK

Przykład pokazuje również, dlaczego dziennik ruchów jest ważny. Papier wygląda dobrze przy 60 ryzach, ale dziennik pokazuje, że 60 wydano w ciągu jednego miesiąca. Jeśli to tempo się utrzyma, papier osiągnie punkt ponownego zamówienia w ciągu kilku tygodni. Pojedyncza kolumna ilości ukryłaby ten trend.

Sprawdź jeden wiersz ręcznie. Notesy zaczęły się od 120, przybyło 50, a ubyło 85, więc 120 + 50 − 85 = 85. To zgadza się z kolumną Stan pod ręką.

Podsumowanie pokazuje całkowitą wartość zapasów wynoszącą $785.50 i 2 przedmioty do ponownego zamówienia. Długopisy i toner są poniżej swoich punktów ponownego zamówienia, więc trafiają do zamówienia w tym tygodniu.

Stwórz arkusz do śledzenia zapasów w Excel z AI

Ręczne budowanie arkusza zajmuje godzinę lub dwie. Najwolniejszą częścią są często dane: czyszczenie eksportu z systemu sprzedażowego, dopasowywanie nazw przedmiotów i naprawianie duplikatów ID.

Narzędzie AI może wykonać to czyszczenie i analizę za Ciebie. Oto metoda w trzech krokach przy użyciu Powerdrill Bloom.

Krok 1: Prześlij swój plik Excel, CSV lub PDF

Prześlij swój eksport stanów magazynowych, listę dostawców lub arkusz spisu z zeszłego miesiąca. Excel AI Assistant pracuje bezpośrednio z plikami arkuszy kalkulacyjnych.

Dołącz zarówno listę przedmiotów, jak i ruchy, jeśli je masz, nawet w oddzielnych plikach.

Przesyłanie eksportu zapasów w celu stworzenia arkusza do śledzenia zapasów w Excel z Powerdrill Bloom

Krok 2: Poproś o arkusz w języku naturalnym

Opisz, czego potrzebujesz. Na przykład: „Zbuduj arkusz do śledzenia zapasów ze stanem pod ręką, wartością zapasów i flagą ponownego zamówienia dla przedmiotów na poziomie 20 jednostek lub poniżej”. Poproś o listę przedmiotów, które wymagają zamówienia w tym tygodniu.

Możesz również zadawać pytania uzupełniające, na przykład która kategoria zamraża najwięcej gotówki.

Krok 3: Przejrzyj wyniki i wyeksportuj je

Sprawdź stany pod ręką dla kilku przedmiotów w porównaniu z ostatnim spisem. Następnie wyeksportuj wyczyszczoną tabelę z powrotem do Excel lub zamień ustalenia w krótki raport lub slajdy dla swojego zespołu.

Jest to przydatne, gdy arkusz do śledzenia zapasów ma zaległości. Miesięczny eksport z systemu sprzedaży może pokazać, co naprawdę zostało wydane, co możesz porównać z dziennikiem.

Zachowaj projekt z dwiema tabelami do codziennych aktualizacji. Używaj kroku z AI, gdy potrzebujesz szybkiego czyszczenia danych lub miesięcznego przeglądu.

Przeglądanie stanu zapasów pod ręką przed eksportem arkusza do śledzenia zapasów

Typowe błędy, których należy unikać

Większość problemów z arkuszem do śledzenia zapasów wynika ze sposobu wprowadzania danych, a nie z formuł. Oto problemy, które najczęściej pojawiają się w arkuszach Excel do śledzenia zapasów.

  • Nadpisywanie ilości. Zamiast tego rejestruj każdą zmianę jako nowy ruch.
  • Nazwy zamiast ID. „Długopisy” i „Pudełko długopisów” stają się dwoma różnymi przedmiotami. Używaj unikalnych ID.
  • Brak stanu początkowego. Bez liczby początkowej każda wartość stanu pod ręką będzie błędna.
  • Mieszane jednostki. Śledź albo pudełka, albo pojedyncze sztuki, a nie oba naraz dla tego samego przedmiotu.
  • Brak regularnych spisów. Porównuj arkusz z fizycznym spisem co miesiąc lub co kwartał.
  • Nieuaktualniane punkty ponownego zamówienia. Aktualizuj je, gdy zmienia się zużycie lub czas dostawy u dostawcy.
  • Usuwanie wierszy dziennika. Nigdy nie usuwaj ruchu, aby naprawić błąd. Zamiast tego dodaj wpis korygujący z notatką.
  • Nadpisywanie formuł. Chroń arkusz Przedmioty, aby nikt nie zastąpił formuły liczbą.

Aby znaleźć przedmioty, które zbyt długo leżą na półce, zobacz nasz przewodnik jak rozpoznać wolnozbywalne zapasy. W celu prognozowania popytu sprawdź naszą listę narzędzi AI do prognozowania zapasów i popytu.

Gdy Twoje dane o zapasach znajdują się w nieuporządkowanym eksporcie, wypróbuj Powerdrill Bloom, aby wyczyścić je i zamienić w gotowy arkusz.

Często zadawane pytania

Jak stworzyć prosty arkusz do śledzenia zapasów w Excel?

Stwórz tabelę Przedmioty z ID przedmiotu, nazwą, kosztem jednostkowym, stanem początkowym i punktem ponownego zamówienia. Dodaj tabelę Ruchy, która rejestruje każdą zmianę. Następnie użyj funkcji SUMIFS, aby obliczyć przyjęcia, wydania i stan zapasów pod ręką.

Jaka formuła oblicza stan zapasów pod ręką w Excel?

Stan zapasów pod ręką równa się stan początkowy plus przyjęcia minus wydania. Użyj SUMIFS, aby zsumować ilości Przyjęcie i Wydanie dla każdego przedmiotu z dziennika ruchów.

Jak sprawić, by Excel oznaczał niski stan zapasów?

Dodaj kolumnę Status z formułą IF, która zwraca „Zamów”, gdy stan pod ręką jest na poziomie lub poniżej punktu ponownego zamówienia. Następnie wyróżnij te komórki za pomocą formatowania warunkowego.

Jak często należy aktualizować arkusz do śledzenia zapasów?

Rejestruj każdy ruch w momencie jego wystąpienia lub przynajmniej raz dziennie. Następnie porównuj arkusz z fizycznym spisem co miesiąc lub co kwartał i naprawiaj wszelkie luki za pomocą wpisu korygującego.

Czy mogę śledzić zapasy w Excel dla wielu lokalizacji?

Tak. Dodaj kolumnę Lokalizacja do tabeli Ruchy i dodaj Lokalizację jako drugi warunek w funkcji SUMIFS. Każdy przedmiot otrzyma wtedy wartość stanu pod ręką dla danej lokalizacji.

Źródła: Microsoft Support, Omówienie tabel programu Excel · Microsoft Support, Stosowanie sprawdzania poprawności danych w komórkach · Microsoft Support, Funkcja SUMIFS · Microsoft Support, Funkcja XLOOKUP · Microsoft Support, Korzystanie z formatowania warunkowego do wyróżniania informacji w programie Excel · Microsoft Support, Korzystanie z funkcji COUNTIF w programie Microsoft Excel.