Analiza ABC în Excel

Una dintre metodele-cheie de management și logistică este analiza ABC. Cu aceasta, puteți clasifica resursele întreprinderii, bunurilor, clienților etc. În funcție de gradul de importanță. În același timp, prin nivelul de importanță, fiecare dintre unitățile de mai sus este atribuită una dintre cele trei categorii: programul A, B sau C. Excel are în instrumentele sale de bagaje care facilitează efectuarea acestui tip de analiză. Să ne dăm seama cum să le folosim și care este analiza ABC.

Utilizarea analizei ABC.

Analiza ABC este un fel de îmbunătățire și adaptată condițiilor moderne pentru principiul Pareto. Conform metodei comportamentului său, toate elementele analizei sunt împărțite în trei categorii în funcție de gradul de importanță:

• Categoria A - Elemente care au o combinație de peste 80% din gravitate specifică;
• Categoria B - Elemente ale căror combinații variază de la 5% la 15% din gravitația specifică;
• Categoria C - elementele rămase, agregatul total al cărei gravitate este de 5% și mai puțin specifică.

Companiile separate utilizează tehnici mai avansate și împărtășesc obiecte nu la 3, dar cu 4 sau 5 grupuri, dar ne vom baza pe schema clasică de analiză ABC.

Metoda 1: Analiza cu sortarea

Analiza ABC Excel se efectuează utilizând sortarea. Toate articolele sunt sortate de la gras la mai puțin. Apoi, cota acumulată din fiecare element este calculată, pe baza căreia îi este atribuită o anumită categorie. Să aflăm cum este folosită metodologia specificată în practică.

Avem un tabel cu o listă de produse pe care compania le vinde și numărul corespunzător de venituri din vânzarea lor pentru o anumită perioadă de timp. În partea de jos a tabelului, rezultatul veniturilor în general pe toate numele mărfurilor. Există o sarcină care utilizează analiza ABC, sparge aceste bunuri în grupuri prin importanța lor pentru întreprindere.

1. Evidențiați o masă cu cursorul prin închiderea butonului stâng al mouse-ului, excluzând capacul și șirul final. Accesați fila "Date". Faceți clic pe butonul "Sortare", situat în bara de instrumente "Sortare și Filter" de pe bandă.

   

   De asemenea, puteți face diferit. Noi alocăm gama de masă de mai sus, apoi treceți la fila "Acasă" și faceți clic pe butonul "Sortare și filtru" situat în caseta de instrumente de editare de pe bandă. Lista este activată în care este activată poziția "sortarea personalizabilă".



2. Când aplicați oricare dintre acțiunile de mai sus, fereastra setărilor de sortare se lansează. Arătăm astfel încât "datele mele conținând parametrul anteturilor" a fost setat la un marcaj de verificare. În cazul absenței sale, instalați.

   În câmpul "coloană", specificați numele coloanei în care sunt conținute datele privind veniturile.

   În câmpul "Sortare", trebuie să specificați, pe care vor fi sortate criterii specifice. Lăsați setările presetate - "valori".

   În câmpul "Ordin", expuneți poziția "descendentă".

   După produsul setărilor specificate, faceți clic pe butonul "OK" din partea de jos a ferestrei.



3. După efectuarea acțiunii specificate, toate articolele au fost sortate prin venituri de la mai mult la un număr mai mic.



4. Acum ar trebui să calculăm proporția fiecăruia dintre elementele pentru un rezultat general. Creați o coloană suplimentară în aceste scopuri, pe care o numim "Greutate Share". În prima celulă din această coloană, am pus semnul "=", după care specificați o legătură cu celula în care este valoarea veniturilor din implementarea produsului relevant. Apoi, setați semnul diviziei ("/"). După aceasta, indicăm coordonatele celulei, care conține valoarea totală a vânzării de bunuri în întreaga întreprindere.

   Având în vedere faptul că formula specificată vom copia în alte celule ale coloanei "Share" de către markerul de umplere, adresa legăturii cu elementul care conține valoarea finală a veniturilor întreprinderii, trebuie să rezolvăm. Pentru a face acest lucru, faceți o legătură absolută. Selectați coordonatele celulei specificate în formula și apăsați tasta F4. Înainte de coordonate, după cum vedem, a apărut un semn de dolar, ceea ce indică faptul că legătura a devenit absolută. Trebuie remarcat faptul că trimiterea la valoarea veniturilor din prima în lista de bunuri (produsul 3) ar trebui să rămână relativă.

   Apoi, pentru a face calcule, apăsați butonul ENTER.



5. După cum vedem, proporția veniturilor din primul produs specificat în listă a apărut în celula țintă. Pentru a copia formula din intervalul de mai jos, punem cursorul în colțul din dreapta jos al celulei. Transformarea sa în marcatorul de umplere, având o privire de o cruce mică. Faceți clic pe butonul stâng al mouse-ului și trageți marcajul de umplere până la capătul coloanei.



6. După cum puteți vedea, întreaga coloană este completată cu date care caracterizează proporția veniturilor din implementarea fiecărui produs. Dar valoarea gravitației specifice este afișată în format numeric și trebuie să o transformăm în procente. Pentru a face acest lucru, evidențiați conținutul coloanei "Greutatea specifică". Apoi ne mutăm în fila "Acasă". Pe banda din grupul de setări de grup există un câmp care afișează formatul de date. În mod implicit, dacă nu ați produs manipulări suplimentare, formatul "General" trebuie instalat acolo. Faceți clic pe pictograma sub forma unui triunghi situat în partea dreaptă a acestui câmp. În lista de formate, selectați poziția "procentuală".



7. După cum vedem, toate valorile coloanei au fost transformate în valori procentuale. Așa cum ar trebui, 100% indicat în șirul de "total". Ponderea bunurilor este așteptată în coloană de la o mai mare la un nivel mai mic.



8. Acum ar trebui să creăm o coloană în care va fi afișată cota acumulată cu un rezultat tot mai mare. Aceasta este, în fiecare linie la greutatea specifică individuală a unui produs specific, se va adăuga proporția tuturor produselor care se află în lista de mai sus. Pentru primele mărfuri din listă (produsul 3), proporția individuală și cota acumulată vor fi egale, dar toate ulterioare indicatorului individual vor trebui să adauge cota acumulată a elementului anterior al listei.

   Deci, în prima linie suntem transferați în coloana "Cota acumulată" Indicatorul coloanei "specific".



9. Apoi, setați cursorul la cea de-a doua celulă a coloanei "Acoperition Share". Aici trebuie să aplicăm formula. Am pus semnul "egal" și pliabil conținutul celulei "proporția" aceleiași linii și conținutul celulei "cota acumulată" din șirul de mai sus. Toate referințele sunt rezervate rudă, adică nu producem niciun manipulare cu ei. După aceasta, efectuați un clic pe butonul ENTER pentru a afișa rezultatul final.



10. Acum trebuie să copiați această formulă în celulele acestei coloane, care sunt plasate mai jos. Pentru a face acest lucru, folosim marca de umplere la care am recurs deja la copierea formulei din coloana de acțiuni. În același timp, șirul "Total" nu este necesar, deoarece rezultatul acumulat este de 100% va fi afișat pe ultimul produs din listă. După cum puteți vedea, toate elementele coloanei noastre după care au fost umplute.



11. După aceea, creați un "grup" coloană. Va trebui să grupăm mărfurile pe categoria A, B și C în conformitate cu cota acumulată specificată. După cum ne amintim, toate elementele sunt distribuite de grupuri în conformitate cu următoarea schemă:
    • A - până la 80%;
    • B - următoarele 15%;
    • C - restul de 5%.

    Astfel, toate bunurile care au acumulat o parte din greutatea specifică este la granița de până la 80%, atribuim categoria A. mărfurile cu greutatea specifică acumulată de la 80% la 95% sunt atribuite categoriei B. Restul Grup de mărfuri cu valoare de peste 95% din gravitația specifică acumulată, atribuim o categorie C.



12. Pentru claritate, puteți completa grupurile specificate cu culori diferite. Dar este la voia.

Astfel, am rupt elementele cu privire la nivelul de importanță, folosind analiza ABC. Atunci când se utilizează alte tehnici, după cum sa menționat mai sus, este folosit pentru a intra într-un număr mai mare de grupuri, dar principiul ruperii în acest caz rămâne aproape neschimbat.

Lecţie: Sortarea și filtrarea în Excel

Metoda 2: Utilizarea unei formule complexe

Desigur, utilizarea de sortare este cea mai frecventă modalitate de a efectua analiza ABC în Excelență. Dar, în unele cazuri, este necesar să se efectueze această analiză fără rearanjarea liniilor în locurile din tabelul sursă. În acest caz, o formulă complexă va veni la salvare. De exemplu, vom folosi aceeași masă sursă ca în primul caz.

1. Adăugam la tabelul sursă care conține numele bunurilor și veniturile din vânzarea fiecăruia dintre ele, coloana "Grupul". După cum puteți vedea, în acest caz, este posibil să nu adăugăm coloane cu calculul fracțiilor individuale și acumulate.



2. Producem prima alocare celulară în coloana Grup, după care efectuați un clic pe butonul "Inserare Funcție" situat în apropierea rândului de formule.



3. Masterat activarea funcțiilor. Ne mutăm la categoria "Link-uri și matrice". Selectați funcția "Alegere". Facem un clic pe butonul "OK".



4. Fereastra de argument joc este activată. Sintaxa este prezentată după cum urmează:

   = Selecție (număr_intex, valoarea1, valoarea2; ...)

   Sarcina acestei caracteristici este retragerea uneia dintre valorile specificate, în funcție de numărul indexului. Numărul de valori poate ajunge la 254, dar vom avea nevoie doar de trei nume care corespund categoriilor de analiză ABC: A, B, C. Putem introduce imediat simbolul "A" în câmpul "B" din "b" "Câmp, câmp" valoare3 "-" c ".



5. Dar, cu argumentul "Numărul indexului" va trebui să fie bine fixat prin construirea mai multor operatori suplimentari. Instalați cursorul în câmpul "Număr index". Apoi, faceți clic pe pictograma cu o vedere a triunghiului, în partea stângă a butonului "Inserare". Se deschide o listă de operatori nou utilizați. Avem nevoie de o funcție a căutării. Deoarece nu este în listă, atunci vom face clic pe inscripția "Alte funcții ...".



6. Fereastra ferestrei Wizard Funcții este pornită din nou. Din nou, mergeți la categoria "Link-uri și matrice". Noi găsim poziția "plăcii de căutare", alocați-o și faceți un clic pe butonul "OK".



7. Argumentele argumentelor argumentelor operatorului de căutare se deschid. Sintaxa are următoarea formă:

   = Board de căutare (căutare_name, vizualizare__Massive; Type_station)

   Scopul acestei funcții este definirea numărului de poziție al elementului specificat. Asta este, exact ceea ce avem nevoie pentru câmpul "Număr de index" oferă o alegere.

   În câmpul "ARRAY ARRAY", puteți cere imediat următoarea expresie:

   {0: 0.8: 0,95}

   Ar trebui să fie tocmai în paranteze curat, ca o formulă de matrice. Nu este dificil să ghiciți că aceste numere (0; 0,8; 0,95) indică limitele cota acumulate între grupuri.

   Câmpul "tipul de comparație" nu este obligatoriu și în acest caz nu îl vom umple.

   În câmpul "a doua valoare", setați cursorul. Apoi, prin pictograma descrisă mai sus sub forma unui triunghi, ne mutăm la vrăjitorul funcțiilor.



8. De data aceasta în expertul de funcții, facem să ne mișcăm la categoria "matematică". Alegeți numele "Silent" și apăsați butonul "OK".



9. Va fi lansată fereastra argumentelor funcției. Operatorul specificat rezumă celulele care îndeplinesc condiția definită. Sintaxa sa este:

   = Silențios (interval; criteriu; gama_suming)

   În câmpul "Range", introduceți adresa coloanei "Venituri". În aceste scopuri, am stabilit cursorul în câmp și apoi făcând clipul butonului stâng al mouse-ului, selectați toate celulele coloanei corespunzătoare, excluzând valoarea "Total". După cum puteți vedea, adresa imediat a apărut în domeniu. În plus, trebuie să facem acest link absolut absolut. Pentru a face acest lucru, produceți alocarea și faceți clic pe tasta F4. Adresa a fost eliberată de semnele de dolar.

   În câmpul "Criterion", trebuie să stabilim o condiție. Introduceți următoarea expresie:

   ">"&

   Apoi imediat după ce intrăm la adresa primei celule a coloanei "venituri". Facem coordonate orizontal în această adresă absolută, adăugând semnul dolarului de la tastatură în fața scrisorii. Coordonatele verticale părăsesc relativ, adică, nu ar trebui să existe nici un semn în fața cifrei.

   După aceasta, nu apăsăm butonul "OK" și faceți clic pe numele funcției de căutare din șirul de formulare.



10. Apoi, revenim la fereastra argumentelor din bordul de căutare. După cum vedem, în câmpul "Semnificația Fogul", datele stabilite de operator au tăcut. Dar asta nu este totul. Mergeți la acest câmp și adăugați deja la datele disponibile pentru a adăuga un semn "+" fără ghilimele. Apoi introducem adresa primei celule a coloanei "venituri". Și din nou facem coordonate orizontal cu o rudă absolută și verticală.

    Apoi, luăm tot conținutul câmpului "valoarea dorită" în paranteze, după care am pus semnul diviziei ("/"). După aceea, din nou prin pictograma triunghiului, mergeți la funcția de selectare a funcțiilor.



11. Ca și ultima oară în Expertul de funcționare, căutăm operatorul necesar în categoria "matematică". De data aceasta funcția dorită se numește "Sums". O evidențiem și apăsați butonul "OK".



12. Se deschide fereastra argumentelor operatorului. Scopul său principal este sumarea datelor din celule. Sintaxa acestui operator este destul de simplă:

    = Sumele (numărul 1, numărul2; ...)

    Pentru scopurile noastre, va fi necesară numai câmpul "număr1". Introducem în ea coordonatele coloanei "Venituri", eliminând celula care conține rezultatele. Am efectuat deja o astfel de operație în câmpul "Range" al funcției. Ca și în acel moment, coordonatele gama fac absolut, evidențiind-le și apăsând tasta F4.

    După aceea, faceți clic pe tasta "OK" din partea de jos a ferestrei.



13. După cum puteți vedea, complexul funcțiilor introduse a făcut un calcul și a emis rezultatul în prima celulă a coloanei "Grup". Primul produs a fost atribuit unui grup "A". Formula completă aplicată de noi pentru acest calcul este după cum urmează:

    = Alegere (bord de căutare ((silențios ($ 2: $ b $ 27;> "& $ b2) + $ b2) / sume ($ B $ 2: $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")

    Dar, desigur, în fiecare caz, coordonatele în această formulă vor fi diferite. Prin urmare, nu poate fi considerată universală. Dar, folosind manualul care a fost dat mai sus, puteți introduce coordonatele oricărei tabele și puteți aplica cu succes această metodă în orice situație.



14. Cu toate acestea, acest lucru nu este totul. Am calculat numai pentru primul rând al mesei. Pentru a completa complet coloana grupului "Grup", trebuie să copiați această formulă în intervalul de mai jos (eliminând celula "Total") utilizând marca de umplere, așa cum am făcut deja mai multe o dată. După efectuarea datelor, analiza ABC poate fi luată în considerare efectuată.

După cum vedem, rezultatele obținute utilizând opțiunea utilizând o formulă complexă nu sunt diferite de acele rezultate pe care le-am efectuat prin sortare. Toate bunurile sunt atribuite acelorași categorii, numai în același timp rândurile nu și-au schimbat poziția inițială.

Lecţie: Maestru de funcții în Excelență

Programul Excel este capabil să atenueze în mod semnificativ analiza ABC pentru utilizator. Acest lucru este realizat prin utilizarea unui astfel de instrument ca sortarea. După aceasta, se calculează gravitatea specifică individuală, cota acumulată și, de fapt, partiționarea în grupuri. În cazurile în care modificarea poziției inițiale a rândurilor din tabel nu este permisă, puteți aplica metoda utilizând o formulă complexă.