Фильтр данных по условиям списка. Фильтрация данных в списке. Фильтрация с помощью формы данных

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

Задание 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 – Параметры расширенного фильтра

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

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

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

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

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

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

Автофильтр

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 4.20. Использование автофильтра для отбора записей по признаку "М" (мужчина)

Если же требуется уточнить, сколько среди этих мужчин начальников, нажмите также кнопку автофильтра в ячейке Должность и выберите в соответствующем ей списке слово Начальник. В строке состояния появится сообщение о том, сколько строк удовлетворяет заданному критерию: Найдено записей: 2 из 10 (то есть ответ будет дан сразу же). Результат изображен на рис. 4.21.

Чтобы отменить фильтрацию по определенному столбцу, достаточно открыть список автофильтра в этом столбце и выбрать пункт Все. Однако если функция фильтрации задана для нескольких столбцов, вам придется повторить эту операцию несколько раз. В этом случае лучше воспользоваться командой Данные/Фильтр/Отобразить все .

Рис. 4.21. Рабочий лист после фильтрации списка сотрудников по критерию "мужчина - начальник"

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

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

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

Автофильтр и расширенный фильтр в Excel

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:

Сразу видим результат:

Особенности работы инструмента:

1. Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
2. Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
3. Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.

У расширенного фильтра гораздо больше возможностей:

1. Можно задать столько условий для фильтрации, сколько нужно.
2. Критерии выбора данных – на виду.
3. С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.



Как сделать расширенный фильтр в Excel

Готовый пример – как использовать расширенный фильтр в Excel:

В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».

Как пользоваться расширенным фильтром в Excel

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

Основные правила:

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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