Telefonszámunk: 1-472-0679

Dinamikus névtartomány képlettel

2017-01-07 - horvimi - Megtekintések száma: 1,685 - Kategória: Általános tippek, PIVOT tábla, Táblázatok, adattáblák
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016

Egy korábbi tananyagban bemutattam a névtartomány lényegét és használatát. Ott az alapfogalmak mellett arról volt szó, hogyan kell egy táblázattá alakított tartományban névtartományt definiálni. A táblázatokon alapuló névtartományok automatikusan követik a táblázat méretének  változásait. Ezt a módszert használhatjuk például akkor, hogy egy legördülő lista adatforrása rendszeresen bővül, és az új értékeket automatikusan szeretnénk megjeleníteni a listamenüben. Ezzel kapcsolatban érkezett ez a kérdés a fórumba, ahol megadtam a részletes megoldást is.

A táblázatok használatának azonban vannak korlátai, illetve az is lehet probléma, hogy ha van legalább egy táblázat a munkafüzetben, akkor néhány Excel funkció le lesz tiltva. Erről is írtam már egy anyagot. Tehát néha előfordul, hogy a dinamikus névtartományt nem lehet táblázatos megoldással megcsinálni.

Névtartomány képlettel

Tekintsük ezt a példát, ahol egy PIVOT táblát látunk. A feladat az, hogy az első oszlopa alapján készítsünk egy legördülő listamenüt. A PIVOT néha frissül, és a címkék listája bővülhet (vagy szűkülhet is akár). PIVOT táblákban nem lehet táblázatot definiálni, ezért a triviális megoldás itt nem lesz jó.

Dinamikus_nevtartomany_alap

Feltételezzük, hogy a PIVOT tábla pozíciója állandó, azaz mindig ugyanott van, csak a tartalma néha változik. Ha tudnánk egy olyan képletet írni, ami a B oszlop első adatcelláján kezdődő, és az utolsón végződő tartományt ad vissza, akkor megoldottuk a problémát. Tartományi hivatkozást készíthetünk az alábbi függvényekkel:

  • ELTOLÁS (OFFSET)
  • INDIREKT (INDIRECT)
  • INDEX

Ezek közül az első kettő un. VOLATILE függvény, azaz bármit módosítunk a munkafüzetben, mindig ujraszámol. Ebből következően az őt használó cellák is mindig újraszámolódnak. Ez csak akkor lehet baj, ha sok cella függ ettől a képlettől. Én most úgy döntöttem, hogy az INDIREKT és az INDEX megoldást fogom bemutatni.

 

Az utolsó sor megállapítása

Rögzítettük, hogy az első cella pzíciója fix, az utolsó celláé  változhat.

A képen látható, hogy az első cellát elneveztem “kezd’ néven.

Az utolsó kitöltött sor számát szeretnénk megkeresni. Mivel a B oszlopban szövegek vannak, ha itt keresünk egy “nagyon nagy” szöveget, ami biztosan nem fordul elő egyik cellában sem, akkor az utolsót fogjuk visszakapni ezzel a képlettel:

=HOL.VAN("zzzzzzzz";PIVOT!$B:$B;1)

Látható, hogy a “nagyon nagy” szöveg mit is jelent. Az ABC utolsó betűjét jó sokszor ismételve adjuk meg a keresendő szöveget. Mivel nem pontos egyezést kérünk, mindig a legutolsó tétel sorszámát fogjuk megkapni. A PIVOT  a munkalap neve, ami lényeges, hogy a nevet bármely munkalapon használhassuk.

Képlet elnevezése

A későbbi képletek egyszerűsítése érdekében, nevezzük el azt a képletet a névkezelőben. (Képletek->Névkezelő->Új…)

utolso-sor-keplet

Ezek után bármely képletben hivatkozva az us névre, visszakapjuk az aktuális utolsó sort dinamikusan.

Névtartomány INDIREKT függvénnyel

Az INDIREKT függvény szövegből képez egy hivatkozást. Emiatt elképesztően rugalmas, bravúrosan használható dinamikus hivatkozások előállítására. Cserébe viszont VOLATILE, azaz jelentősen lassíthatja a modellünket. A tartomány hivatkozást a kezd nevű cellától a B oszlop utolsó soráig így lehet előállítani:

=kezd:INDIREKT("PIVOT!B"&us)

Ezt a képletet is elnevezzük

Nevek_Indirekt

Legördülő lista adatforrás megadása

Adatok->Érvényesítés->Lista

Ervenyesites

Névtartomány INDEX függvénnyel

Ez a megoldás nem lesz VOLATILE ezért inkább ezt javasolják a nagyok. Az INDEX függvény valójában mindig egy hivatkozást ad vissza, ezért így is használható:

=kezd:INDEX(PIVOT!$B:$B;us)

Ez a képlet a kezd cellától a B oszlop utolsó soráig terjedő hivatkozást ad vissza. Ezt is nevezzük el:

Nevek_Index

A legördülő lista adatforrásaként adjuk meg a Nevek_Index nevet.

Letölthető munkafüzet

Dinamikus-nevtartomanyok

És mindez élőben

1 Comment
  1. Gyönyörű! =HOL.VAN(“ami nincs”) REMEK! Köszönöm szépen! 🙂

Vélemény, hozzászólás?