Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Генерация случайных чисел и дат
Дальше: Другие функции для работы с массивами, появившиеся в 2022 году

Динамические массивы

Файл с примерами: Динамические массивы.xlsx
НОВЫЕ ПРАВИЛА РАБОТЫ С МАССИВАМИ
В Excel в рамках пакета Microsoft 365 и версии 2021 произошли существенные и принципиальные изменения в работе с формулами массивов: теперь одна формула/функция может выводить результат не только в одной ячейке, но и сразу в нескольких.
Обычные формулы мы вводим в одну ячейку и результат получаем тоже в одной ячейке. Каждая формула независима в том смысле, что мы можем удалить или изменить отдельную формулу в диапазоне.

 

 

Теперь (в Microsoft 365, в Excel 2021) достаточно просто ввести в одну ячейку формулу, в которой аргументами будут диапазоны, а не ячейки и которая на выходе будет возвращать целый диапазон, и просто нажать Enter (или иным образом завершить ввод формулы).

 

 

Синим контуром выделены размеры диапазона, который возвращает формула. Это цельная неделимая сущность: нельзя выделить какую-либо ячейку, кроме первой (левой верхней, в данном случае D2), и удалить фрагмент этого возвращаемого массива. Можно удалить только всю формулу целиком.
Если выделить одну из ячеек, возвращаемых формулой, мы увидим саму формулу, но она будет выделена в строке формул серым, редактировать ее нельзя, это можно делать только в первой ячейке.

 

 

Строго говоря, формулы массива имелись и ранее во всех версиях Excel, но они были менее удобными:
— требовалось заранее выделить диапазон, в котором будет вводиться формула массива, то есть знать заранее размерность результата;
— нажать после Ctrl + Shift + Enter.

 

 

С новыми формулами массива появилась и новая ошибка — #ПЕРЕНОС!
Если на пути вывода данных будут другие формулы или значения, то формула будет возвращать ошибку, так как она не может «перезаписать» ваши данные, удалить их, чтобы вывести свой результат. Формула может лишь просигнализировать о том, что пустых ячеек внизу и справа недостаточно, чтобы вывести результат. Эта ошибка называется #ПЕРЕНОС! (#SPILL!).

 

 

В Google Таблицах в таких ситуациях отображается ошибка #ССЫЛ! (#REF!).
НОВЫЕ ФУНКЦИИ
Появились и новые функции. Они как раз выводят целый массив значений, который может динамически изменяться (если изменились исходные данные, обрабатываемые функцией).
Допустим, если условию, заданному в функции ФИЛЬТР / FILTER, начнут соответствовать не 10 строк, а 11, то она выведет 11 строк. В этом и есть отличие новых динамических массивов: формулы теперь могут возвращать массив разного размера, размерность массива не задается заранее. Благодаря этому появились новые функции, которые мы рассматриваем.
Эти функции (кроме СОРТПО / SORTBY) ранее были доступны только в Google Таблицах (там у этих функций названия на английском при любом языке формул), но теперь есть и в Excel в последнем пакете Microsoft Office.
Функция УНИК / UNIQUE
Функция УНИК / UNIQUE выводит массив уникальных значений (если аргумент — один столбец) или строк (если аргумент — несколько столбцов). Единственный обязательный аргумент — диапазон.
В следующем примере функция УНИК выводит уникальные имена из списка (каждое значение — только по одному разу, в отличие от исходного диапазона).
Если в исходном диапазоне станет меньше значений, функция будет возвращать массив другого размера.

 

 

Если аргументом УНИК будет диапазон из нескольких столбцов, то функция будет возвращать уникальные строки.

 

 

Функция СОРТ / SORT
Функция СОРТ / SORT сортирует диапазон — по столбцам или строкам:
=СОРТ (массив; [индекс_сортировки]; [порядок_сортировки]; [по_столбцу])
• массив (array) — диапазон данных, который будет возвращаться функцией в отсортированном виде;
• индекс_сортировки (sort_index) — номер столбца (строки), по которому идет сортировка;
• порядок_сортировки (sort_order) — порядок сортировки (1 = по возрастанию, −1 = по убыванию);
• по_столбцу (by_col) — сортировка по столбцам или строкам; если ИСТИНА / TRUE, то по столбцам (то есть будет меняться порядок столбцов); если ЛОЖЬ / FALSE, то по строкам (будет меняться порядок строк).
Применим функцию СОРТ к списку имен, пропустив все необязательные аргументы. В таком случае по умолчанию функция будет сортировать диапазон по возрастанию (по алфавиту для текстовых значений).

 

 

Можно объединить ее с функцией УНИК, чтобы получить отсортированный список уникальных значений (без дубликатов).

 

 

Если в диапазоне несколько столбцов, то понадобятся и другие аргументы функции СОРТ, чтобы указать, по какому столбцу и в каком порядке сортируем. Второй аргумент — порядковый номер столбца, а третий — порядок сортировки (1 = по возрастанию, −1 = по убыванию).

 

 

Функция СОРТПО / SORTBY
Функция СОРТПО / SORTBY возвращает диапазон, отсортированный по другому столбцу/строке (столбцам/строкам):
=СОРТПО (массив; [ключевой_массив1]; [порядок_сортировки1];…)
• массив (array) — диапазон данных, который будет возвращаться функцией в отсортированном виде;
• ключевой_массив1 (by_array1) — номер столбца (строки), по которому идет сортировка;
• порядок_сортировки1 (sort_order1) — порядок сортировки (1 = по возрастанию, −1 = по убыванию).
В функции СОРТ мы обязаны сортировать по одному из столбцов того диапазона, что возвращаем. Нельзя вывести список самых продаваемых книг (то есть список, отсортированный по количеству проданных экземпляров) без столбца с самими продажами — того, по которому идет сортировка.
А СОРТПО позволяет это сделать: вывести один диапазон, отсортированный по значениям из другого.
В следующем примере мы выводим список специалистов, отсортированный по их ставке, при этом в отсортированном списке нет столбца со ставкой, на основе которого происходит сортировка.

 

 

Функция ФИЛЬТР / 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 году