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

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

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

Рассмотрим его применение для следующей таблицы. Расположена она в диапазоне ячеек А6:Е31 . В ней представлена информация об учениках школы.

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

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

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

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

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

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

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

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

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

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

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



Срезы

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

Создание срезов

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

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .

    

2. В группе Сервис  (или на вкладке Вставка  в группе Фильтры ) выбрать кнопку Вставить срез  .

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

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

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Как поставить

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

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

Затем нажать кнопку «Фильтр».

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

В таблице

Пошаговая инструкция: как поставить фильтр в эксель-таблице.

1. Нажать на значок в заголовке столбца.

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

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

3. Просмотреть результат — останутся только строки, соответствующие заданному параметру.

В диапазоне

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

Например, в рассматриваемом файле столбцы В и С имеют числовой отсев.

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

Например, в случае с числовым поиском этот порядок действий выглядит так:

1. Выбираем вид отсева.

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

3. Ввести цифру, которая и станет границей для отсева, — выведены будут все значения больше нее.

4. Нажать «Ок» и оценить результат. В выбранном столбце останутся только значения, превышающие установленную границу.

Использование расширенного фильтра в Эксель

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

1

Подробная инструкция выглядит так:

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

2

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

3

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

4

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

5

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

6

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

7

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

8

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

9

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

10

Как скопировать данные на другой лист с помощью расширенного фильтра в Excel?

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

Вот простой способ решения этой задачи: сделайте следующее:

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

2, Затем нажмите Данные > Дополнительно, см. снимок экрана:

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

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

Скопируйте данные на другой лист с помощью расширенного фильтра с помощью кода VBA

Если у вас есть опыт работы с кодом VBA, вы можете завершить эту работу с помощью следующего кода VBA.

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

2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.

3. Нажмите Вставить > модульи вставьте следующий код в модуль Окно.

Код VBA: скопируйте данные на другой лист с помощью расширенного фильтра:

Sub Advancedfiltertoanothersheet()
'Updateby Extendoffice
    Dim xStr As String
    Dim xAddress As String
    Dim xRg As Range
    Dim xCRg As Range
    Dim xSRg As Range
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the filter range:", "Kutools for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xCRg = Application.InputBox("Please select the criteria range:", "Kutools for Excel", "", , , , , 8)
    If xCRg Is Nothing Then Exit Sub
    Set xSRg = Application.InputBox("Please select the output range:", "Kutools for Excel", "", , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False
    xSRg.Worksheet.Activate
    xSRg.Worksheet.Columns.AutoFit
End Sub

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

5, Затем нажмите OK, и появляется другое диалоговое окно, напоминающее вам о выборе диапазона критериев, на основе которого вы хотите выполнить фильтрацию, см. снимок экрана:

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

7, Наконец, нажмите OK Кнопка, отфильтрованные данные были выведены на другой указанный лист по мере необходимости.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтр в Excel

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

Фильтр устанавливается на заголовки и подзаголовки таблиц; главное, чтобы ячейки, на которые будет устанавливаться фильтр, не были пустыми. А располагается он в меню книги Excel на вкладке «Данные», раздел «Сортировка и фильтр»:

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

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

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

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

В нем можно задать следующие условия:

• Условия «равно» и «не равно» пояснений не требуют, т.к. с ними все предельно ясно;
• «больше», «меньше», «больше или равно» и «меньше или равно». Как строки можно сравнить друг с другом? Чтобы это понять, вспомните, каким образом Excel выполняет сортировку. Т.е. чем дальше в списке сортировки будет находиться строка, чем больше ее значение. Следующие утверждения являются истинными (верными): A А; А
• «начинается с», «не начинается с», «заканчивается на», «не заканчивается на», «содержит» и «не содержит». В принципе, условия говорят сами за себя и в качестве значений могут принимать символ либо набор символов. Обратите на подсказку в окне, расположенную ниже всех условий (пояснения будут дальше).

В случае необходимости можно задать 2 условия, используя логическое «И» либо «ИЛИ».

В случае выбора «И» должны выполняться все условия. Следите за тем, чтобы условия не исключали друга, например « Значение», т.к. ничто в один и тот же момент не может быть и больше, и меньше одного и того же показателя.

При использовании «ИЛИ» должно соблюдаться хотя бы одно из заданных условий.

В самом конце окна пользовательского автофильтра приведена подсказка. Первая ее часть: «Знак вопроса ”?” обозначает один любой знак …». Т.е. при проставлении условий, когда нельзя точно определить символ в конкретном месте строки, подставьте на его место «?». Примеры условий:

• Начинается с «?ва» (начинается с любого символа за которым следуют символы «ва») вернет результаты: «Иванов», «Иванова», «кварц», «сват» и другие строки, подходящие под условие;
• Равно «. » – вернет в результате строку, которая содержит 7 любых символов.

Вторая часть подсказки: «Знак ”*” обозначает последовательность любых знаков». Если в условии невозможно определить, какие символы и в каком количестве должны находиться в строке, то вместо них подставьте «*». Примеры условий:

• Заканчивается на «о*т» (заканчивается символами «о», после которого идет любая последовательность символов, затем символ «т») вернет результат: «пот», «торт», «оборот» и даже такой – «рвнщуооовиунистврункт».
• Равно «*» – вернет строку, которая содержит хотя бы один символ.

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

• «Выше среднего» и «Ниже среднего» – возвращает значения, которые выше и ниже среднего значения соответственно. Среднее значение рассчитывается, исходя из всех числовых значений списка столбца;
• «Первые 10…» – клик по данному пункту вызывает окно:

Здесь можно задать какие элементы выводить первые из наибольших или первые из наименьших. Также, какое количество элементов вывести, если в последнем поле выбран пункт «элементов списка». Если же выбран пункт «% от количества элементов», второе значение задает данный процент. Т.е. если в списке 10 значений, то будет выбрано самое высокое (или самое низкое) значение. Если в списке 1000 значений, то либо первые, либо последние 100.

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

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

Как наложить фильтр по дате на табличный массив

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

Способ 1. Использование опции «Фильтр»

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

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

Исходный табличный массив

1. Выделить составленную таблицу левой клавишей манипулятора.
2. Перейти во вкладку «Главная» в верхней панели инструментов главного меню Excel.
3. Нажать на кнопку «Фильтр» в отобразившейся панели опций. Также в этом разделе есть функция «Сортировка», которая меняет порядок отображения строк или столбцов в исходной таблице, сортируя их по какому-либо параметру.

Путь к наложению фильтра на табличный массив в Excel

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

Стрелочки в заголовках столбцов, которые отобразятся после наложения фильтра

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

Выбор объекта, по которому будет производиться фильтрация

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

Финальный результат фильтрации

Способ 2. Использование опции «Фильтр по дате»

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

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

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

1. К примеру, нажать на кнопку «Между…».
2. Откроется окошко «Пользовательский автофильтр». Здесь в первой строчке необходимо указать начальную дату, а во второй конечную.

Заполнение окна «Пользовательский автофильтр» после выбора функции “Между…”

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

Способ 3. Выполнение фильтрации вручную

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

1. В исходном табличном массиве найти даты, которые не нужны пользователю.
2. Выделить найденные строки левой клавишей мышки.
3. Нажать на кнопку «Backspace» с клавиатуры компьютера, чтобы удалить выделенные значения.

Способ 4. Использование расширенного фильтра по дате

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

1. Наложить фильтр на таблицу через вкладку «Главная». О том, как это сделать, было рассказано выше.
2. Раскрыть выпадающий список в заголовке любого столбика в таблице и щелкнуть ЛКМ по строчке «Фильтр по дате».
3. Указать любой из вариантов. Для примера кликнем по строке «Сегодня».

Выбор опции «Сегодня» в расширенном фильтре Эксель

1. Информация в массиве отфильтруется по указанной дате. Т.е. в таблице останутся только те данные, у которых указана сегодняшняя дата. При выставлении такого фильтра Excel будет ориентироваться по дате, выставленной на компьютере.
2. Выбрав вариант «Больше…», пользователю придется задать определенное число. После этого в табличном массиве останутся даты, которые больше указанной. Все остальные значения будут удалены.

Фильтр по столбцам в Excel

При работе с большими объёмами данных программе Excel может возникнуть необходимость воспользоваться фильтром.

С его помощью которого можно отобразить только ту информацию, которая необходима.

Для того, чтобы в Экселе (Excel) сделать фильтр по столбцам, нужно:

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

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

2) На панели инструментов Excel выбрать “Данные” -> “Фильтр”.

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

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

Например, если нам нужны данные только по сотруднику “Петров А.Д”, то в списке убираем все галочки, кроме значения “Петров А.Д”.

Удобнее сделать так – снять галочку с пункта “Выделить всё” (это автоматически снимет выделение со всех значений), а затем поставить галочку рядом с интересующим нас значением.

В результате на листе Эксель отобразиться только одна строка из таблицы, а стрелка фильтрации в столбце “ФИО сотрудника” изменит свой вид.

Чтобы убрать фильтр достаточно щёлкнуть ещё раз на кнопке “Фильтр” (она будет выделена).

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

Например, нам требуется показать только тех сотрудников, которые имеют зарплату > 30000 рублей.

Сделать такой фильтр можно так:

1) Открываем фильтр для столбца “Зарплата”.

2) Для работы с числами используются “Числовые фильтры”. В списке условий выбираем “Больше”.

3) Появится окно, где указываем значение “30000”, а затем нажимаем на “OK”.

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

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

Например, нужно отобразить сотрудников с зарплатой больше 30000 рублей, которые относятся к отделу “Руководство”.

Для этого нужно применить оба фильтра последовательно.

1) Сначала устанавливаем фильтр “Зарплата > 30000 рублей”.

2) Затем выбираем фильтр столбца “Отдел”. В списке значений будут отображаться не все отделы, а только те отделы, в которых хотя бы 1 сотрудник получает зарплату > 30000 рублей.

Excel нам покажет следующие строки:

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

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

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

С данной таблицы нужно выбрать всех клиентов в возрасте до 30-ти лет проживающих в городах Москва и Санкт-Петербург.

1. Снова перейдите на любую ячейку таблицы базы данных клиентов и выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр».
2. Щелкните по выпадающему списку столбца «Город» и отметьте галочками только Москву и Санкт-Петербург. И нажмите ОК.
3. Щелкните по выпадающему списку столбца «Возраст» и выберите опцию: «Числовые фильтры»-«Настраиваемый фильтр».
4. Заполните поля в окне «Пользовательский автофильтр» как указано на рисунке и нажмите ОК.

Как видно в столбце «№п/п» отсутствуют некоторые номера, что подтверждает о фильтрации данных в таблице

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

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

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

В языках баз данных (SQL и т.п.) строки таблиц считаются рядами, а столбцы – полями. Названия полей записаны в самой первой строке (заголовок таблицы) и эта строка не является рядом базы данных. Все инструменты для работы с электронными таблицами требуют организованной структуры для заполняемых данных. Поэтому не стоит хаотично заполнять ячейки листа. А придерживаться простой табличной структуры: столбцы должны иметь заголовки, а строки должны заполняться неразрывно. Только тогда мы имеем возможность обрабатывать данные в Excel с максимальной эффективностью.

В Excel предусмотрено три типа фильтров:

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

Как фильтровать строки по списку значений в Excel?

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

Фильтрация строк по списку значений с помощью функции расширенного фильтра

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

1. Нажмите Данные > Дополнительно , чтобы открыть диалоговое окно Advanced Filter.

2. В открывшемся диалоговом окне Advanced Filter укажите список, который вы будете фильтровать в исходной таблице как Список диапазонов, и укажите данный список как Диапазон критериев. Смотрите скриншот:

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

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

Фильтровать строки по списку значений с помощью Kutools for Excel

Если у вас установлен Kutools for Excel, вы можете применить его Сравнить диапазоны утилита, позволяющая легко находить и скрывать различные значения в списке. Пожалуйста, сделайте следующее:

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

1. Нажмите Kutools > Сравнить диапазоны , чтобы открыть диалоговое окно «Сравнить диапазоны».

2. В открывшемся диалоговом окне «Сравнить диапазоны» настройте следующие параметры:

(1) Укажите список, который вы будете фильтровать в исходной таблице в Найдите значения в коробка;(2) Укажите данный список в Согласно информации коробка;(3) Проверьте Каждый ряд опция в разделе На основе;(4) Проверьте Разные ценности вариант в Найдите раздел.(5) Проверьте Выбрать целые строки вариант.

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

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

3, Нажмите Ok в диалоговом окне Сравнить диапазоны. Затем появляется диалоговое окно, показывающее, сколько строк было выбрано. Просто нажмите на OK чтобы закрыть его.

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

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

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Демо: фильтрация строк по списку значений с помощью Kutools for Excel

Kutools for Excel включает более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Скачать и бесплатную пробную версию сейчас!

Выводы

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

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

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