Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа 8.
Тема: Фильтрация данных и вычисление итоговых характеристик.
Цель занятия: приобретение практических навыков в обработке табличной информации с помощью формул и встроенных функций в среде табличного процессора Microsoft Excel.
Ход работы:
Постановка задачи: С помощью табличного процессора Microsoft Excel выполнить следующие операции:
Подготовительная работа.
Примечание:
|
Уровень оценки |
Количество листов для копирования |
Обязательное для выполнения задание |
|
4 |
4 |
сортировка 1, условие 1, автофильтр 1, автофильтр 2 |
|
5 |
5 |
То же, что и на 4 + сортировка 2 |
|
6, 7 |
6 |
То же, что и на 5 + сортировка и структура |
|
7, 8 |
7 |
То же, что и на 7 + условие 2 |
|
9, 10 |
8 |
То же, что и на 7, 8 + расширенный фильтр |
|
11, 12 |
9 |
Дополнительно 1-3 сводных таблицы |
Задания для выполнения:
1) сортировка по столбцу годовой сток по возрастанию (используя кнопку ); для этого выделите любую ячейку этого столбца, а затем указанную кнопку;
2) таким же образом отсортируйте по возрастанию по столбцу Общая длина реки, а затем по убыванию - по столбцу доля площади реки.
3) Выделите ячейки с данными столбца годовой сток и отсортируйте его по убыванию, используя пункт меню Данные/Сортировка.
Отсортируйте в пределах указанного выделения и по км.куб – по убыванию. Обратите внимание на результат данной сортировки (чтобы ответить на дополнительный вопрос преподавателя) и после этого отмените последнюю сортировку.
Окончательно отсортируйте таблицу на этом листе по убыванию по столбцу Название реки. Сохраните полученный результат (выполняйте сохранение после каждого выполненного задания).
1) длина реки в Украине – по возрастанию;
2) название реки – по возрастанию.
Рассмотрите внимательно полученный результат.
На листах Условие 1 и Условие 2 зададим условное форматирование данных.
Задание 1 на условное форматирование. При выполнении заданных условий выделите указанные ячейки заданным форматом. На листе Условие 1 окрасьте в жёлтый цвет строки с реками, целиком принадлежащие Украине. Для этого выделите всю таблицу и вызовите диалоговое окно с помощью пункта меню Формат/Условное форматирование. Обратите внимание на запись условия 1: по формуле и при вводе формулы введена смешанная ссылка на первую ячейку столбца доля длины реки в Украине (см. рисунок). Формат отображения ячейки - жёлтый цвет. Таким образом поставлено условие, если в ячейке значение будет равно 100%, то река принадлежит Украине и вся строка окраситься в жёлтый цвет.
Задание 2 на условное форматирование. На листе Условие 2 необходимо задать условное форматирование ячеек столбца Общая длина реки. Необходимо окрасить в синий цвет ячейку с максимальной длиной, в красный – с минимальной и задать оранжевую заливку для рек, длина которых попадает в диапазон значений от 700 км до 1500 км. Воспользуйтесь функциями МИН и МАКС.
1) после списка рек, у которых значение доли длины реки меньше 100 % и во вставленной строке в столбце название реки запишите название – Реки, выходящие за пределы Украины;
2) после списка рек, у которых доля длины реки равна 100 %, соответственно назовите её Реки, лежащие в пределах Украины.
Для создания структуры листа выполните следующее:
1) выделите строки до первой вставленной строки, с помощью команды Данные\Группа и структура\Сгруппировать создайте первый уровень структуры по строкам;
2) аналогично для нижележащих строк создайте ещё один уровень.
Дополнительно сгруппируйте столбцы B-E. После щелчка на всех кнопках , вы должны получить следующую структурированную таблицу (см. слева).
На листе Автофильтр 1 найдём с помощью Автофильтра 7 самых длинных рек Украины. Для этого выберите команду Данные\Фильтр\Автофильтр. В появившемся раскрывающемся списке по полю длина реки в Украине выберите команду Первые 10 и установите в открывшемся окне в команде Показать следующие критерии фильтра [7] [наибольших] [элементов]. В результате получится отфильтрованная таблица (см. справа).
На листе Автофильтр 2 с помощью Автофильтра найдите реки, общая длина которых лежит в пределах от 500 км до 1 500 км (Совет: в раскрывающемся списке выберите команду Условие).
На листе Расширенный фильтр зададим более сложные условия. Требуется найти реки, лежащие полностью в Украине, длина которых больше 500 км. Сначала сформируйте диапазон критериев поиска. Для этого скопируйте названия столбцов B3-G3 в диапазон ячеек I3-N3. Диапазон критериев должен выглядеть как на рисунке справа.
После этого вызываем окно расширенного фильтра с помощью команды Данные\Фильтр\Расширенный фильтр. Задаём соответствующие диапазоны:
Предоставьте работу преподавателю на проверку (для уровня оценок до 10 баллов).
Совет: Для построения сводной таблицы воспользуйтесь мастером сводных таблиц и диаграмм. Будьте внимательны при выделении диапазонов. При построении первых двух сводных таблиц на шаге 3 щёлкните по кнопке Параметры и снимите флажок Общая сумма по строкам. Для изменения операций над данными в уже готовой сводной таблице выделите общий итог и воспользуйтесь панелью инструментов Сводные таблицы и кнопкой Параметры поля . В открывшемся окне (см. справа) выберите нужную операцию из перечисленных.
Для высокой оценки необходимо построить следующие сводные таблицы на отдельных листах, которые следует переименовать соответственно нижеследуемому:
|
№ сводной таблицы |
Наименование листа |
Вид сводной таблицы |
|
1 |
Суммарный годовой сток |
|
|
2 |
Средняя длина реки |
|
|
3 |
Смешанный свод |
Смотри ниже таблицу (обратите внимание на ограниченное количество названий рек; оставьте столько же) |