Общая формула
=FILTER(data,(header="a")+(header="b"))
Резюме
Чтобы отфильтровать столбцы, укажите горизонтальный массив для аргумента включения. В показанном примере формула I5 имеет следующий вид:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Результатом является отфильтрованный набор данных, содержащий только столбцы A, C и E из исходных данных.
Объяснение
Хотя FILTER чаще используется для фильтрации строк, вы также можете фильтровать столбцы, хитрость заключается в том, чтобы предоставить массив с тем же количеством столбцов, что и исходные данные. В этом примере мы создаем нужный нам массив с помощью логической логики, также называемой булевой алгеброй.
В булевой алгебре умножение соответствует логике И, а сложение - логике ИЛИ. В показанном примере мы используем булеву алгебру с логикой ИЛИ (сложение) для нацеливания только на столбцы A, C и E следующим образом:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
После оценки каждого выражения у нас есть три массива значений ИСТИНА / ЛОЖЬ:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Математическая операция (сложение) преобразует значения ИСТИНА и ЛОЖЬ в единицы и нули, поэтому вы можете думать об операции следующим образом:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
В итоге у нас есть один горизонтальный массив из единиц и нулей:
(1,0,1,0,1,0)
который передается непосредственно в функцию FILTER в качестве аргумента include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Обратите внимание, что в исходных данных 6 столбцов и 6 значений в массиве, все либо 1, либо 0. FILTER использует этот массив в качестве фильтра для включения только столбцов 1, 3 и 5 из исходных данных. Столбцы 2, 4 и 6 удалены. Другими словами, единственные оставшиеся столбцы связаны с единицами.
С функцией ПОИСКПОЗ
Применение логики ИЛИ с добавлением, как показано выше, работает нормально, но плохо масштабируется и делает невозможным использование диапазона значений из рабочего листа в качестве критериев. В качестве альтернативы вы можете использовать функцию ПОИСКПОЗ вместе с функцией ЕЧИСЛО, как это, чтобы создать аргумент включения более эффективно:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Функция ПОИСКПОЗ настроена на поиск всех заголовков столбцов в константе массива («a», «c», «e»), как показано. Мы делаем это так, чтобы результат MATCH имел размеры, совместимые с исходными данными, которые содержат 6 столбцов. Также обратите внимание, что третий аргумент в MATCH устанавливается равным нулю, чтобы обеспечить точное соответствие.
После выполнения MATCH возвращается такой массив:
(1,#N/A,2,#N/A,3,#N/A)
Этот массив переходит непосредственно в ISNUMBER, который возвращает другой массив:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Как и выше, этот массив горизонтальный и содержит 6 значений, разделенных запятыми. FILTER использует массив для удаления столбцов 2, 4 и 6.
С диапазоном
Поскольку заголовки столбцов уже находятся на листе в диапазоне I4: K4, приведенную выше формулу можно легко адаптировать для прямого использования диапазона следующим образом:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Диапазон I4: K4 оценивается как ("a", "c", "e") и ведет себя так же, как константа массива в формуле выше.
