Értéktartomány beállítása az Excelben

A Microsoft Excel használatakor nagyon gyakran a felhasználó nem tudja előre, hogy ennek eredményeként mennyi információ lesz a táblázatban. Ezért nem minden helyzetben értjük, hogy milyen tartományt kell lefedni. Végül is a cellák halmaza változó fogalom. A probléma elkerülése érdekében a tartománygenerálást automatikussá kell tenni, hogy az kizárólag a felhasználó által bevitt adatmennyiségen alapuljon.

A cellatartományok automatikus módosítása az Excelben

Az Excel automatikus tartományainak előnye, hogy jelentősen megkönnyítik a képletek használatát. Ezenkívül lehetővé teszik a nagyszámú, sok függvényt tartalmazó képletet tartalmazó összetett adatok elemzésének jelentős egyszerűsítését. Adhat nevet ennek a tartománynak, majd automatikusan frissül attól függően, hogy milyen adatokat tartalmaz.

Hogyan lehet automatikus tartománymódosítást végrehajtani az Excelben

Tegyük fel, hogy Ön befektető, akinek be kell fektetnie valamilyen tárgyba. Ennek eredményeként információt szeretnénk kapni arról, hogy összesen mennyit kereshet arra az időre, amíg a pénz erre a projektre fordítódik. Ennek az információnak a megszerzéséhez azonban rendszeresen figyelemmel kell kísérnünk, hogy ez az objektum mennyi összhasznot hoz számunkra. Készítse el ugyanazt a jelentést, mint ezen a képernyőképen.

Első pillantásra kézenfekvő a megoldás: csak összegezni kell az egész oszlopot. Ha bejegyzések jelennek meg benne, az összeg önállóan frissül. Ennek a módszernek azonban számos hátránya van:

1. Ha így sikerül megoldani a problémát, akkor a B oszlopban szereplő cellák más célra nem használhatók fel.
2. Egy ilyen táblázat sok RAM-ot fogyaszt, ami lehetetlenné teszi a dokumentum használatát gyenge számítógépeken.

Ezért ezt a problémát dinamikus neveken keresztül kell megoldani. Létrehozásához a következő műveletsort kell végrehajtania:

1. Lépjen a „Képletek” fülre, amely a főmenüben található. Lesz egy „Meghatározott nevek” rész, ahol van egy „Név hozzárendelése” gomb, amelyre rá kell kattintanunk.
2. Ezután megjelenik egy párbeszédpanel, amelyben ki kell töltenie a mezőket a képernyőképen látható módon. Fontos megjegyezni, hogy alkalmaznunk kell a függvényt = ELHELYEZÉS funkcióval együtt JELÖLJE BEautomatikus frissítési tartomány létrehozásához. 
3. Ezt követően a függvényt kell használnunk ÖSSZEG, amely a dinamikatartományunkat használja argumentumként. 

Ezen lépések elvégzése után láthatjuk, hogyan frissül a „bevétel” tartományba tartozó cellák lefedettsége, amikor új elemeket adunk hozzá.

OFFSET funkció az Excelben

Nézzük meg azokat a függvényeket, amelyeket korábban a „tartomány” mezőben rögzítettünk. A funkció használata ÁRTALMATLANÍTÁS meg tudjuk határozni a tartomány nagyságát, ha a B oszlopban hány cella van kitöltve. A függvény argumentumai a következők:

1. Indítsa el a cellát. Ezzel az argumentummal a felhasználó jelezheti, hogy a tartomány melyik cellája lesz a bal felső sarokban tekinthető. Lefelé és jobbra fog jelenteni.
2. Tartomány eltolása sorokkal. Ennek a tartománynak a használatával beállítjuk a cellák számát, amellyel a tartomány bal felső cellájához képest eltolás következzen be. Nemcsak pozitív értékeket használhat, hanem nullát és mínuszt is. Ebben az esetben előfordulhat, hogy az elmozdulás egyáltalán nem történik meg, vagy az ellenkező irányba történik.
3. Tartomány eltolása oszlopokkal. Ez a paraméter hasonló az előzőhöz, csak lehetővé teszi a tartomány vízszintes eltolásának mértékének beállítását. Itt nulla és negatív értékeket is használhat.
4. A magassági tartomány nagysága. Valójában ennek az érvelésnek a címe világossá teszi számunkra, hogy mit jelent. Ez a cellák száma, amellyel a tartománynak növekednie kell.
5. A tartomány értéke szélességben. Az érvelés hasonló az előzőhöz, csak az oszlopokra vonatkozik.

Az utolsó két argumentumot nem kell megadnia, ha nem szükséges. Ebben az esetben a tartomány értéke csak egy cella lesz. Például, ha megadja a képletet =ELTOLÁS(A1;0;0), ez a képlet ugyanarra a cellára fog hivatkozni, mint az első argumentumban. Ha a függőleges eltolás 2 egységre van állítva, akkor ebben az esetben a cella az A3 cellára fog hivatkozni. Most írjuk le részletesen, mit jelent a függvény JELÖLJE BE.

COUNT függvény az Excelben

A funkció használata JELÖLJE BE meghatározzuk, hogy a B oszlopban összesen hány cellát töltöttünk ki. Vagyis két függvény segítségével meghatározzuk, hogy a tartományban hány cellát töltenek ki, és a kapott információk alapján meghatározzuk a tartomány méretét. Ezért a végső képlet a következő lesz: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Nézzük meg, hogyan lehet helyesen megérteni ennek a képletnek az elvét. Az első argumentum arra mutat, hogy hol kezdődik a dinamikatartományunk. Esetünkben ez a B2 cella. További paraméterek nulla koordinátákkal rendelkeznek. Ez arra utal, hogy nincs szükségünk eltolásokra a bal felső cellához képest. Csak a tartomány függőleges méretét töltjük ki, aminek a függvényét használtuk JELÖLJE BE, amely meghatározza az adatokat tartalmazó cellák számát. A negyedik paraméter, amelyet kitöltöttünk, az egység. Így megmutatjuk, hogy a tartomány teljes szélességének egy oszlopnak kell lennie.

Így a függvény használatával JELÖLJE BE a felhasználó a lehető leghatékonyabban tudja használni a memóriát, ha csak azokat a cellákat tölti be, amelyek valamilyen értéket tartalmaznak. Ennek megfelelően nem lesznek további hibák annak a számítógépnek a gyenge teljesítményéhez kapcsolódó munkában, amelyen a táblázat működik.

Ennek megfelelően az oszlopok számától függő tartomány méretének meghatározásához hasonló műveletsort kell végrehajtania, csak ebben az esetben meg kell adnia az egységet a harmadik paraméterben, a képletet pedig a negyedikben. JELÖLJE BE.

Látjuk, hogy az Excel képletek segítségével nem csak a matematikai számításokat lehet automatizálni. Ez csak egy csepp a tengerben, de valójában lehetővé teszik szinte minden olyan művelet automatizálását, amely eszedbe jut.

Dinamikus diagramok Excelben

Így az utolsó lépésben létrehozhattunk egy dinamikus tartományt, melynek mérete teljesen attól függ, hogy hány kitöltött cellát tartalmaz. Ezen adatok alapján most dinamikus diagramokat hozhat létre, amelyek automatikusan megváltoznak, amint a felhasználó bármilyen változtatást végrehajt, vagy további oszlopot vagy sort ad hozzá. A műveletek sorrendje ebben az esetben a következő:

1. Kiválasztjuk a tartományunkat, majd beszúrunk egy „Hisztogram csoportosítással” típusú diagramot. Ezt az elemet a „Diagramok – hisztogram” szakasz „Beszúrás” részében találja.
2. A hisztogram egy véletlenszerű oszlopára bal egérgombbal kattintunk, ami után a =SERIES() függvény jelenik meg a függvénysorban. A képernyőképen láthatja a részletes képletet. 
3. Ezt követően néhány változtatást kell végrehajtani a képletben. Cserélnie kell a tartományt a „Sheet1!” után. a tartomány nevére. Ez a következő függvényt eredményezi: =SORO(1. lap!$B$1;;1. lap!bevétel;1)
4. Most már csak új rekordot kell hozzáadni a jelentéshez, és ellenőrizni kell, hogy a diagram automatikusan frissül-e vagy sem.

Vessünk egy pillantást a diagramunkra.

Foglaljuk össze, hogyan csináltuk. Az előző lépésben létrehoztunk egy dinamikatartományt, melynek mérete attól függ, hogy hány elemet tartalmaz. Ehhez a függvények kombinációját használtuk JELÖLJE BE и ÁRTALMATLANÍTÁS. Ezt a tartományt elneveztük, majd az erre a névre való hivatkozást használtuk hisztogramunk tartományaként. Nem olyan fontos, hogy az első szakaszban melyik konkrét tartományt válasszuk adatforrásként. A lényeg az, hogy később lecseréljük a tartomány nevére. Így sok RAM-ot takaríthat meg.

Elnevezett tartományok és felhasználásuk

Most beszéljünk részletesebben arról, hogyan lehet helyesen létrehozni elnevezett tartományokat, és hogyan lehet velük végrehajtani az Excel-felhasználó számára beállított feladatokat.

Alapértelmezés szerint normál cellacímeket használunk az időmegtakarítás érdekében. Ez akkor hasznos, ha egy tartományt egyszer vagy többször kell írnia. Ha állandóan használni kell, vagy adaptívnak kell lennie, akkor elnevezett tartományokat kell használni. Sokkal könnyebbé teszik a képletek létrehozását, és a felhasználó számára nem lesz olyan nehéz elemezni a nagyszámú függvényt tartalmazó összetett képleteket. Ismertessen a dinamikus tartományok létrehozásának néhány lépését.

Minden a cella elnevezésével kezdődik. Ehhez egyszerűen jelölje ki, majd írja be a kívánt nevet a neve mezőbe. Fontos, hogy könnyen megjegyezhető legyen. Van néhány korlátozás, amelyet figyelembe kell venni az elnevezés során:

1. A maximális hossza 255 karakter. Ez elég ahhoz, hogy olyan nevet adjon, amelyre a szíve vágyik.
2. A név nem tartalmazhat szóközt. Ezért, ha több szót tartalmaz, akkor az aláhúzás karakterrel elválasztható.

Ha később ennek a fájlnak a többi lapjain meg kell jelenítenünk ezt az értéket, vagy alkalmazni kell a további számításokhoz, akkor nem kell átváltani a legelső lapra. Csak felírhatja ennek a tartománycellának a nevét.

A következő lépés egy elnevezett tartomány létrehozása. Az eljárás alapvetően ugyanaz. Először ki kell választania egy tartományt, majd meg kell adnia a nevét. Ezt követően ez a név használható az összes többi adatműveletben az Excelben. Például az elnevezett tartományokat gyakran használják az értékek összegének meghatározására.

Ezenkívül lehetőség van elnevezett tartomány létrehozására a Képletek lapon a Név beállítása eszközzel. Miután kijelöltük, megjelenik egy ablak, ahol ki kell választanunk a tartományunk nevét, valamint meg kell adnunk azt a területet, amelyre manuálisan kiterjesztjük. Azt is megadhatja, hogy ez a tartomány hol működjön: egyetlen lapon belül vagy az egész könyvben.

Ha egy névtartomány már létrejött, akkor annak használatához létezik egy speciális szolgáltatás, a névkezelő. Nemcsak szerkesztést vagy új nevek hozzáadását teszi lehetővé, hanem törlését is, ha már nincs rájuk szükség.

Figyelembe kell venni, hogy ha a képletekben nevesített tartományokat használunk, akkor annak törlése után a képletek nem íródnak felül automatikusan a megfelelő értékekkel. Ezért hibák léphetnek fel. Ezért egy elnevezett tartomány törlése előtt meg kell győződnie arról, hogy az egyik képletben sem szerepel.

Egy elnevezett tartomány létrehozásának másik módja az, hogy lekérjük egy táblázatból. Ehhez van egy speciális eszköz, a „Létrehozás kijelölésből”. Mint tudjuk, a használatához először ki kell választania azt a tartományt, amelyet szerkeszteni fogunk, majd be kell állítania azt a helyet, ahol a címsoraink vannak. Ennek eredményeként ezen adatok alapján az Excel automatikusan feldolgozza az összes adatot, és automatikusan hozzárendeli a címeket.

Ha a cím több szót tartalmaz, az Excel automatikusan aláhúzással választja el őket.

Így kitaláltuk, hogyan hozhatunk létre dinamikus nevű tartományokat, és hogyan teszik lehetővé a nagy mennyiségű adattal végzett munka automatizálását. Mint látható, elegendő több funkciót és a funkcionalitásba épített programeszközöket használni. Egyáltalán nincs semmi bonyolult, bár egy kezdő számára első pillantásra annak tűnhet.