Пример использования макросов и программ на VBA для решения практических задач

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

            В рассматриваемом примере, предлагается ознакомиться с приложением на Excel, которым пользуется исследователь при обработке опросных листов, полученных от экспертов. На рис. 1 представлен лист Excel, на котором отображен интерфейс пользователя и таблица с заполненными данными из опросных листов экспертов. Данные вводятся по строкам, что соответствует материалам опросного листа от эксперта. Сколько было собрано опросных листов, столько и предстоит ввести строк в таблицу. В данном случае, имеем 5 проектов, которые оценивали 7 экспертов. Под интерфейсом пользователя в данном случае понимается набор элементов управления и поле да ввода данных. Для того чтобы приступить к работе, требуется очистить поле для ввода данных, для чего предусмотрена кнопка . Так как на последующих листах будут отображаться результаты ранжирования проектов (объектов) и визуальное отображение результатов в виде графика, то действие кнопки «Очистить поле» должно распространяться на все листы книги. Для выполнения функции очистки полей на всех листах книги был создан макрос под именем «Очистить», и привязан к элементу управления – Кнопка.

 

Рис. 1.

 

            Для ввода данных разработана программа на VBA, которая позволяет задать количество рассматриваемых проектов и количество экспертов, материалы от которых вводятся в таблицу. Для запуска программы следует использовать кнопку . Ознакомиться с программными кодами на VBA можно в окне проектов (рис.2 – модуль программа).

Рис. 2.

 

Кнопка  запускает процедуру, выполнение которой приводит к формированию на листе с именем «результаты» преобразованных оценок экспертов в ранги (номера порядковых мест каждого проекта с позиции эксперта); подсчитывает сумму рангов по всем столбцам таблицы (рис. 3), а также на листе с именем «график» (рис. 4) строит гистограмму, на которой отображаются величины рангов по каждому проекту, для анализа выводится величина коэффициента конкордации W (согласованность).

 

Рис. 3.

 

Рис. 4.

 

Задание. Откройте пример описанной задачи, который находится в файле Expert.xls, проведите самостоятельные расчеты для условного примера, в котором рассматриваются марки сотовых телефонов. Разработайте опросный лист, раздайте его членам группы, а затем обработайте полученные данные.

 

Вопросы для самоконтроля

1.      Можно ли поставленную задачу в рассматриваемом примере решить на основе Мастера функций Excel?

2.      Как скопировать готовую программу на VBA в другую книгу Excel?

3.      Можно ли переносить макросы из одной книги в другую книгу Excel?

4.      В чем заключаются действия пользователя при запуске приложения, которое описано в данном примере?