Pivot-tabel asendatakse kolme dünaamilise massiivi valemiga - Exceli näpunäited

Lang L: none (table-of-contents)

Vaata videot

Orlandos toimunud Ignite 2018 konverentsil on dünaamiliste massiivivalemite väljakuulutamisest möödunud kaheksa päeva. Siin on see, mida olen õppinud:

Moodsad massiivid kuulutati välja Ignite'is 24. septembril 2018 ja neid kutsuti ametlikult dünaamilisteks massiivideks.
Olen kirjutanud 60-leheküljelise e-raamatu, milles on 30 näidet nende kasutamiseks, ja pakun seda tasuta kuni 2018. aasta lõpuni.
Kasutuselevõtt läheb palju aeglasemalt, kui keegi soovib, mis on pettumus. Miks nii aeglane? Exceli meeskond on teinud Calc Engine'i koodis muudatusi, mis on püsinud 30 aastat. Eriti murettekitav: lisandmoodulitega, mis süstivad Excelisse valemeid, mis tahtmatult kasutasid kaudset ristmikku. Need lisandmoodulid purunevad, kui Excel tagastab nüüd spillide vahemiku.
Massiivi tagastatud vahemikule viitamiseks on uus viis, =E3#kuid sellel pole veel nime. # Nimetatakse Mahavalgunud Vormel Operaatori . Mida arvate nimest nagu Spill Ref (soovitas Exceli MVP Jon Acampora) või The Spiller (soovitas MVP Ingeborg Hawighorst)?

Pivot Table Data Crunching kaasautorina armastan ma head pöördtabelit. Aga mis siis, kui teil on vaja oma pivot-tabeleid värskendada ja te ei saa oma halduri juhile usaldada värskendamise klõpsamist? Täna kirjeldatud tehnika pakub pöördtabeli asendamiseks kolme valemi seeriat.

Unikaalsete klientide sortitud loendi saamiseks kasutage =SORT(UNIQUE(E2:E564))jaotises I2.

Üks dünaamiline massiivivalem klientide loomiseks aruande servas

Toote ülaserva =TRANSPOSE(SORT(UNIQUE(B2:B564)))asetamiseks kasutage J1.

Veergude ala jaoks kasutage funktsiooni TRANSPOSE

Siin on probleem: te ei tea, kui pikk klientide nimekiri on. Te ei tea, kui lai on tootenimekiri. Kui viidate numbrile I2 #, viitab Spiller automaatselt tagastatud massiivi praegusele suurusele.

Valem tagastada väärtuste ala liigendtabelis on üksik- massiivivalemi in J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Inglise keeles tähendab see, et soovite lisada tulud G2: G564-st, kus kliendid E-s vastavad I2 massiivi valemi praeguse rea klientidele ja B-s olevad tooted vastavad J1 massiivi valemi praegusele veerule.

See on magus valem

Mis siis, kui alusandmed muutuvad? Lisasin uue kliendi ja uue toote, muutes need kaks lahtrit allikas.

Muutke mõnda lahtrit algandmetes

Aruannet värskendatakse uute ridade ja veergudega. Massiivivahemiku I2 # ja J1 # viide käsitleb lisarida ja veergu.

Teie vahelehtede vaheline aruanne laieneb automaatselt uute andmetega

Miks SUMIFS töötab? See on Excelis kontseptsioon, mida nimetatakse ringhäälinguks. Kui teil on valem, mis viitab kahele massiivile:

Üks massiiv on (27 rida) x (1 veerg)
Massiiv kaks on (1 rida) x (3 veergu)
Excel tagastab tulemuseks oleva massiivi, mis on sama pikk ja lai kui viidatud massiivide kõrgeim ja laiem osa:
Tulemuseks on (27 rida) x (3 veergu).
Seda nimetatakse ringhäälingumassiivideks.