Каково Назначение Команды Автофильтр в Excel • Настройки и условия

Фильтры на VBA (AutoFilter Method) читать подробное руководство

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

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

Фильтры на VBA читать подробное руководство по AutoFilter Method онлайн
Метод AutoFilter имеет 5 необязательных параметров, которые мы рассмотрим далее. Если мы не укажем ни один из параметров, как в приведенных выше примерах, метод AutoFilter включит / выключит фильтры для указанного диапазона. Это переключение. Если фильтры включены, они будут выключены, и наоборот.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Я отдаю предпочтение методу использования таблиц, потому что нам не нужно беспокоиться об изменении ссылок на диапазон при увеличении или уменьшении таблицы. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Если мы хотим отменить действия автофильтра и вернуть таблицу в первоначальный вид, тогда снова щелкните по выпадающему списку (с воронкой) и выберите опцию «(Выделить все)». Но если к таблице было применено фильтрование по нескольким столбцам, тогда удобнее будет воспользоваться инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Очистить».
Каково Назначение Команды Автофильтр в Excel • Настройки и условия

Автофильтр Excel 2007. Бухгалтер&Компьютер, № 3, Февраль, 2010 | Factor

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

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

Фильтр в Excel

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

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

Каково Назначение Команды Автофильтр в Excel • Настройки и условия

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

Меню фильтра Excel

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

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

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

Пользовательский автофильтр Excel

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

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

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

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

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

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

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

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

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

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

числовой фильтр

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

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

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
При статичной или абсолютной ссылке проверяться будет только указанная ячейка, относительная дает старт на проверку всех ячеек, начиная с первой. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
На рисунке показана совместная работа операторов «И» и «ИЛИ». После установки таких параметров, будут выбраны строки с надписями «Персик» или «Лук». Причём для «Персик» необходимым является выполнение еще двух критериев: наличие города Москва и менеджера Волина. Для «Лук» нужен только III квартал и город Самара.

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

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

Стандартный фильтр

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

Запуск

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

Результат работы

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

Параметры выбора

Синхронизация по дате

В качестве примера, можно произвести выборку событий между двумя датами: 1 июня 2014 года и 31 декабря 2014 года. Для этого:

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

Результатом будет показ информации между 1 июня и 31 декабря 2014 года.

Текстовой отбор

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

Числовой критерий

Числовые фильтры

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

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

Изменение строки

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

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

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

Фильтр по строка

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

Как сделать в excel автофильтр?

  • формулы нужно вставлять в пустые строки, не содержащие заголовки «таблицы» или отличающиеся от них;
  • формула должна начинать «работать» с первых ячеек после заголовка, чтобы не пропустить какое-либо значение из таблицы. Поэтому ссылка на применение формулы начинается с первой строки в любом столбце;
  • ссылка на проверку по формуле должна быть относительной, вида B5, в отличие от абсолютной, которая пишется в виде $B$5. При статичной или абсолютной ссылке проверяться будет только указанная ячейка, относительная дает старт на проверку всех ячеек, начиная с первой.

N», «Дата», «НаимПредпр», «ИНН», «ОбщСум», «СумБезНДС», «НДС». Область данных в реестре начинается со второй строки рабочего листа. В базе контрагентов нет пустых строк, поэтому диапазон данных Excel сможет распознать автоматически. Работу с автофильтром мы начнем с отбора текстовых значений из базы данных.

Автофильтр Excel 2007

Бухгалтер&Компьютер

Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

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

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

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

Для работы с автофильтром Excel 2007 мы воспользуемся хорошо знакомым реестром контрагентов, фрагмент которого показан на рис. 1. Он содержит семь полей «

N», «Дата», «НаимПредпр», «ИНН», «ОбщСум», «СумБезНДС», «НДС». Область данных в реестре начинается со второй строки рабочего листа. В базе контрагентов нет пустых строк, поэтому диапазон данных Excel сможет распознать автоматически. Работу с автофильтром мы начнем с отбора текстовых значений из базы данных.

Наша первая задача при работе с автофильтром — отобрать из базы данных контрагентов по их наименованию (в реестре это поле «

Сортировка и фильтр» (рис. 2). Откроется меню, изображенное на рис. 3;

Фильтр». В каждой колонке строки заголовков появились значки выбора. Они похожи на кнопки выпадающего списка (рис. 1). С их помощью можно формировать условия для отбора записей из базы данных. Щелчок мышкой на любом значке раскрывает список условий. Этот список будет построен с учетом типа данных в ячейках соответствующей колонки;

НаимПредпр». Раскроется меню, изображенное на рис. 4 на с. 17. В этом меню нас интересует пункт «Текстовые фильтры»;

равно…». Появится окно, изображенное на рис. 6 на с. 18. В левой части этого окна находится список для определения типа операции. Сейчас там установлены поле «НаимПредпр» и операция «равно». В правой части окна находится список для выбора конкретного значения;

8) щелкаем левой кнопкой на этом списке. Появится перечень всех предприятий, которые зарегистрированы в реестре;

ОК». База данных примет вид, как на рис. 7 со с. 18. Все верно. На экране остались только записи, относящиеся к фирме «ООО «Колорит»».

Для включения или отключения автофильтра используйте комбинацию «Ctrl+Shift+L».

Обратите внимание, что в результирующую выборку автофильтр включил записи с контрагентами «ООО «Колорит»» и «Ооо «Колорит»». Вывод: при работе автофильтра регистр символов роли не играет.

Если при работе с автофильтром значки выбора мешают читать текст заголовков (перекрывают их), примените к заголовкам выравнивание влево.

Обратиться к автофильтру можно и по-другому. Например, выбрать пункт «Данные» главного меню, найти группу иконок «Сортировка и фильтр» и воспользоваться значком «Фильтр».

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

1) щелкаем на значке выбора в колонке «НаимПредпр». Раскроется меню, изображенное на рис. 4;

2) в этом меню выбираем пункт «Снять фильтр с «НаимПредпр»». Реестр снова виден в полном объеме.

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

2) в верхнее поле с типом операции указать «содержит»;

4) во втором поле с типом операции тоже указать «содержит»;

5) в качестве значения для этого условия ввести «фирма»;

6) переключатель логической связи между операциями поставить в положение «ИЛИ»;

7) нажать на «ОК». На экране останутся данные по контрагентам «ЧП «Матадор»», «Фирма «КХК»» и «Фирма «Эталон»».

Возможность использовать логические операции «И»/«ИЛИ» в сочетании со стандартным набором условий автофильтра позволяет построить достаточно сложные правила отбора записей из базы данных.

Работа с окном «Пользовательский автофильтр» (рис. 6) — не единственный способ выбрать записи из длинного списка. Вернемся к окну на рис. 4. В нижней части этого окна находится список всех предприятий, которые присутствуют в реестре. Состав этого списка Excel взял из колонки, по которой мы делали фильтрацию данных (напомню, что это был столбец «НаимПредпр»).

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

1) щелчком мыши убираем все галочки, оставляем ее только возле названия «ООО «Колорит»». На экране останутся записи, относящиеся к фирме «ООО «Колорит»». То есть в данном случае Excel применил к базе данных условие фильтрации по признаку равенства. Он выбрал записи, у которых содержимое ячеек в колонке «НаимПредпр» совпадает с указанным значением;

2) снова вызываем меню автофильтра. Ставим галочку возле строки «Фирма «Эталон»». Теперь на экране видны записи о предприятиях «ООО «Колорит»» и «Фирма «Эталон»»;

Нескольких условий в меню автофильтра взаимодействуют между собой по логике «ИЛИ».

3) щелкаем на значке выбора в колонке «НаимПредпр», возвращаемся в меню работы с автофильтром;

4) ставим галочку возле пункта «(Выделить все)». В базе данных стали видны все записи.

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

Работа автофильтра в Excel 2007 зависит от типа данных, к которым он применяется. Вернемся к базе данных на рис. 1 и отфильтруем ее по колонке «

ОбщСум». Допустим, мы хотим выбрать все поступления, сумма которых находится в диапазоне от 1000 до 5000 грн. Делаем так.

Редактирование» главного меню щелкаем на иконке «Сортировка и фильтр» (рис. 2). Откроется меню, как на рис. 3;

ОбщСум». Раскроется меню, изображенное на рис. 8. Нас интересует пункт «Числовые фильтры»;

5) ставим указатель мыши на этот пункт. Появится меню, как на рис. 9;

Между». Появится окно настройки фильтра, изображенное на рис. 10;

больше или равно» указываем «1000». В поле «меньше или равно» вводим значение «5000»;

ОК». Фрагмент отфильтрованной базы данных показан на рис. 11. В этом фрагменте все значения поля «ОбщСум» находятся в указанном диапазоне.

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

между», «Первые 10…», «Выше среднего» и «Ниже среднего». Фильтром по условию «между» мы только что воспользовались. Пункт «Первые 10…» позволяет оставить на экране десять наибольших или десять наименьших элементов базы данных.

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

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

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

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

. И что не менее важно, формировать динамические условия с учетом текущей даты. Делаем так:

2) ставим указатель активной ячейки внутрь области данных;

Фильтр по дате». Раскроется окно, как на рис. 12. В нем представлены основные возможности по фильтрации дат.

Меню состоит из 22 элементов. Все они разбиты на группы. Пункт «

равно…» позволяет выбрать записи по точному соответствию указанной дате.

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

Ниже в меню расположены похожие условия, но для работы с неделями, а именно: «

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

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

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

Пример 1. В базе реестра (рис. 1) я хочу найти самое крупное поступление (т. е. запись с максимальным значением в поле «

Числовые фильтры», затем «Первые 10…». Появится окно, как на рис. 13;

наибольших». Количество чисел устанавливаем равным «1». В списке справа оставляем значение «элементов списка».

ОК». Excel оставит запись о поступлении от «ЧП «Матадор»» за «22.11.2009» на сумму 30769,2 грн. Это максимальное поступление.

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

Пример 2. Проанализировать базу данных и найти в ней все записи с незаполненным полем «

ОК». На экране остались только те записи, у которых не заполнено поле «Дата».

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

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

Пример 3. Я хочу найти в реестре предприятие, название которого похоже на «

Матадор». Точного значения я указать не могу, но тот факт, что в названии есть «Мата», помню наверняка. Делаем так:

ОК». На экране остались только записи о фирме «ЧП «Матадор»».

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Кликнув по значку Фильтр , верхние ячейки диапазона будут определены как заголовки и в фильтрации участия принимать не будут. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Для работы с настройками автофильтра, прежде всего, нужно включить фильтр. Сделать это можно двумя способами. Кликните по любой ячейке таблицы, к которой вы хотите применить фильтр. Затем, находясь во вкладке «Главная», нажмите на кнопку «Сортировка и фильтр», которая размещена в блоке инструментов «Редактирование» на ленте. В открывшемся меню, выберите пункт «Фильтр».

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: