Электронная таблица Excel (стр. 11 )

Поле 1	Поле 2	….	Поле N	Ü строка заголовков
				Записи
Рис. 6.22. Структура базы данных

Возможны три модели баз данных: сетевые, иерархические и реляционные. Сетевые и иерархические СУБД по­лучили наибольшее распространение на больших - и мини-ЭВМ. На ПК ис­пользуется преимущественно реляционная модель данных. Сетевые СУБД ис­пользуют модель представления данных в виде произвольного графа. В ие­рархических СУБД данные представляются в виде древовидной структуры. Реляционная модель ориентирована на представление данных в виде табли­цы. В Excel реализована реляционная база данных.

Таблица реляционной БД (рис.6.22) представляет собой двухмерный мас­сив и обладает следующими свойствами: каждый элемент таблицы - это один элемент данных, повторяющиеся группы отсутствуют; все столбцы (колонки) в таблице однородные. Это означает, что все элементы одного столбца имеют одинаковую природу, например: марка автомобиля или раз­мер заработной платы. Столбцам присвоены уникальные имена; в таблице нет одинаковых строк; в операциях с таблицей ее строки и столбцы могут просматриваться в лю­бом порядке и в любой последовательности безотносительно к их информа­ционному содержанию и смыслу.

Каждая строка таблицы – это запись, каждая колонка таблицы – поле записи. Размещение в одной строке таблицы определенных элементов данных означает установление между ними связи или отношения. Например, если база данных со­держит сведения о запасных частях к автомобилям, то в одной строке мо­гут быть помещены сведения о запасных частях к автомобилю конкретной марки. То есть, данные в одной строке связаны между собой тем, что принадлежат одной марке автомобиля. Строка таблицы с этими данными представляет собой один конкретный экземпляр отношения данного типа или его кортеж, а всю таблицу в целом называют отношением. Таким обра­зом, при описании реляционной модели данных отношением называют всю таблицу в целом как совокупность конкретных экземпляров отношения. Слова "отношения" и "реляционный" (от латинского relation - отношение) представляют собой синонимы.

Совокупность значений элементов данных, размещенных в одном столбце таблицы и определяющих некоторую характеристику или свойство объектов, описываемых строками таблицы, называют атрибутом отношения. Количество элементов данных в кортеже (количество столбцов в таблице) определяет степень отношения. Если таблица включает n столбцов, то она представляет собой отношение степени n. Количество кортежей в отноше­нии (число строк в таблице) определяют его мощность - m. Тогда общее количество элементов данных в отношении степени n будет равно n х m.

Атрибут, значение которого идентифицирует кортеж, то есть позволяет однозначно выделить его из других кортежей данного отношения, называ­ется ключевым атрибутом или просто ключом. Ключ может включать нес­колько атрибутов - составной ключ или представлять собой только часть значения атрибута - частичный ключ. В приведенном выше примере в ка­честве ключа может быть марка автомобиля, что позволяет однозначно вы­делить кортеж из всего отношения.

Программа Excel позволяет импортировать и обрабатывать данные из других баз данных, а также позволяет создавать собственные базы данных.

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

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

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

Листинг 6.22. Пример базы данных
Дата	Откуда	Вид	Количество	Объем	Цена	Стоимость
Сентябрь	Братск	Бумага	22500	45	3500
Сентябрь	Братск	Ватман	15600	31	2400
Сентябрь	Вологда	Цемент	13600	27	5800
Сентябрь	Тюмень	Клей столярный	11000	22	1200
Октябрь	Братск	Картон	12000	48	5600
Октябрь	Вологда	Плитка облицовочная	13500	27	3200

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

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

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

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

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

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

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

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

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

Вид	Количество	Объем
Бумага	22500	45
Ватман	15600	31
Цемент	13600	27
Клей столярный	11000	22
Картон	12000	48
Рис. 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
1	Наименование		Наименование	Дата_поступления	Цена
2	Телевизоры		Телевизор	01.01.2000	<200000
3	Холодильник
4	Пылесос		Наименование	Дата_поступления	Цена
5			Телевизор
6				01.01.2000
7					<200000

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

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

6.9.3. Анализ данных

Для анализа данных можно использовать команды Итоги и Сводные таблицы меню Данные.

Итоги

Команда Итоги позволяет получать сводные данные по числовым параметрам: сумму, минимум, максимум, среднее значение и другие статистические данные. Предварительно необходимо определить по какому параметру требуется группировать итоги и отсортировать данные по этому параметру. В примере на рис. 6.29, Листинг 6.24 в качестве такого параметра выбран вид продукции. Затем необходимо выделить базу данных и ввести команду Данные, Итоги. На экране появится окно диалога Промежуточные итоги (рис. 6.29). Выберем в списке “При каждом изменении в:” параметр Вид, в списке “Операция” выберем вид операции Сумма. В списке “Добавить итоги по:” установим флажки для параметров Стоимость и Количество. Установим флажки “Заменить текущие итоги” и “Итоги под данными”.

Если флажок “Итоги под данными” не установлен, то итоговые данные выводятся срзу же после шапки таблицы, в ином случае итоговые данные будут размещены в конце таблицы.

Флажок “Конец страницы между группами” позволяет разбить данные по видам и напечатать их на отдельных листах. При малом объеме данных это делать не целесообразно.

Кнопка Убрать все удаляет все итоги и переводит базу данных в исходное состояние

После установки всех параметров щелкните по кнопке ОК база данных преобразуется к виду, представленному на листинге 6.22. Под каждым видом продукции помещены промежуточные итоги, а внизу таблицы Общие итоги. Слева от таблицы появиляются кнопки для управления уровнем просмотра. При щелче по кнопке 2 на экране остаются только промежуточные и общие итоги, при щелчке по кнопке 1 – только общие итоги.

Сводные таблицы

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

На первом шаге предлагается выбрать источник данных и вид отчета. В качестве источника данных может быть база данных Excel или внешняя база данных, например, база данных Microsoft Access, Visual FoxPro, dBase-Word.

На втором шаге предлагается указать диапазон, содержащий исходные данные.

На третьем шаге можно указать место для сводной таблицы (на текущем листе или на новой странице), создать макет сводной таблицы и настроить некоторые параметры (рис. 6.30.). При выборе команды Макет открывается одноименное окно диалога (рис. 6.31). На макете обозначены четыре зоны: Страницы, Строка, Столбец, Данные. В эти зоны необходимо поместить кнопки требуемыех полей базы данных, расположенных справа от макета. Заполнение зон Страницы, Строка, Столбец, Данные осуществляется претаскиванием кнопок базы данных мышью. Поле Страница выполняет роль фильтра и позволяет выполнить группировку данных по некоторому полю базы данных, например, по дате поступления, отправителю или виду. В зону Строки помещают данные, которые должны быть помещены в строки сводной та блицы. А в зону столбец помещают имена полей, которые должны размещаться в столбцах базы данных. В зону Данные помещаюся как правило числовые поля.

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

суммирование значений данных по соответствующему полю и соответственно имя кнопки изменяется. Можно выбрать другой вид операции. Для этого щелкните дважды мышкой по кнопке Сумма по полю Количество. Для завершения операции щелкните по кнопке ОК – программа возвращается в окно шага 3 (рис.6.31). Для завершения формирования сводной таблицы щелкните по кнопке Готово. Результат приведен на Листинге 6.25. В списке Все содержится список дат поступления товаров. Список Откуда содержит список источников поступления. Эти списки снабжены флажками, которые позволяют управлять выводом информации на экран. Например, открыв список Откуда, можно снять флажки у Вологды, Мурманска, Тюмени. Тогда на экране останутся данные только по Братску. В списке дата по умолчанию флажки установлены у всех элементов списка. Можно установить флажок только для одной, интересующей Вас даты, тогда на экране будет выведен список товаров, поступивших в указанную дату.

6.9.4. Консолидация

Контрольные вопросы

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

1. Консолидация по расположению ячеек – в этом случае состав и порядок расположения данных во всех диапазонах одинаков;

2. Консолидация по категориям – в этом случае консолидация проводится на основании одинаковых подписей строк и столбцов диапазонов.

Введем команду Данные, Консолидация – на экране появится окно диалога Консолидация (рис. 6.32). Список “Функция” позволяет выбрать операцию над консолидируемыми данными. При консолидации доступны все функции статистических итогов (сумма, минимум, максимум и. д.). Строка ввода “Ссылка” позволяет указать или выбрать диапазон консолидируемых данных. “Список диапазо нов” хранит списки диапазонов консолидируе мых данных. Кнопка Добавить позволяет добавлять данные в список диапазонов через строку “ссылка”.

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

Кнопка Обзор служит для выбора данных в других книгах

Пример 6.9. Пусть требуется найти среднее значение для каждой строки данных в столбцах А и В. Результаты поместить в столбец С.

Алгоритм работы:

- выделите диапазон ячеек, равный высоте столбца А, без учета заголовков;

Листинг 6. 26
А		В		С
1		2		1,5
2		3		2,5
3		4		3,5
4		5		4,5
5		6		5,5
6		7		6,5
7		8		7,5
8		9		8,5

- введите команду Данные, Консолидация;

- активизируйте строку ввода “Ссылка”, выделите первый диапазон в столбце А и щелкните по кнопке Добавить;

- активизируйте строку ввода “Ссылка”, выделите второй диапазон в столбце В и щелкните по кнопке Добавить;

- выберите функцию “Среднее” в списке Функция;

- щелкните по кнопке ОК.

Состояние окна диалога Консолидация после ввода данных и выбора функции приведено на рис. 6.32, а результат – на листинге 6.26.