Функция если в excel автофильтры

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

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

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

Для включения необходимо щелкнуть в любом месте таблицы с данными, перейти на вкладку «Данные» — «Сортировка и фильтр». Нажать кнопку «Фильтр». Или нажмите комбинацию горячих клавиш CTRL+SHIFT+L.

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

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

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

Сделаем фильтрацию числовых значений:

1. Нажимаем кнопку автофильтра у заголовка столбца с числовыми значениями. Выбираем «Числовые фильтры» — раскрывается список условий.



2. Выберем, например, «больше или равно». Откроется пользовательский автофильтр в Excel.



3. Зададим в качестве критерия для фильтрации значение «3000». Введем эти цифры в поле напротив условия.



4. На листе останутся только те данные, которые соответствуют заданному критерию.

Чтобы снять фильтр, нажимаем на значок в заголовке столбца и выбираем «Снять фильтр…».

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

1. Нажимаем кнопку автофильтра у заголовка «Цена». В списке «Числовых фильтров» выбираем оператор «Первые 10».



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



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



4. Чтобы закрыть окно и выполнить условие поиска, жмем ОК.

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

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

Назначим фильтр для столбца «Дата»:

1. Нажимаем кнопку автофильтра. Открываем выпадающий список «Фильтры по дате».



2. Чтобы отобразить данные за второе полугодие 2014 г., назначим критерий «После». В открывшемся окне «Пользовательского автофильтра» для критерия «После» напишем условие «01.06.2014». Выберем функцию «И». Назначим второй критерий – «До». Условие – «31.12.2014». Дату можно вводить вручную, а можно выбрать в «Календаре».



3. После нажатия кнопки ОК пользователю становится доступна информация за второе полугодие 2014 года.

Отфильтруем текстовый диапазон ячеек:

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



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



3. После нажатия ОК.

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

• «?» — заменяет один любой знак. Условие для фильтрации данных в столбце «Наименование» — «Содержит «1?»:



• «*» — заменяет несколько знаков.

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



Пустые ячейки и промежуточные итоги

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

Отберем в диапазоне с пустыми ячейками ячейки со значениями:

1. Выделяем диапазон с данными и щелкаем по кнопке «Фильтр» (чтобы у заголовков появились «стрелочки»).
2. Нажимаем кнопку у заголовка первого столбца. В меню фильтрации выбираем «Выделить все». Убираем выделение напротив значения «Пустые».



3. Чтобы выполнить команду, жмем ОК.

Все пустые строки будут удалены.

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

Сменился диапазон – сумма стала иной.

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

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

​Смотрите также​ в основной таблице.​или​ Resume Next ActiveSheet.ShowAllData​ соответствующие даже одному​Автор: Антон Андронов​ со стрелкой. Если​ Таким образом вы​Фильтрация столбца или выделенного​ отфильтровать.​Последние 10​Действие​в столбце с​ которое нужно отфильтровать.​ цена ниже средней.​ не на всю​содержится слово Гвозди.​Главный недостаток стандартного фильтра​

​ Иначе программа их​Я​

• ​ Range(«A7»).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(«A1»).CurrentRegion​ из установленных критериев.​Среди многообразных функций программы​
• ​ Вы уже применяли​ можете сосредоточиться только​
• ​ фрагмента таблицы при​

​В разделе​​.​​Удаление определенных условий фильтрации​ содержимым, которое нужно​В разделе​ То есть у​ таблицу, а лишь​ Этому условию отбора​ (Данные/ Сортировка и​ просто не найдёт.​<>*о*​ End If End​ В данную таблицу​

Задача 1 (начинается…)

​ Microsoft Excel особенно​ фильтры в таблице,​ на том, что​​ истинности обоих условий​​Фильтр​Лучшие числа по проценту​Щелкните стрелку, _з0з_ в​ отфильтровать.​Фильтр​ нас 3 критерия:​ до первой пустой​

​ удовлетворяют строки только​ фильтр/ Фильтр) –​Заполните дополнительную таблицу значениями​​все слова, не содержащие​​ Sub​ попадут все строки,​ следует выделить функцию​ можете пропустить этот​ вы хотите видеть.​И​​во всплывающем меню​​Первые 10​

​ столбце, который содержит​​В разделе​щелкните​ первый критерий задает​ строки).​ с товарами гвозди и​ это отсутствие визуальной​ критериев так как​

​ букву​

​Эта процедура будет автоматически​ значение суммы в​ автофильтр. Она помогает​ шаг.​ В отличие от​.​По цвету​, щелкните​ фильтр, и выберите​Фильтр​Выберите один из вариантов​ Товар, 2-й -​Предыдущие задачи можно было​ Гвозди (Регистр не​

​ информации о примененном​
​ показано ниже на​О​ запускаться при изменении​ которых больше 10000.​ отсеять ненужные данные,​Нажмите на кнопку со​ сортировки данных, данные​Фильтрация столбца или выделенного​

​щелкните​элементы​ команду​

• ​щелкните​и введите условия​ его Тип, а​ при желании решить обычным​
• ​ учитывается). Значения гвозди​ в данный момент​ рисунке:​
• ​<>*вич​ любой ячейки на​На примере мы выяснили,​ и оставить только​ стрелкой в столбце,​​ переупорядочиваются в определенном​​ фрагмента таблицы при​
• ​Цвет ячейки​, а затем во​Очистить фильтр​Выберите один из вариантов​​ фильтра.​​ 3-й критерий (в​

​ автофильтром. Эту же​ 20 мм, Гвозди​ фильтре: необходимо каждый​Перейдите на любую ячейку​все слова, кроме заканчивающихся​ текущем листе. Если​ что автофильтр является​ те, в которых​

​ который необходимо отфильтровать.​ порядке. Дополнительные сведения​ истинности одного из​,​ всплывающем меню выберите​.​и во всплывающем​Примечания:​ виде формулы) задает​ задачу обычным фильтром​ 10 мм, Гвозди​

​ раз лезть в​ в главной таблице​

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

Задача 2 (точно совпадает)

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

​процент​ к диапазону или​ из указанных ниже​​Фильтры можно применить только​​Критерии разместим в строках​Произведем отбор только тех​ не будут.​ вспомнить критерии отбора​ выберите инструмент: «ДАННЕ»-«Сортировка​(например, фильтр женщин​​ диапазон (A2:I5), то​​ информации. С помощью​

​ разберемся в особенностях​ C.​ списка данных.​

• ​Или​Значок ячейки​.​ таблице​
• ​ действий.​ к одному диапазону​ 6 и 7.​
• ​ строк таблицы, которые​Табличку с условием отбора​ записей. Особенно это​ и фильтр»-«Дополнительно». Появиться​ по отчеству)​​ данный макрос снимает​​ настраиваемого пользовательского автофильтра,​
• ​ работы и настройки​Появится меню фильтра. Наведите​При фильтрации обратите внимание​.​​и выберите цвет.​​Нумерация снизу по проценту​
• ​Выделите столбцы диапазона или​

​Цель фильтрации диапазона​ ячеек на листе​ Введем нужные Товар​точно​ разместим разместим в​ неудобно, когда применено​ диалоговое окно: «Расширенный​=​

​ все фильтры (если​ фильтрацию можно производить​ автофильтра в программе​ указатель мыши на​ на следующие рекомендации:​При создании условий можно​​Это возможно только в​​Снизу 10​ таблицы, к которым​Операция​ за раз.​​ и Тип товара.​​содержат в столбце​ диапазоне​ несколько критериев. Расширенный​ фильтр» с автоматически​все пустые ячейки​ они были) и​ по гораздо большему​ Microsoft Excel.​ пункт​

Задача 3 (условие ИЛИ для одного столбца)

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

​ Для заданного Тип​ Товар продукцию Гвозди, ИЛИ​B1:В2​ фильтр лишен этого​ заполненным первым полем​<>​ заново применяет расширенный​ количеству параметров, чем​Скачать последнюю версию​Текстовые фильтры​​ только первые 10 000​​Щелкните ячейку в диапазоне​

​ столбец содержит пустую​элементы​ затем на вкладке​Содержит​

​ столбцу, в других​ товара вычислим среднее и​ которые в столбце Количество содержат значение​. Табличка должна содержать​​ недостатка – все​​ «Исходный диапазон».​

Задача 4 (условие И)

​все непустые ячейки​ фильтр к таблице​​ в стандартном режиме.​​ Excel​, затем выберите необходимый​ уникальных записей списка.​ или таблице, которую​ ячейку.​, а затем во​Данные​или​ столбцах в качестве​ выведем ее для​ >40. Критерии отбора​ также название заголовка​ критерии помещаются в​Активируйте второе поле ввода​>=5000​ исходных данных, начинающейся​Автор: Максим Тютюшев​​Для работы с настройками​​ текстовый фильтр в​

​Можно отфильтровать по нескольким​ хотите отфильтровать.​Щелкните ячейку в диапазоне​ всплывающем меню выберите​щелкните​Равно​

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

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

Задача 5 (условие ИЛИ для разных столбцов)

​ Excel при слове​ нужно включить фильтр.​ данном случае мы​ фильтра к столбцу​Стандартная​

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

​ диапазоне.​ ввести прямо в​ и Количество) и​ в ячейке​ прост:​ выделите весь диапазон​

Задача 6 (Условия отбора, созданные в результате применения формулы)

​5 или =5​ ввода очередного условия:​ голове всплывает только​ двумя способами. Кликните​

​не содержит​ доступны только значения,​

• ​Фильтр​Стандартная​Примечания:​
• ​ стрелок фильтра в​Не содержит​

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

​ дополнительно таблицы с​все ячейки со значением​Так все гораздо лучше,​ обычный классический фильтр​ по любой ячейке​, чтобы увидеть данные,​​ видимые в текущем​​_з0з_.​нажмите кнопку​​ ​​ диапазоне или таблице​или​ только первые 10 000​ С7.​ разных строках. Условия​укажем формулу =»=Гвозди».​​ будет применяться фильтр​​ критериями. Выделяя данный​

​ 5​ правда? :)​ с вкладки​ таблицы, к которой​ которые не содержат​ отфильтрованном диапазоне.​Щелкните стрелку _з0з_ в​Фильтр​Фильтры можно применить только​Выделите столбцы диапазона или​Не равно​​ уникальных записей списка.​​Далее действуем как обычно:​ отбора должны быть​Теперь все подготовлено для​ (исходная таблица);​ диапазон следует охватить​>=3/18/2013​Теперь, когда все фильтруется​Данные — Фильтр (Data​ вы хотите применить​ заданное слово.​Фильтры можно применить только​ столбце с содержимым,​_з0з_.​ к одному диапазону​ таблицы, к которым​.​

​Щелкните ячейку в диапазоне​ выделяем любую ячейку​

• ​ записаны в специальном​ работы с Расширенным​Создаем табличку с критериями​ как ячейки критериев,​все ячейки с датой​ «на лету», можно​ — Filter)​ фильтр. Затем, находясь​​В появившемся диалоговом окне​​ к одному диапазону​ которое нужно отфильтровать.​​Щелкните стрелку _з0з_ в​​ ячеек на листе​ применяются фильтры, а​В поле рядом с​ или таблице, которую​ таблицы, вызываем Расширенный​ формате: =»>40″ и​ фильтром:​​ (с условиями отбора);​​ так и заголовки​ позже 18 марта​ немного углубиться в​:​
• ​ во вкладке «Главная»,​Пользовательский автофильтр​ ячеек на листе​В разделе​ столбце с содержимым,​ за раз.​ затем на вкладке​
• ​ всплывающим меню введите​ хотите отфильтровать.​ фильтр (Advanced Filter)​

​ =»=Гвозди». Табличку с​
​выделите любую ячейку таблицы​Запускаем Расширенный фильтр.​ столбцов. В поле​ 2013 (включительно)​ нюансы и разобрать​

Задача 7 (Условия отбора содержат формулы и обычные критерии)

​Такой фильтр — штука​ нажмите на кнопку​введите необходимый текст​ за раз.​

​Фильтр​ которое нужно отфильтровать.​Когда фильтр применяется к​Данные​ текст, которое хотите​

​На вкладке​ и указываем диапазон​ условием отбора разместим​ (это не обязательно,​Пусть в диапазоне​ ввода автоматически сгенерируется​Тонкие моменты:​ механизмы более сложных​ привычная, спору нет,​ «Сортировка и фильтр»,​ в поле справа​Примечание:​щелкните​

​В области​ столбцу, в других​щелкните​ использовать.​Данные​ с критериями.​ разместим в диапазоне​ но позволит ускорить​A7:С83​ ссылка на выделенный​Знак * подразумевает под​ запросов в расширенном​ и для большинства​

​ которая размещена в​ от фильтра, затем​ При использовании​Выберите один из вариантов​(Выделить все)​ столбцах в качестве​

​Фильтр​В зависимости от сделанного​щелкните​

​Будут выведены 2 товара​E4:F6​ заполнение параметров фильтра);​имеется исходная таблица с​ диапазон. После чего​ собой любое количество​ фильтре. Помимо ввода​ случаев вполне сойдет.​ блоке инструментов «Редактирование»​ нажмите​

Задача 7.1. (Совпадают ли 2 значения в одной строке?)

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

​.​ выбора вам может​Фильтр​ из 4-х (заданного​.​вызовите Расширенный фильтр (Данные/​ перечнем товаров, содержащая​

Задача 8 (Является ли символ числом?)

​ нажмите на кнопку​ любых символов, а​ точных совпадений, в​

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

​ типа товара).​После нажатия кнопки ОК​ Сортировка и фильтр/​ поля (столбцы) Товар,​ ОК подтвердив и​ ? — один​ диапазоне условий можно​ когда нужно проводить​

​ открывшемся меню, выберите​. В данном примере​ данных выполняется поиск​ свои условия, используя​(Пустые)​ в текущем отфильтрованном​ только те данные,​ дополнительные условия.​Щелкните стрелку _з0з_ в​В файле примера для​ будут выведены записи​ Дополнительно);​ Количество и Цена  (см. файл​ закрыв диалоговое окно​ любой символ.​

Задача 9 (Вывести строки, в которых НЕ СОДЕРЖАТСЯ заданные Товары)

​ использовать различные символы​ отбор по большому​ пункт «Фильтр».​ мы введем слово​ только отображаемые данные;​

​ подстановочные знаки.​.​

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

Вывод уникальных строк

​ «case», чтобы исключить​ данные, которые не​Например, чтобы в результате​Примечания:​

excel2.ru

Фильтрация списка данных

​В окне фильтра отображаются​​ условиям. Данные, которые​Операция​ которое нужно отфильтровать.​ форматирование: выделяются строки​ Товар продукцию Гвозди ИЛИ​ убедитесь, что указан​ должна содержать пустых​В результате скрылись все​ и числовых запросов​ ?) и знаки​ сразу по нескольким​ способом, перейдите во​ все позиции, содержащие​ отображаются, не просматриваются.​ фильтрации найти оба​ ​ только первые 10 000​ не соответствуют этому​Фильтрация столбца или выделенного​В разделе​

​ удовлетворяющие первым 2-м​ значение  >40 (у​ диапазон ячеек таблицы​ строк и столбцов,​ ненужные строки исходной​ немного разная. Так,​ математических неравенств для​

​ столбцам. Обычный фильтр​ вкладку «Данные». Затем,​ это слово.​

Фильтрация по набору верхних или нижних значений

1. ​ Чтобы найти все​ слова «год» и​Фильтры можно применить только​

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

   

3. ​ любого товара).​ вместе с заголовками​ иначе Расширенный фильтр​

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

   

​ данные, снимите флажок​​ «гид», введите​

• ​ к одному диапазону​Вместо фильтрации можно использовать​ фильтрации данных вы​ истинности обоих условий​

• ​щелкните​ статью Выделение строк​Настоящая мощь Расширенного фильтра​ (​ (да и обычный​ видно только те​ с числом 5​

• ​ Регистр символов роли​ удобен и хочется​ первом случае, нужно​

Фильтрация по конкретному числу или диапазону чисел

1. ​ заданному текстовому фильтру.​ все фильтры.​г?д​

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

   

3. ​ таблицы в MS​ проявляется при использовании​A7:С83​

4. ​ Автофильтр) не будет​​ строки, которые соответствуют​​ не означает поиск​​ не играет. Для​​ чего-то помощнее. Таким​ кликнуть по одной​

   

5. ​ В нашем случае​Использование срезов для фильтрации​.​ за раз.​

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

   

​ EXCEL в зависимости​​ в качестве условий​

• ​);​ правильно работать.​ критериям для расширенного​ всех чисел, начинающихся​

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

• ​ числа выделялись в​ и печатать подмножество​Фильтрация столбца или выделенного​

• ​ фильтра.​ от условия в​ отбора формул.​в поле Диапазон условий​Настроим фильтр для отбора строк,​ фильтра. В данном​

Фильтрация по цвету шрифта, цвету ячеек или наборам значков

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

1. ​ фрагмента таблицы при​В поле рядом с​ ячейке).​Существует две возможности задания​ укажите ячейки содержащие​ которые содержат в​ случаи — это​

2. ​ ячейка условия с​​ в таблицу:​​, особенно с небольшой​​ нужно нажать на​​Другие​

   

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

4. ​Таблица с примененным фильтром​​ истинности одного из​​ всплывающим меню введите​​Есть таблица, в которой​​ условий отбора строк:​​ табличку с критерием,​​ наименовании Товара значения​​ информация по двум​​ буквой Б равносильна​​Критерий​​ «доработкой напильником» (по​

Фильтрация пустых ячеек

​ кнопку «Фильтр», размещенную​, которые не содержат​ с помощью условного​Результат поиска​

1. ​ фильтров можно использовать​ см. в статье​ «верхние 4 элементов»​

2. ​ двух или обоих​​ число, которое хотите​​ указаны Год выпуска​​непосредственно вводить значения для​​ т.е. диапазон​

   

3. ​начинающиеся​ видам товаров –​ Б*, т.е. будет​

4. ​Результат​​ традиции).​​ в блоке инструментов​ слово «case».​​ форматирования​​Вопросительный знак (?)​

   ​ только значения, видимые​​ выделение точек данных​

   • ​Фильтры являются аддитивными. Это​ условий​ использовать.​ и Год покупки​

   • ​ критерия (см. задачи​B1:B2​со слова Гвозди. Этому​ «Виноград» и «Крыжовник»,​ искать любой текст,​гр* или гр​Для начала вставьте над​

   • ​ «Сортировка и фильтр»​Расширенные фильтры по дате​Использование гистограмм, цветовых шкал​

Фильтрация для поиска определенного текста

1. ​Любой символ​ в текущем отфильтрованном​ с помощью условного​

2. ​ означает, что каждый​​Или​​В зависимости от сделанного​​ автомобиля.​​ выше);​

   

3. ​.​​ условию отбора удовлетворяют​ которые продавались в​ начинающийся с буквы​

4. ​все ячейки начинающиеся с​​ вашей таблицей с​​ на ленте.​​ позволяют выделить информацию​​ и наборов значков​Пример: условию «стро?а» соответствуют​ диапазоне.​ форматирования.​

   ​ дополнительный фильтр будет​	​.​
   ​ выбора вам может​	​Требуется вывести только те​​сформировать критерий на основе​​Нажмите ОК​​ строки с товарами​
   ​ магазине № 3​ Б.​	​Гр​​ данными несколько пустых​​При использовании любого из​​ за определенный промежуток​

5. ​ для выделения данных​ результаты «строфа» и​В окне фильтра отображаются​Щелкните ячейку в диапазоне​

6. ​ привязан к текущему​Щелкните ячейку в диапазоне​ быть предложено выбрать​ строки, в которых Год​

   

   ​ результатов выполнения формулы.​	​Применять Расширенный фильтр с​
   ​ гвозди 20 мм,​ в период с​Если текстовый запрос не​	​, т.е.​​ строк и скопируйте​
   ​ данных способов, функция​ времени, к примеру,​В прошлом уроке мы​ «строка»​ только первые 10 000​	​ или таблице, которую​​ фильтру и дополнительно​

Фильтрация по началу или окончанию строки текста

1. ​ или таблице, которую​ дополнительные условия.​ выпуска совпадает с​

2. ​Рассмотрим критерии задаваемые формулой.​​ такими простыми критериями​​ Гвозди 10 мм,​​ 20.07.2017 по 23.07.2017.​​ начинается со знака​

   

3. ​Гр​​ туда шапку таблицы​ фильтрации будет включена.​ за прошлый год,​

4. ​ познакомились и научились​​Звездочка (*)​​ уникальных записей списка.​​ хотите отфильтровать.​​ сокращает подмножество данных.​ хотите отфильтровать.​Примечания:​ Годом покупки. Это​

   ​ Формула, указанная в​	​ особого смысла нет,​
   ​ Гвозди 10 мм​	​​​ =, то в​
   ​уша​	​ — это будет​​ Об этом будет​
   ​ за этот месяц​ применять стандартную фильтрацию​Любое количество символов​	​Щелкните ячейку в диапазоне​​На панели инструментов​
   ​ Для сложных фильтров​На панели инструментов​ ​	​ можно сделать с​​ качестве критерия отбора,​

5. ​ т.к. с этими​ и Гвозди.​Примечание. Не обязательно в​ конце можно мысленно​

6. ​,​ диапазон с условиями​ свидетельствовать появление значков​ или между двумя​

   

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

Использование подстановочных знаков для фильтрации

​ (выделен для наглядности​ в каждой ячейке​

1. ​ датами. В следующем​ очень часто возникают​ результаты «северо-восток» и​

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

   

3. ​ =В10=С10.​ ИСТИНА или ЛОЖЬ.​ Автофильтр. Рассмотрим более​

4. ​ разместим разместим в​​ название магазина достаточно​​Даты надо вводить в​​ейпфрут​​ желтым):​

5. ​ шапки таблицы, в​ примере мы воспользуемся​ ситуации, когда базовые​

   ​ «юго-восток»​На панели инструментов​Фильтр​ нескольким форматам или​​Фильтр​​ ячеек на листе​

6. ​Пусть у нас есть​Например, отобразим строки, содержащие​

   ​ сложные задачи по​	​ диапазоне​
   ​ указать по маске​	​ штатовском формате месяц-день-год​ ​,​Между желтыми ячейками и​ виде квадратов с​
   ​ расширенным фильтром по​	​ инструменты фильтрации бессильны​ ​Тильда (~)​Стандартная​_з0з_.​
   ​ нескольким условиям. Например,​	​.​ ​ за раз.​ таблица с перечнем​

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

​ Товар, который встречается​ фильтрации.​

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

​Требуется отфильтровать только те​ 1 раз. Для​ указать не =»=Гвозди»,​ также название заголовка​ 3 – это​ вас русский Excel​и т.д.​ бы одна пустая​ вниз.​ отдано на проверку​ выборки. В этом​

​ результат «здесь?»​_з0з_.​

​ содержимым, которое нужно​ которые больше 5,​ содержимым, которое нужно​ столбцах в качестве​ строки, у которых​ этого введем в​ а просто Гвозди,​ столбца, по которому​ значит любое название​ и региональные настройки).​=лук​ строка.​Для того, чтобы использовать​ сегодня.​ уроке Вы узнаете,​Выполните одно из указанных​Щелкните стрелку​ отфильтровать.​ и ниже среднего.​ отфильтровать.​ фильтров можно использовать​ в столбце Товар​ ячейку​ то, будут выведены​ будет производиться отбор.​ магазина, которое заканчивается​Условия записанные в разных​все ячейки именно и​

​Именно в желтые ячейки​ фильтр, достаточно кликнуть​Откройте вкладку​ как решить эту​ ниже действий.​в столбце с​В разделе​ Но некоторые фильтры​В разделе​ только значения, видимые​ содержится Гвозди 1​H2​ все записи содержащие​ В качестве критерия​ на число 3.​ ячейках, но в​ только со словом​

​ нужно ввести критерии​ по такому значку​Данные​ проблему в Excel​Задача​ содержимым, которое нужно​Фильтр​ (верхние и нижние​Фильтр​ в текущем отфильтрованном​ дюйм, Гвозди 2​формулу =СЧЁТЕСЛИ(Лист1!$A$8:$A$83;A8)=1, а​

​ наименования​ в ячейке​

• ​ Расширенный фильтр понимает​ одной строке -​Лук​

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

• ​ десять, выше и​щелкните​ диапазоне.​ дюйма и т.д.​

​ в​​начинающиеся​​А2​​ значения по маске.​ считаются связанными между​, т.е. точное совпадение​ потом будет произведена​ которого нужно отфильтровать.​Фильтр​ фильтров.​Удаление фильтра, примененного к​

Фильтрация по набору верхних или нижних значений

1. ​В разделе​Выберите один из вариантов​ ниже среднего) зависят​

2. ​Выберите один из вариантов​​В окне фильтра отображаются​​ товары Гвозди нержавеющие,​​Н1​​со слова Гвозди​

3. ​укажем слово Гвозди.​​Читайте начало статьи: Использование​ собой логическим оператором​*лив* или *лив​

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

   ​и во всплывающем​	​ только первые 10 000​
   ​ Гвозди хромированные и​вместо заголовка введем​	​ (Гвозди 80мм, Гвозди2).​​Примечание​
   ​ автофильтра в Excel​	​И (AND)​​ячейки содержащие​
   ​ нужно отобрать бананы​	​ меню, где можно​​ столбца появится кнопка​​ выделить какие-то специфичные​​ диапазоне или таблице​щелкните​ меню выполните одно​​ ячеек. Например, при​​ меню выполните одно​
   ​ уникальных записей списка.​	​ т.д. не должны быть​​ поясняющий текст, например,​​ Чтобы вывести строки​​: Структура критериев у​Обратите внимание! Если нам​:​​лив​​ в московский «Ашан»​

​ снять галочки с​​ со стрелкой. Если​

• ​ данные, то, как​Щелкните стрелку _з0з_ в​Выберите один из вариантов​ из указанных ниже​

• ​ фильтрации десяти первых​ из указанных ниже​Вместо фильтрации можно использовать​ отфильтрованы.​ Неповторяющиеся значения. Применим​ с товаром,​ Расширенного фильтра четко​

• ​ нужно изменить критерии​Т.е. фильтруй мне бананы​как подстроку, т.е.​

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

Фильтрация по конкретному числу или диапазону чисел

1. ​Проще всего это сделать​ Расширенный фильтр, указав​содержащие​

2. ​ определена и она​​ фильтрования для основной​​ именно в третьем​​О​​ то условия будут​

3. ​ нам нужно спрятать.​​ фильтры в таблице,​ фильтрации с такой​ которое нужно отфильтровать,​

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

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

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

6. ​ Москве и при​ки​Чтобы выполнить фильтрацию выделите​ сделано, жмем на​

   

   ​ шаг.​	​ справляются. К счастью,​
   ​Очистить фильтр​ действий.​Числа, которые больше или​	​ не первые десять​​Начало строки текста​
   ​ числовые значения среди​ что после слова​Н1:Н2​ необходимо в качестве​ БДСУММ(), БСЧЁТ() и​	​ раз указывать диапазон​​ этом из «Ашана».​

​,​​ любую ячейку диапазона​

• ​ кнопку «OK».​Нажмите на кнопку со​ Excel содержит множество​.​

• ​Цель фильтрации диапазона​ меньше определенного числа​ значений подмножества последнего​Начинается с​ данных.​ Гвозди должно идти​.​

• ​ критерия указать =»=*Гвозди»​ др.​ критериев. Достаточно просто​

• ​Если нужно связать условия​Лив​ с исходными данными,​Как видим, в таблице​ стрелкой в столбце,​ расширенных фильтров, включая​Удаление всех фильтров, примененных​Операция​Больше​ фильтра.​

Фильтрация по цвету шрифта, цвету ячеек или наборам значков

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

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

2. ​Окончание строки текста​​ на основании визуальных​​ сделать с помощью​​ что диапазон поиска​​ где * является​

3. ​Обычно критерии Расширенного фильтра​​ дополнительной таблице и​ИЛИ (OR)​,​

4. ​Данные​​ со значениями, с​​ В этом примере​​ и фильтрацию по​​В меню​​Содержит​​меньше​​ три типа фильтров:​​Заканчивается на​​ условий, таких как​​ формулы =ЕЧИСЛО(—ПСТР(A11;ДЛСТР($A$8)+2;1))​

Фильтрация пустых ячеек

​ значений введен с​ подстановочным знаком и​ размещают над таблицей,​ нажать «ДАННЫЕ»-«Дополнительно»-«ОК».​

1. ​, то их надо​За​и нажмите кнопку​

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

3. ​.​ по значениям, по​.​

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

   ​ просто вводить в​​лив​

   • ​Дополнительно (Data — Advanced)​ галочки.​ D, чтобы увидеть​ позволяет сузить результаты​

   • ​выберите команду​Равно​Числа, которые равны или​ формату или по​Ячейки, которые содержат текст,​ ячейки или наборы​ товара 1 символ​

   • ​ а критерий в​ символов.​ фильтр, но можно​

Фильтрация для поиска определенного текста

1. ​: Всех приветствую!​ разные строки. Например,​и т.д.​

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

3. ​.​​ не равны конкретному​ критерию. Однако каждый​ но не начинаются​

4. ​ значков. Можно также​​ после слова Гвозди​​ функции СЧЁТЕСЛИ() –​​Настроим фильтр для отбора строк,​​ их разместить и сбоку​Была первостепенная задача:​ если нам нужно​=п*в​

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

5. ​Приветствую всех и прошу​ найти все заказы​слова начинающиеся с​ автоматически введен диапазон​

6. ​ в том же​ указатель мыши на​ Вам нужно.​Удаление всех фильтров, примененных​

   

   ​ текст​	​Равно​
   ​ фильтров является взаимно​Не начинаются с​ наличию в них​	​ Если этот символ​​ Это необходимо, поскольку​
   ​ столбце Товар содержится​ таблички с критериями​ разобраться в следующем:​ менеджера Волиной по​П​	​ с данными и​​ меню, переходим по​

Фильтрация по началу или окончанию строки текста

1. ​ пункт​Excel позволяет искать информацию,​ к таблице​

2. ​Не​​или​​ исключающим. Например, можно​​.​​ формата, примененных стилей​

3. ​ число (цифра), то​​ при применении Расширенного​ значение начинающееся со​ под исходной таблицей,​

4. ​1. Имеется таблица​​ московским персикам и​​и заканчивающиеся на​​ нам останется только​​ пункту «Текстовые фильтры»​Фильтры по дате​ которая содержит точную​Выберите столбцы таблицы, к​

   ​содержит​ ​не равно​	​ выполнить фильтрацию по​
   ​Ячейки, которые содержат текст,​	​ или условного форматирования.​​ формула возвращает ИСТИНА​
   ​ фильтра EXCEL увидит,​	​ слова Гвозди​​ хотя это не​
   ​ с большим объемом​ все заказы по​В​	​ указать диапазон условий,​​ «Числовые фильтры», или​
   ​, затем выберите необходимый​ фразу, число, дату​ которой применены фильтры,​	​или не​​.​

5. ​ цвету ячейки или​ но не оканчиваются​В диапазоне ячеек или​ и строка выводится,​

6. ​ что​ИЛИ​ запрещено, но не​ данных (см. пример).​

   

   ​ луку в третьем​	​т.е.​
   ​ т.е. A1:I2:​ «Фильтры по дате»​ фильтр в раскрывающемся​	​ и многое другое.​​ а затем в​
   ​равно​В поле рядом с​ по списку чисел,​ буквами​ столбце таблицы щелкните​	​ в противном случае​​А8​

Использование подстановочных знаков для фильтрации

​Обои.​ всегда удобно, т.к.​

1. ​ Столбцы D,E и​ квартале по Самаре,​П​

2. ​Обратите внимание, что диапазон​​ (в зависимости от​​ меню. В нашем​​ В следующем примере​​ меню​

3. ​.​ всплывающим меню введите​ но не к​

4. ​Не заканчиваются​​ ячейку с определенным​​ строка не выводится.​​— это относительная​​Критерии отбора в этом​

5. ​ в исходную таблицу​ F будут скрытыми​ то это можно​

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

6. ​В поле рядом с​ число, которое хотите​

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

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

• ​ использовать.​ отфильтровать по значку​

  ​В поле рядом с​	​ или значком, по​
  ​ показано как работает​ перемещаться вниз по​ под соответствующим заголовком​	​ строки.​ но по значениям​ условий следующим образом:​,​​ нельзя выделять лишние​​ надписи «Настраиваемый фильтр…».​
  ​Сегодня​ в журнале эксплуатации​	​Очистить фильтры​​ текст, которое хотите​​В зависимости от сделанного​​ или настраиваемому фильтру,​​ всплывающим меню введите​
  ​ которому вы хотите​ формула, т.е. ее​	​ столбцу Товар по​ столбца (Товар) и​ВНИМАНИЕ!​ ячеек, находящихся в​​Если же нужно наложить​​П​​ пустые желтые строки,​​После этого, открывается пользовательский​
  ​, чтобы увидеть оборудование,​ оборудования только продукцию​.​	​ использовать.​​ выбора вам может​​ но не к​​ текст, которое хотите​​ выполнить фильтрацию.​

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

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

​Удаление или повторное применение​В зависимости от сделанного​

​ быть предложено выбрать​ обоим.​ использовать.​На вкладке​ запуска Расширенного фильтра.​ раз и возвращать​ под другом в​ со значениями условий​ будет производиться фильтр.​ условий на один​в​ в диапазоне условий​Как видим, в пользовательском​ сегодня.​Saris​ стрелок фильтра в​ выбора вам может​ дополнительные условия.​Фильтры скрывают излишние данные.​В зависимости от сделанного​Данные​Требуется отфильтровать только те​ значение либо ИСТИНА,​ одном столбце (см.​ отбора и исходной​Необходимо отображать только​ столбец, то можно​и т.д.​

​ воспринимается Excel как​ автофильтре можно отфильтровать​Данные будут отфильтрованы по​.​ диапазоне или таблице​ быть предложено выбрать​Задача​ Таким образом вы​ выбора вам может​щелкните​ строки, у которых​ либо ЛОЖЬ. Если​ рисунок ниже). Табличку​ таблицей имеется, по​ строки, удовлетворяющие критерию,​ просто продублировать заголовок​а*с​

​ отсутствие критерия, а​ данные в столбце​ заданной дате. В​Откройте вкладку​На панели инструментов​ дополнительные условия.​Операция​ можете сосредоточиться только​ быть предложено выбрать​Фильтр​ в столбце Товар​ будет возвращено значение​

​ с критериями размести​ крайней мере, одна​

• ​ заложенному в ячейках​ столбца в диапазоне​слова начинающиеся с​

• ​ целая пустая строка​ сразу по двум​ нашем случае мы​Данные​Стандартная​Задача​Фильтрация столбца или выделенного​

• ​ на том, что​ дополнительные условия.​.​ НЕ содержатся: Гвозди,​

​ ИСТИНА, то соответствующая​​ в диапазоне​​ пустая строка (это​​ C4:C6. Необходимые значения​ критериев и вписать​А​ — как просьба​ значениям. Но, если​ увидим только позиции​, затем нажмите команду​нажмите кнопку​

См. также

​Операция​ фрагмента таблицы при​

​ вы хотите видеть.​

​Задача​Щелкните стрелку​ Доска, Клей, Обои.​

​ строка таблицы будет​С1:С3​ облегчит работу с​

support.office.com

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

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

​.​ Расширенным фильтром).​2. С учетом​ третье и т.д.​С​ без разбора.​ отбор значений в​ отданы на проверку​. В каждом заголовке​_з0з_.​ фрагмента таблицы при​И​ сортировки данных, данные​Фильтрация столбца или выделенного​ содержимым, которое нужно​ простую формулу =ЕНД(ВПР(A15;$A$8:$A$11;1;0))​

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

​ значение ЛОЖЬ, то​Окно с параметрами Расширенного​Теперь все подготовлено для​ фильтра необходимо считать​ условия. Вот так,​, т.е.​Переключатель​ столбце можно производить​ сегодня.​ столбца появится кнопка​​При фильтрации данных отображаются​​ истинности обоих условий​

1. ​.​​ переупорядочиваются в определенном​​ фрагмента таблицы при​​ отфильтровать.​​Функция ВПР() ищет в​ строка после применения​ фильтра и таблица​ работы с Расширенным​ общую сумму по​ например, можно отобрать​А​
2. ​Скопировать результат в другое​ только исключая ненужные​Расширенные числовые фильтры позволяют​ со стрелкой. Если​ только те данные,​И​
3. ​Фильтрация столбца или выделенного​ порядке. Дополнительные сведения​ истинности обоих условий​В разделе​ столбце Товар каждой​ фильтра отображена не​ с отфильтрованными данными​ фильтром:​ столбцу I (ячейка​ все сделки с​пель​
4. ​ место​​ значения, то тут​​ оперировать данными самыми​
5. ​ Вы уже применяли​ которые соответствуют вашим​.​ фрагмента таблицы при​ о сортировке см.​И​Фильтр​​ строки наименования товаров,​​ будет.​

Использование расширенных текстовых фильтров в Excel

​ будет выглядеть так.​выделите любую ячейку таблицы​ I50).​ марта по май:​с​позволит фильтровать список​ можно воспользоваться целым​ различными способами. В​ фильтры в таблице,​​ условиям. Данные, которые​​Фильтрация столбца или выделенного​​ истинности одного из​​ в статье Сортировка​.​во всплывающем меню​ указанных в диапазоне​​Примеры других формул из​​После нажатия ОК будут​

1. ​ (это не обязательно,​​которая была решена​​В общем и целом,​​ин​​ не прямо тут​ арсеналом дополнительных параметров.​ следующем примере, мы​ то можете пропустить​ не соответствуют этому​ фрагмента таблицы при​ двух или обоих​
2. ​ списка данных.​Фильтрация столбца или выделенного​По цвету​А8:А11​ файла примера:​ выведены все записи,​
3. ​ но позволит ускорить​_Boroda_​ после «доработки напильником»​​,​​ же, на этом​ С помощью пользовательского​ выберем только то​ этот шаг.​ условию, скрыты. После​​ истинности одного из​​ условий​При фильтрации обратите внимание​ фрагмента таблицы при​
4. ​щелкните​​. Если эти товары​​Вывод строк с ценами​ содержащие в столбце​ заполнение параметров фильтра);​, за что ему​​ из расширенного фильтра​​А​ листе (как обычным​ автофильтра, можно выбрать​ оборудование, которое входит​Нажмите на кнопку со​
5. ​ фильтрации данных вы​ двух или обоих​Или​ на следующие рекомендации:​ истинности одного из​​Цвет ячейки​​ НЕ найдены, ВПР()​ больше, чем 3-я​

Использование в Excel расширенных фильтров по дате

​ Товар продукцию Гвозди​вызовите Расширенный фильтр (Данные/​ огромное спасибо!​ выходит вполне себе​нана​ фильтром), а выгрузить​ любые два значения​ в заданный промежуток​ стрелкой в столбце,​ можете копировать, находить,​ условий​.​В окне фильтра отображаются​ двух или обоих​

1. ​,​​ возвращает ошибку #Н/Д,​​ по величине цена​​ИЛИ​​ Сортировка и фильтр/​Но появилась необходимость​ приличный инструмент, местами​с​ отобранные строки в​ в столбце в​ идентификационных номеров.​
2. ​ который необходимо отфильтровать.​ редактировать, форматировать, запланировать​Или​Примечания:​ только первые 10 000​ условий​Цвет шрифта​
3. ​ которая обрабатывается функцией​ в таблице. =C8>НАИБОЛЬШИЙ($С$8:$С$83;5) В​Обои.​​ Дополнительно);​​ сделать то же​ не хуже классического​,​ другой диапазон, который​ соответствующих полях, и​​Откройте вкладку​​ В данном примере​ и печатать подмножество​.​
4. ​ ​ уникальных записей списка.​Или​или​ ЕНД() — в​ этом примере четко​Произведем отбор только тех​

Использование расширенных числовых фильтров в Excel

​в поле Исходный диапазон​ самое но по​ автофильтра.​Ас​ тогда нужно будет​ к ним применить​Данные​ мы выберем столбец​

1. ​ отфильтрованных данных.​​Щелкните ячейку в диапазоне​​Фильтры можно применить только​​Можно отфильтровать по нескольким​​.​Значок ячейки​ итоге, формула возвращает​ проявляется коварство функции​ строк таблицы, которые​ убедитесь, что указан​ строкам:​
2. ​Со временем пользователь Excel​аи​ указать в поле​ следующие параметры:​, затем нажмите команду​ C.​Таблица с примененным фильтром​ или таблице, которую​
3. ​ к одному диапазону​ столбцам. При применении​При создании условий можно​​и выберите цвет.​​ ИСТИНА и строка​ НАИБОЛЬШИЙ(). Если отсортировать​точно​ диапазон ячеек таблицы​Среди столбцов​​ сталкивается со сложными​​и т.д.​Поместить результат в диапазон​Равно;​
4. ​Фильтр​​Появится меню фильтра. Введите​​ «верхние 4 элементов»​ хотите отфильтровать.​ ячеек на листе​​ фильтра к столбцу​​ использовать подстановочные знаки.​Это возможно только в​ выводится.​ столбец​содержат в столбце​ вместе с заголовками​
5. ​J​ задачами по фильтрации​=*с​. В данном случае​Не равно;​. В каждом заголовке​

​ ключевое слово в​

office-guru.ru

Функция автофильтр в Microsoft Excel: особенности использования

​Фильтры являются аддитивными. Это​На панели инструментов​ за раз.​ в других столбцах​Щелкните ячейку в диапазоне​ случае, если фильтруемый​О выводе уникальных строк​С​ Товар продукцию Гвозди,​ (​-​ данных в таблице.​слова оканчивающиеся на​

​ мы эту функцию​Больше;​

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

​ столбца появится кнопка​ строке поиска. Результаты​ означает, что каждый​Стандартная​Когда фильтр применяется к​ доступны только значения,​ или таблице, которую​ столбец содержит пустую​ с помощью Расширенного​(цены), то получим:​ а в столбце Количество​A7:С83​U​ Фильтрация данных по​С​ не используем, оставляем​Меньше​

​ со стрелкой. Если​ поиска появятся под​ дополнительный фильтр будет​нажмите кнопку​ столбцу, в других​ видимые в текущем​ хотите отфильтровать.​ ячейку.​ фильтра можно прочитать​ 750; 700;​ значение >40. Критерии​);​, необходимо отображать только​

​ нескольким условиям требует​=????​Фильтровать список на месте​Больше или равно;​ Вы уже применяли​ полем автоматически, после​ привязан к текущему​Фильтр​ столбцах в качестве​ отфильтрованном диапазоне.​На панели инструментов​

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

​Щелкните ячейку в диапазоне​ в этой статье.​700​ отбора в этом​в поле Диапазон условий укажите​ те, которые в​ больших возможностей чем​все ячейки с текстом​и жмем​Меньше или равно;​

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

​_з0з_.​ фильтров можно использовать​Фильтры можно применить только​Данные​ или таблице, которую​

Настройка автофильтра

​Примечание:​; 700; 620, 620,​ случае должны размещаться​ ячейки содержащие табличку​ строке 10 содержат​ те, что предоставляет​ из 4 символов​ОК​Начинается с;​ можете пропустить этот​ В нашем примере​

​ сокращает подмножество данных.​Щелкните стрелку​

​ только значения, видимые​ к одному диапазону​щелкните​ хотите отфильтровать.​ Мы стараемся как можно​ 160, … В​ под соответствующими заголовками​ с критерием, т.е.​ значения, указанные в​ обычный автофильтр. Тогда​ (букв или цифр,​. Отобранные строки отобразятся​Не начинается с;​ шаг.​ мы введем слово​ Для сложных фильтров​в столбце с​ в текущем отфильтрованном​ ячеек на листе​

• ​Фильтр​
• ​На панели инструментов​
• ​ оперативнее обеспечивать вас​
• ​ человеческом понимании «3-ей​
• ​ (Товар и Количество)​
• ​ диапазон​
• ​ ячейке​
• ​ Excel предоставляет более​
• ​ включая пробелы)​
• ​ на листе:​
• ​Заканчивается на;​
• ​Нажмите на кнопку со​

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

​ и должны располагаться​А1:А2​C7​ сложный и функциональный​=м??????н​»Ну и где же​Не заканчивается на;​ стрелкой в столбце,​ все оборудование этой​

​ по нескольким значениям,​ отфильтровать.​В окне фильтра отображаются​Примечание:​Щелкните стрелку _з0з_ в​щелкните​ на вашем языке.​ соответствует 620, а​ на одной строке.​.​.​

​ инструмент – расширенный​все ячейки с текстом​ тут удобство?» -​

​Содержит;​ который необходимо отфильтровать.​ марки.​ нескольким форматам или​В разделе​ только первые 10 000​ При использовании​ столбце с содержимым,​Фильтр​

​ Эта страница переведена​ в понимании функции​ Условия отбора должны​При желании можно отобранные​В общем, то​ фильтр.​ из 8 символов,​ спросите вы и​Не содержит.​ В этом примере​

​Выполнив все шаги, нажмите​

lumpics.ru

Расширенный фильтр и немного магии

​ нескольким условиям. Например,​Фильтр​ уникальных записей списка.​Find​ которое нужно отфильтровать.​.​​ автоматически, поэтому ее​ НАИБОЛЬШИЙ() –​​ быть записаны в​

​ строки скопировать в​ же самое что​На конкретном примере рассмотрим,​ начинающиеся на​ будете правы. Мало​При этом, мы можем​ мы выберем столбец​ОК​ можно выполнить фильтрацию​щелкните​Вместо фильтрации можно использовать​для поиска отфильтрованных​В разделе​Щелкните стрелку _з0з_ в​​ текст может содержать​​700​ специальном формате: =»=Гвозди»​ другую таблицу, установив​

Основа

​ сделал​ как пользоваться расширенным​М​ того, что нужно​ на выбор обязательно​ A, чтобы увидеть​.​ по всем числам,​Выберите один из вариантов​

​ условное форматирование, чтобы​ данных выполняется поиск​Фильтр​ столбце с содержимым,​ неточности и грамматические​

​. В итоге, будет​ и =»>40″. Табличку​ переключатель в позицию​_Boroda_​ фильтром в Excel.​и заканчивающиеся на​ руками вводить условия​ применять сразу два​ заданный ряд идентификационных​Данные на листе будут​

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

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

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

Добавляем макрос

​ или только один​ указатель мыши на​ В нашем примере​ (верхние и нижние​ действий.​ об условном форматировании​ Чтобы найти все​В текстовом поле введите​прокрутите список вниз​ полезна. Просим вас​Вывод строк с учетом​​E1:F2​​ делать не будем.​ вообще? Пример во​ торговых точках:​андари​

​ вводить туда диапазоны,​ из них. Выбор​ пункт​ после фильтрации таблица​ десять, выше и​Чтобы отфильтровать f​ см. в статье​ данные, снимите флажок​ свои условия, используя​ и установите флажок​ уделить пару секунд​ РЕгиСТра =СОВПАД(«гвозди»;А8). Будут​.​​Нажмите кнопку ОК и​​ вложении.​Допустим нам необходимо узнать,​н​

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

​(Пустые)​ и сообщить, помогла​ выведены только те​После нажатия кнопки ОК​ фильтр будет применен​Заранее спасибо!​ как формируется продажа​,​ОК​ воспользовавшись переключателем «и/или».​, затем выберите необходимый​ марки​ от исходного диапазона​Операция​ с помощью условного​Щелкните ячейку в диапазоне​Например, чтобы в результате​

​.​ ли она вам,​

Реализация сложных запросов

​ строки, в которых​ будут выведены все​ — в таблице​_Boroda_​ товаров «Виноград» и​М​. Грустно, согласен! Но​Например, в колонке о​ числовой фильтр в​Saris​ ячеек. Например, при​Начало строки текста​ форматирования.​ или таблице, которую​ фильтрации найти оба​Примечания:​ с помощью кнопок​ товар гвозди введен​ записи содержащие в​

​Н​

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

Логические связки И-ИЛИ

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

​Тип​ формату или по​Ячейки, которые содержат текст,​В диапазоне ячеек или​В разделе​

​Пример: условию «стро?а» соответствуют​ в текущем отфильтрованном​​ таблице.​​ Фильтр).​ них.​ выделите любую ячейку​_Boroda_​ всего если между​н​ любой желтой ячейки.​ только те строки,​ условий, затем нажмите​отображены только​ критерию. Однако каждый​ но не оканчиваются​ столбце таблицы щелкните​

​Фильтр​ результаты «строфа» и​ диапазоне.​Более новые версии​Рассмотрим теперь другую таблицу​Примечание​ таблицы и нажмите​: А почему они​ исходной таблицей и​ик​ Щелкните правой кнопкой​ которые в ячейках​OK​

​Другие​ из этих типов​ буквами​ ячейку с определенным​щелкните​ «строка»​В окне фильтра отображаются​

planetaexcel.ru

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

​ Office для Mac 2011​ из файла примера​: Если пришлось очистить​CTRL+SHIFT+L​ в примере все​ дополнительной таблицей критериев​а​ мыши по ярлычку​ в столбцах «Сумма​. В этом примере​изделия. В дополнение​ фильтров является взаимно​Не заканчиваются​

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

​ цветом, цветом шрифта​Выберите один из вариантов​Звездочка (*)​ только первые 10 000​ ​ на листе Задача​ параметры Расширенного фильтра​

​(к заголовку будет​ разные? Мы должны​ будет хотя-бы одна​,​ текущего листа и​ заработной платы», имеют​ мы хотим получить​

1. ​ мы исключим все​ исключающим. Например, можно​.​ или значком, по​и во всплывающем​Любое количество символов​ уникальных записей списка.​Щелкните ячейку в диапазоне​ 7.​ (Данные/ Сортировка и​ применен Автофильтр, а​ были сами догадаться,​ пустая строка (столбец)​За​ выберите команду​ значение больше или​ номера, которые больше​ позиции, содержащие слово​ выполнить фильтрацию по​В поле рядом с​ которому вы хотите​ меню выполните одно​Пример: условию «*-восток» соответствуют​Щелкните ячейку в диапазоне​
2. ​ или таблице, которую​В столбце Товар приведено​ фильтр/ Очистить), то​ действие Расширенного фильтра​
3. ​ да? Что у​ разделяя их между​н​Исходный текст (Source Code)​ равно 12821, так​ или равны 3000,​ «case» в столбце​ цвету ячейки или​ всплывающим меню введите​
4. ​ выполнить фильтрацию.​ из указанных ниже​ результаты «северо-восток» и​ или таблице, которую​ хотите отфильтровать.​ название товара, а​ перед вызовом фильтра​ будет отменено) или​ Вас еще за​ собой. Так же​оз​. В открывшееся окно​ как нужно соблюдение​ но меньше или​Описание оборудования​ по списку чисел,​ текст, которое хотите​На панели инструментов​

​ действий.​ «юго-восток»​ хотите отфильтровать.​На вкладке​ в столбце Тип​ выделите любую ячейку​ нажмите кнопку меню Очистить​ пазухой занычено? Ррррр!​ следует учитывать порядок​а​ скопируйте и вставьте​ обоих критериев.​ равны 4000.​.​ но не к​

​ использовать.​

​Стандартная​Чтобы отфильтровать​Тильда (~)​На вкладке​Данные​ товара — его​ таблицы – EXCEL​ (Данные/ Сортировка и​ — это я​ и структуру оформления​и т.д.​ вот такой код:​

​Поставим переключатель в режим​Данные будут отфильтрованы по​

​Откройте вкладку​ обоим. Вы можете​В зависимости от сделанного​нажмите кнопку​Команда​Вопросительный знак или звездочка​Данные​щелкните​ тип. ​ автоматически вставит ссылку​

exceltable.com

Фильтр по значениям ячеек, расположенных в строках (Формулы/Formulas)

​ фильтр/ Очистить).​​ рычу!​
​ дополнительной таблицы с​
​>=э​Private Sub Worksheet_Change(ByVal​
​ «или», и жмем​ заданному числовому фильтру.​Данные​ отфильтровать по значку​ выбора вам может​Фильтр​Вычисление наибольших значений по​Пример: условию «здесь~?» соответствует​щелкните​Фильтр​
​Задача состоит в том,​ на диапазон занимаемый​Настроим фильтр для отбора строк,​Держите​ критериями для расширенного​
​все слова, начинающиеся с​ Target As Range)​ на кнопку «OK».​ В нашем случае​, затем нажмите команду​
​ или настраиваемому фильтру,​​ быть предложено выбрать​​_з0з_.​ значению​
​ результат «здесь?»​Фильтр​.​ чтобы для заданного​
​ таблицей (при наличии​​ у которых в​​SaVasiliy​​ фильтра. Заголовки столбцов​​Э​ If Not Intersect(Target,​Как видим, в этом​ отображаются только номера​Фильтр​​ но не к​​ дополнительные условия.​
​Щелкните стрелку​Первые 10​Выполните одно из указанных​​.​​Щелкните стрелку _з0з_ в​ типа товара вывести​
​ пустых строк в​ столбце Товар​: Благодарю!​
​ и значение критериев​

​,​​ Range(«A2:I5»)) Is Nothing​

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

​ товары, у которых​​ таблице вставится ссылка​точно​То что нужно!​ должны соответствовать значениям​Ю​ Then On Error​ результаты попадают строки​ 3000 до 4000.​ столбца появится кнопка​
​Фильтры скрывают излишние данные.​

​Операция​​ содержимым, которое нужно​
​Нумерации по значению​

excelworld.ru

​Задача​

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

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

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

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

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

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

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

После того, как это сделано, жмем на кнопку «OK».

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

Настройка автофильтра

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

После этого, открывается пользовательский автофильтр.

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

• Равно;
• Не равно;
• Больше;
• Меньше
• Больше или равно;
• Меньше или равно;
• Начинается с;
• Не начинается с;
• Заканчивается на;
• Не заканчивается на;
• Содержит;
• Не содержит.

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

Например, в колонке о заработной плате зададим пользовательский автофильтр по первому значению «больше 10000», а по второму «больше или равно 12821», включив при этом режим «и».

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

Поставим переключатель в режим «или», и жмем на кнопку «OK».

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

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

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

Для наглядности я немного дополнил исходную таблицу новыми сотрудниками:

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

Чтобы не перебирать фамилии (пусть даже и отсортированные), а после выбирать нужные, можно выполнить следующие действия:

1. В таблице сотрудников курсором мыши активируем кнопку фильтра и открываем меню фильтрации.
2. Выбираем пункт меню Текстовые фильтры, в результате чего отобразится перечень команд, при помощи которого можно по разному отфильтровать текстовый столбец.
3. Согласно поставленному условию наибольше подходит команда Начинается с…, на котором выполняем щелчок мышью.
4. В открывшемся окне Пользовательский автофильтр уже указан столбец (Фамилия) и выведено условие фильтрации Начинается с….

   

   Окно «Пользовательский автофильтр»

5. В поле, которое расположено напротив этого условия, вписываем условие фильтрации согласно поставленной руководством задачи – “Иван”. Получается фраза: «Отобразить фамилии, которые начинаются с литер “Иван”.
6. Нажимаем кнопку Ok.
7. Фильтрация применяется к таблице, в результате чего мы видим только тех сотрудников, фамилии которых начинаются с литер “Иван”.
8. Посылаем на печать результат работы и несем его руководству. Пусть вспоминает, зачем они ему нужны.

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

Дабы не употреблять в объяснении умные термины, вроде слов множества или пересечение множеств, постараюсь несколько его упростить. Вспомним 1-й класс школы.  😀

Будем объединять при помощи логических операторов И – ИЛИ две кучки конфет. Допустим, в каждой кучке лежат конфеты отдельного наименования: 1 и 2. Но! К ним, в каждую кучку добавлены по несколько конфет наименования 3. Получаем в одной кучке конфеты 1 и 3, а в другой – конфеты 2 и 3.

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

Работа логического оператора И

Если сложить конфеты по правилам логического ИЛИ, то получим в результирующей кучке только те конфеты, которые попадаются или только в одной, или только в другой кучке – конфеты наименований 1 и 2.

Работа логического оператора ИЛИ

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

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

После нажатия на кнопку Ok получим следующую таблицу:

Соединение по условию ИЛИ

Если же использовать логическое И, то получим только одну фамилию, так как только она начинается с Иван и заканчивается на ин:

Вот! Надеюсь этих примеров было достаточно, чтобы проиллюстрировать логику работы с операторами И и ИЛИ.

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

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

Как включить автофильтр в Excel

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

1. На вкладке «Главная» находится несколько разделов. Среди них – «Редактирование», на него и нужно обратить внимание.
2. Выбираем ячейку, для которой будет задан фильтр, далее кликаем по кнопке «Сортировка и фильтр» в этом разделе.
3. Откроется небольшое меню, где нужно выбрать пункт «Фильтр».

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

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

Настройка фильтра по данным таблицы

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

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

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

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

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

Важно! Отдельно стоит отметить функцию «Дополнительно…» в разделе «Сортировка и фильтр». Она предназначена для расширения возможностей фильтрации. С помощью расширенного фильтра можно задать условия вручную в виде функции.

Действие фильтра сбрасывается двумя методами. Проще всего использовать функцию «Отменить» или нажать комбинацию клавиш «Ctrl+Z». Другой способ – открыть вкладку данные, найти раздел «Сортировка и фильтр» и нажать кнопку «Очистить».

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

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

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

1. К примеру, выберем фильтр «равно» в обеих строках и установим разные значения – например, 39 в одной строке и 79 в другой.
2. Список значений находится в списке, который открывается после нажатия на стрелку, и соответствует содержимому столбца, где было открыто меню фильтра. Нужно сменить выбор выполнения условий с «и» на «или», чтобы фильтр сработал, а не убрал все строки таблицы.
3. После нажатия кнопки «ОК» таблица примет новый вид. Остались только те строки, где установлена цена 39 или 79. Результат выглядит так:

Понаблюдаем за работой текстовых фильтров:

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

Выбираем значение и кликаем по кнопке «ОК».

1. В результате на экране остаются две строки, начинающиеся с выбранной буквы.

Отключение автофильтра через меню Excel

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

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

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

Совет! Чтобы определить включена сортировка или выключена, можно взглянуть не только на шапку таблицы, но и на меню. Пункт «Фильтр» подсвечивается оранжевым, когда его включают.

Заключение

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

Эта функция проверяет, правильно ли заданное в аргументах утверждение, если да то выполняет указанное действие. Например, можно просто вывести ИСТИНА или ЛОЖЬ.

Результат функции

Результатом функции будет указанное вами значение, указать это самое значение можно для двух исходов(истина или ложь)

Формула

=ЕСЛИ(проверяемый_аргумент; значение_если_истина; значение_если_ложь)

Аргументы функции

• проверяемый аргумент — аргумент, который, в результате выполнения функции, будет проверен. Результатом будет ИСТИНА либо ЛОЖЬ;

• значение_если_истина — значение, которое вернет функция ЕСЛИ в случае, если проверяемый аргумент оказался истиной.

• значение_если_ложь — значение, которое вернет функция ЕСЛИ в случае, если проверяемый аргумент оказался ложью.

Важная информация

• Максимум проверяемых аргументов может быть 64;

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

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

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

Тоже самое, но для аргумента «Истины»:

Примеры

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

Проверяем соответствует ли число заданным критериям с помощью функции ЕСЛИ в Excel

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

Сразу же рассмотрим такую ситуацию:

Если число в столбце A больше либо равно 35, то результатом выполнения функции будет «Сдал», если же нет, то «Не сдал».

Проверяем сразу несколько критериев

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

В приведенном ниже примере мы проверяем два условия.

• Меньше ли значение в ячейке чем число 35;

• В случае, когда в результате первой проверки возвращается ЛОЖЬ, проверяется больше или равно значение в ячейке чем число 75.

Вычисляем комиссию

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

В ситуации описанной ниже, продавец не получает комиссию, если у него меньше 50-ти продаж. Если первое проверочное условие он прошел, тогда проверяем второе. Если у продавца меньше 100 продаж, его комиссия будет продажи*2%, а если больше, то — продажи*4%

Пример 4: Использование логических операторов (AND/OR) в функции IF в Excel

Также, мы можем использовать функции И и ИЛИ для проверки по сразу нескольким критериям.

Допустим, как указано на картинке ниже, мы имеем такую табличку:

Наша задача — рассчитать у кого из студентов будет стипендия. Данные для выдачи стипендии будут сразу же в формуле:

=ЕСЛИ(И(B2>80;C2>80%); "Да"; "Нет")

Как убрать ошибки при использовании функции ЕСЛИ в Excel

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

Формула:

=ЕСЛИ(ЕСЛИОШИБКА(A1);0;A1)

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

Точно также можно использовать функцию ЕПУСТО:

=ЕСЛИ(ЕПУСТО(A1);0;A1)

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

Включаем фильтр

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

1. Щелкаем по любой ячейке таблицы, для которой хотим задать фильтр. После этого жмем кнопку “Сортировка и фильтр” (раздел “Редактирование”, вкладка “Главная”). В появившемся списке кликаем по пункту “Фильтр”.
2. Кликаем по любой ячейке таблицы, после этого переключаемся во вкладку “Данные”, где нажимаем кнопку “Фильтр” (группа инструментов “Сортировка и фильтр”). 

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

Настраиваем фильтр

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

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

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

• равно
• не равно
• начинается с
• заканчивается на
• содержит
• не содержит

Чтобы воспользоваться ими, в меню фильтра щелкаем по пункту “Текстовые фильтры” (или “Числовые фильтры”/“Фильтры по дате” – в зависимости от формата ячеек). В результате откроется вспомогательный список с данными условиями.

Настраиваем пользовательский фильтр

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

1. Во вспомогательном меню фильтра кликаем по пункту “Настраиваемый фильтр”.
2. Появится окно для настройки пользовательского автофильтра. Преимуществом этого инструмента является то, что в отличие от простого фильтра, с помощью которого можно просто убрать ненужные данные лишь по одному условию, здесь у нас есть возможность отфильтровать значения в выбранном столбце одновременно по 2-ум параметрам, отвечающим условиям:
   • равно
   • не равно
   • больше
   • больше или равно
   • меньше
   • меньше или равно
   • начинается с
   • не начинается с
   • заканчивается на
   • не заканчивается на
   • содержит
   • не содержит
3. Помимо этого у нас есть возможность выбрать, хотим ли мы, чтобы одновременно выполнялись два заданных условия, или достаточно выполнения хотя бы одного из них. В зависимости от этого нужно установить отметку напротив соответствующего пункта.
4. Допустим, нам нужно оставить таблице только данные по видам спорта “баскетбол” и “теннис”. В этом случае мы устанавливаем в качестве условий параметр “содержит”, выбираем нужные значения (для того нужно нажать на стрелку вниз в конце поля для ввода информации) и устанавливаем переключатель в положение “ИЛИ”. По готовности жмем кнопку OK.
5. В итоге в нашей таблице будут отображаться только данные по выбранным видам спорта.

Выключаем фильтр

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

1. Во вкладке “Данные” нажимаем на кнопку “Фильтр” (группа “Сортировка и фильтр”).
2. Во вкладке “Главная” нажимаем на значок “Сортировка и фильтр”, в открывшемся списке кликаем по пункту “Фильтр”.
3. После этого таблица вернется к первоначальному виду, а спрятанные данные вновь появятся на экране.

Заключение

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