Vízkeménység 7. Diagramok

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.

7. Készítsen kördiagramot az ivóvíz-kategóriánként meghatározott települések számából a következő beállításokkal!
        a.            A diagramot a munkalap 9. sora alá és a G-M oszlopok szélességében helyezze el!
        b.            A diagram címét és a jelmagyarázatát a minta alapján alakítsa ki!
        c.             Jelenítse meg az adatértékeket is a körcikkek belsejében! (Teljes értékű megoldás, ha
                        a nulla érték felirata nem, vagy máshol jelenik meg.)
        d.            A legnagyobb körcikk színe legyen világoszöld!
        e.             A cím és az adatfeliratok legyenek Arial (Nimbus Sans) betűtípusúak és
                        13 pontos betűméretűek!

Minta:

Jelöljük ki az I2:J6 táblázatrészletet. 

Beszúrás - Ajánlott diagramok - Minden diagram - Kör - Az első kört kiválasztjuk - OK.

a.            A diagramot a munkalap 9. sora alá és a G-M oszlopok szélességében helyezze el!

Igazítsuk a diagramot.

Eredmény:

b.            A diagram címét és a jelmagyarázatát a minta alapján alakítsa ki!

Kattintsunk duplán a Diagramcímre és változtassuk meg a Minta szerint.

"A települések aránya az ivóvíz keménysége szerint"

A jelmagyarázaton nem kell változtatni.

Eredmény:

   c.             Jelenítse meg az adatértékeket is a körcikkek belsejében! (Teljes értékű megoldás, ha
                        a nulla érték felirata nem, vagy máshol jelenik meg.)

Kattintsunk a diagramra. 

Diagramtervezés - Diagram-összetevő hozzáadása - Adatfeliratok középen - Kattintsunk külön a "0" adatfeliratra - Diagramtervezés - Diagram-összetevő hozzáadása - Adatfeliratok értéknél kívül.

Eredmény:

d.            A legnagyobb körcikk színe legyen világoszöld!

Változtassuk meg.

Kattintsunk a legnagyobb körcikkre - Jobb oldalt megjelenik a Diagram varázsló - Adat formázása - Kitöltés és vonal - Kitöltés - Egyszínű kitöltés - Szeletenként változó színek - Világoszöld.

Eredmény:

      e.             A cím és az adatfeliratok legyenek Arial (Nimbus Sans) betűtípusúak és
                        13 pontos betűméretűek!

Jelöljük ki a címet: Kattintsunk rá egyszer.

Kezdőlap - Betűtípus - Arial - Betűméret - 13.

Kattintsunk egyszer az egyik adatfeliratra, majd ismételjük meg az előző mozzanatot.

Kezdőlap - Betűtípus - Arial - Betűméret - 13.

Eredmény:

Üvegválogató 9. KEREK.FEL

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!

9. A J17:L17 tartomány celláiban számítsa ki függvény segítségével, hogy színenként hány rekeszre van szükség az üvegek tárolásához!

Mivel 1 üvegnél is 1 rekeszre, és 20 üvegnél is 1 rekeszre van szükség, ezért a KEREK.FEL függvényt fogjuk használni.

KEREK.FEL - Egy számot felfelé, a nullától távolabbra kerekít

Képlete:

=KEREK.FEL(szám; hány_számjegy)

Szám: Az összes fehér üveget elosszuk 20-szal, mert 20 fér egy rekeszbe J16/20

hány_számjegy :  0, mert teljes rekeszre van szükség

Képlete:

 =KEREK.FEL(J16/20;0) 

 Másoljuk végig a barna üvegekig.

Eredmény:

Üvegválogató 8. 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!

8. Az I16:L16 tartomány celláiban összesítse, hogy az üzletekből összesen hány rekesz és üvegtípusonként hány üveg érkezett!

Összesítéshez használjuk a SZUM függvényt.

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

Képlete:

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

szám1,[szám2],... : Üzletenként kiszámolt rekeszek és üvegek száma I7:I15 stb...

Képlete:

=SZUM(I7:I15)

Másoljuk végig a képletet L16-ig.

Eredmény:

Vízkeménység 6. MAX, INDEX, HOL.VAN

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.

6. Mekkora Heves megyében a vezetékes víz legnagyobb keménysége? Melyik településen van ez? A J8-as cellában az értéket és a K8 cellában a település nevét határozza meg!

A legnagyobb keménységet a MAX függvénnyel határozzuk meg.

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  -  E2:E119 cellatartományon beül vizsgálja meg, melyik a legnagyobb vízkeménység

Képlete:

=MAX(E2:E119)

Eredmény:

Melyik településen van ez?

Keressük meg a HOL.VAN függvénnyel, hogy melyik településhez tartozik a 28-as vízkeménység.

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 : J8-as cellába beíratott legnagyobb keménység

tábla : az arányok : E2:E119  - a vízkeménységek meghatározása településenként

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

Képlete:

=HOL.VAN(J8;E2:E119;0)

Ha megvan a helye, akkor jöhet az INDEX függvény.

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 : ahol a települések fel vannak sorolva - A2:A119 cellatartomány

sor_szám : amit az előbb beírtunk képletet : HOL.VAN(J8;E2:E119;0)

Képlete:

=INDEX(A2:A119;HOL.VAN(J8;E2:E119;0);1)

Eredmény:

Üvegválogató 7. SZUMHATÖBB

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!

7. A J7:L15 tartomány celláiban határozza meg, hogy az üzletekből üvegtípusonként hány darab üveg érkezett az újrahasznosító üzembe!

Ehhez a SZUMHATÖBB függvényt fogjuk végigmásolni.

 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 fehér, majd a zöld és a barna üvegek : C$2:C$151 csak a sort zárjuk, hogy tudjuk másolni

kritériumtartomány1 az üzletek számai, F4-gyel le kell zárni, mert másolásnál nem szabad változniuk. $A$2:$A$151

kritérium1 az üzlet száma, amelyiknek éppen számolja az üvegeit. $H7, majd a következő üzlet. Az oszlopot le kell zárni, mivel vízszintes másolásnál nem szabad, hogy megváltozzon.

Képlete:

=SZUMHATÖBB(C$2:C$151;$A$2:$A$151;$H7)

Eredmény:

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