Разработка программного комплекса для анкетирования и анализа желающих на открытое мероприятие при помощи средств Microsoft Excel

Размещено на http://www.allbest.ru

Размещено на http://www.allbest.ru

Введение

Многие event-агентства сталкиваются с тем, что создают формы для подачи заявок на различные проекты и мероприятия. Конечно, возможны различные варианты, но в большинстве случаев организаторы долго не думают и создают форму подачи заявок на GoogleForms.

Т.к. я сам являюсь директором event-агентства «Концерт Studio», то для меня крайне неудобно постоянно заходить на сайт формы заявки GoogleForm в раздел «Ответы». Чтобы упростить задачу и не заходить не на какие сайты, мне пришла идея создать в Excel программу, которая будет скачивать ответы с сервера ответов GoogleForms, загружать в таблицу и при этом подводить статистику по заданным параметрам и сразу распечатывать список заявок и статистики.

В рамках учебной практики было необходимо решить следующие задачи:

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

· Возможные обработки:

1. статистика по каким-либо параметрам,

2. итоги подачи заявок.

· Результаты обработки отобразить в таблицу Excel.

1. Описание средств разработки: MS Excel и встроенного языка VBA

Microsoft Excel (также иногда называется Microsoft Office Excel) - программа для работы с электронными таблицами, созданная корпорацией Microsoft для ОС Windows и Mac OS. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день MS Excel является одним из востребованных приложений в мире.

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

Начиная с 1993 года, в состав Excel входит Visual Basic для приложений (VBA), язык программирования, основанный на Visual Basic, позволяющий автоматизировать задачи Excel. VBA является мощным дополнением к приложению и в более поздних версиях Excel доступна полнофункциональная интегрированная среда разработки. Можно создать VBA-код, повторяющий действия пользователя и таким образом автоматизировать простые задачи. VBA позволяет создавать формы для общения с пользователем. Язык поддерживает использование (но не создание) DLL от ActiveX; более поздние версии позволяют использовать элементы объектно-ориентированного программирования.

Microsoft, с опозданием приняв меры для уменьшения риска попадания вирусов через VBA в MS Excel, добавила возможность выбора режима безопасности: полностью отключить макросы; включить макросы при открытии документа; доверять всем макросам, подписанным с использованием надёжных сертификатов.

Экспорт и перемещение таблиц. API позволяет открывать таблицы Excel в ряде других приложений. Это включает в себя открытие документов Excel на веб-страницах с помощью ActiveX или таких плагинов, как Adobe Flash Player. Проект Apache POI представляет Java-библиотеки для чтения и записи электронных таблиц Excel. Также предпринимались попытки копировать таблицы Excel в веб-приложения с использованием разделённых запятыми значений (CSV).

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

Программирование с помощью VBA в Excel. Ценной возможностью Excel является возможность писать код на основе Visual Basic для приложений (VBA). Этот код пишется с использованием отдельного от таблиц редактора. Управление электронной таблицей осуществляется посредством объектно-ориентированной модели кода и данных. С помощью этого кода данные входных таблиц будут мгновенно обрабатываться и отображаться в таблицах и диаграммах (графиках). Таблица становится интерфейсом кода, позволяя легко работать изменять его и управлять расчётами.

Краткие сведения о Google Forms.

Основная работа с Google Формой состоит в добавлении вопросов. Сами вопросы представляют собой просто текст, а вот типы ответов могут быть разными. Пользователям может быть предложено выбрать один из нескольких вариантов ответов или указать два и более. Если простой ответ невозможен, тогда следует добавить в форму текстовое поле, в котором опрашиваемый сможет написать свое мнение. В наборе вариантов ответов вы также найдете выпадающий список, шкалу, сетку, дату, время.

После того, как создана новая форма, система автоматически создает новый вопрос типа «Один из списка». Здесь можно заметить общие элементы, которые встречаются в любом вопросе:

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

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

· Кнопка «Один из списка» раскрывает перечень типов ответов (текст, шкала, сетка и т.д.). С её помощью в любой момент возможно изменение способа ответа пользователя.

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

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

Тип ответа «Один из списка» предполагает, что респонденту нужно выбрать только один ответ из нескольких. Также возможно предложить пользователю указать свой вариант, добавив поле «Добавить вариант Другое».

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

Рис. 1.1. Описание формы опроса

По окончанию редактирования формы нажимается кнопка «Готово».

Варианты ответов на вопросы. Кнопка «Добавить элемент», добавляет в форму вопрос, тип которого соответствует предыдущему. Однако, правильнее будет выбрать нужный вариант из списка, который будет раскрываться при нажатии на стрелку справа от кнопки «Добавить элемент».

«Текст». «Текст» представляет собой простое поле, в которое можно ввести с клавиатуры все что угодно (Рис.1.2.). Расширенные настройки дают возможность разрешить вводить данные только определенного типа. Например, возраст человека. Для того чтобы пользователь не мог указать произвольную информацию, устанавливается флаг «Проверка данных» и настройка параметров происходит следующим образом: число в диапазоне от 14 до 100 (Рис. 1.3.).

Рис. 1.2. Элемент «Текст»

Рис. 1.3. Проверка данных в поле «Текст»

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

Рис. 1.4. Добавление элемента «Развернутый ответ»

Рис. 1.5. Добавление выпадающего списка ответов

«Выпадающий список». Выпадающий список может быть полезен, когда вариантов ответов несколько (более 5). Он не занимает много места и не «напугает» читателя огромным числом ответов.

«Дата и время». Дату можно использовать, например, чтобы узнать, когда родился пользователь. Если ответ предполагает только дату и месяц, возможно отключить выбор года с помощью соответствующей настройки (Рис. 1.6.).

Рис. 1.6. Добавление в после ответов элементы «Дата и время»

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

Разбивка формы на страницы и разделы. Google формы находят применение не только для мини-опросов и голосований, но в больших исследованиях, где число вопросов может исчисляться десятками. В этом случае, удобно разбить форму на несколько страниц и разделов. Опрашиваемые гораздо охотнее ответят на несколько небольших групп вопросов, чем на один длинный список. Кроме того, система позволяет создавать разветвленные сценарии, в которых выбор дальнейшего направления опроса зависит от ответа на данном этапе. Этот механизм основан на разделении формы по страницам. Чтобы разбить форму на страницы или разделы, выберите в списке «Добавить элемент» соответственно «Разрыв страницы» или «Название раздела». В обоих случаях надо указать заголовок и, по желанию, описание.

Перемещение вопросов в Google форме. Любой вопрос, раздел или разрыв страницы представляет собой блок, который можно захватить мышкой и перетащить в нужное место.

Просмотр формы. Чтобы увидеть, как будет выглядеть форма для тех, кому она предназначена, нажмите кнопку «Открыть форму» или выберите пункт «Открыть форму» в меню «Вид».

Пример формы представлен ниже (Рис.1.7.).

Рис. 1.7. Пример формы на Google Forms

2. Описание конечного приложения

Запускаем MS Excel. Далее в программе переходим на лист «ОТВЕТЫ» (рис. 2.1):

Рис. 2.1. Лист «ответы» в MS Excel

Для того чтобы произвести загрузку данных с Google Forms в Excel на лист «ОТВЕТЫ», воспользуемся встроенным языком программирования Visual Basic for Applications. Для удобного взаимодействия с пользователем была создана форма с кнопкой “Загрузить данные” (рис. 2.2.), которая при нажатии обновляла данные поданных заявок на Google Forms.

Рис. 2.2. Встроенный язык программирования VBA. Форма с кнопкой «Загрузить данные»

Для того чтобы посмотреть отчет с обновленными данными заявок нужно зайти в Excelна лист «ОТЧЕТЫ» (рис. 2.3.):

Рис. 2.3. Лист «Отчеты» в MS Excel

В ячейку «Ответов» вносим статистическую функция «СЧЕТ()», которая подсчитывает количество ячеек в списке аргументов, которые содержат только числовые значения«=СЧЁТ(ответы!C[-1])».

В ячейку «имеют опыт» вносим статистическую функция «СЧЁТЕСЛИ», которая может подсчитать количество ячеек, отвечающих определенному условию «=СЧЁТЕСЛИ(ответы!C[2];"Да")».

В ячейку «не имеют опыт» вносим статистическую функция «СЧЁТЕСЛИ» можно подсчитать количество ячеек, отвечающих определенному условию «=СЧЁТЕСЛИ(ответы!C[2];"Нет")».

В ячейки: «Средний возраст имеющих опыт», «Средний возраст не имеющих опыт», «Средний возраст» вносим:

Функцию «СРЗНАЧ», которая находит среднее арифметическое аргументов

Функцию «ОКРУГЛВНИЗ», которая округляет число до ближайшего меньшего по модулю значения «=ОКРУГЛВНИЗ(СРЗНАЧ(ответы!C[3]);0)».

3. Листинг метода обработки события

программный графический листинг

Sub ЗагрузитьОтветы()

' Макрос

'

//Вывод ответов на Лист “Ответы”:

DimqtsAsQueryTables

Setqts = Worksheets("ответы").QueryTables

//Делаем Лист “Ответы” активным и выделяем ячейки для занесения данных:

Worksheets("ответы").Activate

Range("A1").Select

Range("A:D").Clear

//Загружаем данные из локального файла 1.csv

' With qts.Add(Connection:="TEXT;D:\леонов\1.csv",

//Загружаем данные из Google Forms

Withqts.Add(Connection:="TEXT;https://docs.google.com/spreadsheets/d/1EYTRa8xDGkC05Ve4DCK-DRH5jaIdYWInNgftYNhxseI/pub?output=csv", _

//Определяем форматирование листа для вывода данных

Destination:=Worksheets("ответы").Range("A1"))

.Name = "d1"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 65001

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(4, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

qts.Item(1).Delete

Sheets("Отчеты").Select

EndSub

Заключение

В рамках учебной практики были решены следующие задачи:

· Были приобретены первоначальные основы работы с программой для работы с электронными таблицами MS Excel.

· Были приобретены первоначальные основы работы с языком макропрограммирования VBA (Visual Basic for Application).

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

Литература

1. Гарнаев А. Использование MS Excel и VBA в экономике и финансах. - Санкт-Петербург, BHV, 1999. - 336с.

2. Конрад Карлберг. Бизнес-анализ с помощью Excel 2000.: Учеб. пособие/ Пер. с англ. - М.: Издат. дом “Вильямс”, 2000. - 480 с.

3. А. Гарнаев Самоучитель VBA. Технология создания пользовательских приложений // BHV - Санкт - Петербург, 1999. - 512с.

4. Кен Гетц, Майк Джилберт Программирование в Microsoft Office. Руководство по Visual Basic for Applications. - СПб: BHV, 2000. - 384с.

Размещено на Allbest.ru