Добавить выделенный фрагмент в фильтр excel что это
Расширенный фильтр в Excel и примеры его возможностей
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
У расширенного фильтра гораздо больше возможностей:
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
Расширенный фильтр в EXCEL
history 31 марта 2013 г.
Главный недостаток стандартного фильтра ( Данные/ Сортировка и фильтр/ Фильтр ) – это отсутствие визуальной информации о примененном в данный момент фильтре: необходимо каждый раз лезть в меню фильтра, чтобы вспомнить критерии отбора записей. Особенно это неудобно, когда применено несколько критериев. Расширенный фильтр лишен этого недостатка – все критерии помещаются в виде отдельной таблички над фильтруемыми записями.
Алгоритм создания Расширенного фильтра прост:
Задача 1 (начинается. )
Обычно критерии Расширенного фильтра размещают над таблицей, к которой применяют фильтр, но можно их разместить и сбоку таблицы. Избегайте размещения таблички с критериями под исходной таблицей, хотя это не запрещено, но не всегда удобно, т.к. в исходную таблицу могут добавляться новые строки.
ВНИМАНИЕ! Убедитесь, что между табличкой со значениями условий отбора и исходной таблицей имеется, по крайней мере, одна пустая строка (это облегчит работу с Расширенным фильтром ).
Теперь все подготовлено для работы с Расширенным фильтром:
Номера отобранных строк будут выделены синим шрифтом.
Задача 2 (точно совпадает)
Теперь все подготовлено для работы с Расширенным фильтром:
Задача 3 (условие ИЛИ для одного столбца)
Окно с параметрами Расширенного фильтра и таблица с отфильтрованными данными будет выглядеть так.
Задача 4 (условие И)
После нажатия кнопки ОК будут выведены все записи содержащие в столбце Товар продукцию Гвозди с количеством >40.
СОВЕТ: При изменении критериев отбора лучше каждый раз создавать табличку с критериями и после вызова фильтра лишь менять ссылку на них.
Примечание : Если пришлось очистить параметры Расширенного фильтра ( Данные/ Сортировка и фильтр/ Очистить ), то перед вызовом фильтра выделите любую ячейку таблицы – EXCEL автоматически вставит ссылку на диапазон занимаемый таблицей (при наличии пустых строк в таблице вставится ссылка не на всю таблицу, а лишь до первой пустой строки).
Задача 5 (условие ИЛИ для разных столбцов)
После нажатия кнопки ОК будут выведены записи содержащие в столбце Товар продукцию Гвозди ИЛИ значение >40 (у любого товара).
Задача 6 (Условия отбора, созданные в результате применения формулы)
Настоящая мощь Расширенного фильтра проявляется при использовании в качестве условий отбора формул.
Существует две возможности задания условий отбора строк:
Рассмотрим критерии задаваемые формулой. Формула, указанная в качестве критерия отбора, должна возвращать результат ИСТИНА или ЛОЖЬ.
Например, отобразим строки, содержащие Товар, который встречается в таблице только 1 раз. Для этого:
СОВЕТ: Для проверки работоспособности формулы можно создать дополнительный столбец рядом с таблицей (например в F) и ввести указанную выше формулу в ячейку F8, а затем скопировать ее вниз. Будет сформирован столбец со значениями ИСТИНА/ЛОЖЬ, который поможет определить как работает ваша формула.
Примеры других формул из файла примера :
ВНИМАНИЕ! Применение Расширенного фильтра отменяет примененный к таблице фильтр ( Данные/ Сортировка и фильтр/ Фильтр ).
Задача 7 (Условия отбора содержат формулы и обычные критерии)
Критерии разместим в строках 6 и 7. Введем нужные Товар и Тип товара. Для заданного Тип товара вычислим среднее и выведем ее для наглядности в отдельную ячейку F7. В принципе, формулу можно ввести прямо в формулу-критерий в ячейку С7. Поясняющий текст в ячейке над формулой (С6) НЕ должен совпадать ни с одним заголовком столбца таблицы! В противном случае фильтр будет работать неправильно.
Далее действуем как обычно: выделяем любую ячейку таблицы, вызываем Расширенный фильтр (Advanced Filter) и указываем диапазон с критериями.
Будут выведены 2 товара из 4-х (заданного типа товара).
В файле примера для удобства использовано Условное форматирование : выделяются строки удовлетворяющие первым 2-м критериям (подробнее см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).
Задача 7.1. (Совпадают ли 2 значения в одной строке?)
Есть таблица, в которой указаны Год выпуска и Год покупки автомобиля.
Поясняющий текст в ячейке С6 НЕ должен совпадать ни с одним заголовком столбца таблицы! В противном случае фильтр будет работать неправильно.
Задача 8 (Является ли символ числом?)
Пусть у нас есть таблица с перечнем различных типов гвоздей.
Проще всего это сделать если в качестве фильтра задать условие, что после слова Гвозди должно идти цифра. Это можно сделать с помощью формулы =ЕЧИСЛО(—ПСТР(A11;ДЛСТР($A$8)+2;1))
Задача 9 (Вывести строки, в которых НЕ СОДЕРЖАТСЯ заданные Товары)
Для этого придется использовать простую формулу =ЕНД(ВПР(A15;$A$8:$A$11;1;0))
Вывод уникальных строк
Расширенный фильтр и немного магии
Основа
Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.
Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский «Ашан» в III квартале, то условия будут выглядеть так:
Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:
Добавляем макрос
Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:
Так все гораздо лучше, правда? 🙂
Реализация сложных запросов
Логические связки И-ИЛИ
Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из «Ашана».
Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:
В общем и целом, после «доработки напильником» из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.
Фильтрация данных сводной таблицы в Excel 2013
Фильтрации сводных таблиц в Excel 2013 может быть выполнена десятками методов. На рис. 1 приведены лишь некоторые из них. [1]
Рис. 1. Здесь показаны лишь некоторые из великого множества фильтров сводных таблиц
Скачать заметку в формате Word или pdf, примеры в формате Excel
Перечисленные ниже возможности фильтрации далее будут описаны подробнее:
Фильтры в названиях строк и столбцов
Если в области строк или столбцов сводной таблицы находится поле (или поля), в ячейке заголовка этого поля появится раскрывающийся список, включающий критерии фильтрации. Как показано на рис. 1, в ячейке А4 отображается раскрывающийся список Заказчик, а в ячейке ВЗ – раскрывающийся список Товар. Сводная таблица, показанная на этом рисунке, находится в табличной форме. Если же для сводной таблицы выбрана сжатая форма, раскрывающийся список появится в ячейке Названия строк либо Названия столбцов.
Если в сводной таблице используются несколько полей строк, можно выполнить сортировку с помощью невидимых раскрывающихся списков, которые отображаются после установки указателя мыши над полем, находящимся в верхней части списка полей сводной таблицы (рис. 2).
Рис. 2. Фильтрация с помощью панели Поля сводной таблицы: наведите курсор на поле, по которому хотите выполнить фильтрацию, и щелкните на появившейся кнопке; откроется список доступных опций (как в левой части на рис. 1).
Фильтрация путем установки флажков
Зачастую в первых столбцах сводной таблицы отображается вовсе не та информация, которую вы хотите увидеть. На рис. 3а в сводной таблице отображаются сведения о продаже всех товаров. Объем продаж в категории Препарат незначителен, и вы решаете не учитывать его в процессе анализа.
Чтобы не отображать сведения о продаже товаров, относящихся к этой категории, раскройте список Товар (рис. 3б, ячейка В3) и отмените установку флажка Препарат. Это действие приведет к сокрытию сведений о продаже товаров из этой категории.
Рис. 3. Фильтрация путем установки флажков
Предположим, что нужно отменить установку флажков для нескольких сотен позиций по полю Заказчик, оставив лишь некоторые из них. Для отмены выбранных ранее позиций снимите флажок Выделить все, а потом вручную установите флажки возле выбранных позиций, которые будут отображаться в сводной таблице.
С флажками удобно работать в том случае, когда набор данных относительно небольшой. Например, на рис. 3 было лишь 27 заказчиков. Если же приходится иметь дело с 500 заказчиками, фильтрация данных с помощью флажков будет не столь уж и простой.
Фильтрация данных с помощью поля поиска
Если вам приходится иметь дело с сотнями заказчиков, сэкономьте время с помощью поля поиска. База данных, приведенная на рис. 4, включает сведения о банках, кредитных союзах и страховых компаниях. Чтобы сузить область поиска до банков и кредитных союзов, выполните следующие действия.
Рис. 4. Фильтрация данных с помощью поля поиска: (а) по слову Bank; (б) добавлены позиции, содержащие слова CreditUnion
На сводной таблице останутся строки, в названиях которых встречаются либо слова Bank, либо Credit Union.
Далеко не всегда поле поиска может оказаться полезным. Предположим, нужно найти все страховые компании (Insurance), чтобы исключить их из результатов поиска. Невозможно создать поисковый запрос «Выделить все, за исключением определенных позиций». Также вы не сможете создать запрос в форме «Включить все установки фильтра». В подобных случаях следует воспользоваться фильтрами по подписям, с помощью которых можно создавать такие запросы, как «Выбрать всех заказчиков, в названиях которых отсутствует слово Insurance».
Использование фильтров в областях подписей
Текстовым полям соответствует раскрывающееся меню Фильтры по подписи. Чтобы отфильтровать всех заказчиков, в названии которых содержится слово Insurance, воспользуйтесь фильтром не содержит (рис. 5а). В появившемся на экране диалоговом окне выберите критерий фильтра Ins или Ins* (рис. 5б).
Рис. 5. Использование фильтров в областях подписей: (а) среди фильтров по подписи выберите не содержит; (б) выберите критерий фильтра Ins*
Фильтры по подписи не являются аддитивными. Это означает, что одновременное применение двух разных фильтров невозможно. Если взять набор данных, показанный на рис. 5, и применить новый фильтр по подписи между с аргументами А и Czz (рис. 6), некоторые ранее отфильтрованные страховые компании отобразятся вновь.
Рис. 6. Второй фильтр по подписи не добавляется к ранее примененному, а заменяет его
Фильтрация названий столбцов на основе данных из столбца значений
С помощью раскрывающегося меню Фильтры по значению можно фильтровать заказчиков на основе информации, находящейся в столбцах Значения. Предположим, что нужно вывести список заказчиков с доходом между 20 000 и 30 000 долларов. Воспользуйтесь полем со списком Заказчик для выполнения этой задачи.
Рис. 7. Фильтрация названий столбцов на основе данных из столбца значений (Фильтр по значению)
В результате применения фильтра отображаются заказчики, сумма дохода которых находится между значениями 20 000 и 30 000 долларов включительно (рис. 7б). Применение фильтра по значению приведет к отмене ранее примененных фильтров по подписи.
Фильтр Первые 10
Фильтр Первые 10 – один из наиболее интересных фильтров по значению. Он позволяет избежать просмотра сотен страниц, содержащих сведения о заказчиках, в ходе проведения анализа. В подобных случаях зачастую достаточно выводить сведения о нескольких заказчиках, приносящих наибольший доход. Чтобы воспользоваться этим фильтром, выполните следующие действия.
Рис. 8. Создание отчета по первым пяти заказчикам
Обратите внимание, что итоговая сумма, равная 166 780 долларам, которая находится в ячейке В9, представляет собой сумму дохода, полученную от отображаемых на экране заказчиков. В эту сумму не входит доход, полученный от «невидимых» заказчиков. Если в процессе выполнения анализа возникает потребность в просмотре итоговой суммы дохода по всем заказчикам, воспользуйтесь одним из следующих способов.
Фильтры по дате
Если поле подписи включает даты, вместо раскрывающегося меню Фильтры по подписи отобразится меню Фильтры по дате. В этом меню находятся такие фильтры, как На следующей неделе, В этом месяце, В прошлом квартале и другие (рис. 9). Если выбрать параметр равно, До, После или между, можно будет выбрать дату или диапазон дат. Пятнадцать пунктов меню Фильтры по дате позволяют выбрать текущий, прошлый либо следующий день (неделю, месяц, квартал или год). Если же дополнительно используется параметр С начала года, можно задать практически любой период времени. Вы сможете отображать список проектов в соответствии с датами завершения и выводить те проекты, которые, например, завершаются на следующей неделе. Если же открыть рабочую книгу с фильтром по дате в другой день, произойдет пересчет с учетом новой даты.
Помните, что неделя длится от понедельника до воскресенья. Поэтому в случае выбора фильтра по дате На следующей неделе отобразится период времени от следующего понедельника до воскресенья включительно. После выбора фильтра по дате Все даты за период отобразится раскрывающееся меню, в котором можно выбрать произвольный месяц или квартал.
Рис. 9. Фильтры по дате включают различные периоды времени
Когда поле даты включает значения даты и времени, фильтры по дате могут вести себя непредсказуемым образом. Если, например, запросить отображение дат 4 /15/2015, в ответ можно получить сообщение, в котором говорится о том, что соответствующие записи отсутствуют. Это связано с тем, что значение времени 18:00, относящееся к дате 4/15/2015, хранится в памяти в виде 42109,75. Часть «75» соответствует 18 часам, отсчитываемым от полуночи. Если же нужно отобразить все записи, соответствующие любому периоду времени 15 апреля, выберите пункт Настраиваемый фильтр, и в диалоговом окне Фильтр по дате установите флажок Целые дни (рис. 10).
Рис. 10. Окно Фильтр по дате
Фильтрация с помощью области ФИЛЬТРЫ
Опытные пользователи сводных таблиц еще помнят времена, когда существовала область страниц сводной таблицы. В настоящее время эта область получила название ФИЛЬТРЫ, хотя по-прежнему устроена и работает на принципах, заложенных в основу устаревших версий Excel. Разработчики из компании Microsoft добавили в область ФИЛЬТРЫ возможность выбора нескольких элементов. И хотя эта область не столь наглядна, как срезы, она остается полезной в тех случаях, когда нужно настраивать сводную таблицу для каждого пользователя.
Добавление полей в область ФИЛЬТРЫ. Сводная таблица, показанная на рис. 11, хороша в качестве средства создания отчетов для топ-менеджеров. Стрелки раскрывающихся списков, отображенные в ячейках В1:В8, предназначены для быстрого определения величины дохода, получаемого при выборе произвольных комбинаций секторов, регионов, рынков сбыта, торговых представителей, заказчиков, товаров, дат либо дней недели. Именно для выполнения подобных задач используется фильтр отчета. Для создания подобной сводной таблицы просто перетащите поля Доход и Стоимость оборудования в область ЗНАЧЕНИЯ списка полей сводной таблицы, а затем перетащите поля, по которым будет выполняться фильтрация, в область ФИЛЬТРЫ списка полей сводной таблицы.
Рис. 11. Благодаря нескольким полям в области ФИЛЬТРЫ сводной таблицы можно создавать самые разные отчеты
Область ФИЛЬТРЫ можно настроить совершенно неочевидным образом. На вкладке Анализ щелкните на кнопке Параметры, находящейся в группе Сводная таблица и еще раз Параметры (рис. 12). Окно Параметры сводной таблицы также можно вызвать, кликнув правой кнопкой мыши на сводной таблице, и в контекстном меню выбрать пункт Параметры сводной таблицы.
Рис. 12. Вызов окна Параметры сводной таблицы
В открывшемся окне Параметры сводной таблицы перейдите на вкладку Макет и формат и введите в поле Число полей фильтра отчета в столбце положительное значение, отличное от нуля (рис. 13). Я ввел двойку.
Рис. 13. Окно Параметры сводной таблицы
На рис. 14 показаны фильтры отчета, включающие два поля в колонке. Порядок следования полей можно изменять с помощью раскрывающегося списка Отображать поля в области фильтра отчета, находящегося в окне Параметры сводной таблицы (см. рис. 13). В примере использован параметр вниз, затем поперек. Альтернативный вариант – поперек, затем вниз. Последовательность полей можно менять перетаскивая названия полей в области ФИЛЬТРЫ панели Поля сводной таблицы (см. правую часть рис. 12).
Рис. 14. Область Фильтры представлена четырьмя группами по два поля
Выбор элементов в области ФИЛЬТРЫ. Чтобы отфильтровать сводную таблицу, щелкните на кнопке раскрывающегося списка одного из полей в области фильтра отчета. Раскрывающееся меню начинается со значения (Все), далее приводятся все уникальные элементы списка (рис. 15). Чтобы выбрать нескольких элементов в фильтре отчета в нижней части меню кликните на флажке Выделить несколько элементов. После установки этого флажка можно фильтровать сводную таблицу сразу по нескольким уникальным элементам.
Рис. 15. Воспользуйтесь флажком Выделить несколько элементов для создания комбинированного фильтра
К сожалению, если в фильтре выбрано несколько элементов, Excel отображает невнятную запись (несколько элементов). Пользователь не будет знать, какие элементы выбраны. Если это критично, воспользуйтесь срезом (см., например, Excel 2013. Срезы сводных таблиц; создание временной шкалы).
Рис. 16. Если в фильтре выбрано несколько элементов, то неясно, какие именно; если это критично, воспользуйтесь срезом
Репликация отчета сводной таблицы для каждого элемента фильтра. Несмотря на появление срезов, предоставляющих пользователям новые невиданные ранее возможности, классический фильтр отчета по-прежнему полезен. С его помощью можно выполнять те действия, которые невозможны при использовании срезов. Предположим, что нужно создать отчет, выводящий сведения о доле рынка, контролируемой каждым менеджером. Этот отчет отображает доход и прибыль для каждого заказчика. Каждый менеджер может просматривать сведения о заказчиках, относящихся к его зоне ответственности.
Для создания копий сводной таблицы, предназначенных для каждого менеджера, выполните следующие действия.
Рис. 17. Окно Отображение страниц фильтра отчета
Excel создает новый лист для каждой записи в поле Сектор (рис. 18).
Рис. 18. Для каждой записи поля Сектор автоматически добавлен новый лист
Для первого созданного рабочего листа в качестве значения фильтра программа выбирает первый сектор, а также переименовывает лист таким образом, чтобы его название соответствовало названию сектора. На рис. 18 показан новый рабочий лист Здравоохранение.
В случае изменения исходных данных можно обновить все листы, созданные для записей поля Сектор. Для выполнения этой задачи воспользуйтесь параметром Обновить одной из соответствующих сводных таблиц. Например, после обновления листа Связь обновятся остальные сводные таблицы. Это связано с тем, что все сводные таблицы, созданные репликацией, используют один и тот же кеш (подробнее см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?).
О фильтрации с помощью срезов и временных шкал подробнее см. заметку Excel 2013. Срезы сводных таблиц; создание временной шкалы.
[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.
[2] Надо отметить, что у меня этот трюк не получился. – Прим. Багузина