Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК
Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Если вы ещё с не ознакомились (кому лень читать - там есть видео), то очень советую сделать это сейчас, чтобы понимать о чём, собственно, идёт речь и как заполучить все эти радости в вашем Excel.
Обновление Office 365, которое подарило Microsoft Excel новый вычислительный движок с поддержкой динамических массивов, также добавило к нашему арсеналу 7 новых функций, заточенных специально для работы с массивами. В этой статье я хотел бы рассказать про три самых важных функции: СОРТ, ФИЛЬТР и УНИК. Остальные играют скорее вспомогательную роль - про них чуть позже.
Для простоты я буду во всех примерах я буду показывать работу этих функций на обычных таблицах, но можно иметь ввиду, что с "умными" таблицами (созданными через Главная - Форматировать как таблицу или сочетанием клавиш Ctrl+T) эти функции тоже отлично работают.
Итак, поехали...
Функция СОРТ (SORT)
Синтаксис:
В самом простом варианте требует в качестве аргумента только массив (диапазон) и выдает его уже в отсортированном виде:
По умолчанию сортировка выполняется по возрастанию. Если нужен обратный порядок, то за это отвечает третий аргумент (1 - по возрастанию, -1 - по убыванию):
Если на входе указать диапазон из несколько колонок в данных, то сортировка происходит по первому столбцу. Если нужно сортировать не по первому, то в качестве второго аргумента можно указать номер столбца для сортировки:
Если хочется сортировать по нескольким столбцам сразу, то придётся задать массив номеров столбцов во втором аргументе в фигурных скобках. Одновременно аналогичным образом можно задать третьим аргументом и направление сортировки для каждого столбца.
Например, если мы хотим отсортировать наш список по городам по возрастанию и затем по суммам по убыванию, то это будет выглядеть так:
Последний аргумент используется, если нужно сортировать не строки, а столбцы, т.е. упорядочивать данные по горизонтали, что встречается существенно реже.
Вот так - просто и изящно. Особенно, если вспомнить какую монстрообразную формулу массива требовалось ввести раньше для сортировки всего лишь одного (!) столбца:
Бррр... :(
Функция ФИЛЬТР (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), не забыв добавить после адреса первой ячейки массива знак решётки:
Оглядываясь назад и вспоминая все свои выполненные проекты по автоматизации за последние Х лет, с грустью понимаю сколько дней и даже, наверное, недель можно было бы сэкономить, если бы динамические массивы и такие функции существовали тогда. Даааа....
Ну, лучше поздно, чем никогда, верно? По крайней мере, нашим детям точно будет проще :)
Ссылки по теме
- Что такое динамические массивы в новом Excel и как они работают
- Как отсортировать список формулой
- Как создать выпдающий список в ячейке листа Excel
А как вся эта красота работает с диапазонами из закрытых книг?
у меня Microsoft Office 365 ProPlus
Версия 1909 (сборка 12026.20264) Monyhly Channel
их до сих порт нет.
Если смотреть о Excel то Microsoft Excel для Office 365 MSO (16.0.12026.20100) 32-разрядная
Всё, что между ними - не отображать. На выходе - отфильтрованная табличка с 2-мя столбцами
ИНДЕКС(A2:D20;строка(1:20);{1;4})
ISEMPTY нет такива виду функция в Excel !
=SORT(UNIQUE(FILTER(B2:B100000;NOT(ISNONTEXT(B2:B100000)))))
Вот само задание:
Исходное число - 25456585 Необходимо получить число - 24568
Формула будет следующей -
=ОБЪЕДИНИТЬ("";;СОРТ(УНИК(ПСТР(B2;СТРОКА(1:10);1));1;1))Т.е., несмотря на то, что функции "СОРТ" и "УНИК" сами по себе выводят динамический массив на несколько ячеек, функция "ОБЪЕДИНИТЬ" схлопывает это в одну ячейку, тем самым вывод уникальных значений и их сортировка пользователю не показываются. Браво Excel!
Говорят, что суть та же, но выглядит короче.
Необходимо в одну таблицу построчно вписать данные из другой таблицы по одному признаку