Практические работы по информатике и информационным технологиям

Раздел 2. Разработка программных кодов на VBA

Обработка данных с использованием средств VBA.

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

Рис. 4. Таблица со списком сотрудников

Таблица с исходными данными (рис 4) может быть использована для обработки данных в Excel стандартными приёмами (сортировки, подведение итогов, отборы по заданным параметрам и т.д.). Можно использовать эту таблицу в качестве источника данных для создания автоматизированных форм, которые не потребуют от исполнителя каких-либо знаний функциональных возможностей Excel. Для этой цели достаточно провести анализ потребностей исполнителя и создать небольшой интерфейс с внедрением на лист Excel элементов управления, которые позволят в автоматизированном режиме выполнять набор стандартных операций. В частности, сохранять требуемые данных в отдельных файлах, осуществлять отбор данных по заданным параметрам, формировать выходные документы. Автоматизация работы с Excel предусматривает внедрение макросов и программ на языке программирования Visual Basic for Application (VBA), о чём в дальнейшем и пойдёт речь.

Проектирование интерфейса анализа количества записей в таблице и отображение данных в виде списка

Алгоритм автоматического определения количества строк в таблице и переноса отдельных полей в окно со списком очевиден. Необходимо с определённого места последовательно просматривать данные одного столбца, проверять на наличие записанного данного в ячейке и передавать в элемент управления поле со списком. В том случае, если в ячейке ничего не будет записано, тогда следует прекратить просмотр элементов столбца и отобразить в текстовом окне на листе Excel количество просмотренных записей в столбце.

Воспользуемся уже готовой книгой Excel с наименованием Form1.xls, откроем этот файл и сохраним его под новым именем (на всякий случай), например, зададим имя файла – Form2.xls. Прежде всего, необходимо подготовить среду для разработки интерфейса пользователя и программных кодов на VBA. Для этого (правильнее всего), работая с Excel 2003, в строке Меню следует выбрать: Вид→Панели инструментов→Visual Basic. На экране появится новая панель инструментов, которую можно ухватить мышкой и переместить в любое место (лучше на общую панель инструментов), панель инструментов Visual Basic с основными элементами показана на рис 5.

Рис. 5. Панель инструментов Visual Basic

Активизация кнопки - Элементы управления, приведёт к открытию дополнительной панели инструментов с таким же названием, на которой находятся основные элементы управления, её внешний вид представлен на рис 6. Если подвести указатель мыши к любой пиктограмме на панели инструментов, то будет показана подсказка с наименованием элемента.

Рис. 6. Панель – Элементы управления

Пиктограмма - Режим конструктора при нажатом положении позволяет на рабочем листе поместить необходимый элемент управления, а затем масштабировать его и изменять (задавать) свойства. Так, для запуска программного приложения на VBA, необходимо создать событие – нажать на кнопку (Click). После размещения кнопки на рабочем листе, она будет иметь следующий вид: , что не устраивает разработчика. Поэтому, следует воспользоваться пиктограммой - Свойства (на панели Элементы управления). На рис 7 представлено окно Properties (Свойства) для элемента управления – Кнопка, некоторые свойства были изменены, к ним относятся: BackColor, Caption, Font, ForeColor и MousePointer. Изменение свойств осуществляется либо путём ввода текста в окно с наименованием свойства, либо путём выбора варианта свойства из раскрывающегося списка. Остальные свойства задаются «по умолчанию», т.е. при размещении элемента на листе книги автоматически запоминаются размеры элемента, его местоположение, а так же многие свойства оставляются неизменными, например Visible (Видимость). Обратите внимание, что имя свойства, состоящее из двух и более слов, представляется слитно, так как по правилам программирования, в программных кодах имена переменных и элементов не должны иметь разрывов (символ пробел недопустим).

Рис. 7. Окно свойств элемента управления – Кнопка

Для отображения количества строк в таблице (в столбце) на листе Excel размещён элемент - Поле, которому так же можно задать собственные свойства, например, отображение числа записей в таблице будет: синим цветом, шрифт которого – Arial 18pt. Элемент - Надпись обычно используется для отображения пояснений в виде текста, поэтому его целесообразно размещать около элементов управления. В данном примере на листе Excel создано две надписи около элемента Поле (TextBox) и элемента Поле со списком (ListBox). Для удобства работы пользователя целесообразно создать ещё одну кнопку, при нажатии на которую будут удаляться данные из текстовых окон. В результате, выполняемых действий при размещении элементов управления на листе Excel, внешний вид его будет таким, как показано на рис 8.

Рис. 8. Элементы управления, размещённые на листе Excel

Разработка программы на VBA

После того, как на листе Excel размещены необходимые элементы управления с заданными свойствами, можно приступать к разработке программных кодов на VBA и отладке программы. Удобством VBA является то, что в нем можно использовать в качестве имён переменных зарезервированные слова для обозначения книги, листа и ячеек. Совокупность ячеек представляют двумерный массив, в котором указывается абсолютная адресация текущей ячейки относительно верхнего левого угла. Если результаты обработки данных следует отобразить на том же листе, то в программе нет необходимости указывать имя книги и имя листа.

Разработка программных кодов осуществляется в окне программных кодов (Code), внешний вид которого представлен на рис 9. Открывать окно для создания программных кодов лучше всего двойным щелчком по элементу управления. В рассматриваемой задаче, подразумевается, что при нажатии на кнопку с наименованием «Просмотреть список» будет запущена программа для подсчёта количества строк в списке и в окне Поле со списком будут отображены все фамилии сотрудников.

Рис. 9. Внешний вид окна для записи программных кодов в процедуру

Следует напомнить, что в более сложных задачах возможен вариант, когда потребуется использовать данные из разных книг Excel и листов, которые принадлежат этим книгам, по этой причине следует ориентироваться в именах, которые им присваиваются во время создания. Так, например, рассматривая рис 9, можно увидеть, что книга имеет имя – Form2, а лист, на котором находятся данные носит название – Кадры, хотя другие листы этой книги сохранили свои имена (Лист2 и Лист3), которые им присвоены по умолчанию системой при открытии книги Excel. При внедрении элемента управления Кнопка на лист, система ей присвоила имя (свойство Name) – CommandButton1, которое не менялось (хотя согласитесь, что это слишком громоздко, можно придумать самому имя, например, С1).

Обработка события Click, которое вызывает пользователь, нажав на кнопку с наименованием «Посмотреть список», осуществляется процедурой Private Sub CommandButton1_Click(), внутри которой расположена программа. Программные коды представлены на рис 10.

Рис. 10. Программа обработки события – нажать на кнопку «Посмотреть список»

Конструкция условного оператора While … Wend создана для того, чтобы в цикле просматривать содержимое ячеек, начиная с ячейки B3, что соответствует в записи Cells(i,2), где начальному значению i присвоено число 3 (номер строки), а 2 обозначает номер столбца в таблице. Так как неизвестно, сколько требуется просмотреть строк, то в качестве условия в операторе While записано выражение: Cells(i,2)<>””. Это условие трактуется следующим образом: просматривать ячейки до тех пор, пока их содержимое будет иметь какие-либо символы, в противном случае, когда встретится пустая ячейка – прекратить просмотр и выйти из цикла. Так как оперируем с текстовой информацией, то признаком отсутствия данных в ячейке является сочетание символов “”. Отображение данных осуществляется в окне со списком (ListBox1 – это его имя), в который добавляются значения, полученные из ячеек с номерами сотрудников и фамилий сотрудников. AddItem – это метод (маленькая стандартная подпрограмма), поэтому присоединение метода к имени объекта осуществляется через точку. Выражение: Cells(i, 1) & " " & Cells(i, 2), имеет знак & (амперсанд), который показывает, объединение значений нескольких переменных (в данном случае их 3). Первая переменная Cells(i, 1) – это значение номера сотрудника, вторая " " – пробел, третья Cells(i, 2) – значение из ячейки с фамилией. Таким образом, в окне отобразится номер и фамилия текущего сотрудника, если предварительно отсортировать данные в таблице Excel, например, по фамилиям сотрудников, то номер его сохранится в том виде, какой был ему присвоен при вводе данных. В программе использованы переменные i и k, первая из которых обозначает номер просматриваемого столбца, а вторая введена для подсчёта количества просмотренных ячеек в цикле. Отображение количества просмотренных строк осуществляется в текстовом окне (TextBox) с помощью оператора присвоения TextBox1.Text = k. Следует обратить внимание на то обстоятельство, что объекту с именем TextBox1 присваивается (передаётся) значение переменной k, поэтому к имени переменной через точку добавляется имя свойства Text (TextBox1.Text).

Пользователь может добавлять в исходную таблицу новые строки или вводить исправления в данные, поэтому для того, чтобы посмотреть новый список, целесообразно его очистить. Для этой цели и была создана кнопка «Удалить сведения», текст этой программы приведён на рис 11.

Рис.11. Программа для очистки текстовых окон на листе Excel

Имя кнопки «Удалить сведения» оставлено без изменения, как это предложила система. После свершения события Click по кнопке запускается программа, которая обрабатывает два объекта. Первым объектом является список с именем ListBox1, который содержит несколько строк (комплекс данных), поэтому следует запустить метод Clear (Очистить). Вторым объектом с именем TextBox1 является текстовое окно (Поле) для обнуления которого достаточно свойству Text присвоить пустое значение (пробельную строку). Окончательный вариант интерфейса для пользователя на листе Excel представлен на рис 12. Можете посмотреть пример.

Рис. 12. Отображение данных в виде списка

Особенности разработки интерфейса пользователя на Excel 2007

На рис 13 представлена книга со списком сотрудников, созданная в Excel 2007, на которой расположены элементы управления для автоматизации работы пользователя.

Рис. 13. Отображение панели инструментов для Excel 2007 с размещённой таблицей и элементами управления

Для того чтобы разместить элементы управления на листе Excel 2007, необходимо открыть вкладку Разработчик, нажать на кнопку - Режим конструктора, а затем открыть панель с элементами ActiveX (панель инструментов с наименованием «Элементы управления формы» используется при работе с макросами). Изменение свойств элементов управления, которые размещены на листе Excel, осуществляется нажатием на пиктограмму , после выделения элемента на листе. Разработка программных кодов для поддержки работы интерфейса пользователя ничем не отличается от технологии, которая была описана в разделе выше (можно смело скопировать программные коды с рис 10 и рис 11 в окно Code Visual Basic), если работа ведётся в версии Excel 2007 и выше.

Можно посмотреть пример для Excel 2007.

Передача данных из Excel в текстовые файлы

Данные, которые собирают или создают в Excel, часто требуется использовать в других приложениях. Возникает задача автоматизации формирования массивов с данными таким образом, чтобы сотрудник не использовал ручные операции для открытия книги Excel, поиска и отбора необходимых данных, их копирования и размещения в собственном приложении. Использование VBA делает возможным после обработки данных в Excel одним щелчком мыши сформировать выходной файл для других пользователей. Воспользуемся уже готовым приложением Excel, в котором сформирована таблица с данными о сотрудниках (Form1.xls), добавим в неё столбец «Дата приёма». После заполнения столбца данными (в примере это год приёма на работу), сохраним документ под именем Form3. Предположим, отделу охраны потребуются сведения о сотрудниках организации, чтобы подготовить им новые пропуска. Совершенно очевидно, что, во-первых, отдел кадров не должен передавать все сведения о сотрудниках, во-вторых, отдел охраны должен готовить пропуска только тем сотрудникам, у которых они закончили срок действия или для вновь принятых сотрудников. Следовательно, инициатором формирования списков на выдачу пропусков сотрудникам является отдел кадров, который ведёт учёт движения кадров, в соответствии с приказами по организации. Поэтому, для формирования списка сотрудников на выдачу пропусков в отделе кадров должны провести отбор кандидатур из общего списка, который оформляется в виде отдельного файла и направляется на сервер.

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

