Результаты данной работы проверены в работе и используются по настоящее время. Для получения исходных файлов обращайтесь в личку.

Тема  итоговой  работы: 

«Применение  электронных  таблиц  (Excel)  педагогом – библиотекарем при формировании «Отчёта о работе библиотеки в 1 полугодии 2016 – 2017 учебного года»

Работа выполнена в программе Microsoft Excel 2010 с возможностью применения её в постоянной работе педагога-библиотекаря школьной библиотеки с ведением электронного дневника работы библиотеки в файлах:

Учет читателей2016 – 2017.xlsx;

Учет книговыдачи2016 – 2017.xlsx;

Мероприятия2016 – 2017.xlsx.

Файл отчета «Отчет о работе библиотеки .xlsm» автоматически вычисляет и формирует по данным из файлов дневника работы библиотеки основные показатели работы библиотеки.

Все эти показатели и сформированные диаграммы и графики помещаются в бланк формализованного отчета на рабочем листе «Отчет о работе библиотеки» файла «Отчет о работе библиотеки .xlsm».

Часть числовых данных отчета, которые отсутствуют в файлах дневника работы библиотеки, пользователи вводят на лист «Исх. Данные», а текстовые – непосредственно в рабочем листе «Отчет о работе библиотеки».

В связи с тем, что файлы необходимо высылать в Ваш адрес, для работы связей в файл «Отчет о работе библиотеки .xlsm» встроен макрос:

Private Sub Workbook_Open()

UserForm1.Show

Set wb = ActiveWorkbook

Windows("Отчет о работе библиотеки .xlsm").Activate

Workbooks.Open Filename:=wb.Path & "\" & "Учет читателей2016 – 2017.xlsx"

Workbooks.Open Filename:=wb.Path & "\" & "Учет книговыдачи2016 – 2017.xlsx"

Workbooks.Open Filename:=wb.Path & "\" & "Мероприятия2016 – 2017.xlsx"

' Workbooks.Open Filename:="Учет читателей2016 – 2017.xlsx"

' Workbooks.Open Filename:="Учет книговыдачи2016 – 2017.xlsx"

' Workbooks.Open Filename:="Мероприятия2016 – 2017.xlsx"

Windows("Отчет о работе библиотеки .xlsm").Activate

End Sub

А так же строку (UserForm1.Show ) вывода начальной заставки.

Все файлы должны размещаться в той же директории, что и основной файл «Отчет о работе библиотеки .xlsm».

Данный макрос при загрузке файла «Отчет о работе библиотеки .xlsm», выводит на 5 сек. заставку, загружает в Excel все файлы дневника и делает активным рабочий лист «Отчет о работе библиотеки» файла «Отчет о работе библиотеки .xlsm».

Если Вы постоянно работаете в одном каталоге, то можно использовать закомментированные строки без указания путей к файлу

Workbooks.Open Filename:="Мероприятия2016 – 2017.xlsx,

как в строках

Workbooks.Open Filename:=wb.Path & "\" & "Мероприятия2016 – 2017.xlsx".

Внимание: 1. Для корректной работы программы необходимо разрешить использование макросов.

2. Выяснилась закономерность – при первом запуске, даже при включенных макросах, запуска макроса не происходит и открывается только файл «Отчет о работе библиотеки .xlsm», но после его закрытия без сохранения в программе Excel и повторного открытия через «Файл – Последние», все работает правильно.

Причина данного поведения не выявлена, если у Вас есть предложения, прошу мне сообщить.

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

В настоящий момент, данные этой ячейки «Е7» используются только при формировании имен файлов которые должны быть открыты, например:

="Мероприятия"&E7&".xlsx"

А так же совместно с данными из ячейки «Отчетный период» в автоматическом формировании заголовка отчета на листе «Отчет о работе библиотеки»:

Ячейка В1:

Отчет о работе библиотеки в

Ячейка Е1:

=ЕСЛИ('Исх. данные'!E5=1;"1 полугодии";ЕСЛИ('Исх. данные'!E5=2;"2 полугодии";'Исх. данные'!E7))

Ячейка G1:

=ЕСЛИ('Исх. данные'!E5=1;'Исх. данные'!E7;ЕСЛИ('Исх. данные'!E5=2;'Исх. данные'!E7;" учебном году"))

Ячейка H1:

=ЕСЛИ('Исх. данные'!E5=1;"учебного года";ЕСЛИ('Исх. данные'!E5=2;"учебного года";""))

В дальнейшем планируется по данным ячеек «Учебный год» и «Отчетный период» автоматическое изменение имен загружаемых файлов (в макросе загрузки), в ссылках на данные из других файлов, а также изменение рядов данных графиков.

Описание файлов

Файл Учет читателей2016 – 2017.xlsx:

Это электронные таблицы учёта читателей и учёта посещений библиотеки по месяцам и дням.

Учёт в каждом месяце ведётся на отдельном листе книги.

К данным столбца «Число посещений в день» применено условное форматирование «Гистограмма» с градиентной заливкой в соответствии со значениями ячейки.

Учёт данных по читателям и по числу посещений ведётся с начала учебного года помесячно и накоплением.

Кроме этого вычисляется среднее число посещений за месяц.

Данные ячеек числа:

- читателей;

- учеников;

- посещений в месяц;

- посещений за отчетный период;

- посещений в месяц среднее значение

передаются в файл «Отчет о работе библиотеки .xlsm» для формирования графиков и отчета.

Файл Учет читателей2016 – 2017.xlsx:

Это электронные таблицы учёта книговыдачи по месяцам и дням.

Учёт в каждом месяце ведётся на отдельном листе книги.

Учёт данных книговыдачи ведётся с начала учебного года помесячно и накоплением.

Данные по числу книговыдачи и по распределению книговыдачи по жанрам, передаются в файл «Отчет о работе библиотеки .xlsm» для формирования графиков и отчёта.

Файл Мероприятия2016 – 2017.xlsx:

Это электронные таблицы учёта мероприятий по месяцам и дням.

Книга учёта мероприятий состоит из листов:

- Книжные выставки.

- Участники мероприятий.

- Презентации.

- Уголок книголюба.

В данных электронных таблицах ведётся учёт проведённых мероприятий и участников.

В Таблицах «Участники мероприятий» используется работа со списками и условное форматирование по условию.

Данные по числу мероприятий, передаются в файл «Отчет о работе библиотеки .xlsm» для формирования отчёта.

Файл «Отчет о работе библиотеки .xlsm»:

Это электронные таблицы формирования отчёта о работе библиотеки за отчётный период.

Книга «Отчет о работе библиотеки» состоит из листов:

- Отчет о работе библиотеки

- Исх. Данные.

- Временный.

Рабочий лист «Отчет о работе библиотеки» является формализованным бланком отчёта о работе библиотеки в который интегрируются все данные с других книг.

Кроме этого на нем производятся формульные расчеты показателей работы библиотеки:

- читаемость;

- посещаемость;

- обращаемость;

- книгообеспеченность.

Рабочий лист «Исх. данные» предназначен для выбора отчётного периода, ввода параметров учебного года и данных о фонде:

Рабочий лист «Временный» предназначен для сбора и хранения данных для формирования ряда данных, встраиваемых в отчёт графиков и диаграмм.

Выводы

Данная работа даёт начало перехода в электронный документооборот и позволяет вести совместно с работой в АБИС документацию библиотеки в электронном виде.

Кроме этого в данной работе заложена возможность дальнейшей модернизации и расширение возможностей автоматизации процесса учета и формирования отчетов.