Сортировка и фильтрация. Фильтр в Excel – основные сведения Как в excel установить горизонтальный фильтр
Порой данные, внесенные в документ Эксель, могут насчитывать тысячи строк, и найти среди них необходимые строки довольно сложно. Но если искомые данные удовлетворяют каким-либо определенным критериям, благодаря которым можно отсеять лишнюю информацию, то необходимо использовать фильтр в Excel.
У всех таблиц в Экселе, созданных через меню «Таблица» на вкладке «Вставка» или к которым применено форматирование, как к таблице, уже имеется встроенный фильтр.
Если данные, оформленные в виде таблицы, не имеют фильтр, то его необходимо установить. Для этого выделяем шапку таблицы или просто выделяем ячейку внутри таблицы, переходим на вкладку «Данные» и выбираем в меню «Фильтр» . Такой же пункт меню можно найти на вкладке «Главная» в меню «Сортировка и фильтр» . После подключения фильтра в шапке таблицы с правой стороны заглавия каждого столбца появляется соответствующий знак, квадратик с перевернутым треугольником.
Самое простое использование фильтра, это скрытие всех значений в списке, кроме выделенных. Достаточно нажать на значок фильтра и оставить галочку в списке значений напротив необходимого. После применения фильтра меняется и значок в фильтруемом столбце, что позволяет визуально определить применение фильтра к данным. Для удаления фильтра необходимо нажать на значок фильтра и выбрать «Удалить фильтр из столбца…» .
Также можно настраивать и более сложные фильтры. В нашем первом столбце имеются только текстовые данные, поэтому при нажатии на значок фильтра в данном столбце есть активный пункт «Текстовые фильтры» , где можно выбрать некоторые условия. В случае с числовыми данными, как во втором столбце, будет доступен пункт «Числовые фильтры» .
При выборе любого настраиваемого варианта открывается окошко настраиваемого фильтра, где можно выбрать сразу два условия с сочетанием «И» и «ИЛИ» .
Также есть возможность установить фильтр по цвету текста и цвету ячеек. При этом в списке будут отображаться только примененные в данном столбце цвета текста и ячеек.
В последних версиях Эксель есть возможность для таблиц создавать срезы данных. Для этого активируем любую ячейку таблицу и заходим в появившуюся вкладку «Конструктор» , и выбираем меню «Вставить срез» . При этом появляется окошко, в котором необходимо выбрать столбец, к которому будет применяться срез данных. После выбора столбца появляется плавающее окошко с элементами управления, в котором будут перечислены все пункты среза. Выбирая любое значение мы будем получать соответствующий срез данных.
Фильтрация данных в Excel позволяет отобразить среди большого объема информации только ту, что Вам в данный момент необходима. Например, имея перед собой многотысячный список товаров крупного гипермаркета, Вы можете выделить из него только шампуни или крема, а остальное временно скрыть. В этом уроке мы научимся применять фильтры к спискам в Excel, задавать фильтрацию сразу по нескольким столбцам, а также удалять фильтры.
Если Ваша таблица содержит большой объем данных, могут возникнуть затруднения при поиске нужной информации. Фильтры используются для того, чтобы сузить количество данных, отображаемых на листе Excel, позволяя видеть только нужную информацию.
Применение фильтра в Excel
В следующем примере мы применим фильтр к журналу учета эксплуатации оборудования, чтобы отобразить только ноутбуки и планшеты, доступные для проверки.
- Выделите любую ячейку в таблице, например, ячейку A2.
Чтобы фильтрация в Excel работала корректно, лист должен содержать строку заголовка, которая используется для задания имени каждого столбца. В следующем примере данные на листе организованы в виде столбцов с заголовками в строке 1: ID #, Тип, Описание оборудования и т.д.
Фильтрацию можно также применить, выбрав команду Сортировка и фильтр на вкладке Главная .
Применение нескольких фильтров в Excel
Фильтры в Excel могут суммироваться. Это означает, что Вы можете применить несколько фильтров к одной таблице, чтобы сузить результаты фильтрации. В прошлом примере мы уже отфильтровали таблицу, отобразив только ноутбуки и планшеты. Теперь наша задача сузить данные еще больше и показать только ноутбуки и планшеты, отданные на проверку в августе.
Снятие фильтра в Excel
После применения фильтра рано или поздно возникнет необходимость снять или удалить его, чтобы отфильтровать содержимое другим образом.
Чтобы удалить все фильтры в таблице Excel, щелкните команду Фильтр на вкладке Данные .
Расширенный фильтр в Excel предоставляет более широкие возможности по управлению данными электронных таблиц. Он более сложен в настройках, но значительно эффективнее в действии.
С помощью стандартного фильтра пользователь Microsoft Excel может решить далеко не все поставленные задачи. Нет визуального отображения примененных условий фильтрации. Невозможно применить более двух критериев отбора. Нельзя фильтровать дублирование значений, чтобы оставить только уникальные записи. Да и сами критерии схематичны и просты. Гораздо богаче функционал расширенного фильтра. Присмотримся к его возможностям поближе.
Как сделать расширенный фильтр в Excel?
Расширенный фильтр позволяет фильтровать данные по неограниченному набору условий. С помощью инструмента пользователь может:
- задать более двух критериев отбора;
- скопировать результат фильтрации на другой лист;
- задать условие любой сложности с помощью формул;
- извлечь уникальные значения.
Алгоритм применения расширенного фильтра прост:
Верхняя таблица – результат фильтрации. Нижняя табличка с условиями дана для наглядности рядом.
Как пользоваться расширенным фильтром в Excel?
Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» - «Сортировка и фильтр» - «Очистить».
Найдем с помощью инструмента «Расширенный фильтр» информацию по значениям, которые содержат слово «Набор».
В таблицу условий внесем критерии. Например, такие:
Программа в данном случае будет искать всю информацию по товарам, в названии которых есть слово «Набор».
Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:
Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: ="=Набор обл.6 кл."
После использования «Расширенного фильтра»:
Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.
В табличку условий введем критерии отбора: ="=Набор обл.6 кл." (в столбец «Название») и ="
Обратите внимание: критерии необходимо записать под соответствующими заголовками в РАЗНЫХ строках.
Результат отбора:
Расширенный фильтр позволяет использовать в качестве критерия формулы. Рассмотрим пример.
Отбор строки с максимальной задолженностью: =МАКС(Таблица1[Задолженность]).
Таким образом мы получаем результаты как после выполнения несколько фильтров на одном листе Excel.
Как сделать несколько фильтров в Excel?
Создадим фильтр по нескольким значениям. Для этого введем в таблицу условий сразу несколько критерий отбора данных:
Применим инструмент «Расширенный фильтр»:
Теперь из таблицы с отобранными данными извлечем новую информацию, отобранную по другим критериям. Например, только отгрузки за 2014 год.
Вводим новый критерий в табличку условий и применяем инструмент фильтрации. Исходный диапазон – таблица с отобранными по предыдущему критерию данными. Так выполняется фильтр по нескольким столбцам.
Чтобы использовать несколько фильтров, можно сформировать несколько таблиц условий на новых листах. Способ реализации зависит от поставленной пользователем задачи.
Как сделать фильтр в Excel по строкам?
Стандартными способами – никак. Программа Microsoft Excel отбирает данные только в столбцах. Поэтому нужно искать другие решения.
Приводим примеры строковых критериев расширенного фильтра в Excel:
Чтобы привести пример как работает фильтр по строкам в Excel, создадим табличку.
При работе с таблицами нередко возникает необходимость «собрать в кучку» ряд однотипных данных, например, всех людей с фамилией «Иванов», или все цены в диапазоне от 500 до 1000 рублей. Естественно, MS Excel предлагает для подобных операций целый ряд инструментов, но первым делом, когда речь идет о сортировке и фильтрации, мы вспоминаем о фильтрах данных MS Excel .
Как включить фильтр данных в MS Excel?
Чтобы установить фильтр к столбцам таблицы, выделите мышью «шапку» таблицы, а затем на вкладке «Главная» выберите , и далее «Фильтр» . В правом углу ячеек с заголовками столбцов появятся кнопки с направленной вниз стрелкой. Нажмите на него, и увидите весьма солидный перечень различных встроенных фильтров.
Попробуем разобраться в этом многообразии.
- Фильтр по цвету
. Работает только в том случае, если часть ячеек таблицы размечена цветом (например, если ячейки с доходами помечены зеленым, а расходами — красным, их можно весьма удобно отфильтровать по цвету).
Результат применения фильтра по цвету
Числовые и текстовые фильтры MS Excel
- Числовые/Текстовые фильтры
(в зависимости от того — числа или текст содержится в столбце
). Самая большая и многофункциональная группа фильтров. При выборе любого пункта, запустится инструмент «Пользовательский автофильтр», предлагающий возможности по фильтрации на любой вкус и цвет. Разобраться в них достаточно просто, достаточно взглянуть на примеры ниже:
Отфильтровать все значения в таблице, ценой меньше 160.
Отфильтровать все значения в таблице, ценой меньше 160 И в то же время больше чем 80
Отфильтровать все значения в таблице, которые не равны 180 и 200
- Простой фильтр
. Тут и думать нечего — убираем галочки напротив данных которые нужно отфильтровать и жмем «Ок». В итоге, на листе остается только нужная информация.
Самый простой фильтр в MS Excel — что выбираешь, то и фильтруется
Как отключить фильтр в таблице MS Excel?
Сбросить фильтр в MS Excel ещё проще чем поставить — снова нажмите стрелку с правого бока ячеек заголовка таблице, и в появившемся контекстном меню выберите пункт «Удалить фильтр из столбца» .
Если же вы хотите совершенно убрать фильтр с таблицы, просто повторите те же действия, что и в самом начале — на вкладке «Главная» выберите инструмент «Сортировка и фильтр» , где повторно нажмите «Фильтр» .
Поздравляю, теперь фильтры данных в таблице Excel полностью отключены.
Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами.
Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ.
Используют её экономисты, бухгалтера, ученые, студенты и представители других профессий, которым необходимо обрабатывать математические данные.
Одной из самых удобных функций в этой программе является фильтрация данных. Рассмотрим, как настроить и использовать MS excel фильтры.
Где в Excel фильтры — их виды
Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.
Основные функции фильтрации в Excel:
- отбор по цвету: дает возможность отсортировать данные по цвету шрифта или заливки,
- текстовые фильтры в excel: позволяют задать те или иные условия для строк, например: меньше, больше, равно, не равно и другие, а также задать логические условия — и, или,
- числовые фильтры: отсортировывают по числовым условиям, например, ниже среднего, первые 10 и другие,
- ручной: отбор можно выполнять по выбранным самостоятельно критериям.
Пользоваться ими несложно. Необходимо выделить таблицу и выбрать в меню раздел с фильтрами, а затем уточнить, по какому критерию будет проводиться отсев данных.
Как использовать расширенный фильтр в Excel — как его настроить
У стандартного фильтра есть существенный недостаток — чтобы вспомнить, какие именно критерии отбора использованы, нужно открывать меню. И тем более это вызывает неудобства, когда задан не один критерий. С этой точки зрения более удобен расширенный фильтр, который отображается отдельной таблицей над данными.
ВИДЕО ИНСТРУКЦИЯ
Порядок настройки:
- Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
- Создать таблицу с условиями отбора.
- Запустить расширенный фильтр.
Рассмотрим пример настройки.
У нас есть таблица со столбцами Товар, Количество и Цена.
К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди» Под это условие попадают несколько строк.
Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора – Гвозди (ячейка А2).
Удобнее всего располагать ее над данными или же сбоку. Под ней тоже не запрещено, но не всегда удобно, так как периодически может возникать необходимость добавлять дополнительные строки. Отступите хотя бы одну пустую строку между двумя таблицами.
Затем необходимо:
- выделить любую из ячеек,
- открыть «Расширенный фильтр» по пути: Данные – Сортировка и фильтр – Дополнительно,
- проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
- в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.
После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.
Также легко настроить отбор по строкам, содержащим точно слово «Гвозди» без учета регистра. В диапазоне В1:В2 разместим колонку с новым критерием отбора, не забыв указать заголовок столбца, в котором будет выполняться отсев. В ячейке В2 необходимо указать следующую формулу =»=Гвозди».
- выделить любую из ячеек таблицы,
- открыть «Расширенный фильтр»,
- проверить, что в «Исходный диапазон» попала вся таблица с данными,
- в «Диапазоне условий» указать В1:В2.
После нажатия «ОК» произойдет отсев данных.
Это самые простые примеры работы с фильтрами в excel . В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.
Информацию в таблице можно фильтровать по столбцам – одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».
Пример 1
Если необходимо отсеять данные по столбцу с названиями городов в алфавитном порядке, нужно выделить любую из ячеек этого столбца, открыть «Сортировку» и «Фильтр» и выбрать параметр «АЯ». В результате информация отобразится с учетом первой буквы в названии города.
Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».
Пример 2
Необходим отсев информации по месяцам, а также город с большим объемом продаж должен стоять в таблице выше города с меньшим объемом продаж. Для решения задачи требуется в «Сортировке и фильтре» выбрать параметр «Сортировка». В появившемся окне с настройками уточнить «Сортировать по» — «Месяц».
Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» — «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.
ВИДЕО ИНСТРУКЦИЯ
Почему могут не работать фильтры в Excel
В работе с таким инструментом, как фильтры у пользователей часто возникают сложности. Обычно они связаны с нарушением правил использования тех или иных настроек.
Проблема с фильтром по дате – одна из самых популярных. Возникает после выгрузки данных из учетной системы в виде массива. При попытке фильтрации строк по столбцу, содержащему даты, отсев происходит не по дате, а по тексту.
Решение проблемы:
- выделить столбец с датами,
- открыть вкладку Excel в главном меню,
- выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».
К популярным ошибкам пользователей при работе с данной программой также стоит отнести:
- отсутствие заголовков у столбцов (без них не будут работать фильтрация, сортировка, а также целый ряд других важных параметров),
- наличие пустых строк и столбцов в таблице с данными (это сбивает систему сортировки, Excel воспринимает информацию как две разные независимые друг от друга таблицы),
- размещение на одной странице нескольких таблиц (удобнее располагать каждую таблицу на отдельном листе),
- размещение в нескольких столбцах данных одного типа,
- размещение данных на нескольких листах, например, по месяцам или годам (объем работы можно сразу умножать на количество листов с информацией).
И еще одна критическая ошибка, не позволяющая в полной мере использовать возможности Excel – это применение нелицензионного продукта.
Гарантировать его правильную работу нельзя, к тому же ошибки будут появляться постоянно. Если вы нацелены использовать данный инструмент обработки математических сведений на постоянной основе, приобретите полноценную версию программы.
