Kirjoitin pari päivää sitten artikkelin ”Istumapaikkanumeroiden arpominen Excelillä”. Päätin sitten luoda samankaltaisen sovelluksen myös Google Sheetsillä. Varsinkin kun Excel-versioni toimii vain uusilla 365 Exceleillä.
Alun perin aloitin sovelluksen teon Sheetsillä, mutta en saanut SORTN()-funktiota toimimaan haluamallani tavalla. Ongelma ratkesi helpommin Excelillä ja sen SORTBY() ja SEQUENCE() -funktioilla. Excel-versiossa koko homma onnistui yhdellä pitkähköllä komennolla, Google Sheetsissä pitää (ainakin tässä minun ratkaisussani) luoda pari ylimääräistä saraketta, jotka voi lopuksi piilottaa.
Sovellus toimii seuraavasti.
Laitetaan oppilaiden nimet soluun C2 ja siitä alaspäin. Sarakkeelle A luodaan satunnaislukuja saman verran kuin on oppilaiden nimiäkin solun A2 kaavalla =RANDARRAY(COUNTA(C2:C201))
Kun soluun D2 kirjoitetaan kaava =SORTN(A2:B200;COUNTA(C2:C200);A2:A200) ilmestyy sarakkeelle D sarakkeen A satunnaisluvut suuruusjärjestyksessä ja niiden avulla luotu satunnainen järjestysluku sarakkeelle E.
Kun sarakkeet A, B ja D piilotetaan, on sovellus valmis.
[edit. 14.3.23 lisäsin loppuun kommentin toimimattomuudesta vanhoissa Exceleissä]
Näihin aikoihin varmaankin aika monessa koulussa luodaan istumajärjestyslistoja ylioppilaskirjoituksia varten. Toisaalta opettajat saattavat tarvita opetuksessaan menetelmän, jolla aakkostettuun oppilaslistaan liitetään kullekin oppilaalle satunnainen järjestysluku. Niinpä tein sellaisen Excelillä.
Kauan sitten, kun vielä toimin opehommissa käytin tämän tyyppisen ongelman ratkaisuun GeoGebraa ja sen listakomentoja. Tai sitten sekoitin oppilaita taulukkolaskennan satunnaisluvuilla ja järjestä-valikkokomentojen avulla. Tämä versio perustuu Excelin SORTBY ja SEQUENCE -funktioihin. Aloitin tämän sovelluksen tekemisen Google Sheetsillä, mutta en saanut sitä toimimaan halutulla tavalla. Excelistä löysin sopivat funktiot ongelman ratkaisuun. Palannen joskus ongelman ratkaisuun Google Sheetsillä.
Taulukkoni ainoa kaava on solussa B2. Kaava on =SORTBY(SEQUENCE(COUNTA(A2:A200));RANDARRAY(COUNTA(A2:A200))) Suomeksi =LAJITTELE.ARVOJEN.PERUSTEELLA(JONO(LASKE.A(A2:A200));SATUNN.MATRIISI(LASKE.A(A2:A200)))
Tutkitaan miten kaavassa käytetyt funktiot toimivat.
SEQUENCE(n) funktio tuottaa lukujonon 1, 2, …, n alaspäin. Kun soluun A1 kirjoittaa SEQUENCE(n), niin alueelle A1:A10 ilmestyy lukujono 1, 2, …, 10.
SEQUENCE() on suomeksi JONO() Excelissä.
RANDARRAY(n) tuottaa n-kappaletta satunnaislukuja SEQUENCE-funktion tapaan. Kun soluun B1 kirjoitetaan =RANDARRAY(10), niin taulukkoon ilmestyy 10 satunnaislukua.
RANDARRAY on suomeksi SATUNN.MATRIISI.
SORTBY(alue1; alue2) järjestää solualueen alue1 solualueen alue2 mukaiseen järjestykseen.
=SORTBY(A1:A10;B1:B10)
SORTBY() on suomeksi LAJITTELE.ARVOJEN.PERUSTEELLA(), aika selkeä käännös :o)
COUNTA(alue) laskee solualueessa alue olevien ei tyhjien solujen lukumäärän. Kuvassa solussa D11 on kaava =COUNTA(D1:D10). Se tuottaa alueella olevien Excelin olioiden lukumäärän.
COUNTA() on suomeksi LASKE.A().
Näillä tiedoilla lopullisen kaavan tuottaminen onkin itsestäänselvää. Luodaan tyhjään taulukkoon 1 riville otsikot nimi ja paikka. Kirjoitetaan (tai sijoitetaan) nimi-otsikon alle nimiä, tässä versiossa niitä voi kirjoittaa sinne noin 200 kpl. Kun soluun B2 kirjoitetaan kaava =SORTBY(SEQUENCE(COUNTA(A2:A200));RANDARRAY(COUNTA(A2:A200))) niin sovellus on valmis.
Suomeksi kaava on =LAJITTELE.ARVOJEN.PERUSTEELLA(JONO(LASKE.A(A2:A200));SATUNN.MATRIISI(LASKE.A(A2:A200)))
Pitänee huomata, että nimissä ei saa olla tyhjiä soluja keskellä. Tällaisessa tilanteessa kaikki oppilaat eivät saa omaa arvoa.
Tämä sovellus toimii vain uusimmissa Excel-versioissa. Käsittääkseni SEQUENCE()-funktio toimii vain Excel for Microsoft 365 ja Excel 2021 -versioissa.
Edellisessä tarinassani käsittelin yksiulotteista satunnaiskävelyä sellaisessa tapauksessa, että lähdetään origosta ja arvotaan mennäänkö askel oikealle vai vasemmalle, askeleen pituus on vakio. Tutkitaan nyt tilannetta, jossa askeleen pituus on satunnainen.
Katso edellisestä tarinasta koodin kommentit, käytän tässä edellisen tarinan koodeja ja muokkaan niitä.
Tavoitteena on selvittää, millainen jakauma syntyy kulkijan kävelyn päätepisteistä, kun kävelyä toistetaan vaikkapa 200 kertaa.
loppupaikan tutkiminen
Piirretään Matplotlibin avulla satunnaista liikettä kuvaava kuvaaja muokkamalla edellisen tarinani Pythonkoodia. Rivillä 1 ladataan randomfunktio kirjastostaan. Rivillä 7 arvotaan satunnaisluku, ja tuotetaan arpa-niminen satunnaisluku, joka on välillä -1 < x < 1.
Viidenkymmenen askeleen jälkeen päädyttiin noin neljän yksikön päähän lähtöpisteestä.
histogrammit
Muokkaan edellisen tarinan vastaavaa koodia ja tuotan histogrammin lopullisen paikan jakaumasta.
Kuvassa 1000 satunnaiskävelyn loppupaikan jakaumahistogrammina. Jakauma näyttää samanlaiselta kuin edellisessäkin tarinassa, mutta varmaankin se on ”kapeampi” ainakin terveellä järjellä ajateltuna.
molemman tyyppiset satunnaiskävelyt
Tehdään koodinpätkä, jossa tuotetaan kahden tyyppistä satunaiskävelyä. Tyypissä 1 askeleen pituus on vakio -1 tai +1. Tyypissä 2 askeleen pituus on satunnaisluku eli askel on muotoa 2*random() – 1.
Kuvaaja näyttää päällisin puolin oikealta, mustat tolpat muodostavat kapemman jakauman. Silti kuvaaja on virheellinen, sillä tolppien pituuksien summa pitäisi olla yhtä suuri. Mustien tolppien pituus on viallinen. Tämä johtuu siitä, että hist-komento skaalaa vaaka-suunnassa arvot-listojen (loppulista1 ja loppulista2) suurmimman ja pienimmän arvon mukaan luokkiin (bins). Niinpä kuvassa mustien palkkien leveys on kapeampi kuin sinisten.
Eron saa näkymään selkeämmin, kun vähentää palkkien määrää. Seuraavan kuvaajan koodi on muuten sama, mutta riveillä 19 ja 20 on:
Kymmenellä palkilla näkee selvästi, että mustat palkit ovat kapeampia.
Mikäli skaalauksen haluaa toimivan oikein, niin pitää määrittää molemmille jakaumille samat reunat histogrammiin.
Seuraavassa koodissa riveillä 21-22 määritetään leveämmän jakauman loppulista1:n suurin ja pienin arvo. Rivillä 21 valitaan reunoista itseisarvoltaan suurempi, näin saadaan reunat määriteltyä symmetrisesti origon ympärille. Rivien 24 ja 26 range määrittää histogrammin tolppien vasemman ja oikean reunan. Lisäsin vielä mukaan selitteen (legend) riveillä 22 ja 33.
Jätän lukijan pohdiskelemaan millainen funktio kuvaa oheisia jakaumia, kun annetaan n:n kasvaa oikein suureksi.
Palaan aiheeseen lähiaikoina, silloin tutkin 2D ja 3D satunnaiskävelyä.
Mikon fysiikka ja matikka 