megnevezett tartományok egyike ezeknek a ropogós régi funkcióknak az Excel-ben, amelyeket kevés felhasználó ért meg. Az új felhasználók furcsának és ijesztőnek találhatják őket, sőt a régi kezek is elkerülhetik őket, mert értelmetlennek és összetettnek tűnnek.
de megnevezett tartományok valójában egy nagyon jó funkció. A képleteket sokkal könnyebben lehet létrehozni, olvasni és karbantartani. Bónuszként megkönnyítik a képletek újrafelhasználását (hordozhatóbb).
valójában a névtartományokat használom a képletek tesztelésekor és prototípuskészítésekor. Segítenek abban, hogy a képletek gyorsabban működjenek., Azért is használok elnevezett tartományokat, mert lusta vagyok, és nem szeretek összetett hivatkozásokat beírni:)
A megnevezett tartományok alapjai Excel-ben
mi a megnevezett tartomány?
a megnevezett tartomány csak egy ember által olvasható név egy Excel cellatartomány számára. Például, ha az A1:A100 “adat” tartományt nevezem, akkor a MAX segítségével a maximális értéket egyszerű képlettel kaphatom meg:
=MAX(data) // max value
a megnevezett tartományok szépsége az, hogy értelmes neveket használhat a képletekben anélkül, hogy sejthivatkozásokra gondolna., Ha van egy megnevezett tartomány, csak használja, mint egy sejt referencia. Mindezek a képletek az “adatok”nevű tartományra érvényesek:
=MAX(data) // max value=MIN(data) // min value=COUNT(data) // total values=AVERAGE(data) // min valuevideó: a megnevezett tartomány létrehozása
egy megnevezett tartomány létrehozása egyszerű
egy megnevezett tartomány létrehozása gyors és egyszerű. Csak válasszon ki egy cellatartományt, majd írjon be egy nevet a név mezőbe. Ha megnyomja a return gombot, a név létrejön:
az új tartomány gyors teszteléséhez válassza ki az új nevet a névmező melletti legördülő menüből. Az Excel kiválasztja a munkalap tartományát.,
az Excel automatikusan létrehozhat neveket (ctrl + shift + F3)
ha jól strukturált adatai vannak címkékkel, akkor az Excel névtartományokat hozhat létre az Ön számára. Csak válassza ki az adatokat, valamint a címkéket, majd használja a” Create from Selection”parancsot a képletek lapon a szalag:
használhatja a billentyűparancs vezérlő + shift + F3.
ezzel a funkcióval 12 állam lakosságának névtartományokat hozhatunk létre egy lépésben:
amikor az OK gombra kattint, a nevek létrejönnek., Az összes újonnan létrehozott nevet a névmező melletti legördülő menüben találja meg:
a létrehozott nevekkel ilyen képletekben használhatja őket
=SUM(MN,WI,MI)frissítés nevű tartományok a Névkezelőben (Control + F3)
p > miután létrehozott egy megnevezett tartományt, használja a névkezelőt (control + F3), hogy szükség szerint frissítse. Válassza ki a használni kívánt nevet, majd módosítsa közvetlenül a hivatkozást (azaz szerkessze a “hivatkozást”), vagy kattintson a jobb oldali gombra, majd válasszon ki egy új tartományt.,
a hivatkozás frissítéséhez nincs szükség a Szerkesztés gombra. Ha a Bezárás gombra kattint, a tartomány neve frissül.
Megjegyzés: Ha egy munkalapon egy teljes megnevezett tartományt választ ki, akkor áthúzhat egy új helyre, és a hivatkozás automatikusan frissül. Nem tudom azonban a tartományhivatkozások beállításának módját, ha közvetlenül a munkalapra kattint, majd húzza. Ha tudod, hogyan kell ezt csinálni,harangjáték alatt!
tekintse meg az összes megnevezett tartományt (control + F3)
a munkafüzet összes megnevezett tartományának gyors megtekintéséhez használja a névmező melletti legördülő menüt.,
Ha további részleteket szeretne látni, nyissa meg a Névkezelőt (Control + F3), amely felsorolja az összes nevet referenciákkal, és szűrőt is biztosít:
Megjegyzés: Mac rendszeren nincs Névkezelő, ezért ehelyett a név meghatározása párbeszédpanelt fogja látni.
másolja be az összes megnevezett tartományt (F3)
Ha a munkafüzetben a megnevezett tartományok tartósabb rekordját szeretné, beillesztheti a nevek teljes listáját bárhová., Menjen a képletekhez > használja a képletben (vagy használja az F3 parancsikont), majd válassza a Beillesztés nevét > beillesztés lista:
a Beillesztés lista gombra kattintva láthatja a munkalapba beillesztett neveket és hivatkozásokat: p>
lásd a neveket közvetlenül a munkalapon
Ha a nagyítási szintet 40% – nál kisebbre állítja, az Excel közvetlenül a munkalapon jeleníti meg a tartományneveket:
köszönöm ezt a tippet, Felipe!,
nevek szabályok
névtartományok létrehozásakor kövesse az alábbi szabályokat:
Névtartományok képletekben
A megnevezett tartományok könnyen használhatók a
képletekben például azt mondhatjuk, hogy egy cellát nevez meg a munkafüzetben “frissítve”. Az ötlet az, hogy az aktuális dátumot a cellába helyezheti (Ctrl+;), majd a munkafüzet máshol található dátumára hivatkozhat.
a B8 képlete így néz ki:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")ezt a képletet bárhol beillesztheti a munkafüzetbe, és helyesen fog megjelenni., Amikor megváltoztatja a dátumot a “frissítve”, az üzenet frissíti, ahol a képletet használják. Lásd ezt az oldalt további példákat.
megnevezett tartományok jelennek meg a
képlet beírásakor miután létrehozott egy megnevezett tartományt, automatikusan megjelenik a képletekben, amikor beírja a név első betűjét. Nyomja meg a tab gombot a név megadásához, ha van egyezés, és szeretné, hogy az Excel írja be a nevet.,
A megnevezett tartományok úgy működhetnek, mint az állandók
mivel a megnevezett tartományok egy központi helyen jönnek létre, úgy használhatja őket, mint konstansok cellahivatkozás nélkül. Például létrehozhat olyan neveket, mint az “MPG” (mérföld per gallon) és a “CPG” (cost per gallon), és hozzárendelhet rögzített értékeket:
akkor ezeket a neveket bárhol használhatja képletekben, és frissítheti értéküket egy központi helyen.,
A megnevezett tartományok alapértelmezés szerint abszolút értékűek
alapértelmezés szerint a megnevezett tartományok abszolút referenciaként viselkednek. Például, ezen a munkalapon a következő képlettel számítható ki az üzemanyag lenne:
=C5/$D$2
A referencia-D2 abszolút (zárva), így a képlet másolhatók le anélkül, D2 változik.,
Ha D2 “MPG”-t nevezünk, a képlet a következő lesz:
=C5/MPG
mivel az MPG alapértelmezés szerint abszolút, a képlet a D oszlopba másolható.
A megnevezett tartományok is lehetnek relatív
bár a megnevezett tartományok alapértelmezés szerint abszolútak, relatív is lehetnek. A relatív nevű tartomány olyan tartományra utal, amely az aktív cella helyzetéhez viszonyítva van a tartomány létrehozásakor. Ennek eredményeként a relatív elnevezett tartományok hasznosak olyan általános képletek felépítésében,amelyek bárhol is működnek.,
például létrehozhat egy általános “CellAbove” nevű tartományban, mint ez:
- Válassza ki a cellát A2
- Ctrl + F3 megnyitásához Neve Manager
- Lapon a ‘Utal, hogy’ rész, akkor típusa: =A1
CellAbove most letölteni az érték a cella felett bárhol is használják.
fontos: a név létrehozása előtt ellenőrizze, hogy az aktív cella a megfelelő helyen van-e.
alkalmazza a megnevezett tartományokat a meglévő képletekre
ha olyan meglévő képletei vannak, amelyek nem használnak megnevezett tartományokat, kérheti az Exceltől, hogy alkalmazza a megnevezett tartományokat az Ön képleteiben., Kezdje a frissíteni kívánt képleteket tartalmazó cellák kiválasztásával. Ezután futtassa a képleteket >nevek meghatározása > nevek alkalmazása.
Az Excel ezután felváltja azokat a hivatkozásokat, amelyeknek megfelelő névtartományuk van a névvel.
neveket is alkalmazhat a find and replace segítségével:
fontos: mentse el a munkalap biztonsági mentését, és válassza ki azokat a cellákat, amelyeket meg szeretne változtatni a képletek keresése és cseréje előtt.,A megnevezett tartományok legfontosabb előnyei
A megnevezett tartományok megkönnyítik a képletek olvasását
a megnevezett tartományok legnagyobb előnye, hogy megkönnyítik a képletek olvasását és fenntartását. Ennek oka az, hogy a rejtélyes hivatkozásokat értelmes nevekkel helyettesítik. Vegyük például ezt a munkalapot a naprendszerünk bolygóinak adataival., Nélkül nevű tartományban, egy FKERES képlet, hogy hozzon “Helyzetbe” a tábla elég rejtélyes:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Azonban a B3:E11 egy “adatok”, valamint H4 nevű “bolygó”, írhatunk képletek, mint ez:
=VLOOKUP(planet,data,2,0) // position=VLOOKUP(planet,data,3,0) // diameter=VLOOKUP(planet,data,4,0) // satellites
első pillantásra látható az egyetlen különbség az, hogy ezek a képletek az oszlop index.
A megnevezett tartományok hordozhatóvá és újrafelhasználhatóvá teszik a képleteket
a megnevezett tartományok sokkal könnyebbé teszik a képlet újrafelhasználását egy másik munkalapon., Ha egy munkalapon előre definiál neveket, beilleszthet egy képletet, amely ezeket a neveket használja, és “csak működik”. Ez egy nagyszerű módja annak, hogy gyorsan kap egy képlet működik.
például ez a képlet egyedi értékeket számít a numerikus adatok tartományában:
=SUM(--(FREQUENCY(data,data)>0))a képlet gyors “portolásához” nevezze meg az “adatok” tartományt, majd illessze be a képletet a munkalapba. Mindaddig, amíg az “adatok” numerikus értékeket tartalmaz, a képlet egyenesen működik.,
tipp: azt javaslom, hogy hozza létre a szükséges tartományneveket *először* a rendeltetési munkafüzetben, majd másolja a képletbe csak szövegként (azaz ne másolja a képletet tartalmazó cellát egy másik munkalapon, csak másolja a képlet szövegét). Ez megakadályozza az Excel-t abban, hogy neveket hozzon létre menet közben, így teljes mértékben ellenőrizheti a névkészítési folyamatot. Csak képletszöveg másolásához másolja a szöveget a képletsávból, vagy másolja egy másik alkalmazáson keresztül (azaz böngésző, szövegszerkesztő stb.).).
megnevezett tartományok használhatók a navigációhoz
a megnevezett tartományok nagyszerűek a gyors navigációhoz., Csak válassza ki a legördülő menüt a név mező mellett, majd válassza ki a nevet. Amikor elengedi az egeret, a tartomány lesz kiválasztva. Ha EGY megnevezett tartomány létezik egy másik lapon, akkor automatikusan az adott lapra kerül.
megnevezett tartományok jól működnek a hiperhivatkozásokkal
a megnevezett tartományok megkönnyítik a hiperhivatkozásokat. Például, ha az A1-et a Sheet1 “home” – nak nevezi, létrehozhat egy hiperhivatkozást valahol máshol, amely visszavisz oda.,
használata nevű tartomány belül a HIPERHIVATKOZÁS függvényt, adjunk hozzá fél kiló szimbólum előtt nevű tartomány:
=HYPERLINK("#home","take me home")Megjegyzés: furcsa, nem hyperlink egy asztalhoz, mint egy normál tartomány neve. Megadhat azonban egy táblázattal (azaz =Table1) megegyező nevet, és erre hivatkozhat. Ha valaki tudja, hogyan lehet közvetlenül összekapcsolni egy asztalt, csengessen be!,
megnevezett tartományok adatellenőrzéshez
a nevek tartományai jól működnek az adatok érvényesítéséhez, mivel lehetővé teszik egy logikusan elnevezett hivatkozás használatát a bemenet érvényesítéséhez egy legördülő menüvel. Az alábbiakban a g4:G8 tartomány neve “statuslist”, majd alkalmazza az adatellenőrzést egy ilyen listával összekapcsolva:
az eredmény egy legördülő menü az E oszlopban, amely csak a megnevezett tartományban engedélyezi az értékeket:
dinamikus nevű tartományok
a nevek tartományai rendkívül hasznosak, ha automatikusan beállítják a munkalap új adatait., Az így beállított tartományt “dinamikus nevű tartománynak”nevezik. Két módja van, hogy egy tartomány dinamikus: képletek, táblázatok.
Dynamic named range with a Table
a Table is the easified way to create a dynamic named range. Válassza ki az adatok bármelyik celláját, majd használja a parancsikont Control + t:
Excel táblázat létrehozásakor automatikusan létrehoz egy nevet (pl. Table1), de átnevezheti a táblát, ahogy tetszik. Miután létrehozott egy táblát, automatikusan kibővül, amikor adatokat ad hozzá.,
dynamic named range with a formula
egy dinamikus nevű tartományt is létrehozhat képletekkel, olyan funkciók használatával, mint az eltolás és az INDEX. Bár ezek a képletek mérsékelten összetettek, könnyű megoldást nyújtanak, ha nem szeretne asztalt használni., Az alábbi linkek példákat adnak teljes magyarázatokkal:
- példa a dinamikus tartomány képletére INDEX
- példa a dinamikus tartomány képletére OFFSET
táblázat nevek az adatellenőrzésben
mivel az Excel táblázatok Automatikus dinamikus tartományt biztosítanak, úgy tűnik, hogy természetes illeszkedést jelentenek az adatellenőrzési szabályokhoz, ahol a cél az, hogy érvényesítsék egy olyan listát, amely mindig változik. A táblázatok egyik problémája azonban az, hogy nem használhat strukturált hivatkozásokat közvetlenül az adatok érvényesítésére vagy a feltételes formázási szabályokra., Más szavakkal, nem használhat táblázatnevet feltételes formázásban vagy adatellenőrzési beviteli területeken.
azonban, mint megkerülő megoldás, megadhat egy megnevezett tartományt, amely egy táblára mutat, majd használhatja a megnevezett tartományt az adatok érvényesítéséhez vagy a feltételes formázáshoz. Az alábbi videó részletesen bemutatja ezt a megközelítést.
videó: hogyan kell használni nevű tartományok táblázatok
törlése nevű tartományok
Megjegyzés: Ha van képletek, amelyek hivatkoznak megnevezett tartományok, érdemes frissíteni a képleteket először eltávolítása előtt nevek. Ellenkező esetben látni fogja a # nevet?, hibák a képletekben, amelyek továbbra is törölt nevekre utalnak. A megnevezett tartományok eltávolítása előtt mindig mentse el a munkalapot, ha problémái vannak, és vissza kell térnie az eredetihez.
megnevezett tartományok a
cellák törlésekor és beillesztésekor a megnevezett tartomány *részének törlésekor, vagy ha cellákat/sorokat / oszlopokat helyez be egy megnevezett tartományba, a tartományhivatkozás ennek megfelelően módosul, és érvényes marad. Ha azonban törli az összes cellát, amely egy megnevezett tartományt tartalmaz, a megnevezett tartomány elveszíti a hivatkozást, és #REF hibát jelenít meg., Például, ha az A1-et “tesztnek” nevezem, akkor az A oszlop törlése után a Névkezelő “utal” – ra:
=Sheet1!#REF!nevek törlése a Névkezelővel
a névtartományok manuális eltávolításához nyissa meg a névkezelőt, válasszon ki egy tartományt, majd kattintson a Törlés gombra. Ha egyszerre több nevet szeretne eltávolítani, akkor a Shift + Click vagy a Ctrl + Click több név kiválasztásához, majd egy lépésben törölheti.,
nevek törlése hibákkal
ha sok neve van referenciahibákkal, akkor a névkezelőben található Szűrő gomb segítségével szűrheti a hibákat tartalmazó neveket:
akkor shift+kattintson az összes név kiválasztásához és a törléshez.
megnevezett tartományok és hatókör
megnevezett tartományok az Excelben van valami úgynevezett “hatókör”, amely meghatározza, hogy egy megnevezett tartomány helyi-e egy adott munkalapra, vagy globális-e a teljes munkafüzetben. A globális nevek köre “munkafüzet”, a helyi nevek köre megegyezik a létező lap nevével., Például a helyi név hatóköre lehet “Sheet2”.
A scope
elnevezett tartományok globális hatókörrel hasznosak, ha azt szeretné, hogy a munkafüzet összes lapja hozzáférjen bizonyos adatokhoz, változókhoz vagy állandókhoz. Például, lehet, hogy egy globális nevű tartomány egy adókulcs feltételezés használt több munkalapok.
helyi hatókör
helyi hatókör azt jelenti, hogy a név csak a létrehozott lapon működik. Ez azt jelenti, hogy több munkalap is lehet ugyanabban a munkafüzetben, amelyek mindegyike ugyanazt a nevet használja., Például, lehet, hogy van egy munkafüzet havi nyomkövető lapokkal (havonta Egy), amelyek név szerint azonos nevű tartományokat használnak, amelyek mindegyike helyben van. Ez lehetővé teszi, hogy ugyanazokat a képleteket újra felhasználhassa különböző lapokban. A helyi hatókör lehetővé teszi, hogy az egyes lapok nevei megfelelően működjenek anélkül, hogy ütköznének a többi lap nevével.
Hogy olvassa egy név, egy helyi hatálya alá, akkor az előtag a lap neve, a tartomány neve: Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenuea Tartomány nevek létre a név mezőbe automatikusan globális hatálya alá., A viselkedés felülírásához adja hozzá a lap nevét a név meghatározásakor:
Sheet3!my_new_nameglobális hatókör
globális hatókör azt jelenti, hogy a név a munkafüzetben bárhol működni fog. Például megnevezhet egy cellát “last_update”, írjon be egy dátumot a cellába. Ezután az alábbi képlettel megjelenítheti a munkalapon utoljára frissített dátumot.
=last_updatea globális neveknek egyedinek kell lenniük egy munkafüzetben.
helyi hatókör
helyileg hatókörű elnevezett tartományok értelme munkalapok, amelyek a megnevezett tartományok a helyi feltételezések csak., Például, lehet, hogy van egy munkafüzet havi nyomkövető lapokkal (havonta Egy), amelyek név szerint azonos nevű tartományokat használnak, amelyek mindegyike helyben van. A helyi hatókör lehetővé teszi, hogy az egyes lapok nevei megfelelően működjenek anélkül, hogy ütköznének a többi lap nevével.
A névtartomány hatókörének kezelése
alapértelmezés szerint a névtáblával létrehozott új nevek globálisak, így nem szerkesztheti a névtartomány hatókörét a létrehozás után. Megkerülésként azonban törölhet egy nevet a kívánt hatókörrel.,
ha egyszerre több nevet szeretne megváltoztatni a globálisról a helyire, néha érdemes átmásolni a neveket tartalmazó lapot. Ha lemásol egy megnevezett tartományokat tartalmazó munkalapot, az Excel másolja a megnevezett tartományokat a második lapra, ugyanakkor megváltoztatja a hatókört helyi értékre. Miután megkapta a második lapot helyileg ható nevekkel, opcionálisan törölheti az első lapot.
Jan Karel Pieterse és Charles Williams kifejlesztettek egy Névkezelő nevű segédprogramot, amely számos hasznos műveletet biztosít a megnevezett tartományokhoz. A Névkezelő segédprogramot itt töltheti le.,
