nevek - profi eszközök - A

2021-09-03    Excel 2019
nem szomszédos cellák nevesítése
A tartalom szerinti azonosítás lehetősége a nem szomszédos cellákra is kiterjed. Nézzünk egy példát, hogy lássuk miről van szó.
nem szomszédos azonos tartalmú tartományok
Három önálló tartomány, de celláik tartalma azonos: születésnapok. Célszerű lenne őket egy tartalmi egységként kezelni. Hozzunk létre nevet! Kijelöljük a tartományokat (B2:B11, E2:E11, H2:H11) és Képletek, Definiált nevek, Név megadása. Deklarálhatunk lokális és globális nevet is. A név „Hivatkozás”-a az =A!$B$2:$B$11; A!$E$2:$E$11; A!$H$2:$H$11 képlet lesz. A nem szomszédos cellák neveit a névkezelőben az Értékek oszlopban álló bejegyzésükről ismerhetjük fel: {…}.
Használjuk a nevet! Mikor született a harmadik legidősebb ember? =KICSI(születésnapok; 3). Hozzátartozik az igazsághoz, hogy a nem szomszédos cellák neveinek függvény-támogatottsága korlátozott!
névvel azonosított nem szomszédos cellák függvényei
A feltételes műveletek (SZUMHA, DARABTELI, MAXHA…) függvényei sem működnek a nem szomszédos cellák neveivel, még akkor sem, ha a feltétel a nevesített cellák tartalmára vonatkozik.
változó méretű tartomány nevesítése
Van egy táblázatunk darabszámokkal, amelynek oszlopcímkéi hónapok, sorcímkéi pedig település-nevek. A táblázatunk az idő múlásával, és újabb települések regisztrációjával egyre csak növekszik.
folyamatosan változó méretű tartományok
A három tartalmi egységet névvel szeretném azonosítani. A név deklarálását nehezíti, hogy csak az elnevezendő tartományok (két vektor és egy mátrix) első cellái adottak, az utolsókat mindig a táblázat aktuális állapota határozza meg.
folyamatosan változó méretű tartományok, két vektor és egy mátrix
Megfigyelhettük, hogy a program a nevesített tartományok hivatkozását képletbe ágyazottan tárolja. Ott áll mindig az egyenlőségjel az azonosító előtt. Ez a megoldás teszi lehetővé, hogy változó méretű tartományokat is tartalmuk szerint azonosíthassunk.
A hivatkozás-képletben az ELTOLÁS függvényt fogjuk használni, amely képlet-környezetétől függően egy tartomány hivatkozását vagy celláinak tartalmát adja eredményül. Például, OSZLOPOK(<ELTOLÁS>) vagy =SZUM(<ELTOLÁS>). A függvény működését az alábbi képen tanulmányozhatjuk.
az ELTOLÁS függvény működése és argumentumai
A tartomány első cellájának pozícióját egy másik cellához képest kell megadnunk: függőlegesen sorokban, vízszintesen oszlopokban. Hányadik sorban és hányadik oszlopban áll a tartomány első cellája, ha a viszonyításként használt cella sorszáma nulla? A viszonyítási pont fölötti és tőle balra álló „első cella” pozíció-száma negatív előjelű. A függvény utolsó két (opcionális) argumentumával a tartomány sor és oszlop-számát deklaráljuk.
A mi képleteink nem lesznek ennyire bonyolultak, mert a viszonyítási pontként használt cella és a tartomány első cellája mindhárom esetben azonos. Tehát a függvény pozíció-argumentumai (a második és a harmadik argumentum) nullák lesznek. Ne felejtsük megnyomni az F2-t a Hivatkozás mezőbe kattintás után, mert különben sokat fogunk szenvedni a képlet szerkesztésével.
az ELTOLÁS függvény alkalmazása név hivatkozás-képletében
Három érdekes részletet figyelhetünk meg a képen: a Névkezelő Értékek oszlopában álló három pontra és a hasra ütésre, valamint a névvel megadott tartományokra. Persze, azért volt egy kis tudatosság a DARAB2 utolsó celláinak megadásakor. Ha minden magyar települést felveszünk a táblázatba az is csak háromezer-hatszáz-valamennyi sor lenne. A hónapok tartománya meg bőven elegendő tizenhárom évre. És addig még sok víz lefolyik a Dunán!
De egyáltalán: miért kell végpont? Miért nem adjuk meg a teljes oszlopot (B:B) és a teljes sort (2:2)? Úgy is tehetnénk, de a vektorok első üres celláinak megkeresése sem lehetetlen! Próbáljuk ki az utóbbi variációt!
A tartományok utolsó celláinak hivatkozását az INDEX függvénnyel fogjuk képezni. A függvény argumentumai a tartomány (első argumentum), valamint a tartomány egyik cellájának sorszáma a tartományban függőlegesen (második argumentum) és vízszintesen (harmadik, nem kötelező argumentum). A vektor utolsó cellájának sorszámát a HOL.VAN függvény segítségével számolhatjuk ki. Lekérdezzük az első üres cella sorszámát, majd ebből kivonunk egyet. Természetesen, tömbös kiértékeléssel (Shift+Ctrl+Enter).
az INDEX függvény alkalmazása név hivatkozás-képletében
A tömbös kiértékelést jelző kapcsos zárójeleket a Névkezelőben hiába keressük, a tervezők megfeledkeztek róla. A képletekben ismét szerepelnek a nagy üres tartományok, de most nem rónak felesleges terhet a programra, mert a keresés azonnal leáll, ha a HOL.VAN megtalálta az első üres cellát!
A képletekben szereplő ÜRES függvény IGAZ logikai értéket ad eredményül, ha a vizsgált cella nem rendelkezik tartalommal. A tartomány sorainak illetve oszlopainak számát a SOROK illetve az OSZLOPOK függvényekkel állapíthatjuk meg.
beviteli lista névből
Az objektumok tartalom szerinti azonosítása az adatbeviteli listák alkalmazását is egyszerűsíti. Egy összetett feladat megoldásán keresztül mutatom be a két szolgáltatás kapcsolatát.
Egy vállalat három városban (D1:F1) tart fenn képviseletet. Az ügyeket a képviselők (D2:D8, E2:E10; F2:F9) viszik, akiknek létszáma, mindhárom városban, erősen fluktuál.
adatbeviteli lista névből, példa
Képzeljünk el egy táblázatot, amely az elintézendő ügyek adatait tartalmazza. Ennek a táblázatnak az első két oszlopát (város, képviselő) fogjuk elkészíteni. Mindkét oszlop celláinak kitöltését adatbeviteli listákkal tervezzük felügyelni. A táblázat kitöltője először várost, majd képviselőt választ. Természetesen a B oszlop listáit a kiválasztott város figyelembe vételével kell majd elkészítenünk. Először nevesítsük a képviselők változó méretű tartományait.
adatbázis-táblázatok képlettel megadott nevek helyett
Most nem vacakoltam képletekkel, hanem létrehoztam három adatbázis-táblázatot (Beszúrás, Táblázatok, Táblázat). Azoknak is van neve! Természetesen az automatikus neveket (TáblázatX) nekünk kell lecserélni a város-nevekre. Ezután következik az A oszlop beviteli listáinak elkészítése.
beviteli lista névből, deklaráció
Tehát az A oszlop celláinak listájában csupa név áll. És mi a kiválasztott névre fogunk hivatkozni az INDIREKT függvénnyel, amikor megadjuk a B oszlop beviteli listáinak forrását.
névre hivatkozó beviteli lista deklarációja
A deklaráció közben, amikor átlépünk a Hibajelzés lapra és a deklaráció befejezésekor hiba-üzenetet kapunk.
névre hivatkozó beviteli lista deklarációja közben megjelenő hibaüzenet
…mert még nincs város-név az A2-es cellában. Mindkét esetben válaszoljunk határozott igennel! Vonjuk le a tapasztalást! Ha az INDIREKT függvénnyel egy nevet tartalmazó cellára hivatkozunk, akkor a függvény nem a karakterláncot, szóval nem a nevet, hanem az adott névvel azonosított cellák tartalmát adja eredményül.
az INDIREKT függvény működése ha a hivatkozott cella nevet tartalmaz
…és legyen világ-béke!
margitfalvi.arpad@gmail.com