Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Здесь тоже важно соблюдать одинаковую размерность — так, следующая функция будет возвращать ошибку, потому что «Данные» — это вся таблица, но без заголовков, а Данные[[#Все];[Канал продаж]] — это столбец «Канал продаж» с заголовками включительно (на 1 строку больше).

=ФИЛЬТР(Данные;Данные[[#Все];[Канал продаж]]="Дистрибуция")

Если нужно отфильтровать все строки, кроме какого-то определенного значения, используется знак (а вернее, два знака) «не равно»:

=ФИЛЬТР(Данные! A1:E997;Данные! D1:D997<> "Дистрибуция")

Условия также могут быть на числовые значения, тогда могут использоваться знаки сравнения. Следующий пример — фильтрация строк с количеством более 200:

=ФИЛЬТР(Данные;Данные[Количество]>200)

Для фильтрации по датам используйте функцию ДАТАЗНАЧ / DATEVALUE, которая превращает текстовую дату, указанную в кавычках в стандартном формате, в число и тем самым позволяет использовать ее как условие для фильтрации. В следующем примере мы выбираем только данные с датой от 1 мая 2021 года включительно:

=ФИЛЬТР(Данные;Данные[Дата операции]>=ДАТАЗНАЧ("01.05.2021″))

Если в ФИЛЬТРе нужно выполнение нескольких условий одновременно (И), то перечисляем их со знаком умножения (*) и каждое берем в скобки.

Следующее условие — это фильтрация строк с Санкт-Петербургом в столбце F и B2B в столбце B.

(A1:A100="Санкт-Петербург") * (B1:B100="B2B")

Если хотя бы одного из (ИЛИ), то с плюсом (+). Следующее условие — или Санкт-Петербург, или Москва в столбце A:

(A1:A100= "Санкт-Петербург") + (A1:A100="Москва")

Функцию ФИЛЬТР можно совместить с СОРТ, если нужно и фильтровать, и сортировать данные. В следующей формуле (переносы строк — для наглядности, их можно использовать и в строке формул для лучшей читаемости) мы фильтруем данные по дате, а затем сортируем по пятому столбцу по возрастанию:

=СОРТ(ФИЛЬТР(Данные;Данные[Дата операции]>=ДАТАЗНАЧ("01.05.2021″));5;1)

Добавляем заголовки к результату фильтрации

Обратите внимание, что ФИЛЬТР автоматически не вставляет заголовки сверху над данными. Их можно заранее вставить вручную в верхнюю строку, под которой будет функция ФИЛЬТР, либо использовать функцию ВСТОЛБИК / VSTACK, о которой мы писали выше (если у вас Microsoft 365):

=ВСТОЛБИК(Заголовки];ФИЛЬТР(…))

В Google Таблицах можно объединить массивы с помощью фигурных скобок:

={Ссылка на заголовки; FILTER(…) }

Точка с запятой (в российских региональных настройках) в Google Таблицах — это вертикальное объединение массивов, а обратная косая черта — горизонтальное.

Другие функции для работы с массивами, появившиеся в 2022 году

Файл с примерами: ВЗЯТЬ и другие функции для работы с массивами.xlsx

Помимо VSTACK и HSTACK, в 2022 году в Excel (и Google Таблицах тоже) появились и другие функции для обработки массивов.

Функции TAKE / ВЗЯТЬ и СБРОСИТЬ / DROP

Функция ВЗЯТЬ / TAKE извлекает заданное количество столбцов или строк из массива:

=ВЗЯТЬ(массив;; сколько строк получить; [сколько столбцов получить])

В частном случае можно вытаскивать не только строки или столбцы, но и значения (если на входе задан одномерный массив).

Например, если нам нужны первые 10 значений в столбце таблицы, будет такая формула (в общем виде):

=ВЗЯТЬ(Таблица[Столбец]; 10)

А если нужны последние, а не первые? Прелесть в том, что функция умеет и так — просто укажите отрицательное количество строк в ее аргументе:

=ВЗЯТЬ(Таблица[Столбец]; -10)

Если нужна сумма последних 10, то добавим сверху функцию СУММ / SUM: =СУММ(ВЗЯТЬ(Таблица[Столбец]; -10))

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

СБРОСИТЬ убирает из массива заданное число строк и столбцов из начала (положительный аргумент) или конца (отрицательный):

=СБРОСИТЬ(массив; сколько строк убрать; [сколько столбцов убрать])

Например, если мы хотим сумму без первых трех (по порядку) значений:

=СУММ(СБРОСИТЬ(Сделки[Сумма];3))