Тема 5. Создание базы данных в MS Excel. Сортировка и фильтрация данных.

КРАТКАЯ СПРАВКА

Электронные таблицы позволяют осуществлять сортировку данных, т.е. производить их упорядочение. Данные (числа, текст, даты, логические значения) можно сортировать по возрастанию или убыванию. При сортировке данные выстраиваются в определенном порядке. Можно проводить вложенные сортировки, т.е. сортировать данные по нескольким столбцам; при этом задается последовательность сортировки столбцов.

Для сортировки необходимо выделить одну из ячеек с данными и ввести команду Данные/Сортировка.

В электронных таблицах можно осуществлять поиск данных (строк) в соответствии с заданными условиями. Такие условия называются фильтром.

Фильтрация – это способ поиска подмножества данных в списке. Сущность этого способа заключается в том, что для данных одного или нескольких столбцов задаются условия отбора, после этого в отфильтрованном списке отображаются только те строки, которые содержат данные, отвечающие этим условиям. В табличном процессоре MS Excel используется два способа фильтрации списков: Автофильтр для простых условий отбора и Расширенный фильтр для более сложных условий.

Чтобы отформатировать список с помощью Автофильтра, необходимо выделить одну из ячеек списка и выбрать команду Данные/Фильтр/Автофильтр. В названиях столбцов таблицы появятся раскрывающиеся списки, содержащие стандартные условия поиска.

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

Примеры выполненных  заданий:

Пример №1:

Дан список сотрудников (рис.5.1.). Осуществите сортировку этого списка по отделам и внутри каждого отдела отсортируйте по фамилиям в порядке возрастания.

Рис.5.1.  Список сотрудников

Решение:

Установите курсор внутри списка и выберите команду Данные/Сортировка.

После этого в диалоговом окне Сортировка диапазона задайте параметры согласно рисунку 5.2.

Рис.5.2.  Параметры сортировки

В результате список отсортируется  по заданным параметрам (рис.5.3.).

Рис.5.3.  Результат сортировки

Пример №2:

Из списка данных сотрудников в примере №1 выберите женщин-совместителей.

Решение:

Установите курсор внутри списка и выберите команду Данные/Фильтр/Автофильтр. В названиях столбцов таблицы появятся раскрывающиеся списки. Выберите условия фильтрации в поле (столбце) Пол – ж (рис.5.4), а затем в столбце Принадлежность к штату – С  (рис.5.5.)

Рис.5.4. Установка фильтра по полю Пол

Рис.5.5. Установка фильтра по полю Принадлежность к штату

В результате получится список, удовлетворяющий этим двум условиям (рис.5.6.)

Рис.5.6. Результат отфильтрованного списка

Пример №3:

Из списка данных сотрудников в примере №1 выберите мужчин в возрасте от 50 до 60 лет, учитывая, что задача решается в 2009 году.

Решение:

В этом случае условия отбора запишите в диапазон условий отдельной таблицей как показано на рис.5.7.

Рис.5.7.  Создание диапазона условий

Установите курсор внутри списка и выберите команду Данные/Фильтр/Расширенный фильтр. После этого в диалоговом окне Расширенный фильтр установите  параметры согласно рисунку 5.8.

Рис.5.8.  Установка параметров в окне Расширенный фильтр

В результате список отфильтруется согласно заданным параметрам (рис.5.9.).

Рис.5.9. Результат фильтрации

Пример №4:

Из списка данных в примере №1 выберите сотрудников, чей оклад ниже среднего по учреждению.

Решение:

В данном примере обратите внимание на несколько обстоятельств (рис.5.10.):

1. Столбец диапазона условий, в который должно быть записано ограничение, содержащее формулу, не должен иметь заголовок таблицы;
2. Условие записывается как формула, содержащая логическое выражение, в котором значение первой ячейки столбца сравнивается со значением, вычисляемым формулой;
3. В формуле, вычисляющей среднее значение, используются абсолютные ссылки.

Рис.5.10. Создание диапазона условий

После этого в диалоговом окне Расширенный фильтр установите  параметры согласно рисунку 5.11.

Рис.5.11. Установка параметров в окне Расширенный фильтр

В результате список отфильтруется согласно заданным параметрам (рис.5.12.).

Рис.5.12. Результат фильтрации

Скачано с www.znanio.ru