Съдържание
Може би сте чували за закона на Парето или принципа 20/80. В края на 19 век италианският социолог и икономист Вилфредо Парето открива, че разпределението на богатството в обществото е неравномерно и подлежи на определена зависимост: с увеличаване на богатството броят на богатите намалява експоненциално с постоянен коефициент ( сред италианските домакинства 80% от доходите са в 20% от семействата). По-късно тази идея е развита в книгата му от Ричард Кох, който предлага формулирането на универсалния „Принцип 20/80“ (20% от усилията дават 80% от резултата). На практика този закон обикновено не се изразява в толкова красиви числа (прочетете „Дългата опашка” на Крис Андерсън), но ясно показва неравномерното разпределение на ресурси, печалби, разходи и т.н.
В бизнес анализа често се изгражда диаграма на Парето, за да представи тази неравномерност. Може да се използва за визуално показване например кои продукти или клиенти носят най-голяма печалба. Обикновено изглежда така:
Основните му характеристики:
- Всяка синя колона на хистограмата представлява печалбата за продукта в абсолютни единици и е нанесена по лявата ос.
- Оранжевата графика представлява кумулативния процент на печалбата (т.е. дела на печалбата на кумулативна база).
- На условна граница от 80% обикновено се изчертава прагова хоризонтална линия за яснота. Всички стоки вляво от точката на пресичане на тази линия с графиката на натрупаната печалба ни носят 80% от парите, всички стоки вдясно – останалите 20%.
Нека видим как сами да изградите диаграма на Парето в Microsoft Excel.
Вариант 1. Проста диаграма на Парето, базирана на готови данни
Ако изходните данни дойдоха при вас под формата на подобна таблица (тоест вече в завършен вид):
… тогава правим следното.
Сортирайте таблицата в низходящ ред на печалба (таб Данни – Сортиране) и добавете колона с формулата за изчисляване на натрупания процент печалба:
Тази формула разделя общата натрупана печалба от началото на списъка до текущия елемент на общата печалба за цялата таблица. Също така добавяме колона с константа от 80%, за да създадем хоризонтална прагова пунктирана линия в бъдещата диаграма:
Избираме всички данни и изграждаме правилна хистограма в раздела Вмъкване – хистограма (вмъкване – колонна диаграма). Трябва да изглежда нещо подобно:
Процентната серия в получената диаграма трябва да бъде изпратена по вторичната (дясна) ос. За да направите това, трябва да изберете редовете с мишката, но това може да бъде трудно, тъй като те трудно се виждат на фона на големи колони за печалба. Затова е по-добре да използвате падащия списък в раздела, за да маркирате оформление or формат:
След това щракнете с десния бутон върху избрания ред и изберете командата Форматиране на поредица от данни и в прозореца, който се показва, изберете опцията На вторичната ос (Вторична ос). В резултат нашата диаграма ще изглежда така:
За серията Акумулиран дял от печалбата и праг трябва да промените типа на диаграмата от колони на линии. За да направите това, щракнете върху всеки от тези редове и изберете командата Промяна на типа диаграма на серията.
Всичко, което остава, е да изберете хоризонталния ред Threshold и да го форматирате така, че да изглежда като гранична линия, а не като данни (т.е. премахнете маркерите, направете линията пунктирана в червено и т.н.). Всичко това може да стане, като щракнете с десния бутон върху реда и изберете командата Форматиране на поредица от данни. Сега диаграмата ще приеме окончателната си форма:
Според него можем да заключим, че 80% от печалбата се носи от първите 5 стоки, а всички останали стоки вдясно от картофа представляват само 20% от печалбата.
В Excel 2013 можете да го направите още по-лесно – използвайте новия вграден комбиниран тип диаграма веднага, когато чертаете:
Вариант 2: обобщена таблица и обобщена диаграма на Парето
Какво да направите, ако няма готови данни за изграждане, а само оригиналната сурова информация? Да приемем, че в началото имаме таблица с данни за продажби като тази:
За да изградите върху нея диаграма на Парето и да разберете кои продукти се продават най-добре, първо ще трябва да анализирате изходните данни. Най-лесният начин да направите това е с обобщена таблица. Изберете произволна клетка в изходната таблица и използвайте командата Вмъкване – обобщена таблица (Вмъкване – обобщена таблица). В междинния прозорец, който се появява, не променяйте нищо и щракнете OK, след което в панела, който се появява вдясно, плъзнете полетата с изходни данни от горната към долната част на оформлението на бъдещата обобщена таблица:
Резултатът трябва да бъде обобщена таблица с общите приходи за всеки продукт:
Сортирайте го в низходящ ред на приходите, като зададете активната клетка на колоната Сума в полето Приходи и с помощта на бутона за сортиране От Я до А (От Я до А) етикет Дата.
Сега трябва да добавим изчислена колона с натрупаните приходи от лихви. За да направите това, плъзнете отново полето Приходи към района Ценности в десния прозорец, за да получите дублираща се колона в обобщената точка. След това щракнете с десния бутон върху клонираната колона и изберете команда Допълнителни изчисления – % от текущата обща сума в полето (Показване на данните като – % текуща обща сума). В прозореца, който се показва, изберете полето Име, върху който процентът на приходите ще се натрупва отгоре надолу. Резултатът трябва да изглежда като тази таблица:
Както можете да видите, това е почти готова таблица от първата част на статията. За пълно щастие му липсва само колона с прагова стойност от 80% за построяване на гранична линия в бъдеща диаграма. Такава колона може лесно да се добави с помощта на изчисляемо поле. Маркирайте произволно число в обобщението и след това щракнете върху раздела Начало – Вмъкване – Изчисляемо поле (Начало – Вмъкване – Изчисляемо поле). В прозореца, който се отваря, въведете името на полето и неговата формула (в нашия случай константа):
След като кликнете върху OK към таблицата ще бъде добавена трета колона със стойност 80% във всички клетки и тя накрая ще приеме необходимата форма. След това можете да използвате командата Осева диаграма (Осева диаграма) етикет параметри (Настроики) or Анализ (Анализ) и настройте диаграмата точно по същия начин като първата опция:
Подчертаване на ключови продукти
За да се подчертаят най-влиятелните фактори, т.е. колоните, разположени вляво от пресечната точка на оранжевата крива на натрупаната лихва с хоризонталната гранична линия от 80%, могат да бъдат подчертани. За да направите това, ще трябва да добавите още една колона към таблицата с формулата:
Тази формула извежда 1, ако произведението е отляво на пресечната точка и 0, ако е отдясно. След това трябва да направите следното:
- Добавяме нова колона към диаграмата – най-лесният начин да направите това е просто копиране, т.е. маркиране на колона подсветка, копирайте го (Ctrl + C), изберете диаграмата и вмъкнете (Ctrl + V).
- Изберете добавения ред и го превключете по вторичната ос, както е описано по-горе.
- Сериен тип диаграма подсветка промяна на колони (хистограма).
- Премахваме страничния просвет в свойствата на реда (щракнете с десния бутон върху реда Осветление – Формат на ред – Страничен интервал), така че колоните да се слеят в едно цяло.
- Премахваме границите на колоните и правим запълването полупрозрачно.
В резултат на това получаваме толкова хубав акцент от най-добрите продукти:
PS
Започвайки с Excel 2016, диаграмата на Парето е добавена към стандартния набор от диаграми на Excel. Сега, за да го изградите, просто изберете диапазона и в раздела Поставете (вмъкване) изберете подходящия тип:
Един клик – и диаграмата е готова:
- Как да създадете отчет с помощта на обобщена таблица
- Настройте изчисления в обобщени таблици
- Какво е новото в диаграмите в Excel 2013
- Статия в Уикипедия за закона на Парето