Sissejuhatus dünaamilistesse vahemikku
Funktsiooni VLOOKUP kasutatakse sageli Exceli tabelitesse salvestatud teabe leidmiseks. Näiteks kui meil on nimekiri inimeste nimedest ja vanustest:
Ja siis saame lähedal asuvas lahtris kasutada funktsiooni VLOOKUP, et määrata Pauluse vanus:
Siiani on see üsna tavaline. Aga mis juhtub, kui peame loendisse veel mõned nimed lisama? Ilmselge mõte oleks muuta VLOOKUPi vahemikku. Tõeliselt keerulises mudelis võib aga olla mitmeid viiteid VLOOKUPile. See tähendab, et me peame iga viidet muutma - eeldades, et me teame, kus nad asuvad.
Siiski pakub Excel alternatiivset viisi - DYNAMIC vahemik. See on vahemik, mis laiendab värskendusi automaatselt. See sobib ideaalselt, kui teie loendeid pidevalt laiendatakse (nt igakuised müügiandmed).
Dünaamilise vahemiku seadistamiseks peab meil olema vahemiku nimi - seega nimetame oma AGE_DATA. Dünaamiliste vahemike seadistamise lähenemisviis erineb Excel 2007 ja Exceli varasemate versioonide vahel.
Programmis Excel 2007 klõpsake valemite all nuppu „Määra nimi”:
Exceli varasemates versioonides klõpsake nuppu "Lisa" ja seejärel Nimed.
Sisestage hüpikaknasse meie dünaamilise vahemiku nimi - "AGE DATA":
Lahtrisse "Viitab" peame sisestama oma andmete vahemiku. Seda saavutatakse OFFSET -funktsiooni abil. Sellel on 5 argumenti:
= OFFSET (viide, read, veerud, kõrgus, laius)
- Viide on meie vahemiku ÜLES VASAKU nurga aadress - antud juhul lahter B5
- Ridad on ridade arv ülalt vasakult, mida me tahame, et see vahemik oleks - mis on sel juhul 0
- Cols on ridade arv ülalt vasakult, mida me tahame, et see vahemik oleks - mis on sel juhul 0
- Vahemiku kõrgus - vaadake seda allpool
- Vahemiku laius - see on 2, meil on vahemikus KAKS veergu (isikute nimi ja vanus)
Nüüd peab vahemiku kõrgus varieeruma sõltuvalt meie tabeli kirjete arvust (mis on praegu 7).
Loomulikult tahame oma tabeli ridade loendamise viisi, mis värskendatakse automaatselt - nii et üks viis seda teha on kasutada funktsiooni COUNTA. See lihtsalt loeb kokku tühjade lahtrite arvu vahemikus. Kuna meie nimed on veerus B, on meie andmetes kirjete arv COUNTA (B: B).
Pange tähele, et kui panete selle lahtrisse, saate väärtuse 8 - kuna see sisaldab päist Nimed. Siiski, et see on ebaoluline.
Seega paneme lahtrisse "Viitab":
= OFFSET ($ B $ 5,0,0, arv (B: B), 2)
Ja klõpsake nuppu OK. Meie dünaamiline ulatus on nüüd loodud.
Naaske nüüd VLOOKUP valemite juurde ja asendage vahemik $ B: 4: $ C11 meie uue dünaamilise vahemiku AGE_DATA nimega, nii et meil on:
Siiani pole midagi muutunud. Kui aga lisada tabelisse veel mõned nimed:
Ja lahtris, kus meil oli Paul, asendage see uue nimega, näiteks Pedro (see ei olnud algses loendis):
Ja näeme, et Excel on Pedro vanuse automaatselt tagastanud - kuigi me pole VLOOKUP valemeid muutnud. Selle asemel on dünaamilise vahemiku ulatus suurenenud, lisades lisanimed.
Dünaamilised vahemikud on väga kasulikud, kui meil on üha suuremad andmemahud - eriti kui on vaja VLOOKUP ja PIVOT tabeleid.
