Tunnetko Excelin dynaamiset matriisit?

Julkaistu Julkaistu: Blogi,Excel,Microsoft Office

Excelin dynaaminen matriisi on toiminto, joka helpottaa alueita koskevien kaavojen kirjoittamista. Voit viitata kaavassa alueisiin ja kaava myös palauttaa alueita. Aiemmin esittelemäni XHAKU kuuluu funktioihin, jotka hyödyntävät dynaamisia matriiseja. Jos olet pitkän linjan Excel-käyttäjä, saatat nyt ihmetellä kohkaamista: ainahan Excelissä on voinut viitata alueisiin, koska on olemassa matriisikaavat. Dynaamiset matriisit tekevät kuitenkin alueisiin viittaavien kaavojen kirjoittamisesta aikaisempaa helpompaa – ei enää Ctrl+Shift+Enteriä, joka viimeistään kaavaa muokattaessa unohtuu. Toiminnon huonoin puoli taitaa olla, että se on käytettävissä vain Microsoft Office 365 -tilausten mukana tulevissa Excel-versioissa (PC ja Mac).

Aloitetaan yksinkertaisesta esimerkistä. Oletaan, että jostakin syystä alueen A4:C5 soluihin pitäisi saada kaava, joka viittaa alueen A1:C2 soluihin. Arvelen, että useimmat kirjoittaisivat kaavan klikkaamalla =-merkin jälkeen solua A1, painamalla Enter ja kopioisivat sitten kaavan muihin soluihin.

Edellinen on oikea tapa sekin, mutta jos olet Microsoft Office 365 käyttäjä, kokeile tätä: valitse solu A4 ja kirjoita siihen =A1:C2. Viittaukset soluihin voit tehdä hiirellä klikkaamalla kuten aina ennenkin.

Eikä tässä vielä kaikki! Koska alueella A4:C5 nyt on dynaaminen matriisi, siihen voidaan viitata yksinkertaisella tavalla: kirjoita soluun A7 kaavaksi =A4# tai vaikkapa =SUMMA(A4#).

 

Kun klikkaat mitä tahana alueen A4:C5 solua, koko alue reunustetaan. Kaavarivi näyttää saman kaavan kaikkien alueen solujen kohdalla toisin kuin “vanhalla tavalla” tehdyissä taulukoissa.

Huomaa, että voit muokata kaavaa vain siinä solussa, johon alunperin kirjoitit sen. Jos haluat muuttaa viittauksen kattamaan alueen ensimmäisen rivin, korjaa kaava muotoon A1:C1. Huomaa myös, mitä tapahtuu alueella A7:C8.

 

Käytännön esimerkkinä olkoon kertolasku kahdessa sarakkeessa olevien lukujen välillä. Urakka ei sinällään ollut iso aikaisemminkaan. Kirjoitat kertolaskun kaavan ja kopioit sen alaspäin sarakkeessa, mutta dynaamisen matriisin avulla selviät vieläkin vähemmällä: valitse yksikköhintojen sarake, kirjoita kertomerkki väliin, valitse kappalemäärien sarake ja paina Enter.

Kertolaskukaavan kirjoittaminen.

 

Oletetaan, että oltaisiin höveleitä ja annettaisiin ostosten loppusummasta alennusta 10 %, tämän voisi laskea kaavalla =D2#-(D2#*10%). Se tarvitsee kirjoittaa vain soluun E2 – koska kaava viittaa dynaamisen matriisin osaan, se lisätään automaattisesti matriisia vastaaviin kohtiin E-sarakkeessa.

Kaava alennuksen laskemista varten.

 

Yksilöllisten arvon listaus ja lajittelu

Eräs usein kysytty Excel-kysymys kuuluu “miten saan pitkästä listauksesta näkyviin yksilölliset arvot”. Pitäisi esimerkiksi selvittää mitä eri tuotteita on myyntiraportissa ja lopputulos halutaan listata taulukkoon. Olen tarjonnut tähän aikaisemmin ratkaisuksi joko erikoissuodatusta, pivot-taulukkoraporttia tai Power Queryä. Dynaamista matriisia hyödyntävä AINUTKERTAISET.ARVOT -funktio tekee työn nopeasti. Kuvan aineistossa on 1145 riviä, mutta yksittäisiä tuotteita on huomattavasti vähemmän. Tuotteiden määrän selvittäminen ja niiden nimien listaus taulukkoon onnistuu kaavalla

=AINUTKERTAISET.ARVOT(Myynti[Tuote])

 

Yksilöllisten arvojen hakeminen.

 

Tässä tiedot sisältävä alue on muotoiltu taulukoksi. Avaavan kaarisulun jälkeen on taulukoksi muotoillun alueen nimi ja hakasulkujen sisällä sarakkeen nimi, johon viittaaminen on mukavampaa kuin solualueen D2:D1146 valinta.

Miksi ei samalla lajiteltaisi tuotevalikoimaa aakkosjärjestykseen. Siihenkin on olemassa funktio:

=LAJITTELE((AINUTKERTAISET.ARVOT(Myynti[Tuote])))

Funktionimet englanniksi: UNIQUE, SORT.

Ryhmät nopeasti

Lopuksi vielä esimerkki dynaamisia matriiseja hyödyntävän SUODATUS-funktion käytöstä. Kilpailun osallistujat on jaettu kahteen ryhmään. Kaava

=SUODATA($A$2:$A$15;$B$2:$B$15=1)

sijoittaa Ryhmään 1 kuuluvat omaan sarakkeeseensa ja kakkosryhmä saadaan vaihtamalla numeron 1 tilalle 2.

 

Esimerkki SUODATA-funktion käytöstä.

 

Kuva suodatetuista tiedoista.

 

Muistathan, että jos ryhmän tunnus olisi tekstimuotoinen, siihen viitattaessa on käytettävä lainausmerkkejä kuten kaavoissa yleensäkin.

Ja tekevälle sattuu virheitä! Dynaamiset matriisit ovat tuoneet mukanaan uuden virheilmoituksen, joten jos näet taulukossa merkinnän #LEVITTYMINEN (#SPILL!), viittaat luultavimmin kaavassa alueeseen, jossa on jo sisältöä.

 

Muita dynaamisen matriisin kanssa toimivia funktioita:

Numerosarjan palauttava JONO (SEQUENCE)

Alueen tai matriisin lajittelu vastaavan alueen tai matriisin perusteella LAJITTELE.ARVOJEN.PERUSTEELLA (SORTBY)

Satunnaislukujen matriisin palauttava SATUNN.MATRIISI (RANDARRAY)

Helpompi hakufunktio XHAKU (XLOOKUP) ks. esittely esimerkkeineen

Matriisissa olevan kohteen suhteellisen sijainnin palauttava XVASTINE (MATCH)

ageismi ajanhallinta esiintyminen esitysgrafiikka esitysgrafiikkakoulutus Evernote Excel Excel-funktio Excel-koulutus Instagram iOS itsensä johtaminen kaavio kirjat kuva Microsoft Office Microsoft Office -koulutus monipaikkatyö office 365 OneNote OneNote-koulutus oppiminen peliajattelu Pivot-taulukko PowerPoint PowerPoint-koulutus PowerPoint-vinkki presentaatio Prezi Prezi-koulutus some sosiaalinen media sparkline sparkline-kaavio Sway sähköposti tietotyö Tuolin ja näppäimistön välistä tuottavuus työssäoppiminen uusi työ visuaalinen ajattelu visualisointi webinaari yrittäjyys

Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *