Элементы управления формы в excel

Обновлено: 07.07.2024

В Excel «Контроль формы» доступен на вкладке «Разработчик» в Excel.

Как видите, у нас есть две категории: «Элементы управления формы» и «Элементы управления Active X». В этой статье мы сосредоточимся только на «элементах управления формой». На изображении ниже подробно описаны все «Элементы управления формой в Excel».

Как использовать элементы управления формы в Excel?

Теперь посмотрим, как работать с каждым из них подробно.

Элемент управления формы 1: кнопка

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

Элемент управления формой 2: поле со списком

Теперь, чтобы вставить значения, создайте список дней в столбце A.

Теперь выберите «Поле со списком», щелкните правой кнопкой мыши и выберите «Управление форматом».

Теперь в окне «Управление форматом» выберите «Управление». В поле «Диапазон ввода» выберите диапазон ячеек имен месяцев. Щелкните ОК.

Теперь мы должны увидеть список выбранных дней в поле со списком.

Элемент управления формой 3: CheckBox

CheckBox используется для отображения выбора элемента. Если он отмечен, мы можем ссылаться на определенную ячейку, чтобы отображать выбор как ИСТИНА и ЛОЖЬ, если он не отмечен.

Нарисуйте флажок на листе.

Щелкните правой кнопкой мыши и выберите параметр «Редактировать текст».

Измените имя по умолчанию с «Check Box1» на «Option».

Снова щелкните правой кнопкой мыши и выберите «Управление форматом».

На вкладке «Управление» выберите «Не отмечено» и укажите ссылку на ячейку D3. Нажмите ОК

Установите флажок, чтобы увидеть ИСТИННОЕ значение в ячейке D3.

Теперь снимите флажок, чтобы увидеть значение FALSE.

Элемент управления формы 4: кнопка вращения

Используя кнопку «Вращать», мы можем увеличивать и уменьшать значение в связанной ячейке. Нарисуйте кнопку вращения, чтобы увидеть варианты.

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

На вкладке «Контроль» мы можем сделать все настройки. Мы можем установить минимальное значение; мы можем установить максимальное значение, мы можем установить текущее значение, а также мы можем настроить, каким должно быть инкрементальное и уменьшающееся значение при нажатии кнопки вращения. Щелкните ОК.

Теперь, если вы нажмете стрелку вверх на кнопке прокрутки в ячейке A2 one, мы сможем увидеть инкрементное значение.

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

Поэтому, когда вы нажимаете стрелку вверх, она увеличивается на 1, пока не достигнет 12; после этого не увеличится.

Элемент управления формы 5: список

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

Для этого списка мы создадим список дней.

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

Теперь на вкладке «Управление» для «Диапазон ввода» выберите список дней, а для ячейки ссылка выбирает ячейку C10. Поскольку я выбрал «Один» для типа «Выбор», мы можем выбирать только один элемент за раз. Щелкните ОК.

Теперь посмотрите список дней в поле со списком.

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

Как вы можете видеть выше, мы получили 6 в качестве значения в ячейке C10; это потому, что в списке мы выбрали «Суббота», что означает 6 th элемент, поэтому результат в ячейке C10 равен 6.

Элемент управления формы 6: групповое поле

Используя Group Box, мы можем создать в нем несколько элементов управления. Мы не можем взаимодействовать с этим; скорее, это позволяет нам сгруппировать другие элементы управления под одной крышей.

Нарисуйте рамку группы на листе.

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

Вставьте переключатели, которые хотите сгруппировать.

Элемент управления формы 7: метка

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

Элемент управления формы 8: полоса прокрутки

Используя полосу прокрутки в Excel, мы можем увеличивать и уменьшать значение связанной ячейки. Это похоже на кнопку Spin, но на полосе прокрутки мы можем видеть, как прокрутка перемещается при увеличении и уменьшении.

Нарисуйте полосу прокрутки на листе.

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

На вкладке «Контроль» мы можем произвести все настройки.

Поэтому, когда вы нажимаете стрелку вверх, она увеличивается на 1, пока не достигнет 12; после этого увеличиваться не будет.

Элементы управления формы (Поле со списком, Флажок, Счетчик и др.) помогают быстро менять данные на листе в определенном диапазоне, включать и выключать опции, делать выбор и пр. В принципе, без них можно обойтись, но они делают управление данными на листе более наглядным и уменьшают вероятность ввода некорректных данных.

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

Теперь вставить элемент управления можно через меню: Разработчик/ Элементы управления/ Вставить .

Обратите внимание, что в этом меню можно вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

Для тех, кто не ранее не работал с Элементами управления формы, советуем подробно ознакомиться с ними в следующих статьях:

В этой статье рассмотрим более сложный пример совместного использования элементов управления и Условного форматирования .

Пример

Разберем конкретный пример применения сразу нескольких Элементов управления. В файле примера с помощью элементов управления показано как пользователь может отредактировать значения в таблице (диапазон F9:K12 ).

С помощью одного из 3-х элементов управления Поле со списком, Список и Счетчик , пользователь может выбрать столбец таблицы (год) . Нужный элемент управления выбирается с помощью группы Переключателей . Название выбранного элемента подсвечивается серым цветом (см. A8:B8 на рис. выше). Выбранный год выделяется в таблице Условным форматированием темно серым цветом (см. H9 :H12 на рис. выше). Отображение этого выделения регулируется Флажком (фон флажка - красный). Полосами прокрутки можно редактировать Цену и Количество в выбранном году, но только в определенном диапазоне. Теперь – подробнее.

Переключатели

На листе использовано 3 Переключателя объединенных в Группу . Каждому Переключателю соответствует определенный элемент управления: Поле со списком, Список, Счетчик .

Для объединения Элементов в группу помещаем на лист Элемент управления Группа (через меню Разработчик/ Элементы управления/ Вставить ). В рамках группы создаем 3 переключателя (также через меню Разработчик/ Элементы управления/ Вставить ) и связываем их все с одной ячейкой С2 (выделив элемент управления, правой клавишей вызываем контекстное меню, Формат объекта …, вкладка Элемент управления ).

Удерживая клавишу CTRL выделяем 3 переключателя и элемент Группа , вызываем правой клавишей мыши контекстное меню и выбираем Группировка/ Группировать . Теперь при выборе одного из трех Переключателей в Группе , в ячейке С2 будет выводиться значение 1, 2 или 3.

Поле со списком

Теперь вставим элемент управления Поле со списком . Вставить элемент можно через меню: Разработчик/ Элементы управления/ Вставить . В ячейках столбца М введем несколько значений лет: 2009, 2010, 2011, 2012, 2013. Эти значения будут использованы в элементе Поле со списком .

выделяем диапазон М9:М12;
нажимаем Формулы/ Определенные имена/ Присвоить имя ;
в поле Имя вводим Список .

Теперь свяжем элемент управления с данными на листе. Для этого:

выделите элемент управления Поле со списком ;
правой клавишей вызовите его контекстное меню, затем Формат объекта… , вкладка Элемент управления ;
в поле Формировать список по диапазону вводим Список (вместо ссылки на ячейку мы ввели ссылку на определенное Имя !). Конечно, вместо имени можно было указать просто ссылку на диапазон;
свяжем элемент с ячейкой $C$8 . В этой ячейке будет выводится порядковый номер выбранного элемента списка, т.е. если выберем 2009 , то выведется 1, т.к. это первый элемент в списке. Для дальнейших целей нам проще использовать именно год, а не его порядковый номер. Для этого в ячейку D8 введем формулу =C8+2008 .

Список

Вставляем на лист элемент Список . Аналогично предыдущему элементу связываем его с ячейкой $C$13 и формируем список на основе того же Именованного диапазона Список . В ячейку D13 введем формулу =C13+2008 .

Счётчик

Вставляем на лист элемент Счетчик . Определяем минимальное значение 2009 , максимальное – 2013 , шаг 1 . Связываем элемент с ячейкой $C$17 . В D17 введем формулу =С17 , т.к. элемент Счетчик в нашем случае возвращает значение года.

Чтобы определить значение какого элемента ( поле со списком, список или счетчик ) является активным в настоящий момент, в ячейке E9 введем формулу: =ЕСЛИ(C2=1;D8;ЕСЛИ(C2=2;D13;D17)) Как мы помним, значение в ячейке С2 определяется Группой переключателей .

Полоса прокрутки

Вставляем на лист элемент Полоса прокрутки . Этим элементом мы будем изменять ячейку на пересечении строки Количество (строка 10) и столбца выбранного года. Значения ячейки будет меняться в диапазоне от 0 до 1000. Но как определить эту ячейку?

Создадим Именованную формулу СмещГода для определения позиции выбранного года в диапазоне лет G 9: K 9 . Нажимаем Формулы/ Определенные имена/ Присвоить имя , в поле Имя вводим СмещГода , в поле диапазон вводим формулу =ПОИСКПОЗ($E$9; $G$9:$K$9;0) Если выбран 2009 , то формула вернет 1.

Для определения ячейки строки Количество , соответствующую выбранному году используем формулу =СМЕЩ($F$10;0;СмещГода) . Формула вернет диапазон, состоящий из одной ячейки.

В поле Связь с ячейкой элемента Полоса прокрутки нельзя ввести формулу, но можно, как мы уже видели, ввести Имя . Создадим Именованную формулу Количество , в поле Диапазон укажем формулу =СМЕЩ($F$10;0;СмещГода) . Теперь в поле Связь с ячейкой элемента полоса прокрутки введите Количество .

Аналогичные манипуляции проделайте с полосой прокрутки для Цены . Для этого необходимо создать Именованную формулу Цена , где в поле Диапазон указать формулу =СМЕЩ($F$11;0;СмещГода) .

Флажок

При выборе пользователем текущего года, в таблице с данными ( G9:K12 ) соответствующий столбец будет закрашиваться серым фоном. Для выделения столбца выбранного года используем Условное форматирование .

Сначала вставим на лист элемент Флажок . Этим элементом мы будем включать и выключать выделение в таблице столбца выбранного года. Элемент свяжите с ячейкой $G$2 . Если флажок снят, то в этой ячейке будет ЛОЖЬ (этому значению соответствует 0), если установлен, то ИСТИНА (этому значению соответствует 1).

Для настройки Условного форматирования выделим диапазон G9:K12 . Так как формула в Условном форматировании будет содержать относительную ссылку , то убедимся, что после выделения диапазона активной ячейкой является G9 (т.е. диапазон надо выделять начиная именно с нее. Подсказкой служит поле Имя , находящееся слева от Строки формул . После выделения диапазона оно должно содержать G 9 ).

вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило );
выберите Использовать формулу для определения форматируемых ячеек;
в поле « Форматировать значения, для которых следующая формула является истинной » введите =И(СТОЛБЕЦ(G9)=СмещГода+6;$G$2) Формула примет значение ИСТИНА, когда выполнится одновременно 2 условия:
значение выражения ( СмещГода (изменяется от 1 до 5 (т.е. от 2009 до 2013 года) + 6) совпадет с номером текущего столбца (7, т.е. 2009 год);
Флажок Условное форматирование установлен.
выберите требуемый формат, например, серый цвет заливки;
нажмите ОК.

Тестируем

убедимся, что флажок Условное форматирование установлен;
выберем переключатель Список ;
в элементе управления Список выберем 2010;
убедимся, что столбец 2010 выделен серым;
Полосой прокрутки изменим количество в столбце 2010.

Результат показан на рисунке.

К сожалению, у элементов управления формы Флажок, Поле со списком и Список нет возможности отформатировать отображаемый шрифт. Зато это можно сделать у элементов ActiveX ( Разработчик/ Элементы управления/ Вставить ). Правда, для работы с этими элементами требуется писать программу на VBA.

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 и вы щелкаете его вверх.

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

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

Если мы сначала скопировали несколько ячеек Excel, а потом нам необходимо их вставить в другой диапазон с использованием определенных параметров, мы после выполнения команды «Копировать» выполним команду «Специальная вставка. ». И вот какие варианты действий нам предлагают при выполнении этой команды специальной вставки:

Что мы здесь видим? сам прямоугольник, содержащий перечень возможных действий – это и есть экранная форма. А все то, что внутри этой формы, это и есть элементы управления. Информация «Специальная вставка» в самом верху этой формы – это есть заголовок этой формы; команды «пропускать пустые ячейки» и транспортировать» - это элементы управления, которые называются «Флажки»; «Вставить связь», «ОК» и «Отмена» - это командные кнопки. «Вставить» и «Операция» - это элементы управления Надпись, а все остальные элементы управления на данной экранной форме – это переключатели. Главное отличие флажка от переключателя в том, что флажков можно выделить несколько или не выделять ни одного вообще, а переключатель должен быть выбран только один в пределах каждой из групп.

Одна из самых интересных особенностей Excel – в том, что любой пользователь может сам создавать и экранные формы, и элементы управления. Делается это просто, в редакторе Visual Basic. Достаточно выполнить команду: Insert→UserForm. В переводе это и будет означать: Вставка → Пользовательская форма.

Вот что получится:

Что мы здесь видим? Во-первых, недавно созданная пользовательская форма, ее заголовок UserForm1 (в редакторе Visual Basic все экранные формы покрыты мелкими точками, эти точки – что-то типа ориентира, чтобы было удобнее добавлять в форму элементы управления).

Во-вторых, под этой пользовательской формой – Toolbox, или панель инструментов, или те элементы управления, которые можно вставить в нашу экранную форму. Мы видим, что в форму можно ввести не менее 15-ти различных элементов управления. Левая верхняя стрелочка – левее буквы А – предназначена для управления элементами управления, например для добавления таких элементов управления, которых нет «по умолчанию». Кстати, все элементы управления, «по умолчанию» включенные в список, и изображены на нашей панели управления. При желании можно что-то добавить или что-то убрать.

Приведем один пример – создадим небольшую экранную форму:

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

Выключатель отличается от командной кнопки. Выключатель может принимать два положения – нажатое и отжатое. Под выключателем – простая командная кнопка. Обычные простые кнопки – это кнопки типа «ОК» или «Отмена».

Небольшой комментарий к нашей форме. Обычно многостраничные формы – это те, при которых каждая страница имеет большой объем, ее формат почти совпадает с форматом всей экранной формы. Но, как мы видим, в каждом правиле есть исключения. Можно внедрить многостраничные элементы управления как небольшие части экранной формы.

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

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