Расчет KPI в Excel примеры и формулы. Реферат: Принятие решений по нескольким критериальным показателям Используем несколько ВПР в одной формуле

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

Обратите внимание на этот пример, в котором у нас есть два условия: требуется сумма продаж мясо (из столбца 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. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.

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

Поиск в Excel по нескольким критериям

Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets , заказанное покупателем Jeremy Hill , запишите вот такую формулу:

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– эта формула вернет результат 15 , соответствующий товару Apples , так как это первое совпадающее значение.

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2 . Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&” “&C2 . После этого можно использовать следующую формулу:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

Чтобы сделать формулу более читаемой, Вы можете задать имя для просматриваемого диапазона, и тогда формула станет выглядеть гораздо проще:

VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&” “&C2).

Запомните! Функция ВПР ограничена 255 символами, она не может искать значение, состоящее из более чем 255 символов. Имейте это ввиду и следите, чтобы длина искомого значения не превышала этот лимит.

Соглашусь, добавление вспомогательного столбца – не самое изящное и не всегда приемлемое решение. Вы можете сделать то же самое без вспомогательного столбца, но в таком случае потребуется гораздо более сложная формула с комбинацией функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ).

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1 , John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)

После этого Вы можете использовать обычную функцию ВПР , чтобы найти нужный заказ. Например:

• Находим 2-й Dan Brown :

  VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Находим 3-й товар, заказанный покупателем Dan Brown :

  VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4;0)+2)&":$C16");2;ИСТИНА);"")

В этой формуле:

• $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
• $B$ – столбец Customer Name ;
• Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
• $C16 – конечная ячейка Вашей таблицы или диапазона.

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

Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в Excel есть функция INDEX (ИНДЕКС), которая с легкостью справится с этой задачей. Как будет выглядеть такая формула, Вы узнаете в следующем примере.

Извлекаем все повторения искомого значения

Как упоминалось выше, ВПР не может извлечь все повторяющиеся значения из просматриваемого диапазона. Чтобы сделать это, Вам потребуется чуть более сложная формула, составленная из нескольких функций Excel, таких как INDEX (ИНДЕКС), SMALL (НАИМЕНЬШИЙ) и ROW (СТРОКА)

Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Введите эту формулу массива в несколько смежных ячеек, например, в ячейки F4:F8 , как показано на рисунке ниже. Количество ячеек должно быть равным или большим, чем максимально возможное число повторений искомого значения. Не забудьте нажать Ctrl+Shift+Enter , чтобы правильно ввести формулу массива.

Если Вам интересно понять, как она работает, давайте немного погрузимся в детали формулы:

Часть 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – сравниваем значение в ячейке F2 с каждым из значений диапазона B2:B16. Если найдено совпадение, то выражение СТРОКА(C2:C16)-1 возвращает номер соответствующей строки (значение -1 позволяет не включать строку заголовков). Если совпадений нет, функция IF (ЕСЛИ) возвращает пустую строку.

Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив: {1,"",3,"",5,"","","","","","",12,"","",""}

Часть 2:

ROW()-3
СТРОКА()-3

Здесь функция ROW (СТРОКА) действует как дополнительный счётчик. Так как формула скопирована в ячейки F4:F9, мы вычитаем число 3 из результата функции, чтобы получить значение 1 в ячейке F4 (строка 4, вычитаем 3), чтобы получить 2 в ячейке F5 (строка 5, вычитаем 3) и так далее.

Часть 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Функция SMALL (НАИМЕНЬШИЙ) возвращает n-ое наименьшее значение в массиве данных. В нашем случае, какую по счёту позицию (от наименьшего) возвращать – определено функцией ROW (СТРОКА) (смотри Часть 2). Так, для ячейки F4 функция НАИМЕНЬШИЙ({массив};1) возвращает 1-й (наименьший) элемент массива, то есть 1 . Для ячейки F5 возвращает 2-й наименьший элемент массива, то есть 3 , и так далее.

Часть 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Функция INDEX (ИНДЕКС) просто возвращает значение определённой ячейки в массиве C2:C16 . Для ячейки F4 функция ИНДЕКС($C$2:$C$16;1) возвратит Apples , для F5 функция ИНДЕКС($C$2:$C$16;3) возвратит Sweets и так далее.

Часть 5:

IFERROR()
ЕСЛИОШИБКА()

В завершение, мы помещаем формулу внутрь функции IFERROR (ЕСЛИОШИБКА), поскольку вряд ли Вас обрадует сообщение об ошибке #N/A (#Н/Д) в случае, если количество ячеек, в которые скопирована формула, будет меньше, чем количество повторяющихся значений в просматриваемом диапазоне.

Двумерный поиск по известным строке и столбцу

Выполнение двумерного поиска в Excel подразумевает поиск значения по известному номеру строки и столбца. Другими словами, Вы извлекаете значение ячейки на пересечении конкретной строки и столбца.

Итак, давайте обратимся к нашей таблице и запишем формулу с функцией ВПР , которая найдет информацию о стоимости проданных в марте лимонов.

Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.

Функции ВПР и ПОИСКПОЗ

Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:

VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Формула выше – это обычная функция ВПР , которая ищет точное совпадение значения “Lemons” в ячейках от A2 до A9. Но так как Вы не знаете, в каком именно столбце находятся продажи за март, то не сможете задать номер столбца для третьего аргумента функции ВПР . Вместо этого используется функция ПОИСКПОЗ , чтобы определить этот столбец.

MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)

В переводе на человеческий язык, данная формула означает:

• Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
• Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
• Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

Использовав 0 в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР .

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

Функция СУММПРОИЗВ

Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:

SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

Функции ИНДЕКС и ПОИСКПОЗ

В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:

INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Именованные диапазоны и оператор пересечения

Если Вы не в восторге от всех этих сложных формул Excel, Вам может понравиться вот такой наглядный и запоминающийся способ:

При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.

1. Нажмите Enter и проверьте результат

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

Используем несколько ВПР в одной формуле

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

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new) , куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old) .

Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР .

1. Запишите функцию ВПР , которая находит имя товара в таблице Lookup table 1 , используя SKU , как искомое значение:

   VLOOKUP(A2,New_SKU,2,FALSE)
   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1 , а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)

2. Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР :

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2 , а 3 – это столбец C, содержащий цены.

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

В начале разъясним, что мы подразумеваем под выражением “Динамическая подстановка данных из разных таблиц”, чтобы убедиться правильно ли мы понимает друг друга.

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

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

Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:

VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

• $D$2 – это ячейка, содержащая название товара. Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
• $D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
• FL_Sal es и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах. Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10 , но именованные диапазоны гораздо удобнее.

Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.

Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ . Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

• $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
• $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL .
• _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ .

Как работают ДВССЫЛ и ВПР

Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):

• A1 , если аргумент равен TRUE (ИСТИНА) или не указан;
• R1C1 , если F ALSE (ЛОЖЬ).

Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales , FL_Sales , TX_Sales и так далее. Как видите, во всех именах присутствует “_Sales”.

Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку “_Sales”, тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение “FL”, формула выполнит поиск в таблице FL_Sales , если “CA” – в таблице CA_Sales и так далее.

Результат работы функций ВПР и ДВССЫЛ будет следующий:

Если данные расположены в разных книгах Excel, то необходимо добавить имя книги перед именованным диапазоном, например:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Если функция ДВССЫЛ ссылается на другую книгу, то эта книга должна быть открытой. Если же она закрыта, функция сообщит об ошибке #REF! (#ССЫЛ!).

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

План сравнительного анализа

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

Дисциплины, которые основаны на сравнительном методе

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

Достоинства и недостатки метода сравнения

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

Классификация

Сравнительный анализ можно подразделить на:

1. Количественный - анализируются количественные характеристики.
2. Качественный - анализ характеристик, определяющих качество исследуемого объекта, явления, результата.
3. Ретроспективный - анализируются прошедшие изменения, оказывающие влияние на текущее положение.
4. Прикладной - исследуется результат деятельности объекта.
5. Исследовательский - данный вид анализа используется в аналитических науках.
6. Описательный - изначально исследует структуру объекта (явления), постепенно переходя к анализу функций и целей.
7. Общий - основан на общей теории систем.
8. Структурный - данный вид анализа занимается изучением структуру анализируемых объектов.
9. Микросистемный - используется для исследования конкретной системы.
10. Макросистемный - анализируется роль конкретных систем в многообразии схожих систем, связанных между собой общими признаками.
11. Витальный - занимается изучением развития и его основных этапов анализируемой системы.
12. Генетический - исследуется наследственность, ее механизм, генетические системы.
13. Другие виды.

Сравнение в экономике

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

Разновидности метода сравнения, относящего к экономической деятельности

Применительно для исследований в экономической области деятельности бывают следующие виды сравнительного анализа:

1. Горизонтальный и вертикальный. Горизонтальный сравнительный анализ специализируется на определении абсолютного и относительного отклонения между показателями, которые сравнивают между собой. Вертикальный же анализ расчета удельного веса частей позволяет изучать экономические явления, а именно их структуру.
2. Динамический и статический. Динамический метод еще называют временным, он позволяет изучать изменения анализируемых процессов (явлений, объектов) во времени. Статический, или пространственный, изучает уровень одного и того же показателя в течение определенного времени по разным объектам исследования.
3. Одномерный и многомерный. Суть одномерного анализа заключается в том, что или по одному показателю анализируются несколько объектов, или один объект анализируют по разным показателям. Многомерный метод позволяет исследовать несколько объектов по нескольким показателям.
4. По базе сравнения. На этом пункте необходимо остановиться немного подробнее. Дело в том, что этот вид включает в себя несколько подпунктов.

• Первый из них - сопоставление ожидаемых данных с данными периода, рассматриваемых в качестве эталонных. Иными слова, составляется прогноз результатов, которые должны получиться, далее полученный результат сравнивают с подотчетными данными определенного периода.
• Сопоставление планируемых показателей с фактически полученными показателями.
• Сопоставление полученных по факту данных со стандартными значениями показателей, установленными нормами.
• Когда известны среднеотраслевые значения анализируемых показателей, то производится сопоставление фактически полученных данных со среднеотраслевыми.
• Сопоставление фактических данных с данными, полученными в предыдущий период (в прошлом году/месяце/полугодие).
• Сопоставление полученных данных с данными, полученными на ведущих предприятиях, которые являются лидирующими в отрасли.
• Если изменяли какой-либо фактор или изменена производственная ситуация, то производится сопоставление данных, которые были получены до и после внесения изменений.
• И, наконец, производится сравнительный анализ разных вариантов решений в области управления.

Итоги

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

Сравнение моделей корпоративного управления

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

Показатели финансово-экономической деятельности предприятия

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

Примеры, не относящиеся к экономической сфере деятельности

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

Вывод

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

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 баллу, а худшие значения в долях этого балла. Данный подход прост, дает объективную оценку, но вместе с тем не учитывает лучшие достижения, которые лежат за пределами рассматриваемых вариантов.

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

n = 1 + 3,322 lg N , где

N – число статистических наблюдений;

n – принятая система бальной оценки полученная с использованием правил округления.

Перевод в баллы осуществляется на основе построенной шкалы перевода с применением процедуры интерполирования табличных данных.

Задание:

Из 6-ти вариантов альтернативных решений каждое из которых оценивается 5-ю критериальными показателями необходимо выбрать лучший вариант.

Оценку выполнить используя 2 подхода:

при отсутствии статистических данных по значению рассматриваемых показателей;

при их наличии.

Пределы изменения показателей установлены по следующим количествам наблюдений (N):

для четных вариантов N = 8;

Оценку значимости выполнить на основе по парной оценки по мнению исполнителя.

Таблица 1.

Варианты заданий

Таблица 2.

Исходные данные: