Excel-elmélkedőknek és időmilliomosoknak: a RANDBETWEEN duplikációk eliminálása

2016. március 30. - Office Guru

Mindenféle fontos és kevésbé fontos elfoglaltságok okán sajnos az elmúlt napokban kissé háttérbe szorult a blog, de semmiképpen sem végleg, úgyhogy aki esetleg egy-egy felvetett problémája megoldására vár, az se csüggedjen, ismét újraindult a mókuskerék. A mai posztban egy olyan olvasói kérdést fogok körbejárni, amely egyaránt felmerülhet magánéleti kérdések kapcsán, de bármilyen vállalati környezetben is - és természetesen teljesül rá az Excel-problémák legnagyobb részében kimondható tézis: biztos, hogy van még legalább egy másik megoldás is.

A probléma teljes egészében az alapvetően elég egyszerű formula, a RANDBETWEEN körül forog, amelyről közismert, hogy az első és második paramétereként megadott (elsőként az alsó határt, másodikként a felsőt kell megadnunk) számok között fog véletlenszerűen számokat kisorsolni nekünk.

Önmagában tehát ez a kis függvény tökéletesen használható (lenne - egy kis csavarral) véletlenszerű mintaválasztástól kezdve zeneszámok sorrendjének összeállításán keresztül akár lottószámok sorsolására is, azzal a sajnálatos kitétellel, hogy a függvény minden szívfájdalom nélkül duplikációkat is hozni fog nekünk - a tartomány méretétől függően kisebb-nagyobb valószínűséggel.

st01.jpgEnnek a problémának a legegyszerűbb és leggyakrabban használt kiküszöbölési módja a RAND függvény bevetése és egy segédoszlop alkalmazása, hiszen a RAND 0 és 1 között fog nekünk egy véletlen számot generálni, amelynél a duplikáció kialakulása szinte lehetetlen. Ha valakit érdekel ez a megoldási forma, az jelezze és akkor kitérek rá, de most inkább egy olyan irányt vázolok fel, amely lehetővé teszi, hogy egyetlen tömbfüggvény segítségével, segédoszlopok nélkül eliminálhassuk a duplikációk lehetőségét RANDBETWEEN esetén.

Folytassuk a fentebb elkezdett lottószám sorsolási példánkat, ahol tökéletesen láthatjuk, hogy az első szám kisorsolására milyen módon használtuk a RANDBETWEEN-t, de ha ezt szimplán lehúznánk, az nem garantálná, hogy nem lesz azonosság.

Ennek kiküszöbölésére a második szám kisorsolásánál már az egyszerű formula helyett egy tömbfüggvényt fogunk használni, amely egy LARGE függvényben fog felépülni. A LARGE függvény az első paramétereként megadott tömbben adja meg a második paramétereként megadott sorszámnak megfelelő nagyságú számot, azaz például

=LARGE(A1:A5,2)

megadja az A1:A5 tömb 2. legnagyobb elemét.

Ha ezt legképezzük mostani problémánkra, akkor az első kérdés az, hogy mi is a tömbünk? A tömbünknek egy olyan tartományt kell most leképeznünk, amely tartalmazza elvileg az összes számunkat 1 és 90 között, kivéve azt, amelyiket már kisorsoltuk az első számként (vagyis tartalmazza azt is, csak valahogy jelezzük a függvény számára, hogy az már nem jelölt). Ezt úgy fogjuk elérni, hogy bevetjük a ROW függvényt, ami megadja nekünk a paramétereként megadott cella sorszámát, tömb esetén értelemszerűen a tömbben szereplő cellák sorszámát. Ezen logika mentén tehát

=ROW($1:$90) egy 90 sorszámból álló tömböt fog nekünk létrehozni referenciaként.

Na most nézzük meg, hogy ebben a tömbben előfordul-e a korábban kisorsolt számunk-számaink közül valamelyik. A célra igencsak jó barátunkat, a COUNTIF formulát fogjuk felhasználni, ami az első paramétereként megadott tartományban megnézi, hogy a második paraméternek megfelelő értékek előfordulnak-e. A mi esetünkben tehát

=COUNTIF($F$2:F2,ROW($1:$90))

nem fog mást tenni, mint létrehoz egy egytől kilencvenig terjedő tömböt, amelyben minden értéknél 0-t fogunk látni, kivéve azt, amelyik az F2 cellában szerepel, ott ugyanis egy egyesünk lesz, hiszen teljesül a feltétel. Annak érdekében azonban, hogy eredeti 90-es tömbünket a továbbiakban megfelelően fel tudjuk használni, ezeket a nullásokat és egyeseket FALSE és TRUE értékekre kell konvertálnunk, méghozzá úgy, hogy a nulláink TRUE-ként jelenjenek meg, az egyeseink pedig FALSE-ként, hiszen így érjük el, hogy legyen egy 90-es tömbünk, amelyben egytől kilencvenig látjuk az összes fel nem használt számot és nullaként mutatjuk majd ki a felhasználtakat.

A NOT függvény pontosan ezt teszi, szimplán kiadja majd megfordítja a TRUE és FALSE értékeket, tehát

=NOT(COUNTIF($F$2:F2,ROW($1:$90)))

formula egy olyan kilencvenes tömböt fog nekünk létrehozni, amelyben 89 TRUE és egyetlen FALSE érték lesz, méghozzá pont azon a sorszámon, ahol az F2-ben szereplő számunk szerepel.

Arról pedig már korábban a SUMPRODUCT függvény kapcsán volt szó, hogy például a * karaktert mi mindenre használhatjuk formuláinkban, mi jelen esetünkben egy olyan tömböt fogunk létrehozni a segítségével, amely egytől kilencvenig tartalmazza azon számainkat, amelyeket a RANDBETWEEN még nem sorsolt ki és nullával (FALSE) fogja azon sorszámokat megjeleníteni a tömbben, amelyeket már kiválasztottunk.

=ROW($1:$90)*NOT(COUNTIF($F$2:F2,ROW($1:$90)))

Értelemszerűen végig CTRL+SHIFT+ENTER kombinációval operálunk, hiszen itt tömbökről és tömbfüggvényekről beszélünk. Szóval a fenti függvénnyel elkészült LARGE függvényünk első, tömb paramétere.

A LARGE második paramétere pedig a nagyság sorrendjét határozza meg, ahogy fentebb már volt róla szó, tehát most fogjuk a létrehozott és a már kiválasztott számok sorszámán nullát szerepeltető tömbünkből kiválasztani a következő számot, ami semmiképpen nem lesz majd duplikáció.

Egyszerűen nem kell mást tennünk, mint beágyazni ide egy RANDBETWEENT, amelynek alsó paramétere értelemszerűen az egyes lesz, de a felsőt már az alapján fogjuk meghatározni, hogy eddig hány számot sorsoltunk ki, hiszen az eredetileg kilencvenes tömbünkben először csak egy, később már több nulla is lesz, azaz akkor már nem 90 lehetséges számról, hanem 89-ről, 88-ról és így tovább, beszélünk.

=RANDBETWEEN(1,(90+1)-ROW(F2))

Szóval az alsó érték az egyes, a felső pedig nem más, mint a maximális felső határunk, hozzáadva egyet a fejléc miatt és lecsökkentve az aktuális sorszám értékével, tehát F3 cellában

90 + 1 (a fejléc miatt) - 2 (ez F2 sorszáma) = 89 (és tényleg eddig egyetlen számot sorsoltunk ki)

F4-ben

90 + 1 - 3 = 88

Ezzel el is értünk a végére, így nézünk ki elvileg:

={LARGE(ROW($1:$90)*NOT(COUNTIF($F$2:F2,ROW($1:$90))),RANDBETWEEN(1,(90+1)-ROW(F2)))}

Ne felejtsük továbbra sem, hogy ez egy tömbfüggvény, tehát CTRL + SHIFT + ENTER-t kell nyomnunk és utána húzhatjuk majd csak le.

st02.jpg

A bejegyzés trackback címe:

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

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.

Ketzifintshy 2016.04.01. 23:35:08

Nem tudom, hogy a szám generálása a windowsban vagy az excelben gyökerezik, de az exceles véletlenszámok "véletlenebbek" a valóságban véletlenül előforduló sorozatoknál, azaz: a valóságban sokkal több ismétlődés történhet, mint az excel véletlenszámgenerátoránál. Aki természetes véletlenszámokat (kiváltképp, ha nagy mennyiségben van rá szüksége úgy, hogy a sorrend számít)szeretne, használjon linuxot. Ott az ismétlődés valószínüsége természetes mintázatot ad.

Atzs · https://adatrendezo.hu 2016.04.03. 10:17:57

@Ketzifintshy: Nem a Windows a "hibás", hanem a valószínűségszámítás.

A véletlen.között függvény egyenletes eloszlású véletlenszámokat állít elő. A nagy számok Bernoulli-féle törvénye alapján azt várjuk, hogy ha elegendően sok véletlenszámot generálunk 1 és n között, akkor az egyes értékek relatív gyakorisága jellemzően 1/n körül ingadozik. Minél több számot állítunk elő, annál több tizedesjegy pontossággal megegyezik általában 1/n-nel. Lefuttattam most néhány tesztet, és az én elvárásaim szerint még több is az ismétlődés, mint kellene, ráadásul követ egyfajta mintázatot. Ritkábban előforduló értékek szomszédai is ritkábban fordulnak elő. (Véletlenszámok 1 és 100 között, egymilliós mintaelemszám mellett.)

Amire szerintem gondolsz, hogy a természetben nagyon sok megfigyelhető folyamat normális eloszlású valószínűségi változóval jellemezhető. Ezeknél az átlag körüli érték sokkal gyakoribb, az átlagtól 2 szórásnyi távolságon túl már az értékek kevesebb mint huszada lelhető fel, 2 szórásnyi távolságon túl meg már szinte semmi. Ezeknek a modellezéséhez természetesen nem jó az egyenletes eloszlású véletlenszám-generátor. Ebben az esetben használható az Adatok/Adatelemzés/Véletlenszám-generálás funkció, amely az egyenletes eloszláson kívül tud normális, binomiális és Poisson eloszlást is.
Igaz, itt legfeljebb 32767 véletlenszám (találós kérdés: vajon miért épp ennyi?) generálható, és eléggé megfekteti a gépet. Nem győztem kivárni, hogy ellenőrizzem, egyenletesebb-e, mint a véletlen.között.

Lottószelvény kitöltéséhez maradjunk az egyenletes eloszlású véletlenszámoknál.

Jozsef · http://road2boston.blog.hu 2016.04.04. 15:54:44

Ismétlődés nélküli véletlen sorrendre egy pofonegyszerű módszer:
1. A1:B1-be két oszlop fejléc: Lista, Sorrend
2. A2-től lefelé felsoroljuk a lista elemeket pl. lottó számoknál 1-90 számok, de lehetnek pl. nevek bármi,
3. B2:=VÉL() (angolul RAND() függvény), amit lehúzunk az utolsó listaelem soráig
4. Rendezzük az így kapott táblázatot a Sorrend (B) oszlop szerint

Ha teljes véletlen ismétlődés nélküli permutáció kell, akkor a vesszük az A2-lefelé az összes értékeket, ha ezek közül csak pár darab (pl. lottó esetében 5), akkor pl. az első k darabot.