Работа с электронной таблицей как с базой данных. Сортировка и фильтрация данных

Цели работы:

- познакомиться с использованием электронной таблицы как базы данных;

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

- научиться производить сортировку информации.

Методические указания.

ЗАДАНИЕ 1. Заполните таблицу, содержащую информацию о планетах солнечной системы и сохраните ее под именем lab6.xls.

Таблица 7.1.

ПЛАНЕТЫ СОЛНЕЧНОЙ СИСТЕМЫ
Планета	Период	Расстояние	Диаметр	Масса	Спутники
Солнце
Меркурий	0,211		4,9	0,32
Венера	0,615		12,1	4,86
Земля			12,8
Марс	1,881		6,8	0,61
Юпитер	11,86		142,6	1906,98
Сатурн	29,46		120,2	570,9
Уран	84,01			87,24
Нептун	164,8		50,2	103,28
Плутон	247,7		2,8	0,1

Единицы измерения, используемые в таблице:

- период обращения по орбите, в земных городах;

- среднее расстояние от Солнца, млн. км;

- экваториальный диаметр, тыс. км;

- масса - *10²⁴ кг.

Основные понятия баз данных. Область таблицы A2:F12 можно рассматривать как базу данных. Столбцы А, В, С, D, Е, F этой таблицы называются полями, а строки 3-12 называются записями. Область A2:F2 содержит имена по­лей.

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

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

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

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

Основная функция любой базы данных - поиск информации по определенным критериям. С уве­личением количества записей поиск определенной информации затрудняется. Excel позволяет упро­стить этот процесс путем фильтрации данных.

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

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

ЗАДАНИЕ 2. С использованием Автофильтра осуществить поиск планет, начинающихся на букву «С» или букву «Ю» с массой менее 600*10²⁴ кг.

2.1. Выполните команды: Данные, Фильтр, Автофильтр. На полях появились кнопки.

2.2. Нажмите на кнопку на поле Планета. Выберите пункт Условие.

2.3. В диалоговом окне Пользовательский автофильтр задайте критерии и нажмите кнопку ОК.

Проверьте! В базе данных остались планеты: Солнце, Юпитер, Сатурн.

2.4. Нажмите на кнопку в поле Масса. Выберите пункт Условие.

2.5. В диалоговом окне задайте критерий: меньше 600.

Проверьте! Остался только Сатурн.

2.6. Выполните команды меню: Данные, Фильтр, Отобразить все.

ЗАДАНИЕ 3.

3.1. осуществите поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу

менее 4*10²⁴ кг (Меркурий, Марс, Плутон);

3.2. Осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*10²⁴ кг, а также не более 2 спутников (Венера, Земля, Нептун).

ЗАДАНИЕ 4. С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.

Расширенный фильтр. При использовании Расширенного фильтра необходимо сначала определить (создать) три облас­ти (табл. 7.2):

§ интервал списка - это область базы данных (А2:F12);

§ интервал критериев - это область, где задаются критерии фильтрации (A14:F15);

§ интервал извлечения - это область, в которой будут появляться результаты фильтрации (A17:F21).

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

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

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

4.1. Создайте интервал критериев и интервал извлечения (см. табл. 7.2).

4.2. Запишите критерии поиска в интервале критериев (см. табл. 7.2).

4.3. Поместите курсор в область базы данных.

4.4. Выполните команды: Данные, Фильтр, расширенный фильтр.

4.5. В диалоговом окне Расширенный фильтр установите переключатель [●] - копировать на другое место.

4.6. Проверьте правильность задания интервалов. Нажмите кнопку ОК.

Проверьте!Найдены планеты Юпитер, Сатурн, Уран, Нептун.

Таблица 7.2.

	а	b	c	d	e	f
	ПЛАНЕТЫ СОЛНЕЧНОЙ СИСТЕМЫ
	Планета	Период	Расстояние	Диаметр	Масса	Спутники
	Солнце
	Меркурий	0,211		4,9	0,32
	Венера	0,615		12,1	4,86
	Земля			12,8
	Марс	1,881		6,8	0,61
	Юпитер	11,86		142,6	1906,98
	Сатурн	29,46		120,2	570,9
	Уран	84,01			87,24
	Нептун	164,8		50,2	103,28
	Плутон	247,7		2,8	0,1
	Планета	Период	Расстояние	Диаметр	Масса	Спутники
		>10				>=2
	Планета	Период	Расстояние	Диаметр	Масса	Спутники
	Юпитер	11,86		142,6	1906,98
	Сатурн	29,46		120,2	570,9
	Уран	84,01			87,24
	Нептун	164,8		50,2	103,28

ЗАДАНИЕ 5. Сохраните результаты в файле planeta.xls.

ЗАДАНИЕ 6. С использованием Расширенного фильтра самостоятельно:

6.1. Найдите планеты, имеющие период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км (Уран, Плутон).

6.2. Осуществите поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км и имеющих больше чем один спутник (Сатурн, Уран, Нептун).

ЗАДАНИЕ 7. Отсортируйте данные в таблице в порядке убывания количества спутников.

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

ЗАДАНИЕ 8. Отсортируйте данные в таблице в алфавитном порядке названий планет.

ЗАДАНИЕ 10. Отсортируйте данные в порядке возрастания их расстояния от Солнца.

Лабораторная работа № 8