Как фильтровать в excel по нескольким значениям

Excel: как отфильтровать столбец по нескольким значениям

Часто вам может понадобиться отфильтровать столбец по нескольким значениям в Excel.

К счастью, это легко сделать с помощью функции расширенного фильтра .

В следующем примере показано, как использовать эту функцию на практике.

Пример: фильтрация столбца по нескольким значениям в Excel

Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетболистах:

Теперь предположим, что мы хотим отфильтровать набор данных, чтобы отображались только те строки, в которых столбец «Команда» содержит «Хит» или «Селтикс».

Для этого мы можем создать столбец, указывающий, какие команды мы хотим фильтровать:

Затем щелкните вкладку « Данные » на верхней ленте, а затем нажмите кнопку « Дополнительно » в группе « Сортировка и фильтр »:

В появившемся новом окне используйте A1:C16 в качестве диапазона списка и E1:E3 в качестве диапазона критериев:

Как только вы нажмете OK , данные будут автоматически отфильтрованы, чтобы отображались только те строки, в которых название команды равно Heat или Celtics:

В этом конкретном примере мы выбрали фильтрацию данных на месте.

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Как фильтровать в excel по нескольким значениям

Здравствуйте.
Сбился с ног.
Хочу следующее: есть таблица в которой я хочу отфильтровать все строки содержащие определённые значения (в примере — хочу отфильтровать все строки содержащие 100 или 200 или 300). Вариант с настраиваемым фильтром не подходит, т.к. там можно использовать только 2 значения. Да и вбивать всё руками не хочется, т.к. таких значений может быть довольно много.
Подскажите, как это можно сделать? Вроде, должно быть довольно просто, но найти не удаётся (

Прикладываю файлик для примера.

Прикрепленные файлы

• post_355170.xlsx (9.56 КБ)

Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
15.08.2012 16:57:21

Во-первых, в 2007-10 можно в автофильтр вводить массив значений. Во-вторых есть еще расширенный фильтр. Есть ко всему вышесказанному клавиша F1. Читайте, выбирайте что Вам больше подходит.

Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
15.08.2012 17:00:55

Посмотрел файл. На форуме полно примеров, как из строки извлечь число формулой или ЮДФ. Это в доп. столбец и по нему фильтровать.

Я сам — дурнее всякого примера! .
15.08.2012 17:03:21

. Да и вбивать всё руками не хочется.
Вообще все. — Не получится. Макросом — умеете. Тады — расширенный фильтр, но вбивать придется. 😉 -68961-

Пользователь
Сообщений: 15 Регистрация: 07.12.2011
15.08.2012 17:11:46

Спасибо за оперативность. На форуме не нашёл. Возможно как-то не так ищу.
Можно поподробней, как вводить массив в автофильтр, и как фильтровать с доп. столбцом. Либо дать ссылку, где это было бы подробно описано. F1 не помогает 🙂

Пользователь
Сообщений: 15 Регистрация: 07.12.2011
15.08.2012 17:14:29
Тады — расширенный фильтр
Как?
Пользователь
Сообщений: 5099 Регистрация: 22.12.2012
15.08.2012 17:27:31

. как фильтровать с доп. столбцом.
в Н8 пишем =ПРАВСИМВ(C8;3) растягиваем вниз. Фильтруем по полученному столбцу.

Пользователь
Сообщений: 15 Регистрация: 07.12.2011
15.08.2012 17:38:32
И что это мне даёт? Я получаю свой столбец, только без букв. Фильтровать-то как?
15.08.2012 17:42:24

Понятно: F1 не работает, в «Приемы» не пускають, поиск отрубили. В помощь горемыке — вложение. 😉 -88067-

Прикрепленные файлы

• post_355192.xlsx (32.28 КБ)

Пользователь
Сообщений: 15 Регистрация: 07.12.2011
15.08.2012 18:51:55

Понятно: F1 не работает, в «Приемы» не пускають, поиск отрубили. В помощь горемыке — вложение. 😉 -88067-
Ух ты! Работает 🙂
А как это дело можно автоматизировать, чтобы каждый раз с расширенным фильтром не работать?

Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
15.08.2012 19:36:26

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

Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 15 Регистрация: 07.12.2011
15.08.2012 22:28:34

KukLP, вы ранее писали, что на форуме полно примеров решения данной задачи. Это так сложно дать ссылку на другую тему, где это обсуждалось? Или это стоит денег?

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

Пользователь
Сообщений: 9709 Регистрация: 22.12.2012
15.08.2012 22:45:56

фрилансер Excel, VBA — контакты в профиле
«Совершенствоваться не обязательно. Выживание — дело добровольное.» Э.Деминг

Пользователь
Сообщений: 15 Регистрация: 07.12.2011
16.08.2012 10:03:11

пожалуйста: http://clubs.ya.ru/4611686018427398066/replies.xml?item_no=125707
Браво.
Вам не сложно утруждать себя такими сообщениями? Заходить в тему, читать её, а потом давать такие ссылки? А вот ответить по теме — это очень сложно, да?

Пользователь
Сообщений: 9709 Регистрация: 22.12.2012
16.08.2012 10:13:04

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

Мне ответить — не сложно. Хотя нужная Вам ссылка у меня не приклеена стикером к монитору. Вопрос простой. Примеров и на этом форуме, и на других, и даже в книжках — полным-полно.
Но Вам ведь никто не обязан отвечать, не правда ли?

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

пппс. я стесняюсь спросить — Вам сколько лет, если не секрет?

фрилансер Excel, VBA — контакты в профиле
«Совершенствоваться не обязательно. Выживание — дело добровольное.» Э.Деминг

покупка

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

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

Исходные данные	Фильтр на основе	Результат фильтра

Отфильтруйте несколько значений из одного столбца с помощью функции расширенного фильтра

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

1. Нажмите Данные > Фильтр, см. снимок экрана:

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

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

(2.) Затем выберите диапазон данных, который вы хотите отфильтровать в Диапазон спискаи укажите список из нескольких значений, которые вы хотите отфильтровать на основе Диапазон критериев, (Внимание: Имя заголовка столбца фильтра и списка критериев должны совпадать.)

3, Затем нажмите OK , указанные вами элементы были отфильтрованы из диапазона данных.

Отфильтруйте несколько значений из одного столбца с помощью вспомогательного столбца

В этом разделе вы также можете использовать вспомогательный столбец для выполнения этой работы.

1. Введите следующую формулу в ячейку — C2 рядом с вашими данными:

=COUNTIF($E$2:$E$5, A2)

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

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

3. Затем выберите вспомогательный столбец и щелкните Данные > Фильтрв поле списка фильтров отметьте 1 из Выбрать все раздел, см. снимок экрана:

3. И все указанные вами значения были отфильтрованы, см. Снимок экрана:

Исходные данные	Фильтр на основе	Результат фильтра

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

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

Советы: Чтобы применить это Суперфильтр функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

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

1. Выберите диапазон данных, который вы хотите отфильтровать, а затем щелкните Кутулс Плюс > Суперфильтр, см. снимок экрана:

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

(1.) В Отношения в группе раскрывающийся список, выберите Or вариант;

(2.) Затем укажите критерии фильтрации в поле критериев один за другим по мере необходимости;

(3.) Щелкните Фильтр кнопку.

3. И вы получите нужный результат фильтрации, см. Снимок экрана:

Tips: Вы можете сохранить текущий критерий фильтра и повторно использовать его в следующий раз, как показано ниже:

Больше относительных статей:

• Применить один и тот же фильтр к нескольким листам в Excel
• Нам может быть легко применить функцию «Фильтр» для фильтрации данных на листе, но иногда может потребоваться фильтрация на нескольких листах, которые имеют общее форматирование данных с одинаковыми критериями фильтрации. Чтобы отфильтровать их по одному, вы потратите много времени, здесь я могу предложить простой способ решить эту проблему сразу.

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

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

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

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

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

• Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
• Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
• Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
• Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
• Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
• Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
• Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
• Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
• Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .

Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .

Office Tab — приносит интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Как отфильтровать список элементов из длинного-длинного списка

Опубликовано 25.06.2020 Автор Ренат Лотфуллин

Каким образом можно найти и выделить полезную информацию из большой таблицы?! В Excel есть как минимум два метода. Первый заключается в использовании формул с функциями поиска частичного соответствия. Второй способ дает аналогичный результат, но не содержит формул – его мы и рассмотрим в сегодняшнем номере EXCELTIP. Добро пожаловать в Расширенный Фильтр, да детка!

Команда закреплена на вкладке Данные рядом с обычным Фильтром.

Задача — выделим из длинного списка только те строки, в которых встречается слово Фреза или Сверло

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

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

Процедуру можно упростить, для этого Фильтр содержит поле с поиском.

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

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

Для быстрой и безошибочной работы с длинной таблицей или списком отлично подходит команда Расширенный Фильтр.

Перед запуском команды задаётся диапазон условий. Это отдельный список имён, которые, собственно, требуется найти в таблице. В примере он установлен ячейками E2:E4

Диапазон условий должен включать названия столбцов!

Перейдите на вкладку Данные -> Сортировка и фильтр -> Дополнительно

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

Неплохо, но так Расширенный Фильтр отобрал только те строки, которые начинаются с «фреза» или «сверло».

Чтобы результат включал строки, содержащие имя, используем простой приём в виде символа подстановки *. Заключим условия между двумя «звездочками» (*), тогда Excel интерпретирует их как всё, где встречается группа символов «фреза» или «сверло».

Однако и этого всё ещё недостаточно, т.к. критериям фильтра соответствуют и фрезы и бор-фрезы (которые нам не нужны), но как гласит легенда: «There can be only one».

Чтобы получить строки только со словом «фреза», воспользуемся ещё одной хитростью — поставим пробел до и после него.

Она заставляет Excel искать (пробел)фреза(пробел) и теперь «бор-фреза» не соответствует условиям фильтра. Хитрость основана на предположении, что каждое слово разделено пробелом и как следствие этого исключает из результата два возможных случая:

1. Имена начинаются с «фреза»
2. Имена заканчиваются на «фреза»

Чтобы учесть вышеупомянутые возможности, необходимо расширить диапазон критериев до E6, а именно:

E3- * фреза * ‘Содержит слово фреза (только отдельно и не в начале или конце)

E4 — *сверло* ‘Содержит слово сверло (в любом месте)

E5 — =»=фреза *» ‘Начинается со слова фреза

E6 — =»=*фреза» ‘Заканчивается словом фреза

Примечание: * в конце не обязательна. Она стоит там, чтобы было видно где кончается строка

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

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

Вам также могут быть интересны следующие статьи

• Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
• Создание списка уникальных значений в Excel с помощью формул массивов
• Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA
• Импорт данных с Excel книги, находящейся на веб-сайте
• Формулы массивов в Excel — синтаксис формул массивов и массивов констант
• SkyDrive Excel или Как организовать одновременную работу нескольких людей в одном файле
• Именованные диапазоны в Excel — несколько трюков использования
• Гиперссылка в Excel — создание, изменение и удаление
• Инструмент Найти и заменить в Excel
• Группировка в сводных таблицах

Рубрика: Ввод данных | Метки: фильтрация | 2 комментария | Permalink

2 комментария

Ренат, приветствую!
Вот мне прям именно это и нужно — фильтровать по списку слов, но вот только не в Экселе, а в LibreOffice.
Там вроде все менюшки по аналогии с Экселем, но вот именно «Расширенный фильтр» выглядит по-другому, и у меня не получается перенести Ваш алгоритм туда =(

А почему вы не стали использовать: Фильтр — Текстовые фильтры — Содержит — «фраза»?
Вроде бы очевидный способ найти в столбце упоминания фразы

Добавить комментарий Отменить ответ

Рубрики

• Google Таблицы (3)
• PowerPivot (8)
• Без рубрики (1)
• Ввод данных (13)
• Визуализация (12)
• Дашборды (15)
• Диаграммы (16)
• Макросы/VBA (4)
• Математика (5)
• Надстройки (7)
• Основы (16)
• Сводные таблицы (10)
• Формат (3)
• Формулы (20)

Подписаться на e-mail рассылку

Популярные статьи

• Несколько условий ЕСЛИ в Excel
• Как рассчитать коэффициент корреляции в Excel
• Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
• Как рассчитать регрессию в Excel
• Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
• 14 способов вставки Excel, о которых вы должны знать
• Как расчитать дисперсию в Excel с помощью функции ДИСП.В
• Как сравнить два столбца в Excel — методы сравнения данных Excel
• Создание связи между таблицами Excel
• Как построить график с нормальным распределением в Excel