Telefonszámunk: 1-472-0679

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

2016-07-21 - horvimi - Megtekintések száma: 1,416 - Kategória: Általános tippek, Képletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016

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.

DEMO

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.

Uj_adatforras

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.

Demo2

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

3 Comments
  1. Szia Imi,
    ez tényleg nagyon ütős 🙂
    Találtam egy megoldást arra, hogy a kézzel beírt ismétléseket se fogadja el. A példában te az érvényesítési listát elnevezett tartományként ill. táblázatként hoztad létre. Ha a normál tartományon megszünteted az elnevezést, és egyszerűen csak $C$1:$C$6-ként hivatkozol rá, akkor működik.
    Na, ez viszont nem szép az Exceltől…
    Üdv
    András

  2. Szia Imi!

    Egy vezénylést segítő táblázatot szeretnék készíteni, hogy tudnám ezt hasznosítani. Van 8 emberem 5 feladat jut nekik egy munkanapra. A lényeg, hogy 1 feladat 1 nap csak 1 emberhez osztható. 1 napra már sikerült megcsinálnom, de nekem minden napra kellene?

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