Bevezetés a dinamikus tartományokba
A VLOOKUP funkciót gyakran használják az Excel táblázataiban tárolt információk megkeresésére. Például, ha van egy listánk az emberek nevéről és életkoráról:
És akkor a közeli cellában a VLOOKUP függvény segítségével meghatározhatjuk Paul életkorát:
Ez egyelőre meglehetősen szabványos. De mi történik, ha további neveket kell hozzáadnunk a listához? A nyilvánvaló gondolat az lenne, hogy módosítani kell a VLOOKUP tartományát. Egy igazán összetett modellben azonban több hivatkozás is lehet a VLOOKUP -ra. Ez azt jelenti, hogy minden hivatkozást módosítanunk kell - feltételezve, hogy tudjuk, hol vannak.
Az Excel azonban alternatív módot kínál - DYNAMIC tartománynak. Ez egy olyan tartomány, amely automatikusan kibővíti a frissítéseket. Ez tökéletes, ha listái folyamatosan bővülnek (pl. Havi értékesítési adatok).
A dinamikus tartomány beállításához tartománynévvel kell rendelkeznünk - így AGE_DATA -nak fogjuk hívni. A dinamikus tartományok beállításának módja eltér az Excel 2007 és az Excel korábbi verziói között:
Az Excel 2007 programban kattintson a „Név meghatározása” gombra a képletek alatt:
Az Excel korábbi verzióiban kattintson a "Beszúrás", majd a Nevek "gombra.
A felugró ablakban adja meg dinamikus tartományunk nevét - ez „AGE DATA”:
A „Hivatkozik” feliratú mezőbe meg kell adnunk adataink tartományát. Ezt az OFFSET funkció fogja használni. Ennek 5 érve van:
= OFFSET (Referencia, Sorok, Cols, Magasság, Szélesség)
- A referencia a tartományunk bal felső sarkának címe - ebben az esetben a B5 cella
- A sorok a FEL BAL bal oldali sorok száma, amelyet szeretnénk, hogy ez a tartomány legyen - ami ebben az esetben 0 lesz
- A Cols a sorok száma a FELSŐ BALRÓL, hogy ezt a tartományt szeretnénk - ami ebben az esetben 0 lesz
- A tartomány magassága - lásd alább
- A tartomány szélessége - ez 2, Kettő oszlop van a tartományunkban (személyek neve és életkora)
Most a tartomány magasságának változnia kell a táblázatunkban szereplő bejegyzések számától függően (amely jelenleg 7).
Természetesen szeretnénk a táblázat sorainak automatikus frissítésének megszámlálását - tehát ennek egyik módja a COUNTA funkció használata. Ez csak számolja a nem üres cellák számát egy tartományban. Mivel nevünk a B oszlopban található, az adataink bejegyzéseinek száma COUNTA (B: B).
Ne feledje, hogy ha ezt egy cellába tenné, akkor a 8 értéket kapná - mivel tartalmazza a fejléc nevét. Azonban, hogy lényegtelen.
Tehát a „Hivatkozik” mezőbe ezt írjuk:
= OFFSET ($ B $ 5,0,0, számolás (B: B), 2)
És kattintson az OK gombra. Dinamikus tartományunk most létrejött.
Most térjen vissza a VLOOKUP képletekhez, és cserélje le a $ B: 4: $ C11 tartományt az új dinamikus tartományunk AGE_DATA nevével, így kapjuk:
Eddig semmi sem változott. Ha azonban még néhány nevet hozzáadunk a táblázatunkhoz:
És abban a cellában, ahol Paulunk volt, cserélje le egy új névvel, például Pedro -val (ez nem szerepelt az eredeti listán):
És látjuk, hogy az Excel automatikusan visszaadta Pedro korát - annak ellenére, hogy nem változtattunk a VLOOKUP képleteken. Ehelyett a dinamikus tartomány hatóköre kibővült az extra nevekkel.
A dinamikus tartományok nagyon hasznosak, ha egyre nagyobb adatmennyiséggel rendelkezünk - különösen akkor, ha VLOOKUP és PIVOT táblákra van szükség.
