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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные найдите группу Сортировка и фильтр .
3. Щелкнуть по кнопке Фильтр .

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

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

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… , используя который можно задать одновременно два условия отбора, связанные отношением И – одновременное выполнение 2 условий, ИЛИ – выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

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

1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Пользовательская сортировка данных

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

В таком случае стоит обратить внимание на настраиваемую сортировку в Excel

1. Открываем меню «Сортировка и фильтр» и выбираем пункт «Настраиваемая сортировка» или «Пользовательская сортировка» (название зависит от версии программы).

3

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

4

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

Обратите внимание на пометку «Затем по». Она показывает, что приоритетными для сортировки таблицы являются условия первой строки

1. Если все поля заполнены правильно, нажимаем «ОК».

5

Настройки можно изменить, выбрав одну из ячеек рассортированного диапазона и открыв окно пользовательской/настраиваемой сортировки.

Как поставить

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

Для начала работы необходимо выбрать одну, любую, ячейку внутри таблицы, открыть вкладку «Данные».

Затем нажать кнопку «Фильтр».

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

В таблице

Пошаговая инструкция: как поставить фильтр в эксель-таблице.

1. Нажать на значок в заголовке столбца.

Перед пользователем появится выпадающее окошко с перечислением всех значений в этом столбце.

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

3. Просмотреть результат — останутся только строки, соответствующие заданному параметру.

В диапазоне

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

Например, в рассматриваемом файле столбцы В и С имеют числовой отсев.

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

Например, в случае с числовым поиском этот порядок действий выглядит так:

1. Выбираем вид отсева.

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

3. Ввести цифру, которая и станет границей для отсева, — выведены будут все значения больше нее.

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

Подготовка диапазона для условий фильтрации

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

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

Правильная организация данных для работы с расширенным фильтром

Как динамически фильтровать данные с одного листа на другой в Excel?

Мы можем легко фильтровать данные и копировать отфильтрованные данные в другое место на активном листе с помощью функции расширенного фильтра, но пробовали ли вы когда-нибудь фильтровать данные с одного листа на другой и делать фильтр динамически? Это означает, что при изменении данных в исходном листе будут изменены и новые отфильтрованные данные. В этой статье я представлю Microsoft Query в Excel для решения этой задачи.

Динамическая фильтрация данных с одного листа на другой в Excel

Чтобы завершить эту задачу, выполните следующие действия шаг за шагом:

1. Сначала определите имя диапазона для исходных данных, выберите диапазон данных, который вы хотите отфильтровать, и введите имя диапазона в поле Имя Box, а затем нажмите Enter ключ, см. снимок экрана:

2. Во-вторых, вы должны создать таблицу для данных, выберите диапазон данных и нажмите Вставить > Настольные, в выскочившем Создать таблицу диалоговое окно, отметьте В моей таблице есть заголовки вариант, см. снимок экрана:

3, Затем нажмите OK кнопка, таблица была создана для диапазона данных, затем откройте новый рабочий лист, на котором вы хотите найти результат фильтрации, и нажмите Данные > Из других источников > Из Microsoft Query, см. снимок экрана:

4. В Выберите источник данных диалоговое окно, щелкните Файлы Excel * в Базы данных список, см. снимок экрана:

5. Затем нажмите OK кнопку, чтобы перейти к Выберите книгу диалог, справа Справочники поле со списком, выберите папку, в которой находится активная книга, а затем щелкните имя вашей текущей книги слева Имя базы данных список, см. снимок экрана:

6. Продолжайте нажимать OK кнопка, во всплывающем Мастер запросов — Выбор столбцов В левом диалоговом окне выберите имя диапазона, который вы создали для своих данных, и щелкните значок плюса (+) , чтобы развернуть параметр, и все заголовки столбцов были отображены, как показано на следующем снимке экрана:

7. Затем вы должны добавить заголовки столбцов из Доступные таблицы и столбцы коробку в Столбцы в вашем запросе поле, выбрав заголовки и нажав

