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

Pivot ja Excel YTL:n datasta

Edellisessä tarinassani esitin miten YTL:n jakamaa oppilaskohtaista dataa voi käsitellä Google Sheetsissä. Tässä esitän saman MS Excelillä. Käytän tarinassani edellisen tarinan virkkeitä laiskuuksissani, joten älä ihmettele jos tarina tuntuu tutulta.

Ylioppilaslautakunnan sivulla Oppilaitoskohtaisia tunnuslukuja https://www.ylioppilastutkinto.fi/tietopalvelut/tilastot/koulukohtaisia-tunnuslukuja on ohjeet miten ladata kevään 19 ylioppilaskirjoitusten tulokset csv-tiedostona. Myös aiempien tutkintokertojen tiedostot ovat ladattavissa. Tiedostoja on kahta eri tyyppiä riippuen niiden päivitettävyydestä. D3000-sarjan tiedostot eivät päivity. D4000-sarjan tiedostot päivittyvät kun ylioppilastutkinnon tiedot täsmentyvät esimerkiksi tarkistusarvostelun takia.

Tehdään tämä versio eri tiedostosta kuin edellinen Google Sheets -versio. Sheets-versiossa tutkittiin pakollisten aineiden keskiarvoa. Tässä käytetään kaikkien aineiden dataa. Kevään 19 kaikkien aineiden aineiden (ei päivittyvät) tulokset löytyvät tiedostosta https://www.ylioppilastutkinto.fi/ext/data/FT2019KD3001.csv. Käytän tässä Excelin Mac-versiota koska olen omenaihminen. Testailen lähipäivinä onko Windows-versiossa niin paljon eroa, että sitä pitäisi kommentoida.

Kun klikkaat tuohon osoitteeseen, niin selaimesi lataa tiedoston lataukset-kansioosi. Käytän tätä tiedostoa mallina siitä, miten tietoa voi käsitellä Excelin Pivot-taulukkotoiminnolla.

Avaa Excelillä ladattu csv-tiedosto. Excel avaa tiedoston Ohjattu tiedoston luominen-ikkunaan.

Valitse seuraavassa ikkunassa Erottimeksi puolipiste ”:”. Jos kaikki meni oikein, niin Excel avaa suuren taulukon.

Tässä vaiheessa tiedostolle on syytä antaa nimi vaikka 19Kkaikki. Taulukon otsikkoriveinä on:

tutkintokerta koulun_nro   koulun_nimi opetuskieli    tyyppi           sukup            yht                A                   O                   Z                   I                      W                  Q                   A5                 O5                 M                  N                   BI                  FF                 FY                 HI                      PS                 UE                 UO                ET                 GE                KE                TE                 YH                BA                BB                      CA                CB                 EA                FA                 SA                 PA                VA                EC                 FC                 SC                      PC                 VC                DC                IC                  QC                GC                TC                 L1                 L7                      opintotausta

Ytl:n sivulla on ohje muuttujanimistä:

  • [koekoodi] – Arvosanapisteet; I=0, A=2, B=3, C=4, M=5, E=6, L=7
  • yht – Tutkinnon arvosanapisteet yhteensä
  • sukup – Sukupuoli 1=mies, 2=nainen
  • ka_pak – Pakollisten aineiden keskiarvo
  • ka – Oppilaitoksen keskiarvo kokelaiden kaikken kokeiden keskiarvosta
  • n – Vastaavasti kokeiden lukumäärä (oppilatoksen keskiarvo)
  • ka_pkr – Oppilaitoksen keskiarvo kokelaiden ”pitkien aineiden” keskiarvosta; mukana pitkä matematiikka, pitkät kielet, pitkä toinen kotimainen, äidinkieli ja kaikki reaalikokeet
  • n_pkr – Vastaavasti kokeiden lukumäärä (oppilaitoksen keskiarvo)
  • ylioppilas – niiden kokelaiden lukumäärä, jotka ovat kyseisellä tutkintokerralla saaneet hyväksytyn tutkintokokonaisuuden ja joilla lisäksi on julkaisuajankohtana lukion päättötodistus.
  • opintotausta:
    • 1 – Lukion opiskelija
    • 2 – Ammatillisten opintojen pohjalta tutkintoa suorittava kokelas
    • 3 – Lukion oppimäärää ja ammatillista tutkintoa suorittava kokelas
    • 4 – Muu opiskelija/tuntematon

Ytl:n sivulta löytyy linkki sivulle https://www.ylioppilastutkinto.fi/ext/data/FT2016KD0010.csv, sieltä löytyvät yo-kokeiden koodit.

koe                nimi              namn

A                   Äidinkieli, suomi                Modersmålet, finska

A5                 Suomi toisena kielenä        Finska som andraspråk

BA                Ruotsi, pitkä oppimäärä     Svenska, lång lärokurs

BB                Ruotsi, keskipitkä oppimäärä                  Svenska, medellång lärokurs

BI                  Biologia        Biologi

CA                Suomi, pitkä oppimäärä     Finska, lång lärokurs

CB                Suomi, keskipitkä oppimäärä                  Finska, medellång lärokurs

DC                Pohjoissaame, lyhyt oppimäärä               Nordsamiska, kort lärokurs

EA                Englanti, pitkä oppimäärä  Engelska, lång lärokurs

EC                 Englanti, lyhyt oppimäärä  Engelska, kort lärokurs

ET                 Elämänkatsomustieto         Livsåskådningskunskap

FA                 Ranska, pitkä oppimäärä    Franska, lång lärokurs

FC                 Ranska, lyhyt oppimäärä    Franska, kort lärokurs

FF                 Filosofia       Filosofi

FY                 Fysiikka        Fysik

GC                Portugali, lyhyt oppimäärä Portugisiska, kort lärokurs

GE                Maantiede     Geografi

HI                 Historia         Historia

I                    Äidinkieli, inarinsaame      Modersmålet, enaresamiska

IC                  Inarinsaame, lyhyt oppimäärä                  Enaresamiska, kort lärokurs

KE                Kemia           Kemi

L1                 Latina, lyhyt oppimäärä     Latin, kort lärokurs

L7                 Latina, pidempi oppimäärä Latin, längre lärokurs

M                  Matematiikka, pitkä oppimäärä               Matematik, lång lärokurs

N                   Matematiikka, lyhyt oppimäärä               Matematik, kort lärokurs

O                   Äidinkieli, ruotsi                Modersmålet, svenska

O5                 Ruotsi toisena kielenä        Svenska som andraspråk

PA                 Espanja, pitkä oppimäärä   Spanska, lång lärokurs

PC                 Espanja, lyhyt oppimäärä   Spanska, kort lärokurs

PS                 Psykologia    Psykologi

Q                   – ei käytössä –                     – ej i bruk –

QC                Koltansaame, lyhyt oppimäärä                Skoltsamiska, kort lärokurs

SA                 Saksa, pitkä oppimäärä      Tyska, lång lärokurs

SC                 Saksa, lyhyt oppimäärä      Tyska, kort lärokurs

TC                 Italia, lyhyt oppimäärä       Italienska, kort lärokurs

TE                 Terveystieto  Hälsokunskap

UE                Evankelis-luterilainen uskonto                Evangelisk-luthersk religion

UO                Ortodoksi uskonto              Ortodox religion

VA                Venäjä, pitkä oppimäärä    Ryska, lång lärokurs

VC                Venäjä, lyhyt oppimäärä    Ryska, kort lärokurs

W                  Äidinkieli, koltansaame     Modersmålet, skoltsamiska

YH                Yhteiskuntaoppi                 Samhällslära

Z                   Äidinkieli, pohjoissaame    Modersmålet, nordsamiska

Valitse Excelin Lisää valikosta Pivot-taulukko

ja paina OK.

Valitse Pivot—taulukon kentät ikkunasta koulun_nimi ja yht.

Vedä ∑ Arvot-laatikosta koulun nimi Rivit laatikkoon ja klikkaa ∑ Arvot-laatikossa i-pompulaan. Muuta Summa Keskiarvoksi.

Valitse alue B4:B404 eli koulujen keskiarvot ilman otsikkoa ja Tiedot-valikosta Lajittele ja suodata Ö:stä A:han.

Näin saatiin koulut järjestykseen. Miksi, ainakin minä kysyn?

Kun pisteiden arvot kopioi ja liittää uuteen lomakkeeseen, niin Excel piirtää oletusarvoilla (hyvä on, oli pakko lisätä otsikko) seuraavanlaisen kuvaajan.

kevään 19 yo-kokeiden kaikkien aineiden koulukohtainen keskiarvo exelillä

Kun tätä vertaa Google Sheetsin piirtämään histogrammiin pakollisista aineista, niin pohdituttaa, miksi siinä on tuo kyhmy oikealla ja mitä se tarkoittaa.

kevään 19 yo-kokeiden pakollisten aineiden koulukohtainen keskiarvo sheetsillä

Tätä kirjoitettaessa havaitsin, että ainakin minulla Excel toimi jouhevammin. Se voinee johtua siitä, että olen käyttänyt Exceliä 30 v ja Sheetsiä 5 v.

Palaan aiheeseen lähiaikoina. Tiedon louhinta on kivaa, kun sitä osaa. Vasitenkin se oppimispuoli on aina hauskaa.