Dynaamisten taulukoiden luominen Excelissä (TABLE & OFFSET -toiminnon käyttäminen)
Dynaamiset taulukot excelissa ovat taulukoita, joissa kun siihen lisätään uusi arvo, taulukko säätää itse kokoa, dynaamisen taulukon luomiseksi exceliin meillä on kaksi erilaista menetelmää, jotka ovat kerran ja jotka luovat taulukon tiedoista kun taas toinen on offset-toimintoa käyttämällä, dynaamisissa taulukoissa myös raportit ja pivot-taulukot muuttuvat, kun dynaamisen taulukon tiedot muuttuvat.
Dynaamiset taulukot Excelissä
Dynaaminen itsessään tarkoittaa prosessorijärjestelmää, jolle on ominaista jatkuva muutos tai muutos toiminnassa. Vastaavasti Excelissä, kun luomme luetteloita tai tietoja työkirjaan ja teemme siitä raportin, mutta jos lisäämme tietoja tai poistamme ne tai siirrämme tai muutamme tietoja, koko raportti voi olla epätarkka. Excelillä on siihen ratkaisu dynaamisina taulukoina.
Nyt herää kysymys, miksi tarvitsemme dynaamista aluetta tai dynaamisia taulukoita. Vastaus tähän johtuu siitä, että aina kun luetteloa tai tietoaluetta päivitetään tai muokataan, ei ole varmaa, että raporttia muutetaan datamuutoksen mukaisesti.
Periaatteessa dynaamisilla pöydillä on kaksi pääetua:
- Dynaaminen alue laajenee tai supistuu automaattisesti tietojen muutoksen mukaan.
- Excelin dynaamiseen taulukkoon perustuvat pivot-taulukot voidaan päivittää automaattisesti, kun pivot päivitetään.
Kuinka luoda dynaamisia taulukoita Excelissä?
Dynaamisten taulukoiden käyttämiseen Excelissä on kaksi perustapaa - 1) TABLES ja 2) OFFSET-toiminnon käyttäminen.
Voit ladata tämän dynaamisen taulukon Excel-mallin täältä - Dynamic Table Excel Template# 1 - Taulukoiden käyttäminen dynaamisten taulukoiden luomiseen Exceliin
Taulukoiden avulla voimme rakentaa dynaamisen taulukon Excelissä ja perustaa pivotin dynaamisen taulukon päälle.
Esimerkki
Meillä on seuraavat tiedot,
Jos teemme pivot-taulukon tällä normaalilla datasäteellä välillä A1: E6, niin jos lisätään data riville 7, se ei heijastu pivot-taulukkoon.
Joten teemme ensin dynaamisen alueen.
# 1 - Valitse tiedot eli A1: E6.
# 2 - Napsauta lisäyksessä Sarkain taulukot -osiossa Taulukot.
# 3 - Näyttöön tulee valintaikkuna.
Koska tiedoissamme on otsikoita, muista muistaa tarkistaa "Oma taulukko on otsikot" -valintaruutu ja napsauttaa ok.
# 4 - Dynaaminen alueemme on luotu.
# 5 - Valitse tiedot ja napsauta Lisää-välilehdessä Excel-taulukot-osiossa pivot-taulukoita.
# 6 - Kun olemme luoneet taulukon, se vie alueen taulukoksi 2. Napsauta OK ja vedä pivot-taulukoissa Tuote riveissä ja myynti arvoissa.
# 7 - Nyt taulukossa, jossa meillä on taulukko, lisää toinen data seitsemänteen
Päivitä pivot-taulukko pivot-taulukossa.
Dynaaminen pivot-taulukko on päivittänyt pivot-taulukon tuotteen 6 tiedot automaattisesti.
# 2 - OFFSET-toiminnon käyttäminen dynaamisen taulukon luomiseen Excelissä
Voimme myös käyttää OFFSET-toimintoa dynaamisten taulukoiden luomiseen Excelissä. Katsotaanpa yksi tällainen esimerkki.
Esimerkki
Minulla on tuotteilleni hinnasto, jota käytän laskelmissani,
Valitse tiedot ja anna sille nimi
Aina kun viittaan tietojoukon hinnastoon, se vie minut tietoihin, jotka ovat hintaluettelossani B2: C7. Mutta jos päivitän toisen rivin tietoihin, se vie silti B2: C7-alueen, koska luettelomme on staattinen.
Käytämme siirtofunktiota tekemään alueesta dynaaminen.
# 1 - Napsauta Määritetyn alueen kaavat -välilehdessä Määritetty nimi ja valintaikkuna avautuu.
# 2 - Nimikentässä kirjoitan minkä tahansa nimen, käytän PriceA: ta. Laajuus on nykyinen työkirja ja tällä hetkellä se viittaa nykyiseen valittuun soluun, joka on B2.
Kohdassa Viitteet kirjoittaa seuraava kaava,
= offset (Arkki2! $ B $ 2,1,0, countta (Arkki2! $ B: $ B) -1,2)
= offset (
# 3 - Valitse nyt aloitussolu, joka on B2,
# 4 - Nyt meidän on kirjoitettava 1,0, koska se laskee kuinka monta riviä tai saraketta mennä
# 5 - Nyt tarvitsemme sen laskemaan kaikki sarakkeessa B olevat tiedot ja käyttämään sitä rivien lukumääränä, joten käytä COUNTA-toimintoa ja valitse sarake B.
# 6 - Koska emme halua, että ensimmäinen otsikko, joka on tuotteen otsikko, lasketaan niin (-) 1 siitä.
# 7 - Sarakkeiden lukumäärä on aina kaksi, joten kirjoita 2 ja napsauta OK.
# 8 - Tämä data-alue ei ole oletusarvoisesti näkyvissä, joten jotta voimme nähdä tämän, meidän on napsautettava Kaavan välilehden Nimiohjainta ja valitsemalla Tuote,
# 9 - Jos napsautamme viittaa siihen, se näyttää data-alueen,
# 10 - Lisää nyt toinen tuote taulukkoon Tuote 6.
# 11 - Napsauta nyt tuotetaulukkoa Nimi-hallinnassa. Se viittaa myös lisättyihin uusiin tietoihin,
Näin voimme käyttää Offset-toimintoa dynaamisten taulukoiden tekemiseen.
Muistettavaa
- Dynaamiseen alueeseen perustuvat pivot-taulukot päivittyvät automaattisesti päivitettäessä.
- Siirtofunktion käyttäminen määritetyissä nimissä näkyy Nimihallinnasta kaavan välilehdessä.