- просмотр списка фамилий сотрудников предприятия;

- выбор из списка фамилий и других данных, кому потребуются пропуска;

- формирование файла с данными и отправка его отделу охраны предприятия.

Подготовим интерфейс для работы сотрудника отдела кадров с приложением Excel, внешний вид заготовки представлен на рис 14.

Рис. 14. Интерфейс пользователя для подготовки файла с данными

В качестве нового элемента управления, который появился на листе Excel, выбран: - Поле со списком (ComboBox). Этот элемент управления обладает дополнительными свойствами по сравнению с элементом Список (ListBox). Например, можно сделать раскрывающийся список или, непосредственно, в окне отобразить сведения о его состоянии (чем мы и воспользуемся).

Разработку программы целесообразно начать последовательно для каждого элемента. Чтобы не путаться примем в качестве соглашение – имена объектов на листе (элементов управления) оставим такими, какие им присваивает система. Первое событие, которое должен осуществить сотрудник при работе с данным приложением – указать условия отбора данных из таблицы со сведениями о персонах. Для этого необходимо нажать на кнопку с наименованием «Ввести условия отбора», по заданному условию в элементе Поле со списком должны отобразиться данные о сотрудниках. Поэтому, требуется организовать диалог пользователя с приложением, для этого воспользуемся стандартным диалоговым окном на основе функции InputBox(). Программные коды для обработки события Click по кнопке представлены на рис 15.

Рис. 15. Программа для обработки события - нажать на кнопку «Ввести условие отбора»

Следует напомнить, что в программах используют переменные, зона действий которых распространяется по-разному. Чтобы переменная была доступной в пределах приложения для всех процедур, тогда её необходимо объявить в общей области, если нет необходимости использовать имя переменной во всех процедурах, тогда её объявляют внутри процедуры или внутри оператора. При рассмотрении данной задачи, на это будет обращено внимание. Для отбора сведений из таблицы Excel по заданному критерию необходимо сформировать промежуточные массивы, в которых будут находиться данные, предназначенные для записи в файл. Массивы будут создаваться в процедуре отбора данных из таблицы, а использоваться в процедуре записи данных в файл, поэтому оператор: Dim M1(20), M2(20), M3(20), M4(20) As String, создан раньше объявления всех процедур. По той же причине объявление переменной Dim k As Integer для подсчёта количества отобранных записей вынесено в начало программы. Обратите внимание, что оператор Dim i As Integer находится внутри процедуры, т.к. действие переменной i распространяется только при просмотре ячеек таблицы. Операторы, позволяющие осуществлять удаление данных из поля со списком и из заголовка списка, используются для того, чтобы пользователь мог несколько раз менять своё решение при вводе критерия отбора, не выполняя при этом дополнительных действий. Организация диалогового окна осуществляется функцией InputBox(), у которой задаются аргументы, а результаты ввода сохраняются в переменной y. Рассмотрим несколько подробнее содержимое функции InputBox().

y = InputBox("Введите год, ниже которого будут отобраны сведения из таблицы", _

"Вы указываете критерии отбора")

Диалоговое окно для ввода критерия отбора данных из таблицы представлено на рис 16, у которого есть поле для заголовка, тело для отображения инструкций, указаний и т.п., а так же есть текстовое окно для ввода данных. При нажатии на кнопку ОК на диалоговом окне, значение из текстового окна передаётся переменной y. Если пользователь нажмёт на кнопку Cancel, то будет сгенерирован код конца работы, который передаётся переменной y, обычно программисты это учитывают и создают программные коды, которые анализируют, какую кнопку нажал пользователь. В рассматриваемом примере оператор If y <> "" Then проверяет наличие любого кода, введённого в текстовую строку. В том случае, если нажата кнопка Cancel, то код будет пустым, после этого оператора ни одна инструкция в программе не будет выполнена (завершение проверки условия End If стоит в самом конце процедуры).

Рис. 16. Диалоговое окно для ввода критерия отбора данных из таблицы

Далее, в процедуре по отбору данных встречается конструкция While … Wend, которая была описана в предыдущем примере. Внутри этой конструкции встроен условный оператор If … Then … Else. Условием является сравнение текущего значения в ячейках с датой поступления сотрудников на работу с критерием отбора. Так как в ячейках находятся данные в числовом формате, а критерий отбора введён в текстовом виде, то использована функция Int(), с помощью которой текстовые данные преобразуются в целые числа (заметим, что аргументы функции всегда заключены в круглые скобки). При выполнении заданного условия (“<=”) осуществляется сохранение данных в массивах (Фамилия, Имя, Отчество, Должность) и идёт заполнение строк элемента управления -Поле со списком. По окончанию просмотра ячеек таблицы со сведениями о годе поступления на работу, осуществляется замена текста в заголовке элемента – Поле со списком. На рис 17 показан результат отбора записей при заданном критерии отбора (в диалоговом окне указан 2008 год).

Рис. 17. Пример отбора данных по заданному критерию

Передача, отобранных сведений из таблицы, осуществляется после того, как пользователь нажмёт на кнопку «Подготовить файл». В этом случае будет запущена процедура, текст которой представлен на рис 18.

Рис. 18. Программные коды процедуры - запись данных в файл

Самым простым способом создавать файл является - указать полный путь к файлу в программе, хотя есть и другие способы, о которых будет сказано ниже. В данном примере рассмотрим, как работает оператор Open. Для создания файла необходимо задать путь к файлу: "C:\Prim-vba\Sved.txt", режим - For Output (открыть файл для записи), и задать указатель файла - As #1 (цифра может быть любой). Если файла с указанным именем нет, то он будет создан, если такое имя существует, то запись начнётся с начала, а все предыдущие записи будут удалены. Путь к файлу C:\Prim-vba\ - содержит: имя диска, и наименования папок. В том случае, если наименований указанных папок не существует, то система даст сообщение об ошибке. Завершение записи осуществляется операцией закрытия (добавление специального символа в конец файла) с помощью оператора Close #1. В данном примере, необходимо содержание массивов с данными передать в файл, для чего использована конструкция For … Next.

В операторе Write #1, M1(j); M2(j); M3(j); M4(j) даётся ссылка на указатель файла и после запятой указывается список переменных, отделяемых друг от друга знаком разделителя (в данном примере знаком разделителя данных является – точка с запятой «;»). Результаты созданного файла с оператором Write показаны на рис 19. Вместо оператора Write можно использовать оператор Print, тогда знак разделителя не будет отображаться.

Рис. 19. Отображение в приложении Блокнот, записанных сведений в файл

Пользователь получает на экране компьютера подтверждение (рис 20), которое создано с помощью функции MsgBox(). Обратите внимание, что при задании в функции аргумента – vbInformation, на диалоговом окне отображается только одна кнопка и символ информация.

Рис. 20. Сообщение пользователю о завершении записи данных в файл

Процедура очистки окон запускается нажатием на кнопку «Очистить список», как она создавалась описание дано в предыдущем примере.

Посмотрите работу примера в Excel 2003

Посмотрите работу примера в Excel 2007

Использование файлов с данными для работы в Excel

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

Рис. 21. Лист Excel с заготовкой для вывода данных

Рамка обозначает внешний вид документа (Пропуск), внутри рамки обведены ячейки, которые залиты одним цветом. На документе размешены его атрибуты (рисунок в виде флага; название организации; заголовок документа «Пропуск»; реквизиты документа фамилия, имя, отчество, должность). Ячейки B3:G3 объединены , в которых записано название организации, в строке 4 крупным шрифтом написано слово «ПРОПУСК», в ячейке B8 – Должность и т.д. (все ячейки, которые использованы для шаблона на рис 21 выделены другим цветом). В ячейке С10 изменён формат представления данных с Общего на формат Дата, обратите внимание, что в эту ячейку вставлена формула: =СЕГОДНЯ(). Варианты автоматизации получения пропуска для сотрудника с заполненными данными, отличаются друг от друга интерфейсом, который расположен на листе с наименованием «Список».

Вариант 1. Импорт текстовых данных на лист Excel

Список сотрудников из файла поместим на листе Excel с наименованием «Список», для чего откроем в строке Меню→ Данные→ Импорт внешних данных→ Импортировать данные, как показано на рис 22.

Рис. 22. Подготовительные действия для импорта таблицы из файла

При импорте внешних данных на лист Excel, сначала следует найти местоположение файла (с помощью окна – Импорт текстового файла), а затем провести три последовательных шага в приложении – Мастер текстов.

Для тех, кто работает с Excel 2007, необходимо в Меню раскрыть вкладку Данные, чтобы обратиться к списку команд группы – Получить внешние данные. На рис 23 выделена пиктограмма – Из текста, которая запускает диалоговое окно для поиска файла и Мастер текстов, при работе с этими диалоговыми окнами, все последующие действия при импорте текстового файла будут аналогичны, как в Excel 2003.

Рис. 23. Импортирование внешних данных, созданных в виде текста

Приложение Мастер текстов преобразует строки в отдельные записи (создаёт списки), поэтому при выполнении второго шага, следует указать, что является символом-разделителем в файле. На рис 24 показано окно Мастер текстов, в котором отмечен символ-разделитель – запятая.

Рис. 24. Пример преобразования строк текста в табличные данные с помощью приложения Мастер текстов

Теперь следует позаботиться, чтобы данные о сотруднике с листа «Список» попали на лист «Пропуск». Передача сведений о сотруднике, которые предварительно выделяют в списке, будет осуществляться после нажатия на кнопку с наименованием «Подготовить пропуск» на листе с наименованием «Список», как показано на рис 25.

Рис. 25. Пример импортированного списка на лист Excel для подготовки пропуска сотруднице Вавиловой

Так как на листе Excel размещена одна кнопка, то только для неё создаётся программный код, представленный на рис 26. Рассмотрим некоторые особенности программы, с помощью которой создаётся диалоговое окно для ввода диапазона ячеек со сведениями о сотруднике, а затем эти сведения передаются на лист «Пропуск» в шаблон.

Воспользуемся возможностями объекта Application, который обладает обширной коллекцией свойств, позволяет программно установить значения многих опций для объектов, которые используются в программе VBA (подробно об объекте Application можно ознакомиться в литературе, например, [1]). В данной программе используем функцию InputBox() в качестве диалогового окна для сбора параметров о сотруднике. Поэтому, сначала дадим описание параметров этого окна в операторе Set (Установить), запись будет выглядеть следующим образом:

Set input_sveden = Application.InputBox( _

Title:="Найдите необходимую ячейку", _

Prompt:="Выделите в таблице ячейки в строке," & Chr(13) _

& "которые относятся к сотруднику", _

Type:=8)

Зададим имя переменной - input_sveden, которой будут передаваться сведения из текстовой строки диалогового окна. Application.InputBox - это новый объект (на основе функции InputBox). Объекту следует задать параметры, которые являются атрибутами функции, к которым относятся:

Title – строковое выражение, отображаемое в строке заголовка диалогового окна;

Prompt – строковое выражение, отображаемое как сообщение в диалоговом окне;

Type – параметр, указывающий что должно отображаться в текстовом окне (код 8 – адрес выделяемой ячейки на листе).

Рис. 26. Программные коды для обработки выделенной строки после свершения события Click при передаче в шаблон документа

Для того, чтобы осуществить работу с выделенными ячейками на листе, необходимо выяснить их количество (количество столбцов и количество строк), что осуществляется операторами:

Num_col = input_sveden.Columns.Count

Num_row = input_sveden.Rows.Count

Операторы цикла осуществляют просмотр содержимого переменной input_sveden с последующей записью в индексированный массив Mas(i), далее формируется общая строка FIO, содержащая фамилию имя и отчество сотрудника. Для создания сообщения о выполнении работы использован оператор MsgBox (обратите внимание, в операторе не используются круглые скобки). Но вместе с тем, в этот оператор встроена функция Chr(13), которая воспринимает код 13, как возврат каретки и перевод курсора на новую строку, что позволяет в диалоговом окне сообщение делить на несколько строк. Пример результатов выполнения запроса показан на рис 27.

Рис. 27. Пример использования оператора MsgBox для формирования сообщения в виде диалогового окна

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

Worksheets("Пропуск").Cells(6, 2) = FIO

Worksheets("Пропуск").Cells(8, 3) = Mas(4)

Посмотрите пример работы программы для Excel

Пример, который работает только в Excel 2007 и выше

Вариант 2. Использование данных непосредственно из файла.

Можно создать интерфейс для пользователя на листе Excel, для того, чтобы (в данном примере) сотрудник отдела охраны, имел возможность выбирать файл, отбирать необходимые сведения, распечатывать бланк пропуска с готовыми данными. Создадим новую книгу Excel, присвоим ей имя – Form5. Первый лист книги назовём «Сотрудники», а второй лист с наименованием «Пропуск» скопируем из предыдущего примера. На листе «Сотрудники» расположим необходимые элементы управления как показано на рис 28.

Рис. 28. Проект интерфейса пользователя на листе Excel

Для осуществления операции просмотра файлов введём новый элемент управления, на панели инструментов пиктограммой - Другие элементы, После нажатия на эту пиктограмму, откроется список, из которого выберем - Common Dialog (Общий диалог). Класс объектов управления Общий диалог позволяет открыть в работающем приложении одну из стандартных диалоговых панелей, например, Открытие файла (File Open), Сохранение файла (File Save As), Выбор шрифта (Select Font) и другие, как показано на рис 29 выделяют название элемента, а затем нажимают на кнопку ОК.

Рис. 29. Выбор дополнительного объекта в разделе Windows – Другие элементы

Пиктограмма - Общий диалог появится на листе Excel, после чего необходимо установить свойства элемента. В данном примере, свойству Name (Имя) присвоим – CD1. Напомним, что элементы управления, которые относятся к интерфейсу пользователя, по умолчанию имеют заданные свойства, изменим некоторые свойства, которые перечислены в таблице 2.

Таблица 2. Наименования элементов управления и их свойства в программе

Наименование элемента	Свойство	Значение
CommandButton1	Name	CB1
CommandButton1	Caption	Посмотреть файлы
Label1	Caption	Для выбора фамилии из списка, выделите её мышкой
ListBox1	Name	LB1
CommandButton2	Name	CB2
CommandButton2	Caption	Подготовить пропуск
Label2	Caption	Вы выбрали фамилию, можете сделать новый выбор
Label3	Name	LL2
Label3	Caption	«Пусто»

Интерфейс пользователя включает две кнопки. Первая кнопка с наименованием «Посмотреть файлы» должна обеспечить пользователя поисковым средством для выбора файла (где бы он не находился), отобразить фамилии сотрудников в виде списка на экране в объекте с именем LB1. Программа обработки события, представлена на рис 30.

Рис. 30. Программа обработки события при нажатии на кнопку с наименованием «Поиск файлов»

Обратите внимание, что в общую область вынесено два оператора Dim, для описания переменных, которые будут использованы во всех процедурах. В то время как переменные Puti и k используются только в процедуре CB1_Click(). В предыдущих примерах было дано подробное описание работы с оператором If … else … end и функцией MsgBox(), поэтому покажем новые элементы программы. Так, запись CD1.ShowOpen, есть не что иное, как открытие диалогового окна для поиска файлов. Метод ShowOpen позволяет запустить диалоговое окно в режиме поиска файлов, как показано на рис 31.

Рис. 31. Диалоговое окно для поиска файлов

Путь к файлу (перечень устройств и папок), а так же имя файла сохраняются в переменной: Puti = CD1.Filename, где CD1 – имя объекта Общий диалог, а Filename – свойство, которому передаётся путь к файлу.

Оператор Open Puti For Input As #2 указывает, что необходимо открыть содержимое файла, находящегося по адресу Puti.

Новый приём создания цикла основан на конструкции While … Wend. Внутри цикла необходимо заполнить строки поля со списком (его имя LB1), и передать все значения о сотрудниках в массивы. При просмотре файла, неизвестно, сколько находится записей в нём, поэтому, в операторе начала цикла в качестве условия стоит признак конца файла – EOF (End of File). Когда условие выполнится, произойдёт выход из цикла.

Следующее действие, которое должен совершить пользователь, это отметить в списке фамилию и нажать на кнопку «Подготовить список» (можно упростить задачу, ликвидировать эту кнопку, а обрабатывать событие щелчка мышью в строке списка). Для обработки события – щелчок мыши, программа будет выглядеть, как показано на рис 32.

Рис. 32. Программа формирования документа на листе «Пропуск»

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

j = LB1.ListIndex переменная j используется для получения данных из массивов. Результаты работы пользователя представлены на рис 33.

Рис. 33. Результаты работы с интерфейсом на листе Excel

Посмотрите пример в Excel

Пример, который работает только в Excel 2007 и выше

Раздел 3. Макросы в Excel

Направления использования макросов

При автоматизации работ с часто повторяющимися действиями, процесс их выполнения осуществляется с помощью макросов. Макрос (macro) – это последовательность команд, которые написаны на VBA и которые хранятся в стандартном модуле среды разработки VBA приложений. Макросы могут осуществлять широкий спектр задач от простых вычислений до создания пользовательского интерфейса вашего приложения. Выделяются три основные разновидности макросов [3]:

Ø Командные – основными предназначениями, которых является выполнение действий, аналогично командам меню.

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

Ø Макрофункции – представляют собой сочетание командных макросов и пользовательских функций. Если необходимо в различных макросах выполнять ряд одинаковых действий, то эти действия обычно выделяются в отдельную макрофункцию (подпрограмму). Фактически создаются предпосылки для организации модульного программирования в среде VBA.

Корпорация Microsoft разработала и внедрила в приложения MS Windows специальную программу, которую назвали – Макрорекордер. С его помощью осуществляется формирования программного кода, который оформляется в виде отдельного модуля. Суть работы макрорекордера заключается в следующем:

§ Пользователь запускает программу – Начать запись (в дальнейшем – программа).

§ Программа перехватывает события, которые вызывает пользователь (выделение ячеек на листе, нажатие в строке меню на строку с указанием программы, например, копировать) и преобразует эти события в стандартные команды и операторы на языке VBA.

§ Пользователь завершает создание макроса – Остановить запись.

§ Программа формирует модуль, имя которому присваивает пользователь.

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

Создание макросов

Прежде чем запустить макрорекордер для создания макроса, надо тщательно спланировать ваши действия. Чтобы освоить порядок работы, создадим интерфейс пользователя в виде командного макроса. Например, созданный список сотрудников (пример 1 – Form1.xls) необходимо скопировать в новую книгу Excel и сохранить её в определённой папке. Для выполнения данной работы потребуются типовые команды, которые нет смысла самому писать на VBA, пусть это сделает макрорекордер.

Для активизации макрорекордера в Excel 2003 в строке Меню→ Сервис→Макрос→ Начать запись, как показано на рис 34.

Рис. 34. Порядок запуска макрорекордера через строку Меню

Тот, кто работает с Excel 2007 и выше, следует в строке меню открыть вкладку Вид, а затем раскрыть пиктограмму Макросы, чтобы выбрать команду Запись макроса, как показано на рис 35.

Рис. 35. Последовательность доступа к команде Запись макроса

После того, как будет выполнена команда Запись макроса, на экране появится окно с одноимённым названием, которое показано на рис 36.

Рис. 36. Окно для записи параметров макроса

В диалоговом окне Имя макроса предлагает система, можно дать ему любое имя, например, Копия_списка. Обратите внимание, что в именах не должно быть пробелов. Поле Сочетание клавиш состоит из обязательной клавиши Ctrl и той, которую назначает пользователь (можно не назначать). Система предлагает сохранить макрос в различных вариантах, выбираем – Эта книга.

Поле Описание макроса заполняется пользователем в текстовом окне.

После того, как будет нажата кнопка ОК в окне Запись макроса, макрорекордер начнёт отслеживать действия пользователя и создавать программу на VBA. Алгоритм действий понятен, состоит из следующих этапов: открыть имеющийся файл с данными; выделить область ячеек, которые следует скопировать; выбрать из меню команду копировать; открыть новую книгу Excel; дать команду вставить на лист содержимое буфера обмена; сохранить новую книгу в виде файла; закрыть этот файл. Завершение работы макрорекордера осуществляется командой , которая находится в группе команд Макрос.

Посмотрите, как работает созданный макрос, который можно запустить сочетанием клавиш Ctrl+a.

Работа с макросом

Созданный макрос «Копия_списка» сохраняется в виде отдельного модуля в книге файла Form1.xls. Для того, чтобы посмотреть содержимое программы на VBA или запустить макрос, достаточно открыть диалоговое окно, нажав на команду в меню , представленное на рис 37.

Рис. 37. Диалоговое окно Макрос с вариантами работы с макросом

Кнопка предназначена для запуска, выбранного из списка макроса (в данном примере всего один макрос).

Кнопка предназначена для просмотра текста макроса в редакторе и исправления необходимых параметров (прежде чем начать редактирование кодов, следует запустить режим конструктора). Для рассматриваемого примера, программные коды макроса показаны на рис 38.

Рис. 38. Программные коды макроса

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

Кнопка находится в неактивном состоянии, для того чтобы её активизировать, необходимо перейти в режим работы с формой.

Кнопка позволяет из имеющегося списка макросов удалить тот, который будет выделен.

Кнопка запускает диалоговое окно Параметры макроса, которое показано на рис 39.

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

Рис. 39. Диалоговое окно для изменения параметров уже созданного макроса

Использование панели инструментов Формы при создании макросов

Панель инструментов Формы содержит в своём составе элементы управления, к которым можно «привязаться» с помощью макрорекордера. Для открытия указанной панели инструментов в Excel 2003, следует в строке Меню→Вид→Панели инструментов→Формы, внешний вид которой показан на рис 40.

Рис. 40. Панель инструментов Формы

Тот, кто использует Excel 2007, панель инструментов Формы может найти через открытие вкладки Разработчик→ Вставить (режим конструктора не активизировать), как показано на рис 41.

Рис. 41. Элементы управления формы

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

Откроем файл с исходными данными под именем Forma1.xls.
Создадим новую книгу в Excel, сохраним её под именем Form6.xls.
Откроем панель инструментов Формы для файла Form6.xls.
Поместим на листе с наименованием «Просмотр списка» элемент управления - список.
Запустим команду для записи макроса, в окне Запись макроса присвоим имя макросу – Получить_список.
Сделаем щелчок правой клавишей мыши по элементу Список, в открывшемся окне выберем строку с наименованием «Формат объекта».
В открывшемся окне, которое показано на рис 42, под названием Форматирование объекта на вкладке Элемент управления необходимо щёлкнуть по значку - Формировать список по диапазону, а затем в книге под именем Form1 выделить столбец с фамилиями.
Закрыть окно Форматирование объекта кнопкой ОК.

После выполнения пункта 8 список будет заполнен фамилиями, которые получены с листа под наименованием «Кадры» из книги – Form1.

Рис. 42. Окно для форматирования объекта – Список

Закройте эту форму, сохраните книгу Form6 и тоже закройте. Можете убедиться, что макрос заполнения списка создан, программа которого представлена на рис 43.

Рис. 43. Программа макроса для заполнения объекта Список данными

Теперь попробуйте открыть файл Form6.xls, к сожалению, в открытой книге будет только элемент управления Список совершенно чистый, хотя, макрос был создан. Причина такого результата кроется в том, что источника информации нет (книга со списком сотрудников закрыта). Создадим ещё один макрос, обращение к которому, позволяет открыть книгу с именем Form1. В книге Form6 дадим команду для макроса – Начать запись, откроем файл Form1.xls, дадим команду – Остановить запись. Таким образом, создан второй модуль, программа которого показана на рис 44.

Рис. 44. Программный код макроса для открытия файла Form1.xls

Таким образом, создано два модуля с программами, которые необходимо объединить. Это делается с помощью командной кнопки, которую разместим на листе книги Form6. Используем панель элементов Формы, на которой выбираем элемент - кнопка, располагаем его на листе.

Для такого элемента система открывает диалоговое окно - Назначить макрос объекту, которое представлено на рис 45. В том случае, если макрос ещё не готов, то нажмите на кнопку «Отмена» в окне «Назначить макрос объекту». Когда понадобится выполнить операцию привязки макроса к кнопке, тогда щёлкните правой клавишей мыши по объекту, а затем в открывшемся списке выберите необходимую строку с наименованием команды, как это показано на рис 46.

Обратите внимание, что панель инструментов Формы самостоятельно создаёт процедуры для обработки событий, не требует перехода в режим конструктора, но вместе с тем, имеет ограниченные возможности для изменения свойств объектов.

Рис. 45. Диалоговое окно назначения макроса объекту кнопка

Рис. 46. Пример использования всплывающего меню для работы с объектом

Посмотрите работу примера в Excel 2003

Этот вариант будет работать только в Excel 2007 и выше

Раздел 4. Пользовательские функции в VBA

Создание пользовательских функций

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

Начало работы пользователя осуществляется с открытия новой или уже существующей книги Excel. Для Excel 2003 при обращении к макросам необходимо щёлкнуть по строке , после чего будет открыто окно редактора Visual Basic, в этом окне существует строка меню, пиктограммы, которые отражают наиболее часто используемые команды меню, окно для отображения объектов Excel (слева), рабочее поле для просмотра кодов или элементов формы. На рис 47 отображено открытая книга Excel и текстовый редактор Code.

Рис. 47. Книга Excel и редактор Visual Basic для создания модуля

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

Постановка задачи

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

Рис. 48. Интерфейс пользователя для обработки материалов из анкет экспертов методом ранжирования

Проект приложения на Excel

На рис 48 показано, что книга Excel содержит три листа. На первом листе расположены командные кнопки и поле для ввода данных из анкет экспертов. Кнопка позволяет пользователю удалять ранее введённые данные с листа под наименованием «данные». Эта кнопка создана с помощью панели инструментов Visual Basic, в дальнейшем будет сказано, почему так поступили. Кнопка предназначена для запуска процедуры подготовки работы с данными, что требует вывода на экран дополнительных диалоговых окон. Кнопка создана с помощью панели инструментов Формы, что позволяет процедуру обработки данных сохранить в виде отдельного модуля (хотя, модуль можно создать и по-другому). Кнопка позволяет запускать процедуру преобразования бальных оценок экспертов в ранги (порядковые места), вычислять коэффициент согласованности мнений экспертов, который в статистике называют коэффициентом конкордации W [4], а так же построить диаграмму распределения мест ранжируемых проектов (объектов). Отражение результатов работы модуля после вызова процедуры «Расчёт» осуществляется на листах книги с наименованиями «результаты» и «график». Кнопка создана с помощью панели инструментов Формы.

Посмотрите, как работает приложение.

Описание процедур обработки событий для приложения – «ранжирование объектов»

Процедура – Очистить поле

Прежде, чем заполнить таблицу, пользователь удаляет с листа под наименованием «данные», те числа, которые могут остаться после предыдущей работы. Для этого достаточно нажать на кнопку «Очистить поле». В качестве примера, как можно использовать макрос в программе обработки события при нажатии на кнопку, поступим следующим образом. Создадим макрос с помощью макрорекордера, который назовём «Очистить». Запустим макрорекордер, выделим на листе с именем «данные» область A7:P48, выполним команду Delete (Удалить), откроем лист с именем «результаты», выделим область A2:R26, всё удалим, откроем лист с именем «график», удалим содержимое из области A2:R2, откроем лист с именем «данные», установим указатель курсора в ячейку A7, остановим запись макрорекордера. В итоге, программный код для удаления данных от предыдущих расчётов будет сформирован (самим ничего писать не пришлось), который показан на рис 49. Может быть, это не самый лучший вариант создания кода, т.к., на всякий случай, выделяли избыточное количество ячеек на листах, но результат достигнут – получен макрос (создан модуль). Тогда достаточно для кнопки написать всего один оператор: Call Очистить, где оператор Call (вызвать) использует в качестве метода макрос под именем «Очистить».

Рис. 49. Программные коды макроса и процедуры обработки события Click при нажатии на командную кнопку с наименованием «Очистить поле»

Процедура по удалению данных со всех листов книги Excel работает автономно, это сделано специально. Пользователь должен иметь возможность неоднократно проводить расчёты с уже введёнными данными, расширять количество столбцов и количество строк в исходной таблице или вносить изменения в некоторых ячейках таблицы (часто это делают, когда задаются вопросом «что будет, если?», т.е. исследователь начинает изменять данные и просматривать новый вариант результатов).

Процедура - Подготовка к вводу данных

Автор не стремился создать универсальную программу для ввода данных на лист Excel. Это вызвано тем обстоятельством, что основной целью разбора, используемых приёмов при составлении программы, демонстрация правил работы с VBA и некоторых его возможностей. Процедура подготовки к вводу данных представлен на рис 50.

Рис. 50. Программные коды процедуры подготовки к вводу данных

В разрабатываемых процедурах, будут использоваться переменные, которые содержат данные и сведения о количестве, рассматриваемых проектов (количество заполненных столбцов), а так же сведения о количестве экспертов, чьи мнения необходимо учесть при выполнении расчётов (количество строк в таблице), это целые числа. Матрица с оценками должна храниться в виде массива. Поэтому, введены операторы Global для описания указанных переменных, действие которых распространяется на все процедуры.

Ещё один приём создания объекта в виде диалогового окна в программе на VBA отображён программными кодами (рис 50):

ThisWorkbook.Sheets("данные").Activate

u = InputBox("ВВЕДИТЕ К-ВО ПРОЕКТОВ", "ВВОД ПАРАМЕТРОВ")

u1 = InputBox("ВВЕДИТЕ К-ВО ЭКСПЕРТОВ", "ВВОД ПАРАМЕТРОВ")

Где, ключевое слово This указывает, что на листе с именем «данные» формируются диалоговые окна для ввода данных.

Оператор: If u = "" Or u1 = "" Then проверяет одновременно содержимое введённых пользователем данных в диалоговые окна, в случае, если пользователь не заполнит одно из окон, программа даст сообщение с помощью оператора MsgBox. Для задания имени листа, с которым необходимо работать, в программе требуется указать оператор With (совместно с), который заканчивается оператором End With. Внутри этой конструкции можно вставлять любые операторы и функции, которые будут участвовать в обработке данных на указанном листе книги. Как видите, для каждого листа осуществляется очистка рабочего диапазона ячеек методом Clear (очистить). Обратите внимание, что в каждом цикле (их три) используется переменная i, действие которой распространяется только на момент работы оператора цикла.

Процедура выполнения расчётов

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

Первый блок (рис 51) предназначен для считывания с листа под наименованием «данные» введённые баллы по проектам от каждого эксперта. Вначале объявляются переменные оператором Dim, которые участвуют в различных блоках программ. Переменные a() и b() являются массивами для хранения бальных оценок от эксперта и преобразованные в дальнейшем в ранги. Обратите внимание, что оператор .Cells(i + 6, 1) = i начинается с точки, в котором ячейке с заданными параметрами (номер строки и номер столбца) на листе с наименованием «данные» присваивается порядковый номер эксперта, что отображается на листе в ячейках, начиная с ячейки A7 и ниже. Точка перед методом Cells ставится без указания имени книги и листа, что было назначено в операторе With Worksheets("данные").

Рис. 51. Программные коды для обработки строки с введёнными баллами от одного эксперта

Второй блок (рис 52) предназначен для выяснения величин баллов в просматриваемой строке оценок, которые присвоил эксперт, рассматриваемым проектам. В массиве o3() создаём просто номера с 1 до n, где n – число проектов, которые оценивал каждый эксперт. При нахождении максимального числа баллов в строке (цикл просмотра строки ниже), осуществляется хранение в переменной f - максимального числа, а в переменной j1 - порядкового номера в строке.

Рис. 52. Выбор наибольшего балла в строке оценок

Третий блок (рис 53) предназначен для преобразования баллов в ранги. Преобразование бальных оценок в ранги (порядковые места) несколько позволяет независимо от выбранной шкалы экспертами для оценки проектов, свести все мнения экспертов к единой порядковой шкале. Комментарии, которые находятся на рис 53, созданы для того, чтобы просматривался алгоритм перехода от одной шкалы измерения к другой. Основой этого алгоритма является – работа с массивами данных, поэтому для этой цели использована конструкция оператора For … Next.

Четвёртый блок (рис 54) предназначен для записи рангов (порядковых мест, которые были получены после преобразования баллов) на лист с наименованием «результаты». Обратите внимание, что в первом блоке обработка введённых данных осуществлялась по строкам, поэтому, в четвёртом блоке результаты передаются на лист с наименованием «результаты» тоже по строкам. Но, необходимо было обработать все строки, для этой цели, в начале программы был введён оператор For ij = 1 To m, действие которого заканчивается в четвёртом блоке оператором Next ij.

Рис. 53. Преобразование баллов в ранги

Рис. 54. Перенос оценок в виде порядковых мест на лист с наименованием «результаты»

Пятый блок (рис 55) предназначен для вычисления коэффициента конкордации (W) и для отображения результатов в виде гистограммы на листе с наименованием «график». Коэффициент W вычисляется по стандартной формуле [4], который очень полезен для анализа, полученных результатов. Статистически доказано, что значимость коэффициента конкордации зависит от числа рассматриваемых объектов (в данном примере под объектом понимают проекты) и количества экспертов, которые принимали участие в оценивании объектов. Используя соответствующие таблицы [5], можно сделать заключение о приемлемости результатов (чем величина коэффициента конкордации ближе к единице, тем лучше). Пользователь приложения может удалить из общего массива оценки того эксперта, которые значительно отличаются от оценок большинства экспертов, а затем снова провести расчеты, чтобы убедиться, что согласованность мнений экспертов стала выше.

Рис. 55. Программные коды для отображения результатов на листе с наименованием «график»

Наглядное представление выполненных расчётов всегда помогает исследователю (пользователю программы) принимать решение о том, на сколько, его удовлетворяют результаты. На листе с наименованием «график» отображается гистограмма, которая содержит сведения о суммарных количествах рангов по каждому, оцениваемому объекту (проекту). На рис 56 показан лист с наименованием «график».

Рис. 56. Результаты оценки семи проектов девятью экспертами

Для того чтобы построить диаграмму, используя язык VBA, потребуется воспользоваться методом Add Charts, указать ссылку на тот диапазон, на основе которого строится диаграмма, задать тип диаграммы, ввести в свойства текстовые значения наименования осей и названия диаграммы (как это делается при работе с мастером диаграмм). Получится достаточно большое количество программных кодов. Можно всё сделать гораздо проще, как это выполнено в данном примере. А именно, на листе с наименованием «график» с помощью мастера построения диаграмм задан диапазон ячеек с данными (в данном случае ограничились 15 ячейками второй строки листа, в котором отображаются суммарные величины рангов по проектам), выбран вид диаграммы и внесены описания осей. После заполнения данных на первом листе приложения, диаграмма появится автоматически.

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

Обозначение	Имя в VBA	Название
	CommandButton	Кнопка
	TextBox	Поле
	Label	Надпись
	ListBox	Список
	ComboBox	Поле со списком
	OptionButton	Переключатель
	CheckBox	Флажок
	Image	Рисунок