В этой статье рассмотрим элементы управления формы которые есть в EXCEL. Элементы управления формы помогают быстро менять данные на листе. Можно обойтись и без них, но они делают управление данными на листе более наглядными и уменьшают вероятность ввода некорректных данных.
Статья будет разделена на две части.
Для вставки элементов управления на лист необходимо включить вкладку "Разработчик".
В EXCEL 2007 это можно сделать через меню "Кнопка офис" -> "Параметры Excel" -> "Основные" -> "Показывать вкладку Разработчик на ленте".
В EXCEL 2010 это можно сделать так: "Файл" -> "Параметры" -> "Настроить ленту" -> "Настройка ленты" и в разделе "Основные вкладки" установить флажок "Разработчик".
Теперь вставить элемент управления можно через меню: "Разработчик" -> "Элементы управления" -> "Вставить".
Поле со списком (простой)
Поле со списком состоит из текстового поля и списка, которые вместе образуют раскрывающийся список.
Вставка и настройка
- На вкладке "Разработчик" нажмите кнопку "Вставить".
- Выберите тип элемента "Поле со списком".
- Щелкните на листе в место, где должен располагаться левый верхний угол элемента, а затем переместите курсор в то место, где должен находиться правый нижний угол элемента.
- Щелкните по элементу "Поле со списком" правой кнопкой мыши и выберите пункт "Формат объекта".
- Перейти во вкладку "Элемент управления" и настройте следующие параметры.
- Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
- Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
- Количество строк списка: введите размер раскрытого списка.
Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой C1, а диапазон ячеек для списка — A2:A6. Если в ячейку D1 ввести формулу =ИНДЕКС(A2:A6;C1), то при выборе третьего пункта в ячейке D1 появится текст "Item 3".
Поле со списком (ActiveX)
Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX "Поле со списком".
Вставка и настройка
- На вкладке "Разработчик" нажмите кнопку "Вставить".
- Выберите тип элемента "Поле со списком (ActiveX)".
- Щелкните на листе в место, где должен располагаться левый верхний угол элемента, а затем переместите курсор в то место, где должен находиться правый нижний угол элемента.
- Щелкните по элементу "Поле со списком (ActiveX)" правой кнопкой мыши и выберите пункт "Свойства".
- Откройте вкладку "Alphabetic" (По алфавиту) и измените нужные свойства.
- LinkedCell: поле со списком можно связать с ячейкой, где отображается элемент при его выборе из списка. Введите номер ячейки, где должен отображаться элемент.
- ListFillRange: введите диапазон ячеек, содержащий список элементов.
- ListRows: введите размер раскрытого списка.
6. Отключите "Режим конструктора".
Описание всех свойств можно найти тут.
Сравнение
Поле со списком (простой):
- Возможность настройки шрифта, цвета и т.д. - НЕТ
- Быстрый поиск элемента по первым буквам - НЕТ
- Необходимость использования дополнительной функции - ДА
- Возможность создания связанных выпадающих списков - ДА
Поле со списком (ActiveX):
- Возможность настройки шрифта, цвета и т.д. - ДА
- Быстрый поиск элемента по первым буквам - ДА
- Необходимость использования дополнительной функции - НЕТ
- Возможность создания связанных выпадающих списков - ДА
Советы
- Если необходимо использовать элемент управления "Поле со списком" для управления датами, то лучше воспользоваться сводной таблицей и её срезами (фильтры) или использовать простое "Поле со списком", т.к. "Поле со списком (ActiveX)" преобразует все входящие данные в текст из-за чего становиться трудно получить на выходе требуемое значение в правильном формате данных.
Список (простой)
Элемент управления формы "Список" выводит список нескольких элементов, которые может выбрать пользователь. Данный элемент управления имеет много общего с "Поле со списком".
Вставка и настройка
- На вкладке "Разработчик" нажмите кнопку "Вставить".
- Выберите тип элемента "Список".
- Щелкните на листе в место, где должен располагаться левый верхний угол элемента, а затем переместите курсор в то место, где должен находиться правый нижний элемента.
- Щелкните по элементу "Список" правой кнопкой мыши и выберите пункт "Формат объекта".
- Перейти во вкладку "Элемент управления" и настройте следующие параметры.
- Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
- Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
- Возможен выбор: убедитесь, что установлен выбор "одного значения", т.к. только в этом случае "Список" будет возвращать значение в связанную ячейку (множественный выбор значений требует использования VBA).
Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере список связан с ячейкой C1, а диапазон ячеек для списка — A2:A6. Если в ячейку D1 ввести формулу =ИНДЕКС(A2:A6;C1), то при выборе третьего пункта в ячейке D1 появится текст "Item 3".
Список (ActiveX)
Существенных отличий у данного списка, как и у поля со списком (ActiveX), от простого списка нет.
Вставка и настройка
- На вкладке "Разработчик" нажмите кнопку "Вставить".
- Выберите тип элемента "Список (ActiveX)".
- Щелкните на листе в место, где должен располагаться левый верхний угол элемента, а затем переместите курсор в то место, где должен находиться правый нижний угол элемента.
- Щелкните по элементу "Список (ActiveX)" правой кнопкой мыши и выберите пункт "Свойства".
- Откройте вкладку "Alphabetic" (По алфавиту) и измените нужные свойства.
- LinkedCell: поле со списком можно связать с ячейкой, где отображается элемент при его выборе из списка. Введите номер ячейки, где должен отображаться элемент.
- ListFillRange: введите диапазон ячеек, содержащий список элементов.
6. Отключите "Режим конструктора".
Описание всех свойств можно найти тут.
Сравнение
Список (простой):
- Возможность настройки шрифта, цвета и т.д. - НЕТ
- Быстрый поиск элемента по первым буквам - НЕТ
- Необходимость использования дополнительной функции - ДА
- Возможность создания связанных списков - ДА
Список (ActiveX):
- Возможность настройки шрифта, цвета и т.д. - ДА
- Быстрый поиск элемента по первым буквам - ДА
- Необходимость использования дополнительной функции - НЕТ
- Возможность создания связанных списков - ДА
Флажок (простой)
Флажок (CheckBox) - это элемент управления, предназначенный для передачи информации о выборе или невыборе его пользователем и возвращающий одно из двух значений: ЛОЖЬ (галочки нет) или ИСТИНА (галочка установлена).
Вставка и настройка
- На вкладке "Разработчик" нажмите кнопку "Вставить".
- Выберите тип элемента "Флажок".
- Щелкните на листе в место, где должен располагаться левый верхний угол элемента, а затем переместите курсор в то место, где должен находиться правый нижний угол элемента.
- Щелкните по элементу "Флажок" правой кнопкой мыши и выберите пункт "Формат объекта".
- Перейти во вкладку "Элемент управления" и настройте следующие параметры.
- Значение: установлен - отображает выбранный переключатель, снят - отображает невыбранный переключатель.
- Связь с ячейкой: флажок можно связать с ячейкой, где отображается значение "ИСТИНА"/"ЛОЖЬ" при установке/снятии галочки. Введите номер ячейки, где должно отображаться значение элемента.
Переименование флажка
- Нажать правой кнопкой мыши на нужном флажке.
- Дважды нажать на текст для появления курсора.
- Переименовать.
Формат вывода данных является логическим, следовательно сравнивая значения ячейки с необходимым значением использование кавычек "" не требуется.
Флажок (ActiveX)
Флажок (ActiveX) имеет более широкий набор настроек в отличие от его простой версии.
Вставка и настройка
- На вкладке "Разработчик" нажмите кнопку "Вставить".
- Выберите тип элемента "Флажок (ActiveX)".
- Щелкните на листе в место, где должен располагаться левый верхний угол элемента, а затем переместите курсор в то место, где должен находиться правый нижний угол элемента.
- Щелкните по элементу "Флажок" правой кнопкой мыши и выберите пункт "Свойства".
- Откройте вкладку "Alphabetic" (По алфавиту) и измените нужные свойства.
- Caption: подпись флажка.
- LinkedCell: флажок можно связать с ячейкой, где отображается значение "ИСТИНА"/"ЛОЖЬ" при установке/снятии галочки. Введите номер ячейки, где должно отображаться значение элемента.
Описание всех свойств можно найти тут.
Формат вывода данных является логическим, следовательно сравнивая значения ячейки с необходимым значением использование кавычек "" не требуется.
Сравнение
Флажок (простой):
- Возможность настройки шрифта, цвета и т.д. - НЕТ
- Необходимость использования дополнительной функции - НЕТ
Флажок (ActiveX):
- Возможность настройки шрифта, цвета и т.д. - ДА
- Необходимость использования дополнительной функции - НЕТ
Во второй части статьи рассмотрим: Переключатель, Группа, Счетчик и Полоса прокрутки.