Тема: Принятие решений по нескольким критериальным показателям.
В практике обычно приходится выбирать управленческое решение не по одному критерию, а по нескольким. Поэтому их значения при сравнительной оценке имеют разнонаправленный характер, т.е. по одному показателю альтернатива выигрывает, а по другим проигрывает.
В этих условиях необходимо рассматриваемую систему оценок показателей свести к одному комплексному, на основе которого и будет приниматься решение.
Для построения комплексной оценки необходимо решить две проблемы:
Первая проблема заключается в том, что рассматриваемые критериальные показатели имеют неодинаковую значимость;
Вторая проблема характеризуется тем, что показатели оцениваются в различных единицах измерения и для построения комплексной оценки необходимо перейти к единому измерителю.
Первая проблема решается за счет применения одной из четырех модификаций метода экспертных оценок, а именно метода по парного сравнения, что позволяет дать количественную оценку значимости. Суть метода по парного сравнения заключается в том, что эксперт (специалист, потенциальный инвестор, потребитель) проводит по парную оценку рассматриваемых критериальных показателей, определяя для себя их степень важности в виде бальной оценки. После этого, проведя соответствующую обработку полученной информации расчитывается коэффициент значимости по каждому из рассматриваемых критериальных показателей.
Вторая проблема решается путем использования единого измерителя для частных показателей. Чаще всего, в качестве такого измерителя применяется бальная оценка. При этом оценка выполняется двумя подходами:
- первый подход используется при отсутствии статистических данных по значению рассматриваемых показателей;
- второй подход используется при наличии статистических данных (пределов изменения) по значению рассматриваемых показателей.
При использовании первого подхода для перевода в баллы поступают следующим образом: лучшее значение рассматриваемого показателя принимается равным 1 баллу, а худшие значения в долях этого балла. Данный подход прост, дает объективную оценку, но вместе с тем не учитывает лучшие достижения, которые лежат за пределами рассматриваемых вариантов.
Для исключения этого недостатка необходима информация о пределах изменения рассматриваемого показателя. При ее наличии – используется второй подход. В этом случае для перевода в баллы строится шкала перевода. При этом система бальной оценки выбирается с использованием положений теории статистики по формуле Стерджеса:
n = 1 + 3,322 lg N , где
N – число статистических наблюдений;
n – принятая система бальной оценки полученная с использованием правил округления.
Перевод в баллы осуществляется на основе построенной шкалы перевода с применением процедуры интерполирования табличных данных.
Задание:
Из 6-ти вариантов альтернативных решений каждое из которых оценивается 5-ю критериальными показателями необходимо выбрать лучший вариант.
Оценку выполнить используя 2 подхода:
при отсутствии статистических данных по значению рассматриваемых показателей;
при их наличии.
Пределы изменения показателей установлены по следующим количествам наблюдений (N):
для четных вариантов N = 8;
Оценку значимости выполнить на основе по парной оценки по мнению исполнителя.
Таблица 1.
Варианты заданий
|
№ задания |
|||||
|
№№ альтернатив |
|||||
|
№ задания |
|||||
|
№№ альтернатив |
|||||
|
№ задания |
|||||
|
№№ альтернатив |
|||||
|
№ задания |
|||||
|
№№ альтернатив |
|||||
|
№ задания |
|||||
|
№№ альтернатив |
|||||
|
№ задания |
|||||
|
№№ альтернатив |
Таблица 2.
Исходные данные:
Альтернативные решения |
|||||||||||||||
|
показателей |
А6 |
||||||||||||||
|
X 1 |
|||||||||||||||
|
X 2 |
|||||||||||||||
|
X 3 |
|||||||||||||||
|
X 4 |
|||||||||||||||
|
X 5 |
|||||||||||||||
13.12.5. Интегральные критерии: оценка качества экосистем по нескольким показателям
Классы качества воды по гидробиологическим и
микробиологическим показателям определяются "Правилами контроля качества
воды водосливов и водотоков" [ГОСТ 17.1.3.07–82], которые регламентируют
содержание программ контроля гидрологических, гидрохимических и
гидробиологических показателей, периодичность контроля, а также назначение и
расположение пунктов отбора проб
(табл. 13.7).Согласно этому документу, степень загрязненности воды оценивается
с учетом индекса сапробности по Пантле и Букку в модификации Сладечека,
олигохетного индекса Гуднайта–Уитлея и Пареле, биотического индекса Вудивисса и
традиционного набора микробиологических показателей
Интегральный показатель по Е.В. Балушкиной разработан и используется для оценки состояния экосистем водоемов, подверженных смешанному органическому и токсическому загрязнению. Прошел широкое тестирование в системе Ладожское озеро – р. Нева – восточная часть Финского залива [Балушкина с соавт.,1996]. Интегральный показатель IP рассчитывается по формуле:
IP = K 1 * S t + K 2 *OI + K 3 *K ch + K 4 / BI ,
где S t – индекс сапротоксобности В.А. Яковлева (K 1 = 25); OI – олигохетный индекс Гуднайта и Уитлея, равный отношению численности олигохет к суммарной численности зообентоса в процентах (K 2 = 1); K ch – хирономидный индекс Балушкиной (K 3 = 8.7); 1 / BI – величина, обратная биотическому индексу Вудивисса (K 4 @ 100).
Е.В. Балушкина полагает, что полученный ею интегральный показатель включил в себя все лучшие черты родительских индексов и максимально учитывает характеристики донных сообществ: наличие видов-индикаторов сапроботоксобности, соотношение индикаторных групп животных более высокого таксономического ранга, степень доминирования отдельных групп и структуру сообщества в целом.
Комбинированный индекс состояния сообщества по А.И. Баканову. При оценке состояния донных сообществ ряда рек, озер и водохранилищ России для количественной характеристики состояния бентоса автор использовал следующие показатели: численность (N), экз./м 2 ; биомассу (B), г/м 2 ; число видов (S); видовое разнообразие по Шеннону (Н), бит/экз.; олигохетный индекс Пареле (ОИП, %), равный отношению численности олигохет-тубифицид к общей численности бентоса, среднюю сапробность (СС), рассчитываемую как средневзвешенную сапробность трех первых доминирующих по численности видов бентосных организмов. Для объединения значений перечисленных показателей и замене их одним числом предлагается результирующий показатель – комбинированный индекс состояния сообщества (КИСС; [Баканов, 1997]), находимый по обычной методике расчета интегральных ранговых показателей:
где R i – ранг станции по i-му показателю, Р i – "вес" этого показателя, k – число показателей.
Вначале все станции ранжируются по каждому показателю, причем, ранг 1 присваивается максимальным значениям N, B, Н и S. Если на нескольких станциях значения какого-либо показателя были одинаковыми, то они характеризовались одним средним рангом. В статье приводятся разные версии итоговой формулы (4.22) (подчеркнем, что в формулы входят не абсолютные значения показателей, а их ранги):
КИСС = (2B + N + Н + S)/5, где биомассе придан "вес", равный 2, поскольку с ней связана величина потока энергии, проходящей через сообщество, что чрезвычайно важно для оценки его состояния;
КИСС = (2СС + 1.5ОИП + 1.5B + N + Н + S)/8, где считается, что с загрязнением наиболее тесно связана средняя сапробность.
Чем меньше величина КИСС, тем лучше состояние сообщества.
Поскольку состояние сообщества зависит как от естественных факторов среды (глубины, грунта, течения и т.п.), так и от наличия, характера и интенсивности загрязнения, дополнительно рассчитывается комбинированный индекс загрязнения (КИЗ; [Баканов, 1999]), включающий ранговые значения трех показателей:
КИЗ = (СС + ОИП + B)/3 . (4.23)
Ранжирование показателей здесь проводится в обратном порядке (от минимальных значений к максимальным)
КИСС и КИЗ – относительные индексы, ранжирующие станции по шкале, в которой наилучшее по выбранному набору показателей состояние сообщества характеризуется минимальными значениями индексов, наихудшее – максимальными. Кроме значений, характеризующих величины показателей на конкретной станции, рассчитывают их средние значения для всего набора станций. Варьирование величин индексов на отдельных станциях относительно среднего позволяет судить, хуже или лучше обстоят на них дела по сравнению с общей тенденцией.
Вычисление коэффициента ранговой корреляции по Спирмену между значениями КИСС и КИЗ показывает, насколько загрязнение влияет на состояние сообществ зообентоса. Если между значениями этих индексов существует достоверная положительная корреляция, то состояние сообществ донных животных в значительной степени определяется наличием загрязнений (в противном случае оно определяется естественными факторами среды).
| Предыдущая |
Из данной статьи вы узнаете о 5 полезных свойствах сводных таблиц, которые помогу вам быстро и детально проанализировать ситуацию (на примере анализа клиентской базы). Вы узнаете:
1. Как сгруппировать данные;
2. Какие показатели можно рассчитать при сведении данных;
3. Как одновременно рассчитать несколько показателей по одному параметру при сведении данных;
4. Какие дополнительные возможности расчета при сведении данных вы можете использовать?
5. О возможности сортировки.
И на основании этого анализа мы затронем мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках FMCG.
Для начала сделаем сводную таблицу. Возьмем простую таблицу продажи клиентам по дням.
Установим курсор в левый верхний угол нашей таблицы, затем переходим в меню "Вставка" и нажимаем на кнопку "Сводная таблица":
В диалоговом окне "Создание сводной таблицы" нажимаем "ОК":
Получили сводную таблицу на новом листе:
1-е полезное свойство сводной таблицы для бизнес анализа - группировка данных
Итак, у нас есть отгрузки клиентам по дням, мы хотим понять, в каком диапазоне отгрузок у нас максимальные объемы продаж. Для этого нам надо сгруппировать отгрузки в диапазоны.
Перетаскиваем поле "Отгрузка сумма" в область сводной таблицы "Название строк" (зажимаем поле "Отгрузка_сумма" левой кнопкой мыши и перетаскиваем в раздел сводной "Название строк"):
Мы вывели все отгрузки в левый столбец сводной. Теперь устанавливаем курсор наши отгрузки (как на рисунке):
Заходим в меню Excel "Данные" и нажимаем на кнопку "Группировать"
В появившемся диалоговом окне устанавливаем шаг группировки "5000" (вы можете ввести любой) и нажимаем "ОК"
Получаем сгруппированные объемы продаж с заданным шагом:
Для того, чтобы группировка выглядела красиво и воспринималось, нажимаем еще раз кнопку "Группировать" и руками ставим ровные значения, для значения "начиная с" - "-15 000" (ниже минимального значения, кратного 5000) "по" - "45 000" (больше максимальной группы, кратное 5000).
Получаем сгруппированные данные по сумме отгрузке:
2-е полезное свойство сводных таблиц для бизнес анализа -
возможность рассчитать различные сводные параметры по полям из исходной таблицы
Итак, отгрузки сгруппировали, теперь посмотрим - какие объемы продаж приходятся на каждый диапазон отгрузок. Для этого просуммируем отгрузки в сводной.
Зажимаем левой кнопкой мыши поле "Отгрузка_сумма" и перетаскиваем его в поле сводной "Значения":
Сводная по умолчанию рассчитала "Количество по полю Отгрузка_сумма", т.е. кол-во записей в нашей исходной таблице на листе "Данные". Т.к. таблица у нас содержит информацию по продажам клиентам по дням, то наш показатель "Количество по полю Отгрузка_сумма" - это количество отгрузок клиентам.
В итоге в сводной таблице мы видим количество отгрузок клиентам в разных диапазонах отгрузок:
Как нам из количества отгрузок получить сумму отгрузок?
Нажимаем левой кнопкой мыши на поле "Количество по полю Отгрузка сумма" в области сводной таблицы "Значения", и в раскрывшемся меню выбираем "Параметры полей значений…"
В раскрывшемся окне выбираем интересующую нас операцию сведения данных (Сумма, количество, среднее, максимум, минимум...). Выберем нужную нам операцию "сумма" и нажимаем "ОК".
Получаем суммарный объём продаж для каждого диапазона отгрузок:
Т.е. мы видим, какой объем продаж приходится на отгрузки в диапазоне от 0 до 5000 руб, от 5000 до 10 000 руб. и т.д. И видно, что максимальный объём отгрузок приходится на диапазон от нуля до 5000 руб.
3 свойство – возможность для одного поля рассчитывать различные операции сведения данных
Теперь мы хотели бы посмотреть, какое количество отгрузок и какие средние отгрузки у нас приходятся на каждый из диапазонов. Для этого мы с помощью сводной таблицы рассчитаем количество отгрузок и средние отгрузки.
В область сводной таблицы "Значения" перетаскиваем еще 2 раза поле "Отгрузка_сумма" и в параметрах поля значений для второго выбираем "количество" и для третьего поля выбираем "среднее".
Получаем для каждого диапазона отгрузок объем продаж, количество отгрузок и среднюю отгрузку:
Теперь мы видим, в каком диапазоне отгрузок максимальный объем продаж и максимальное количество отгрузок. В нашем примере это для диапазона от 0 до 5000 руб. и объём продаж и количество отгрузок максимально.
4-е свойство сводных таблиц - возможность проводить дополнительные расчеты
Для наглядности анализа данных добавим еще 2 параметра - "Долю по объёма продаж по каждой группе"и "Долю количества отгрузок для каждой группы".
Для этого в поле сводной таблицы "Значения" перетащим еще 2 раза поле "Отгрузка сумма"
Причем для одного параметра в меню "Параметры поля значений" () мы выберем операцию "сумма", а для второго операцию "количество" .
Получаем таблицу следующего вида:
Теперь еще раз заходим в "Параметры полей значений" и входим во вкладку "Дополнительные вычисления":
Выбираем в поле "Дополнительные вычисления" пункт "Доля от общей суммы"
Получаем таблицу, в которой для каждого диапазона отгрузок клиентам мы видим объём продаж, количество отгрузок, среднюю отгрузку, долю объема продаж по каждой группе и долю количества отгрузок по каждой группе:
5 полезное свойство - сортировка
Теперь для наглядности от максимальной к минимальной группе по объёму продаж сделаем сортировку. Для этого установим курсор в поле с объёмом продаж по группам и нажмем на кнопку "сортировка от максимального к минимальному":
Видно, что максимальная группа по объёму продаж и количеству отгрузок - это группа "от 0 до 5000 руб." средние продажи в этой группе составляют 1971 руб.
Обратите внимание! Средняя отгрузка по всем клиентам значительно отличается от 86% отгрузок. Причем отличается значительно
- по всем группам средняя отгрузка равна 2 803 руб. (в строке общий итог).
- А по 86% отгрузок 1 971 руб.
Это серьезная разница, и если мы будем стимулировать продажи опираясь на 86% отгрузок и среднюю по ним - 1 971 руб., то наши действия будут точней, а эффект гораздо выше, т.к. мы сможем заинтересовать максимальное количество клиентов.
Данный пример показывает мощнейшую технику планирования мероприятий по стимулированию сбыта на рынках массового сбыта и прогнозирования объёма продаж, которая может помочь вам значительно влиять на ситуацию и изменять её.
Если есть вопросы, обращайтесь.
Точных вам прогнозов!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :
- Novo Forecast Lite - автоматический расчет прогноза в Excel .
- 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.
Выше мы рассмотрели задачу исследования операций, где требовалось так выбрать решение, чтобы максимизировать (или минимизировать) один-единственный показатель эффективности W. На практике часто встречается случай, когда эффективность операции приходится оценивать не по одному, а сразу по нескольким показателям: одни из этих показателей желательно сделать больше, другие - меньше.
Как правило, эффективность больших по объему, сложных операций не может быть исчерпывающим образом охарактеризована с помощью одного показателя; на помощь ему приходится привлекать и другие, дополнительные.
Например, при оценке деятельности промышленного предприятия приходится учитывать целый ряд показателей, как то:
Прибыль,
Полный объем продукции («вал»),
Себестоимость и т. д.
При анализе боевой операции, помимо основного показателя, характеризующего ее эффективность (например, математическое ожидание причиненного противнику ущерба), приходится учитывать и ряд дополнительных, как то:
Собственные потери,
Время выполнения операции,
Расход боеприпасов и т. д.
Такая множественность показателей эффективности, из которых некоторые желательно максимизировать, а другие - минимизировать, характерна для любой сколько-нибудь сложной задачи исследования операций. Возникает вопрос: как же быть?
Прежде всего надо подчеркнуть, что выдвинутые требования, вообще говоря, несовместимы. Решение, обращающее в максимум один какой-то показатель как правило, не обращает ни в максимум, ни в минимум другие показатели Поэтому широко распространенная формулировка «достижение максимального эффекта при минимальных затратах» для научного исследования не подходит. Корректной является любая из формулировок «достижение максимального эффекта при заданных затратах» или же «достижение заданного эффекта при минимальных затратах».
В общем случае не существует решения, которое обращало бы в максимум один показатель и одновременно в максимум (или минимум) другой показатель тем более, такого решения не существует для нескольких показателей. Однако, количественный анализ эффективности может оказаться весьма полезным и в случае нескольких показателей эффективности.
Прежде всего, он позволяет заранее отбросить явно нерациональные варианты решений, уступающие лучшим вариантам по всем показателям.
Проиллюстрируем сказанное на примере. Пусть анализируется боевая операция О, оцениваемая по двум показателям:
W - вероятность выполнения боевой задачи («эффективность»);
S - стоимость израсходованных средств.
Очевидно, первый показатель желательно обратить в максимум, а второй в минимум.
Предположим для простоты, что предлагается на выбор конечное число - 20 различных вариантов решения; обозначим их Для каждого из них известны значения обоих показателей W и
Изобразим для наглядности каждый вариант решения в виде точки на плоскости с координатами W и S (рис. 1.1).
Рассматривая рисунок, мы видим, что некоторые варианты решения «неконкурентоспособны» и заранее должны быть отброшены. Действительно, те варианты, которые имеют над другими вариантами с той же стоимостью S преимущество по эффективности W, должны лежать на правой границе области возможных вариантов. Те же варианты, которые при равной эффективности обладают меньшей стоимостью, должны лежать на нижней границе области возможных вариантов.
Какие же варианты следует предпочесть при оценке эффективности по двум показателям? Очевидно, те, которые лежат одновременно и на правой, и на нижней границе области (см. пунктирную линию на рис. 1.1). Действительно, для каждого из вариантов, не лежащих на этом участке границы, всегда найдется другой вариант, не уступающий ему по эффективности, но зато более дешевый или, наоборот, не уступающий ему по дешевизне, но зато более эффективный. Таким образом, из 20 предварительно выдвинутых вариантов большинство выпадает из соревнования, и нам остается только проанализировать оставшиеся четыре варианта: . Из них - наиболее эффективный, но зато сравнительно дорогой; - самый дешевый, но зато не столь эффективный. Дело принимающего решение - разобраться в том, какой ценой мы согласны оплатить известное повышение эффективности или, наоборот, какой долей эффективности мы согласны пожертвовать, чтобы не нести слишком больших материальных потерь.
Аналогичный предварительный просмотр вариантов (хотя и без такой наглядной геометрической интерпретации) может быть произведен и в случае многих показателей:
Такая процедура предварительной отбраковки неконкурентоспособных вариантов решения должна всегда предшествовать решению задачи исследования операций с несколькими показателями. Это, хотя и не снимает необходимости компромисса, но существенно уменьшает множество решений, в пределах которого осуществляется выбор.
Ввиду того, что комплексная оценка операции сразу по нескольким показателям затруднительна и требует размышлений, на практике часто пытаются искусственно объединить несколько показателей в один обобщенный показатель (или критерий). Нередко в качестве такого обобщенного (составного) критерия берут дробь; в числителе ставят те показатели которые желательно увеличить, а в знаменателе, - те, которые желательно уменьшить:
Например, если речь идет о боевой операции, в числителе ставят такие величины, как «вероятность выполнения боевой задачи» или «потери противника»; в знаменателе - «собственные потери», «расход боеприпасов», «время выполнения операции» и т. п.
Общим недостатком «составных критериев» типа (5.1) является, то, что недостаток эффективности по одному показателю всегда можно скомпенсировать за счет другого (например, малую вероятность выполнения боевой задачи - за счет малого расхода боеприпасов, и т. п.). Критерии подобного рода напоминают в шутку предложенный Львом Толстым «критерий оценки человека» в виде дроби, где числитель - истинные достоинства человека, а знаменатель - его мнение о себе. Несостоятельность такого критерия очевидна: если принять его всерьез, то человек, почти без достоинств, но зато совсем без самомнения, будет иметь бесконечно большую ценность!
Часто «составные критерии» предлагаются не в виде дроби, а в виде «взвешенной суммы» отдельных показателей эффективности:
где - положительные или отрицательные коэффициенты. Положительные ставятся при тех показателях, которые желательно максимизировать; отрицательные при тех, которые желательно минимизировать. Абсолютные значения коэффициентов («веса») соответствуют степени важности показателей.
Нетрудно убедиться, что составной критерий вида (5.2) по существу ничем не отличается от критерия вида (5.1) и обладает теми же недостатками (возможность взаимной компенсации разнородных показателей). Поэтому некритическое пользование любого вида «составными» критериями чревато опасностями и может привести к неправильным рекомендациям. Однако, в некоторых случаях, когда «веса» не выбираются произвольно, а подбираются так, чтобы составной критерий наилучшим образом выполнял свою функцию, удается получить с его помощью некоторые результаты ограниченной ценности.
В некоторых случаях задачу с несколькими показателями удается свести к задаче с одним-единственным показателем, если выделить только один (главный) показатель эффективности и стремиться его обратить в максимум, а на остальные, вспомогательные показатели наложить только некоторые ограничения вида:
Эти ограничения, разумеется, войдут в комплекс заданных условий
Например, при оптимизации плана работы промышленного предприятия можно потребовать, чтобы прибыль была максимальна, план по ассортименту - выполнен, а себестоимость продукции - не выше заданной. При планировании бомбардировочного налета можно потребовать, чтобы нанесенный противнику ущерб был максимален, но при этом собственные потери и стоимость операции не выходили за известные пределы.
При такой постановке задачи все показатели эффективности, кроме одного, главного, переводятся в разряд заданных условий операции. Варианты решения, не укладывающиеся в заданные границы, сразу же отбрасываются, как неконкурентоспособные. Полученные рекомендации, очевидно, будут зависеть от того, как выбраны ограничения для вспомогательных показателей. Чтобы определить, насколько это влияет на окончательные рекомендации по выбору решения, полезно проварьировать ограничения в разумных пределах.
Наконец, возможен еще один путь построения компромиссного решения, который можно назвать «методом последовательных уступок».
Предположим, что показатели эффективности расположены в порядке убывающей важности: сначала основной затем другие, вспомогательные: Для простоты будем считать, что каждый из них нужно обратить в максимум (если это не так, достаточно изменить знак показателя). Процедура построения компромиссного решения сводится к следующему. Сначала ищется решение, обращающее в максимум главный показатель эффективности Затем назначается, исходя из практических соображений и точности, с какой известны исходные данные (а часто она бывает небольшой), некоторая «уступка» которую мы согласны допустить для того, чтобы обратить в максимум второй показатель Налагаем на показатель ограничение, чтобы он был не меньше, чем где W - максимально возможное значение и при этом ограничении ищем решение, обращающее в максимум Далее снова назначается «уступка» в показателе ценой которой можно максимизировать и т. д.
Такой способ построения компромиссного решения хорош тем, что здесь сразу видно, ценой какой «уступки» в одном показателе приобретается выигрыш в другом.
Заметим, что свобода выбора решения, приобретаемая ценой даже незначительных «уступок», может оказаться существенной, так как в районе максимума обычно эффективность решения меняется очень слабо.
Так или иначе, при любом способе формализации, задача количественного обоснования решения по нескольким показателям остается не до конца определенной, и окончательный выбор решения определяется волевым актом «командира» (так мы условно будем называть ответственное за выбор лицо). Дело исследователя - предоставить в распоряжение командира достаточное количество данных, позволяю. ему всесторонне оценить преимущества и недостатки каждого варианта решения и, опираясь на них, сделать окончательный выбор.
Это глава из книги: Майкл Гирвин. 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
