Справочник с отчетом в MS EXCEL

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

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

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

В результате мы должны получить вот такую форму:

Исходные данные о водителях будем вводить на другом листе:

Подробнее с таблицами можно ознакомится в файле примера: ссылка на него - внизу статьи.

Формируем лист с исходными данными

На листе Водители создадим таблицу, содержащую подробные данные о водителях. Таблица простая, но при ее создании рекомендуется придерживаться нескольких правил:

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

Примечание: Рекомендации о создании таблиц в MS EXCEL см. статью Советы по построению таблиц в MS EXCEL

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

Также можно преобразовать таблицу в формат таблиц EXCEL. Об этом подробнее написано здесь. Это существенно упростит добавление и удаление строк в таблицу. В этом случае ссылки в формулах на столбцы таблицы будут в виде структурированных ссылок, например, =СУММ(Таблица1[Стоимость])

Заполнять многостолбцовую таблицу не всегда удобно, поэтому в параметрах MS EXCEL можно настроить переход к другой ячейке после нажатия клавиши ВВОД (ENTER).

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

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

Если категорий две или больше, то можно предусмотреть дополнительные элементы выпадающего списка: E и F, А и В и т.д. Либо ввести дополнительный столбец Другие категории. В этот столбец можно ввести дополнительные категории. Либо можно вообще отказаться от выпадающего списка и вводить категории через запятую. Однако, это может стать проблемой, если потребуется сделать другой отчет, например: Кто из водителей имеет 2 категории, одна из которых А? 

Для удобства ввода номера паспорта использовано пользовательское форматирование ячейки: 00" "00" "000000

Для контроля повторяющихся фамилий использовано Условное форматирование.

Контроль повторов фамилий очень важен, т.к. по ней осуществляется поиск водителя. Если 2 и более водителя имеют одинаковую фамилию, то поиск усложняется. Об этом ниже, но сначала рассмотрим как формируется отчет.

Формируем отчет

Напомним, что форма отчета выглядит так:

Выбрав в желтой ячейке фамилию водителя, формулы на основе функции ВПР() автоматически выведут все данные из соответствующей строки листа Водители.

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

Решением могут служить разные подходы:

  • осуществлять поиск водителя по его коду (не удобно);
  • сначала выбрать фамилию, затем сформировав список имен и отчеств, сделать окончательный выбор. См. статью Вложенный связанный список в MS EXCEL. Это реализовать достаточно сложно.
  • к фамилии добавить короткое пояснение, например Иванов[длинный] и Иванов[рыжий]. Это позволит избавиться от повторов в исходной таблице. В отчете при выводе фамилии можно автоматически убрать скобки: функцией ПОИСК() найдите позицию символа [, затем с помощью ЛЕВСИМВ() выведите фамилию до скобки.

Примечание: Область печати листа настроена таким образом, чтобы на печать выводился только отчет.


Связанные статьи

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