Zamijenite zaokretnu tablicu s 3 formule dinamičkih nizova - Excel savjeti

Sadržaj

Gledaj video

Prošlo je osam dana otkako su najavljene formule dinamičkih nizova na konferenciji Ignite 2018 u Orlandu. Evo što sam naučio:

Moderni nizovi najavljeni su na Igniteu 24. rujna 2018. i službeno nazvani Dynamic Arrays.
Napisao sam e-knjigu od 60 stranica s 30 primjera kako ih koristiti i nudim je besplatno do kraja 2018. godine.
Uvođenje će biti puno sporije nego što bilo tko želi, što je frustrirajuće. Zašto tako sporo? Excelov tim izvršio je promjene u kodu Calc Engine koji je stabilan 30 godina. Od posebne zabrinutosti: s dodacima koji ubrizgavaju formule u Excel koji su nenamjerno koristili implicitno presijecanje. Ti će se dodaci prekinuti ako Excel sada vrati raspon izlijevanja.
Postoji novi način pozivanja na raspon koji vraća niz: =E3#ali on još nema ime. Oznaka # naziva se Spilt Formula Operator . Što mislite o imenu poput Spill Ref (predložio Excel MVP Jon Acampora) ili The Spiller (predložio MVP Ingeborg Hawighorst)?

Kao koautor krčenja podataka pivot tablice, volim dobru pivot tablicu. Ali što ako vam trebaju pivot tablice za ažuriranje i ako ne možete vjerovati upravitelju svog upravitelja da klikne Osvježi? Danas opisana tehnika nudi niz od tri formule koje zamjenjuju zaokretnu tablicu.

Da biste dobili sortirani popis jedinstvenih kupaca, upotrijebite =SORT(UNIQUE(E2:E564))u I2.

Jedna dinamička formula niza za stvaranje kupaca sa strane izvješća

Da biste stavili proizvod preko vrha, upotrijebite =TRANSPOSE(SORT(UNIQUE(B2:B564)))u J1.

Za područje stupaca upotrijebite TRANSPOSE

Evo problema: ne znate koliko će visok biti popis kupaca. Ne znate koliko će širok biti popis proizvoda. Ako se pozivate na I2 #, Spiller će se automatski pozivati na trenutnu veličinu vraćenog polja.

Formula za povratak vrijednostima područje stožerna tablica je jedan niz formula u J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Na engleskom, ovo govori da želite dodati prihode od G2: G564 gdje se Kupci u E podudaraju s kupcem trenutnog retka iz formule niza I2, a proizvodi u B odgovaraju trenutnom stupcu formule niza u J1.

Ovo je slatka formula

Što ako se temeljni podaci promijene? Dodao sam novog kupca i novi proizvod promjenom ove dvije ćelije u izvoru.

Promijenite neke stanice u izvornim podacima

Izvješće se ažurira novim redovima i novim stupcima. Referenca raspona polja I2 # i J1 # obrađuje dodatni redak i stupac.

Izvješće s više kartica automatski se proširuje s novim podacima

Zašto SUMIFS radi? Ovo je koncept u Excelu pod nazivom Broadcasting. Ako imate formulu koja se odnosi na dva polja:

Niz jedan je (27 redaka) x (1 stupac)
Niz dva je (1 redak) x (3 stupca)
Excel će vratiti rezultatski niz koji je visok i širok kao najviši i najširi dio referenciranih nizova:
Rezultat će biti (27 redaka) x (3 stupca).
To se naziva Broadcasting nizovi.