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

Фильтр в Excel – основные сведения

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 3 мин Опубликовано 10.02.2020

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

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

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

Содержание

1. Применение фильтра в Excel
2. Применение нескольких фильтров в Excel
3. Снятие фильтра в Excel

Применение фильтра в Excel

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

1. Выделите любую ячейку в таблице, например, ячейку A2.

Чтобы фильтрация в Excel работала корректно, лист должен содержать строку заголовка, которая используется для задания имени каждого столбца. В следующем примере данные на листе организованы в виде столбцов с заголовками в строке 1: ID #, Тип, Описание оборудования и т.д.

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

Фильтрацию можно также применить, выбрав команду Сортировка и фильтр

на вкладке Главная.

Применение нескольких фильтров в Excel

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

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

Снятие фильтра в Excel

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

1. Нажмите на кнопку со стрелкой в столбце, с которого необходимо снять фильтр. В нашем примере мы удалим фильтр со столбца D.
2. Появится меню фильтра.
3. Выберите пункт Удалить фильтр из столбца… В нашем примере мы удалим фильтр из столбца Отдано на проверку.
4. Фильтр будет удален, а скрытые ранее данные вновь отобразятся на листе Excel.

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

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

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

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

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

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

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

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

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

1. Делаем таблицу с исходными данными либо открываем имеющуюся. Например, так:
2. Создаем таблицу условий. Особенности: строка заголовков полностью совпадает с «шапкой» фильтруемой таблицы. Чтобы избежать ошибок, копируем строку заголовков в исходной таблице и вставляем на этот же лист (сбоку, сверху, снизу) или на другой лист. Вносим в таблицу условий критерии отбора.
3. Переходим на вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно».
   Если отфильтрованная информация должна отобразиться на другом листе (НЕ там, где находится исходная таблица), то запускать расширенный фильтр нужно с другого листа.
4. В открывшемся окне «Расширенного фильтра» выбираем способ обработки информации (на этом же листе или на другом), задаем исходный диапазон (табл. 1, пример) и диапазон условий (табл. 2, условия). Строки заголовков должны быть включены в диапазоны.
5. Чтобы закрыть окно «Расширенного фильтра», нажимаем ОК. Видим результат.

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



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

Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» — «Сортировка и фильтр» — «Очистить».

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

В таблицу условий внесем критерии.

Например, такие:

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

Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:

Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: =»=Набор обл.6 кл.»

После использования «Расширенного фильтра»:

Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

В табличку условий введем критерии отбора: =»=Набор обл.6 кл.» (в столбец «Название») и =»

Обратите внимание: критерии необходимо записать под соответствующими заголовками в РАЗНЫХ строках.

Результат отбора:

Расширенный фильтр позволяет использовать в качестве критерия формулы. Рассмотрим пример.

Отбор строки с максимальной задолженностью: =МАКС(Таблица1[Задолженность]).

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

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

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

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

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

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

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

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

Стандартными способами – никак. Программа Microsoft Excel отбирает данные только в столбцах. Поэтому нужно искать другие решения.

Приводим примеры строковых критериев расширенного фильтра в Excel:

1. Преобразовать таблицу. Например, из трех строк сделать список из трех столбцов и к преобразованному варианту применить фильтрацию.
2. Использовать формулы для отображения именно тех данных в строке, которые нужны. Например, сделать какой-то показатель выпадающим списком. А в соседнюю ячейку ввести формулу, используя функцию ЕСЛИ. Когда из выпадающего списка выбирается определенное значение, рядом появляется его параметр.

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

Для списка товаров создадим выпадающий список:

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

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

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

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

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

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

В этой статье мы объясним, как сортировать и фильтровать данные в Excel.

Сортировка данных в Excel

Существует множество вариантов сортировки данных.

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

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

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

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

идентификатор	заголовок	сезон	imdb_rating
10	Ночная жизнь Гомера	1	7,4
12	Красти арестовали	1	8,3
14	Барт получает пятерку	2	8,2
17	Две машины в каждом гараже и по три глаза на каждую рыбу	2	8.1
19	Общество мертвецов	2	8,0
35	Кровавая месть	2	8,0
37	Мистер Лиза едет в Вашингтон	3	7,7
39	Барт-убийца	3	8,7
41	Как отец, как клоун	3	7,7
44	Громовые субботы	3	7,9

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

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

Чтобы отсортировать данные в Excel:

1. Выберите ячейку в столбце, который вы хотите отсортировать.
2. На вкладке Данные перейдите в группу Сортировка и фильтрация . Тогда у вас есть два варианта.
   1. Чтобы отсортировать значения в порядке возрастания или убывания на основе интерпретации столбца Excel, щелкните значок Сортировка от А до Я или Сортировка от Я до А .
   2. Чтобы получить дополнительные параметры сортировки, нажмите кнопку Сортировать . Затем вы можете указать Столбец , что до Сортировать по и Заказать . С помощью параметра Добавить уровень вы можете выполнить вторичный уровень сортировки, если это необходимо.

Перейти: Сортировка данных в диапазоне или таблице [1]

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

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

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

Оператор сравнения	Значение	Пример	Результат (A1=1, B1=2)
=	Равно	А1=В1	ЛОЖЬ
>	Больше	А1>В1	ЛОЖЬ
<	Менее	А1<В1	ИСТИНА
>=	Больше или равно	А1>=В1	ЛОЖЬ
<=	Меньше или равно	А1<=В1	ИСТИНА
<>	Не равно	А1<>В1	ИСТИНА

Чтобы создать фильтр в Excel:

1. Выберите данные, с которыми вы хотите работать.
2. Выберите Данные > Фильтр в меню ленты.
3. В верхней части выделения выберите стрелку заголовка столбца (серое поле со стрелкой вниз).
4. Выберите Текстовые фильтры или Числовые фильтры , а затем выберите сравнение, например Между .
5. Введите критерии фильтра и нажмите OK.

Для более сложной фильтрации можно использовать логические операторы, такие как И и ИЛИ, для выбора результатов — в зависимости от того, какие критерии оцениваются как истинные. И будет оцениваться как истинное только в том случае, если оба критерия оцениваются как истинные (например, 1<2 И 3<4). ИЛИ оценивается как истинное, если один из критериев или оба оцениваются как истинные (например, 1<2 ИЛИ 3>4 оценивается как ИСТИНА).

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

Перейти: фильтрация данных в диапазоне или таблице [2]

Перейти: Фильтровать по расширенным критериям [3]

На этих страницах службы поддержки Microsoft есть дополнительные примеры и инструкции по фильтрации.

Сводные таблицы

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

Сводные таблицы могут быстро превратить это:

В:

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

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

Ссылки

1. Сортировка данных в диапазоне или таблице [Интернет]. Поддержка Майкрософт. Доступно по адресу: https://support.microsoft.com/en-us/office/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654?ui=en-us&rs=en -нас&объявление=нас
2. Фильтрация данных в диапазоне или таблице [Интернет]. Поддержка Майкрософт. Доступно по адресу: https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e?ui=en-us&rs=en -нас&объявление=нас
3. Фильтр по расширенным критериям [Интернет]. Поддержка Майкрософт. Доступно по адресу: https://support.microsoft.com/en-us/office/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b?ui=en-us&rs=en-us&ad=us

Эта статья взята из бесплатного онлайн-ресурса

Excel для анализа данных

Создано

Присоединяйся сейчас

Достигните своих личных и профессиональных целей

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

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

Начать обучение

Как фильтровать в Excel для отображения нужных данных

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

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

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

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

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

Рассмотрим следующие методы фильтрации данных в Excel.

Как фильтровать по категориям

Открыв таблицу данных, на ленте Excel щелкните Данные .

Затем найдите группу Sort & Filter и щелкните Filter . Это помещает кнопку раскрывающегося списка в каждый столбец вашей таблицы данных.

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

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

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

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

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

Иногда может потребоваться фильтрация на основе определенного диапазона чисел.

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

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

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

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

Нажмите OK , когда закончите.

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

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

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

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

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

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

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

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

В качестве критериев для фильтрации чисел можно использовать логические функции Excel, такие как меньше (<) или больше (>).

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

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

Затем поместите курсор в поле Диапазон критериев и выделите столбцы критериев, которые вы создали ранее: заголовки и значения.

Нажмите OK , чтобы применить фильтр.

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

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

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

Затем поместите курсор в Скопируйте в поле и выберите любую ячейку на листе Excel, затем нажмите OK .

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

Как просмотреть отфильтрованный результат с выбранными столбцами в Excel

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

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

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

Затем нажмите OK , чтобы просмотреть отфильтрованный результат под этими заголовками.

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

Если вы хотите вставить полученные данные в новый лист, нажмите кнопку Добавить ( + ) в левом нижнем углу листа Excel, чтобы открыть новый лист.

Щелкните любую ячейку на новом листе, чтобы активировать ее.

Выберите Data на ленте, затем нажмите Advanced , чтобы открыть всплывающее меню Advanced Filter . Выберите параметр Копировать в другое место .

Поместите курсор в поле List range и перейдите на лист, содержащий исходные данные. Затем выделите все исходные данные.

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

Выберите поле Копировать в . Затем снова выберите любую ячейку на новом листе. Щелкните OK , чтобы вставить отфильтрованный результат на новый лист.

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

Связано: Объединение файлов и листов Excel

Получите максимальную отдачу от фильтров Excel

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