Фильтрация данных в диапазоне или таблице
Используйте фильтры, чтобы временно скрывать некоторые данные в таблице и видеть только те, которые вы хотите.
Фильтрация диапазона данных
- Выберите любую ячейку в диапазоне данных.
- Выберите Данные >Фильтр.
- Выберите стрелку в столбце
. - Выберите пункт Текстовые фильтры или Числовые фильтры, а затем — сравнение, например пункт Между.
- Введите условия фильтрации и нажмите кнопку ОК.
Фильтрация данных в таблице
При создании и формате таблицв их заглавные таблицы автоматически добавляются элементы управления фильтром.
- Выберите стрелку в столбца, который вы хотите отфильтровать.
- Снимите флажок (Выделить все) и установите флажки для тех элементов, которые вы хотите отобразить.
- Нажмите кнопку ОК.
Стрелка заглавного изменится на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.
Фильтрация данных в Excel
Для обработки части большого диапазона данных можно воспользоваться фильтрацией. При фильтрации остаются видимыми только те строки, которые удовлетворяют заданным условиям, а остальные скрываются до тех пор, пока не будет отменен фильтр.
В Excel предусмотрено три типа фильтров:
- Автофильтр – для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора.
- Срезы – интерактивные средства фильтрации данных в таблицах.
- Расширенный фильтр – для фильтрации данных с помощью сложного критерия отбора.
Автофильтр
- Выделить одну ячейку из диапазона данных.
- На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
- Щелкнуть по кнопке Фильтр [Filter] .
- В верхней строке диапазона возле каждого столбца появились кнопки со стрелочками. В столбце, содержащем ячейку, по которой будет выполняться фильтрация, щелкнуть на кнопку со стрелкой. Раскроется список возможных вариантов фильтрации.
- Выбрать условие фильтрации.
Варианты фильтрации данных
- Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
- Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
- Можно воспользоваться строкой быстрого поиска
- Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:
- При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
- При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
- При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
- Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.
Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.
Отмена фильтрации
Для того чтобы отменить фильтрацию диапазона данных, достаточно повторно щелкнуть по кнопке Фильтр.
Чтобы снять фильтр только с одного столбца, достаточно щелкнуть по кнопке со стрелочкой в первой строке и в контекстном меню выбрать строку: Удалить фильтр из столбца.
Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные
Срезы
Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.
Создание срезов
В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.
Для этого нужно выполнить следующие шаги:
-
Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].
- В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
Форматирование срезов
- Выделить срез.
- На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.
- Выбрать кнопку с подходящим стилем форматирования.
Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.
Расширенный фильтр
Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.
Задание условий фильтрации
Вначале надо скопировать шапку таблицы. Построить таблицу условий отбора данных можно либо на активном листе, либо на другом. Предпочтительнее на другом листе, иначе после фильтрации эти условия или их часть могут быть скрыты.
Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие « И »), а в разных строках — как условие выбора (« ИЛИ »). В качестве условия может быть совпадение значения, которое заносится в ячейку, или сравнение с заданным в ячейке значением с помощью знаков или > . Если один столбец должен удовлетворять двум условиям, его заголовок нужно повторить еще раз и записать в этом столбце второе условие.
- В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].
- Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
- Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
- Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
- Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].
Функция ФИЛЬТР
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше
Функция ФИЛЬТР позволяет выполнять фильтрацию диапазона данных на основе условий, которые вы определяете.
В следующем примере мы использовали формулу =FILTER(A5:D20,C5:C20=H2,»») для возврата всех записей Для Apple, как указано в ячейке H2, и если яблок нет, возвращается пустая строка («»).
Функция ФИЛЬТР фильтрует массив с учетом массива логических значений (ИСТИНА/ЛОЖЬ).
=ФИЛЬТР(массив;включить;[если_пусто])
Массив или диапазон для фильтрации
Массив логических переменных с аналогичной высотой или шириной, что и массив.
Значение, возвращаемое, если все значения во включенном массиве пустые (фильтр не возвращает ничего)
- Массив может рассматриваться как ряд значений, столбец со значениями или комбинация строк и столбцов значений. В приведенном выше примере массив для нашей формулы ФИЛЬТР представляет собой диапазон A5:D20.
- Функция ФИЛЬТР возвращает массив, который будет переноситься на другие ячейки, если является конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете структурированные ссылки. Дополнительные сведения см. в статье о переносе массива.
- Если набор данных потенциально может возвращать пустое значение, используйте третий аргумент ([если_пусто]). В противном случае возникнет ошибка #ВЫЧИС! , так как Excel в настоящее время не поддерживает пустые массивы.
- Если какое-либо значение аргумента include является ошибкой (#N/A, #VALUE и т. д.) или не может быть преобразовано в логическое значение, функция 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 или получить поддержку в сообществах.
Как работать с фильтрами в Excel
Фильтрация данных в Excel — это процесс выборки определенных строк на основе заданных условий. Рассмотрим примеры фильтрации с русской локализацией Excel.
- Фильтрация с использованием автофильтра:
- Откройте таблицу в Excel.
- Выберите заголовки столбцов, по которым хотите провести фильтрацию.
- На вкладке «Главная» найдите раздел «Сортировка и фильтр» и нажмите на кнопку «Фильтр».
- Появятся стрелки рядом с заголовками столбцов.
- Нажмите на стрелку у нужного столбца и выберите значения, по которым нужно отфильтровать данные.
Фильтрация данных различных типов
В Excel существует несколько типов данных, которые могут быть использованы для организации и анализа информации. Ниже приведены основные типы данных в Excel:
- Числовые данные: Этот тип данных используется для представления числовых значений. Он может включать целые числа (например, 1, 100, -5), числа с плавающей запятой (например, 3,14; 2,718) и денежные значения (например, $10.50). Числовые данные могут использоваться для математических операций, вычислений и статистического анализа.
- Текстовые данные: Этот тип данных используется для хранения текстовой информации. Он может включать буквы, цифры, символы и пробелы. Текстовые данные часто используются для идентификации, названий, описаний и других текстовых полей.
- Дата и время: Этот тип данных используется для работы с датами и временем. В Excel даты могут быть представлены в различных форматах (например, день, месяц, год или день/месяц/год) и временем (например, часы, минуты, секунды). Эти данные позволяют выполнять операции с датами, такие как сортировка, фильтрация и расчеты временных интервалов.
- Логические данные: Этот тип данных представляет собой два возможных значения: «Истина» или «Ложь» (или 1 и 0 соответственно). Логические данные используются для логических вычислений и условных операций. Они также широко применяются в формулах и функциях для проверки условий.
- Ошибки: Этот тип данных используется для обозначения ошибок в ячейках. В Excel существуют различные типы ошибок, такие как #ДЕЛ/0! (деление на ноль), #Н/Д (нет доступных данных) и другие. Ошибки помогают идентифицировать проблемные значения или некорректные формулы.
- Пустые ячейки: Пустые ячейки не содержат никаких данных и используются для обозначения отсутствия информации или невведенных значений. Они могут быть оставлены пустыми или могут быть заполнены позднее.
Возможные фильтры для чисел:
- Равно (Equals): Отображает только те строки, в которых числовое значение равно указанному значению.
- Не равно (Does Not Equal): Отображает только те строки, в которых числовое значение не равно указанному значению.
- Больше (Greater Than): Отображает только те строки, в которых числовое значение больше указанного значения.
- Больше или равно (Greater Than or Equal To): Отображает только те строки, в которых числовое значение больше или равно указанному значению.
- Меньше (Less Than): Отображает только те строки, в которых числовое значение меньше указанного значения.
- Меньше или равно (Less Than or Equal To): Отображает только те строки, в которых числовое значение меньше или равно указанному значению.
- Между (Between): Отображает только те строки, в которых числовое значение находится в указанном диапазоне (например, между 10 и 20).
- Первые 10: Отображает только те строки, в которых числовое значение входит в диапазон n наибольших или наименьших значений.
- Больше среднего: Отображает только те строки, в которых числовое значение больше среднего арифметического значения указанного столбца.
- Меньше среднего: Отображает только те строки, в которых числовое значение меньше среднего арифметического значения указанного столбца.
Фильтрация текстовых данных
- Равно (Equals): Отображает только те строки, в которых текст полностью соответствует указанному значению.
- Не равно (Does Not Equal): Отображает только те строки, в которых текст отличается от указанного значения.
- Начинается с (Begins With): Отображает только те строки, в которых текст начинается с указанной последовательности символов.
- Заканчивается на (Ends With): Отображает только те строки, в которых текст заканчивается на указанную последовательность символов.
- Содержит (Contains): Отображает только те строки, в которых текст содержит указанную последовательность символов.
- Не содержит (Does Not Contain): Отображает только те строки, в которых текст не содержит указанную последовательность символов.
- Пусто (Blanks): Отображает только пустые ячейки в столбце текстовых данных.
- Не пусто (NonBlanks): Отображает только непустые ячейки в столбце текстовых данных.
Фильтрация столбцов с датой/временем
- Равно (Equals): Отображает только те строки, в которых дата/время точно соответствует указанному значению.
- После: Отображает только те строки, в которых дата/время больше указанного значения.
- До: Отображает только те строки, в которых дата/время меньше указанного значения.
- Между (Between): Отображает только те строки, в которых дата/время находится в указанном диапазоне (например, между двумя датами или временными значениями).
- Сегодня (Today): Отображает только те строки, в которых дата соответствует текущей дате.
- Вчера (Yesterday): Отображает только те строки, в которых дата соответствует предыдущему дню.
- Завтра (Tomorrow): Отображает только те строки, в которых дата соответствует следующему дню.
- Пусто (Blanks): Отображает только пустые ячейки в столбце с датой/временем.
- Последние 7 дней (Last 7 Days): Отображает только те строки, в которых дата находится в течение последних 7 дней.
- Следующие 7 дней (Next 7 Days): Отображает только те строки, в которых дата находится в течение следующих 7 дней.
- Этот месяц (This Month): Отображает только те строки, в которых дата соответствует текущему месяцу.
- Прошлый месяц (Last Month): Отображает только те строки, в которых дата соответствует предыдущему месяцу.
- Следующий месяц (Next Month): Отображает только те строки, в которых дата соответствует следующему месяцу.