Замініть зведену таблицю на 3 формули динамічного масиву - Поради Excel

Минуло вісім днів з моменту оголошення динамічних формул масивів на конференції Ignite 2018 в Орландо. Ось що я дізнався:

Сучасні масиви були анонсовані на Ignite 24 вересня 2018 року і офіційно названі Dynamic Arrays.
Я написав 60-сторінкову електронну книгу з 30 прикладами, як ними користуватися, і пропоную її безкоштовно до кінця 2018 року.
Розгортання буде набагато повільнішим, ніж хто хоче, що засмучує. Чому так повільно? Команда Excel внесла зміни до коду Calc Engine, який стабільний протягом 30 років. Особливе занепокоєння: з надбудовами, які вводять формули в Excel, які ненавмисно використовували неявний перетин. Ці надбудови зламаються, якщо Excel тепер поверне діапазон розливу.
Існує новий спосіб посилатися на діапазон, повернутий масивом: =E3#але він ще не має назви. # Називається Пролита Формула оператора . Що ви думаєте про таку назву, як Spill Ref (запропонована Excel MVP Jon Acampora) або The Spiller (запропонована MVP Ingeborg Hawighorst)?

Як співавтор зведення даних зведеної таблиці, я люблю хорошу зведену таблицю. Але що, якщо вам потрібні ваші зведені таблиці для оновлення, і ви не можете довірити менеджеру свого менеджера натиснути кнопку «Оновити»? Техніка, описана сьогодні, пропонує серію з трьох формул, які замінюють зведену таблицю.

Щоб отримати відсортований список унікальних клієнтів, використовуйте =SORT(UNIQUE(E2:E564))в I2.

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

Для розміщення продукту зверху, використовуйте =TRANSPOSE(SORT(UNIQUE(B2:B564)))в J1.

Для області стовпців використовуйте ПРОЗОРУВАТИ

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

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

По-англійськи це означає, що ви хочете додати доходи від G2: G564, де Клієнти в E відповідають клієнту поточного рядка з формули масиву I2, а продукти в B відповідають поточному стовпцю формули масиву в J1.

Це солодка формула

Що робити, якщо базові дані змінюються? Я додав нового клієнта та новий продукт, змінивши ці дві клітинки у джерелі.

Змініть деякі комірки у вихідних даних

Звіт оновлюється новими рядками та новими стовпцями. Посилання на діапазон масивів I2 # та J1 # обробляє зайвий рядок та стовпець.

Ваш звіт з перехресними вкладками автоматично розширюється разом із новими даними

Чому працює SUMIFS? Це концепція в Excel під назвою Broadcasting. Якщо у вас є формула, яка посилається на два масиви:

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