Útvonal 4.FKERES

Egy egész napos biciklitúrára indulva érdemes az útvonalat részletesen megtervezni. A Siófok és Szekszárd közötti kiránduláshoz az útvonalat térképekről és útvonaltervező programok adataiból jelöljük ki. A forrásállományban rendelkezésre állnak az érintett települések nevei, azon belül az utca- és útnevek, a szükséges irányváltások és a távolságok.

4. Az egyes útszakaszok azok hosszától függő átlagsebességgel tehetők meg. A figyelembe vehető távolság-sebesség kategóriákat a H6:I11 tartomány tartalmazza. Számítsa ki az F oszlopban az egyes útszakaszok megtételéhez szükséges időt úgy, hogy az útszakasz kilométerben mért hosszát ossza el az útszakaszhoz tartozó átlagsebességgel!

A feladatban haladjunk visszafelé, és kerestessük meg az útszakaszhoz tartozó átlagsebességet az FKERES függvénnyel a megadott táblázatból.

 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 - a megadott táv : D2 , nem zárjuk le, mert ez alapján határozzuk meg az útszakaszhoz tartozó átlagsebességet.

tábla : H7:I11 , a segédtábla, ami az egyes útszakaszhoz tartozó állagsebességet tartalmazza. Le kell zárni F4-gyel, mert másolásnál a tábla nem csúszhat el : $H$7:$I$11

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

Megkapjuk, hogy az átlagsebesség: 67 méternél 6 km/h.

A feladat azt mondja, hogy ezzel az összeggel kell elosztani az útszakasz km-ben mért hosszát, vagyis az E2-t, nem zárjuk le, hogy másolható legyen a képlet az egyes útszakaszokhoz.

 Képlet:

=E2/FKERES(D2;$H$7:$I$11;2)

Másoljuk végig ezt a képletet minden útszakaszra.

Megkapjuk mennyi óra alatt tette meg az egyes útszakaszokat a biciklis.

Eredmény:

Hogyan oldanád meg?

5. Az I1 cellában függvény segítségével határozza meg az A oszlop alapján, hogy a túra során hány településen tekerhetünk át!

Útvonal 3. Átváltás

Egy egész napos biciklitúrára indulva érdemes az útvonalat részletesen megtervezni. A Siófok és Szekszárd közötti kiránduláshoz az útvonalat térképekről és útvonaltervező programok adataiból jelöljük ki. A forrásállományban rendelkezésre állnak az érintett települések nevei, azon belül az utca- és útnevek, a szükséges irányváltások és a távolságok.

 3. A D oszlopban az egyes útszakaszok távolsága van méterben. Ezeket jelenítse meg az E oszlopban kilométerbe átváltva!

1 km = 1 000 m

Ez alapján a D oszlop adatait el kell osztani 1 000 -rel, és megkapjuk hány km a távolság. 

Képlete:

=D2/1000

Eredmény:

Hogyan oldanád meg?

4. Az egyes útszakaszok azok hosszától függő átlagsebességgel tehetők meg. A figyelembe vehető távolság-sebesség kategóriákat a H6:I11 tartomány tartalmazza. Számítsa ki az F oszlopban az egyes útszakaszok megtételéhez szükséges időt úgy, hogy az útszakasz kilométerben mért hosszát ossza el az útszakaszhoz tartozó átlagsebességgel!

Útvonal 2. Üres cella kitöltés

Egy egész napos biciklitúrára indulva érdemes az útvonalat részletesen megtervezni. A Siófok és Szekszárd közötti kiránduláshoz az útvonalat térképekről és útvonaltervező programok adataiból jelöljük ki. A forrásállományban rendelkezésre állnak az érintett települések nevei, azon belül az utca- és útnevek, a szükséges irányváltások és a távolságok.

2. A táblázat B oszlopában az egyes útvonal szakaszokhoz tartozó irányváltások találhatók. Az üres cellákba helyezze el az „egyenesen” szót!

Jelöljük ki a B1:B130 táblázatot.

Nyomjuk meg a Ctrl+B billentyűkombinációt.

Megjelenik egy "Ugrás" segédpanel - OK.

Ismét nyomjuk meg a Ctrl+B billentyűkombinációt.

Itt már megjelenik a Ugrás részen a kijelölt $B$1:$B$130 a Hivatkozás részen a B1:B130 jelenik meg.

Kattintsunk az Irányított gombra, és jelöljük ki az "üres cellák"-at.

 Csak az üres cellák lesznek kijelölve.

Az első üres cellába írjuk be:

="egyenesen"

Zárjuk le Ctrl+Enter billentyűkombinációval. Fontos, hogy ne csak Entert üssünk a végén, mert akkor csak ebben az egy cellában jelenik meg.

Eredményül, minden üres cellában megjelenik az "egyenesen" szöveg.

Eredmény:

Hogyan oldanád meg?

 3. A D oszlopban az egyes útszakaszok távolsága van méterben. Ezeket jelenítse meg az E oszlopban kilométerbe átváltva!

Útvonal 1. Adatbevitel

Egy egész napos biciklitúrára indulva érdemes az útvonalat részletesen megtervezni. A Siófok és Szekszárd közötti kiránduláshoz az útvonalat térképekről és útvonaltervező programok adataiból jelöljük ki. A forrásállományban rendelkezésre állnak az érintett települések nevei, azon belül az utca- és útnevek, a szükséges irányváltások és a távolságok.

1. Töltse be a tabulátorokkal tagolt, UTF-8 kódolású bicikliut.txt szövegfájlt a táblázatkezelőbe az A1-es cellától kezdődően! Munkáját utvonal 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 bicikliut.txt  szövegfájlt.

Fájl - Megnyitás - Tallóz - Fájl típusa: Minden fájl - Keressük meg a bicikliut.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: utvonal.

Eredmény:

Hogyan oldanád meg?

2. A táblázat B oszlopában az egyes útvonal szakaszokhoz tartozó irányváltások találhatók. Az üres cellákba helyezze el az „egyenesen” szót!

Kollégium 11. Diagram

A Kocka Kollégiumban kis csoportokban foglalkoznak a kollégistákkal. Az egyes csoportokat színekkel azonosítják. A félévi értesítők kiosztását követően minősítik a csoportokat  a diákok átlagai alapján, valamint az intézmény honlapján megjelentetik a statisztikai adatokat. Az alábbi feladatban ezt kell elkészítenie.

Minta:

11. Készítsen egy diagramot, amely a csoportok átlagát mutatja a mintán látható módon! Az értéktengely skáláját a minta alapján alakítsa ki! Az egyes oszlopok színét a csoport nevének megfelelően állítsa be! A diagram szélessége egyezzen meg a felette található adatok szélességével!

Jelöljük ki a B30:B33 Ctrl és a T30:T33 táblázatokat.

Beszúrás - Diagramok - Ajánlott diagramok - Minden diagram - Oszlop - OK.

Kattintsunk a diagram Y tengelyének adataira kétszer és jobb oldalt egy segítő párbeszédpanel jelenik meg.

Tengely formázása - Tengely beállításai - Határok - Minimum : 3,05 - Maximum : 3,75.

Keretezzük az oszlopokat.

Adatsor formázása - Adatsor beállításai - Kitöltés és vonal - Szegély : Folytonos vonal - Szín : Fekete - Szélesség : 2 pt.

Módosítsuk az oszlopok színeit.

Kattintsunk az első oszlopra 2szer, így csak az első oszlop lesz kijelölve.

Kezdőlap - Betűtípus - Kitöltőszín : Kék.

Kattintsunk a következő oszlopokra, és a csoportnévnek megfelelő színt adjuk hozzá.

Módosítsuk a diagramot a felette levő táblázatnak megfelelő nagyságra.

Kattintsunk a Diagramcímre és nevezzük át : "Csoportátlag".

Eredmény:

Érdekesség

Hogyan oldanád meg fejben?

Kollégium 9.ÁTLAG

A Kocka Kollégiumban kis csoportokban foglalkoznak a kollégistákkal. Az egyes csoportokat színekkel azonosítják. A félévi értesítők kiosztását követően minősítik a csoportokat  a diákok átlagai alapján, valamint az intézmény honlapján megjelentetik a statisztikai adatokat. Az alábbi feladatban ezt kell elkészítenie.

Minta:

9. A T30:T33 tartományban a csoportok átlagát a tantárgyi átlagok alapján számítsa ki!

 Az átlagot az ÁTLAG függvénnyel számoljuk ki.

Á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:

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

érték_1;érték_2;... - Az előzőleg kiszámolt tanulmányi átlagok csoportonként - F30:S30 - Nem zárjuk le, mert minden csoportra át szeretnénk másolni a képletet. 

Képlete:

=ÁTLAG(F30:S30)

 A T30 cella jobb alsó zöld négyzete segítségével másoljuk végig a képletet T33-ig.

Eredmény:

Hogyan oldanád meg?

 10. A B28:T33 tartomány celláit formázza a mintának megfelelően! A tanulmányi átlagokat két tizedes pontossággal jelenítse meg! Az oszlopszélességek és sormagasságok beállításánál ügyeljen arra, hogy minden adat látható legyen! Az F:T oszlopok szélessége egyezzen meg! A B:E oszlopok szélessége legyen az előbbiek (körülbelül) duplája! A B28:T28 és a B30:T33 tartományokat kívülről duplán szegélyezze!

Kollégium 8. SZUMHA, ÁTLAGHA

A Kocka Kollégiumban kis csoportokban foglalkoznak a kollégistákkal. Az egyes csoportokat színekkel azonosítják. A félévi értesítők kiosztását követően minősítik a csoportokat  a diákok átlagai alapján, valamint az intézmény honlapján megjelentetik a statisztikai adatokat. Az alábbi feladatban ezt kell elkészítenie.

8. A D30:S33 tartományban számítsa ki hibamentesen másolható képlet segítségével az adott csoport tanulóinak hiányzási, illetve tantárgyi átlagait!

 Kétféle megoldás:

1. Átlagot megkapjuk, ha összeadjuk az összes számot, ami az adott csoporthoz tartozik, és elosztjuk a csoportlétszámmal.

Ehhez a SZUMHA függvényt használjuk, és az előzőleg kiszámolt csoportlétszámot.

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 B2:B27 , ahol a csoportok szerepelnek, mivel másolni szeretnénk a képletet, ezt le kell zárni F4 segítségével : $B$2:$B$27

kritérium - a csoport neve, amit össze szeretnénk adni : B30, de mivel nem csak egy csoport van és külön-külön az összes csoportot szeretnénk összegezni, ezért nem teljesen zárjuk le. Addig nyomjuk az F4-et, amíg ezt kapjuk: $B30

összegtartomány : amit össze szeretnénk adni, tehát a hiányzásokat és a tantárgyakat, D2:D27, ezt is csak részben zárjuk le F4-gyel, mert minden hányzás és tantárgy esetében  a saját oszlopát vizsgáltatjuk :D$2:D$27

Az eredményt osztani kell a csoport létszámmal, hogy megkapjuk az átlagot. Ezt is részben zárjuk le, mert a csoport másolásnál változik : $C30

Képlete:

=SZUMHA($B$2:$B$27;$B30;D$2:D$27)/$C30

A cella jobb alsó zöld négyzete segítségével másoljuk a képletet az S oszlopig, majd az így másolt képletet lefelé a zöld csoportig.

Eredmény:

2. Eleve az ÁTLAGHA függvényt használjuk, akkor nem kell osztani, viszont nem használjuk itt fel az előző feladatban kiszámolt létszámot.

ÁTLAGHA -Az adott feltétel vagy kritérium által meghatározott cellák átlagát (számtani közepét) számítja ki

Képlete:

=ÁTLAGHA(tartomány;kritérium;(átlag_tartomány))

tartomány  - a B2:B27 , ahol a csoportok szerepelnek, mivel másolni szeretnénk a képletet, ezt le kell zárni F4 segítségével : $B$2:$B$27

kritérium - a csoport neve, aminek az átlagát szeretnénk megkapni: B30, de mivel nem csak egy csoport van és külön-külön minden csoport átlagát szeretnénk megkapni, ezért nem teljesen zárjuk le. Addig nyomjuk az F4-et, amíg ezt kapjuk: $B30

átlag_tartomány -  amiből az átlagot szeretnénk megkapni, tehát a hiányzásokat és a tantárgyakat, D2:D27, ezt is csak részben zárjuk le F4-gyel, mert minden hiányzás és tantárgy esetében  a saját oszlopát vizsgáltatjuk :D$2:D$27

Képlete:

=ÁTLAGHA($B$2:$B$27;$B30;D$2:D$27)

Itt nem kell osztani, mert már eleve az átlagot számoltattuk ki.

Eredmény: 

 Az eredmény azonos , csak a megoldáshoz használt függvény különbözik.

Hogyan oldanád meg?

9. A T30:T33 tartományban a csoportok átlagát a tantárgyi átlagok alapján számítsa ki!

Kollégium 7. DARABHA

A Kocka Kollégiumban kis csoportokban foglalkoznak a kollégistákkal. Az egyes csoportokat színekkel azonosítják. A félévi értesítők kiosztását követően minősítik a csoportokat  a diákok átlagai alapján, valamint az intézmény honlapján megjelentetik a statisztikai adatokat. Az alábbi feladatban ezt kell elkészítenie.

7. A C30:C33 tartományban határozza meg az egyes csoportok létszámát!

 Számoltassuk meg feltételes számolással, hogy hány gyerek neve mellett találhatóak meg az egyes csoportok. Ehhez a DARABHA függvényt használjuk.

 DARABHA - Egy tartományban összeszámolja azokat a nem üres cellákat, amelyek eleget tesznek a megadott feltételeknek.

Képlete:

=DARABHA(Tartomány;kritérium)

Tartomány - a B2:B27 cellatartományban nézessük meg függvény segítségével, hány gyerek neve mellett szerepel az aktuális csoport , le kell zárni f4-gyel, mivel mindig ezt kell vizsgálni - $B$2:$B$27

kritérium - a csoport neve : B30, nem zárjuk le, mert másolásnál a következő csoport létszámát is szeretnénk tudni.

Képlete:

 =DARABHA($B$2:$B$27;B30)

Eredmény:

Hogyan oldanád meg?

8. A D30:S33 tartományban számítsa ki hibamentesen másolható képlet segítségével az adott csoport tanulóinak hiányzási, illetve tantárgyi átlagait!

 Kollégium 6. Adatbevitel

A Kocka Kollégiumban kis csoportokban foglalkoznak a kollégistákkal. Az egyes csoportokat színekkel azonosítják. A félévi értesítők kiosztását követően minősítik a csoportokat  a diákok átlagai alapján, valamint az intézmény honlapján megjelentetik a statisztikai adatokat. Az alábbi feladatban ezt kell elkészítenie.

Minta:

 6. A B30:B33 tartományba írja be a csoportok nevét!

A csoportok nevei a B oszlopban található.

Írjuk be őket a Mintán látható sorrendben.

Eredmény:

Hogyan oldanád meg?

7. A C30:C33 tartományban határozza meg az egyes csoportok létszámát!