Заменете обобщена таблица с 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)?

Като съавтор на Свиване на данни на обобщената таблица, обичам добрата обобщена таблица. Но какво, ако имате нужда от вашите обобщени таблици за актуализация и не можете да се доверите на мениджъра на вашия мениджър да щракне върху Refresh? Техниката, описана днес, предлага поредица от три формули за замяна на обобщена таблица.

За да получите сортиран списък с уникални клиенти, използвайте =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 реда) x (3 колони).
Това се нарича Излъчващи масиви.