Лабораторная работа 8 работа со списками. Использование сортировки и фильтрации данных

Цель работы:

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

Чтобы достичь максимальной эффективности в работе со списками, следует выполнять элементарные правила:

• каждый столбец должен содержать информацию одного типа;

• первая строка в списке должна содержать имена полей как в базе данных;

• количество столбцов в списке должно быть постоянным, а записи можно добавлять, удалять или переставлять;

• одна или две верхние строки списка должны содержать заголовки, описывающие содержимое расположенного ниже столбца;

• не включать в списки пустые строки и столбцы;

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

• ее размещайте данные слева и справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.

Список является аналогом таблицы базы данных, строки списка соот­ветствуют записям базы данных, столбцы списка – полям записей. Команда Данные → Сортировка позволяет упорядочить строки в списке в соответствии с содержимым определенных столбцов. Для этого:

• выделить сортируемые строки в списке или его части;

• определить требуемый порядок сортировки;

• сортировка осуществляется на том же листе;

• порядок сортировки записей указывается по значениям одного, двух или трех столбцов по возрастанию или убыванию;

• при сортировке по возрастанию упорядочение записей идет от меньшего к большему по алфавиту или в хронологическом порядке дат, но при этом имеет место приоритет: числа, текст, логические значения. Сортировка по убыванию имеет обратный порядок.

Задание 8.1.

Создать список, представленный на рис 8.1. Произвести сортировку и фильтрацию данных.

Рекомендации по выполнению:

1. Создайте на листе 1 таблицу 8.1 и сохраните ее в своей папке под именем Лабораторная работа 8.

3. Переименуйте лист 1 в Сортировка.

Рис. 8.1. Список Остатки товара на складе

2. Выполнить расчеты для полей Цена, руб., Стоимость, у.е., Стоимость, руб.

3. Скопируйте созданную таблицу на лист 2, лист 3 книги.

4. На листе 2 выделить диапазон А3:I19 (если таблица не содержит названия и дополнительных строк перед списком, то выделить любую ячейку списка).

5. Выполнить команду Данные (Сортировка и фильтр) → Сортировка. В окне Сортировка:

• в раскрывающемся списке Сортировать по выбрать Поставщик; Порядок сортировки – От А до Я;

• нажать кнопку Добавить уровень ( );

• в списке Затем по выбрать Категории, Порядок – От А до Я;

• нажать кнопку Добавить уровень ( );

• в списке Затем по выбрать Цена, у.е., Порядок – По возрастанию;

• нажать кнопку ОК для выполнения сортировки (рис. 8.2).

Рис. 8.2. Формирование порядка сортировки записей списка

6. После выполнения вышеизложенной сортировки записей получим следующие отсортированные записи в списке Остатки товаров на складе (рис. 8.3).

Рис. 8.3. Результат сортировки записей

Задание 8.2.

Отсортировать записи списка Остатки товаров на складе в порядке, который определяет пользователь.

Рекомендации по выполнению:

1. На листе 3 выделить диапазон А3:I19;

2. Выполнить команду Данные (Сортировка и фильтр) → Сортировка. В окне Сортировка в раскрывающемся списке Сортировать по выбрать Категории; Порядок – Настраиваемый список… (рис 8.4);

Рис. 8.4. Создание пользовательского порядка сортировки записей

3. В окне Список в области Элементы списка ввести отдельными строками наименования категорий товара: Напитки, Кондитерские изделия, Молочные продукты (после ввода очередного наименования нажимать клавишу Enter).

После формирования пользовательского списка (рис. 8.5) нажать кнопку Добавить.

Пользовательский список сформирован. Нажать кнопку ОК.

Рис. 8.5. Формирование пользовательского списка

4. Порядок сортировки сформирован. Нажать кнопку ОК.

Записи списка отсортировались в заданном пользователем порядке.

Задание 8.3.

С помощью Фильтра и Настраиваемого фильтра найти (отфильтровать) необходимые данные в списке по одному или двум параметрам поиска.

Рекомендации по выполнению:

1. Создать лист 4. Скопировать на лист 4 данные листа Сортировка. На листе 4 выделить диапазон А3:I19;

2. Выполнить команду Данные (Сортировка и фильтр) → Фильтр. После этого в каждом поле шапки таблицы появиться кнопка фильтра ( ), которая позволяет выполнять отбор записей.

3. Выбрать поставщика Оливария:

• щелкнуть на кнопке фильтра в поле Поставщик;

• в раскрывшемся списке необходимо выбрать Оливария и щелкнуть на кнопке ОК (рис. 8.6).

Рис. 8.6. Фрагмент окна с выбором поставщика

4. В результате выполнения фильтрации скрылись строки, которые не содержат поставщика Оливария, и на экране будет отображаться список в виде, представленном на рис. 8.7.

Рис. 8.7. Результат фильтрации списка

Задание 8.4.

Поиск записей по одному или двум условиям с применением пользовательского автофильтра. Его применение целесообразно, например, в том случае, если необходимо выбрать товары, цена которых находится в пределах от 50 000 до 80 000 руб.

Рекомендации по выполнению:

1. Создать лист 5. Скопировать на лист 5 данные листа Сортировка. На листе 5 выделить диапазон А3:I19;

2. Выполнить команду Данные (Сортировка и фильтр) → Фильтр.

3. Щелкнуть на кнопке фильтра в поле Цена, руб. В окне выбрать Числовые фильтры → Настраиваемый фильтр (рис. 8.8).

Рис. 8.8. Фрагмент окна выбора пользовательского автофильтра

Нажать кнопку ОК.

4. В окне Пользовательский автофильтр двух левых раскрывающих списках выбрать соответствующие операторы (больше или равно и меньше или равно соответственно), а в двух правых раскрывающих списках ввести требуемые значения (50 000 и 80 000 соответственно). Затем установить переключатель в положение И.

Нажать кнопку ОК для выполнения фильтрации (рис. 8.9).

Рис. 8.9. Формирование пользовательского автофильтра

В списке будут отображены записи, удовлетворяющие заданным критериям (рис. 8.10).

Рис. 8.10. Результат фильтрации списка

Задание 8.5.

Фильтрации записей по более сложным условиям отбора.

Произвести отбор записей по параметрам Кондитерские изделия и Напитки по складам 1 и 2 соответственно с помощью фильтрации Дополнительно.

Рекомендации по выполнению:

1. Создать лист 6. Скопировать на лист 6 данные листа Сортировка.

2. Вставить перед данными 4 строки.

3. Скопировать шапку таблицы в диапазон А1:I1.

В ячейку А1 ввести номер склада – 1; в ячейку А3 – 2.

В ячейку С2 ввести наименование категории – Напитки, в ячейку С3 – Кондитерские изделия (рис. 8.11).

Рис. 8.11. Фрагмент настройки данных для расширенной фильтрации

4. Выделить диапазон А7:I23. Выполнить команду Данные (Сортировка и фильтр) → Дополнительно.

В окне Расширенный фильтр установить значения фильтрации данных (рис. 8.12):

• фильтровать список на месте – ;

• Исходный диапазон – автоматически указывается выделенный диапазон в абсолютной адресации: $A$7:$I$23;

• Диапазон условий – выделить диапазон A1:I3 (отобразиться в поле в абсолютной адресации $A$1:$I$3). Нажать кнопку ОК.

Рис. 8.12. Установка значений для расширенной фильтрации

Результаты отбора записей по параметрам представлены на рис. 8.13.

Рис. 8.13. Результаты отбора записей по параметрам