Как составить каталог книг домашней библиотеки в excel

Корпорация "Центр"

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

CC BY

Аннотация научной статьи по компьютерным и информационным наукам, автор научной работы — Паньгин Александр Викторович

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

Похожие темы научных работ по компьютерным и информационным наукам , автор научной работы — Паньгин Александр Викторович

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

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

МОДЕЛЬ «ЭЛЕКТРОННАЯ БИБЛИОТЕКА» В 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 выбором меню Формат ® Условное форматирование.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

03.03.2007 А А А паспорт

28.02.2007 Б Б Б студ

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

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

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

Таким образом, при заполнении ячейки 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 на листе «Служебный»), и взятые книги они не возвращали (ячейка поля «Строка возврата» пустая (а точ-

■ гм Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

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

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

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

Корпорация "Центр"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

можно определить по формуле

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

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

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

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

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

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

Как использовать Microsoft Excel для каталогизации книг? — Вокруг-Дом — 2021

Table of Contents:

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

Многоуровневая сортировка Excel помогает визуализировать вашу библиотеку по категориям. Кредит: Изображение предоставлено Microsoft

Создать коллекцию книг. Кредит: Изображение предоставлено Microsoft

Нажмите «Файл» и «Создать» в Excel 2013. Найдите «Коллекция книг» и выберите «Список коллекции книг». Если вы предпочитаете, вы можете вместо этого выложить свою электронную таблицу с нуля, но использование шаблона автоматизирует большую часть форматирования и дизайна столбцов и все еще позволяет выполнять настройку.

Удалить ненужные колонки. Кредит: Изображение предоставлено Microsoft

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

Вставьте и переименуйте columnscredit: Изображение предоставлено Microsoft

Добавьте свои собственные столбцы данных, выбрав и напечатав ненужные столбцы, или щелкнув правой кнопкой мыши заголовок столбца и выбрав «Вставить», чтобы создать новый столбец. Удалите, переименуйте и вставьте столько столбцов, сколько вам нужно, чтобы создать категории каталогов, которые вы хотите.

Добавьте и измените размеры cell.credit: Изображение предоставлено Microsoft

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

Держите заголовки столбцов видимыми. Кредит: Изображение предоставлено Microsoft

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

Сортировать catalog.credit: Изображение предоставлено Microsoft

Выберите «Сортировка» в разделе «Сортировка и фильтрация» на вкладке «Данные», чтобы изменить порядок строк. Выберите столбец для сортировки и нажмите «Добавить уровень», если вы хотите уточнить сортировку по другому столбцу. См. Ссылка 4. Когда вы нажимаете «ОК», Excel окончательно реорганизует ваши строки — если вы заботитесь о порядке, в котором вы ввели свои книги, создайте столбец, обозначающий «Заказ добавлен» с постепенно увеличивающимися числами перед сортировкой.

Фильтр catalog.credit: Изображение предоставлено Microsoft

Нажмите на стрелку рядом с заголовком столбца, чтобы отфильтровать каталог. Отметьте один или несколько элементов в столбце и нажмите «ОК», чтобы скрыть все записи, которые не соответствуют выбранным элементам. Фильтрация не изменяет ваши данные навсегда — снова откройте фильтр и выберите «Очистить фильтр», чтобы снова отобразить всю электронную таблицу.

Добавить новые sheet.credit: Изображение предоставлено Microsoft

При необходимости добавьте дополнительные страницы, например, чтобы разделить каталог на таблицы «Художественная литература» и «Научная литература». Щелкните правой кнопкой мыши вкладку текущего листа, выберите «Вставить» и выберите шаблон коллекции книг, чтобы добавить другую страницу. Чтобы переименовать страницу, дважды щелкните заголовок вкладки.

Корпорация "Центр"

Ссылка на основную публикацию