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

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

Сортировка списков

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

Сделайте небольшой список для тренировки.

Выделите его.

Нажмите кнопку «Сортировка и фильтр» на панели «Редактирование» ленты «Главная».

Выберите «Сортировка от А до Я». Наш список будет отсортирован по первому столбцу, т.е. по полю ФИО.

Если надо отсортировать список по нескольким полям, то для этого предназначен пункт «Настраиваемая сортировка..».

Сложная сортировка подразумевает упорядочение данных по нескольким полям. Добавлять поля можно при помощи кнопки «Добавить уровень».

В итоге список будет отсортирован, согласно установленным параметрам сложной сортировки.

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

Перемещать уровни сортировки можно при помощи кнопок «Вверх» и «Вниз».

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

Фильтрация списков

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

Фильтры бывают двух типов: обычный фильтр (его еще называют автофильтр) и расширенный фильтр.

Для применения автофильтра нажмите ту же кнопку, что и при сортировке — «Сортировка и фильтр» и выберите пункт «Фильтр» (конечно же, перед этим должен быть выделен диапазон ячеек).

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

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

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

При использовании расширенного фильтра критерии отбора задаются на рабочем листе.

Для этого надо сделать следующее.

Скопируйте и вставьте на свободное место шапку списка.

В соответствующем поле (полях) задайте критерии фильтрации.

Выделите основной список.

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

На той же панели нажмите кнопку «Дополнительно».

В появившемся окне «Расширенный фильтр» задайте необходимые диапазоны ячеек.

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

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

В начало страницы

В начало страницы

2 метода:Применение фильтраУдаление фильтра

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

Метод 1 Применение фильтра

1. 

   Откройте таблицу, в которой вы хотите отфильтровать данные.

2. Подготовьте данные для фильтрования.

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

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

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

4. 

   Перейдите на вкладку «Данные» меню Excel.

5. 

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

6. Нажмите на стрелку.

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

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

Метод 2 Удаление фильтра

1. Нажмите на стрелку раскрывающегося меню фильтра (верхняя ячейка столбца) и выберите «Снять фильтр с …». Таким образом, удаляется любое фильтрование с соответствующего столбца.

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

Советы

• При применении фильтров, вы также можете отсортировать данные. Их можно сортировать по возрастанию или убыванию (числовые данные), по алфавиту (прямой и обратный порядок) или по цвету шрифта или ячейки.
• Для обновления результатов фильтрования данных, перейдите на вкладку «Данные» в меню Excel, а затем нажмите «Применить повторно».

Информация о статье

Эту страницу просматривали 27 732 раз.

Была ли эта статья полезной?

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

Автофильтр и расширенный фильтр в Excel

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

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

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

Сразу видим результат:

Особенности работы инструмента:

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

У расширенного фильтра гораздо больше возможностей:

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

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

Готовый пример – как использовать расширенный фильтр в Excel:

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

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

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

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

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

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

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

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

Основные правила:

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

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

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

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

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.