1. В дополнительный столбец, в котором будем указывать рейтинг, вставляем функцию РАНГ (пишем в ячейке =РАНГ и выбираем из списка предложенную EXCEL функцию, жмем в строке формул fx)
2. Заполняем аргументы в открывшемся окне: «Число» - указываем первое значение в нашей таблице в той же строке, где находится формула.
3. «Ссылка» - указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).
4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.
5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.
При пользовании данной функцией, расчет рейтинга производится автоматически, и если вы изменили какое-либо значение, то по рейтингу произойдет автоматический пересчет.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA
и далее "перевести")
Во второй части нашего учебника по функции ВПР (VLOOKUP) в Excel мы разберём несколько примеров, которые помогут Вам направить всю мощь ВПР на решение наиболее амбициозных задач Excel. Примеры подразумевают, что Вы уже имеете базовые знания о том, как работает эта функция. Если нет, возможно, Вам будет интересно начать с первой части этого учебника , в которой объясняются синтаксис и основное применение ВПР . Что ж, давайте приступим.
Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.
Предположим, у нас есть список заказов и мы хотим найти Количество товара (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 (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.
Если Вам необходимо обновить основную таблицу (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-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!
Предположим, в одном столбце таблицы записаны имена клиентов (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)
После этого Вы можете использовать обычную функцию ВПР , чтобы найти нужный заказ. Например:
VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)
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;ИСТИНА);"")
В этой формуле:
Эта формула находит только второе совпадающее значение. Если же Вам необходимо извлечь остальные повторения, воспользуйтесь предыдущим решением.
Если Вам нужен список всех совпадений – функция ВПР тут не помощник, поскольку она возвращает только одно значение за раз – и точка. Но в 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 , чтобы правильно ввести формулу массива.
Если Вам интересно понять, как она работает, давайте немного погрузимся в детали формулы:
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,"","",""}
ROW()-3
СТРОКА()-3
Здесь функция ROW (СТРОКА) действует как дополнительный счётчик. Так как формула скопирована в ячейки F4:F9, мы вычитаем число 3 из результата функции, чтобы получить значение 1 в ячейке F4 (строка 4, вычитаем 3), чтобы получить 2 в ячейке F5 (строка 5, вычитаем 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 , и так далее.
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 и так далее.
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)
В переводе на человеческий язык, данная формула означает:
Использовав 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 будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.
В целом, какой бы из представленных выше методов Вы ни выбрали, результат двумерного поиска будет одним и тем же:
Бывает так, что основная таблица и таблица поиска не имеют ни одного общего столбца, и это мешает использовать обычную функцию ВПР . Однако, существует ещё одна таблица, которая не содержит интересующую нас информацию, но имеет общий столбец с основной таблицей и таблицей поиска.
Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new) , куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old) .
Чтобы добавить цены из второй таблицы поиска в основную таблицу, необходимо выполнить действие, известное как двойной ВПР или вложенный ВПР .
VLOOKUP(A2,New_SKU,2,FALSE)
=ВПР(A2;New_SKU;2;ЛОЖЬ)
Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1 , а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)
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;ЛОЖЬ)
Однако, когда таких таблиц много, функция ЕСЛИ – это не лучшее решение. Вместо нее можно использовать функцию ДВССЫЛ (INDIRECT), чтобы возвратить нужный диапазон поиска.
Как Вы, вероятно, знаете, функция ДВССЫЛ используется для того, чтобы вернуть ссылку, заданную текстовой строкой, а это как раз то, что нам сейчас нужно. Итак, смело заменяем в представленной выше формуле выражение с функцией ЕСЛИ на ссылку с функцией ДВССЫЛ . Вот такая комбинация ВПР и ДВССЫЛ отлично работает в паре:
VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):
Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: 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! (#ССЫЛ!).
Выше мы рассмотрели задачу исследования операций, где требовалось так выбрать решение, чтобы максимизировать (или минимизировать) один-единственный показатель эффективности W. На практике часто встречается случай, когда эффективность операции приходится оценивать не по одному, а сразу по нескольким показателям: одни из этих показателей желательно сделать больше, другие - меньше.
Как правило, эффективность больших по объему, сложных операций не может быть исчерпывающим образом охарактеризована с помощью одного показателя; на помощь ему приходится привлекать и другие, дополнительные.
Например, при оценке деятельности промышленного предприятия приходится учитывать целый ряд показателей, как то:
Прибыль,
Полный объем продукции («вал»),
Себестоимость и т. д.
При анализе боевой операции, помимо основного показателя, характеризующего ее эффективность (например, математическое ожидание причиненного противнику ущерба), приходится учитывать и ряд дополнительных, как то:
Собственные потери,
Время выполнения операции,
Расход боеприпасов и т. д.
Такая множественность показателей эффективности, из которых некоторые желательно максимизировать, а другие - минимизировать, характерна для любой сколько-нибудь сложной задачи исследования операций. Возникает вопрос: как же быть?
Прежде всего надо подчеркнуть, что выдвинутые требования, вообще говоря, несовместимы. Решение, обращающее в максимум один какой-то показатель как правило, не обращает ни в максимум, ни в минимум другие показатели Поэтому широко распространенная формулировка «достижение максимального эффекта при минимальных затратах» для научного исследования не подходит. Корректной является любая из формулировок «достижение максимального эффекта при заданных затратах» или же «достижение заданного эффекта при минимальных затратах».
В общем случае не существует решения, которое обращало бы в максимум один показатель и одновременно в максимум (или минимум) другой показатель тем более, такого решения не существует для нескольких показателей. Однако, количественный анализ эффективности может оказаться весьма полезным и в случае нескольких показателей эффективности.
Прежде всего, он позволяет заранее отбросить явно нерациональные варианты решений, уступающие лучшим вариантам по всем показателям.
Проиллюстрируем сказанное на примере. Пусть анализируется боевая операция О, оцениваемая по двум показателям:
W - вероятность выполнения боевой задачи («эффективность»);
S - стоимость израсходованных средств.
Очевидно, первый показатель желательно обратить в максимум, а второй в минимум.
Предположим для простоты, что предлагается на выбор конечное число - 20 различных вариантов решения; обозначим их Для каждого из них известны значения обоих показателей W и
Изобразим для наглядности каждый вариант решения в виде точки на плоскости с координатами W и S (рис. 1.1).
Рассматривая рисунок, мы видим, что некоторые варианты решения «неконкурентоспособны» и заранее должны быть отброшены. Действительно, те варианты, которые имеют над другими вариантами с той же стоимостью S преимущество по эффективности W, должны лежать на правой границе области возможных вариантов. Те же варианты, которые при равной эффективности обладают меньшей стоимостью, должны лежать на нижней границе области возможных вариантов.
Какие же варианты следует предпочесть при оценке эффективности по двум показателям? Очевидно, те, которые лежат одновременно и на правой, и на нижней границе области (см. пунктирную линию на рис. 1.1). Действительно, для каждого из вариантов, не лежащих на этом участке границы, всегда найдется другой вариант, не уступающий ему по эффективности, но зато более дешевый или, наоборот, не уступающий ему по дешевизне, но зато более эффективный. Таким образом, из 20 предварительно выдвинутых вариантов большинство выпадает из соревнования, и нам остается только проанализировать оставшиеся четыре варианта: . Из них - наиболее эффективный, но зато сравнительно дорогой; - самый дешевый, но зато не столь эффективный. Дело принимающего решение - разобраться в том, какой ценой мы согласны оплатить известное повышение эффективности или, наоборот, какой долей эффективности мы согласны пожертвовать, чтобы не нести слишком больших материальных потерь.
Аналогичный предварительный просмотр вариантов (хотя и без такой наглядной геометрической интерпретации) может быть произведен и в случае многих показателей:
Такая процедура предварительной отбраковки неконкурентоспособных вариантов решения должна всегда предшествовать решению задачи исследования операций с несколькими показателями. Это, хотя и не снимает необходимости компромисса, но существенно уменьшает множество решений, в пределах которого осуществляется выбор.
Ввиду того, что комплексная оценка операции сразу по нескольким показателям затруднительна и требует размышлений, на практике часто пытаются искусственно объединить несколько показателей в один обобщенный показатель (или критерий). Нередко в качестве такого обобщенного (составного) критерия берут дробь; в числителе ставят те показатели которые желательно увеличить, а в знаменателе, - те, которые желательно уменьшить:
Например, если речь идет о боевой операции, в числителе ставят такие величины, как «вероятность выполнения боевой задачи» или «потери противника»; в знаменателе - «собственные потери», «расход боеприпасов», «время выполнения операции» и т. п.
Общим недостатком «составных критериев» типа (5.1) является, то, что недостаток эффективности по одному показателю всегда можно скомпенсировать за счет другого (например, малую вероятность выполнения боевой задачи - за счет малого расхода боеприпасов, и т. п.). Критерии подобного рода напоминают в шутку предложенный Львом Толстым «критерий оценки человека» в виде дроби, где числитель - истинные достоинства человека, а знаменатель - его мнение о себе. Несостоятельность такого критерия очевидна: если принять его всерьез, то человек, почти без достоинств, но зато совсем без самомнения, будет иметь бесконечно большую ценность!
Часто «составные критерии» предлагаются не в виде дроби, а в виде «взвешенной суммы» отдельных показателей эффективности:
где - положительные или отрицательные коэффициенты. Положительные ставятся при тех показателях, которые желательно максимизировать; отрицательные при тех, которые желательно минимизировать. Абсолютные значения коэффициентов («веса») соответствуют степени важности показателей.
Нетрудно убедиться, что составной критерий вида (5.2) по существу ничем не отличается от критерия вида (5.1) и обладает теми же недостатками (возможность взаимной компенсации разнородных показателей). Поэтому некритическое пользование любого вида «составными» критериями чревато опасностями и может привести к неправильным рекомендациям. Однако, в некоторых случаях, когда «веса» не выбираются произвольно, а подбираются так, чтобы составной критерий наилучшим образом выполнял свою функцию, удается получить с его помощью некоторые результаты ограниченной ценности.
В некоторых случаях задачу с несколькими показателями удается свести к задаче с одним-единственным показателем, если выделить только один (главный) показатель эффективности и стремиться его обратить в максимум, а на остальные, вспомогательные показатели наложить только некоторые ограничения вида:
Эти ограничения, разумеется, войдут в комплекс заданных условий
Например, при оптимизации плана работы промышленного предприятия можно потребовать, чтобы прибыль была максимальна, план по ассортименту - выполнен, а себестоимость продукции - не выше заданной. При планировании бомбардировочного налета можно потребовать, чтобы нанесенный противнику ущерб был максимален, но при этом собственные потери и стоимость операции не выходили за известные пределы.
При такой постановке задачи все показатели эффективности, кроме одного, главного, переводятся в разряд заданных условий операции. Варианты решения, не укладывающиеся в заданные границы, сразу же отбрасываются, как неконкурентоспособные. Полученные рекомендации, очевидно, будут зависеть от того, как выбраны ограничения для вспомогательных показателей. Чтобы определить, насколько это влияет на окончательные рекомендации по выбору решения, полезно проварьировать ограничения в разумных пределах.
Наконец, возможен еще один путь построения компромиссного решения, который можно назвать «методом последовательных уступок».
Предположим, что показатели эффективности расположены в порядке убывающей важности: сначала основной затем другие, вспомогательные: Для простоты будем считать, что каждый из них нужно обратить в максимум (если это не так, достаточно изменить знак показателя). Процедура построения компромиссного решения сводится к следующему. Сначала ищется решение, обращающее в максимум главный показатель эффективности Затем назначается, исходя из практических соображений и точности, с какой известны исходные данные (а часто она бывает небольшой), некоторая «уступка» которую мы согласны допустить для того, чтобы обратить в максимум второй показатель Налагаем на показатель ограничение, чтобы он был не меньше, чем где W - максимально возможное значение и при этом ограничении ищем решение, обращающее в максимум Далее снова назначается «уступка» в показателе ценой которой можно максимизировать и т. д.
Такой способ построения компромиссного решения хорош тем, что здесь сразу видно, ценой какой «уступки» в одном показателе приобретается выигрыш в другом.
Заметим, что свобода выбора решения, приобретаемая ценой даже незначительных «уступок», может оказаться существенной, так как в районе максимума обычно эффективность решения меняется очень слабо.
Так или иначе, при любом способе формализации, задача количественного обоснования решения по нескольким показателям остается не до конца определенной, и окончательный выбор решения определяется волевым актом «командира» (так мы условно будем называть ответственное за выбор лицо). Дело исследователя - предоставить в распоряжение командира достаточное количество данных, позволяю. ему всесторонне оценить преимущества и недостатки каждого варианта решения и, опираясь на них, сделать окончательный выбор.
Тема: Принятие решений по нескольким критериальным показателям.
В практике обычно приходится выбирать управленческое решение не по одному критерию, а по нескольким. Поэтому их значения при сравнительной оценке имеют разнонаправленный характер, т.е. по одному показателю альтернатива выигрывает, а по другим проигрывает.
В этих условиях необходимо рассматриваемую систему оценок показателей свести к одному комплексному, на основе которого и будет приниматься решение.
Для построения комплексной оценки необходимо решить две проблемы:
Первая проблема заключается в том, что рассматриваемые критериальные показатели имеют неодинаковую значимость;
Вторая проблема характеризуется тем, что показатели оцениваются в различных единицах измерения и для построения комплексной оценки необходимо перейти к единому измерителю.
Первая проблема решается за счет применения одной из четырех модификаций метода экспертных оценок, а именно метода по парного сравнения, что позволяет дать количественную оценку значимости. Суть метода по парного сравнения заключается в том, что эксперт (специалист, потенциальный инвестор, потребитель) проводит по парную оценку рассматриваемых критериальных показателей, определяя для себя их степень важности в виде бальной оценки. После этого, проведя соответствующую обработку полученной информации расчитывается коэффициент значимости по каждому из рассматриваемых критериальных показателей.
Вторая проблема решается путем использования единого измерителя для частных показателей. Чаще всего, в качестве такого измерителя применяется бальная оценка. При этом оценка выполняется двумя подходами:
- первый подход используется при отсутствии статистических данных по значению рассматриваемых показателей;
- второй подход используется при наличии статистических данных (пределов изменения) по значению рассматриваемых показателей.
При использовании первого подхода для перевода в баллы поступают следующим образом: лучшее значение рассматриваемого показателя принимается равным 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.
Это глава из книги: Майкл Гирвин. 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
