Анализ данных Excel - фильтрация

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

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

Вы можете фильтровать данные по -

Выбранные значения
Текстовые фильтры, если выбранный вами столбец содержит текст
Фильтры даты, если выбранный вами столбец содержит даты
Числовые фильтры, если выбранный вами столбец содержит числа
Числовые фильтры, если выбранный вами столбец содержит числа
Цвет шрифта, если выбранный вами столбец содержит шрифт с цветом
Значок ячейки, если выбранный столбец содержит значки ячеек
Расширенный фильтр
Использование слайсеров

В таблице заголовки столбцов автоматически помечаются фильтрами, известными как AutoFilters. AutoFilterпредставлен стрелкой

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

рядом с заголовком столбцаNumber Filter Options появляются.

Когда вы выбираете опцию фильтра или когда вы нажимаете Custom Filter который отображается в конце параметров фильтра, Custom AutoFilter появится диалоговое окно, в котором вы можете настроить параметры фильтрации.

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

Фильтр по выбранным значениям

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

Рассмотрим следующие данные -

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

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

Щелкните ОК. Будут отображаться только те строки, для которых установлено значение High как Position.

Фильтр по тексту

Рассмотрим следующие данные -

Вы можете отфильтровать эти данные так, чтобы отображались только те строки, у которых заголовок - «Менеджер».

Щелкните стрелку рядом с заголовком столбца Заголовок. В раскрывающемся списке щелкнитеText Filters. Появятся параметры текстового фильтра.

Выбрать Containsиз доступных вариантов. ВCustom AutoFilterоткрывается диалоговое окно. Type Manager в поле рядом с Contains.

Щелкните ОК. Будут отображаться только те строки, в которых заголовок содержит Manager.

Фильтр по дате

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

Щелкните стрелку рядом с заголовком столбца HireDate. В раскрывающемся списке щелкнитеDate Filters. Появятся параметры фильтра по дате. ВыбратьBefore из раскрывающегося списка.

Custom AutoFilterоткрывается диалоговое окно. Введите 01.01.2011 в поле рядом сis before. Вы также можете выбрать дату в средстве выбора даты рядом с полем.

Щелкните ОК. Будут отображаться только строки, в которых Title содержит Manager, а HireDate - до 01.01.2011.

Фильтр по номерам

Рассмотрим следующие данные -

Вы можете отфильтровать эти данные так, чтобы отображались только те строки, в которых Всего оценок> 850.

Щелкните стрелку рядом с заголовком столбца Всего оценок. В раскрывающемся списке щелкнитеNumber Filters. Появятся параметры числового фильтра.

Нажмите Greater Than. Custom AutoFilterоткрывается диалоговое окно. Введите 850 в поле рядом сGreater Than.

Щелкните ОК. Будут отображаться только строки, в которых общее количество баллов превышает 850.

Фильтр по цвету ячейки

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

Рассмотрим следующие данные. Столбец Total Marks имеет условное форматирование с разными цветами ячеек.

Щелкните стрелку

в заголовке Total Marks. В раскрывающемся списке щелкнитеFilter by Color. ВFilter by Cell Color появляются варианты.

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

Фильтр по цвету шрифта

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

Рассмотрим следующие данные. Столбец «Всего оценок» имеет условное форматирование с примененным цветом шрифта.

Щелкните стрелку

в заголовке Total Marks. Из раскрывающегося списка,click Filter by Color. Filter by Font Color появляются варианты.

Выберите зеленый цвет и нажмите ОК. Будут отображаться только строки, в которых столбец «Всего оценок» имеет зеленый шрифт.

Фильтр по значку ячейки

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

Рассмотрим следующие данные. Столбец Total Marks имеет условное форматирование с примененными значками.

Щелкните стрелку

в заголовке Total Marks. В раскрывающемся списке выберитеFilter by Color. ВFilter by Cell Icon появляются варианты.

Выберите значок

и нажмите ОК.

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

Очистить фильтр

Удаление фильтров называется Clear Filter в Excel.

Вы можете удалить

Фильтр из определенного столбца или
Все фильтры на листе сразу.

Чтобы удалить фильтр из определенного столбца, щелкните стрелку в заголовке таблицы этого столбца. В раскрывающемся меню щелкнитеClear Filter From “<specific Column Name>”.

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

Clear в

Editing группа по Home вкладка или
Sort & Filter группа в Data таб.

Все фильтры на листе удаляются сразу. НажмитеUndo Show All

если вы удалили фильтры по ошибке.

Повторно применить фильтр

Когда в ваших данных произойдут изменения, нажмите Reapply в Sort & Filter группа по Dataтаб. Заданный фильтр будет снова применен к измененным данным.

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

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

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

Далее нажмите Advanced в Sort & Filter группа по Dataтаб. ВAdvanced Filter появится диалоговое окно.
Укажите List Range и Criteria Range.
Вы также можете filter the list, на месте или скопировать в другое место.
В приведенной ниже фильтрации filter the data in place выбран.

Информация о сотруднике, где ManagerID = 2 OR Отображается Title = «* Специалист».

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

Вы применили следующие критерии: Должность = «* Специалист» ИЛИ Должность = «Вице-президент». Будет отображена информация о сотрудниках специалистов и вице-президентов.

Вы can copy the filtered data to another location. Вы также можете выбрать только несколько столбцов для включения в операцию копирования.

Скопируйте EmployeedID, Title и SalariedFlag в ячейки Q2, R2, S2. Это будет первая строка ваших отфильтрованных данных.
Нажмите «Дополнительно» и в диалоговом окне «Расширенный фильтр» нажмите «Копировать в другое место». В поле «Копировать в» укажите ссылку на заголовки, которые вы скопировали в другом месте, например Q2: S2.

Нажмите ОК после указания List Range и Criteria Range. Выбранные столбцы в отфильтрованных данных будут скопированы в указанное вами место.

Фильтр с помощью слайсеров

Slicers для фильтрации данных в сводных таблицах были введены в Excel 2010. В Excel 2013 вы можете использовать Slicers также фильтровать данные в таблицах.

Рассмотрим данные в следующей таблице.

Щелкните таблицу.
Нажмите Table Toolsкоторые появляются на ленте.
В Design Появляется лента.
Нажмите Insert Slicer.

Insert Slicers появится диалоговое окно, как показано на снимке экрана ниже.
в Insert Slicers В диалоговом окне вы найдете все заголовки столбцов, включая те, которые скрыты.
Установите флажки Title и HireDate. Click OK.

А Slicer появляется для каждого из заголовков таблицы, которые вы отметили в Insert Slicersдиалоговое окно. В каждомSlicer, будут выделены все значения этого столбца.

В заголовке Slicer, щелкните первое значение. Будет выделено только это значение, а остальные значения не будут выбраны. Далее вы найдете значения в HireDateSlicer которые соответствуют значению в заголовке Slicer также выделяются.

В таблице отображаются только выбранные значения.

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

Выберите значения Title, принадлежащие бухгалтерии, и значения HireDate в 2015 году из двух срезов.

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

в правом конце заголовка слайсера.