Tartalomjegyzék:
- Pakolás körökkel: egyszerű matematikai képletek
- Háromszögelt felület elemzése: adatok importálása, egyszerű függvények, feltételes formázás
- GY: Másodfokú egyenlet megoldása: egyszerű függvények
Bevezető
Az Excelben nagyon sok beépített függvény található, amelyek megkönnyítik a munkánkat. Általános esetben a függvénynek vannak bemeneti adatai, amelyeken valamilyen műveletet elvégez és meghatároz egy kimenetet, amit a program kiír az adott cellában. A függvényeket kombinálhatjuk is egymással.
Példa: Az ábráról leolvashatók a szabályok. Ha képletet írunk egy cellába, akkor annak "=" jellel kell kezdődnie. A függvény neve után zárójelben következnek a bemeneti paraméterek, egymástól pontosvesszővel (vagy a számítógép beállításaitól függően vesszővel) elválasztva. A cellában a végeredmény jelenik meg. Ha a képletet szeretnénk megnézni, akkor rá kell kattintani a cellára egyszer vagy kétszer. A cella tartalmát szerkeszthetjük a képletszerkesztő mezőben vagy a cellában is. A SUM() függvény bemeneti paramétere egy vagy több tartomány, a végeredmény pedig az összes tartományban lévő szám összege. A példában két tartományt adtunk meg (egy-egy cella), de írhattuk volna azt is, hogy =SUM(B1:B2).
A függvények bemeneti adatainak a sorrendjét nem kell megtanulni, amint elkezdjük gépelni a függvény nevét, a program megjelenít egy súgót, ami segít abban, hogy milyen paramétereket kell megadnunk. Azt is jelöli a paraméter színével, hogy éppen hol járunk a képletben.
TIPP: Ha nem működik valami, vagy nem világos, hogy hogyan kell egy függvényt használni, akkor érdemes használni az Excel súgóját. Egyszerű internetes kereséssel az első találatok között megtaláljuk a Microsoft weboldalát, ahol részletes, példákkal illusztrált magyarázatokat találunk.
Egyszerűbb matematikai függvények:
- SIN(), COS(): a bemeneti szöget radiánban kell megadni, visszaadja a szinusz/koszinusz értéket
- ASIN(), ACOS(): arkusz szinusz/koszinusz
- PI(): a 𝛑 értékét adja vissza (de "csak" 15 számjegyet tud), nincs bemeneti paramétere!
- SQRT() (GYOK()): négyzetgyök
- RADIANS() (RADIÁN()): átváltás fokról radiánra
- DEGREES() (SZÖG()): átváltás radiánról fokra
TIPP: Függvényeket kombinálhatunk, egymásba ágyazhatjuk őket (pl. =SIN(PI())) vagy összeadhatjuk őket (pl. =SIN(PI())+COS(PI()).
Egyszerűbb aggregációs függvények:
- SUM() (SZUM()): összegzés
- AVERAGE() (ÁTLAG()): átlag
- COUNT() (DARAB()): számokat tartalmazó cellák darabszáma
- MIN(), MAX(): legkisebb, legnagyobb érték
Hibakezelés. A képletek és függvények használatakor nagyon fontos, hogy csak a tökéletesen pontos, a szabályoknak megfelelő értékeket tud értelmezni a program. Ha valamilyen hibát talál, akkor különböző hibaüzenetekkel segít abban, hogy megtaláljuk a problémát.
- ######: az adat nem fér ki a cellában, a cella méretének növelésével megoldódik a probléma
- #DIV/0!: nullával való osztás
- #N/A: hibás hivatkozás
- #NAME?: ismeretlen nevű függvény (pl. elgépeltük)
- #REF!: ez a hibaüzenet akkor szokott megjelenni, amikor utólag kitörölünk egy cellát, amire az adott képlet hivatkozott
TIPP: A hibaüzenetek gyorsan visszafejthetők egyszerű internetes kereséssel . Az első találatok között meg fogjuk találni, hogy hol érdemes keresni a hibát.
A következő feladatokban a fenti függvényeket kell használni. Hiba esetén pedig próbáljuk meg ellenőrizni, hogy helyes volt-e a szintaktika és értelmezzük a hibaüzenetet.
1. Pakolás körökkel
Feladat: egy A4-es lapra köröket rajzolunk, amelyek érintkezhetnek egymással. Számoljuk ki, hogy egy adott sugár esetén hány darab kör fér rá a lapra és mennyi a hasznos terület %-ban kifejezve.
Először az A4-es lap méreteit kell a táblázatba beírni és kiszámolni a területét.
- az A5 cellába írjuk, hogy "kör sugara", a B5 cellába írjunk be egy tetszőleges számot
- az A6 cellába írjuk, hogy "kör területe", a B6 cellában számítsuk ki a kör területét:
=B5^2*PI() - az A7 cellába írjuk, hogy "darab vízsz.", a B7 cellában számítsuk ki a darabszámot vízszintesen (szélesség/(2*sugár)):
=B2/(2*B5) - az A8 cellába írjuk, hogy "darab függ.", a B8 cellában számítsuk ki a darabszámot függőlegesen (magasság/(2*sugár)):
=B1/(2*B5)
- ha magyar nyelvű Excelt használunk, akkor magyarul keressünk rá a szükséges funkcióra
- ha angol nyelvű Excelt használunk, akkor pedig angolul
- ha tudjuk magyarul a parancs nevét, de angol Excelt használunk, akkor pedig keressünk rá a fordításra
- módosítsuk a darabszám képleteit a megtalált függvény segítségével
gombra kattintva az eredmény százalékban jelenik meg.
- az A10 cellába írjuk, hogy "összesen", a B10 cellában számoljuk ki a darabszámot:
=B7*B8 - az A11 cellába írjuk, hogy "hasznos terület", a B11 cellában számoljuk ki a hasznos területet:
=B10*B6/B3 - jelenítsük meg a hasznos területet %-ban
- állítsunk be a többi cellához is mértékegységeket
Megoldását összevetheti a megoldókulccsal.
2. Háromszögelt felület elemzése
Feladat: Terveztünk az ábra szerint egy háromszögekből álló szerkezetet. A tervet egy CAD programmal készítettük el, ami sorszámot is rendelt a háromszögekhez és mindegyiknek kigyűjtötte az oldalhosszait a haromszogek.csv fileba. Szeretnénk a tervet megmakettezni papírból, de a háromszögek találkozását ragasztószalaggal, a 20°-nál kisebb szögeket pedig egy kis fém elemmel meg kell erősítenünk!
Számolja ki, hogy:
- mennyi papírra lesz szükségünk?
- körülbelül mennyi ragasztószalagra lesz szükségünk?
- mekkorák a háromszögek szögei?
- átlagosan mekkora háromszögeket terveztünk be?
- hány darab háromszög van?
A makettezést szeretnénk megkönnyíteni és megjelölni a megerősítendő szögeket. Használjon feltételes formázást!
Először töltsük le a CSV filet, majd importáljuk az Excellel. A CSV ("comma separated values") egy szöveges file formátum, amiben az adatok vesszővel/pontosvesszővel vannak egymástól elválasztva. Ezt a formátumot nagyon sok program képes kezelni, így segítségével adatokat vihetünk át az egyik programból a másikba.
- nyissuk meg az Excelt és válasszuk a Data/Get & Transform Data menüpontból a Get Data/From File/From text/CS lehetőséget
- a következő párbeszédablakban navigáljunk el a megnyitandó filehoz
- ezután meg kell adnunk, hogy az adatokat milyen jel választja el egymástól (Delimiter)
- jelöljük meg a Semicolon (Pontosvessző) lehetőséget
- mást nem kell beállítani, kattintsunk a Load gombra
A táblázatban minden sor egy háromszög adatait tartalmazza. A kerülethez az oldalak összegét kell vennünk, a területhez pedig használhatjuk a Hérón-képletet:
, ahol a, b, c a háromszög oldalai és s a kerület fele.
- az E1, F1, G1 cellákba írjuk be rendre, hogy "kerület", "s", "terület"
- az E2 cellában számoljuk ki a kerületet a SUM képlet segítségével:
=SUM(B2:D2) - az F2 cellában számoljuk a fél kerületet:
=E2/2 - a G2 cellába pedig írjuk be a Hérón-képletet
=SQRT(F2*(F2-B2)*(F2-C2)*(F2-D2))
A szögeket a mellékelt ábra alapján nevezzük el. Használjuk a koszinusz-tételt, hogy a c oldallal szemközti gamma szöget meghatározzuk! A tétel szerint:
c² = a² + b² - 2⋅a⋅b⋅cos(γ),
ebből:
A másik két szöget a szinusz-tétellel határozzuk meg:
,
így
α = asin(a⋅sin(γ)/c)),
β = asin(b⋅sin(γ)/c))
- a H1, I1, J1 cellákba írjuk be rendre, hogy "alpha", "beta", "gamma"
- először a gammát számoljuk ki a J2 cellában a fenti képletbe helyettesítve:
=ACOS((B2^2+C2^2-D2^2)/(2*B2*C2)) - a gamma segítségével számoljuk ki az alpha értéket a H2 cellában:
=ASIN(B2*SIN(J2)/D2) - hasonlóan az I2 cellában:
=ASIN(C2*SIN(J2)/D2) - másoljuk a képleteket lefelé duplakattintással
Az adatok mellett egy kis táblázatban számoljuk ki a szükséges anyagmennyiségeket. Az L oszlopot töltsük ki az ábra alapján!
- az M2 cellában számoljuk ki az összterületet:
=SUM(H:H) - az M3 cellában a darabszámhoz bármelyik - számokat tartalmazó - oszlopot megadhatjuk a COUNT() függvénynek:
=COUNT(H:H) - az M4 cellában az átlagos területet számoljuk ki:
=AVERAGE(H:H) - a ragasztószalag becsült hosszához vehetjük az összkerület felét, vagy összegezhetjük az s értéket is:
=SUM(E:E)/2
TIPP: A SUM(), AVERAGE(), COUNT(), MIN(), MAX() függvények a szöveget tartalmazó cellákat figyelmen kívül hagyják, így nem kell foglalkoznunk a fejlécekkel, átadhatjuk nekik az egész oszlopot.
Már csak a 20°-nál kisebb szögeket kell megjelölnünk. Mivel a szögeket radiánban kaptuk meg, ezért először a 20°-ot át kell váltanunk radiánra. Ezután a kapott számot használjuk fel a feltételes formázás beállítására: csak azokat a cellákat színezzük be, amelyek 20°-nál kisebb szöget tartalmaznak.
- az M9 cellában végezzük el az átváltást:
=RADIANS(20) - jelöljük ki a H:J oszlopokat
- válasszuk a Home / Conditional Formatting / Highlight Cells Rules / Less than menüpontot
- a megjelenő ablakban írjuk be a fenti értéket
Megoldását összevetheti a megoldókulccsal.
3. Másodfokú egyenlet megoldó (gyakorló feladat)
Feladat: készítsünk egy kis táblázatot, amelybe beírva a másodfokú egyenlet együtthatóit, visszakapjuk a megoldásokat! Számoljuk ki a diszkriminánst is és feltételes formázással jelöljük, ha negatív az értéke!
Az ax² + bx + c = 0 másodfokú egyenlet megoldóképlete:
,
a diszkrimináns pedig a gyök alatti rész. Ha a diszkrimináns negatív, akkor nincs megoldása az egyenletnek a valós számok halmazán.
- adjunk meg tetszőleges a, b, c értékeket a B1,B2,B3 cellákban
- számoljuk ki a diszkriminánst a B5 cellában:
=B2^2-4*B1*B3 - számoljuk ki az x1 értékét a B6 cellában:
=-B2+SQRT(B5)/(2*B1) - számoljuk ki az x2 értékét a B7 cellában:
=-B2-SQRT(B5)/(2*B1)
- válasszuk a Home / Conditional Formatting / Highlight Cells Rules / Less than menüpontot
- a megjelenő ablakban írjuk be, hogy 0