Üvegválogató 6. DARABHA

Az élelmiszerboltokban a nem visszaváltható üvegeket is összegyűjtik és újrahasznosítást végző üzembe szállítják. Az üvegeket – válogatás nélkül – 20 üveg tárolására alkalmas rekeszekbe gyűjtik és szállítják. Minden rekeszről feljegyezték, hogy hány fehér, zöld, illetve barna üveget tárol. A különböző boltokból beszállított rekeszek adatai állnak rendelkezésére az UTF-8 kódolású szallitolevel.txt állományban.

A táblázatban a következő adatok állnak rendelkezésére: a rekeszt a kilenc üzlet közül melyikből szállították a feldolgozó üzembe, a szállítás napja és az üvegek száma szín szerint. Az üzleteket sorszámuk azonosítja. A táblázatot 150 beérkezett rekesz adatainak feldolgozására készítse fel! A megoldás során végig vegye ezt figyelembe!

6. Határozza meg az I7:I15 tartomány celláiban, hogy az egyes üzletekből hány rekeszt szállítottak az üzembe!

Számoljuk össze, hányszor fordulnak elő az egyes üzletek külön-külön a DARABHA függvény segítségével. 

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 - az A2:A151 cellatartomány tartalmazza az üzletek számait, de mivel másoljuk a képletet, le kell zárni F4-gyel, tehát  A$2:A$151

kritérium - az üzletek számai folyamatosan : H7

Képlete:

=DARABHA(A$2:A$151;H7)

Eredmény:

Vízkeménység 5. DARABHA, SZUMHATÖBB, SZUM

Az ivóvíz keménységét az oldott kalcium- és magnéziumsók okozzák. A vízkeménységet német keménységi fokban (nk°) adják meg, melyet a táblázatban található mérési adatokból számolnak, és ez alapján kategóriákba sorolnak. 1 német keménységi fok 10 milligramm/liter kalcium-oxiddal egyenértékű kalcium- és magnéziumionnak felel meg.
Heves megye településeinek népességszáma és vízkeménységi mérési adatainak alsó és felső határértékei állnak rendelkezésére az UTF-8 kódolású vizforras.txt állományban.

5. A települések ivóvíz minősége alapján határozza meg:
        a.            a J2:J6 tartomány celláiban az egyes kategóriákba tartozó települések számát!
        b.            a K2:K6 tartomány celláiban – a települések népességét figyelembe véve –
                        ivóvíz-kategóriánként a fogyasztók számát!
        c.             az L2:L6 tartomány celláiban ivóvíz-kategóriánként a fogyasztók százalékos arányát
                        Heves megye összlakosságának számához képest!

Az egyes vízkeménységekhez tartozó települések számát a DARABHA feltételes számolással fogjuk meghatározni.

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 - az F oszlop , melyben meghatároztuk a víz minőségét F2:F119 , mivel másolni szeretnénk függőlegesen, ezért zárjuk le F4-gyel : $F$2:$F$119

kritérium - A víz kategória, amelyiknek meg szeretnénk számolni a hozzá tartozó települések számát I2, nem zárjuk le, mert másolásnál a víz minőségi kategóriájának változni kell.

Képlete:

=DARABHA($F$2:$F$119;I2)

Eredmény:

b.            a K2:K6 tartomány celláiban – a települések népességét figyelembe véve – ivóvíz-kategóriánként a fogyasztók számát!

A SZUMHATÖBB függvény segítségével számoltassuk össze a különböző vízkeménységhez tartozó népességeket.

SZUMHATÖBB - A megadott feltétel- vagy kritáriumkészletnek eleget tevő cellákban található értékeket adja össze

Képlete:

=SZUMHATÖBB(összegtartomány; kritériumtartomány1; kritérium1)

összegtartomány : a népességek településenként B2:B119 tartományban találhatóak, hogy a képlet másolható legyen, le kell zárni az oszlopot, így az összegtartomány : $B$2:$B$119

kritériumtartomány1 a településekhez tartozó vízkeménységet a F2:F119 cellatartomány tartalmazza, ezt le kell zárni F4-gyel teljesen, hogy másolásnál se változzon $F$2:$F$119

kritérium1 a vízkeménység I2, függőlegesen kell végighúzni a képletet, nem szabad lezárni, hogy mindig a következő vízkeménységet számolja.

Képlete:

=SZUMHATÖBB($B$2:$B$119;$F$2:$F$119;I2)

Eredmény:

c.             az L2:L6 tartomány celláiban ivóvíz-kategóriánként a fogyasztók százalékos arányát Heves megye összlakosságának számához képest!

Heves megye összlakosságát megkapjuk, ha a SZUM függvénnyel összeadatjuk a K2:K6 cellatartományt, vagyis a teljes népességet. Ez lesz a 100%.

SZUM - Az összes számot összeadja egy adott cellatartományban

Képlete:

=SZUM(szám1,[szám2],...)

szám1,[szám2],... : K2:K6 cellatartomány, a népesség száma 

Képlete:

=SZUM(K2:K6)

A teljes népesség100%-hoz  képest vízkeménységenként a százalékos arányt megkapjuk, ha a vízkeménységhez tartozó népességet K2 osztjuk a teljes népességgel. A K2:K6 cellatartományt le kell zárni F4-gyel, hogy tudjuk másolni a képletet. $K$2:$K$6

Képlete:

=K2/SZUM($K$2:$K$6)

Állítsuk át az eredményt cellaformázással százalékra egy tizedes jeggyel.

Eredmény:

Üvegválogató 5. DARAB, DARABHA, SZUM

Az élelmiszerboltokban a nem visszaváltható üvegeket is összegyűjtik és újrahasznosítást végző üzembe szállítják. Az üvegeket – válogatás nélkül – 20 üveg tárolására alkalmas rekeszekbe gyűjtik és szállítják. Minden rekeszről feljegyezték, hogy hány fehér, zöld, illetve barna üveget tárol. A különböző boltokból beszállított rekeszek adatai állnak rendelkezésére az UTF-8 kódolású szallitolevel.txt állományban.

A táblázatban a következő adatok állnak rendelkezésére: a rekeszt a kilenc üzlet közül melyikből szállították a feldolgozó üzembe, a szállítás napja és az üvegek száma szín szerint. Az üzleteket sorszámuk azonosítja. A táblázatot 150 beérkezett rekesz adatainak feldolgozására készítse fel! A megoldás során végig vegye ezt figyelembe!

5. Az üzletekből beérkezett rekeszek adataiból határozza meg:
         a.    az I1-es cellában a beérkezett rekeszek számát, figyelembe véve az elképzelhető maximális rekeszszámot,
        b.     az I2-es cellában a teli rekeszek számát,
        c.     az I3-as cellában az összes beérkezett üveg számát!

a.    az I1-es cellában a beérkezett rekeszek számát, figyelembe véve az elképzelhető maximális rekeszszámot,

Ehhez a feladathoz a DARAB függvény fog segíteni.

A számoláshoz a DARAB függvényt fogjuk használni.

DARAB - Megszámolja, hogy hány olyan cella van egy tartományban, amely számot tartalmaz

Képlete: 

=DARAB(érték1;érték2)

érték1  A2:A151 , megszámolja, hogy összesen hány üzlet van, feltételezve, ha mind küld be egy rekeszt.

=DARAB(A2:A151)

Eredmény:

 b.     az I2-es cellában a teli rekeszek számát,

Az F oszlopban meghatároztuk, hogy melyik üzletben van teli láda. Számoljuk meg azokat, ahol "+" jel van. Ehhez a DARABHA feltételes számolást 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 - az F oszlop , melyben meghatároztuk, van e teli rekesz F2:F151 

kritérium - "+" jel

Képlete:

=DARABHA(F2:F151;"+")

Eredmény:

c.     az I3-as cellában az összes beérkezett üveg számát!

Számoljuk össze az összes üveget a SZUM függvény segítségével.

SZUM - Az összes számot összeadja egy adott cellatartományban

Képlete:

=SZUM(szám1,[szám2],...)

szám1,[szám2],... : C2:E151 cellatartomány, a rekeszek száma

Képlete:

=SZUM(C2:E151)

Eredmény:

Üvegválogató 4. SZUM, HA

Az élelmiszerboltokban a nem visszaváltható üvegeket is összegyűjtik és újrahasznosítást végző üzembe szállítják. Az üvegeket – válogatás nélkül – 20 üveg tárolására alkalmas rekeszekbe gyűjtik és szállítják. Minden rekeszről feljegyezték, hogy hány fehér, zöld, illetve barna üveget tárol. A különböző boltokból beszállított rekeszek adatai állnak rendelkezésére az UTF-8 kódolású szallitolevel.txt állományban.

A táblázatban a következő adatok állnak rendelkezésére: a rekeszt a kilenc üzlet közül melyikből szállították a feldolgozó üzembe, a szállítás napja és az üvegek száma szín szerint. Az üzleteket sorszámuk azonosítja. A táblázatot 150 beérkezett rekesz adatainak feldolgozására készítse fel! A megoldás során végig vegye ezt figyelembe!

 4. A beérkezett, teljesen feltöltött rekeszek számát kell meghatároznia. Ehhez minden teli rekesz mellett az F oszlopban jelenítsen meg egy „+” jelet a cellában! Más esetben a cella üresen jelenjen meg.

 Egy rekeszbe 20 üveg fér. Nézzük meg, hogy üzletenként hány üveg van összesen. Összesen adatot a SZUM függvénnyel kaphatunk.

 SZUM - Az összes számot összeadja egy adott cellatartományban

Képlete:

=SZUM(szám1,[szám2],...)

szám1,[szám2],... :  az üvegek a C2:E2 cellatartományban vannak

Képlete:

Az összes üveg száma:   =SZUM(C2:E2)

  Vizsgáljuk meg, hogy a boltban levő összes üveg egyenlő e 20 darabbal. Ehhez a HA logikai függvényre van szükségünk.

HA - Ellenőrzi a feltétel megfelelőségét, és ha a megadott feltétel IGAZ, az egyik értéket adja vissza, ha HAMIS, akkor a másikat.

Képlete:

=HA(logikai vizsgálat (kötelező); érték, ha igaz ( kötelező); érték, ha hamis (nem kötelező)) 

 logikai vizsgálat - ha a boltban levő összes üveg - SZUM(C2:E2) = 20

érték, ha igaz - írja ki : "+"

érték, ha hamis - ne írjon ki semmit : " "

Képlete:

=HA(SZUM(C2:E2)=20;"+";"")

 Eredmény:

Vízkeménység 4. FKERES

Az ivóvíz keménységét az oldott kalcium- és magnéziumsók okozzák. A vízkeménységet német keménységi fokban (nk°) adják meg, melyet a táblázatban található mérési adatokból számolnak, és ez alapján kategóriákba sorolnak. 1 német keménységi fok 10 milligramm/liter kalcium-oxiddal egyenértékű kalcium- és magnéziumionnak felel meg.
Heves megye településeinek népességszáma és vízkeménységi mérési adatainak alsó és felső határértékei állnak rendelkezésére az UTF-8 kódolású vizforras.txt állományban.

4. Határozza meg az F2:F119 tartomány celláiban, hogy a településeken szolgáltatott ivóvíz melyik vízkeménységi kategóriába tartozik! A kategóriák alsó határértéke és megnevezése a H2:I6 tartományban található.

Keressük meg, hogy az E oszlopban szereplő vízkeménység melyik vízkeménységi kategóriába tartozik.

Ehhez az FKERES függvényt használjuk.

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 településekhez tartozó vízkeménység - E2

tábla : H2:I6, de le kell zárni F4-gyel, mert mindig ezt kell vizsgálnia : $H$2:$I$6

oszlop_szám2, mert a tábla 2. oszlopában van a vízkeménységi kategória

tartományban_keres  ha 1 értéket ad meg, visszatérési érték kereséséhez közelítő egyezést szeretne előírni, vagy a 0 értéket, ha pontos egyezést szeretné. Ha nem ad meg semmit, az alapértelmezett érték az 1, vagyis a közelítő egyezés. Közelítő egyezést szeretnénk, ezért nem írunk oda semmit.

Képlete:

=FKERES(E2;$H$2:$I$6;2)

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

Eredmény:

Üvegválogató 3. Adatbevitel

