Analiza ABC/XYZ w Excelu

W firmach produkcyjnych każdego dnia przewija się masa danych. Zamówienia, stany magazynowe, sprzedaż, plan produkcji, dostawy… Można od tego dostać zawrotu głowy. A przecież ktoś musi nad tym zapanować.

I tu wchodzi na scenę analiza ABC/XYZ w Excelu – narzędzie, które wygląda niepozornie, a potrafi naprawdę zrobić różnicę. To metoda, która pozwala nie tylko poukładać informacje o produktach czy materiałach, ale też podejmować lepsze decyzje – szybciej i pewniej.

Firmy, które nauczyły się korzystać z tej analizy, zwykle zauważają dwie rzeczy. Po pierwsze – spadają koszty zapasów, bo magazyn nie pęka w szwach od niepotrzebnych materiałów. Po drugie – łatwiej reagować na zmiany rynkowe, bo wiadomo, co jest naprawdę ważne, a co można odłożyć na bok.

Spis treści:

1. Dlaczego analiza ABC/XYZ jest tak ważna?
2. Gdzie firmy się potykają? Typowe problemy z Excelem.
3. Jak przygotować Excela do analizy?
4. Analiza ABC krok po kroku.
5. Analiza XYZ w praktyce.
6. Typowe problemy przy analizie.
7. Praktyczne wskazówki.
8. Popraw przepływ danych w swojej firmie, dzięki analizie ABC/XYZ. Sprawdź odcinek: Analiza ABC/XYZ – jak zrobić w Excelu

Dlaczego analiza ABC/XYZ jest tak ważna?

Nie ma co ukrywać – w produkcji każdy produkt i każdy materiał mają inne znaczenie. Jedne są absolutnie kluczowe. Bez nich linia staje, klient się denerwuje, a dział sprzedaży dostaje lawinę telefonów. Inne? Są mniej istotne, a mimo to często zajmują miejsce w magazynie i generują koszty.

Analiza ABC/XYZ pozwala to poukładać.

• Klasa A – to „święta trójca” Twojej produkcji. Produkty i materiały, które odpowiadają za większość wartości sprzedaży czy przychodów. Ich brak = poważne kłopoty.
  
• Klasa B – średniacy. Ważne, ale nie aż tak krytyczne. Trzeba je monitorować, ale zwykle nie są aż tak wrażliwe.
  
• Klasa C – drobnica. Mają mały udział w sprzedaży, a mimo to potrafią zająć sporo przestrzeni i pochłonąć kapitał.
  

Do tego dochodzi XYZ – druga warstwa, która mówi o stabilności popytu:

• X – produkty przewidywalne, które sprzedają się w stałym rytmie.
  
• Y – umiarkowanie zmienne, czasem górka, czasem dołek.
  
• Z – całkowita loteria, duże wahania, sezonowość albo kaprysy rynku.
  

Połączenie obu analiz daje pełny obraz. Na przykład A-X to produkty kluczowe i stabilne – musisz je mieć zawsze. Ale już C-Z? To sygnał ostrzegawczy: niski udział w sprzedaży i ogromna zmienność. Magazynowanie ich to koszt, którego często lepiej uniknąć.

Gdzie firmy się potykają? Typowe problemy z Excelem

Brzmi pięknie, ale praktyka bywa trudniejsza. Excel jest świetnym narzędziem, ale jeśli pracujesz z dużymi plikami, wiesz, że potrafi napsuć krwi.

Najczęstsze bolączki:

• arkusze ważą tyle, że liczą się w minutach, nie sekundach,
  
• makra blokują część funkcjonalności, zamiast pomagać,
  
• co chwilę wyskakuje żółty pasek „Zawartość zewnętrzna”,
  
• Power Query bombarduje pytaniami o prywatność danych.
  

Każdy, kto choć raz próbował przeanalizować duży plik z danymi produkcyjnymi, zna to uczucie: „Excel znowu myśli…”. I właśnie dlatego warto zacząć od przygotowania środowiska, zanim przejdziemy do analizy.

Jak przygotować Excela do analizy?

Zanim przejdziesz do samej analizy ABC/XYZ, warto najpierw zadbać o narzędzie. Excel w wersji „fabrycznej” jest ustawiony bardzo ostrożnie – chroni użytkownika przed potencjalnymi zagrożeniami. Problem w tym, że w środowisku firmowym te zabezpieczenia częściej przeszkadzają, niż faktycznie chronią.

Dlatego dobrze jest poświęcić kilka minut na konfigurację. To inwestycja, która zwróci się błyskawicznie – bo zamiast klikać ostrzeżenia i komunikaty, od razu skupisz się na pracy.

1. Wyłącz widok chroniony

Każdy, kto otwiera pliki z maila albo firmowego intranetu, zna to uczucie. Chcesz coś szybko sprawdzić, a Excel zamiast otworzyć arkusz, serwuje żółty pasek: „Ten plik jest w trybie chronionym”. Klikasz „Włącz edytowanie”. Minutę później – kolejny plik, kolejny komunikat. Po kilkunastu takich akcjach masz ochotę wyłączyć komputer.

Na szczęście da się to ustawić raz na zawsze:

1. Kliknij Plik → Opcje → Centrum zaufania.
   
2. Wejdź w Ustawienia Centrum zaufania → Widok chroniony.
   
3. Odznacz opcje blokujące pliki z internetu i załączniki.
   

Gotowe. Od tej pory Excel traktuje Twoje arkusze jak normalne pliki, a nie potencjalne bomby wirusowe. Praca staje się płynna, a Ty nie tracisz rytmu przez głupie komunikaty.

2. Odblokuj makra

Makra to małe programy, które potrafią załatwić za Ciebie nudną, powtarzalną robotę. Mogą automatycznie przypisywać klasy ABC/XYZ, generować raporty czy odświeżać dane. Problem w tym, że Excel z góry zakłada: „makro = zagrożenie”, i blokuje je domyślnie.

Jeśli Twoja firma korzysta z makr, ustaw to raz, żeby nie tracić czasu:

• W Centrum zaufania przejdź do Ustawienia makr.
  
• Zaznacz Włącz wszystkie makra (z powiadomieniem) – dzięki temu będziesz widział, kiedy makro działa, ale nie będziesz musiał go odblokowywać ręcznie.
  
• Koniecznie zaznacz też opcję Zaufanie do dostępu do modelu obiektów VBA – to ułatwia pracę bardziej zaawansowanym makrom.
  

Efekt? Arkusz z makrami otwiera się i działa od razu. Zero klikania, zero nerwów, a Twoja analiza ABC/XYZ przebiega szybciej.

3. Pozbądź się żółtego paska od zawartości zewnętrznej

Kolejna przeszkoda to słynny żółty pasek z komunikatem: „Ostrzeżenie dotyczące zawartości zewnętrznej”. Pojawia się, gdy plik łączy się z innymi źródłami danych – na przykład systemem ERP, innym arkuszem czy bazą SQL.

W produkcji to absolutna codzienność. Dane o zamówieniach, zapasach czy sprzedaży zwykle spływają z różnych systemów. Dlatego ciągłe klikanie „Zezwól” to tylko strata czasu.

Rozwiązanie jest proste:

1. Wejdź w Plik → Opcje → Centrum zaufania → Zawartość zewnętrzna.
   
2. Wybierz opcję Zezwalaj na automatyczne łączenie z danymi zewnętrznymi.
   

Od tej pory Excel sam wie, że te połączenia są bezpieczne. Pliki odświeżają się automatycznie, a Ty nie musisz pilnować kolejnych komunikatów.

4. Ustaw prywatność w Power Query

Power Query to potężne narzędzie – pozwala łączyć dane z wielu źródeł i błyskawicznie je przekształcać. Problem? Jeśli nie ustawisz odpowiednio poziomów prywatności, Excel zacznie zasypywać Cię pytaniami: „Czy na pewno chcesz połączyć te dane?”. Po piątym takim okienku człowiek ma ochotę zamknąć cały plik.

Żeby tego uniknąć:

1. Otwórz Power Query i wejdź w Plik → Opcje i ustawienia → Ustawienia źródeł danych.
   
2. Dla źródeł, których używasz w firmie, ustaw poziom Publiczny lub Organizacyjny.
   
3. Zapisz zmiany i odśwież zapytania.
   

I koniec kłopotów. Dane łączą się płynnie, raporty odświeżają się jednym kliknięciem, a Ty możesz skupić się na analizie zamiast na potwierdzaniu każdej czynności.

Analiza ABC krok po kroku

Krok 1. Zbierz dane

Bez dobrych danych nawet najlepsza analiza nie ma sensu. Dlatego na początek przygotuj porządną tabelę. Powinna zawierać co najmniej:

• listę wszystkich produktów lub materiałów,
  
• ilości sprzedanych jednostek albo wartość sprzedaży,
  
• opcjonalnie dodatkowe kolumny, np. koszty zakupu czy marżę.
  

Brzmi banalnie, ale to właśnie tutaj popełnia się najwięcej błędów. Jeden brakujący wpis albo literówka w nazwie produktu i wyniki potrafią rozjechać się kompletnie.

Dlatego warto poświęcić chwilę na oczyszczenie danych. Usuń duplikaty, sprawdź czy wszystkie wartości są w jednej walucie, popraw formatowanie (np. zamiast „1 000” i „1000” trzymaj się jednej wersji). Może się wydawać, że to strata czasu, ale wierz mi – lepiej spędzić 15 minut na sprzątaniu, niż później godzinę zastanawiać się, dlaczego suma sprzedaży nie zgadza się z raportem księgowości.

Krok 2. Policz udziały i przypisz klasy

Teraz przechodzimy do serca analizy. Twoim zadaniem jest sprawdzenie, które produkty generują największą część wartości sprzedaży.

Krok po kroku wygląda to tak:

1. Zsumuj całkowitą sprzedaż – policz, ile łącznie wyniosła sprzedaż wszystkich pozycji.
   
2. Policz udział procentowy – podziel sprzedaż pojedynczego produktu przez sumę całkowitą. W ten sposób wiesz, jaki procent całości daje dany produkt.
   
3. Dodaj kolumnę z sumą skumulowaną – Excel sam pokaże Ci, jak te udziały „nakładają się” na siebie.
   

I teraz najważniejsze: klasyfikacja.

• Klasa A – produkty, które łącznie odpowiadają za ok. 70% wartości sprzedaży. To absolutny fundament biznesu.
  
• Klasa B – kolejne 20%. Ważne, ale nie krytyczne.
  
• Klasa C – cała reszta, czyli produkty, które mają znaczenie marginalne.
  

Prosta zasada 70/20/10 działa świetnie w praktyce. Po tym etapie już dokładnie wiesz, co jest naprawdę kluczowe, a co tylko „zapychaczem magazynu”.

Krok 3. Zrób wizualizację

Same liczby są ważne, ale to dopiero obraz sprawia, że wyniki analizy stają się zrozumiałe i użyteczne.

Najlepsze rozwiązania?

• Wykres słupkowy – pokaże, które produkty dominują.
  
• Kolorowanie tabeli – zaznacz klasę A na czerwono, B na żółto, C na zielono. Dzięki temu od razu widać priorytety.
  
• Tabela podsumowująca – kilka linijek na dole z informacją: ile produktów w każdej klasie, ile procent wartości sprzedaży obejmują, jaka jest średnia marża.
  

To właśnie w tym momencie analiza zaczyna żyć. Zamiast patrzeć na kolumny cyfr, Twój zespół widzi klarowny obraz: które produkty są strategiczne, które wymagają uwagi, a które można ograniczyć bez większych konsekwencji.

Dobrze przygotowana wizualizacja to też świetny materiał na spotkania zespołowe – nie trzeba wtedy tłumaczyć skomplikowanych formuł, bo całość mówi sama za siebie.

Analiza ABC

Analiza XYZ w praktyce

Krok 1. Oblicz zmienność

Tutaj chodzi o to, żeby sprawdzić, jak stabilny jest popyt. Najprościej policzyć odchylenie standardowe lub współczynnik zmienności (odchylenie ÷ średnia).

Krok 2. Podziel na kategorie

• X – stabilne, zmienność < 10%.
  
• Y – średnia zmienność, 10–25%.
  
• Z – wysokie wahania, powyżej 25%.
  

Dzięki temu wiesz, które produkty są przewidywalne, a które zachowują się jak rollercoaster.

Krok 3. Połącz z analizą ABC

I tu zaczyna się magia. Łączysz obie analizy i dostajesz matrycę, która mówi wprost:

• A-X – absolutny priorytet, trzymaj zawsze pod ręką.
  
• C-Z – potencjalna pułapka, lepiej ograniczać do minimum.
  

Typowe problemy przy analizie

• Wolne pliki – chyba każdy, kto pracował w Excelu na dużych zestawach danych, zna ten ból. Plik otwiera się minutę, przewijanie tnie jak stary komputer, a każda zmiana formuły kończy się czekaniem z kawą w ręku. Najczęściej winna jest nadmiarowa liczba skomplikowanych formuł, zagnieżdżonych funkcji albo brak optymalizacji. Excel daje radę naprawdę długo, ale kiedy ma policzyć tysiące wierszy z kilkoma warunkami – zwalnia. I to mocno.
  
• Makra psują arkusz – z jednej strony są wybawieniem, bo automatyzują nudną robotę. Z drugiej – jeśli są źle napisane, potrafią zamienić plik w koszmar. Zablokowane przyciski, brak możliwości edycji, dziwne błędy przy uruchamianiu… brzmi znajomo? To sygnał, że ktoś „na szybko” skopiował kod z internetu, zamiast go dostosować.
  
• Power Query pyta w kółko o prywatność – niby drobiazg, a potrafi człowieka doprowadzić do szału. Za każdym razem ten sam komunikat: „Określ poziom prywatności”. Jeśli nie ustawisz tego globalnie, każda aktualizacja zapytań przerywa rytm pracy i wytrąca z koncentracji.

I tutaj warto dodać jedno: Excel ma swoje granice. Da się te problemy obejść, zoptymalizować, naprawić. Ale jeśli próbujesz traktować go jak pełnoprawny system ERP – w pewnym momencie zacznie protestować.

Praktyczne wskazówki

• Optymalizuj pliki – mniej znaczy więcej. Usuń zbędne formatowanie warunkowe (zwłaszcza to, które sprawia, że każdy wiersz liczy się osobno). Zamiast pracować na „gołych” zakresach, używaj tabel – są szybsze, czytelniejsze i łatwiej je aktualizować. Czasem drobna zmiana w strukturze arkusza skraca czas obliczeń o połowę.
  
• Automatyzuj makrami – ale rób to z głową. Proste makro, które przypisuje klasy ABC/XYZ, generuje raporty czy odświeża dane jednym kliknięciem, to ogromna oszczędność czasu. Nie chodzi o pisanie tysięcy linijek kodu – czasem wystarczy kilka prostych instrukcji, żeby zamiast 30 minut, praca zajęła 30 sekund.
  
• Ustaw politykę bezpieczeństwa – brzmi poważnie, ale w praktyce chodzi o kilka kliknięć. Dodaj firmowe katalogi do „zaufanych lokalizacji”, ustaw domyślny poziom prywatności w Power Query i zdecyduj, co zrobić z makrami (blokować wszystkie czy dopuszczać w określonych przypadkach). Dzięki temu Excel przestaje Cię zasypywać ostrzeżeniami i pozwala pracować bez ciągłego „zezwalaj/nie zezwalaj”.
  

To małe rzeczy, niby detale. Ale kiedy pomnożysz je przez setki plików i kilkunastu użytkowników w firmie, okazuje się, że oszczędzasz dziesiątki godzin miesięcznie. A to czas, który można poświęcić na analizę danych, a nie na walkę z Excelem.