Теоретические сведения. MS Excel обладает некоторыми возможностями систем управления базами данных (СУБД)

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

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

Список Excel имеет следующие ограничения:

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

MS Excel работает как плоская СУБД, т.е. за один раз можно получить информацию из одного набора данных (реляционные базы данных в отличие от плоских позволяют обрабатывать информацию находящуюся в разных наборах данных-таблицах);

Правила составления списка.

Имена полей списка должны быть корректными, отличными друг от друга;

§ Формат имен полей должен быть отличен от формата основного списка, что позволяет Excel различать простую таблицу и список;

§ Столбцы должны располагаться в том порядке, в котором вводятся данные;

§ Внутри списка не должно быть пустых записей или полей;

§ На рабочем листе список от других данных должен быть отделен пустым столбцом или пустой строкой;

§ Формулы в списке, ссылающиеся на данные вне списка, должны содержать абсолютные ссылки. Так как при сортировке списка эти формулы не будут работать;

§ Формулы в списке, ссылающиеся на данные внутри списка должны содержать относительные ссылки, при сортировке они будут автоматически корректироваться;

Ввод данных в список.

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

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

2. Ввод данных с помощью команды Данные ®Форма представляет более наглядное и удобное средство заполнения списка. Используя названия полей из первой строки, Excel конструирует форму с соответствующими полями. Форма содержит элементы управления кнопки и полосу прокрутки, с помощью которых можно добавлять и удалять записи, вводить критерии, переходить от записи к записи. Недостаток формы в том, что она не может работать с более чем 32 полями.

Сортировка данных.

Команда Данные ® Сортировка позволяет отсортировать список по значениям одного или нескольких полей. Одновременно сортировка может вестись максимум по трем полям. Если необходимо отсортировать например 6 полей, сначала сортирую последние три поля, а затем первые три. Для сортировки необходимо выделить нужные столбцы и для каждого указать режим сортировки (по возрастанию или по убыванию).

Поиск и отбор данных, удовлетворяющих некоторому критерию.

Существует три способа поиска и отбора данных:

o С помощью Формы данных;

o Применение Автофильтра;

o Применение Расширенного фильтра;

Использование Формы данных.

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

Применение Автофильтра.

Это средство включается командой Данные ®Фильтр®Автофильтр. При этом справа от имени поля появится кнопка раскрытия списка в котором находятся следующие значения: Все; Первые 10; Условие.

Выбор одного из элементов поля приводит к ограничению отображаемых записей списка. На экране остаются записи, в соответствующем поле которых находится выбранный элемент. Кроме того, данные можно отобрать по какому-либо условию, выбрав из списка значение Условие. В этом случае можно задать сложный критерий отбора записей. Для того чтобы снять наложенные условия фильтрации необходимо выбрать значение Все. Значение Первые 10 выводит на экран первые десять записей. Отключить автофильтр можно путем повторного выполнения команды Данные ®Фильтр®Автофильтр.

Применение расширенного фильтра.

По сравнению с Автофильтром, Расширенный фильтр позволяет задавать более сложные критерии отбора записей. Диапазон ячеек, соответствующий условию фильтрации, следует указать в поле Диапазон условий, появляющегося в результате выполнения команды Данные ® Фильтр®Расширенный фильтр.