Сортировка и фильтрация данных

Практическая работа 1

Цель работы: Изучить и освоить методы сортировки и фильтрации массивов информации при проведении различных научно-исследовательских работ.

1. Откройте файл «Книга 1», лист «География», табл. 2.1.

Таблица 2.1

Мировые океаны
Название Площадь, тыс. км2 Наибольшая глубина, м
Тихий
Атлантический
Индийский
Северный Ледовитый

2.Произведите сортировку океанов по алфавиту в столбце «Название». Выберите вкладку Главная • Редактирование • Сортировка и фильтр • Сортировка от А до Я(табл. 2.38.). Во всплывающем окне выберите пункт Автоматически расширить выделенный диапазон и нажмите кнопку Сортировка (рис. 2.32.). Полученную таблицу скопируйте на Лист 9, переименуйте его в «Сорт. и фильтр».

сортировка и фильтрация данных - student2.ru

Рис. 2.32

Таблица 2.38

Сортировка по алфавиту
Название Площадь, тыс. км2 Наибольшая глубина, м
Атлантический
Индийский
Северный Ледовитый
Тихий

4. Произведите сортировку озер по алфавиту в столбце «Название» в табл. 2.2 лист «География» и скопируйте полученную таблицу на лист «Сорт. и фильтр».

Самые большие озера мира
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Байкал Азия
Балхаш Азия
Бол. Медвежье Сев. Америка
Бол. Невольничье Сев. Америка
Ван Азия
Венерн Европа
Верхнее Сев. Америка
Виктория Африка
Виннипег Сев. Америка
Гурон Сев. Америка
Иссык-Куль Азия
Каспийское море Европа
Ладожское Европа
Маракайбо Южн. Америка
Мичиган Сев. Америка
Ньяса Африка
Онежское Европа
Онтарио Сев. Америка
Таймыр Азия
Танганьика Африка
Титикака Южн. Америка
Хубсугул Азия
Чад Африка
Чудское с Псковским Европа
Эри Сев. Америка

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

6. Отфильтруйте озера Азии в таблице. Для этого выделите табл. 2.2 «Крупнейшие озера мира», выберите вкладку Главная • Редактирование • Сортировка и фильтр • Фильтр.В строке заголовков таблицы появятся флажки (рис. 2.33). Нажмите флажок в столбце «Географическое положение», чтобы открыть меню Фильтр (рис. 2.34). Уберите все галочки, кроме пункта «Азия». Выделите полученную таблицу (табл. 2.40) и скопируйте на лист «Сорт. и фильтр».

сортировка и фильтрация данных - student2.ru

Рис. 2.33

сортировка и фильтрация данных - student2.ru

Рис. 2.34

Таблица 2.40

Самые большие озера мира
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Байкал Азия
Балхаш Азия
Ван Азия
Иссык-Куль Азия
Таймыр Азия
Хубсугул Азия

7. Аналогично п. 6 отфильтруйте в табл. 2.2 «Крупнейшие озера Африки», «Крупнейшие озера Европы», «Крупнейшие озера Северной Америки». Полученный результат скопируйте на лист «Сорт. и фильтр».

8. При помощи Автофильтра выберите из табл. 2.2 озера, глубина которых больше 1000 м, скопируйте полученную таблицу (табл. 2.41) на лист «Сорт, и фильтр». Для этого выполните фильтрацию по аналогиис п. 6, в меню Фильтр(рис. 2.34) выберите Числовые фильтры • Настраиваемый фильтр.В открывшемся окне Пользовательский фильтрзадайте следующие параметры: наибольшая глубина больше 1000 м (рис. 2.35).

сортировка и фильтрация данных - student2.ru

Рис. 2.34

Таблица 2.41

Глубочайшие озера мира
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Каспийское море Европа
Танганьика Африка
Байкал Азия

9. Аналогично п. 8 выберите из табл. 2.2 озера, глубина которых больше 195 м и меньше 999 м, выделите полученную таблицу и скопируйте на лист «Сорт. и фильтр».

10. При помощи Автофильтра выберите из табл. 2.2 озера, глубина которых больше 330 м, а площадь свыше 31 250 км2. Отсортируйте по наибольшей глубине по убыванию, выделите полученную таблицу и скопируйте на лист «Сорт, и фильтр» (табл. 2.42).

Таблица 2.42

Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Байкал Азия
Танганьика Африка
Каспийское море Европа
Ньяса Африка
Верхнее Сев. Америка

11. При помощи Автофильтравыберите из табл. 2.3 «Крупнейшие реки мира» самые крупныереки Азии, выделите полученную табл. 2.43 и скопируйте на лист «Сорт. и фильтр.».

Таблица 2.43

Название Географическое положение Длина, км Площадь бассейна, км2
Янцы Азия
Обь (с Иртышом) Азия
Хуанхэ Азия
Меконг Азия
Амур (с Аргунью) Азия
Лена Азия

12. При помощи Автофильтра выберите из табл. 2.3 реки, длина которых более 4350 км и площадь бассейна больше 2350 км2, выделите полученную табл. 2.44 и скопируйте на лист «Сорт, и фильтр». Произведите сортировку по убыванию площади бассейна.

Таблица 2.3

Самые полноводные реки мира
Название Географическое положение Длина, км Площадь бассейна, км2
Янцы Азия
Юкон Сев. Америка
Хуанхэ Азия
Токанитис Южн. Америка
Сан-Франсиску Южн. Америка
Ориноко Южн. Америка
Обь (с Иртышом) Азия
Нил (с Кагерой) Африка
Миссисипи Сев. Америка
Меконг Азия
Лена Азия
Ла Плата (с Параной) Южн. Америка
Конго Африка
Дунай Европа
Волга Европа
Амур (с Аргунью) Азия
Амазонка (с Укаяли) Южн. Америка
Амазонка Южн. Америка

Таблица 2.44

Самые полноводные реки мира
Название Географическое положение Длина, км Площадь бассейна, км2
Амазонка (с Мараньон) Южн. Америка
Амазонка (с Укаяли) Южн. Америка
Конго Африка
Миссисипи Сев. Америка
Ла Плата (с Параной) Южн. Америка
Обь (с Иртышом) Азия
Нил (с Кагерой) Африка
Лена Азия

13. Сохраните файл.

Практическая работа 2

Цель работы: Выбрать по предложенным условиям необходимую информацию из таблицы 2.2 с использованием фильтра.

Порядок выполнения работы

1. Откройте файл «Книга 1», лист «География», табл. 2.2 «Крупнейшие озера мира».

2. При помощи Фильтра выберите из табл. 2.2 озера, расположенные в Северной Америке, площадью более 40 000 км2 и глубиной более 250 м.

3. Выделите столбец «Географическое положение», выберите вкладку ГлавнаяРедактированиеСортировка и фильтр • Фильтр. Нажмите флажок в столбце «Географическое положение», чтобы открыть меню Фильтр. Уберите все галочки, кроме пункта «Северная Америка» (табл. 2.45).

Таблица 2.45

Крупнейшие озера Северной Америки
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Верхнее Сев. Америка
Гурон Сев. Америка
Мичиган Сев. Америка
Бол. Медвежье Сев. Америка
Бол. Невольничье Сев. Америка
Эри Сев. Америка
Виннипег Сев. Америка
Онтарио Сев. Америка

4. Из табл. 2.45 отфильтруйте озера, площадь которых более 40 000 км2 (табл. 2.46). Измените название таблицы и скопируйте на лист «Сорт. и фильтр».

Таблица 2.46

Самые большие по площади озера Сев. Америки
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Верхнее Сев. Америка
Гурон Сев. Америка
Мичиган Сев. Америка

5. Из полученной табл. 2.46 выделите озера глубиной более 250 м (табл. 2.47) и скопируйте на лист «Сорт. и фильтр».

Таблица 2.47

Самые глубокие озера Сев. Америки
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Верхнее Сев. Америка
Мичиган Сев. Америка

6. При помощи Фильтра выберите из табл. 2.2 крупнейшие озера мира с глубиной менее 95 м и площадью более 23 000 км2 (табл. 2.48) аналогично п. 4-5 и скопируйте на лист «Сорт. и фильтр».

Таблица 2.48

Крупнейшие озера мира
Название Географическое положение Площадь, тыс. км2 Наибольшая глубина, м
Виктория Африка
Эри Сев. Америка
Виннипег Сев. Америка

7. При помощи Фильтра выберите из табл. 2.3 крупнейшие реки Африки и Азии с бассейном больше 990 км2 и длинной больше 5000 м (табл. 2.49) и скопируйте на лист «Сорт. и фильтр».

Таблица 2.3

Самые полноводные реки мира
Название Географическое положение Длина, км Площадь бассейна, км2
Янцы Азия
Юкон Сев. Америка
Хуанхэ Азия
Токанитис Южн. Америка
Сан-Франсиску Южн. Америка
Ориноко Южн. Америка
Обь (с Иртышом) Азия
Нил (с Кагерой) Африка
Миссисипи Сев. Америка
Меконг Азия
Лена Азия
Ла Плата (с Параной) Южн. Америка
Конго Африка
Дунай Европа
Волга Европа
Амур (с Аргунью) Азия
Амазонка (с Укаяли) Южн. Америка
Амазонка Южн. Америка

Таблица 2.49

Крупнейшие реки Африки и Азии
Название Географическое положение Длина, км Площадь бассейна, км2
Нил (с Кагерой) Африка
Янцы Азия
Обь (с Иртышом) Азия

8. Сохраните файл.

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

Практическая работа

Цель работы: Используя электронную таблицу в качестве базы данных, выполнить операции поиска и замены элементов таблицы и построить новые базы данных.

Порядок выполнения работы

1. На рабочем столе создайте файл «Книга 5».

2. На Лист 1 введите личные данные студентов (табл. 2.50), границы и выравнивание выполните по образцу.

Таблица 2.50

Список студентов отделения
Зачетная книжка Ф.И.О. Дата рождения Домашний адрес Телефон Группа
Алешин Т.И. 24.01.1995 ул. Попова 12/33 245-11-22 10ОЭ
Анкин И.П. 24.01.1999 ул. Жукова 4/17 736-32-21 12ОЭ
Баранов М.А. 24.01.1996 пл. Гагарина 23/51 456-61-45 11ОЭ
Гордов М.А. 24.01.1989 пр-т Кутузова 48 / 12 925-57-45 11ОЭ
Гришина О.А. 29.06.1989 пл. Маяковского 31/11 648-67-91 12ОЭ
Гулов А.Б. 01.09.1996 ул. Березова 12/15 358-24-23 12ОЭ
Гущин М.А. 04.04.1987 пл. Театральная 60 /39 750-19-61 10ОЭ
Дубов А.В. 12.05.1995 ул. Неделина 35 / 27 858-80-13 11ОЭ
Ельцин Д.О. 12.05.1990 ул. Пилюгина 127/95 428-91-63 10ОЭ
Звонков Ю.В. 12.05.1988 ул. Малыгина 22 / 72 830-77-37 11ОЭ
Ильин А.В. 29.06.1990 ул. Арбат 29/ 16 436-73-33 12ОЭ
Кашкин А.Л. 04.04.1996 пл. Маяковского 29/19 948-77-02 11ОЭ
Клюев М.В. 12.05.1988 пр-т Буденного 61 / 15 654-12-41 12ОЭ
Коротков А.В. 13.05.1996 ил. Ильича 12 / 87 232-17-05 10ОЭ
Колосов Е.Н. 29.06.1995 пл. Маяковского 10 / 17 648-43-87 10ОЭ
Любшин А.А. 05.04.1993 ул. Неделина 33/16 758-82-17 11ОЭ
Марков Г.Н. 05.04.1989 ул. Павлова 90 / 47 158-29-29 10ОЭ
Маркелов А.М. 01.09.1988 пл. Маяковского 7/10 348-65-88 12ОЭ
Молохов А.Ю. 05.04.1995 ул. Солянка 4/12 144-12-73 12ОЭ
Носов Е.Л. 05.04.1988 пл. Гагарина 33/51 256-11-90 11ОЭ
Оводов А.С. 11.03.1988 пл. Победы 4 / 34 261-12-34 12ОЭ
Обломов Н.С. 05.12.1988 ул. Паперника 14/21 154-12-96 10ОЭ
Оленев В.А. 01.09.1996 пл. Театральная 76 / 11 532-43-37 11ОЭ
Павлов Г.И. 29.06.1990 ул. Неделина 12/4 458-24-90 10ОЭ
Иванов Е.Н. 11.03.1997 пл. Маяковского 8/71 748-88-09 12ОЭ
Пушкова Н.А. 26.05.1989 ул. Беговая 45/12 858-81-17 11ОЭ
Токарева Н.С. 11.03.1995 ул. Поликарпова 41/29 428-18-41 11ОЭ
Чапаев Л.В. 01.09.1996 пр-т Мира 33/10 725-47-32 10ОЭ
Щукин М.А. 23.06.1993 ул. Мневники 81 / 28 745-56-61 12ОЭ
Яковенко Н.С. 04.04.1995 ул. Мневники 81 / 28 936-71-39 10ОЭ

3. В созданной базе данных найдите фамилию Иванов и замените ее на фамилию Петров, номер телефона 858-81-17- на номер 858-81-67, улицу Неделина – на улицу Басманная.

Выполнить данное задание можно двумя способами:

- нажмите комбинацию клавиш Ctrl+F, в появившемся окне выберите вкладку Заменить • Найти. В строку впишите данные, которые следует найти, наприме: Иванов или 858-81-17, или ул. Неделина • Заменить. В строку ниже вписать то, на что необходимо заменить: Петров или 858-81-67, или ул. Басманная;

- выберите вкладку Главная • Редактирование • Найти и выделить • Заменитьидалее заполните параметры замены аналогично первому способу.

4. Создайте табл. 2.51 по образцу.

Таблица 2.51

Ведомость сдачи экзаменов Гр. 10ОЭ
Зачетная книжка Ф.И.О. ИКТ ОИВТ Программное обеспечение Экономика Аппаратное обеспечение История Средний бал
Средний бал успеваемости группы

5. Из табл. 2.50 «Список студентов отделения» при помощи фильтра отберите студентов из группы 10ОЭ. Получившийся результат из столбцов А («Зачетная книжка») и В («Ф.И.О.») скопируйте в табл. 2.51.

6. Проставьте каждому студенту произвольно оценки по внесенным в таблицу дисциплинам, и подсчитайте средний балл. Выделите строку, выберите вкладку Главная • Редактирование • Сумма «Σ» • Среднее значение (табл. 2.51. Итоговая).

Таблица 2.51. Итоговая

Ведомость сдачи экзаменов Гр. 10ОЭ
Зачетная книжка Ф.И.О. ИКТ ОИВТ Программное обеспечение Экономика Аппаратное обеспечение История Средний бал
Алешин Т.И. 4,5
Гущин М.А. 5,0
Ельцин Д.О. 4,5
Коротков А.В. 4,7
Колосов Е.Н. 4,3
Марков Г.Н. 4,7
Обломов Н.С. 4,0
Павлов Г.И. 4,5
Чапаев Л.В. 4,3
Яковенко Н.С. 4,2
Средний бал успеваемости группы 4,5

7. На новом листе, назовите его «Стипендия», создайте табл. 2.52.

Таблица 2.52

Ведомость стипендии
Группа Зачетная книжка Ф.И.О. Средний балл Стипендия
Итого за месяц:
Итого за семестр:

8. В столбец «Группа» внести «10ОЭ».

9. Заполнить столбцы «Зачетная книжка» и «Ф.И.О.» данными из табл. 2.51. итоговая ссылками на соответствующие ячейки: в ячейку ВЗ ввести формулу =10ОЭ!А3, в ячейку С3 =10ОЭ!ВЗ. Примените автозаполнение для остальных ячеек. Столбец «Средний балл» заполните аналогично.

10. Произведите в таблице сортировку данных фамилий студентов по алфавиту.

11. Введите в столбец ЕЗ («Стипендия») формулу =ЕСЛИ(D3<4;0; ЕСЛИ (D3=5;550;400)). Стипендии не полагается студентам, у которых средний бал <4; если средний бал равен 5, то стипендия составляет 550, если средний бал равен 4, то стипендия будет 400.

12. ВЫчислите сумму, которая потребуется для выплаты стипендии студентам ежемесячно и в семестр (табл. 2.52. Итоговая).

Таблица 2.52. Итоговая

Ведомость стипендии
Группа Зачетная книжка Ф.И.О. Средний балл Стипендия
10ОЭ Алешин Т.И. 4,5
10ОЭ Гущин М.А. 5,5
10ОЭ Ельцин Д.О. 4,5
10ОЭ Коротков А.В. 4,7
10ОЭ Колосов Е.Н. 4,3
10ОЭ Марков Г.Н. 4,7
10ОЭ Обломов Н.С. 4,0
10ОЭ Павлов Г.И. 4,5
10ОЭ Чапаев Л.В. 4,3
10ОЭ Яковенко Н.С. 4,2
Итого за месяц:
Итого за семестр:

13. Самостоятельно по аналогии выполните действия для групп «11ОЭ» и «12ОЭ» (табл. 2.53 и 2.54).

Таблица 2.53

Ведомость сдачи экзаменов Гр. 10ОЭ
Зачетная книжка Ф.И.О. ИКТ ОИВТ Программное обеспечение Экономика Аппаратное обеспечение История Средний бал
Баранов В.А. 4,5
Гордов М.А. 2,6
Дубов А.В. 3,0
Звонков Ю.В. 4,1
Кашкин А.Л. 4,1
Любшин А.А. 3,8
Носов Е.Л. 4,6
Оленев В.А. 4,3
Пушкова Н.А. 3,6
Токарева Н.С. 3,6
Средний бал успеваемости группы 3,8

Таблица 2.54

Ведомость сдачи экзаменов Гр. 10ОЭ
Зачетная книжка Ф.И.О. ИКТ ОИВТ Программное обеспечение Экономика Аппаратное обеспечение История Средний бал
Анкин И.П. 3,3
Гришина О.А. 3,3
Гулов А.Б. 3,1
Ильин А.В. 4,0
Клюев М.В. 4,3
Маркелов А.М. 3,5
Молохов А.Ю. 3,3
Оводов А.С. 3,6
Иванов Е.Н. 3,5
Щукин М.А. 4,0
Средний бал успеваемости группы 3,6

14. Сохраните файл.

Наши рекомендации