Folytassuk a dinamikus listák készítését - minél kevesebb extra mozdulat, annál jobb

VBA-ismeretek nélkül sem kell teljesen manuális munkát végeznünk

2015. október 06. - Office Guru

Nemrégiben szembesültem egy érdekes problémával Excelben a mindennapok során, amelyre lehet, hogy többeknek már régóta ott volt a megoldás a tarsolyában, de okulásként és hát azért, hogy minden nap tanuljunk valami újat, megosztanám azt a megoldást, amire én jutottam - aztán ha valakinek van jobb, más ötlete, jelezze bátran!

Valószínűleg Data Validation listát és a hozzá tartozó legördülő menüt bárki tud készíteni Excelben, ez egy nagyon hasznos és szép funkció, ahogy látható is ezen a képen: 

160.jpgKönnyedén el tudjuk készíteni a Ribbonunk Data fülének Data Tools szekciójában található Data Validation funkció segítségével, aztán a listából kiválasztott/validált értékre építhetünk a későbbiekben függvényeket, más műveleteket is, tehát VBA ismeretek nélkül is tudunk egy kis automatizmust vinni táblázatainkba - ezzel szerintem senkinek semmi gondja.

Na de mi van akkor, ha menet közben bővül a listánk és meg szeretnénk takarítani azt az időt, amelyet a validációnk bővítése jelentene? Persze, lehet erre azt mondani, hogy jelöljük ki validációra a full oszlopot vagy tartományt, ami megoldás lehet (és mennyire straightforward!), de azért adjuk meg magunknak azt a bizalmat, hogy ennél többre is képesek vagyunk.

Ugyanis erre (és sokminden másra) a dinamikus frissítésre tökéletesen használható az Insert menü Tables szekciójában található Table funkció, úgyhogy szépen jelöljük is ki autóink listáját és szúrjunk be rá egy táblázatot:

161.jpgEzután a Formulas Ribbonfül Defined Names szekciójában található, roppantul hasznos és használatra igen ajánlott Name Manager funkcióhoz forduljunk és hozzunk létre egy új, általunk választott névre hallgató tartományt, amely az előbb létrehozott táblánkra mutat, valahogy így:

162.jpgInnentől pedig adja magát mit is kell tennünk, szépen menjünk vissza a Data fülre és a Data Validation alatt módosítsuk úgy listánkat, hogy ne konkrét cellatartományt adjunk meg, hanem az előbb elnevezett tartományunkat:

163.jpgÉs ezzel kész is, szépen automatikusan bővülni is fog a lista validációnk, ahogy bővítjük magát a listát - és ezt a logikát, ötletet tucatnyi, hasonló kérdéskörben lehet hasznosítani.

164.jpgA poszt végére kicsit más, afféle extra, mostanában kaptam a kérdést egy szűrési problémával kapcsolatban, amelyre a választ az Excel korlátai jelentik: azaz 10 ezernél több értéket tartalmazó listánkba szűrőt beszúrva ne lepődjünk meg, ha a filter legördülő listájában néhány értéket már nem látunk viszont, ugyanis a filter maximális legördülő listás korlátozása 10 ezer, afeletti értékszám esetén már nem kapunk teljes képet a drilldownban.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr557914732

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Gukker 2015.10.08. 11:31:11

Kisebb mennyiségű listaelemnél felesleges segédoszlopokkal vacakolni. Simán be lehet írni kézzel a listaelemeket a forrás mezőbe, pontosvesszővel elválasztva.

Lénárd Gábor 2015.10.09. 14:56:01

Nagyon jó a blog, már sok ötletet merítettem belőle. Ehhez a témához van egy kérdésem: szükségem lenne egy "többlépcsős" (kategória - alkategória - szubkategória) validált listára, de nem jövök rá a megoldásra. A fenti autós példánál maradva: kategória lenne az autó gyártmánya ("Volkswagen", "Audi", "Aston Martin", stb.), alkategória lenne a típus ("Golf", "A4", "Vanquish", stb.), és szubkategória mondjuk a színek fantázianevei - hogy ne legyen átfedés az értékek között ("Pacific blue", "Metal yellow", stb.). A lényeg, hogy 3 legördülő lenne, de ha az elsőben kiválasztottam az Audit, akkor már csak az Audi típusokat dobja fel a második (alkategória) lista, a harmadik pedig csak az adott típus színválasztékát (szubkategória lista). Tudtok erre megoldást?

Atzs · https://adatrendezo.hu 2015.10.09. 23:00:46

Nem tökéletes megoldás, mert nem állítja be automatikusan azt, hogy melyik legördülőben hány elemet láss. Úgy kell őket méretezni, hogy a leghosszabb lista is elférjen bennük.

Van egy munkalapod, legjobb, ha rejtett. Ennek első oszlopában vannak a gyártmányok. A másodikban a típusok, a harmadikban a színek. Ezek a validációs listák.

Az első oszlopban a gyártmányok fix szövegként vannak benne az első sortól kezdve. A második oszlop képletezve van, oda annak a gyártmánynak a típusai kerülnek majd be, amit az előző listából kiválasztottál. Ehhez segítség: Legyen például négy gyártmányod, ezeket beírod az I5:L5 cellákba. Minden gyártmánynév alatt felsorolod a hozzá tartozó típusneveket. A B1-es cellába pedig ez a képlet kerül, ha az E5-ben van a kiválasztott gyártmány neve: =HA(INDEX(I5:L5;HOL.VAN(E$5;A$1:A$4;0))="";"";INDEX(I5:L5;HOL.VAN(E$5;A$1:A$4;0)))
Ezt másolod lefelé. Így a második oszlopban mindig a kiválasztott gyártmányhoz tartozó típusok jelennek meg. Erre ülteted rá a második legördülőt.

Készítesz még egy táblázatot, annak az első sorában a típusok vannak, alattuk az elérhető színek. Hasonló képlettel bevarázsolod a harmadik oszlopba a megfelelő színeket, és erre ráülteted a harmadik legördülőt.

Maga a képlet lehetne ennyi is: INDEX(I5:L5;HOL.VAN(E$5;A$1:A$4;0)) Ezzel az volt a bajom, hogy ha egy gyártmányhoz kevesebb típus van, mint ahány sorra lemásolom a képletet, akkor nullákat látok. Ezeket tüntettem el a A függvény használatával. Megoldható e helyett az is, hogy a munkalapon letiltom a nullák megjelenítését.

Lénárd Gábor 2015.10.10. 01:56:26

@Atzs: Nagyon köszönöm a megoldási javaslatot, hamarosan kipróbálom és visszajelzek, sikerült-e.

Office Guru 2015.10.10. 13:13:15

@Lénárd Gábor: Egy rövid posztban bedobtam én is egy javaslatot, szintén segédtáblás megoldás.

Lénárd Gábor 2015.10.10. 20:10:32

@Office Guru: Köszi, nem is gondoltam, hogy új poszt születik belőle :) mindkét megoldást megnézem. Köszi még egyszer a segítséget.