2 способа фильтрации данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

«Глаза боятся, а руки делают»

P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.

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

Также статьи о работе с данными в Экселе:

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

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

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

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

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

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

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

Расширенный фильтр в Excel: примеры

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

В Excel представлен простейший фильтр, который запускается с вкладки «Данные» — «Фильтр» (Data — Filter в англоязычной версии программы) или при помощи ярлыка на панели инструментов, похожего на конусообразную воронку для переливания жидкости в ёмкости с узким горлышком.

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

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

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

В приведённой таблице строки 1 и 2 предназначены для диапазона условий, строки с 4 по 7 - для диапазона исходных данных.

Для начала следует ввести в строку 2 соответствующие значения, от которых будет отталкиваться расширенный фильтр в Excel.

Запуск фильтра осуществляется с помощью выделения ячеек исходных данных, после чего необходимо выбрать вкладку «Данные» и нажать кнопку «Дополнительно» (Data — Advanced соответственно).

В открывшемся окне отобразится диапазон выделенных ячеек в поле «Исходный диапазон». Согласно приведённому примеру, строка принимает значение «$A$4:$F$12».

Поле «Диапазон условий» должно заполниться значениями «$A$1:$F$2».

Окошко также содержит два условия:

• фильтровать список на месте;
• скопировать результат в другое место.

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

Основываясь на введённых данных, фильтр сформирует следующую таблицу.

При использовании условия «Скопировать результат в другое место» значения из 4 и 5 строк отобразятся в заданном пользователем диапазоне. Исходный диапазон же останется без изменений.

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

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

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

Таблица символов для сложных запросов приведена ниже.

возвращает все слова, начинающиеся с буквы П:

• персик, помидор, петрушка (если ввести в ячейку B2);
• Пятёрочка (если ввести в ячейку F2).

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

выведутся все непустые ячейки.

все значения, где имеется буквосочетание «ию»: июнь, июль.

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

Стоит знать, что значок * может означать любое количество символов. То есть при введённом значении «п*» будут возвращены все значения, вне зависимости от количества символов после буквы «п».

Знак «?» подразумевает только один символ.

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

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

Таблица значений примет следующий вид:

Ещё один способ фильтрования данных осуществляется с помощью команды «Вставка — Таблица — Сводная таблица» (Insert — Table — PivotTable в англоязычной версии).

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

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

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

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

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

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

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

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

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

Фильтр устанавливается на заголовки и подзаголовки таблиц; главное, чтобы ячейки, на которые будет устанавливаться фильтр, не были пустыми. А располагается он в меню книги Excel на вкладке «Данные», раздел «Сортировка и фильтр»:

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

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

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

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

В нем можно задать следующие условия:

• Условия «равно» и «не равно» пояснений не требуют, т.к. с ними все предельно ясно;
• «больше», «меньше», «больше или равно» и «меньше или равно». Как строки можно сравнить друг с другом? Чтобы это понять, вспомните, каким образом Excel выполняет сортировку. Т.е. чем дальше в списке сортировки будет находиться строка, чем больше ее значение. Следующие утверждения являются истинными (верными): A<Б; АА>А; А<=Я; 5 яблок < апельсин.
• «начинается с», «не начинается с», «заканчивается на», «не заканчивается на», «содержит» и «не содержит». В принципе, условия говорят сами за себя и в качестве значений могут принимать символ либо набор символов. Обратите на подсказку в окне, расположенную ниже всех условий (пояснения будут дальше).

В случае необходимости можно задать 2 условия, используя логическое «И» либо «ИЛИ».

В случае выбора «И» должны выполняться все условия. Следите за тем, чтобы условия не исключали друга, например «<Значение И >Значение», т.к. ничто в один и тот же момент не может быть и больше, и меньше одного и того же показателя.

При использовании «ИЛИ» должно соблюдаться хотя бы одно из заданных условий.

В самом конце окна пользовательского автофильтра приведена подсказка. Первая ее часть: «Знак вопроса ''?'' обозначает один любой знак …». Т.е. при проставлении условий, когда нельзя точно определить символ в конкретном месте строки, подставьте на его место «?». Примеры условий:

• Начинается с «?ва» (начинается с любого символа за которым следуют символы «ва») вернет результаты: «Иванов», «Иванова», «кварц», «сват» и другие строки, подходящие под условие;
• Равно «. » – вернет в результате строку, которая содержит 7 любых символов.

Вторая часть подсказки: «Знак ''*'' обозначает последовательность любых знаков». Если в условии невозможно определить, какие символы и в каком количестве должны находиться в строке, то вместо них подставьте «*». Примеры условий:

• Заканчивается на «о*т» (заканчивается символами «о», после которого идет любая последовательность символов, затем символ «т») вернет результат: «пот», «торт», «оборот» и даже такой – «рвнщуооовиунистврункт».
• Равно «*» – вернет строку, которая содержит хотя бы один символ.

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

• «Выше среднего» и «Ниже среднего» – возвращает значения, которые выше и ниже среднего значения соответственно. Среднее значение рассчитывается, исходя из всех числовых значений списка столбца;
• «Первые 10…» – клик по данному пункту вызывает окно:

Здесь можно задать какие элементы выводить первые из наибольших или первые из наименьших. Также, какое количество элементов вывести, если в последнем поле выбран пункт «элементов списка». Если же выбран пункт «% от количества элементов», второе значение задает данный процент. Т.е. если в списке 10 значений, то будет выбрано самое высокое (или самое низкое) значение. Если в списке 1000 значений, то либо первые, либо последние 100.

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

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

Excel 2013: Расширенный фильтр в Excel

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

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

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

1. Откройте вкладку Данные. затем нажмите команду Фильтр. В каждом заголовке столбца появится кнопка со стрелкой. Если Вы уже применяли фильтры в таблице, то можете пропустить этот шаг.
2. Нажмите на кнопку со стрелкой в столбце, который необходимо отфильтровать. В данном примере мы выберем столбец C.

Расширенный фильтр в Excel и примеры его возможностей

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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