Основы работы с фильтрами
Применение фильтров к таблице
Щелкните на значке
В данном меню с помощью флажков Вы можете отмечать те элементы, по которым необходимо фильтровать данные.
Совет 1
Если в таблице много значений, то используйте строку поиска. Для этого начните вводить в нее часть слова, которое Вам необходимо найти. Список вариантов будет автоматически сокращаться.
Минус этого способа в том, что можно отметить только одно значение или несколько значений, содержащих искомую фразу, но абсолютно разные значения найти не удастся. Т.е., например, сразу найти таким образом менеджеров по имени Сергей и Александр не получится, но можно найти все значения, содержащие «Серг»: Сергей, Сергеев, Сергиенко и т.п.
Совет 2
Например, Вам нужно отметить только 2 значения из нескольких десятков. Снимать флажок вручную с каждой позиции кроме нужных достаточно затратно по времени. Для ускорения этого процесса снимите флажок с пункта «(Выделить все)». При этом снимутся все остальные флажки. Теперь можно отметить только те пункты, которые Вам нужны.
MS Excel поддерживает множественные фильтры, т.е. фильтр сразу по нескольким столбцам.
Например, Вам необходимо найти все заказы менеджера Иванова от 18.01.2014.
Для начала щелкните на
Теперь щелкните на
Таблица примет следующий вид:
Аналогичным образом Вы можете продолжить фильтровать данные по столбцам «Описание», «Кол-во» и т.д.
Обратите внимание, что в столбцах, по которым был применен фильтр, значок
Таким образом Вы всегда будете знать по каким столбцам происходит фильтрация данных.
Отмена фильтра
Для того, чтобы снять все фильтры сразу, перейдите на вкладку «Данные» и нажмите на кнопку «Очистить».
Если необходимо снять фильтр только с одного столбца, оставив фильтры по другим, то щелкните на значке
Если необходимо полностью отказаться от фильтров в таблице, то перейдите на вкладку «Данные» и щелкните на кнопке «Фильтр». Она перестанет подсвечиваться, из строки с заголовками исчезнут значки
После
Текстовые фильтры
Щелкните на значке
- 2. Условия «больше» и «меньше» предполагают, что в таблице останутся значения, которые по алфавиту начинаются с более ранней или более поздней буквы. Например, если выбрать значение «Иванов» при опции «больше», то в таблице останутся только те ячейки, которые начинаются на букву «Й»(Картов, Йогуртов и т.п.). А при опции «меньше» — значения на букву «З» (Захаров, Букин).
- 3. Единственное отличие условий «больше или равно» и «меньше или равно» от предыдущего пункта в том, что в фильтр включает и выбранное значение.
- 4. Если необходимо найти все значения, которые начинаются на «Ива», то используйте условие «начинается с», а если хотите узнать, сколько в таблице значений, оканчивающихся на «рович», то выберите опцию «заканчивается на».
- 5. Соответственно, условия «не начинается с» и «не заканчивается на» предполагают, что Вам не надо отображать в таблице значения, содержащие искомую фразу.
- 6. При выборе условий «содержит» или «не содержит» можно указать любую фразу или сочетание букв, которые необходимо включить или исключить из фильтра. Отличие этого пункта от пунктов 1, 4 и 5, в том, что искомая фраза может находится в любом месте ячейки. Например, задав в качестве фильтра «Ива», в результате получим «Иванов Алексей», «Сергей Иваровский», «кривая» и т.п.
Числовые фильтры
Большинство условий те же самые, что и при текстовых фильтрах. Рассмотрим только новые.
1. Условие «между». При выборе данного условия, в появившемся окне сразу же устанавливаются нужные критерии, что облегчает Вам задачу:
Вам остается только ввести значения с клавиатуры или выбрать их из списка.
- 2. Условие «Первые 10». Данный пункт имеет следующие опции:
- Показать наименьшие или наибольшие значения.
- Сколько значений отобразить.
- В данном пункте требуется пояснение по второму значению: % от количества элементов. Например, у Вас в таблице 15 строк с числовыми значениями. При выборе 20% в таблице останется только 15/100*20 = 3 строки.
- 3. При выборе условий «Выше среднего» или «Ниже среднего» Excel автоматически высчитывает среднее арифметическое значение в столбце и фильтрует данные согласно критерию.
Единственное на что стоит обратить внимание, что в стандартном окне выбора условий фильтра появляется кнопка Календарь для облегчения ввода даты
Расширенный фильтр
Расширенный фильтр удобно использовать в тех случаях, когда результат отбора желательно поместить отдельно от основного списка. Для использования расширенного фильтра в отдельном диапазоне условий вводятся ограничения, в соответствии с которыми требуется произвести фильтрацию. По сравнению с автофильтром, в диапазоне условий можно задать более сложные условия отбора.
Диапазон условий должен состоять не менее чем из двух строк. В первой строке размещаются заголовки столбцов, во второй и последующих строках — соответствующие условия фильтра. Если не используются вычисляемые условия, то заголовки в диапазоне условий должны точно совпадать с заголовками столбцов списка.
Одно условие для одного столбца
Например, нужно из существующей таблицы выделить записи (строки) людей с ростом больше 180 см:
Сначала создаем Диапазон условий. Например:
Далее выделяем любую ячейку внутри таблицы и вызываем диалоговое окно Расширенного фильтра (на вкладке Данные в группе Сортировка и фильтр нажимаем кнопку Дополнительно:
Выбираем – скопировать результат в другое место, Исходный диапазон – оставляем (если мы предварительно выделили всю исходную таблицу), Диапазон условий – $D$24:$D$25, Поместить результат в диапазон – указываем любую ячейку в свободной области рабочего листа (где-нибудь ниже основной таблицы и диапазона условий):
Новая таблица будет нарисована в области с указанной ячейкой в левом верхнем углу. Если там находились какие-либо данные, то они будут перезаписаны. Отменить результат операции нельзя!
Результат фильтрации:
Несколько условий для одного столбца (логическое ИЛИ)
Например, в таблице с фамилиями в столбце, требуется отобрать все строки с фамилиями Симонов, Сидоров, Богданов. В этом случае используем следующий диапазон условий:
В диалоговом окне Расширенного фильтра указываем нужный диапазон условий (D38:D41) и новое место для отфильтрованной таблицы:
В результате получится таблица, содержащая только указанные фамилии:
Для числовых столбцов поступаем аналогично. Например, надо отобрать людей с ростом меньшим 160 см или большим 180 см. Для этого нужно задать следующий диапазон условий:
Несколько условий (логическое И)
Пусть необходимо отобрать людей с ростом, попадающим в диапазон от 160 см до 180 см, фамилия которых начинается на букву С. Для этого зададим следующий диапазон условий:
Таким же образом можно составлять более сложные условия отбора, комбинируя логические операции И и ИЛИ. Например:
Вычисляемые условия
В качестве условия можно использовать значение, являющееся результатом вычисления по формуле. При создании условия отбора с помощью формулы не используйте заголовок столбца в качестве заголовка столбца условий (либо оставьте условие отбора без заголовка, либо используйте произвольный текст).
Например, следующий диапазон условий используем для отбора строк, которые содержат в столбце Рост значения, превышающее среднее значение в столбце:
Формула, используемая для создания условия отбора, должна использовать относительную ссылку на первую ячейку в соответствующем столбце. Все остальные ссылки в формуле должны быть абсолютными ссылками, а формула должна возвращать результат ИСТИНА или ЛОЖЬ:
Результат фильтрации:
« Назад
Вперед »
Пример 1. Фильтрация данных по одному критерию (регион)
Предположим, у вас есть набор данных, показанный ниже, и вы хотите отфильтровать все записи только для США.
Ниже приведена формула ФИЛЬТРА, которая сделает это:
= ФИЛЬТР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "США")
В приведенной выше формуле набор данных используется в качестве массива, а условие — $ B $ 2: $ B $ 11 = «США».
Это условие заставит функцию FILTER проверять каждую ячейку в столбце B (ту, которая имеет область), и будут отфильтрованы только те записи, которые соответствуют этому критерию.
Кроме того, в этом примере у меня есть исходные данные и отфильтрованные данные на одном листе, но вы также можете иметь их на отдельных листах или даже в книгах.
Функция фильтра возвращает результат, который является динамическим массивом (что означает, что вместо возврата одного значения она возвращает массив, который распространяется на другие ячейки).
Чтобы это работало, вам нужно иметь область, где результат будет пустым. В любой из ячеек в этой области (E2: G5 в этом примере) что-то уже есть, функция выдаст вам ошибку #SPILL.
Кроме того, поскольку это динамический массив, вы не можете изменить часть результата. Вы можете удалить весь диапазон, в котором есть результат, или ячейку E2 (где была введена формула). В обоих случаях будет удален весь результирующий массив. Но вы не можете изменить какую-либо отдельную ячейку (или удалить ее).
В приведенной выше формуле я жестко запрограммировал значение региона, но вы также можете поместить его в ячейку, а затем ссылаться на эту ячейку, которая имеет значение региона.
Например, в приведенном ниже примере у меня есть значение региона в ячейке I2, и оно затем упоминается в формуле:
= ФИЛЬТР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)
Это делает формулу еще более полезной, и теперь вы можете просто изменить значение региона в ячейке I2, и фильтр автоматически изменится.
У вас также может быть раскрывающийся список в ячейке I2, где вы можете просто сделать выбор, и он мгновенно обновит отфильтрованные данные.
Примеры функции filter()
Фильтр нечетных чисел
В этом примере в качестве итерируемого объекта — список числовых значений
Копировать
И есть функция, которая отфильтровывает нечетные числа. Она передается в качестве первого аргумента вызову .
Копировать
Теперь соединим эти части и посмотрим на финальный код.
Копировать
Вот на что стоит обратить внимание в примере:
- Функция filter() возвращает , а для проверки типа данных использовалась ;
- Конструктор был вызван для конвертации объекта в список Python.
После запуска примера выйдет следующий результат:
Тип объекта out_filter: <class ‘filter’>Отфильтрованный список:
Он показывает только четные числа, отфильтровывая нечетные.
Фильтр повторяющихся значений из двух списков
Функцию можно использовать для получения разницы двух последовательностей. Для этого нужно отфильтровывать повторяющиеся элементы.
Предположим, что существует два списка строк.
Копировать
Видно, что в них есть одинаковые названия языков программирования. Задача заключается в том, чтобы написать функцию, которая бы проверяла повторяющиеся значения.
Копировать
Теперь соберем все это вместе.
Копировать
После выполнения результат будет следующий:
Отфильтрованный список:
Как и ожидалось, код вывел разницу двух списков. Однако это лишь примеры работы функции.
Зачем нужны фильтры в таблицах Эксель
А затем, чтобы иметь возможность быстро отбирать только необходимые вам данные, скрывая ненужные строки таблицы. Таким образом фильтр позволяет без удаления строк таблицы Эксель просто временно скрыть их.
Скрытые при помощи фильтра строки таблицы не исчезают. Можно условно представить, что их высота становится равной нулю (про изменение высоты строк и ширины столбцов я ранее рассказывал). Таким образом, остальные строки, не скрытые фильтром, как бы «склеиваются». То, что получается в результате, и есть таблица с наложенным фильтром.
Внешне таблица с фильтром в Excel выглядит так же, как и любая другая, но в её верхней части в каждой колонке появляются специальные стрелочки. Пример таблицы с наложенным фильтром вы можете видеть на картинке ниже.
Теперь давайте посмотрим, как собственно добавить фильтры в таблицу.
Виды фильтров в Excel
Фильтрация — это выбор необходимых данных из списка для последующей работы с ними. Результатом этой процедуры будут определенные строки, которые подходят под критерии отбора. Остальные записи временно скрываются и не используются до тех пор, пока пользователь не отключит фильтр. С отобранными данными можно выполнять стандартные действия: редактирование, форматирование, печать, построение графиков, диаграмм и т.д.
Всего в Экселе есть 2 способа фильтрации: автофильтр и расширенный фильтр. Запустить их можно через панель меню, щелкнув «Данные — Фильтр». С помощью первого варианта осуществляется быстрый отбор необходимой информации с простыми критериями поиска. В режиме автофильтра заглавная строка таблицы в каждой колонке будет содержать кнопку со стрелочкой, кликнув на которую можно указать критерии для отбора. Для каждого столбца можно установить свои настройки. В данном режиме можно установить следующие параметры:
- Сортировка по возрастанию или по убыванию.
- «Все» — Excel покажет (восстановит) все строки.
- «Первые 10» — Excel отобразит первые 10 записей. При выборе этого пункта откроется новое окно, в котором можно указать число записей, выбрать, какие из них показывать (наибольшие или наименьшие), а также установить ограничение на количество отображаемых записей.
- «Условие» — здесь пользователь может самостоятельно создать 2 критерия отбора данных, объединяя их простыми операторами И, ИЛИ.
- Любой из элементов. Здесь можно выбрать любое значение, которое находится в колонке. Например, если столбец состоит из наименований товаров, то в перечне элементов будут указаны абсолютно все товары. Пользователь может указать любой из них.
-
«Пустые» и «Непустые» — Excel отобразит пустые (или непустые) ячейки. Данная опция появляется только в том случае, если в столбце есть незаполненные поля.
Чтобы удалить фильтр из одной колонки, нужно кликнуть в перечне элементов на пункт «Все». Если нужно отменить его для всей таблицы, необходимо выбрать в панели меню пункты «Данные — Фильтр — Отобразить все». Удаление автофильтра осуществляется так же, как и его запуск.
Ограничения
Из-за особенностей реализации имеются ограничения в справочнике групп:
- Общее количество элементов в каждой подгруппе — не более 99Формулы поддерживают два знака на каждую группу.
- Количество уровней вложенности — не более 4Задано в ячейке G1, можно увеличить
- Группы должны иметь уникальные наименования
- Заголовки полей фильтрации групп в таблице и в служебном столбце справочника должны быть одинаковые.В примере — Group.
Основная причина ограничений в размере поля FilterID, там должно храниться целое число. В каждом конкретном случае практической реализации можно настроить пример на расширение ограничений, например, увеличить максимальное количество групп, уменьшив лимит уровней вложенности.
Смотри также
» Условное форматирование
Форматирование ячеек Excel предназначено для представления информации в удобном наглядном виде, что повышает уровень понимания информации. Какие-то простые одноразовые вычисления можно производить в файлах, совсем не используя форматы ячеек – результат от этого не изменится. Если же предполагается показывать или передавать отчет другим пользователям, то без форматирования не обойтись.Стандартное простое форматирование ячеек электронной таблицы Excel с использованием цвета фона, шрифта и рамок осваивается пользователями очень быстро. На этом уровне прогресс в оформлении отчетов в большинстве случаев останавливается. Зачастую даже опытные пользователи Excel, способные написать сложную формулу, вообще не используют интерфейсное средство «Условное форматирование». Между тем, оно, с нашей точки зрения,…
» Обработка больших объемов данных. Часть 2. Интерфейс
В статье систематизируются простые приемы обработки больших объемов данных при помощи стандартных методов интерфейса Excel. Информация будет полезна в первую очередь начинающим пользователям.
» Использование справочников
При построении сложных экономических моделей обычно имеется набор данных, используемых в нескольких таблицах как в качестве параметров расчетов, так и для построения отчетных форм. Создание и использование отдельных таблиц-справочников позволяет ускорить процесс разработки модели, повышает надежность расчетов, а также дает возможность применять специальные средства интерфейса Microsoft Excel.
Показано три варианта реализации списков для выбора значения.
Фильтр по географическому расположению
Фильтр по географическому расположению – это инструмент, который позволяет отфильтровать данные в соответствии с определенным географическим регионом или местоположением. Такой фильтр может быть полезен для анализа данных, связанных с конкретным регионом или для создания отчетов о деятельности организации в разных частях мира.
При использовании фильтра по географическому расположению данные, которые не соответствуют заданным критериям, будут отфильтрованы. Это означает, что только данные, связанные с выбранным географическим регионом или местоположением, будут показаны или использованы для анализа.
Фильтр по географическому расположению может быть настроен на основе различных параметров, таких как страна, регион, город или даже конкретные координаты. Например, при анализе данных о продажах товаров по регионам можно использовать фильтр, чтобы отобразить только данные о продажах в определенной стране или регионе.
Использование фильтра по географическому расположению может помочь в принятии более обоснованных решений на основе данных, связанных с определенным местоположением. Например, при планировании маркетинговой кампании можно использовать фильтр, чтобы узнать, какие регионы требуют большего внимания или где находятся наиболее перспективные рынки.
Пример использования фильтра по географическому расположению:
Регион
Продажи (в тыс. долларов)
Северная Америка
500
Европа
400
Азия
300
Австралия
200
В этом примере фильтр по географическому расположению может быть настроен на отображение только данных о продажах в Северной Америке. Как результат, остальные строки таблицы (Европа, Азия, Австралия) будут отфильтрованы, и на экране будет показана только информация о продажах в Северной Америке.
Фильтр по значениям n.
Верхние и нижние значения n в настоящее время доступны в фильтрах карточек для линейных диаграмм, столбчатых диаграмм, пузырьковых диаграмм, кольцевых диаграмм и диаграмм-графиков. Если применяется фильтр по значениям n, затем происходит переключение на неподдерживаемую визуализацию, фильтр удаляется.
Фильтры по значениям n не доступны для . Если удаленные слои объектов не поддерживают фильтры по значениям n, вы можете скопировать слой в свою рабочую книгу и применить фильтр к копии.
Фильтрация с помощью значений n позволяет выбирать нижние или верхние значения на диаграмме, в зависимости от категории и суммарной статистики.
Выполните следующие шаги для фильтрации по значениям n:
Фильтрация по двум отдельным критериям. Как правильно ее сделать?
Расширенная фильтрация позволяет использовать различные формулы имеющейся электронной таблицы. Если вам необходимо разграничить ее сначала по одному критерию, потом по другому (отдельно друг для друга), необходимо:
- Создать место для ввода параметра фильтрования. Удобнее всего оставлять это место над основной таблицей и не забывать копировать шапку (названия столбцов), чтобы не запутаться, в какую колонку вводить этот критерий.
- Ввести нужный показатель для фильтрации. Например, все записи, чьи значения столбца больше 1000 (> 1000).
- Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» выбрать пункт «Дополнительно».
- В открывшемся окошке указать диапазоны рассматриваемых значений и ячейку со значением рассматриваемого критерия.
- Нажать на Ок. После этого будет выведена отфильтрованная по заданному критерию таблица.
- Скопировать результат разграничения. Вставить отфильтрованную таблицу куда-нибудь в сторону на том же листе Excel. Можно воспользоваться другой страницей.
- Выбрать «Очистить». Данная кнопка находится во вкладке «Данные» в разделе «Фильтрация и сортировка». После ее нажатия отфильтрованная таблица вернутся в первоначальный вид. И можно будет работать с ней.
- Далее необходимо снова выделить свободное место для таблицы, которая будет отфильтрована.
- Потом нужно скопировать шапку (названия столбцов) основного поля и перенести их в первую строчку освобожденного под отфильтрованную структуру места.
- Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» выбрать «Дополнительно».
- В открывшемся окошке выбрать диапазон записей (столбцов), по которому будет проводиться фильтрация.
- Добавить адрес ячейки, в которой записан критерий разграничения, например, «город Одесса».
- Нажать на Ок. После этого произойдет фильтрация по значению «Одесса».
- Скопировать отфильтрованную таблицу и вставить ее либо на другой лист документа, либо на той же странице, но в стороне от основной.
- Снова нажать на «Очистить». Все, готово. Теперь у вас имеются три таблицы. Основная, отфильтрованная по одному значению (>1000), а также та, что отфильтрована по другому значению (Одесса).
Фильтры
Применение автофильтра ограничивается тем, что его можно использовать только для фильтрации таблиц на основании данных в столбцах, причем не более чем по двум условиям. Фильтры же лишены этих ограничений и могут использоваться для полноценного отбора данных.
Фильтр — это сохраненный набор условий и значений фильтрации, согласно которому происходит отбор информации для отображения в представлении MS Project. Фильтры содержат условия на значения в столбцах, и поэтому (так же, как и таблицы) делятся на два типа: относящиеся к ресурсам и задачам. Соответственно, применить фильтр можно только к таблице одного типа с ним.
MS Project содержит набор предопределенных фильтров, которые позволяют быстро отбирать из таблиц наиболее необходимые данные. Список этих фильтров с описанием каждого из них можно просмотреть с помощью ссылки Доступные фильтры на странице Полезные страницы справки MS Project 2002 в индексном файле.
Работа с фильтрами осуществляется через подменю Project > Filtered for: (Проект > Фильтр). В пункте Filtered for: (Фильтр) (рис. 4.11, файл 1.mpp) указано название выбранного в данный момент фильтра, а при наведении на него курсора открывается подменю со списком фильтров, которые можно использовать для текущей
таблицы. В подменю отображается неполный список фильтров. Полный список фильтров можно получить, щелкнув на пункте More Filters (Другие фильтры).
Рис. 4.11. Работа с фильтрами осуществляется через подменю Project > Filtered for: (Проект > Фильтр)
Помимо меню для быстрого доступа к полному списку фильтров можно воспользоваться панелью форматирования (рис. 4.12, файл 5.mpp). Как вы можете заметить, первым пунктом в списке (и на панели, и в меню) идет фильтр All Tasks1 (Все задачи), при использовании которого отображаются все записи таблицы. Этот фильтр включен по умолчанию. (При открытой таблице с информацией о ресурсах фильтр будет называться All Resources (Вce ресурсы)).
Рис. 4.12. Панель форматирования позволяет получить быстрый доступ к фильтрам
При выборе любого другого фильтра данные в таблице отбираются в соответствии с условиями фильтрации. Например, если включить фильтр, отбирающий только критические задачи (пункт Critical (Критические задачи)), то число строк в таблице сразу уменьшится (рис. 4.13, файл 5.mpp).
Рис. 4.13. После включения фильтра, отбирающего критические задачи (этот фильтр выбран на панели форматирования), число строк, отображаемых в таблице, заметно уменьшилось
Фильтрация может выполняться и в другом режиме, когда строки таблицы, удовлетворяющие критериям, выделяются цветом, а остальные продолжают отображаться. Для того чтобы выделить цветом соответствующие условиям фильтра строки, нужно выбрать команду меню Project > Filtered for > More Filters (Проект > Фильтр > Другие фильтры), а затем в открывшемся диалоговом окне со списком всех фильтров (рис. 4.14) выбрать нужный фильтр и нажать кнопку Highlight (Выделить). Переключатели вверху диалогового окна позволяют переключаться между списками фильтров разных типов.
Выбранный в списке фильтр можно отредактировать, а также скопировать и создать на его основе новый фильтр, нажав соответствующую кнопку диалогового окна. Нельзя изменить лишь три фильтра: All Tasks/All Resources (Все задачи/Все Ресурсы) и Using Resource (Использование ресурса).
Фильтры контекста
Контекст фильтра работает на уровне модели. Когда вы помещаете фильтр в столбец, фильтр влияет на столбец, таблицу, которой принадлежит столбец, и все таблицы, к которым можно получить доступ через связи. По умолчанию фильтр распространяется от одной стороны отношения ко многим сторонам. Тем не менее, активируя двунаправленные отношения, вы можете распространять контекст фильтра в обоих направлениях: от одной стороны к многим сторонам и от многих сторон к одной стороне.
Направление распространения контекста фильтра отражается стрелкой в середине любого отношения в представлении схемы Power BI.
По умолчанию распространение происходит от одной стороны ко многим сторонам, что означает, что фильтр в таблице Date распространяется на таблицу Sales , и то же самое применяется от Product к Sales . Но тогда фильтр по продажам не распространяется ни на продукт , ни на дату .
У вас есть возможность изменить способ распространения контекста фильтра, активировав двунаправленные отношения. Если бы отношение между Product и Sales было двунаправленным, то фильтр по Sales распространялся бы на Product .
Написание кода автофильтра
Вот пошаговые инструкции по написанию строки кода для автофильтра.
Шаг 1: Ссылка на диапазон или таблицу
Метод AutoFilter является частью объекта Range. Поэтому мы
должны ссылаться на диапазон или таблицу, к которым применяются фильтры на
листе. Это будет весь диапазон, к которому применяются фильтры.
Следующие примеры включают / отключают фильтры в диапазоне B3: G1000 на листе автофильтра.
Вот пример использования таблиц Excel.
Метод AutoFilter имеет 5 необязательных параметров, которые
мы рассмотрим далее. Если мы не укажем ни один из параметров, как в приведенных
выше примерах, метод AutoFilter включит / выключит фильтры для указанного
диапазона. Это переключение. Если фильтры включены, они будут выключены, и
наоборот.
Диапазоны или таблицы?
Фильтры работают одинаково как для обычных диапазонов, так и для таблиц
Excel.
Я отдаю предпочтение методу использования таблиц, потому что
нам не нужно беспокоиться об изменении ссылок на диапазон при увеличении или
уменьшении таблицы. Однако код будет одинаковым для обоих объектов. В остальных
примерах кода используются таблицы Excel, но вы можете легко изменить это для
обычных диапазонов.
5 (или 6) параметров автофильтра
Метод
AutoFilter имеет 5 (или 6) необязательных параметров, которые используются для
указания критериев фильтрации для столбца. Вот список параметров.
Страница справки MSDN
Мы можем использовать комбинацию этих параметров, чтобы
применять различные критерии фильтрации для разных типов данных. Первые четыре
являются наиболее важными, поэтому давайте посмотрим, как их применять.
Шаг 2: Параметр поля
Первый параметр — это Field. Для параметра Field мы указываем число, которое является номером столбца, к которому будет применяться фильтр. Это номер столбца в диапазоне фильтра, который является родителем метода AutoFilter. Это НЕ номер столбца на рабочем листе.
В приведенном ниже примере поле 4 является столбцом
«Продукт», поскольку это 4-й столбец в диапазоне фильтра / таблице.
Фильтр столбца очищается, когда мы указываем только параметр
Field, а другие критерии отсутствуют.
Мы также можем использовать переменную для параметра Field и
установить ее динамически. Я объясню это более подробно ниже.
Шаг 3: Параметры критериев
Существует два параметра, которые можно использовать для указания фильтра Критерии, Criteria1 и Criteria2 . Мы используем комбинацию этих параметров и параметра Operator для разных типов фильтров. Здесь все становится сложнее, поэтому давайте начнем с простого примера.
Это то же самое, что выбрать один элемент из списка флажков в раскрывающемся меню фильтра.
Общие правила для Criteria1 и Criteria2
Значения, которые мы указываем для Criteria1 и Criteria2,
могут быть хитрыми. Вот несколько общих рекомендаций о том, как ссылаться на
значения параметра Criteria.
- Значением критерия является строка, заключенная в кавычки. Есть несколько исключений, когда критерии являются постоянными для периода времени даты и выше / ниже среднего.
- При указании фильтров для отдельных чисел или дат форматирование чисел должно соответствовать форматированию чисел, применяемому в диапазоне / таблице.
- Оператор сравнения больше / меньше чем также включен в кавычки перед числом.
- Кавычки также используются для фильтров для пробелов «=» и не пробелов «<>».
Шаг 4: Параметр оператора
Что если мы хотим выбрать несколько элементов из
раскрывающегося списка фильтров? Или сделать фильтр для диапазона дат или
чисел?
Для этого нам нужен Operator . Параметр Operator используется для указания типа фильтра, который мы хотим применить. Он может варьироваться в зависимости от типа данных в столбце. Для Operator должна использоваться одна из следующих 11 констант.
Вот ссылка на страницу справки MSDN, которая содержит список констант для перечисления XlAutoFilterOperator.
Operator используется в сочетании с Criteria1 и / или Criteria2, в зависимости от типа данных и типа фильтра. Вот несколько примеров.
Это основы написания строки кода для метода AutoFilter. Будет сложнее с различными типами данных.
Итак, я привел много примеров ниже, которые содержат большинство комбинаций критериев и операторов для разных типов фильтров.
Итоги.
При вводе условий поиска можно использовать общепринятые знаки подстановки и математические знаки:
- * — любое количество любых символов
- ? – один любой символ
- = — равно
- < — меньше
- > — больше
- <= — меньше или равно
- >= — больше или равно
- <> — не равно
Расширенный фильтр в Excel гибок и информативен при использовании. Критерии отбора всегда перед глазами пользователя в виде таблицы критериев отбора, что, несомненно, очень удобно.
Если записей очень много, а в реальных базах данных уникальных записей тысячи, то отфильтровывать нужные становится проблематично с помощью автофильтра, так как в выпадающем списке приходится пользоваться прокруткой. Расширенный фильтр лишен этого недостатка.
«Поковыряйтесь» самостоятельно в возможностях этого инструмента — он стоит того, чтобы в нем разобраться! Возросшая эффективность вашей работы многократно перекроет затраты времени потраченного на изучение!
Продолжение темы хранения и управления большими объемами информации — в следующих статьях цикла.