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

2.2.5.4. Поиск данных в списке Excel

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

К средствам фильтрации относятся:

  • Автофильтр (существуют два способа применения команды Автофильтр: с помощью меню «Данные» — «Фильтр» — «Автофильтр» — «раскрывающийся список команд автофильтра» и с помощью кнопки «Автофильтр» на панели инструментов стандартная)
  • Расширенный фильтр («Данные» — «Фильтр» — «Расширенный фильтр»)

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

 Использование автофильтра для поиска записей

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

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

Рис. 1.

Список команд автофильтра и их назначение:

  • Все — все записи будут отображаться на экране
  • Первые 10… — откроется окно диалога «Наложение условия по списку» в том случае, если данные в столбце являются числами. С помощью этого окна можно быстро просмотреть до десяти наибольших или наименьших чисел в столбце.
  • Условие… — откроется окно диалога «Пользовательский автофильтр». С помощью этого окна можно отсортировать записи, удовлетворяющие одному или двум условиям
  • Уникальные имена — это список неповторяющихся записей в ячейках столбца. Если щелкнуть левой кнопкой мыши на одном из уникальных имен, то редактор скроет строки, которые не содержат выбранных данных

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

Алгоритм поиска записей отвечающих одному условию в одном столбце

  • Выберите ячейку в списке
  • Выполните команду «Данные» — «Фильтр» — «Автофильтр»
  • Щелкните на кнопке автофильтра в том столбце, который используете для фильтрации данных
  • Щелкните на одном из уникальных имен, редактор мгновенно скроет строки, которые не содержат выбранных данных

На рисунке представлен отфильтрованный список по столбцу «Категории» при выбранном условии отбора «Конд. продукты»

Рис. 2.

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

 Алгоритм поиска записей отвечающих одному или двум условиям в одном столбце

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

  • Выберите ячейку в списке
  • Выполните команду «Данные» — «Фильтр» — «Автофильтр»
  • Щелкните на кнопке автофильтра в том столбце, который используете для фильтрации данных
  • Щелкните на команде «Условие», откроется окно диалога «Пользовательский автофильтр»
  • В двух левых раскрывающих списках выберите соответствующие операторы, а в двух правых раскрывающих списках выберите требуемые значения. Затем установите переключатель в одно из двух положений: «и», «или»
  • Щелкните на кнопке ОК для выполнения фильтрации. В списке будут отображены записи, удовлетворяющие заданным критериям.

Для примера выберем следующие условия отбора: показать в списке строки, содержащие продукты, цены которых больше или равны 69,80 грн и меньше 99,99 грн.

Рис. 3.

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

  • равно
  • не равно
  • больше
  • больше или равно
  • меньше
  • меньше или равно

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

На рисунке представлен отфильтрованный список по столбцу «Цена» при выбранных условиях отбора.

Рис. 4.

Фильтр по выделенному

Фильтрация по выделенному — это быстрый способ отфильтровать записи по одному условию с помощью кнопки «Автофильтр» на панели инструментов стандартная.Рис. 5.Алгоритм фильтрации по выделенному:

  • Выделите требуемую запись в списке
  • Нажмите кнопку «Автофильтр», чтобы выполнить фильтрацию по выделенному. Редактор включит «Автофильтр» и скроет строки, которые не содержат выбранных данных

 Фильтрация списка с помощью расширенного фильтра

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

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

Создание диапазона условий отбора:

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

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

Рис. 6.

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

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

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

На рисунке представлен образец «Расширенного фильтра» выбранными параметрами фильтрации.

Рис. 7.

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

Рис. 8.

Далее …>>> 2.2.5.5. Промежуточные «Итоги» в списках

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

image

В этой статье мы постараемся подробнее рассмотреть работу с «Автофильтром».

Содержание

Что такое «Автофильтр» в Excel

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

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

Теперь пройдёмся по порядку по поводу работы именно с «Автофильтром».

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

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

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

    Примечание. В новых версиях Excel может не быть кнопки «Сортировка и фильтр» в полноэкранном режиме. Вместо неё должен находится блок с соответствующим названием. Здесь и будут расположены все необходимые кнопки.

  4. Аналогичным образом можно включить «Фильтр», если переместиться в раздел «Данные», который расположен в верхней части табличного редактора. Там нужно нажать по соответствующей кнопке. image
  5. Когда фильтр будут включён у заголовков столбцов таблицы должны будут появиться специальные обозначения в виде стрелок в квадратах. Однако если в таблице нет чёткого обозначения заголовков у столбцов, то могут быть проблемы с корректностью отображения.

