KPI – показатель эффективности, позволяющий объективно оценить результативность выполняемых действий. Данная система применяется для оценки различных показателей (деятельности всей компании, отдельных структур, конкретных специалистов). Она выполняет не только функции контроля, но и стимулирует трудовую активность. Часто на основе KPI строится система оплаты труда. Это методика формирования переменной части зарплаты.

KPI ключевые показатели эффективности: примеры в Excel

Стимулирующий фактор в системе мотивации KPI – денежное вознаграждение. Получить его может тот работник, который выполнил поставленную перед ним задачу. Сумма премии / бонуса зависит от результата конкретного сотрудника в отчетном периоде. Объем вознаграждения может быть фиксированным или выражаться в процентах к окладу.

Каждое предприятие определяет ключевые показатели эффективности и вес каждого индивидуально. Данные зависят от задач компании. Например:

1. Цель – обеспечить план продаж продукта в размере 500 000 рублей ежемесячно. Ключевой показатель – план продаж. Система измерения: фактическая сумма продаж / плановая сумма продаж.
2. Цель – повысить сумму отгрузки в периоде на 20%. Ключевой показатель – средняя сумма отгрузки. Система измерения: фактическая средняя величина отгрузки / плановая средняя величина отгрузки.
3. Задача – увеличить число клиентов на 15% в определенном регионе. Ключевой показатель – число клиентов в базе данных предприятия. Система измерения: фактическое число клиентов / плановое число клиентов.

Разброс коэффициента (весы) предприятие также определяет самостоятельно. Например:

1. Выполнение плана менее 80% - недопустимо.
2. Выполнение плана 100% - коэффициент 0,45.
3. Выполнение плана 100-115% - коэффициент 0,005 за каждые 5%.
4. Отсутствие ошибок – коэффициент 0,15.
5. В отчетном периоде не было замечаний – коэффициент 0,15.

Это лишь возможный вариант определения мотивационных коэффициентов.

Ключевой момент в измерении KPI – отношение фактического показателя к плановому. Практически всегда заработная плата сотрудника складывается из оклада (постоянной части) и премии (переменной / изменяемой части). Мотивационный коэффициент влияет на формирование переменной.

Предположим, что соотношение постоянной и изменяемой частей в зарплате – 50 × 50. Ключевые показатели эффективности и вес каждого из них:

Примем следующие значения коэффициентов (одинаковые для показателя 1 и показателя 2):

Таблица KPI в Excel:

Пояснения:

Это примерная таблица KPI в Excel. Каждое предприятие составляет собственную (с учетом особенностей работы и системы премирования).



Матрица KPI и пример в Excel

Для оценки работников по ключевым показателям эффективности составляется матрица, или соглашение о целях. Общая форма выглядит так:

1. Ключевые показатели – критерии, по которым оценивается работа персонала. Для каждой должности они свои.
2. Веса – числа в интервале от 0 до 1, общая сумма которых равняется 1. Отражают приоритеты каждого ключевого показателя с учетом задач компании.
3. База – допустимое минимальное значение показателя. Ниже базового уровня – отсутствие результата.
4. Норма – плановый уровень. То, что сотрудник должен выполнять обязательно. Ниже – работник не справился со своими обязанностями.
5. Цель – значение, к которому нужно стремиться. Сверхнормативный показатель, позволяющий улучшить результаты.
6. Факт – фактические результаты работы.
7. Индекс KPI показывает уровень результата по отношению к норме.

Формула расчета kpi:

Индекс KPI = ((Факт – База) / (Норма – База)) * 100%.

Пример заполнения матрицы для офис-менеджера:

Коэффициент результативности – сумма произведений индексов и весов. Оценка эффективности сотрудника наглядно показана с помощью условного форматирования.

1. В дополнительный столбец, в котором будем указывать рейтинг, вставляем функцию РАНГ (пишем в ячейке =РАНГ и выбираем из списка предложенную EXCEL функцию, жмем в строке формул fx)

2. Заполняем аргументы в открывшемся окне: «Число» - указываем первое значение в нашей таблице в той же строке, где находится формула.

3. «Ссылка» - указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).

4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.

5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.

При пользовании данной функцией, расчет рейтинга производится автоматически, и если вы изменили какое-либо значение, то по рейтингу произойдет автоматический пересчет.

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее "перевести")

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (4)

ВПР по двум и более критериям

Наверняка все, кто знаком с функцией ВПР знают, что она осуществляет поиск заданных значений исключительно в левом столбце указанной таблицы(подробнее про ВПР можно прочитать в статье: Как найти значение в другой таблице или сила ВПР). Так же многие знают, что ВПР ищет только на основании одного значения.

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Предположим, что требуется суммировать значения с более чем одним условием, например сумма продаж продукта в определенном регионе. Это хороший случай использования функции СУММЕСЛИМН в формуле.

Обратите внимание на этот пример, в котором у нас есть два условия: требуется сумма продаж мясо (из столбца C) в Южной области (из столбца a).

Вот формула, которую можно использовать для акомплиш:

= СУММЕСЛИМН (D2: D11; a2: A11; "Юг"; C2: C11; "мясо")

Результат - значение 14 719.

Рассмотрим более подробное представление каждой части формулы.

СУММЕСЛИМН - это арифметическая формула. Вычисляет числа, которые в данном случае находятся в столбце D. Первый шаг - указать расположение чисел.

=СУММЕСЛИМН(D2:D11,

Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Этот диапазон ячеек - первый аргумент в этой формуле - первый элемент данных, который функция использует в качестве ввода.

Затем нужно найти данные, отвечающие двум условиям, и ввести первое условие, указав для функции расположение данных (a2: A11), а также то, что такое условие - "Юг". Обратите внимание запятые между отдельными аргументами.

=СУММЕСЛИМН(D2:D11;A2:A11;"Южный";

Кавычки вокруг слова "Юг" определяют, что эти текстовые данные.

Наконец, вы вводите аргументы для второго условия - диапазон ячеек (C2:C11), которые содержат слово "Мясо", а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. Завершите формулу, закрыв закрывающая круглая скобка ) , а затем нажмите клавишу ВВОД. Результат - еще раз в 14 719.

=СУММЕСЛИМН(D2:D11;A2:A11,"Южный";C2:C11,"Мясо")

По мере ввода функции СУММЕСЛИМН в Excel, если вы не помните эти аргументы, Справка готова. После ввода формулы = СУММЕСЛИМН (Автозаполнение формул появится под формулой, а список аргументов будет указан в нужном порядке.

Взгляните на изображение автозаполнения формул и списка аргументов в нашем примере сум_ранже - это D2: D11, столбец чисел, которые требуется суммировать. criteria_range1 - a2. A11 - столбец с данными, в котором находится условие1 "Южный".

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 - это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 - “Мясо”.

Если щелкнуть СУММЕСЛИМН в автозавершении формулы, откроется статья, в которой вы сможете получить дополнительные сведения.

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функцией СУММЕСЛИМН, вот несколько примеров данных и формула, использующая функцию.

В этой Excel в Интернете книге вы можете работать с образцом данных и формулами прямо здесь. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.

Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.

Регион	Продавец		Продажи
Западный
Восточный	Песоцкий
Северный	Песоцкий	Молочные продукты
	Маринова
Восточный	Песоцкий
Северный

Выше мы рассмотрели задачу исследования операций, где требовалось так выбрать решение, чтобы максимизировать (или минимизировать) один-единственный показатель эффективности W. На практике часто встречается случай, когда эффективность операции приходится оценивать не по одному, а сразу по нескольким показателям: одни из этих показателей желательно сделать больше, другие - меньше.

Как правило, эффективность больших по объему, сложных операций не может быть исчерпывающим образом охарактеризована с помощью одного показателя; на помощь ему приходится привлекать и другие, дополнительные.

Например, при оценке деятельности промышленного предприятия приходится учитывать целый ряд показателей, как то:

Прибыль,

Полный объем продукции («вал»),

Себестоимость и т. д.

При анализе боевой операции, помимо основного показателя, характеризующего ее эффективность (например, математическое ожидание причиненного противнику ущерба), приходится учитывать и ряд дополнительных, как то:

Собственные потери,

Время выполнения операции,

Расход боеприпасов и т. д.

Такая множественность показателей эффективности, из которых некоторые желательно максимизировать, а другие - минимизировать, характерна для любой сколько-нибудь сложной задачи исследования операций. Возникает вопрос: как же быть?

Прежде всего надо подчеркнуть, что выдвинутые требования, вообще говоря, несовместимы. Решение, обращающее в максимум один какой-то показатель как правило, не обращает ни в максимум, ни в минимум другие показатели Поэтому широко распространенная формулировка «достижение максимального эффекта при минимальных затратах» для научного исследования не подходит. Корректной является любая из формулировок «достижение максимального эффекта при заданных затратах» или же «достижение заданного эффекта при минимальных затратах».

В общем случае не существует решения, которое обращало бы в максимум один показатель и одновременно в максимум (или минимум) другой показатель тем более, такого решения не существует для нескольких показателей. Однако, количественный анализ эффективности может оказаться весьма полезным и в случае нескольких показателей эффективности.

Прежде всего, он позволяет заранее отбросить явно нерациональные варианты решений, уступающие лучшим вариантам по всем показателям.

Проиллюстрируем сказанное на примере. Пусть анализируется боевая операция О, оцениваемая по двум показателям:

W - вероятность выполнения боевой задачи («эффективность»);

S - стоимость израсходованных средств.

Очевидно, первый показатель желательно обратить в максимум, а второй в минимум.

Предположим для простоты, что предлагается на выбор конечное число - 20 различных вариантов решения; обозначим их Для каждого из них известны значения обоих показателей W и

Изобразим для наглядности каждый вариант решения в виде точки на плоскости с координатами W и S (рис. 1.1).

Рассматривая рисунок, мы видим, что некоторые варианты решения «неконкурентоспособны» и заранее должны быть отброшены. Действительно, те варианты, которые имеют над другими вариантами с той же стоимостью S преимущество по эффективности W, должны лежать на правой границе области возможных вариантов. Те же варианты, которые при равной эффективности обладают меньшей стоимостью, должны лежать на нижней границе области возможных вариантов.

Какие же варианты следует предпочесть при оценке эффективности по двум показателям? Очевидно, те, которые лежат одновременно и на правой, и на нижней границе области (см. пунктирную линию на рис. 1.1). Действительно, для каждого из вариантов, не лежащих на этом участке границы, всегда найдется другой вариант, не уступающий ему по эффективности, но зато более дешевый или, наоборот, не уступающий ему по дешевизне, но зато более эффективный. Таким образом, из 20 предварительно выдвинутых вариантов большинство выпадает из соревнования, и нам остается только проанализировать оставшиеся четыре варианта: . Из них - наиболее эффективный, но зато сравнительно дорогой; - самый дешевый, но зато не столь эффективный. Дело принимающего решение - разобраться в том, какой ценой мы согласны оплатить известное повышение эффективности или, наоборот, какой долей эффективности мы согласны пожертвовать, чтобы не нести слишком больших материальных потерь.

Аналогичный предварительный просмотр вариантов (хотя и без такой наглядной геометрической интерпретации) может быть произведен и в случае многих показателей:

Такая процедура предварительной отбраковки неконкурентоспособных вариантов решения должна всегда предшествовать решению задачи исследования операций с несколькими показателями. Это, хотя и не снимает необходимости компромисса, но существенно уменьшает множество решений, в пределах которого осуществляется выбор.

Ввиду того, что комплексная оценка операции сразу по нескольким показателям затруднительна и требует размышлений, на практике часто пытаются искусственно объединить несколько показателей в один обобщенный показатель (или критерий). Нередко в качестве такого обобщенного (составного) критерия берут дробь; в числителе ставят те показатели которые желательно увеличить, а в знаменателе, - те, которые желательно уменьшить:

Например, если речь идет о боевой операции, в числителе ставят такие величины, как «вероятность выполнения боевой задачи» или «потери противника»; в знаменателе - «собственные потери», «расход боеприпасов», «время выполнения операции» и т. п.

Общим недостатком «составных критериев» типа (5.1) является, то, что недостаток эффективности по одному показателю всегда можно скомпенсировать за счет другого (например, малую вероятность выполнения боевой задачи - за счет малого расхода боеприпасов, и т. п.). Критерии подобного рода напоминают в шутку предложенный Львом Толстым «критерий оценки человека» в виде дроби, где числитель - истинные достоинства человека, а знаменатель - его мнение о себе. Несостоятельность такого критерия очевидна: если принять его всерьез, то человек, почти без достоинств, но зато совсем без самомнения, будет иметь бесконечно большую ценность!

Часто «составные критерии» предлагаются не в виде дроби, а в виде «взвешенной суммы» отдельных показателей эффективности:

где - положительные или отрицательные коэффициенты. Положительные ставятся при тех показателях, которые желательно максимизировать; отрицательные при тех, которые желательно минимизировать. Абсолютные значения коэффициентов («веса») соответствуют степени важности показателей.

Нетрудно убедиться, что составной критерий вида (5.2) по существу ничем не отличается от критерия вида (5.1) и обладает теми же недостатками (возможность взаимной компенсации разнородных показателей). Поэтому некритическое пользование любого вида «составными» критериями чревато опасностями и может привести к неправильным рекомендациям. Однако, в некоторых случаях, когда «веса» не выбираются произвольно, а подбираются так, чтобы составной критерий наилучшим образом выполнял свою функцию, удается получить с его помощью некоторые результаты ограниченной ценности.

В некоторых случаях задачу с несколькими показателями удается свести к задаче с одним-единственным показателем, если выделить только один (главный) показатель эффективности и стремиться его обратить в максимум, а на остальные, вспомогательные показатели наложить только некоторые ограничения вида:

Эти ограничения, разумеется, войдут в комплекс заданных условий

Например, при оптимизации плана работы промышленного предприятия можно потребовать, чтобы прибыль была максимальна, план по ассортименту - выполнен, а себестоимость продукции - не выше заданной. При планировании бомбардировочного налета можно потребовать, чтобы нанесенный противнику ущерб был максимален, но при этом собственные потери и стоимость операции не выходили за известные пределы.

При такой постановке задачи все показатели эффективности, кроме одного, главного, переводятся в разряд заданных условий операции. Варианты решения, не укладывающиеся в заданные границы, сразу же отбрасываются, как неконкурентоспособные. Полученные рекомендации, очевидно, будут зависеть от того, как выбраны ограничения для вспомогательных показателей. Чтобы определить, насколько это влияет на окончательные рекомендации по выбору решения, полезно проварьировать ограничения в разумных пределах.

Наконец, возможен еще один путь построения компромиссного решения, который можно назвать «методом последовательных уступок».

Предположим, что показатели эффективности расположены в порядке убывающей важности: сначала основной затем другие, вспомогательные: Для простоты будем считать, что каждый из них нужно обратить в максимум (если это не так, достаточно изменить знак показателя). Процедура построения компромиссного решения сводится к следующему. Сначала ищется решение, обращающее в максимум главный показатель эффективности Затем назначается, исходя из практических соображений и точности, с какой известны исходные данные (а часто она бывает небольшой), некоторая «уступка» которую мы согласны допустить для того, чтобы обратить в максимум второй показатель Налагаем на показатель ограничение, чтобы он был не меньше, чем где W - максимально возможное значение и при этом ограничении ищем решение, обращающее в максимум Далее снова назначается «уступка» в показателе ценой которой можно максимизировать и т. д.

Такой способ построения компромиссного решения хорош тем, что здесь сразу видно, ценой какой «уступки» в одном показателе приобретается выигрыш в другом.

Заметим, что свобода выбора решения, приобретаемая ценой даже незначительных «уступок», может оказаться существенной, так как в районе максимума обычно эффективность решения меняется очень слабо.

Так или иначе, при любом способе формализации, задача количественного обоснования решения по нескольким показателям остается не до конца определенной, и окончательный выбор решения определяется волевым актом «командира» (так мы условно будем называть ответственное за выбор лицо). Дело исследователя - предоставить в распоряжение командира достаточное количество данных, позволяю. ему всесторонне оценить преимущества и недостатки каждого варианта решения и, опираясь на них, сделать окончательный выбор.