Замените пивот табелу са 3 формуле динамичких низова - Екцел савети

Преглед садржаја

Погледајте видео

Прошло је осам дана од објављивања формула динамичких низова на конференцији Игните 2018 у Орланду. Ево шта сам научио:

Савремени низови су најављени на Игните-у 24. септембра 2018. године и званично названи Динамиц Арраис.
Написао сам е-књигу од 60 страница са 30 примера како да их користим и нудим је бесплатно до краја 2018. године.
Увођење ће бити много спорије него што било ко жели, што је фрустрирајуће. Зашто тако споро? Екцел тим је извршио промене у Цалц Енгине коду који је стабилан 30 година. Посебно забрињавајуће: са програмским додацима који убризгавају формуле у Екцел који су ненамерно користили имплицитну пресек. Ти додаци ће се покварити ако Екцел сада врати опсег изливања.
Постоји нови начин позивања на опсег који је вратио низ: =E3#али он још увек нема име. Ознака # назива се Спилт Формула Оператор . Шта мислите о имену попут Спилл Реф (предложио Екцел МВП Јон Ацампора) или Тхе Спиллер (предложио МВП Ингеборг Хавигхорст)?

Као коаутор крчења података пивот табела, волим добру пивот табелу. Али шта ако вам требају пивот табеле за ажурирање и ако не можете да верујете менаџеру менаџера да кликне на Рефресх? Данас описана техника нуди низ од три формуле које замењују стожерну табелу.

Да бисте добили сортирану листу јединствених купаца, користите =SORT(UNIQUE(E2:E564))у И2.

Једна динамичка формула низа за стварање купаца са стране извештаја

Да бисте ставили производ преко врха, користите =TRANSPOSE(SORT(UNIQUE(B2:B564)))у Ј1.

За подручје ступаца користите ТРАНЗОС

Ево проблема: не знате колика ће бити листа купаца. Не знате колико ће широка бити листа производа. Ако се позивате на И2 #, Спиллер ће се аутоматски позивати на тренутну величину враћеног низа.

Формула да врати вредности подручје вешања стола је један низ формула у Ј2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

На енглеском ово говори да желите да додате приходе од Г2: Г564 где се Купци у Е подударају са купцем тренутног реда из формуле низа И2, а производи из групе Б одговарају тренутној колони формуле низа у Ј1.

Ово је слатка формула

Шта ако се основни подаци промене? Додао сам новог купца и нови производ променом ове две ћелије у извору.

Промените неке ћелије у оригиналним подацима

Извештај се ажурира новим редовима и новим колонама. Референца опсега низова за И2 # и Ј1 # обрађује додатне редове и колоне.

Извештај на више картица аутоматски се проширује са новим подацима

Зашто СУМИФС ради? Ово је концепт у програму Екцел који се зове Броадцастинг. Ако имате формулу која се односи на два низа:

Низ један је (27 редова) к (1 колона)
Низ два је (1 ред) к (3 колоне)
Екцел ће вратити резултатски низ који је висок и широк као највиши и најшири део референцираних низова:
Резултат ће бити (27 редова) к (3 колоне).
То се назива Броадцастинг низови.