Excel функция фильтр с какой версии

Обновлено: 03.07.2024

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

видео может дополнять статью или содержать её полностью

Зачем нужны фильтры в таблицах Эксель

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

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

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

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

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

Как сделать (наложить) фильтр на таблицу Excel

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

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

Обратите внимание:

Если выделить ВСЮ строку, то Эксель автоматически установит фильтр на ВСЕ столбцы, в которых есть хоть что-то. Если в столбце ничего нет, то фильтр не накладывается.

После того как выделили нужные столбцы, нужно собственно установить фильтр. Самый простой способ — это зайти на вкладке ленты "Главное" / "Фильтр и сортировка" и нажать фильтр. Привожу скриншот из Office 2010, в прочих версиях Excel фильтр на таблицу накладывается аналогично.

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

После нажатия этой кнопки в выделенные ячейки добавится фильтр.

Если не выделять несколько ячеек, а просто щёлкнуть на одну, то фильтр добавляется на ВСЮ строку, как если бы вы её выделили.

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

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

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

Как пользоваться фильтром в Эксель

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

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

Смысл фильтра:

заключается в том, что Excel оставит только те строки таблицы, которые в ЭТОМ (с настроенным фильтром) столбце содержат ячейку с выбранным значением. Прочие строки будут скрыты.

Чтобы убрать фильтрацию (не удаляя фильтр!) просто отметьте галочками все пункты. Такой же эффект будет при удалении фильтра совсем — таблица вновь примет первоначальный вид.

Наложение нескольких фильтров на таблицу Эксель

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

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

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

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

Теперь посмотрите видеоролик, в котором я показываю реальный пример фильтрации данных в прайс листе более чем из 15 000 строк.

Подведём итоги

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

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

Уникальная метка публикации: 70827467-CA45-0D8B-92CB-220C2578B456
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/kak-sdelat-filtr-v-excel/

Смотреть видео
Фильтр Эксель (как сделать фильтр в Excel)

Файлы для загрузки

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

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

При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

Форматирование срезов

Выделить срез.
На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

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

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

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

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

Подготовка диапазона для условий фильтрации
Формирование условий фильтрации
Работа с мастером "Расширенного фильтра"

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

Разберем каждый из этапов его применения поподробнее.

1. Подготовка диапазона для условий фильтрации

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

Также важно помнить, что при указании в 3 шаге (работа в Мастере "Расширенного фильтра") диапазона условий нужно выделять шапку и строки, где есть какие-то критерии. Пустые строки выделять не нужно, иначе фильтра воспримет их как сигнал "Отобразить все строки".

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

Правильная организация данных для работы с расширенным фильтром Правильная организация данных для работы с расширенным фильтром

2. Формирование условий фильтрации

Этот этап является ключевым и раскрывает всю мощь инструмента. Для начала Вы должны научиться правильно задавать критерии для отбора.

Они могут быть 3 видов:

- текстовые критерии

Если в качестве текстового критерия ввести в поле какое-то слово, например, "Москва", то будут отобраны ВСЕ строки, в которых в заданном столбце запись начинается со слова "Москва"

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

Если нужно найти точное вхождение слова или фразы, то критерий придется задать несколько необычной формулой. Например, чтобы найти строки, в которых записано "Петербург" и не отображать строки "Санкт-Петербург", нужно ввести формулу: ="=Петербург" (именно так, с двумя знаками "=") .

- числовые критерии и даты

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

Также можно вводить выражения с использованием логических операторов (>, <, >=, <=, <>). Например, найти строки с суммой больше 500 000 можно введя критерий >500000

Особо внимательным нужно быть при вводе критериев в виде даты. Даты обязательно необходимо вводить через косую черту. Например, чтобы отобрать все сделки после 4 января 2017, нужно ввести критерий по полю "Дата" - >04/01/2017 (в некоторых версиях Excel требуется осуществлять ввод в формате ММ/ДД/ГГГГ, то есть сначала указывать месяц. Имейте это в виду при работе).

Самое лучшее, что умеет расширенный фильтр - это использовать в качестве критерия формулы. Чтобы все работало, задаваемая Вами формула должна возвращать значение ИСТИНА (и тогда строка выведется) или ЛОЖЬ (строка будет скрыта). Крайне важно - шапка столбца с формулой должна отличаться от любой записи в шапке таблицы (можете вообще оставить ее пустой). При написании формул, не забывайте правильно расставлять абсолютные и относительные ссылки.

Например, если нужно показать топ 5 строк по полю сумма, то необходимо будет ввести следующую формулу:

=F10>НАИБОЛЬШИЙ($F$10:$F$37;6),

где F10 - ячейка первой строки в столбце "Сумма" (она не закреплена, так как формула будет перебирать строки по очереди), $F$10:$F$37 - ссылка на диапазон, который занимает столбец "Сумма" (ссылка закреплена, так как столбец не изменяется).

В результате формула пройдет по всем строкам (от 10-ой до 37-ой) и скроет все, кроме тех, где значение больше шестого по величине (то есть оставит ТОП 5).

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

Итак, основные концепции, которые Вам нужно усвоить для успешного применения Расширенного фильтра:

- заголовок столбца, в котором пишем критерий отбора, должен быть точно таким же, как у того столбца, к которому применяем этот критерий. То есть, если отбираем строки, в которых в столбце "Сумма" значение больше 500, то и условие >500 пишем под шапку "Сумма";

- условия, записанные в одной строке, воспринимаются фильтром как связанные оператором И. Например, на картинке ниже записано условие И год 2017, И город Москва, И менеджер Петров .

- условия, записанные в разных строках, воспринимаются фильтром как связанные оператором ИЛИ. Условия могут применяться как к одному столбцу, так и к разным. Например, на картинке ниже записано условие ИЛИ город Москва, ИЛИ менеджер Иванов . Таким образом, каждая строка - это единый набор условий.

- если нужно задать условие И, но при этом использовать один и тот же столбец (например, И сумма больше 500 000, И сумма меньше 600 000 ), то заголовок такого столбца нужно продублировать дважды. Пример:

Теперь Вы знаете, какие критерии можно задавать, и как их правильно комбинировать. Этого достаточно, чтобы создавать сложные запросы, которые не под силу обычному автофильтру. Например, если нужно показать все сделки в Москве за 2017 год с суммой больше 500 000, а также одновременно отобразить все сделки Иванова за 2016 год, которые входят в ТОП5, то критерии будут выглядеть вот так:

3. Работа с мастером "Расширенного фильтра"

Самое сложное позади - Вы научились формировать критерии отбора. Остался один шаг. Выделяем диапазон с исходными данными (или любую его ячейку - Excel сам определит и выделит нужный массив) и находим на ленте команду "Дополнительно" во вкладке "Данные" в группе "Сортировка и Фильтр". Перед Вами появится окно "Расширенный фильтр"

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

2) определить диапазон условия (ваша мини-табличка с критериями, автоматически Excel может определить диапазон неточно, лучше каждый раз переназначать его заново), на этом шаге помним, что выделяем только заголовки и строки с критерием, пустые строки включать в диапазон нельзя ,

3) выбрать способ обработки (фильтровать данные в вашей исходной таблице или скопировать отфильтрованные строки в другое место),

4) указать, стоит ли показать все записи, или вывести только уникальные.

После настройки нажимаем "ОК" и получаем желаемый результат.

Чтобы очистить фильтр, переместите курсор в шапку таблицы с отфильтрованными данными и воспользуйтесь командой "Очистить" на ленте, или примените Автофильтр (Ctrl+Shift+L).

Недостатки Расширенного фильтра

Главным недостатком является то, что данный инструмент не является интерактивным и динамическим. Нельзя просто вписать в диапазон условий новый критерий и увидеть результат. Придется каждый раз вызывать Мастер "Расширенного фильтра" и повторять некоторые операции. Конечно, можно обойти и этот недостаток, но это уже тема для другой статьи.

Бонус. Полезный трюк с Расширенным фильтром

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

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

При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

Форматирование срезов

Выделить срез.
На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

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

Задание условий фильтрации

В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Функция расширенного фильтра в Microsoft Excel

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

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

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

Шаг 1: Создание таблицы с условиями отбора

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

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

Шаг 2: Запуск расширенного фильтра

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

«Данные»

«Сортировка и фильтр»

«Дополнительно»

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

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

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

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Многие пользователи ПК хорошо знакомы с пакетом продуктов для работы с различного рода документами под названием Microsoft Office. Среди программ этой компании есть MS Excel. Данная утилита предназначена для работы с электронными таблицами.

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

Что это за функция? Описание

Что значит автофильтр?

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

Как делать правильно?

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

Инструкция по расширенной фильтрации электронной таблицы:

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

Для этого необходимо:

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

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

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

Рассмотрим пример расширенного фильтра в Excel 2010 и использования в нем формул. К примеру, разграничим значения какого-нибудь столбца с числовыми данными по результату среднего значения (больше или меньше).

Инструкция для работы с расширенным фильтром в Excel по среднему значению колонки:

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

Автофильтр. Пример использования

Читать еще: Руководство по настройке брандмауэра в Windows 10

Небольшое заключение

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

Применение расширенного фильтра в Excel

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

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

Применение расширенного фильтра

Итак, у нас есть таблица с данными, которые требуется отфильтровать.

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

Первым делом потребуется создать вторую вспомогательную таблицу с условиями фильтра. Для ее создания необходимо скопировать шапку исходной таблицы и вставить во вспомогательную.
Для наглядности поместим дополнительную таблицу сверху, рядом с основной. А также пометим ее, залив другим цветом (это делается для большей наглядности и не обязательно). Вспомогательную таблицу можно поместить абсолютно в любом месте документа, причем, вовсе не обязательно, чтобы она была на том же самом листе, где располагается основная таблица.
Затем приступим к заполнению вспомогательной таблицы данными, которые потребуются для работы. А нужны нам значения из основной таблицы, по которым необходимо отфильтровать данные. В нашем случае, мы хотим отобрать информацию по женскому полу и виду спорта – теннис.
Когда вспомогательная таблица готова, можно приступать к следующему шагу. Поместив курсор на любую ячейку начальной или вспомогательной таблицы, в основном меню программы кликните по вкладке «Данные», выберите из открывшегося блока инструментов “Фильтр” пункт “Дополнительно”.
В результате должно появиться окно с настройками расширенного фильтра.
У данной функции есть два варианта применения: «Скопировать результаты в другое место» и «Фильтровать список на месте».Как можно понять из названий, эти опции отвечают за то, каким образом будет выводится отфильтрованная информация. В первом варианте данные будут выведены отдельно в указанном вами месте документа. Во втором – непосредственно в исходной таблице. Выбираем подходящий вариант (в нашем случае оставляем фильтрацию на месте) и двигаемся дальше.
В поле «Диапазон списка» необходимо указать координаты таблицы (вместе с шапкой). Выполнить это можно, прописав их вручную, либо простым выделением таблицы мышью, щелкнув по небольшой пиктограмме в конце поля для ввода координат. В строке «Диапазон условий» таким же образом указываем координаты вспомогательной таблицы (заголовок и строку с условиями).Хотим обратить внимание на одну немаловажную деталь. Следите за тем, чтобы в выделяемую область не попали пустые ячейки. В противном случае, ничего не получится. По завершении подтвердите указанные координаты нажатием «OK».
В результате проделанных действий в исходной таблице останутся только требуемые нам данные.
Если мы выберем вариант «Скопировать результаты в другое место», то результат будет выведен в указанное нами место, а исходная таблица останется без изменений. При этом в строке «Поместить результат в диапазон» от нас требуется ввести координаты для вывода результата. Можно указать лишь одну ячейку, которая будет самой верхней левой координатой для новой таблицы. В данном случае, выбранная ячейка – A42.
После нажатия кнопки “OK” новая таблица с заданными параметрами фильтрации будет вставлена, начиная с ячейки A42.

Как отменить расширенную фильтрацию

Для того чтобы отменить расширенную (равно как и любую другую) фильтрацию, перейдите во вкладку “Главная”, в блоке инструментов “Фильтр” нажмите кнопку «Очистить».
Также, очистить фильтр можно и другим способом. Перейдите во вкладку “Главная”, щелкните по разделу “Редактирование”, далее раскройте список “Сортировка и фильтр” и выберите пункт “Очистить”.
Если вы создали отдельную новую таблицу с отфильтрованными данными в новом месте, очистка фильтров не удалит ее, и сделать это нужно будет вручную.

Заключение

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

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