Несколько фильтров на одном листе excel
Под фильтрацией списков понимается выделение из всего списка только тех записей, значения полей которых удовлетворяют заданным условиями.
MS Excel позволяет выполнять фильтрацию с помощью трех инструментов, которые имеют различные функциональные возможности.
В Excel для фильтрации списков есть три инструмента - Автофильтр , который применяется в случае простых условий отбора, Пользовательский автофильтр и Расширенный фильтр для усложненных условий отбора.
Применение для фильтрации Автофильтра
Для вызова Автофильтра нужно выделить любую ячейку списка и на ленте Данные в группе Сортировка и фильтр кликнуть на пиктографической кнопке Фильтр .
После вызова Автофильтра на листе таблицы рядом с каждым заголовком столбца появятся кнопки в виде стрелок (раскрывающиеся списки) (рис.1).
Если кликнуть на любой стрелке, то раскроется список значений, допустимых для задания условий фильтрации по этому столбцу (рис. 2).
Если требуется, например, просмотреть все операции только по счету № 1 , нужно кликнуть по стрелке рядом со столбцом № счета и в раскрывшемся списке установить пометку (галочку) только для счета 1. Результат фильтрации представлен на рис. 3.
Для отмены результатов фильтрации нужно кликнуть на пиктограмме Фильтр на ленте Данные .
Наложение условий фильтрации по списку
Для наложения условий по списку:
- нужно кликнуть на стрелке рядом с наименованием атрибута списка;
- в раскрывшемся списке выбрать пункт Числовые фильтры (заметим что название этого пункта может меняться в зависимости от типа данных, содержащихся в столбце списка, например, для столбца Дата операции он будет иметь наименование Фильтры по дате );
- в выпашем списке ( рис. 4) выбрать пункт Первые 10 - откроется диалоговое окно Наложение условия по списку (рис. 5);
Задавая условия с помощью этого диалогового окна можно найти заданное значение (заданный процент) наибольших или наименьших элементов в списке.
Пользовательский автофильтр
Применение пользовательского автофильтра позволяет задать более сложное условие для фильтрации. Чтобы включить пользовательский автофильтр нужно:
- в списке (рис. 2) выбрать пункт Числовые фильтры ;
- в раскрывшемся списке (рис. 4.) выбрать один из пунктов Равно . Между или Настраиваемый фильтр, откроется диалоговое окно Пользовательский автофильтр (рис. 6) ;
- в соответствующих полях этого диалогового окна установить значения, как показано на рисунке (используйте раскрывающиеся списки).
Например, решается задача отбора всех операций, проведенных в период с 5.01 по 24.01 включительно. Условия для фильтрации приведены на рис. 6.
После щелчка на кнопке ОК список будет отфильтрован и в нем останутся только те строки, которые удовлетворяют заданному условию.
Применение для фильтрации Расширенного фильтра
Расширенный фильтр, в отличие от «Автофильтра» позволяет:
- Задавать условия, соединенные одним или несколькими логическими операторами.
- Задать несколько условий для одного столбца.
- Задать вычисляемые условия.
- Скопировать результаты фильтрации в другое место.
Другим отличием от Автофильтра является то, что условия для фильтрации записываются в диапазоне ячеек свободного участка рабочего листа.
Задание диапазона условий
Рассмотрим технологию задания диапазона условий на примере отбора сведений об операциях по номерам счетов с 5 по 8 включительно. Для решения задачи выполним действия:
1. В свободных ячейках рабочего листа определим диапазон, в котором будет записано условие для фильтрации (например, C2:D3).
2. Запишем в первой строке этого диапазона наименования столбцов списка, на которые будут наложены условия фильтрации (это лучше сделать копированием), а во второй выражение условия (рис.7).
Заметим, что при записи выражений для условий на одной строке условия как бы соединяются логическим оператором «И». Если нужно применить логический оператор «ИЛИ», то условия нужно записать на разных строках одно под другим.
3. Установим курсор в область списка.
4. На ленте Данные в группе Сортировка и фильтр кликнем на кнопе пиктографического меню Дополнительно -откроется диалоговое окно Расширенный фильтр (рис. 8) .
5. В поле Исходный диапазон укажем адрес диапазона, в котором размещен список (в примере - $A$2:$E$124), а в поле Диапазон условий - адрес диапазона, содержащего условия фильтрации (в примере - $C$2:$D$3).
6. В поле Поместить результат в диапазон укажем адрес начальной ячейки диапазона, куда следует вывести результат (A6).
После клика на кнопке «ОК» получим выборку по заданным условиям(рис 9).
В диапазоне условий можно ввести любое количество условий. Возможно одновременное применение «И» и «ИЛИ».
Применение условий с вычисляемыми значениями
Вычисляемые условия позволяют использовать в условиях выборки значения, возвращаемые формулой. При их применении необходимо соблюдать правила:
- Заголовок над вычисляемым условием должен отличаться от заголовков столбцов в списке. Это условие противоположно требованиям для формирования обычных условий.
- Ссылки на ячейки, находящиеся вне списка, должны быть абсолютными.
- Ссылки на ячейки в списке должны быть относительными.
Пример.
Рассмотрим создание вычисляемого условия на примере выборки строк в которых значение числа в столбце Приход больше среднего значения прихода за учетный период.
- В любой ячейке вне списка запишем формулу вычисления среднего значения (например, в ячейке H2) (рис. 10).
- В ячейку H4 запишем слово «Выборка» (или что-либо другое), а в ячейке H5 - выражение вычисляемого условия =D3>$G$2. Если условия фильтрации в ячейках записаны правильно, то в них появится запись ИСТИНА или ЛОЖНО.
- Включим расширенный фильтр.
- В открывшемся диалоговом окне Расширенный фильтр введем адрес исходного диапазона, адрес диапазона условий, одну из опций обработки и адрес вывода результата (рис. 10).
- Кликнем на кнопке ОК.
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
- Выделяем мышкой любую ячейку внутри диапазона. Переходим на вкладку «Данные» и нажимаем кнопку «Фильтр».
- Рядом с заголовками таблицы появляются стрелочки, открывающие списки автофильтра.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
- Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
- Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
- Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.
У расширенного фильтра гораздо больше возможностей:
- Можно задать столько условий для фильтрации, сколько нужно.
- Критерии выбора данных – на виду.
- С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
- Создадим таблицу с условиями отбора. Для этого копируем заголовки исходного списка и вставляем выше. В табличке с критериями для фильтрации оставляем достаточное количество строк плюс пустая строка, отделяющая от исходной таблицы.
- Настроим параметры фильтрации для отбора строк со значением «Москва» (в соответствующий столбец таблички с условиями вносим = «=Москва»). Активизируем любую ячейку в исходной таблице. Переходим на вкладку «Данные» - «Сортировка и фильтр» - «Дополнительно».
- Заполняем параметры фильтрации. Исходный диапазон – таблица с исходными данными. Ссылки появляются автоматически, т.к. была активна одна из ячеек. Диапазон условий – табличка с условием.
- Выходим из меню расширенного фильтра, нажав кнопку ОК.
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
- Результат формулы – это критерий отбора.
- Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
- Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
При создании сложных отчетов и, особенно, дашбордов в Microsoft Excel, весьма часто возникает необходимость одновременной фильтрации сразу нескольких сводных таблиц. Давайте разберёмся, как такое можно можно реализовать.
Способ 1. Общий срез для фильтрации сводных на одном источнике данных
Если сводные построены по одной исходной таблице данных, то проще всего использовать для их одновременной фильтрации срез - графический кнопочный фильтр, подключенный сразу ко всем сводным таблицам.
Чтобы его добавить, выделите любую ячейку в одной из сводных и на вкладке Анализ выберите команду Вставить срез (Analyze - Insert slicer) . В открывшемся окошке пометьте галочками те столбцы, по которым вы хотите фильтровать данные и нажмите ОК:
Созданный срез будет, по умолчанию, фильтровать только ту сводную, для которой он был создан. Однако, воспользовавшись кнопкой Подключения к отчетам (Report connections) на вкладке Срез (Slicer) мы можем легко добавить к списку фильтруемых таблиц другие сводные:
Способ 2. Общий срез для фильтрации сводных на разных источниках
Если ваши сводные были построены не по одной, а по разным исходным таблицам данных, то приведённый выше способ не сработает, т.к. в окне Подключения к отчётам отображаются только те сводные, которые были построены по одному источнику.
Однако, можно легко обойти это ограничение, если использовать Модель Данных (мы подробно разбирали её в этой статье). Если загрузить наши таблицы в Модель и связать их там, то фильтрация станет распространяться на обе таблицы одновременно.
Допустим, что в качестве исходных данных у нас есть две таблицы по продажам и транспортным расходам:
Предположим, что перед нами стоит задача по каждой из них построить свою сводную и фильтровать их затем одновременно по городам общим срезом.
1 . Превращаем наши исходные таблицы в динамические "умные" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) и даём им имена таблПродажи и таблТранспорт на вкладке Конструктор (Design) .
2 . Загружаем по очереди обе таблицы в Модель с помощью кнопки Добавить в модель данных (Add to Data Model) на вкладке Power Pivot.
Напрямую связать эти таблицы в Модели не получится, т.к. пока Power Pivot поддерживает только тип связей "один-ко-многим", т.е. требует, чтобы в одной из таблиц не было дубликатов в столбце, по которому мы связываем. У нас же в обеих таблицах в поле Город присутствуют повторения. Так что потребуется создать ещё одну промежуточную таблицу-справочник со списком уникальных названий городов из обеих таблиц. Проще всего это сделать с помощью функционала надстройки Power Query, которая встроена в Excel начиная с 2016 версии (а для Excel 2010-2013 бесплатно скачивается с сайта Microsoft).
3 . Выделив любую ячейку внутри "умной" таблицы, загружаем их по очереди в Power Query кнопкой Из таблицы / диапазона на вкладке Данные (Data - From table/range) и затем в окне Power Query выбираем на Главной команды Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to. ) и вариант импорта Только создать подключение (Only create connection) :
4 . Соединяем обе таблицы в одну командой Данные - Объединить запросы - Добавить (Data - Combine queries - Append) . Совпадающие по названиям в шапке колонки встанут друг под друга (как столбец Город), а не совпадающие будут разнесены в разные столбцы (но для нас это не важно).
5 . Удаляем все столбцы, кроме колонки Город, щёлкнув по её заголовку правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) и затем удаляем все дубликаты названий городов, щёлкнув ещё раз правой кнопкой мыши по заголовку столбца и выбрав команду Удалить дубликаты (Remove duplicates) :
6 . Созданный список-справочник выгружаем в Модель Данных через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close&Load - Close&Load to. ) и выбираем вариант Только создать подключение (Only create connection) и - самое главное! - включаем флажок Добавить эти данные в модель данных (Add this data to Data Model) :
7 . Теперь можем, вернувшись в окно Power Pivot (вкладка Power Pivot - кнопка Управление), переключиться в Представление диаграммы (Diagram view) и связать наши таблицы продаж и траспортных расходов через созданный промежуточный справочник по городам (перетаскиванием полей между таблицами):
8 . Теперь можно создать все требуемые сводные таблицы по созданной модели с помощью кнопки Сводная таблица (Pivot Table) на Главной (Home) вкладке в окне Power Pivot и, выделив любую ячейку в любой сводной, на вкладке Анализ добавить срез кнопкой Вставить срез (Analyze - Insert Slicer) и выбрать для среза в списке поле Город в добавленном справочнике:
Теперь, нажав на знакомую кнопку Подключения к отчетам на вкладке Срез (Slicer - Report connections) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК - и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.
У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter) :
Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).
Основа
Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):
Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.
Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:
Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:
Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.
Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:
Добавляем макрос
"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!
Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:
Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:
Так все гораздо лучше, правда? :)
Реализация сложных запросов
Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:
Критерий | Результат |
гр* или гр | все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д. |
=лук | все ячейки именно и только со словом Лук, т.е. точное совпадение |
*лив* или *лив | ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д. |
=п*в | слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д. |
а*с | слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д. |
=*с | слова оканчивающиеся на С |
=. | все ячейки с текстом из 4 символов (букв или цифр, включая пробелы) |
=м. н | все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д. |
=*н??а | все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д. |
>=э | все слова, начинающиеся с Э , Ю или Я |
<>*о* | все слова, не содержащие букву О |
<>*вич | все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству) |
= | все пустые ячейки |
<> | все непустые ячейки |
>=5000 | все ячейки со значением больше или равно 5000 |
5 или =5 | все ячейки со значением 5 |
>=3/18/2013 | все ячейки с датой позже 18 марта 2013 (включительно) |
- Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
- Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
- Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
- Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).
Логические связки И-ИЛИ
Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND) :
Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".
Если нужно связать условия логическим оператором ИЛИ (OR) , то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:
Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:
В общем и целом, после "доработки напильником" из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.
Таблицы с огромным количеством числовой и текстовой информации часто встречаются во внутренних документах крупных и мелких компаний. Среди множества строк легко потерять важную информацию из виду. Разработчики из компании Microsoft понимают это, поэтому в программе Microsoft Excel присутствуют опции сортировки и фильтрации данных. Разобраться в них без подсказок достаточно сложно. Попробуем понять, как правильно настраивать сортировку и фильтры в обычных и сводных таблицах.
Обычная (простая) сортировка
Эта опция называется простой, потому что ее несложно использовать даже новичкам. В результате сортировки информация автоматически организуется в установленном порядке. Например, можно составить строки таблицы по алфавиту или упорядочить числовые данные от мелких к крупным.
Быстрая организация данных в таблице возможна благодаря набору инструментов «Сортировка и фильтр». Эта кнопка находится на вкладке «Главная», в правой ее части. Опции сортировки соответствуют формату выбранных ячеек.
- Проверим работу инструментов в действии. Для этого откроем существующую таблицу с большим количеством записей, выберем ячейку в столбце текстового формата и откроем меню сортировки и фильтра. Появятся две опции. Представим, что нужно рассортировать список имен по алфавиту. Нужно кликнуть по кнопке «Сортировка от А до Я». Записи распределятся в указанном порядке.
- Попробуем развернуть таблицу в обратную сторону – от конца к началу. Вновь необходимо открыть меню сортировки, теперь выбираем функцию «Сортировка от Я до А».
- Рассортировать числа тоже возможно, но опции для этого появляются только после выбора ячейки числового формата. Кликнем по одной из таких ячеек и откроем «Сортировку и фильтр». В меню появятся новые функции – «По возрастанию» и наоборот. Для дат заготовлены опции сортировки «От старых к новым» и в обратную сторону.
Пользовательская сортировка данных
Иногда необходимо построить строки таблицы в определенном порядке, который установлен условиями задачи. Например, возникает нужда в сортировке по нескольким параметрам, а не только по одному. В таком случае стоит обратить внимание на настраиваемую сортировку в Excel.
- Открываем меню «Сортировка и фильтр» и выбираем пункт «Настраиваемая сортировка» или «Пользовательская сортировка» (название зависит от версии программы).
- На экране появится окно сортировки. В основной части расположена строка из трех интерактивных элементов выбора. Первый определяет столбец, по которому будет оптимизирована вся таблица. Далее выбирается принцип сортировки – по значению, цвету ячеек, цвету шрифта или значкам. В разделе «Порядок» нужно установить способ сортировки данных. Если выбран столбец с текстом, в последнем списке будут варианты для текстового формата, то же и с другими форматами ячеек.
Дополнительная информация! В случае, если таблица начинается с шапки, нужно поставить галочку в графе, расположенной в правом верхнем углу экрана.
- Усложним упорядочивание данных, нажав кнопку «Добавить уровень». Под первой строкой появится такая же вторая. С ее помощью можно выбрать дополнительные условия сортировки. Заполняем его по тому же принципу, но в этот раз выберем другой формат ячеек.
Обратите внимание на пометку «Затем по». Она показывает, что приоритетными для сортировки таблицы являются условия первой строки.
Настройки можно изменить, выбрав одну из ячеек рассортированного диапазона и открыв окно пользовательской/настраиваемой сортировки.
Как настроить фильтр в таблице
Фильтры в Excel позволяют временно скрыть часть информации с листа и оставить только самое необходимое. Информация не пропадает навсегда – изменение настроек вернет ее на лист. Разберемся, как фильтровать строки электронной таблицы.
Внимание! Перейти к фильтрации можно также через вкладку «Данные». На ней располагается большая кнопка «Фильтр» с воронкой.
- В шапке таблицы появятся кнопки со стрелками, по одной на каждый столбец. С их помощью будет проводиться настройка фильтров.
- Нажмем одну из кнопок в том столбце, по которому будем фильтровать всю таблицу. Откроется меню, где располагаются настройки сортировки и фильтров, соответствующие формату данных в ячейках. Сортировать информацию в таблице можно по одному из значений в ячейках. Например, выберем одно из имен и оставим галочку только рядом с ним. Далее нужно нажать кнопку «ОК». В таблице останутся строки, соответствующие выбранному значению.
- Получившуюся таблицу можно дополнительно рассортировать по другим данным, например по дате. Открываем меню фильтрации в столбце с датами и убираем несколько галочек. Когда фильтр установлен, нажимаем «ОК». Количество строк в таблице снова уменьшится.
Как убрать фильтр в таблице
Не обязательно расставлять галочки обратно в меню, чтобы восстановить прежний вид таблицы. Воспользуемся инструментами Microsoft Excel, чтобы отменить результаты фильтрации – есть два способа сделать это.
- Открываем меню фильтра в столбце, где были установлены настройки. В нижней части окна находится кнопка «Очистить фильтр». Если нажать по ней, таблица вернется в прежний вид. Так нужно поступить со всеми столбцами, где применен фильтр.
- Другой метод подразумевает использование панели инструментов. Открываем меню «Сортировка и фильтр» на главной вкладке и находим пункт «Очистить». Он активен, когда диапазон ячеек подвергся фильтрации. Кликаем по этому пункту, и все настройки фильтров стираются.
- Теперь уберем кнопки фильтрации из шапки. Откроем меню сортировки и фильтров – если фильтр включен, рядом с соответствующим пунктом стоит галочка, в более старых версиях подсвечивается оранжевым. Нужно кликнуть по нему и снять эту галочку – кнопки исчезнут из всех ячеек шапки.
Как создать «умную таблицу»
Подключение опций сортировки и фильтрации к таблице можно совместить с выбором цветовой темы для нее. Такие таблицы называют «умными». Выясним, как сделать «умным» обычный диапазон ячеек.
Существует еще один метод создания «умной» таблицы:
Как сделать фильтр в Excel по столбцам
Таблицы Excel фильтруются только по строкам. В меню, которое появляется после нажатия на кнопку со стрелкой в шапке столбца, нельзя убрать все галочки, то есть нельзя скрыть целый столбец. Вся информация в диапазоне ячеек важна при фильтрации и сортировке, поэтому отфильтровать один или несколько столбцов не получится.
Сортировка по нескольким столбцам в Excel
Когда говорят о сортировке по нескольким столбцам, подразумевается, что для упорядочивания таблицы применяют усложненные настройки с указанием двух или более столбцов.
Важно! Количество уровней для сортировки ограничено только количеством столбцов или строк в таблице.
Автофильтр
Автоматическая фильтрация строк таблицы возможна с помощью меню фильтров. Эта функция позволяет установить более сложные настройки и создать уникальный фильтр. Набор автоматических фильтров меняется в зависимости от формата ячеек. Применяются текстовые и числовые фильтры.
Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.
- Снова открываем текстовые или числовые фильтры. В конце списка находится нужный пункт, по которому следует кликнуть.
- Заполняем поля – можно выбрать любой тип фильтра, походящий по формату ячеек, из списка и значения, существующие в диапазоне ячеек. После заполнения нажимаем кнопку «ОК». Если заполнение было непротиворечивым, настройки будут применены автоматически.
Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично.
Срезы
Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.
Создание срезов
- Кликаем по одной из ячеек таблицы – на панели инструментов появится вкладка «Конструктор», которую нужно открыть.
Обратите внимание! Если ваша версия Microsoft Excel старше 2013-го года, составить срез для обычной таблицы будет невозможно. Функция применима только к отчетам в формате сводных таблиц.
Срезы выглядят, как диалоговые окна со списками кнопок. Названия пунктов зависят от того, какие элементы таблицы были выбраны при создании среза. Чтобы отфильтровать данные, нужно кликнуть по кнопке в одном из списков. Фильтрация по нескольким диапазонам данных возможна, если нажать кнопки в нескольких срезах.
Форматирование срезов
Редактирование внешнего вида срезов и их взаимодействия с другими элементами возможно с помощью специальных инструментов. Попробуем изменить цветовую схему.
- Открываем вкладку «Параметры» и находим раздел «Стили срезов». В нем находятся темы для срезов разных цветов. Выбираем любую из них – цвет не повлияет на эффективность работы элемента. Сразу после клика по стилю срез приобретет указанные цвета.
- Также возможно изменить положение срезов на экране. Воспользуемся кнопками «Переместить вперед» и «Переместить назад» в разделе «Упорядочить». Необходимо выбрать один из срезов и нажать кнопку на панели инструментов. Теперь при перемещении по экрану срез будет оказываться поверх всех срезов или попадет под них.
- Удаление срезов – несложная операция. Выберите лишнее окно и нажмите клавишу «Delete» на клавиатуре. Срез исчезнет с экрана и перестанет влиять на фильтрацию данных в таблице.
Заключение
Читайте также: