Sisu
Kas teil on Excelis andmetega tabeleid, mille suurust saab muuta ehk ridade (veergude) arv võib töö käigus suureneda või väheneda? Kui laua suurused "ujuvad", peate seda hetke pidevalt jälgima ja parandama:
- lingid aruande valemites, mis viitavad meie tabelile
- Pöördtabelite esialgsed vahemikud, mis on koostatud meie tabeli järgi
- meie tabeli järgi koostatud diagrammide esialgsed vahemikud
- vahemikud rippmenüüde jaoks, mis kasutavad andmeallikana meie tabelit
Kõik see kokku ei lase sul igavleda 😉
Palju mugavam ja õigem on luua dünaamiline kummivahemik, mille suurus kohandub automaatselt tegeliku andmeridade ja veergude arvuga. Selle rakendamiseks on mitu võimalust.
1. meetod. Nutikas tabel
Tõstke esile oma lahtrite vahemik ja valige vahekaardilt Avaleht – vorminda tabelina (avaleht – vorminda tabelina):
Kui te ei vaja tabelisse kõrvalmõjuna lisatud triibulist kujundust, saate selle ilmuval vahelehel välja lülitada Konstruktor (disain). Iga sel viisil loodud tabel saab nime, mille saab vahekaardil samas kohas mugavamaga asendada Konstruktor (disain) valdkonnas Tabeli nimi (Tabeli nimi).
Nüüd saame kasutada dünaamilisi linke meie nutikale lauale:
- Tabel 1 – link tervele tabelile, välja arvatud päise rida (A2:D5)
- Tabel 1[#Kõik] – link kogu tabelile (A1:D5)
- Tabel 1 [Peeter] – viide vahemiku veerule ilma esimese lahtri päiseta (C2:C5)
- Tabel 1[#Headers] – link "päisele" koos veergude nimedega (A1:D1)
Sellised viited töötavad suurepäraselt valemites, näiteks:
= SUM (Tabel 1[Moskva]) – veeru “Moskva” summa arvutamine
or
=VPR(F5;Tabel 1;3;0) – otsi tabelist kuu lahtrist F5 ja väljasta selle eest Peterburi summa (mis on VLOOKUP?)
Selliseid linke saab vahekaardil valides pivot-tabelite loomisel edukalt kasutada Sisestamine – Pivot Table (Sisestamine – Pivot Table) ja sisestades andmeallikaks nutika tabeli nime:
Kui valite sellise tabeli fragmendi (näiteks kaks esimest veergu) ja loote mis tahes tüüpi diagrammi, lisatakse need uute ridade lisamisel automaatselt diagrammi.
Rippmenüüde loomisel ei saa kasutada otselinke nutika tabeli elementidele, kuid sellest piirangust saate hõlpsasti mööda taktikalise nipi abil – kasutage funktsiooni KAUDSEID (Kaudne), mis muudab teksti lingiks:
Need. tekstistringina (jutumärkides!) olev nutitabeli link muutub täisväärtuslikuks lingiks ja ripploend tajub seda tavaliselt.
2. meetod: dünaamiline nimega vahemik
Kui oma andmete nutikaks tabeliks muutmine on mingil põhjusel ebasoovitav, siis võib kasutada veidi keerulisemat, kuid palju peenemat ja mitmekülgsemat meetodit – luua Excelis dünaamiline nimevahemik, mis viitab meie tabelile. Seejärel, nagu nutika tabeli puhul, saab loodud vahemiku nime vabalt kasutada mis tahes valemites, aruannetes, diagrammides jne. Alustame lihtsa näitega:
Ülesanne: looge dünaamiline nimega vahemik, mis viitaks linnade loendile ning venib ja kahaneb uute linnade lisamisel või kustutamisel automaatselt.
Vajame kahte sisseehitatud Exceli funktsiooni, mis on saadaval mis tahes versioonis − POICPOZ (MATCH) vahemiku viimase lahtri määramiseks ja INDEX (INDEX) dünaamilise lingi loomiseks.
Viimase lahtri leidmine kasutades MATCH
VASTAVUS(otsingu_väärtus, vahemik, vaste_tüüp) – funktsioon, mis otsib antud väärtust vahemikus (reas või veerus) ja tagastab selle lahtri järjekorranumbri, kust see leiti. Näiteks valem MATCH(“märts”;A1:A5;0) tagastab tulemuseks arvu 4, sest sõna “märts” asub veeru A1:A5 neljandas lahtris. Funktsiooni viimane argument Match_Type = 0 tähendab, et otsime täpset vastet. Kui seda argumenti ei täpsustata, lülitub funktsioon lähima väikseima väärtuse otsingurežiimi – just seda saab edukalt kasutada meie massiivi viimase hõivatud lahtri leidmiseks.
Triki olemus on lihtne. MATCH otsib lahtreid vahemikust ülevalt alla ja peaks teoreetiliselt peatuma, kui leiab antud väärtusele lähima väikseima väärtuse. Kui määrate soovitud väärtuseks väärtuse, mis on ilmselgelt suurem kui mis tahes tabelis saadaolevast, siis jõuab MATCH tabeli lõppu, ei leia midagi ja annab viimati täidetud lahtri järjekorranumbri. Ja me vajame seda!
Kui meie massiivis on ainult numbreid, siis saame soovitud väärtuseks määrata arvu, mis on ilmselgelt suurem kui ükski tabelis olevatest:
Garantii jaoks saab kasutada numbrit 9E + 307 (9 korda 10 astmeni 307 ehk 9 307 nulliga) – maksimaalne arv, millega Excel põhimõtteliselt töötada saab.
Kui meie veerus on tekstiväärtusi, siis saate suurima võimaliku arvu ekvivalendiks sisestada konstruktsiooni REPEAT(“i”, 255) – 255 tähest koosnev tekstistring “i” – tähe viimane täht. tähestik. Kuna Excel võrdleb otsimisel märgikoode, on meie tabeli mis tahes tekst tehniliselt „väiksem” kui selline pikk „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” rida:
Looge link INDEXi abil
Nüüd, kui teame tabelis viimase mittetühja elemendi asukohta, jääb üle luua link kogu meie vahemikuga. Selleks kasutame funktsiooni:
INDEX(vahemik; rea_arv; veeru_arv)
See annab lahtri sisu vahemikust rea ja veeru numbri järgi, st näiteks funktsioon =INDEX(A1:D5;3;4) meie tabelis linnade ja kuudega eelmisest meetodist annab 1240 – sisu 3. reast ja 4. veerust ehk lahtritest D3. Kui on ainult üks veerg, siis võib selle numbri ära jätta, st valem INDEX(A2:A6;3) annab viimasel ekraanipildil “Samara”.
Ja on üks mitte täiesti ilmne nüanss: kui INDEXit ei sisestata lihtsalt lahtrisse pärast märki =, nagu tavaliselt, vaid seda kasutatakse kooloni järel vahemiku viite viimase osana, siis see ei anna enam välja. lahtri sisu, vaid selle aadress! Seega annab valem nagu $A$2:INDEX($A$2:$A$100;3) väljundis viite vahemikule A2:A4.
Ja siin tuleb sisse funktsioon MATCH, mille sisestame loendi lõpu dünaamiliseks määramiseks INDEXisse:
=$A$2:INDEKS($A$2:$100; MATCH(REP("I";255);A2:A100))
Loo nimega vahemik
Jääb üle see kõik ühtseks tervikuks pakkida. Avage vahekaart valem (Valemid) Ja klõpsake nuppu Nimehaldur (Nimehaldur). Avanevas aknas klõpsake nuppu Looma (uus), sisestage väljale meie vahemiku nimi ja valem Valik (Viide):
Jääb üle klõpsata OK ja valmis vahemikku saab kasutada mis tahes valemites, ripploendites või diagrammides.
- Funktsiooni VLOOKUP kasutamine tabelite ja otsinguväärtuste linkimiseks
- Automaatselt täidetava rippmenüü loomine
- Kuidas luua pivot-tabelit suure hulga andmete analüüsimiseks