Сортировка и фильтрация данных
Практическая работа 1
Цель работы: Изучить и освоить методы сортировки и фильтрации массивов информации при проведении различных научно-исследовательских работ.
1. Откройте файл «Книга 1», лист «География», табл. 2.1.
Таблица 2.1
| Мировые океаны | ||
| Название | Площадь, тыс. км2 | Наибольшая глубина, м |
| Тихий | ||
| Атлантический | ||
| Индийский | ||
| Северный Ледовитый |
2.Произведите сортировку океанов по алфавиту в столбце «Название». Выберите вкладку Главная • Редактирование • Сортировка и фильтр • Сортировка от А до Я(табл. 2.38.). Во всплывающем окне выберите пункт Автоматически расширить выделенный диапазон и нажмите кнопку Сортировка (рис. 2.32.). Полученную таблицу скопируйте на Лист 9, переименуйте его в «Сорт. и фильтр».
Рис. 2.32
Таблица 2.38
| Сортировка по алфавиту | ||
| Название | Площадь, тыс. км2 | Наибольшая глубина, м |
| Атлантический | ||
| Индийский | ||
| Северный Ледовитый | ||
| Тихий |
4. Произведите сортировку озер по алфавиту в столбце «Название» в табл. 2.2 лист «География» и скопируйте полученную таблицу на лист «Сорт. и фильтр».
| Самые большие озера мира | |||
| Название | Географическое положение | Площадь, тыс. км2 | Наибольшая глубина, м |
| Байкал | Азия | ||
| Балхаш | Азия | ||
| Бол. Медвежье | Сев. Америка | ||
| Бол. Невольничье | Сев. Америка | ||
| Ван | Азия | ||
| Венерн | Европа | ||
| Верхнее | Сев. Америка | ||
| Виктория | Африка | ||
| Виннипег | Сев. Америка | ||
| Гурон | Сев. Америка | ||
| Иссык-Куль | Азия | ||
| Каспийское море | Европа | ||
| Ладожское | Европа | ||
| Маракайбо | Южн. Америка | ||
| Мичиган | Сев. Америка | ||
| Ньяса | Африка | ||
| Онежское | Европа | ||
| Онтарио | Сев. Америка | ||
| Таймыр | Азия | ||
| Танганьика | Африка | ||
| Титикака | Южн. Америка | ||
| Хубсугул | Азия | ||
| Чад | Африка | ||
| Чудское с Псковским | Европа | ||
| Эри | Сев. Америка |
5. Произведите сортировку озер в порядке уменьшения их глубины в столбце «Наибольшая глубина, м», выделите полученную таблицу и скопируйте на «Сорт. и фильтр».
6. Отфильтруйте озера Азии в таблице. Для этого выделите табл. 2.2 «Крупнейшие озера мира», выберите вкладку Главная • Редактирование • Сортировка и фильтр • Фильтр.В строке заголовков таблицы появятся флажки (рис. 2.33). Нажмите флажок в столбце «Географическое положение», чтобы открыть меню Фильтр (рис. 2.34). Уберите все галочки, кроме пункта «Азия». Выделите полученную таблицу (табл. 2.40) и скопируйте на лист «Сорт. и фильтр».
Рис. 2.33
Рис. 2.34
Таблица 2.40
| Самые большие озера мира | |||
| Название | Географическое положение | Площадь, тыс. км2 | Наибольшая глубина, м |
| Байкал | Азия | ||
| Балхаш | Азия | ||
| Ван | Азия | ||
| Иссык-Куль | Азия | ||
| Таймыр | Азия | ||
| Хубсугул | Азия |
7. Аналогично п. 6 отфильтруйте в табл. 2.2 «Крупнейшие озера Африки», «Крупнейшие озера Европы», «Крупнейшие озера Северной Америки». Полученный результат скопируйте на лист «Сорт. и фильтр».
8. При помощи Автофильтра выберите из табл. 2.2 озера, глубина которых больше 1000 м, скопируйте полученную таблицу (табл. 2.41) на лист «Сорт, и фильтр». Для этого выполните фильтрацию по аналогиис п. 6, в меню Фильтр(рис. 2.34) выберите Числовые фильтры • Настраиваемый фильтр.В открывшемся окне Пользовательский фильтрзадайте следующие параметры: наибольшая глубина больше 1000 м (рис. 2.35).
Рис. 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. Сохраните файл.