Расширенный фильтр в Excel: примеры. Как сделать расширенный фильтр в Excel и как им пользоваться? Фильтрация данных в Excel Как в экселе сделать фильтр строкам

Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами.

Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ.

Используют её экономисты, бухгалтера, ученые, студенты и представители других профессий, которым необходимо обрабатывать математические данные.

Одной из самых удобных функций в этой программе является фильтрация данных. Рассмотрим, как настроить и использовать MS excel фильтры.

Где в Excel фильтры — их виды

Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.

Основные функции фильтрации в Excel:

• отбор по цвету: дает возможность отсортировать данные по цвету шрифта или заливки,
• текстовые фильтры в excel: позволяют задать те или иные условия для строк, например: меньше, больше, равно, не равно и другие, а также задать логические условия — и, или,
• числовые фильтры: отсортировывают по числовым условиям, например, ниже среднего, первые 10 и другие,
• ручной: отбор можно выполнять по выбранным самостоятельно критериям.

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

Как использовать расширенный фильтр в Excel — как его настроить

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

1. Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
2. Создать таблицу с условиями отбора.
3. Запустить расширенный фильтр.

Рассмотрим пример настройки.
У нас есть таблица со столбцами Товар, Количество и Цена.

К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди» Под это условие попадают несколько строк.

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора – Гвозди (ячейка А2).

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

Затем необходимо:

1. выделить любую из ячеек,
2. открыть «Расширенный фильтр» по пути: Данные – Сортировка и фильтр – Дополнительно,
3. проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
4. в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.

Также легко настроить отбор по строкам, содержащим точно слово «Гвозди» без учета регистра. В диапазоне В1:В2 разместим колонку с новым критерием отбора, не забыв указать заголовок столбца, в котором будет выполняться отсев. В ячейке В2 необходимо указать следующую формулу =»=Гвозди».

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

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel . В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

Информацию в таблице можно фильтровать по столбцам – одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Пример 1

Если необходимо отсеять данные по столбцу с названиями городов в алфавитном порядке, нужно выделить любую из ячеек этого столбца, открыть «Сортировку» и «Фильтр» и выбрать параметр «АЯ». В результате информация отобразится с учетом первой буквы в названии города.

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Пример 2

Необходим отсев информации по месяцам, а также город с большим объемом продаж должен стоять в таблице выше города с меньшим объемом продаж. Для решения задачи требуется в «Сортировке и фильтре» выбрать параметр «Сортировка». В появившемся окне с настройками уточнить «Сортировать по» — «Месяц».

Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» — «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

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

Решение проблемы:

1. выделить столбец с датами,
2. открыть вкладку Excel в главном меню,
3. выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».

К популярным ошибкам пользователей при работе с данной программой также стоит отнести:

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

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

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

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

У всех таблиц в Экселе, созданных через меню «Таблица» на вкладке «Вставка» или к которым применено форматирование, как к таблице, уже имеется встроенный фильтр.

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

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

Также можно настраивать и более сложные фильтры. В нашем первом столбце имеются только текстовые данные, поэтому при нажатии на значок фильтра в данном столбце есть активный пункт «Текстовые фильтры» , где можно выбрать некоторые условия. В случае с числовыми данными, как во втором столбце, будет доступен пункт «Числовые фильтры» .

При выборе любого настраиваемого варианта открывается окошко настраиваемого фильтра, где можно выбрать сразу два условия с сочетанием «И» и «ИЛИ» .

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

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

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

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

Расширенным фильтром можно отфильтровать в таблице всё, что угодно!!! Пределом возможностей этого инструмента является только фантазия пользователя!

Вы читаете четвертый пост в цикле статей о создании баз данных в MS Excel и организации обработки информации.

О том, как , можно прочесть в предыдущих статьях цикла.

Фильтр №2 – расширенный фильтр!

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

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

1. Открываем в MS Excel файл .

