Hogyan hozhat létre elnevezett tartományokat az Excelben (lépésről lépésre)

Mi van a névben?

Ha Excel táblázatokkal dolgozik, ez sok időmegtakarítást és hatékonyságot jelenthet.

Ebben az oktatóanyagban megtudhatja, hogyan hozhat létre elnevezett tartományokat az Excelben, és hogyan használhatja azt időtakarékosság érdekében.

Nevezett tartományok az Excelben - Bevezetés

Ha valakinek fel kell hívnia vagy hivatkoznia kell rám, akkor a nevemet fogja használni (ahelyett, hogy azt mondaná, hogy egy férfi ilyen és olyan helyen tartózkodik ilyen és olyan magassággal és súlyával).

Jobb?

Hasonlóképpen, az Excelben nevet is adhat egy cellának vagy cellatartománynak.

Most a cellahivatkozás (például A1 vagy A1: A10) használata helyett egyszerűen használhatja a hozzá rendelt nevet.

Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel:

Ha ebben az adatkészletben a Dátumot tartalmazó tartományra kell hivatkoznia, akkor az A2: A11 kifejezést kell használni a képletekben. Hasonlóképpen, az értékesítési képviselő és az értékesítés esetében a B2: B11 és a C2: C11 értékeket kell használnia.

Bár nem baj, ha csak pár adatpontja van, de ha hatalmas, összetett adathalmazokat használ, a cellahivatkozások használata az adatokra való hivatkozáshoz időigényes lehet.

Az Excel Named Ranges megkönnyíti az Excel adatkészleteire való hivatkozást.

Az Excelben minden adatkategóriához létrehozhat egy elnevezett tartományt, majd ezt a nevet használja a cellahivatkozások helyett. Például a dátumok neve „Dátum”, az értékesítési képviselők neve „SalesRep”, az értékesítési adatok pedig „Értékesítés”.

Egy cella nevét is létrehozhatja. Például, ha egy cellában megvan az értékesítési jutalék százalékos aránya, akkor ezt a cellát „jutaléknak” nevezheti el.

Az Excel nevű tartományok létrehozásának előnyei

Íme az elnevezett tartományok használatának előnyei az Excelben.

Nevek használata cellahivatkozások helyett

Amikor elnevezett tartományokat hoz létre az Excelben, ezeket a neveket használhatja a cellahivatkozások helyett.

Például használhatja a = SUM (SALES) értéket a = SUM (C2: C11) helyett a fenti adatkészlethez.

Vessen egy pillantást az alább felsorolt ​​képletekre. A cellahivatkozások használata helyett az Elnevezett tartományokat használtam.

• Az 500 -nál nagyobb értékű értékesítések száma: = COUNTIF (Értékesítések, "> 500")
• Tom összes értékesítésének összege: = SUMIF (SalesRep, „Tom”, értékesítés)
• Joe által szerzett jutalék (Joe eladásai megszorozva a jutalék százalékával):
  = SUMIF (SalesRep, ”Joe”, Sales)*jutalék

Ön egyetért azzal, hogy ezek a képletek könnyen létrehozhatók és könnyen érthetők (különösen akkor, ha megosztja valakivel, vagy saját maga nézi újra.

A cellák kiválasztásához nem kell visszatérni az adatkészlethez

A Nevezett tartományok Excelben való használatának másik jelentős előnye, hogy nem kell visszamennie és kiválasztania a cellatartományokat.

Csak beírhat néhány ábécét a megnevezett tartományból, és az Excel megjeleníti a megfelelő elnevezett tartományokat (az alábbiak szerint):

A megnevezett tartományok dinamikussá teszik a képleteket

Az Excel nevű tartományok használatával dinamikusvá teheti az Excel képleteit.

Például értékesítési jutalék esetén a 2,5%érték helyett a Név tartományt használhatja.

Most, ha cége később úgy dönt, hogy 3%-ra emeli a jutalékot, egyszerűen frissítheti a Named Range -t, és minden számítás automatikusan frissül, hogy tükrözze az új jutalékot.

Hogyan hozhat létre elnevezett tartományokat az Excelben

Háromféleképpen hozhat létre elnevezett tartományokat az Excelben:

1. módszer - Név meghatározása

Az alábbi lépésekkel hozhat létre elnevezett tartományokat az Excel programban a Név meghatározása használatával:

• Válassza ki azt a tartományt, amelyhez elnevezett tartományt szeretne létrehozni az Excelben.
• Lépjen a Képletek -> Név definiálása menüpontra.
• Az Új név párbeszédpanelen írja be a kiválasztott adattartományhoz rendelni kívánt nevet. Megadhatja a hatókört, mint a teljes munkafüzetet vagy egy adott munkalapot. Ha kiválaszt egy adott lapot, a név nem lesz elérhető más lapokon.
• Kattintson az OK gombra.

Ezzel létrejön egy elnevezett tartomány SALESREP.

2. módszer: A Név mező használata

• Válassza ki azt a tartományt, amelyhez nevet szeretne létrehozni (ne válasszon fejléceket).
• Lépjen a Név mezőbe a Képlet sáv bal oldalán, és írja be annak nevét, amellyel létre kívánja hozni a Névtartományt.
• Ne feledje, hogy az itt létrehozott név a teljes munkafüzet számára elérhető lesz. Ha egy munkalapra szeretné korlátozni, használja az 1. módszert.

3. módszer: A Létrehozás a kiválasztásból lehetőség használata

Ez az ajánlott módszer, ha táblázatos formában vannak adatai, és minden oszlophoz/sorhoz elnevezett tartományt szeretne létrehozni.

Például, ha az alábbi adatkészletben gyorsan szeretne három elnevezett tartományt létrehozni (Dátum, Értékesítési_Rep és Értékesítés), akkor az alább látható módszert használhatja.

Az alábbi lépésekkel gyorsan létrehozhat elnevezett tartományokat egy adatkészletből:

• Válassza ki a teljes adathalmazt (beleértve a fejléceket).
• Lépjen a Képletek -> Létrehozás a kijelölésből (Billentyűparancs - Control + Shift + F3). Megnyílik a "Nevek létrehozása a kiválasztásból" párbeszédpanel.
• A Nevek létrehozása a kiválasztásból párbeszédpanelen jelölje be azokat a beállításokat, ahol a fejlécek találhatók. Ebben az esetben csak a felső sort választjuk ki, mivel a fejléc a felső sorban van. Ha a fejlécek mind a felső sorban, mind a bal oszlopban vannak, mindkettőt választhatja. Hasonlóképpen, ha az adatok úgy vannak elrendezve, hogy a fejlécek csak a bal oszlopban vannak, akkor csak a Bal oszlop opciót kell bejelölni.

Ez három elnevezett tartományt hoz létre - Dátum, Értékesítési_Rep és Értékesítés.

Ne feledje, hogy automatikusan felveszi a neveket a fejlécekből. Ha szóköz van a szavak között, aláhúzást szúr be (mivel nem lehet szóköz a megnevezett tartományokban).

Az Excel elnevezési tartományainak elnevezési konvenciója

Vannak bizonyos elnevezési szabályok, amelyeket ismernie kell az Excel nevű tartományok létrehozásakor:

• A megnevezett tartomány első karaktere betű és aláhúzás karakter (_), vagy fordított perjel (\) kell, hogy legyen. Ha valami másról van szó, akkor hiba jelenik meg. A fennmaradó karakterek lehetnek betűk, számok, speciális karakterek, pont vagy aláhúzás.
• Az Excelben nem használhat cellahivatkozásokat is megjelenítő neveket. Például nem használhatja az AB1 -et, mivel ez is cellahivatkozás.
• A szóközöket nem használhatja a megnevezett tartományok létrehozásakor. Például nem lehet értékesítési képviselő nevezett tartományként. Ha két szót szeretne összevonni, és elnevezett tartományt szeretne létrehozni, akkor aláhúzás, pont vagy nagybetűk használatával hozza létre. Például lehet Sales_Rep, SalesRep vagy SalesRep.
  • A megnevezett tartományok létrehozása során az Excel a nagy- és kisbetűket ugyanúgy kezeli. Például, ha létrehoz egy elnevezett tartományt SALES, akkor nem hozhat létre másik elnevezett tartományt, például „értékesítés” vagy „Értékesítés”.
• Egy elnevezett tartomány legfeljebb 255 karakter hosszú lehet.

Túl sok megnevezett tartomány az Excelben? Ne aggódj

Néha nagy adathalmazokban és összetett modellekben sok elnevezett tartományt hozhat létre az Excelben.

Mi van, ha nem emlékszik a létrehozott elnevezett tartomány nevére?

Ne aggódj - Íme néhány hasznos tipp.

Az összes elnevezett tartomány nevének megszerzése

A következő lépésekkel kaphatja meg a létrehozott összes elnevezett tartomány listáját:

• Lépjen a Képletek fülre.
• A Megnevezett csoportban kattintson a Használat a képletben elemre.
• Kattintson a „Nevek beillesztése” gombra.

Ekkor megjelenik a munkafüzet összes elnevezett tartományának listája. Neves tartomány használatához (képletekben vagy cellákban) kattintson duplán rá.

Az egyező elnevezett tartományok megjelenítése

• Ha van némi ötlete a névvel kapcsolatban, írjon be néhány kezdeti karaktert, és az Excel megjeleníti a legördülő listát a megfelelő nevekről.

Hogyan szerkesztheti a megnevezett tartományokat az Excelben

Ha már létrehozott egy elnevezett tartományt, akkor az alábbi lépésekkel szerkesztheti:

• Lépjen a Képletek fülre, és kattintson a Névkezelő elemre.
• A Névkezelő párbeszédpanel felsorolja az adott munkafüzet összes elnevezett tartományát. Kattintson duplán a szerkeszteni kívánt elnevezett tartományra.
• A Név szerkesztése párbeszédpanelen hajtsa végre a módosításokat.
• Kattintson az OK gombra.
• Zárja be a Névkezelő párbeszédpanelt.

Hasznos elnevezett tartomány parancsikonok (az F3 ereje)

Íme néhány hasznos billentyűparancs, amely hasznos lesz, amikor az Excel nevű tartományokkal dolgozik:

• Az összes elnevezett tartomány listájának beszerzése és beillesztése a képletbe: F3
• Új név létrehozása a Névkezelő párbeszédpanel használatával: Control + F3
• Elnevezett tartományok létrehozása a kiválasztásból: Control + Shift + F3

Dinamikus elnevezett tartományok létrehozása Excelben

Ebben az oktatóanyagban eddig statikus elnevezett tartományokat hoztunk létre.

Ez azt jelenti, hogy ezek a megnevezett tartományok mindig ugyanarra az adatkészletre vonatkoznak.

Például, ha az A1: A10 -t „Értékesítés” -nek nevezték el, az mindig az A1: A10 -re vonatkozik.

Ha további értékesítési adatokat ad hozzá, akkor manuálisan kell frissítenie a hivatkozást a megnevezett tartományban.

A folyamatosan bővülő adatkészletek világában ez végül sok időt igényelhet. Minden alkalommal, amikor új adatokat kap, előfordulhat, hogy frissítenie kell az Excel elnevezett tartományait.

A probléma megoldása érdekében létrehozhatunk dinamikus elnevezett tartományokat az Excelben, amelyek automatikusan figyelembe veszik a további adatokat, és belefoglalják azokat a meglévő elnevezett tartományba.

Például: Ha például két további értékesítési adatpontot adok hozzá, a dinamikus elnevezésű tartomány automatikusan A1: A12 -re hivatkozik.

Ez a fajta dinamikus elnevezésű tartomány létrehozható az Excel INDEX funkcióval. A cellahivatkozások megadása helyett a Nevesített tartomány létrehozása során a képletet adjuk meg. A képlet automatikusan frissül az adatok hozzáadása vagy törlése után.

Lássuk, hogyan hozhat létre dinamikus elnevezett tartományokat az Excelben.

Tegyük fel, hogy az értékesítési adatok az A2: A11 cellában vannak.

Az alábbi lépésekkel hozhat létre dinamikus elnevezett tartományokat az Excelben:

1. 1. Lépjen a Képlet fülre, és kattintson a Név definiálása elemre.
   2. Az Új név párbeszédpanelen írja be a következőt:
      • Név: Értékesítés
      • Hatály: Munkafüzet
      • Ide utal: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” és ””))
   3. Kattintson az OK gombra.

Kész!

Most van egy dinamikus elnevezésű tartománya „Értékesítés” névvel. Ez automatikusan frissül, amikor adatokat ad hozzá, vagy eltávolít belőle adatokat.

Hogyan működik a dinamikus elnevezett tartományok?

Ennek a működésnek a magyarázatához egy kicsit többet kell tudnia az Excel INDEX funkcióról.

A legtöbb ember az INDEX -et használja a sorok és oszlopok száma alapján egy érték visszaadására a listából.

De az INDEX függvénynek van egy másik oldala is.

Meg lehet szokni cellahivatkozást ad vissza amikor cellahivatkozás részeként használják.

Például itt van a képlet, amellyel dinamikus elnevezésű tartományt hoztunk létre:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””)) -> A képlet ezen része várhatóan visszaad egy értéket (ami a 10. érték a listából, figyelembe véve, hogy tíz tétel van).

Ha azonban hivatkozás előtt használják (=$ 2 $:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) az érték helyett a cella hivatkozását adja vissza.

Ezért itt visszatér = $ A $ 2: $ A $ 11

Ha két további értéket adunk az értékesítési oszlophoz, akkor az visszatér = $ A $ 2: $ A $ 13

Amikor új adatokat ad hozzá a listához, az Excel COUNTIF függvény visszaadja az adatok nem üres celláinak számát. Ezt a számot használja az INDEX függvény a lista utolsó elemének cellahivatkozásának lekéréséhez.

Jegyzet:

• Ez csak akkor működik, ha nincsenek üres cellák az adatokban.
• A fenti példában nagyszámú cellát (A2: A100) rendeltem a Named Range képlethez. Ezt az adatkészlet alapján módosíthatja.

Az OFFSET funkcióval dinamikus elnevezett tartományokat is létrehozhat az Excelben, azonban mivel az OFFSET függvény ingadozó, lassú Excel munkafüzethez vezethet. Az INDEX viszont félig illékony, így jobb választás a dinamikus elnevezett tartományok létrehozása az Excelben.

A következő Excel -erőforrások is tetszhetnek:

• Ingyenes Excel sablonok.
• Ingyenes online Excel képzés (7 részes online video tanfolyam).
• Hasznos Excel makrókód példák.
• 10 Speciális Excel VLOOKUP példák.
• Legördülő lista létrehozása Excelben.
• Elnevezett tartomány létrehozása a Google Táblázatokban.
• Hogyan lehet hivatkozni egy másik lapra vagy munkafüzetre az Excelben