Главная » Разное » Как настроить фильтр в эксель

Как настроить фильтр в эксель


фильтрация данных с помощью автофильтра

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

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

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

Процедура

Выделите данные, которые требуется отфильтровать.    

  1. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.

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

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

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

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

1. Используйте поле Поиск для ввода текста или чисел, которые следует найти

2. Установите и снимите флажки для отображения значений, найденных в столбце данных

3. Используйте расширенные критерии для поиска значений, которые соответствуют определенным условиям

  1. Чтобы выбрать значения для списка, снимите флажок (Выделить все). При этом снимаются все флажки. Затем выберите только те значения, которые хотите видеть, и нажмите кнопку ОК, чтобы посмотреть результаты.

  2. Чтобы найти текст в столбце, введите текст или числа в поле Поиск. Также можно использовать подстановочные знаки, например звездочку (*) или знак вопроса (?). Нажмите клавишу ВВОД, чтобы получить результаты.

Фильтрация данных с указанием условий    

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

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

  2. Выберите условие, а затем выберите или введите критерии. Нажмите кнопку И кнопка для сочетания критериев (то есть два или более критериев, все из которых должны быть выполнены), или кнопку Или, чтобы потребовать выполнения только одного из критериев.

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

Дальнейшие действия

Сортировка и фильтр в Excel на примере базы данных клиентов

Сортировка в Excel – это распределение строк таблицы в определенном порядке, который соответствует конкретным условиям пользователя. Упорядочивание строк при сортировке всегда выполняется относительно выбранного столбца.

Работа в Excel c фильтром и сортировкой

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

Способ 1:

  1. Заполните таблицу как на рисунке:
  2. Перейдите на любую ячейку столбца F.
  3. Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Сортировка от А до Я».

Способ 2:

  1. Перейдите на любую ячейку таблицы с данными.
  2. Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр»
  3. Щелкните по выпадающему списку опций в заголовке «Город» таблицы и выберите опцию «Сортировка от А до Я».

Первый способ более простой, но он может выполнить сортировку только по одному столбцу (критерию). Для сортировки по нескольким столбцам следует использовать «Способ 2».

Полезный совет! В таблицах рекомендуется всегда использовать столбец с номерами строк. Он позволит всегда вернуться к первоначальному расположению строк после нескольких сортировок. Для этого достаточно выполнить сортировку этого столбца (например, №п/п).

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



Сортировка по нескольким столбцам в Excel

Отсортируем базу данных клиентов в соответствии с двумя критериями:

  1. Наименование городов в алфавитном порядке.
  2. Возраст клиентов от младших до старших.

Выполним сортировку по отдельным столбцам таблицы:

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

В результате таблица Excel отсортирована по нескольким критериям.

Чем выше уровень параметров сортировки, тем выше его приоритет.

Примечание. В Excel начиная с версии 2010-го года, количество уровней может быть столько, сколько столбцов в таблице. В старших версиях Excel разрешалось только 3 уровня.

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

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

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

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

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

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

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

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

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

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

Включение автофильтра:

  1. Выделить одну ячейку из диапазона данных.
  2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
  3. Щелкнуть по кнопке Фильтр [Filter] .

Фильтрация записей:

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

Варианты фильтрации данных

  • Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
  • Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
  • Можно воспользоваться строкой быстрого поиска 
  • Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:
  1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др. 
  2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит..., начинается с… и др.
  3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
  4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные 

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

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

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

 

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

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

     

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

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

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

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

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

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

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

Задание условий фильтрации

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

  2. Записать условия фильтрации. Условия, записанные в одной строке, выполняются одновременно (как условие «И»), а в разных строках - как условие выбора («ИЛИ»). В качестве условия может быть совпадение значения, которое заносится в ячейку, или сравнение с заданным в ячейке значением с помощью знаков < или >. Если один столбец должен удовлетворять двум условиям, его заголовок нужно повторить еще раз и записать в этом столбце второе условие.

  3. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и выбрать команду Дополнительно [Advansed]. 

  1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

  1. Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
  2. Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
  3. Указать при необходимости место с результатами в поле  Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
  4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Как добавить фильтр в Excel - Фильтр Excel, фильтрация данных

Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами. Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ. Используют её экономисты, бухгалтера, ученые, студенты и представители других профессий, которым необходимо обрабатывать математические данные.

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

Где в Excel фильтры — их виды

Основные функции фильтрации в Excel:

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

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

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

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

  1. Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
  2. Создать таблицу с условиями отбора.
  3. Запустить расширенный фильтр.

Рассмотрим пример настройки. У нас есть таблица со столбцами Товар, Количество и Цена.

К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди» Под это условие попадают несколько строк.

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора — Гвозди (ячейка А2).

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

Затем необходимо:

  1. выделить любую из ячеек ,
  2. открыть «Расширенный фильтр» по пути: Данные — Сортировка и фильтр — Дополнительно,
  3. проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
  4. в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

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

После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.

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

Далее потребуется:

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

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel. В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

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

Информацию в таблице можно фильтровать по столбцам — одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Пример 1

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

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Пример 2

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

Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» — «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

Решение проблемы:

  1. выделить столбец с датами,
  2. открыть вкладку Excel в главном меню,
  3. выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».

К популярным ошибкам пользователей при работе с данной программой также стоит отнести:

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

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

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

ЗАПИСИ ПО ТЕМЕ

Фильтрация данных в Excel с использованием расширенного фильтра. Расширенный фильтр в MS EXCEL Как сделать фильтр в excel по столбцам

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

Как добавить

Если Вы оформляли информацию через вкладку «Вставка» – «Таблица» , или вкладка «Главная» – «Форматировать как таблицу» , то в ней возможность фильтрации будет включена по умолчанию. Отображается нужная кнопка в виде стрелочки, которая расположена в верхней ячейке с правой стороны.

Если Вы просто заполнили блоки данными, а затем отформатировали их в виде таблицы – фильтр нужно включить. Для этого выделите весь диапазон ячеек, включая строку с заголовками, так как нужная нам кнопочка будет добавлена в верхний рядок. А вот если выделить блоки начиная с ячейки с данными, то первый рядок не будет относиться к фильтруемой информации. Затем перейдите на вкладку «Данные» и нажмите кнопку «Фильтр» .

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

Если Вас интересует вопрос, как сделать таблицу в Эксель , перейдите по ссылке и прочтите статью по данной теме.

Как работает

Теперь давайте рассмотрим, как работает фильтр в Эксель. Для примера воспользуемся следующими данными. У нас есть три столбца: «Название продукта» , «Категория» и «Цена» , к ним будем применять различные фильтры.

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

Например, оставим в «Категории» только фрукты. Снимаем галочку в поле «овощ» и нажимаем «ОК» .

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

Как удалить

Если Вам нужно удалить фильтр данных в Excel, нажмите в ячейке на соответствующий значок и выберите из меню «Удалить фильтр с (название столбца)» .

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

Использование фильтра

Числовой

Применим «Числовой…» к столбцу «Цена» . Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным. Например, отобразим все товары, цена которых ниже «25» . Выбираем «меньше».

В соответствующем поле вписываем нужное значение. Для фильтрации можно применять несколько условий, используя логическое «И» и «ИЛИ» . При использовании «И» – должны соблюдаться оба условия, при использовании «ИЛИ» – одно из заданных. Например, можно задать: «меньше» – «25» – «И» – «больше» – «55» . Таким образом, мы исключим товары, цена которых находится в диапазоне от 25 до 55.

В примере у меня получилось так. Здесь отображены все данные с «Ценой» ниже 25.

Текстовый

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

Оставим в таблице продукты, которые начинаются с «ка» . В следующем окне, в поле пишем: «ка*» . Нажимаем «ОК» .

«*» в слове, заменяет последовательность знаков. Например, если задать условие «содержит» – «с*л» , останутся слова: стол, стул, сокол и так далее. «?» заменит любой знак. Например, «б?тон» – батон, бутон, бетон. Если нужно оставить слова, состоящие из 5 букв, напишите «?????» .

Вот так я оставила нужные «Названия продуктов» .

По цвету ячейки

Фильтр можно настроить по цвету текста или по цвету ячейки.

Сделаем «Фильтр по цвету» ячейки для столбика «Название продукта» . Кликаем по кнопочке со стрелкой и выбираем из меню одноименный пункт. Выберем красный цвет.

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

По цвету текста

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

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

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

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

Расширенным фильтром можно отфильтровать в таблице всё, что угодно!!! Пределом возможностей этого инструмента является только фантазия пользователя!

Вы читаете четвертый пост в цикле статей о создании баз данных в MS Excel и организации обработки информации.

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

Фильтр №2 – расширенный фильтр!

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

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

1. Открываем в MS Excel файл .

2. Создавая таблицу критериев отбора расширенного фильтра, рекомендую вначале действовать шаблонно, не задумываясь о задачах, которые предстоит решать. Копируем все заголовки столбцов-полей из ячеек A7…F7 в ячейки A1…F1 – заготовка для таблицы критериев готова! Можно приступать к основной работе.

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

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

Задача №5:

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

1. Записываем параметр фильтрации – слово «балка» в столбце «Изделие» верхней таблицы критериев отбора.

2. Активируем («встаем мышью») любую ячейку внутри таблицы базы данных – это обеспечит автоматическое заполнение окошка «Исходный диапазон» в выпадающем диалоговом окне «Расширенный фильтр».

3. Включаем расширенный фильтр в Excel 2003 через главное меню программы. Выбираем: «Данные» — «Фильтр» — «Расширенный фильтр».

4. В выпавшем окне «Расширенный фильтр» заполняем окна так, как показано на снимке экрана, расположенном ниже этого текста.

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

5. Результат работы расширенного фильтра – на следующем снимке экрана. Расширенный фильтр показал все записи базы данных, которые содержат слово «балка» в столбце «Изделие» — задача выполнена.

Регистр букв не влияет на результаты фильтрации!

Правила совместной «работы» нескольких условий отбора

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

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

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

2. Условия отбора, размещенные на разных строках в одном или нескольких столбцах таблицы критериев, предписывают расширенному фильтру показывать все строки, для которых выполняется хотя бы одно из этих условий. (Параметры отбора соединены логическими операторами «ИЛИ».)

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

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

Задача №6:

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

В результате работы расширенного фильтра показаны все пластины базы данных, имеющие массу менее 0,1 тонны (иллюстрация правила №1).

Задача №7:

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

Задача решена – показаны записи обо всех пластинах базы данных и обо всех изделиях тяжелее 1 тонны (иллюстрация правила №2).

Для отмены действия расширенного фильтра необходимо выполнить команду главного меню программы Excel «Данные» - «Фильтр» - «Отобразить все».

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

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$ F $3 »!

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

Итоги.

При вводе условий поиска можно использовать общепринятые знаки подстановки и математические знаки:

  • * — любое количество любых символов
  • ? – один любой символ
  • = — равно
  • > — больше
  • >= — больше или равно
  • — не равно

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

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

«Поковыряйтесь» самостоятельно в возможностях этого инструмента — он стоит того, чтобы в нем разобраться! Возросшая эффективность вашей работы многократно перекроет затраты времени потраченного на изучение!

Продолжение темы хранения и управления большими объемами информации — в следующих статьях цикла.

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, вопросы и замечания пишите в комментариях внизу страницы.

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

Первый – автофильтр, предназначен для наиболее простых операций – выделение записей с конкретным значением (например, только выделение только записей, относящихся к Леброну Джеймсу), данных, лежащих в определенном диапазоне (или выше среднего или первую десятку) или ячеек/шрифтов определенного цвета (кстати, очень удобно). Соответственно, пользоваться им очень просто. Вам достаточно выделить те данные, которые вы хотите видеть отфильтрованными. Потом команда «Данные»/ «Фильтр». На каждой верхней ячейке верхней таблицы появится флажок списка, там уже легко разобраться с каждой командой, освоить просто и объяснять, я надеюсь, дальше не нужно, только нюансы использования автофильтра:

