Замена сводной таблицы тремя формулами динамического массива - Советы по Excel

Содержание

Смотреть видео

Прошло восемь дней с тех пор, как формулы динамических массивов были объявлены на конференции Ignite 2018 в Орландо. Вот что я узнал:

Современные массивы были анонсированы на Ignite 24 сентября 2018 года и официально называются динамическими массивами.
Я написал 60-страничную электронную книгу с 30 примерами их использования, и я предлагаю ее бесплатно до конца 2018 года.
Развертывание будет намного медленнее, чем кто-либо хочет, и это разочаровывает. Почему так медленно? Команда Excel внесла изменения в код Calc Engine, который оставался стабильным в течение 30 лет. Особое беспокойство: с надстройками, которые вводят формулы в Excel, которые случайно использовали неявное пересечение. Эти надстройки не работают, если Excel теперь возвращает диапазон разлива.
Есть новый способ ссылки на диапазон, возвращаемый массивом: =E3#но у него еще нет имени. Знак # называется оператором разлитой формулы . Что вы думаете о таких названиях, как Spill Ref (предложено MVP Excel Джоном Акампорой) или The Spiller (предложено MVP Ингеборгом Хавигхорстом)?

Как соавтор сводной таблицы Data Crunching, я люблю хорошую сводную таблицу. Но что, если вам нужно обновить сводные таблицы, и вы не можете доверять менеджеру своего менеджера, который нажимает «Обновить»? Описанный сегодня метод предлагает серию из трех формул для замены сводной таблицы.

Чтобы получить отсортированный список уникальных клиентов, используйте =SORT(UNIQUE(E2:E564))в I2.

Одна формула динамического массива для создания клиентов в нижней части отчета

Чтобы нанести продукт поверх, используйте =TRANSPOSE(SORT(UNIQUE(B2:B564)))в J1.

Для области столбцов используйте TRANSPOSE

Вот проблема: вы не знаете, насколько высок будет список клиентов. Вы не знаете, насколько широким будет список товаров. Если вы обратитесь к I2 #, Spiller автоматически обратится к текущему размеру возвращаемого массива.

Формула для возврата области значений сводной таблицы является одной формулой массива в J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

На английском это означает, что вы хотите сложить доходы от G2: G564, где клиенты в E соответствуют покупателю текущей строки из формулы массива I2, а продукты в B соответствуют текущему столбцу формулы массива в J1.

Это сладкая формула

Что, если базовые данные изменятся? Я добавил нового клиента и новый продукт, изменив эти две ячейки в источнике.

Измените некоторые ячейки в исходных данных

Отчет обновляется новыми строками и новыми столбцами. Ссылка на диапазон массивов I2 # и J1 # обрабатывает дополнительную строку и столбец.

Ваш кросс-табличный отчет автоматически дополняется новыми данными

Почему работает СУММЕСЛИМН? Это концепция в Excel под названием Broadcasting. Если у вас есть формула, относящаяся к двум массивам:

Первый массив (27 строк) x (1 столбец)
Массив два (1 строка) x (3 столбца)
Excel вернет результирующий массив высотой и шириной, равным самой высокой и самой широкой части массивов, на которые имеется ссылка:
Результат будет (27 строк) x (3 столбца).
Это называется широковещательными массивами.