8, Затем нажмите Next кнопка перейти к Мастер запросов — Фильтрация данных В диалоговом окне укажите критерии фильтрации, по которым вы хотите выполнять фильтрацию, см. снимок экрана:

9. Затем нажмите Next > Next кнопки для перехода к Мастер запросов — Готово диалоговое окно, в этом диалоговом окне выберите Вернуть данные в Microsoft Excel вариант, см. снимок экрана:

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

11, Наконец, нажмите OK кнопка, данные фильтра были импортированы в новый лист, см. снимок экрана:

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

Фильтровать данные по нескольким критериям или другим конкретным условиям, например по длине текста, с учетом регистра и т. Д. Kutools for ExcelАвтора Суперфильтр feature — это мощная утилита, вы можете применить эту функцию для выполнения следующих операций: Фильтровать данные по нескольким критериям; Фильтровать данные по длине текста; Фильтровать данные по верхнему / нижнему регистру; Дата фильтрации по году / месяцу / дню / неделе / ​​кварталу Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

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

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

Задание условий фильтрации

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте или скопировать результат в другое место .

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

Использование расширенных числовых фильтров в Excel

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

1. Откройте вкладку Данные, затем нажмите команду Фильтр. В каждом заголовке столбца появится кнопка со стрелкой. Если Вы уже применяли фильтры в таблице, можете пропустить этот шаг.
2. Нажмите на кнопку со стрелкой в столбце, который необходимо отфильтровать. В этом примере мы выберем столбец A, чтобы увидеть заданный ряд идентификационных номеров.
3. Появится меню фильтра. Наведите указатель мыши на пункт Числовые фильтры, затем выберите необходимый числовой фильтр в раскрывающемся меню. В данном примере мы выберем между, чтобы увидеть идентификационные номера в определенном диапазоне.
4. В появившемся диалоговом окне Пользовательский автофильтр введите необходимые числа для каждого из условий, затем нажмите OK. В этом примере мы хотим получить номера, которые больше или равны 3000, но меньше или равны 4000.
5. Данные будут отфильтрованы по заданному числовому фильтру. В нашем случае отображаются только номера в диапазоне от 3000 до 4000.

Как сделать (наложить) фильтр на таблицу Excel

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

Обратите внимание:

После того как выделили нужные столбцы, нужно собственно установить фильтр. Самый простой способ — это зайти на вкладке ленты “Главное” / “Фильтр и сортировка” и нажать фильтр. Привожу скриншот из Office 2010, в прочих версиях Excel фильтр на таблицу накладывается аналогично.

После нажатия этой кнопки в выделенные ячейки добавится фильтр.

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

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

Простой фильтр в Экселе

Рассмотрим по пунктам, как установить фильтр в Экселе по столбцам:

1. Кликните по любой ячейке внутри таблицы (а лучше – выделите всю таблицу с шапкой)
2. Нажмите на ленте Данные – Сортировка и фильтр – Фильтр
3. В первой строке таблицы появятся кнопки со стрелками, обозначающие, что для столбца можно задать фильтр:

Как теперь отфильтровать? Например, нужно в таблице примера выбрать всех людей из Алушты:

1. В колонке «Город» нажмем на значок фильтра
2. Откроется список, где галками отмечены все города, а также, форма поиска
3. В поиске напишем «Алушта» и нажмем Ок

Теперь у нас на экране только те строки, где город – Алушта. Остальные строки не удалены, а только скрыты.

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

Чтобы повторно применить установленный фильтр, когда информация изменилась, нажмите Данные – Сортировка и фильтр – Повторить, или комбинацию клавиш Ctrl+Alt+L.

А когда нужно сбросить все примененные фильтры, жмем Данные – Сортировка и фильтр – Очистить.

Вы можете фильтровать в нескольких колонках одновременно, даже во всех!

Сортировка в Excel по дате и месяцу

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

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

1. В ячейке A1 введите название столбца «№п/п», а ячейку A2 введите число 1. После чего наведите курсор мышки на маркер курсора клавиатуры расположенный в нижнем правом углу квадратика. В результате курсор изменит свой внешний вид с указательной стрелочки на крестик. Не отводя курсора с маркера нажмите на клавишу CTRL на клавиатуре в результате чего возле указателя-крестика появиться значок плюсик «+».
2. Теперь одновременно удерживая клавишу CTRL на клавиатуре и левую клавишу мышки протяните маркер вдоль целого столбца таблицы (до ячейки A15).

В результате чего столбец автоматически заполниться последовательностью номеров транзакций от 1 до 14.

Полезный совет! В Excel большинство задач имеют несколько решений. Для автоматического нормирования столбцов в Excel можно воспользоваться правой кнопкой мышки. Для этого достаточно только лишь навести курсор на маркер курсора клавиатуры (в ячейке A2) и удерживая только правую кнопку мышки провести маркер вдоль столбца. После того как отпустить правую клавишу мышки, автоматически появиться контекстное меню из, которого нужно выбрать опцию «Заполнить». И столбец автоматически заполниться последовательностью номеров, аналогично первому способу автозаполнения.

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

1. Ячейки D1, E1, F1 заполните названиями заголовков: «Год», «Месяц», «День».
2. Соответственно каждому столбцу введите под заголовками соответствующие функции и скопируйте их вдоль каждого столбца:

• D1: =ГОД(B2);
• E1: =МЕСЯЦ(B2);
• F1: =ДЕНЬ(B2).

В итоге мы должны получить следующий результат:

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

Допустим мы хотим выполнить сортировку дат транзакций по месяцам. В данном случае порядок дней и годов – не имеют значения. Для этого просто перейдите на любую ячейку столбца «Месяц» (E) и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка по возрастанию».

Теперь, чтобы сбросить сортировку и привести данные таблицы в изначальный вид перейдите на любую ячейку столбца «№п/п» (A) и вы снова выберите тот же инструмент «Сортировка по возрастанию».

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

Когда вы применяете функцию «Фильтр», после фильтрации одного столбца следующие столбцы будут фильтроваться только на основе результата предыдущего отфильтрованного столбца. Это означает, что только критерий И может применяться к более чем одному столбцу. Как в этом случае применить критерии И и ИЛИ для одновременной фильтрации нескольких столбцов на листе Excel?

Одновременная фильтрация нескольких столбцов с помощью расширенного фильтра

Например, у меня есть диапазон данных, теперь мне нужно отфильтровать их на основе критериев из нескольких столбцов: Product = AAA-1 и Order> 80 или Total Price> 10000, чтобы получить следующий результат фильтрации:

Была основана Расширенный фильтр может помочь вам решить эту задачу так, как вам нужно, пожалуйста, сделайте шаг за шагом:

1. Создайте критерии фильтра в соответствии с вашими потребностями, в этом примере я введу критерии в диапазоне G1: I2, см. Снимок экрана:

Внимание: Чтобы создать И критерий, поместите свои критерии в ту же строку, если создаете OR критерий, поместите критерии в отдельные строки. 2

После создания критериев нажмите Данные > Дополнительные, см. снимок экрана:

2. После создания критериев нажмите Данные > Дополнительные, см. снимок экрана:

3. В Расширенный фильтр диалоговом окне выполните следующие операции:

(1.) Выберите Отфильтровать список по месту вариант под Действие раздел;

(2.) Щелкните

Диапазон списка

(3.) Затем щелкните

Диапазон критериев

4. Затем нажмите OK, и несколько столбцов были отфильтрованы одновременно на основе критериев, см. снимок экрана:

Отфильтруйте несколько столбцов одновременно с помощью Kutools for Excel

Если вы не можете применить расширенный фильтр со знанием дела, Kutools for ExcelАвтора Суперфильтр также могу оказать вам услугу. С его помощью вам просто нужно выбрать критерии, такие же, как при использовании функции фильтра, не вводя критерии вручную.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 60 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel пожалуйста, сделайте следующее:

1. Нажмите Предприятие > Суперфильтр, см. снимок экрана:

2. В появившемся диалоговом окне Super Filter:

(1.) Проверить Указанный и нажмите

(2.) Выберите взаимосвязь между критериями. Разумеется, or Or из Родство раскрывающийся список;

(3.) Затем выберите необходимые критерии в поле списка критериев.

3. После завершения критериев нажмите Фильтр кнопку, и данные были отфильтрованы одновременно по нескольким критериям столбца, см. снимок экрана:

Демо: фильтруйте несколько столбцов одновременно с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные найдите группу Сортировка и фильтр .
3. Щелкнуть по кнопке Фильтр .

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

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

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… , используя который можно задать одновременно два условия отбора, связанные отношением И – одновременное выполнение 2 условий, ИЛИ – выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

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

1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

Иногда вам может потребоваться отфильтровать ячейки по длине текста в Excel. Например, вам нужно отфильтровать текст длиной 6 символов. Эта статья покажет вам подробные инструкции о том, как фильтровать ячейки по заданной длине текста (количеству символов).

Отфильтруйте ячейки по заданной длине текста с помощью команды Filter

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

Шаг 1. Выберите столбец B и щелкните значок Фильтр под Данные меню.

Шаг 2: Щелкните стрелку фильтра

Текстовые фильтрыПользовательский фильтр

Шаг 3. Во всплывающем диалоговом окне Custom AutoFilter укажите критерии как Равнои введите 6 вопросительных знаков, чтобы указать длину.

Если вы хотите отфильтровать ячейки, которые содержат 6 символов, введите 6 вопросительных знаков (?) как показано на следующем снимке экрана.

Шаг 4: нажмите OK.

Затем он фильтрует ячейки с 6 символами в столбце B.

Быстро фильтруйте ячейки по длине текста в Excel:

Была основана Специальный фильтр полезности Kutools for Excel может помочь вам быстро отфильтровать ячейки по определенной длине текста в Excel, как показано в демонстрации ниже.Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)

Фильтровать ячейки по заданной длине текста с помощью функции Len

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

Шаг 1. В столбце справки, в столбце D, введите формулу = ДЛСТР (B2).

Функция Len будет подсчитывать символы в определенной ячейке и возвращает длину символов.

Шаг 2: выберите столбец D, щелкните значок Главная >> Заполнять >> вниз чтобы скопировать эту формулу во весь столбец.

Шаг 3: выберите столбец D и щелкните значок Фильтр под Данные меню.

Шаг 4: Щелкните стрелку фильтра

Выбрать всеOK

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

Фильтр ячеек по длине текста с помощью Kutools for Excel

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

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Kutools Plus > Специальный фильтр чтобы включить функцию. Смотрите скриншот:

2. в Специальный фильтр диалоговое окно, настройте следующим образом:

• 2.1). Диапазон поле выберите столбец, в котором вы будете фильтровать данные по длине;
• 2.2). Фильтр раздел правил, сохраните Текст выбран вариант, выберите Длина текста равна вариант из раскрывающегося списка, а затем введите длину символов в текстовое поле;
• 2.3) Нажмите OK кнопка. Смотрите скриншот:

3. Появится диалоговое окно, в котором сообщается, сколько ячеек найдено. Нажмите OK закрыть его.

Затем ячейки с указанной длиной текста немедленно фильтруются.

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

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

У подавляющего большинства пользователей Excel при слове «фильтрация данных» в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

Такой фильтр — штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой «доработкой напильником» (по традиции).

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы — это будет диапазон с условиями (выделен для наглядности желтым):

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский «Ашан» в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data — Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

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

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

Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:

Добавляем макрос

«Ну и где же тут удобство?» — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но «все меняется, когда приходят они » — макросы!

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

Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

Так все гораздо лучше, правда?

Реализация сложных запросов

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

Пустые ячейки и промежуточные итоги

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

Отберем в диапазоне с пустыми ячейками ячейки со значениями:

1. Выделяем диапазон с данными и щелкаем по кнопке «Фильтр» (чтобы у заголовков появились «стрелочки»).
2. Нажимаем кнопку у заголовка первого столбца. В меню фильтрации выбираем «Выделить все». Убираем выделение напротив значения «Пустые».

Чтобы выполнить команду, жмем ОК.

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

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

Сменился диапазон – сумма стала иной.

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