Az Excel-adatok gyakran változnak, ezért hasznos egy dinamikusan meghatározott tartomány létrehozása, amely automatikusan bővül és összehúzódik az adattartomány méretére. Lássuk hogyan.
Dinamikus definiált tartomány használatával nem kell manuálisan szerkesztenie a képletek, diagramok és kimutatások tartományait az adatok megváltozásakor. Ez automatikusan megtörténik.
A dinamikus tartományok létrehozásához két képlet használható: OFFSET és INDEX. Ez a cikk az INDEX függvény használatára összpontosít, mivel ez egy hatékonyabb megközelítés. Az OFFSET egy illékony függvény, és lelassíthatja a nagy táblázatokat.
Tartalomjegyzék
Hozzon létre egy dinamikus definiált tartományt az Excelben
Első példánkban az alábbiakban látható egyoszlopos adatok listája.
Ennek dinamikusnak kell lennie, hogy ha több országot adnak hozzá vagy távolítanak el, a tartomány automatikusan frissül.
Ebben a példában szeretnénk elkerülni a fejlécet. Mint ilyen, a $A$2:$A$6 tartományt szeretnénk, de dinamikus. Ehhez kattintson a Képletek > Név meghatározása lehetőségre.
Írja be a „countries” kifejezést a „Név” mezőbe, majd írja be az alábbi képletet a „Refers to” mezőbe.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Az egyenlet beírása egy táblázat cellájába, majd az Új név mezőbe másolása néha gyorsabb és egyszerűbb.
Hogy működik ez?
A képlet első része a tartomány kezdőcelláját adja meg (esetünkben A2), majd a tartomány operátora (:) következik.
=$A$2:
A tartomány operátor használata arra kényszeríti az INDEX függvényt, hogy egy tartományt adjon vissza a cella értéke helyett. Az INDEX függvény ezután a COUNTA funkcióval együtt használatos. A COUNTA megszámolja az A oszlop nem üres celláinak számát (a mi esetünkben hat).
INDEX($A:$A,COUNTA($A:$A))
Ez a képlet arra kéri az INDEX függvényt, hogy adja vissza az A oszlop utolsó nem üres cellájának tartományát ($A$6).
A végeredmény $A$2:$A$6, és a COUNTA függvény miatt dinamikus, mivel az utolsó sort is megtalálja. Mostantól használhatja ezt az „országok” definiált nevet egy adatellenőrzési szabályban, képletben, diagramban vagy bárhol, ahol hivatkoznunk kell az összes ország nevére.
Hozzon létre egy kétirányú dinamikus meghatározott tartományt
Az első példa csak dinamikus magasságú volt. Egy kis módosítással és egy másik COUNTA funkcióval azonban olyan tartományt hozhat létre, amely mind magasságban, mind szélességben dinamikus.
Ebben a példában az alább látható adatokat fogjuk használni.
Ezúttal létrehozunk egy dinamikusan meghatározott tartományt, amely tartalmazza a fejléceket. Kattintson a Képletek > Név meghatározása elemre.
A „Név” mezőbe írja be a „sales” szót, és írja be az alábbi képletet a „Refers To” mezőbe.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Ez a képlet a $A$1 értéket használja kezdőcellaként. Az INDEX függvény ezután a teljes munkalap egy tartományát ($1:$1048576) használja a betekintéshez és a visszatéréshez.
A COUNTA függvények egyike a nem üres sorok számlálására szolgál, a másik pedig a nem üres oszlopokhoz, így mindkét irányban dinamikussá válik. Bár ez a képlet A1-ből indult, bármelyik kezdőcellát megadhatta volna.
Most már használhatja ezt a meghatározott nevet (értékesítés) egy képletben vagy diagram adatsorként, hogy dinamikussá tegye őket.