0

Инструмент форма в excel

Использование панели инструментов Формы при создании макросов

Панель инструментов Формы содержит в своём составе элементы управления, к которым можно «привязаться» с помощью макрорекордера . Для открытия указанной панели инструментов в Excel 2003, следует в строке Меню→Вид→Панели инструментов→Формы, внешний вид которой показан на рис 40.

Рис. 40. Панель инструментов Формы

Тот, кто использует Excel 2007, панель инструментов Формы может найти через открытие вкладки Разработчик → В ставить (режим конструктора не активизировать), как показано на рис 41.

Рис. 41. Элементы управления формы

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

  1. Откроем файл с исходными данными под именем Forma 1. xls .
  2. Создадим новую книгу в Excel , сохраним её под именем Form 6. xls .
  3. Откроем панель инструментов Формы для файла Form 6. xls .
  4. Поместим на листе с наименованием «Просмотр списка» элемент управления – список.
  5. Запустим команду для записи макроса, в окне Запись макроса присвоим имя макросу – Получить_список .
  6. Сделаем щелчок правой клавишей мыши по элементу Список, в открывшемся окне выберем строку с наименованием «Формат объекта».
  7. В открывшемся окне, которое показано на рис 42, под названием Форматирование объекта на вкладке Элемент управления необходимо щёлкнуть по значку – Формировать список по диапазону, а затем в книге под именем Form 1 выделить столбец с фамилиями.
  8. Закрыть окно Форматирование объекта кнопкой ОК.

После выполнения пункта 8 список будет заполнен фамилиями, которые получены с листа под наименованием «Кадры» из книги – Form 1.

Рис. 42. Окно для форматирования объекта – Список

Закройте эту форму, сохраните книгу Form 6 и тоже закройте. Можете убедиться, что макрос заполнения списка создан, программа которого представлена на рис 43.

Рис. 43. Программа макроса для заполнения объекта Список данными

Теперь попробуйте открыть файл Form 6. xls , к сожалению, в открытой книге будет только элемент управления Список совершенно чистый, хотя, макрос был создан. Причина такого результата кроется в том, что источника информации нет (книга со списком сотрудников закрыта). Создадим ещё один макрос, обращение к которому, позволяет открыть книгу с именем Form 1. В книге Form 6 дадим команду для макроса – Начать запись, откроем файл Form 1. xls , дадим команду – Остановить запись. Таким образом, создан второй модуль, программа которого показана на рис 44.

Рис. 44. Программный код макроса для открытия файла Form 1. xls

Таким образом, создано два модуля с программами, которые необходимо объединить. Это делается с помощью командной кнопки, которую разместим на листе книги Form 6. Используем панель элементов Формы, на которой выбираем элемент – кнопка, располагаем его на листе.

Для такого элемента система открывает диалоговое окно – Назначить макрос объекту, которое представлено на рис 45. В том случае, если макрос ещё не готов, то нажмите на кнопку «Отмена» в окне «Назначить макрос объекту». Когда понадобится выполнить операцию привязки макроса к кнопке, тогда щёлкните правой клавишей мыши по объекту, а затем в открывшемся списке выберите необходимую строку с наименованием команды, как это показано на рис 46.

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

Рис. 45. Диалоговое окно назначения макроса объекту кнопка

Рис. 46. Пример использования всплывающего меню для работы с объектом

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Описание

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

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

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

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

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

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

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

В левой области выберите пункт настроить ленту .

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

Читайте также:  Греется чип на материнской плате

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

Нажмите кнопку Microsoft Office, а затем — Параметры Excel.

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

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

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

H1: роликовый Скатес

H6: Вашинг Machine

H7: Роккет Launcher

H11: — это очень привлекательные

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

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

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

= ИНДЕКС (H1: H20; G1; 0)

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

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


Чтобы добавить список в Excel 2003 и более ранних версиях Excel, нажмите кнопку "список" на панели инструментов "формы". Если панель инструментов формы не отображается, в меню Вид выберите пункт панели инструментов, а затем — пункт формы.

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

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

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

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

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

Примечание: Формула INDEX () использует значение в ячейке G1, чтобы вернуть правильный элемент списка.

В разделе тип выделенияубедитесь, что выбран параметр один вариант.

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

Список элементов должен отображаться в списке. Чтобы использовать список, щелкните любую ячейку, чтобы не выделять список. Если щелкнуть элемент в списке, ячейка G1 будет обновлена числом, указывающим позицию выбранного в списке элемента. Формула INDEX в ячейке a1 использует этот номер для отображения имени элемента.

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

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


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

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

Щелкните поле со списком правой кнопкой мыши и выберите пункт Формат элемента управления.

Введите указанные ниже данные и нажмите кнопку ОК.

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

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

Примечание: Формула INDEX использует значение в ячейке G1, чтобы вернуть правильный элемент списка.

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

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

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

Пример кнопки "Счетчик"

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


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

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

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

Введите указанные ниже данные и нажмите кнопку ОК.

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

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

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

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

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

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

В поле Шаг изменения введите 1.

Это значение определяет степень приращения текущего значения с помощью элемента управления "Счетчик".

Читайте также:  В какой программе можно редактировать фото

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

Щелкните любую ячейку, чтобы кнопка счетчика не выделена. Если щелкнуть элемент управления вверх или вниз на кнопке счетчик, ячейка G1 будет обновлена числом, которое указывает текущее значение счетчика, а также инкрементное изменение счетчика. Затем этот номер обновляет формулу INDEX в ячейке a1 для отображения следующего или предыдущего элемента.

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

Пример полосы прокрутки

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


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

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

Щелкните полосу прокрутки правой кнопкой мыши и выберите пункт Формат элемента управления.

Введите указанные ниже данные и нажмите кнопку ОК.

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

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

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

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

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

В поле Шаг изменения введите 1.

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

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

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

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

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

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

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

  • В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
  • В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл; Нажмите кнопку Параметры; Нажмите кнопку Настроить ленту; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик.

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

Обратите внимание, что в этом меню можно вставить Элементы 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. Но как определить эту ячейку?

Создадим Именованную формулу СмещГода для определения позиции выбранного года в диапазоне лет G9:K9. Нажимаем Формулы/ Определенные имена/ Присвоить имя, в поле Имя вводим СмещГода, в поле диапазон вводим формулу =ПОИСКПОЗ($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 (т.е. диапазон надо выделять начиная именно с нее. Подсказкой служит поле Имя, находящееся слева от Строки формул. После выделения диапазона оно должно содержать G9).

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

Тестируем

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

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

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

admin

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *