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 I1:llä, 2 Ω:n läpi kulkeva virtaa I2: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.

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

Kuvaus luotu automaattisesti

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

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

Kuvaus luotu automaattisesti

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.

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

Kuvaus luotu automaattisesti

Kokonaisresistanssi lasketaan soluihin A31 ja A32

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

Kuvaus luotu automaattisesti

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.

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

Kuvaus luotu automaattisesti

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

Solussa 8 on sijoitus

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

Kuvaus luotu automaattisesti

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.

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

Kuvaus luotu automaattisesti

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))
Kuva, joka sisältää kohteen pöytä

Kuvaus luotu automaattisesti

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

Korrelaatiomatriisi LibreOfficella YTL:n datasta

[edit 20.1.20 Lisäsin yo-koematriisikuvan oheen taulukon aineiden lyhennyksistä luettavuuden parantamiseksi.]

GeoGebran Kahden muuttujan analyysi -työkalulla on helppoa tuottaa kahden muuttujan tilastosta korrelaatiokaavio, samalla saa useiden tilastollisten tunnuslukujen arvot, kuten esimerkiksi korrelaatiokertoimen. Valitettavasti GeoGebrassa ei ole mahdollisuutta tuottaa korrelaatiomatriisia. Se kannattaakin tuottaa Abittijärjestelmässä LibreOfficen Calcilla. Tietysti saman voi tehdä myös Excelillä.

pieni esimerkki

Kuvan LibreOffice Calcin taulukossa on pienen opetusryhmän oppilaiden pituus senttimetreinä, kengännumero ja syntymäkuukausi. Tiedosto löytyi koneeni uumenista, joten se voi olla myös jonkin oppikirjan dataa tai sitten ihan oikea kysely omilta oppilailtani. 

Valitaan alue A1:C12, toki otsikot voi jättää poiskin valinnasta. Valitaan Data-valikosta Tilastotiedot -> Korrelaatio… Avautuvaan ikkunaan laitetaan tulosmatriisin vasen ylänurkan paikka ja valitaan OK.

Alkuperäisen taulukon sarakkeet eivät tulostu korrelaatiomatriisiin.

Valitaan alkuperäiset otsikot alueelta A1:C1, kopioidaan ja sijoitetaan ne soluun F1. LibreOffice varoittaa sijoituksesta, ei välitetä siitä. Pystyotsikot saadaan paikoilleen klikkaamalla soluun E2 hiiren oikealla painikkeella. Avautuvassa valikossa valitaan Liitä määräten -> Liitä Määräten. Avautuvassa ikkunassa laitetaan ruksi kohtaan Transponoi.

iso esimerkki ytl:n datasta

Kirjoitin jokin aika sitten Pivot taulukko-komennosta ”Ylioppilasdatan louhimista Pivot-taulukoilla” ja ”Pivot ja Excel YTL:n datasta”. Näissä käytin csv-tiedostoa, jossa on jokaisen keväällä 19 kirjoittaneiden opiskelijoiden ylioppilaskoetulokset. Käytän tässä tässä samaa tiedostoa https://www.ylioppilastutkinto.fi/ext/data/FT2019KD3001.csv

Oheinen linkki lataa tiedoston tietokoneen Lataukset-kansioon. Kun tiedosto avataan LibreOfficella, niin isohkon tietomäärän saa näkyville. Tiedostossa on noin 26000 riviä ja sarakkeita on 51 kappaletta. Tiedoston otsakerivin koodit selkokielisenä löytyvät sivulta https://www.ylioppilastutkinto.fi/ext/data/FT2016KD0010.csv.

Poistin kirjoitetuista aineista sellaiset, joissa oli vähän kirjoittajia < 100, näin sain korrelaatiomatriisin hieman pienemmäksi. Edellä esitetyllä tavalla loin korrelaatiomatriisin eri kirjoitettujen aineiden välille. Väritin ne solut, joissa korrelaatiokerroin r > 0,8 (voimakas) punaiseksi, 0,6 < r ≤ 0,8 (huomattava) keltaiseksi ja r < -0,6 vihreäksi. 

Minua itseäni ihmetyttää eniten se, että äidinkielellä ei ole tuon suurempia korrelaatiokertoimia muiden aineiden kanssa. Sitä vastoin biologialla ja maantieteellä on huomattava tai kohtalainen korrelaatio yhdeksän kirjoitettavan aineen kanssa. Toisaalta pitkällä matematiikalla kohtalainen korrelaatio on vain fysiikan ja kemian kanssa.

Alla kirjoitettavien aineiden tulkinta.

Jätän lukijan ihmettelemään yo-korrelaatiomatriisia ja tekemään syistä ja seurauksista omia tulkintojaan.

Ympyrädiagrammi LibreOfficella

Ohessa on LibreOffice taulukko. Siinä on GoogleFormsilla kerätystä datasta kopioitu erään oppilasryhmän oppilaiden silmien väri ja sukupuoli. GeoGebralla on hankala tuottaa ympyrädiagrammeja, niinpä tuotan sen LibreOfficella.

Lataa tiedosto https://drive.google.com/file/d/1-0Q7uiqu98JPCJ_jqJFK8tJ9Y6wyCIOQ/view?usp=sharing omalle koneellesi ja avaa se LibreOfficella.

Simppeli tapa

Tehdään diagrammi ensin mahdollisimman yksinkertaisesti, ilman kummempaa koodaamista.

Ensin pitää päätellä mitä luokkia silmien väri -muuttujassa on. Helpointa on vain katsoa taulukosta, että eri luokat ovat: sininen, ruskea, sinivihreä, vihreä sekä vihreä ja ruskea.

Kirjoitetaan luokat C-sarakkeen soluihin.

Toki nuo silmien värejä vastaavat lukumäärätkin saisi laskettua ihan käsinkin, mutta käytetään tässä LASKE.JOS-funktiota apuna.

Kirjoitetaan soluihin D2, …, D6 kaavat
=LASKE.JOS(A2:A28;”sininen”)

=LASKE.JOS(A2:A28;”vihreä ja ruskea”)

Diagrammi saadaan aikaiseksi valitsemalla alue C1:D6 ja tämän jälkeen Työkalupalkin Lisää kaavio tai Lisää-valikosta Kaavio.

Ohjattu kaavion luonti -ikkunassa valitaan Ympyrä ja sen jälkeen Seuraava.

Toisessa ja kolmannessa ikkunassa Seuraava.

Viimeisessä ikkunassa kirjoitetaan tarvittava tieto otsikoihin ja lopuksi Valmis.

Näin piirakka on valmis.

Monimutkaisempi menetelmä

Jos A-sarakkeen silmien värejä olisi ollut paljon, olisi luokkien päättely voinut olla hankalaa. Tehdään luokkien etsiminen ja frekvenssien laskeminen hieman monimutkaisemmin. Tällainen automatisointi toimii isommissakin taulukoissa.

Tuotetaan ensin silmien värit käyttämällä suodatusta eli filtteröintiä. Avaa alkuperäinen tiedosto silmä.ods. Valitse sarake A klikkaamalla sarakkeen tunnukseen ja Data-valikosta Oletussuodatin…

Oletussuodatin-ikkunassa valitse Arvo kohdassa Ei tyhjä ja klikkaa Asetukset-kolmioon. Klikkaa Alue sisältää sarakeotsikot, Karsi identtiset ja Kopioi tulokset kohteeseen: -ruutuihin ja valitse kohteeksi $Taulukko1.$C$1 (klikkaamalla taulukossa soluun C1).

Näin silmien värit suodattuvat C-sarakkeelle.

Frekvenssien laskemiseksi käytetään edellä esitettyä LASKE.JOS-funktiota, mutta käytetään apuna dollarimerkkiä kiinnittämään viittaukset soluihin siten, että voidaan käyttää apuna kahvaa kaavan monistamiseen. Tätä menetelmää kutsutaan yleensä soluviitteen lukitukseksi tai suoraksi eli absoluuttiseksi soluviittaukseksi.

Kirjoita soluun D2 kaava
=LASKE.JOS(A$2:A$28;C2)

Valitse solu D2 ja vedä kahvasta soluun D6 saakka. Näin dollarimerkki kiinnittää viittauksen riveille 2…28, samalla viittaus silmän väriin sarakkeella C muuttuu suhteellisesti, kun kaavaa kopioidaan kahvasta vetämällä.

Diagrammi syntyy samalla tavalla kuin edellä on esitetty.

Lisätään piirakkaan vielä prosenttiosuudet. Klikkaa ympyrädiagrammiin siten, että saat hiiren oikean painikkeen avulla valittua Lisää arvopisteiden otsikot.

Valitse samalla tavalla piirakka hiiren oikealla painikkeella ja Muotoile arvopisteiden otsikot…

Poista ruksi Näytä arvo lukuina -kohdassa ja lisää ruksi Näytä arvo prosenttiosuutena -ruutuun.

Nyt ympyrädiagrammi on valmis. Toki voit opiskella lisää miten muokata värejä tms.

Tee harjoituksen vuoksi ympyrädiagrammi sukupuolesta.