Павел Шаповалов - Автоматизация работы в Excel

списком».

Теперь настроим его указав диапазон равный B5:E10, а зависимую ячейку
укажем B1

Теперь форма будет выглядеть вот так.

А при выборе значения в ячейке В1 публикуется порядковый номер
выбранной строки. Например, при выборе Сидоров, который в списке 4-й в
В1 появляется число 4.

Начинаем вводить формулу в ячейку «должность»

Теперь переходим к ячейке «стаж»

И последнее поле - «дата рождения»

Теперь в поле со списком выбираем «Мишин А». Так как товарищ Мишин 5й по списку - в связанной ячейке B1 загорается число 5. Это значит, что все
ячейки с формулами ИНДЕКС будут выдавать значения из 5-й строки.

В графе «должность» из 4-го столбца, в графе «стаж» из 3-го, а в графе
«дата рождения» из 2-го.

Теперь попробуем выбрать вариант «Петров С.» Так как это вторая строчка,
во всех ячейках с формулой ИНДЕКС отображаются данные из 2-й строки.

Следующая функция, которая придет нам на помощь в автоматизации это
ДВССЫЛ. Она поможет организовать нам связанные диапазоны. Т.е. будут 2
и более списков, которые будут формироваться в зависимости от того, что
выбрано в первом списке. Итак, начнем.
У нас имеется вот такой список.

Теперь создадим три именованных диапазона. В первый войдет диапазон
A2:A4, во второй B2:B4 в третий C2:С4. Названия диапазонов должны
совпадать с названиями стран.

В итоге должно получиться вот так:

Теперь переходим на вкладку «Данные» и выбираем «проверка данных»

В появившемся окне тип данных установим «список», а источник диапазон
с названиями диапазонов, в нашем случае это название стран.

Получился вот такой выпадающий список.

Теперь в ячейке F1 также запускаем проверку данных. Тип данных также
указываем «список», а вот в поле «источник» вбиваем формулу
=ДВССЫЛ(F1)

После этого при выборе в ячейке E1 параметра «Россия», в ячейке F1 будут
выдаваться только российские города.

РАЗДЕЛ №2 ПРАКТИКА
Теперь попробуем закрепить материал на практическом примере.
Компания «Светлый путь» имеет свой гараж и ежедневно
регистрирует в Excel все путевые листы. Для этого используется вот
такой шаблон.

Т.е. данные записываются в шаблон и сохраняются на одном листе, затем
копируется второй лист и так далее.
Это конечно очень неудобно, приходится создавать новые листы, книги,
очень трудно вести поиск, не говоря уже о регистрации путевых листов.
Сейчас мы с Вами совместно попробуем автоматизировать данный процесс.
Перед нами стоит задача создать регистрацию всех путевых листов с
возможностью вывода на экран или печати любого из них.
Для начала создаем второй лист под названием «регистрация».

Вводим на нем следующую форму:

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

Устанавливаем его на рабочую область.

Переходим в формат объекта.

И нажимаем на вот этот значок.

Теперь переходим на вкладку «регистрация» и выбираем столбец А
целиком.

В поле «связь с ячейкой» укажем ВВ1.

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

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

В этой таблице выделяем столбец гос.номер и присваиваем выбранному
диапазону имя «гос_номер». Тоже самое делаем со столбцом марка и
присваиваем имя «марка»

Для того, чтобы создать диапазон с именем достаточно выбрать его, в поле
имя ввести название и нажать на Enter. Только учтите, что пробел в
названиях недопустим, поэтому используем нижнее подчеркивание.

Теперь переходим на вкладку «регистрация» и выделяем столбец В. Затем
идем на вкладку «Данные» – «Проверка данных».

В поле тип данных установим «список», а в поле источник укажем =марка.

Выражение «=марка» заменяет стандартное выделение диапазона с
перечнем марок автомобилей.

Теперь устанавливаем табличный курсор на любой ячейке столбца B и
увидим выпадающий --">