Excel создание анкеты на

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

Применение инструментов заполнения

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

Теперь давайте рассмотрим, как пользоваться этими двумя видами инструментов.

Способ 1: встроенный объект для ввода данных Excel

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

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



2. В открывшемся окне параметров Эксель перемещаемся в раздел «Панель быстрого доступа». Большую часть окна занимает обширная область настроек. В левой её части находятся инструменты, которые могут быть добавлены на панель быстрого доступа, а в правой – уже присутствующие.

   В поле «Выбрать команды из» устанавливаем значение «Команды не на ленте». Далее из списка команд, расположенного в алфавитном порядке, находим и выделяем позицию «Форма…». Затем жмем на кнопку «Добавить».



3. После этого нужный нам инструмент отобразится в правой части окна. Жмем на кнопку «OK».



4. Теперь данный инструмент располагается в окне Excel на панели быстрого доступа, и мы им можем воспользоваться. Он будет присутствовать при открытии любой книги данным экземпляром Excel.



5. Теперь, чтобы инструмент понял, что именно ему нужно заполнять, следует оформить шапку таблицы и записать любое значение в ней. Пусть табличный массив у нас будет состоять из четырех столбцов, которые имеют названия «Наименование товара», «Количество», «Цена» и «Сумма». Вводим данные названия в произвольный горизонтальный диапазон листа.



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



7. После этого выделяем любую ячейку заготовки таблицы и щелкаем на панели быстрого доступа по значку «Форма…», который мы ранее активировали.



8. Итак, открывается окно указанного инструмента. Как видим, данный объект имеет поля, которые соответствуют названиям столбцов нашего табличного массива. При этом первое поле уже заполнено значением, так как мы его ввели вручную на листе.



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



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





11. Заполняем окно инструмента теми значениями, которые хотим видеть во второй строке табличной области, и снова щелкаем по кнопке «Добавить».



12. Как видим, значения второй строчки тоже были добавлены, причем нам даже не пришлось переставлять курсор в самой таблице.



13. Таким образом, заполняем табличный массив всеми значениями, которые хотим в неё ввести.



14. Кроме того, при желании, можно производить навигацию по ранее введенным значениям с помощью кнопок «Назад» и «Далее» или вертикальной полосы прокрутки.



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



16. Как видим, изменение сразу произошло и в табличной области.



17. Если нам нужно удалить, какую-то строчку, то через кнопки навигации или полосу прокрутки переходим к соответствующему ей блоку полей в форме. После этого щелкаем по кнопке «Удалить» в окошке инструмента.



18. Открывается диалоговое окно предупреждения, в котором сообщается, что строка будет удалена. Если вы уверены в своих действиях, то жмите на кнопку «OK».



19. Как видим, строчка была извлечена из табличного диапазона. После того, как заполнение и редактирование закончено, можно выходить из окна инструмента, нажав на кнопку «Закрыть».



20. После этого для предания табличному массиву более наглядного визуального вида можно произвести форматирование.

Способ 2: создание пользовательской формы

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

1. Как и в предыдущем способе, прежде всего, нужно составить шапку будущей таблицы на листе. Она будет состоять из пяти ячеек с именами: «№ п/п», «Наименование товара», «Количество», «Цена», «Сумма».



2. Далее нужно из нашего табличного массива сделать так называемую «умную» таблицу, с возможностью автоматического добавления строчек при заполнении соседних диапазонов или ячеек данными. Для этого выделяем шапку и, находясь во вкладке «Главная», жмем на кнопку «Форматировать как таблицу» в блоке инструментов «Стили». После этого открывается список доступных вариантов стилей. На функционал выбор одного из них никак не повлияет, поэтому выбираем просто тот вариант, который считаем более подходящим.



3. Затем открывается небольшое окошко форматирования таблицы. В нем указан диапазон, который мы ранее выделили, то есть, диапазон шапки. Как правило, в данном поле заполнено все верно. Но нам следует установить галочку около параметра «Таблица с заголовками». После этого жмем на кнопку «OK».



4. Итак, наш диапазон отформатирован, как «умная» таблица, свидетельством чему является даже изменение визуального отображения. Как видим, помимо прочего, около каждого названия заголовка столбцов появились значки фильтрации. Их следует отключить. Для этого выделяем любую ячейку «умной» таблицы и переходим во вкладку «Данные». Там на ленте в блоке инструментов «Сортировка и фильтр» щелкаем по значку «Фильтр».
   

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



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



6. Затем нам следует создать саму форму ввода данных. Она тоже будет представлять собой своего рода табличный массив, состоящий из двух столбцов. Наименования строк данного объекта будут соответствовать именам столбцов основной таблицы. Исключение составляют столбцы «№ п/п» и «Сумма». Они будут отсутствовать. Нумерация первого из них будет происходить при помощи макроса, а расчет значений во втором будет производиться путем применения формулы умножения количества на цену.

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



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



8. Затем выделяем первую пустую ячейку объекта ввода значений. Переходим во вкладку «Данные». Щелкаем по значку «Проверка данных», который размещен на ленте в блоке инструментов «Работа с данными».



9. Запускается окно проверки вводимых данных. Кликаем по полю «Тип данных», в котором по умолчанию установлен параметр «Любое значение».



10. Из раскрывшихся вариантов выбираем позицию «Список».



11. Как видим, после этого окно проверки вводимых значений несколько изменило свою конфигурацию. Появилось дополнительное поле «Источник». Щелкаем по пиктограмме справа от него левой клавишей мыши.



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



13. Происходит возврат к окошку проверки вводимых значений. Как видим, координаты выделенного диапазона в нем уже отображены в поле «Источник». Кликаем по кнопке «OK» внизу окна.



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



15. Как видим, выбранная позиция тут же отобразилась в поле «Наименование товара».



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



17. Точно таким же образом присваиваем ячейке, в которую будем вводить количество товара, имя «Volum».



18. А ячейке с ценой – «Price».



19. После этого точно таким же образом даем название всему диапазону из вышеуказанных трех ячеек. Прежде всего, выделим, а потом дадим ему наименование в специальном поле. Пусть это будет имя «Diapason».



20. После последнего действия обязательно сохраняем документ, чтобы названия, которые мы присвоили, смог воспринимать макрос, созданный нами в дальнейшем. Для сохранения переходим во вкладку «Файл» и кликаем по пункту «Сохранить как…».



21. В открывшемся окне сохранения в поле «Тип файлов» выбираем значение «Книга Excel с поддержкой макросов (.xlsm)». Далее жмем на кнопку «Сохранить».



22. Затем вам следует активировать работу макросов в своей версии Excel и включить вкладку «Разработчик», если вы это до сих пор не сделали. Дело в том, что обе эти функции по умолчанию в программе отключены, и их активацию нужно выполнять принудительно в окне параметров Excel.
23. После того, как вы сделали это, переходим во вкладку «Разработчик». Кликаем по большому значку «Visual Basic», который расположен на ленте в блоке инструментов «Код».



24. Последнее действие приводит к тому, что запускается редактор макросов VBA. В области «Project», которая расположена в верхней левой части окна, выделяем имя того листа, где располагаются наши таблицы. В данном случае это «Лист 1».



25. После этого переходим к левой нижней области окна под названием «Properties». Тут расположены настройки выделенного листа. В поле «(Name)» следует заменить кириллическое наименование («Лист1») на название, написанное на латинице. Название можно дать любое, которое вам будет удобнее, главное, чтобы в нем были исключительно символы латиницы или цифры и отсутствовали другие знаки или пробелы. Именно с этим именем будет работать макрос. Пусть в нашем случае данным названием будет «Producty», хотя вы можете выбрать и любое другое, соответствующее условиям, которые были описаны выше.

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

    Как видим, после этого автоматически изменится и наименование Листа 1 в области «Project», на то, которое мы только что задали в настройках.



26. Затем переходим в центральную область окна. Именно тут нам нужно будет записать сам код макроса. Если поле редактора кода белого цвета в указанной области не отображается, как в нашем случае, то жмем на функциональную клавишу F7 и оно появится.



27. Теперь для конкретно нашего примера нужно записать в поле следующий код:

    
Sub DataEntryForm()
Dim nextRow As Long
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
With Producty
If .Range("A2").Value = "" And .Range("B2").Value = "" Then
nextRow = nextRow - 1
End If
Producty.Range("Name").Copy
.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volum").Value
.Cells(nextRow, 4).Value = Producty.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value
.Range("A2").Formula = "=IF(ISBLANK(B2), """", COUNTA($B$2:B2))"
If nextRow > 2 Then
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
End If
.Range("Diapason").ClearContents
End With
End Sub


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

    Итак, первая строка:

    Sub DataEntryForm()

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

    Везде, где встречается в коде слово «Producty» вы должны его заменить на то наименование, которое ранее присвоили для своего листа в поле «(Name)» области «Properties» редактора макросов. Естественно, это нужно делать только в том случае, если вы назвали лист по-другому.

    

    Теперь рассмотрим такую строку:

    nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

    Цифра «2» в данной строчке означает второй столбец листа. Именно в этом столбце находится колонка «Наименование товара». По ней мы будем считать количество рядов. Поэтому, если в вашем случае аналогичный столбец имеет другой порядок по счету, то нужно ввести соответствующее число. Значение «End(xlUp).Offset(1, 0).Row» в любом случае оставляем без изменений.

    

    Далее рассмотрим строку

    If .Range("A2").Value = "" And .Range("B2").Value = "" Then

    «A2» — это координаты первой ячейки, в которой будет выводиться нумерация строк. «B2» — это координаты первой ячейки, по которой будет производиться вывод данных («Наименование товара»). Если они у вас отличаются, то введите вместо этих координат свои данные.

    

    Переходим к строке

    Producty.Range("Name").Copy

    В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.

    

    В строках

    
.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volum").Value
.Cells(nextRow, 4).Value = Producty.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value


    наименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.

    

    В этих же строках, которые мы указали выше, цифры «2», «3», «4», «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара», «Количество», «Цена» и «Сумма». Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.

    

    В строке производится умножение количества товара на его цену:

    .Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value

    Результат, как видим из синтаксиса записи, будет выводиться в пятый столбец листа Excel.

    

    В этом выражении выполняется автоматическая нумерация строк:

    
If nextRow > 2 Then
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
End If


    Все значения «A2» означают адрес первой ячейки, где будет производиться нумерация, а координаты «A» — адрес всего столбца с нумерацией. Проверьте, где именно будет выводиться нумерация в вашей таблице и измените данные координаты в коде, если это необходимо.

    

    В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:

    .Range("Diapason").ClearContents

    Не трудно догадаться, что («Diapason») означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.

    

    Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.

    После того, как вы записали код макроса в окно редактора, следует нажать на значок сохранения в виде дискеты в левой части окна. Затем можно его закрывать, щелкнув по стандартной кнопке закрытия окон в правом верхнем углу.



28. После этого возвращаемся на лист Excel. Теперь нам следует разместить кнопку, которая будет активировать созданный макрос. Для этого переходим во вкладку «Разработчик». В блоке настроек «Элементы управления» на ленте кликаем по кнопке «Вставить». Открывается перечень инструментов. В группе инструментов «Элементы управления формы» выбираем самый первый – «Кнопка».



29. Затем с зажатой левой клавишей мыши обводим курсором область, где хотим разместить кнопку запуска макроса, который будет производить перенос данных из формы в таблицу.



30. После того, как область обведена, отпускаем клавишу мыши. Затем автоматически запускается окно назначения макроса объекту. Если в вашей книге применяется несколько макросов, то выбираем из списка название того, который мы выше создавали. У нас он называется «DataEntryForm». Но в данном случае макрос один, поэтому просто выбираем его и жмем на кнопку «OK» внизу окна.



31. После этого можно переименовать кнопку, как вы захотите, просто выделив её текущее название.
    

    В нашем случае, например, логично будет дать ей имя «Добавить». Переименовываем и кликаем мышкой по любой свободной ячейке листа.



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



33. Как видим, значения перемещены в таблицу, строке автоматически присвоен номер, сумма посчитана, поля формы очищены.



34. Повторно заполняем форму и жмем на кнопку «Добавить».



35. Как видим, и вторая строка также добавлена в табличный массив. Это означает, что инструмент работает.

Читайте также:
Как создать макрос в Excel
Как создать кнопку в Excel

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

Разберем решение еще одной практической ситуации — заполнение электронного бланка анкеты. Сам разрабатываемый лист будет достаточно насыщен элементами управления, поэтому здесь мы рассмотрим оформление листа последовательно.

Начнем разработку (рис. 2.12) с небольших деталей. Так, заполним три ячейки в столбце В поясняющей информацией, а для трех соответствующих ячеек в столбце С необходимо лишь подобрать соответствующее форматирование — заливку и размер шрифта. В дальнейшем в процессе работы с этим бланком пользователь будет вносить в ячейку С2 фамилию, в С4 — имя, а в С6 — отчество. Теперь, как и в предыдущих книгах, следует убрать сетку с рабочего листа.

На рис. 2.12 в правой части расположено три элемента управления: текстовое окно и группа из двух переключателей. Мы уже рассматривали пример, связанный с функционированием переключателей. Этот элемент управления позволяет обеспечить два состояния: «включено» и «выключено». Идея использования двух подобных элементов на нашем листе достаточно простая. А именно, человек, который заполняет бланк, указывает (щелчком на одном из переключателей) один из двух вариантов:

• Н. Новгород;
• Другой город.

В случае выбора варианта Другой город следует указать, какой именно. Это производится в соседнем текстовом окне справа. Понятно, что рассматривается ситуация, когда большинство людей, заполняющих бланк, проживает в Нижнем Новгороде. Зададим значения свойства Name элементов на рис. 2.12 следующим образом:

• Opt1 (переключатель Н. Новгород);
• 0pt2 (переключатель Другой город);
• City (текстовое окно для ввода названия города).

В начальном варианте (при открытии книги) по умолчанию установлен вариант Н. Новгород (это выполняется в окне свойств, где следует установить True в качестве значения свойства Value). При этом текстовое окно для выбора города должно быть невидимым. Для этого в окне свойств для свойства Visible объекта City необходимо установить значение False.

При щелчке на переключателе Другой город текстовое окно City становится видимым, а при щелчке на переключателе с подписью Н. Новгород опять пропадает. Сами тексты процедур обработки щелчков на переключателях, обеспечивающих подобный эффект, приведены в листинге 2.17.

В дальнейшем мы обеспечим программную установку значений свойства Value переключателей и значения свойства Visible текстового окна City.

1
2
3
4
5
6
7
8

'Листинг 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 сосредоточены элементы, которые заполняются при условии, что анкетируемый является студентом. Соответственно, правая часть — для лиц, уже имеющих диплом об образовании. При этом названия Место учебы, Курс, Место работы и Примечание являются элементами управления типа «Надпись». Они введены для пояснения содержимого соседних (находящихся справа от них) текстовых окон. В связи с тем, что эти надписи программно в дальнейшем не используются, имена этих объектов мы не приводим.

Переключатели Студент (Name — St) и Специалист (Name — Sp) относятся к одной группе переключателей, отличной от группы переключателей, используемых для выбора городов. Теперь поясним, как они будут использоваться.

Так, при выборе категории Студент видимыми становятся текстовые окна для заполнения полей анкеты Место учебы (Name — Place) и Курс (Name — Kyrs), а текстовые окна для заполнения полей Место работы (Name — Work) и Примечание (Name — Prim) становятся невидимыми. Соответственно, при выборе категории Специалист все наоборот — видимыми становятся текстовые окна, которые должен заполнить специалист. В нижней части листа на рис. 2.13 располагаются три флажка — это простые элементы управления, в функциональном плане похожие на переключатели. Основное используемое свойство флажка — Value, которое принимает два возможных значения: False и True.

Теперь можно сказать, что мы рассмотрели функциональное назначение элементов на листе электронной анкеты. Перейдем к программным процедурам.

Как уже говорилось, при открытии книги по умолчанию необходимо сделать выбор на вариантах Н.Новгород и заполнении анкеты студентом. Это лучше реализовать в процедуре Workbook_Open (листинг. 2.18).

На панели элементов ActiveX (см. рис. 1.24) пиктограмма элемента управления «Флажок» третья слева.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

' Листинг 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).

1
2
3
4
5
6
7
8
9
10
11
12
13
14

' Листинг 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.15). Здесь для данных по каждому анкетируемому отводится по одной строке. И по щелчку на кнопке Записать на 2-й лист (см. рис. 2.14) информация анкеты переписывается в очередную свободную строку второго листа. В листинге 2.20 приводится текст данной процедуры. Как вы уже заметили, из названия процедуры следует, что для свойства Name кнопки установлено значение WriteList.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

' Листинг 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

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

«Табличный
процессор MS EXCEL.

Составление
анкеты»

Содержание

Введение 
………………………………………………………………..с. 3-4

Глава 1.  Общие
сведения  о табличном  процессоре   MS Excel…….с.
4 –7

1.1  
. Microsoft Excel
. Понятия и возможности………………………..с. 4-5

1.2. Основные элементы окна MS Excel………………………………с. 5-6

1.3. Структура электронных таблиц………………………………….с. 6-7

Глава 2. Создание
базы данных
анкетного опроса: возможности MS Excel.с.8-11

Заключение………………………………………………………………..с.
12

Список литературы………………………………………………………..с.
13

Введение

Человек всегда стремился облегчить
свою деятельность, изобретая всё новые и новые орудия труда, совершенствуя
технологии. Появление ЭВМ дало начало новому этапу развития человеческого
общества – информационному. С тех пор компьютер претерпел серьёзные изменения:
стал персональным, появились новые средства для работы с текстовой, графической
и звуковой информацией. И сейчас область компьютерных технологий стремительно
развивается, постоянно появляются новинки. Однако в рамках уже существующих и
давно используемых возможностей компьютера имеется огромное количество
секретов. Microsoft Excel – одна из самых загадочных и интересных программ в
пакете MS Office. Интересна она многочисленными средствами автоматизации
работы, оформления документов и богатыми вычислительными возможностями.
Загадочность её состоит в том, что большинство пользователей применяют лишь
малую толику того, что может дать им Excel. Это тем более удивительно, что
спектр возможностей программы практически безграничен: от создания простых таблиц,
построения диаграмм и графиков до решения сложных вычислительных задач и
моделирования различных процессов. Мне всегда было интересно: «А как создаются 
тесты, анкеты?»                                                                                                                         Так
появилась проблема моего проекта: я хочу  научиться создавать
анкеты  в программе Excel, но для этого нужны необходимые теоретические знания
и навыки практической работы с программой MS Excel. Исходя
из                                                                    
проблемы, определилась тема проекта.

Тема проекта «Табличный
процессор MS Excel. Составление анкеты».

Актуальность проекта:
Сегодня набирает популярность онлайн-анкетирование, а также
создание анкет в  MS Excel.  Этот способ проведения и
обработки результатов  позволяет охватить огромное количество человек без
каких-либо ограничений, связанных с местоположением участников.

 Цель проекта:
выяснить возможности редактора MS Excel для создания анкет.

Задачи:

·       
изучить теоретический материал о структуре
табличного процессора;

·       
научиться создавать электронную таблицу;

·       
уметь вводить и оформлять информацию.

  Глава
1.  Общие сведения  о табличном  процессоре  
MS Excel

Табличный процессор —
комплекс взаимосвязанных программ, предназначенный для обработки электронных
таблиц. Электронная таблица — компьютерный эквивалент обычной таблицы,
состоящей из строк и столбцов, на пересечении которых располагаются клетки, в
которых содержится числовая информация, формулы или текст. Это наиболее удобная
форма представления данных. Особенность электронных таблиц заключается в
возможности применения формул для описания связи между значениями различных
данных, расчёт по которым выполняется автоматически.    Применение электронных
таблиц упрощает работу с данными и позволяет получать результаты без проведения
расчётов вручную или специального программирования.                     Наиболее
распространёнными средствами для работы с документами, имеющими табличную
структуру, являются программы MICROSOFT EXCEL.

         Microsoft  Excel – программа,
предназначенная для организации данных в таблице для документирования и
графического представления информации. Программа MS Excel применяется при
создании комплексных документов, в которых необходимо использовать одни и те же
данные в разных рабочих листах; изменять и восстанавливать связи. Преимуществом
MS Excel является то, что программа помогает оперировать большими объёмами
информации.

1.1.Microsoft Excel . Понятия и
возможности                                               Табличный
процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых
приложений пакета MS Office. Основное назначение MS Excel – решение практически
любых задач расчетного характера, входные данные которых можно представить в
виде таблиц. Применение электронных таблиц упрощает работу с данными и
позволяет получать результаты без программирования расчётов. Особенность
электронных таблиц заключается в возможности применения формул для описания
связи между значениями различных ячеек.

Основные возможности электронных
таблиц:

·       
проведение
однотипных сложных расчётов над большими наборами данных;

·       
 автоматизация
итоговых вычислений;

·       
решение
задач путём подбора значений параметров;

·       
обработка
(статистический анализ) результатов экспериментов;

·       
проведение
поиска оптимальных значений параметров (решение оптимизационных задач);

·       
подготовка
табличных документов;

·       
построение
диаграмм (в том числе и сводных) по имеющимся данным;

·       
создание
и анализ баз данных (списков).

1.2. Основные элементы окна MS Excel

·       
Основными
элементами рабочего окна являются: строка заголовка (в ней указывается имя программы) с
кнопками управления окном программы и окном документа (Свернуть, Свернуть в
окно или Развернуть во весь экран, Закрыть);

·       
строка
основного меню (каждый пункт меню представляет собой набор команд, объединенных
общей функциональной направленностью) плюс окно для поиска справочной
информации;

·       
панели
инструментов (Стандартная, Форматирование и др.);

·       
строка
формул, содержащая в качестве элементов поле Имя и кнопку Вставка функции (fx),
предназначена для ввода и редактирования значений или формул в ячейках. В поле
Имя отображается адрес текущей ячейки;

·       
рабочая
область (активный рабочий лист);

·       
 полосы
прокрутки (вертикальная и горизонтальная);

·       
набор
ярлычков (ярлычки листов) для перемещения между рабочими листами;

·       
строка
состояния.

1.3
Структура электронных таблиц

     Файл, созданный средствами MS Excel, принято
называть рабочей книгой. Рабочих книг создать можно столько, сколько позволит
наличие свободной памяти на соответствующем устройстве памяти. Открыть рабочих
книг можно столько, сколько их создано. Однако активной рабочей книгой может
быть только одна текущая (открытая) книга.                                                                  Рабочая
книга представляет собой набор рабочих листов, каждый из которых имеет
табличную структуру. В окне документа отображается только текущий (активный)
рабочий лист, с которым и ведётся работа. Каждый рабочий лист имеет название,
которое отображается на ярлычке листа в нижней части окна. С помощью ярлычков
можно переключаться к другим рабочим листам, входящим в ту же рабочую книгу.
Чтобы переименовать рабочий лист, надо дважды щёлкнуть мышкой на его ярлычке и
заменить старое имя на новое или путём выполнения следующих команд: меню
Формат, строка Лист в списке меню, Переименовать. А можно и, установив
указатель мышки на ярлык активного рабочего листа, щёлкнуть правой кнопкой
мыши, после чего в появившемся контекстном меню щёлкнуть по строке
Переименовать и выполнить переименование. В рабочую книгу можно добавлять
(вставлять) новые листы или удалять ненужные. Вставку листа можно осуществить
путём выполнения команды меню Вставка, строка Лист в списке пунктов меню. Чтобы
поменять местами рабочие листы нужно указатель мышки установить на ярлычок
перемещаемого листа, нажать левую кнопку мышки и перетащить ярлычок в нужное
место.                                                                                                       Рабочий
лист (таблица) состоит из строк и столбцов. Столбцы озаглавлены прописными
латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист
содержит 256 столбцов, поименованных от A до IV. Строки последовательно
нумеруются числами от 1 до 65536.                   На пересечении столбцов и
строк образуются ячейки таблицы. Они являются минимальными элементами,
предназначенными для хранения данных. Каждая ячейка имеет свой адрес. Адрес
ячейки состоит из имени столбца и номера строки, на пересечении которых
расположена ячейка, например, A1, B5, DE324. Адреса ячеек используются при
записи формул, определяющих взаимосвязь между значениями, расположенными в
разных ячейках. В текущий момент времени активной может быть только одна
ячейка, которая активизируется щелчком мышки по ней и выделяется рамкой. Эта
рамка в Excel играет роль курсора. Операции ввода и редактирования данных
всегда производятся только в активной ячейке. В целом MS Excel содержит более
400 функций рабочего листа (встроенных функций). Все они в соответствии с
предназначением делятся на 11 групп (категорий):

1. финансовые функции;        2. функции даты и
времени;

3. арифметические и тригонометрические
(математические) функции;

4. статистические функции;   5. функции ссылок и
подстановок;

6. функции баз данных (анализа списков);   7.
текстовые функции; 8. логические функции; 9. информационные функции (проверки
свойств и значений);  10.инженерные функции;   11.внешние функции.

Глава 2. Создание
базы данных
анкетного опроса: возможности MS Excel

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

Анкетирование — эмпирический
метод исследования, который строится на опросе определенного числа респондентов
с помощью анкет.

Анкета – растиражированный,
структурированный список вопросов, созданный с определенной исследовательской
целью.

По способу представления
вопросов анкеты различают: анкетирование на основе печатной формы, офлайн
(offline) и онлайн (online) анкетирование. Офлайн анкетирование представляет
собой представление вопросов анкеты респонденту в виде электронного документа
без подключения к сети Интернет (например, анкета в приложении MS Office
Excel). Респонденты заполняют анкету, отвечают на вопросы открытого и закрытого
типа на компьютере, полученные результаты сохраняются и/или отправляются
разработчику анкеты на любой носитель информации и по электронной почте для
дальнейшей обработки полученных данных.

Достаточно значимый процент
клиентов желает видеть выходные данные после обработки бумажных анкет в
электронных таблицах. Речь идет о программе Excel из офисного пакета Microsoft
Office. Как правило, пользователи хорошо знакомы с данным продуктом по причине
его популярности и простоты использования. Программа позволяет оперативно найти
данные из любой анкеты, сделать отчет или построить график.

Первый шаг обработки данных
анкетного опроса — формирование электронной (компьютерной) базы данных.

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

Когда все открытые вопросы
анкеты приведены в «закрытый» вид, следует присвоить числовой код каждому
варианту ответа в каждом вопросе, то есть закодировать его. Если вопрос задан в
виде таблицы, то при его анализе необходимо каждую строку ответа кодировать как
отдельный вопрос. Если же респондент пропустил вопрос или не смог ответить на
него, то код отсутствию ответа не присваивается.                                                                                       На
втором этапе происходит формирование базы данных социологического
опроса в Excel. В первый столбец матрицы необходимо внести номера анкет, а в
первую строку — краткие формулировки вопросов или их номера. Таким образом,
каждой строке матрицы соответствует одна анкета, а каждому столбцу — один
вопрос или подвопрос.  Поскольку во втором вопросе анкеты
респондент может выбрать несколько вариантов, вопрос необходимо разбить на
колонки по числу вариантов ответа (подвопросы).                            При
обработке вопроса, заданного в виде таблицы, следует разбивать его на
подвопросы по количеству строк. Затем в матрицу вносятся данные всех анкет в
соответствии с ранее произведенным кодированием.                                Для
удобства формирования базы данных социологического опроса рекомендуется
закреплять первую строку матрицы (вкладка «Вид» > «Закрепить области» >
«Закрепить верхнюю строку») (рис. 3), что позволит всегда видеть заголовок
таблицы.  Кроме того, если в анкете присутствует значительное
количество вопросов, требующих разбивки в матрице данных, эти вопросы
желательно выделять одним цветом (щелчок левой кнопкой мыши по столбцу выделяет
его, далее во вкладке «Главная» выбираем «Заливка» и необходимый цвет).                                                                                                   На
третьем этапе исследователем должен быть осуществлен поиск и
устранение ввода ошибочных значений. Реализуется такая процедура с помощью
функции «Условное форматирование», она позволяет выделить цветом все ячейки,
содержащие ошибку. Согласно нашей кодировке в вопросе № 1 в матрице данных
могут присутствовать только значения 1-5. Все иные цифры являются ошибочными и
должны быть исправлены. Для поиска иных значений в вопросе №1 выделим его
щелчком мыши. Далее перейдем во вкладку «Главная» > «Условное
форматирование» > «Создать правило». В открывшемся окне отметим
«Форматировать только ячейки, которые содержат» в полях раздела «Форматировать
только ячейки, для которых выполняется следующее условие», выберем «значение
ячейки», «вне», «1», «5». Затем выберем требуемый формат, например фон. При
нажатии кнопки «OK», Excel выделит зеленым ошибочные значения.                                                                    На
четвертом этапе происходит непосредственная обработка социологической
информации. Для подсчета процентного распределения ответов на вопросы,
предполагающие только один ответ, необходимо пользоваться функцией «СЧЕТЕСЛИ».
Для этого под таблицей, в столбце «№ анкеты» прописываем номера вариантов
ответа на вопросы. Во втором столбце прописываем формулу. Если вопрос предполагает множественный ответ, то расчет
процентного соотношения ответов рассчитывается следующим образом: сначала
необходимо узнать, сколько всего ответов дали респонденты при ответе на вопрос.
Для этого воспользуемся счетом заполненных ячеек, с помощью формулы: =СЧЁТЗ(C2:J11),
где C2:J11- диапазон столбцов, в которых находятся интересующие нас ответы.
Далее применим формулу, использованную ранее. Для подсчета процентного
распределения первого варианта ответа во втором вопросе анкеты формула будет
иметь вид:

=СЧЁТЕСЛИ(C2:C11;1)/27, где

C2:C11 — диапазон столбцов, в которых находятся интересующие нас ответы:

·       
номер
варианта ответа, процент которого необходимо посчитать;

·       
27 —
сумма всех ответов на вопрос № 2.

Заключение

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

Список литературы

1. Айзек М.П. Вычисления, графики и анализ данных в Excel 2013.
Самоучитель. – М.П. Айзек. — СПб.: Наука и техника, 2015. — 416 c

2.
Лазарева А.В. Интересные возможности программы Excel / Юный ученый. — 2019. — №
3.1 (23.1). — С. 58-60. https://moluch.ru/young/archive/23/1450/

3. Леонтьев В.П. Excel 2016. Новейший
самоучитель / В.П. Леонтьев. — М.: Эксмо, 2018. — 640

4. Карчевский, Е. М.  Филиппов, И. Е.  Филиппова, И.А. Учебное пособие

«Excel 2010 в примерах»-Казань, 2012 – 100 с.

5.Слесарева Е.А., Смирнов Д.Е. Информационные технологии как средство
решения практических задач в деятельности психолога // Государственная служба и
кадры №4, 2016

6.Рабочая книга социолога / под ред. Г.В. Осипова. Изд. 4-е,
стереотипное. — М.: КомКнига, 2006. — 480 с.

7.https://nsportal.ru/vuz/fiziko-matematicheskie
nauki/library/2014/11/06/informatika-rabota-v-microsoft-excel

8.https://itguides.ru/soft/excel/tablichnyj-processor-excel.html

9.https://ru.wikipedia.org/wiki/Microsoft_Excel

10.https://support.microsoft.com/ru-ru/office