Если дочитаете до конца, то научитесь использовать такую полезную функцию Эксель, как фильтр . Сейчас на вполне реальном примере я покажу что такое фильтры Excel и как экономить время при работе с большими таблицами. Это совсем несложно. В конце статьи вы сможете скачать таблицу, на примере которой я здесь рассматриваю работу с фильтрами Excel.
Зачем нужны фильтры в таблицах Эксель
А затем, чтобы иметь возможность быстро отбирать только необходимые вам данные, скрывая ненужные строки таблицы. Таким образом фильтр позволяет без удаления строк таблицы Эксель просто временно скрыть их.
Скрытые при помощи фильтра строки таблицы не исчезают. Можно условно представить, что их высота становится равной нулю (про изменение высоты строк и ширины столбцов я ранее рассказывал). Таким образом, остальные строки, не скрытые фильтром, как бы "склеиваются". То, что получается в результате, и есть таблица с наложенным фильтром.
Базы данных очень удобны для хранения информации, но мы создаем их для того, чтобы получать нужную для нас справку, когда возникает подобная необходимость.
Например, нам нужно расписание железнодорожных поездов, которые отправляются в Москву в пятницу после четырех часов дня и т.п.
Поиск нужной информации осуществляется путем отбора строк, удовлетворяющих некоторому критерию. В большинстве случаев критерием отбора является равенство содержимого ячейки определенному значению.
Помимо сравнения на равенство, при отборе записей можно использовать и другие операции сравнения. Например, больше, меньше, больше или равно, меньше или равно. Использование этих операций позволяет сформулировать критерий запроса менее строго. Например, если требуется найти информацию о человеке, фамилия которого начинается с "Ку", то в качестве критерия можно использовать правило "содержимое ячейки Фамилия больше или равно Ку и содержимое ячейки Фамилия меньше Л".
Процесс выборки из таблицы строк, удовлетворяющих определенному критерию (правилу), называется фильтрацией, а критерий (условие отбора строк) - фильтром.
- Для того чтобы выбрать из таблицы строки, удовлетворяющие определенному условию, нужно:
- 1. Выделить строку таблицы, в которой находятся названия столбцов, открыть вкладку Главная -> Редактирование и в списке Сортировка и фильтр выбрать команду Фильтр. В результате в выделенной строке, рядом с названием столбцов таблицы, появятся кнопки раскрывающихся списков.
- 2. Раскрыть список, сделав щелчок в заголовке столбца, и в раскрывшемся окне сначала сбросить переключатель (Выделить все), затем выбрать значение, которое следует использовать в качестве критерия отбора строк.
В результате выполнения перечисленных выше действий на экране останутся только те строки, которые удовлетворяют условию запроса.
В качестве критерия отбора столбца, который содержит численные значения, можно указать диапазон, в котором должны находиться значения ячеек. Например, чтобы получить список расходов, сумма которых лежит в диапазоне от 100 р. до 1000 р., требуется раскрыть окно фильтра для столбца Сумма, выбрать Числовые фильтры -> между и в появившемся окне Пользовательский автофильтр ввести границы диапазона.
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
- Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
- Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
- Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.
У расширенного фильтра гораздо больше возможностей:
- Можно задать столько условий для фильтрации, сколько нужно.
- Критерии выбора данных – на виду.
- С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
Основные правила:
- Результат формулы – это критерий отбора.
- Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
- Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
Иногда таблицы в Excel содержат довольно большое количество данных, это может быть, например, перечень закупленных за год расходных материалов. А вам нужно найти среди них только данные, относящиеся к вашему подразделению. Как это сделать?
Для того чтобы из общей массы записей выбрать только часть, удовлетворяющую вашему условию, можно воспользоваться инструментом под названием фильтр. Чтобы установить фильтр, выделите мышью заголовки в шапке таблицы либо всю таблицу, затем на вкладке Главная
щелкните на кнопке Сортировка и фильтр
и в появившемся меню выберите пункт Фильтр
. В правом нижнем углу ячеек с заголовками столбцов таблицы появятся кнопки с направленной вниз стрелкой, как показано на рис. 5.4.
Если вы щелкнете мышью на такой кнопке, появится меню, в котором можно указать направление сортировки по этому столбцу, а также условия отбора записей (рис. 5.5).
Если вам, например, нужно выбрать в списке только имена, начинающиеся на букву С, щелкните на кнопке в столбце с именами, в появившемся меню раскройте подменю Текстовые фильтры и выберите пункт начинается с . Откроется окно, показанное на рис. 5.6. В поле справа от слов начинается с введите букву С. Можно ввести не одну букву, а несколько. В результате будут выбраны только те имена, которые начинаются с указанного сочетания букв, а остальные записи таблицы скроются с экрана (рис. 5.7).
Кнопка в ячейке с заголовком столбца, по которому установлен фильтр, меняется и принимает соответствующий вид. Если щелкнуть мышью на кнопке в столбце с числовыми данными, вместо пункта Текстовые фильтры появится пункт меню Числовые фильтры . При выборе такого пункта вы можете указать диапазон значений чисел для выбираемых записей (рис. 5.8). Можно выбрать все записи больше указанного вами значения или меньше.
Для рассматриваемого нами примера (см. рис. 5.4), выберем список людей, родившихся после 1973 года. Для этого щелкнем мышью на кнопке в ячейке Е1 (Год рождения), в появившемся меню раскроем подменю Числовые фильтры , как показано на рис. 5.8, и выберем пункт больше. Откроется окно, показанное на рис. 5.9. В поле справа от слова больше введем значение 1973 и щелкнем мышью на кнопке OK. В результате в списке останутся только люди, родившиеся после 1973 года (рис. 5.10).
Фильтр можно в любой момент поменять или вовсе убрать. Для этого щелкните мышью на кнопке и в появившемся меню выберите пункт Удалить фильтр из столбца .
Фильтрация данных Excel включает в себя два фильтра: автофильтр и расширенный фильтр. Предположим, у вас большой набор данных, но из всего массива вам нужно посмотреть или выделить данные, которые относятся к определенной дате, определенному человеку и др. Для этого существуют фильтры. Для тех, кто первый раз сталкивается с этим инструментом – фильтр не удаляет, а скрывает записи, которые не отвечают условиям фильтрации, которые вы им задаете.
Первый – автофильтр, предназначен для наиболее простых операций – выделение записей с конкретным значением (например, только выделение только записей, относящихся к Леброну Джеймсу), данных, лежащих в определенном диапазоне (или выше среднего или первую десятку) или ячеек/шрифтов определенного цвета (кстати, очень удобно). Соответственно, пользоваться им очень просто. Вам достаточно выделить те данные, которые вы хотите видеть отфильтрованными. Потом команда «Данные»/ «Фильтр». На каждой верхней ячейке верхней таблицы появится флажок списка, там уже легко разобраться с каждой командой, освоить просто и объяснять, я надеюсь, дальше не нужно, только нюансы использования автофильтра:
1) Работает только с неразрывным диапазоном. Два разных списка на одном листе отфильтровать уже не получится.
2) Самая верхняя строчка таблица автоматически назначается заголовком и в фильтрации не участвует.
3) Вы можете применять любые фильтры в разных столбцах, но имейте в виду, что в зависимости от порядка применения фильтров какие-то условия могут быть не применены, т.к. предыдущие фильтры уже скрыли необходимые записи. Здесь нет проблемы, эти записи бы так и так были скрыты, но если вы хотите использовать несколько наборов фильтров, то лучше начинать с тех условий, которые имеют наименьшее применение.
Практическое применение в работе: например, вы работаете по этому списку, чтобы найти ошибку или проверить данные. После применения автофильтра можно поочередно проходить всю таблицу, последовательно отмечая те данные, которые уже просмотрены. Кнопками «Очистить» и «Применить повторно» определяется вид таблицы после применения условий. Потом, после окончания работы с таблицей можно вернуть шрифты обратно в первоначальный вид, не меняя самих данных. Кстати, некоторых смущает факт пропадания всех записей в таблице после применения каких-либо условий. Ну что ж, посмотрите внимательней, вы задали условия, при которых нет записей, удовлетворяющих этим условиям. Факт того, что таблица отфильтрована – выделение синим цветом номеров строк таблицы.
Теперь, переходим к расширенному фильтру. Он отличается от автофильтра более тонкой настройкой, но и большим выбором при фильтрации данных. В частности:
1) Задает столько условий, сколько необходимо.
2) Позволяет выделить ячейки с уникальными (неповторяющимися) данными. Это часто бывает нужно в работе с данными и опция отлично справляется с проблемой.
3) Позволяет копировать результат фильтра в отдельное место, не трогая основной массив.
Итак, основное отличие в работе с этим фильтром заключается, что предварительно нам нужно подготовить таблицу условий. Делается она просто. Копируются заголовки основной таблицы и вставляются в удобное нам место (предлагаю выше основной таблицы). В этой таблице должно быть столько строчек, чтобы после определения условий не залезть на основную таблицу.
Примеры условий:
1) ‘L*’ – ячейки начинающиеся с L
2) ‘>5’ - данные больше 5
Если вы удаляете из отфильтрованной таблицы строки, то они удалятся, не прихватывая с собой соседей. Т.е. если таблица отфильтрована и показывает строчки с 26-29 и 31-25, выделение всех строк и их удаление не повлечет удаление строчки 30. Это удобно, лично я часто пользуюсь этим при написании макросов. Какое преимущество это дает – часто нам достаются таблицы, которые надо привести в рабочий вид, т.е. удалить, например пустые строки. Что мы делаем: применяем фильтр к таблице, показывая только те строки, которые нам не нужны, затем удаляем всю таблицу, включая заголовок. Удаляются ненужные строки и заголовок, при этом таблица не имеет пробелов и составляет единый диапазон. А строку заголовков можно добавить простой операций копирования из загодя заготовленной области. Почему это важно при написании макросов? Неизвестно, с какой строки начинаются нежелательные данные и непонятно, с какой строки их начать удалять, удаление всей таблицы помогает быстро решить эту проблему.
