Как я делаю шаблоны⁠⁠

Всем добра. Продолжаем совершенствоваться в овладении великим и могучим Excel.
Сегодня я решил показать один из способов, как в excel`е можно сделать заполняемые формы. Тут есть что то из старого материала (смотри предыдущие уроки), ну и кое-что новенькое покажу. Итак, допустим у нас есть какая либо форма, которую периодически нужно заполнять (на пример какие ни будь заявления, приказы, объяснения и т.д.). Данные в форме немного варьируются, некоторые слова немного меняются, но основной текст остается прежней, некоторые слова (имена, фамилии) нужно выбирать из списка, какие то значения вбивать и т.д. В таком случае можно просто брать шаблон и вбивать туда данные, но при частом использовании взгляд «замыливается» и возможны допущения ошибок. Так что сделаем такой шаблон, который можно было бы «настраивать». Мой пример будет иметь малое практическое применение, я просто в нем хочу показать варианты использования элементов управления формами. Для начала давайте их найдем. Находятся они на вкладке «Разработчик». Если ее не видно идем в параметры и включаем ее во вкладке «Настройка ленты». Жмакаем вставить и смотрим что нам тут есть полезного (функционал я расскажу сразу в примере):

-кнопка (ее использовать не будем, она в основном нужна для запуска макросов, в макросы я пока не лезу)
- поле со списком (компактный выпадающий вариант списка)
-флажок (вкл или выкл)
-счетчик(стрелочки для «накрутки» какого либо показателя)
-список (выбор из нескольких элементов без выпадения списка)
-переключатель (кружечек, который имеет только одно активное положение)
-полоса прокрутки (как счетчик только еще с бегунком)
Итак, все эти элементы можно привязать к какой ни будь ячейке, в которую будет выводиться результат наших манипуляций, а поля из списка нужно подтягивать из нескольких ячеек.
Вытащим какой любой элемент, и посмотрим на формат объекта (правый клик по элементу), нам интересен раздел «Элемент управления». Для списка и поля со списком есть такие свойства:
-Форматировать список по диапазону (здесь мы указываем список из возможных значений для выбора диапазона)
-связать с ячейкой (выбираем ячейку в которую выводится результат)
также в выпадающем списке есть: -Количество строк списка (сколько всего будет строк в выпадающем списке, если вбить меньше количества элементов списка, появятся стрелочки для перемотки)
а в обычном списке есть выбор одного значения, либо набора, либо списка нескольких значений (к своему большому стыду я так и не разобрался зачем нужны последние 2 пункта, так как при их выборе изменения в списке никак не влияет на изменений в связанной ячейке)
Для счетчика и полоски прокрутки есть свойства мин, макс значения, шаг изменения и связь с ячейкой, тут я думаю все наглядно.
Для флажка и переключателя интересны только связи с ячейкой, причем я не смог добиться от Excel того чтобы на одном листе можно было сделать несколько списков переключателей, если один привязываешь к ячейке все другие переключатели на листе сами туда привязываются. При изменении состояния флажка в ячейку передается номер ячейки, в той последовательности, что вы их добавляли на лист
Итак, для начала накидаем шаблон.

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

Теперь будем «связывать». Я обычно делаю все на 1 «техническом» листе, потом «технические» ячейки скрываю. но можно их подтянуть на отдельный лист и скрыть весь лист.
небольшое удобство: если выбрать элемент управления правой кнопкой он выделяется, и его можно на пример передвигать, а в строке ввода формул появляется связанная ячейка. ее это та же ячейка что и в Формат объекта - > элемент управления ->Связь с ячейкой.
Флажок уважаемости связываем с ячейкой G4, переключатели адресата связываем с ячейкой G4, список месяца с G7, список числа с G6, полосу прокрутки времени опоздания с G8, список причины с G9.
Получаем такую табличку:

в столбце H стоят вот такие формулы (есть волшебная комбинация клавиш Ctrl+ё, которая на листе показывает вместо значений формулы в ячейках):

которые подставляют данные посредством функций ВПР и ИНДЕКС из табличек:

здесь мы формируем список для дней недели, формулы в ячейках О31-О33 проверяют выбранный месяц и в зависимости от того сколько в нем дней из столбца М прописывают нужное количество. (кстати если лень лесть в календарь и смотреть нужное количество дней в столбец М я прописал формулу =ДЕНЬ(КОНМЕСЯЦА(ДАТА(2018;J3;1);0)) которая в результате показывает количество дней в порядковом номере месяца из столбца J. это вам для самостоятельного изучения)

и так далее до 59 минуты. Тут я просклонял минуты, поскольку мне было лень писать формулу для выставления окончания, а диапазон в минутах у меня ограничен 59 минутами.Назовем этот лист конструктором.
При желании можно скрыть все кроме первых трех столбцов, а результат смотреть уже на шаблоне
Итак, теперь перенесем все это на шаблон ( показываю формулы):

Файл Гугл докс лежит тут:

https://drive.google.com/file/d/0B8QwhfN2DgusSDZBVUtOYW5Gdl9...

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