Базы данных в 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