Создание баз данных

Список рекомендуется создавать на отдельном листе. В этом случае программа быстрее использует команды сортировки и фильтр, а также исключается возможность испортить другие данные.

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

Листинг 6.22. Пример базы данных
Дата	Откуда	Вид	Количество	Объем	Цена	Стоимость
Сентябрь	Братск	Бумага
Сентябрь	Братск	Ватман
Сентябрь	Вологда	Цемент
Сентябрь	Тюмень	Клей столярный
Октябрь	Братск	Картон
Октябрь	Вологда	Плитка облицовочная

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

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

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

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

- щелкните мышью по кнопке Критерий – открывается пустая форма;

- введите в нужное поле значение критерия.

Например, если требуется найти товары поступившие из Омска, тогда укажите в поле Откуда “Омск” – на экран будут выведена первая запись, удовлетворяющей условиям поиска. Просмотрите другие записи используя кнопки Далее и Назад.

Команда Сортировка позволяет отсортировать выделенный диапазон по значениям одного, двух или трех полей (рис. 6.24). Принцип сортировки аналогичен сортировке в Microsoft Word. Сортировку можно проводить по возрастанию или убыванию значения соответствующего поля. В случае сортировки базы данных столбцы будут фигурировать под названиями полей, а в случае сортировки простого списка – под названиями столбцов. В группе “Идентифицировать поля по” два переключателя. Если активизировать переключатель “подписями (первая строка диапазона)” то в списках будут указаны поля базы данных, а если активизировать переключатель “обозначениями столбца листа”, то в списки будут выведены заголовки столбцов таблицы. Кнопка “Параметры” позволяет указать дополнительные условия сортировки: учитывать ли регистр, сортировать по строки диапазона или столбцы диапазона.

Команда Фильтр. Команда Фильтр содержит опции Автофильтр, Отобразить все и Расширенный фильтр.

Вид	Количество	Объем
Бумага
Ватман
Цемент
Клей столярный
Картон
Рис. 6.25. База данных, режим Автофильтра

При выборе опции Автофильтр на каждом поле появляется кнопка раскрывающегося списка (Рис.6.25.). Если щелкнуть мышью по кнопке раскрывающегося списка, то открывается список параметров (Рис.6.26). Выбор требуемого параметра осуществляется щелчком мыши. Параметр Условие выводит на экран диалоговое окно Пользовательский автофильтр, которое позволяет объединить два параметра по логическому условию И или ИЛИ (Рис.6.27.). В левых раскрывающихся списках выбираются логические условия для выбора числовой или текстовой информации, в правых раскрывающихся списках выбираются из списка или вводятся значения параметров для отбора. При указании значений параметров допускается использование маски: вопросительный знак или звездочка. Вопросительный знак – заменяет один символ в текущей позиции. Звездочка заменяет все слово или его часть. После применения фильтра все записи, не удовлетворяющие заданным критериям, убираются с экрана. Для отображения всех записей необходимо применить параметр Все.

Расширенный фильтр предоставляет пользователю дополнительные возможности по выбору критериев и формированию результатов: список можно фильтровать на месте или скопировать результат в указанный диапазон; условия для выбора могут быть заданы в отдельном диапазоне рабочего листа; можно использовать при выборе только уникальные записи.

Блок критериев для расширенного фильтра содержит условия для поиска и выборки данных. Он может располагаться в любом месте электронной таблицы. Блок критериев состоит из двух или более строк. В первой строке задаются имена полей, а в последующих строках – значения критериев поиска. Критерием может быть текстовая или числовая константа, логическая функция или логическое выражение. Если критерий содержит несколько строк, то считается, что эти строки связаны функцией ИЛИ. Если строка критерия содержит несколько полей, то считается, что эти поля связаны функцией И. Примеры блоков критериев для расширенного фильтра приведены на листинге 6.27.

В блоке критериев E1:E4 (листинг 6.27) три наименования объединены по схеме ИЛИ. То есть при наличии на складе указанных товаров все они будут включены в выходной список. В блоке критериев G1:I2 три параметра объединены по схеме И. Это значит, что для выбора товара должны быть выполнены все три условия: в базе данных будет отыскиваться телевизор, поступивший на склад первого января 2000 года по цене меньше 200000 рублей. В блоке критериев G4:I7 три разных критерия объединены по схеме ИЛИ, то есть будут отыскиваться телевизор, все товары, поступившие первого января 2000 года и все товары по цене меньше 200000 рулей.

Листинг 6.21. Примеры блоков критериев расширенного фильтра
	E	F	G	H	I
	Наименование		Наименование	Дата_поступления	Цена
	Телевизоры		Телевизор	01.01.2000	<200000
	Холодильник
	Пылесос		Наименование	Дата_поступления	Цена
			Телевизор
				01.01.2000
					<200000

В поле критерия для текстовых данных могут использоваться шаблоны?, *. Символ “?” заменяет один знак в указанной позиции. Например, "ию?ь" совпадает с "июнь" и "июль". Символ * заменяет все слово или его часть. Например, "авто*" соответствует "автомобиль", "автокар" и т. д.

Блок критериев для расширенного фильтра рекомендуется задавать выше базы данных (списка), отделяя его от базы данных одной строкой.

Для получения выборки с помощью расширенного фильтра необходимо:

- создать блок критериев;

- выделить базу данных (для выделения базы данных достаточно установить курсор в любую ячейку этой базы данных);

- ввести команду Данные, Фильтр, Расширенный фильтр;

- указать в окне диалога Расширенный фильтр (рис.6.28) исходный диапазон, диапазон условий;

- если предполагается получить выборку на месте, то следует нажать клавишу ОК. Если предполагается поместить выборку в другое место, тот следует активизировать флажок “Скопировать результат в другое место” и указать в строке ввода “Поместить результаты в диапазон” начальную ячейку выходного блока данных.

При обработке баз данных полезными являются функции обработки данных:

ДСРЗНАЧ() – среднее значение элементов базы данных, соответствующих заданному критерию;

БСЧЕТ() - количество записей в базе данных, удовлетворяющих заданному критерию;

ДМАКС() – максимальное значение записей, соответствующих критерию, заданному в поле;

ДМИН() - минимальное значение записей, соответствующих критерию, заданному в поле;

БДСУМ() – сумма значений записей, соответствующих критерию, заданному в поле и другие функции.

Все функции базы данных имеют одинаковый формат:

<Имя_функции>(база_данных, поле, критерий)

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

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

Аргумент критерий соответствует ссылке на диапазон условий. Диапазон условий задается также как и при формировании блока критериев для расширенного фильтра.

Например, для вычисления суммы количества бумаги, поступившей от поставщиков (рис.6.26)следует ввести в ячейку формулу:

БДСУММ(A10:G31;D10;C4:C5)

или

БДСУММ(ПокупныеИзделия;"Количество";C4:C5)

Здесь ПокупныеИзделия – имя базы данных, “Количество” – имя поля, С4:С5 – блок критериев.

Дата добавления: 2014-01-06