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

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

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

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

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

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

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

Флаг ;
Счетчик ;
Полоса прокрутки ;
Переключатель ;
Перечень ;
Поле со перечнем .

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

Пример

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

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

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

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

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

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

Поле со перечнем

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

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

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

выделите отран управления Поле со перечнем ;
правой кнопкой вызовите его контекстное меню, потом Формат объекта… , вкладка Отран управления ;
в поле Сформировывать перечень по спектру вводим Перечень (заместо ссылки на ячейку мы ввели ссылку на определенное Имя !). Естественно, заместо имени можно было указать просто ссылку на спектр;
свяжем элемент с ячейкой $C$8 . В данной ячейке будет выводится порядковый номер избранного элемента перечня, т.е. если выберем 2009 , то выведется 1, т.к. это 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;СмещГода) .

Интересно почитать: Как в excel сделать таблицу с формулами

Флаг

При выбирании юзером текущего года, в таблице с данными ( 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.

Ввод данных в Excel через форму

Огромное количество различных компьютерных программ, включая «самую главную программку в мире» — MS Windows, ведут общение с юзером с помощью выпадающих диалоговых окон. Эти окна представляют собой формы, состоящие из надписей, изображений, полей для.

. ввода данных, флагов, переключателей, списков, клавиш и иных частей управления.

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

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

1. Делают таблицы базы данных.

2. Делают формы для ввода данных в таблицы.

3. Делают нужные запросы к таблицам базы данных.

4. Сформировывают отчеты на основании запросов для вывода на печать.

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

В данной (пятой в цикле) статье разглядим п.2 вышеизложенного метода – вызов и внедрение формы для ввода данных.

Форма Excel. Вводим данные!

Для чего же нужна какая-то форма?! Бери таблицу — и пиши прямо в нее еще одну строчку инфы! Для чего еще что-то сочинять?

Мы так и делали при разработке нашего маленького и обычного примера базы данных БД2 «Выпуск металлоконструкций участком №2», с которым работаем во всех статьях этого цикла.

Таблица малая, все столбцы-поля, относящиеся к одной записи, помещаются на экран и доступны взору юзера без использования полос прокрутки. Но так бывает изредка! Настоящие таблицы баз данных нередко содержат не 6, как в нашем примере, а 10, 20 и наиболее столбцов-полей!

При вводе данных в такие таблицы Excel «впрямую» при заполнении строк для перемещения по столбцам нужно всякий раз жать кнопку «Tab», а для перехода к новейшей записи на новейшую строчку воспользоваться прокруткой и мышью либо кнопками «Enter» и «<—» («стрелка на лево») для возврата на 1-ое поле очередной строчки.

Справедливости ради следует уточнить, что переходы по таблице, являющейся именованным спектром и объявленной перечнем, как в нашем примере, осуществляются лишь с помощью клавиши «Tab». Дойдя до крайнего столбца у правой границы перечня и нажав в очередной раз кнопку «Tab», мы перейдем на последующую строчку и 1-ый столбец у левой границы перечня.

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

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

Задачка №8:

Ввести еще одну строчку инфы в базу данных через форму.

Разглядим ввод данных в Excel с помощью формы на примере прибавления очередной записи в базу данных БД2 «Выпуск металлоконструкций участком №2».

1. Открываем в MS Excel файл database.xls.

2. Активируем («щелкаем мышкой») всякую ячейку снутри таблицы базы.

3. Исполняем команду головного меню программки «Данные» — «Форма…».

4. В показавшемся окне, представляющем из себя форму с именованием «БД2» (по имени листа книжки Excel, на котором размещена таблица), жмем клавишу «Добавить».

5. Заполняем окна данными новейшей записи, к примеру, так, как показано ниже на скриншоте. Перебегаем от окна к окну с помощью клавиатуры, нажимая кнопку «Tab».

Интересно почитать: Формула в excel чиствндох

6. Жмем клавишу формы «Закрыть» либо «Enter» на клавиатуре. Ввод данных в Excel удачно осуществлен — новенькая запись добавлена в базу!

На скриншоте в таблице базы данных понизу расположилась строчка «Результат», которую можно вызвать, выполнив команду головного меню «Данные» — «Перечень» — «Строчка итогов». Пощелкайте мышкой по ячейкам данной строчки! В появляющихся выпадающих перечнях можно настроить получение самых различных итогов. Не считая очевидной суммы можно получить для всякого столбца персонально: среднее, количество, количество чисел, максимум, минимум, смещенное отклонение, смещенную дисперсию! Весьма нужная функция перечня!

Если в форме «БД2» пользоваться полосой прокрутки либо клавишами «Дальше» и «Вспять», то можно, стремительно перемещаясь по базе данных, просматривать интересующие записи.

Итоги.

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

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

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

Прошу уважающих труд создателя подписаться на новости статей в окне, расположенном в конце каждой статьи либо в окне вверху странички!

Почетаемые читатели, пишите отзывы, вопросцы и замечания в комментах понизу странички.

Макросы Excel — пользовательские формы

Время от времени для вас, может быть, придется собирать информацию повторно от остальных. Excel VBA предоставляет для вас обычный метод решения данной задачки — UserForm . Как и неважно какая иная форма, которую вы заполняете, UserForm упрощает осознание того, какую информацию следует предоставлять. Пользовательская форма комфортна для юзера, потому что предоставляемые элементы управления не требуют пояснений и сопровождаются доп инструкциями, если это нужно.

Главным преимуществом UserForm будет то, что вы сможете сберечь время, которое вы тратите на то, как и как информация обязана быть заполнена.

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

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

Нажмите вкладку РАЗРАБОТЧИК на ленте.
Нажмите Visual Basic. Раскроется окно Visual Basic для книжки.
Нажмите Вставить,
Изберите UserForm из выпадающего перечня.

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

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

Разверните окно UserForm.xlsx — UserForm1.

Вы находитесь в режиме дизайна на данный момент. Вы сможете вставить элементы управления в пользовательскую форму и написать код для соответственных действий. Элементы управления доступны в панели инструментов. Характеристики UserForm находятся в окне параметров. UserForm1 (подпись UserForm) указывается в разделе «Формы» в проводнике проектов.

Измените заголовок UserForm на Project Report — Daily в окне параметров.
Измените имя пользовательской формы на ProjectReport.

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

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

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

Excel предоставляет два типа частей управления — элементы управления формы и элементы управления ActiveX. Вы должны осознавать разницу меж этими 2-мя типами частей управления.

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

Элементы управления формой — это уникальные элементы управления Excel, которые совместимы с наиболее ранешними версиями Excel, начиная с Excel версии 5.0. Элементы управления формой также предусмотрены для использования на листах макроса XLM.

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

Элементы управления ActiveX

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

У вас есть последующие элементы управления ActiveX в UserForm ToolBox —

Указатель
этикетка
Текстовое окно
Поле со перечнем
ListBox
CheckBox
OptionButton
Рамка
Клавиша-переключатель
CommandButton
TabStrip
MultiPage
Полоса прокрутки
в полях ввода
Образ

В дополнение к сиим элементам управления Visual Basic предоставляет функцию MsgBox, которую можно употреблять для отображения сообщений и / либо запроса юзера о действии.

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

этикетка

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

Текстовое окно

Вы сможете употреблять TextBox в виде прямоугольника для ввода, просмотра либо редактирования текста. Вы также сможете употреблять TextBox в качестве статического текстового поля, которое представляет информацию лишь для чтения.

Перечень

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

Вставьте ListBox в пользовательскую форму.
Нажмите на перечень.
Введите ProjectCodes для Name в окне параметров ListBox.

Интересно почитать: Формула пстр в excel примеры

Есть три типа списков —

Поле со перечнем с одним выбором — Поле со перечнем с одним выбором включает лишь один выбор. В этом случае поле перечня припоминает группу клавиш выбора, кроме того, что поле перечня может наиболее отлично обрабатывать огромное количество частей.

Перечень выбора множественного выбора — Перечень перечня множественного выбора включает или один выбор, или смежные (смежные) варианты.

Поле со перечнем расширенного выбора — Поле со перечнем расширенного выбора дозволяет избрать один, непрерывный и несмежный (либо несвязанный) выбор.

Вы сможете избрать один из этих типов списков в окне параметров.

Щелкните правой клавишей мыши на пользовательской форме.
Изберите View Code из выпадающего перечня. Раскроется окно кода UserForm.
Нажмите Инициализировать в правом верхнем углу окна кода.
Введите последующее в Private Sub UserForm_Initialize ().

Перейдите на вкладку «Выполнить» на ленте.
Изберите Run Sub / UserForm из выпадающего перечня.

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

Поле со перечнем

Вы сможете употреблять ComboBox, который соединяет воединыжды текстовое поле со перечнем для сотворения выпадающего перечня. Поле со перечнем является наиболее малогабаритным, чем поле со перечнем, но просит, чтоб юзер щелкнул стрелку вниз, чтоб показать перечень частей. Используйте поле со перечнем, чтоб избрать лишь один элемент из перечня.

Создание формы наполнения документов в Excel

Нам будет нужно обыденный файл Excel. Вы сможете взять за базу имеющийся либо начать с незапятнанного листа.

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

Оформление быть может хоть каким, на ваш вкус и цвет. При формировании формы я употреблял стили, расположенные на вкладке Основная. Это стремительно и комфортно, также дозволяет в один клик поменять цветовую палитру на вкладке меню "Разметка странички" — "Тема"

Автоматизация формы

Часть данных (номер контракта, сумма, срок), нужно заполнять постоянно здесь ничего не сделаешь. Но как мы лицезреем есть часть данных, которые отлично бы заполнить автоматом. Это — сумма прописью, срок прописью, данные заказчика в родительном падеже и так дальше. Давайте их автоматизируем.

Дата контракта

В Excel существует огромное количество функций по работе с датами. Если дата контракта постоянно текущая, то можно употреблять функцию =СЕГОДНЯ(). По мере необходимости можно настроить, чтоб дата рассчитывалась как последующий рабочий денек =РАБДЕНЬ(СЕГОДНЯ();1).

Для вставки даты вы сможете употреблять календарь из программки:

Сумма контракта

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

Срок прописью

Срок прописью аналогично, охото вводить лишь числа, чтоб в документ вставлялось прописью вполне. К слову, это касается не только лишь срока, это быть может количество чего же угодно: услуги, штуки, килограммы, яблоки, помидоры и так дальше. В программке Doc.filler все это можно заавтоматизировать. Откройте функции и изберите Вещи прописью.

В диалоговом окне сформируйте подходящую для вас формулу.

Наполнение заказчика

Данные заказчика совершенно подгружать из внутренней системы заказчика 1С либо иной CRM. Если таковой способности нет, то для начала можно упростить жизнь методом сотворения справочника правовых форм (ООО, ОАО (форма организации публичной компании; акционерное общество) и т.д.) Тогда нам будет нужно просто избрать из перечня подходящую форму и указать наименование. Остальное мы создадим на формулах.

Итак сделаем отдельный лист и разместим там таблицу со перечнем правовых форм.

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

Сейчас на форме сделаем оживленный выпадающий перечень с данными правовых форм.

Осталось сделать формулы для наполнения заказчика сокращенно в моем случае это формула = Правовая_форма &" "& Заказчик_наименование . Поменяйте на надлежащие ячейки.

Формула для полного наименования заказчика последующая =ВПР( Правовая_форма ; ПравовыеФормы ;2;0)&" "& Заказчик_наименование . ПравовыеФормы — заглавие таблицы со справочником форм.

Итог смотрится последующим образом:

Склонение ФИО подписанта

На данный момент поглядим как автоматом склонять ФИО подписанта. Для этого в программке есть соответственная функция, которую можно вставить из меню:

Программка умеет склонять 90% российских ФИО по всем падежам.

Склонение должности и остальных фраз

Аналогичным образом склоняются должности и остальные недлинные фразы контракта, к примеру Утомившись. Вставьте функцию Склонение фраз через ленту меню: