Модель «Электронная библиотека» в Excel Текст научной статьи по специальности «Компьютерные и информационные науки»

VuÊKHAÇj

МА'СТЁРСКАЯ

Паньгин Александр Викторович

МОДЕЛЬ «ЭЛЕКТРОННАЯ БИБЛИОТЕКА» В EXCEL

Любая деятельность предусматривает свое совершенство, ее совершенство же предусматривает иную деятельность.

ВВЕДЕНИЕ

Создадим модель работы небольшой городской библиотеки (или ме-диатеки) по учету выдачи книг читателям. Цель данной модели - организация «безбумажного абонемента» на основе изучения возможностей электронных таблиц.

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

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

ПОСТАНОВКА ЗАДАЧИ

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

- вести текущий учет движения книг для конкретного читателя по дате выдачи и дате возврата книг;

- отслеживать замену или списание книг в общем фонде;

- устанавливать список книг, выданных на текущий момент времени, и определять читателей, имеющих их «на руках»;

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

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

ПОСТРОЕНИЕ МОДЕЛИ

Функционально весь собранный материал можно разбить по пяти разделам с условными названиями: Фонд, Абонемент (или выдача книг), Возврат книг, Читатели, Служебный (конфиденциальный раздел информации). Создадим листы книги

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

РАЗДЕЛ «ФОНД»

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

На пространстве вкладки Фонд определим столбцы данных (рис. 1).

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

Столбцы B, C, D - Наименование книги, Авторы и Издательство, Год издания -представляют соответственно информацию, не требующую дополнительных пояснений.

Столбец E - Исходная цена (книги) -информация, необходимая для определения «ущерба», нанесенного библиотеке в случае просроченного возврата или утери книги читателем.

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

Столбец G - Стоимость (книги, текущая), определяется формулой

=ECni/l(4(F12)>0;E12*F12;E12) для ячейки G12. Для остальных ячеек подобная формула означает, что если числовой коэффициент в столбце F положительный (то есть задано число), то он умножается на цену книги.

Столбец H - Замена - отмечает замененные книги фонда, например: книга с инвентарным номером 7 заменена на книгу с инвентарным номером 11, а книга с номером 8 на данный момент времени заменена на 0 (то есть списана).

Столбец I - Строка абонемент - если ячейка не пустая, то она указывает для листа Excel с именем «Абонемент» номер строки, в которой отмечено, кому и когда выдана данная книга. Пояснение формулы для ячеек столбца I будет приведено ниже, когда познакомимся со всеми данными модели.

Номер выданной книги фонда отмечается красным цветом. Условное форматирование для ячеек столбца A (например, A2) показано на рис. 2 выбором меню Формат ® Условное форматирование.

Рис. 1

Паньгин A.B.

VcilQDHDB ljj-np.V.,n II ринат«* а

1 ЧЧН 1

в""'™ w -IZHI ы

■?Т«'НЧ Ч»МТ1 1

|iip- 1>| lïlb^o 1 1 « II

Рис. 2

Присвоим имя Фонд набору ячеек Фонд!$Л$1:$0$65. Выделим указанные ячейки, откроем меню Вставка ® Имя ® Присвоить, в поле «Имя» наберем слово «Фонд», в поле «Формула» будет отмечен выделенный набор ячеек, нажмем кнопку «ОК» или «Добавить». Аналогичным образом присвоим набору ячеек Фонд!$Л$1:$А$65 имя Инв_номер.

Замечание. Отмеченный набор ограничивается условной строкой 65. При создании модели это делается специально для проверки правильности вводимых формул или их фрагментов. В готовом приложении во всех именах число 65 следует заменить на большее число строчек Excel, например 65535. Ячейки с формулами не требуют рутинного «ручного» заполнения данными.

РАЗДЕЛ «ЧИТАТЕЛИ»

Лист «Читатели» содержит сведения о читателях библиотеки (рис. 3):

- номер читательского билета для идентификации читателя библиотеки;

- дату регистрации билета;

- фамилию, имя, отчество читателя;

- данные документа, удостоверяющие личность;

- адрес проживания; Поля «Адрес» и «Номер телефона» необходимы для извещения (звонка, SMS, e-mail сообщений) читателю о его задолженности и предупреждении о начислении пени без уважительной причины

при возврате книг.

Поле «Дата рождения» присутствует для статистического анализа возрастных групп читателей.

РАЗДЕЛ «СЛУЖЕБНЫЙ»

«Служебный» лист информации (рис. 4) содержит: в ячейке А2 - значение ставки пени (в процентах) и в ячейке В2 - дату (текущего дня), от которой производится поиск должников.

Ячейка В 2 содержит формулу =СЕГОДНЯ(), дата текущего дня обновляется при открытии данного приложения Excel (и биб-

лиотеки).

РАЗДЕЛ «АБОНЕМЕНТ»

Лист «Абонемент» имеет более сложный вид (рис. 5) и предназначен для хранения информации о выдачи книг.

Столбец A - Номер читательского билета (ячейка заполняется при выдаче книг).

Столбец B - Дата (и время) выдачи (книг), задается формулой, например для ячейки B10

=ЕСЛИ(Д10<>"";ТДАТА();"").

А

Читательский билет 7 1Ü 11

;3 27 34

В

D

Дата ФИО Документ регистрации

03.03.2007 А А А паспорт

28.02.2007 Б Б Б студ~билет

20.01.2007 В В В паспорт 21.01.2007 С С С 21.02.2007 Р 0 □

Дата

рождения 16.05.1S90

Адрес Номер телефона

26 02 2005 XXX

Адр7 ДцрЮ

АдрЩ

АДР11 Адр13 Адр27

05 03.2007 У У.У

/ЧарЭ4

47615

2Ü202 2Ü202

71234

Рис. 4

Рис. 3

Таким образом, при заполнении ячейки A10 формируется текущая дата в ячейке B10. Формат ячейки ДД.ММ.ГГГГ ч:мм

Столбец C - Дата возврата (книг), формируется формулой (для 10-й строки)

=ЕСЛИ(В10<>"";ДАТАМЕС(Б10;1);""), то есть рекомендуется сдать книги не позже одного месяца со дня выдачи (как увидим далее, при задержке будут начисляться пени).

Столбцы D, E, F - содержат инвентарные номера выдаваемых книг, соответственно для Книги 1, Книги2, КнигиЭ (не более трех книг на одного читателя).

Столбцыь G, H, I - Стоимости Книги 1, Книги2, КнигиЗ соответственно, значение в ячейке G10 задается формулой

=ЕСЛИ(Ч(й10)>0;ВПР(й10;Фонд;7;ЛОЖЬ);""), что означает, если выдана Книга1 с номером в ячейке D10, то производится поиск этого номера в крайнем левом столбце массива ячеек с именем Фонд и в найденной строке выбирается значение в ячейке 7-го столбца массива Фонд (стоимость книги). Параметр интервального просмотра ЛОЖЬ функции ВПР указывает, что поиск может производиться в неотсортированном массиве номеров книг фонда.

Для скрытия вспомогательных столбцов стоимостей отдельных книг выделите столбцы G, H, I, затем выберите команду меню Данные ® Группа и структура ® Группировать. Над выбранными столбца-

ми появится полоса уровня структуры и символ скрытия деталей (квадратик с «минусом»). Щелкая мышью на полосе уровня или символу скрытия деталей (или показа - квадратик с «плюсом»), можно скрывать (за ненадобностью) или раскрывать (по необходимости) соответствующие детальные элементы модели.

Столбец 3 - Стоимость книг, подсчет суммарной стоимости выданных книг для конкретного читателя. Используем условное форматирование (рис. 6) фона ячейки для значений стоимости, которые выше критически принимаемой (условно, более 500).

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

Столбец L - Долг, идентифицирует буквой «д» тех читателей, у которых срок возврата книг (ячейка столбца С) назначен ранее, чем текущий день (в общем случае, ранее, чем установленное значение в ячейке В2 на листе «Служебный»), и взятые книги они не возвращали (ячейка поля «Строка возврата» пустая (а точ-

Рис. 5

Б

™ ______

-г-|Ч*г * ■-. Я»

■ гм<н||т* ■ ■■ 1 гт- |

| г^-ьтъ | ^ ~1 [ ^

Рис. 6

нее, имеет значение 0). Соответствующая формула для Ь7 ячейки

=ЕСЛИ(И(С7<Служебный!$В$2;К7=0);"д";"").

РАЗДЕЛ «ВОЗВРАТ КНИГ»

Лист возврата книг содержит следующие столбцы (рис. 7).

Столбец А - номер читательского билета.

Столбец В - дата фактического возврата книг, задается формулой, аналогичной полю «Дата выдачи» в разделе «Абонемент».

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

Индекс А в имени относится к листу «Абонемент», индекс В - к листу «Возврат книг».

Вернемся к описанию раздела «Возврат книг» и ячеек с формулами.

Столбец С - Строка листа абонемента - содержит в ячейках (например, для С2) табличную формулу:

{=МАКС((Читатель_А=Д2)* С^РОК^(Ч^Гспель_А;Г(Р^па_выдЕьм<В2))} Данной формулой производится поиск номера сроки на листе «Абонемент», в которой содержится последняя (по дате) информация о выдачи книг данному читателю. Опишем формулу по частям. Фрагмент формулы (Читатель_А=Д2) возвращает массив логических значений ИСТИНА (1) и ЛОЖЬ (0), в зависимости от того, содержится ли номер читательского билета (содержимое А2) в строке массива «Читатель_А». Если это значение - ЛОЖЬ, то фрагмент

(Ч итате л ь_А=Д2 )*СТРОКА(Читател ь_А) возвращает значение 0, а если ИСТИНА -значение, равное номеру строки. Умножение на фрагмент (Дата_выдачи<В2) селектирует ненулевые строки, для которых дата выдачи меньше даты возврата (в ячейке В2). Наконец, функция МАКС определяет максимальную строку с информацией о последней выдаче перед данным возвратом книг. Ввод табличной формулы должен завершаться совместным нажатием клавиш <СМ>+<8Ый>+<ЕПег>.

Столбец С - (количество) Дней невозврата, определяется формулой, например для С6 ячейки

Рис. 7

=ЕСЛИ(В6>ИНДЕКС(Дата_возврата_А;06); ЦЕЛОЕ(В6-ИНДЕКС(Дата_возврата_А; 06)) ;0)

То есть если дата фактического возврата больше даты запланированного возврата (которая расположена на листе «Абонемент» в строке с найденным номером в ячейке С6), то будет возвращено целое значение от разницы дат, иначе - значение 0. В ячейках столбца С задать формат «Общий».

Столбец Е - Пени, осуществляет подсчет пени перемножением значений: суммарной стоимости выданных книг (в столбце ] листа «Абонемент»), количества дней невозврата, процентной ставки пени.

Замечания

Таблица 1

Имя массива ячеек Формула

Дата_возврата_А =Абонемент!$0$1:$С$65

Дата_возврата_В -Возврат книг '!$В$1:$В$65

Дата_выдачи =Абонемент!$В$1:$В$65

Инв_номер =Фонд!$А$1:$А$65

Книга1 =Абонемент!$0$1:$й$65

Книга2 =Абонемент!$Е$1:$Е$65

КнигаЗ =Абонемент!$Р$1 :$Р$65

Книги_В =Абонемент!$й$1:$Р$65

Стоимость =Фонд!$0$1:$в$65

Строка_возврата =Абонемент!$К$1:$К$65

Фонд =Фонд!$А$1:$в$65

Читатель_А =Абонемент!$А$1:$А$65

Читатель_В ='Возврат книг '!$А$1:$А$65

1. При отладке формулы можно в строке формулы увидеть значения, которые возвращает любая используемая функция или ее параметры. Выделите на рабочем листе ячейку, содержащую формулу, и в строке формул выделите мышью интересующую вас часть этой формулы. Нажмите клавишу <Б9>, выделенная часть формулы заменится на вычисленные значения (массивы значений). Для возврата к обычному отображению формулы нажмите клавишу <Б8с> или щелкните на кнопке «Отмена» в строке формул (кнопка с косым красным крестиком).

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

Теперь можно пояснить смысл табличной формулы поля «Строки возврата» на листе «Абонемент»:

{=МИН(ЕСЛИ((Читатель_В=А2)* (Дата_возврата_В> В2)>0 ;СТРО КА(Ч итател ь_В)))}

По данной формуле если на листе «Возврат книг» нужный читатель (точнее номер его билета, заданный в ячейке А2) вернул книги позже данной даты выдачи (В2), то определяются номера таких строк, а из этих номеров ищется минимальный, иначе - значение 0). Проверьте, какие результирующие значения содержат отдель-

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

Чтобы скрыть отображение нулевых значений в столбце, выделите ячейки столбца в меню Формат ® Ячейки на вкладке «Число», в списке «Числовые форматы» выберите пункт (все форматы), а в поле Тип введите 0;-0;;@.

Для поля «Строка абонемент» на листе «Фонд» ячейки (например, 12) содержат табличную формулу

{=МАКС(((Книга1=А2)+(Книга2=А2)+(Книга3=А2))* (Строка_возврата=0)*СТРОКА(Читатель_В))}

По этой формуле для каждой книги фонда ищутся на листе «Абонемент» все строки, в которых присутствует данная книга в качестве одной из выданных (то есть в строке либо (Книга1=А2), либо (Книга2=А2), либо (Книга3=А2) имеет значение ИСТИНА), а строка возврата - пустая. Выбирается максимальная такая строка в качестве результирующей (так как строки листа «Абонемент» заполняются по возрастанию даты выдачи книг). Примените форматирование для скрытия нулевых значений.

Модель готова. Готовы ли Вы найти для нее практическое применение и совершенствование?

Консолидация Ж

Функция:

| | Количество чисел V |

1 Ссылка:

; Абонемент! 1: 10 Ш [ Обзор... I

Список диапазонов;

Абонемент !±A±l:±Fi 10

Добавить

Удалить

Использовать в качестве имен 0 подписи верхней строки 0 значения левого столбца

I I Создавать связи с исходными данными | ^ | | закрыть ]

Рис. 8

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Выполните следующие задания.

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

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

3. Определите рейтинг активности читателей по количеству прочитанных ими книг, а также общее количество книг, выданных за отчетный период.

Указания:

Выделить данные для нужного интервала времени можно с использованием команды Данные ® Фильтр ® Автофильтр,

при этом наложить должные условия на поля с датами.

1. Для подсчета количества ячеек с данными в интервале или массиве можно применить функцию СЧЁТЗ.

2. Частоту использования книги с инвентарным номером 3 (ячейка Фонд!А2)

можно определить по формуле {=СУММ((Книга1=Д2)+(Книга2=Д2)+(Книга3=Д2))}

3. Для выполнения задания 3 подойдет в качестве нахождения промежуточных данных процедура консолидации данных (команда меню Данные ® Консолидация). Выберите любую свободную ячейку листа «Служебный», в окне команды Консолидация задайте параметры, как на рис. 8, и нажмите кнопку «ОК».

Для каждого читателя создается таблица количества единиц взятых книг (по полю Книга1, Книга2, КнигаЗ), просуммировав данные можно получить общее количество книг как для отдельного читателя, так и в совокупности по библиотеке в целом.

ТВОРЧЕСКОЕ ЗАДАНИЕ

В определенное время библиотека проводит перерегистрацию читателей. В чем ее смысл и какие действия предусматривает данная процедура? Дополните модель данной функцией.

РЕЗЮМЕ

Профессиональное ис-полненне поставленной задачи, возможно, потребовало бы использование «движка» (СУБД) для работы с базами данных. Однако для демонстрации взаимосвязей между данными был использован простейший инструмент, но с «богатыми» возможностями по обработке информации различных типов. Обучение этим возможностям при решении конкретной повседневной задачи и ставилось целью данной работы.

© Наши авторы, 2007 Our authors, 2007

Паньгин Александр Викторович, инженер Центра информационных технологий, г. Сосновыш Бор.