Настраиваемый фильтр в excel


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

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

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

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

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

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

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

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

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

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

  1. Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
  2. Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
  3. Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.

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

  1. Можно задать столько условий для фильтрации, сколько нужно.
  2. Критерии выбора данных – на виду.
  3. С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.


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

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

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

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

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

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

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

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

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

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

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

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

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

Основные правила:

  1. Результат формулы – это критерий отбора.
  2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
  3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
  4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

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

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

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

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

Скачать пример работы с расширенным фильтром

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

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

Если для фильтрации данных требуются сложные условия (например, Тип = "Фрукты" ИЛИ Продавец = "Егоров"), можно использовать диалоговое окно Расширенный фильтр.

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

Обзор расширенных условий фильтра

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

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

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

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

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

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

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

Тип

Продавец

Продажи

Тип

Продавец

Продажи

Напитки

Шашков

5 122 ₽

Мясо

Егоров

450 ₽

фрукты

Грачев

6328 ₽

Фрукты

Егоров

6544 ₽

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

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

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

Значение

Пример

= (знак равенства)

Равно

A1=B1

> (знак «больше»)

Больше

A1>B1

< (знак «меньше»)

Меньше

A1<B1

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

Больше или равно

A1>=B1

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

Меньше или равно

A1<=B1

<> (знак «не равно»)

Не равно

A1<>B1

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

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

=''= ввод ''

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

Вводится в ячейку

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

="=Егоров"

=Егоров

="=3000"

=3000

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

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

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

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

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

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

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

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

    =''= ввод ''

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

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

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

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

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

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

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

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

    Тип

    Продавец

    Продажи

    ="=Егоров"

    ="=Грачев"

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

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

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

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

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.

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

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

    Тип

    Продавец

    Продажи

    Мясо

    Егоров

    450 ₽

    фрукты

    Грачев

    6 328 ₽

    Фрукты

    Егоров

    6 544 ₽

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

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

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

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

    Тип

    Продавец

    Продажи

    ="=Фрукты"

    >1 000

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

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

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

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

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.

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

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

    Тип

    Продавец

    Продажи

    фрукты

    Грачев

    6 328 ₽

    Фрукты

    Егоров

    6 544 ₽

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

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

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

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

    Тип

    Продавец

    Продажи

    ="=Фрукты"

    ="=Грачев"

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

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

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

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

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.

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

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

    Тип

    Продавец

    Продажи

    фрукты

    Грачев

    6 328 ₽

    Фрукты

    Егоров

    6 544 ₽

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

Логическое выражение:     ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )

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

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

    Тип

    Продавец

    Продажи

    Продажи

    >6 000

    <6 500

    <500

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

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

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

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

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$D$3.

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

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

    Тип

    Продавец

    Продажи

    Мясо

    Егоров

    450 ₽

    фрукты

    Грачев

    6 328 ₽

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

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

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

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

    Тип

    Продавец

    Продажи

    ="=Егоров"

    >3 000

    ="=Грачев"

    >1 500

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

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

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

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

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3. Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .

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

    Тип

    Продавец

    Продажи

    фрукты

    Грачев

    6 328 ₽

    Фрукты

    Егоров

    6 544 ₽

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

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

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

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

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

      Используйте

      Чтобы найти

      ? (вопросительный знак)

      Любой символ (один)
      Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"

      * (звездочка)

      Любое количество символов
      Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"

      ~ (тильда), за которой следует ?, * или ~

      Вопросительный знак, звездочку или тильду
      Пример: условию "ан91~?" соответствует результат "ан91?"

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

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

    Тип

    Продавец

    Продажи

    ="=Мя*"

    ="=?г*"

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

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

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

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

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

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

  7. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.

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

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

    Тип

    Продавец

    Продажи

    Напитки

    Шашков

    5 122 ₽

    Мясо

    Егоров

    450 ₽

    фрукты

    Грачев

    6 328 ₽

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

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

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

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

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

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

Одно условие для одного столбца

Например, нужно из существующей таблицы выделить записи (строки) людей с ростом больше 180 см:

Сначала создаем Диапазон условий. Например:

Далее выделяем любую ячейку внутри таблицы и вызываем диалоговое окно Расширенного фильтра (на вкладке Данные в группе Сортировка и фильтр нажимаем кнопку Дополнительно:

Выбираем – скопировать результат в другое место, Исходный диапазон – оставляем (если мы предварительно выделили всю исходную таблицу), Диапазон условий$D$24:$D$25, Поместить результат в диапазон – указываем любую ячейку в свободной области рабочего листа (где-нибудь ниже основной таблицы и диапазона условий):

Новая таблица будет нарисована в области с указанной ячейкой в левом верхнем углу. Если там находились какие-либо данные, то они будут перезаписаны. Отменить результат операции нельзя!

Результат фильтрации:

Несколько условий для одного столбца (логическое ИЛИ)

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

В диалоговом окне Расширенного фильтра указываем нужный диапазон условий (D38:D41) и новое место для отфильтрованной таблицы:

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

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

Несколько условий (логическое И)

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

Таким же образом можно составлять более сложные условия отбора, комбинируя логические операции И и ИЛИ. Например:

Вычисляемые условия

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

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

Формула, используемая для создания условия отбора, должна использовать относительную ссылку на первую ячейку в соответствующем столбце. Все остальные ссылки в формуле должны быть абсолютными ссылками, а формула должна возвращать результат ИСТИНА или ЛОЖЬ:

Результат фильтрации:

как создать и для чего использовать автофильтр и расширенный фильтр

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

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

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

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

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

  1. Сортировка по возрастанию или по убыванию.
  2. «Все» — Excel покажет (восстановит) все строки.
  3. «Первые 10» — Excel отобразит первые 10 записей. При выборе этого пункта откроется новое окно, в котором можно указать число записей, выбрать, какие из них показывать (наибольшие или наименьшие), а также установить ограничение на количество отображаемых записей.
  4. «Условие» — здесь пользователь может самостоятельно создать 2 критерия отбора данных, объединяя их простыми операторами И, ИЛИ.
  5. Любой из элементов. Здесь можно выбрать любое значение, которое находится в колонке. Например, если столбец состоит из наименований товаров, то в перечне элементов будут указаны абсолютно все товары. Пользователь может указать любой из них.
  6. «Пустые» и «Непустые» — Excel отобразит пустые (или непустые) ячейки. Данная опция появляется только в том случае, если в столбце есть незаполненные поля.

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

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

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

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

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

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

Таким образом, принцип работы фильтрации в Microsoft Excel должен быть понятен. Этим способом можно отбирать любые элементы из базы данных, насколько большой она бы ни была. Такая процедура поможет упростить работу пользователя с большими объемами данных.

Расширенный фильтр в Excel | Блог Александра Воробьева

Опубликовано 20 мая 2014
Рубрика: Справочник Excel | 4 комментария

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

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

Расширенным фильтром можно отфильтровать в таблице всё, что угодно!!! Пределом возможностей этого инструмента является только фантазия пользователя!

Вы читаете четвертый пост в цикле статей о создании баз данных в MS Excel и организации  обработки информации.

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

Фильтр №2 – расширенный фильтр!

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

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

1. Открываем в MS Excel файл database.xls.

2. Создавая таблицу критериев отбора расширенного фильтра, рекомендую вначале действовать шаблонно, не задумываясь о задачах, которые предстоит решать. Копируем все заголовки столбцов-полей из ячеек A7…F7 в ячейки A1…F1 – заготовка для таблицы критериев готова! Можно приступать к основной работе.

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

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

Задача №5:

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

1. Записываем параметр фильтрации – слово «балка» в столбце «Изделие» верхней таблицы критериев отбора.

2. Активируем («встаем мышью») любую ячейку внутри таблицы базы данных – это обеспечит автоматическое заполнение окошка «Исходный диапазон» в выпадающем диалоговом окне «Расширенный фильтр».

3. Включаем расширенный фильтр в Excel 2003 через главное меню программы. Выбираем: «Данные» — «Фильтр» — «Расширенный фильтр».

4. В выпавшем окне «Расширенный фильтр» заполняем окна так, как показано на снимке экрана, расположенном ниже этого текста.

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

5. Результат работы расширенного фильтра – на следующем снимке экрана. Расширенный фильтр показал все записи базы данных, которые содержат слово «балка» в столбце «Изделие» — задача выполнена.

Регистр букв не влияет на результаты фильтрации!

Правила совместной «работы» нескольких условий отбора

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

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

1. Условия отбора, размещенные на одной строке в одном или нескольких разных столбцах таблицы критериев, предписывают расширенному фильтру показывать строки, для которых выполняются одновременно все без исключения эти условия. (Параметры отбора соединены логическими операторами «И».)

2. Условия отбора, размещенные на разных строках в одном или нескольких столбцах таблицы критериев, предписывают расширенному фильтру показывать все строки, для которых выполняется хотя бы одно из этих условий. (Параметры отбора соединены логическими операторами «ИЛИ».)

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

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

Задача №6:

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

В результате работы расширенного фильтра показаны все пластины базы данных, имеющие массу менее 0,1 тонны (иллюстрация правила №1).

Задача №7:

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

Задача решена – показаны записи обо всех пластинах базы данных и обо всех изделиях тяжелее 1 тонны (иллюстрация правила №2).

Для отмены действия расширенного фильтра необходимо выполнить команду главного меню программы Excel «Данные» — «Фильтр» — «Отобразить все».

Обращаю внимание на необходимость внимательного контроля корректности указания исходного диапазона базы данных и диапазона таблицы условий в выпадающем диалоговом окне «Расширенный фильтр»!

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$F$3»!

Если в диапазоне условий или в вашей базе окажутся полностью  пустые строки, то расширенный фильтр работать не будет!

Итоги.

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

  • * — любое количество любых символов
  • ? – один любой символ
  • = — равно
  • < — меньше
  • > — больше
  • <= — меньше или равно
  • >= — больше или равно
  • <> — не равно

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

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

«Поковыряйтесь» самостоятельно в возможностях этого инструмента — он стоит того, чтобы в нем разобраться! Возросшая эффективность вашей работы многократно перекроет затраты времени потраченного на изучение!

Продолжение темы хранения и управления большими объемами информации — в следующих статьях цикла.

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, вопросы и замечания пишите в комментариях внизу страницы.

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Как добавить фильтр в Excel - Фильтр Excel, фильтрация данных

Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами. Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ. Используют её экономисты, бухгалтера, ученые, студенты и представители других профессий, которым необходимо обрабатывать математические данные.

Одной из самых удобных функций в этой программе является фильтрация данных. Рассмотрим, как настроить и использовать MS excel фильтры.

Где в Excel фильтры — их виды

Основные функции фильтрации в Excel:

  • отбор по цвету: дает возможность отсортировать данные по цвету шрифта или заливки,
  • текстовые фильтры в excel: позволяют задать те или иные условия для строк, например: меньше, больше, равно, не равно и другие, а также задать логические условия — и, или,
  • числовые фильтры: отсортировывают по числовым условиям, например, ниже среднего, первые 10 и другие,
  • ручной: отбор можно выполнять по выбранным самостоятельно критериям.

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

Как использовать расширенный фильтр в Excel — как его настроить

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

  1. Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
  2. Создать таблицу с условиями отбора.
  3. Запустить расширенный фильтр.

Рассмотрим пример настройки. У нас есть таблица со столбцами Товар, Количество и Цена.

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

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора — Гвозди (ячейка А2).

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

Затем необходимо:

  1. выделить любую из ячеек ,
  2. открыть «Расширенный фильтр» по пути: Данные — Сортировка и фильтр — Дополнительно,
  3. проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
  4. в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

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

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

Также легко настроить отбор по строкам, содержащим точно слово «Гвозди» без учета регистра. В диапазоне В1:В2 разместим колонку с новым критерием отбора, не забыв указать заголовок столбца, в котором будет выполняться отсев. В ячейке В2 необходимо указать следующую формулу ="=Гвозди«.

Далее потребуется:

  • выделить любую из ячеек таблицы,
  • открыть «Расширенный фильтр»,
  • проверить, что в «Исходный диапазон» попала вся таблица с данными,
  • в «Диапазоне условий» указать В1:В2.

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel. В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

Как сделать фильтр в Excel по столбцам

Информацию в таблице можно фильтровать по столбцам — одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Пример 1

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

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Пример 2

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

Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» — «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

Решение проблемы:

  1. выделить столбец с датами,
  2. открыть вкладку Excel в главном меню,
  3. выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».

К популярным ошибкам пользователей при работе с данной программой также стоит отнести:

  • отсутствие заголовков у столбцов (без них не будут работать фильтрация, сортировка, а также целый ряд других важных параметров),
  • наличие пустых строк и столбцов в таблице с данными (это сбивает систему сортировки, Excel воспринимает информацию как две разные независимые друг от друга таблицы),
  • размещение на одной странице нескольких таблиц (удобнее располагать каждую таблицу на отдельном листе),
  • размещение в нескольких столбцах данных одного типа,
  • размещение данных на нескольких листах, например, по месяцам или годам (объем работы можно сразу умножать на количество листов с информацией).

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

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

ЗАПИСИ ПО ТЕМЕ

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

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

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

Но что, если необходимо отфильтровать данные по нескольким критериям одновременно? Или использовать при фильтрации формулу? Или отобрать значения одновременно в двух и более столбцах? На помощь в такой ситуации может прийти Расширенный фильтр (Advanced Filter).

Команда на ленте

Команда на ленте

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

  • Подготовка диапазона для условий фильтрации
  • Формирование условий фильтрации
  • Работа с мастером "Расширенного фильтра"

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

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

Разберем каждый из этапов его применения поподробнее.

1. Подготовка диапазона для условий фильтрации

В автофильтре условия для фильтрации данных задаются в меню, выпадающем после клика на кнопке в шапке таблицы. В расширенном же - условия фильтрации должны задаваться в отдельном диапазоне. Этот диапазон может располагаться на том же листе, что и данные, на другом листе или даже в другой книге. Главное, чтобы он был отделен от исходных данных как минимум одним пустым столбцом или строкой (чтобы Excel не принял их за один диапазон).

Также важно помнить, что при указании в 3 шаге (работа в Мастере "Расширенного фильтра") диапазона условий нужно выделять шапку и строки, где есть какие-то критерии. Пустые строки выделять не нужно, иначе фильтра воспримет их как сигнал "Отобразить все строки".

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

Правильная организация данных для работы с расширенным фильтром

Правильная организация данных для работы с расширенным фильтром

2. Формирование условий фильтрации

Этот этап является ключевым и раскрывает всю мощь инструмента. Для начала Вы должны научиться правильно задавать критерии для отбора.

Они могут быть 3 видов:

- текстовые критерии

Если в качестве текстового критерия ввести в поле какое-то слово, например, "Москва", то будут отобраны ВСЕ строки, в которых в заданном столбце запись начинается со слова "Москва"

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

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

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

Если нужно найти точное вхождение слова или фразы, то критерий придется задать несколько необычной формулой. Например, чтобы найти строки, в которых записано "Петербург" и не отображать строки "Санкт-Петербург", нужно ввести формулу: ="=Петербург" (именно так, с двумя знаками "=").

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

- числовые критерии и даты

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

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

Также можно вводить выражения с использованием логических операторов (>, <, >=, <=, <>). Например, найти строки с суммой больше 500 000 можно введя критерий >500000

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

Особо внимательным нужно быть при вводе критериев в виде даты. Даты обязательно необходимо вводить через косую черту. Например, чтобы отобрать все сделки после 4 января 2017, нужно ввести критерий по полю "Дата" - >04/01/2017 (в некоторых версиях Excel требуется осуществлять ввод в формате ММ/ДД/ГГГГ, то есть сначала указывать месяц. Имейте это в виду при работе).

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

- формулы

Самое лучшее, что умеет расширенный фильтр - это использовать в качестве критерия формулы. Чтобы все работало, задаваемая Вами формула должна возвращать значение ИСТИНА (и тогда строка выведется) или ЛОЖЬ (строка будет скрыта). Крайне важно - шапка столбца с формулой должна отличаться от любой записи в шапке таблицы (можете вообще оставить ее пустой). При написании формул, не забывайте правильно расставлять абсолютные и относительные ссылки.

Например, если нужно показать топ 5 строк по полю сумма, то необходимо будет ввести следующую формулу:

=F10>НАИБОЛЬШИЙ($F$10:$F$37;6),

где F10 - ячейка первой строки в столбце "Сумма" (она не закреплена, так как формула будет перебирать строки по очереди), $F$10:$F$37 - ссылка на диапазон, который занимает столбец "Сумма" (ссылка закреплена, так как столбец не изменяется).

В результате формула пройдет по всем строкам (от 10-ой до 37-ой) и скроет все, кроме тех, где значение больше шестого по величине (то есть оставит ТОП 5).

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

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

Итак, основные концепции, которые Вам нужно усвоить для успешного применения Расширенного фильтра:

- заголовок столбца, в котором пишем критерий отбора, должен быть точно таким же, как у того столбца, к которому применяем этот критерий. То есть, если отбираем строки, в которых в столбце "Сумма" значение больше 500, то и условие >500 пишем под шапку "Сумма";

- условия, записанные в одной строке, воспринимаются фильтром как связанные оператором И. Например, на картинке ниже записано условие И год 2017, И город Москва, И менеджер Петров.

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

- условия, записанные в разных строках, воспринимаются фильтром как связанные оператором ИЛИ. Условия могут применяться как к одному столбцу, так и к разным. Например, на картинке ниже записано условие ИЛИ город Москва, ИЛИ менеджер Иванов. Таким образом, каждая строка - это единый набор условий.

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

- если нужно задать условие И, но при этом использовать один и тот же столбец (например, И сумма больше 500 000, И сумма меньше 600 000), то заголовок такого столбца нужно продублировать дважды. Пример:

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

Теперь Вы знаете, какие критерии можно задавать, и как их правильно комбинировать. Этого достаточно, чтобы создавать сложные запросы, которые не под силу обычному автофильтру. Например, если нужно показать все сделки в Москве за 2017 год с суммой больше 500 000, а также одновременно отобразить все сделки Иванова за 2016 год, которые входят в ТОП5, то критерии будут выглядеть вот так:

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

3. Работа с мастером "Расширенного фильтра"

Самое сложное позади - Вы научились формировать критерии отбора. Остался один шаг. Выделяем диапазон с исходными данными (или любую его ячейку - Excel сам определит и выделит нужный массив) и находим на ленте команду "Дополнительно" во вкладке "Данные" в группе "Сортировка и Фильтр". Перед Вами появится окно "Расширенный фильтр"

Мастер "Расширенного фильтра"

Мастер "Расширенного фильтра"

В нем нужно:

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

2) определить диапазон условия (ваша мини-табличка с критериями, автоматически Excel может определить диапазон неточно, лучше каждый раз переназначать его заново), на этом шаге помним, что выделяем только заголовки и строки с критерием, пустые строки включать в диапазон нельзя,

3) выбрать способ обработки (фильтровать данные в вашей исходной таблице или скопировать отфильтрованные строки в другое место),

4) указать, стоит ли показать все записи, или вывести только уникальные.

После настройки нажимаем "ОК" и получаем желаемый результат.

Чтобы очистить фильтр, переместите курсор в шапку таблицы с отфильтрованными данными и воспользуйтесь командой "Очистить" на ленте, или примените Автофильтр (Ctrl+Shift+L).

Команда "Очистить" на ленте

Команда "Очистить" на ленте

Недостатки Расширенного фильтра

Главным недостатком является то, что данный инструмент не является интерактивным и динамическим. Нельзя просто вписать в диапазон условий новый критерий и увидеть результат. Придется каждый раз вызывать Мастер "Расширенного фильтра" и повторять некоторые операции. Конечно, можно обойти и этот недостаток, но это уже тема для другой статьи.

Бонус. Полезный трюк с Расширенным фильтром

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

1) Выделите диапазон

2) Запустите Расширенный фильтр

3) Укажите вот такие параметры:

Извлечение уникальных записей с помощью Расширенного фильтра

Извлечение уникальных записей с помощью Расширенного фильтра

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

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Данные фильтра в диапазоне или таблице

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

Диапазон данных фильтра

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

  2. Выберите пункт Данные > Фильтр .

  3. Выберите стрелку заголовка столбца.

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

  5. Введите критерии фильтрации и выберите OK .

Данные фильтра в таблице

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

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

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

  3. Нажмите OK .

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

Похожие темы

Обучение работе с Excel: фильтрация данных в таблице

Советы и примеры сортировки и фильтрации данных по цвету

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

Фильтрация по расширенным критериям

Удаление фильтра

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

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

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

Подробнее о фильтрации

Два типа фильтров

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

Многоразовый фильтр

Чтобы определить, был ли применен фильтр, найдите значок в заголовке столбца:

