Фильтрация данных в списке - это выбор данных по заданному критерию, т.е. это операция, которая позволяет выделить нужные данные среди имеющихся.

С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простой команды - «Автофильтрация». Более сложные запросы к базе данных можно реализовать при помощи команды «Расширенный фильтр».

Автофильтрация

Для того, чтобы произвести автофильтрацию, необходимо изначально скопировать на новый лист «Автофильтрация» исходную базу данных с листа «Подсчет данных по формулам». Затем установим курсор в область списка и выполним команду «Данные» - «Фильтр» - «Автофильтр». По этой команде Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка. Щелкнув по стрелке, можно ознакомиться со списком возможных критериев выбора. Если кнопка была использована для назначения фильтра, то стрелка окрашивается в синий цвет. Существуют следующие варианты списка критериев:

· «Все» - выбираются все записи;

· «Первые 10» - в диалоговом окне «Наложение условия по списку» выбрать определенное кол-во наименьших или наибольших элементов списка, которое необходимо отобразить;

· «Значения» - будут выбраны только те записи, которые в данном столбце создают указанное значение;

· «Условие» - выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский автофильтр»;

· «Пустые» - предъявляются строки, не содержащие данные в колонке;

· «Непустые» - предъявляются только те записи, которые содержат непустые строки в колонке.

В данном случае необходимо сформировать следующие условия для проведения операции «Автофильтрация»: для поля «Льготы» нужно задать значение «Ветеран или Инвалид», а для поля «Количество членов семьи» нужно задать условие - «Больше или равно 3»». В соответствии с тем, что одновременно установлены фильтры в двух столбцах, то фильтрация записей будет выполнена по двум условиям одновременно, то есть в итоге будут отобрана льготы Ветеран и Инвалид, количество членов семьи которых больше или равно 3. В итоге были найдены квартиросъемщики, которые удовлетворяют вышеизложенным условиям. Данный результат представлен на рисунке Таблица 4 «Автофильтрация».

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

Фильтрация с помощью расширенного фильтра осуществляется с помощью команды: «Данные» - «Фильтр» - «Расширенный фильтр».

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

В «Расширенном фильтре» также как и в «Автофильтре» существует несколько вариантов видов критерия, такие как:

Критерий сравнения включает операции следующего типа:

· точного значения;

· значения, формируемого с помощью операторов отношения;

· шаблона значения, включающего символы или

Множественный критерий - критерий, формируемый в нескольких столбцах.

· Если критерии указываются в каждом столбце на одной строке, то они считаются связанными условием И.

· Если критерии записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Вычисляемый критерий - представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение «ИСТИНА» или «ЛОЖЬ».

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

Первый – автофильтр, предназначен для наиболее простых операций – выделение записей с конкретным значением (например, только выделение только записей, относящихся к Леброну Джеймсу), данных, лежащих в определенном диапазоне (или выше среднего или первую десятку) или ячеек/шрифтов определенного цвета (кстати, очень удобно). Соответственно, пользоваться им очень просто. Вам достаточно выделить те данные, которые вы хотите видеть отфильтрованными. Потом команда «Данные»/ «Фильтр». На каждой верхней ячейке верхней таблицы появится флажок списка, там уже легко разобраться с каждой командой, освоить просто и объяснять, я надеюсь, дальше не нужно, только нюансы использования автофильтра:

1) Работает только с неразрывным диапазоном. Два разных списка на одном листе отфильтровать уже не получится.

2) Самая верхняя строчка таблица автоматически назначается заголовком и в фильтрации не участвует.

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

Практическое применение в работе: например, вы работаете по этому списку, чтобы найти ошибку или проверить данные. После применения автофильтра можно поочередно проходить всю таблицу, последовательно отмечая те данные, которые уже просмотрены. Кнопками «Очистить» и «Применить повторно» определяется вид таблицы после применения условий. Потом, после окончания работы с таблицей можно вернуть шрифты обратно в первоначальный вид, не меняя самих данных. Кстати, некоторых смущает факт пропадания всех записей в таблице после применения каких-либо условий. Ну что ж, посмотрите внимательней, вы задали условия, при которых нет записей, удовлетворяющих этим условиям. Факт того, что таблица отфильтрована – выделение синим цветом номеров строк таблицы.

Теперь, переходим к расширенному фильтру. Он отличается от автофильтра более тонкой настройкой, но и большим выбором при фильтрации данных. В частности:

1) Задает столько условий, сколько необходимо.

2) Позволяет выделить ячейки с уникальными (неповторяющимися) данными. Это часто бывает нужно в работе с данными и опция отлично справляется с проблемой.

3) Позволяет копировать результат фильтра в отдельное место, не трогая основной массив.

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

Примеры условий:

1) ‘L*’ – ячейки начинающиеся с L

2) ‘>5’ - данные больше 5

Если вы удаляете из отфильтрованной таблицы строки, то они удалятся, не прихватывая с собой соседей. Т.е. если таблица отфильтрована и показывает строчки с 26-29 и 31-25, выделение всех строк и их удаление не повлечет удаление строчки 30. Это удобно, лично я часто пользуюсь этим при написании макросов. Какое преимущество это дает – часто нам достаются таблицы, которые надо привести в рабочий вид, т.е. удалить, например пустые строки. Что мы делаем: применяем фильтр к таблице, показывая только те строки, которые нам не нужны, затем удаляем всю таблицу, включая заголовок. Удаляются ненужные строки и заголовок, при этом таблица не имеет пробелов и составляет единый диапазон. А строку заголовков можно добавить простой операций копирования из загодя заготовленной области. Почему это важно при написании макросов? Неизвестно, с какой строки начинаются нежелательные данные и непонятно, с какой строки их начать удалять, удаление всей таблицы помогает быстро решить эту проблему.

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

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

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

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

В Excel доступны две команды для фильтрации списков:

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

Автофильтр

Для включения Автофильтра нужно выделить любую ячейку в таблице, затем на вкладке Данные в группе Сортировка и фильтр нажать большую кнопку :

После этого в шапке таблицы справа от каждого заголовка столбца появится кнопка со стрелкой вниз :

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

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

Фильтрация по точному значению

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

При этом все строки, в поле которых не содержится выбранное значение, скрываются.

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

Для выключения Автофильтра нужно еще раз нажать кнопку .

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

Признаки фильтрации данных

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

(находится слева внизу окна). Исходное состояние:

Сразу после фильтрации данных итог применения фильтра отображаются в левом нижнем углу строки состояния. Например, “Найдено записей: 2 из 11 ”:

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

Вид стрелок . Изменение стрелки автофильтра в отфильтрованном столбце на указывает на то, что данный столбец отфильтрован.

”

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

“” - это очень условное название. На самом деле возможности этого фильтра гораздо шире. С помощью этого фильтра можно находить или первые элементы или последние элементы (наименьшие или наибольшие числа либо даты). И, вопреки названию фильтра, получаемые результаты не ограничиваются первыми 10 элементами или последними 10 элементами. Число отображаемых элементов можно выбирать от 1 до 500.

” позволяет также отбирать данные по проценту от общего числа строк в столбце. Если в столбце содержится 100 чисел и требуется просмотреть наибольшие пятнадцать, то выбираем 15 процентов.

Фильтр можно использовать для нахождения продуктов с наибольшими или наименьшими ценами, для определения списка сотрудников, нанятых последними по времени, или для просмотра списка студентов с наилучшими или наихудшими отметками. Чтобы применить фильтр “” к столбцу данных (только числа или даты!!! ), щелкаем в столбце стрелку и выбираем пункт Числовые фильтры далее :

После этого откроется диалоговое окно Наложение условия по списку :

В диалоговом окне выбираем число (строк или процентов), наибольших или наименьших , элементов списка или % от количества элементов .

Создание собственных настраиваемых фильтров

Например, нам нужно вывести только строки с должностями, начинающимися с буквы ‘Д ’. Для этого щелкаем по стрелке автофильтра в первом столбце и выбираем Текстовые фильтры , затем пункт начинается с… :

При этом появится диалоговое окно (какой бы пункт справа вы бы ни выбирали, все равно появится одно и то же диалоговое окно.):

В поле Должность выбираем – начинается с , справа вводим д :

В окне есть подсказка:

Знак вопроса “? ” обозначает один любой знак.

Знак “* ” обозначает последовательность любых знаков.

Цель работы: выполнение сортировки данных, ознакомление со способом фильтрации записей списка, автофильтрацией, работой с формой данных.

Задание 1.

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

Методика выполнения работы

1. Откройте новую рабочую книгу и сохраните ее под именем «Сортировка» в своей рабочей папке.

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

Рисунок 5.56 – Начальная таблица с данными

3. Задайте параметры форматирования для таблицы.

Шрифт Times New Roman, размер шрифта 12 пт., для заголовков начертание полужирное и выравнивание по центру, перенос по словам, заливка серым цветом; для основной части. Напоминаем, что команды форматирования доступны на ленте Главная Þ Ячейки .

4. Для выполнения сортировки по полю фамилия покупателя поставьте курсор в любое место этого столбца и выполните команду Данные Þ Сортировка (рис. 5.51).

В открывшемся диалоговом окне в поле Сортировать по выберите «Фамилия покупателя». По возрастанию.

5. Повторите все шаги пункта 4 и задайте сортировку по «Сумме сделки», по убыванию.

6. Выполните повторно сортировку по полю «Дата сделки», по возрастанию.

7. Скопируйте таблицу на новый лист и на нем выполните сортировку по совокупности признаков. Для этого вызовите команду Данные Þ Сортировка. Установите Сортировать по фамилии в порядке возрастания, Затем по дате в порядке возрастания, В последнюю очередь, по сумме в порядке убывания.

8. С помощью команды Переименовать присвойте имена этим двум листам.

Задание 2 . Осуществите выборку информации из списка на основе команды Автофильтр.

Методика выполнения работы .

1. На листе 4 создайте таблицу и заполните ее сведениями из таблицы 5.5.

2. Переименуйте Лист4, присвоив ему имя «Автофильтр №1».

3. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные ÞФильтр. Рядом с названиями граф таблицы появятся стрелки направленные вниз, которые раскрывают список возможных значений. В графе «Пол» выберите «М» Скопируйте таблицу на лист 5 и переименуйте его в «Автофильтр №2».

4. На листе «Автофильтр №1» в графе «Пол» откройте список фильтрования и выберите «Все». Затем в графе «Дата рождения» выберите в списке фильтрования «Условие» и задайте условие (рис.5.57):

Таблица 5.5

Фамилия	Имя	Дата приема на работу	Дата рождения	Пол	Оклад	Возраст
Пашков	Игорь	16.05.74	15.03.49	М
Андреева	Анна	16.01.93	19.10.66	Ж
Ерохин	Владимир	23.10.81	24.04.51	М
Попов	Алексей	02.05.84	07.10.56	М
Тюньков	Владимир	03.11.88	19.07.41	М
Ноткин	Евгений	27.08.85	17.08.60	М
Кубрина	Марина	20.04.93	26.06.61	Ж
Гудков	Никита	18.03.98	05.04.58	М
Горбатов	Михаил	09.08.99	15.09.52	М
Быстров	Алексей	06.12.00	08.10.47	М
Крылова	Татьяна	28.12.93	22.03.68	Ж
Бершева	Ольга	14.12.01	22.12.74	Ж
Русанова	Надежда	24.05.87	22.01.54	Ж

Рисунок 5.57 – Задание условий фильтрации

5. Скопируйте отфильтрованную таблицу на лист 6 и переименуйте его в «Автофильтр №3. На листе «Автофильтр №1» отмените выборку.

Рисунок 5.58 – Пользовательский фильтр

6. В столбце «Фамилия» выберите в списке фильтрации «Условие» и задайте условие на отбор всех сотрудников, чья фамилия начинается на «Б» (рис.5.58).

7. Скопируйте отфильтрованный список на лист 7 переименуйте его в «Автофильтр №4».

8. На листе «Автофильтр №1» для графы «Фамилия» задайте «Все», а в графе «Оклад» задайте «Первые 10…» где в диалоговом окне введите «Показать 5 наибольших элементов списка».

9. Сохраните файл.

Задание 3. Выполните отбор записей, из списка используя команду Расширенный фильтр.

Методика выполнения работы.

1. Перейдите на лист 8 и переименуйте его в «Расширенный фильтр».

2. Скопируйте на этот лист таблицу из предыдущего задания (табл. 5.5), вставьте ее, начиная со строки 7. Первые 6 строк отводятся для задания условий.

3. Создадим диапазон условий. Предположим, нам требуется отобрать фамилии сотрудников, которые получают больше 5000 руб. Или чей возраст превышает 50 лет. Заполните условия, как показано на рисунке 5.59.

Рисунок 5.59 – Условия для расширенного фильтра

4. Выполните команду Данные Þ Дополнительно . Заполните диалоговое окно следующим образом (рис. 5.60):

Рисунок 5.60 – Окно параметров расширенного фильтра

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

5. Предположим нам требуется вывести только тех сотрудников, фамилии которых начинаются с букв А, Г или Н. Заполните диапазон условий (рисунок 5.61).

Рисунок 5.61 – Условия для расширенного фильтра

6. Выполните команду ДанныеÞДополнительно и заполните диалоговое окно (рисунок 5.62).

Рисунок 5.62 – Окно параметров расширенного фильтра

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

1. Выведите список всех сотрудников, заработная плата которых больше среднего уровня. Перед созданием этого фильтра введите в ячейку H2 формулу =СРЗНАЧ(F8:F20) для вычисления среднего оклада.

2. Затем в ячейку А2 вводим вычисляемое условие =F8>$H$2, ссылающееся на ячейку H2 (рисунки 5.63 и 5.64).

Рисунок 5.63 – Условия для расширенного фильтра

Рисунок 5.64 – Параметры расширенного фильтра