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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Итоги

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

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

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

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

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