Сортировка с использованием списка

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

СОРТИРОВКА ДАННЫХ. ФИЛЬТРАЦИЯ (ВЫБОРКА) ДАННЫХ.

Цель работы:

1. Освоение приемов работы в табличном процессоре MS Excel.

2. Ознакомление с методами сортировки и фильтрации данных.

1. СОРТИРОВКА ДАННЫХ

В документе Tabl.xls создайте приведенную на рис. 1 таблицу.

Сохраните рабочую книгу в своей папке в виде файла с именем Сортировка и выборка.xls.

В документе Сортировка и выборка.xls. скопируйте рабочий лист 1 на все остальные рабочие листы (со 2 по 10).

С помощью контекстного меню переименуйте лист 1 в лист Сортировка 1.

Сортировка по первому ключу

Для списков Excel ключ – это поле.

Необходимо на листе Сортировка отсортировать данные столбца «Фамилия».

Для этого в меню Данные выбрать Сортировка. В диалоговом окне укажите в первом поле ввода «Сортировка по » поле «Фамилия», (выберите это поле в выпадающем меню «Сортировка диапазона») и выберите переключатель – « по возрастанию».

Результат:

Сначала в списке будут фигурировать студенты, чьи фамилии начинаются с буквы А и далее в алфавитном порядке.

Задание 1.Заново отсортируйте список,но чтобына этот раз фамилии начинаются с буквы Я и далее в алфавитном порядке.

Задание 2.Отсортировать поля: «Оценка», «№ группы», «Дата», «Наименование предмета» - « по возрастанию». (Каждое задание на отдельном листе).

Сортировка с использованием списка

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

Предположим, что нам требуется отсортировать «Наименование предмета» в предлагаемом порядке: Информатика, Высшая математика, История, Философия.

Для этого:

§ в меню «Сервис» выберем команду «Параметры» далее «Списки»(мы видим списки дней недели и месяцев);

§ в «Списке»выберем «Новый список»;

§ и в элементах списка введем список предметов:

Информатика,

Высшая математика,

История,

Философия

и щелкнем кнопку «Добавить» (к стандартным спискам добавится и наш, пользовательский).

§ Сортируем столбец «Наименование предмета».

Для этого в меню «Данные» выбираем команду «Сортировка», далее кнопку «Параметры» и в окне «По первому ключу» выбираем с помощью раскрывающегося списка созданный нами пользовательский список.

Задание 3.Самостоятельно разберитесь, как удалить пользовательский (созданный вами) список.

Сортировка по двум или трем ключам

Задание 4.Отсортируйте список студентов по полю «№ группы» по убыванию, а по полю «Оценка» по возрастанию.

Задание 5.Выполните сортировку «Оценка» по возрастанию, а «№ группы» по убыванию и сравните результаты.

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

Задание 6.Отсортируйте список по предметам, а внутри по возрастанию полученной оценки.

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

Сортировка с учетом регистра букв

При сортировке строк по умолчанию не учитывается, какими буквами набраны строки – прописными или строчными. Это удобно, т.к. по ошибке при заполнении таблицы «Имя» студента могли обозначать и строчными буквами.

Если регистр существенен, то при сортировке в диалоговом окне «Параметры сортировки» установите переключатель «Учитывать регистр».

2. Восстановление исходного порядка записей

Закройте Рабочую книгу (Ctrl+F4) без сохранения изменений.

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

Для создания скрытого столбца необходимо:

Выбрать столбец
В меню Форматуказать команду Столбец (Строка), далее команду Скрыть.

!Если первая строка или столбец листа являются скрытыми (например 1-ая), то выберите в меню Правкакоманду Перейти, далее в поле Ссылка введите А1 и нажмите ОК. В меню Формат установите указатель на пункте Столбец (Строка) и выберите команду Показать.

ФИЛЬТРАЦИЯ ДАННЫХ

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

В документе Сортировка и выборка.xls. добавим еще 9 листов и откроем рабочий лист 9. С помощью контекстного меню переименуйте лист 11 в лист Фильтрация 1 .

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

Отбор по одному полю

Задание 8.Покажем строки с информацией о студентах группы №12.

Для этого:

Выделяем одну из ячеек списка.
В меню «Данные» выбираем команду «Фильтр» и далее команду «Автофильтр». Если еще раз войти в меню, то около пункта «Автофильтр» мы увидим галочку, а в заголовке каждого столбца появляется кнопка- треугольник выбора фильтра для данного поля;
Щелкнем кнопку выбора в ячейке «№ группы». Из выпадающего списка выбираем группу «12»;
Кнопка в поле «№ группы» приобрела голубой цвет. В этот же цвет окрашены и номера строк. Это показывает, что список подвергался фильтрации.
В строке состояния появилось сообщение: Найдено записей 7 из 20.

Отмена режима фильтрации

1. Для отмены отбора по критерию необходимо еще раз щелкнуть кнопку в поле «№ группы» и выбрать из выпадающего списка «Все».

2. Полная отмена фильтрации:

Выбираем в меню «Данные» команду «Фильтр», далее «Автофильтр» и щелчком снимаем галочку.

Отбор по нескольким полям

Задание 9.Необходимо вывести на экран фамилии студентов группы №13 получивших оценку 2.

Проводим фильтрацию по полю «№ группы». Для этого из раскрывающегося списка выбираем 13;
Проводим фильтрацию по полю «Оценка».

Пункт раскрывающегося списка (Первые 10)применим только к числовым элементам списка.

В нашем случае для поля «Оценка»: вывести kнаибольших или наименьших значений, где k задается в диалоговом окне. Наложение условия по списку.

Задание 10.Выберем три работы, выполненные раньше других. (Дата – это количество дней, прошедших с 1 января 2009г.).

1. Выполним фильтрацию по полю «Дата»;

2. Выберем из выпадающего списка Первые 10;

В диалоговом окне «Наложение условия по списку» выберем показать 3, далее наибольших, далее элементов списка.

Задание 11. Выберем 12% работ, выполненных раньше других.