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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Кнопка фильтра _з0з_ означает, что фильтр применен.

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

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

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

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

Не используйте смешанные типы данных

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтры можно спокойно «прикрепить» ко всем столбцам.

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

Теперь рассмотрим выпадающее меню каждого фильтра (они будут одинаковы):

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

— Фильтр по цвету;

— возможность снять фильтр;

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

— возможность «выделить все» (если снять этот флажок, то совершенно все столбцы попросту перестанут отображаться на листе);

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

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

Если говорить о числовых фильтрах , то здесь программа также предлагает достаточно большое количество самых разных вариантов сортировки имеющихся значений. Это: «Больше», «Больше или равно», «Равно», «Меньше или равно», «Меньше», «Не равно», «Между указанными значениями». Активируем пункт «Первые 10» и появляется окошко

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

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

Настраиваемый автофильтр в Excel 2010, как и было сказано, дает расширенный доступ к параметрам фильтрации. С его помощью можно задать условие (состоит из 2 выражений или «логических функций» ИЛИ / И), согласно которому будет проведен отбор данных.

Текстовые фильтры были созданы исключительно для работы с текстовыми значениями. Здесь для отбора используются такие параметры, как: «Содержит», «Не содержит», «Начинается с…», «Заканчивается на…», а также «Равно», «Не равно». Их настройка достаточно похожа на настройку любого числового фильтра.

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

Фильтрацию, когда в ней более нет нужды, можно отменить любым из возможных способов – использовать комбинацию кнопок «Shift+Ctrl+L», нажать кнопочку «Фильтр» (вкладка «Главные», большая пиктограмма «Сортировка и фильтр», входящая в группу «Редактирование»). Или же просто нажать кнопочку «Фильтр» на вкладке «Данные».

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

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

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

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

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

Теперь ищем вкладку «Данные», «Сортировка и фильтр» и нажимаем небольшую пиктограмму «Дополнительно» и видим вот такое окошко

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

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

«Диапазон условий», как вы догадались, содержит адрес тех ячеек, в которых хранятся условия фильтрации и названия столбцов. Для нас это будет «В10:С12».

Если вы решили отойти от примера и выбрали функцию «скопировать результат…», то в 3-ей графе необходимо указать адрес диапазона тех ячеек, куда программе необходимо отправить данные прошедшие фильтр. Поэтому мы также выберем эту возможность и укажем «А27:С27».

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

Успехов в работе.

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

Как добавить

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

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

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

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

Как работает

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

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

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

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

Как удалить

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

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

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

Числовой

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

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

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

Текстовый

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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


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

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



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

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

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

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

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


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

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

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

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


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

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

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

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

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

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


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

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

Здравствуйте друзья!

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

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

Использовать можно двумя способами:

С помощью диалогового окна «Расширенный фильтр»

Рассмотрим это на примере, нам нужно отобрать страны в мире где есть монархия. Для удобства и наглядной визуализации, скопируйте шапку таблицы немного выше, создайте несколько пустых строк. Обязательно между двумя таблицами должен быть разделитель – пустая строка . Теперь можем применить фильтр, согласно заданных условий.
Для начала выделяем любую из ячеек диапазона хранения ваших данных, на вкладке «Данные» , нужно найти пункт «Дополнительно» и в открывшемся диалоговом окне «Расширенный фильтр» мы видим переключатель «Скопировать результат, в другое место» который позволяет вам перенести полученные данные в указанное место, но нам это пока не нужно. «Исходный диапазон» в диалоговом окне проставляется по умолчанию, а вот «Диапазон условий» вам нужно проставить вручную. Советую не спешить и указывать только заполненные строки с условием, так как пустая строка в диапазоне - это условие — «отобрать всё что есть» . При подтверждении введенных условий в диалоговом окне «Расширенный фильтр» мы получаем указанный результат.

С помощью макроса

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

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C2:I2")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("C5").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("C1").CurrentRegion End If End Sub

Private Sub Worksheet_Change (ByVal Target As Range )

If Not Intersect (Target , Range ("C2:I2" ) ) Is Nothing Then

On Error Resume Next

ActiveSheet . ShowAllData

Range ("C5" ) . CurrentRegion . AdvancedFilter Action : = xlFilterInPlace , CriteriaRange : = Range ("C1" ) . CurrentRegion

End If

End Sub

Этот код будет автоматически, применятся при любом изменении полей и накладывать фильтр на ваши данные. Он просматривает диапазон (C2:I2) на наличие введенных данных и мгновенно применяет условия, к диапазону ваших данных, которые начинаются с ячейки (C5).
Кроме точно сформулированных условий, когда используется можно также использовать знаки подстановки и замены, разнообразные математические знаки, которые позволят произвести приблизительный поиск или захватит .

Вот предоставлены варианты:

Условие

Полученный результат

Отбор всех ячеек, где есть пять символов. (Например, Прага)

Отбор текста на 4 знака, который начинается с «в» и заканчивается на «а» (Например, Вена)

Отбор значений с тремя буквами в конце, одна из которых неизвестна (Например, София)

Отбор всех значений, которые начинаются с букв «пр» (Например, Прага)

Отбор значений, которые начинаются с «п», содержат или заканчиваются на «р» (Например, Париж)

Отбор значений, которые заканчиваются на «н» (Например, Берлин)

Отбор точного совпадения (Например, Рим)

<>монарх*

Производится отбор всех пустых ячеек

Производится отбор всех не пустых ячеек

Отбор значений, которые равны или больше 40

Точный отбор значений, которые равны 20.

Отбор всех значений, которые начинаются с «р»

Отбор данных, которые содержат «монарх»

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

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

Если у вас есть дополнения, пишите в комментариях. Был рад вам помочь!

«Иным людям богатство только и приносит, что страх потерять его.»

А. Ривароль