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

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


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

  • В 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. Но как определить эту ячейку?

Создадим Именованную формулу СмещГода для определения позиции выбранного года в диапазоне лет 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.


Комментарии
Только для авторизованных пользователей

Аноним, 13 сентября 2020 г.
Как мне задать максимальное значение для полосы прокрутки так, что бы оно было больше 30 000?
Михаил, 13 сентября 2020 г.
Элемент управления формы "Полоса прокрутки" не поддерживает больше 30000. Это Вам известно. Попробуйте использовать ActiveX элемент Полоса прокрутки, может он поддерживает (я не проверял), но нужно писать код под него на VBA. Можно сделать 2 связанных полосы прокрутки (одна мотает страницы, другая внутри страницы). Как вариант, подумать над тем, почему разработчики EXCEL сделали ограничение 30000. Возможно они "не догоняют", а возможно намекают, что больше 30000 делать полоски "неправильно" и работать пользователю с такой полоской будет не удобно. Лично я думаю, что разработчики установили 30000 по скудоумию, нужно было сделать 3000000000.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved