Фильтр данных по условиям списка. Фильтрация данных
Фильтр - это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям. В отличие от сортировки, фильтр не меняет порядок записей в списке. При фильтрации временно скрываются строки, которые не требуется отображать .
Строки, отобранные при фильтрации, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.
При фильтрации выбираются только необходимые данные, а остальные данные скрываются. Таким образом, отображается только то, что вы хотите увидеть, и это можно сделать одним щелчком.
При фильтрации данные никак не изменяются. Как только фильтр удален, все данные появляются снова в том же виде, в каком они были до применения фильтра.
В Excel доступны две команды для фильтрации списков:
- Автофильтр , включая фильтр по выделенному, для простых условий отбора.
- Расширенный фильтр для более сложных условий отбора.
Автофильтр
Для включения Автофильтра нужно выделить любую ячейку в таблице, затем на вкладке Данные в группе Сортировка и фильтр нажать большую кнопку :
После этого в шапке таблицы справа от каждого заголовка столбца появится кнопка со стрелкой вниз :
Щелчок по стрелке открывает меню со списком для соответствующего столбца. В списке содержатся все элементы столбца в алфавитном или числовом порядке (в зависимости от типа данных), так что можно быстро найти требуемый элемент:
Если нам нужен фильтр только для одного столбца, то можно не выводить кнопки со стрелкой для остальных столбцов. Для этого перед нажатием кнопки выделяем несколько ячеек нужного столбца вместе с заголовком.
Фильтрация по точному значению
Включаем Автофильтр , щелкаем по кнопке со стрелкой и выбираем из раскрывшегося списка какое-нибудь значение. Для того, чтобы быстро выделить все элементы столбца или снять выделение со всех элементов, щелкните по пункту (Выделить все) :
При этом все строки, в поле которых не содержится выбранное значение, скрываются.
При выполнении лабораторной работы, выделяем результат фильтрации, копируем на другое место листа и подписываем.
Для выключения Автофильтра нужно еще раз нажать кнопку .
Для отмены действия фильтра, не выходя из режима фильтрации, щелкаем по кнопке и выбираем из раскрывшегося списка пункт (Выделить все) . При этом появляются скрытые фильтром строки таблицы.
Признаки фильтрации данных
Фильтры скрывают данные. Именно для этого они и предназначены. Однако, если о фильтрации данных не известно, может возникнуть впечатление, что некоторые данные пропущены. Вы могли, например, открыть чей-нибудь отфильтрованный лист или даже забыть, что сами ранее применили фильтр. Поэтому когда на листе есть фильтры, можно обнаружить различные визуальные указатели и сообщения.
(находится слева внизу окна). Исходное состояние:
Сразу после фильтрации данных итог применения фильтра отображаются в левом нижнем углу строки состояния. Например, “Найдено записей: 2 из 11 ”:
Номера строк . По прерывистым номерам строк можно сказать, что некоторые строки скрыты, а изменившийся цвет номеров видимых строк указывает на то, что выделенные строки являются результатом отбора фильтра.
Вид стрелок . Изменение стрелки автофильтра в отфильтрованном столбце на указывает на то, что данный столбец отфильтрован.
”
“” - это еще один универсальный фильтр, который можно применять к столбцам с числами или датами.
“” - это очень условное название. На самом деле возможности этого фильтра гораздо шире. С помощью этого фильтра можно находить или первые элементы или последние элементы (наименьшие или наибольшие числа либо даты). И, вопреки названию фильтра, получаемые результаты не ограничиваются первыми 10 элементами или последними 10 элементами. Число отображаемых элементов можно выбирать от 1 до 500.
” позволяет также отбирать данные по проценту от общего числа строк в столбце. Если в столбце содержится 100 чисел и требуется просмотреть наибольшие пятнадцать, то выбираем 15 процентов.
Фильтр можно использовать для нахождения продуктов с наибольшими или наименьшими ценами, для определения списка сотрудников, нанятых последними по времени, или для просмотра списка студентов с наилучшими или наихудшими отметками. Чтобы применить фильтр “” к столбцу данных (только числа или даты!!! ), щелкаем в столбце стрелку и выбираем пункт Числовые фильтры далее :
После этого откроется диалоговое окно Наложение условия по списку :
В диалоговом окне выбираем число (строк или процентов), наибольших или наименьших , элементов списка или % от количества элементов .
Создание собственных настраиваемых фильтров
Например, нам нужно вывести только строки с должностями, начинающимися с буквы ‘Д ’. Для этого щелкаем по стрелке автофильтра в первом столбце и выбираем Текстовые фильтры , затем пункт начинается с… :
При этом появится диалоговое окно (какой бы пункт справа вы бы ни выбирали, все равно появится одно и то же диалоговое окно.):
В поле Должность выбираем – начинается с , справа вводим д :
В окне есть подсказка:
Знак вопроса “? ” обозначает один любой знак.
Знак “* ” обозначает последовательность любых знаков.
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
- Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
- Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
- Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.
У расширенного фильтра гораздо больше возможностей:
- Можно задать столько условий для фильтрации, сколько нужно.
- Критерии выбора данных – на виду.
- С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
Основные правила:
- Результат формулы – это критерий отбора.
- Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
- Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
Фильтрация данных в списке
Фильтр - это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям (условие, ограничение, заданное для отбора записей) заданным для столбца. При этом остальные строки оказываются скрытыми .
Все это упрощает процесс ввода и удаления записей, а также процесс поиска информации.
Преимущество применения фильтров состоит в том, что результат фильтрации можно скопировать в отдельную область таблицы и сразу же использовать в вычислениях
В Microsoft Excel доступны две команды для фильтрации списков:
- Автофильтр , включая фильтр по выделенному, для простых условий отбора;
- Расширенный фильтр для более сложных условий отбора.
В отличие от сортировки, фильтр не меняет порядок записей в списке. При фильтрации временно скрываются строки, которые не требуется отображать.
Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.
Автофильтр применяется в случаях, когда необходимо быстро отфильтровать данные с заданием одного или двух простых условий отбора. Эти условия накладываются на содержимое ячеек отдельного столбца.
При использовании команды Автофильтр справа от названий столбцов в фильтруемом списке появляются кнопки со стрелками.
Microsoft Excel обозначает отфильтрованные элементы голубым цветом
Когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов;
Когда к ячейкам одного столбца необходимо применить три и более условий отбора:
Когда в условии отбора используется значение, полученное в результате вычисления заданной формулы.
Расширенный фильтр может использовать операции И, ИЛИ, а также составлять вычисляемые критерии.
Обе команды вызываются в результате выбора команды Данные/Фильтр.
С помощью команды Расширенный фильтр можно фильтровать список так же, как и с помощью команды Автофильтр , но при этом не отображаются раскрывающиеся списки для столбцов.
Для поиска данных с помощью расширенного фильтра необходимо сначала подготовить данные соответствующим образом:
1 СоздатьБАЗУ ДАННЫХ (Исходный диапазон) .
2. СоздатьДиапазон условия, который задает условия поиска данных. Верхняя строка этого диапазона должна содержать заголовки полей, в точности повторяющие заголовки полей в исходном диапазоне (готовый список).
Диапазон условий должен включать хотя бы одну пустую строку ячеек сразу под заголовками. В этой строке или строках записываются условия, причем все, что записано в столбце под заголовком поля, относится именно к этому полю.
Условия всех столбцов каждой строки соединяются логической операцией «И », а затем все строки соединяются логической операцией «ИЛИ »
3 Из менюДанные выбрать командуФильтр, затемРасширенный фильтр.
В поле ввода Диапазон условий указывается интервал ячеек на рабочем листе, который содержит ваши условия. В поле ввода Поместить результат в диапазон указывается интервал ячеек, в которые копируются строки. Это поле доступно только тогда, когда выбран переключатель Скопировать на другое место .
4 Проверить правильность автоматического заполнения всех диапазонов и при необходимости откорректировать их.
Примеры сложных условий отбора:
Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, введите условия отбора в разные строки диапазона условий отбора. Например,
Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, введите эти условия отбора в отдельные строки. Например,
Для того чтобы найти строки, отвечающие более чем двум наборам условий, включите несколько столбцов с одинаковыми заголовками. Например,
Дата публикования: 2015-10-09 ; Прочитано: 205 | Нарушение авторского права страницы | Заказать написание работы
сайт - Студопедия.Орг - 2014-2020 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.002 с) ...Цель работы: выполнение сортировки данных, ознакомление со способом фильтрации записей списка, автофильтрацией, работой с формой данных.
Задание 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.
Способ 1. Автофильтр
Отфильтровать список автофильтром - значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню Данные - Фильтр - Автофильтр (Data - Filter - AutoFilter) .
В первой строке, содержащей заголовки столбцов, появятся кнопки со стрелками - кнопки автофильтра:
Для выборки записей необходимо щелкнуть по кнопке автофильтра в заголовке нужного столбца и выбрать из выпадающего списка то, что необходимо отфильтровать:
После фильтрации обратите внимание на номера выведенных строк - Excel скрыл все строки, не удовлетворяющие заданному условию, а номера отфильтрованных отобразил синим цветом, чтобы напомнить пользователю, что в данный момент он видит неполный список.
К сожалению, ни одна версия Excel, кроме Excel 2007 не позволяет выбрать из выпадающего списка более одного варианта.
Мы можем также использовать автофильтр, чтобы найти заданное число (или процент) наибольших или наименьших элементов в списке. Для этого необходимо щелкнуть по кнопке автофильтра в соответствующем поле и выбрать Первые 10 (Top 10) . Откроется окно, в котором пользователь может задать количество элементов и выбрать из выпадающих списков их тип - наибольшие или наименьшие:
Способ 2. Пользовательский автофильтр
Мы можем использовать автофильтр для выборки записей по более сложным условиям. Допустим, необходимо выбрать из списка строки, где оклад сотрудника лежит в диапазоне от 1000 до 2000 или меньше/больше определенной величины. Для выполнения такой фильтрации из выпадающего списка поля "Стоимость заказа" необходимо выбрать пункт Условие (Custom) . Появится окно :
При помощи выпадающих списков необходимо задать условие отбора и нажать кнопку ОК - Excel отобразит только те строки, которые удовлетворяют заданным условиям.
Пользовательский автофильтр способен также работать с текстом - для этого в списке условий есть операторы Начинается с... , Заканчивается на... , Содержит... , Не содержит... и т.д.
Способ 3. Расширенный фильтр
В отличие от Автофильтра и Пользовательского автофильтра - Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но (бесплатных пирожных не бывает!) требует некоторых подготовительных операций. А именно:
Например, чтобы отобрать из списка сотрудников в нашем файле потенциальные пары молодоженов, можно задать условия следующим образом:
Excel отберет всех мужчин с окладом 1000 и более, неженатых и женщин не старше 1 января 1950 года рождения, незамужних, с детьми.
Для того, чтобы отфильтровать данные по критериям из нашей таблицы, выделите любую ячейку исходного списка сотрудников и выберите в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter) . Откроется окно Расширенного фильтра , в которое необходимо ввести адрес исходного диапазона, адрес диапазона условий и указать место, куда поместить результаты фильтрации:
Флажок Только уникальные записи служит для отбора неповторяющихся данных .
