KURZUS: Informatikai rendszerek alapjai
MODUL: Matematikai számítások Excellel
1. lecke: Pontosság, nevesítések, blokkműveletek és függvények
Cél: A blokkműveletek és függvények használatával egyes rutinfeladatok megoldása jelentős mértékben felgyorsítható. Különösen szembetűnő a különbség nagyobb mennyiségű adat feldolgozásakor. A nevesítések megfelelő alkalmazása növeli a munkánk átláthatóságát, tiszta megközelítést eredményez. A nevesítések használatával és a blokkok hatékony kezelésével Ön a saját munkájában is jelentős hatékonyságnövekedést érhet el. | |||||||
Gyakorlati feladatok megoldásánál tisztában kell lennünk azzal, hogy az általunk használt/választott szoftvereszközök számolási pontossága korlátozott. Bár a 15-16 jegyű pontosság a problémák többségénél teljesen elegendő, a tárolási korlátok megfelelő figyelembe vétele nagyon sok esetben szükséges. Az eltérések halmozódásából eredő pontatlanságok különösen nem kellően megfelelő megoldási módszer választása esetén okozhatnak problémákat. A leckében bemutatott példák és alkalmazások Önt is hozzásegíthetik ahhoz, hogy a későbbiekben ne "fusson bele a csőbe" ilyen típusú számítási feladatoknál. | |||||||
Követelmények: Ön akkor sajátította el megfelelően a tananyagot, ha (az Excel segítségével) | |||||||
| |||||||
Időszükséglet: A tananyag elsajátításához (a feladatok megoldásával együtt) hozzávetőlegesen 3 órára lesz szüksége. | |||||||
Kulcsfogalmak | |||||||
| |||||||
Pontosság | |||||||
Amikor az Excel segítségével matematikai, műszaki, gazdasági feladatokat oldunk meg, nem árt azt tudnunk, hogy minden numerikus értéket az ún. double lebegőpontos formában (ld. IEEE-754 szabvány) tárol, és ezekkel számol. Ennek a számábrázolási formának a pontossága nem haladja meg a 16 decimális számjegyet. Tehát ha egy értéket olyan cellaformátumban akarunk megjeleníteni, amely 16-nál több értékes jegy kiírását kéri, akkor az utolsó jegyek teljesen használhatatlanok lesznek. Ez a pontosság, illetve még inkább pontatlanság olyan eredményekhez is vezethet, amelyek megjelenítése zavarokat okozhat. | |||||||
Írja be a táblázatkezelőbe a következő számot: 12345678901234567890. Mi lett a cella tartalma? Elemezze az eredményt! | |||||||
Tegyük fel, hogy egy általános formátumú cellában a számítási eredményünk elméletileg 0 lenne. Ehelyett ott a számítási pontatlanságok miatt például a 2,62E-16 kijelzést látjuk. Ekkor érdemes a következő egyéni és egyben feltételes cellaformázást alkalmazni, amely a 0-hoz nagyon közeli értékek helyett magát a 0-t jeleníti meg: | |||||||
[<-1e-14]-Normál;[>1e-14]Normál;0 | |||||||
Gépi epszilonnak nevezik azt a double típusban tárolható legkisebb pozitív számot, amelyet 1-hez adva az eredmény értéke még 1-nél nagyobbnak adódik. Mivel a 64 biten tárolt double típus 52 bitet használ a bináris törtrész megjelenítésére, ezért a gépi epszilon értéke: | |||||||
Idézze fel az előző modulban tanult példát a gépi epszilonról! | |||||||
| |||||||
Az ábrán azt mutatjuk be, hogy az Excel milyen pontosan számol, illetve a decimális kijelzési forma előállításakor mennyi jegyig korrekt. A B oszlopban 1-ből indulva folyamatos felezgetéssel jutunk a megfelelő hatványig. Az értékek megjelenítését tudományos formában kértük 16 tizedes jeggyel. Az 56-os sorban közölt érték az utolsó és még pontosan közölt 52-dik sorbeli érték sorozatos 2-vel osztásakor kézzel kiszámolt és szövegként visszaírt érték! | |||||||
Látjuk, hogy az Excel csak a 14 tizedes jegyig (15 értékes jegy) jelzi ki pontosan az értéket, és a további jegyek helyett 0-t jelez ki, és ezekkel már nem számol. | |||||||
A számítási lépéseink közben a pontatlan értékeken végzett műveletek tovább növelik az eredmény pontatlanságát (relatív hibáját). Nem mindegy, hogy egy értéket a matematikailag azonos eredményt szolgáltató lépéssorok közül melyikkel számítunk ki. A következő példánk is ezt illusztrálja: | |||||||
Az első és az utolsó ún. Horner-zárójelezéses alak segítségével kiszámíttattuk a hatodfokú függvény értékeit a [0,995; 1,005] intervallumban és vonalas grafikonon ábrázoltuk. | |||||||
| |||||||
Cellák, tartományok nevesítése | |||||||
Idézze fel az Informatikai rendszerek alapjai tantárgynál tanultakat a nevek használatáról (Excel alapok)! | |||||||
Eddigi ismereteinket most kiegészítjük néhány fontos információval. | |||||||
A cellák és tartományok (blokkok) nevesítésének alapvető célja az, hogy a rögzített cellákra és tartományokra való abszolút hivatkozás egyszerűbb legyen. | |||||||
A későbbiekben látni fogjuk, hogy sokkal áttekinthetőbb képleteket írhatunk egy paraméteresen megadott függvény vizsgálatakor és ábrázoltatásakor, ha a paramétereket nevesített cellákban adjuk meg. | |||||||
Hasonlóképpen a mátrixokat használó feladatok esetén a mátrixblokkot névvel hivatkozva egyrészt kevesebbet kell begépelni, másrészt áttekinthetőbb és beszédesebb hivatkozásokhoz jutunk. Például egy lineáris egyenletrendszer együtthatómátrixát -nak, az inhomogén tagok oszlopvektorát -nek, és az ismeretlenek oszlopvektorát -nek nevezhetjük el. | |||||||
A nevek megadásakor egyaránt használhatunk kis- és nagybetűket, nincs különbség közöttük. | |||||||
Különbség van viszont a nevek használatakor az egyes Excel verziók között. Amíg az Excel 2003 csak a füzetszintű nevet ismeri, addig az Excel 2010-ben egy név hatóköre a teljes munkafüzet, vagy csak egy füzetlap lehet. Ez utóbbi esetben ugyanazt a nevet más-más füzetlapon más-más célra is használhatjuk. | |||||||
A keveredések elkerülése végett az aktuális lapon szerepelő lapszintű név elsőbbséget élvez a füzetszintű névhez képest. A másik füzetlapon lévő azonos névre minősített névvel lehet hivatkozni. Például a Munka1 lapon lévő lapszintű z névre az ugyancsak z nevet tartalmazó Munka2 lapon Munka1!z minősített módon hivatkozhatunk. | |||||||
A következő, elnevezésről szóló rész elolvasása közben hozzon létre egy munkalapszinten elnevezett blokkot az ábrának megfelelően! Próbálja ki, hogy használható-e ez a név egy másik munkalapon! | |||||||
A blokk nevének megadását célszerű a blokk kijelölése után a másodlagos egérgombbal kezdeményezhető helyi menü vagy a Névkezelő segítségével kezdeményezni. Ugyanis ekkor lehetőséget kapunk a név lapszintű megadására. A hagyományos névmegadási mezőben viszont csak füzetszintű név adható meg! Példánkban a Névmegadás füzetlapon adunk nevet. | |||||||
| |||||||
A nevek első jele betű, vagy \, vagy _ jel lehet. Egy név nem tartalmazhat space-t, műveleti jeleket és egyéb extra jeleket. Egy név nem ütközhet beépített névvel, vagy más objektum nevével. Így nem használhatjuk a Sor, Oszlop fogalmak első S illetve O betűjét, mert ezek védettek. Az Excel 2010-ben az R, C nevek is védettek (row, column). Érdekes dolog történik, ha egy r, c neveket tartalmazó Excel 2003 munkafüzetet megnyitunk Excel 2010-ben. Ekkor a védett nevek automatikusan kiegészülnek a _ vezető jellel, és _r illetve _c néven használódnak tovább. Ez az átalakítás minden r és c nevet tartalmazó hivatkozásban is végrehajtódik. | |||||||
Természetesen az érvényes cellahivatkozások sem adhatók meg névként. | |||||||
Próbáljon meg cellát elnevezni néhány védett névvel! Használhatunk függvényneveket névként? | |||||||
Blokkműveletek (tartományműveletek) | |||||||
Az adatblokkokkal és blokkokon műveletek végezhetők el (matematikai, függvény). Ezek eredménye többnyire egy újabb tartományba, blokkba kerül. Amikor egy képlet argumentumai blokkok és az eredmény is egy blokk, akkor az eredményt egy ún. blokkművelettel helyezzük el a célterületen. Ennek lépései a következők: | |||||||
| |||||||
Az, hogy egy cellában blokkművelettel származtatott eredmény van, onnan látszik, hogy a szerkesztősávbeli képlet automatikusan { } zárójelpárba kerül. Ezek az automatikus kapcsos zárójelek nem szerkeszthetőek. | |||||||
Milyen képleteket és műveleteket használva tölthetünk fel értékekkel egy új adatblokkot? A fontosabb szabályok a következők: | |||||||
A) Blokkot fel lehet tölteni tömbállandóval is. | |||||||
| |||||||
Tömbállandó: {} zárójelpárba helyezett értéklista. Az Excel 2003 esetén a sorok adatait pont választja el, Excel 2010 esetén a \ jel, a sorokat pedig mindkét esetben a ; jel. (Megjegyzés: a tömbállandókat ritkán használjuk.) | |||||||
Hozza létre tömbállandóval a fenti ábrán látható mátrixot! | |||||||
B) Ha egy cellába kerülő értéket legális kifejezéssel más cellák tartalmára hivatkozva származtatunk, akkor ugyanez a kifejezés cellák helyett blokkokra is alkalmazható, csak minden cellahivatkozás helyett azonos méretű blokkhivatkozás kell használnunk. | |||||||
Legyenek például szögértékek fokokban megadva egy blokkban. Helyezzük el egy ugyanilyen méretű blokkban a szögértékek szinuszát! | |||||||
| |||||||
Ez a példa azt is mutatja, hogy az egyváltozós függvényeket lehet tartományokra alkalmazni, és az eredmény ugyanilyen méretű tartományba kerül (blokkművelettel). | |||||||
Hasonlóképpen igen egyszerűen számítható a két azonos méretű blokkon végzett aritmetikai műveletek (összeadás, kivonás, szorzás, osztás) eredménye, amely cellapáronként készül el. | |||||||
C) Ha a forrástartomány nevesített adatsor, vagy adatoszlop, és a leképezés ugyanilyen méretű parallel elhelyezkedésű tartományt (adatoszlop esetén a sorindexek azonosak, illetve adatsor esetén az oszlopindexek azonosak) eredményez, akkor nem szükséges a blokkművelet használata. | |||||||
Legyen például a 20. sorban egy x-szel nevesített értéksorozat, és a 21. sorban szeretnénk ezen értékek négyzetét parallel társítva elhelyezni. Ekkor elegendő bármelyik eredménycellában az =x^2 képlethivatkozást megadni ahhoz, hogy minden érintett eredménycellába a vele egy oszlopban lévő x érték négyzete kerüljön. | |||||||
| |||||||
Ugyanezt az eredményt érjük el természetesen, ha mindezt blokkművelettel származtatjuk. | |||||||
Blokkfüggvények | |||||||
Az Excel néhány speciális és a mátrixszámításban alkalmazható blokkfüggvényt is használ, amelyek a lineáris algebrai feladatok megoldásakor nyújtanak segítséget. Ezek a következők: | |||||||
Blokkot (mátrixot, vektort) eredményeznek: | |||||||
| |||||||
Értéket eredményez: Mdeterm(). | |||||||
További, blokkot eredményező függvények (nem teljes felsorolás): Index(), Lin.ill(), Log.ill(), Növ(), Trend() stb. | |||||||
A Transzponálás() blokkfüggvény egy mátrix elemeit a főátlóra (azonos sor és oszlopindexű elemek) tükrözi. A matematikában a mátrix (vektor) neve után felső indexbe helyezett * jellel jelölik: . Sorvektor transzponáltja oszlopvektor és fordítva, oszlopvektor transzponáltja sorvektor. | |||||||
A lenti ábra egy nem nevesített blokk transzponálását illusztrálja. Sorvektor transzponáltja oszlopvektor lesz, és egy oszlopvektor transzponáltja sorvektor lesz. | |||||||
| |||||||
Generáljon véletlenszámokkal egy 5×8-as mátrixot (Informatikai rendszerek alapjai tananyag, Véletlen.között függvény), és transzponálja! | |||||||
Az Mszorzat(tömb1; tömb2) blokkfüggvény blokkművelettel előállítja két összeszorozható mátrix mátrixszorzatát. Két mátrixot akkor nevezünk összeszorozhatónak, ha az első tényező oszlopainak száma megegyezik a második tényező sorainak számával. Az eredménymátrix egy-egy eleme az első mátrix megfelelő sorvektora és második mátrix megfelelő oszlopvektora elempárjainak szorzatösszegével (skaláris szorzat) számítódik ki. | |||||||
A méretszabályok formálisan megadva: [; ] × [; ] [; ] és = (első méret: sorok száma). | |||||||
| |||||||
Legyen egy 3×4-es, pedig egy 5×3-as mátrix. Döntse el, hogy kiszámolható-e , illetve ! | |||||||
Az elmondottak szerint a mátrix bal felső eleme az 1×4 + 2×14 + 3×24 művelettel számítódott ki. | |||||||
A lineáris algebrában gyakorlatilag nem használják az azonos méretű P és Q blokkok P*Q elempáronkénti szorzatát, ami természetesen egészen mást eredményez, mint az Mszorzat(P;Q) függvénnyel kapott PQ mátrix, ha az utóbbi egyáltalán kiszámítható! | |||||||
| |||||||
Vegye fel Excelben tömbállandóként a következő mátrixokat: , . Számítsa ki -t és -t, illetve és elempáronkénti szorzatát! | |||||||
Az Inverz.mátrix(tömb) blokkfüggvény egy reguláris négyzetes méretű mátrix inverzét számítja ki, amely pontosan ugyanilyen méretű négyzetes mátrix lesz. A matematikában az mátrix inverzét -gyel jelölik. Ha egy négyzetes mátrixnak létezik az inverze, akkor a mátrix és az ő inverz mátrixának mátrixszorzata az egységmátrix lesz, amely a főátlójában csupa 1-est tartalmaz, azon kívül csak 0-kat. | |||||||
Ahhoz, hogy egy négyzetes méretű mátrix invertálható legyen, az szükséges, hogy bármelyik oszlopvektora lineárisan független legyen a többitől. Egy mátrix oszlopvektorai lineárisan összefüggők, ha bármelyikük is kifejezhető a többi vektorból lineáris műveletekkel (nyújtás, összeadás). Hasonlót mondhatunk a sorvektorokra is. A négyzetes méretű mátrix oszlopvektorai lineáris függetlenségének ellenőrzésére az Mdeterm(mátrix) függvényt használhatjuk, amely egyetlen értéket eredményez. Ha ez 0, akkor a mátrix nem invertálható, és az inverz helyén hibajelzést kapunk. Az ábráról az is leolvasható, hogy a szorzással kapott egységmátrix 0 elemei nem látszanak annak. Itt lehetne alkalmazni a pontosság részben ismertetett egyéni cellaformátumot. | |||||||
Jegyezzük meg, ha egy négyzetes mátrix elemei egész számok, akkor a kifejtési tétel miatt, amelyben az additív műveleteken kívül csak szorzás van, a determináns értéke egész szám lesz. Az inverz mátrix előállítása során (lásd Lineáris Algebra kurzusok) az mátrix adjungáltját a mátrix determinánsával kell osztani. Így ekkor az inverz mátrix minden eleme racionális tört lesz, azaz maximum 3-jegyű determináns esetén az Excel tört cellaformátuma segítségével ezt a tört alakot is megjeleníthetjük. | |||||||
Figyelje meg, hogy a következő ábrán a mátrix és inverzének szorzata a számolási pontatlanság miatt nem pontosan az egységmátrixot adja. Az eltérés azonban a gépi epszilon nagyságrendjébe esik. | |||||||
| |||||||
| |||||||
Számítsa ki az előző feladatban létrehozott és mátrixok determinánsát! Invertálhatóak a mátrixok? Ha valamelyik mátrix invertálható, akkor számítsa ki az inverzét! Jelenítse meg a kiszámított inverz mátrixo(ka)t megfelelő tört kijelzéssel! | |||||||
Transzformációk a lineáris térben, speciális mátrixok | |||||||
A megismert blokkfüggvényekkel igen sok matematikai és elsősorban lineáris algebrai feladat Excellel is kezelhető. Közülük a jegyzet keretein belül csak néhány típussal tudunk foglalkozni, a továbbiak - szükség esetén - önálló tanulással sajátíthatók el. | |||||||
A direkt számítási lépéseken alapuló megoldások a lineáris algebra fogalmaival írhatók le. A precíz definíciók megismétlése helyett mi most csak a szemléletes bemutatásra szorítkozunk. | |||||||
Egy T transzformáció lineáris, ha a lineáris műveletekkel sorrendben felcserélhető, azaz teljesen mindegy, hogy egy vektort előbb megnyújtunk, és azután transzformáljuk, vagy előbb transzformáljuk, és ennek eredményét nyújtjuk meg. Hasonlót mondhatunk az összeadás és a transzformáció sorrendjéről is. | |||||||
Idézzük fel, hogy egy lineáris térbeli vektor lineáris transzformációjának eredményvektorát a transzformáció mátrixának és az vektornak a mátrixszorzatával számítjuk. | |||||||
A transzformáció mátrixának oszlopvektorait a lineáris tér , , ..., triviális bázisvektorainak (a 3-dimenziós vektorok vektoralgebrájában ezek az , , egységvektorok) transzformáltjai adják. Azaz az első oszlopba koordinátái kerülnek, és így tovább. | |||||||
| |||||||
A fenti ábrát tanulmányozva fogalmazza meg, hogy milyen transzformációt hajt végre az SW12 mátrix! (A swap szó jelentése: csere.) A példa segítségével elemezze és próbálja ki az SW13 = [0 0 1; 0 1 0; 1 0 0] és az SW23 = [1 0 0; 0 0 1; 0 1 0] mátrixok által megvalósított transzformációkat is. | |||||||
Mit kapunk, ha valamelyik SW transzformációt kétszer egymás után is elvégezzük? Magyarázza meg az eredményt! | |||||||
Egy lineáris transzformáció inverze - amennyiben ilyen létezik - az a transzformáció, amelyre | |||||||
teljesül, azaz amelyet az vektor transzformáltjára végrehajtva visszakapjuk az eredeti vektort. | |||||||
Speciális transzformáció a helybenhagyás művelete, amelyre teljesül. A helybenhagyás mátrixa az egységmátrix, ennek oszlopaiban az , , ..., vektorok szerepelnek: | |||||||
Az egységmátrix főátlójában minden elem 1, azon kívül pedig 0. Az egységmátrix vektorai páronként merőlegesek, azaz ortogonálisak (páronként skaláris szorzatuk 0) és a hosszuk, pontosabban mondva az euklideszi normájuk (Pitagorasz-tétel általánosítása) 1. Egy vektorrendszert ortonormáltnak mondunk, ha vektorai páronként ortogonálisak és mindegyikük normája 1. | |||||||
A mátrixszorzás műveletének alkalmazásával próbálja ki, hogy az egységmátrix - mint transzformáció - valóban helyben hagyja a térben a vektorokat. | |||||||
Egy euklideszi lineáris térbeli oszlopvektor (olyan n dimenziós vektor, amelynek n darab koordinátája van) normájának négyzetét, azaz általánosított hosszának négyzetét többféleképp is kiszámíthatjuk. Nevesítsük x-szel a kérdéses oszlopvektort, ekkor a következő két lehetőség adódik az vektor önmagával vett skaláris szorzatának kiszámítására: | |||||||
=Négyzetösszeg(x) | |||||||
=Mszorzat(Transzponálás(x); x) | |||||||
Az utóbbi metodika szerint egy sorvektort szorzunk a mátrixszorzás szabályai szerint egy oszlopvektorral, ami így egyetlen számot eredményez. |