Общая формула
=FILTER(data,logic)
Резюме
Чтобы отфильтровать данные, расположенные по горизонтали в столбцах, вы можете использовать функцию ФИЛЬТР. В показанном примере формула C9 имеет следующий вид:
=TRANSPOSE(FILTER(data,group="fox"))
где данные (C4: L6) и группа (C5: L5) являются именованными диапазонами.
Объяснение
Примечание. ФИЛЬТР - это новая функция динамического массива в Excel 365. В других версиях Excel есть альтернативы, но они более сложные.
В диапазоне C4: L6 имеется десять столбцов данных. Цель состоит в том, чтобы отфильтровать эти горизонтальные данные и извлечь только столбцы (записи), в которых группа - «лиса». Для удобства и удобочитаемости рабочий лист содержит три именованных диапазона: данные (C4: L6) и группы (C5: L5) и возраст (C6: L6).
Функцию ФИЛЬТР можно использовать для извлечения данных, расположенных вертикально (строками) или горизонтально (столбцами). ФИЛЬТР вернет совпадающие данные в той же ориентации. Никакой специальной настройки не требуется. В показанном примере формула C9 имеет следующий вид:
=FILTER(data,group="fox")
Аргумент include для FILTER, работающий изнутри, является логическим выражением:
group="fox" // test for "fox"
Когда логическое выражение оценивается, оно возвращает массив из 10 значений ИСТИНА и ЛОЖЬ:
(TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE)
Примечание: запятые (,) в этом массиве обозначают столбцы. Точка с запятой (;) обозначает строки.
Массив содержит по одному значению на столбец данных, и каждое ИСТИНА соответствует столбцу, в котором группа - «лиса». Этот массив возвращается непосредственно в FILTER в качестве аргумента включения, и он выполняет фактическую фильтрацию:
FILTER(data,(TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE))
Фильтр проходят только данные, соответствующие ИСТИННЫМ значениям, поэтому ФИЛЬТР возвращает 6 столбцов, в которых группа - «лиса». ФИЛЬТР возвращает эти данные в исходной горизонтальной структуре. Поскольку FILTER - это функция динамического массива, результаты попадают в диапазон C9: H11.
Это динамическое решение - при изменении каких-либо исходных данных в C4: L6 результаты FILTER обновляются автоматически.
Перенести в вертикальный формат
Чтобы преобразовать результаты фильтрации в вертикальный (строки) формат, вы можете обернуть функцию TRANSPOSE вокруг функции FILTER следующим образом:
=TRANSPOSE(FILTER(data,group="fox"))
Результат выглядит так:
Эта формула объясняется более подробно здесь.
Фильтр по возрасту
Одна и та же базовая формула может использоваться для фильтрации данных разными способами. Например, чтобы отфильтровать данные, чтобы отображались только столбцы с возрастом менее 22 лет, можно использовать такую формулу:
=FILTER(data,age<22)
ФИЛЬТР возвращает четыре совпадающих столбца данных:
