Как создать бесплатный опрос и собирать данные в Excel
Вы устали от необходимости вручную собирать и объединять данные разных людей в электронную таблицу Excel? Если это так, скорее всего, вам еще предстоит открыть Excel Survey.
Microsoft представила Excel Survey несколько лет назад вместе с Office Online . Однако вы, возможно, этого не заметили, если не выходили за пределы настольной версии Office. Функция опроса доступна только в онлайн-версии, что имеет смысл, учитывая, что вам нужно, чтобы ваш опрос был доступен пользователям через Интернет.
Что такое опрос Excel?
Опрос Excel — это веб-форма, которую вы разрабатываете для сбора и хранения структурированных данных в электронной таблице Excel. У вас есть много вариантов, когда дело доходит до веб-опросов или форм. Альтернативы, такие как Google Forms и Survey Monkey, могут иметь более надежные функции, но когда вам нужно собрать простые наборы данных от нескольких человек, этот инструмент без проблем справится с работой в экосистеме Microsoft.
Создать опрос Excel
Если у вас еще нет личной или служебной учетной записи Microsoft Online , вам потребуется создать ее, чтобы войти в OneDrive . Оттуда у вас есть два способа создать опрос:
1. Создайте новый опрос из OneDrive
В меню выберите « Создать»> «Обзор Excel».
2. Добавить опрос в существующую таблицу Excel
В существующей электронной таблице Excel Online выберите « Главная»> «Обзор»> «Новый опрос».
Создайте форму опроса
Вы захотите дать вашему опросу хорошее название и описание, чтобы ваши респонденты быстро поняли, почему их просят предоставить данные. Далее вы добавите вопрос для каждой части информации, которая вам потребуется.
Вопросы из вашего опроса будут соответствовать столбцам в вашей электронной таблице, и каждый ответ будет представлен в виде строки.
Изучите ваши варианты
Важно понять, как вы хотите использовать результаты, прежде чем настраивать типы вопросов. Если у вас есть данные, которые вам нужно будет отсортировать или отфильтровать, вам, скорее всего, следует использовать поле «выбор» для управления вводом. Поле «текст» должно использоваться для динамической информации, которая не может быть предопределена, например, имя человека.
Просмотрите доступные типы полей и выберите лучшее для каждого столбца. Затем решите, хотите ли вы, чтобы вопрос был обязательным или необязательным, и хотите ли вы, чтобы значение по умолчанию автоматически отображалось в форме.
- Текст — короткое текстовое поле.
- Текст абзаца — длинное текстовое поле.
- Число — Числовые данные.
- Дата — значения даты.
- Время — значения времени.
- Да / Нет — выпадающий список, который позволяет выбрать только «Да» или «Нет».
- Выбор — выпадающий список с выбором, выбранным вами.
Знай ограничения
Как вы можете видеть, Excel Survey — очень простой инструмент, который, скорее всего, справится с работой в большинстве ситуаций. Тем не менее, он имеет некоторые ограничения, которые необходимо учитывать. Если какой-либо из них нарушит условия сделки, вы можете рассмотреть некоторые альтернативные решения
- Анонимный доступ — у вас нет возможности требовать входа в систему или ограничить доступ к опросу после его публикации. Если у вас есть ссылка, вы можете отправить ответ.
- Ветвление, иначе Skip Logic — то, что вы видите, это то, что вы получаете. Вы не можете предоставить пользователю различную информацию, основываясь на ответах, которые они дают на вопрос.
- Проверка ввода — вы не можете проверять данные, введенные в форму, за исключением использования одного из типов полей «выбора».
Предварительный просмотр вашего опроса
Теперь, когда вы завершили дизайн, вы должны предварительно просмотреть свою работу, чтобы увидеть, как она будет отображаться при отправке. Для этого нажмите кнопку « Сохранить и просмотреть» в нижней части формы.
Вот что увидят получатели вашего опроса, когда вы поделитесь с ними ссылкой:
Поделитесь своим опросом
Как только вы довольны тем, как все выглядит, пришло время поделиться! Нажмите кнопку « Поделиться опросом» , и вам будет представлена ссылка, которую вы можете отправить своей целевой аудитории.
У вас есть возможность сократить ссылку. Это необязательно, но если вы хотите сделать это, просто нажмите на ссылку Сократить .
Просмотр ваших ответов
В этом примере опрос собирает данные, чтобы помочь спланировать корпоративную вечеринку, но возможности ее использования безграничны. Вы можете создать опрос, чтобы ваша команда отслеживала свое время в конкретном проекте, собирала отзывы о производительности или даже попросила вашу семью помочь определиться с местом вашего следующего воссоединения!
После отправки ссылки на опрос вы можете приступить к мониторингу электронной таблицы для ответов и анализа результатов . Данные будут храниться в электронной таблице, с которой связан опрос. Если вы хотите объединить наборы данных чтобы помочь с анализом данных, вы можете сделать это с помощью инструмента Power Query, встроенного в Excel.
Кредиты изображений: исследование рынка от Pixsooz через Shutterstock
Как внести данные анкет в таблицу excel
Во-первых, вам нужно посчитать общее количество отзывов в каждом вопросе.
1. Выберите пустую ячейку, например ячейку B53, введите эту формулу = СЧИТАТЬПУСТОТЫ (B2: B51) (диапазон B2: B51 — это диапазон отзывов по вопросу 1, вы можете изменить его по своему усмотрению) в нем и нажмите Enter кнопку на клавиатуре. Затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу, здесь я заполняю его до диапазона B53: K53. Смотрите скриншот:
2. В ячейке B54 введите эту формулу = СЧЁТ (B2: B51) (диапазон B2: B51 — это диапазон отзывов по вопросу 1, вы можете изменить его по своему усмотрению) в него и нажмите Enter кнопку на клавиатуре. Затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу, здесь я заполняю его до диапазона B54: K54. Смотрите скриншот:
3. В ячейке B55 введите эту формулу = СУММ (B53: B54) (диапазон B2: B51 — это диапазон обратной связи по вопросу 1, вы можете изменить его по своему усмотрению) и нажмите кнопку Enter на клавиатуре, затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу , вот и заливаю до диапазона B55: K55. Смотрите скриншот:
Затем подсчитайте количество ответов по каждому вопросу: «Полностью согласен», «Согласен», «Не согласен» и «Полностью не согласен».
4. В ячейке B57 введите эту формулу = СЧЁТЕСЛИ (B2: B51; $ B $ 51) (диапазон B2: B51 — это диапазон отзывов по вопросу 1, ячейка $ B $ 51 — это критерии, которые вы хотите подсчитать, вы можете изменить их по своему усмотрению) и нажмите Enter на клавиатуре, затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу, здесь я заполняю его до диапазона B57: K57. Смотрите скриншот:
5. Тип = СЧЁТЕСЛИ (B2: B51; $ B $ 11) (диапазон B2: B51 — это диапазон отзывов по вопросу 1, ячейка $ B $ 11 — это критерии, которые вы хотите подсчитать, вы можете изменить их по своему усмотрению) в ячейку B58 и нажмите Enter на клавиатуре, затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу, здесь я заполняю его до диапазона B58: K58. Смотрите скриншот:
6. Повторите шаги 5 или 6, чтобы подсчитать количество отзывов по каждому вопросу. Смотрите скриншот:
7. В ячейке B61 введите эту формулу = СУММ (B57: B60) (диапазон B2: B51 — это диапазон отзывов по вопросу 1, вы можете изменить его по своему усмотрению), просуммируйте общий отзыв и нажмите Enter на клавиатуре, затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу, здесь я заполняю его до диапазона B61: K61. Смотрите скриншот:
Часть 2. Рассчитайте процентное соотношение всех отзывов.
Затем вам нужно рассчитать процент каждой обратной связи по каждому вопросу.
8. В ячейке B62 введите эту формулу = 57 бат / 61 млрд долларов (Ячейка B57 указывает на особую обратную связь, которую вы хотите подсчитать, ее количество, Ячейка $ B $ 61 обозначает общее количество отзывов, вы можете изменить их по своему усмотрению), чтобы просуммировать общую обратную связь и нажмите Enter на клавиатуре, затем перетащите маркер заполнения в диапазон, в котором вы хотите использовать эту формулу. Затем отформатируйте ячейку в процентах, щелкнув правой кнопкой мыши> Форматы ячеек > Процент. Смотрите скриншот:
Вы также можете отобразить эти результаты в процентах, выбрав их и нажав % (Процентный стиль) в Число группы на Главная меню.
9. Повторите шаг 8, чтобы вычислить процент каждой обратной связи в каждом вопросе. Смотрите скриншот:
Часть 3. Создание отчета об опросе с расчетными результатами выше
Теперь вы можете составить отчет о результатах опроса.
10 Выберите заголовки столбцов опроса (в данном случае A1: K1) и щелкните правой кнопкой мыши> Копировать а затем вставьте их в другой пустой лист, щелкнув правой кнопкой мыши> Транспонировать (T). Смотрите скриншот:
Если вы используете Microsoft Excel 2007, вы можете вставить эти рассчитанные проценты, выбрав пустую ячейку и нажав Главная > Вставить > транспонировать. См. Следующий снимок экрана:
11 Отредактируйте заголовок, как вам нужно, см. Снимок экрана:
 |
 |
 |
12 Выберите часть, которую необходимо отобразить в отчете, и щелкните правой кнопкой мыши> Копировать, а затем перейдите на рабочий лист, который вам нужно вставить, и выберите одну пустую ячейку, например Ячейку B2, щелкните Главная > Вставить > Специальная вставка. Смотрите скриншот:
13 В разделе Специальная вставка диалог, проверьте Ценности и транспонирование в Вставить и транспонировать разделы и щелкните OK чтобы закрыть этот диалог. Смотрите скриншот:
 |
 |
 |
Повторите шаги 10 и 11, чтобы скопировать и вставить нужные вам данные, после чего будет составлен отчет об исследовании. Смотрите скриншот:
Как сделать анкету в excel
Как создается электронная анкета средствами VBA Excel
Разберем решение еще одной практической ситуации — заполнение электронного бланка анкеты. Сам разрабатываемый лист будет достаточно насыщен элементами управления, поэтому здесь мы рассмотрим оформление листа последовательно.
Начнем разработку (рис. 2.12) с небольших деталей. Так, заполним три ячейки в столбце В поясняющей информацией, а для трех соответствующих ячеек в столбце С необходимо лишь подобрать соответствующее форматирование — заливку и размер шрифта. В дальнейшем в процессе работы с этим бланком пользователь будет вносить в ячейку С2 фамилию, в С4 — имя, а в С6 — отчество. Теперь, как и в предыдущих книгах, следует убрать сетку с рабочего листа.
Рис. 2.12. Верхняя часть электронной анкеты
На рис. 2.12 в правой части расположено три элемента управления: текстовое окно и группа из двух переключателей. Мы уже рассматривали пример, связанный с функционированием переключателей. Этот элемент управления позволяет обеспечить два состояния: «включено» и «выключено». Идея использования двух подобных элементов на нашем листе достаточно простая. А именно, человек, который заполняет бланк, указывает (щелчком на одном из переключателей) один из двух вариантов:
В случае выбора варианта Другой город следует указать, какой именно. Это производится в соседнем текстовом окне справа. Понятно, что рассматривается ситуация, когда большинство людей, заполняющих бланк, проживает в Нижнем Новгороде. Зададим значения свойства Name элементов на рис. 2.12 следующим образом:
- Opt1 (переключатель Н. Новгород);
- 0pt2 (переключатель Другой город);
- City (текстовое окно для ввода названия города).
В начальном варианте (при открытии книги) по умолчанию установлен вариант Н. Новгород (это выполняется в окне свойств, где следует установить True в качестве значения свойства Value). При этом текстовое окно для выбора города должно быть невидимым. Для этого в окне свойств для свойства Visible объекта City необходимо установить значение False.
При щелчке на переключателе Другой город текстовое окно City становится видимым, а при щелчке на переключателе с подписью Н. Новгород опять пропадает. Сами тексты процедур обработки щелчков на переключателях, обеспечивающих подобный эффект, приведены в листинге 2.17.
В дальнейшем мы обеспечим программную установку значений свойства Value переключателей и значения свойства Visible текстового окна City.
‘Листинг 2.17. Процедуры обработки щелчков ‘ на переключателях для выбора города Private Sub Opt1_Click() City.Visible = False End Sub Private Sub Opt2_Click() City.Visible = True End Sub
Подчеркнем один важный технический момент. Мы расположили два переключателя, которые связаны друг с другом. При щелчке на одном из них значение свойства Value другого автоматически становится False. Далее на нашем рабочем листе мы расположим еще одну группу переключателей, которая фиксирует категорию анкетируемого (учащийся или специалист). Для того чтобы группы переключателей правильно работали, необходимо подчеркнуть, какие из них к какой группе относятся. Для этого необходимо значения свойства GroupName для переключателей, связанных с городами, сделать одинаковыми (например, можно выбрать Op_city). Для других переключателей значение данного свойства должно быть другим.
Теперь можно выйти из режима конструктора и проверить работу написанных процедур. Убедившись, что все функционирует по плану, продолжим создание рассматриваемой разработки. На рис. 2.13 показана следующая группа элементов управления, которые нам необходимо добавить на том же рабочем листе. В левой части рис. 2.13 сосредоточены элементы, которые заполняются при условии, что анкетируемый является студентом. Соответственно, правая часть — для лиц, уже имеющих диплом об образовании. При этом названия Место учебы, Курс, Место работы и Примечание являются элементами управления типа «Надпись». Они введены для пояснения содержимого соседних (находящихся справа от них) текстовых окон. В связи с тем, что эти надписи программно в дальнейшем не используются, имена этих объектов мы не приводим.
Рис. 2.13. Нижняя часть электронной анкеты
Переключатели Студент (Name — St) и Специалист (Name — Sp) относятся к одной группе переключателей, отличной от группы переключателей, используемых для выбора городов. Теперь поясним, как они будут использоваться.
Так, при выборе категории Студент видимыми становятся текстовые окна для заполнения полей анкеты Место учебы (Name — Place) и Курс (Name — Kyrs), а текстовые окна для заполнения полей Место работы (Name — Work) и Примечание (Name — Prim) становятся невидимыми. Соответственно, при выборе категории Специалист все наоборот — видимыми становятся текстовые окна, которые должен заполнить специалист. В нижней части листа на рис. 2.13 располагаются три флажка — это простые элементы управления, в функциональном плане похожие на переключатели. Основное используемое свойство флажка — Value, которое принимает два возможных значения: False и True.
Теперь можно сказать, что мы рассмотрели функциональное назначение элементов на листе электронной анкеты. Перейдем к программным процедурам.
Как уже говорилось, при открытии книги по умолчанию необходимо сделать выбор на вариантах Н.Новгород и заполнении анкеты студентом. Это лучше реализовать в процедуре Workbook_Open (листинг. 2.18).
На панели элементов ActiveX (см. рис. 1.24) пиктограмма элемента управления «Флажок» третья слева.
‘ Листинг 2.18. Процедура, выполняемая при открытии книги PPrivate Sub Workbook_Open() Worksheets(1).Opt1.Value = True Worksheets(1).Opt2.Value = False Worksheets(1).City.Visible = False Worksheets(1).St.Value = True Worksheets(1).Place.Visible = True Worksheets(1).Place.Text = «» Worksheets(1).Kyrs.Visible = True ‘ По умолчанию рассматривается студент первого курса Worksheets(1).Kyrs.Text = «1 » Worksheets(1).Work.Visible = False Worksheets(1).Work.Text = «» Worksheets(1).Prim.Visible = False Worksheets(1).Prim.Text = «» Worksheets(1).Engl.Value = False Worksheets(1).Auto.Value = False Worksheets(1).Info.Value = False End Sub
Из текста листинга 2.18 видно, что для флажков значения свойства Name установлены следующим образом:
- Eng1 — знание английского языка;
- Auto — умение управлять автомобилем;
- Info — навыки работы на компьютере.
В результате мы обеспечили автоматическую установку начальных значений при открытии книги. Действие переключателей Студент и Специалист мы уже прокомментировали, и теперь приведем программные процедуры обработки щелчков мышью на них (листинг 2.19).
‘ Листинг 2.19. Процедуры, выполняемые по щелчкам на переключателях Sp и St Private Sub St_Click() Place.Visible = True Kyrs.Visible = True Work.Visible = False Prim.Visible = False End Sub Private Sub Sp_Click() Place.Visible = False Kyrs.Visible = False Work.Visible = True Prim.Visible = True End Sub
Таким образом, мы обеспечили необходимый интерфейс ввода информации на первом рабочем листе книги. Заполненный вариант анкеты представлен на рис. 2.14.
Рис. 2.14. Заполненная форма анкеты
Далее будем считать, что информацию с первого листа следует записать в базу данных — на второй лист (рис. 2.15). Здесь для данных по каждому анкетируемому отводится по одной строке. И по щелчку на кнопке Записать на 2-й лист (см. рис. 2.14) информация анкеты переписывается в очередную свободную строку второго листа. В листинге 2.20 приводится текст данной процедуры. Как вы уже заметили, из названия процедуры следует, что для свойства Name кнопки установлено значение WriteList.
Рис. 2.15. Представление информации на втором листе книги
‘ Листинг 2.20. Процедура, выполняемая при щелчке на кнопке Записать на 2-й лист Private Sub WriteList_Click() ‘ Подсчет количества имеющихся записей на втором листе N = 0 While Worksheets(2).Cells(N + 2, 1).Value <> «» N = N + 1 Wend ‘ Запись порядкового номера в первый столбец Worksheets(2).Cells(N + 2, 1).Value = N + 1 ‘ Копирование фамилии, имени отчества Worksheets(2).Cells(N + 2, 2).Value = Range(«C2») Worksheets(2).Cells(N + 2, 3).Value = Range(«C4») Worksheets(2).Cells(N + 2, 4).Value = Range(«C6») ‘ Название города располагается в пятом столбце на втором листе If Opt1.Value = True Then Worksheets(2).Cells(N + 2, 5).Value = «H.Новгород» Else Worksheets(2).Cells(N + 2, 5).Value = City.Text End If ‘ Статус в шестом столбце, место работы или учебы в седьмом, ‘ примечание в восьмом столбце. If St.Value = True Then Worksheets(2).Cells(N + 2, 6).Value = «студент» Worksheets(2).Cells(N + 2, 7).Value = Place.Text Worksheets(2).Cells(N + 2, 8).Value = Kyrs.Text Else Worksheets(2).Cells(N + 2, 6).Value = «спец. с в/о» Worksheets(2).Cells(N + 2, 7).Value = Work.Text Worksheets(2).Cells(N + 2, 8).Value = prim.Text End If ‘ Характеристики человека, зафиксированные во флагах If Eng1.Value = True Then Worksheets(2).Cells(N + 2, 9).Value = «Да» Else Worksheets(2).Cells(N + 2, 9).Value = «Нет» End If If Auto.Value = True Then Worksheets(2).Cells(N + 2, 10).Value = «Да» Else Worksheets(2).Cells(N + 2, 10).Value = «Нет» End If If Info.Value = True Then Worksheets(2).Cells(N + 2, 11).Value = «Да» Else Worksheets(2).Cells(N + 2, 11).Value = «Нет» End If End Sub
Теперь все процедуры готовы, и можно поработать с созданной электронной анкетой. Понятно, что данная разработка не включает многие детали, которые в каждой практической ситуации накладывают свои требования. Однако в этой статье и не ставилась цель создать что-то универсальное. Гораздо важнее на рассмотренных примерах получить навыки, необходимые для выполнения самостоятельных разработок.
Урок 55
Практикум
Автоматизированная обработка данных с помощью анкет
Выполнив практическую работу, вы научитесь:
— создавать шаблон для регистрации данных в виде анкеты;
— настраивать формы ввода данных;
— организовывать накопление данных с последующей их обработкой;
— создавать макросы для автоматизации однообразных действий.
Постановка задачи — разработка информационной системы для анкетирования
В качестве примера, иллюстрирующего работу с анкетами и последующую обработку накопленных данных, рассмотрим анкетирование в рамках конкурса на место ведущего музыкальной программы. (Задача поиска и оценки претендентов намеренно упрощена, чтобы не загромождать ее решение.)
Претенденты оцениваются по нескольким параметрам. Собеседования проводятся по мере поступления заявок. После коллективного обсуждения члены жюри выставляют претенденту оценку по каждому параметру, высшая оценка — 10 баллов. Данные по каждому конкурсанту суммируются. По окончании срока подачи заявлений результаты всех претендентов сравниваются. Конкурс выигрывает претендент, набравший наибольшее количество баллов.
Для автоматизации работы жюри необходимо в электронной таблице Excel создать шаблон анкеты претендента, накопить статистику по всем параметрам, характеризующим каждого претендента, и обработать накопленную статистику. Все итоги конкурса претендентов на место ведущего телевизионной музыкальной программы будут отражены в итоговой таблице, из которой можно выбрать наиболее подходящего претендента.
Оформление шаблона анкеты претендента
1. Откройте новый документ Excel или файл-заготовку.
2. Выделите область ячеек А1:J5 и выберите для нее светлую заливку, чтобы в дальнейшем элементы управления были хорошо видны на фоне таблицы.
3. Заполните строки 1-7 таблицы по образцу (рис. 5.8).
Рис. 5.8 Образец оформления шапки задания Конкурс
Сохраните файл в учебной папке под названием Конкурс.
Создание форм оценок, вводимых в анкету членами жюри
1. Отобразите на экране панель инструментов Формы, выбрав в меню команду Вид/Панели инструментов/Формы.
2. Выберите форму Счетчик и прорисуйте ее под первым оцениваемым параметром. Размер формы сделайте примерно 1×2 см. Вид анкеты изображен на рис. 5.10
3. Щелкните на форме правой кнопкой мыши и выберите в контекстном меню команду Формат объекта.
4. В появившемся окне (рис. 5.9) на вкладке Размер установите предел изменения счетчика (исходя из минимального и максимального баллов):
✔ Минимальное значение — 0;
✔ Максимальное значение — 10;
✔ Шаг изменения — 1.
Рис. 5.9. Настройка формы Счетчик
5. На вкладке Свойства установите переключатель привязки объекта к фону в положение Перемещать, но не изменять размеры.
6. Щелчком правой кнопки выделите форму и скопируйте ее.
7. Вставьте копии 8 раз (по количеству оцениваемых параметров), разместив их под соответствующими параметрами, например в ячейках А8:19.
Задание 5.17. Настройка форм оценок
1. Щелкните на первой форме правой кнопкой мыши и выберите в контекстном меню команду Формат объекта.
2. В появившемся окне на вкладке Формат элемента управления щелкните в строке Связь с ячейкой (см. рис. 5.5) и затем на ячейке А12 в таблице. Нажмите кнопку 0К для сохранения настроек.
3. Повторите пункты 1 и 2 для остальных форм, связав их последовательно с ячейками В12:112.
В результате оценок по девяти параметрам в ячейках А12:112, связанных с формами, появится набор значений от О до 10. Это результаты одного претендента.
Организация накопления данных
Задание 5.18. Создание макросов
Накопление статистических данных будет производиться на втором листе книги Excel по щелчку на кнопке управления. Второй лист книги следует озаглавить «Протокол оценок жюри по всем конкурсантам» и скопировать на него параметры оценки по каждому конкурсанту с листа 1.
Для автоматизации наиболее часто выполняемых действий будем использовать макросы.
Макрос — это программа (набор макрокоманд), которая создается путем записи реальных действий (например, в таблице Excel это выделение ячеек, выбор команд из меню, смена текущего листа и т. д.) при помощи специальных средств для записи макросов или на языке Visual Basic for Applications. При записи макроса сохраняется информация о каждом выполненном шаге в последовательности команд. Записав макрос, его можно запускать всякий раз, когда необходимо выполнить запрограммированную в нем последовательность действий.
Для работы нам необходимо создать три макроса: Накопление_ данных, Очистка и Итоги. Действия, которые следует выполнить для создания макроса Накопление_данных, приведены в табл. 5.2.
Макрос Очистка должен сначала выделять, а затем очищать (клавиша Delete) ячейки D2 и А12:112 на листе 1, готовя их для очередного претендента. Запись макроса проделайте самостоятельно.
Макрос Итоги должен перевести действие с листа 1 на лист 2, ввести в ячейку К5 формулу суммирования результатов одного конкурсанта и скопировать эту формулу в нижестоящие ячейки (количество конкурсантов неизвестно, поэтому задействуйте при копировании формулы 20-30 нижестоящих ячеек). Запись макроса Итоги проделайте самостоятельно. Начните действия с листа 1 и закончите их там же.
Таблица 5.2. Алгооитм создания макроса Накопление данных
Как сделать анкету в excel
Ячейки со значение ключевых ответов содержат «скрытый текст»
- Загрузите MS-Excel .
- Сохраните текущую книгу в своей папке, дав ему имя anketa . xls
- Наберите в MS — Excel текст анкеты:
«Вы витаете в облаках?»
Инструкция к ответам на вопросы: при ответе на каждый вопрос ставьте цифру 1 в графе «Да» или «Нет» в зависимости от вашего выбора.
1. Получив газету, просматриваете ли вы ее, прежде чем читать?
2. Едите ли вы больше обычного когда расстроены?
3. Думаете ли вы о своих делах во время еды?
4. Храните ли вы любовные письма?
5. Интересует ли вас психология?
6. Боитесь ли вы ездить на большой скорости?
7. Избегаете ли вы мыслей о смерти?
8. Любите ли вы помечтать перед сном лежа в постели?
9. Способны ли вы сильно устать после восьмичасового сна?
10. Читаете ли вы любовные романы?
11. Делитесь ли вы с другими личными трудностями?
12. Избегаете ли одиночества?
13. Бывает ли так, что из-за неприятностей вы заболеваете?
14. Случалось ли вам в задумчивости проезжать нужную остановку?
15. Возникала ли у вас желание жить в другом городе?
16. Считаете ли вы характер человека наследственной чертой?
17. Ходите ли вы часто в кино, особенно если в репертуаре фильмы о любви?
Для расположения вопросов анкеты в соответствии с образцом, выделите ячейки, в которых помещены вопросы, увеличьте ширину столбца В примерно так, как это сделано в варианте оформления документа, затем воспользуйтесь командой Формат/Ячейка/ Выравнивание по левому краю, установите флажок Переносить по словам.
- Введите в нужную ячейку формулу для подсчета результата (за каждый положительный ответ 5 баллов).
- Анкета будет выглядеть наиболее презентабельно, если тексты ответов будут изначально скрыты от испытуемого. Для этого:
- Введите в нужные ячейки только значения суммы баллов за ответы;
- Выделите одну из ячеек, в которую введены баллы, затем воспользуйтесь командой Вставка/Примечание. В открывшемся поле Текстовое примечание наберите текст, соответствующий данному количеству баллов (тексты см. ниже).
- Для того, чтобы ячейки с баллами ответов выделялись на фоне общего текста анкеты, сделайте цветную заливку.
- Поставьте курсор на любую ячейку с баллами за ответы и убедитесь, что на экране появится скрытый в примечании текст.
От 57 до 85 баллов. Кажется, вы в «бегах». Как страус, прячущий голову в песок, вы прячетесь от действительности. Вам не мешало бы хотя бы изредка взглянуть в глаза реальности. Это поможет лучше ориентироваться в жизни и относительно успешно ограждать себя от различных неприятностей.
От 55 до 74 баллов. Ваши мечты не всегда сообразуются с «жестокой правдой жизни». Вам это мешает, но не уделяйте этому слишком много внимания и душевной энергии. Не следует искать совершенного (с вашей точки зрения) решения всех трудностей и жизненных неурядиц. Помните, что «звезды сияют, и когда их не видишь».
От 20 до 54 баллов. Вы чрезмерно заземлены, прагматичны. Вам пошла бы на пользу толика романтичности и мечтательности. Жизнь, конечно, вещь серьезная, но иногда и чувство юмора помогает преодолевать некоторые препятствия.
- Сохраните документ.
- Посмотрите, как расположился документ на листе (кнопка Предварительный просмотр на пиктограмме меню).
- Протестируйте себя по созданной анкете. Убедитесь, что текст ответа, соответствующий набранному количеству баллов, появляется на экране после указания курсором на ячейку с баллами.
- Предъявите результаты работы преподавателю.
Как сделать анкету в excel
Обработка анкет в электронных таблицах Excel.
Обоснование актуальности темы работы.
Из разговора с учителями и директором школы я узнал, что они испытывают затруднения при обработке результатов анкетирования, требующей большого внимания и отнимающей много времени. По просьбе учителя информатики Карачи А.Ю. и директора школы № 21 Калибиной Н.Н. я ознакомился с принципом обработки анкет. Ознакомившись с ним, я понял, что этот процесс можно автоматизировать с помощью Excel.
создать программу, позволяющую без особых хлопот обрабатывать анкеты.
- Организовать математические рассчёты.
- Построить необходимые гистограммы.
- Защитить данные от несанкционированного изменения.
-
Способ обработки анкет.
- Возможности электронных таблиц Microsoft Excel, необходимые для реализации поставленной задачи.
Созданная программа упростит процесс обработки анкет.
В ходе работы были использованы следующие методы исследования:
Опрос.
Мне рассказывали о трудностях обработки анкет.Наблюдения.
Я видел, как это делают.Поиск и отбор информации.
Мы подобрали литературу, содержащую сведения, необходимые для выполнения работы.Моделирование.
Были созданы эскизы таблиц и обдумана структура заполнения ячеек необходимыми данными.- Составить сценарий программы.
- Оформить лист.
- Составить программу в Excel.
- Обкатать программу.
- Навести красоту.
- Набраны серии вопросов и серии соответствующих ответов.
- Я предусмотрел ячейки, в которые пользователь должен вводить количество человек, участвовавших в анкетировании, и должным образом ответивших на поставленные вопросы.
- Отвел ячейки, в которые ввёл формулы для расчета количество ответивших в процентах к общему количеству, участвовавших в анкетировании.
- Для каждого вопроса создал гистограмму, позволяющую видеть соотношение выбравших ответы на поставленные вопросы.
- Создал итоговую таблицу с суммой количества человек, давших одноименные ответы.
- Отвел ячейки, в которые ввёл формулы для расчета количества ответивших в процентах к общему количеству, участвовавших в анкетировании.
- Для ответов создал гистограмму, позволяющую видеть соотношение выбравших одноименные ответы.
- Также предусмотрена защита от несанкционированных изменений содержания ячеек, в которых пользователю делать нечего.
Обобщение и систематизация.
Создана программа, позволяющая в реальном времени получать результаты анкетирования и проводить его анализ, используя гистограммы.Сравнение и анализ.
На заключительном этапе я сравнил время обработки анкет вручную и с помощью своей программы. Превосходство программы неоспоримо.Калибина Н.Н. ознакомившись с работой, отозвалась о ней так:
-
По заказу администрации школы Агашов Дмитрий написал программу, которая позволяет проводить анализ анкетных данных по степени значимости ответов и видеть сложившуюся ситуацию на каждой ступени обучения. Программа после внесения информации сразу выдаёт результат не только в числовом виде, но и в виде гистограмм, что освобождает заказчика от рутинной работы подсчёта и сведения в единое целое полученных данных. Защита ячеек не позволяет неквалифицированному пользователю испортить программу. Программа может использоваться многократно, проста в применении и хранении, может использоваться в объёмном аналитическом тексте.
- Получившаяся программа превратила обработку анкет в удовольствие.
- Программа красива, проста и удобна в обращении.
- Применение программы в разы уменьшает время обработки результатов тестирования.
- «Основы работы с Microsoft Excel» Карачи А.Ю., Ишмуратов Р.К.
Как сделать анкету в excel
Библиографическая ссылка на статью:
Чуднова О.В. Алгоритм базового анализа данных социологического опроса в программе MS Excel // Современные научные исследования и инновации. 2015. № 4. Ч. 5 [Электронный ресурс]. URL: http://web.snauka.ru/issues/2015/04/45596 (дата обращения: 02.02.2020).В ходе проведения массовых социологических опросов перед исследователями нередко возникает проблема, связанная с обработкой больших совокупностей полученных данных и их преобразованием из рукописного вида в электронный, машиночитаемый формат.
К сожалению, практически все специализированные программы для обработки социологической информации (SPSS, Statistica, Vortex, PolyAnalyst и др.) распространяются на коммерческой основе, предъявляют серьезные требования к техническим характеристикам персональных компьютеров и зачастую не имеют русифицированного файла помощи.
В связи с этим возрастает необходимость обращения к программному обеспечению, имеющемуся на большинстве современных ЭВМ и позволяющему решать различные задачи необходимые социологу-практику. Одной из таковых программ является Microsoft Office Excel (Excel).
Обработка первичной социологической информации полученной в ходе опроса происходит в Excel в несколько этапов.На первом этапе необходимо пронумеровать все анкеты подлежащие анализу, для постоянного контроля ввода данных и возможности их своевременного корректирования. Далее необходимо «закрыть» все открытые вопросы анкеты, объединив ответы респондентов в группы [1, с. 434-437].Так, при ответе на открытый вопрос «Сколько лет Вы трудитесь в вузе?» человек может указать точный стаж, который социолог для удобства анализа отнесет в группы: «менее 5 лет», «5-10 лет», «11-16 лет», «17-22 года», «23 и более лет» (рис.1, вопрос 1).
Рис. 1 Фрагмент анкеты
Когда все открытые вопросы анкеты приведены в «закрытый» вид, следует присвоить числовой код каждому варианту ответа в каждом вопросе, то есть закодировать его. Если вопрос задан в виде таблицы (рис 1, вопрос 3), то при его анализе необходимо каждую строку ответа кодировать как отдельный вопрос. Ведь, по сути, каждый вопрос таблицы задается респонденту как отдельный: «Насколько Вы удовлетворены заработной платой?», «Насколько Вы удовлетворены графиком работы?» и т.д. Если же респондент пропустил вопрос или не смог ответить на него, то код отсутствию ответа не присваивается.
На втором этапе происходит формирование базы данных социологического опроса в Excel.
В первый столбец матрицы необходимо внести номера анкет, а в первую строку – краткие формулировки вопросов или их номера. Таким образом, каждой строке матрицы соответствует одна анкета, а каждому столбцу – один вопрос или подвопрос (рис. 2).
Рис. 2. Фрагмент базы данных социологического опроса в Excel
Поскольку во втором вопросе анкеты (рис.1) респондент может выбрать несколько вариантов, вопрос необходимо разбить на колонки по числу вариантов ответа (подвопросы).
При обработке вопроса заданного в виде таблицы, следует разбивать его на подвопросы по количеству строк.
Затем в матрицу вносятся данные всех анкет в соответствии с ранее произведенным кодированием.
Таким образом, согласно нашей матрице, респондент заполнивший анкету № 2, имеющий стаж работы более 23 лет, выбрал в качестве ответов на второй вопрос варианты №2, 4, 6 (возможность сделать хорошую карьеру, интерес к науке, свободный график работы и возможность совместительства). Он же удовлетворен заработной платой; скорее удовлетворен графиком работы; не удовлетворен разнообразием выполняемой деятельности; скорее не удовлетворен возможностями карьерного роста.
Для удобства формирования базы данных социологического опроса рекомендуется закреплять первую строку матрицы (вкладка «Вид» → «Закрепить области» → «Закрепить верхнюю строку») (рис. 3), что позволит всегда видеть заголовок таблицы.
Рис. 3 Матрица данных с закрепленным заголовком
Кроме того, если в анкете присутствует значительное количество вопросов, требующих разбивки в матрице данных, эти вопросы желательно выделять одним цветом (щелчок левой кнопкой мыши по столбцу выделяет его, далее во вкладке «Главная» выбираем «Заливка» и необходимый цвет).
На третьем этапе исследователем должен быть осуществлен поиск и устранение ввода ошибочных значений. Реализуется такая процедура с помощью функции «Условное форматирование», она позволяет выделить цветом все ячейки, содержащие ошибку. Согласно нашей кодировке в вопросе № 1 в матрице данных могут присутствовать только значения 1-5. Все иные цифры являются ошибочными и должны быть исправлены. Для поиска иных значений в вопросе №1 выделим его щелчком мыши. Далее перейдем во вкладку «Главная» → «Условное форматирование» → «Создать правило». В открывшемся окне отметим «Форматировать только ячейки, которые содержат» в полях раздела «Форматировать только ячейки, для которых выполняется следующее условие», выберем «значение ячейки», «вне», «1», «5». Затем выберем требуемый формат, например фон. При нажатии кнопки «OK», Excel выделит зеленым ошибочные значения. (Рис. 4).
Рис. 4. Поиск ошибок ввода данных
На четвертом этапе происходит непосредственная обработка социологической информации. Для подсчета процентного распределения ответов на вопросы, предполагающие только один ответ, необходимо пользоваться функцией «СЧЕТЕСЛИ». Для этого под таблицей, в столбце «№ анкеты» прописываем номера вариантов ответа на вопросы. Во втором столбце прописываем формулу (рис. 5). В нашем примере формула подсчета первого варианта ответа на вопрос о стаже работы будет иметь следующий вид:
=СЧЁТЕСЛИ(B2:B11;1)/10, где
B2:B11- столбец, в котором находятся интересующие нас ответы;
1 – номер варианта ответа, процент которого необходимо посчитать;
10 – общее количество анкет.
Для подсчета второго варианта, формула приобретет значение: =СЧЁТЕСЛИ(B2:B11;2)/10. Полученное число необходимо перевести в процентный формат: вкладка «Главная» → «Процентный формат».
Когда все варианты ответа в первом столбце просчитаны, формулу можно растянуть вправо для подсчета процентов по всем вопросам, предполагающим один ответ.
Рис. 5 Подсчет процентного распределения ответов на вопросы, предполагающие один вариант
Если вопрос предполагает множественный ответ, то расчет процентного соотношения ответов рассчитывается следующим образом: сначала необходимо узнать, сколько всего ответов дали респонденты при ответе на вопрос. Для этого воспользуемся счетом заполненных ячеек, с помощью формулы: =СЧЁТЗ(C2:J11), где C2:J11- диапазон столбцов, в которых находятся интересующие нас ответы.
Далее применим формулу использованную ранее. Для подсчета процентного распределения первого варианта ответа во втором вопросе анкеты, формула будет иметь вид:
=СЧЁТЕСЛИ(C2:C11;1)/27, где
C2:C11 – диапазон столбцов, в которых находятся интересующие нас ответы;
1- номер варианта ответа, процент которого необходимо посчитать;
27 – сумма всех ответов на вопрос № 2. (Рис.6)
Рис. 6 Подсчет процентного распределения ответов на вопрос, предполагающий множественный ответ.
Если в ходе исследования социологу необходимо определить связь между признаками, например, выяснить, сколько респондентов со стажем работы от 5 до 10 лет полностью удовлетворены заработной платой (столбец В3-1), необходимо пользоваться формулой вида:
=СЧЁТЕСЛИМН(B2:B11;2;K2:K11;1)/СЧЁТЕСЛИ(B2:B11;2), где
B2:B11 – диапазон столбцов, в которых находятся ответы о стаже работы;
2 – код ответа, обозначающий стаж работы от 5 до 10 лет;
K2:K11- диапазон столбцов, в которых находятся ответы об удовлетворенностью заработной платой;
1 – код ответа, обозначающий полную удовлетворенность заработной платой.
Таким образом, с помощью программы MS Excel, социолог может в сжатые сроки базовый анализ данных, интерпретировать значительные числовые массивы, полученные в ходе эмпирических исследований. Высокая адаптивность и простота работы, легкость экспорта данных, как между пользователями, так и между другими программными продуктами, позволяет реализовать на практике любой метод количественных исследований и решить большую часть задач, встречающихся в работе социолога.
Базовый анализ данных средствами MS Excel
В предыдущем подразделе мы коротко описали порядок обработки данных опросов, принятый в Фонде Общественное Мнение. Каждая исследовательская организация использует свою, детально разработанную технологию обработки данных. Однако можно строить таблицы распределения ответов и без использования специального программного обеспечения. Например, почти все описанное выше можно сделать с помощью программы MS Excel. Заготовив в первой строке листа названия переменных (см. табл. 12.1), можно (хотя, конечно, довольно трудно и опасно с точки зрения ошибок) вводить данные непосредственно в клетки этого листа. После ввода можно получать таблицы распределения ответов.
Покажем, как можно получить таблицу, аналогичную табл. 12.1, пользуясь стандартными средствами MS Excel-2007. Возьмем, к примеру, вопрос "Q29". Это вопрос, допускающий лишь один вариант ответа (альтернативный), и для представления таких вопросов, как уже отмечалось, достаточно одного столбца (табл. 12.2).
Мы видим, что первая строка листа Excel содержит обозначения вопросов. Первый столбец отведен под ответы на сам интересующий нас вопрос, а второй — под данные о возрасте респондента.
При вводе данных столбец, соответствующий альтернативному вопросу, содержал номера выбранных ответов. Для того чтобы результаты расчетов в MS Excel были наглядными, можно с помощью контекстной замены приписать каждому варианту ответа его значение. Например, заменить "1" на "1. в Интернете" и т.д.
Таблица 12.2. Ответы респондентов на вопрос: "Одни считают, что большинство товаров удобнее выбирать в Интернете. Другие — что удобнее делать это в обычных магазинах. С каким мнением — с первым или со вторым — Вы согласны?" и данные о возрасте респондентов
Весьма удобным инструментом для выполнения интересующих нас расчетов в MS Excel является механизм сводных таблиц (Pivot Tables). Покажем, как он применяется в данном случае.
В программе MS Excel в составе комплекса MS Office-2007 подменю "Вставка сводной таблицы" расположено на вкладке "Вставка" слева. Перед выбором этого подменю удобно, что называется, "встать мышкой" внутрь диапазона данных, т.е. сделать активной любую заполненную клетку таблицы. При выборе подменю возникает диалог, представленный на рис. 12.3. Как мы видим, программа сама распознает размеры таблицы с данными: она занимает 2 столбца (Л и В) и 1476 строк, причем первая — строка заголовков, а остальные соответствуют каждому из 1475 респондентов, опрошенных в ходе опроса.
Рис. 12.3. Диалог при вставке сводной таблицы
Мы видим, что по умолчанию сводная таблица будет вставлена на новый лист. С этим предложением следует согласиться, нажав клавишу "ОК". После этого на новом листе ("Лист 4") возникнет следующая конфигурация (рис. 12.4).
Рис. 12.4. Диалог при вставке сводной таблицы
Рис. 12.5. Назначение полей сводной таблицы
Теперь надо указать, какую именно таблицу мы хотим построить. Нам надо, чтобы столбцы сводной таблицы соответствовали разным возрастам респондентов, а строки — разному отношению к покупкам через Интернет. Соответственно в появившейся справа диалоговой форме под названием "Список полей сводной таблицы" надо "перетащить" поле "Q29" в прямоугольник "Название строк", а поле "Возраст" — в прямоугольник "Названия столбцов". В прямоугольник "Значения" можно "перетащить" любое из полей, например "Q29". После этого в этом прямоугольнике появится надпись "Количество по полю Q29" (рис. 12.5).
После этого возникает табл. 12.3.
Таблица 12.3. Сводная таблица в абсолютных значениях
В каждой клетке таблицы указано число респондентов определенного возраста, давших определенный ответ. Нам, однако, необходимо рассчитать не абсолютное число респондентов, а процент по столбцу, т.е. долю респондентов, давших определенный ответ, в процентах от числа всех респондентов определенного возраста.
Для этого нужно щелкнуть кнопкой мыши на названии поля "Количество по полю Q29" и в открывшемся контекстном меню выбрать строку "Параметры поля значений". Откроется следующее диалоговое окно (рис. 12.6).
По умолчанию в этом диалоговом окне откроется вкладка "Операция". Нужно перейти на вкладку "Дополнительные вычисления" и выбрать вариант "Доля от суммы по столбцу". Затем необходимо нажать на кнопку "Числовой формат" и задать процентный формат с нулем знаков после запятой.
Рис. 12.6. Назначение полей сводной таблицы
После этого таблица данных преобразуется к следующему виду (табл. 12.4).
Таблица 12.4. Сводная таблица в абсолютных значениях
Сравнив табл. 12.4 и 12.1, можно убедиться, что расчеты с помощью MS Excel дали тот же результат, что и расчеты с помощью специального программного обеспечения Фонда Общественное Мнение.
Таким образом, механизм сводных таблиц позволяет при известном навыке достаточно легко осуществлять простейшую обработку результатов опросов, не прибегая к специальному программному обеспечению. Более того, мы отразили только самые простейшие возможности этого механизма. Для освоения других его возможностей (в частности, сводных диаграмм) читатель может обратиться к специальной литературе или воспользоваться уроками, входящими в состав программного комплекса MS Office компании Microsoft.
И все же надо иметь в виду, что специализированное программное обеспечение обладает неизмеримо более широкими возможностями как в плане удобства, так и в плане глубины получаемых результатов. В частности, для поиска разного рода неочевидных закономерностей применяется пакет Marketing Engineering, являющийся надстройкой к MS Excel, или комплекс программ SPSS. Первый из этих программных продуктов мы в этой книге рассматривать не будем. Что касается второго, то последующее обсуждение методов анализа данных мы будем вести, ориентируясь на возможности комплекса программ SPSS.