Istumapaikkanumeroiden arpominen Excelillä

[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.

Excel-tiedosto, lataa täältä.
https://mikonfysiikka.files.wordpress.com/2023/03/yo-istumapaikka.xlsx

6 helmikuun, 20227 helmikuun, 2022

Kertotaulu tai yhteenlaskulaskutaulu taulukkolaskennalla

Joskus tarvitsen yhteenlaskutaulukkoa, vaikkapa silloin, kun opetan kahden nopan summaan liittyviä todennäköisyyslaskennan laskuja. Esitän tässä, miten sellaisen luonti onnistuu suhteellisen helposti taulukkolaskentaohjelmissa.

Tehdään esimerkin vuoksi kahden nopan heiton summataulukko. Käytän tässä GeoGebra 5:n taulukkolaskentoa. Sama ohje toimii Excelissä, LibreOfficessa ja Google Sheetsissä.

yhteenlaskutaulukko

Laitetaan ensimmäisen nopan arvot Soluihin B1, …, G1 ja toisen A2, …, A7.

Syötetään soluun BC luku 1 ja C1 luku 2. Valitaan alue B1:C1 ja vedetään kahvasta (sininen pikkuneliö alueen oikeassa alakulmassa) oikealle soluun G1 saakka.

Kuva, joka sisältää kohteen pöytä

Kuvaus luotu automaattisesti

Luodaan samalla menetelmällä toisen nopan tulokset.

Varsinaista summataulukkoa varten tarvitsee luoda kaava, joka kopioituu oikein, kun kaavaa monistetaan oikealle tai alaspäin. Viittaukset liittyen rivi- ja saraketunnuksiin saa kiinnitettyä absoluuttisiksi käyttämällä dollari-merkkiä $.

Kirjoitetaan soluun B2 kaava =B$1 + $A2. Kertolaskutaulussa plussan tilalla olisi ollut kertomerkki. GeoGebrassa ei tarvitsisi laittaa taulukkolaskennan kaavan alkuun =-merkkiä, ei siitä haittaakaan ole. Muissa taulukkolaskentaohjelmissa sitä tarvitaan.

Valitaan solu B2 ja monistetaan kaava oikealle soluun G2 saakka.

Alue B2:G2 valittuna monistetaan rivin pätkä alaspäin kahvan avulla soluun G7 saakka.

Kuva, joka sisältää kohteen teksti, elektroniikka, näyttökuva

Kuvaus luotu automaattisesti

Samalla tavalla saa luotua kertolasku – tai jakolaskutaulukoita.

jakolaskutaulukko GeoGebralla 3D-pisteiksi

Tehdään ihan huvin vuoksi jakolaskutaulukko siten, että laskettuun soluun tulee 3D-pisteen koordinaatit (x, y, x/y).

Solussa B10 on kaava =(B$9, $A10, B$9 / $A10)

Rivillä 15 olevat kysymysmerkit viittavat nollalla jakamiseen.

Tässä vaiheessa voisi kuvitella, että nuo pisteet näkyisivät 3D-alueella. Jostain kummasta syystä ne eivät näy siellä. Pisteet saa näkyville, kun kopioi pisteet soluviitteinä uuteen taulukon alueeseen.

Kirjoitetaan soluun B22 soluviite eli kaava = B10. Monistetaan soluviitettä oikealle ja sitten alue B22:L22 alaspäin riittävän pitkälle.

Kuva pisteistä koordinaatistossa on vähän tylsä.

Laitetaan se pyörimään.

lähteitä

Excel ohje liittyen soluviittauksiin
https://support.microsoft.com/fi-fi/office/suhteellisten-suorien-ja-sekaviittausten-v%C3%A4lill%C3%A4-vaihtaminen-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

Malinin ohje GeoGebran taulukkolaskentaan http://www.malinc.se/math/geogebra/spreadsheeten.php

23 tammikuun, 202123 tammikuun, 2021

Yhtälöryhmän ratkaiseminen taulukkolaskennolla ja GeoGebralla

Törmäsin vanhaan tiedostooni vuodelta 2004, siinä oli ratkaistu hankalan vastussysteemin resistanssi käyttäen taulukkolaskentaa. Tiedosto oli alun perin tehty Excelissä ja nytpä se ei enää toiminut oikein. Arvelen, että alun perin olin tehnyt tämän viime vuosituhannella. Päätinpä tehdä yhtälöryhmän ratkaisun uudelleen käyttäen matriisifunktioita.

Esitän menetelmän Google Sheetsissä, menetelmä toimii samalla tavoin Excelissä ja LibreOfficen Calcissa. Lopuksi teen saman GeoGebralla matriisikomennoilla.

resistanssiongelma

Ongelma on oheisessa kuvassa. Pitää ratkaista kyseisen vastussysteemin resistanssi pisteiden A ja B välillä. Muistaakseni ongelma oli alkuperäisessä muodossa sellainen, että kaikkien vastusten resistanssi oli 1 Ω. Sen ratkaiseminen onnistunee lahjakkaalta fyysikolta pelkästään katsomalla tuota kytkentäkaaviota :o)

ratkaisu Kirchoffin laeilla

Itse en ainakaan keksi mitään simppeliä kaavaa resistanssin laskemiseksi. Tehdään Kirchoffin lakien avulla yhtälöt eri osissa kulkevista virroista, ratkaistaan niiden suuruudet ja käytetään sitä tietoa kokonaisresistanssin laskemiseen.

Merkitään 1 Ω:n vastuksen läpi kulkeva virtaa I₁:llä, 2 Ω:n läpi kulkeva virtaa I₂:lla, jne. Virtojen suunta on vasemmalta oikealle. Kuvitellaan, että pisteiden A ja B välillä jännite U = 10 V:n. Näin saadaan Kirchoffin lakien avulla helposti viisi yhtälöä

Kuva, joka sisältää kohteen teksti

Kuvaus luotu automaattisesti

Kokonaisresistanssi R tulee olemaan

Kuva, joka sisältää kohteen teksti, kello

Kuvaus luotu automaattisesti

ratkaisu Google Sheetsillä

Laitan Google Sheetsiin resistanssit soluihin B6:B10 ja jännitteen soluuun B12.

Yhtälöryhmän kertoimet ovat soluissa A17:F21. Laitan ne soluihin viittauksena R:n ja U:n arvoihin, eli solussa A17 on =-B6, solussa C17 on =B8 jne.

Näytä-valikon Näytä kaavat-komennolla kaavat saa näkyviin ja tietysti takaisin näkyville kuten näissä kuvissa.

Soluun A25 lasketaan kerroinmatriisin determinantti komennolla

=MDETERM(A17:E21)

Mikäli determinantti on nollasta poikkeava, niin yhtälöryhmällä on ratkaisu.

Ratkaistaan virtojen arvot soluihin A28:E28. Kirjoitetaan soluun A28 komento

=TRANSPOSE(MMULT(MINVERSE(A17:E21);F17:F21))

Suomenkielisessä Excelissä ja LibreOfficessa tuo rivi pitäisi kirjoittaa muodossa

=TRANSPONOI(MKERRO(MKÄÄNTEINEN(A17:E21);F17:F21))

Kaavassa lasketaan kerroinmatriisin käänteismatriisin ja vakiotermivektorin tulo ja käännetään se vaakasuuntaan.

Kokonaisresistanssi lasketaan soluihin A31 ja A32

Tätä kirjoittaessa löysin vanhemman version alkuperäisestä Excel-tiedostosta, se toimi moitteettomasti. Siinä solun A28 ratkaisu oli tehty LINREGR-funktiolla

=LINREGR(F17:F21;A17:E21;EPÄTOSI;EPÄTOSI)

GeoGebran CAS 6×6 yhtälöryhmä

GeoGebran CAS:issa ongelma olisi ratkennut vaikkapa seuraavasti.

Solun 7 ratkaisu kertoo, että kokonaisresistanssi R on riippumaton U:sta niin kuin pitääkin.

Solussa 8 on sijoitus

Lasketaan samalla kuinka suuri kokonaisresistanssi olisi, jos kaikkien vastusten resistanssi olisi 1.

Nyt kun tietää ratkaisun, niin saattaa olla helpompaa nähdä miksi näin käy tuijottamalla pelkästään kytkentäkaaviota.

GeoGebran matriisikomennot

Edellistä CAS-versiota tehdessä oli aika työlästä kirjoittaa yhtälöitä muuttujineen, mitäpä jos halutaan tehdä GeoGebralla saman tapainen matriisityylinen ratkaisu kuin Google Sheetsissä.

Kopioidaan Sheetsratkaisusta alkuperäinen matriisi ja sijoitetaan se GeoGebran taulukkolaskentaan alueelle A2:F6. Valitaan alue A2:E2 eli ensimmäisen yhtälön kertoimet ja hiiren oikealla painikkeella valitaan Luo → Lista.

Luodaan samalla tavalla muiden yhtälöiden kertoimista listat l2, l3, l4 ja l5 sekä vakiotermeistä lista l6.

Kuva, joka sisältää kohteen nuoli

Kuvaus luotu automaattisesti

Muodostetaan kerroinlistoista matriisi kirjoittamalla CAS:iin

M:={l1, l2, l3, l4, l5}

Dererminantin arvoksi saadaan 157 komennolla

Determinantti(M)

Annetaan yhtälöryhmän ratkaisun nimeksi Ra ja ratkaistaan se näppäilemällä

Ra:=M^-1*l6

Kokonaisresistanssi saadaan käyttämällä vakiotermilistan viidettä ja ratkaisulistan Ra ensimmäistä ja kolmatta jäsentä.

R=l6(5)/(Ra(1)+Ra(3))

linkit

Google Sheets-tiedosto https://docs.google.com/spreadsheets/d/1aW1ZqA-rO2MnbvGXrVf9V8fZVz_58nVNwzjC-jEqWko/edit?usp=sharing

GeoGebra-tiedosto yhtälöryhmänä https://www.geogebra.org/m/zhjndgt7

Vaikuttaa siltä, että alla olevat ei avaudu geogebra.orgissa ja Classic 6:lla. Jos haluat tiedoston omalle koneellesi, niin klikkaa tuota linkkiä ja oikeasta yläkulmasta kolmen pisteetn takaa Tietoa ja Lataa koneellesi

GeoGebra-tiedosto matriisikomentoratkaisuun https://www.geogebra.org/m/jqgmknb7