Как выполнить фильтрацию с помощью формулы в Excel

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

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

Синтаксис

=IFERROR(ИНДЕКС(диапазон списка,SMALL(IF(критерии= диапазон критериев, СТРОКА (диапазон критериев),»»),ROW()-СТРОКА (возвращает ячейку заголовка))),»»)

Шаги

  1. Выберите диапазон ячеек, которые будут заполнены отфильтрованными значениями
  2. Запустите формулу с помощью = IFERROR( функция для возврата пустой строки при возникновении ошибки)
  3. Продолжите с ИНДЕКСОМ(
  4. Выберите или введите ссылку на диапазон, содержащую ваш исходный список B:B,
  5. Продолжите с функцией SMALL(, которая предоставляет индексы строк ячеек
  6. Затем используйте IF(, чтобы вернуть массив, содержащий числа и пустые строки
  7. Используйте уравнение для фильтрации $ E $ 3=C: C, ячейка критериев должна быть абсолютной ссылкой
  8. Продолжите с ,ROW(C:C),»»), которая обеспечит условия TRUE / FALSE для функции IF
  9. Введите в ROW()-СТРОКА(E5), чтобы сгенерировать инкрементное число для массива из функции IF
  10. Введите )),»») и нажмите Ctrl + Shift + Enter, чтобы заполнить формулу массива

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

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

Наш пример возвращает значения из столбца B путем поиска значения ячейки E3 в столбце C. Это условие приводит к логическому тестированию,

$E $ 3=C:C

Этот тест возвращает массив логических значений (TRUE и FALSE). Например, если значение ячейки E3 равно «I», логический тест возвращает массив, подобный приведенному ниже,

{FALSE;ЛОЖЬ;ИСТИНА;TRUE;ПРАВДА;…….;FALSE}

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

ЕСЛИ($E $ 3=C:C,СТРОКА(C:C),»»)

Здесь функция IF возвращает массив. На этот раз массив номеров строк и пустых строк.

{«»;»»;3;4;5;…….;»»}

Следующим шагом будет сортировка номеров строк в нашем новом массиве. Функция SMALL может возвращать n-е наименьшее число из массива. Также обратите внимание, что Excel оценивает строковые значения как почти бесконечно большие числа, что делает любое другое число маленьким по сравнению с ним. Именно по этой причине вместо функции LARGE используется функция SMALL.

Чтобы присвоить функции SMALL значение ‘n’, мы снова используем функцию ROW с одной ячейкой, которая должна быть на одну ячейку выше первой строки, чтобы возвращать числа от 1, и используем относительную ссылку для увеличения номера строки. В результате; формула SMALL(IF($ E $ 3=C:C,ROW(C:C),»»),ROW()-СТРОКА(E5)) возвращает значение индекса строки, которое будет использоваться функцией INDEX для возврата значения из непустой ячейки.

{1}

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

=IFERROR(ИНДЕКС(B:B,МАЛЕНЬКИЙ(IF($ E $3=C: C,СТРОКА(C:C),»»),СТРОКА()-СТРОКА(E5))),»»)

Наконец, нажмите комбинацию Ctrl + Shift + Enter вместо простого нажатия клавиши Enter, чтобы ввести свою формулу в виде формулы массива, и все, фильтрация через формулу в Эксель готова!

Автор А. Днепров

Обучаю основам работы с офисными программами.

Как выполнить фильтрацию с помощью формулы в Excel
Статистические функции в Excel с примерами