Įvadas į dinaminius diapazonus
Funkcija VLOOKUP dažnai naudojama norint rasti informaciją, kuri saugoma „Excel“ lentelėse. Pavyzdžiui, jei turime žmonių vardų ir amžių sąrašą:
Tada galime netoliese esančioje ląstelėje naudoti funkciją VLOOKUP, kad nustatytume Pauliaus amžių:
Kol kas tai yra gana standartas. Bet kas atsitiks, jei į sąrašą turėsime įtraukti dar keletą pavadinimų? Akivaizdu, kad būtų galima pakeisti VLOOKUP diapazoną. Tačiau tikrai sudėtingame modelyje gali būti keletas nuorodų į VLOOKUP. Tai reiškia, kad turėtume pakeisti kiekvieną nuorodą - darant prielaidą, kad žinome, kur jie yra.
Tačiau „Excel“ siūlo alternatyvų būdą - vadinamą DYNAMIC diapazonu. Tai diapazonas, kuris automatiškai išplečia atnaujinimus. Tai puikiai tinka, jei jūsų sąrašai nuolat plečiasi (pvz., Mėnesio pardavimo duomenys).
Norėdami nustatyti dinaminį diapazoną, turime turėti diapazono pavadinimą, todėl mes vadinsime savo AGE_DATA. Dinaminių diapazonų nustatymo metodas skiriasi „Excel 2007“ ir ankstesnėse „Excel“ versijose:
„Excel 2007“ po formulėmis spustelėkite „Apibrėžti vardą“:
Ankstesnėse „Excel“ versijose spustelėkite „Įterpti“, tada - Vardai.
Iššokančiajame lauke įveskite mūsų dinaminio diapazono pavadinimą - „AGE DATA“:
Laukelyje „Nuoroda į“ turime įvesti savo duomenų diapazoną. Tai bus pasiekta naudojant OFFSET funkciją. Tai turi 5 argumentus:
= OFFSET (nuoroda, eilutės, atžymos, aukštis, plotis)
- Nuoroda yra mūsų diapazono viršutinio kairiojo kampo adresas - šiuo atveju langelis B5
- Eilutės - tai eilutės iš viršaus į kairę, kurio norime, kad šis diapazonas būtų - šiuo atveju bus 0
- „Cols“ yra eilučių skaičius iš viršaus į kairę, kurio norime, kad šis diapazonas būtų - šiuo atveju bus 0
- Diapazono aukštis - skaitykite žemiau
- Diapazono plotis - tai yra 2, mūsų diapazone yra du stulpeliai (asmenų vardas ir amžius)
Dabar diapazono aukštis turės skirtis priklausomai nuo įrašų skaičiaus mūsų lentelėje (šiuo metu yra 7).
Žinoma, norime, kad mūsų lentelės eilutės būtų automatiškai skaičiuojamos, taigi vienas iš būdų tai padaryti yra naudoti funkciją COUNTA. Tai tik suskaičiuoja tuščių langelių skaičių diapazone. Kadangi mūsų vardai yra B stulpelyje, mūsų duomenų įrašų skaičius yra COUNTA (B: B).
Atminkite, kad jei įdėtumėte tai į langelį, gautumėte vertę 8, nes ji apima antraštę Pavadinimai. Tačiau, kad tai nereikšminga.
Taigi langelyje „Nuoroda į“ įdėjome:
= OFFSET ($ B $ 5,0,0, skaitiklis (B: B), 2)
Ir spustelėkite mygtuką Gerai. Dabar sukurtas mūsų dinaminis diapazonas.
Dabar grįžkite prie VLOOKUP formulių ir pakeiskite diapazoną $ B: 4: $ C11 naujojo dinaminio diapazono pavadinimu AGE_DATA, kad turėtume:
Iki šiol niekas nepasikeitė. Tačiau jei prie lentelės pridėsime dar keletą pavadinimų:
Ląstelėje, kurioje turėjome Paulių, pakeiskite jį nauju vardu, pvz., Pedro (to nebuvo pradiniame sąraše):
Ir matome, kad „Excel“ automatiškai grąžino Pedro amžių - nors mes nepakeitėme VLOOKUP formulių. Vietoj to, dinaminio diapazono apimtis padidėjo, įtraukiant papildomus pavadinimus.
Dinaminiai diapazonai yra labai naudingi, kai turime vis daugiau duomenų, ypač kai reikia VLOOKUP ir PIVOT lentelių.