Az élelmiszerboltokban a nem visszaváltható üvegeket is összegyűjtik és újrahasznosítást végző üzembe szállítják. Az üvegeket – válogatás nélkül – 20 üveg tárolására alkalmas rekeszekbe gyűjtik és szállítják. Minden rekeszről feljegyezték, hogy hány fehér, zöld, illetve barna üveget tárol. A különböző boltokból beszállított rekeszek adatai állnak rendelkezésére az UTF-8 kódolású szallitolevel.txt állományban.

A táblázatban a következő adatok állnak rendelkezésére: a rekeszt a kilenc üzlet közül melyikből szállították a feldolgozó üzembe, a szállítás napja és az üvegek száma szín szerint. Az üzleteket sorszámuk azonosítja. A táblázatot 150 beérkezett rekesz adatainak feldolgozására készítse fel! A megoldás során végig vegye ezt figyelembe!

3. A H7:H15 tartomány celláiba írja be az üzletek sorszámát 1-től 9-ig!

Írjuk be az üzletek sorszámát 1-9-ig. Egyesével beírjuk, vagy ha az első kettőt kijelöljük, akkor a zöld négyzettel végig tudom húzni 9-ig a sorszámokat.

Eredmény:

Üvegválogató 2. Beszúrás

Az élelmiszerboltokban a nem visszaváltható üvegeket is összegyűjtik és újrahasznosítást végző üzembe szállítják. Az üvegeket – válogatás nélkül – 20 üveg tárolására alkalmas rekeszekbe gyűjtik és szállítják. Minden rekeszről feljegyezték, hogy hány fehér, zöld, illetve barna üveget tárol. A különböző boltokból beszállított rekeszek adatai állnak rendelkezésére az UTF-8 kódolású szallitolevel.txt állományban.

A táblázatban a következő adatok állnak rendelkezésére: a rekeszt a kilenc üzlet közül melyikből szállították a feldolgozó üzembe, a szállítás napja és az üvegek száma szín szerint. Az üzleteket sorszámuk azonosítja. A táblázatot 150 beérkezett rekesz adatainak feldolgozására készítse fel! A megoldás során végig vegye ezt figyelembe!

2. Az E oszlop után szúrjon be két új oszlopot!

Jelöljünk ki az E oszlop után 2 oszlopot - Jobb gomb - Beszúrás.

Eredmény:

Vízkeménység 2-3. KEREKÍTÉS, ÁTLAG

Az ivóvíz keménységét az oldott kalcium- és magnéziumsók okozzák. A vízkeménységet német keménységi fokban (nk°) adják meg, melyet a táblázatban található mérési adatokból számolnak, és ez alapján kategóriákba sorolnak. 1 német keménységi fok 10 milligramm/liter kalcium-oxiddal egyenértékű kalcium- és magnéziumionnak felel meg.
Heves megye településeinek népességszáma és vízkeménységi mérési adatainak alsó és felső határértékei állnak rendelkezésére az UTF-8 kódolású vizforras.txt állományban.

 2. A táblázatban a következő adatok állnak rendelkezésre: a települések neve, népessége (fő), a szolgáltatott víz keménységének alsó és felső mérési értéke kalcium-oxid egyenérték mg/l mértékegységben. A H1:I6 tartomány celláiban a vízkeménység szerinti határértékek és kategóriák találhatók.

3.  Az E2:E119 tartomány celláiban határozza meg a települések ivóvizének átlagos keménységét német keménységi fokban! A számítás során az alsó és felső mérési értékek átlagának tizedrészét kell meghatároznia, és függvény segítségével egy tizedesjegyre kerekítenie!

A számítás során az alsó és felső mérési értékek átlagának tizedrészét kell meghatároznia:

Átlagának, tehát 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:

=ÁTLAG(szám1....szám10)

szám1....szám10  F2:F100

Képlete:

=ÁTLAG(C2:D2)

Tizedrésze:

=ÁTLAG(C2;D2)/10

Függvény segítségével egy tizedesjegyre kerekítenie.

Kerekíteni a KEREKÍTÉS függvény segítségével fogunk.

KEREKÍTÉS - Egy számot adott számú számjegyre kerekít

 Képlete:

=KEREKÍTÉS(szám; hány_számjegy)

 szám - az előbb kiszámolt átlag : ÁTLAG(C2;D2)/10

hány_számjegy : 1

Képlete:

=KEREKÍTÉS(ÁTLAG(C2:D2)/10;1)

Másoljuk végig a jobb alsó sarokban levő kis zöld négyzet segítségével.

Eredmény:

Üvegválogató 1. Adatbevitel

Az élelmiszerboltokban a nem visszaváltható üvegeket is összegyűjtik és újrahasznosítást végző üzembe szállítják. Az üvegeket – válogatás nélkül – 20 üveg tárolására alkalmas rekeszekbe gyűjtik és szállítják. Minden rekeszről feljegyezték, hogy hány fehér, zöld, illetve barna üveget tárol. A különböző boltokból beszállított rekeszek adatai állnak rendelkezésére az UTF-8 kódolású szallitolevel.txt állományban.

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

A táblázatban a következő adatok állnak rendelkezésére: a rekeszt a kilenc üzlet közül melyikből szállították a feldolgozó üzembe, a szállítás napja és az üvegek száma szín szerint. Az üzleteket sorszámuk azonosítja. A táblázatot 150 beérkezett rekesz adatainak feldolgozására készítse fel! A megoldás során végig vegye ezt figyelembe!

Nyissunk meg egy üres Excel munkafüzetet.

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

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

Mentsük el.

Fájl - Mentés másként - Tallóz - Fájl-Típus : Excel munkafüzet - Fájl neve : valogato - Mentés.

Eredmény:

Internethasználók aránya 9. Diagram

Az internethasználók száma az egész világon rohamosan növekszik, van olyan ország, ahol már elérte a 100%-ot. Ebben a feladatban az egyes országok adatait kell összehasonlítania a 2015-ös és a 2016-os év adatai alapján.

Táblázatkezelő program segítségével oldja meg a következő feladatot.

A következő feladatokat a 2016-os adatokat tartalmazó munkalapon kell megoldania.

9. Ábrázolja oszlopdiagramon az internethasználók 2016-os arányát a Visegrádi országok („Czech Republic”, „Hungary”, „Poland”, „Slovakia”) esetében! A diagram elkészítéséhez a szükséges adatokat egy segédtáblázatba másolhatja. Végezze el az alábbi beállításokat!
        a.             A diagramon ne legyen jelmagyarázat!
        b.            A cím „Visegrádi négyek internethasználata” legyen!
        c.             Magyarország oszlopa sötétvörös, a többi oszlop zöld színű legyen!
        d.            Az oszlopok fölött jelenjen meg az internethasználók 2016-os aránya!
        e.             A diagram a H12:K24 tartományba essen!

Jelöljük ki a négy országot és a hozzájuk tartozó internethasználók 2016-os arányát.

Készítsük el a Diagramot.

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

  a.             A diagramon ne legyen jelmagyarázat!

Nincs rajta. Ha mégis, akkor szedjük ki.

Kattintsunk a Diagramra - Diagramtervezés - Diagram - összetevő hozzáadása - Jelmagyarázat - Nincs.

Eredmény:

b.            A cím „Visegrádi négyek internethasználata” legyen!

Kattintsunk duplán a "Diagramcímre", és írjuk át.

Eredmény:

c.             Magyarország oszlopa sötétvörös, a többi oszlop zöld színű legyen!

Kattintsunk az oszlopokra, majd a kitöltést változtassuk zöld színűre.

Jobb oldalt Adatsor formázása - Kitöltés és vonal - Kitöltés - Szín : zöld.

Kattintsunk csak a Magyarország oszlopra, és változtassuk sötétvörösre.

Jobb oldalt Adatsor formázása - Kitöltés és vonal - Kitöltés - Szín : sötétvörös.

Eredmény:

     d.            Az oszlopok fölött jelenjen meg az internethasználók 2016-os aránya!

Diagramtervezés - Diagram-összetevő hozzáadása - Adatfelirat - Értéknél, kívül. Mind a négy oszlopnál felül lesz az internethasználók aránya.

Eredmény:

e.             A diagram a H12:K24 tartományba essen!

Húzzuk a helyére a diagramunkat.

Eredmény: