Данные - Фильтр - Отобразить все

¨ Выход из режима Автофильтра:

Данные - Фильтр - Автофильтр.

Примеры 7.3.1. Работа автофильтра.

Уникальное значение

a) Отбор записей по одному полю

Вывести данные о товарах кондитерского отдела.

Выполнение:

- Поставить курсор в БД «Магазин», включить автофильтр (Данные - Фильтр – Автофильтр);

- открыть список фильтра в поле «Отдел» (щёлчок по кнопке списка в указанном поле);

- выбрать уникальное значение – «Кондитерский»;

- обратить внимание на строку состояния в нижней части экрана: «Найдено записей: 4 из 13».

b) Отбор записей по нескольким полям – объединение критериев по принципу логического И.

Задание 2. Вывести данные об упакованных товарах кондитерского отдела.

Выполнение:

В дополнение к действиям примера а) открыть список фильтра в поле «Единица измерения» и выбрать уникальное значение – «упак.».

Проанализировать результаты и строку состояния, затем отменить фильтрацию:

Данные/Фильтр/ Показать все.

Первые 10)

Задание 3. Выбрать три самых дорогих товара.

Выполнение:

- Включить автофильтр в БД «Магазин»;

- открыть список фильтра в поле «Цена расхода»;

- выбрать пункт (Первые 10);

- в окне диалога задать параметры:

 

Рис. 16.

Окно диалога

(Первые 10)

G Примечания к примеру 2), задание 3, рис. 16.

· В первом списке окна можно задать любое число от 1 до 500;

· во 2-м списке выбирается одно из двух: «наибольших» или «наименьших»;

· в 3-м списке выбирается либо «элементов списка» либо, «% от количества элементов».

3) (Условие) -собственное условие пользователя для отбора записей задаётся в диалоговом окне «Пользовательский автофильтр».

 

 

Рис. 17. Диалоговое окно команды (Условие)

Пояснения к рис.17:

1 - название столбца, по которому формируется фильтр;

2 - поля со списками, которые содержат операторы сравнения;

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

4 - переключатели для объединения двух условий отбора одного и того же столбца по принципу логических операций И (конъюнкции) и ИЛИ (дизъюнкция)

В условиях поиска можно использовать шаблоны - символы * и ?, иногда их успешно заменяютспециальныеоператоры всписке 2 типа: «начинается с» и т.д..

Задание 4. Вывести список оставшихся товаров.

Выполнение:

Задание 5. Вывести список товаров, сумма остатка которых в интервале от 20 до 100 руб.

Выполнение: см. рис.19.

Задание 6. Поиск текстовых значений в заданном алфавитном диапазоне: выбрать товары, название которых начинается с буквы А и заканчивающиеся буквой М.

Выполнение:

4) (Пустые) – критерий отбора, по которому выводятся только записи с пустым значением поля.

(Непустые) - критерий отбора, по которому выводятся записи с непустым значением поля.

Самостоятельно!

Проверить работу пунктов 4) списка фильтра.

Задача 7.3.1. Вывести самый дешевый товар кондитерского отдела.

Задача 7.3.2. Выбрать в один список самые дешёвые (менее 20 руб.) и самые дорогие (более 100 руб.) товары.

Расширенный фильтр

В ряде практических задач достаточно возможностей автофильтра. Но профессиональный пользователь не может игнорировать более богатые возможности расширенного фильтра.

Расширенный фильтр позволяет:

ü сразу копировать отфильтрованные записи в другое место рабочего листа;

ü сохранять критерий отбора для дальнейшего использования при изменяемых данных в БД;

ü выводить отфильтрованные записи только с нужными столбцами;

ü использовать логические операции

- ИЛИ для разных столбцов,

- И и ИЛИ для одного столбца, объединяя более двух условий;

ü создавать вычисляемые критерии;

ü выводить только уникальные значения

Для работы расширенного фильтра обязательны две области: область данных и область критериев поиска.

Иногда возникает потребность в дополнительной области - области вывода результатов поиска

Область данных - собственно база данных (список), сформированная по правилам (см. 7.1.).

Область критериев поиска формируется в свободном месте рабочего листа не менее, чем из двух строк:

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

s строки или строка критериев отбора.

При создании критериев отбора следует учитывать правила:

· заголовки критериев должны точно совпадать с заголовками полей БД (столбцов), поэтому их следует копировать из базы данных в область критериев;

· если критерии находятся в одной строке, то они работают по принципу логической операции И.

· если в разных строках - по принципу операции ИЛИ.

· в критериях можно применять шаблоны * и?.

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

1) заголовок критерия может быть любым, но не заголовком столбца исходной базы данных (он может содержать любой текст);

2) если формула критерия содержит ссылки на ячейки вне БД, то они должны быть абсолютными;

3) ссылки на ячейки внутри базы данных должны быть относительными и выбираться из первой строки данных (“правило первой строки”);

4) результатом вычисляемого критерия является логическая величина ИСТИНА или ЛОЖЬ.

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

Количество строк в области вывода Excel определяет сам.

Примеры 7.3.2. Создание критериев расширенного фильтра. (Все примеры приведены на основе базы данных «Магазин» (рис.13))

1) Критерий по точному значению

Задание 1. Вывести в отдельный список цены товаров кондитерского отдела.

Выполнение:

a) В свободное место на листе скопировать заголовок критериев поиска. В данном примере: Отдел.

b) Заполнить строку критериев.

В данном примере копированием точного значения из поля «Отдел» - Кондитерский.

c) Сформировать область вывода результатов поиска -

скопировать в свободное место на листе заголовки интересующих в результате отбора полей.

В данном примере: Отдел, Наименование товара, Цена расхода.

Примечание. Заголовок «Отдел» выводить по заданию не обязательно, но для проверки работы фильтра – полезно.

d) Выполнить команду: