Vrtilno tabelo zamenjajte s 3 formulami dinamičnih nizov - Excel Nasveti

Kazalo

Oglejte si video

Mineva osem dni od razglasitve formul dinamičnih nizov na konferenci Ignite 2018 v Orlandu. Tukaj sem se naučil:

Sodobni nizi so bili napovedani na Ignite 24. septembra 2018 in so se uradno imenovali Dynamic Arrays.
Napisal sem 60-stransko e-knjigo s 30 primeri, kako jih uporabljati, in jo do konca leta 2018 ponujam brezplačno.
Uvajanje bo veliko počasneje, kot si kdo želi, kar je frustrirajoče. Zakaj tako počasi? Skupina Excel je spremenila kodo Calc Engine, ki je stabilna že 30 let. Še posebej zaskrbljujoče: z dodatki, ki vbrizgajo formule v Excel, ki so nehote uporabili implicitno presečišče. Ti dodatki se bodo pokvarili, če Excel zdaj vrne obseg razlitja.
Obstaja nov način sklicevanja na obseg, ki ga vrne matrika: =E3#vendar še nima imena. # Se imenuje razliva Formula operaterja . Kaj menite o imenu, kot je Spill Ref (predlagal Excel MVP Jon Acampora) ali The Spiller (predlagal MVP Ingeborg Hawighorst)?

Kot soavtor krčenja podatkov vrtilne tabele imam rad dobro vrtilno tabelo. Kaj pa, če potrebujete vrtilne tabele za posodobitev in ne morete zaupati upravitelju svojega upravitelja, da klikne Osveži? Danes opisana tehnika ponuja vrsto treh formul za zamenjavo vrtilne tabele.

Če želite dobiti razvrščen seznam edinstvenih strank, uporabite =SORT(UNIQUE(E2:E564))v I2.

Ena dinamična matrična formula za ustvarjanje strank ob strani poročila

Če želite izdelek postaviti na vrh, uporabite =TRANSPOSE(SORT(UNIQUE(B2:B564)))v J1.

Za območje stolpcev uporabite TRANSPOSE

Tu je težava: ne veste, kako visok bo seznam strank. Ne veste, kako širok bo seznam izdelkov. Če se sklicujete na I2 #, se bo Spiller samodejno skliceval na trenutno velikost vrnjene matrike.

Formula za vrnitev Vrednosti površino vrtilni tabeli enojna matrika formulo v J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

V angleščini to pomeni, da želite dodati prihodke od G2: G564, kjer se stranke v E ujemajo s stranko trenutne vrstice iz formule matrike I2, proizvodi v B pa s trenutnim stolpcem formule matrike v J1.

To je sladka formula

Kaj, če se osnovni podatki spremenijo? S spremembo teh dveh celic v izvoru sem dodal novo stranko in nov izdelek.

Spremenite nekaj celic v prvotnih podatkih

Poročilo se posodobi z novimi vrsticami in novimi stolpci. Referenca obsega matrike I2 # in J1 # obravnava odvečno vrstico in stolpec.

Poročilo o zavihkih se samodejno razširi z novimi podatki

Zakaj SUMIFS deluje? To je koncept v Excelu, imenovan Broadcasting. Če imate formulo, ki se nanaša na dva polja:

Polje ena je (27 vrstic) x (1 stolpec)
Polje dva je (1 vrstica) x (3 stolpci)
Excel bo vrnil nastalo matriko, ki je visoka in široka kot najvišji in najširši del referenčnih nizov:
Rezultat bo (27 vrstic) x (3 stolpci).
To se imenuje Broadcasting matrike.