1) Работает только с неразрывным диапазоном. Два разных списка на одном листе отфильтровать уже не получится.

2) Самая верхняя строчка таблица автоматически назначается заголовком и в фильтрации не участвует.

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

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

Теперь, переходим к расширенному фильтру. Он отличается от автофильтра более тонкой настройкой, но и большим выбором при фильтрации данных. В частности:

1) Задает столько условий, сколько необходимо.

2) Позволяет выделить ячейки с уникальными (неповторяющимися) данными. Это часто бывает нужно в работе с данными и опция отлично справляется с проблемой.

3) Позволяет копировать результат фильтра в отдельное место, не трогая основной массив.

Итак, основное отличие в работе с этим фильтром заключается, что предварительно нам нужно подготовить таблицу условий. Делается она просто. Копируются заголовки основной таблицы и вставляются в удобное нам место (предлагаю выше основной таблицы). В этой таблице должно быть столько строчек, чтобы после определения условий не залезть на основную таблицу.

Примеры условий:

1) ‘L*’ – ячейки начинающиеся с L

2) ‘>5’ - данные больше 5

Если вы удаляете из отфильтрованной таблицы строки, то они удалятся, не прихватывая с собой соседей. Т.е. если таблица отфильтрована и показывает строчки с 26-29 и 31-25, выделение всех строк и их удаление не повлечет удаление строчки 30. Это удобно, лично я часто пользуюсь этим при написании макросов. Какое преимущество это дает – часто нам достаются таблицы, которые надо привести в рабочий вид, т.е. удалить, например пустые строки. Что мы делаем: применяем фильтр к таблице, показывая только те строки, которые нам не нужны, затем удаляем всю таблицу, включая заголовок. Удаляются ненужные строки и заголовок, при этом таблица не имеет пробелов и составляет единый диапазон. А строку заголовков можно добавить простой операций копирования из загодя заготовленной области. Почему это важно при написании макросов? Неизвестно, с какой строки начинаются нежелательные данные и непонятно, с какой строки их начать удалять, удаление всей таблицы помогает быстро решить эту проблему.

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

Скрытые при помощи фильтра строки таблицы не исчезают. Можно условно представить, что их высота становится равной нулю (про изменение высоты строк и ширины столбцов я ранее рассказывал). Таким образом, остальные строки, не скрытые фильтром, как бы "склеиваются". То, что получается в результате, и есть таблица с наложенным фильтром.

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

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

Как сделать (наложить) фильтр на таблицу Excel

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



[нажмите на картинку для увеличения]

Обратите внимание:

Если выделить ВСЮ строку, то Эксель автоматически установит фильтр на ВСЕ столбцы, в которых есть хоть что-то. Если в столбце ничего нет, то фильтр не накладывается.

После того как выделили нужные столбцы, нужно собственно установить фильтр. Самый простой способ — это зайти на вкладке ленты "Главное" / "Фильтр и сортировка" и нажать фильтр. Привожу скриншот из Office 2010, в прочих версиях Excel фильтр на таблицу накладывается аналогично.



[нажмите на картинку для увеличения]

После нажатия этой кнопки в выделенные ячейки добавится фильтр.

Если не выделять несколько ячеек, а просто щёлкнуть на одну, то фильтр добавляется на ВСЮ строку, как если бы вы её выделили.

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



[нажмите на картинку для увеличения]

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

Как пользоваться фильтром в Эксель

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


[нажмите на картинку для увеличения]

Смысл фильтра:

заключается в том, что Excel оставит только те строки таблицы, которые в ЭТОМ (с настроенным фильтром) столбце содержат ячейку с выбранным значением. Прочие строки будут скрыты.

Чтобы убрать фильтрацию (не удаляя фильтр!) просто отметьте галочками все пункты. Такой же эффект будет при удалении фильтра совсем — таблица вновь примет первоначальный вид.

Наложение нескольких фильтров на таблицу Эксель

Фильтры в таблице Эксель можно комбинировать. Наложение нескольких фильтров происходит по принципу логического "И". Что это значит, давайте посмотрим на примере.

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

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

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

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

Подведём итоги

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

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

Скачать файл, на примере которого я рассматривал работу с фильтрами, вы можете

Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами.

Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ.

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

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

Где в Excel фильтры — их виды

Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.


Основные функции фильтрации в Excel:

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

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

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

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

  1. Создать таблицу с данными для дальнейшей работы с ней. В ней не должно быть пустых строк.
  2. Создать таблицу с условиями отбора.
  3. Запустить расширенный фильтр.

Рассмотрим пример настройки.
У нас есть таблица со столбцами Товар, Количество и Цена.

К примеру, нужно отсортировать строки, названия товаров которых начинаются со слова «Гвозди» Под это условие попадают несколько строк.

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора – Гвозди (ячейка А2).

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

Затем необходимо:

  1. выделить любую из ячеек,
  2. открыть «Расширенный фильтр» по пути: Данные – Сортировка и фильтр – Дополнительно,
  3. проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
  4. в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.

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

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

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel . В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

Информацию в таблице можно фильтровать по столбцам – одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Пример 1

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

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Пример 2

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

Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» — «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

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

Решение проблемы:

  1. выделить столбец с датами,
  2. открыть вкладку Excel в главном меню,
  3. выбрать кнопку «Ячейки», в выпадающем списке выбрать параметр «Преобразовать текст в дату».

К популярным ошибкам пользователей при работе с данной программой также стоит отнести:

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

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

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

Фильтрация Excel | ПТИЦА-СЕКРЕТАРЬ

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

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

В редакторе Excel инструменты фильтрации и сортировки (выделены красным) предлагаются как бы в комплексе.

Инструменты фильтрации

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

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

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

Исходная таблица

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

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

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

Кнопки управления фильтрацией

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

Фильтрация по выбранным данным.

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

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

Выпадающий список фильтрации

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

Результат фильтрации

Чтобы вернуть отображение всех данных нужно опять вызвать выпадающее меню и воспользоваться командой Удалить фильтр с «Отдел» или глобально – выйти из режима работы с фильтрами (выбрать в меню Данные пункт Фильтр).

Продолжение в следующей статье.

Facebook

Twitter

Вконтакте

Google+

Данные фильтра в диапазоне или таблице

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

Диапазон данных фильтра

  1. Выберите любую ячейку в диапазоне.

  2. Выберите пункт Данные > Фильтр .

  3. Выберите стрелку заголовка столбца.

  4. Выберите Текстовые фильтры или Числовые фильтры , а затем выберите сравнение, например, Между .

  5. Введите критерии фильтрации и выберите OK .

Данные фильтра в таблице

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

  1. Выберите стрелку заголовка столбца, который вы хотите отфильтровать.

  2. Снимите флажок (Выбрать все) и установите флажки, которые вы хотите видеть.

  3. Нажмите OK .

    Стрелка заголовка столбца на значке фильтра . Выберите этот значок, чтобы изменить или очистить фильтр.

Похожие темы

Обучение работе с Excel: фильтрация данных в таблице

Советы и примеры сортировки и фильтрации данных по цвету

Фильтрация данных в сводной таблице

Фильтрация по расширенным критериям

Удаление фильтра

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

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

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

Подробнее о фильтрации

Два типа фильтров

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

Многоразовый фильтр

Чтобы определить, был ли применен фильтр, найдите значок в заголовке столбца:

90 106

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

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

  • Кнопка «Фильтр» указывает на то, что был применен фильтр.

    Когда вы наводите указатель мыши на заголовок отфильтрованного столбца, всплывающая подсказка показывает фильтр, примененный к этому столбцу, например, «Цвет ячейки равен красному» или «Больше 150».

    • Результаты повторной фильтрации могут отличаться по следующим причинам:

    90 106

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

  • Возвращаемые значения формулы изменились, и рабочий лист был пересчитан.

    • Не смешивайте типы данных

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

    Данные фильтра в таблице

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

    1. Выберите данные для фильтрации.На вкладке Main нажмите Format as Table , а затем выберите Format as Table .

    2. В диалоговом окне Создать таблицу вы можете решить, будут ли у таблицы заголовки.

      90 106

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

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

    5. Щелкните OK .

    6. Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтра.

    Диапазон данных фильтра

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

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

    2. На карточке Данные выберите Фильтр .

    Параметры фильтра для таблиц или диапазонов

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

    .

    90 221

    Параметр Числовые фильтры применяет пользовательский фильтр:

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

    Вот как это сделать:

    1. Щелкните стрелку фильтра рядом с Март> Числовые фильтры > Менее и введите 6000.

    2. Нажмите OK .

      Excel для Интернета применяет фильтр и отображает только регионы с продажами менее 6000 долларов США.

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

    Для очистки фильтра колонки

    Чтобы удалить все фильтры в таблице или диапазоне

    .

    фильтрация данных с помощью AutoFilter

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

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

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

    Как?

    Выберите данные для фильтрации

    1. На вкладке Данные в группе Сортировка и фильтрация щелкните Фильтр .

    2. Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать параметры фильтрации.

      Примечание В зависимости от типа данных столбец в списке в Microsoft Excel отображает Числовые фильтры или Текстовые фильтры .

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

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

    1. Используйте поле Search для ввода текста или цифр, которые вы хотите найти

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

    .

    3. Используйте расширенные критерии

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

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

    2. Для поиска текста в столбце введите текст или цифры в поле Поиск . При желании вы можете использовать подстановочные знаки, такие как звездочка ( * ) или вопросительный знак ( - ). Нажмите ENTER, чтобы увидеть результаты.

    Фильтрация данных по заданным условиям

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

    1. Выберите Числовые фильтры или Текстовые фильтры в списке.Появится меню, позволяющее фильтровать по различным условиям.

    2. Выберите условие, а затем выберите или введите критерии. Нажмите кнопку и , чтобы объединить критерии (то есть, по крайней мере, два критерия, которые должны быть выполнены), и кнопку или , если должно быть выполнено только одно из множества условий.

    3. Щелкните OK , чтобы применить фильтр и получить желаемые результаты.

    Следующие шаги

    • Поэкспериментируйте с текстовыми и числовыми фильтрами данных, попробовав множество встроенных тестовых условий, таких как Равно , Не равно , Содержит , Больше и Меньше . Дополнительные сведения см. в разделе Фильтрация данных в диапазоне или таблице.

      Примечание Некоторые из этих условий применяются только к тексту, а некоторые — только к числам.

    • Создайте настраиваемый фильтр, использующий несколько критериев. Дополнительные сведения см. в разделе Фильтр с использованием расширенных критериев.

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

    .

    Фильтрация Excel: расширенная, горизонтальная, несколько столбцов - Как фильтровать данные в Excel?

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

    Операция фильтра данных Excel

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

    • Числа (включая логические значения: ИСТИНА и ЛОЖЬ).
    • Финики
    • Тексты (включая значения ошибок).

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

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

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

    Синий цвет, которым написаны номера строк. Только номера строк в отфильтрованной таблице окрашены в синий цвет. Это очень полезно, потому что невооруженным глазом не всегда видно, что определенные строки скрыты — представьте себе таблицу из 10 000 строк, когда только 5 из них скрыты — это было бы трудно увидеть.
    В столбце, который был отфильтрован, есть знак воронки, указывающий, что здесь был применен какой-то фильтр.

    Поисковая система в фильтре Excel

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

    То, что вы вводите в поисковик, достаточно того, что оно входит в состав пунктов списка - если ввести слово "Дерево", то пункт "Хвойное дерево" будет найден и отображен в списке.
    Поисковая система нечувствительна к регистру - если ввести "Дерево", будет также найдено "дерево".
    Поисковая система имеет некоторые дополнительные функции, которые рассматриваются в отдельных разделах фильтрации (Фильтрация по дате, Пользовательская фильтрация).

    .

    Фильтрация данных без секретов - Excelness.com

    Чтение минут: 4

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

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

    Это лишь некоторые примеры использования фильтрации данных.


    Фильтрация - где она у меня?

    Инструменты фильтрации доступны на ленте ДАННЫЕ в разделе СОРТИРОВКА И ФИЛЬТР.


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

    • Нажмите кнопку ФИЛЬТР на ленте ДАННЫЕ:

    • Используйте сочетание клавиш: CTRL + SHIFT + L

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


    Обратите внимание: если в отфильтрованной таблице есть целая пустая строка или весь пустой столбец, фильтроваться будет только часть данных (до пустой строки/столбца).Это связано с тем, что Excel по умолчанию выбирает «непрерывный». Он просто обрабатывает эту область как единую область фильтра. В этом случае вы можете удалить пустую строку/столбец из таблицы, а затем снова применить фильтрацию.

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

    Щелкнув левой кнопкой мыши по значку фильтра, вы увидите меню фильтра:


    В этом меню есть несколько параметров фильтрации.

    • Отметьте отдельные значения в данном столбце символом «галочка», например.выберите только "Свекла":

    Отфильтрованная таблица будет выглядеть так:


    • Установите или снимите флажок рядом с ВЫБЕРИТЕ ВСЕ, что либо выберет все значения, либо отменит выбор всех значений.
    • Выберите ОЧИСТИТЬ ФИЛЬТР С…, что удалит фильтрацию из данного столбца (например, ВЫБРАТЬ ВСЕ).
    • ФИЛЬТР ПО ЦВЕТУ позволит вам фильтровать только те строки, для которых ячейки в данном столбце имеют фон определенного цвета.Например, если ягоды в примере ниже имели светло-красный фон, то в меню фильтрации с помощью этой опции вы сможете отфильтровать строки, для которых ячейки в категории Товары имеют светло-красный фон:


    • Параметр ФИЛЬТР ТЕКСТА позволяет фильтровать ячейки, содержащие определенный текст.

    Если отфильтрованный столбец содержит числовые данные, здесь появится параметр ЧИСЛОВЫЕ ФИЛЬТРЫ.Эта опция позволяет фильтровать данные по определенным значениям (например, цена выше 2 злотых):


    • Окно ПРОСМОТР позволяет искать значения для фильтрации. Например, ввод текста "ja" (без кавычек) предложит вам выбрать только " ja синий" и " ja свадьба", которые содержат эту фразу.

    В этом случае появится дополнительная опция - ДОБАВИТЬ ТЕКУЩУЮ МЕТКИ В ФИЛЬТР.Если вы отметите его, выбранные значения будут добавлены к ранее использованному фильтру в данном столбце. Это особенно полезно, когда у вас нет единого шаблона фильтра. Вместо этого вам достаточно выбрать из списка несколько конкретных примеров — например, конкретных сотрудников.

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

    Например, если в столбце Категория выбрать только " Овощи" , то в столбце Товары для фильтрации будут доступны только товары категории Овощи :


    90 105

    Если вы хотите быстро избавиться от всех фильтров из таблицы, на ленте ДАННЫЕ в разделе СОРТИРОВКА И ФИЛЬТР выберите ОЧИСТИТЬ.


    Чтобы отключить фильтрацию в таблице, выберите одну из ячеек, содержащихся в таблице данных, и щелкните значок ФИЛЬТР.

    СКАЧАТЬ PDF С 20 СОКРАЩЕННЫМИ СОКРАЩЕНИЯМИ EXCEL

    Дополнительно получите два купона на скидку !

    Успех!

    Спасибо. Вскоре вы получите от меня электронное письмо и обещанные материалы.

    Резюме

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

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

    Возникли проблемы с Excel? Получите справку по Сохраняйте спокойствие и используйте группу Excel .

    90 112

    90 139 90 140 Связанные записи: 90 141 90 142

    .Учебник по фильтрам

    Excel | Excel - как пользоваться фильтрами

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

    1. В нашем примере мы будем использовать список покупок, который мы будем фильтровать по категориям. Сначала подготавливаем такую ​​таблицу (см. рисунок ниже).

    2. Выберите строку заголовка таблицы и на вкладке Главная щелкните значок Сортировка и фильтрация и Фильтр (в Excel 2003 в меню Данные выберите Фильтр и Автофильтр ).

    3. В таблице появляются кнопки фильтрации данных. Нажмите на Категория . В списке снимаем галочку с категории, которую нужно скрыть (фильтровать), например химия . Нажмите ОК .

    4. После применения фильтра остались данные только из одной категории Еда .

    Мы можем легко суммировать затраты из таблицы примера.Просто поместите курсор мыши в поле D18 и нажмите кнопку AutoSum .

    5. Мы можем повторить точку 3 подсказок и выбрать другие данные для отображения.Тогда сумма затрат изменится - в нее всегда будут включены товары, видимые в списке.

    .

    Excel Online — сортировка и фильтрация

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

    Сортировка в Excel Online
    • Если мы работаем с таблицей, где определенные значения (например,количество часов) присваиваются датам и дням недели, мы можем сортировать числовые данные, не опасаясь, что они оторвутся от соответствующих им дней — в данном случае речь идет о содержимом ячеек из одной строки. Итак, мы выбираем все три столбца, щелкнув C и перетащив их на A .
    • На ленте в разделе Редактирование находим функцию Сортировка и фильтрация , на которую нажимаем.

    • Чтобы отсортировать элементы от меньшего к большему, нажмите Сортировать по возрастанию .Если мы хотим упорядочить их в обратном порядке, мы нажимаем Сортировать по убыванию .
    Фильтрация в Excel Online
    • Excel Online позволяет применять фильтр данных, благодаря которому мы найдем только те результаты, которые соответствуют заданным критериям. Мы снова выбираем столбцы, с которыми хотим действовать. Мы можем выбрать много столбцов — применяем фильтрацию к каждому из них отдельно.
    • На ленте в разделе Редактирование находим функцию Сортировка и фильтрация , на которую нажимаем.
    • Во всплывающем меню выберите параметр Фильтр .
    • Над нашими столбцами появится всплывающий значок. Здесь у нас есть еще несколько вариантов. Начнем с Фильтр... .

    • Появится окно со всеми элементами данного столбца, включая повторяющиеся. Мы хотим проверить, сколько суббот включено в таблицу, поэтому сначала мы нажимаем Select All , чтобы отменить выбор всех элементов, затем мы выбираем только поле «Суббота» и нажимаем OK .

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

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

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

    Вам понравилась эта статья? Поделись с другими!

    .

    Фильтрация в Excel по нескольким столбцам

    Иногда нам нужно отфильтровать таблицу сразу по нескольким столбцам. Это не проблема для Ms Excel, но есть некоторые ограничения. Вы можете узнать больше о фильтрации на http://arkuszekalkulacyjne.pl/

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

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


    Невозможно фильтровать несколько столбцов по отдельности. Если значение соответствует условиям одного столбца, а другой не скрыт. Обратите внимание, что фильтрация всегда ОБЩАЯ.

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

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


    При удалении фильтра из выбранных столбцов щелкните стрелку в заголовке данного столбца и нажмите Очистить фильтр из. Если же мы хотим удалить все фильтры, перейдите на вкладку ДАННЫЕ и выберите параметр «Очистить». , мы затем очистим фильтры, но сохраним стрелки автофильтра. Вы также можете использовать сочетание клавиш CTRL + SHIFT + L.

    .

    Смотрите также

    Только новые статьи

    Введите свой e-mail

    Видео-курс

    Blender для новичков

    Ваше имя:Ваш E-Mail:

    Содержание, карта.