Korvaa pivot-taulukko kolmella dynaamisella taulukon kaavalla - Excel-vinkit

Sisällysluettelo

Katso video

Orlandossa pidetyssä Ignite 2018 -konferenssissa on kulunut kahdeksan päivää siitä, kun dynaamiset matriisikaavat ilmoitettiin. Tässä olen oppinut:

Modernit taulukot ilmoitettiin Igniten 24. syyskuuta 2018 ja niitä kutsuttiin virallisesti dynaamisiksi ryhmiksi.
Olen kirjoittanut 60 sivun e-kirjan, jossa on 30 esimerkkiä niiden käytöstä, ja tarjoan sen ilmaiseksi vuoden 2018 loppuun asti.
Käyttöönotto tulee olemaan paljon hitaampaa kuin kukaan haluaa, mikä on turhauttavaa. Miksi niin hidas? Excel-tiimi on tehnyt muutoksia Calc Engine -koodiin, joka on ollut vakaa 30 vuoden ajan. Erityisen huolestuttava: lisäosien kanssa, jotka injektoivat kaavoja Exceliin, jotka vahingossa käyttivät implisiittistä risteystä. Nämä apuohjelmat hajoavat, jos Excel palauttaa nyt Spill-alueen.
Matriisin palauttamalle alueelle on uusi tapa viitata: =E3#mutta sillä ei vielä ole nimeä. # Kutsutaan Spilled Formula Operator . Mitä mieltä olet nimestä, kuten Spill Ref (ehdotti Excel MVP Jon Acampora) tai The Spiller (ehdotti MVP Ingeborg Hawighorst)?

Pivot Table Data Crunching -kirjoittajana rakastan hyvää kääntötaulukkoa. Mutta entä jos tarvitset pivot-taulukot päivitettäväksi etkä voi luottaa esimiehesi johtajaan napsauttamaan Päivitä? Tänään kuvattu tekniikka tarjoaa kolmen kaavan sarjan pivot-taulukon korvaamiseksi.

Saadaksesi lajiteltu luettelo yksittäisistä asiakkaista, käytä =SORT(UNIQUE(E2:E564))I2: ssä.

Yksi dynaaminen matriisikaava asiakkaiden luomiseksi raportin reunaan

Jos haluat laittaa tuotteen ylhäältä, käytä =TRANSPOSE(SORT(UNIQUE(B2:B564)))kohdassa J1.

Käytä sarakealueella TRANSPOSE

Tässä on ongelma: et tiedä kuinka pitkä asiakasluettelo tulee olemaan. Et tiedä kuinka laaja tuoteluettelo tulee olemaan. Jos viittaat kohtaan I2 #, Spiller viittaa automaattisesti palautetun taulukon nykyiseen kokoon.

Kaava, jolla palautetaan kääntötaulukon arvoalue, on yksi taulukon kaava J2: ssä =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Englanniksi tämä tarkoittaa, että haluat lisätä tulot G2: G564: stä, jossa E: n asiakkaat vastaavat nykyisen rivin asiakasta I2-taulukon kaavasta ja B: n tuotteet vastaavat J1: n taulukon kaavan nykyistä saraketta.

Tämä on makea kaava

Entä jos taustalla olevat tiedot muuttuvat? Lisäsin uuden asiakkaan ja uuden tuotteen vaihtamalla nämä kaksi solua lähteessä.

Muuta joitain soluja alkuperäisissä tiedoissa

Raportti päivitetään uusilla riveillä ja sarakkeilla. I2 #: n ja J1 #: n taulukkoalue-viite käsittelee ylimääräistä riviä ja saraketta.

Välilehtien välinen raportti laajenee automaattisesti uusien tietojen kanssa

Miksi SUMIFS toimii? Tämä on Excel-konsepti nimeltä Broadcasting. Jos sinulla on kaava, joka viittaa kahteen ryhmään:

Taulukko yksi on (27 riviä) x (1 sarake)
Taulukko kaksi on (1 rivi) x (3 saraketta)
Excel palauttaa tuloksena olevan taulukon, joka on yhtä pitkä ja leveä kuin viitattujen taulukoiden korkein ja levein osa:
Tulokseksi tulee (27 riviä) x (3 saraketta).
Tätä kutsutaan lähetysryhmiksi.