Как осуществить поиск в списке, используя Автофильтр

Для поиска Автофильтром следует выполнить следующие шаги:

• Вызвать на экран список.

• Выбрать из меню команду Данные, Фильтр, Автофильтр. На экране: в каждом имени поля появилась стрелка.

• Курсор на стрелку того поля, по которому хотим произвести поиск. Щёлкнуть левой кнопкой мыши. На экране: перечень значений всех записей в этом поле.

• Ввести значение, по которому нужно выполнить поиск. На экране: все записи, удовлетворяющие введённому значению.

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

• Ввести необходимый критерий поиска. На экране: результат поиска.

• Такой поиск можно последовательно выполнять по нескольким полям.

• Задание

1. Предположим, что нам нужно вывести на экран список рек, исследованных в 1980г. Для этого выполните следующие шаги:

А) Вызовите на экран список

Б) Выделить 1 ячейку списка.

В) Данные, Фильтр, Автофильтр. На экране: в каждом имени поля появилась стрелка.

Г) Курсор установите на стрелку того столбца, по которому хотим произвести фильтрацию (в примере: «Год исследования»)

Д) В раскрывающемся списке рядом с заголовком столбца выбираем «1980».

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

Как восстановить исходный список

• Чтобы удалить Автофильтр для конкретного столбца, раскройте соответствующий список Автофильтра и выберите в нём Все.

• Для удаления всех Автофильтров и их кнопок, выберите команду Автофильтр ещё раз, удалив, таким образом, галочку рядом с названием этой команды в подменю Фильтр из меню Данные.

• Задание

1. Восстановите исходный список.

2. Рассмотрим более сложный пример фильтрации данных. Требуется вывести список рек, для которых площадь бассейна находится в диапазоне от 400 до 1200 км² включительно. Для этого выполните следующие шаги:

а) Вызвать на экран список.

б) Выделить 1 ячейку списка.

в) Данные, Фильтр, Автофильтр. На экране: в каждом имени поля появилась стрелка.

г) Курсор установите на стрелку того столбца, по которому хотим произвести фильтрацию (в примере: «Площадь бассейна»).

д) В раскрывающемся списке рядом с заголовком столбца выберите "Условие...".

Н а экране: диалоговое окно «Пользовательский автофильтр»

е) Введите необходимый критерий.

3. Самостоятельно выведите список рек, название которых начинается на букву К и с годовым стоком меньше 100 км³. Замечание: при вводе условия на название используйте знак «*» для обозначения последовательности любых символов. Результат скопируйте на чистый лист. Назовите этот лист «Задания». На этот же лист будете размещать и все последующие выполненные задания с тем, чтобы показать преподавателю в конце занятия.

Как осуществить поиск в списке, используя Расширенный фильтр

Команда Расширенный фильтр позволяет выполнить следующие действия:

1) фильтрацию по нескольким столбцам;

2) фильтрацию по вычисляемому критерию (не рассматривается)

Рассмотрим последовательность действий при фильтрации по нескольким столбцам или полям:

• Вызвать список, в котором нужно осуществить фильтрацию

• Сформировать критерий поиска – диапазон условий. При этом учесть следующие замечания: Команда Расширенный фильтр, в отличии от Автофильтр, требует задания условий отбора записей в отдельном диапазоне рабочего листа. Поскольку при фильтрации скрываются целые строки, диапазон условий лучше поместить выше или ниже списка. Если вы предполагаете, что список со временем будет расширяться, то диапазон условий следует разместить выше списка. Диапазон условий должен содержать, по крайней мере, 2 строки. Введите один или несколько заголовков столбцов в верхней строке, а условия отбора - во вторую и последующую строки. За исключением вычисляемых условий заголовки в диапазоне условий должны точно совпадать с заголовками столбцов в списке. Для обеспечения точности эти заголовки лучше всего создавать, выделяя заголовки столбцов в списке и копируя их в верхнюю строку диапазона условий с помощью команд Копировать и Вставить из меню Правка.

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

Диапазон условий должен быть отделён от строк списка хотя бы одной пустой строкой.

В диапазоне условий можно ввести любое количество условий. Excel интерпретирует их в соответствии со следующими правилами:

1) условия на одной строке считаются соединёнными логическим оператором "И";

2) условия на разных строках считаются соединёнными логическим оператором "ИЛИ";

3) пустая ячейка в диапазоне условий означает любое значение для соответствующего поля.

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

• Ввести необходимые данные в диалоговом окне Расширенный фильтр

• Задание

1. Выведите на экран список рек, исследуемых в 1981 и 1994г и с длиной не более 2000км. Для этого выполните следующие шаги:

А) Вызовите на экран список.

Б ) Вставьте несколько строк перед первой строкой списка, сформируйте критерий фильтрации (см. ниже образец)

В) Выделите 1 ячейку списка

Г) Данные, Фильтр, Расширенный фильтр.

Д ) Введите необходимые данные.

Е) Результат покажите преподавателю.

Ещё одно важное замечание:

При работе с текстовыми условиями соблюдаются следующие правила:

1. Единственная буква означает: "Найти все значения, которые начинаются с этой буквы". Пример: при вводе К условие выполняется для следующих названий рек: Кама, Колыма, Кубань.

2. Символ ">" или "<" означает: "Найти все значения, которые находятся по алфавиту после или перед введённым текстовым значением".

3. Формула = "=текст" означает: "Найти значения, которые точно совпадают со строкой символов текст". Пример: задайте ="=Нева", чтобы найти строки, содержащие только реку Нева. Если задать Нева без формулы, Excel отберёт строки, содержащие реки Нева, Невский, Неве и т.д..

В условиях отбора расширенного фильтра разрешается использование символов шаблона, и они работают так же, как и в пользовательском Автофильтре.

• Задание

1. (используя расширенный фильтр)

1. Вывести на экран список рек, имеющих истоки с Кавказа или впадающие в Азовское море.

2. Вывести на экран список рек, названия которых начинаются с букв "К" и "П" и длина в них не менее 3000 км.

Результаты выполненных заданий скопируйте на лист «Задания».

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

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

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

Excel предлагает 14 функций - СЧЁТЕСЛИ, СУММЕСЛИ и БДФункции (функции для работы с базами данных): ДСРЗНАЧ, БСЧЁТ, БСЧЁТА, БИЗВЛЕЧЬ, ДМАКС, ДМИН, БДПРОИЗВЕД, ДСТАНДОТКЛ, ДСТАНДОТКЛП, БДСУММ, БДДИСП и БДДИСПП - которые специально предназначены для работы со списками. Каждая из этих функций возвращает информацию об элементах списка, которые удовлетворяют некоторому условию.

• Задание

1. Подсчитать годовой сток рек, впадающих в Карское море.

А) Вызовите исходный список.

Б) Вставьте три-четыре пустые строки между заголовком таблицы и названиями полей.

В) В ячейку А3 скопируйте название столбца – Куда впадает, а в ячейку А4 – условие – Карское море

Г) Курсор установите в ячейку, где должен быть результат.

Д) Выберите кнопку - вставка функции, категория – работа с базой данных, функция -БДСУММ

Е) Заполните поля функции по образцу:

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

Второй аргумент задаёт столбец, по которому производится подсчёт. В качестве второго аргумента можно использовать заголовок столбца, задаваемый в виде текстового значения (название, заключённое в кавычки), или порядковый номер столбца в списке (кавычки не обязательны).

Третий аргумент задаёт диапазон условий.

У вас должно получиться следующее:

1. Самостоятельно определить среднее значение площади бассейнов рек, впадающих в Азовское море.

2. Определить максимальную длину реки, исследуемой в 1980 году.

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

• Задание

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

А) Восстановите исходный список.