Методика по Информатике / Excel / Лавриненко Л.Г.-Работа в табличном процессоре EXCEL / Ex_Лаб4

Лабораторная работа 4

РАБОТА СО СПИСКОМ ДАННЫХ

4.1. О б щ и е п о н я т и я

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

Строки в базе данных называются записями, а столбцы – полями.

Запись – это характеристика одного объекта списка. Пример базы данных приведен в табл. 4.

Т а б л и ц а 4

МЕСЯЦ	КНИГИ	ЖУРНАЛЫ	ГАЗЕТЫ	БРОШЮРЫ
Январь	14 000р.	24 000р.	13 000р.	10 000р.
Февраль	23 000р.	30 000р.	16 000р.	9 000р.
Март	45 000р.	23 000р.	55 000р.	10 000р.
Апрель	10 000р.	31 000р.	40 000р.	32 000р.
Январь	23 000р.	11 000р.	18 000р.	34 000р.
Март	43 000р.	12 000р.	15 000р.	30 000р.

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

Для того чтобы создать базу данных, необходимо:

в пустую строку таблицы ввести название полей;

в каждую ячейку в строке, которая находится непосредственно под строкой с названиями полей, ввести данные по каждому полю.

Существуют некоторые правила организации списка:

не создавать более одного списка на листе;

создавать заголовки столбцов в первой строке списка. Далее они будут использоваться для поиска и организации данных;

все ячейки столбца должны содержать однотипные данные;

не вводить пробелы перед содержимым ячейки, поскольку они влияют на порядок сортировки и результаты выполнения операции поиска.

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

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

Для вставки записей в конец списка с помощью команды Форма необходимо:

активизировать любую ячейку списка;

в меню Данные выбрать команду Форма;

нажать кнопку Добавить;

ввести данные новой записи, используя клавишу ТАВ для перемещения между полями;

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

4.4. С о р т и р о в к а с п и с к а

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

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

4.5. Ф и л ь т р а ц и я с п и с к а

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

Порядок создания автофильтра:

указать любую ячейку в фильтруемом списке данных;

в меню Данные выбрать пункт Фильтр, затем команду Автофильтр;

открыть список значений столбца, содержащего данные, которые необходимо отфильтровать;

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

Для того чтобы отфильтровать список по нескольким значениям, встречающимся в столбце, или с использованием операторов сравнения, нужно выбрать вкладку Условие … из развернувшегося набора элемента управления.

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

4.6. П о с т р о е н и е с в о д н о й т а б л и ц ы

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

Мастер сводных таблиц состоит из четырех шагов:

1) подтверждение построения таблицы на основе данных списка;

2) подтверждение диапазона исходных данных;

3) разметка таблицы:

а) можно указать имя поля для разбиения на отдельные страницы данных;

б) следует указать категории для обозначения строк, столбцов;

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

4) определение дополнительных параметров сводной таблицы.

Завершение работы мастера приводит к возникновению в рабочей книге нового листа, являющегося сводной таблицей.

При построении сводной таблицы можно воспользоваться панелью инструментов Сводная таблица, которая вызывается из меню Вид (рис. 5).

Рис. 5

Данные сводных таблиц можно обновить по мере изменения данных списка исходной таблицы с помощью команды Обновить данные на панели инструментов Сводная таблица.

Лист сводной таблицы удаляется обычным образом.

З А Д А Н И Е 4

1) Запустите программу EXCEL.

2) В собственном каталоге создайте файл с именем “lab_4.xls”.

3) Первый лист рабочей книги назовите “Сортировка данных”.

4) Создайте базу данных по образцу табл. 5.

5) Отсортируйте полученную базу данных по фамилиям в алфавитном порядке.

6) Добавьте при сортировке вторичный ключ – время регистрации.

7) С помощью автофильтра сделайте выборку клиентов, которые прожи-вают в городе Омске.

8) Сделайте выборку клиентов, которые зарегистрированы во временном промежутке 10.10.92 – 10.10.00.

Т а б л и ц а 5

Клиент	Город	Улица	Дом	Телефон	Время регистрации
Иванов	Омск	Маяковского	1	313456	01.03.90
Морозов	Москва	Тверская	2	7654325	02.03.91
Яковлев	Пермь	Прохладная	45	456378	30.09.93
Иванов	Томск	Дмитриева	44	143456	12.12.92
Кузнецов	Омск	Маяковского	3	312877	23.10.95
Селезнев	Москва	Вишневая	35	9876574	06.06.96
Кузнецов	Курган	Тверская	11	143467	22.10.97
Ястребов	Москва	Романова	87	3948574	07.03.99
Иванов	Уфа	Вернадского	5	459909	05.07.99

9) Второй лист рабочей книги назовите “Список данных”.

10) Создайте и заполните базу данных по образцу табл. 6. На другом листе создайте сводную таблицу:

поля “Год”, “Продавец” оформите полями страниц;

поле “Район” сделайте полем строк;

поле “Товар” – полем столбцов;

поле “Объем” – полем данных.

Для каждого значения в списке “Год” (см. табл. 6) создайте отдельный рабочий лист. Замените функцию Сумма, примененную к данным сводной таблицы по умолчанию, на функцию Количество значений. Поменяйте местами поля строк и столбцов.

Т а б л и ц а 6

Месяц	Год	Продавец	Товар	Район	Объём
Январь	1999	Кузнецов	Детектив	Северный	12
Февраль	1998	Морозов	Детектив	Южный	13
Март	1999	Яковлев	Детектив	Западный	11
Январь	1997	Кузнецов	Поэзия	Западный	14
Апрель	1998	Кузнецов	Поэзия	Восточный	13
Июнь	1998	Яковлев	Классика	Южный	10
Март	1999	Кузнецов	Поэзия	Южный	16
Июль	1998	Яковлев	Детектив	Западный	20
Август	1999	Морозов	Классика	Южный	15
Апрель	1998	Яковлев	Поэзия	Южный	11

11) Сохраните содержимое рабочей книги в файле на диске.

24