Таблицы в Excel могут содержать большое количество информации. Для комфортной работы с массивами данных применяются обычный и расширенный фильтры. Однако их использование иногда доставляет ряд трудностей начинающим пользователям.
Назначение и существующие виды
Под фильтрацией понимают выделение необходимых данных при одновременном скрытии ненужных. Таким образом, облегчается работа с большим количеством информации в Excel. Не нужно искать по всей таблице «заветные» строки, достаточно задать требуемые параметры в фильтре, и они «подсветятся». Существуют два вида сортировки: автофильтр и расширенный фильтр.
Пользовательский или расширенный фильтр
Установка фильтра
Название опции подчеркивает более широкие возможности по сравнению со стандартным вариантом. Перед применением необходимо создать поле с условиями. Обычно для этого копируются заголовки нужной таблицы и вставляются сверху нее. Между строками с условиями и исходными данными должна быть минимум одна пустая строка.
После этого включается фильтр в Excel:
- На панели вкладок нажать на «Данные».
- В окне «Сортировка и фильтр» кликнуть по «Дополнительно».
- Появится меню «Расширенный фильтр» с различными настройками.
Фильтр инициализирован. Настройки и условия фильтрации рассматриваются ниже.
Настройки и условия
Есть несколько настроек в меню фильтрации:
- По умолчанию в появившемся окне отмечен подпункт «Фильтровать список на месте» или «Filter the list, in-place» в англоязычной версии. Эта опция позволяет провести операцию в той же локации, что и исходная таблица.
- Строка «Исходный диапазон» или «List range». Здесь нужно проставить координаты фильтруемой таблицы, включая заголовки. Начальная координата — первая ячейка, конечная — последняя.
- «Диапазон условий» или «Criteria range». Указывается местоположение условий фильтрования. Условия задания координат аналогичны предыдущему пункту.
Если в критерии отбора будет включена пустая строка, то этот фильтр не будет действовать. Будут выбраны все данные, поэтому нужно внимательно указывать диапазон.
- Если нужно оставить только неповторяющиеся строки, то нужно отметить ячейку «Только уникальные записи» или «Unique records only».
Кроме задания точных критериев отбора, в расширенном режиме возможна выборка, путём создания приблизительных значений, использования формул и условий. Основные операторы для создания подобных запросов следующие:
Критерий | Результат |
---|---|
пр* или пр | все записи, которые начинаются с «Пр», т.е. прима, продукт и аналогичные данные |
=диван | все данные, имеющие точное совпадение со словом «диван» и только |
*лив* или *лив | слова, содержащие слог «лив», например, оливки, Ливония, прилив. |
=т*в | надписи, начинающиеся с «Т» и заканчивающиеся на «В» — Титов, Тургенев |
п*с | схожее с предыдущим пунктом написание критерия, но здесь буква С не должна быть обязательно в конце слова, а может находиться в любом месте после П, например, просо, простой |
=*г | записи, оканчивающиеся на Г |
=??? | слова, где есть 3 символа, включая пробелы и цифры |
=а????с | выбор данных, состоящих из 6 символов и имеющих в начале букву «А», а в конце букву «С», такие как «ананас», «адонис» |
=*л??а | слова, которые заканчиваются на «а», а четвёртой буквой с конца в слове является «л», например, «малина». |
<=В | записи, начинающиеся с букв «А», «Б», «В» |
<>*е* | слова, которые не имеют букву «Е» |
<>*вна | все слова, исключая те, которые заканчиваются на «вна» |
= | выборка всех пустых ячеек |
<> | отбор несвободных «клеток» |
>=199 | отбираются данные со значением большим или равным 199 |
10 или =10 | показатели со значениями равными 10 |
>=2/28/2018 | события с датой позже 28 февраля 2018 года (включительно) |
<=12/03/2019 | Данные с датой раньше, чем 12 марта 2019 года |
Нужно помнить следующее при составлении условий:
- * — может означать любое количество различных знаков;
- ? — один символ;
- дата ставится в североамериканском стиле, то есть мм/дд/гггг.
Базовыми условиями пользовательской фильтрации в Excel являются логические «И( AND)» и «ИЛИ(OR)». Все установки отбора используют эти операторы в различных вариациях.
Логическое «И» получается путем размещения условий выборки в одной строке на разных или одинаковых столбцах. При такой конфигурации будут выбраны элементы, отвечающие одновременно всем критериям, указанным в строке фильтра.
На рисунке показана работа оператора «И». Будут выбраны товары с наименованием «Бананы», которые поставлялись в III квартале года в Москве, в гипермаркеты «Ашан». Предметы, не отвечающие вышеуказанным условиям, показаны не будут.
Если нужно применить условие «И» к одному столбцу несколько раз, то нужно создать нужное количество таких столбцов в диапазоне условий и применить к ним оператор.
На примере показано двойное использование логического «И» в столбце «Дата». В результате такого применения будут выбраны все события между 1 марта и 31 мая 2013 года.
Логическое «ИЛИ» получается при размещении критериев отбора на разных строках диапазона условий. В этом случае будут показаны элементы, удовлетворяющие любому из пунктов выбора.
На рисунке показана совместная работа операторов «И» и «ИЛИ». После установки таких параметров, будут выбраны строки с надписями «Персик» или «Лук». Причём для «Персик» необходимым является выполнение еще двух критериев: наличие города Москва и менеджера Волина. Для «Лук» нужен только III квартал и город Самара.
Действенным методом в расширенном фильтре является использование формул для формирования критерия выборки. Алгоритм прост — формула проверяет ячейки на «Истину» или «Ложь» и показывает строки с истинным результатом. При составлении задания с помощью формулы нужно учитывать следующее:
- формулы нужно вставлять в пустые строки, не содержащие заголовки «таблицы» или отличающиеся от них;
- формула должна начинать «работать» с первых ячеек после заголовка, чтобы не пропустить какое-либо значение из таблицы. Поэтому ссылка на применение формулы начинается с первой строки в любом столбце;
- ссылка на проверку по формуле должна быть относительной, вида B5, в отличие от абсолютной, которая пишется в виде $B$5. При статичной или абсолютной ссылке проверяться будет только указанная ячейка, относительная дает старт на проверку всех ячеек, начиная с первой.
На рисунке показан пример использования формулы, которая выделяет товары, встречающиеся 1 раз.
Здесь в столбце H, в первой ячейке введен текст для объяснения формулы. Сама формула указана в ячейке H2 и выглядит следующим образом:
=СЧЁТЕСЛИ(Лист1!$A$8:$A$83;A8)=1
Здесь $A$8:$A$83 — абсолютная ссылка, указывающая диапазон действия формулы. A8 — относительная ссылка. Она показывает номер ячейки, с которой начнется проверка по формуле. Результаты, отвечающие условию «ИСТИНА», будут показаны. Таким образом, все товары, которые представлены в единичном экземпляре отобразятся.
Применение
Алгоритм применения пользовательского фильтра следующий:
- Создать пустую таблицу с теми же заголовками, что и в редактируемой базе.
- Вписать критерии выборки.
- Инициализировать фильтр, описанным выше способом.
- После открытия окна с настройками, ввести необходимые показатели и подтвердить.
- После применения, в базе данных будут показаны только строки, удовлетворяющие условиям отбора.
Перемещение результатов отбора в другую таблицу
Для того чтобы данные фильтрации отобразились в ином диапазоне нужно сделать следующее:
- в окне настроек отметить пункт «скопировать результат в другое место»;
- после этого станет активной строка «поместить результат в другое место»;
- указать координаты новой таблицы, где отобразятся итоги отбора;
- после применения выборки, первоначальная таблица останется без изменений, а итоги будут выведены в новую базу.
Удаление пользовательского фильтра
Сбросить функцию можно несколькими способами:
- Если результаты были выведены без перемещения, то удалить их можно, нажав на вкладку «Данные» и в подменю «Сортировка и фильтр» кликнуть по пункту «Очистить».
- Стандартным нажатием клавиш Ctrl + Z, отменяющим предыдущие действия.
- Включить автофильтр, в результате чего, все результаты отбора будут сброшены.
Стандартный фильтр
Кроме расширенного отбора, в простых случаях, удобнее пользоваться автофильтром.
Запуск
Включить стандартную функцию выборки данных можно тремя способами:
- На главной панели нажать на пункт «Данные», в подменю «Сортировка и фильтр» кликнуть по иконке с надписью «Фильтр».
- Выбрать пункт «Главная», в подсистеме «Редактирование» нажать на «Сортировка и фильтр». В появившемся окне выбрать «Фильтр».
- С помощью нажатия кнопок Ctrl + Shift + L.
В итоге на заголовке списка появятся стрелки, с помощью которых можно установить критерии отбора.
Параметры выбора
Существуют несколько параметров исключения.
Синхронизация по дате
При наличии столбца с датами, можно произвести сортировку информации этого типа. Для этого нужно нажать на стрелку в соответствующем заголовке. В появившемся меню много интуитивно понятных пунктов. При выборе «Выделить все», помечаются все данные столбца, и фильтрация применяется к ним. Также можно выбрать определенные значения, убрав галочку с предыдущего пункта и поставив её перед нужной позицией.
В качестве примера, можно произвести выборку событий между двумя датами: 1 июня 2014 года и 31 декабря 2014 года. Для этого:
- выбрать в контекстном меню надпись «После…»;
- откроется подменю, в нем для функции «После…» выбрать дату 01.06.2014;
- выбрать логическое «И»;
- в нижней строке «До» выбрать вторую дату и подтвердить.
Результатом будет показ информации между 1 июня и 31 декабря 2014 года.
Текстовой отбор
Также как и для значений даты, можно произвести выборку данных с определенным текстом или сигнальным словом. В рассмотренной выше таблице текст содержится в столбце «Наименование». Кликнуть по стрелке на нем. Откроется окно с настройками фильтра по слову.
Все действия понятны и аналогичны действию с датами. Для фильтрации данных у которых есть символ «2», можно выполнить следующие шаги:
- кликнуть по условию «Содержит…»;
- в следующем окне выбрать «И» и для критерия «Содержит…» указать «2»;
- подтвердить;
- получится таблица, содержащая цифру «2» в столбце «Наименование».
Числовой критерий
Также можно произвести сортировку по числовым данным. Для этого нажать на столбец, содержащий числовую информацию и выбрать «Числовые фильтры».
Открывшееся подменю содержит многочисленные параметры для выборки. Они не сложны и понятны при взаимодействии с ними. Отдельно можно упомянуть опцию «Первые 10». Исходя из названия, критерий выберет 10 самых больших или, если поменять направление, 10 наименьших чисел.
Однако результат может отличаться от ожидаемого. Так как среди значений могут быть повторяющиеся числа, в итоге может быть больше отфильтрованных значений, чем 10.
Изменение строки
Иногда бывает нужным изменить порядок столбцов в строке. Для этого применяется сортировка по строкам, включающая следующие действия:
- Выбрать во вкладке «Данные» или «Data», пункт «Сортировать» или «Sort».
- Появится окно с настройками «Сортировка» или «Sort». Если строка содержит заголовки, то нужно отметить пункт «Мои данные содержат заголовки» или «My data has headers». В противном случае ставить галочку не нужно. После этого кликнуть по пункту «Параметры» или «Options».
- В подменю «Параметры» отметить, как будут меняться столбцы — сверху вниз (Sort top to bottom) или слева направо (Sort left to right). Если изменяется порядок следования в строке, то нужно выбрать «Слева направо».
- Далее в окне «Sort» указать строку в которой будет изменен порядок следования столбцов и указать в каком порядке будет идти перестроение — от А до Я (A to Z) или наоборот.
После подтверждения произойдет фильтрация по строкам.
Отключение фильтра
Для удаления выборки со столбца необходимо нажать на стрелку и кликнуть по надписи «Удалить фильтр из столбца».
Для удаления всех критериев отбора на вкладке «Данные» кликнуть по «Фильтр».