Для чего используются фильтры в excel

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Инструкция по созданию и использованию фильтов данных в Excel

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

Видео по фильтрации и сортировке данных в Excel

Виды фильтров в Excel

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

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

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

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

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

Пример создания расширенного фильтра — необходимо задать диапазон исходных данных, фильтров и диапазон, куда помещаются отфильтрованные данные

Второй вариант фильтрации предоставляет пользователю больше возможностей для отбора необходимых данных.Чтобы запустить расширенную опцию, необходимо создать копию заголовков таблицы (т.е. просто скопировать шапку). Это будет диапазон условий. Затем надо заполнить этот диапазон критериями отбора. Но тут важно придерживаться правил: если нужно, чтобы значения отбирались по двум параметрам (к примеру, фамилия студента и оценка), то условия записываются в одну строку; если же критерии будут отбираться в режиме «ИЛИ» (марка машины или объем двигателя), тогда они записываются в разные строки.

Допустим, есть таблица с 2 колонками — наименование товара и количество. Всего товаров 3 — бананы, апельсины, мандарины, а количество — 10, 20 и 15 штук соответственно. После того как будет скопирована шапка, можно создать условие, например, показать товары, количество которых меньше или равно 15. То есть под скопированной шапкой в колонке «Кол-во» надо написать
Отблагодари меня, поделись ссылкой с друзьями в социальных сетях:

Источник

Функция ФИЛЬТР

Функция ФИЛЬТР позволяет выполнять фильтрацию диапазона данных на основе условий, которые вы определяете.

Примечание: Эта функция в настоящее время доступна только Microsoft 365 подписчикам.

В следующем примере мы использовали формулу =ФИЛЬТР(A5:D20;C5:C20=H2;»») для возврата всех записей для Apple, как было выбрано в ячейке H2, и, если яблоки нет, возвращает пустую строку («»).

Функция ФИЛЬТР фильтрует массив с учетом массива логических значений (ИСТИНА/ЛОЖЬ).

Массив или диапазон для фильтрации

Массив логических переменных с аналогичной высотой или шириной, что и массив.

Значение, возвращаемое, если все значения во включенном массиве пустые (фильтр не возвращает ничего)

Массивом может быть ряд значений, столбец со значениями или комбинация строк и столбцов значений. В приведенном выше примере массив для нашей формулы ФИЛЬТР представляет собой диапазон A5:D20.

Функция ФИЛЬТР возвращает массив, который будет переноситься на другие ячейки, если является конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете структурированные ссылки. Дополнительные сведения см. в статье о переносе массива.

Если любое значение аргумента include является ошибкой (#N/A, #VALUE и т. д.) или не может быть преобразовано в значение boolean, функция FILTER возвращает ошибку.

Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Примеры

Функция ФИЛЬТР, используемая для возврата нескольких условий

В данном случае мы используем оператор умножения (*) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст «Яблоко» И находящихся в восточном регионе: =ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);»»).

Функция ФИЛЬТР, используемая для возврата нескольких условий и сортировки

В данном случае мы используем предыдущую функцию ФИЛЬТР с функцией СОРТ для возврата всех значений в диапазоне массива (A5:D20), содержащих текст «Яблоко» И находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);»»);4;-1)

В данном случае мы используем функцию ФИЛЬТР с оператором сложения (+) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст «Яблоко» ИЛИ находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)+(A5:A20=H2);»»),4;-1).

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

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

Проверьте, как это работает!

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

Фильтрация диапазона данных

Выберите любую ячейку в диапазоне данных.

Выберите Данные > Фильтр.

Выберите стрелку в столбце .

Выберите пункт Текстовые фильтры или Числовые фильтры, а затем — сравнение, например пункт Между.

Введите условия фильтрации и нажмите кнопку ОК.

Фильтрация данных в таблице

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

Выберите стрелку в столбца, который вы хотите отфильтровать.

Снимите флажок (Выделить все) и установите флажки для тех элементов, которые вы хотите отобразить.

Стрелка заглавного изменится на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.

Источник

Расширенный фильтр в Excel и примеры его возможностей

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

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

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

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

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

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

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

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

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

Готовый пример – как использовать расширенный фильтр в Excel:

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

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

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

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

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

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

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

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

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

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.

Источник

Использование расширенных условий фильтрации

Если для фильтрации данных требуются сложные условия (например, Type = «Produce» OR Salesperson = «Егоров»), можно использовать диалоговое окно Расширенный фильтр.

Чтобы открыть диалоговое окно Расширенный фильтр, щелкните Данные > Расширенные.

Продавец = «Егоров» ИЛИ Продавец = «Грачев»

Тип = «Фрукты» И Продажи > 1000

Тип = «Фрукты» ИЛИ Продавец = «Грачев»

(Продажи > 6000 И Продажи 3000) ИЛИ
(Salesperson = «Buchanan» AND Sales > 1500)

Продавец = имя со второй буквой «г»

Обзор

Действие команды Дополнительно отличается от действия команды Фильтр в некоторых важных аспектах.

Она отображает диалоговое окно Расширенный фильтр, а не меню «Автофильтр».

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

Образец данных

Этот пример данных используется для всех процедур, используемых в этой статье.

Данные включают четыре пустые строки над диапазоном списка, которые будут использоваться в качестве диапазона условия (A1:C4) и диапазон списка (A6:C10). Диапазон условий содержит названия столбцов и по крайней мере одну пустую строку между значениями условий и диапазоном списка.

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

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.

= (знак «больше или равно»)

Использование знака равенства для ввода текста или значения

Поскольку знак «равно» (=) используется для указать формулу при введите текст или значение в ячейку, Excel его значение. однако это может привести к непредвиденным результатам фильтрации. Чтобы указать оператор сравнения равенства для текста или значения, введите условия в качестве строкового выражения в соответствующей ячейке в диапазоне условий:

где ввод — искомый текст или значение. Например:

Вычисляется и отображается

Учет регистра

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

Использование заранее определенных имен

Вы можете назвать диапазон Условия,и ссылка на диапазон автоматически появится в поле Диапазон условия. Вы также можете определить имя База данных для фильтруемого диапазона списка и имя Извлечь для области, в которой нужно вставки строк, и эти диапазоны автоматически появятся в полях Диапазон списка и Копировать в соответственно.

Создание условий с помощью формулы

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

Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

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

Не используйте название столбца в качестве названия условия. Либо оставьте название условия пустым, либо используйте название, не являющееся названием столбца в диапазоне списка (в последующих примерах: «Среднее арифметическое» и «Точное совпадение»).

Если в формуле используется название столбца, а не относительная ссылка на ячейку или имя диапазона, в ячейке с условием будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно проигнорировать, так как она не влияет на то, как фильтруется диапазон списка.

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

Все остальные ссылки в формуле должны быть абсолютными.

Несколько условий, один столбец, любое из условий истинно

Логическое выражение: (Продавец = «Егоров» ИЛИ Продавец = «Грачев»)

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

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

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

Несколько условий, несколько столбцов, все условия истинны

Логическое выражение: (Тип = «Фрукты» И Продажи > 1000)

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

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

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

Несколько условий, несколько столбцов, любое из условий истинно

Логическое логика: (Тип = «Фрукты» ИЛИ Продавец = «Грачев»)

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

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

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

Несколько наборов условий, один столбец во всех наборах

Логическое выражение: ( (Продажи > 6000 И Продажи Тип

Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

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

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

Несколько наборов условий, несколько столбцов в каждом наборе

Логическое выражение: ( (Продавец = «Егоров» И Продажи > 3000) ИЛИ (Продавец = «Грачев» И Продажи > 1500) )

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

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

Условия с подстановочными знаками

Логическое выражение: Продавец = имя со второй буквой «г»

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

Чтобы найти строки, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства ( =). Например, если ввести условие Бел, будут найдены строки с ячейками, содержащими слова «Белов», «Беляков» и «Белугин».

Воспользуйтесь подстановочными знаками.

Любой символ
Например, если найти «кузнецов» и «смайл»

Любое количество символов
Например, если найти «*-восток», будут «северо-восток» и «Юго-восток»

Вопроси метка, звездочка или тильда
Например, fy91

? соответствует результат «ан91?»

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

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

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

Источник