Analiza XYZ za pomocą MS Excel

Wprowadzenie do analizy XYZ

Analiza XYZ to jedno z narzędzi wykorzystywanych w zarządzaniu zapasami, które pozwala na klasyfikację produktów lub materiałów według stopnia zmienności ich zapotrzebowania w czasie. Dzięki tej metodzie możliwe jest lepsze zrozumienie, które pozycje magazynowe charakteryzują się stabilnym zużyciem, a które wykazują nieregularności, co ma bezpośredni wpływ na podejmowanie decyzji zakupowych, poziomów zapasów czy strategii logistycznych.

W odróżnieniu od analizy ABC, która grupuje zapasy ze względu na wartość zużycia, analiza XYZ koncentruje się na przewidywalności i regularności zapotrzebowania. To sprawia, że obie metody często są stosowane komplementarnie, dając bardziej kompleksowy obraz efektywności zarządzania stanami magazynowymi.

W ramach analizy XYZ produkty dzielone są zazwyczaj na trzy grupy:

• Grupa X – pozycje o bardzo stabilnym, przewidywalnym zużyciu,
• Grupa Y – pozycje o umiarkowanej zmienności zapotrzebowania,
• Grupa Z – pozycje o dużej nieregularności zapotrzebowania.

Klasyfikacja ta ułatwia lepsze dopasowanie strategii operacyjnych, takich jak częstotliwość zamówień, poziomy bezpieczeństwa zapasów czy wybór dostawców. Analiza XYZ jest szczególnie przydatna w środowiskach charakteryzujących się dużą liczbą pozycji magazynowych i zmiennym popytem.

Znaczenie i zastosowanie analizy XYZ w zarządzaniu zapasami

Analiza XYZ jest jednym z kluczowych narzędzi wspomagających zarządzanie zapasami w przedsiębiorstwach. Jej głównym celem jest klasyfikacja produktów lub materiałów na podstawie regularności zużycia lub popytu, co pozwala na bardziej precyzyjne planowanie dostaw, produkcji oraz optymalizację kosztów magazynowania.

W odróżnieniu od innych metod, takich jak analiza ABC, która skupia się głównie na wartości zużycia, analiza XYZ uwzględnia zmienność zapotrzebowania. Dzięki temu umożliwia efektywne rozróżnienie między pozycjami o stabilnym, przewidywalnym zużyciu (kategoria X), umiarkowanej zmienności (kategoria Y) oraz o bardzo nieregularnym zapotrzebowaniu (kategoria Z).

Temat tego artykułu pojawia się w niemal każdej sesji szkoleniowej Cognity – czasem w formie pytania, czasem w formie frustracji.

Główne zastosowania analizy XYZ w praktyce to m.in.:

• Planowanie zapasów: dopasowanie poziomów zapasów do stopnia przewidywalności popytu, co pozwala redukować ryzyko nadmiarów lub braków magazynowych.
• Optymalizacja zakupów: skupienie uwagi na bardziej przewidywalnych pozycjach przy planowaniu częstotliwości i wielkości zamówień.
• Wspomaganie decyzji strategicznych: klasyfikacja produktów według zmienności zapotrzebowania może ułatwiać decyzje dotyczące strategii utrzymywania zapasu bezpieczeństwa.
• Wsparcie w prognozowaniu: lepsze dopasowanie metod prognozowania do specyfiki danego asortymentu.

Ostatecznie analiza XYZ pozwala na zwiększenie efektywności operacyjnej w obszarze zarządzania zapasami, umożliwiając przedsiębiorstwom lepsze wykorzystanie zasobów i ograniczenie kosztów operacyjnych.

Rodzaje danych wymaganych do przeprowadzenia analizy XYZ

Aby skutecznie przeprowadzić analizę XYZ w Microsoft Excel, niezbędne jest przygotowanie odpowiednich danych historycznych dotyczących obrotu magazynowego. Analiza XYZ koncentruje się na ocenie stabilności i regularności zużycia towarów, dlatego kluczowe znaczenie ma jakość i kompletność zgromadzonych informacji liczbowych.

Podstawą analizy są dane ilościowe przedstawiające zużycie lub sprzedaż poszczególnych pozycji asortymentowych w kolejnych okresach. Najczęściej stosuje się dane miesięczne, jednak forma kwartalna lub tygodniowa również może być wykorzystywana, w zależności od specyfiki działalności.

Wymagane dane obejmują:

• Identyfikator towaru – unikalna nazwa lub kod produktu.
• Jednostka miary – np. sztuki, litry, kilogramy (opcjonalnie, dla kontekstu).
• Okresowe poziomy zużycia lub sprzedaży – liczby przedstawiające zapotrzebowanie w kolejnych okresach (np. 12 miesięcy).

Przykładowy układ danych wejściowych w Excelu może wyglądać następująco:

| Produkt | Sty | Lut | Mar | Kwi | Maj | Cze | Lip | Sie | Wrz | Paź | Lis | Gru |
|---------|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
| A123    | 120 | 115 | 118 | 121 | 119 | 122 | 118 | 116 | 117 | 120 | 119 | 121 |
| B234    |  80 |  60 | 100 |  90 | 110 |  70 | 105 |  95 |  85 |  75 |  90 | 100 |
| C345    |   5 |   0 |   8 |   2 |   0 |   6 |   1 |   0 |   3 |   2 |   0 |   4 |

Ostateczny wybór zakresu danych historycznych powinien uwzględniać typowość sezonową, dostępność danych oraz wymagany poziom dokładności analizy.

Warto również zaznaczyć, że dane powinny być możliwie nieprzerywane i pozbawione błędów – np. braków danych w poszczególnych miesiącach lub błędnych wartości zerowych, gdy sprzedaż faktycznie miała miejsce.

W kontekście pracy w Excelu, ważne jest, aby dane były w układzie tablicowym – uporządkowane w wierszach i kolumnach w sposób umożliwiający efektywne wykorzystanie funkcji takich jak ŚREDNIA, ODCH.STANDARDOWE oraz formuł do wyliczania współczynnika zmienności.

Prawidłowe przygotowanie danych stanowi fundament dla dalszych etapów analizy XYZ, pozwalając na rzetelne przypisanie produktów do odpowiednich kategorii X, Y lub Z. Jeśli chcesz pogłębić swoją wiedzę i jeszcze lepiej wykorzystać możliwości Excela w analizach, sprawdź nasze Kurs Microsoft Excel w analizach danych – zaawansowane opcje filtrowania, tworzenie tabeli przestawnej i makra oraz praca z wykresami.

Przygotowanie danych w Microsoft Excel

Przygotowanie danych jest kluczowym etapem poprzedzającym właściwą analizę XYZ. Microsoft Excel oferuje wszechstronne narzędzia do organizowania, czyszczenia i wstępnego przetwarzania danych, co umożliwia efektywne przeprowadzenie dalszych obliczeń i klasyfikacji. Na warsztatach Cognity wiele osób dopiero pierwszy raz zauważa, jak bardzo to zagadnienie wpływa na ich efektywność.

Na potrzeby analizy XYZ dane powinny zawierać historyczne informacje o zużyciu lub zapotrzebowaniu na produkty w określonym przedziale czasu (np. tygodniowo lub miesięcznie). Dane te powinny być ułożone w sposób umożliwiający łatwe agregowanie i analizowanie zmienności.

Przykładowa struktura danych

Struktura tabeli powinna być przejrzysta – każdy wiersz reprezentuje inny produkt, a kolumny przedstawiają jego zużycie w kolejnych okresach. Dzięki temu możliwe będzie późniejsze obliczanie średniej oraz odchylenia standardowego dla każdego produktu.

Najważniejsze kroki przygotowania danych

• Import danych: Dane mogą pochodzić z systemów ERP, plików CSV, baz danych lub być wprowadzane ręcznie. Excel umożliwia importowanie danych z różnych źródeł przy użyciu funkcji Power Query lub polecenia Dane → Z tekstu/CSV.
• Weryfikacja spójności: Należy usunąć lub uzupełnić brakujące wartości, sprawdzić poprawność wpisów liczbowych oraz zidentyfikować i usunąć ewentualne duplikaty.
• Standaryzacja jednostek: Wszystkie dane powinny być przedstawione w jednakowej jednostce (np. sztuki, litry), aby zapewnić poprawność obliczeń.
• Przekształcenie układu danych: W razie potrzeby dane można przetransponować lub zgrupować za pomocą tabel przestawnych (Power Pivot), by dostosować je do analiz statystycznych.

Przykładowa formuła pomocnicza

Aby sprawdzić, czy w danym zakresie znajdują się puste komórki, można użyć formuły:

=LICZ.PUSTE(B2:M2)

Jeśli wynik jest większy od zera, oznacza to, że produkt ma niekompletne dane i wymaga uzupełnienia lub wykluczenia z analizy.

Poprawnie przygotowane dane stanowią fundament analizy XYZ i umożliwiają dalsze kroki związane z obliczeniami statystycznymi oraz klasyfikacją produktów.

Obliczanie współczynnika zmienności w Excelu

Współczynnik zmienności (ang. coefficient of variation, CV) jest miarą statystyczną, która pozwala porównać stopień rozproszenia danych względem ich średniej. W kontekście analizy XYZ pełni kluczową rolę w klasyfikacji produktów według stabilności popytu. Im niższy współczynnik zmienności, tym bardziej przewidywalny jest popyt danego towaru. Jeśli chcesz pogłębić swoją wiedzę na temat pracy z danymi w Excelu, sprawdź Kurs Excel Masterclass - efektywne formuły, wykresy i analiza danych.

Aby obliczyć współczynnik zmienności w programie Microsoft Excel, konieczne jest posiadanie danych dotyczących zużycia (lub sprzedaży) danego produktu w kolejnych okresach (np. miesiącach). CV oblicza się jako stosunek odchylenia standardowego do średniej arytmetycznej, wyrażony zazwyczaj w procentach.

Formuła CV

CV = (Odchylenie standardowe / Średnia) × 100%

W Excelu można posłużyć się następującymi funkcjami:

• ŚREDNIA() – oblicza średnią arytmetyczną
• ODCH.STANDARDOWE.POP() lub ODCH.STANDARDOWE.S() – oblicza odchylenie standardowe (dla całej populacji lub próby)

Przykład dla danych w zakresie B2:M2 (wartości miesięczne jednego produktu):

=ODCH.STANDARDOWE.POP(B2:M2)/ŚREDNIA(B2:M2)*100

W zależności od specyfiki danych można wybrać funkcję populacyjną (ODCH.STANDARDOWE.POP) lub próbki (ODCH.STANDARDOWE.S). W analizie XYZ zwykle stosuje się odchylenie dla całej populacji, gdyż analizujemy kompletne dane historyczne.

Porównanie przykładowych danych

Analiza wartości CV umożliwia późniejsze przypisanie produktów do kategorii X, Y lub Z w zależności od poziomu zmienności, co zostanie omówione w kolejnych częściach artykułu.

Przypisywanie kategorii X, Y, Z na podstawie wyników analizy

Po obliczeniu współczynnika zmienności (CV) dla każdego indeksu materiałowego lub produktu, kolejnym krokiem analizy XYZ jest przypisanie poszczególnych pozycji do odpowiednich kategorii: X, Y lub Z. Każda z tych kategorii charakteryzuje się innym poziomem zmienności zużycia lub zapotrzebowania, co przekłada się na odmienne strategie zarządzania zapasami.

Kategoryzacja odbywa się najczęściej poprzez zastosowanie funkcji warunkowych w Excelu. Można to osiągnąć przykładowo za pomocą formuły:

=JEŻELI(CV<=0,1;"X";JEŻELI(CV<=0,25;"Y";"Z"))

W powyższej formule CV odnosi się do komórki zawierającej wyliczony współczynnik zmienności dla danego produktu. Wynikiem działania funkcji będzie przypisanie odpowiedniej litery oznaczającej kategorię XYZ.

Dzięki tej klasyfikacji możliwe jest szybkie zidentyfikowanie grupy produktów wymagających różnych podejść w planowaniu zapasów, co stanowi podstawę dalszej analizy i decyzji operacyjnych.

Interpretacja wyników i podejmowanie decyzji

Po przeprowadzeniu analizy XYZ w Excelu wyniki należy zinterpretować w kontekście zmienności zapotrzebowania na poszczególne produkty. Klasyfikacja produktów do kategorii X, Y lub Z pozwala na lepsze zrozumienie dynamiki zapasów i podejmowanie trafniejszych decyzji zarządczych.

Produkty z grupy X charakteryzują się stabilnym i przewidywalnym zużyciem. Dla nich warto stosować systemy planowania zapasów oparte na dokładnych prognozach oraz dążyć do minimalizacji poziomu zapasów przy zachowaniu wysokiej dostępności.

Grupa Y obejmuje produkty o umiarkowanej zmienności – ich popyt nie jest tak regularny jak w grupie X, ale nadal wykazuje pewne wzorce. W ich przypadku wskazane jest stosowanie bardziej elastycznych strategii zaopatrzenia oraz monitorowanie sezonowości lub trendów.

Produkty z grupy Z wykazują dużą nieregularność w zużyciu, co utrudnia prognozowanie. Dla tej grupy rekomenduje się podejście ostrożne – często stosuje się strategie typu „na żądanie” lub utrzymywanie minimalnych stanów magazynowych, aby uniknąć nadmiernych zapasów.

Na podstawie przypisania produktów do odpowiednich kategorii XYZ menedżerowie mogą podejmować decyzje dotyczące optymalizacji stanów magazynowych, częstotliwości zamówień, strategii zakupów czy priorytetów w planowaniu produkcji. Interpretacja wyników analizy XYZ staje się zatem fundamentem dla skutecznego zarządzania zapasami i redukcji kosztów operacyjnych.

Podsumowanie i najlepsze praktyki

Analiza XYZ to skuteczne narzędzie służące do klasyfikacji zapasów na podstawie ich stabilności zużycia w czasie. W przeciwieństwie do analizy ABC, która koncentruje się na wartości zużycia, analiza XYZ skupia się na zmienności popytu, co pozwala lepiej dostosować strategie zarządzania zapasami do rzeczywistych wzorców konsumpcji.

Zastosowanie analizy XYZ umożliwia przypisanie pozycji magazynowych do jednej z trzech kategorii – X, Y lub Z – w zależności od stopnia regularności popytu. Kategoria X obejmuje towary o przewidywalnym zużyciu, Y – o umiarkowanej zmienności, a Z – o nieregularnym, trudnym do przewidzenia zapotrzebowaniu. Dzięki takiemu podziałowi możliwe jest bardziej precyzyjne planowanie zakupów, optymalizacja poziomu zapasów oraz redukcja kosztów związanych z magazynowaniem.

W celu uzyskania rzetelnych wyników analizy XYZ, warto stosować się do kilku najlepszych praktyk:

• Regularna aktualizacja danych: Dane o zużyciu powinny być systematycznie uzupełniane i weryfikowane, aby analiza odzwierciedlała aktualne trendy.
• Spójność okresów pomiarowych: Przeprowadzając analizę, należy korzystać z tych samych jednostek czasowych (np. miesięcy), co umożliwia porównywalność wyników.
• Utrzymanie jednolitych formatów danych: Przejrzyste i jednolite struktury danych ułatwiają obróbkę w Excelu oraz pozwalają uniknąć błędów obliczeniowych.
• Uwzględnianie sezonowości: W przypadku towarów sezonowych warto rozważyć dodatkowe analizy, które pozwolą właściwie zinterpretować zmienność popytu.
• Integracja z innymi metodami: Analiza XYZ dostarcza informacji komplementarnych do innych narzędzi, takich jak analiza ABC, dzięki czemu możliwe jest stworzenie bardziej kompleksowego podejścia do zarządzania zapasami.

Stosowanie analizy XYZ w praktyce pozwala podejmować trafniejsze decyzje operacyjne i strategiczne w zakresie logistyki i zaopatrzenia, prowadząc do zwiększenia efektywności łańcucha dostaw i lepszego wykorzystania zasobów. W Cognity łączymy teorię z praktyką – dlatego ten temat rozwijamy także w formie ćwiczeń na szkoleniach.

Jak przygotować dane pod wydajne raportowanie w Power BI 08 marca 2026

Power Query vs DAX — co robić gdzie? Jasny podział ról 06 marca 2026