2. Создавая таблицу критериев отбора расширенного фильтра, рекомендую вначале действовать шаблонно, не задумываясь о задачах, которые предстоит решать. Копируем все заголовки столбцов-полей из ячеек A7…F7 в ячейки A1…F1 – заготовка для таблицы критериев готова! Можно приступать к основной работе.

Для стабильной и безошибочной работы фильтра между таблицей критериев отбора и таблицей базы данных обязательно должна быть хотя бы одна пустая строка!

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

Задача №5:

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

1. Записываем параметр фильтрации – слово «балка» в столбце «Изделие» верхней таблицы критериев отбора.

2. Активируем («встаем мышью») любую ячейку внутри таблицы базы данных – это обеспечит автоматическое заполнение окошка «Исходный диапазон» в выпадающем диалоговом окне «Расширенный фильтр».

3. Включаем расширенный фильтр в Excel 2003 через главное меню программы. Выбираем: «Данные» — «Фильтр» — «Расширенный фильтр».

4. В выпавшем окне «Расширенный фильтр» заполняем окна так, как показано на снимке экрана, расположенном ниже этого текста.

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

5. Результат работы расширенного фильтра – на следующем снимке экрана. Расширенный фильтр показал все записи базы данных, которые содержат слово «балка» в столбце «Изделие» — задача выполнена.

Регистр букв не влияет на результаты фильтрации!

Правила совместной «работы» нескольких условий отбора

Расширенный фильтр в Excel позволяет выполнить отбор по различным весьма сложным сочетаниям условий фильтрации. Можно задать несколько разных условий для нескольких столбцов, можно задать несколько условий для одного столбца или задать параметры отбора формулой – вариантов очень много!

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

1. Условия отбора, размещенные на одной строке в одном или нескольких разных столбцах таблицы критериев, предписывают расширенному фильтру показывать строки, для которых выполняются одновременно все без исключения эти условия. (Параметры отбора соединены логическими операторами «И».)

2. Условия отбора, размещенные на разных строках в одном или нескольких столбцах таблицы критериев, предписывают расширенному фильтру показывать все строки, для которых выполняется хотя бы одно из этих условий. (Параметры отбора соединены логическими операторами «ИЛИ».)

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

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

Задача №6:

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

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

Задача №7:

Вывести информацию обо всех имеющихся в базе пластинах, а так же о прочих любых изделиях массой более 1 тонны.

Задача решена – показаны записи обо всех пластинах базы данных и обо всех изделиях тяжелее 1 тонны (иллюстрация правила №2).

Для отмены действия расширенного фильтра необходимо выполнить команду главного меню программы Excel «Данные» - «Фильтр» - «Отобразить все».

Обращаю внимание на необходимость внимательного контроля корректности указания исходного диапазона базы данных и диапазона таблицы условий в выпадающем диалоговом окне «Расширенный фильтр»!

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$ F $3 »!

Если в диапазоне условий или в вашей базе окажутся полностью пустые строки, то расширенный фильтр работать не будет!

Итоги.

При вводе условий поиска можно использовать общепринятые знаки подстановки и математические знаки:

• * — любое количество любых символов
• ? – один любой символ
• = — равно
• < — меньше
• > — больше
• <= — меньше или равно
• >= — больше или равно
• <> — не равно

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

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

«Поковыряйтесь» самостоятельно в возможностях этого инструмента — он стоит того, чтобы в нем разобраться! Возросшая эффективность вашей работы многократно перекроет затраты времени потраченного на изучение!

Продолжение темы хранения и управления большими объемами информации — в следующих статьях цикла.

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, вопросы и замечания пишите в комментариях внизу страницы.

Microsoft Excel - это повсеместно распространенный и удобный инструмент для работы с электронными таблицами. Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ. Используют её экономисты, бухгалтера, ученые, студенты и представители других профессий, которым необходимо обрабатывать математические данные.

Одной из самых удобных функций в этой программе является фильтрация данных. Рассмотрим, как настроить и использовать MS excel фильтры.

Где в Excel фильтры - их виды

Найти фильтры в этой программе легко - нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.

Как задать фильтр в Excel

Основные функции фильтрации в Excel:

• отбор по цвету: дает возможность отсортировать данные по цвету шрифта или заливки,
• текстовые фильтры в excel: позволяют задать те или иные условия для строк, например: меньше, больше, равно, не равно и другие, а также задать логические условия - и, или,
• числовые фильтры: отсортировывают по числовым условиям, например, ниже среднего, первые 10 и другие,
• ручной: отбор можно выполнять по выбранным самостоятельно критериям.

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

Как использовать расширенный фильтр в Excel - как его настроить

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

1. Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
2. Создать таблицу с условиями отбора.
3. Запустить расширенный фильтр.

Рассмотрим пример настройки.
У нас есть таблица со столбцами Товар, Количество и Цена.

К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди» Под это условие попадают несколько строк.

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора – Гвозди (ячейка А2).

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

Затем необходимо:

1. выделить любую из ячеек,
2. открыть «Расширенный фильтр» по пути: Данные – Сортировка и фильтр – Дополнительно,
3. проверить, что задано в поле «Исходный диапазон» - туда должна попасть вся таблица с информацией,
4. в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.

Также легко настроить отбор по строкам, содержащим точно слово «Гвозди» без учета регистра. В диапазоне В1:В2 разместим колонку с новым критерием отбора, не забыв указать заголовок столбца, в котором будет выполняться отсев. В ячейке В2 необходимо указать следующую формулу =»=Гвозди».

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

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel . В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

Как сделать фильтр в Excel по столбцам

Информацию в таблице можно фильтровать по столбцам – одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Если необходимо отсеять данные по столбцу с названиями городов в алфавитном порядке, нужно выделить любую из ячеек этого столбца, открыть «Сортировку» и «Фильтр» и выбрать параметр «АЯ». В результате информация отобразится с учетом первой буквы в названии города.

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Необходим отсев информации по месяцам, а также город с большим объемом продаж должен стоять в таблице выше города с меньшим объемом продаж. Для решения задачи требуется в «Сортировке и фильтре» выбрать параметр «Сортировка». В появившемся окне с настройками уточнить «Сортировать по» - «Месяц».

Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» - «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

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

Решение проблемы:

1. выделить столбец с датами,
2. открыть вкладку Excel в главном меню,
3. выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».

К популярным ошибкам пользователей при работе с данной программой также стоит отнести:

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

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

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

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

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

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

Расширенный фильтр позволяет фильтровать данные по неограниченному набору условий. С помощью инструмента пользователь может:

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

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

1. Делаем таблицу с исходными данными либо открываем имеющуюся. Например, так:
2. Создаем таблицу условий. Особенности: строка заголовков полностью совпадает с «шапкой» фильтруемой таблицы. Чтобы избежать ошибок, копируем строку заголовков в исходной таблице и вставляем на этот же лист (сбоку, сверху, снизу) или на другой лист. Вносим в таблицу условий критерии отбора.
3. Переходим на вкладку «Данные» - «Сортировка и фильтр» - «Дополнительно». Если отфильтрованная информация должна отобразиться на другом листе (НЕ там, где находится исходная таблица), то запускать расширенный фильтр нужно с другого листа.
4. В открывшемся окне «Расширенного фильтра» выбираем способ обработки информации (на этом же листе или на другом), задаем исходный диапазон (табл. 1, пример) и диапазон условий (табл. 2, условия). Строки заголовков должны быть включены в диапазоны.
5. Чтобы закрыть окно «Расширенного фильтра», нажимаем ОК. Видим результат.

Верхняя таблица – результат фильтрации. Нижняя табличка с условиями дана для наглядности рядом.

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

Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» - «Сортировка и фильтр» - «Очистить».

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

В таблицу условий внесем критерии. Например, такие:

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

Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:

Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: =»=Набор обл.6 кл.»

После использования «Расширенного фильтра»:

Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

В табличку условий введем критерии отбора: =»=Набор обл.6 кл.» (в столбец «Название») и =»

Обратите внимание: критерии необходимо записать под соответствующими заголовками в РАЗНЫХ строках.

Результат отбора:

Расширенный фильтр позволяет использовать в качестве критерия формулы. Рассмотрим пример.

Отбор строки с максимальной задолженностью: =МАКС(Таблица1).

Таким образом мы получаем результаты как после выполнения несколько фильтров на одном листе Excel.

Как сделать несколько фильтров в Excel?

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

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

Теперь из таблицы с отобранными данными извлечем новую информацию, отобранную по другим критериям. Например, только отгрузки за 2014 год.

Вводим новый критерий в табличку условий и применяем инструмент фильтрации. Исходный диапазон – таблица с отобранными по предыдущему критерию данными. Так выполняется фильтр по нескольким столбцам.

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

Как сделать фильтр в Excel по строкам?

Стандартными способами – никак. Программа Microsoft Excel отбирает данные только в столбцах. Поэтому нужно искать другие решения.

Приводим примеры строковых критериев расширенного фильтра в Excel:

1. Преобразовать таблицу. Например, из трех строк сделать список из трех столбцов и к преобразованному варианту применить фильтрацию.
2. Использовать формулы для отображения именно тех данных в строке, которые нужны. Например, сделать какой-то показатель выпадающим списком. А в соседнюю ячейку ввести формулу, используя функцию ЕСЛИ. Когда из выпадающего списка выбирается определенное значение, рядом появляется его параметр.

Чтобы привести пример как работает фильтр по строкам в Excel, создадим табличку:

Для списка товаров создадим выпадающий список:

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

Рядом с выпадающим списком ячейку введем следующую формулу: Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

Скачать примеры расширенного фильтра

Таким образом, с помощью инструмента «Выпадающий список» и встроенных функций Excel отбирает данные в строках по определенному критерию.

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

Если Вы делали таблицу в Эксель через вкладку «Вставка» – «Таблица», или вкладка «Главная» – «Форматировать как таблицу», то в такой таблице, фильтр включен по умолчанию. Отображается он в виде стрелочки, которая расположена в верхней ячейке справой стороны.

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

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

Если Вас интересует вопрос, как сделать таблицу в Эксель, перейдите по ссылке и прочтите статью по данной теме.

Теперь давайте рассмотрим, как работает фильтр в Эксель. Для примера воспользуемся следующей таблицей. В ней три столбца: «Название продукта», «Категория» и «Цена», к ним будем применять различные фильтры.

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

Например, оставим в «Категории» только фрукты. Снимаем галочку в поле «овощ» и нажимаем «ОК».

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

Если Вам нужно удалить фильтр данных в Excel, нажмите в ячейке на значок фильтра и выберите из меню «Удалить фильтр с (название столбца)».

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

Применим «Числовой фильтр» к столбцу «Цена». Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным столбца. Например, отобразим все товары, цена которых ниже «25». Выбираем «меньше».

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

Таблица с фильтром по столбцу «Цена» ниже 25.

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

Оставим в таблице продукты, которые начинаются с «ка». В следующем окне, в поле пишем: «ка*». Нажимаем «ОК».

«*» в слове, заменяет последовательность знаков. Например, если задать условие «содержит» – «с*л», останутся слова стол, стул, сокол и так далее. «?» заменит любой знак. Например, «б?тон» – батон, бутон. Если нужно оставить слова, состоящие из 5 букв, напишите «?????».

Фильтр для столбца «Название продукта».

Фильтр можно настроить по цвету текста или по цвету ячейки.

Сделаем «Фильтр по цвету» ячейки для столбца «Название продукта». Кликаем по кнопочке фильтра и выбираем из меню одноименный пункт. Выберем красный цвет.

В таблице остались только продукты красного цвета.

Фильтр по цвету текста применим к столбцу «Категория». Оставим только фрукты. Снова выбираем красный цвет.

Теперь в таблице примера отображены только фрукты красного цвета.

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

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