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ó.
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.
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…)
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
Legördülő lista adatforrás megadása
Adatok->Érvényesítés->Lista
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:
A legördülő lista adatforrásaként adjuk meg a Nevek_Index nevet.
Gyönyörű! =HOL.VAN(„ami nincs”) REMEK! Köszönöm szépen! 🙂
Üdv,
ez egy szép megoldás…és mi a helyzet akkor, ha tömböm utolsó sorát fixen ismerem, de nekem az offset függvény kiváltására az első sort kell dinamikusan meghatározni mindig az előző találat (vlookup vagy index,match-esetén), mivel az offset nem fut le zárt külső hivatkozáson?
Üdv…
mmmm az offset-et ezzel sem tudom kiváltani, nem fut le zárt doksin. Gondolom nem hozza létre így a tartományt.
Az INDIRECT és az OFFSET biztosan nem fut zárt doksin.
Kicsit utánanéztem, és úgy tűnik, hogy az INDEX-el is vannak problémák.
Makrós megoldások vannak UDF-ként vay ADD-In-ként, amiről itt olvastam, de nem próbáltam
https://www.mrexcel.com/forum/excel-questions/618039-index-match-referenced-file-path-closed-file.html
Imre
Üdv,
Köszönöm a megoldást, igazán remek.
De, miért nem működik (nem Pivot tábla), mikor az így meghatározott táblázatot egy másik fülön egy cellára (ahol a táblázat neve szerepel) indirekt hivatkozással akarom előhozni?
Szervusz,
Boldog Új Évet!
Kérdésem lenne az alábbival kapcsolatban
Ha a „tömb” másik lapon van akkor miként tudom megoldani a feladatot?
Infók:
Képlet -„adatelemzés” lap Fkeres(termék neve;tömb;2)
FKERES(D2;INDEX(P:P;HOL.VAN(A2;Partner;0)):INDEX(S:S;HOL.VAN(A2;Partner;1));2)
Tömb – „Sheet” lap – index(P:P;Sor_Elso):index(S:S;Sor_Utolso)
index(P:P;HOL.VAN(A2;Partner;0)):index(S:S;HOL.VAN(A2;Partner;1))
Sor első HOL.VAN(A2;Partner;0)
Sor utolsó HOL.VAN(A2;Partner;1)
Partner Sheet!$M$3:$M$5616
Előre is köszönöm a segítséget
Üdv
Yorak
Most így elsőre azt csinálnám, hogy minden hivatkozás elé beírnám a Sheet nevét.
A Tömb, a sor első és sor utolsó neveknél is.
Az oszlop hivatkozásokat én lefixálnám, mert így el tud mászni vízszintesen.
A Partner név viszont be van fixálva 5616-ra. Az nem baj?
Imre
Kedves Imi,
Köszönöm a választ.
Partnernév rendben van így, de jó gondolat, azt is dinamikusra alakítom.
Üdv
Yorak
Sziasztok,
Nálam sajnos valami miatt a Névkezelő, illetve abba írt – beágyazott függvényeket nem fogadja el a rendszer és nem tudom miért.
Pl: a fenti példánál maradva Nevek_Indirekt elnevezést – abba írt hivatkozást – nem hajtja végre.
Ha a függvényeket a cellába írom hol.van / indirekt egymástól függetlenül, akkor végrehajtja szépen.
[Nálam annyi az eltérés, hogy:
– külső adatállományból kérem le a táblázatot, (állományok között szinkronizáció működik)
– a legördülő lista más lapon van, mint a külső adatállományokból származó táblázat]
Esetleg valaki tud tippet adni, merre induljak el?
Köszönöm előre is!
Ü:
zboy