Microsoft Excel — широко распространенный и удобный инструмент для работы с электронными таблицами. Широкий функционал делает эту программу второй по популярности после MS Word среди всех офисных программ. Его используют экономисты, бухгалтеры, исследователи, студенты и представители других профессий, которым необходимо обрабатывать математические данные.
Одной из самых удобных функций этой программы является фильтрация данных. Рассмотрим, как настроить и использовать фильтры MS Excel.
Где в Excel фильтры — их виды
Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.
Как установить фильтр в Excel
Основные функции фильтрации в Excel:
- фильтр по цвету: позволяет сортировать данные по шрифту или цвету заливки,
- текстовые фильтры в excel: позволяет задавать определенные условия для строк, например: меньше, больше, равно, не равно и другие, а также задавать логические условия - и, или,
- числовые фильтры: сортировка по числовым условиям, таким как ниже среднего, топ-10 и другие,
- ручной: выбор может быть сделан в соответствии с выбранными вами критериями.
Они просты в использовании. Необходимо выбрать таблицу и выбрать в меню раздел с фильтрами, а затем уточнить, по какому критерию будут отсеиваться данные.
Как использовать расширенный фильтр в Excel — как его настроить
У стандартного фильтра есть существенный недостаток — чтобы вспомнить, какие критерии отбора использовались, нужно открыть меню. И еще больше причиняет неудобства, когда задано более одного критерия. С этой точки зрения удобнее расширенный фильтр, который отображается в виде отдельной таблицы данных.
ВИДЕО-ИНСТРУКЦИЯ
Введите заказ:
- Создайте таблицу с данными для дальнейшей работы с ней. Он не должен содержать пустых строк.
- Создайте таблицу с условиями выбора.
- Запустите расширенный фильтр.
Давайте рассмотрим пример настройки.
У нас есть таблица со столбцами Product, Quantity и Price.
Например, вам нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди». Несколько строк подпадают под это условие.
Таблица условий будет размещена в ячейках A1:A2. Важно ввести название столбца, где будет происходить выбор (ячейка А1) и собственно слово для выбора - Гвозди (ячейка А2).
Удобнее всего разместить его над данными или на странице. Под ним тоже не запрещено, но не всегда практично, так как время от времени может возникнуть необходимость в добавлении дополнительных строк. Сделайте отступ хотя бы на одну пустую строку между двумя таблицами.
Тогда вам нужно:
- выделить одну из ячеек
- открыть "Расширенный фильтр" по пути: Данные - Сортировка и фильтрация - Расширенный,
- проверьте, что указано в поле "Исходный диапазон" - туда должна идти вся таблица информации,
- в «Диапазон отношения» необходимо ввести значения ячеек с условием выбора, в данном примере это диапазон А1:А2.
Кроме того, данные, полученные в результате сортировки, удобно хранить в отдельной таблице; для этого достаточно в настройках выбрать «Копировать результат в другое место».
После нажатия кнопки «ОК» будет выбрана необходимая информация, а в таблице появятся только строчки с нужным словом, в нашем случае это «Гвозди». Остальные номера строк становятся синими. Чтобы отменить указанный фильтр, просто нажмите клавиши CTRL+SHIFT+L.
Также легко фильтровать строки, содержащие точное слово «Гвозди» без учета регистра. В области B1:B2 разместим столбец с новым критерием отбора, при этом не забудьте ввести заголовок столбца, в котором будет проводиться скрининг. В ячейку B2 необходимо ввести следующую формулу = «=Гвозди».
Далее вам нужно:
- выделить одну из ячеек таблицы,
- открыть «Расширенный фильтр»,
- проверьте, что вся таблица данных включена в «Исходный диапазон,
- в разделе «Область отношений» указано B1:B2.
После нажатия «ОК» данные будут отфильтрованы.
Это самые простые примеры работы с фильтрами в Excel. В расширенной версии удобно задавать другие условия отбора, например, отсев параметром "ИЛИ", отсев параметром "Спайкер" и значением в столбце "Число" > 40.
Как сделать фильтр в Excel по столбцам
Информацию в таблице можно фильтровать по столбцам - одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».
Пример 1
Если вам нужно отфильтровать данные по столбцу с названиями городов в алфавитном порядке, вам нужно выбрать одну из ячеек в этом столбце, открыть «Сортировка» и «Фильтр» и выбрать опцию «Y». В результате информация будет отображаться с учетом первой буквы названия города.
Чтобы получить информацию об обратном принципе, нужно использовать параметр «Я».
Пример 2
Необходимо отфильтровать информацию по месяцам, а также город с большим объемом продаж должен быть выше в таблице, чем город с меньшим объемом продаж. Чтобы решить проблему, вам нужно выбрать опцию «Сортировка» в «Сортировка и фильтрация». В появившемся окне с настройками укажите «Сортировать по» — «Месяц».
Далее вам нужно добавить второй уровень сортировки. Для этого выберите в «Сортировке» - «Добавить уровень» и укажите столбец «Продажи». В графе настроек «Порядок» выберите «По убыванию». После нажатия «ОК» данные будут выбраны по заданным параметрам.
ВИДЕО-ИНСТРУКЦИЯ
Почему могут не работать фильтры в Excel
При работе с таким инструментом, как фильтры, у пользователей часто возникают трудности. Обычно они связаны с нарушениями правил использования тех или иных настроек.
Проблема с фильтром по дате одна из самых популярных. Происходит после выгрузки данных из учетной системы в виде матрицы. Когда вы пытаетесь отфильтровать строки по столбцу, содержащему даты, выпадение происходит не по дате, а по тексту.
Решение проблемы:
- выберите столбец дат,
- откройте вкладку Excel в главном меню,
- выберите кнопку «Ячейки», в выпадающем списке выберите опцию «Преобразовать текст в дату».
К частым ошибкам пользователей при работе с данной программой следует также отнести:
- отсутствие заголовков для столбцов (без них не будет работать фильтрация, сортировка и ряд других важных параметров),
- наличие пустых строк и столбцов в таблице с данными (это сбивает с толку систему сортировки, Excel воспринимает информацию как две разные независимые таблицы),
- размещение нескольких таблиц на одной странице (удобнее размещать каждую таблицу на отдельном листе),
- размещение в нескольких столбцах данных одного типа,
- размещение данных на нескольких листах, например, по месяцам или годам (объем работы можно сразу умножить на количество листов информации).
И еще одна критичная ошибка, не позволяющая в полной мере использовать функции Excel, — это использование нелицензионного продукта.
Нельзя гарантировать правильную работу, и ошибки будут появляться все время. Если вы собираетесь использовать этот инструмент математической обработки информации постоянно, приобретите полную версию программы.
Расширенный фильтр в Excel дает вам больше контроля над данными вашей электронной таблицы. Он сложнее в настройках, но гораздо эффективнее в действии.
С помощью стандартного фильтра пользователь Microsoft Excel может решить далеко не все задачи. Нет визуального отображения применяемых условий фильтрации. Невозможно использовать более двух критериев выбора. Вы не можете фильтровать повторяющиеся значения, чтобы возвращать только уникальные записи. А сами критерии схематичны и просты. Функционал расширенного фильтра намного богаче. Рассмотрим подробнее его возможности.
Как сделать расширенный фильтр в Excel?
Расширенный фильтр позволяет фильтровать данные по неограниченному набору условий. С помощью инструмента пользователь может:
- указать более двух критериев выбора;
- скопировать результат фильтрации на другой лист;
- сформулировать условие любой сложности с помощью формул;
- извлекать уникальные значения.
Алгоритм использования расширенного фильтра прост:
- Создаем таблицу с исходными данными или открываем уже существующую. Например вот так:
- Создайте таблицу состояний. Свойства: строка заголовка полностью совпадает с "заголовком" фильтруемой таблицы. Во избежание ошибок скопируйте строку заголовка исходной таблицы и вставьте ее на тот же лист (сбоку, сверху, снизу) или другой лист. Введите критерии выбора в таблицу условий.
- Перейдите на вкладку «Данные» — «Сортировка и фильтрация» — «Дополнительно». Если вы хотите, чтобы отфильтрованная информация отображалась на другом листе (НЕ там, где находится исходная таблица), вам необходимо запустить расширенный фильтр с другого листа.
- В открывшемся окне «Расширенный фильтр» выберите способ обработки информации (на том же листе или на другом), задайте начальную область (таблица 1, пример) и область условия (таблица 2, условия). Заголовки должны быть включены в области.
- Чтобы закрыть окно расширенного фильтра, нажмите кнопку ОК. Мы видим результат.
Верхняя таблица является результатом фильтрации. Нижняя табличка с условиями приведена для наглядности рядом с ней.
Как пользоваться расширенным фильтром в Excel?
Чтобы отменить действие расширенного фильтра, установите курсор в любом месте таблицы и нажмите комбинацию клавиш Ctrl+Shift+L или «Данные» — «Сортировка и фильтрация» — «Удалить».
С помощью инструмента «Расширенный фильтр» найдем информацию о значениях, содержащих слово «Set».
Давайте добавим критерии в таблицу состояний. Например эти:
В этом случае программа будет искать всю информацию о товарах, в названии которых есть слово «Комплект».
Вы можете использовать символ "=" для поиска точного значения. Добавим в таблицу состояний следующие критерии:
Excel воспринимает знак «=» как сигнал о том, что пользователь сейчас вводит формулу. Для корректной работы программы в строке формул должна быть запись вида: = "= Набор ячеек региона 6.»
После использования «Расширенного фильтра»:
Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в пределах одного столбца.
В таблицу условий вводим критерии отбора: = "= Набор из 6 ячеек региона". (в столбце «Имя») и =»
Обратите внимание: критерии должны быть написаны под соответствующими заголовками в РАЗНЫХ строках.
Результат выбора:
Расширенный фильтр позволяет использовать формулы в качестве критерия. Рассмотрим пример.
Выбор строки с максимальной задолженностью: =MAX(Table1).
Таким образом, мы получаем результаты, как после запуска нескольких фильтров на одном листе Excel.
Как сделать несколько фильтров в Excel?
Создадим фильтр по нескольким значениям. Для этого введем в таблицу условий несколько критериев отбора данных одновременно:
Использовать расширенный фильтр»:
Теперь из таблицы с выбранными данными мы будем извлекать новую информацию, отобранную по другим критериям. Например, только отгрузки за 2014 год.
Вносим новый критерий в таблицу условий и используем инструмент фильтрации. Начальная область представляет собой таблицу данных, отобранных по предыдущему критерию. Как фильтровать по нескольким столбцам.
Чтобы использовать больше фильтров, вы можете создать больше таблиц состояний на новых листах. Способ реализации зависит от поставленной пользователем задачи.
Как сделать фильтр в Excel по строкам?
Не стандартным способом. Microsoft Excel выбирает данные только в столбцах. Поэтому необходимо искать другие решения.
Вот примеры расширенных критериев строки фильтра в Excel:
- Преобразовать таблицу. Например, из трех строк создать список с тремя столбцами и применить фильтрацию к преобразованной версии.
- Используйте формулы, чтобы отобразить именно те данные в нужной вам строке. Например, превратить индикатор в раскрывающийся список. А в следующую ячейку введите формулу с помощью функции ЕСЛИ. При выборе определенного значения из раскрывающегося списка параметр отображается рядом с ним.
Для примера работы фильтра по строкам в Excel создадим таблицу:
Для списка товаров создайте выпадающий список:
Вставьте пустую строку над таблицей исходных данных. В ячейки мы должны ввести формулу, показывающую, из каких столбцов берется информация.
Рядом с выпадающим списком введите в ячейку следующую формулу: Ее задача — выбрать из таблицы значения, соответствующие конкретному товару
Скачать примеры расширенных фильтров
Используя раскрывающийся список и встроенные функции, Excel выбирает данные в строках на основе определенных критериев.
Фильтрация данных в Excel позволит отображать в столбцах таблицы ту информацию, которая интересует пользователя в определенное время. Это упрощает процесс работы с большими таблицами. Вы сможете контролировать как данные, которые будут отображаться в столбце, так и данные, которые будут исключены из списка.
Если вы создали таблицу в Excel через вкладку "Вставка" - "Таблица", или вкладку "Главная" - "Форматировать как таблицу", то в такой таблице фильтр активирован по умолчанию. Он появляется в виде стрелки, которая находится в верхней ячейке с правой стороны.
Если вы просто заполнили ячейки данными, а затем отформатировали их как таблицу, фильтр должен быть включен. Для этого выделите весь диапазон ячеек таблицы, включая заголовки столбцов, так как кнопка фильтра находится в верхнем столбце, и если выбрать столбец, начинающийся с ячейки с данными, он не будет применяться к отфильтрованным данным этот столбец. Затем перейдите на вкладку «Данные» и нажмите на кнопку «Фильтр».
В примере стрелка фильтра находится в заголовках, и это правильно — все данные в колонке ниже будут отфильтрованы.
Если вас интересует вопрос, как создать таблицу в Excel, перейдите по ссылке и прочитайте статью на эту тему.
Теперь давайте посмотрим, как фильтр работает в Excel. В качестве примера воспользуемся следующей таблицей. В нем три столбца: «Название товара», «Категория» и «Цена», к ним мы будем применять разные фильтры.
Щелкните стрелку в верхней ячейке нужного столбца. Здесь вы увидите список неповторяющихся данных из всех ячеек этого столбца. Рядом с каждым значением будут флажки. Снимите флажки для значений, которые вы хотите исключить из списка.
Например, давайте просто оставим фрукты в «Категории». Снимите галочку с поля «овощи» и нажмите «ОК».
Для тех столбцов в таблице, к которым применен фильтр, в верхней ячейке появится соответствующий значок.
Если вам нужно удалить фильтр данных в Excel, щелкните значок фильтра в ячейке и выберите в меню пункт «Удалить фильтр из (имя столбца)».
Существует множество способов создать фильтр данных в Excel. Есть текстовые и числовые фильтры. Они используются соответственно, если ячейки в столбце содержат либо текст, либо числа.
Применим «числовой фильтр» к колонке «Цена». Нажмите кнопку в верхней ячейке и выберите соответствующий пункт в меню. В раскрывающемся списке вы можете выбрать условие, которое хотите применить к данным столбца. Например, давайте покажем все товары, цена которых ниже «25». Мы выбираем «меньше».
Введите желаемое значение в соответствующее поле. Для фильтрации данных с использованием логических И и ИЛИ можно применять несколько условий. При использовании «И» должны выполняться оба условия, при использовании «ИЛИ» должно выполняться одно из указанных условий. Например, можно ввести: «меньше» — «25» — «И» — «больше» — «55». Поэтому исключим из таблицы товары, цена которых находится в диапазоне от 25 до 55.
Таблица с фильтром в столбце "Цена" ниже 25.
«Текстовый фильтр» в образце таблицы можно применить к колонке «Название товара». Нажмите кнопку фильтра в колонке и выберите в меню одноименный пункт. В открывшемся раскрывающемся списке используйте, например, «начинается с».
Оставим в таблице продукты, начинающиеся на «ка». В следующем окне в поле пишем: "ka*". Нажмите «ОК».
«*» в слове заменяет последовательность символов. Например, если поставить условие "содержит" - "с*л", останутся слова стол, стул, сокол и так далее. "?" заменит любой символ. Например "b?tone" - буханка хлеба, бутон. Если вы хотите оставить слова, состоящие из 5 букв, введите «?????».
Фильтр по столбцу Название продукта».
Фильтр можно настроить в соответствии с цветом текста или цветом ячейки.
Создадим ячейку «Фильтр по цвету» для столбца «Название товара». Нажимаем на кнопку фильтра и выбираем в меню одноименный пункт. Выберем красный.
В таблице остались только красные продукты.
Цветовой фильтр текста применяется к столбцу «Категория». Оставьте только фрукты. Снова выберите красный.
Теперь в таблице образцов отображаются только красные фрукты.
Если вы хотите, чтобы все ячейки в таблице были видны, но первой была красная ячейка, затем зеленая, синяя и так далее, используйте сортировку в Excel. Перейдя по ссылке, вы можете прочитать статью по теме.
Фильтры в Excel помогут вам работать с большими таблицами. Мы рассмотрели основные моменты, как сделать фильтр и как с ним работать. Выберите необходимые условия и оставьте интересующие данные в таблице.
Поделитесь этой статьей с друзьями:
