Алгоритм отбора строк с помощью расширенного фильтра

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

Работа со списками в Excel

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

Задание № 1

В своей книге на новом листе создать список, содержащий следующие поля

Задание № 2

Выполнить сортировку списка

Действия	Пояснения и визуализация действий
1. Выполнить сортировку списка по наименованию товара.	Выделить данные – Данные – Сортировка,в окне Сортировка диапазонавыбрать поле (Товар) и направление (по возрастанию) для сортировки, ОК
2.Просмотрев результаты, отменить сортировку.
3.Аналогично выполнить сортировку списка по следующим категориям: Наименование + Опт.цена Наименование + Опт.цена + Розн.цена Производитель + Поставщик + Товар
4.Восстановить первоначальный порядок

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

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

• Автофильтр, включает фильтр по выделенному, для простых условий отбора;
• Расширенный фильтр для более сложных условий отбора.

Задание № 3

Действия	Пояснения и визуализация действий
1.Используя Автофильтр, выделить из списка записи: содержащие производителей мониторов	Выделить данные – Данные – Фильтр –Автофильтр.Для каждого столбца появятся списки категорий и условий для автоматической фильтрации. В нашем случае надо в списке столбца Товар выбрать Монитор, в таблице отобразятся данные по всем мониторам и мы увидим производителей мониторов.
В результирующей таблице мы видим данные только по мониторам. Оценив производителей мониторов, отменим фильтрацию: в списке Товар выбрать (Все).
2. Используя Автофильтр, выделить из списка записи: содержащие поставщиков мониторов из Ростова,скопировать выделенные записи на другой лист	В этом случае Автофильтр будет осуществляться по двум столбцам: Товар и Город. Отобранные фильтром записи выделить и скопировать на другой лист.
3. Используя Автофильтр, выделить из списка записи: содержащие первые три записи самых дешевых товаров	В этом случае Автофильтр будет осуществляться по столбцу Опт.цена, в списке которого надо выбрать категорию (Первые 10…) и в окне Наложение условия по списку задать “3” значения “наименьших”
4. Используя Автофильтр, выделить из списка записи: содержащие производителей сканеров, у которых оптовая цена меньше 4100	В этом случае Автофильтр будет осуществляться по двум столбцам: Товар (Сканер) и Опт.цена (Условие…). В окне Пользовательский Автофильтр задать операцию”меньше” и в соседнее поле ввести число 4100
5. Используя Автофильтр, выделить из списка записи: содержащие список товаров, название которых начинается на букву м	В этом случае Автофильтр будет осуществляться по столбцу Товар, будет использоваться категория (Условие…)
6.Используя Автофильтр, выделить из списка записи: содержащие производителей модемов, у которых розничная цена находится в пределах от 1200 до 1400	В этом случае Автофильтр будет осуществляться по двум столбцам: Товар (Модем) и Розн.цена (Условие…)
Замечание: после выполнения каждой операции в списках, с которыми мы работали выбираем категорию (Все), чтобы вернуться к исходному состоянию. В самом конце: Данные – Фильтр – Автофильтр (отключить автофильтр).

Алгоритм отбора строк с помощью расширенного фильтра

1. Чтобы воспользоваться расширенным фильтром, необходимо сформировать диапазон условий. Диапазон условий может располагаться ниже или выше диапазона ячеек, в котором находится список (фильтруемый). Кроме того, между ним и списком должна находиться, по крайней мере, одна пустая строка.

2. Ниже заголовков столбцов диапазона условий в ячейки ввести требуемые критерии отбора.

3. Указать ячейку в списке.

4. В меню Данные выбрать команду Фильтр, а затем — команду Расширенный фильтр.

5. Чтобы показать результат фильтрации, скрыв ненужные строки, в окне Расширенный фильтр установить переключатель «Обработка» в положение Ф ильтровать список на месте. Чтобы скопировать отфильтрованные строки в другую область листа, установить переключатель «Обработка» в положение С копировать результаты в другое место, перейти в поле Поместить результат в диапазон, а затем указать верхнюю левую ячейку области вставки.

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

7. Ввести в поле Исходный диапазон ссылку на диапазон ячеек списка.

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

9. Чтобы отменить результаты фильтрации (выполненные на месте списка): Данные – Фильтр - Отобразить все

Чтобы получить точное соответствие отобранных значений заданному образцу, например, текст, следует ввести следующую формулу: ="= текст ".

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

Следующие подстановочные знаки можно использовать в условиях сравнения в фильтрах, а также при поиске и замене.

Задание № 4

Действия	Пояснения и визуализация действий
1.Используя Расширенный фильтр, разместить на другом месте листа список: производителей модемов
2. Используя Расширенный фильтр, разместить на другом месте листа список: ростовских поставщиков сканеров, у которых оптовая цена меньше 4000
3. Используя Расширенный фильтр, разместить на другом месте листа список: ростовских поставщиков товаров из США и Англии
4. Используя Расширенный фильтр, разместить на другом месте листа список: товаров с оптовой ценой от 1000 до 10000 и количеством не менее 1000.

Используя Расширенный фильтр, разместить на другом месте листа список:

а) производителей модемов

б) ростовских поставщиков сканеров, у которых оптовая цена меньше 4000:

в) ростовских поставщиков товаров из США и Англии;

г) товаров с оптовой ценой от 1000 до 10000 и количеством не менее 1000.