Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК
Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в 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)
=УНИК( массив ; [по_столбцам ] ; [один_раз ] )
В самом простом варианте эта функция извлекает из входного массива все имеющиеся там значения, удаляет повторы и выдаёт то, что осталось:
Если в качестве исходного массива выделено несколько столбцов, то проверка на уникальность идёт по всем, т.е. по связке значений из всех ячеек каждой строки:
Очень интересно работает третий аргумент — он заставляет функцию извлечь из исходного массива только те данные, которые встречаются там всего один раз, т.е. не имеют клонов:
Комбинирование функций
Само-собой, никто не запрещает нам «женить» все упомянутые функций между собой — здесь-то и начинается настоящая магия, мощь и синергия.
Разберём, для примера, классическую задачу: предположим, что нам нужно сформировать справочник по городам для выпадающего списка на основе выгрузки из какой-нибудь базы данных. В исходной выгрузке города повторяются в случайном порядке, есть пустые ячейки и дубли. А необходимый нам справочник, содержащий эталонный набор городов, должен быть:
После этого останется только указать получившийся динамический массив как источник для выпадающего списка на вкладке Данные — Проверка данных (Data — Validation) , не забыв добавить после адреса первой ячейки массива знак решётки:
Причем, несмотря на приличный размер исходного диапазона (100 тыс. строк!) никакого торможения при пересчёте такой формулы нет абсолютно — новый вычислительный движок Excel справляется «на ура». При этом классические формулы массива на подобных задачах начинали ощутимо подтупливать на таблицах уже с 3-5 тыс. строк, а на 100 тыс. просто загнали бы ваш Excel в кому.
Оглядываясь назад и вспоминая все свои выполненные проекты по автоматизации за последние Х лет, с грустью понимаю сколько дней и даже, наверное, недель можно было бы сэкономить, если бы динамические массивы и такие функции существовали тогда. Даааа.
Ну, лучше поздно, чем никогда, верно? По крайней мере, нашим детям точно будет проще 
Основы программирования. Инструментальные средства MS Office | Основные встроенные функции Excel
Единственным аргументом функции является текст, который принимает ссылку на ячейку с текстовыми данными или текстовую строку, заключенную в кавычки (например, в результате выполнения =СЖПРОБЕЛЫ(«некоторый текст») будет возвращена строка «некоторый текст»).
[expert_bq id=»1570″]Функции ПРОИЗВЕД произведение , СТЕПЕНЬ возведение чисел в степень , SIN, COS, TAN тригонометрические функции также очень популярны, и, что самое важное, не требуют дополнительных знаний для их введения. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Функции ПРОИЗВЕД (произведение), СТЕПЕНЬ (возведение чисел в степень), SIN, COS, TAN (тригонометрические функции) также очень популярны, и, что самое важное, не требуют дополнительных знаний для их введения.
Встроенные функции в Excel: основные данные, категории и особенности применения
- Установить курсор в выбранную ячейку, куда будет записываться количество букв и знаков;
- В мастере функций выбрать функцию «ДЛСТР».
- В появившемся окне указать ячейку с текстом, из которой считаем количество знаков.
- Нажать «Интер»
- В выбранной ячейке отобразилось количество знаков в строке.
По существу, эта формула объединяет текстовую строку с содержимым ячейки ВЗ и отображает полученный результат. Обратите внимание, что в А5 к содержимому ВЗ не был применен ни один специальный формат. Тем не менее при желании можно установить для нее денежный формат с использованием пробелов и символа валюты.
