Фильтрация данных помогает сосредоточиться на определенных аспектах набора данных. Excel имеет встроенные функции для этого, формула Excel для фильтрации данных не входила в область применения программного обеспечения до введения функций динамического массива.
В этом руководстве мы собираемся показать вам, как вы можете использовать формулу Excel для фильтрации данных.
Синтаксис
=IFERROR(ИНДЕКС(диапазон списка,SMALL(IF(критерии= диапазон критериев, СТРОКА (диапазон критериев),»»),ROW()-СТРОКА (возвращает ячейку заголовка))),»»)
Шаги
- Выберите диапазон ячеек, которые будут заполнены отфильтрованными значениями
- Запустите формулу с помощью = IFERROR( функция для возврата пустой строки при возникновении ошибки)
- Продолжите с ИНДЕКСОМ(
- Выберите или введите ссылку на диапазон, содержащую ваш исходный список B:B,
- Продолжите с функцией SMALL(, которая предоставляет индексы строк ячеек
- Затем используйте IF(, чтобы вернуть массив, содержащий числа и пустые строки
- Используйте уравнение для фильтрации $ E $ 3=C: C, ячейка критериев должна быть абсолютной ссылкой
- Продолжите с ,ROW(C:C),»»), которая обеспечит условия TRUE / FALSE для функции IF
- Введите в ROW()-СТРОКА(E5), чтобы сгенерировать инкрементное число для массива из функции IF
- Введите )),»») и нажмите 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, чтобы ввести свою формулу в виде формулы массива, и все, фильтрация через формулу в Эксель готова!