90 106
  • Стрелка раскрывающегося списка указывает, что фильтрация включена, но не применяется.

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

  • Кнопка «Фильтр» указывает на то, что был применен фильтр.

    Когда вы наводите указатель мыши на заголовок отфильтрованного столбца, всплывающая подсказка показывает фильтр, примененный к этому столбцу, например, «Цвет ячейки равен красному» или «Больше 150».

  • Результаты повторной фильтрации могут отличаться по следующим причинам:

    90 106
  • Данные были добавлены, изменены или удалены из диапазона ячеек или столбцов таблицы.

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

  • Не смешивайте типы данных

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

    Данные фильтра в таблице

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

    1. Выберите данные для фильтрации.На вкладке Main нажмите Format as Table , а затем выберите Format as Table .

    2. В диалоговом окне Создать таблицу вы можете решить, будут ли у таблицы заголовки.

      90 106
    3. Выберите Моя таблица имеет заголовки , поэтому верхняя строка области данных становится заголовками таблицы.Данные в этой строке не будут фильтроваться.

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

    5. Щелкните OK .

    6. Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтра.

    Диапазон данных фильтра

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

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

    2. На карточке Данные выберите Фильтр .

    Параметры фильтра для таблиц или диапазонов

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

    .

    90 221

    Параметр Числовые фильтры применяет пользовательский фильтр:

    В этом примере, если вы хотите увидеть регионы, в которых продажи за март были меньше 6.000 злотых, вы можете применить пользовательский фильтр:

    Вот как это сделать:

    1. Щелкните стрелку фильтра рядом с Март> Числовые фильтры > Менее и введите 6000.

    2. Нажмите OK .

      Excel для Интернета применяет фильтр и отображает только регионы с продажами менее 6000 долларов США.

    Пользовательские фильтры Фильтры даты и Текстовые фильтры можно применять аналогичным образом.

    Для очистки фильтра колонки

    Чтобы удалить все фильтры в таблице или диапазоне

    .

    Фильтрация с расширенными критериями

    Если данные для фильтрации требуют сложных критериев (например, Тип = «Овощи» ИЛИ Продавец = «Кретович»), вы можете использовать диалоговое окно «Расширенный фильтр» .

    Чтобы открыть диалоговое окно Advanced Filter , нажмите Data > Advanced .

    Обсудить критерии расширенного фильтра

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

    • Команда «Дополнительно» отображает диалоговое окно «Дополнительный фильтр» вместо меню «Автофильтр».

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

    Пример данных

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

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

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

    Тип

    Дилер

    Продажи

    Тип

    Дилер

    Продажи

    Напитки

    Стапор

    5122 зл.

    Мясо

    Кретович

    450 злотых

    овощи

    Мырча

    6328 зл.

    Овощи

    Кретович

    6 544 злотых

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

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

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

    Значение

    Пример

    = (знак равенства)

    равно

    А1 = В1

    > (знак больше)

    Больше

    А1> В1

    <(знак меньше)

    Менее

    А1 <В1

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

    Больше или равно

    А1> = В1

    <= (знак меньше или равен)

    Меньше или равно

    А1 <= В1

    <> (знак неравенства)

    Разное

    А1 <> В1

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

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

    = '' = запись ''

    Где , запись указывает текст или значение для поиска.Примеры:

    Значение, введенное в ячейку

    Результат отображается в Excel

    = "= Кретович"

    = Кретович

    = "= 3000"

    = 3000

    С учетом регистра

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

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

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

    Создайте критерии, используя формулу

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

    • Результатом формулы должно быть либо ИСТИНА, либо ЛОЖЬ.

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

      = '' = запись ''

    • Не используйте метку столбца в качестве метки критерия. Оставьте метки критериев пустыми. Вы также можете использовать метку, которая не является меткой столбца в диапазоне списка (в следующих примерах метки Расчетное среднее и Точное совпадение).

      Если в формуле используется метка столбца вместо относительной ссылки на ячейку или имени диапазона, Excel отображает значение ошибки, например #ИМЯ?, в ячейке, содержащей критерии. или #АРГ! . Вы можете игнорировать эту ошибку, так как она не влияет на то, как фильтруется диапазон списка.

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

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

    Несколько критериев, один столбец, любые истинные критерии

    Логическое условие: (Продавец = "Кретович" ИЛИ Торговец = "Мирча")

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

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

    Тип

    Дилер

    Продажи

    = "= Кретович"

    = "= Мырча"

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

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

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

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

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

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

  • В поле Criterion Range введите ссылку на диапазон критериев, включая метки критериев.В этом примере введите ссылку $A$1:$C$3.

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

  • В этом примере результат фильтрации диапазона списка выглядит следующим образом:

    Тип

    Дилер

    Продажи

    Мясо

    Кретович

    450 злотых

    овощи

    Мырча

    6328 зл.

    Овощи

    Кретович

    6 544 злотых

  • Несколько критериев, несколько столбцов, все критерии истинны

    Логическое условие: (Тип = "Овощи" И Продажи > 1000)

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

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

    Тип

    Дилер

    Продажи

    = "= Овощи"

    > 1000

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

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

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

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

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

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

  • В поле Criterion Range введите ссылку на диапазон критериев, включая метки критериев.В этом примере введите ссылку $A$1:$C$2.

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

  • В этом примере результат фильтрации диапазона списка выглядит следующим образом:

    Тип

    Дилер

    Продажи

    овощи

    Мырча

    6328 зл.

    Овощи

    Кретович

    6 544 злотых

  • Несколько критериев, несколько столбцов, любые истинные критерии

    Логическое условие: (Тип = "Овощи" ИЛИ Продавец = "Мирча")

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

  • Чтобы найти строки с несколькими критериями в нескольких столбцах (где любые критерии могут быть истинными), введите критерии в разных столбцах и строках для диапазона критериев. В этом примере введите:

    Тип

    Дилер

    Продажи

    = "= Овощи"

    = "= Мырча"

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

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

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

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

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

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

  • В поле Criterion Range введите ссылку на диапазон критериев, включая метки критериев.Для этого примера введите ссылку $A$1:$B$3.

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

  • В этом примере результат фильтрации диапазона списка выглядит следующим образом:

    Тип

    Дилер

    Продажи

    овощи

    Мырча

    6328 зл.

    Овощи

    Кретович

    6 544 злотых

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

    Логическое условие: ((Продажи> 6000 И Продажи <6500) ИЛИ (Продажи <500))

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

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

    Тип

    Дилер

    Продажи

    Продажи

    > 6000

    <6500

    <500

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

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

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

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

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

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

  • В поле Criterion Range введите ссылку на диапазон критериев, включая метки критериев.Для этого примера введите ссылку $A$1:$D$3.

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

  • В этом примере результат фильтрации диапазона списка выглядит следующим образом:

    Тип

    Дилер

    Продажи

    Мясо

    Кретович

    450 злотых

    овощи

    Мырча

    6328 зл.

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

    Логическое условие: ((Продавец = "Кретович" И Продажи> 3000) ИЛИ (Продавец = "Мирча" И Продажи> 1500))

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

  • Чтобы найти строки, соответствующие нескольким наборам критериев (каждый набор содержит критерии для нескольких столбцов), введите каждый набор критериев в отдельный столбец и строку. В этом примере введите:

    Тип

    Дилер

    Продажи

    = "= Кретович"

    > 3000

    = "= Мырча"

    > 1500

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

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

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

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

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

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

  • В поле Criterion Range введите ссылку на диапазон критериев, включая метки критериев.Для этого примера введите $A$1:$C$3. Чтобы временно скрыть диалоговое окно расширенного фильтра при выборе диапазона критериев, нажмите кнопку Свернуть диалоговое окно .

  • В этом примере результат фильтрации диапазона списка выглядит следующим образом:

    Тип

    Дилер

    Продажи

    овощи

    Мырча

    6328 зл.

    Овощи

    Кретович

    6 544 злотых

  • Критерии с подстановочными знаками

    Логическое условие: Продавец = Фамилия со второй буквой "о"

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

    • Введите один или несколько символов без знака равенства ( = ), чтобы найти строки, содержащие текстовые значения, начинающиеся с этих символов в своих столбцах.Например, ввод Kre в качестве текстового критерия приведет к поиску в Excel слов «Kretowicz», «Cream» и «Drafter».

    • Использовать подстановочный знак.

      Использовать

      Найти

      ? (вопросительный знак)

      Любой одиночный символ
      Например, к?т находит слова "кит" и "кот"

      * (звездочка)

      Любое количество из
      символов Например, k*c находит слова «одеяло» и «насыпь».

      ~ (тильда), затем ?, *, Или ~

      Знак вопроса, звездочка или тильда
      Например, fy91~? приводит к поиску строки "fy91?"

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

  • В строках под метками столбцов введите критерии, которым вы хотите соответствовать. В этом примере введите:

    Тип

    Дилер

    Продажи

    = "= Я *"

    ="=?У*"

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

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Дополнительно .

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

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

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

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

  • В поле Criterion Range введите ссылку на диапазон критериев, включая метки критериев.Для этого примера введите ссылку $A$1:$B$3.

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

  • В этом примере результат фильтрации диапазона списка выглядит следующим образом:

    Тип

    Дилер

    Продажи

    Напитки

    Стапор

    5122 зл.

    Мясо

    Кретович

    450 злотых

    овощи

    Мырча

    6328 зл.

  • Нужна дополнительная помощь?

    Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить техническую поддержку в сообществе ответов.

    .

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

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

    Excel фильтрует более 2 критериев

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

    Можно использовать несколько фильтров в Excel с автофильтром, чтобы применить расширенный фильтр к открытой электронной таблице и найти соответствие нескольким критериям в одном или нескольких столбцах.

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

    1 - Фильтр Excel 2013 по одному критерию

    Первый и самый простой способ отфильтровать по одному или двум критериям — применить фильтр в меню Данные => Фильтр.

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

    • ABC для записей, содержащих ABC,
    • ABC * для записей, начинающихся с ABC ,
    • * ABC для записей, оканчивающихся на ABC.

    Это также способ выполнить фильтр Excel для нескольких столбцов, выбрав фильтр, который будет применяться к каждому столбцу для данных, хранящихся в этом столбце.

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

    2 — Двойной фильтр Excel

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

    В открытом поле можно ввести один или два критерия - если выбрать ИЛИ, будут применены оба. Если вы выберете И (по умолчанию), будут выбраны только записи, соответствующие обоим критериям.

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

    Excel 2013: фильтрация данных — GCFLearnFree — GCFGlobal.org

    3 — расширенные фильтры Excel с несколькими критериями в одном столбце

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

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

    И вуаля! См. приведенный ниже пример: список отелей был отфильтрован для всех отелей Holiday Inn, Crowne Plaza, Radisson или Novotel с помощью расширенного фильтра Excel 2013, в результате чего был создан пользовательский автоматический фильтр Excel с более чем двумя критериями, который также называется расширенным фильтром Excel по нескольким критериям в один столбец, так как разные критерии применяются одновременно в одном и том же столбце.

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

    См. ниже результат с применением некоторых фильтров Excel.

    Фильтрация и сортировка данных — Использование Microsoft Excel — Руководства по исследованиям

    Пользовательский пользовательский фильтр Excel

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

    Затем выберите столбец для фильтрации. Откройте меню> данные> фильтр> дополнительно.

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

    Заполните 2019 Excel для начинающих видео в видео

    .

    Excel (английский): фильтрация данных

    Урок 20: Данные фильтра

    / en / tr_pl-excel / сортировка данных / содержание /

    Введение

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

    Необязательно: Загрузите нашу рабочую книгу.

    Посмотрите видео ниже, чтобы узнать больше о фильтрации данных в Excel.

    Для фильтрации данных:

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

    1. Для правильной работы фильтрации рабочий лист должен содержать строку заголовка , которая идентифицирует имя каждого столбца.В нашем примере рабочий лист разделен на разные столбцы, которые идентифицируются ячейками заголовка в строке 1: Идентификационный номер , тип , детали устройство и так далее.
    2. Выберите вкладку Данные и нажмите Фильтр .
    3. Стрелка раскрывающегося списка появится в ячейке заголовка для каждого столбца.
    4. Щелкните стрелку раскрывающегося списка столбца, который вы хотите отфильтровать.В нашем примере мы отфильтруем столбец B, , чтобы отобразить только определенные типы приборов.
    5. меню Появляется фильтр .
    6. Снимите флажок в поле рядом с Выбрать все, , чтобы быстро отменить выбор всех данных.
    7. Установите флажок рядом с данными, которые вы хотите отфильтровать, а затем нажмите OK . В этом примере мы выберем Ноутбук и Проектор, для отображения только этих типов устройств.
    8. Данные будут фильтроваться , временно скрывая содержимое, не соответствующее критериям. В нашем примере видны только ноутбуки и проекторы.

    Параметры фильтра также можно получить с помощью команды Сортировка и фильтрация на вкладке Инструменты Мастер .

    Чтобы использовать несколько фильтров:

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

    1. Нажмите раскрывающийся список стрелку столбца, который вы хотите отфильтровать. В этом примере мы добавим фильтр в столбец D, для отображения информации по дате.
    2. Появится меню Фильтр .
    3. Установите флажок или в зависимости от данных, которые вы хотите отфильтровать, а затем нажмите OK .В нашем примере мы будем отмечать все, кроме 9000, 9 августа года.
    4. Будет применен новый фильтр. В нашем примере рабочий лист теперь отфильтрован, чтобы отображать только ноутбуки и проекторы, которые были изъяты в августе.
    Для очистки фильтра:

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

    1. Щелкните стрелку раскрывающегося списка фильтра, который необходимо очистить.В нашем примере мы очистим фильтр в столбце D.
    2. Появится меню Фильтр .
    3. Выберите Снимите фильтр с [ИМЯ СТОЛБЦА] в меню Фильтр. В нашем примере мы выбираем . Очистим фильтр от «Проверено ».
    4. Фильтр будет удален из столбца. Отобразятся ранее скрытые данные.

    Чтобы удалить все фильтры с рабочего листа, щелкните Фильтр на вкладке Данные .

    Расширенная фильтрация

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

    Для фильтрации с помощью поиска:

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

    1. Выберите вкладку Данные и нажмите Фильтр . В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка . Примечание : Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
    2. Щелкните стрелку раскрывающегося списка столбца, который вы хотите отфильтровать. В нашем примере мы будем фильтровать C.
    3. Появится меню Фильтр . Введите поисковый запрос в поле поиска . Результаты поиска автоматически появятся в поле Фильтры текста по мере ввода. В нашем примере мы набрали бы saris, , чтобы найти любое оборудование Saris. Нажмите OK , когда закончите.
    4. Лист будет отфильтрован в соответствии с поисковым запросом. В нашем примере рабочий лист теперь отфильтрован для отображения только оборудования Saris.
    Чтобы использовать расширенные текстовые фильтры:

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

    1. Выберите вкладку Данные и нажмите Фильтр . В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка . Примечание : Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
    2. Щелкните стрелку раскрывающегося списка столбца, который вы хотите отфильтровать. В нашем примере мы будем фильтровать C.
    3. Появится меню Фильтр . Наведите указатель мыши на текстовых фильтров , а затем выберите нужный текстовый фильтр в раскрывающемся меню.В нашем примере мы выберем опцию Не содержит..., для отображения данных, не содержащих указанный текст.
    4. Появится диалоговое окно Автофильтр n Пользовательский . Введите текст, который вы хотите справа от фильтра, затем нажмите OK . В нашем примере мы введем ноутбук, , чтобы исключить все элементы, содержащие это слово.
    5. Данные будут отфильтрованы выбранным текстовым фильтром.В нашем примере на листе теперь отображаются элементы, не содержащие слово ноутбук .
    Для использования дополнительных фильтров номеров:

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

    1. Выберите вкладку Данные на ленте и щелкните Фильтр . В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка . Примечание : Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
    2. Щелкните стрелку раскрывающегося списка столбца, который вы хотите отфильтровать. В нашем примере мы отфильтруем столбец A, , чтобы отобразить только определенный диапазон идентификационных номеров.
    3. Появится меню Фильтр . Наведите указатель мыши на Числовые фильтры , а затем выберите нужный числовой фильтр в раскрывающемся меню.В нашем примере мы выбираем M Между, для отображения идентификационных номеров из определенного диапазона номеров.
    4. Появится диалоговое окно Пользовательский автофильтр . Введите нужные цифры справа от каждого фильтра, затем нажмите OK . В нашем примере мы хотим фильтровать по идентификационным номерам, большим или равным 3000, , но меньшим или равным 6000 , которые будут отображать идентификационные номера в диапазоне 3000-6000.
    5. Данные будут отфильтрованы выбранным числовым фильтром. В нашем примере видны только элементы с идентификационным номером от 3000 до 6000 .
    Для использования расширенных фильтров даты:

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

    1. Выберите вкладку Данные и нажмите Фильтр . В ячейке заголовка для каждого столбца появится стрелка раскрывающегося списка . Примечание : Если вы уже добавили фильтры на лист, этот шаг можно пропустить.
    2. Щелкните стрелку раскрывающегося списка столбца, который вы хотите отфильтровать. В нашем примере мы будем фильтровать D, , чтобы отображать только указанный диапазон дат.
    3. Появится меню Фильтр .Наведите указатель мыши на Фильтры даты , а затем выберите нужный фильтр даты из раскрывающегося меню. В нашем примере мы выбираем опцию M между ... , чтобы увидеть оборудование, которое было проверено в период с 15 июля по 15 августа.
    4. Появится диалоговое окно Пользовательский автофильтр . Введите нужные даты справа от каждого фильтра, затем нажмите OK . В нашем примере мы хотим использовать фильтр для даты, равной или превышающей 15 июля 2015 г., , и даты, равной или предшествующей 15 августа 2015 г., , который будет отображать диапазон между этими датами.
    5. Лист будет отфильтрован в соответствии с выбранным фильтром даты. В нашем примере теперь мы можем видеть, какие элементы были извлечены в период с 15 июля по 15 августа .

    Вызов!

    1. Откройте нашу рабочую тетрадь.
    2. Щелкните вкладку Challenge в левом нижнем углу рабочей книги.
    3. Применить фильтр, для отображения только Electronics и Instrument .
    4. Используйте функцию поиска , , чтобы отфильтровать описание предмета, содержащее слово Sansei .После этого вы должны увидеть шесть записей.
    5. Очистить фильтр Описание товара.
    6. Используя числовой фильтр , отобразите суммы кредита больше или равные 100 долларов США.
    7. Фильтр для отображения только элементов с крайним сроком в 2016 г.
    8. По завершении ваша книга должна выглядеть следующим образом:

    / en / tr_pl-excel / группировка и частичные суммы / содержание /

    .

    Фильтр Excel: как добавить, использовать и удалить

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

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

    • Что такое фильтр в Excel?
    • Как добавить фильтр в Excel
    • Как применить фильтр к одному или нескольким столбцам
    • Как использовать фильтр в Excel
      • Текстовые значения фильтра
      • Номера фильтров
      • Даты фильтра
    • Фильтр Excel по цвету
    • Как фильтровать в Excel с помощью поиска
    • Фильтр по значению или формату выбранной ячейки
    • Повторно применить фильтр после изменения данных на
    • Как скопировать отфильтрованные данные
    • Как очистить фильтр
    • Как удалить фильтр в Excel
    • Фильтр Excel не работает

    Что такое фильтр в Excel?

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

    Фильтр Excel против сортировки Excel

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

    • Для текстовых значений: Сортировка от А до Я, Сортировка от Я до А и Сортировка по цвету.
    • Для чисел: сортировать от меньшего к большему, сортировать от большего к меньшему и сортировать по цвету.
    • Для дат: сортировать от старых к новым, от новых к старым и отсортировать по цвету.

    Разница между сортировкой и фильтрацией в Excel заключается в следующем:

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

    Как добавить фильтр в Excel

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

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

    3 способа добавить фильтр в Excel

    1. На вкладке Данные в группе Сортировка и щелкните Фильтр.
    2. На вкладке «Главная» в группе «Редактирование» нажмите «Сортировка и фильтр» > «Фильтр».
    3. Используйте ярлык Excel Filter для включения/отключения фильтров: Ctrl + Shift + L

    Какой бы метод вы ни использовали, в каждой ячейке заголовка появятся раскрывающиеся стрелки:

    Как применить фильтр в Excel

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

    Чтобы отфильтровать данные в Excel, выполните следующие действия:

    1. Щелкните стрелку раскрывающегося списка для столбца, который вы хотите отфильтровать.
    2. Снимите флажок «Выбрать все», чтобы быстро отменить выбор всех данных.
    3. Установите флажки рядом с данными, которые вы хотите отобразить, и нажмите OK.

    Например, мы можем отфильтровать данные в столбце «Регион» таким образом, чтобы отображались продажи только для Востока и Севера:

    Готово! Фильтр применяется к столбцу A, временно скрывая регионы, кроме Востока и Севера.

    Стрелка вниз в отфильтрованном столбце меняется на кнопку «Фильтр», и при наведении курсора на эту кнопку отображается всплывающая подсказка с указанием используемых фильтров:

    Фильтровать несколько столбцов

    Чтобы применить фильтр Excel к нескольким столбцам, просто повторите описанные выше шаги для любого количества столбцов.

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

    Совет.Чтобы расширить и/или удлинить окно Excel Filter, наведите указатель мыши на ручку внизу и перетащите ее вниз или вправо, когда появится двунаправленная стрелка.

    Пустые фильтры / непустые ячейки

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

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

    Чтобы отфильтровать непустые поля, т. е. отображать только пустые ячейки, снимите флажок (Выбрать все), а затем выберите (Пустой). Это покажет только строки с пустой ячейкой в ​​этом столбце.

    Комментарии:
    • (пусто) доступно только для столбцов, содержащих хотя бы одну пустую ячейку.
    • Если вы хотите удалить пустые строки на основе какого-либо ключевого столбца, вы можете отфильтровать непустые строки в этом столбце, выбрать отфильтрованные строки, щелкнуть правой кнопкой мыши выбранное и нажать «Удалить строку».Если вы хотите удалить только полностью пустые строки и оставить строки с некоторым содержимым и некоторыми пустыми ячейками, проверьте это решение.

    Как использовать фильтр в Excel

    В дополнение к основным параметрам фильтрации, описанным выше, Автофильтр в Excel предоставляет ряд дополнительных инструментов, которые могут помочь вам фильтровать определенные типы данных, такие как текст, числа и даты, именно так, как вы хотите.

    Комментарии:
    • Различные типы фильтров Excel являются взаимоисключающими.Например, вы можете отфильтровать данный столбец по значению ячейки или цвету, но не по обоим параметрам.
    • Для получения правильных результатов не смешивайте разные типы значений в одном столбце, так как для каждого столбца доступен только один тип фильтра. Если столбец содержит несколько типов значений, фильтр добавляется к наиболее часто встречающимся данным. Например, если вы храните числа в определенном столбце, но большинство чисел отформатированы как текст, в этом столбце появятся текстовые фильтры, но не числовые фильтры.

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

    Фильтр текстовых данных

    Если вы хотите отфильтровать текст столбца для чего-то очень конкретного, вы можете использовать множество дополнительных параметров, предоставляемых текстовыми фильтрами Excel, например:

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

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

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

    1. Щелкните стрелку раскрывающегося списка в заголовке столбца и выберите Текстовые фильтры.
    2. В раскрывающемся меню выберите нужный фильтр (в данном примере «Не содержит…»).
    3. Появится диалоговое окно Пользовательский автофильтр. В поле справа от фильтра введите текст или выберите нужный пункт из выпадающего списка.
    4. Нажмите кнопку ОК.

    В результате все строки Bananas, включая зеленые бананы и бананы Goldfinger, будут скрыты.

    Столбец фильтра с 2 критериями

    Чтобы отфильтровать данные в Excel с двумя текстовыми критериями, выполните описанные выше шаги для настройки первого критерия, а затем выполните следующие действия:

    • Выберите переключатель I или Or в зависимости от того, должны ли выполняться оба критерия или любой из них.
    • Выберите оператор сравнения для второго критерия и введите текстовое значение в поле справа.

    Например, вот как отфильтровать строки, содержащие бананы или лимоны:

    Как создать фильтр в Excel с подстановочными знаками

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

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

    Как фильтровать числа в Excel

    Числовые фильтры в Excel позволяют обрабатывать числовые данные различными способами, в том числе:

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

    На следующем снимке экрана показан весь список числовых фильтров, доступных в Excel.

    Например, чтобы создать фильтр, отображающий только заказы на сумму от 250 до 300 долларов США, выполните следующие действия:

    1. Щелкните стрелку автофильтра в заголовке столбца и выберите Числовые фильтры.
    2. Выберите из списка соответствующий оператор сравнения, в данном примере между….
    3. В пользовательском диалоговом окне Автофильтр введите значения нижнего и верхнего пределов. По умолчанию Excel предлагает использовать операторы сравнения «больше или равно» и «меньше или равно». Вы можете изменить их на «Больше чем» и «Меньше чем», если не хотите, чтобы предельные значения учитывались.
    4. Нажмите кнопку ОК.

    В результате видны только заказы на сумму от 250 до 300 долларов:

    Как фильтровать даты в Excel

    Фильтры даты

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

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

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

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

    Если выбрать оператор «Равно», «До», «После», «Между» или «Пользовательский фильтр», появится диалоговое окно «Пользовательский автофильтр», в котором можно указать нужные критерии.

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

    Как фильтровать по цвету в Excel

    Если данные на листе отформатированы вручную или с условным форматированием, он также может фильтровать данные по цвету.

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

    • Фильтр по цвету ячейки
    • Фильтр по цвету шрифта
    • Фильтр по ячейке
    • значок

    Например, если вы отформатировали ячейки в данном столбце с использованием 3 разных цветов фона rs (зеленый, красный и оранжевый) и хотите отображать только оранжевые ячейки, вы можете сделать следующее:

    1. Щелкните стрелку фильтра в ячейке заголовка и выберите Фильтр по цвету.
    2. Выберите нужный цвет — в данном примере оранжевый.

    Вуаля! Видны только значения, отформатированные оранжевым цветом шрифта, все остальные строки временно скрыты:

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

    Как фильтровать в Excel с поиском

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

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

    Чтобы отфильтровать несколько поисковых запросов, примените фильтр в соответствии с первым условием поиска, как показано выше, затем введите второе условие и, как только появятся результаты поиска, установите флажок «Добавить текущий выбор в фильтр» и нажмите «ОК».В этом примере мы добавляем записи «запад» к уже отфильтрованным элементам «восток»:

    Это было довольно быстро, не так ли? Всего три щелчка мышью!

    Фильтр по выбранному значению ячейки или формату

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

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

    В этом примере мы фильтруем данные по значку выбранной ячейки:

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

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

    .
    1. Еще раз нажмите «Применить» на вкладке «Данные» в группе «Фильтр и сортировка».
    2. Нажмите «Сортировка и фильтр» > «Применить» еще раз на вкладке «Главная» в группе «Редактирование».

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

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

    1. Выберите любую отфильтрованную ячейку, затем нажмите Ctrl + A, чтобы выбрать все отфильтрованные данные, включая заголовки столбцов.

      Чтобы выбрать отфильтрованные данные, за исключением заголовков столбцов, выберите первую (вверху слева) ячейку данных и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.

    2. Нажмите Ctrl + C, чтобы скопировать выбранные данные.
    3. Переключитесь на другой лист/книгу, выберите верхнюю левую ячейку целевого диапазона и нажмите Ctrl + V, чтобы вставить отфильтрованные данные.

    Примечание. Обычно при копировании отфильтрованных данных в другое место отфильтрованные строки пропускаются.В некоторых редких случаях, в основном в очень больших книгах, Excel может копировать скрытые строки в дополнение к видимым строкам. Чтобы этого не произошло, выделите диапазон отфильтрованных ячеек и нажмите Alt+; чтобы выбрать только видимые ячейки, игнорируя скрытые строки. Если вы не привыкли использовать сочетания клавиш, вместо этого вы можете использовать функцию «Перейти к специальному» (вкладка «Главная»> «Группа редактирования»> «Найти и выделить»> «Перейти к специальному…»> «Только видимые ячейки»).

    Как очистить фильтр

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

    Чтобы очистить фильтр для определенного столбца, нажмите кнопку фильтра в заголовке столбца, а затем щелкните Очистить фильтр из <имя столбца>:

    Как удалить фильтр в Excel

    Чтобы удалить все фильтры на листе, выполните одно из следующих действий:

    • Перейдите на вкладку «Данные» > «Группа сортировки и фильтрации» и нажмите «Очистить».
    • Перейдите на вкладку «Главная» «Редактировать группу» и нажмите «Сортировка и фильтр»> «Очистить».

    Фильтр не работает в Excel

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

    Это в основном то, как вы добавляете, применяете и используете фильтр в Excel. Но это намного больше! В следующем руководстве мы рассмотрим возможности расширенного фильтра и увидим, как фильтровать данные с использованием нескольких наборов критериев. Оставаться в курсе!

    • Расширенный фильтр Excel — как создать и использовать
    • Использование расширенного фильтра в Excel — примеры диапазонов критериев с формулами
    • Как суммировать только отфильтрованные (видимые) ячейки
    • Как фильтровать дубликаты в Excel
    • Как фильтровать уникальные значения в Excel
    • Как добавлять и использовать фильтры в Google Sheets
    .

    Пользовательская фильтрация дат, или какие дополнительные визуализации скрывают в Power BI - Excel BI - Power Query, Power Pivot, Power BI

    Тема работы с датами в Power BI несколько раз появлялась в блоге в контексте преобразования столбца дат в Power Query, относительной фильтрации или стандартных фильтров дат в Power BI. В этом посте мы рассмотрим некоторые пользовательские визуализации для работы с датами, т.е. бесплатно загруженные из магазина Power BI.Благодаря им можно не только создавать эстетические фильтры, но и показывать дополнительные данные. Встречайте: Time Brush Slicer и Timeline Slicer . Кроме того, мы также рассмотрим диаграмму Ганта в виде визуализации в виде временной шкалы .

    Импорт пользовательских визуализаций

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

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

    После импорта значок визуализации появится среди других визуализаций на панели.

    Time Brush Slicer

    Time Brush Slicer представляет собой визуализацию в виде гистограммы, на которой мы отмечаем интересующий нас диапазон для дальнейшего анализа.Найдите Time Brush Slicer в Microsoft Store и добавьте его в визуализацию.

    Затем щелкните значок на панели и добавьте элемент на панель инструментов.

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

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

    Во вкладке форматирования, отмеченной кистью, можно настроить внешний вид фильтра. Например, включим отображение значений по оси Y, но без линии.

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

    Слайсер временной шкалы

    Чуть больше, чем Time Brush Slicer, Timeline Slicer, сертифицированный Microsoft визуальный фильтр даты.Найдите его в магазине приложений и добавьте на вкладку Визуализации.

    После добавления в визуализацию нажмите на иконку визуализации: вставьте ее в отчет и добавьте столбец с датами.

    В отличие от Time Brush Slicer, здесь мы работаем только с датами, без дополнительных опций визуализации. Кроме того, названия месяцев отображаются на польском языке. Давайте посмотрим на другие возможности Timeline Slicer. Прежде всего, он автоматически конвертирует их в недели, месяцы, кварталы и годы, основываясь только на ежедневных датах.Мы можем установить ползунок на интересующий нас уровень на полосе в левом верхнем углу визуализации. Сейчас он на уровне месяца - давайте поменяем на квартал. В результате вся визуализация умещается в отчете без ползунка внизу.

    Перейдем к форматированию на панели Визуализации. Нажмите на значок кисти.

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

    Здесь особенно важны две вкладки; первый - финансовый год. Бывает, что финансовый год в компаниях начинается не 1 января: во вкладке «Финансовый год» вы можете установить тот, который относится к вашей компании. Все квартальные и годовые расчеты будут выполняться на основе новых настроек.Давайте переключим финансовый год на 1 мая и на мгновение вернемся к отображению месяцев. Обратите внимание, что первая четверть начинается в мае.

    Эта настраиваемая визуализация также решает проблему первого дня недели — по умолчанию в Power BI это воскресенье. Вы можете легко установить понедельник на вкладке Первый день недели.

    Как и предыдущая визуализация, Timeline Slicer работает со всей графикой на странице отчета.

    как временная шкала

    Последняя пользовательская визуализация — временная шкала.Однако это не фильтр, а график, используемый в управлении проектами, например, для отслеживания продолжительности задач и временных интервалов их выполнения. Она называется диаграммой Ганта, хотя в то же время ее также показал поляк Кароль Адамецкий. Как и в случае с предыдущими визуализациями, я ищу ее в магазине визуализаций Power BI.

    В примере с данными о продажах мы можем использовать его, чтобы визуализировать, сколько времени прошло от даты заказа до даты отгрузки клиентами.Поставим в качестве Timeline, в поле Entity столбец с именем и фамилией заказчика, в Start Date дату заказа, а в End Date дату отгрузки.

    Строки для каждого клиента - это его заказы, а толщина - продолжительность. Поскольку у нас много клиентов, график не очень разборчив; давайте ограничим его до 3 лучших клиентов по продажам. На панели «Фильтры» выберите тип фильтра Best N и отобразите 3 сверху до Total Sales.

    Sales Total — это мера — сумма продаж.Зачем это нужно? Вы найдете объяснение, среди прочего в записи последнего вебинара Power BI - Dashboard через час.

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

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

    Откройте для себя больше пользовательских визуализаций

    Time Brush Slicer позволяет создавать столбчатую диаграмму с возможностью отмечать интересующие интервалы.Timeline Slicer служит временной шкалой и имеет больше параметров для установки финансового года или первого дня недели. В качестве Timeline будут проверяться данные, в которых можно указать определенный диапазон, например, продолжительность периода исполнения контракта. Показанные настраиваемые визуализации — это лишь примеры того, что вы найдете в магазине приложений Power BI.

    Знаете еще одну интересную визуализацию для фильтрации дат в Power BI?

    Поделитесь в комментарии.

    .

    Фильтрация дат в Excel | Программа Эксель

    Если вы хотите просмотреть приложение определенных стандартов с датами, не указывая конкретные значения, которые вы хотите найти, но с определенным условием, перейдите в «Фильтр дат». Многие параметры открывают одно и то же поле, поэтому нажмите кнопку «Равно», чтобы найти их. Функции, которые открывают это окно:

    • Равно
    • До
    • После
    • Между
    • Пользовательский фильтр

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

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

    • Equals — фильтрация ищет числовые значения, точно равные тому, что введено в поле.
    • Не равно — фильтр ищет все значения, кроме тех, которые точно равны тому, что было введено в поле.
    • Есть после - фильтр ищет даты более поздние, чем дата, указанная в поле.
    • После или равно — фильтр ищет даты, более поздние или равные дате, указанной в поле.
    • Is Before — фильтрация ищет даты, указанные в поле, кроме даты, указанной в поле.
    • Is Before or Equals — фильтр ищет даты, указанные в списке или равные данным, введенным в поле.

    .

    Смотрите также

    Только новые статьи

    Введите свой e-mail

    Видео-курс

    Blender для новичков

    Ваше имя:Ваш E-Mail: