Динамические массивы Excel: ФИЛЬТР, УНИКАЛЬНОСТЬ и СОРТИРОВКА в одной формуле

1. Что такое разлив

Перед пояснением: вводите одну формулу в одну ячейку и нажимаете Входить. Скажите, сколько клеток вы ожидаете изменить.

Столько, сколько необходимо для ответа. Запросите отдельные регионы, и Excel вернет четыре значения, поэтому он заполнит четыре ячейки:

=UNIQUE(Orders[Region])

North
South
East
West

Только верхняя ячейка содержит формулу. Три под ним содержат переполнение, и сами по себе их нельзя редактировать; щелкните один, и в строке формул формула будет выделена серым цветом, поскольку она принадлежит ячейке выше. Excel рисует тонкую синюю рамку вокруг всего блока, чтобы показать, что это одно.

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

Обратите внимание на порядок, в котором он вернулся: Север, Юг, Восток, Запад. Это порядок, в котором значения сначала появляются в данных, а не в алфавитном порядке. UNIQUE не сортирует, поэтому существует следующая функция.

2. УНИКАЛЬНОСТЬ, СОРТИРОВКА И ФИЛЬТРАЦИЯ

Три функции охватывают большую часть того, что нужно каждому.

UNIQUE возвращает различные значения.

=SORT(UNIQUE(Orders[Region]))       East, North, South, West
=SORT(UNIQUE(Orders[Product]))      Chair, Desk, Lamp
=COUNTA(UNIQUE(Orders[Region]))     4

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

SORT заказывает диапазон, не касаясь его. SORT(array, column, order), где порядок равен 1 для возрастания и −1 для убывания.

=SORT(Orders[Revenue], 1, -1)
1100, 880, 880, 850, 765, 680, 660, 660, 600, 510, 480, 440, 440, 425, 280, 240

=TAKE(SORT(Orders[Revenue], 1, -1), 3)
1100, 880, 880

Сортировка по формуле, а не с помощью кнопки «Сортировка» имеет большее значение, чем кажется. Кнопка постоянно меняет порядок ваших данных и берет с собой любую формулу, которая ссылается на номер строки. Формула СОРТИРОВКИ создает отсортированную копию, оставляет источник в покое и повторно сортирует себя при изменении чисел.

FILTER возвращает строки, соответствующие условию.

=FILTER(Orders[OrderID], Orders[Region]="North")
1001, 1004, 1007, 1011, 1014

=ROWS(FILTER(Orders[OrderID], Orders[Region]="North"))       5
=SUM(FILTER(Orders[Revenue], Orders[Region]="North"))     2495

Прочтите аргументы как предложение: дайте мне этот, где что это правда. Первый аргумент — это то, что вы хотите вернуть, а второй — проверка, и это могут быть разные столбцы, что отличает ФИЛЬТР от кнопки фильтра.

Обратите внимание, что ФИЛЬТР можно обернуть чем угодно. SUM фильтруемого столбца — условный итог, ответ тот же SUMIFS дает, а 2495 — это доход Норта в любом случае. Разница в том, что версия FILTER может возвращать сами строки, когда вы хотите их просмотреть.

3. Два или более условий в ФИЛЬТРЕ.

Перед пояснением: В FILTER некуда ставить AND или OR. Скажите, как бы вы запросили заказы с Востока на сумму более 600 штук.

С арифметикой. Умножьте условия для И, добавьте их для ИЛИ и заключите каждое из них в скобки.

=FILTER(Orders[OrderID], (Orders[Region]="East") * (Orders[Revenue]>600))
1003, 1006, 1010                                          3 rows

=ROWS(FILTER(Orders[OrderID], (Orders[Region]="East") + (Orders[Region]="West")))
7 rows

Причина в том, что условие выдает ИСТИНА и ЛОЖЬ, которые в арифметике ведут себя как 1 и 0. Умножение дает 1 только тогда, когда оба числа равны 1, то есть И. Сложение дает как минимум 1, если любой из них равен 1, то есть ИЛИ. Как только вы это увидите, синтаксис перестанет выглядеть произвольным.

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

4. Когда ничего не совпадает

Перед пояснением: вы фильтруете регион, которого нет в данных. Предскажите, что появится в ячейке.

=FILTER(Orders[OrderID], Orders[Region]="Central")

#CALC!

#CALC! это более новая ошибка, означающая, что формула создала пустой массив, который ячейка не может отобразить. В вашей формуле нет ошибки; Excel говорит вам, что ответ — ничего.

У FILTER есть третий аргумент именно для этого, и использовать его лучше, чем заключать все это в обработчик ошибок:

=FILTER(Orders[OrderID], Orders[Region]="Central", "no orders")

no orders

Третий аргумент срабатывает только в пустом случае, поэтому реальная проблема в формуле по-прежнему проявляется как собственная ошибка. Заворачивание в IFERROR вместо этого скрыл бы оба, что является одной и той же сделкой ЕСЛИОШИБКА всегда просит вас сделать, и здесь нет никаких оснований принимать это, поскольку существует целенаправленный аргумент.

5. Номер разлива, A1#.

Разлитый блок меняет размер, поэтому обратиться к нему по адресу — проблема, ожидающая следующего месяца. Хэш-суффикс решает эту проблему: C1# означает «во что бы ни пролилось C1, каким бы большим оно ни было сейчас».

C1:  =UNIQUE(Orders[Region])        spills into C1:C4

=COUNTA(C1#)                        4
=TEXTJOIN("/", TRUE, C1#)           North/South/East/West

Именно это заставляет динамические массивы объединяться в небольшую систему, а не оставаться одной умной формулой. Создайте отдельный список регионов, затем наведите на него диаграмму, раскрывающийся список или столбец SUMIFS. C1#, и все они растут при добавлении региона. Ссылка на ячейку, например C1:C4 не стал бы.

Это также дает вам гораздо лучший список проверки данных, чем фиксированный диапазон: =$C$1# в поле «Источник» раскрывающегося списка, а параметры поддерживаются на основе данных, которые напрямую связаны с раскрывающееся руководство аргумент в пользу того, что список должен находиться там, где он может расти.

6. #РАЗЛИВ! и что вызывает это

До пояснений: ваша УНИКАЛЬНАЯ формула работала вчера и сегодня показывает #SPILL!. Скажите, что изменилось.

Что-то мешает. Для сброса необходимо, чтобы каждая ячейка, которую он хочет занять, была пустой, а если хотя бы одна из них не пуста, вся формула отказывается, а не перезаписывает что-либо. Вот прогон: формула правильно разлилась в C1:C4, затем в C3 было набрано значение.

before               North, South, East, West     COUNTA(C1#) = 4
after typing in C3   #SPILL!                      COUNTA(C1#) = 1

Обратите внимание на второй эффект. Все, что ссылается на ссылку на разлив, теперь видит одну ячейку вместо четырех, поэтому на основе нее строится диаграмма, раскрывающийся список или итоговое значение. C1# незаметно сжимается до ячейки ошибки. Ошибка громкая; удара нет.

Excel помогает найти блокировку: щелкните предупреждающий треугольник на ячейке с ошибкой и выберите «Выбрать мешающие ячейки». Четыре причины охватывают почти все.

Что-то находится в диапазоне, включая случайное пространство, введенное много лет назад. Это случай выше.

Пролитая жидкость стекала с простыни. Формуле в строке 1048000 некуда поставить двадцать строк.

Зона разлива находится внутри объединенной ячейки. Объединенные ячейки не могут получить сброс.

Формула находится внутри таблицы. У которого есть отдельный раздел, потому что люди встречаются с ним случайно.

7. Сковывание их и остальных членов семьи.

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

=SORT(UNIQUE(FILTER(Orders[Rep], Orders[Revenue]>600)))

UNIQUE также может работать с несколькими столбцами, поэтому вы получаете каждую комбинацию, которая действительно встречается, а не каждую комбинацию, которая могла бы:

=ROWS(UNIQUE(CHOOSE({1,2}, Orders[Region], Orders[Product])))     12

Двенадцать пар «регион-продукт» из шестнадцати заказов, то есть все четыре региона умножаются на все три продукта, то есть каждый регион продает каждый продукт. Сводная таблица покажет вам то же самое, что и сетка; это дает вам список, который может использовать формула.

Еще четверо, которые стоит знать поименно.

=SORTBY(Orders[OrderID], Orders[Region], 1, Orders[Revenue], -1)
sorts by one column and returns another; here, by region then by revenue descending,
so the first order out is 1010

=SEQUENCE(3)         1, 2, 3            a column of numbers
=SEQUENCE(1,4)       1, 2, 3, 4         a row of numbers
=TAKE(array, 3)                          the first three rows
=TEXTJOIN(", ", TRUE, SORT(UNIQUE(Orders[Region])))
East, North, South, West                 a spill flattened into one cell

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

Представьте на мгновение свою самую повторяющуюся задачу на листе: вкладку, на которой вы копируете столбец, удаляете дубликаты, сортируете его и вставляете куда-нибудь. Какая строка выше заменяет все это?

8. Единственное место, куда не пойдет разлив

Перед объяснением: столы являются подходящим домом для данных, а динамические массивы — это современный способ написания формул. Скажите, что происходит, когда вы помещаете одно в другое.

a cell inside a table:  =UNIQUE(Orders[Region])

#SPILL!

Таблица не может содержать развернутый результат. Причина скорее структурная, чем произвольная: задача таблицы — одна строка на запись, причем каждый столбец имеет одинаковую длину, а формула, которая сама решает, сколько строк ей нужно, нарушит это обещание. Таким образом, две функции, которые по отдельности являются лучшими практиками в Excel, не объединяются, и обходной путь состоит в том, чтобы просто разместить формулы на листе за пределами любой таблицы.

Это не то же самое, что сказать, что они не умеют читать таблицу. Каждая формула на этой странице читается Orders[Region], столбец таблицы, и выводит ответ на лист рядом с ним. Таблицы на входе, массивы на выходе.

Крайние случаи, которые ловят людей

Шесть, о которых стоит знать, прежде чем они произойдут.

Удаление верхней ячейки удаляет весь блок. В ячейках ниже нет формулы, поэтому они исчезают вместе с ней. Все, что относится к блоку # идет с ними.

Вставка строки через область разлива. Блок представляет собой один объект и не принимает строку через середину, поэтому вставка либо перемещает весь блок, либо запускает триггер. #SPILL!. Оставьте свободную колонку для разливов, а не размещайте их среди другого контента.

Пустая ячейка в исходном столбце. UNIQUE рассматривает пробел как значение и возвращает для него ноль, поэтому отдельный список может вернуться на единицу длиннее, чем ожидалось, с случайным 0 в конце. Отфильтруйте это: =SORT(UNIQUE(FILTER(range, range<>""))).

Отправка файла в более старую версию Эксель. Этих функций там не существует, и формулы приходят в виде _xlfn.UNIQUE и покажи #NAME?. Если книгу нужно переместить, проверьте версию получателя, прежде чем восстанавливать ее с учетом потерь.

Ссылки на весь столбец. =UNIQUE(A:A) просит Excel рассмотреть миллион строк и обычно возвращает миллион строк, большая часть которых пуста. Вместо этого укажите на столбец таблицы.

Объем. Разлив десятков тысяч строк — это одна формула и множество ячеек, а несколько таких на одном листе заметно замедлят работу книги. Подведите итоги, используя сводку, если ответом является краткое изложение; пролить, когда ответом является список.