İdarəetmə və logistika əsas üsullarından biri ABC analizi. Bununla birlikdə müəssisənin, məhsulun, müştərinin və s. Resurslarını təsnif edə bilərsiniz. əhəmiyyətinə görə. Eyni zamanda, əhəmiyyət səviyyəsinə görə, yuxarıda sadalanan vahidlərin hər biri üç kateqoriyadan birinə verilir: A, B və ya C. Excel bu cür təhlili yerinə yetirmək üçün əlverişli olan baqaj vasitələrinə malikdir. Onları necə istifadə edəcəyik və ABC analizi nədir.
ABC analizindən istifadə
ABC analizi Pareto prinsipinin müasir şərait variantlarına uyğunlaşdırılmış və təkmilləşdirilmiş bir növdür. Xülasə metodikasına əsasən təhlilin bütün elementləri vacib qaydada üç kateqoriyaya bölünür:
- Kateqoriya A - ortaq olan elementlər 80% xüsusi çəki;
- Kateqoriya B - məcmuəsi olan elementlər 5% qədər 15% xüsusi çəki;
- Kateqoriya C - ümumi məcmu olan qalan elementlər 5% və daha az xüsusi çəki.
Bəzi şirkətlər daha qabaqcıl üsullardan istifadə edirlər və elementləri 3-də deyil, 4-də və ya 5-də qruplara ayırırlar, lakin biz ABC analizinin klassik sxeminə əsaslanırıq.
Metod 1: çeşidlənməsi ilə təhlil
Excel-də ABC təhlili çeşidlənməsi ilə həyata keçirilir. Bütün əşyalar ən kiçikdən kiçiklərə qədər sıralanır. Sonra hər elementin məcmu xüsusi çəkisi hesablanır, bunun əsasında müəyyən bir kateqoriya təyin olunur. Bu texnikanın praktikada necə tətbiq edildiyini öyrənmək üçün xüsusi bir nümunə istifadə edək.
Şirkətimiz satan malların siyahısı və müəyyən bir müddətə satılan satış gəlirləri ilə cədvəlimiz var. Cədvəlin altındakı ümumi gəlir malın bütün maddələrinə görə yekunlaşdırılır. Vəzifə bu məhsullar şirkət üçün əhəmiyyətinə görə qruplara bölmək üçün ABC analizindən istifadə etməkdir.
- Sütunu və son satır istisna olmaqla, sol siçan düyməsini basıb, məlumat kursoru ilə masa seçin. Nişana keçin "Məlumat". Düyməni basın. "Sort"alətlər blokunda yerləşir "Sort və filter" lentdə.
Ayrıca fərqli olaraq edə bilərsiniz. Cədvəlin yuxarıdakı aralığını seçin, sonra sekmeye keçin "Ev" və düyməni basın "Sort və filter"alətlər blokunda yerləşir Redaktə et lentdə. Siyahıda bir mövqe seçdiyimiz aktivləşir. "Xüsusi Sort".
- Yuxarıda göstərilən tədbirlərdən birini tətbiq edərkən, çeşidləmə parametrləri pəncərəsi işə salınır. Parametrə baxırıq "Məlumatlarım başlıqları ehtiva edir" kene təyin olundu. Onun olmaması halında, qurun.
Sahədə "Sütun" gəlir haqqında məlumatın olduğu sütunun adını göstərin.
Sahədə "Sort" hansı müəyyən meyarlara görə sıralanacaq. Öncəki parametrləri buraxırıq - "Dəyərlər".
Sahədə "Sifariş" mövqeyi müəyyənləşdirin "Azalan".
Bu parametrləri etdikdən sonra düyməni basın "OK" pəncərənin dibində.
- Bu aksiyanı yerinə yetirdikdən sonra, bütün maddələr gəlirdən ən yüksəkdən ən aşağı səviyyəyə görə sıralanırdı.
- İndi ümumi elementlərin hər birinin nisbətini hesablamalıyıq. Biz bu məqsədlər üçün zəng edəcəyimiz bir əlavə sütun yaratırıq "Paylaş". Bu sütunun ilk hüceyrəsində bir işarə qoyulur "="bundan sonra müvafiq məhsulun satışından əldə edilən gəlirlərin cəminin yerləşdiyi hüceyrəyə istinad göstəririk Sonra, bölmə işarəsini təyin edin ("/"). Bundan sonra biz müəssisə daxilində malların satışının ümumi məbləğini ehtiva edən hüceyrənin koordinatlarını göstərir.
Göstərilən formulu sütunun digər hüceyrələrinə köçürəcəyini nəzərə alaraq "Paylaş" bir dolgu markerini istifadə edərək, müəssisə üçün ümumi gəlir məbləğini ehtiva edən elementə aid linkin ünvanını düzəltməliyik. Bunun üçün mütləq link qoyun. Formulda göstərilən hüceyrənin koordinatlarını seçin və düyməni basın F4. Gördüyümüz kimi, əlaqələndiricilərin qarşısında bir dollar əlaməti ortaya çıxdı ki, bu linkin mütləq halına gəldiyini göstərir. Qeyd edək ki, siyahıda ilk maddənin gəlir həcminə istinad (Maddə 3) nisbi olaraq qalmalıdır.
Sonra hesablamaları etmək üçün düyməni basın. Daxil edin.
- Gördüyünüz kimi, siyahıda sadalanan ilk məhsuldan gəlir nisbəti hədəf hüceyrədə göstərilir. Aşağıdakı intervalda formulun bir surətini etmək üçün, imleci hüceyrənin sağ alt köşesine qoyun. Kiçik xaç kimi görünən bir dolama markasına çevrilir. Sol siçan düyməsini basın və doldurun tutamağını 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 gəlir payını təsvir edən məlumatlarla doludur. Amma xüsusi ağırlığın dəyəri ədədi formatda göstərilir və biz onu yüzdə birinə çevirməliyik. Bunu etmək üçün sütunun məzmununu seçin "Paylaş". Sonra sekmeye keçin "Ev". Ayarlar qrupundakı lentdə "Sayı" Məlumatın formatını göstərən bir sahə var. Varsayılan olaraq, hər hansı əlavə manipulyasiya etmədiyiniz təqdirdə, format orada yerləşdirilməlidir. "Ümumi". Bu sahənin sağ tərəfində yerləşən üçbucaq şəklində simvol 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 yüzlərə çevrildi. Olmalıdır ki, xəttdə "Ümumi" göstərdi 100%. Sütunda daha kiçikdən kiçik olması gözlənilən malların nisbəti.
- İndi bir toplu smeta nümayiş ediləcək bir sütun yaratmalıyıq. Yəni, hər satırda yuxarıda göstərilən siyahıda olan bütün malların xüsusi çəkisi müəyyən bir məhsulun fərdi xüsusi ağırlığına əlavə olunacaq. Siyahıda ilk maddə üçün (Maddə 3) fərdi xüsusi çəki və yığılmış pay bərabər olacaq, lakin bütün sonrakıları üçün siyahıda əvvəlki maddənin yığılmış payı fərdi göstəriciyə əlavə olunmalıdır.
Beləliklə, ilk sütunda biz sütuna köçürürük "Birgə Pay" sütun dərəcəsi "Paylaş".
- Sonra, ikinci sütun hüceyrəsinə imleci seçin. "Birgə Pay". Burada formula tətbiq etmək məcburiyyətindəyik. Bir işarə qoyduq bərabərdir və hüceyrənin məzmununu katlayın "Paylaş" eyni sıra və hüceyrə məzmunu "Birgə Pay" yuxarıdakı xəttdən. Bütün əlaqələr nisbi, yəni biz onlarla heç bir manipulyasiya etmirik. Bundan sonra düyməni basın. Daxil edin son nəticə göstərmək üçün.
- İndi bu formulu aşağıda yerləşdirilən bu sütunun hüceyrələrinə kopyalamaq lazımdır. Bunu etmək üçün, doldurulmuş markerdən istifadə edin, biz artıq sütunda formula çıxarmaq üçün müraciət etdik "Paylaş". Eyni zamanda simli "Ümumi" Tutulma lazım deyil, çünki yığılmış nəticə 100% siyahıdakı son maddədə göstərilir. Gördüyünüz kimi, bundan sonra sütunumuzun bütün elementləri doldu.
- Bundan sonra bir sütun yaradırıq "Qrup". Məhsulları kateqoriyalara qruplaşdırmaq lazımdır A, B və C ümumi payına görə göstərildi. Xatırladığımız kimi, bütün elementlər aşağıdakı sxemlərə görə qruplara bölünür:
- A - qədər 80%;
- B - aşağıdakılar 15%;
- İlə - qalan 5%.
Beləliklə, xüsusi çəkisi sərhədə daxil olan bütün mallar 80%bir kateqoriya təyin A. Toplanan xüsusi ağırlığı olan mallar 80% qədər 95% bir kateqoriya təyin B. Qalan məhsul qrupu daha çox dəyəri ilə 95% yığılmış xüsusi çəki bir kateqoriya təyin edir C.
- Aydınlıq üçün, bu qrupları müxtəlif rənglərdə doldurmaq olar. Lakin bu, isteğe bağlıdır.
Beləliklə, ABC analizindən istifadə edərək elementləri əhəmiyyətli dərəcədə qruplara ayırdıq. Yuxarıda göstərildiyi kimi, digər üsullardan istifadə edərkən, bir hissəni daha çox qruplara tətbiq edin, lakin bölünmə prinsipi demək olar ki, dəyişməz qalır.
Dərs: Excel-də çeşidləmə və filtrasiya
Metod 2: kompleks bir formulun istifadə edilməsi
Əlbəttə ki, çeşidləmə ABC analizini Exceldə aparmaq üçün ən ümumi üsuldur. Ancaq bəzi hallarda mənbə cədvəlində satırları düzəltmədən bu analizi aparmaq tələb olunur. Bu vəziyyətdə xilasetmə kompleks bir formul olacaq. Məsələn, ilk halda olduğu kimi eyni mənbə cədvəlini istifadə edəcəyik.
- Malların adını və onların hər birinin satışından əldə edilən gəlirləri olan sütunu olan orijinal masa əlavə edin "Qrup". Gördüyünüz kimi, bu vəziyyətdə fərdi və kümülatif səhmlərin hesablanması ilə sütunlar əlavə edə bilmirik.
- Sütundakı ilk hüceyrəni seçin. "Qrup"sonra düyməni basın. "Əlavə funksiyası"formula barının yaxınlığında yerləşir.
- Aktivləşdirmə aparılır Fəaliyyət ustaları. Kateqoriyaya keçin "Linklər və seriallar". Bir funksiyanı seçin "SELECT". Düyməni basın. "OK".
- Function argument window aktivləşdirilir. SEÇİM. Onun sözdizimi belədir:
= SEÇİN (Index_number; Value1; Value2; ...)Bu funksiyanın məqsədi indeks sayılarına əsasən göstərilən dəyərlərdən birini çıxartmaqdır. Qiymətlərin sayı 254-ə çata bilər, ancaq ABC analizi kateqoriyalarına cavab verən yalnız üç ada lazımdır: A, B, İlə. Dərhal sahəyə girə bilərik "Qiymət1" simvolu "A"sahəsində "Qiymət2" - "B"sahəsində "Qiymət3" - "C".
- Ancaq bir arqument "İndeks sayı" Bir sıra əlavə operatorlar qurduqdan sonra hərtərəfli tinker etmək lazımdır. İmleci sahəyə qoyun "İndeks sayı". Sonra, düymənin sol tərəfində üçbucaq şəklində olan simvol basın "Əlavə funksiyası". Son zamanlarda istifadə edilən operatorların siyahısı açılır. Bir funksiyaya ehtiyacımız var MATCH. Siyahıda olmadığı üçün başlığı basın "Digər xüsusiyyətlər ...".
- Yenidən pəncərəni işə salır. Fəaliyyət ustaları. Yenə kateqoriyaya gedin "Linklər və seriallar". Orada bir mövqe tapırıq "MATCH"seçin və düyməsini basın "OK".
- Operator arqumenti pəncərəsi açılır MATCH. Onun sözdizimi belədir:
= MATCH (Aradakı dəyər; Baxılan array; Match_type)Bu funksiyanın məqsədi göstərilən elementin mövqeyini müəyyən etməkdir. Yəni, sahəyə ehtiyacımız var "İndeks sayı" funksiyaları SEÇİM.
Sahədə "Göründüyü array" Dərhal aşağıdakı ifadəni təyin edə bilərsiniz:
{0:0,8:0,95}Bir sıra formulası kimi tam olaraq əyri birləşmələrlə olmalıdır. Bu rəqəmlərin (0; 0,8; 0,95) qruplar arasında yığılmış payın sərhədlərini ifadə edir.
Alan "Xəritəçəkmə Tipi" məcburi deyil və bu halda biz onu doldurmayacağıq.
Sahədə "Arzu olunan dəyər" imleci seçin. Yenə yuxarıda göstərilən simvol vasitəsilə bir üçbucaq şəklində biz hərəkət edirik Function Sihirbazı.
- Bu dəfə Function sihirbazı kateqoriyaya keçin "Riyaziyyat". Bir ad seçin "SUMMESLI" və düyməni basın "OK".
- Fəaliyyət arqumenti pəncərəsi başlayır. Məbləğlər. Göstərilən operator müəyyən edilmiş şərtlərə cavab verən hüceyrələri toplamağa borcludur. Onun sözdizimi:
= SUMMES (aralıq; ölçmə;Sahədə "Range" sütunun ünvanını daxil edin "Gəlir". Bu məqsədlər üçün imleci sahəyə qoyduq və sonra sol siçan düyməsini bağladıqdan sonra müvafiq sütundakı bütün hüceyrələri seçin, dəyəri istisna olmaqla "Ümumi". Gördüyünüz kimi, ünvan dərhal sahəsində göstərilir. Bundan əlavə, biz bu əlaqəni mütləq etmək məcburiyyətindəyik. Bunu etmək üçün seçimini edin və düyməni basın F4. Ünvanı dollar əlamətləri ilə vurğulanır.
Sahədə "Kriter" bir şərt qoymalıyıq. Aşağıdakı ifadəni daxil edin:
">"&Sonra dərhal sonra sütunun ilk hüceyrəsinin ünvanını daxil edəcəyik. "Gəlir". Bu ünvandakı üfüqi koordinatları mütləq edərkən, məktubun önündəki klaviatura bir dollar əlamətini əlavə edirik. Şaquli koordinatlar nisbi, yəni sayı qarşısında heç bir əlamət olmur.
Bundan sonra düyməni basmayın "OK"və funksiyanın adını basın MATCH formula barında.
- Sonra funksiya argümanı pəncərəsinə qayıdırıq. MATCH. Gördüyünüz kimi, sahədə "Arzu olunan dəyər" operator tərəfindən verilən məlumatlar ortaya çıxdı Məbləğlər. Amma bu hamısı deyil. Bu sahədə gedin və əlaməti mövcud məlumatlara əlavə edin. "+" quotes olmadan. Sonra sütunun ilk hüceyrəsinin ünvanını daxil edəcəyik. "Gəlir". Yenə biz bu linkin üfüqi koordinatlarını mütləq edirik və dikey olaraq nisbi buraxırıq.
Sonra, sahənin bütün məzmununu götürün "Arzu olunan dəyər" mötərizədə, sonra bölmə işarəsini"/"). Bundan sonra, yenidən üçbucaq simvolu vasitəsilə funksiyanı seçmə pəncərəsinə gedin.
- Keçən dəfə olduğu kimi Function sihirbazı kateqoriyaya istədiyiniz operator axtarır "Riyaziyyat". Bu dəfə istənilən funksiya çağırılır "SUMM". Seçin və düyməni basın. "OK".
- Operator arqumenti pəncərəsi açılır SUM. Onun əsas məqsədi hüceyrələrdəki məlumatların toplanmasıdır. Bu ifadənin sintaksisi olduqca sadədir:
= SUM (Sayı1; Nömrə2; ...)Bizim məqsədimiz üçün yalnız bir sahəyə ehtiyacımız var. "1 nömrə". Sütun aralığının koordinatlarını daxil edin "Gəlir", ümumi sayı olan hüceyrə istisna olmaqla. Biz artıq sahədə də oxşar bir əməliyyat etdik. "Range" funksiyaları Məbləğlər. O vaxtdan etibarən, biz onları seçərək ardıcıllıqla mütləq koordinatları edirik və düyməni basaraq F4.
Bundan sonra düyməni basın "OK" pəncərənin dibində.
- Gördüyünüz kimi, daxil edilən funksiyaların kompleksi bir hesablama hazırlayıb nəticəni kolonun ilk hüceyrəsinə verdi "Qrup". Birinci maddə bir qrup təyin edildi. "A". Bu hesablamada istifadə etdiyimiz tam formul aşağıdadır:
= SELECT (MATCH (($ B $ 2: $ B $ 27; "> & $ B2 + $ B2) / SUM ($ B $ 2: $ B 27 $); {0: 0.8: 0.95} ), "A", "B", "C")Lakin, əlbəttə ki, hər bir halda, bu formada olan koordinatlar fərqli olacaq. Buna görə universal olaraq qəbul edilə bilməz. Lakin, yuxarıda göstərilən dərslikdən istifadə edərək hər hansı bir masanın koordinatlarını əlavə edə və bu üsulu istənilən vəziyyətdə uğurla tətbiq edə bilərsiniz.
- Ancaq bu hamısı deyil. Yalnız cədvəlin ilk sıra üçün hesablanır. Məlumat sütunu tamamilə doldurmaq üçün "Qrup", bu formülü aşağıda göstərilən ardıcıllıqla kopyalamaq lazımdır (satır hüceyrəsi istisna olmaqla) "Ümumi") birdən çox dəfə etdiyimiz kimi, dolğu markerindən istifadə etmək. Məlumatlar daxil edildikdən sonra, ABC təhlili tam hesab edilə bilər.
Gördüyünüz kimi, kompleks bir formulun istifadəsi ilə variantdan əldə edilən nəticələr, sıralamaqla etdiyimiz nəticələrdən fərqlənmir. Bütün məhsullar eyni kateqoriyaya aiddir, lakin xətlər onların başlanğıc mövqeyini dəyişdirməmişdir.
Dərs: Excel funksiyası sihirbazı
Excel, bir istifadəçi üçün ABC analizini çox asanlaşdırır. Bu sıralama kimi bir vasitə istifadə edilir. Bundan sonra fərdi xüsusi çəki, yığılmış pay və əslində qruplara bölünmə hesablanır. Cədvəldə satırların başlanğıc mövqesindəki dəyişikliklərin icazə verilmədiyi hallarda, kompleks formuldan istifadə edərək metodu tətbiq edə bilərsiniz.
