Лабораторная работа № 5. Базы данных – 2

 

Цель работы: ознакомление с методами сортировки, фильтрации и упорядочения данных. (4 часа.)

 

Сортировка данных. Записи в нашем списке расположены хаотически, что значительно затрудняет поиск нужной информации. Для сортировки данных в определенном порядке воспользуемся командой Сортировка меню Данные. После ее выбора на экране появится диалоговое окно задания критериев сортировки данных (Рис. 5.1).

 

 

Рис. 5.1

 

В этом окне можно задать несколько критериев (ключей) сортировки. Записи можно расположить в алфавитном и цифровом порядке или отсортировать по дате и т.д. Если сортируемый столбец содержит алфавитно-числовые данные, сначала выполняется сортировка по числам. Если первая строка списка содержит названия полей, она может быть исключена из сортировки: для этого следует установить в группе Идентифицировать поля по опцию Подписям (первая строка диапазона). Если же список не содержит заголовков столбцов, то надлежит активизировать опцию Обозначениям столбцов листа, чтобы первая строка диапазона была включена в сортируемый список. Список можно отсортировать не более чем по трем столбцам одновременно.

На рис. 5.2 показана таблица, отсортированная по полю Город.

 

Рис. 5.2

 

На Рис 5.3 приведены результаты сортировки по двум критериям «Город» и «Код». Причем, по критерию «Код» сортировка произведена в порядке убывания.

 

 

Рис. 5.3

 

Нажатием кнопки Параметры можно открыть диалоговое окно, в котором будет предоставлена возможность задать дополнительные параметры сортировки. Если при сортировке следует учитывать различие между строчными и прописными буквами, необходимо активизировать опцию Учитывать регистр.

По умолчанию Excel выполняет сортировку строк (активизирована опция Строки диапазона). Существует возможность сортировки столбцов в соответствии со значениями ячеек отдельной строки (или строк). Задать ее можно с помощью включения опции Столбцы диапазона команды Сортировать. После нажатия кнопки Ok и возвращения в диалоговое окно Сортировка диапазона в качестве критерия можно выбрать строки, по которым будет происходить сортировка столбцов.

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

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

Из всех средств фильтрации данных наиболее простой и удобной является функция Автофильтр. После применения данной функции на экране отображаются только те записи, которые удовлетворяют заданным критериям отбора. При вызове этой функции некоторые операции выполняются автоматически. Для применения автофильтра к нашему списку воспользуемся командой Фильтр/Автофильтр меню Данные. После щелчка на имени этой команды в первой строке рядом с названием каждого поля появится кнопка со стрелкой (Рис. 5.4). С ее помощью можно открыть ниспадающий список, содержащий все встречающиеся в столбце значения полей.

 

Рис. 5.4

Если выбрать в списке нужное значение, то на экране будут видны только те записи, которые соответствуют заданному критерию отбора. На Рис. 5.5 приведен результат применения в качестве фильтра название города «Киев»

 

 

Рис. 5.5

 

Кроме значений полей, каждый из списков содержит еще три элемента: Все, Первые 10, Условие. Элемент Все следует использовать для восстановления отображения на экране всех записей после применения фильтра. Функция автоматического представления на экране первых десяти записей списка активизируется выбором элемента Первые 10. Элемент Условие используется для формирования более сложного критерия отбора, в котором можно применять условные операторы И и ИЛИ. Выберем из нашего списка киевских клиентов только тех, у кого скидка больше или равна 10%. Для того чтобы «отсеять» ненужные записи с помощью автофильтра выберем элемент Условие из ниспадающего списка в поле Скидка. На экране появится диалоговое окно пользовательского автофильтра. (Рис 5.6)

 

 

Рис. 5.6

 

Теперь можно задать критерии, которым должны соответствовать записи. В первом списке выберем оператор сравнения «больше или равно" и выберем в поле ввода значение «10%». После нажатия кнопки Ok в списке будут представлены только значения, удовлетворяющие заданному критерию отбора (Рис. 5.7).

 

 

Рис. 5.7

 

Для восстановления на экране всех записей следует воспользоваться командой Фильтр/Показать все.

Использование расширенного (усиленного) фильтра позволяет задавать более сложные критерии отбора. Эти критерии задаются непосредственно в рабочем листе. Для этого следует вставить в рабочий лист выше списка дополнительные строки (диапазон условий), которые и будут содержать критерии отбора. В пустую строку требуется ввести или скопировать строку заголовка списка. Затем в расположенные ниже строки надлежит ввести критерии отбора. Введению условий отбора для нескольких столбцов одновременно соответствует указание критериев в одной строке диапазона условий. Например, для диапазона условий Код > 3000 и Город Киев, будут отобраны строки, содержащие Киев в столбце Город и имеющие код, превышающий 3000 (Рис. 5.8).

 

 

Рис. 5.8

 

Чтобы указать разные критерии отбора для разных столбцов, следует вводить условия отбора в ячейки, расположенные в разных строках диапазона условий. При задании критериев можно использовать также операторы сравнения, однако, нельзя применять знак равенства для указания точного соответствия поля записи заданному критерию. В этом случае (при вводе знака равенства) значение критерия будет интерпретировано программой как формула, что приведет к появлению в ячейке значения ошибки. Между диапазоном условий и фильтруемым списком необходимо оставить не менее одной пустой строки.

После задания критериев можно активизировать действие расширенного фильтра. Для этого следует выбрать команду Фильтр/Расширенный фильтр меню Данные. На экране появится диалоговое окно Расширенный фильтр, в котором следует задать основные параметры расширенного фильтра. В частности, указать диапазон фильтруемых ячеек (поле Исходный диапазон) и ячеек с критериями (поле Диапазон критериев). Указывать адрес диапазона критериев следует с помощью выделения нужного диапазона после помещения курсора в поле ввода Диапазон критериев (Рис. 5.9).

 

 

Рис. 5.9

 

 

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

Результат применения расширенного фильтра с указанными критериями приведен на Рис. 5.10.

 

 

Рис. 5.10

 

При включении опции Скопировать результат в другое место выбранные после применения расширенного фильтра записи будут скопированы в диапазон ячеек, указываемый в поле ввода Поместить результат в диапазон. Если необходимо, чтобы при фильтрации не учитывались записи с одинаковыми данными, следует включить опцию Только уникальные записи. После нажатия кнопки Ok и запуска расширенного фильтра не отвечающие критериям записи будут скрыты.