6.2.11. Управление данными

Электронные таблицы Excel часто используют для ведения простейших баз данных. Возможности таких баз заметно меньше, чем у баз данных, разработанных в программе Access, но многие предпочитают не тратить время на освоение новой системы, а использовать подручные средства.

Однотабличную базу данных в Excel называют списком. Таблица, используемая в качестве базы данных, обычно состоит из нескольких столбцов, являющихся полями базы данных. Каждая строка представляет отдельную запись.

Существуют ограничения, накладываемые на структуру базы данных:

• первый ряд базы данных должен содержать неповторяющиеся имена полей (их называют метками, подписями, именами полей);

• остальные ряды базы данных должны содержать записи, которые не являются пустыми рядами;

• информация по полям (столбцам) должна быть однородной, т.е. только цифры или только текст

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

Формы. Это средство позволяет просматривать и редактировать отдельные строки списка (в терминологии баз данных – записи). Оно вызывается из меню Данные (нужно только следить, чтобы курсорная рамка в момент вызова находилась на какой-нибудь из ячеек таблицы) и представляет собой диалоговое окно со списком всех полей в виде карточки с окошками-прорезями для ввода информации. По окошкам можно перемещаться, как обычно в Windows, с помощью клавиши Tab (а в обратном направлении – используя комбинацию клавиш Shift + Tab). В правом верхнем углу выводятся номер текущей строки (записи) и общее количество строк. Можно быстро перемещаться по записям с помощью полосы прокрутки рядом с полями ввода. Можно также перемещаться по записям, нажимая кнопки Назад и Далее. С помощью формы можно добавлять записи (кнопка Добавить – записи появляются в конце списка) и удалять текущие записи базы данных (безвозвратно).

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

Фильтрация данных. Основная работа с любой базой данных заключается в поиске информации по определенным критериям. С увеличением количества записей поиск информации затрудняется. Excel позволяет упростить этот процесс путем фильтрации данных. Команды Данные, Фильтр позволяет выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр - Автофильтр, так и через ручной - Усиленный.

Автофильтр. При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, или выделить ее. Затем нужно выполнить команды Данные, Фильтр, Автофильтр. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появившемся подменю пункт Все отключает фильтрацию, а пункт Условие вызывает диалоговое окно Пользовательский автофильтр, в котором можно установить параметры фильтрации. Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ. Предельные величины выбираются из правых раскрывающихся списков, а “отношения” - из левых. Там предлагаются варианты (операторы) равно, не равно, больше, меньше и т.д. В результате применения фильтра, по нажатию ОК, происходит временное сокрытие тех записей, которые не удовлетворяют заданным условиям. В знак того, то некоторые записи скрыты, сама стрелка, да и номера видимых строк таблицы становятся не черными, а синими.

Усиленный (расширенный) фильтр позволяет:

• сразу копировать отфильтрованные записи в другое место рабочего листа (того же листа, на котором находится исходный список);

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

• показывать в отфильтрованных записях не все столбцы, а только указанные;

• объединять оператором или условия для разных столбцов;

• для одного столбца объединять операторами И, ИЛИ более двух условий;

• создавать вычисляемые критерии;

• выводить только уникальные значения.

При использовании Усиленного фильтра необходимо сначала определить (создать) три области:

• интервал списка - область базы данных;

• интервал критериев - область, где задаются критерии фильтрации. В одну из строк копируются из базы имена полей. В следующей строке в соответствующие поля вводятся условия поиска;

• интервал извлечения - область, в которой будут появляться результаты

Имена полей во всех интервалах должны точно совпадать.

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

Если необходимо получать результаты фильтрации в интервале извлечения, нужно выбрать Копировать на другое место.

Сортировка данных в списке. Команда Данные, Сортировка позволяют упорядочивать (сортировать) базу данных. Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команду Данные, Сортировка. При этом появится диалоговое окно. Нужно выбрать название поля, по которому нужно производить сортировку. Кроме того, необходимо указать метод сортировки: по возрастанию или по убыванию и нажать кнопку ОК. После указанных действий база будет упорядочена. Символьные поля упорядочиваются в алфавитном порядке. В данном диалоговом окне можно выбрать от одного до трех полей сортировки, а также задать порядок сортировки по каждому полю.