Если вы знакомы с формулами массивов, то простота новых динамических массивов Excel станет для вас глотком свежего воздуха. А если вы всегда сторонились формул массивов, то Динамические массивы – это то, что вам нужно.
Excel больше не требует ввода формул массивов с помощью CTRL+SHIFT+ENTER, а формулы, возвращающие несколько результатов, теперь автоматически “переливаются” в ячейки ниже и правее, как показано ниже:
До появления динамических массивов формула СТРОКА(), приведенная выше, возвращала 1, потому что она может отображать только первое значение в одной ячейке, но теперь, когда она может “переливаться” в ячейки ниже, она возвращает значения от 1 до 4.
Также обратите внимание, что в строке формул выше нет фигурных скобок вокруг формулы, указывающих на то, что это формула массива. А когда я перемещаю выделение ячейки в сторону от A1, вы можете увидеть, что панель формул теперь содержит призрачную формулу, указывающую на то, что я не могу ее изменить.
Этот новый результат “рассыпания” придает функциям массива Excel прозрачность и делает их более легко обнаруживаемыми, что позволяет пользователям быстрее, чем когда-либо, освоить формулы массива.
Формулы массивов теперь могут использовать все пользователи, а не только гуру Excel.больше не ограничиваются суперпользователями, они для всех.
Примечание: На момент написания статьи динамические массивы доступны только в Office 365!
Давайте рассмотрим некоторые из новых функций.
Функция УНИК
Извлечение уникальных или отличных значений из списка является распространенным требованием, и до сих пор вы могли либо использовать сложную формулу массива, подобную этой:
{=ЕСЛИОШИБКА(ИНДЕКС($C$3:$C$13;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$14:A14;$C$3:$C$13)=0;СТРОКА($C$1:$C$11));1));"")}
Однако теперь, когда у нас есть функция УНИК, приведенную выше формулу можно заменить следующей:
=УНИК(C3:C13)
Функция СОРТ
Приятно иметь возможность легко получить четкий список с помощью функции UNIQUE, но что, если вам также нужно отсортировать его. Старый подход с формулой массива выглядел примерно так:
{=ЕСЛИОШИБКА(ИНДЕКС($A$15:$A$19;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ($A$15:$A$19;"<"&$A$15:$A$19);СТРОКА(1:1));СЧЁТЕСЛИ($A$15:$A$19;"<"&$A$15:$A$19);0));"")}
Но вы будете рады узнать, что существует новая функция СОРТ, которую можно просто обернуть вокруг УНИК следующим образом:
Создание списка
Теперь, когда у нас есть хорошо отсортированный уникальный список, логично, что его можно использовать в списке проверки данных. Для ссылки на заполненный массив мы просто используем новый оператор заполненного диапазона #, как показано ниже в поле “Источник” проверки данных:
Когда мы ссылаемся на заполненный диапазон, используя нотацию #, она автоматически настраивается на включение новых ячеек по мере роста заполненного диапазона или исключение строк по мере сокращения диапазона. Другими словами, это динамическая ссылка.
Совет: Оператор разлинованного диапазона автоматически вставляется, если вы выделяете весь разлинованный диапазон ячеек в формуле, например.
Функция ФИЛЬТР
Функция ФИЛЬТР возвращает диапазон, отфильтрованный по заданным вами критериям. К примеру, для того, чтобы вывести новую отфильтрованную таблицу достаточно лишь применить эту несложную функцию.
Фильтр по нескольким критериям. К примеру, нам нужны строки, по которым стоимость доставки >= 50 или <6. Для вывода значения достаточно дописать формулу:
Фильтр по нескольким критериям. Допустим нам нужны строки со стоимостью доставки >= 50 или <6, но по которым тип платежа – чек. Стоит дописать лишь одно условие.
Стоит также напомнить, что все эти фильтры можно вынести в отдельные ячейки и фильтроваться с помощью выпадающих списков.
Создадим с помощью известных нам формул УНИК и СОРТ отдельный справочник с типами платежей, создадим выпадающие списки и изменим ссылку в формуле по последней переменной. Результат будет следующим.
Влияние динамических массивов на существующие функции
Динамические массивы изменили способ обработки функций Excel, которые могут возвращать диапазон, в том смысле, что теперь они могут “рассыпаться”. Это также означает, что некоторые функции, которые ранее не возвращали массивы, также могут “рассыпаться”, если вы ссылаетесь на диапазон в аргументе, который ожидает одно значение/ячейку (скаляр).
Например, приведенная ниже формула СЧЁТЕСЛИ “рассыпается”, потому что в аргументе критериев указан динамический диапазон, т.е. =СЧЁТЕСЛИ(C3:C13;A18#)
Заключительное слово
Для всех поклонников Google Sheets: хотя вы, возможно, уже знакомы с версиями функций СОРТ, ФИЛЬТР и УНИК от Google, я думаю, вы согласитесь, что новый оператор диапазона (#) будет очень полезен. И само по себе дополнение новых функций в Excel откроет множество решений, которые мы сейчас даже не можем себе представить.
