Zastosowanie analizy ABC w Microsoft Excel

Jedną z kluczowych metod zarządzania i logistyki jest analiza ABC. Za jego pomocą można klasyfikować zasoby przedsiębiorstwa, produktów, klientów itp. według stopnia ważności. Jednocześnie, zgodnie z poziomem ważności, każdej z powyższych jednostek przypisano jedną z trzech kategorii: A, B lub C. Excel ma w swoich narzędziach bagażowych, które ułatwiają przeprowadzenie tego rodzaju analizy. Zastanówmy się, jak z nich korzystać i jaka jest analiza ABC.

Korzystanie z analizy ABC

Analiza ABC jest rodzajem ulepszonego i dostosowanego do współczesnych warunków wariantu zasady Pareto. Zgodnie z metodologią jego działania, wszystkie elementy analizy są podzielone na trzy kategorie pod względem ważności:

• Kategoria A - elementy o łącznej masie większej niż 80% ciężaru właściwego;
• Kategoria B - elementy, których agregat wynosi od 5% do 15% ciężaru właściwego;
• Kategoria C - pozostałe elementy, których łączny agregat wynosi 5% i mniej niż masa własna.

Niektóre firmy stosują bardziej zaawansowane techniki i dzielą elementy nie na 3, lecz na 4 lub 5 grup, ale będziemy opierać się na klasycznym schemacie analizy ABC.

Metoda 1: analiza przez sortowanie

W programie Excel analiza ABC jest przeprowadzana przez sortowanie. Wszystkie elementy są sortowane z większych na mniejsze. Następnie oblicza się łączną masę właściwą każdego elementu, na podstawie którego przypisana jest określona kategoria. Dowiedzmy się na konkretnym przykładzie, jak ta technika jest stosowana w praktyce.

Mamy tabelę z listą towarów, które firma sprzedaje, i odpowiadającą im kwotę przychodów z ich sprzedaży przez określony czas. W dolnej części tabeli trafiają przychody z wszystkich towarów. Warto, stosując analizę ABC, podzielić te towary na grupy według ich znaczenia dla przedsiębiorstwa.

1. Wybierz tabelę z danym kursorem, przytrzymując lewy przycisk myszy, wykluczając nagłówek i ostatnią linię. Przejdź do zakładki "Dane" . Kliknij przycisk "Sortuj" znajdujący się w polu narzędzia "Sortuj i filtruj" na wstążce.

   Możesz także działać inaczej. Wybierz powyższy zakres tabeli, a następnie przejdź do zakładki "Strona główna" i kliknij przycisk "Sortuj i filtruj" znajdujący się w polu "Edycja" na wstążce. Aktywowana jest lista, w której wybieramy pozycję "Sortowanie niestandardowe" .

2. Po zastosowaniu dowolnej z powyższych czynności uruchomione zostanie okno sortowania. Zobaczmy, że opcja "Moje dane zawiera nagłówki" została zaznaczona. W przypadku jego nieobecności ustalamy.

   W polu "Kolumna" podajemy nazwę kolumny, która zawiera dane o przychodach.

   W polu "Sortuj" musisz określić konkretne kryteria sortowania. Opuszczamy ustawienia wstępne - "Wartości" .

   W polu "Zamówienie" ustawiamy pozycję "Malejąco" .

   Po wprowadzeniu określonych ustawień, kliknij przycisk "OK" w dolnej części okna.

3. Po wykonaniu określonej akcji wszystkie elementy zostały posortowane według przychodów od największego do mniejszego.
4. Teraz musimy obliczyć ciężar właściwy każdego z elementów dla całkowitej sumy. Dla tych celów tworzymy dodatkową kolumnę, którą nazwiemy "Ciężar właściwy" . W pierwszej komórce tej kolumny umieszczamy znak "=" , po czym podajemy link do komórki, w której znajduje się kwota przychodu ze sprzedaży odpowiedniego produktu. Następnie ustawiamy znak podziału ( "/" ). Następnie określ współrzędne komórki, która zawiera całkowitą kwotę sprzedaży towarów w całym przedsiębiorstwie.

   Biorąc pod uwagę fakt, że skopiujemy tę formułę do innych komórek w kolumnie "Ciężar właściwy" za pomocą znacznika wypełnienia, musimy poprawić adres łącza do elementu zawierającego całkowitą kwotę przychodów przez przedsiębiorstwo. W tym celu dokonujemy odniesienia absolutnego. Wybierz współrzędne określonej komórki w formule i naciśnij klawisz F4 . Przed pojawieniem się współrzędnych pojawił się znak dolara, który wskazuje, że link stał się bezwzględny. Należy zauważyć, że odniesienie do dochodów z pierwszego elementu na liście ( towary 3 ) powinno pozostać względne.

   Następnie, aby wykonać obliczenia, naciśnij przycisk Enter .

5. Jak widać, udział przychodów z pierwszego produktu wymienionego na liście był wyświetlany w komórce docelowej. Aby skopiować formułę do poniższego zakresu, umieść kursor w prawym dolnym rogu komórki. Przekształca się w znacznik wypełnienia, który wygląda jak mały krzyżyk. Kliknij lewym przyciskiem myszy i przeciągnij znacznik wypełnienia do końca kolumny.
6. Jak widać, cała kolumna jest wypełniona danymi charakteryzującymi konkretną wagę wpływów ze sprzedaży każdego produktu. Ale wartość ciężaru właściwego jest wyświetlana w formacie liczbowym i musimy ją przekształcić w procent. Aby to zrobić, wybierz zawartość kolumny "Ciężar właściwy" . Następnie przejdź do karty Strona główna . Na wstążce w grupie ustawień "Liczba" znajduje się pole wyświetlające format danych. Domyślnie, jeśli nie wykonano dodatkowych manipulacji, należy ustawić format "Ogólne" . Klikamy na ikonę w formie trójkąta, znajdującego się po prawej stronie tego pola. Na otwartej liście formatów wybierz pozycję "Odsetki" .
7. Jak widać, wszystkie wartości kolumn zostały przekonwertowane na wartości procentowe. Zgodnie z oczekiwaniami, linia "Razem" wskazuje 100% . Oczekuje się, że ciężar właściwy towarów będzie w kolumnie od największego do mniejszego.
8. Teraz musimy utworzyć kolumnę, w której zgromadzony udział zostanie wyświetlony z łączną sumą. Oznacza to, że w każdym wierszu konkretna waga wszystkich towarów, które znajdują się w powyższym wykazie, zostanie dodana do indywidualnej wagi właściwej określonego towaru. W przypadku pierwszego produktu na liście ( pozycja 3 ) indywidualny udział i zakumulowany udział będą równe, ale dla wszystkich następujących po indywidualnym wskaźniku konieczne będzie dodanie skumulowanej części poprzedniego elementu listy.

   Zatem w pierwszym wierszu przenieś do kolumny "Skumulowany udział" wskaźnik z kolumny "Ciężar właściwy" .

9. Następnie umieszczamy kursor w drugiej komórce kolumny "Skumulowany udział" . Tutaj musimy zastosować formułę. Umieszczamy znak "równości" i dodajemy zawartość komórki "Ciężar właściwy" z tego samego wiersza i zawartość komórki "Skumulowany udział" z powyższej linii. Wszystkie odnośniki są względne, tzn. Nie manipulujemy nimi. Następnie klikamy przycisk Enter , aby wyświetlić wynik końcowy.
10. Teraz musisz skopiować tę formułę do komórek tej kolumny, które znajdują się poniżej. Aby to zrobić, stosujemy marker wypełnienia, do którego już uciekliśmy podczas kopiowania wzoru w kolumnie "Ciężar właściwy" . W takim przypadku linia "Łącznie" nie jest wymagana do przechwycenia, ponieważ skumulowany wynik w 100% zostanie wyświetlony na ostatnim produkcie z listy. Jak widać, wszystkie elementy naszej kolumny zostały wypełnione po tym.
11. Następnie utwórz kolumnę "Grupa" . Będziemy musieli pogrupować towary według kategorii A , B i C według skumulowanego udziału. Jak pamiętamy, wszystkie elementy pogrupowano według następującego schematu:
    • A - do 80% ;
    • B - kolejne 15% ;
    • C - pozostałe 5% .

    W związku z tym wszystkim towarom, których łączna część ciężaru właściwego wchodzi na granicę do 80% , przypisuje się kategorię A. Towary o skumulowanej masie właściwej od 80% do 95% mają przypisaną kategorię B. Pozostała grupa towarów o wartości większej niż 95% łącznej masy jednostkowej otrzymuje kategorię C.

12. Dla jasności możesz wypełnić te grupy różnymi kolorami. Ale to jest do woli.

W ten sposób podzieliliśmy elementy na grupy według stopnia ważności, korzystając z analizy ABC. Korzystając z innych technik, jak wspomniano powyżej, zastosuj partycjonowanie do większej liczby grup, ale zasada partycjonowania pozostaje praktycznie niezmieniona.

Lekcja: Sortowanie i filtrowanie w Excelu

Metoda 2: Używanie złożonej formuły

Oczywiście użycie sortowania jest najczęstszym sposobem wykonywania analizy ABC w Excelu. Ale w niektórych przypadkach ta analiza jest wymagana bez zmiany wierszy w miejscach w tabeli źródłowej. W takim przypadku na ratunek przyjdzie złożona formuła. Na przykład użyjemy tej samej tabeli źródłowej, co w pierwszym przypadku.

1. Dodajemy do początkowej tabeli, zawierającą nazwę towarów i wpływy ze sprzedaży każdego z nich, kolumnę "Grupa" . Jak widać, w tym przypadku nie możemy dodawać kolumn z kalkulacją udziałów indywidualnych i akumulacyjnych.
2. Wybierz pierwszą komórkę w kolumnie "Grupa" , a następnie kliknij przycisk "Wstaw funkcję" obok paska formuły.
3. Aktywacja Kreatora funkcji zostanie wykonana . Przechodzimy do kategorii "Referencje i tablice" . Wybierz funkcję "SELECT" . Kliknij przycisk "OK" .
4. Okno argumentu funkcji WYBIERZ jest aktywne. Składnia jest następująca:

   =ВЫБОР(Номер_индекса;Значение1;Значение2;…)

   Zadaniem tej funkcji jest wyprowadzenie jednej z podanych wartości, w zależności od numeru indeksu. Liczba wartości może osiągnąć 254, ale potrzebujemy tylko trzech nazw, które odpowiadają kategoriom analizy ABC: A , B , C. Możemy od razu wpisać w polu "Wartość 1" symbol "A" , w polu "Wartość 2" - "B" , w polu "Wartość3" - "C" .

5. Ale z argumentem "Numer indeksu" będzie musiał dokładnie majstrować, mając wbudowany w to kilka dodatkowych operatorów. Ustaw kursor w polu "Numer indeksu" . Następnie kliknij ikonę, która wygląda jak trójkąt, po lewej stronie przycisku "Wstaw funkcję" . Wyświetlana jest lista ostatnio używanych operatorów. Potrzebujemy funkcji MATCH . Ponieważ lista nie jest, klikamy na napis "Inne funkcje ..." .
6. Okno kreatora funkcji zostanie uruchomione ponownie. Ponownie przejdź do kategorii "References and arrays . " Znajdziemy tam pozycję "SZUKAJ" , wybierz ją i kliknij przycisk "OK" .
7. Otwiera okno argumentów OPERATORA . Jego składnia jest następująca:

   =ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)

   Celem tej funkcji jest określenie numeru pozycji określonego elementu. To jest właśnie to, czego potrzebujemy w polu "Numer indeksu" funkcji SELECT .

   W polu "Skanowana tablica" możesz od razu ustawić następujące wyrażenie:

   {0:0,8:0,95}

   Musi być w nawiasach klamrowych, jak formuła tablicy. Nietrudno zgadnąć, że te liczby ( 0 ; 0,8 ; 0,95 ) oznaczają granice skumulowanego udziału między grupami.

   Pole "Typ dopasowania" jest opcjonalne iw tym przypadku nie będziemy go wypełniać.

   W polu "Wartość wyszukiwania" ustawiamy kursor. Następnie ponownie poprzez trójkąt przedstawiony powyżej, przechodzimy do Kreatora funkcji .

8. Tym razem w kreatorze funkcji przechodzimy do kategorii "Matematyczne" . Wybieramy nazwę "SUMMER" i klikamy przycisk "OK" .
9. Zostanie uruchomione okno argumentów funkcji SUMIFER . Podany operator podsumowuje komórki odpowiadające pewnemu warunkowi. Jego składnia jest następująca:

   =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

   W polu "Zakres" wpisz adres kolumny "Przychody" . W tym celu ustawiamy kursor na polu, a następnie, po zablokowaniu lewego przycisku myszy, zaznaczamy wszystkie komórki w odpowiedniej kolumnie, z wyłączeniem wartości "Razem" . Jak widać, adres był natychmiast wyświetlany w polu. Ponadto musimy uczynić ten link absolutnym. Aby to zrobić, dokonaj wyboru i kliknij klawisz F4 . Adres wyróżniał się znakami dolara.

   W polu "Kryteria" musimy określić warunek. Wprowadzamy następujące wyrażenie:

   ">"&

   Zaraz potem wpisujemy adres pierwszej komórki w kolumnie "Przychody" . Łącza poziome w tym adresie są bezwzględne, dodając znak dolara z klawiatury przed literą. Współrzędne w pionie są względne, tzn. Przed liczbą nie powinno być znaku.

   Po tym nie klikaj przycisku "OK" , ale kliknij nazwę funkcji MATCH na pasku formuły.

10. Następnie wracamy do okna argumentów funkcji MATCH . Jak widać, w polu "Wartość wyszukiwania" pojawiły się dane wprowadzone przez operatora SUMESELI . Ale to nie wszystko. Idź do tego pola i dodaj znak "+" bez cudzysłowów do istniejących danych. Następnie wpisujemy adres pierwszej komórki w kolumnie "Przychody" . I znowu robimy poziome współrzędne tego odniesienia absolutnego, a w pionie zostawiamy względne współrzędne.

    Następnie w nawiasach bierzemy całą zawartość pola "Żądana wartość" , po czym umieszczamy znak podziału ( "/" ). Następnie ponownie poprzez ikonę trójkąta przejdź do okna wyboru funkcji.

11. Podobnie jak w ostatnim uruchomionym kreatorze funkcji szukamy wymaganego operatora w kategorii "Matematyczne" . Tym razem pożądana funkcja nazywa się "SUMM" . Wybierz go i kliknij przycisk "OK" .
12. Otwiera okno argumentów operatora SUM . Jego głównym celem jest podsumowanie danych w komórkach. Składnia tego operatora jest dość prosta:

    =СУММ(Число1;Число2;…)

    Do naszych celów wymagane jest tylko pole "Numer 1" . Wprowadź współrzędne zakresu kolumny "Przychody" , z wyjątkiem komórki zawierającej sumy. Taką operację wykonaliśmy już w polu "Zakres" funkcji SUMMER . Podobnie jak w tym czasie, współrzędne zakresu są bezwzględne, wybierając je i naciskając klawisz F4 .

    Następnie kliknij przycisk "OK" w dolnej części okna.

13. Jak widać, zestaw funkcji wejściowych przeprowadził obliczenia i wyprowadził wynik do pierwszej komórki w kolumnie "Grupa" . Pierwszy produkt został przypisany do grupy "A" . Pełna formuła zastosowana do obliczeń jest następująca:

    =ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;">"&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});"A";"B";"C")

    Ale, oczywiście, w każdym konkretnym przypadku współrzędne w tym wzorze będą się różnić. Dlatego nie można go uznać za uniwersalny. Ale korzystając z instrukcji podanej powyżej, możesz wstawić współrzędne dowolnej tabeli i zastosować tę metodę w dowolnej sytuacji.

14. Jednak to nie wszystko. Obliczyliśmy tylko dla pierwszego wiersza tabeli. Aby całkowicie wypełnić kolumnę "Grupa" danymi, musisz skopiować tę formułę do poniższego zakresu (z wyłączeniem komórki linii "Łącznie" ) przy użyciu znacznika wypełnienia, tak jak zrobiliśmy to więcej niż raz. Po wprowadzeniu danych analizę ABC można uznać za spełnioną.

Jak widać, wyniki uzyskane za pomocą wariantu wykorzystującego złożoną formułę nie różnią się od wyników, które przeprowadziliśmy przez sortowanie. Wszystkie produkty mają przypisane te same kategorie, ale wiersze nie zmieniły swojej początkowej pozycji.

Lekcja: Kreator funkcji w programie Excel

Program Excel może znacznie ułatwić analizę ABC dla użytkownika. Osiąga się to za pomocą narzędzia, takiego jak sortowanie. Następnie obliczana jest indywidualna waga, łączny udział i w rzeczywistości grupowanie. W przypadkach, w których zmiana początkowej pozycji wierszy w tabeli jest niedozwolona, ​​można zastosować tę metodę za pomocą złożonej formuły.

• ← Korzystanie z funkcji SELECT w programie Microsoft Excel
• Ustaw dzień tygodnia według daty w programie Microsoft Excel →