Yhteenvetotaulukot dynaamisilla matriiseilla

UNIQUE-funktio ottaa alueen ja palauttaa kustakin arvosta vain yhden esiintymän. Se levittää tuloksen, eli yksi kaava täyttää niin monta solua kuin yksilöllisiä arvoja on.

Kirjoittakaa tämä soluun E2, niin alueluettelo ilmestyy automaattisesti sen alapuolelle:

Jos dataan lisätään myöhemmin uusi alue, leviävä luettelo laajenee itsestään. Kaavaa ei tarvitse koskaan muokata.

Seuraavaksi tarvitsemme kunkin sarakkeen E alueen kokonaismäärän. SUMIFS laskee yhden alueen arvot yhteen vain, kun toisen alueen arvo vastaa ehtoa.

Rakenne on SUMIFS(sum_range, criteria_range, criteria). Sijoittakaa tämä soluun F2 ensimmäisen alueen viereen:

E2#-viittaus on tässä ratkaiseva. #-merkki tarkoittaa koko solusta E2 alkavaa leviävää aluetta. Tällä yhdellä kaavalla lasketaan siis kaikkien UNIQUE-funktion tuottamien alueiden summat.

Leviämisviittaus E2# osoittaa aina kaavan tuottamaan koko lohkoon riippumatta siitä, kuinka suureksi lohko kasvaa. Tämä tekee yhteenvedosta dynaamisen.

Kun UNIQUE löytää kolme aluetta, E2# on kolmen solun korkuinen ja SUMIFS palauttaa kolme summaa. Kun data kasvaa viiteen alueeseen, molemmat alueet laajenevat yhdessä ilman yhtäkään muokkausta.

• E2 = vain yksittäinen ylin solu.
• E2# = koko solusta E2 alkava leviävä matriisi.

Totutelkaa #-merkkiin, sillä se on koontinäyttökaavojen ydin.

Yhteenvetoa on helpompi lukea, kun summat ovat järjestyksessä. Lajitelkaa alueluettelo SORT-funktion avulla, jotta luokat näkyvät aakkosjärjestyksessä, tai lajitelkaa koko taulukko summan perusteella.

Voitte luetella alueet aakkosjärjestyksessä solussa E2:

Koska sarakkeen F summat viittaavat edelleen alueeseen E2#, alueiden lajittelu kohdistaa summat automaattisesti uudelleen. Sarakkeet pysyvät synkronoituina.

Joskus tarvitsette yhden luokan alkuperäiset rivit pelkän summan sijaan. FILTER palauttaa kaikki ehdon täyttävät rivit ja levittää ne taulukkoon.

Voitte näyttää kaikki myyntirivit, joilla alue vastaa solun H1 arvoa:

Jos H1 sisältää arvon East, näette kaikki East-alueen rivit. Kun vaihdatte H1:n arvoksi West, lohko päivittyy heti. Tämä on koontinäytön porautumisnäkymän perusta.

FILTER antaa #CALC!-virheen, kun vastaavia rivejä ei ole. Voitte pitää näkymän siistinä antamalla valinnaisen kolmannen argumentin varaviestiksi.

Kolmas argumentti näytetään, kun vastaavia rivejä ei löydy:

Nyt alue, jolla ei ole myyntiä, näyttää virheen sijaan selkeän ilmoituksen. Lisätkää tämä varavaihtoehto aina koontinäyttöihin, jotta yksittäinen virheellinen valinta ei riko asettelua.

Yhteenveto näyttää usein myös kunkin alueen tilausten määrän, ei ainoastaan rahamäärää. COUNTIFS laskee ehdon täyttävät rivit samaan tapaan kuin SUMIFS, mutta ilman summa-aluetta.

Sijoittakaa tämä sarakkeeseen G summien viereen:

Nyt kolmisarakkeinen yhteenveto sisältää alueen, myynnin summan ja tilausten määrän. Kaikki perustuu yhteen solussa E2# olevaan leviävään alueluetteloon, joten kaikki päivittyy yhdessä.

Tässä on koko ohje vierekkäin:

• E2: =SORT(UNIQUE(Sales!A2:A200)) luettelee alueet.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) summaa kunkin alueen.
• G2: =COUNTIFS(Sales!A2:A200, E2#) laskee kunkin alueen rivit.

Vain E2-kaava kirjoitetaan; F- ja G-sarakkeet leviävät #-viittauksen perusteella. Kun lisäätte uuden myynnin mihin tahansa Sales-taulukossa, kaikki kolme saraketta päivittyvät ilman napsautuksia.

Kaavoihin perustuvalla yhteenvedolla on selviä etuja arvojen kirjoittamiseen tai pivot-taulukon päivittämiseen verrattuna:

• Reaaliaikainen: se laskee tulokset uudelleen heti datan muuttuessa.
• Automaattisesti mitoitettu: uudet luokat ilmestyvät automaattisesti UNIQUE-funktion ja #-viittauksen avulla.
• Läpinäkyvä: kuka tahansa voi lukea solussa olevan logiikan.

Haittapuolena on, että leviävät alueet tarvitsevat ympärilleen tyhjää tilaa kasvaakseen. Käsittelemme estyneitä leviämisiä myöhemmällä oppitunnilla. Jättäkää toistaiseksi kaavojen alapuolelle riittävästi tilaa.

Rakensitte yhteenvetotaulukon, joka ylläpitää itse itseään:

• UNIQUE luettelee kunkin luokan kerran ja levittää tuloksen.
• SORT järjestää luettelon luettavuuden parantamiseksi.
• SUMIFS ja COUNTIFS summaavat ja laskevat kunkin luokan käyttämällä E2#-leviämisviittausta.
• FILTER hakee vastaavat rivit porautumista varten ja näyttää varaviestin, jos vastaavia rivejä ei ole.

Koska kaikki kaavat perustuvat leviävään luetteloon, uusien tietojen lisääminen päivittää koko yhteenvedon ilman manuaalisia toimia. Seuraavaksi luotte kokonaisia pivot-tyylisiä raportteja pelkillä kaavoilla.