А СОРТПО позволяет это сделать: вывести один диапазон, отсортированный по значениям из другого.
В следующем примере мы выводим список специалистов, отсортированный по их ставке, при этом в отсортированном списке нет столбца со ставкой, на основе которого происходит сортировка.
Функция ФИЛЬТР / FILTER
Функция ФИЛЬТР / FILTER выводит значения из одного или нескольких столбцов, отфильтрованные по одному или нескольким условиям:
=ФИЛЬТР (массив; включить; [если_пусто])
• массив (array) — диапазон данных, который будет возвращаться функцией в отфильтрованном виде;
• включить (include) — условие фильтрации, записанное в следующем виде:
A1:A100 = "Москва" (фильтруем по слову «Москва» в столбце A). Диапазон условия должен быть той же размерности, что и диапазон данных:
• если_пусто (if_empty) — что возвращать, если функция не найдет никаких строк по условию.
Условия на текстовые значения записываются следующим образом:
диапазон="условие"
Например, только «Дистрибуция» в столбце D:
=ФИЛЬТР(Данные! A1:E997;Данные! D1:D997="Дистрибуция")
Обратите внимание, что аргументы должны быть одинаковой размерности (первый — фильтруемый диапазон и второй — условие). То есть следующая функция, например, будет возвращать ошибку:
=ФИЛЬТР(Данные!A2:E997;Данные!D1:D998="Дистрибуция")
Фильтруемые данные могут быть не только обычным диапазоном, но и таблицей (Table). Тогда ссылки будут выглядеть соответствующим образом:
=ФИЛЬТР(Данные;Данные[Канал продаж]="Дистрибуция")
Здесь тоже важно соблюдать одинаковую размерность — так, следующая функция будет возвращать ошибку, потому что «Данные» — это вся таблица, но без заголовков, а Данные[[#Все];[Канал продаж]] — это столбец «Канал продаж» с заголовками включительно (на 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 извлекает заданное количество столбцов или строк из массива:
=ВЗЯТЬ(массив;; сколько строк получить; [сколько столбцов получить])
В частном случае можно вытаскивать не только строки или столбцы, но и значения (если на входе задан одномерный массив).
Например, если нам нужны первые 10 значений в столбце таблицы, будет такая формула (в общем виде):
=ВЗЯТЬ(Таблица[Столбец]; 10)
А если нужны последние, а не первые? Прелесть в том, что функция умеет и так — просто укажите отрицательное количество строк в ее аргументе:
=ВЗЯТЬ(Таблица[Столбец]; -10)
Если нужна сумма последних 10, то добавим сверху функцию СУММ / SUM: =СУММ(ВЗЯТЬ(Таблица[Столбец]; -10))
Теперь формула всегда будет возвращать сумму значений из 10 последних строк таблицы из выбранного столбца, даже когда будут добавляться новые строки.
СБРОСИТЬ убирает из массива заданное число строк и столбцов из начала (положительный аргумент) или конца (отрицательный):
=СБРОСИТЬ(массив; сколько строк убрать; [сколько столбцов убрать])
Например, если мы хотим сумму без первых трех (по порядку) значений:
=СУММ(СБРОСИТЬ(Сделки[Сумма];3))
Эти функции делают массив плоским — в одну строку или в один столбец соответственно. Можно превратить несколько столбцов в один вертикальный или горизонтальный список.
А дальше можно обрабатывать этот список: например, получить список уникальных значений, без повторов — с помощью функции УНИК / UNIQUE:
=УНИК(ПОСТОЛБЦ(диапазон))
И таким образом мы получим список спикеров (без повторов), хотя изначально это три отдельных столбца, и к ним функцию УНИК применять для решения этой задачи было бесполезно (возвращались бы уникальные строки, а не значения).
Эти функции делают обратную операцию — превращают плоский массив в двумерный. Бывает полезно, чтобы исправить какую-нибудь выгрузку в виде списка и получить ее в табличном виде.
Первый аргумент этих функций — собственно массив (список), а второй (он называется wrap_count) — сколько строк или столбцов нужно получить на выходе:
=СВЕРНСТОЛБЦ(массив; число строк)
В следующем примере мы превращаем список (те же данные, что и в предыдущем примере), в котором каждые 4 строки — это время и три фамилии, в таблицы. В случае со СВЕРНСТОЛБЦ в ней будет 4 строки, в случае со СВЕРНСТРОК — 4 столбца. Второй аргумент обеих функций — 4 (это цикличность наших данных).
Эти функции извлекают заданные (по номерам) строки или столбцы из массива. То есть можно извлечь, например, первый и седьмой столбец. Или извлечь весь массив, поменяв порядок столбцов:
=ВЫБОРСТРОК(массив; номер первой извлекаемой строки; номер второй; …)
Давайте извлечем первую и последнюю строку из таблицы. Первая — это первая, вторым аргументом функции ВЫБОРСТРОК можно задать единицу. А чтобы получать номер последней на данный момент строки, можно посчитать число значений с помощью функции СЧЁТЗ / COUNTA:
=ВЫБОРСТРОК(Название таблицы;1;СЧЁТЗ(Столбец из таблицы для подсчета значений))
Эта функция увеличивает массив, добавляя к исходному массиву (диапазону) какое-то заданное значение:
=РАЗВЕРНУТЬ (исходный массив, число строк в новом массиве, число столбцов, чем заполнить)
Если последний аргумент не задать, то новые значения будут ошибками #H/Д (#N/A).
В следующем примере мы также делаем новый массив размерами 3 × 3 (то есть добавляем к исходному из диапазона A1:B2 одну строку и один столбец), но новые значения задаем как нули.
Функция ПОСЛЕД / SEQUENCE
Файл с примерами: ПОСЛЕД.xlsx
Функция ПОСЛЕД / SEQUENCE появилась вместе с динамическими массивами в Excel 2021 и Microsoft 365, то есть отсутствует во всех «коробочных» версиях Excel вплоть до 2019.
Но она есть в Google Таблицах, там эта функция при любом языке формул называется SEQUENCE.
Эта функция возвращает массив из чисел, заданный следующими параметрами — ее аргументами:
• строки (rows);
• столбцы (columns);
• начало (start);
• шаг (step).
Например, такая функция выведет столбец с числами от 1 до 1000:
=ПОСЛЕД(1000;1;1;1)
А такая — диапазон размеров 3 × 3 с числами от 10 до 100:
=ПОСЛЕД(3;3;10;10)
Так как даты в Excel и Google Таблицах — это числа (одна единица = 1 календарный день), то можно выводить и их. Вот несколько примеров.
Если есть две ячейки, в которых указаны даты начала и окончания периода, то с помощью ПОСЛЕД можно сформировать список всех дат в этом периоде.
Допустим, мы хотим сформировать их в один столбец, тогда числом строк здесь будет продолжительность периода (конец периода минус начало периода и плюс один день, если мы хотим включать последнюю дату периода), столбец будет один, шаг — 1 (один день). Начало последовательности — это первая дата периода: