Шаг 1 Формирование набора матриц парных сравнений

Тема 2

ЛАБОРАТОРНАЯ РАБОТА № 2

Цель :Изучение технологии обработки результатов экспертных опросов.

Стандартный инструментарий MS Excel, к сожалению, не позволяет рассчитывать значения стандартизированных рангов. Для упрощения расчетов может быть использован макрос, написанный на языке Visual Basic for Applications, позволяющий создать новую функцию St_rang. Рассмотрим процесс разработки динамической модели, позволяющей реализовать весь алгоритм расчетов методом экспертных оценок.

Шаг 1 Формирование набора матриц парных сравнений

1. Создайте новый документ MS Excel

2. Сформировать набор матриц парных сравнений в количестве равном числу экспертов. Исходная форма матрицы представлена на рисунке:

Рисунок 1.51 – Форма матрицы

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

- в ячейку с адресом С4 ввести функцию:

=ЕСЛИ(D3=0;2;ЕСЛИ(D3=2;0;ЕСЛИ(D3=1;1)))

Теперь при вводе в ячейку с адресом D3 значения «2» в ячейке С4 будет выводиться значение «0», как и предполагает принцип парных сравнений; при вводе в ячейку D3 значения «0» в ячейке С4 будет выведено значение «2»; ввод единицы приведет к появлению такого же значения в соответствующей ячейке левой нижней диагонали.

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

С5: =ЕСЛИ(E3=0;2;ЕСЛИ(E3=2;0;ЕСЛИ(E3=1;1)))

С6: =ЕСЛИ(F3=0;2;ЕСЛИ(F3=2;0;ЕСЛИ(F3=1;1)))

С7: =ЕСЛИ(G3=0;2;ЕСЛИ(G3=2;0;ЕСЛИ(G3=1;1)))

D5: =ЕСЛИ(E4=0;2;ЕСЛИ(E4=2;0;ЕСЛИ(E4=1;1)))

D6: =ЕСЛИ(F4=0;2;ЕСЛИ(F4=2;0;ЕСЛИ(F4=1;1)))

D7: =ЕСЛИ(G4=0;2;ЕСЛИ(G4=2;0;ЕСЛИ(G4=1;1)))

E6: =ЕСЛИ(F5=0;2;ЕСЛИ(F5=2;0;ЕСЛИ(F5=1;1)))

Е7: =ЕСЛИ(G5=0;2;ЕСЛИ(G5=2;0;ЕСЛИ(G5=1;1)))

F7: =ЕСЛИ(G6=0;2;ЕСЛИ(G6=2;0;ЕСЛИ(G6=1;1)))

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

- Для заполнения диапазона Н3:Н7 рекомендуется использовать функцию =СУММА(ссылка), указав ссылку на диапазон ячеек соответствующей строки.

Рисунок 1.52 – Расчет рангов (шаг 1)

- Для заполнения столбца I рекомендуется использовать встроенную функцию MS Excel РАНГ, имеющую следующий синтаксис:

=РАНГ(число; ссылка; порядок)

«Число» — это ссылка на ячейку диапазона, содержащую число, для которого определяется ранг.

«Ссылка» — это ссылка на диапазон ячеек, содержащий список чисел.

«Порядок» — аргумент, определяющий способ ранжирования. Может принимать два значения: 0 и 1.

Если значение аргумента «порядок» будет установлено равным «0» или опущено, то ранг равный 1 будет присвоен объекту с наибольшей суммой баллов; если значение аргумента «порядок» будет установлено равным «1», то первый ранг получит объект с наименьшей суммой баллов.

Наиболее распространенный подход к ранжированию предполагает присвоение первого ранга объекту с наибольшей суммой баллов. В ячейку I3 введем следующую формулу:

I3: =РАНГ(H3;$H$3:$H$7;0)

Далее рекомендуется растянуть введенную формулу на диапазон ячеек I3:I7. В результате выполнения всех описанных действий получим готовую матрицу парных сравнений:

Рисунок 1.53 – Расчет рангов (шаг 2)

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