Как в Excel в Фильтре Выбрать Сразу Несколько Позиций • Что это за функция описание

Exceltip

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

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

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

Расширенный фильтр в Excel: как сделать и как им пользоваться.
С флажками удобно работать в том случае, когда набор данных относительно небольшой. Например, на рис. 3 было лишь 27 заказчиков. Если же приходится иметь дело с 500 заказчиками, фильтрация данных с помощью флажков будет не столь уж и простой.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Например, в первом случае можно отобразить первые 5 заказчиков, во втором случае заказчиков, приносящих в сумме 80 дохода, в третьем случае заказчиков, дающих в сумме 1 000 000 долларов дохода. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Фильтры по подписи не являются аддитивными. Это означает, что одновременное применение двух разных фильтров невозможно. Если взять набор данных, показанный на рис. 5, и применить новый фильтр по подписи между с аргументами А и Czz (рис. 6), некоторые ранее отфильтрованные страховые компании отобразятся вновь.

Отфильтровать данные в Excel — Как правильно сортировать и фильтровать ячейки таблиц Excel 2010? Как в офисе.

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

Что это за функция? Описание

Что значит расширенный фильтр в Excel? Это функция, которая позволяет разграничивать выбранные данные (по столбцам в «Экселе») относительно введенных требований.

обычный и расширенный фильтр

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl Shift L или Данные Сортировка и фильтр Очистить. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Чтобы результат включал строки, содержащие имя, используем простой приём в виде символа подстановки *. Заключим условия между двумя «звездочками» (*), тогда Excel интерпретирует их как всё, где встречается группа символов «фреза» или «сверло».

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

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

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

Отфильтровать данные в Excel

Рис. 1. Здесь показаны лишь некоторые из великого множества фильтров сводных таблиц

Перечисленные ниже возможности фильтрации далее будут описаны подробнее:

  • Временная шкала «Дата заказа», расположенная в ячейках Н2:М8, появилась лишь в Excel 2013.
  • Срез «Рынок сбыта», расположенная в ячейках Н9:О16, появился в версии Excel 2010.
  • Раскрывающий список в ячейке В1 содержит фильтр, который в Excel 2003 именовался фильтром страницы, в Excel 2010 – фильтром отчета, а в Excel 2013 — просто фильтром.
  • Традиционные фильтры, активизируемые флажками, находящимися возле каждого элемента сводной таблицы (см. метку «Флажки» на рис. 1).
  • Поле «Поиск фильтра», появившееся в версии Excel 2010.
  • Подменю, включающее фильтры по подписям.
  • В зависимости от типа поля в раскрывающемся списке можно найти подменю Фильтры по значению. В этом подменю может находиться пункт «Первые 10». С помощью этого пункта можно настроить такие фильтры, как «Первые 10», «Последние 5», «Последние 3%», «Первые 8 млн долл.» и прочие подобные фильтры.
  • В зависимости от типа поля в раскрывающемся списке может быть подменю Фильтры по дате. В этом подменю могут находиться до 37 виртуальных фильтров, таких как «В следующем месяце», «В прошлом году» и «В этом году».

Фильтры в названиях строк и столбцов

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

Рис. 2. Фильтрация с помощью панели Поля сводной таблицы

Рис. 2. Фильтрация с помощью панели Поля сводной таблицы: наведите курсор на поле, по которому хотите выполнить фильтрацию, и щелкните на появившейся кнопке; откроется список доступных опций (как в левой части на рис. 1).

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

Чтобы не отображать сведения о продаже товаров, относящихся к этой категории, раскройте список Товар (рис. 3б, ячейка В3) и отмените установку флажка Препарат. Это действие приведет к сокрытию сведений о продаже товаров из этой категории.

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

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

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

Фильтрация данных с помощью поля поиска

Если вам приходится иметь дело с сотнями заказчиков, сэкономьте время с помощью поля поиска. База данных, приведенная на рис. 4, включает сведения о банках, кредитных союзах и страховых компаниях. Чтобы сузить область поиска до банков и кредитных союзов, выполните следующие действия.

  1. Раскройте список Заказчик.
  2. В поле поиска введите слово Bank (рис. 4а).
  3. По умолчанию устанавливается флажок Выделить все результаты поиска. Щелкните ОК.
  4. Снова раскройте список Заказчик.
  5. В поле поиска введите слова Credit Union.
  6. Установите флажок Добавить выделенный фрагмент в фильтр (рис. 4б). Щелкните ОК.

Рис. 4. Фильтрация данных с помощью поля поиска

Рис. 4. Фильтрация данных с помощью поля поиска: (а) по слову Bank; (б) добавлены позиции, содержащие слова CreditUnion

На сводной таблице останутся строки, в названиях которых встречаются либо слова Bank, либо Credit Union.

Использование фильтров в областях подписей

Текстовым полям соответствует раскрывающееся меню Фильтры по подписи. Чтобы отфильтровать всех заказчиков, в названии которых содержится слово Insurance, воспользуйтесь фильтром не содержит (рис. 5а). В появившемся на экране диалоговом окне выберите критерий фильтра Ins или Ins* (рис. 5б).

Рис. 5. Использование фильтров в областях подписей

Рис. 5. Использование фильтров в областях подписей: (а) среди фильтров по подписи выберите не содержит; (б) выберите критерий фильтра Ins*

Фильтры по подписи не являются аддитивными. Это означает, что одновременное применение двух разных фильтров невозможно. Если взять набор данных, показанный на рис. 5, и применить новый фильтр по подписи между с аргументами А и Czz (рис. 6), некоторые ранее отфильтрованные страховые компании отобразятся вновь.

Рис. 6. Второй фильтр по подписи не добавляется к ранее примененному, а заменяет его

Рис. 6. Второй фильтр по подписи не добавляется к ранее примененному, а заменяет его

Фильтрация названий столбцов на основе данных из столбца значений

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

Рис. 7. Фильтрация названий столбцов на основе данных из столбца значений

Рис. 7. Фильтрация названий столбцов на основе данных из столбца значений (Фильтр по значению)

В результате применения фильтра отображаются заказчики, сумма дохода которых находится между значениями 20 000 и 30 000 долларов включительно (рис. 7б). Применение фильтра по значению приведет к отмене ранее примененных фильтров по подписи.

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

  1. Раскройте список Заказчик. Выберите пункты меню Фильтры по значению →Первые 10.
  2. В первом поле диалогового окна Фильтр «Первые 10» задайте количество отображаемых заказчиков (рис. 8).
  3. Во втором поле (со списком) можно выбрать один из двух параметров: наибольших или наименьших.
  4. В третьем поле (со списком) можно выбрать один из трех параметров: элементов списка; % от количества элементов; Сумма. Например, в первом случае можно отобразить первые 5 заказчиков, во втором случае – заказчиков, приносящих в сумме 80% дохода, в третьем случае – заказчиков, дающих в сумме 1 000 000 долларов дохода.

Рис. 8. Создание отчета по первым пяти заказчикам

  • Пункт раскрывающегося списка Промежуточные итоги, находящегося на контекстной вкладке Конструктор, позволяет включить в итоги значения, соответствующие отфильтрованным элементам. Этот пункт списка доступен только для наборов данных 0LAP. Чтобы превратить обычный набор данных в набор данных 0LAP, обратитесь к средству PowerPivot (глава 10).
  • Можно удалить общие итоги из сводной таблицы и создать новую сводную таблицу, состоящую из одной строки и находящуюся под текущим набором данным. Скройте строку заголовка второй сводной таблицы, что приведет к отображению подлинного общего итога в нижней части сводной таблицы.
  • Если выделить пустую ячейку справа от заголовка (ячейка СЗ на рис. 8), можно будет воспользоваться фильтром на вкладке Данные. Этот фильтр не предназначен для применения в сводных таблицах и обычно недоступен, если выбрана сводная таблица. Встаньте в ячейку С3, кликните кнопку Фильтр на вкладке Данные ленты. Раскройте список в ячейке ВЗ. Выберите пункт Первые 10 и задайте отображение первых шести элементов. Отобразятся первые пять заказчиков и общий итог для исходного набора данных. Учтите, что эта методика основана на ошибке в Excel, поэтому изменение данных не приведет к обновлению фильтра. [2]

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

Рис. 9. Фильтры по дате включают различные периоды времени

Рис. 9. Фильтры по дате включают различные периоды времени

Рис. 10. Окно Фильтр по дате

Фильтрация с помощью области ФИЛЬТРЫ

Рис. 11. Несколько полей в области ФИЛЬТРЫ

Рис. 11. Благодаря нескольким полям в области ФИЛЬТРЫ сводной таблицы можно создавать самые разные отчеты

Область ФИЛЬТРЫ можно настроить совершенно неочевидным образом. На вкладке Анализ щелкните на кнопке Параметры, находящейся в группе Сводная таблица и еще раз Параметры (рис. 12). Окно Параметры сводной таблицы также можно вызвать, кликнув правой кнопкой мыши на сводной таблице, и в контекстном меню выбрать пункт Параметры сводной таблицы.

Рис. 12. Вызов окна Параметры сводной таблицы

В открывшемся окне Параметры сводной таблицы перейдите на вкладку Макет и формат и введите в поле Число полей фильтра отчета в столбце положительное значение, отличное от нуля (рис. 13). Я ввел двойку.

Рис. 13. Окно Параметры сводной таблицы

Рис. 14. Область Фильтры представлена четырьмя группами по два поля

Рис. 14. Область Фильтры представлена четырьмя группами по два поля

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

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

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

Рис. 16. Если в фильтре выбрано несколько элементов, то неясно, какие именно; если это критично, воспользуйтесь срезом

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

  1. Настройте форматирование сводной таблицы. Поскольку вы планируете создать несколько копий сводной таблицы и не собираетесь форматировать каждый лист рабочей книги, тщательно проверьте формат числовых значений и заголовков до репликации.
  2. В область ФИЛЬТРЫ добавьте поле Сектор. Для этого поля оставьте заданное по умолчанию значение (Все).
  3. Чтобы отобразить контекстную вкладку ленты Анализ, выделите одну из ячеек сводной таблицы.
  4. Найдите кнопку Параметры, находящуюся в левой части вкладки Анализ. Справа от этой кнопки находится стрелка раскрывающегося списка. Щелкните на ней. Выберите опцию Отобразить страницы фильтра отчета… (см. рис. 12).
  5. В диалоговом окне Отображение страниц фильтра отчета показан список всех полей области отчета. Поскольку сводная таблица включает единственное поле Сектор, в данном случае проблема выбора отсутствует (рис. 17).
  6. Щелкните на кнопке ОК, чтобы завершить создание копий сводной таблицы.

Рис. 17. Окно Отображение страниц фильтра отчета

Excel создает новый лист для каждой записи в поле Сектор (рис. 18).

Рис. 18. Для каждой записи поля Сектор автоматически добавлен новый лист

Рис. 18. Для каждой записи поля Сектор автоматически добавлен новый лист

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

О фильтрации с помощью срезов и временных шкал подробнее см. заметку Excel 2013. Срезы сводных таблиц; создание временной шкалы.

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4. [2] Надо отметить, что у меня этот трюк не получился. – Прим. Багузина
Как в excel сделать фильтр на каждый столбец?
… по ключевым словам Excel сам находит именно то, что нужно. Если ввести в поле поиска “фреза“, Фильтр покажет только те элементы, в которых есть это слово. Также можно добавлять новые поисковые запросы к предыдущим, установив флажок «добавить выделенный фрагмент в фильтр“.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Затем выделяем любую ячейку в основной таблице, переходим на вкладку Данные и нажимаем в группе Сортировка и фильтр на кнопку Дополнительно. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Применим «Числовой фильтр» к столбцу «Цена». Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным столбца. Например, отобразим все товары, цена которых ниже «25». Выбираем «меньше».

Применение нескольких фильтров в сводной таблице | Exceltip

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

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

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

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова Гвозди Под это условие попадают несколько строк. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Удобнее всего располагать ее над данными или же сбоку. Под ней тоже не запрещено, но не всегда удобно, так как периодически может возникать необходимость добавлять дополнительные строки. Отступите хотя бы одну пустую строку между двумя таблицами.
как сделать расширенный фильтр

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

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

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

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

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

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

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

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

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

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

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

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

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

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
После клика по кнопке ОК произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это Гвозди. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Также легко настроить отбор по строкам, содержащим точно слово «Гвозди» без учета регистра. В диапазоне В1:В2 разместим колонку с новым критерием отбора, не забыв указать заголовок столбца, в котором будет выполняться отсев. В ячейке В2 необходимо указать следующую формулу =»=Гвозди».

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

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: