Создание формы в excel

Обновлено: 07.07.2024

Представим ситуацию: отдел маркетинга уже начал готовиться к новому году (кстати, 1 сентября, уже пора!), придумал концепцию поздравления, три варианта подарка и запросил у всех сотрудников список партнеров с указанием, кому какой подарок дарить.

Наша же задача, собрать эти данные, внести в одну общую таблицу, в одинаковом виде, чтобы с данными потом было удобно работать. Как лучше всего это сделать? Рассмотрим несколько вариантов:

Онлайн таблица
Рассылка шаблона и сбор ответных данных с помощью Query (Получить данные)
Формы
Автоматизация (сервис Flow)

Вариант первый - онлайн таблица.

Создаем шаблон для ввода данных на листе, оформляем его, как таблицу (Ctrl+T) - это значительно упростит ввод и обработку данных. Если делаете, как в примере выше, нужно отметить галочкой, что в таблице уже есть заголовки (My table has headers).

Теперь данные в таблицу добавляются легко - просто нужно начать писать на следующей, после окончания таблице строчке, и они автоматом добавятся в таблицу. Кстати, после ввода данных в первую ячейку вместо Enter можно нажать Tab, и курсор переместится в следующую ячейку.

Добавление данных - вместо Enter можно нажать Tab. Листайте вправо --> Добавление данных: в таблицу добавилась новая строка.

Если сотрудников и партнеров немного, то можно так и оставить, и добавить остальных вручную. Если их больше 10-20, это уже займет достаточно много времени, и лучше пусть сотрудники сами заполняют.

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

Добавление условного форматирования. Листайте вправо --> Добавление условного форматирования. Листайте вправо -->

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

Осталось дело за малым - выложить таблицу на сервер SharePoint или SharePoint Online или OneDrive или OneDrive Business и предоставить доступ на редактирования всем сотрудникам.

После дедлайна таблицу можно "суммировать" с помощью сводной таблицы и предоставить отделу маркетинга готовые данные.

Плюсы : просто, быстро, все сотрудники будут заносить данные самостоятельно.

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

Рассылка шаблона и сбор данных обратно по почте.

Microsoft Excel содержит несколько элементов управления для листов диалогов, которые удобно использовать для выбора элементов из списка. Примерами элементов управления могут быть списки, поля со списком, счетчики и полосы прокрутки.

Дополнительные сведения об элементе управления формы в Excel см. в ActiveX формах и формах.

Дополнительные сведения

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

Включить вкладку "Разработчик"

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

Откройте вкладку Файл и выберите команду Параметры.

В правой области Основные вкладки выберите разработчик и нажмите кнопку ОК.

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

Щелкните Популярные,выберите вкладкуПоказать вкладку "Разработчик" на ленте и нажмите кнопку ОК.

Настройка списка, ссылки на ячейку и индекса

Введите на новый таблицу следующие элементы в диапазоне H1:H20:

H1: ОльговСкие о-ва

H7 : Rocket Launcher

H18: набор инструментов

H20: жесткий диск

В ячейке A1 введите следующую формулу:

Пример списка

Чтобы добавить список в Excel 2007 и более поздних версиях, на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в группе Элементы управления формы выберите элемент Форма списка (элемент управления).

Чтобы добавить список в Excel 2003 и более ранних версиях Excel, нажмите кнопку Список на панели инструментов Формы. Если панель инструментов Формы не отображается, найдите в меню Вид пункт Панели инструментов и выберите пункт Формы.

Щелкните место на листе, где должен отображаться левый верхний угол списка, и перетащите его в то место, где должен быть нижний правый угол списка. В этом примере создайте список для ячеек B2:E10.

В группе Элементы управления нажмите кнопку Свойства.

В окне Формат объекта введите следующие данные и нажмите кнопку ОК.

Чтобы указать диапазон для списка, введите H1:H20 в поле Диапазон ввода.

Чтобы поместить число в ячейку G1 (в зависимости от того, какой элемент выбран в списке), введите G1 в поле Связь с ячейкой.

Примечание: Формула ИНДЕКС() использует значение в G1 для возврата правильного элемента списка.

Убедитесь,что в области Тип выделения выбран вариант Один.

Примечание: Параметры Multi и Extend полезны только при использовании процедуры Microsoft Visual Basic для приложений для возврата значений списка. Обратите внимание также на то, что при этом к списку добавляется объемный вид.

В списке должен отображаться список элементов. Чтобы использовать список, щелкните любую ячейку, чтобы он не был выбран. Если щелкнуть элемент в списке, ячейка G1 будет обновлена на число, которое указывает положение элемента, выбранного в списке. Формула ИНДЕКС в ячейке A1 использует это число для отображения имени элемента.

Пример "Поле со combo"

Чтобы добавить поле со полем со Excel 2007 и более поздних версий, на вкладке Разработчик нажмите кнопку Вставить ивыберите поле со полем со полем в области Элементы управления формы.

Чтобы добавить поле со Excel 2003 и более ранних версиях Excel, нажмите кнопку Поле со полем со полем на панели инструментов Формы.

Щелкните место на листе, где должен отображаться левый верхний угол списка, а затем перетащите поле со списком в то место, где должен быть нижний правый угол списка. В этом примере создайте поле соbo, которое охватывает ячейки B2:E2.

Щелкните правой кнопкой мыши поле со полем и выберите форматирование.

Введите следующую информацию и нажмите кнопку ОК.

Чтобы указать диапазон для списка, введите H1:H20 в поле Диапазон ввода.

Примечание: Формула ИНДЕКС использует значение в G1 для возврата правильного элемента списка.

В поле Drop down lines (Вниз) введите 10. Эта запись определяет, сколько элементов будет отображаться перед использованием точки прокрутки для просмотра других элементов.

Примечание: При желании не может быть затенение. Она добавляет трехмерный вид в поле со полем со структурой.

В поле со списком должен отображаться список элементов. Чтобы использовать поле со полем со ссылкой или полем со ссылкой, щелкните любую ячейку, чтобы объект не был выбран. При щелчке элемента в поле со списком или в поле со списком ячейка G1 обновляется на число, которое указывает на положение в списке выбранного элемента. Формула ИНДЕКС в ячейке A1 использует это число для отображения имени элемента.

Пример счетчика

Чтобы добавить счетчик в Excel 2007 и более поздних версиях, на вкладке Разработчик нажмите кнопку Вставить ив области Элементы управления формынажмите кнопку Счетчик.

Чтобы добавить счетчик в Excel 2003 и более ранних версиях Excel, нажмите кнопку Счетчик на панели инструментов Формы.

Щелкните место, где должен отображаться левый верхний угол счетчика, и перетащите его в то место, где должен быть нижний правый угол счетчика. В этом примере создайте счетчик, который охватывает ячейки B2: B3.

Щелкните правой кнопкой мыши счетчик и выберите форматирование.

Введите следующую информацию и нажмите кнопку ОК.

В поле Текущее значение введите 1.

Это значение инициализирует счетчик, чтобы формула ИНДЕКС укачивает на первый элемент в списке.

В поле Минимальное значение введите 1.

Это значение ограничивает верхнюю часть счетчика первым элементом в списке.

В поле Максимальное значение введите 20.

Это число определяет максимальное количество записей в списке.

В поле Приращение введите 1.

Это значение управляет приращением текущего значения при счетчике.

Щелкните любую ячейку, чтобы счетчик не был выбран. При нажатии на счетчик элемента управления вверх или вниз ячейка G1 обновляется на число, которое указывает текущее значение счетчика плюс или минус добавочная смена счетчика. Это число затем обновляет формулу ИНДЕКС в ячейке A1 для показа следующего или предыдущего элемента.

Значение счетчика не изменится, если текущее значение — 1, если щелкнуть вниз или если текущее значение 20 и при нажатии кнопки вверх.

Пример scroll bar

Чтобы добавить полоса прокрутки в Excel 2007 и более поздних версиях, на вкладке Разработчик нажмите кнопку Вставить ив области Элементы управления формы выберите элемент Полоса прокрутки.

Чтобы добавить в Excel 2003 и более ранних версиях Excel, нажмите кнопку Scroll Bar на панели инструментов Формы.

Щелкните правой кнопкой мыши прокрутку и выберите форматирование.

Введите следующую информацию и нажмите кнопку ОК.

В поле Текущее значение введите 1.

Это значение инициализирует ось прокрутки, чтобы формула ИНДЕКС укачивала на первый элемент в списке.

В поле Минимальное значение введите 1.

Это значение ограничивает верхнюю часть верхней части ручека первым элементом в списке.

В поле Максимальное значение введите 20. Это число определяет максимальное количество записей в списке.

В поле Приращение введите 1.

Это значение управляет тем, сколько чисел приращение текущего значения на панели прокрутки.

