Înlocuiți un tabel pivot cu 3 formule matrice dinamice - sfaturi Excel

Au trecut opt zile de când au fost anunțate formule matrice dinamice la conferința Ignite 2018 de la Orlando. Iată ce am învățat:

Modern Arrays au fost anunțate la Ignite pe 24 septembrie 2018 și denumite oficial Dynamic Arrays.
Am scris o carte electronică de 60 de pagini cu 30 de exemple de utilizare a acestora și o ofer gratuit până la sfârșitul anului 2018.
Lansarea va fi mult mai lentă decât își dorește oricine, ceea ce este frustrant. De ce atât de lent? Echipa Excel a modificat codul Calc Engine, care a fost stabil de 30 de ani. De o îngrijorare deosebită: cu programe de completare care injectează formule în Excel care au folosit din greșeală intersecția implicită. Aceste programe de completare se vor sparge dacă Excel returnează acum un interval de deversare.
Există o nouă modalitate de a face referire la intervalul returnat de o matrice: =E3#dar nu are încă un nume. # Se numește Formula deversării operatorului . Ce părere aveți despre un nume precum Spill Ref (sugerat de Excel MVP Jon Acampora) sau The Spiller (sugerat de MVP Ingeborg Hawighorst)?

În calitate de co-autor al Pivot Table Data Crunching, ador un tabel pivot bun. Dar ce se întâmplă dacă aveți nevoie pentru actualizarea tabelelor pivot și nu puteți avea încredere în managerul managerului dvs. pentru a face clic pe Reîmprospătare? Tehnica descrisă astăzi oferă o serie de trei formule pentru a înlocui un tabel pivot.

Pentru a obține o listă sortată de clienți unici, utilizați =SORT(UNIQUE(E2:E564))în I2.

O formulă matrice dinamică pentru a crea clienți în partea de jos a raportului

Pentru a pune produsul deasupra, utilizați =TRANSPOSE(SORT(UNIQUE(B2:B564)))în J1.

Pentru zona coloanelor, utilizați TRANSPOSE

Iată o problemă: nu știți cât de înaltă va fi lista de clienți. Nu știți cât de largă va fi lista de produse. Dacă faceți referire la I2 #, Spiller se va referi automat la dimensiunea curentă a matricei returnate.

Formula pentru a readuce zona valorilor din tabelul pivot este o singură formulă matrice în J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

În limba engleză, acest lucru spune că doriți să adăugați veniturile din G2: G564, unde clienții din E se potrivesc cu clientul rândului curent din formula matrice I2 și produsele din B se potrivesc cu coloana curentă a formulei matrice din J1.

Aceasta este o formulă dulce

Ce se întâmplă dacă datele subiacente se modifică? Am adăugat un client nou și un produs nou schimbând aceste două celule din sursă.

Modificați câteva celule din datele originale

Raportul se actualizează cu rânduri noi și coloane noi. Referința Array-Range pentru I2 # și J1 # gestionează rândul și coloana suplimentară.

Raportul dvs. cu filă încrucișată se extinde automat odată cu noile date

De ce funcționează SUMIFS? Acesta este un concept în Excel numit Broadcasting. Dacă aveți o formulă care se referă la două tablouri:

Array one este (27 de rânduri) x (1 coloană)
Matricea doi este (1 rând) x (3 coloane)
Excel va returna o matrice rezultată, care este la fel de înaltă și lată ca cea mai înaltă și mai largă porțiune a matricelor de referință:
Rezultatul va fi (27 de rânduri) x (3 coloane).
Aceasta se numește matrice de difuzare.