Басқарудың және логистиканың негізгі әдістерінің бірі ABC талдау болып табылады. Онымен сіз кәсіпорынның, өнімдердің, клиенттердің және т.б. ресурстарын жіктеуге болады. маңыздылығы жағынан. Сонымен қатар, маңыздылық деңгейіне сәйкес жоғарыда аталған бөлімшелердің әрқайсысы A, B немесе C санаттарының біреуіне тағайындалады. Excel бағдарламасында өз багажында осындай талдауды жеңілдететін құралдар бар. Оларды қалай қолдануға болатынын көрейік, ал ABC талдауы қандай.
ABC талдауын қолдану
ABC талдау - Парето принципінің заманауи шарттарына бейімделген және жақсартылған. Оның мінез-құлық әдісіне сәйкес талдаудың барлық элементтері маңыздылығына қарай үш санатқа бөлінеді:
- Санат A - жалпыға ортақ элементтер бар 80% нақты салмақ;
- Санат B - жиынтығы түскен элементтер 5% дейін 15% нақты салмақ;
- Санат C - жалпы жиынтығы болып табылатын қалған элементтер 5% және нақты салмақ аз.
Кейбір компаниялар алдыңғы қатарлы әдістерді қолданады және элементтерді 3-ке емес, 4 немесе 5 топтарға бөледі, бірақ біз ABC талдаудың классикалық схемасына сенеміз.
1-әдіс: сұрыптау бойынша талдау
Excel бағдарламасында ABC талдау сұрыптау арқылы орындалады. Барлық заттар ең үлкенден ең кішкентайға дейін сұрыпталады. Содан кейін әрбір элементтің жиынтық салмағы есептеледі, оның негізінде белгілі бір санат тағайындалады. Бұл әдіс практикада қалай қолданылатынын білу үшін нақты мысал келтірейік.
Компанияда сатылатын тауарлар тізбесі бар кесте бар, және белгілі бір уақыт кезеңі ішінде оларды сатудан түсетін кіріс мөлшері бар. Кестенің төменгі жағында жалпы кіріс тауардың барлық элементтері үшін жинақталады. Аталмыш өнімдерді компания үшін маңыздылығына қарай топтарға бөлу үшін ABC-талдауды пайдалану міндеті.
- Деректер меңзерімен кестені таңдап, тақырыпты және соңғы жолды қоспағанда, тінтуірдің сол жақ батырмасын ұстап тұрыңыз. Қойындыға өтіңіз «Деректер». Түймешікті басыңыз. «Сұрыптау»құралдар блогында орналасқан «Сұрыптау және сүзгілеу» таспаға арналған.
Сонымен қатар сіз басқаша жасай аласыз. Кестенің жоғарыдағы ауқымын таңдап, өтіңіз «Үй» батырмасын басыңыз «Сұрыптау және сүзгілеу»құралдар блогында орналасқан Өңдеу таспаға арналған. Тізім активтендірілген, онда біз онда позицияны таңдаймыз. «Пайдаланушы сұрыптау».
- Жоғарыдағы әрекеттердің кез келгенін қолданғанда сұрыптау параметрлері терезесі іске қосылады. Параметрді қарастырамыз «Менің деректерімтегі тақырыптар бар» белгі орнатылды. Ол болмаған жағдайда орнатыңыз.
Алаңда «Баған» табыстың деректері туралы бағанның атын көрсетіңіз.
Алаңда «Сұрыптау» қандай нақты критерийлер сұрыпталатындығын көрсетуіңіз керек. Алдын ала орнатылған параметрлерді қалдырамыз - «Мәндер».
Алаңда «Тапсырыс» позициясын орнатыңыз «Кему бойынша».
Осы параметрлерді орнатқаннан кейін түймесін басыңыз «ОК» терезенің төменгі жағында.
- Осы әрекетті орындағаннан кейін, барлық элементтер ең жоғарыдан ең төменгі деңгейге дейінгі табыс бойынша сұрыпталған.
- Енді біз элементтердің әрқайсысының жалпы санының үлесін есептеуіміз керек. Біз осы мақсаттарға біз шақыратын қосымша бағаны жасаймыз «Үлес салмағы». Бұл бағандағы бірінші ұяшықта белгі қойылады "="содан кейін тиісті өнімді сатудан түсетін түсімдердің сомасы орналасқан ұяшыққа сілтеме көрсетіледі. Содан кейін, бөлу белгісін орнатыңыз ("/"). Осыдан кейін біз бүкіл кәсіпорын ішінде тауар сатудың жалпы көлемін қамтитын ұяшықтың координаттарын көрсетеді.
Көрсетілген формуланы бағанның басқа ұяшықтарына көшіретінімізді ескере отырып «Үлес салмағы» толтыру таңбалауышын пайдаланып, кәсіпорын үшін кірістің жалпы сомасын қамтитын элемент сілтемесінің мекен-жайын түзету қажет. Мұны істеу үшін сілтемесін абсолютті етіп жасаңыз. Формуладағы көрсетілген ұяшықтың координаттарын таңдаңыз және пернені басыңыз F4. Көріп отырғанымыздай, координаттардың алдындағы доллар белгісі пайда болды, бұл сілтеме абсолютті болғанын білдіреді. Айта кету керек, тізімдегі бірінші тармақтың кіріс көлеміне сілтеме (3-тармақ) салыстырмалы түрде қалуы керек.
Содан кейін есептеулерді жасау үшін түймесін басыңыз. Кіру.
- Көріп отырғаныңыздай, тізімде тізімделген бірінші өнімнің кірісінің үлесі мақсатты ұяшықта көрсетіледі. Төмендегі ауқымда формуланың көшірмесін жасау үшін, меңзерді ұяшықтың төменгі оң жақ бұрышына қойыңыз. Ол кішкене крестке ұқсайтын толтырғышқа айналады. Тінтуірдің сол жақ батырмасын басыңыз және толтырғышты бағанның соңына қарай сүйреңіз.
- Көріп отырғаныңыздай, бүкіл баған әрбір өнімнің сатудан түсетін табыс үлесін сипаттайтын деректермен толтырылады. Бірақ нақты салмақтың мәні сандық пішімде көрсетіледі және оны оны пайызға айналдыру керек. Мұны істеу үшін бағанның мазмұнын таңдаңыз «Үлес салмағы». Содан кейін қойындыға жылжытыңыз «Үй». Параметрлер тобындағы таспада «Нөмір» Деректер пішімін көрсететін өріс бар. Әдепкі бойынша, сіз ешқандай қосымша манипуляцияларды орындамаған болсаңыз, онда формат орнатылуы керек. «Жалпы». Біз осы өрістің оң жағында орналасқан үшбұрыш түріндегі белгішені басамыз. Ашылатын пішімдер тізімінде позицияны таңдаңыз «Қызығушылық».
- Көріп отырғандай, барлық бағандар мәндері пайызға түрлендірілген. Қалай болуы керек, желіде «Барлығы» көрсетілген 100%. Колоннада орналасуы күтілетін тауарлардың үлесі үлкенірек кішігірім.
- Енді біз жиынтық жиынтықтағы жинақталған үлестің көрсетілетін бағаны жасауымыз керек. Яғни, әрбір жолда жоғарыдағы тізімде тұрған барлық тауарлардың нақты салмағы белгілі бір өнімнің жеке салмағына қосылады. Тізімдегі бірінші тармақ үшін (3-тармақ) жеке салмақ пен жинақталған үлес тең болады, бірақ кейінгі барлық тізімде алдыңғы тізімдегі алдыңғы тармақтың жинақталған үлесі жеке индикаторға қосылуы тиіс.
Мәселен, біз бірінші бағанда бағанға көшеміз «Жинақталған қатысу үлесі» баған бағамы «Үлес салмағы».
- Содан кейін курсорды екінші баған ұяшығына қойыңыз. «Жинақталған қатысу үлесі». Мұнда формуланы қолдану керек. Біз белгі қойдық тең және ұяшықтың мазмұнын бүктеңіз «Үлес салмағы» сол жол және ұяшық мазмұны «Жинақталған қатысу үлесі» жоғарыдағы жолдан. Барлық сілтемелер салыстырмалы, яғни біз олармен ешқандай манипуляция жасамаймыз. Осыдан кейін түймесін басыңыз. Кіру соңғы нәтижені көрсету үшін.
- Енді осы формуланы төменде орналасқан осы бағанның ұяшықтарына көшіріп алу керек. Бұл әрекетті орындау үшін, бағандағы формуланы көшіруге жүгінген толтырғышты пайдаланыңыз «Үлес салмағы». Сонымен қатар жол «Барлығы» түсіру қажет емес, себебі жинақталған нәтиже 100% тізімдегі соңғы элементте көрсетіледі. Көріп отырғанымыздай, біздің бағанның барлық элементтері толтырылды.
- Осыдан кейін бағанды жасаймыз «Топ». Өнімдерді санаттарға топтастыру қажет A, B және C кумулятивтік үлеске сәйкес көрсетілген. Естеріңізге салсақ, барлық элементтер төмендегі схема бойынша топтарға бөлінеді:
- A - дейін 80%;
- B - Мынадай 15%;
- С - қалған 5%.
Осылайша, нақты салмағы шекараға кіретін жиынтық үлесі бар барлық тауарлар 80%санатты тағайындаңыз A. Жинақталған нақты салмағы бар тауарлар 80% дейін 95% санатты тағайындаңыз B. Қалған өнім тобы көп мәнге ие 95% есептелген нақты салмақ санатын тағайындайды C.
- Түсінікті болу үшін сіз бұл топтарды түрлі түстерге толтыра аласыз. Бірақ бұл міндетті емес.
Осылайша, ABC талдауын пайдалана отырып, элементтерді маңыздылық деңгейіне қарай топтарға бөлдік. Жоғарыда айтылғандай, басқа әдістерді қолданған кезде бөлімдерді көп топтарға қолданыңыз, бірақ бөлу әдісі дерлік өзгеріссіз қалады.
Сабақ: Excel бағдарламасында сұрыптау және сүзу
2-әдіс: күрделі формуланы пайдалану
Әрине, сұрыптауды пайдалану - ABC талдауын Excel бағдарламасында жүргізудің ең кең таралған тәсілі. Бірақ кейбір жағдайларда бұл талдауды бастапқы кестедегі жолдарды қайта өңдеусіз жүргізу керек. Бұл жағдайда құтқаруға күрделі формула келеді. Мысалы, біз бірдей бастапқы кестені бірінші жағдайда қолданамыз.
- Түпкі кестеге тауарлардың атауы мен олардың әрқайсысының сатысынан түсетін бағаны қосыңыз «Топ». Көріп отырғаныңыздай, бұл жағдайда жеке және жинақталған акцияларды есептеу арқылы бағандарды қосуға болмайды.
- Бағандағы бірінші ұяшықты таңдаңыз. «Топ»содан кейін түймесін басыңыз. «Функцияны кірістіру»формула жолының жанында орналасқан.
- Қосылу орындалады Функцияның шеберлері. Санатқа көшіңіз «Сілтемелер және массивтер». Функцияны таңдаңыз «ТАҢДАУ». Түймешікті басыңыз. «ОК».
- Функция дәлелінің терезесі белсендіріледі. ТАҢДАУ. Оның синтаксисі келесідей:
= SELECT (индексі_нөмірі; мән1; мән2; ...)Бұл функцияның мақсаты индекс нөміріне байланысты берілген мәндердің бірін шығару болып табылады. Мәндердің саны 254-ке жетуі мүмкін, бірақ ABC талдауының санаттарына сәйкес келетін тек үш атау қажет: A, B, С. Біз бірден далаға кіре аламыз «Мән1» символы «A», алаңда «Мән2» - «B», алаңда «Мән3» - «С».
- Бірақ дәлел «Индекс нөмірі» Оған кейбір қосымша операторларды салғаннан кейін мұқият болуға тырысу керек. Курсорды өріске орнатыңыз «Индекс нөмірі». Содан кейін түйменің сол жағындағы үшбұрыштың пішіні бар белгішені басыңыз «Функцияны кірістіру». Жақында пайдаланылған операторлардың тізімі ашылады. Бізге функция қажет MATCH. Ол тізімде жоқ болғандықтан, тақырыпты басыңыз «Басқа мүмкіндіктер ...».
- Терезені қайтадан іске қосады. Функцияның шеберлері. Тағы да санатқа өтіңіз «Сілтемелер және массивтер». Біз бұл жерде позицияны табамыз «MATCH»оны таңдаңыз және түймені басыңыз «ОК».
- Оператор дәлел терезесі ашылады MATCH. Оның синтаксисі келесідей:
= MATCH (Табылған мән; Көрінген массив; Match_type)Бұл функцияның мақсаты көрсетілген элементтің позиция санын анықтау болып табылады. Яғни, далаға қажетті нәрсе «Индекс нөмірі» функциялары ТАҢДАУ.
Алаңда «Қарастырылған массив» Бірден келесі өрнекті орнатуға болады:
{0:0,8:0,95}Ол массив формуласы сияқты дәл бұдырлы жақшаларда болуы керек. Бұл сандардың (мысалы,0; 0,8; 0,95) топтар арасында жинақталған үлестің шекарасын білдіреді.
Өріс «Түрді салыстыру» міндетті емес және бұл жағдайда оны толтырмаймыз.
Алаңда «Қажет мән» курсорды орнатыңыз. Содан кейін, үшбұрыш түріндегі жоғарыда сипатталған белгі арқылы қайтадан жүреміз Функция шебері.
- Бұл жолы Функция шебері санатқа ауысыңыз «Математикалық». Атын таңдаңыз «SUMMESLI» батырмасын басыңыз «ОК».
- Функция дәлелінің терезесі басталады. Сомалар. Көрсетілген оператор көрсетілген шартқа сәйкес келетін ұяшықтарды толтырады. Оның синтаксисі:
= SUMMES (ауқым; критерий;Алаңда «Ауқым» бағанның мекенжайын енгізіңіз «Табыс». Бұл мақсаттар үшін жүгіргіні өріске орнатамыз, содан кейін сол жақ тінтуір түймесін қысып, сәйкес бағандағы барлық ұяшықтарды таңдап, маңызы жоқ «Барлығы». Көріп тұрғаныңыздай, мекен-жайы бірден өрісте көрсетіледі. Сонымен қатар, біз бұл сілтеме абсолютті болуы керек. Мұны істеу үшін оның таңдауын жасаңыз және пернені басыңыз F4. Мекенжай доллар белгісімен ерекшеленеді.
Алаңда «Критерий» біз шартты орнатуымыз керек. Келесі өрнекті енгізіңіз:
">"&Содан кейін бірден кейін бағанның бірінші ұяшығының мекен-жайын енгіземіз. «Табыс». Біз осы адресте көлденең координаттарды абсолютті түрде жасаймыз, әріптің алдында пернетақтадан доллар белгісін қосамыз. Тік координаттары салыстырмалы, яғни санның алдында белгі болмауы керек.
Осыдан кейін түймені баспаңыз «ОК», және функцияның атын нұқыңыз MATCH формула жолында.
- Содан кейін функция дәлелінің терезесіне ораламыз. MATCH. Көріп отырғандай, далада «Қажет мән» деректерді оператор берген Сомалар. Бірақ бұл бәрі емес. Осы өріске барыңыз және қолда бар деректерге қосыңыз. "+" баға белгілерінсіз. Содан кейін бағанның бірінші ұяшығының мекен-жайын енгіземіз. «Табыс». Және біз тағы да осы сілтемедің көлденең координаттарын абсолютті етіп жасаймыз және тігінен салыстырмалы түрде шығамыз.
Содан кейін өрістің барлық мазмұнын алыңыз «Қажет мән» жақшаға қойыңыз да, бөлу белгісін қойыңыз ("/"). Содан кейін тағы үшбұрыш белгішесі арқылы функцияны таңдау терезесіне өтіңіз.
- Соңғы рет өткендей Функция шебері санаттағы қажетті операторды іздейді «Математикалық». Бұл жолы қалаған функция шақырылады «СУММ». Оны таңдаңыз және түймесін басыңыз. «ОК».
- Оператор дәлел терезесі ашылады SUM. Оның негізгі мақсаты - жасушалардағы деректерді жинақтау. Бұл сөздердің синтаксисі өте қарапайым:
= SUM (Сан1; Сан2; ...)Біздің мақсатымыз үшін тек қана өріс қажет. «№1». Баға ауқымының координаттарын енгізіңіз «Табыс», жиынтығы бар ұяшықты қоспағанда. Біз далада осындай операция жасадық. «Ауқым» функциялары Сомалар. Сол кездегідей, біз оларды таңдау арқылы ауқымның абсолютті координаттарын жасаймыз және пернені басамыз F4.
Осыдан кейін кілтті басыңыз «ОК» терезенің төменгі жағында.
- Көріп отырғаныңыздай, енгізілген функциялардың кешені есептеуді жасап, нәтижені бағанның бірінші ұяшығына берді «Топ». Бірінші тармаққа топ тағайындалды. «A». Осы есептеу үшін пайдаланған толық формула төмендегідей:
= SELECT (MATCH (($ B $ 2: $ B $ 27; «> & $ B2 + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} «А», «В», «С»)Әрине, әр форматтағы координаттар әр түрлі болады. Сондықтан оны әмбебап деп санауға болмайды. Бірақ жоғарыдағы нұсқаулықты қолданып, кез-келген кестенің координаттарын кірістіріп, кез-келген жағдайда бұл әдісті сәтті қолдануға болады.
- Бірақ бұл бәрі емес. Біз кестенің бірінші жолында ғана есептелдік. Деректер бағанын толығымен толтыру үшін «Топ», осы формуланы төмендегі ауқымда көшіруіңіз керек (жол ұяшығын қоспағанда) «Барлығы»), біз бірнеше рет жасағанымыздай, толтырғышты қолдану арқылы. Деректер енгізілгеннен кейін ABC талдауын толық деп санауға болады.
Көріп отырғанымыздай, күрделі формуланы қолдану арқылы нұсқаны қолдана отырып алынған нәтижелер мүлде сұрыптау арқылы жасаған нәтижелерден ерекшеленбейді. Барлық өнімдер бірдей санаттармен белгіленеді, бірақ желілер олардың бастапқы позициясын өзгертпеді.
Сабақ: Excel функциясының шебері
Excel бағдарламасы пайдаланушыға ABC талдауын айтарлықтай жеңілдетеді. Бұл сұрыптау сияқты құралдың көмегімен қол жеткізіледі. Осыдан кейін, жеке салмақ, жинақталған үлес және, шын мәнінде, топтарға бөлу есептеледі. Кестеде жолдардың бастапқы күйінде өзгертуге жол берілмеген жағдайларда, күрделі формуланы пайдаланып, әдісті қолдануға болады.
