Dünaamiliste tabelite loomine Excelis (funktsiooni TABLE & OFFSET kasutamine)
Excelis olevad dünaamilised tabelid on tabelid, kus uue väärtuse lisamisel kohandab tabel selle suurust ise. Excelis dünaamilise tabeli loomiseks on meil kaks erinevat meetodit, mis loovad tabeli andmetest kui teine on offset-funktsiooni kasutades, muutuvad dünaamilistes tabelites ka aruanded ja pöördtabelid, kui dünaamilise tabeli andmed muutuvad.
Dünaamilised tabelid Excelis
Dünaamiline iseenesest tähendab see protsessorisüsteemi, mida iseloomustab pidev muutus või aktiivsuse muutus. Samamoodi võib Excelis, kui loome töövihikus loendeid või andmeid ja koostame neist aruande, kuid kui lisame mingeid andmeid või eemaldame need või teisaldame või muudame andmeid, võib kogu aruanne olla ebatäpne. Excelis on sellele lahendus kui dünaamilised tabelid.
Nüüd tekib küsimus, miks me vajame dünaamilist vahemikku või dünaamilisi tabeleid. Vastus sellele on, sest kui loendit või andmevahemikku värskendatakse või muudetakse, ei ole kindel, et aruannet muudetakse vastavalt andmete muutumisele.
Põhimõtteliselt on dünaamiliste tabelite kaks peamist eelist:
- Dünaamiline vahemik laieneb või väheneb automaatselt vastavalt andmete muutumisele.
- Exceli dünaamilisel tabelil põhinevaid liigendtabeleid saab pivoti värskendamisel automaatselt värskendada.
Kuidas Excelis dünaamilisi tabeleid luua?
Dünaamiliste tabelite kasutamiseks Excelis on kaks põhilist viisi - 1) TABELIDE ja 2) Funktsiooni OFFSET kasutamine.
Selle dünaamilise tabeli Exceli malli saate alla laadida siit - dünaamilise tabeli Exceli mall# 1 - Tabelite kasutamine Excelis dünaamiliste tabelite loomiseks
Tabelite abil saame Excelis üles ehitada dünaamilise tabeli ja rajada pöördetapp dünaamilise tabeli kohale.
Näide
Meil on järgmised andmed,
Kui me teeme selle normaalse andmevahemikuga pöördtabeli vahemikus A1: E6, siis kui me sisestame andmed 7. ritta, siis see ei kajastu pöördtabelis.
Seega teeme kõigepealt dünaamilise vahemiku.
# 1 - valige andmed, st A1: E6.
# 2 - vahekaardil klõpsake tabelite jaotise all tabelit.
# 3 - ilmub dialoogiboks.
Kuna meie andmetel on päised, pidage meeles, et märkige ruut „Minu tabelis on päised” ja klõpsake nuppu OK.
# 4 - meie dünaamiline ulatus on loodud.
# 5 - valige andmed ja klõpsake jaotises Exceli tabelid vahekaardil Lisa vahekaardil liigendtabelid.
# 6 - Kuna oleme tabeli loonud, võtab see vahemiku nagu tabel 2. Klõpsake nuppu OK ja liigendtabelites lohistage toode ridades ja müük väärtustes.
# 7 - nüüd lehel, kus meil on tabel, sisestage veel üks teave seitsmendaks
Värskendage pöördtabelis pöördtabelit.
Meie dünaamiline Pivoti tabel värskendab pöördtabelis toote 6 andmeid automaatselt.
# 2 - funktsiooni OFFSET kasutamine Excelis dünaamilise tabeli loomiseks
Excelis saab dünaamiliste tabelite loomiseks kasutada ka funktsiooni OFFSET. Vaatame ühte sellist näidet.
Näide
Mul on oma toodete hinnakiri, mida ma kasutan oma arvutuste jaoks,
Valige andmed ja andke neile nimi
Kui viitan andmekogumi hinnakirjale, viib see mind vahemiku B2: C7 andmetele, millel on minu hinnakiri. Kuid kui värskendan veel ühte rida andmetele, viib see mind siiski vahemikku B2: C7, kuna meie loend on staatiline.
Andmevahemiku dünaamiliseks muutmiseks kasutame funktsiooni Nihe.
# 1 - klõpsake määratletud vahemiku vahekaardi Valemid all nuppu Määratletud nimi ja ilmub dialoogiboks.
# 2 - Nimede lahtrisse kirjutage mis tahes nime korral PriceA. Reguleerimisala on praegune töövihik ja praegu viitab see praegusele valitud lahtrile, mis on B2.
Jaotises Viitab järgmise valemi kirjutamisele
= nihe (leht2! $ B $ 2,1,0, countta (leht2! $ B: $ B) -1,2)
= nihe (
# 3 - valige nüüd algrakk B2,
# 4 - Nüüd peame sisestama 1,0, kuna see loeb mitu rida või veergu minna
# 5 - Nüüd vajame seda, et loendada kõik veerus B olevad andmed ja kasutada seda ridade arvuna, seega kasutage funktsiooni COUNTA ja valige veerg B.
# 6 - Kuna me ei soovi, et esimest rida, mis on toote päis, loendatakse (-) 1 sellest.
# 7 - nüüd on veergude arv alati kaks, nii et tippige 2 ja klõpsake nuppu OK.
# 8 - see andmevahemik ei ole vaikimisi nähtav, nii et selle nägemiseks peame klõpsama vahekaardil Valem valikul Nimehaldur ja valima Toode,
# 9 - kui klõpsame nuppu viitab, näitab see andmevahemikku,
# 10 - lisage nüüd tabelisse toode 6 veel üks toode.
# 11 - klõpsake nüüd nimehalduris valikul Tootetabel, see viitab ka uutele sisestatud andmetele,
Nii saame dünaamiliste tabelite koostamiseks kasutada funktsiooni Nihe.
Asjad, mida meeles pidada
- Dünaamilisel vahemikul põhinevad liigendtabelid värskendatakse värskendamisel automaatselt.
- Nihefunktsiooni kasutamist määratletud nimedes saab näha valemikaardil olevast nimehaldurist.
