Использование фильтров для нескольких столбцов при работе с документом Excel облегчает пользователю поиск информации, оставляя в электронной таблице только те строки, которые не соответствуют заданным критериям. Я уже писал статью о том, как сделать фильтр в Excel, перейдя по ссылке, вы можете ее прочитать.
Однако использование обычного фильтра может подойти не всем пользователям. Дело в том, что с его помощью можно фильтровать данные, задав ограниченное количество условий. Если вас это не устраивает, следует воспользоваться расширенным фильтром в Excel.
Рассмотрим ваше приложение для следующей таблицы. Он находится в диапазоне ячеек A6:E31. Он содержит информацию о студентах школы.
Например, если вы хотите выбрать всех девушек ростом менее 160 см, вы можете сделать это, применив фильтр обычного столбца. Но если мы хотим подобрать детей ростом более 180 см, то с их помощью мы ничего не сможем сделать. Результаты будут отображаться для мальчиков и девочек ростом от 160 до 180 см, здесь пригодится расширенный фильтр. Надеюсь, вы понимаете разницу.
Как применить
Теперь давайте посмотрим, как использовать расширенный фильтр в Excel. Во-первых, вам нужно создать ряд условий; это делается путем копирования всех заголовков столбцов в другое место на листе.
Их лучше копировать над таблицей, иначе они тоже могут попасть под фильтр, например, если поставить их рядом с ней. Также обратите внимание, что между условиями и основной информацией должна быть хотя бы одна пустая строка.
Условия устанавливаются следующим образом. Пусть, к примеру, все девочки учатся в 9-м классе. Заполните необходимые ячейки в диапазоне условия. Затем выберите любую ячейку в основной таблице, перейдите на вкладку «Данные» и нажмите кнопку «Дополнительно» в группе «Сортировка и фильтрация» .
Откроется диалоговое окно «Расширенный фильтр». В нем выберите маркером, где показать результат, в той же таблице или сделайте это в другом месте. В качестве «Исходного диапазона» выбираем наши ячейки A6:E31. «Диапазон условий» — это наши рубрики с условиями А1:Е2. Нажмите «ОК» .
Очень важно правильно задать диапазон условий. В примере это A1:E2. Если нужно добавить еще одно условие, оно станет A1:E3 и так далее. В противном случае ничего не получится.
При различных условиях данные столбца, введенные в строку, обрабатываются как логическое «И». Данные на разных строках воспринимаются как логическое «ИЛИ». В примере оставляем всех девочек, а из них выбираем тех, кто в 9 классе. Если во второй строке написать «девушка» — «>170», то из таблицы также будут выбраны девочки, чей рост выше на 170см. При этом они могут учиться и в других классах, это логично «Или» .
Как удалить
Чтобы удалить его из данных таблицы, на вкладке «Данные» в группе «Сортировка и фильтр» нажмите «Очистить» .
Поместим отфильтрованные данные в другую таблицу
Рассмотрим еще один пример. Выберем девочек ростом менее 170 см и мальчиков ростом более 180 см. Отфильтрованную информацию разместим на одном листе, но в разных ячейках.
Записываем данные в диапазоне условий. Выберите любую ячейку основной таблицы и перейдите на вкладку «Данные» — «Дополнительно» .
Отмечаем маркером «Копировать результат в другое место», выделяем ячейки «Исходный диапазон» А6:Е31, в поле «Диапазон условий» вводим адрес А1:Е3. В поле «Поместить результат в диапазон» нажмите кнопку выбора ячейки и выберите нужные ячейки на листе, можно выбрать другой лист из открытой книги. Нажмите «ОК» .
Чтобы поместить результат расширенного фильтра в другой диапазон, сначала скопируйте заголовок таблицы и вставьте его туда. При выборе ячеек для отфильтрованных данных выберите этот заголовок и необходимое количество строк под ним. Если выбранных строк недостаточно, программа предупредит вас.
Результат с условиями, указанными для расширенного фильтра, представлен в другой части листа. Первоначальная информация осталась без изменений.
Теперь для дополнительной таблицы с отфильтрованными данными можно применить, например, сортировку в Excel. Подробнее о том, как сортировать данные в Excel, можно прочитать, перейдя по ссылке.
Я уверен, что вы понимаете, как использовать расширенный фильтр в Excel, чтобы выбрать нужную информацию из таблицы.
Расширенный фильтр в Excel
Если вы хотите отобразить только те данные, которые соответствуют определенному критерию или условию, вам следует использовать такую функцию, как фильтр. Как это сделать?
Стандартный метод
Начнем с рассмотрения техники создания самого обычного фильтра, а затем научимся делать продвинутый. Сначала нужно кликнуть по ячейке и нажать на кнопку «Фильтр», которая находится на вкладке «Данные”.
а
После этого в заголовках появятся стрелки.
два
В нашем случае нужно нажать на ту, что рядом с обозначением страны.
Далее появится окно, в котором нам нужно снять галочку напротив пункта «Выбрать все» и оставить только пункт «США”.
3
После нажатия кнопки «ОК» список будет автоматически отфильтрован, чтобы отображать только информацию о продажах в США.
4
Далее нужно нажать на стрелочку в соседней колонке «Квартал» и поставить галочку напротив строки «Квартал 4», удалив все остальные.
пять
Ура! После этих нехитрых манипуляций будут отображаться только товары США за 4 квартал.
6
Если вам необходимо очистить данные, то в соответствующей вкладке нужно нажать кнопку «Очистить». Если необходимо отключить фильтр, то для этого нужно еще раз нажать кнопку «Фильтр.
Расширенный фильтр
Стандартный фильтр используется очень часто. И его можно использовать для стандартных ситуаций. Но в некоторых случаях необходимо ориентироваться на большее количество критериев, учитывая при этом большое количество столбцов. И функционала штатного фильтра здесь будет явно недостаточно. Обязательно будет желание использовать инструмент, который имеет больше возможностей.
Расширенный фильтр — лишь один из примеров такого инструмента. Особенно, если он немного улучшился.
Основа
Во-первых, добавьте в таблицу несколько новых строк без информации в ячейках и сделайте заголовок таблицы вверху. Это будет диапазон, содержащий критерии для фильтра. Мы выделяем его желтым цветом, чтобы сделать процесс более наглядным.
7
Между таблицей с критериями и основной таблицей необходимо вставить хотя бы одну пустую строку. Условия отбора данных вставляются в желтую таблицу. Например, если нам нужно показать только те бананы, которые продаются в московском Ашане, и показать их продажи в третьем квартале, таблица будет выглядеть так.
8
Чтобы отфильтровать данные, вам нужно щелкнуть любую ячейку в диапазоне, который нужно отфильтровать, и открыть вкладку «Данные» на ленте. Там вы увидите кнопку «Дополнительно». Если нажать на нее, появится окно, где уже будет указан правильный диапазон. Остается только указать ячейки, в которых возникают условия.
9
Важно! Необходимо выделить только те желтые строки, которые содержат условия. Дело в том, что все ячейки в диапазоне, где указаны критерии фильтрации, не содержащие никакой информации, читаются Excel как команда показать абсолютно всю информацию. Простыми словами, отмените фильтр.
С помощью кнопки «Копировать результат в другое место» можно сделать так, чтобы фильтр отображал значения не здесь, а в отдельном диапазоне. Если нам нужно это сделать, то для этого есть поле «Поместить результат в диапазон». В случае с нашим примером эта функция использоваться не будет, поэтому просто нажимаем ОК. Отфильтрованные строки мгновенно появятся на листе.
10
Использование макроса расширенного фильтра
Большинство людей согласятся, что расширенный фильтр в его стандартном виде ужасно неудобен. И да, это действительно так. Кроме того, что нужно создать таблицу специально для условий, вручную вносить туда данные, а также открывать отдельный диалог и вводить туда диапазоны. Но если вы используете макросы, большинство операций можно автоматизировать.
Кроме того, просто используйте очень простой макрос, который запрограммирован на запуск расширенного фильтра при изменении любой ячейки в диапазоне условий. Согласитесь, что это намного удобнее. Для этого нужно вызвать контекстное меню метки этого листа и нажать на пункт «Исходный текст». Далее появится окно, куда можно вставить этот код.
Private Subworksheet_Change (Цель ByVal как диапазон)
Если nointersect(target,range(«A2:I5»)) ничего не значит, то
При ошибке Возобновить Далее
ActiveSheet.ShowAllData
Range(«A7»).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(«A1»).CurrentRegion
Естественно, вы должны вставить наиболее подходящие ячейки в конкретной ситуации. Теперь всякий раз, когда изменяется какая-либо ячейка, эта подпрограмма будет выполняться. Как только будет отредактирована ячейка, которая находится в диапазоне с условиями (то есть в пустой ячейке), все фильтры будут автоматически удалены, а также будет установлен расширенный.
Вот и все, никаких дополнительных действий не требуется. Фильтр будет автоматически адаптироваться к любым изменениям, внесенным пользователем.
Согласитесь, что теперь это намного удобнее.
Подробнее о сложных запросах
После реализации команды автоматического применения расширенного фильтра к таблице, с малейшей коррекцией условий, теперь можно узнать, как вообще работают сложные условия в расширенном фильтре.
Помимо точных совпадений, человек вправе использовать дополнительные символы, некоторые из которых позволяют более гибко настраивать поиск. Неважно, в каком падеже находятся персонажи. Чтобы было понятнее, вот таблица с подробным описанием всех возможных условий.
12
Здесь нужно учитывать такие моменты:
- Символ * означает любое количество символов, а ? — единственный.
- Числовые и текстовые запросы обрабатываются по-разному. Так, если пользователь указывает условную ячейку, в которой указана цифра 5, это не значит, что фильтр будет искать все числа, начинающиеся с этой цифры. Но если в нем указана буква B, то фильтр отберет те данные, которые содержат текст, начинающийся с нее. Простыми словами, если текст не начинается со знака =, то он эквивалентен тому, который заканчивается знаком *.
- Если используются даты, они вводятся в том же формате, что и в США. Сначала указывается месяц, затем число, затем год, а элементы перечисления разделяются дробью. Это касается даже локализованного для России Excel.
Логические условия
Большим преимуществом расширенного фильтра является возможность использования логических условий. Что это? Логическое условие — это условие, в котором используется логический оператор И или ИЛИ. Конечно, есть и другие, но они описаны выше.
Характерной чертой любой логической операции является то, что она возвращает условие «Истина» или «Ложь». В случае расширенного фильтра условие будет следующим: если некоторое условие истинно.
То есть логическое условие «И» на самом деле читается как «если критерий 1 и критерий 2 соответствуют критерию 3». Например, если яблоки и морковь стоят одинаково, то ИЛИ — аналогично, просто вместо «И» вставляется «ИЛИ». На практике это может выглядеть так. Если пользователи приобрели товары у Компании 1 или Компании 2, покажите эти покупки. В этом случае при покупке чего-либо у третьего лица оно не отображается.
На самом деле любой фильтр, каким бы простым или сложным он ни был, содержит логические условия. Но вы можете сделать его использование более гибким.
Итак, что вам нужно знать? Если условия находятся в одной строке, хотя и в разных ячейках, то они считаются связанными друг с другом логическим оператором «И».
13
В описанном примере фильтр покажет только бананы, купленные в московском Ашане в третьем квартале.
Если условия нужно связать с помощью логического оператора «ИЛИ», то их нужно писать на отдельных строках.
Например, в следующем примере фильтр будет учитывать только заказы менеджера Волиной, как на персики, купленные в Москве, так и на лук, проданный в третьем квартале в Самаре.
четырнадцать
Если вам нужно наложить более одного условия на столбец, то, как вариант, вы можете просто продублировать заголовок столбца в диапазоне критериев и написать последующие условия под ним, например, вы можете применить фильтр, чтобы отображались все транзакции, совершенные с марта до мая.
15
Поэтому, если следовать приведенным выше рекомендациям, расширенный фильтр уже не будет казаться таким уж неудобным инструментом, а набор его функций будет весьма обширным и покроет любую задачу, которую ставит перед собой пользователь.
Функция «Фильтр»
24 сентября 2018 года была введена новая функция, которая тоже умеет фильтровать данные. Он пришел вместе с инновационным обновлением, которое добавило динамические массивы в Excel. Эта функция, как и остальные шесть, доступна пользователям последней версии Microsoft Office 365.
Фактически, он предназначен для полной замены расширенной функциональности фильтров в Excel, которая потребовала немного танцев с бубном, чтобы сделать ее более или менее удобной в использовании.
Синтаксис функции следующий.
=ФИЛЬТР(массив; включать; [if_empty])
Разберем основные аргументы:
- Повышение квалификации. Это диапазон (ссылочный или именованный), который будет использоваться для фильтрации данных.
- Включи. Это массив переменных той же ширины и высоты, что и массив. Простыми словами, это условия, при которых должны отображаться конкретные значения.
- Если_пусто. Это необязательный аргумент, который указывает значение, которое будет возвращено, когда все значения во включенном массиве окажутся пустыми.
Вы можете использовать те же логические операторы, что и условия, указанные выше. При этом в работе этой функции есть ряд особенностей, которые необходимо учитывать:
- Используйте динамические массивы. Учитывая это, результат всегда будет касаться и соседних ячеек.
- Если вам нужно задать несколько условий для фильтра, вы должны использовать знак умножения во втором аргументе и заключать условия в круглые скобки.
- Чтобы использовать оператор ИЛИ, вы должны использовать знак плюс вместо знака умножения, а также заключать критерии в круглые скобки.
- Если оказывается, что в ячейке, в которую будет возвращено значение, уже есть какие-то значения, то возникает ошибка #ПЕРЕВОД! Чтобы от него избавиться, нужно удалить данные, содержащиеся в соответствующей ячейке.
Таким образом, сложные расширенные функции фильтрации могут быть реализованы с помощью простой функции Excel. Главное немного потренироваться и иметь последнюю версию Microsoft Office.
Выводы
Поэтому фильтрация данных в Excel позволяет найти среди большого разнообразия данных те, которые необходимы в определенный момент времени. Например, если в большом магазине есть список товаров, то можно отображать и анализировать только шампуни или крема, а все остальные позиции можно временно скрыть. Если вам нужно посмотреть, скажем, элитный французский алкоголь, вы можете применить сразу несколько фильтров.
Мы обнаружили, что нужно применять фильтры к спискам, а также учитывать сразу большое количество сложных критериев, чтобы отображать только нужные данные.
А в современных версиях Excel есть еще и расширенный вариант фильтра, который выражается простой формулой с очень простым синтаксисом, но при этом имеет большой функционал. И пользователь теперь может сам выбирать, какой из способов лучше всего подходит именно для его случая. Но, конечно, профессиональный пользователь Excel освоит все три способа как минимум для правильного чтения чужих таблиц.
Расширенный фильтр в Excel и примеры его возможностей
Вы можете отобразить информацию об одном или нескольких параметрах, отфильтровав данные в Excel.
Для этого есть два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не соответствующие условию. Автофильтр выполняет простейшие операции. Расширенный фильтр имеет гораздо больше параметров.
Автофильтр и расширенный фильтр в Excel
Есть простая таблица, не отформатированная и не объявленная как список. Вы можете активировать автоматический фильтр через главное меню.
- Выделите мышью любую ячейку в пределах диапазона. Перейдите на вкладку «Данные» и нажмите кнопку «Фильтр».
- Стрелки появляются рядом с заголовками таблиц, чтобы открыть списки автоматических фильтров.
Если вы отформатируете диапазон данных как таблицу или объявите его как список, автоматический фильтр будет добавлен немедленно.
Пользоваться автофильтром просто: нужно выбрать вход с нужным значением. Например, показать доставку в магазин №4. Поставьте галочку напротив соответствующего условия фильтра:
Мы сразу видим результат:
Особенности инструмента:
- Автофильтр работает только в диапазоне без перебоев. Различные таблицы на одном листе не фильтруются. Даже если они имеют одинаковый тип данных.
- Инструмент обрабатывает верхнюю строку как заголовки столбцов; эти значения не входят в фильтр.
- Вы можете применить несколько условий фильтрации одновременно. Но каждый предыдущий результат может скрывать записи, необходимые для следующего фильтра.
Расширенный фильтр имеет гораздо больше параметров:
- Вы можете установить столько условий фильтрации, сколько вам нужно.
- Критерии отбора данных на виду.
- С расширенным фильтром пользователь может легко найти уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример, как использовать расширенный фильтр в Excel:
- Создадим таблицу с условиями отбора. Для этого скопируйте заголовки из исходного списка и вставьте их выше. В табличке с критериями фильтрации оставляем достаточное количество строк, плюс пустая строка, отделяющая исходную таблицу.
- Настроим параметры фильтра на выбор строк со значением «Москва» (в соответствующем столбце таблицы с условиями вводим = «=Москва»). Активируем любую ячейку исходной таблицы. Перейдите на вкладку «Данные» — «Сортировка и фильтрация» — «Дополнительно».
- Заполните параметры фильтра. Исходный диапазон: таблица с исходными данными. Ссылки появляются автоматически, так как одна из ячеек была активной. Диапазон условий: метка с условием.
- Выйдите из расширенного меню фильтра, нажав кнопку OK.
В исходной таблице остались только строки, содержащие значение «Москва». Для отмены фильтрации необходимо нажать кнопку «Очистить» в разделе «Сортировка и фильтрация».
Как пользоваться расширенным фильтром в Excel
Рассмотрите возможность использования расширенного фильтра в Excel для выбора строк, содержащих слова «Москва» или «Рязань». Условия фильтрации должны быть в одном столбце. В нашем примере один под другим.
Заполните меню расширенного фильтра:
Получаем таблицу с выбранными строками по заданным критериям:
Выделим строки, содержащие значение «№ 1» в столбце «Магазин» и «> 1 000 000 рублей» в столбце себестоимости. Критерии фильтрации должны находиться в соответствующих столбцах метки условия. В линию.
Заполните параметры фильтра. Нажимаем Принять.
Оставим в таблице только те строки, которые содержат слово «Рязань» в столбце «Регион» или значение «> 10 000 000 рублей» в столбце «Стоимость». Поскольку критерии отбора относятся к разным столбцам, мы размещаем их на разных строках под соответствующими заголовками.
Применить инструмент «Расширенный фильтр»:
Этот инструмент может работать с формулами, что позволяет пользователю решить практически любую задачу при выборе значений из массивов.
- Результатом формулы является критерий выбора.
- Написанная формула возвращает ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается с использованием абсолютных ссылок, а критерии выбора (в форме формулы) указываются с использованием относительных ссылок.
- Если возвращается TRUE, строка будет отображаться после применения фильтра. ЛОЖЬ — Нет.
Мы собираемся показать строки, содержащие сумму выше среднего. Для этого в дополнение к табличке с критерием (в ячейку I1) введем наименование «Самое большое число». Ниже приведена формула. Мы используем функцию СРЗНАЧ.
Выберите любую ячейку в исходном диапазоне и вызовите «Расширенный фильтр». В качестве критериев выбора мы указываем I1:I2 (относительные ссылки!).
В таблице остались только те строки, где значения в столбце «Сумма» выше среднего.
Чтобы оставить в таблице только неповторяющиеся строки, в окне «Расширенный фильтр» установите флажок «Только уникальные записи».
Нажмите «ОК». Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
Расширенный фильтр в Excel: как сделать и как им пользоваться
Многие пользователи ПК знакомы с пакетом продуктов Microsoft Office для работы с различными типами документов. Среди программ этой компании MS Excel. Эта утилита предназначена для работы с электронными таблицами.
В программе есть так называемый расширенный фильтр в Excel. Он предназначен для простоты использования и создания таблиц. О нем пойдет речь в нашей статье. Мы покажем вам, как это сделать и как это использовать.
Что это за функция? Описание
Что означает расширенный фильтр в Excel? Это функция, позволяющая разграничить выбранные данные (по столбцам в Excel) по отношению к введенным требованиям.
Например, если у нас есть таблица с информацией обо всех учениках школы (рост, вес, класс, пол и т д.), то мы легко можем выбрать среди них, скажем, всех детей ростом 160 из 8 сорт. Вы можете сделать это, используя функцию расширенного фильтра в Excel. Мы поговорим об этом подробно позже.
Что значит автофильтр?
Каковы характеристики обычного и расширенного фильтра? Сначала поговорим о первом. Автофильтр (обычный) предназначен для различения данных по заранее известным критериям, количество которых определяется. Он используется, когда у вас есть электронная таблица небольшого размера, по столбцам и заданным параметрам. Если нам нужно установить неограниченное количество критериев для фильтрации, мы должны выбрать расширенный.
Как делать правильно?
Как сделать расширенный фильтр в Excel? Чтобы пояснить, как происходит процедура и как она делается, рассмотрим пример.
Инструкции по расширенной фильтрации электронных таблиц:
- Вам нужно создать место над основным столом. Здесь будут размещены результаты фильтрации. Места для готового стола должно быть достаточно. Также требуется еще одна строка. Он отделит отфильтрованную таблицу от основной.
- На первую строку свободного места скопируйте полный заголовок (имена столбцов) основной таблицы.
- Введите необходимые данные для фильтрации по нужному столбцу. Обратите внимание, что ввод должен выглядеть так: = «= отфильтрованное значение».
- Теперь нужно перейти в раздел «Данные». В области фильтрации (иконка в виде воронки) выберите «Дополнительно» (находится внизу списка справа от соответствующего баннера).
- Далее во всплывающем окне необходимо ввести параметры расширенного фильтра в Excel. «Диапазон условий» и «Начальный диапазон» автоматически заполняются, если выбрана ячейка в начале рабочего листа. В противном случае вам придется вводить их самостоятельно.
- Нажмите «ОК». Он выйдет из конфигурации расширенных параметров фильтрации.
После проделанных шагов основная таблица будет содержать только записи для данного значения разделителя. Для отмены последнего действия (фильтрации) необходимо нажать кнопку «Очистить», которая находится в разделе «Данные».
Работа с расширенным фильтром в «Экселе»
Как использовать расширенный фильтр в Excel? Чтобы понять, давайте еще раз посмотрим на эту функцию. Предположим, у нас есть столбец с городами в электронной таблице. До этого мы уже делали расширенную фильтрацию по городу Ростову. Теперь в этом же документе мы хотим добавить к полученным для города Ростова те записи, которые соответствуют городу Самаре.
Для этого вам нужно:
- Разместите граничные условия (=»-Самара») под предыдущим запросом (=»=Ростов»).
- Вызвать меню расширенного фильтра (раздел «Данные», вкладка «Фильтрация и сортировка», в нем выбрать «Дополнительно»).
- Нажмите ОК. Это закроет расширенную фильтрацию в Excel. И на экране появится подготовленная таблица, состоящая из записей, в которых указан город Самара или Ростов.
При работе следует учитывать следующий очень важный момент. Все введенные критерии должны быть только в тех столбцах, по которым производится фильтрация. На главном столе.
После того, как вы изучили базовый метод расширенной фильтрации, можно приступить к изучению всех возможностей этой функции. Это означает использование формул.
Расширенная фильтрация. Основные правила использования при работе «Экселе»
- Критерием выбора являются результаты исходной формулы.
- Результат может иметь только два значения: «ИСТИНА» или «ЛОЖЬ».
- Абсолютные ссылки определяют исходный диапазон отфильтрованной таблицы.
- Результаты формулы будут отображать только те строки, которые имеют общее значение «ИСТИНА». Значения строк, которые были получены в результате формулы «ЛОЖЬ.
Используя эти правила, вы можете создать любую формулу с допустимыми операндами и значениями.
Пример в «Экселе 2010»
Давайте рассмотрим пример расширенного фильтра в Excel 2010 и использование в нем формул. Например, разграничим значения некоторого столбца с числовыми данными результатом среднего значения (плюс-минус).
Инструкция по работе с расширенным фильтром в Excel по среднему значению столбца:
- Первый шаг — выбрать формулу для среднего значения данных в столбце. Для этого необходимо выбрать диапазон результатов от начальной до конечной записи. Сложите их и разделите на количество строк (количество записей).
- После этого выберите пункт «Дополнительно» в разделе «Фильтрация и сортировка». Введите туда необходимые данные (по диапазонам).
- После этого нажмите ОК. Результатом является таблица, в которой есть записи, не превышающие среднее значение данного столбца.
Таким образом, вы можете использовать расширенную фильтрацию несколькими способами. Это можно сделать, прибегая к составлению различных видов формул.
Главное не забыть ввести критерии фильтра над основной таблицей в графу, по которой будет производиться разграничение.
Внимание! В ходе операции записи, не прошедшие критерии фильтра, не удаляются. Они просто не появляются (не появляются). Расширенная фильтрация предназначена для того, чтобы можно было скопировать результат и вставить его в другое место. После этого нужно нажать «Очистить» в разделе «Фильтрация и сортировка». Затем ваша исходная таблица вернется на экран.
Автофильтр. Пример использования
Автоматический фильтр является распространенным инструментом. Его можно применить, только установив точные параметры. Например, вывести в таблице все значения, которые больше 1000 (1000).
- Перейдите на вкладку «Данные». В разделе «Фильтрация и сортировка» выберите «Дополнительно».
- В открывшемся окне укажите диапазоны рассматриваемых значений и ячейку со значением рассматриваемого критерия.
- Нажмите «ОК». После этого отобразится таблица, отфильтрованная по заданным критериям.
- Скопируйте результат разделителя. Вставьте отфильтрованную таблицу где-нибудь сбоку от того же листа Excel. Вы можете использовать другую страницу.
- Выберите «Удалить». Эта кнопка находится на вкладке «Данные» в разделе «Фильтрация и сортировка». После ее нажатия отфильтрованная таблица вернется в исходный вид. И вы можете работать с ним.
- Далее необходимо перераспределить свободное место для таблицы, которую нужно отфильтровать.
- Затем нужно скопировать заголовок (имена столбцов) из родительского поля и переместить их на первую строку освободившегося места для отфильтрованной структуры.
- Перейдите на вкладку «Данные». В разделе «Фильтрация и сортировка» выберите «Дополнительно».
- В открывшемся окне выберите диапазон записей (столбцов) для фильтрации.
- Добавьте адрес ячейки, в которой написан критерий разграничения, например, «город Одесса».
- Нажмите «ОК». После этого он будет отфильтрован по значению «Одесса».
- Скопируйте отфильтрованную таблицу и вставьте ее на другой лист документа или на ту же страницу, но подальше от основной.
- Нажмите «Удалить» еще раз. Все готово. Теперь у вас есть три доски. Основная, отфильтрованная по значению (>1000), а также отфильтрованная по другому значению (Одесса).
Небольшое заключение
Несмотря на то, что на первый взгляд эта функция кажется сложной, на самом деле все достаточно просто. Все, что вам нужно знать, — это диапазон расширенных условий фильтрации в Excel. Однако не следует забывать следующее. Не путайте обычные и расширенные фильтры. Также рекомендуется изучить правила логики и формулы Excel, тогда работа с фильтром станет легкой.
Как сделать и использовать расширенный фильтр в Excel
Использование фильтров для нескольких столбцов при работе с документом Excel облегчает пользователю поиск информации, оставляя в электронной таблице только те строки, которые не соответствуют заданным критериям. Я уже писал статью о том, как сделать фильтр в Excel, перейдя по ссылке, вы можете ее прочитать.
Однако использование обычного фильтра может подойти не всем пользователям. Дело в том, что с его помощью можно фильтровать данные, задав ограниченное количество условий. Если вас это не устраивает, следует воспользоваться расширенным фильтром в Excel.
Рассмотрим ваше приложение для следующей таблицы. Он находится в диапазоне ячеек A6:E31. Он содержит информацию о студентах школы.
Например, если вы хотите выбрать всех девушек ростом менее 160 см, вы можете сделать это, применив фильтр обычного столбца. Но если мы хотим подобрать детей ростом более 180 см, то с их помощью мы ничего не сможем сделать. Результаты будут отображаться для мальчиков и девочек ростом от 160 до 180 см, здесь пригодится расширенный фильтр. Надеюсь, вы понимаете разницу.
Как применить
Теперь давайте посмотрим, как использовать расширенный фильтр в Excel. Во-первых, вам нужно создать ряд условий; это делается путем копирования всех заголовков столбцов в другое место на листе.
Их лучше копировать над таблицей, иначе они тоже могут попасть под фильтр, например, если поставить их рядом с ней. Также обратите внимание, что между условиями и основной информацией должна быть хотя бы одна пустая строка.
Условия устанавливаются следующим образом. Пусть, к примеру, все девочки учатся в 9-м классе. Заполните необходимые ячейки в диапазоне условия. Затем выберите любую ячейку в основной таблице, перейдите на вкладку «Данные» и нажмите кнопку «Дополнительно» в группе «Сортировка и фильтрация» .
Откроется диалоговое окно «Расширенный фильтр». В нем выберите маркером, где показать результат, в той же таблице или сделайте это в другом месте. В качестве «Исходного диапазона» выбираем наши ячейки A6:E31. «Диапазон условий» — это наши рубрики с условиями А1:Е2. Нажмите «ОК» .
Очень важно правильно задать диапазон условий. В примере это A1:E2. Если нужно добавить еще одно условие, оно станет A1:E3 и так далее. В противном случае ничего не получится.
При различных условиях данные столбца, введенные в строку, обрабатываются как логическое «И». Данные на разных строках воспринимаются как логическое «ИЛИ». В примере оставляем всех девочек, а из них выбираем тех, кто в 9 классе. Если во второй строке написать «девушка» — «>170», то из таблицы также будут выбраны девочки, чей рост выше на 170см. При этом они могут учиться и в других классах, это логично «Или» .
Как удалить
Чтобы удалить его из данных таблицы, на вкладке «Данные» в группе «Сортировка и фильтр» нажмите «Очистить» .
Поместим отфильтрованные данные в другую таблицу
Рассмотрим еще один пример. Выберем девочек ростом менее 170 см и мальчиков ростом более 180 см. Отфильтрованную информацию разместим на одном листе, но в разных ячейках.
Записываем данные в диапазоне условий. Выберите любую ячейку основной таблицы и перейдите на вкладку «Данные» — «Дополнительно» .
Отмечаем маркером «Копировать результат в другое место», выделяем ячейки «Исходный диапазон» А6:Е31, в поле «Диапазон условий» вводим адрес А1:Е3. В поле «Поместить результат в диапазон» нажмите кнопку выбора ячейки и выберите нужные ячейки на листе, можно выбрать другой лист из открытой книги. Нажмите «ОК» .
Чтобы поместить результат расширенного фильтра в другой диапазон, сначала скопируйте заголовок таблицы и вставьте его туда. При выборе ячеек для отфильтрованных данных выберите этот заголовок и необходимое количество строк под ним. Если выбранных строк недостаточно, программа предупредит вас.
Результат с условиями, указанными для расширенного фильтра, представлен в другой части листа. Первоначальная информация осталась без изменений.
Теперь для дополнительной таблицы с отфильтрованными данными можно применить, например, сортировку в Excel. Подробнее о том, как сортировать данные в Excel, можно прочитать, перейдя по ссылке.
Я уверен, что вы понимаете, как использовать расширенный фильтр в Excel, чтобы выбрать нужную информацию из таблицы.
Как в Excel сделать фильтр по столбцам
Фильтрация данных по различным критериям помогает манипулировать информацией, выбирать и анализировать только то, что нужно. Сегодня мы узнали, как поставить фильтр в таблицу Excel и как им пользоваться.
Простой фильтр в Экселе
Рассмотрим пошагово, как настроить фильтр в Excel по столбцам:
- Нажмите на любую ячейку в таблице (или лучше выберите всю таблицу с заголовком)
- Щелкните Лента данных — Сортировка и фильтрация — Фильтр
- В первой строке таблицы появятся кнопки со стрелками, указывающие, что столбец можно фильтровать:
Как теперь фильтровать? Например, вам нужно выбрать всех людей из Алушты в таблице примера:
- В столбце «Город» нажмите на значок фильтра
- Откроется список, где все города отмечены галочками, а также форма поиска
- В поиске введите «Алушта» и нажмите ОК
Сейчас у нас на экране только те строчки, где город Алушта. Остальные строки не удаляются, только скрываются.
В столбце, где установлен фильтр на кнопке, вместо стрелки будет отображаться воронка.
Чтобы повторно применить установленный фильтр при изменении информации, щелкните Данные — Сортировка и фильтрация — Повторить или сочетание клавиш Ctrl+Alt+L .
А когда вам понадобится сбросить все примененные фильтры, нажмите Данные — Сортировать и фильтровать — Очистить .
Вы можете фильтровать сразу несколько столбцов, даже все!
Текстовый фильтр в Эксель
Давайте посмотрим, как фильтровать ячейки с определенным текстом в Excel. Проще всего по аналогии с предыдущим примером ввести в поиск нужный текст (или его часть.
Однако можно настроить выборку более гибко. Если вы нажмете «Текстовые фильтры» в окне фильтра, в контекстном меню отобразится вариант метода сопоставления: одинаковый, другой, начинается с, заканчивается на, содержит, не содержит.
Например, вам нужно выбрать людей, чье имя не Богдан. Выберем вариант «не содержит» и пропишем для него критерии «Богдан». Ставьте пробелы до и после имени. В противном случае, например, под фильтр попадет и Егор Егорович Богданов, хотя зовут его не Богдан:
Настраиваемый тестовый фильтр
Расскажу, как поставить фильтр в Excel по двум условиям в одной ячейке. Для этого нажмите Текстовые фильтры — Пользовательский фильтр .
Допустим, нам нужно было выбрать людей по имени Богдан или Никита. Напишем логику, как на картинке
И вот результат:
Как определить, какой оператор сравнения выбрать, «И» или «ИЛИ»? Логика такова:
- И — когда оба условия должны выполняться одновременно
- ИЛИ — когда достаточно хотя бы одного из двух условий
Подробнее о логических операторах можно прочитать в этой статье.
Кроме того, в условии можно использовать следующие операторы:
- ? любой персонаж
- * — любое количество символов
Например, чтобы выбрать полное имя, содержащее строку «ctor», мы записываем условие следующим образом: *ctor*.
Как поставить фильтр в Экселе на столбец с числами
Числовые фильтры тоже гибко настраиваются, есть такие методы подбора:
- То же самое или не то же самое
- Больше, больше или равно, меньше, меньше или равно
- Между (в промежутке)
- Первые 10
- Выше среднего, ниже среднего
Посмотрите, есть интересные варианты. Попробуем выбрать людей с продажами 200 тысяч — 500 тысяч. Выберем элемент МЕЖДУ. Мне это нравится:
Пользовательский фильтр доступен для числовых данных, как и для текста.
Фильтрация дат
Если ячейки в столбце отформатированы как даты, фильтрация таких данных становится намного проще.
Для начала поле значения содержит даты, сгруппированные по году, месяцу, дню. Вы можете выбрать нужные годы или месяцы с помощью флажков.
Группа команд «Фильтровать по дате» содержит большой список популярных запросов:
Чтобы установить собственный интервал, выберите МЕЖДУ .
Фильтр по цвету в Excel
Если некоторые ячейки имеют набор цветов текста или фона, вы можете фильтровать по ним. При этом программа сформирует список цветов на основе форматов, указанных в столбце:
Выберите свой вариант из списка.
Расширенный фильтр в Excel
Расширенный фильтр обеспечивает быстрый метод фильтрации. Он заключается в создании предварительной таблицы данных для выбора, а затем основной. Поля заголовка должны совпадать. Вот как это будет выглядеть, если нам нужно выбрать людей только из городов Агрыз и Азов:
Теперь вам нужно сделать:
- Нажмите «Данные» — «Сортировка и фильтрация» — «Дополнительно
- В открывшемся окне выберите:
- Исходный диапазон — таблица с данными
- Диапазон условия: диапазон со значениями для фильтрации
- Только уникальные записи — поставьте галочку, если хотите, чтобы программа скрывала дубликаты
- Обработка: выберите «копировать выходные данные в другое место», если вы хотите, чтобы отфильтрованные данные были помещены в другое место на том же листе
- Нажмите ОК
Посмотрите на фото, у нас список только для Агрыза и Азова:
Вы можете ввести дополнительное условие в содержание. Для этого справа от строки, по которой будет отбор, нужно написать формулу с условием.
Добавим пример. Теперь нужно выбрать такие же города, но записи, в которых продажи превышают 500 тыс. Условия будут следующими:
Мы снова применим расширенный фильтр, но также включим новые столбцы в таблицу фильтров. Результат на изображении выше.
Пожалуй, это все, о чем я хотел сегодня рассказать. Мы увидели, как настроить фильтр в Excel, чтобы получить желаемый выбор. Жду ваших вопросов в комментариях. До встречи!
Функция COUNT браузеров в excel Использование фильтров для разных столбцов во время работы 0
Расширения Microsoft Word в Яндекс браузере Использование фильтров для нескольких столбцов при работе 0
