„Pivot“ lentelę pakeiskite 3 dinaminio masyvo formulėmis - „Excel“ patarimai

Praėjo aštuonios dienos, kai Orlande vykusioje „Ignite 2018“ konferencijoje buvo paskelbtos dinaminės masyvo formulės. Štai ką aš išmokau:

Šiuolaikiniai masyvai buvo paskelbti „Ignite“ 2018 m. Rugsėjo 24 d. Ir oficialiai vadinami „Dynamic Arrays“.
Parašiau 60 puslapių el. Knygą su 30 pavyzdžių, kaip jomis naudotis, ir siūlau ją nemokamai iki 2018 m. Pabaigos.
Diegimas vyks daug lėčiau nei kas nors nori, o tai kelia vargą. Kodėl taip lėtai? „Excel“ komanda atliko „Calc Engine“ kodo pakeitimus, kurie stabiliai veikia 30 metų. Ypač neramu: su priedais, kurie į „Excel“ įpurškia formules, kurios netyčia naudojo numanomą sankirtą. Šie priedai nutrūks, jei „Excel“ dabar pateiks „Spill“ diapazoną.
Yra naujas būdas nurodyti masyvo grąžintą diapazoną: =E3#tačiau jis dar neturi pavadinimo. # Vadinamas pabiro Formulės operatorius . Ką manote apie tokį pavadinimą kaip „ Spill Ref“ (pasiūlė „Excel MVP Jon Acampora“) ar „ The Spiller“ (pasiūlė MVP Ingeborg Hawighorst)?

Kaip „Pivot Table Data Crunching“ bendraautorė, aš myliu gerą sukamą lentelę. Bet ką daryti, jei jums reikia atnaujinti suvestines lenteles ir negalite patikėti, kad valdytojo valdytojas spustelės „Atnaujinti“? Šiandien aprašyta technika siūlo trijų formulių seriją, kuri pakeis sukamą lentelę.

Norėdami gauti surūšiuotą unikalių klientų sąrašą, naudokite =SORT(UNIQUE(E2:E564))I2.

Viena dinaminio masyvo formulė klientams sukurti ataskaitos šone

Norėdami įdėti gaminį į viršų, naudokite =TRANSPOSE(SORT(UNIQUE(B2:B564)))J1.

Stulpelių srityje naudokite TRANSPOSE

Čia yra problema: jūs nežinote, koks aukštas bus klientų sąrašas. Jūs nežinote, koks platus bus produktų sąrašas. Jei nurodysite I2 #, „Spiller“ automatiškai nurodys dabartinį grąžinto masyvo dydį.

Formulė grąžinti reikšmes plotas suvestinės lentelės yra vieno masyvo formulė J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Anglų kalba tai reiškia, kad norite pridėti pajamas iš G2: G564, kai klientai E atitinka dabartinės eilutės klientą iš I2 masyvo formulės, o B produktai - dabartinį masyvo formulės stulpelį J1.

Tai saldi formulė

Ką daryti, jei pasikeis pagrindiniai duomenys? Pridėjau naują klientą ir naują produktą, pakeisdama šias dvi langelio langelio vietas.

Pakeiskite kai kurias pirminių duomenų langelius

Ataskaitoje atnaujinamos naujos eilutės ir stulpeliai. Masyvo diapazono nuoroda I2 # ir J1 # tvarko papildomą eilutę ir stulpelį.

Kryžminių skirtukų ataskaita automatiškai išsiplečia su naujais duomenimis

Kodėl veikia SUMIFS? Tai yra „Excel“ koncepcija, vadinama transliacija. Jei turite formulę, nurodančią du masyvus:

Masyvas yra (27 eilutės) x (1 stulpelis)
Antrasis masyvas yra (1 eilutė) x (3 stulpeliai)
„Excel“ grąžins gautą masyvą, kuris yra toks pat aukštas ir platus kaip aukščiausia ir plačiausia nurodytų masyvų dalis:
Rezultatas bus (27 eilutės) x (3 stulpeliai).
Tai vadinama transliavimo masyvais.