Sisällys
Mitä ovat dynaamiset taulukot
Syyskuussa 2018 Microsoft julkaisi päivityksen, joka lisää täysin uuden työkalun Microsoft Exceliin: Dynaamiset taulukot ja 7 uutta toimintoa niiden kanssa työskentelemiseen. Nämä asiat, liioittelematta, muuttavat radikaalisti kaiken tavanomaisen kaavojen ja funktioiden työskentelytekniikan ja koskevat kirjaimellisesti jokaista käyttäjää.
Harkitse yksinkertaista esimerkkiä selittääksesi olemuksen.
Oletetaan, että meillä on yksinkertainen taulukko kaupunkikuukausien tiedoista. Mitä tapahtuu, jos valitsemme minkä tahansa tyhjän solun arkin oikealta puolelta ja syötämme siihen kaavan, joka ei linkitä yhteen soluun, vaan välittömästi alueeseen?
Kaikissa aiemmissa Excel-versioissa napsautuksen jälkeen enter saisimme vain yhden ensimmäisen solun B2 sisällön. Kuinka muuten?
No, tai olisi mahdollista kääriä tämä alue johonkin aggregointifunktioon, kuten =SUM(B2:C4) ja saada siitä loppusumma.
Jos tarvitsimme monimutkaisempia operaatioita kuin primitiivinen summa, kuten yksilöllisten arvojen tai Top 3:n poimiminen, meidän on syötettävä kaavamme taulukkokaavana käyttämällä pikanäppäintä Ctrl+siirtyminen+enter.
Nyt kaikki on toisin.
Nyt kun olet syöttänyt tällaisen kaavan, voimme yksinkertaisesti napsauttaa enter – ja saat sen seurauksena välittömästi kaikki arvot uXNUMXbuXNUMXb, joihin viittasimme:
Tämä ei ole taikuutta, vaan Microsoft Excelin uudet dynaamiset taulukot. Tervetuloa uuteen maailmaan 🙂
Dynaamisten taulukoiden kanssa työskentelyn ominaisuudet
Teknisesti koko dynaaminen matriisi on tallennettu ensimmäiseen soluun G4, joka täyttää tarvittavan määrän soluja oikealle ja alas tiedoillaan. Jos valitset taulukosta jonkin muun solun, kaavapalkin linkki ei ole aktiivinen, mikä osoittaa, että olemme jossakin "lapsi"-soluista:
Yritys poistaa yksi tai useampi "lapsi" ei johda mihinkään – Excel laskee ja täyttää ne välittömästi uudelleen.
Samaan aikaan voimme turvallisesti viitata näihin "lapsi"-soluihin muissa kaavoissa:
Jos kopioit taulukon ensimmäisen solun (esimerkiksi G4:stä F8:aan), koko taulukko (sen viittaukset) liikkuu samaan suuntaan kuin tavallisissa kaavoissa:
Jos meidän on siirrettävä taulukkoa, se riittää siirtämään (hiirellä tai niiden yhdistelmällä Ctrl+X, Ctrl+V), taas vain ensimmäinen pääsolu G4 – sen jälkeen se siirretään uuteen paikkaan ja koko joukkomme laajenee jälleen.
Jos haluat viitata luotuun dynaamiseen taulukkoon jonnekin muualle arkille, voit käyttää erikoismerkkiä # ("punta") sen johtavan solun osoitteen jälkeen:
Voit esimerkiksi nyt helposti tehdä avattavasta luettelosta soluun, joka viittaa luotuun dynaamiseen taulukkoon:
Dynaamiset taulukon virheet
Mutta mitä tapahtuu, jos tilaa ei ole tarpeeksi taulukon laajentamiseen tai jos sen polulla on soluja, jotka ovat jo muiden tietojen varassa? Tapaa täysin uudenlaiset virheet Excelissä – #SIIRTÄÄ! (#LÄIKKYMINEN!):
Kuten aina, jos napsautamme kuvaketta, jossa on keltainen timantti ja huutomerkki, saamme tarkemman selityksen ongelman lähteestä ja löydämme nopeasti häiritsevät solut:
Samanlaisia virheitä tapahtuu, jos taulukko siirtyy pois arkilta tai osuu yhdistettyyn soluun. Jos poistat esteen, kaikki korjataan heti lennossa.
Dynaamiset taulukot ja älykkäät taulukot
Jos dynaaminen matriisi osoittaa "älykkääseen" taulukkoon, joka on luotu pikanäppäimellä Ctrl+T tai Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona), silloin se perii myös tärkeimmän ominaisuutensa – automaattisen koon muuttamisen.
Kun lisäät uusia tietoja alareunaan tai oikealle, älytaulukko ja dynaaminen alue venyvät myös automaattisesti:
On kuitenkin yksi rajoitus: emme voi käyttää dynaamisen alueen viittausta älykkäässä taulukossa olevissa kaavoissa:
Dynaamiset taulukot ja muut Excel-ominaisuudet
Okei, sinä sanot. Kaikki tämä on mielenkiintoista ja hauskaa. Ei tarvitse, kuten ennen, manuaalisesti venyttää kaavaa viittaamalla alkuperäisen alueen ensimmäiseen soluun alas ja oikealle ja kaikki muu. Ja siinä kaikki?
Ei aivan.
Dynaamiset taulukot eivät ole vain yksi työkalu Excelissä. Nyt ne on upotettu Microsoft Excelin sydämeen (tai aivoihin) – sen laskentakoneeseen. Tämä tarkoittaa, että myös muut meille tutut Excel-kaavat ja -funktiot tukevat työskentelyä dynaamisten taulukoiden kanssa. Katsotaanpa muutamia esimerkkejä, jotta saat käsityksen tapahtuneiden muutosten syvyydestä.
transponoida
Alueen transponoimiseksi (vaihtaa rivejä ja sarakkeita) Microsoft Excelissä on aina ollut sisäänrakennettu toiminto TRANSP (TRANSPOSI). Kuitenkin, jotta voit käyttää sitä, sinun on ensin valittava tulosten alue oikein (jos syöte oli esimerkiksi alue 5×3, sinun on valittava 3×5), syötä sitten funktio ja paina yhdistelmä Ctrl+siirtyminen+enter, koska se voisi toimia vain taulukkokaavatilassa.
Nyt voit vain valita yhden solun, kirjoittaa siihen saman kaavan ja napsauttaa normaalia enter – dynaaminen taulukko tekee kaiken itse:
Kertotaulu
Tämä on esimerkki, jonka annoin, kun minua pyydettiin visualisoimaan taulukkokaavojen edut Excelissä. Nyt koko Pythagoraan taulukon laskemiseksi riittää, että seisot ensimmäisessä solussa B2, syötät sinne kaava, joka kertoo kaksi taulukkoa (pysty- ja vaakalukujoukko 1...10) ja napsauta vain enter:
Liimaus ja kotelon muuntaminen
Matriiseja ei voi vain kertoa, vaan myös liimata yhteen vakiooperaattorilla & (et-kirjaimella). Oletetaan, että meidän täytyy poimia etu- ja sukunimi kahdesta sarakkeesta ja korjata hyppäävä kirjainkoko alkuperäisessä tiedossa. Teemme tämän yhdellä lyhyellä kaavalla, joka muodostaa koko taulukon, ja sitten käytämme funktiota siihen PROPNACH (OMA)rekisterin kuntoon:
Johtopäätös Top 3
Oletetaan, että meillä on joukko numeroita, joista haluamme johtaa kolme parasta tulosta järjestämällä ne laskevaan järjestykseen. Nyt tämä tehdään yhdellä kaavalla ja jälleen ilman mitään Ctrl+siirtyminen+enter kuten ennen:
Jos haluat, että tulokset eivät sijoiteta sarakkeeseen, vaan riviin, riittää, että korvaat tämän kaavan kaksoispisteet (rivin erotin) puolipisteellä (elementtierotin yhden rivin sisällä). Excelin englanninkielisessä versiossa nämä erottimet ovat puolipisteitä ja pilkkuja.
VLOOKUP purkaa useita sarakkeita kerralla
Tehtävät VPR (HAKU) nyt voit vetää arvoja ei yhdestä, vaan useista sarakkeista kerralla – määritä vain niiden numerot (missä tahansa halutussa järjestyksessä) taulukona funktion kolmannessa argumentissa:
OFFSET-funktio palauttaa dynaamisen taulukon
Yksi mielenkiintoisimmista ja hyödyllisimmistä (VLOOKUP:n jälkeen) data-analyysin funktioista on funktio HÄVITTÄMINEN (OFFSET), jolle omistin kerralla kokonaisen luvun kirjassani ja artikkelin täällä. Tämän funktion ymmärtämisen ja hallitsemisen vaikeus on aina ollut se, että se palautti tuloksena taulukon (alueen), mutta emme voineet nähdä sitä, koska Excel ei vieläkään osannut käsitellä taulukoita valmiina.
Nyt tämä ongelma on menneisyydessä. Katso, kuinka voit nyt poimia kaikki tietyn tuotteen rivit mistä tahansa lajitetusta taulukosta käyttämällä yhtä kaavaa ja OFFSETin palauttamaa dynaamista taulukkoa:
Katsotaanpa hänen argumenttejaan:
- A1 - aloitussolu (vertailupiste)
- ПОИСКПОЗ(F2;A2:A30;0) – siirtymän laskeminen aloitussolusta alas – ensimmäiseen löydettyyn kaaliin.
- 0 – "ikkunan" siirtyminen oikealle suhteessa aloitussoluun
- СЧЁТЕСЛИ(A2:A30;F2) - palautetun "ikkunan" korkeuden laskeminen - niiden rivien lukumäärä, joilla on kaalia.
- 4 — "ikkunan" koko vaakasuunnassa, eli tulosta 4 saraketta
Uusia toimintoja dynaamisille taulukoille
Vanhojen funktioiden dynaamisen taulukkomekanismin tukemisen lisäksi Microsoft Exceliin on lisätty useita täysin uusia toimintoja, jotka on teroitettu erityisesti dynaamisten taulukoiden kanssa työskentelyyn. Erityisesti nämä ovat:
- GRADE (JÄRJESTELLÄ) – lajittelee syöttöalueen ja tuottaa tulosteeseen dynaamisen taulukon
- SORTPO (JÄRJESTÄ) – voi lajitella yhden alueen arvojen mukaan toisesta
- SUODATIN (SUODATTAA) – hakee rivit lähdealueelta, jotka täyttävät määritetyt ehdot
- UNIK (AINUTLAATUINEN) – poimii yksilöllisiä arvoja alueelta tai poistaa kaksoiskappaleet
- SLMASSIIVINEN (RANDARRAY) – luo joukon tietynkokoisia satunnaislukuja
- JÄLKEISET (SEQUENCE) — muodostaa taulukon numerosarjasta tietyllä askeleella
Niistä lisää – vähän myöhemmin. Ne ovat erillisen artikkelin (eikä yhden) arvoisia harkitun tutkimisen vuoksi 🙂
Päätelmät
Jos olet lukenut kaiken yllä kirjoitetun, uskon, että ymmärrät jo tapahtuneiden muutosten laajuuden. Niin monet asiat Excelissä voidaan nyt tehdä helpommin, helpommin ja loogisemmin. Minun on myönnettävä, että olen hieman järkyttynyt siitä, kuinka monta artikkelia on nyt korjattava täällä, tällä sivustolla ja kirjoissani, mutta olen valmis tekemään tämän kevyellä sydämellä.
Yhteenvetona tulokset, ammattilaiset dynaamisia taulukoita, voit kirjoittaa seuraavan:
- Voit unohtaa yhdistelmän Ctrl+siirtyminen+enter. Excel ei näe nyt eroa "säännöllisten kaavojen" ja "taulukkokaavojen" välillä ja käsittelee niitä samalla tavalla.
- Tietoja toiminnosta SUMPRODUCT (SUMPRODUCT), jota käytettiin aiemmin taulukkokaavojen syöttämiseen ilman Ctrl+siirtyminen+enter voit myös unohtaa – nyt se on tarpeeksi helppoa SUMMA и enter.
- Älykkäät taulukot ja tutut funktiot (SUM, IF, VLOOKUP, SUMIFS jne.) tukevat nyt myös täysin tai osittain dynaamisia taulukoita.
- Taaksepäin yhteensopivuus on olemassa: jos avaat työkirjan, jossa on dynaamisia taulukoita Excelin vanhassa versiossa, ne muuttuvat taulukkokaavoiksi (sulkeissa) ja jatkavat työskentelyä "vanhalla tyylillä".
Joku numero löytyi miinukset:
- Et voi poistaa yksittäisiä rivejä, sarakkeita tai soluja dynaamisesta taulukosta, eli se elää yhtenä kokonaisuutena.
- Et voi lajitella dynaamista taulukkoa tavalliseen tapaan Tiedot – Lajittelu (Tiedot — Lajittele). Tätä varten on nyt erityinen toiminto. GRADE (JÄRJESTELLÄ).
- Dynaamista aluetta ei voi muuttaa älykkääksi taulukoksi (mutta voit tehdä dynaamisen alueen älytaulukon perusteella).
Tämä ei tietenkään ole loppu, ja olen varma, että Microsoft jatkaa tämän mekanismin parantamista tulevaisuudessa.
Mistä voin ladata?
Ja lopuksi pääkysymys 🙂
Microsoft julkisti ja esitti Excelin dynaamisten taulukoiden esikatselun ensimmäisen kerran syyskuussa 2018 konferenssissa Sytyttää. Seuraavien kuukausien aikana testattiin perusteellisesti ja otettiin käyttöön uusia ominaisuuksia, ensin kissat Microsoftin itsensä työntekijöitä ja sitten vapaaehtoisia testaajia Office Insiders -piiristä. Tänä vuonna päivitystä, joka lisää dynaamisia taulukoita, alettiin asteittain ottaa käyttöön tavallisille Office 365 -tilaajille. Sain sen esimerkiksi vasta elokuussa Office 365 Pro Plus (Monthly Targeted) -tilaukseni mukana.
Jos Excelissäsi ei vielä ole dynaamisia taulukoita, mutta haluat todella työskennellä niiden kanssa, on seuraavat vaihtoehdot:
- Jos sinulla on Office 365 -tilaus, voit yksinkertaisesti odottaa, kunnes tämä päivitys saapuu sinulle. Kuinka nopeasti tämä tapahtuu, riippuu siitä, kuinka usein päivitykset toimitetaan Officellesi (kerran vuodessa, kerran puolessa vuodessa, kerran kuukaudessa). Jos sinulla on yritystietokone, voit pyytää järjestelmänvalvojaa määrittämään päivitykset, jotka ladataan useammin.
- Voit liittyä Office Insiders -testivapaaehtoisten joukkoon – silloin saat ensimmäisenä kaikki uudet ominaisuudet ja toiminnot (mutta Excelissä on tietysti mahdollisuus lisääntyä bugi).
- Jos sinulla ei ole tilausta, vaan laatikollinen erillinen Excel-versio, sinun on odotettava ainakin Officen ja Excelin seuraavan version julkaisua vuonna 2022. Tällaisten versioiden käyttäjät saavat vain tietoturvapäivityksiä ja virheenkorjauksia, ja kaikki uudet "hyvuudet" menevät nyt vain Office 365 -tilaajille. Surullista mutta totta 🙂
Joka tapauksessa, kun dynaamiset taulukot näkyvät Excelissäsi - tämän artikkelin jälkeen olet valmis siihen 🙂
- Mitä ovat taulukkokaavat ja kuinka niitä käytetään Excelissä
- Ikkunan (alueen) summaus OFFSET-toiminnolla
- 3 tapaa siirtää taulukko Excelissä