Excel

Dynaamiset matriisikaavat Excelissä

Dynamic Array Formulas Excel

Dynaamiset taulukot ovat suurin muutos Excel -kaavoihin vuosiin. Ehkä suurin muutos ikinä. Tämä johtuu siitä, että dynaamisten matriisien avulla voit helposti käsitellä useita arvoja samanaikaisesti kaavassa. Monille käyttäjille se on ensimmäinen kerta, kun he ymmärtävät ja käyttävät matriisikaavoja.





Tämä on suuri päivitys ja tervetullut muutos. Dynaamiset taulukot ratkaisevat todella vaikeita Excelin ongelmia ja muuttavat perusteellisesti laskentataulukoiden suunnittelua ja rakennetta.

Saatavuus

Dynaamiset taulukot ja alla olevat uudet toiminnot ovat käytettävissä vain Excel 365 . Excel 2016 ja Excel 2019 eivät tarjoa dynaamisen matriisikaavan tukea. Kätevyyden vuoksi erotan alla olevat versiot dynaamisella Excelillä (Excel 365) ja perinteisellä Excelillä (2019 tai aikaisempi).





Uusi: Dynamic Array Formula -videokoulutus

Uudet toiminnot

Osana dynaamisen taulukon päivitystä Excel sisältää nyt 8 uutta toimintoa, jotka hyödyntävät dynaamisia matriiseja suoraan ongelmien ratkaisemiseen, joita perinteisesti on vaikea ratkaista tavanomaisilla kaavoilla. Napsauta alla olevia linkkejä saadaksesi lisätietoja ja esimerkkejä jokaisesta toiminnosta:

Toiminto Tarkoitus
SUODATTAA Suodata tiedot ja palauta vastaavat tietueet
RANDARRAY Luo joukko satunnaislukuja
JÄRJESTYS Luo matriisi peräkkäisiä numeroita
JÄRJESTELLÄ Lajittele alue sarakkeen mukaan
JÄRJESTÄ Lajittele alue toisen alueen tai taulukon mukaan
AINUTLAATUINEN Pura ainutlaatuiset arvot luettelosta tai alueesta
XLOOKUP Moderni korvaaja VLOOKUPille
XMATCH Moderni korvaaja MATCH -toiminnolle

Video: Uusia dynaamisia matriisitoimintoja Excelissä (noin 3 minuuttia).



excel lisää nykyinen päivämäärä ja kellonaika

Huomautus: XLOOKUP ja XMATCH eivät olleet alkuperäisten uusien dynaamisten matriisitoimintojen ryhmässä, mutta ne toimivat erinomaisesti uudessa dynaamisessa matriisimoottorissa. XLOOKUP korvaa VLOOKUPin ja tarjoaa modernin, joustavan lähestymistavan, joka hyödyntää matriiseja. XMATCH on päivitys MATCH -toimintoon, joka tarjoaa uusia ominaisuuksia INDEKSI ja MATCH kaavoja.

Esimerkki

Ennen kuin menemme yksityiskohtiin, katsotaanpa yksinkertaista esimerkkiä. Alla käytämme uutta UNIQUE -toiminto poimia ainutlaatuisia arvoja alueelta B5: B15, a yksittäinen kaava syötetty E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

Esimerkki UNIQUE -toiminnosta

Tuloksena on luettelo viidestä ainutlaatuisesta kaupungin nimestä, jotka näkyvät kohdassa E5: E9.

Kuten kaikki kaavat, UNIQUE päivittyy automaattisesti, kun tiedot muuttuvat. Alla Vancouver on korvannut Portlandin rivillä 11. UNIQUEn tulos sisältää nyt Vancouverin:

UNIQUE -toiminnon esimerkki muutoksen jälkeen

Valuminen - yksi kaava, monia arvoja

Dynaamisessa Excelissä kaavat, jotka palauttavat useita arvoja, peli 'nämä arvot suoraan laskentataulukkoon. Tämä on heti loogisempaa kaavan käyttäjille. Se on myös täysin dynaaminen käyttäytyminen - kun lähdetiedot muuttuvat, vuotaneet tulokset päivittyvät välittömästi.

Suorakulmio, joka sulkee arvot, kutsutaan pelivalikoima ''. Huomaat, että vuotoalueella on erityinen korostus. Yllä olevassa UNIQUE -esimerkissä vuotoalue on E5: E10.

Kun tiedot muuttuvat, vuotoväli laajenee tai supistuu tarpeen mukaan. Saatat nähdä uusia arvoja lisättyinä tai olemassa olevat arvot katoavat. Tällä tavalla vuotoalue on uudenlainen dynaaminen alue.

Huomautus: kun muut tiedot estävät vuotamisen, näet #SPILL -virheen. Kun teet tilaa vuotoalueelle, kaava vuotaa automaattisesti.

Video: Roiskeet ja vuotoalue

Viitealueen viite

Jos haluat viitata vuotoalueeseen, käytä hash -symbolia (#) alueen ensimmäisen solun jälkeen. Esimerkiksi, jos haluat viitata UNIQUE -toiminnon tuloksiin yllä, käytä:

 
=E5# // reference UNIQUE results

Tämä on sama kuin viittaaminen koko vuotoalueeseen, ja näet tämän syntaksin, kun kirjoitat kaavan, joka viittaa täydelliseen vuotoalueeseen.

Voit syöttää vuotoalueen viitteen suoraan muihin kaavoihin. Voit esimerkiksi laskea UNIQUEn palauttamien kaupunkien määrän käyttämällä

 
= COUNTA (E5#) // count unique cities

Esimerkki dynaamisen taulukon vuotoalueviitteestä

Kun vuotoalue muuttuu, kaava heijastaa uusimmat tiedot.

Massiivinen yksinkertaistaminen

Uusien dynaamisten matriisikaavojen lisääminen tarkoittaa, että tiettyjä kaavoja voidaan yksinkertaistaa huomattavasti. Tässä muutama esimerkki:

  • Pura ja listaa ainutlaatuiset arvot ( ennen | jälkeen )
  • Laske ainutlaatuiset arvot ( ennen | jälkeen )
  • Suodata ja poista tietueet ( ennen | jälkeen )
  • Pura osittaisia ​​osumia ( ennen | jälkeen )

Yhden voima

Yksi yhden kaavan, monia arvoja -lähestymistavan tehokkaimmista eduista on vähemmän riippuvuus ehdoton tai sekoitettu viittauksia. Kun dynaaminen matriisikaava levittää tulokset laskentataulukolle, viittaukset pysyvät muuttumattomina, mutta kaava tuottaa oikeat tulokset.

Esimerkiksi alla käytämme FILTER -funktiota poimiaksesi tietueita ryhmästä A. Soluun F5 syötetään yksi kaava:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

Esimerkki dynaamisesta taulukosta, vain yksi kaava

Huomaa, että molemmat alueet ovat lukitsemattomia suhteellisia viittauksia, mutta kaava toimii täydellisesti.

Tämä on valtava etu monille käyttäjille, koska se tekee kaavojen kirjoittamisesta paljon yksinkertaisempaa. Katso toinen hyvä esimerkki alla olevasta kertolaskusta.

Ketjutustoiminnot

Asiat muuttuvat todella mielenkiintoisiksi, kun ketjutat yhteen useamman kuin yhden dynaamisen matriisitoiminnon. Ehkä haluat lajitella UNIQUEn palauttamat tulokset? Helppo. Kääri vain SORT -toiminto UNIQUE -toiminnon ympärillä näin:

Esimerkki UNIQUE ja SORT yhdessä

Kuten ennenkin, lähdetietojen muuttuessa uudet ainutlaatuiset tulokset näkyvät automaattisesti hienosti lajiteltuina.

Alkuperäinen käyttäytyminen

On tärkeää ymmärtää, että dynaaminen matriisikäyttäytyminen on a kotoisin ja syvälle integroitunut . Kun minkä tahansa kaava palauttaa useita tuloksia, nämä tulokset leviävät useisiin laskentataulukon soluihin. Tämä sisältää vanhemmat toiminnot, joita ei ole alun perin suunniteltu toimimaan dynaamisten matriisien kanssa.

Esimerkiksi perinteisessä Excelissä, jos annamme LEN -toiminto kohteeseen valikoima tekstiarvoista, näemme a yksittäinen tulos. Dynamic Excelissä, jos annamme LEN -funktiolle arvoalue, näemme useita tuloksia. Tässä alla olevassa näytössä näkyy vanha käyttäytyminen vasemmalla ja uusi käyttäytyminen oikealla:

LEN -toiminto matriiseilla - vanhoja ja uusia

Tämä on valtava muutos, joka voi vaikuttaa kaikenlaisiin kaavoihin. Esimerkiksi VLOOKUP -toiminto on suunniteltu hakemaan yksittäinen arvo taulukosta sarakeindeksin avulla. Jos kuitenkin Dynamic Excelissä annamme VLOOKUPille useamman kuin yhden sarakehakemiston käyttämällä matriisi vakio kuten tämä:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP palauttaa useita sarakkeita:

Useita tuloksia VLOOKUP ja dynaamiset taulukot

Toisin sanoen, vaikka VLOOKUPia ei ole koskaan suunniteltu palauttamaan useita arvoja, se voi nyt tehdä sen Dynamic Excelin uuden kaavamoottorin ansiosta.

Kaikki kaavat

Huomaa lopuksi, että dynaamiset taulukot toimivat kaikki kaavat ei vain toimintoja . Alla olevassa esimerkissä solu C5 sisältää yhden kaavan:

miten saada excel tilausnumeroihin
 
=B5:B14*C4:L4

Tulos leviää 10 x 10 alueeseen, joka sisältää 100 solua:

Dynaamisen matriisin kertotaulukko

Huomautus: Perinteisessä Excelissä voit nähdä useita tuloksia, jotka palautetaan taulukkokaavalla, jos tarkista kaava F9: n avulla . Mutta ellet syötä kaavaa muodossa monisoluinen matriisikaava , vain yksi arvo näkyy laskentataulukossa.

Taulukot menevät valtavirtaan

Dynaamisten matriisien käyttöönoton myötä sana ' matriisi 'tulee esiin paljon useammin. Itse asiassa saatat nähdä `` array '' ja `` range '', joita käytetään lähes keskenään. Näet Excelin taulukot, jotka on suljettu kiharaisiin aaltosulkeisiin:

 
{1,2,3} // horizontal array {123} // vertical array

Taulukko on ohjelmointitermi, joka viittaa luetteloon kohteista, jotka näkyvät tietyssä järjestyksessä. Syy, miksi taulukot tulevat niin usein esiin Excel -kaavoissa, on se, että taulukot voivat ilmaisevat täydellisesti arvot solualueella .

Video: Mikä on taulukko?

Array -toiminnoista tulee tärkeitä

Koska dynaamiset Excel -kaavat voivat helposti työskennellä useiden arvojen kanssa, matriisitoiminnoista tulee tärkeämpiä. Termi 'matriisitoiminto' viittaa lausekkeeseen, joka suorittaa loogisen testin tai matemaattisen operaation matriisissa. Esimerkiksi alla oleva lauseke testaa, ovatko B5: B9: n arvot yhtä suuret kuin 'ca'

 
=B5:B9='ca' // state = 'ca'

Ryhmän toiminnan esimerkkitesti a

koska B5: B9: ssä on 5 solua, tuloksena on 5 TRUE/FALSE -arvoa taulukossa:

 
{FALSETRUEFALSETRUETRUE}

Alla oleva taulukkotoiminto tarkistaa yli 100: n summat:

 
=C5:C9>100 // amounts > 100

Ryhmän toiminnan esimerkki testi b

Viimeinen matriisitoiminto yhdistää testin A ja testin B yhdeksi lausekkeeksi:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

Ryhmän toimintaesimerkki testi a ja b

Huomautus: Excel pakottaa TOSI- ja EPÄTOSI -arvot automaattisesti arvoon 1 ja 0 matemaattisen operaation aikana.

Palauttaaksemme tämän takaisin Excelin dynaamisiin taulukkokaavoihin alla oleva esimerkki osoittaa, kuinka voimme käyttää täsmälleen samaa taulukkotoimintoa SUODATIN -toiminnossa kuin sisältää Perustelu:

Ryhmäkäyttö FILTER -toiminnolla

FILTER palauttaa kaksi tietuetta, joissa tila = 'ca' ja summa> 100.

Katso esittely: Kuinka suodattaa kahdella kriteerillä (video).

Uudet ja vanhat matriisikaavat

Dynaamisessa Excelissä taulukkokaavoja ei tarvitse syöttää control + shift + enter. Kun kaava luodaan, Excel tarkistaa, saattaako kaava palauttaa useita arvoja. Jos näin on, se tallennetaan automaattisesti dynaamisen matriisin kaavaksi, mutta et näe kaarevia aaltosulkeita. Alla olevassa esimerkissä näkyy tyypillinen taulukkokaava, joka on syötetty dynaamiseen Exceliin:

Perusmatriisikaava perinteisessä Excelissä

Jos avaat saman kaavan perinteisessä Excelissä, näet kiharat aaltosulkeet:

Perusmatriisikaava dynaamisessa Excelissä

Toiseen suuntaan, kun 'perinteinen' matriisikaava avataan Dynamic Excelissä, näet kaarevat palkit kaavapalkissa. Esimerkiksi alla oleva näyttö näyttää yksinkertaisen taulukkokaavan perinteisessä Excelissä:

Yksinkertainen matriisikaava, jossa on kiharat aaltosulkeet

Jos syötät kaavan kuitenkin uudelleen ilman muutoksia, kiharat poistetaan ja kaava palauttaa saman tuloksen:

Yksinkertainen matriisikaava, jossa kiharat aaltosulkeet eivät näy

Tärkeintä on, että taulukon kaavat, jotka on syötetty näppäimellä control + shift + enter (CSE), toimivat edelleen yhteensopivuuden ylläpitämiseksi, mutta sinun ei tarvitse syöttää taulukkokaavoja CSE: n kanssa dynaamisessa Excelissä.

Hahmo

Kun otat käyttöön dynaamiset taulukot, näet @ -merkin esiintyvän useammin kaavoissa. @ -Merkki mahdollistaa käyttäytymisen, joka tunnetaan nimellä implisiittinen risteys ''. Implisiittinen leikkaus on looginen prosessi, jossa monet arvot pienennetään yhdeksi arvoksi.

Perinteisessä Excelissä implisiittinen leikkaus on hiljainen toiminta, jota käytetään (tarvittaessa) useiden arvojen pienentämiseen yhdeksi tulokseksi yhdessä solussa. Dynaamisessa Excelissä sitä ei yleensä tarvita, koska laskentataulukkoon voi kaatua useita tuloksia. Tarvittaessa implisiittistä leikkausta kutsutaan manuaalisesti @ -merkillä.

Kun avaat laskentataulukoita, jotka loivat vanhemman Excel -version, saatat nähdä, että @ -merkki lisätään automaattisesti olemassa oleviin kaavoihin, joissa on potentiaalia palauttaa monia arvoja. Perinteisessä Excelissä kaava, joka palauttaa useita arvoja, ei lähde laskentataulukkoon. @ -Merkki pakottaa saman toiminnan Dynamic Excelissä niin, että kaava käyttäytyy samalla tavalla ja palauttaa saman tuloksen kuin alkuperäisessä Excel -versiossa.

Toisin sanoen @ lisätään, jotta vanhempi kaava ei kaada useita tuloksia laskentataulukolle. Kaavasta riippuen voit ehkä poistaa @ -merkin, eikä kaavan toiminta muutu.

Yhteenveto

  • Dynaamiset taulukot tekevät tiettyjen kaavojen kirjoittamisesta paljon helpompaa.
  • Voit nyt suodattaa vastaavia tietoja, lajitella ja poimia yksilöllisiä arvoja helposti kaavojen avulla.
  • Dynaamisen taulukon kaavat voidaan ketjuttaa (sisäkkäin) esimerkiksi suodattaa ja lajitella.
  • Kaavat, jotka palauttavat useamman kuin yhden arvon, leviävät automaattisesti.
  • Ei tarvitse käyttää näppäinyhdistelmää Ctrl+Vaihto+Enter kirjoittaaksesi taulukkokaavan.
  • Dynaamiset matriisikaavat ovat käytettävissä vain Excel 365: ssä.
Kirjailija Dave Bruns


^