Базы данных в MS Excel

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

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

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

Вводить данные можно непосредственно в таблицу MS Excel, однако удобнее воспользоваться так называемой формой, которая представляет собой диалоговое окно, содержащее поля ввода, соответствующие столбцам таблицы. Пример формы для табл. 13.6 изображен на рис. 13.20. Для открытия окна формы нужно выделить всю таблицу, в том числе и строку, содержащую названия полей, а затем выполнить команду Данные > Форма.... В выведенном на экран окне формы с помощью кнопок Далее и Назад можно перемещаться между существующими записями и вносить в них изменения, редактируя содержимое соответствующих полей формы. С помощью кнопки Добавить можно включить новую запись (строку) в таблицу, а с помощью кнопки Удалить — исключить из нее любую запись.

С помощью формы можно обратиться к механизмам поиска записей, удовлетворяющих некоторым простым условиям. В качестве условия может выступать искомый набор символов — образец поиска. Пусть, например, нужно найти запись с конкретным названием предприятия. В этом случае образец поиска может выглядеть, скажем, так: Предприятие 675. В образцах поиска могут использоваться символы подстановки? и *. Напоминаем, что символу? соответствует любой одиночный символ поля записи, а символу * — любая последовательность любых символов. Например, образцу поиска «д?м» соответствуют слова «дым» и «дом», но не соответствует слово «другом». А образцу поиска «*ино» соответствуют названия «Люблино» и «Выхино».

Рис. 13.20. Пример формы

Условия можно накладывать на числовые значения, даты, время и т. д. Например, если нужно найти предприятия, которые за первый квартал выпустили более трехсот единиц продукции, то в этом случае условие будет иметь вид: >300. В таких условиях можно использовать операции сравнения <, <=, >, >=, =, <>, которые применяются к значениям числовых полей.

Поиск организуется следующим образом. В форме есть кнопка Критерии, после нажатия которой все поля формы станут пустыми, а на месте кнопки Критерии появится кнопка Правка. В поля формы, по которым будет проводиться поиск, вводятся образцы поиска или условия. Так, для формы, изображенной на рис. 13.20, упомянутые выше образцы поиска должны вводиться в поля Название предприятия: и 1 квартал:. Одновременно можно заполнить несколько полей. Это означает, что нужно найти записи, которые одновременно удовлетворяют всем записанным в полях формы условиям. Другими словами, такие условия связаны операцией «логическое И» (конъюнкцией). После заполнения полей следует нажать кнопку Далее. Программа MS Excel отберет в списке все записи, удовлетворяющие поставленным условиям. Для перехода между выбранными записями можно использовать кнопки Назад и Далее.

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

С помощью форм легко осуществить поиск по критериям, в которых используется операция «логическое И», однако для более сложных условий поиска формы не подходят. В этом случае следует обратиться к команде Данные > фильтр > Автофильтр. В результате ее выполнения возле каждого поля появится кнопка раскрытия списка (рис. 31.21). В списке (на рис. 13.21 развернут список поля 1 квартал) содержатся все значения поля и пункт Условие..., позволяющий определить более сложный критерий. Выбор этого пункта вызывает диалоговое окно Пользовательский автофильтр (рис. 13.22), с помощью элементов управления которого можно задать достаточно сложное условие отбора записей.

Рис. 13.21. Выполнение команды Автофильтр

Рис. 13.22. Окно Пользовательский автофильтр

В приведенном на рис. 13.22 примере запрашивается поиск записей, в которых за первый квартал произведено больше чем 100 единиц продукции или меньше чем 50. Когда записи появятся на экране, их (не все, а только выбранные) можно распечатать, выполнив команду Файл >Печать.... По окончании работы автофильтр с помощью той же самой команды Данные > Фильтр > Автофильтр следует отключить.

Существует также возможность одновременного поиска по нескольким полям и поиска по вычисляемому критерию. Для этого служит команда Данные > Фильтр >Расширенный фильтр.... Прежде чем воспользоваться этой возможностью, необходимо сформировать таблицу диапазона условий, которая состоит, по крайней мере, из одной строки заголовков полей и одной строки условий. Эта таблица может содержать несколько одинаковых заголовков полей. Если в сложном условии используется операция «логическое И», то входящие в него простые условия записываются в одной и той же строке условий таблицы. Если применяется операция «логическое ИЛИ» (дизъюнкции), то простые условия следует записать друг под другом в одном и том же поле (столбце). Если, например, нужно отобрать предприятия, которые выпустили в первом квартале не меньше чем 100 и не больше чем 300 единиц продукции и при этом во втором квартале выпустили либо больше 200, либо меньше 500 единиц продукции, то таблица диапазона условий должна выглядеть следующим образом:

I кв. I кв. II кв.

>=100 <=300 >200

<500

ВНИМАНИЕ

В этой таблице дважды используется заголовок одного и того же поля. Это значит, что значение из этого поля используется в сложном условии несколько раз и участвует в операции «логическое И».

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

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

Часто данные, содержащиеся в списке, требуется представлять различными способами, подводя различные промежуточные итоги. Для этого можно использовать возможности команд Данные > Итоги... и Данные > Сводная таблица.... Выполнение команды Данные > Итоги... приводит к созданию таблицы специальной структуры, подобной вложенному списку, отдельные элементы которой могут быть ради удобства анализа скрыты или вновь показаны. Пусть, например, в таблице накапливаются сведения о ежедневных продажах, выпрлненных разными отделениями торговой организации. Чтобы с помощью команды Данные > Итоги... получить сведения о продажах по каждому отделению, в диалоговом окне команды в списке При каждом изменении в: окна команды следует выбрать заголовок поля, содержащего названия отделений. А если нужно подводить ежедневные итоги, то в этом списке следует выбрать заголовок поля, содержащего даты выполнения продаж. Далее, в списке Операция нужно выбрать способ подведения итогов. Это может быть количество продаж или сумма значений в каком-либо поле, например, в поле стоимости проданного товара. После чего в списке флажков Добавить итоги по: включить флажки, соответствующие тем полям списка, по которым следует сформировать промежуточные итоги. Чтобы вернуть таблице исходный вид, нужно вновь открыть окно команды Данные > Итоги... и нажать кнопку Убрать все.

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