Listamenü – ismétlődések elkerülése

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Aki ismeri a cellában készíthető listamenü lehetőségeit, talán felkapja a fejét a cím láttán. Ugyanis beviteli szabályból egyelőre csak egyet lehet megadni az Excelben. Tehát vagy lista, vagy egyedi szabály az ismétlődések tiltására. A kettőt együtt hogy? Hát, egy kis trükközéssel elérhető, hogy a legördülő menüből eltűnjenek a már felhasznált tételek. Ha nem a menüt használjuk, hanem kézzel beírjuk, akkor elfogadja az ismétlődéseket, mert csodát még nem tudunk tenni. Mindenesetre érdemes megismerni az eljárást, mert rendkívül tanulságos. Az eredeti verziót itt találtam, de eléggé átdolgoztam, egyszerűsítettem rajta.

Demo

Azt kell látni, hogy ahogy haladok lefelé, egyre csökken a kiválasztható elemek száma, és mindig csak a még nem használt elemek vannak a menüben.

Hogy is van ez?

Nyilván a lista adatforrását kell valahogy manipulálni, és az eredeti listából eltüntetni a már használtakat. Első lépésként megállapítjuk, hogy mely tételek vannak már használatban. Ez egy egyszerű DARABTELI (COUNTIF) függvénnyel megtehető.  Egy új oszlopba a használtak helyett üreset, a nem használtak helyett a sorszámukat írjuk. Majd második lépésként egy következő oszlopba egy speciális képlettel átrendezzük  az eredeti listát, és felülre rendezzük a még nem használtakat, a használtak helyett pedig üreset írunk. Az új adatforrás a harmadik oszlop lesz.

Működés közben látható, hogy kezdetben nincs még kitöltött elem, így az első és a harmadik oszlop megegyezik. De ahogy töltöm ki a neveket, úgy változik a második és a harmadik oszlop, a még nem használtak adataival.

Na lássuk a képleteket!

E oszlop

Az E oszlop képlete megvizsgálja, hogy az aktuális listaelem a D oszlopban, használatban van-e a B oszlopban. ha igen, akkor üreset ír, ha még nem, akkor az aktuális sor számát. Ezzel megadva, hogy az elem hányas sorszámú. Ez utóbbit lehetne egy HOL.VAN (MATCH) függvénnyel is.

=HA(DARABTELI($B$2:$B$7;D1)>=1;"";SOR())

F oszlop

Ez a képlet azokat teszi felülre, amiknek az indexe ki van töltve, és az indexek szerint növekvő sorban. Ehhez a KICSI (SMALL) függvényt használjuk, ami megadja egy tartományban a valahanyádik legkisebb értéket.  A tartomány mindig az E oszlop, és a SOR() függvénnyel növeljük, hogy hányadik legnagyobb kell. Végül az E oszlop sorba rendezett indexeihez az INDEX függvényel lekérjük a hozzá tartozó nevet. Ha elfogynak a kitöltött elemek az E oszlopban, akkor a KICSI függvény hibát ad, de ezt lekezeljük a HAHIBA (IFERROR) függvénnyel.

=HAHIBA(INDEX($D$1:$D$6;KICSI(E$1:E$6;SOR())));"")

Szerintem zseniális!

Letölthető munkafüzet

A munkafüzetben két megoldás is van, az adatforrások külön munkalapon vannak, így a képletek is ennek megfelelőek. Végül is ez az életszerűbb. A második megoldás táblázatos hivatkozásokat tartalmaz.

Listamenu_hasznaltak_elrejtese

És a Video néhány extrával