EXCEL-DƏ ABC ANALIZI

Send

Əsas idarəetmə və logistika metodlarından biri ABC analizidir. Onun köməyi ilə müəssisənin resurslarını, mallarını, müştərilərini və s. Təsnif edə bilərsiniz. əhəmiyyət dərəcəsinə görə. Eyni zamanda, əhəmiyyət səviyyəsinə görə yuxarıda göstərilən bölmələrin hər biri üç kateqoriyadan birinə verilir: A, B və ya C. Excel bu cür təhlil aparmağı asanlaşdıran baqaj alətlərindədir. Onlardan necə istifadə edəcəyimizi və ABC analizini nədən ibarət olduğunu anlayaq.

ABC Analizindən istifadə

ABC analizi bir növ təkmilləşdirilmiş və Pareto prinsipinin müasir şərtlər versiyasına uyğunlaşdırılmışdır. Həyata keçirmə metodologiyasına görə, analizin bütün elementləri əhəmiyyət dərəcəsinə görə üç kateqoriyaya bölünür:

Kateqoriya A - məcmu çox olan elementlər 80% xüsusi çəkisi;
Kateqoriya B - birləşməsi olan elementlər 5% əvvəl 15% xüsusi çəkisi;
Kateqoriya C - qalan birləşmələr, bunların ümumi birləşməsidir 5% və daha az xüsusi çəkisi.

Bəzi şirkətlər daha inkişaf etmiş üsullar tətbiq edir və elementləri 3 və ya 4 və ya 5 qrupa bölür, ancaq klassik ABC analiz sxeminə etibar edəcəyik.

Metod 1: çeşidləmə təhlili

Excel-də ABC təhlili çeşidləmə istifadə olunur. Bütün maddələr ən böyüyündən ən böyüyünə qədər sıralanır. Sonra, hər bir elementin kumulyativ xüsusi çəkisi hesablanır, bunun əsasında müəyyən bir kateqoriyanın təyin olunduğu bildirilir. Müəyyən bir nümunədən istifadə edərək bu texnikanın praktikada necə tətbiq olunduğunu öyrənək.

Şirkətdə satdığı malların siyahısı və müəyyən bir müddət ərzində satışından əldə olunan gəlir miqdarı olan bir cədvəlimiz var. Cədvəlin altındakı bütün mallar üzrə ümumi gəlir vurulur. ABC analizindən istifadə edərək, bu məhsulları müəssisə üçün əhəmiyyətinə görə qruplara bölməkdir.

Başlıq və son sıra xaricində sol siçan düyməsini tutaraq məlumat kursoru ilə cədvəl seçin. Nişana keçin "Məlumat". Düyməni vurun. "Sort"alətlər blokunda yerləşir Sırala və süzün lentdə.
Ayrıca edə bilərsiniz. Cədvəlin yuxarıdakı aralığını seçin, sonra nişana keçin "Ev" düyməsini basın Sırala və süzünalətlər blokunda yerləşir "Redaktə" lentdə. Bir mövqe seçdiyimiz bir siyahı aktivləşdirilir. Xüsusi Sırala.

Yuxarıda göstərilən hərəkətlərdən birini tətbiq edərkən çeşidləmə parametrləri pəncərəsi işə salınır. Parametr ətrafında belə baxırıq "Məlumatlarımda başlıqlar var" onay işareti qoyulub. Olmadığı təqdirdə quraşdırın.
Sahədə Sütun gəlir məlumatlarını ehtiva edən sütunun adını göstərin.
Sahədə "Sort" çeşidlənmənin hansı konkret kriteriya ilə aparılacağını göstərməlisiniz. Əvvəlcədən təyin edilmiş parametrləri tərk edirik - "Dəyərlər".
Sahədə "Sifariş" bir mövqe qoydu "Düşən".
Göstərilən parametrləri etdikdən sonra düyməni vurun "OK" pəncərənin altındakı.

Göstərilən hərəkəti etdikdən sonra, bütün elementlər ən böyükdən ən böyüyünə qədər gəlirlə sıralandı.

İndi cəmi üçün elementlərin hər birinin xüsusi çəkisini hesablamalıyıq. Bu məqsədlər üçün əlavə bir sütun yaradırıq, onu çağıracağıq "Xüsusi cazibə". Bu sütunun ilk kamerasına bir işarə qoyun "=", bundan sonra müvafiq məhsulun satışından əldə olunan gəlir məbləğinin yerləşdiyi hücrəyə bir keçid göstəririk. Sonra bölmə işarəsini qoyun ("/") Bundan sonra, müəssisə daxilində mal satışının ümumi miqdarını ehtiva edən hücrənin koordinatlarını göstərin.
Göstərilən düsturu sütundakı digər hüceyrələrə köçürəcəyimizi nəzərə alaraq "Xüsusi cazibə" doldurma işarəsini istifadə edərək, müəssisə üçün ümumi gəlirin məbləğini ehtiva edən elementə keçidin ünvanını düzəltməliyik. Bunu etmək üçün linki mütləq edin. Düsturda göstərilən hücrənin koordinatlarını seçin və düyməni basın F4. Koordinatların qarşısında, gördüyümüz kimi, bir bağlantının mütləq hala gəldiyini göstərən bir işarə göründü. Qeyd etmək lazımdır ki, siyahıdakı ilk maddənin gəlir dəyərinə keçid (Məhsul 3) nisbi olaraq qalmalıdır.
Sonra hesablamalar aparmaq üçün düyməni vurun Daxil edin.

Gördüyünüz kimi, siyahıda göstərilən ilk məhsuldan əldə olunan gəlir nisbəti hədəf xanada göstərildi. Düsturu aşağıdakı diapazona kopyalamaq üçün, kursoru hücrənin aşağı sağ küncünə qoyun. Kiçik bir xaça bənzəyən bir doldurma işarəsinə çevrilir. Sol siçan düyməsini vurun və doldurma işarəsini sütunun sonuna qədər sürükləyin.

Gördüyünüz kimi, bütün sütun hər bir məhsulun satışından əldə olunan gəlir payını xarakterizə edən məlumatlarla doldurulur. Ancaq xüsusi çəkisi ədədi bir formatda göstərilir və onu bir faizə çevirməliyik. Bunu etmək üçün sütunun tərkibini seçin "Xüsusi cazibə". Sonra nişana keçirik "Ev". Parametrlər qrupundakı lentdə "Nömrə" Məlumat formatını göstərən bir sahə var. Varsayılan olaraq, əlavə manipulyasiyalar etməmisinizsə, format orada qurulmalıdır "Ümumi". Bu sahənin sağında yerləşən üçbucaq şəklində işarəni vururuq. Açılan formatlar siyahısında mövqeyi seçin "Faiz".

Gördüyünüz kimi, bütün sütun dəyərləri faiz dəyərlərinə çevrildi. Gözlənildiyi kimi, uyğun olaraq "Cəmi" göstərilib 100%. Malların nisbətinin daha böyükdən kiçikə qədər sütunda olacağı gözlənilir.

İndi yığılmış cəmi olan yığılmış payın göstəriləcəyi bir sütun yaratmalıyıq. Yəni, hər cərgədə müəyyən bir məhsulun xüsusi çəkisi yuxarıda göstərilən siyahıda yer alan bütün məhsulların xüsusi çəkisini əlavə edəcəkdir. Siyahının ilk maddəsi üçün (Məhsul 3) fərdi xüsusi çəkisi və yığılmış pay bərabər olacaq, lakin bütün sonrakılar üçün siyahının əvvəlki elementinin toplanmış payını fərdi göstəriciyə əlavə etmək lazımdır.
Beləliklə, ilk sırada sütuna keçirik Yığılmış pay sütun göstəricisi "Xüsusi cazibə".

Sonra, kursoru sütundakı ikinci hücrəyə qoyun. Yığılmış pay. Burada düsturu tətbiq etməliyik. Bir işarə qoyduq bərabərdir və hüceyrənin tərkibini əlavə edin "Xüsusi cazibə" eyni sıra və hüceyrə məzmunu Yığılmış pay yuxarıdakı xəttdən. Bütün əlaqələri nisbi olaraq tərk edirik, yəni onları manipulyasiya etmirik. Bundan sonra düyməni vurun Daxil edin son nəticəni göstərmək üçün.

İndi bu düsturu aşağıda yerləşən bu sütunun hücrələrinə köçürməlisiniz. Bunu etmək üçün, sütundakı düsturu kopyalayarkən artıq müraciət etdiyimiz doldurma işarəsindən istifadə edin "Xüsusi cazibə". Bu vəziyyətdə, xətt "Cəmi" ələ keçirməyə ehtiyac yoxdur, çünki yığılmış nəticə 100% siyahıdakı sonuncu elementdə göstərilir. Gördüyünüz kimi sütununuzun bütün elementləri bundan sonra dolduruldu.

Bundan sonra bir sütun yaradırıq "Qrup". Məhsulları kateqoriyalara qruplaşdırmalıyıq A, B və C göstərilən yığılmış paya görə. Yadımızdadır, bütün elementlər aşağıdakı sxemə görə qruplara bölünür:
- A - üçün 80%;
- B - aşağıdakı 15%;
- İlə - qalan 5%.
Beləliklə, bütün mallar üçün xüsusi çəkisinin toplanmış payı, bu vaxta qədər sərhədə daxil edilir 80%kateqoriya təyin edin A. Müəyyən bir çəkisi olan mallar 80% əvvəl 95% kateqoriya təyin edin B. Daha çox dəyəri olan qalan məhsullar qrupu 95% yığılmış xüsusi çəki təyin kateqoriyası C.

Aydınlıq üçün bu qrupları fərqli rənglərlə doldura bilərsiniz. Ancaq bu isteğe bağlıdır.

Beləliklə, ABC analizindən istifadə edərək elementləri əhəmiyyət səviyyəsinə görə qruplara ayırdıq. Yuxarıda qeyd edildiyi kimi bəzi digər texnikalardan istifadə edərkən daha çox sayda qrupa bölünmə tətbiq olunur, lakin parçalanma prinsipi demək olar ki, dəyişməz qalır.

Dərs: Excel çeşidlənməsi və süzülməsi

Metod 2: kompleks bir formul istifadə edin

Əlbətdə, çeşidləmə istifadəsi, Excel-də ABC analizini aparmaq üçün ən yaygın bir yoldur. Ancaq bəzi hallarda, orijinal cədvəldəki satırları dəyişdirmədən bu təhlili aparmaq lazımdır. Bu vəziyyətdə mürəkkəb bir formula xilasetmə üçün gələcəkdir. Bir nümunə olaraq, ilk vəziyyətdə olduğu kimi eyni mənbə cədvəlindən istifadə edəcəyik.

Malların adı və onların hər birinin satışından əldə edilən gəlirlər olan bir sütuna əlavə edin "Qrup". Gördüyünüz kimi, bu vəziyyətdə fərdi və məcmu hissələrin hesablanması ilə sütun əlavə edə bilmərik.

Sütundakı ilk hücrəni seçin "Qrup"sonra düyməni vurun "Funksiya daxil et"düsturlar xəttinin yaxınlığında yerləşir.

Aktivləşdirmə davam edir Funksiya sehrbazları. Kateqoriyaya keçirik İstinadlar və seriallar. Funksiyanı seçin "Seçim". Düyməni vurun "OK".

Funksiya dəlil pəncərəsi aktivdir. SEÇİM. Onun sintaksisi aşağıdakı kimi təqdim olunur:
= SELECT (İndeks_Nömrə; Dəyər1; Dəyər2; ...)
Bu funksiyanın məqsədi, indeks nömrəsindən asılı olaraq göstərilən dəyərlərdən birini çıxarmaqdır. Dəyərlərin sayı 254-ə çata bilər, ancaq ABC analizinin kateqoriyalarına uyğun yalnız üç ad lazımdır: A, B, İlə. Dərhal sahəyə girə bilərik "Dəyər1" simvolu "A"sahəsində "Dəyər2" - "B"sahəsində "Dəyər3" - "C".

Ancaq bir mübahisə ilə İndeks nömrəsi bir neçə əlavə operatoru birləşdirərək onunla yaxşı tanış olmaq lazımdır. Kursoru sahəyə qoyun İndeks nömrəsi. Sonra, düymənin solundakı üçbucaq şəklində işarəni vurun "Funksiya daxil et". Bu yaxınlarda istifadə olunan operatorların siyahısı açılır. Bizə bir funksiya lazımdır AXIR. Siyahıda olmadığından yazını vurun "Digər xüsusiyyətlər ...".

Pəncərə yenidən işə başlayır. Funksiya sehrbazları. Yenə kateqoriyaya keçirik İstinadlar və seriallar. Orada bir mövqe tapın "AXTAR", onu seçin və düyməni vurun "OK".

Operator Arqument Pəncərəsi Açılır AXIR. Onun sintaksisi aşağıdakı kimidir:
= AXTARIŞ (axtarılan_ qiymətləndirmə; Görülən_arrayış; Uyğunluq növü)
Bu funksiyanın məqsədi göstərilən elementin mövqe nömrəsini təyin etməkdir. Yəni, sahə üçün nə lazımdırsa İndeks nömrəsi funksiyaları SEÇİM.
Sahədə Array baxdı Dərhal aşağıdakı ifadəni göstərə bilərsiniz:
{0:0,8:0,95}
Bir sıra düsturu kimi buruq mötərizədə olmalıdır. Bu rəqəmlərin olduğunu təxmin etmək çətin deyil (0; 0,8; 0,95) qruplar arasındakı yığılmış payın sərhədlərini göstərir.
Sahə Uyğunluq növü isteğe bağlıdır və bu vəziyyətdə onu doldurmayacağıq.
Sahədə "Dəyər axtarır" kursoru təyin edin. Sonra yuxarıya çəkilən üçbucaq şəklində yuxarıdakı piktoqram vasitəsilə yenidən Xüsusiyyət sehrbazdır.

Bu dəfə Funksiya ustası kateqoriyaya keçin "Riyazi". Bir ad seçin YOLLAR düyməsini basın "OK".

Funksiya dəlil pəncərəsi başlayır YOLLAR. Göstərilən operator müəyyən bir şərtə cavab verən hücrələri yığır. Onun sintaksisi:
= SUMMES (sıra; meyar; cəmi_rəng)
Sahədə "Menzil" sütun ünvanını daxil edin "Gəlir". Bu məqsədlər üçün kursoru sahəyə qoyun və sonra sol siçan düyməsini tutaraq dəyər istisna olmaqla müvafiq sütundakı bütün hüceyrələri seçin "Cəmi". Gördüyünüz kimi, adres dərhal sahədə göstərildi. Bundan əlavə, bu əlaqəni mütləq etməliyik. Bunu etmək üçün onu seçin və düyməni basın F4. Ünvan dollar əlamətləri ilə fərqləndi.
Sahədə "Meyar" bir şərt qoymalıyıq. Aşağıdakı ifadəni daxil edirik:
">"&
Bundan dərhal sonra sütunun ilk kamerasının ünvanını daxil edirik "Gəlir". Məktubun qarşısında klaviaturadan bir dollar işarəsi əlavə edərək bu ünvanda üfüqi koordinatları mütləq edirik. Şaquli koordinatları nisbətən tərk edirik, yəni rəqəmin qarşısında heç bir işarə olmamalıdır.
Bundan sonra düyməni basmayın "OK", və funksiyanın adını vurun AXIR düstur çubuğunda.

Sonra funksiya arqument pəncərəsinə qayıdırıq AXIR. Gördüyünüz kimi sahədə "Dəyər axtarır" operator tərəfindən təyin olunan məlumatlar ortaya çıxdı YOLLAR. Ancaq bu hamısı deyil. Bu sahəyə gedin və mövcud məlumatlara işarəni əlavə edin. "+" kotirovkalar olmadan. Sonra sütunun ilk kamerasının ünvanını daxil edirik "Gəlir". Yenə də bu əlaqənin üfüqi koordinatlarını mütləq edirik və şaquli nisbi qoyuruq.
Sonra, sahənin bütün məzmununu götürün "Dəyər axtarır" mötərizədə, sonra bölmə işarəsi qoyuruq ("/") Bundan sonra yenidən üçbucaq işarəsi vasitəsi ilə funksiya seçimi pəncərəsinə gedin.

Son dəfə qaçışda olduğu kimi Funksiya ustası kateqoriyasında istədiyiniz operator axtarır "Riyazi". Bu dəfə istədiyiniz funksiya çağırılır SUM. Onu seçin və düyməni vurun. "OK".

Operator Arqument Pəncərəsi Açılır SUM. Onun əsas məqsədi hüceyrələrdəki məlumatları ümumiləşdirməkdir. Bu ifadənin sintaksisi olduqca sadədir:
= SUM (Say1; Sayı2; ...)
Məqsədlərimiz üçün yalnız bir sahə lazımdır "Sayı1". Sütun aralığının koordinatlarını daxil edin. "Gəlir"cəmi olan hücrədən başqa. Artıq sahədə oxşar əməliyyat keçirdik "Menzil" funksiyaları YOLLAR. O dövrdə olduğu kimi, aralıqların koordinatlarını seçib düyməni basaraq mütləq edirik F4.
Bundan sonra düyməni vurun "OK" pəncərənin altındakı.

Gördüyünüz kimi, təqdim olunan funksiyalar kompleksi hesablamanı həyata keçirdi və nəticəni sütunun ilk kamerasına qaytardı "Qrup". İlk məhsul bir qrup təyin edildi "A". Bu hesablama üçün istifadə etdiyimiz tam formula aşağıdakı kimidir:
= SEÇİN (AXTARIŞ ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C")
Ancaq əlbəttə ki, hər bir vəziyyətdə bu düsturdakı koordinatlar fərqli olacaqdır. Buna görə də universal hesab edilə bilməz. Ancaq yuxarıda göstərilən təlimatlardan istifadə edərək istənilən masanın koordinatlarını daxil edə və istənilən vəziyyətdə bu metodu uğurla tətbiq edə bilərsiniz.

Ancaq bu hamısı deyil. Hesablamanı yalnız cədvəlin ilk sıra üçün etdik. Tamamilə məlumatları olan bir sütunu doldurmaq üçün "Qrup", bu düsturu (sıra xanası istisna olmaqla) aşağıdakı aralığa köçürməlisiniz "Cəmi") doldurma işarəsini istifadə edərək, bir dəfədən çox etdiyimiz kimi. Məlumatlar daxil olduqdan sonra ABC təhlili başa çatmış hesab edilə bilər.

Gördüyünüz kimi, mürəkkəb bir düsturdan istifadə edərək seçim nəticəsində əldə edilən nəticələr çeşidləmə ilə həyata keçirdiyimiz nəticələrdən heç də fərqlənmir. Bütün məhsullar eyni kateqoriyalara təyin olunur, lakin satırlar başlanğıc mövqeyini dəyişməyib.

Dərs: Excel xüsusiyyət sihirbazı

Excel istifadəçi üçün ABC analizini çox asanlaşdıra bilər. Buna çeşidləmə kimi bir vasitə istifadə edərək nail olur. Bundan sonra fərdi xüsusi çəkisi, yığılmış pay və əslində qruplara bölünməsi hesablanır. Cədvəldə satırların ilkin vəziyyətinin dəyişdirilməsinə icazə verilmədiyi hallarda, kompleks bir formul istifadə edərək metodu tətbiq edə bilərsiniz.

Send