Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Выборки, основанные на одном или нескольких условиях. Ряд функций Excel используют операторы сравнения. Например, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. Эти функции осуществляют выборки на основе одного или нескольких условий (критериев). Проблема в том, что эти функции могут только складывать, подсчитывать количество, и находить среднее. А если вы хотите наложить условия на поиск, например, максимального значения или стандартного отклонения? В этих случаях, поскольку не существует встроенной функции, вы должны изобрести формулу массива. Нередко это связано с использованием оператора сравнения массивов. Первый пример в этой главе, показывает, как рассчитать минимальное значения при одном условии.
Воспользуемся функцией ЕСЛИ, чтобы выбрать элементы массива, отвечающие условию. На рис. 4.1 в левой таблице присутствуют столбец с названиями городов и столбец с временем. Требуется найти минимальное время для каждого города и поместить это значение в соответствующую ячейку правой таблицы. Условие для выборки – название города. Если вы используете функцию МИН, то сможете найти минимальное значение столбца В. Но как вы выберите только те числа, что относятся только к Окленду? И как вам скопировать формулы вниз по колонке? Поскольку в Excel нет встроенной функции МИНЕСЛИ, вам необходимо написать оригинальную формулу, совмещающую функции ЕСЛИ и МИН.
Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города
Скачать заметку в формате или в формате
Как показано на рис. 4.2, вам следует начать ввод формулы в ячейку E3 с функции МИН. Но вы же не можете поместить в аргумент число1 все значения столбца B!? Вы хотите отобрать только те значения, которые относятся к Окленду.
Как показано на рис. 4.3, на следующем этапе введите функцию ЕСЛИ в качестве аргумента число1 для МИН. Вы вложили ЕСЛИ внутрь МИН.
Разместив курсор в месте введения аргумента лог_выражение функции ЕСЛИ (рис. 4.4), вы выделяете диапазон с названиями городов А3:А8, а затем нажимаете F4, чтобы сделать ссылки на ячейки абсолютными (подробнее см., например, ). Затем вы набираете сравнительный оператор – знак равенства. Наконец, вы выделите ячейку слева от формулы – D3, оставляя ссылку на нее относительной. Сформулированное условие позволит выбрать только Окленды при просмотре диапазона А3:А8.
Рис. 4.4. Создайте оператор массива в аргументе лог_выражение функции ЕСЛИ
Итак, вы создали оператор массива с помощью оператора сравнения. В любой момент обработки массива оператор массива является оператором сравнения, так что результатом его работы будет массив, состоящий из значений ИСТИНА и ЛОЖЬ. Чтобы убедиться в этом, выделите массив (для этого щелкните во всплывающей подсказке на аргумент лог_выражение ) и нажмите F9 (рис. 4.5). Обычно вы используете один аргумент лог_выражение, возвращающее либо ИСТИНУ, либо ЛОЖЬ; здесь же результирующий массив вернет несколько значений ИСТИНЫ и ЛЖИ, так что функция МИН выберет минимальное число только для тех городов, которые соответствуют значению ИСТИНА.
Рис. 4.5. Чтобы увидеть массив, состоящий из значений ИСТИНА и ЛОЖь, щелкните во всплывающей подсказке на аргумент лог_выражение и нажмите F9
KPI – показатель эффективности, позволяющий объективно оценить результативность выполняемых действий. Данная система применяется для оценки различных показателей (деятельности всей компании, отдельных структур, конкретных специалистов). Она выполняет не только функции контроля, но и стимулирует трудовую активность. Часто на основе KPI строится система оплаты труда. Это методика формирования переменной части зарплаты.
KPI ключевые показатели эффективности: примеры в Excel
Стимулирующий фактор в системе мотивации KPI – денежное вознаграждение. Получить его может тот работник, который выполнил поставленную перед ним задачу. Сумма премии / бонуса зависит от результата конкретного сотрудника в отчетном периоде. Объем вознаграждения может быть фиксированным или выражаться в процентах к окладу.
Каждое предприятие определяет ключевые показатели эффективности и вес каждого индивидуально. Данные зависят от задач компании. Например:
- Цель – обеспечить план продаж продукта в размере 500 000 рублей ежемесячно. Ключевой показатель – план продаж. Система измерения: фактическая сумма продаж / плановая сумма продаж.
- Цель – повысить сумму отгрузки в периоде на 20%. Ключевой показатель – средняя сумма отгрузки. Система измерения: фактическая средняя величина отгрузки / плановая средняя величина отгрузки.
- Задача – увеличить число клиентов на 15% в определенном регионе. Ключевой показатель – число клиентов в базе данных предприятия. Система измерения: фактическое число клиентов / плановое число клиентов.
Разброс коэффициента (весы) предприятие также определяет самостоятельно. Например:
- Выполнение плана менее 80% - недопустимо.
- Выполнение плана 100% - коэффициент 0,45.
- Выполнение плана 100-115% - коэффициент 0,005 за каждые 5%.
- Отсутствие ошибок – коэффициент 0,15.
- В отчетном периоде не было замечаний – коэффициент 0,15.
Это лишь возможный вариант определения мотивационных коэффициентов.
Ключевой момент в измерении KPI – отношение фактического показателя к плановому. Практически всегда заработная плата сотрудника складывается из оклада (постоянной части) и премии (переменной / изменяемой части). Мотивационный коэффициент влияет на формирование переменной.
Предположим, что соотношение постоянной и изменяемой частей в зарплате – 50 × 50. Ключевые показатели эффективности и вес каждого из них:
Примем следующие значения коэффициентов (одинаковые для показателя 1 и показателя 2):
Таблица KPI в Excel:
Пояснения:
Это примерная таблица KPI в Excel. Каждое предприятие составляет собственную (с учетом особенностей работы и системы премирования).
Матрица KPI и пример в Excel
Для оценки работников по ключевым показателям эффективности составляется матрица, или соглашение о целях. Общая форма выглядит так:
- Ключевые показатели – критерии, по которым оценивается работа персонала. Для каждой должности они свои.
- Веса – числа в интервале от 0 до 1, общая сумма которых равняется 1. Отражают приоритеты каждого ключевого показателя с учетом задач компании.
- База – допустимое минимальное значение показателя. Ниже базового уровня – отсутствие результата.
- Норма – плановый уровень. То, что сотрудник должен выполнять обязательно. Ниже – работник не справился со своими обязанностями.
- Цель – значение, к которому нужно стремиться. Сверхнормативный показатель, позволяющий улучшить результаты.
- Факт – фактические результаты работы.
- Индекс KPI показывает уровень результата по отношению к норме.
Формула расчета kpi:
Индекс KPI = ((Факт – База) / (Норма – База)) * 100%.
Пример заполнения матрицы для офис-менеджера:
Коэффициент результативности – сумма произведений индексов и весов. Оценка эффективности сотрудника наглядно показана с помощью условного форматирования.
Тема: Принятие решений по нескольким критериальным показателям.
В практике обычно приходится выбирать управленческое решение не по одному критерию, а по нескольким. Поэтому их значения при сравнительной оценке имеют разнонаправленный характер, т.е. по одному показателю альтернатива выигрывает, а по другим проигрывает.
В этих условиях необходимо рассматриваемую систему оценок показателей свести к одному комплексному, на основе которого и будет приниматься решение.
Для построения комплексной оценки необходимо решить две проблемы:
Первая проблема заключается в том, что рассматриваемые критериальные показатели имеют неодинаковую значимость;
Вторая проблема характеризуется тем, что показатели оцениваются в различных единицах измерения и для построения комплексной оценки необходимо перейти к единому измерителю.
Первая проблема решается за счет применения одной из четырех модификаций метода экспертных оценок, а именно метода по парного сравнения, что позволяет дать количественную оценку значимости. Суть метода по парного сравнения заключается в том, что эксперт (специалист, потенциальный инвестор, потребитель) проводит по парную оценку рассматриваемых критериальных показателей, определяя для себя их степень важности в виде бальной оценки. После этого, проведя соответствующую обработку полученной информации расчитывается коэффициент значимости по каждому из рассматриваемых критериальных показателей.
Вторая проблема решается путем использования единого измерителя для частных показателей. Чаще всего, в качестве такого измерителя применяется бальная оценка. При этом оценка выполняется двумя подходами:
- первый подход используется при отсутствии статистических данных по значению рассматриваемых показателей;
- второй подход используется при наличии статистических данных (пределов изменения) по значению рассматриваемых показателей.
При использовании первого подхода для перевода в баллы поступают следующим образом: лучшее значение рассматриваемого показателя принимается равным 1 баллу, а худшие значения в долях этого балла. Данный подход прост, дает объективную оценку, но вместе с тем не учитывает лучшие достижения, которые лежат за пределами рассматриваемых вариантов.
Для исключения этого недостатка необходима информация о пределах изменения рассматриваемого показателя. При ее наличии – используется второй подход. В этом случае для перевода в баллы строится шкала перевода. При этом система бальной оценки выбирается с использованием положений теории статистики по формуле Стерджеса:
n = 1 + 3,322 lg N , где
N – число статистических наблюдений;
n – принятая система бальной оценки полученная с использованием правил округления.
Перевод в баллы осуществляется на основе построенной шкалы перевода с применением процедуры интерполирования табличных данных.
Задание:
Из 6-ти вариантов альтернативных решений каждое из которых оценивается 5-ю критериальными показателями необходимо выбрать лучший вариант.
Оценку выполнить используя 2 подхода:
1) при отсутствии статистических данных по значению рассматриваемых показателей;
2) при их наличии.
Пределы изменения показателей установлены по следующим количествам наблюдений (N):
Для четных вариантов N = 8;
Оценку значимости выполнить на основе по парной оценки по мнению исполнителя.
Таблица 1.
Варианты заданий
| № задания | 1 | 2 | 3 | 4 | 5 |
| №№ альтернатив | 1,2,3,4,5,6 | 2,4,8,9,11,15 | 1,3,5,7,9,10 | 4,6,8,12,13,14 | 1,5,10,11,12,15 |
| № задания | 6 | 7 | 8 | 9 | 10 |
| №№ альтернатив | 6,7,10,11,14,15 | 3,4,5,8,9,10 | 7,8,9,10,13,15 | 1,2,3,13,14,15 | 2,4,5,7,12,13 |
| № задания | 11 | 12 | 13 | 14 | 15 |
| №№ альтернатив | 1,7,8,9,10,11 | 6,9,12,13,14,15 | 2,5,7,9,10,11 | 7,8,9,10,11,12 | 1,2,3,4,8,9 |
| № задания | 16 | 17 | 18 | 19 | 20 |
| №№ альтернатив | 1,2,3,10,12,13 | 2,5,7,8,10,15 | 1,6,7,12,13,14 | 3,4,5,6,10,14 | 2,8,11,12,14,15 |
| № задания | 21 | 22 | 23 | 24 | 25 |
| №№ альтернатив | 1,2,6,7,9,10 | 3,5,8,9,13,14 | 4,7,8,10,11,12 | 5,6,7,8,11,13 | 8,9,10,11,12,13 |
| № задания | 26 | 27 | 28 | 29 | 30 |
| №№ альтернатив | 1,3,4,10,11,15 | 2,3,5,8,9,15 | 1,4,7,11,13,15 | 2,6,7,8,12,14 | 1,10,11,12,8,4 |
Таблица 2.
Исходные данные:
| №№ | Альтернативные решения | ||||||||||||||
| показателей | А1 | А2 | А3 | А4 | А5 | А6 | А7 | А8 | А9 | А10 | А11 | А12 | А13 | А14 | А15 |
| X 1 | 5 | 10 | 15 | 6 | 11 | 16 | 7 | 14 | 18 | 20 | 19 | 8 | 21 | 13 | 10 |
| X 2 | 10 | 9 | 8 | 8 | 5 | 7 | 4 | 9 | 5 | 8 | 7 | 7 | 6 | 3 | 2 |
| X 3 | 4 | 3 | 5 | 10 | 6 | 5 | 11 | 7 | 7 | 9 | 8 | 12 | 8 | 5 | 9 |
| X 4 | 1 | 2 | 3 | 4 | 4 | 3 | 2 | 1 | 1 | 3 | 4 | 2 | 2 | 4 | 3 |
| X 5 | 10 | 14 | 13 | 11 | 12 | 20 | 21 | 23 | 17 | 18 | 19 | 24 | 22 | 16 | 18 |
Таблица 3.
Пример:
Даны четыре варианта альтернативных решений, каждый из которых оценивается 5-ю критериальными показателями. Исходя из условий задания необходимо выбрать лучший вариант.
На 1- ом этапе необходимо дать количественную оценку значимости каждого показателя. Используется метод по парного сравнения, в основе которого лежат экспертные оценки.
На основе этой оценки составляется таблица – матрица и расчитывается коэффициент значимости –Kзi.
Количественная оценка значимости показателей определяется следующим образом: если при по парной оценке эксперт (специалист, потенциальный инвестор, потребитель) отдал предпочтение одному из факторов, то в строку и столбец матрицы количественной оценки ставится номер того фактора, которому отдано предпочтение (см. табл. 4). После этого по каждой строке определяется число предпочтений отданных тому или иному фактору при по парной их оценки и их сумма (Σпi). Затем расчитывается коэффициент значимости по следующей формуле:
Количественная оценка значимости показателей:
Таблица 4
| Х1 | Х2 | Х3 | Х4 | Х5 | ΣПi | Kзi | ||
| Х1 | 1 | 1 | 3 | 1 | 5 | 3 | 0,2 | |
| Х2 | 1 | 2 | 2 | 2 | 5 | 3 | 0,2 | |
| Х3 | 3 | 2 | 3 | 4 | 5 | 2 | 0,133 | |
| Х4 | 1 | 2 | 4 | 4 | 5 | 2 | 0,133 | |
| Х5 | 5 | 5 | 5 | 5 | 5 | 5 | 0,333 | |
| ∑∑Пi | 15 | 1 | ||||||
Первый подход.
Первый подход перевода в баллы характеризуется тем, что лучшее значение показателя принимаются равным 1 баллу, худшее оценивается в долях этого балла. Данный подход прост, дает объективную сравнительную оценку, но учитывает лучшие достижения, которые не входят в состав сравнительных вариантов.
| Шифр показателя | Оценка в баллах | Kзi | Оценка в баллах с учетом Kзi | ||||||
| А1 | А2 | А3 | А4 | А1 | А2 | А3 | А4 | ||
| Х1 | 0,3 | 0,35 | 0,7 | 1 | 0,2 | 0,06 | 0,07 | 0,14 | 0,2 |
| Х2 | 0,89 | 0,45 | 1 | 0,89 | 0,2 | 0,178 | 0,09 | 0,2 | 0,178 |
| Х3 | 0,91 | 1 | 0,64 | 0,82 | 0,133 | 0,121 | 0,133 | 0,085 | 0,110 |
| Х4 | 0,25 | 0,5 | 1 | 0,33 | 0,133 | 0,033 | 0,066 | 0,133 | 0,043 |
| Х5 | 1 | 0,52 | 0,48 | 0,61 | 0,333 | 0,333 | 0,173 | 0,159 | 0,203 |
| Комплексная оценка | 0,725 | 0,532 | 0,717 | 0,73 4 | |||||
Например: Х1А1: 6/20=0,3
Х2А1: 8/9=0,89
Вывод: используя первый подход лучшим вариантом из альтернативных будет вариант А4, так как он имеет наибольшую комплексную оценку. Далее идут варианты А1, А3, А2.
Второй подход.
Исключает недостатки первого подхода, но для его использования необходима информация о пределах изменения рассматриваемого показателя. При этом для перевода в баллы строится шкала перевода. Система бальной оценки выбирается на основе положений теории статистики и зависит от числа наблюдений, положенных в основу формирования пределов изменения показателей.
Предположим, в нашем примере проведено 8 наблюдений (N=8), которые позволили установить следующие пределы изменения качественных показателей (см. табл. 3).
При наличии этих показателей строится шкала перевода в баллы.
- формула Стерджеса,
где N – число наблюдений.
Следовательно, оценка качественного показателя будет производиться по 4-х бальной системе, т.е. n = 4.
- размах варьирования,
где - максимальное и минимальное значения из пределов изменения i – показателя.
Шаг изменения показателя.
Шкала перевода в баллы представляет собой таблицу, в которой для каждого балла указываются пределы изменения показателей. При переводе значений показателей в баллы по данной шкале, если значение показателя лежит внутри интервала, то применяют процедуру интерполирования табличных данных.
Шкала перевода в баллы
Далее производится оценка качественных показателей всех изделий в баллах. Например, по альтернативе А1: из исходных данных берется численное значение показателя, затем используя шкалу перевода в баллы определяется интервал куда попадает это значение. После дается бальная оценка: из численного значения показателя вычитается нижний предел изменения показателя в данном интервале делится на шаг и прибавляется предыдущий интервал. По показателям Х4,Х5- из верхнего предела изменения показателя в данном интервале вычитается численное значение показателя делится на шаг и прибавляется предыдущий интервал.
Полученные значения сводятся в нижеследующую таблицу.
| показателя | Оценка в баллах | Kзi | Оценка в баллах с учетом Kзi | ||||||
| А1 | А2 | А3 | А4 | А1 | А2 | А3 | А4 | ||
| Х1 | 0,2 | 0,4 | 1,8 | 3 | 0,2 | 0,04 | 0,08 | 0,36 | 0,6 |
| Х2 | 3 | 1 | 3,5 | 3 | 0,2 | 0,6 | 0,2 | 0,7 | 0,6 |
| Х3 | 2,33 | 2,66 | 1,33 | 2 | 0,134 | 0,313 | 0,357 | 0,179 | 0,268 |
| Х4 | 0 | 2,34 | 4 | 1,67 | 0,134 | 0 | 0,314 | 0,536 | 0,224 |
| Х5 | 3,04 | 1,44 | 1,12 | 1,92 | 0,334 | 1,02 | 0,481 | 0,374 | 0,642 |
| Комплексная оценка | 1,973 | 1,432 | 2,149 | 2,334 | |||||
Вывод: используя второй подход лучшим вариантом из альтернативных будет вариант А4, так как он имеет наибольшую комплексную оценку. Далее идут варианты А3, А2, А1.
Предположим что у Вас есть вот такой отчёт по продажам торговых представителей:
Из него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе .
ПРОБЛЕМА : Как суммировать данные по нескольким критериям??
РЕШЕНИЕ
: Способ 1:
БДСУММ(A1:G16;F1;I1:K2)
В английской версии:
DSUM(A1:G16,F1,I1:K2)
КАК ЭТО РАБОТАЕТ:
Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле " = F1 ) по заданным в ячейках I1:K2 (Продавец = Иванов ; Продукция = Карандаши ; Месяц = Январь ) критериям.
МИНУСЫ : Список критериев должен быть на листе.
ПРИМЕЧАНИЯ
: Количество критериев суммирования ограничено оперативной памятью.
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel
Способ 2:
СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
В английской версии:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
КАК ЭТО РАБОТАЕТ:
Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel.
Если-бы вычисления производились в ячейках листа (для наглядности я всю работу формулы продемонстрирую так, как-будто вычисления происходят на листе, а не в памяти), то массивы выглядели бы так:
Очевидно что если например, D2=Карандаши , то значение будет равно ИСТИНА, а если D3=Папки , то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши ).
Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1.
Перемножив полученные значения массивов между собой последовательно, мы получим ОДИН массив из нолей и единиц. Там где выполнялись все три критерия отбора, (ИВАНОВ, КАРАНДАШИ, ЯНВАРЬ
) т.е. все условия принимали значения ИСТИНА получаем 1 (1*1*1 = 1), если же хотя-бы одно условие не выполнялось - получим 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1*1 = 0).
Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать (диапазон F2:F16 ) и собственно, просуммировать то что на 0 не умножилось.
Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным).
Думаю всё понятно:)
МИНУСЫ : СУММПРОИЗВ - "тяжёлая" формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта.
ПРИМЕЧАНИЯ
ОБЛАСТЬ ПРИМЕНЕНИЯ : Любая версия Excel
Способ 3: Формула массива
СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))
В английской версии:
SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
КАК ЭТО РАБОТАЕТ: Точно так же как и Способ №2. Есть только два отличия - данная формула вводится нажатием Ctrl+Shift+Enter , а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ.
МИНУСЫ : Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.
ПРИМЕЧАНИЯ
: Количество обрабатываемых массивов ограничено 255.
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel
Способ 4:
СУММЕСЛИМН(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)
Условное форматирование (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}}
Похожие статьи