В поле Изменение страницы введите 5. Это значение управляет тем, насколько приращением будет текущее значение, если щелкнуть в окне прокрутки с обеих сторон окна прокрутки.

Примечание: При желании не может быть затенение. К панели прокрутки будет добавлен трехмерный вид.

Щелкните любую ячейку, чтобы не выделить прокрутку. При нажатии на элемент управления прокрутки вверх или вниз ячейка G1 обновляется на число, которое указывает текущее значение крутки в плюс или минус добавочная смена крутки. Это число используется в формуле ИНДЕКС в ячейке A1 для показа элемента рядом с текущим элементом или перед этим. Вы также можете перетащить поле прокрутки, чтобы изменить значение, или щелкнуть в окне прокрутки с обеих сторон, чтобы приращение на 5 (значение изменения страницы). Прокрутка не изменится, если текущее значение — 1, если щелкнуть вниз или если текущее значение — 20 и вы щелкаете его вверх.

Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов) или с расширением .xls в старых версиях приложения.

Перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».

В открывшемся окне редактора VBA выберите вкладку «Insert» главного меню и нажмите кнопку «UserForm». То же подменю откроется при нажатии на вторую кнопку (после значка Excel) на панели инструментов.

На экране редактора VBA появится новая пользовательская форма с именем «UserForm1»:

Добавление элементов управления

Обычно вместе с пользовательской формой открывается панель инструментов «Toolbox», как на изображении выше, с набором элементов управления формы. Если панель инструментов «Toolbox» не отобразилась, ее можно вызвать, нажав кнопку «Toolbox» во вкладке «View»:

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

Найдите на панели инструментов «Toolbox» элемент управления с подсказкой «TextBox», кликните по нему и, затем, кликните в любом месте рабочего поля формы. Элемент управления «TextBox» (текстовое поле) будет добавлен на форму.

Найдите на панели инструментов «Toolbox» элемент управления с подсказкой «CommandButton», кликните по нему и, затем, кликните в любом месте рабочего поля формы. Элемент управления «CommandButton» (кнопка) будет добавлен на форму.

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

Нажатием клавиши «F4» вызывается окно свойств, с помощью которого можно вручную задавать значения свойств пользовательской формы и элементов управления. В окне свойств отображаются свойства выбранного элемента управления или формы, если выбрана она. Также окно свойств можно вызвать, нажав кнопку «Properties Window» во вкладке «View».

Отображение формы на экране

Чтобы запустить пользовательскую форму для просмотра из редактора VBA, необходимо выбрать ее, кликнув по заголовку или свободному от элементов управления полю, и совершить одно из трех действий:

нажать клавишу «F5»;
нажать на треугольник на панели инструментов (на изображении выше треугольник находится под вкладкой «Debug»);
нажать кнопку «Run Sub/UserForm» во вкладке «Run».

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

Чтобы использовать в Excel встроенную автоформу, необходимо добавить кнопку ее вызова на панель инструментов. Как это сделать, смотрите в статье «Умная таблица» в Excel.

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

Чтобы отобразить встроенную автоформу Excel, выберите ячейку внутри таблицы и нажмите кнопку вызова автоформы на панели инструментов.

Главный недостаток встроенной автоформы Excel заключается в невозможности использования раскрывающихся списков для заполнения ее полей. Для таблицы на изображении выше мы создадим с помощью кода VBA Excel пользовательскую автоформу с текстовыми полями и раскрывающимися списками.

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

Рабочая таблица и списки

Наименования полей и наборы данных для раскрывающихся списков при создании пользовательской автоформы будем брать с листа «Списки».

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

В коде VBA, создающем автоформу, используются имена листов: Лист1 («Таблица») и Лист2 («Списки»). Благодаря этому, можно переименовывать ярлычки листов без внесения изменений в код. Кнопка «Новая запись» добавлена на рабочий лист из коллекции «Элементы ActiveX».

Создание проекта формы

Добавьте в проект VBA пользовательскую форму UserForm1 и добавьте на нее кнопки CommandButton1 и CommandButton2:

Размеры формы и кнопок не имеют значения, мы будем задавать их программно.

Код инициализации автоформы

В процессе инициализации формы мы будем добавлять элементы управления, их расположение и размеры. Наименования полей таблицы будут записаны в параметры Caption элементов Label.

Поля таблицы для заполнения в пользовательской автоформе будут представлены элементами TextBox и ComboBox. Если в таблице на листе «Списки» под наименованием графы есть данные для раскрывающегося списка, на форму добавляется ComboBox, если нет – TextBox.

Читайте также: