Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК

Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Если вы ещё с не ознакомились (кому лень читать - там есть видео), то очень советую сделать это сейчас, чтобы понимать о чём, собственно, идёт речь и как заполучить все эти радости в вашем Excel.

Обновление Office 365, которое подарило Microsoft Excel новый вычислительный движок с поддержкой динамических массивов, также добавило к нашему арсеналу 7 новых функций, заточенных специально для работы с массивами. В этой статье я хотел бы рассказать про три самых важных функции: СОРТ, ФИЛЬТР и УНИК. Остальные играют скорее вспомогательную роль - про них чуть позже.

Для простоты я буду во всех примерах я буду показывать работу этих функций на обычных таблицах, но можно иметь ввиду, что с "умными" таблицами (созданными через Главная - Форматировать как таблицу или сочетанием клавиш Ctrl+T) эти функции тоже отлично работают.

Итак, поехали...

Функция СОРТ (SORT)

Синтаксис:

=СОРТ(массив; [индекс_сортировки]; [порядок_сортировки]; [по_столбцу])

В самом простом варианте требует в качестве аргумента только массив (диапазон) и выдает его уже в отсортированном виде:

Функция СОРТ (SORT) - простой случай

По умолчанию сортировка выполняется по возрастанию. Если нужен обратный порядок, то за это отвечает третий аргумент (1 - по возрастанию, -1 - по убыванию):

Обратная сортировка

Если на входе указать диапазон из несколько колонок в данных, то сортировка происходит по первому столбцу. Если нужно сортировать не по первому, то в качестве второго аргумента можно указать номер столбца для сортировки:

Сортировка по 3-му столбцу

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

Например, если мы хотим отсортировать наш список по городам по возрастанию и затем по суммам по убыванию, то это будет выглядеть так:

Одновременная сортировка по нескольким столбцам в разном направлении

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

Вот так - просто и изящно. Особенно, если вспомнить какую монстрообразную формулу массива требовалось ввести раньше для сортировки всего лишь одного (!) столбца:


Бррр... :(

Функция ФИЛЬТР (FILTER)

Синтаксис:

=ФИЛЬТР(массив; включить; [если_пусто])

Назначение этой функции - принять в качестве аргумента массив исходных ячеек и отфильтровать его по заданному условию(ям). Какие строки включить в результаты, а какие убрать - определяется вторым аргументом. Он должен представлять из себя массив логических значений ЛОЖЬ (FALSE) и ИСТИНА (TRUE), задающих статус для каждой строки:

Фильтрация функцией ФИЛЬТР

Логическую ИСТИНУ и ЛОЖЬ можно, для компактности, заменить на 1 (или любое другое число) и 0 (или пустую ячейку):

Фильтрация по единичкам

А самое интересное, что логические значения могут быть результатом какого-либо выражения, например:

Фильтр по условию

Если чуть подумать, то простор для фантазии тут открывается широкий. Вот вам для затравки несколько вариантов условий, с которыми замечательно будет работать эта функция:

  • =ФИЛЬТР(B2:D25; D2:D25>=10000) - отбираем все заказы, где стоимость больше или равна 10 000
  • =ФИЛЬТР(B2:D25; ЛЕВСИМВ(B2:B25) = "Б") - фильтрация всех строк, где название товара начинается с "Б" (блуза, брюки, бриджи и т.д.)
  • =ФИЛЬТР(B2:D25; (B2:B25 = "Брюки") * (C2:C25 = "Анна")) - отбор всех сделок Анны, где она продавала брюки
  • =ФИЛЬТР(B2:D25; (C2:C25 = "Анна") + (C2:C25 = "Иван")) - все сделки Анны и Ивана
  • =ФИЛЬТР(B2:D25; ЕСЛИОШИБКА(ПОИСК("Самара";A2:A25);0)) - фильтрация всех сделок, где в названии города содержится слово Самара (г. Самара, Самара г., город Самара, Самара-городок и т.д.)

Если функция ФИЛЬТР не находит ни одного значения, удовлетворяющего условию, то она выдаёт ошибку #ВЫЧИСЛ! Чтобы вывести вместо неё что-то более осмысленное, можно использовать третий аргумент:

Сообщение, если ничего не найдено

Функция УНИК (UNIQUE)

Синтаксис:

=УНИК(массив; [по_столбцам]; [один_раз])

В самом простом варианте эта функция извлекает из входного массива все имеющиеся там значения, удаляет повторы и выдаёт то, что осталось:

Извлечение уникальных функцией УНИК

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

Извлечение уникальных строк

Очень интересно работает третий аргумент - он заставляет функцию извлечь из исходного массива только те данные, которые встречаются там всего один раз, т.е. не имеют клонов:

Извлечение только уникальных элементов

Комбинирование функций

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

Разберём, для примера, классическую задачу: предположим, что нам нужно сформировать справочник по городам для выпадающего списка на основе выгрузки из какой-нибудь базы данных. В исходной выгрузке города повторяются в случайном порядке, есть пустые ячейки и дубли. А необходимый нам справочник, содержащий эталонный набор городов, должен быть:

  • без повторов
  • без пустых ячеек
  • отсортирован по возрастанию от А до Я

Делается всё вышеперечисленное одной (!) формулой:

Комбинирование функций массива

В английской версии эта функция выглядит как:

=SORT(UNIQUE(FILTER(B2:B100000;NOT(ISEMPTY(B2:B100000)))))

Здесь:

  • ФИЛЬТР - отбирает только те ячейки, где есть данные (не пусто)
  • УНИК - убирает повторы в отобранном функцией ФИЛЬТР списке
  • СОРТ - сортирует получившийся справочник по алфавиту

После этого останется только указать получившийся динамический массив как источник для выпадающего списка на вкладке Данные - Проверка данных (Data - Validation), не забыв добавить после адреса первой ячейки массива знак решётки:

Вып.список на динамическом массиве с формулами

Причем, несмотря на приличный размер исходного диапазона (100 тыс. строк!) никакого торможения при пересчёте такой формулы нет абсолютно - новый вычислительный движок Excel справляется "на ура". При этом классические формулы массива на подобных задачах начинали ощутимо подтупливать на таблицах уже с 3-5 тыс. строк, а на 100 тыс. просто загнали бы ваш Excel в кому.

Оглядываясь назад и вспоминая все свои выполненные проекты по автоматизации за последние Х лет, с грустью понимаю сколько дней и даже, наверное, недель можно было бы сэкономить, если бы динамические массивы и такие функции существовали тогда. Даааа....

Ну, лучше поздно, чем никогда, верно? По крайней мере, нашим детям точно будет проще :)

Ссылки по теме




24.09.2019 14:57:31
Николай, спасибо за статью. Чую скоро Power Pivot целиком переплывет в Excel. ;)

А как вся эта красота работает с диапазонами из закрытых книг?
03.10.2019 13:05:34
Подскажите, пожалуйста, начиная с какой версии доступны эти функции?
у меня Microsoft Office 365 ProPlus
Версия 1909 (сборка 12026.20264) Monyhly Channel
их до сих порт нет.
Если смотреть о Excel то Microsoft Excel для Office 365 MSO (16.0.12026.20100) 32-разрядная
11.10.2019 11:54:12
А можно ФИЛЬТР в ФИЛЬТР засунуть ? А то что-то не получается. То ли я не понимаю, то ли и впрям нельзя.
Наверх