На этом шаге мы рассмотрим общие принципы формирования отчетов.

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

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

Рассмотрим автоматизацию процесса по подготовке отчетов на двух примерах:

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

Формирование отчетов вручную

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

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

Отчет по расходу топлива за произвольный интервал времени

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

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

Ручной метод подготовки отчета

Наиболее простой способ - создание точной копии листа БазаДанных, который после последующей обработки и будет являться отчетом. Обработка заключается в следующем:

сортировка списка по дате выезда из гаража и потом по дате возвращения в гараж;
удаление "лишних" столбцов и строк;
придание созданной таблице формы отчета.

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

Автоматизированная форма подготовки отчета

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

Форма создаваемого отчета на листе ОтчетПериод показана на рисугке 1 с формулами и на рисунке 2 с числовым примером.

Рис.1. Лист ОтчетПериод с формулами создаваемого отчета

Рис.2. Лист ОтчетПериод с числовым примером создаваемого отчета

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

Формулы подготовки отчета

В ячейку А7 введена формула, которая сравнивает:

дату, введенную в ячейку С2 и дату возвращения в гараж первой строчки созданной табличной базы данных (ячейка С2 листа БазаДанных);
если дата в ячейке С2 больше даты на листе БазаДанных, то тогда функция ЕСЛИ возвращает значение 0;
если дата в ячейке С2 меньше или равна дате на листе БазаДанных, то тогда вторая функция ЕСЛИ сравнивает дату в ячейке В2 с датой в ячейке В2 на листе БазаДанных;
если начальная дата формирования отчета равна или более даты на листе БазаДанных, то тогда формула возвращает значение, содержащееся в ячейке А2 листа БазаДанных, или, в противном случае, возвращается значение 0.

Все остальные формулы строки 7 сравнивают значение в ячейке А7 со значением 0. Значение 0 указывает на то, что эта строка в табличной базе данных не представляет интерес для отчета. И поэтому, если это условие выполняется, то и эти формулы возвращают значение 0.

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

Последовательность формирования отчета

Если строку 7 (или диапазон ячеек) с формулами скопировать в нижние строки таблицы (например, до строки 1000), то формулы "выхватят" только указанный диапазон времени, который, вероятнее всего, будет находиться где-то в середине таблицы. Кроме того, файл, содержащий такое количество формул, будет громоздок.

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

Для формирования отчета нужно заменить формулы вычисленными ими значениями и применить сортировку по убыванию, например, по столбцу А, что позволит расположить выбранные данные в верхней части таблицы (отчета).

Макрос СоздатьОтчетТопливо, выполняющий формирование отчета, показан на рисунке 3.

Рис.3. Макрос СоздатьОтчетТопливо, выполняющий формирование отчета по топливу

Он производит ввод формул в строке 7, последующее копирование формул в заданный диапазон таблицы, замену формул на вычисленные ими значения и сортировку выделенного диапазона по убыванию.

Отчет по водителям

Следующий пример создаваемого отчета показан на листе ОтчетВодитель с введенными формулами (рисунок 4) и числовым примером (рисунок 5).

Рис.4. Рабочий лист ОтчетВодитель с формулами, применяемыми при создании отчета

Рис.5. Рабочий лист ОтчетВодитель с числовым примером

Отчет должен содержать следующие данные:

автомобиль и его государственный номер;
дата выезда из гаража;
дата возвращения в гараж;
пробег автомобилей по спидометру

и формироваться по введенной в ячейку D3 фамилии водителя.

Для ввода фамилии водителя скопируйте созданный элемент управления Список ввода фамилии водителя, после чего произведите вставку скопированного на лист ОтчетВодитель. Аналогичную операцию произведите с формулой ячейки D3 на листе ВводДанных.

Последовательность действий создания макроса по формированию отчета аналогична предыдущему. Отличие заключается только в формуле анализа данных, введенной в ячейку А7 листа ОтчетВодитель.

Формула в ячейке А7 производит поиск текста фамилии водителя, введенной в ячейку D3 листа ОтчетВодитель в тексте, находящемся в ячейке А2 листа БазаДанных. Поиск текста основан на применении функции НАЙТИ, которая при нахождении искомого текста возвращает начальную позицию совпадения.

Сложность заключается в том, что если функция НАЙТИ не находит заданный текст, то она возвращает значение ошибки #ЗНАЧ!, поэтому функция НАЙТИ помещена внутрь функции ЕОШИБКА, которая возвращает значение ИСТИНА, если ее аргумент содержит значение ошибки.

Функция ЕСЛИ возвращает значение 0 при отсутствии текста фамилии водителя в тексте, содержавшемся в ячейке А2 на листе БазаДанных.

В противном случае формула возвращает левые символы текста, находящегося в ячейке А2 листа БазаДанных, в количестве, уменьшенном на количество символов, находящихся в этом тексте справа до первой буквы фамилии водителя. Для этого применяется функция ЛЕВСИМВ.

Создание отчета выполняет макрос СоздатьОтчетВодитель (рисунок 6).

Рис.6. Макрос СоздатьОтчетВодитель

Процедура выполнения его аналогична предыдущему макросу.

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

Полный текст этого приложения можно взять здесь.

Со следующего шага мы начнем рассматривать планирование маршрута.

Предыдущий шаг Содержание Следующий шаг