Как пользоваться «Автофильтром» в Excel

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

  1. Нажмите в нужном столбце для фильтрации иконку в виде стрелочки в квадрате.
  2. Здесь откроется сам фильтр для ячейки. В нём доступны следующие настройки:
    • Настройка сортировки – по алфавиту (для текста), от большего к меньшему (для чисел), от старого к новому (для дат);
    • Сортировка по цветам. Применима только в тех случаях, если в тексте и/или заливке встречается цветовое обозначение;
    • Фильтрация по тексту;
    • Выборка отдельных элементов столбца, например, дат, имён и т.д.
  3. Указав нужные настройки не забудьте нажать «Ок», чтобы они окончательно применились. Для примера мы снимем галочки с некоторых фамилий, чтобы они не мешались в отчёте, оставив только одну, дабы посмотреть данные по этому человеку.

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

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

Читайте также: Расширенный фильтр в Excel — как пользоваться Как сортировать и фильтровать данные в Excel Умная таблица в Excel (Эксель): создание и использование Как в Повер Поинте (PowerPoint) сделать таблицу — инструкция

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

  1. У нужного столбца нажмите по иконке в виде стрелки, чтобы перейти к настройкам его фильтра.
  2. В контекстном меню выберите пункт «Числовые фильтры», «Текстовые фильтры» или «Фильтры по дате» в зависимости от типов данных, расположенных в таблице.
  3. Появится ещё одно контекстное подменю. Здесь можно выбрать уже заданные по умолчанию настройки или поставить свои, воспользовавшись кнопкой «Настраиваемый фильтр».
  4. Откроется окошко пользовательского автофильтра. Тут пользователь может отфильтровать данные сразу по двум значениям. При этом доступно использование логических операторов – «и» и «или». Выберите соответствующий параметр для фильтрации.
  5. Здесь можно задать условия для фильтрации данных в двух строках. Всего доступны следующие параметры фильтрации:
    • Равно;
    • Не равно;
    • Больше;
    • Меньше;
    • Больше или равно;
    • Меньше или равно;
    • Начинается с;
    • Не начинается с;
    • Заканчивается на;
    • Не заканчивается на;
    • Содержит;
    • Не содержит.
  6. Задайте по одному из данных условий в двух блоках. Укажите применяемый логический оператор («и» или «или»).
  7. В строках напротив условий нужно указать сравниваемые данные. Здесь числа либо вписываются вручную, либо выбираются из существующих.
  8. Прописав нужные параметры нажмите кнопку «Ок», чтобы применить их.
  9. После применения заданных условий в таблице должны будут остаться только данные, которые соответствуют ранее указанным условиям.

Дополнительное пояснение по поводу логических операторов:

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

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

{«smallUrl»:»https://www.wikihow.com/images_en/thumb/2/2f/Add-a-Filter-in-Excel-2007-Step-1-Version-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-1-Version-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/2/2f/Add-a-Filter-in-Excel-2007-Step-1-Version-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-1-Version-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 1 Откройте таблицу, в которой вы хотите отфильтровать данные. {«smallUrl»:»https://www.wikihow.com/images_en/thumb/2/25/Add-a-Filter-in-Excel-2007-Step-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/2/25/Add-a-Filter-in-Excel-2007-Step-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 2 Подготовьте данные для фильтрования. Excel может фильтровать данные во всех выделенных ячейках, при этом ни одна строка и ни один столбец в выбранном диапазоне не должны быть абсолютно пустыми. После обнаружения пустой строки или столбца, фильтрование прекращается. Если данные, которые вы хотите отфильтровать, разделены пустыми строками или столбцами, удалите такие строки или столбцы перед началом фильтрации.

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

{«smallUrl»:»https://www.wikihow.com/images_en/thumb/a/a9/Add-a-Filter-in-Excel-2007-Step-3-Version-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-3-Version-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/a/a9/Add-a-Filter-in-Excel-2007-Step-3-Version-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-3-Version-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 3 Выделите любые ячейки, в которых необходимо отфильтровать данные. {«smallUrl»:»https://www.wikihow.com/images_en/thumb/0/0f/Add-a-Filter-in-Excel-2007-Step-4-Version-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-4-Version-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/0/0f/Add-a-Filter-in-Excel-2007-Step-4-Version-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-4-Version-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 4 Перейдите на вкладку «Данные» меню Excel. {«smallUrl»:»https://www.wikihow.com/images_en/thumb/8/86/Add-a-Filter-in-Excel-2007-Step-5-Version-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-5-Version-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/8/86/Add-a-Filter-in-Excel-2007-Step-5-Version-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-5-Version-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 5 Нажмите «Фильтр» из группы «Сортировка и фильтр». Раскрывающиеся меню в виде стрелки появятся в верхней части каждого выделенного столбца. Если у этих столбцов есть заголовки, то стрелки меню появятся в заголовках. {«smallUrl»:»https://www.wikihow.com/images_en/thumb/2/22/Add-a-Filter-in-Excel-2007-Step-6.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-6.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/2/22/Add-a-Filter-in-Excel-2007-Step-6.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-6.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 6 Нажмите на стрелку. Раскроется меню с параметрами для фильтрования. Выполните одно из следующих действий:

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

Реклама

Метод 2 из 2:

Удаление фильтра

  1. {«smallUrl»:»https://www.wikihow.com/images_en/thumb/e/ed/Add-a-Filter-in-Excel-2007-Step-7-Version-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-7-Version-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/e/ed/Add-a-Filter-in-Excel-2007-Step-7-Version-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-7-Version-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 1 Нажмите на стрелку раскрывающегося меню фильтра (верхняя ячейка столбца) и выберите «Снять фильтр с …». Таким образом, удаляется любое фильтрование с соответствующего столбца.
  2. {«smallUrl»:»https://www.wikihow.com/images_en/thumb/d/d3/Add-a-Filter-in-Excel-2007-Step-8-Version-2.jpg/v4-460px-Add-a-Filter-in-Excel-2007-Step-8-Version-2.jpg»,»bigUrl»:»https://www.wikihow.com/images/thumb/d/d3/Add-a-Filter-in-Excel-2007-Step-8-Version-2.jpg/v4-728px-Add-a-Filter-in-Excel-2007-Step-8-Version-2.jpg»,»smallWidth»:460,»smallHeight»:345,»bigWidth»:728,»bigHeight»:546,»licensing»:»</div>»} 2 Перейдите на вкладку «Данные» в меню Excel, а затем нажмите «Очистить», чтобы удалить фильтры из всех столбцов. Реклама

Советы

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

Реклама

Источники

Об этой статье

На других языках English:Add a Filter in Excel 2007 Español:crear filtros en Excel Italiano:Aggiungere un Filtro in Excel 2007 中文:在Excel 2007中应用筛选功能 Français:insérer un filtre dans Excel Bahasa Indonesia:Menambah Filter di Excel 2007 Реклама

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

Назначение и существующие виды

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

Пользовательский или расширенный фильтр

Установка фильтра

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

После этого включается фильтр в Excel:

  1. На панели вкладок нажать на «Данные».
  2. В окне «Сортировка и фильтр» кликнуть по «Дополнительно».
  3. Появится меню «Расширенный фильтр» с различными настройками.

Фильтр инициализирован. Настройки и условия фильтрации рассматриваются ниже.

Настройки и условия

Есть несколько настроек в меню фильтрации:

  1. По умолчанию в появившемся окне отмечен подпункт «Фильтровать список на месте» или «Filter the list, in-place» в англоязычной версии. Эта опция позволяет провести операцию в той же локации, что и исходная таблица.
  2. Строка «Исходный диапазон» или «List range». Здесь нужно проставить координаты фильтруемой таблицы, включая заголовки. Начальная координата — первая ячейка, конечная — последняя.
  3. «Диапазон условий» или «Criteria range». Указывается местоположение условий фильтрования. Условия задания координат аналогичны предыдущему пункту.

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

  1. Если нужно оставить только неповторяющиеся строки, то нужно отметить ячейку «Только уникальные записи» или «Unique records only».

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

Критерий Результат
пр* или пр все записи, которые начинаются с «Пр», т.е. прима, продукт и аналогичные данные
=диван все данные, имеющие точное совпадение со словом «диван» и только
*лив* или *лив слова, содержащие слог «лив», например, оливки, Ливония, прилив.
=т*в надписи, начинающиеся с «Т» и заканчивающиеся на «В» — Титов, Тургенев
п*с схожее с предыдущим пунктом написание критерия, но здесь буква С не должна быть обязательно в конце слова, а может находиться в любом месте после П, например, просо, простой
=*г записи, оканчивающиеся на Г
=??? слова, где есть 3 символа, включая пробелы и цифры
=а????с выбор данных, состоящих из 6 символов и имеющих в начале букву «А», а в конце букву «С», такие как «ананас», «адонис»
=*л??а слова, которые заканчиваются на «а», а четвёртой буквой с конца в слове является «л», например, «малина».
<=В</td> записи, начинающиеся с букв «А», «Б», «В»
<>*е* слова, которые не имеют букву «Е»
<>*вна все слова, исключая те, которые заканчиваются на «вна»
= выборка всех пустых ячеек
<> отбор несвободных «клеток»
>=199 отбираются данные со значением большим или равным 199
10 или =10 показатели со значениями равными 10
>=2/28/2018 события с датой позже 28 февраля 2018 года (включительно)
<=12/03/2019</td> Данные с датой раньше, чем 12 марта 2019 года

  Как использовать гиперссылки в Эксель

Нужно помнить следующее при составлении условий:

  • * — может означать любое количество различных знаков;
  • ? — один символ;
  • дата ставится в североамериканском стиле, то есть мм/дд/гггг.

Базовыми условиями пользовательской фильтрации в Excel являются логические «И( AND)» и «ИЛИ(OR)». Все установки отбора используют эти операторы в различных вариациях.

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

На рисунке показана работа оператора «И». Будут выбраны товары с наименованием «Бананы», которые поставлялись в III квартале года в Москве, в гипермаркеты «Ашан». Предметы, не отвечающие вышеуказанным условиям, показаны не будут.

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

На примере показано двойное использование логического «И» в столбце «Дата». В результате такого применения будут выбраны все события между 1 марта и 31 мая 2013 года.

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

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

Действенным методом в расширенном фильтре является использование формул для  формирования критерия выборки. Алгоритм прост — формула проверяет ячейки на «Истину» или «Ложь» и показывает строки с истинным результатом. При составлении задания с помощью формулы нужно учитывать следующее:

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

  Программа Excel — способы сохранения и его параметры

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

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

=СЧЁТЕСЛИ(Лист1!$A$8:$A$83;A8)=1

Здесь $A$8:$A$83 — абсолютная ссылка, указывающая диапазон действия формулы. A8 — относительная ссылка. Она показывает номер ячейки, с которой начнется проверка по формуле. Результаты, отвечающие условию «ИСТИНА», будут показаны. Таким образом, все товары, которые представлены в единичном экземпляре отобразятся.

Применение

Алгоритм применения пользовательского фильтра следующий:

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

Перемещение результатов отбора в другую таблицу

Для того чтобы данные фильтрации отобразились в ином диапазоне нужно сделать следующее:

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

Удаление пользовательского фильтра

Сбросить функцию можно несколькими способами:

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

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

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

Запуск

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

  1. На главной панели нажать на пункт «Данные», в подменю «Сортировка и фильтр» кликнуть по иконке с надписью «Фильтр».
  2. Выбрать пункт «Главная», в подсистеме «Редактирование» нажать на «Сортировка и фильтр». В появившемся окне выбрать «Фильтр».
  3. С помощью нажатия кнопок Ctrl + Shift + L.

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

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

Существуют несколько параметров исключения.

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

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

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

  • выбрать в контекстном меню надпись «После…»;
  • откроется подменю, в нем для функции «После…» выбрать дату 01.06.2014;
  • выбрать логическое «И»;
  • в нижней строке «До» выбрать вторую дату и подтвердить.

  Вставка картинки в Excel и способы закрепления изображения в ячейке

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

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

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

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

  • кликнуть по условию «Содержит…»;
  • в следующем окне выбрать «И» и для критерия «Содержит…» указать «2»;
  • подтвердить;
  • получится таблица, содержащая цифру «2» в столбце «Наименование».

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

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

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

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

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

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

  1. Выбрать во вкладке «Данные» или «Data», пункт «Сортировать» или «Sort».
  2. Появится окно с настройками «Сортировка» или «Sort». Если строка содержит заголовки, то нужно отметить пункт «Мои данные содержат заголовки» или «My data has headers». В противном случае ставить галочку не нужно. После этого кликнуть по пункту «Параметры» или «Options».
  3. В подменю «Параметры» отметить, как будут меняться столбцы — сверху вниз (Sort top to bottom) или слева направо (Sort left to right). Если изменяется порядок следования в строке, то нужно выбрать «Слева направо».
  4. Далее в окне «Sort» указать строку в которой будет изменен порядок следования столбцов и указать в каком порядке будет идти перестроение — от А до Я (A to Z) или наоборот.

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

Состояние до сортировки
После сортировки

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

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

Для удаления всех критериев отбора на вкладке «Данные» кликнуть по «Фильтр».

Читайте также:

image

Автофильтр Excel (Содержание)

  • Автофильтр в Excel
  • Как использовать автофильтр в Excel?

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

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

В Excel мы можем найти опцию Автофильтр на вкладке данных в группе сортировки и фильтрации. Нажмите фильтр, как показано на скриншоте ниже:

Сочетание клавиш для применения фильтра — CTRL + SHIFT + L, где он применяет фильтр ко всей электронной таблице.

Как использовать автофильтр в Excel?

Автофильтр в Excel очень прост и удобен в использовании. Давайте разберемся в работе Excel AutoFilter на некотором примере.

Вы можете скачать этот шаблон AutoFilter Excel здесь — Шаблон AutoFilter Excel

Автофильтр в Excel — Пример № 1

Рассмотрим пример, приведенный ниже, где у нас есть данные о продажах BRAND, такие как местные, зональные и национальные данные.

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

  • Сначала выберите ячейку, чтобы применить фильтр. Перейти на вкладку данных. Выберите опцию Автофильтр.
  • Мы видим, что фильтр применен ко всем ячейкам.
  • Теперь нам нужно отфильтровать конкретную марку, чтобы проверить продажи.
  • Мы можем найти БРЕНД в столбце C, щелкнув раскрывающийся список, в котором отображаются все бренды, как показано на скриншоте ниже.
  • Снимите все флажки и выберите марку BRAUN из следующего списка.
  • После того, как мы выберем BRAUN из следующего списка, Excel отфильтрует только BRAUN, и он скроет все остальные строки и отобразит конкретную марку в качестве вывода следующим образом.
  • На приведенном выше снимке экрана показано, что продажи бренда BRAUN возможны только на местном, региональном и национальном уровнях.

Очистить автофильтр в Excel

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

Автофильтр в Excel — Пример № 2

В этом примере мы теперь собираемся проверить продажи по годам вместе с продажами по брендам и рассмотреть приведенный ниже пример.

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

  • Выберите ячейки, чтобы применить фильтр.
  • Перейдите на вкладку данных и нажмите кнопку фильтра.
  • Теперь фильтр был применен следующим образом. Мы видим, что фильтр был применен ко всей ячейке.
  • Выберите столбец «Год» и щелкните раскрывающийся список с выходными данными следующим образом.
  • После того, как мы нажмем на выпадающий список, мы увидим, что название месяца, которое было проверено, теперь идет за конкретный месяц, который нам нужен для проверки данных о продажах. Предположим, что нам нужно проверить продажи в апреле месяце. поэтому снимите флажок «выбрать все» из списка, как показано ниже.
  • Теперь мы видим, что все флажки сняты, и теперь выбираем месяц « АПРЕЛЬ », чтобы он фильтровал только продажи за месяц « АПРЕЛЬ ».
  • Здесь были показаны продажи за апрель, а также продажи по регионам и по регионам. Следовательно, мы можем предоставить данные о продажах за месяц, применив опцию фильтра.

Автофильтр в Excel — Пример № 3

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

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

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

Автофильтр в Excel — Пример № 4

В этом примере мы увидим, как получить топ-3 самых высоких продаж, которые были в этом году.

Принимая во внимание те же данные о продажах, мы можем получить 5 самых высоких показателей продаж, выбрав 10 лучших вариантов, указанных в числовом фильтре, как показано ниже:

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

Автофильтр в Excel — Пример № 5

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

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

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

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

То, что нужно запомнить

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

Рекомендуемые статьи

Это было руководство по автофильтрации в Excel. Здесь мы обсудим, как использовать автофильтр в Excel, а также с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

  1. Как создать полосу прокрутки в Excel?
  2. Руководство по Excel QUARTILE Функция
  3. Функция LINEST Excel с лучшими примерами
  4. Примеры вспомогательной функции Excel
  5. Примеры макросов Excel

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий