Teherautó fogyasztás 7. ÉV, MA

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

7. A G oszlopban határozza meg a járművek életkorát években! (A járművet a gyártás évében nulla évesnek tekintjük.) A képleteket úgy készítse el, hogy a táblázatot a következő évben megnyitva is a helyes értékek legyenek láthatók!

A járművek életkorát úgy tudjuk meghatározni, ha a mai napból kivonjuk a teherautók gyártási évét. A mai napot a MA függvénnyel tudjuk meghatározni. Mivel csak az évre van szükségünk, ezért az ÉV függvény segítségével csak az évet íratjuk ki.

MA - Visszatérési értéke az aktuális dátum dátumként formázva.

Képlete:

=MA()

ÉV - Kiszámítja, hogy az adott dátum melyik évre esik (1900 és 9999 közötti egész szám)

Képlete:

=ÉV(dátumérték) 

dátumérték - MA()

Képlete:

ÉV(MA())

Életkor meghatározásának képlete:

=ÉV(MA())-C2

Eredmény:

Teherautó fogyasztás 6. Fogyasztás

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

6. Az F oszlopban másolható képlet segítségével határozza meg a járművek által eddig elfogyasztott üzemanyag mennyiségét!

A D oszlopban a járművek által megtett utat találjuk.

Az E oszlopban kiszámoltuk, hogy 100 km-en hány liter üzemanyagot használnak a járművek.

Ezek alapján a D oszlopban lévő km-t elosztjuk 100-zal, és megszorozzuk az E oszlopban lévő átlagfogyasztással, és megkapjuk, mennyi üzemanyagot használt el eddig a teherautó.

Képlet:

=D2/100*E2

A cella jobb alsó sarkában levő zöld négyzet segítségével másoljuk végig a képletet.

Eredmény:

Teherautó fogyasztás 5. FKERES

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

5. A cégen belül tapasztalati adatok állnak rendelkezésre a különböző hengerűrtartalmú gépjárművek jellemző fogyasztásáról. Ezt az alábbi táblázat tartalmazza:

Az E oszlopban másolható képlet segítségével határozza meg az egyes gépjárművek jellemző fogyasztását! Amennyiben szükséges, az L1 cellától kezdődően hozzon létre segédtáblát!

Írjuk be az L1 cellától kezdve a következő segédtáblát:

0              7,5

1001        8,2

1501        9,1

2001      11,8

Ebben a segédtáblában fogjuk az FKERES függvény segítségével megkeresni, a teherautó átlagfogyasztását.

FKERES -  Egy táblázat bal szélső oszlopában megkeres egy értéket, és a megtalált értékhez tartozó sorból a megadott oszlopban elhelyezkedő értéket adja eredményül; alapesetben a táblázatnak növekvő sorrendbe rendezettnek kell lennie.

Képlete:

=FKERES(keresési_érték; tábla; oszlop_szám; [tartományban_keres])

keresési_érték - B2 - a teherautók hengerűrtartalma , nem szabad lezárni, mert változnia kell minden sorban.

tábla : L1:M4 , a létrehozott segédtábla,  le kell zárni F4-gyel, mert másolásnál a tábla nem csúszhat el : $L$1:$M$4

oszlop_szám 2, mert a tábla 2. oszlopára van szükség.

A teherautó átlagfogyasztásának képlete:

=FKERES(B2;$L$1:$M$4;2)

A jobb alsó zöld négyzetre duplán kattintva tudod másolni a képletet.

Eredmény:

Teherautó fogyasztás 4. HOL.VAN, INDEX

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

4. A B42-es cellában határozza meg, hogy a legtöbbet futott járműnek mi az azonosítója!

Már megkerestettük, mennyi a legmagasabb kilométerfutás. Most nézzük meg, hol van ez a kilométerfutás, és melyik teherautóhoz tartozik. Ehhez a HOL.VAN (hol van a keresett kilométer) és az INDEX függvényt fogjuk használni. 

HOL.VAN : Egy adott értéknek megfelelő tömbelem viszonylagos helyét adja meg adott sorrendben.

Képlete:

=HOL.VAN(keresési_érték; tábla; [egyezés_típusa])

keresési_érték : B41, ahol meghatároztuk a legtöbb megtett utat.

tábla :  D2:D39 a megtett utak felsorolása

egyezés_típusa : egyenlőnek kell lenni a keresési értékkel, tehát 0.

Képlete:

=HOL.VAN(B41;D2:D39;0)

 Eredmény:

 Láthatjuk, hogy a HOL.VAN függvény a megadott táblán belül a 33 sorban határozta meg a legtöbb utat, ami az Excel sorait nézve a 34.

Most az INDEX függvény segítségével határozzuk meg, hogy melyik teherautó tartozik ehhez a 33. sorhoz a táblán belül.

 INDEX - Értéket vagy hivatkozást ad vissza egy adott tartomány bizonyos sorának és oszlopának metszéspontjában lévő cellából.

Képlete:

 =INDEX(tömb; sor_szám; [oszlop_szám])

 tömb : a teherautó rendszámok az A oszlopban vannak - A2:A39

sor_szám : amit az előbb beírtunk képletet : HOL.VAN(B41;D2:D39;0)

 [oszlop_szám] egy oszlop van, nem szükséges kiírni

Képlete:

=INDEX(A2:A39;HOL.VAN(B41;D2:D39;0))

Eredmény:

Teherautó fogyasztás 3. MAX

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

3. A B41-es cellában határozza meg, hogy a legtöbbet futott jármű hány kilométert tett meg eddig!

A futott kilométerek a D oszlopban találhatók. A legtöbb megtett kilométert a MAX függvény segítségével tudjuk meghatározni.

MAX : Egy értékhalmazban szereplő legnagyobb számot adja meg. A logikai értékeket és szövegeket figyelmen kívül hagyja.

Képlete:

 =MAX(szám1;szám2;....szám255)

szám1;szám2;....szám255  -  D2:D39 cellatartományon beül vizsgálja meg, melyik a legmagasabb megtett kilométer.

 Képlete:

MAX(D2:D39)

Eredmény:

Teherautó fogyasztás 2. ÁTLAG

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

2. A D41-es cellában határozza meg a D oszlop adatainak felhasználásával, hogy a járművek átlagosan mennyi utat tettek meg eddig!

Átlagos út meghatározásához az ÁTLAG függvényt fogjuk használni.

ÁTLAG - Argumentumainak átlagát (számtani közepét) számítja ki, az argumentumok nevek, tömbök vagy számokat tartalmazó hivatkozások lehetnek.

Képlete:

=ÁTLAGA(érték_1;érték_2;...)

érték_1;érték_2;... - a megtett út teherautónként : D2:D39

Képlete:

=ÁTLAG(D2:D39)

Eredmény:

Teherautó fogyasztás 1. Adatbevitel

Egy szállítmányozással foglalkozó cég nyilvántartást vezet a használatában lévő gépjárművekről. Rögzítették a járművek cégen belüli azonosítóját, hengerűrtartalmát, gyártási évét és  a járművel eddig megtett kilométereket. Ezek az adatok a jarmu.txt állományban találhatók.

1. Töltse be a tabulátorokkal tagolt, UTF-8 kódolású jarmu.txt szövegfájlt a táblázatkezelőbe az A1-es cellától kezdődően! Munkáját fogyaszt néven mentse el a táblázatkezelő alapértelmezett formátumában!

Nyissunk meg egy üres Excel munkafüzetet.

Töltsük be a  jarmu.txt  szövegfájlt.

Fájl - Megnyitás - Tallóz - Fájl típusa: Minden fájl - Keressük meg a  jarmu.txt  fájlt és nyissuk meg.

Menjünk végig a Szövegbeolvasó varázslóval. 

Ha mindent elfogadtunk, akkor Befejezés.

Jelöljük ki a teljes munkafüzetet, majd legszélesebben kijelöl (Kattintsunk a fejléc oszlopelválasztó függőleges csíkra) - Így minden cella tartalma tökéletesen látható.

Mentsük el:

Fájl - Mentés másként - Fájltípus: Excel munkafüzet - Neve: fogyaszt.

Eredmény:

Ittas vezetés 11. Formázás

Az ittas vezetők évről évre sok személyi sérüléssel járó közúti balesetet okoznak. A rendőrség havi szinten közli megyékre lebontva az ilyen típusú balesetek számát. A 2011. és 2012. évi adatokat tartalmazza az ittas_2011.txt és az ittas_2012.txt tabulátorokkal tagolt, UTF-8 kódolású állomány. Feladata az adatok feldolgozása a következő leírás és a minta alapján.

Minta:

A következő feladatokat a 2012 munkalapon végezze el, ha a feladat szövege másképp nem kéri!

11. Formázza a táblázatot a minta és a következők szerint!
        a.           Az első két sorban lévő oszlopfeliratokra, illetve az A24:A25 cellákra, valamint az összes
                        számított mezőt tartalmazó cellákra félkövér betűstílust állítson be!
        b.            Az első két sorban lévő oszlopfeliratokat, illetve a számértékeket tartalmazó
                        cellákat igazítsa vízszintesen középre!
        c.             Az A1:O1 tartomány celláit vonja össze!
        d.            Az oszlopok szélességét állítsa be úgy, hogy a szövegek, illetve a számok
                        ne legyenek takarásban!
        e.             Az A1:O25 tartományt szegélyezze a minta szerint úgy, hogy a külső szegély legyen vastagabb!

a.           Az első két sorban lévő oszlopfeliratokra, illetve az A24:A25 cellákra, valamint az összes
                        számított mezőt tartalmazó cellákra félkövér betűstílust állítson be!

Jelöljük ki az első két sort!

Nyomjuk meg a Ctrl+B billentyűkombinációt és félkövér betűstílusú lesz az első két sor.

Jelöljük ki B24:M24 Ctrl - t tartsuk nyomva és jelöljük ki a N3:O22;  R3:S22;  V3:V5 ;  W7:W13 cellatartományokat.

Kijelölés után ismét nyomjuk meg a Ctrl+B billentyűkombinációt. Egyszerre félkövér lesz az összes kijelölt, számított mező.

b.            Az első két sorban lévő oszlopfeliratokat, illetve a számértékeket tartalmazó
                        cellákat igazítsa vízszintesen középre!

Jelöljük ki a felső két sort. 

Kezdőlap - Igazítás - Középen vízszintesen igazítás.

Jelöljük ki az összes számértéket.

Jelöljük ki a B3:O22 , nyomjuk meg a Ctrl-t, tartsuk nyomva folyamatosan és jelöljük ki a B24:M25; Q3:S22; V3:V5; V7:W13.

Kezdőlap - Igazítás - Középen vízszintesen igazítás.

c.             Az A1:O1 tartomány celláit vonja össze!

Jelöljük ki az A1:O1 tartományt.

Kezdőlap - Igazítás - Cellaegyesítés.

d.            Az oszlopok szélességét állítsa be úgy, hogy a szövegek, illetve a számok
                        ne legyenek takarásban!

Folyamatosan figyeltünk rá!

e.             Az A1:O25 tartományt szegélyezze a minta szerint úgy, hogy a külső szegély legyen vastagabb!

Jelöljük ki az A2:O25 cellatarományt.

Jobb gomb - Cellaformázás - Szegély - Vékonyvonal - Körül - Belül - OK.

Jelöljük ki az A24:M25 cellatarományt.

Jobb gomb - Cellaformázás - Szegély - Vékonyvonal - Körül - Belül - OK.

Jelöljük ki az A1:O25 cellatarományt.

Jobb gomb - Cellaformázás - Szegély - Vastagvonal - Körül - OK.

Eredmény: 

Ittas vezetés 10. Diagram

Az ittas vezetők évről évre sok személyi sérüléssel járó közúti balesetet okoznak. A rendőrség havi szinten közli megyékre lebontva az ilyen típusú balesetek számát. A 2011. és 2012. évi adatokat tartalmazza az ittas_2011.txt és az ittas_2012.txt tabulátorokkal tagolt, UTF-8 kódolású állomány. Feladata az adatok feldolgozása a következő leírás és a minta alapján.

Minta:

A következő feladatokat a 2012 munkalapon végezze el, ha a feladat szövege másképp nem kéri!

10. Készítsen tortadiagramot a minta szerint a régiókban okozott balesetek száma alapján! Jelmagyarázatot ne tüntessen fel! A régiók nevei és a százalékos értékek jelenjenek meg a cikkekhez tartozóan! A diagram címe „Ittas vezetés miatti balesetek régiónként 2012” legyen! A diagramot a 2012 munkalapra helyezze el a 27. sor alá! A szélességet úgy állítsa be, hogy a 12 hónapot tartalmazó cellatartománynál ne legyen szélesebb!

Jelöljük ki az U7:U13 Ctrl-t tarsuk lenyomva és a W7:W13 cellatartományt.

Beszúrás - Diagramok - Ajánlott diagramok - Minden diagram - Kör - 3D kör - Stílus 5 - OK.

- Jelmagyarázatot ne tüntessen fel!

Diagramtervezés - Diagramelrendezések - Diagram-összetevő hozzáadása - Jelmagyarázat - Nincs.

- A régiók nevei és a százalékos értékek jelenjenek meg a cikkekhez tartozóan!

Diagramtervezés - Diagramelrendezések - Diagram-összetevő hozzáadása - Adatfelirat - Értéknél, kívül.

Kattintsunk a megjelenő adatfeliratra, értékre duplán, és megjelenik jobb oldalt egy segítő tábla. 

Adatfeliratok formázása - Felirat beállításai - Kategória neve : pipa - Érték : kivesszük a pipát - Százalék : pipa .

- A diagram címe „Ittas vezetés miatti balesetek régiónként 2012” legyen!

Kattintsunk a duplán a "Diagramcím" -re. Írjuk át "Ittas vezetés miatti balesetek régiónként 2012"

- A diagramot a 2012 munkalapra helyezze el a 27. sor alá!  A szélességet úgy állítsa be, hogy a 12 hónapot tartalmazó cellatartománynál ne legyen szélesebb!

Egér segítségével fogjuk meg a diagramot, és húzzuk a 28. sorba a táblázatunk alá, valamint az M oszlopig. Igazítsuk méretre.

Eredmény:

Ittas vezetés 9. SZUMHA

Az ittas vezetők évről évre sok személyi sérüléssel járó közúti balesetet okoznak. A rendőrség havi szinten közli megyékre lebontva az ilyen típusú balesetek számát. A 2011. és 2012. évi adatokat tartalmazza az ittas_2011.txt és az ittas_2012.txt tabulátorokkal tagolt, UTF-8 kódolású állomány. Feladata az adatok feldolgozása a következő leírás és a minta alapján.

A következő feladatokat a 2012 munkalapon végezze el, ha a feladat szövege másképp nem kéri!

9. Magyarország megyéit hét régióba soroljuk. Az egyes régiók nevét és számát az U7:V13 tartomány tartalmazza. A Q oszlopban lévő számok mutatják meg, hogy melyik megye melyik régióba tartozik. Határozza meg másolható képlet segítségével a W7:W13 tartomány celláiban az egyes régiókban az ittasan okozott balesetek számát!

 Össze szeretnénk adni feltétellel a régiókba tartozó megyékben okozott ittas baleseteket. Összeadás a SZUM függvény a feltétel a HA függvény. Nekünk a SZUMHA függvényre van szükségünk.

SZUMHA - A megadott feltételnek vagy kritériumnak eleget tevő cellákban található értékeket adja össze

Képlete:

 =SZUMHA(tartomány;kritérium;összegtartomány), ahol k meghatározza azon számok halmazát, amelyeket össze szeretnénk adni.

tartomány - a Q3:Q22 , ahol a régiókhoz rendelt kódok szerepelnek, mivel másolni szeretnénk a képletet, ezt le kell zárni F4 segítségével : $Q$3:$Q$22

kritérium - a kód maga, amit össze szeretnénk adni : V7, de mivel az összes kódot szeretnénk összeadatni, ezért nem zárjuk le.

összegtartomány : amit össze szeretnénk adni, tehát az ittas baleset mennyisége, B2:B71, ezt is lezárjuk F4-gyel, mert minden kód esetében ezt az oszlopot vizsgáltatjuk : $N$3:$N$22

Képlete:

=SZUMHA($Q$3:$Q$22;V7;$N$3:$N$22)

Eredmény: