
51. óra gyakorlat: Kertvendéglõ
Egy budai kertvendéglõben a
vendégek rendeléseit rögzítették a kocsma.ods
fájlban, az A32:G198 tartományban. Az adatok egy napra
vonatkoznak. (A nap vége felé járunk…)
A rendelés azonosítójának utolsó számjegye az asztal sorszáma, az
elõtte álló számjegyek az adott asztalhoz ülõ újabb vendégeket
(társaságokat) sorszámozzák.
Például a 14-es sorszámú
vendégcsapat aznap az elsõ társaság, aki a 4-es asztalhoz ült, a
24-es sorszámú a második olyan társaság, aki a 4-es asztalnál
ült.
|

|
Nyissa meg táblázatkezelõben a kocsma.ods
fájlt, mentse el az Export könyvtárába, majd oldja
meg az alábbi feladatokat!
(A fájlt az megtalálja az Import könyvtárában, vagy
letöltheti a fájl nevére kattintva a fentebbi linken.)
Ha segédszámításokat szeretne végezni, azokat az O oszlopban,
vagy attól jobbra tegye!
1.
feladat: Az I33:K48 tartományban a rendelhetõ áruk
adatai vannak. A D oszlopba írassa ki a rendelt áruk
mennyiségi egységét! ('üveg', 'dl', 0,03, stb.)
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
1.1.: Az FKERES függvény indítása
Álljon a kijelölõvel a D33-as cellára, indítsa el a
függvényvarázslót, és kérje az FKERES függvényt!
Az FKERES paramétereinek jelentése:
Keresésifeltétel: Mit keresünk?
(A terméket, pl. sör.)
Mátrix: Hol keressük? (A
termékeket tartalmazó segédtáblában, annak is az elsõ
oszlopában.)
index: Ha megtaláltuk az elsõ oszlopban, akkor
hányadik oszlop adatát kérjük ide?
(A második oszlop adatát kérjük, a
mennyiségi egységet.)
Rendezett...: Pontos egyezéssel keresünk az
elsõ oszlopban (0), vagy 'laza'tól-ig illszetéssel (1)?
|

|
1.2.: Az FKERES függvény paraméterei
Kijelöléssel, v. gépeléssel adja meg a paramétereket:
Keresésifeltétel: B33 (a termék
megnevezése)
Mátrix: $I$33:$J$48
(a termékek segédtáblázata)
Ügyeljen a 'dollározásra'! - mert majd lefele szeretné
kiterjeszteni ezt a képletet, de ez a hivatkozás
nem jöhet velünk lefele. (A sorindexek védendõk.)
index: 2 (mert a 2. oszlop
adatát kérjük)
Rendezett...: 0 (pontos
egyezést kérünk az áru nevére)
Ha kész, hagyja jóvá, és terjessze ki a D oszlopra!
|

|
1.3.: Az eredmény:
Ha minden jó, akkor a következõ eredményt kapja:
|

|
2.
feladat: Határozza meg az E oszlopban a I32:K48
tartomány adatai alapján, hogy mennyi az egyes rendelt tételek
egységára!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
2.1.: Az FKERES függvény paraméterezése
A feladat nagyon emlékeztet az 1. feladatra. A különbség
mindössze annyi, hogy az index nevû paraméter,
- mely azt mutatja meg, hogy a segédtábla hányadik oszlopát
kérjük eredményül - most nem 2, hanem 3.
(Hiszen az egységárakat tartalmazó harmadik oszlopot kérjük.
Álljon az E33 cellára, indítsa el az FKERES fv.-t, paraméterezze
azt az alábbiak szerint:
Keresésifeltétel: B33 (a termék
megnevezése)
Mátrix: $I$33:$K$48
(a termékek segédtáblázata)
Ügyeljen a 'dollározásra'! - tudjuk miért, és arra, hogy most a
K oszlop is benne kell legyen, mert annak az adait
kéri. (A 3. oszlopot, tehát legyen 3. oszlopa a 'Mátrix'-nak!)
index: 3 (mert a 3. oszlop
adatát kérjük)
Rendezett...: 0 (pontos
egyezést kérünk az áru nevére)
Ha kész, hagyja jóvá, és terjessze ki az E oszlopra!
|

|
2.2.: Az eredmény
Ha minden jó, az eredményt ez lesz:
|

|
3.
feladat: Adja meg az F oszlopban, mekkora az egyes
tételekért fizetendõ összeg!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
3.1.: A képlet 'kitalálása'
Egy-egy tételért fizetendõ összeg nyilván a mennyiség (C33) és
az egységár (E33) szorzata.
Álljon tehát az F33 cellára, és gépelje be az alábbi
képletet:
=C33*E33
Jóváhagyás, majd kiterjesztés!
|

|
3.2.: Az eredmény
Ha minden jó, az eredményt ez lesz: |

|
4.
feladat: A rendelés azonosítója alapján határozza meg, melyik
rendelés melyik asztalnál történt! (Az asztal száma a rendelésazonosító
utolsó számjegye.)
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
4.1.: A rendelésazonosító utolsó
számjegye
Egy lehetõség, hogy lekérjük a szám (rendelésazon.) 10-zel való
osztási maradékát. Ez nyilván az utolsó számjegy lesz.
A függvény, ami ezt megteszi, a MARADÉK.
Álljon a G33 cellára, és kérje a MARADÉK függvényt!
A függvény paraméterei:
osztandó: A33 (a rendelés azonosítója)
osztó: 10 (amivel az osztályi maradékot kérjük)
Ha minden rendben, hagyja jóvá, és terjessze ki az F
oszlopra!
|

|
4.2.: Az eredmény
Ha minden jó, az eredményt ez lesz: |

|
5.
feladat: Az L33:L48 tartományban számoltassa meg,
hogy összesen hány tételt rendeltek az adott napon az egyes
árufajtákból!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
5.1.: A megfelelõ függvény kitalálása
Elsõ gondolat talán az, hogy ez egy megszámlálás,de
valójában feltételes összeadás. Egy-egy rendelésben
ugyanis nem mindig egy tételt kértek, hanem a C
oszlopban rögzített darabszámút.
Így ezeket a darabszámokat kell összeadnunk a rendelt tétel
fajtája szerint megkölünböztetve.
Az erre alkalmas fv.: a SZUMHATÖBB (feltételes összeadás)
|

|
5.2.: A SZUMHATÖBB fv. és paramétereinek
jelentése
Álljon az L33 cellára, és indítsa el a SZUMHATÖBB
fvüggvényt!
A függvény paraméterei:
Összegzendõtartomány: az a tartomány, amit
összegezni kell - persze feltételesen;
Tartomány1: az 1. feltételt tartalmazó
tartomány;
Feltétel1: az 1. feltétel maga.
Lehetnének további feltételeink is, (ezért SZUMHATÖBB),
de most nincsenek. Csak egy van.
|

|
5.3.: A SZUMHATÖBB paramétereinek
beállítása
Összegzendõtartomány: C$33:C$198 (a
darabszámok)
Ügyeljen a dollározásra! - bár ezt vélhetõleg a 'gép' jó
csinálja.
Tartomány1: B$33:B$198 (az áru fajtája)
A dollározásra vonatkozó megjegyzés ugyanaz.
Feltétel1: I33 (az adott áru)
Hagyja jóvá a képletet, és terjessze ki az L33:L48
tartományra! |

|
5.4.: Az eredmény
Ha minden jól ment, az eredmény ez lesz:
|

|
6. feladat: Az M33:M48 tartományban adja meg,
hogy az egyes árufajtákból kiadásából mennyi volt az összes bevétel az
adott napon!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
6.1.: A képlet kitalálása és
megvalósítása
A feladat erõsen emlékeztet az 5. feladatra. Az eltérés
mindössze annyi, hogy a feltételesen összegzendõ tartomány az F33:F198
(a fizetendõ)
Álljon tehát az M33 cellára, indítsa el a SZUMHATÖBB
fv.-t, és állítsa be a paramétereit:
Összegzendõtartomány: F$33:F$198 (a
darabszámok)
Ügyeljen a dollározásra! - bár ezt vélhetõleg a 'gép' jó
csinálja.
Tartomány1: B$33:B$198 (az áru fajtája)
A dollározásra vonatkozó megjegyzés ugyanaz.
Feltétel1: I33 (az adott áru)
Hagyja jóvá a képletet, és terjessze ki az L33:L48
tartományra!
|

|
6.2.: Az eredmény
Vélhetõleg ez lesz:
|

|
7. feladat: A J52:J86 tartományban
rögzítették, ha egy társaság távozott. (A megfelelõ cellában IGEN
szerepel.) Ezen rendelésekre, de csak ezekre állapítsa meg a K44:K78
tartományban a fizetendõ összeget! Ha a rendelésazonosítóhoz tartozó
társaság még nem távozott (a J oszlopban NEM szerepel), akkor
itt, a Fizetendõ-nél ne jelenjen meg semmi!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
7.1.: A képlet kitalálása
A képletet a K52 cellában kell elkészíteni.
A képlet egyrészt el kell döntse, hogy 'IGEN' van-e a J52
cellában vagy sem. Ezt egy HA fv. megoldja.
Ha pedig 'IGEN' van ott, akkor össze kell adni a megfelelõ
rendelések egyes tételeinek Fizetendõ összegét (F
oszlop). Ez egy SZUMHATÖBB fv.-nyel megoldható. (Ez lesz a belsõ
fv.)
Álljon a K52 cellára, és kérje a HA függvényt!
A HA fv. paramétereinek jelentése:
Teszt: valami eldönthetõ feltétel;
Akkorérték: ez lesz az eredmény, ha a teszt
igaz;
Különbenérték: ez az eredmény, ha a teszt hamis
|

|
7.2.: A HA függvény paramétereinek
beállítása
A paraméterekek értéke:
Teszt: J52="IGEN" (a vendég tegát már
távozott);
Akkorérték: Ide kerül majd a SZUMHATÖBB ezt
még hagyjuk!
Különbenérték: "" (két egymáshoz tapadó
idézõjel - ez jelenti a semmit - ha vendég még nem távozott)
Végül nyomja meg az Akkorérték melletti
függvényvarázslót! (Ide varázsoljuk be a SZUMHATÖBB fv.-t.)
(Közben érdemes ránéznie az alsó keretre, ott alakul ki a teljes
képlet.)
|

|
7.3.: A belsõ SZUMHATÖBB fv. paraméterei
Ha elindította a SZUMHATÖBB (belsõ) fv.-t, a feltételes
összegzésre, nézzük a paramétereit:
Összegzendõtartomány: F$33:F$198 (az egyes
tételek Fizetendõ összege, mely rendelésenként összeadandó);
Tartomány1: A$33:A$198 (ahol a feltételek
vannak, vagyis a tételek rendelésazonosítója);
Feltétel1: I52 (a feltétel, vagyis a tételhez
tartozó rendelés, vagy számla azonosítója).
Jóváhagyás, kiterjesztés...
|

|
7.4.: Az eredmény
Ez kéne legyen: |

|
8. feladat: Az L52:L86 tartományban a
felszolgáló rögzítette, hogy a társaság mennyi pénzt hagyott az asztalon
a távozás után. Határozza meg az M44:M78 tartományban, hogy az
összeg fedezi-e a fogyasztást, vagy sem! Ha igen, jelenjen meg a
cellában egy 'pipa' (✔), ha nem, akkor egy felkiáltójel (!). Ha a
társaság még nem távozott, nem jelenjen meg semmi!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
8.1.: Döntenünk kell. Elõször arról, hogy
távozott-e a társaság
Mindenekelõtt 'vegyen magához' egy 'pipa' (✔) karaktert - mert
azt a függvényvarázslóban már nem tudja elõszedni. Jelölje ki -
pl. itt, és CTRL-C. (Vagy a táblázat feladatából is kiszedheti.
Amúgy az OpoenSymbols betûkészletben van.)
Álljon az M52 cellára,indítsa el a HA függvényt, és
adja meg a paramétereit:
Teszt: J52="IGEN" (tehát a vendég már
távozott);
Akkorérték: Ide kerül az 'elég-e' a pénz
vizsgálata - ezt majd késõbb...
Különbenérték: "" (ha még nem távozott, akkor a
cellába a 'semmi' kerüljön).
Ha mindez rendben, nyomja meg az Akkorérték melletti
(belsõ) függvényvarázslót és kérjen egy újabb (belsõ) HA fv.-t!
|

|
8.2.: A belsõ HA fv., mely eldönti,
elég-e az otthagyott pénz
A belsõ HA függvény paraméterei:
Teszt: K52<=L52 (a 'Fizetendõ'-t fedezi az
'Ennyit adott');
Akkorérték: "✔" (vagyis
rendben);
Különbenérték: "!" (gond van).
Jóváhagyás, kiterjesztés...
(Ügyeljen arra, hogy a kiterjesztés most nem megy duplja
kattintással, mert az elsõ szomszédos üres cellánál az el fog
akadni. Húzza végig!)
|

|
8.3.: Az eredmény
Ha minden szép és drága, akkor így lesz:
|

|
9. feladat: Az Asztalok munkalap B2:B10 celláiban
határozza meg, hogy egy-egy asztalnál hány társaság fogyasztott!
(Mennyire volt 'népszerû' az az asztal.)
Segítség: Egy asztal esetében kérje a
legnagyobb rendelés-azonosítót, mely ahhoz az asztalhoz tartozik, és
annak a tízes helyiértéken álló számjegye a keresett érték.
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
9.1.: Elõször az 'Asztalok' munkalap O
segédoszlopában lekérjük az adott asztalhoz tartozó legnagyobb
rendelésazonosítót
Ez egy MAXHA függvény lesz. Álljon az 'Asztalok' munkalap O2
cellájára, és kérje a MAXHA függvényt!
(A függvény szerkezete hasonló a SZUMHATÖBB-höz, csak nem
összead, hanem maximalizál - feltételesen.)
A MAXHA fv. paraméterei:
Maxtartomány: ahol a maximumot keressük;
Tartomány 1: ahol a szûrõfeltétel van;
Feltétel 1: a feltétel maga (csak azok
vonatkozásában nézi a fv. a maxximumot a Maxtartományban,
akik ezt a feltételt a Tartomány1ben
teljesítik.
|

|
9.2.: A MAXHA függvény paraméterei
Maxtartomány: $Rendelések.A$33:A$198 (a
rendelésazonosítók)
(át kell mennie a 'Rendelések' munkalapra kijelölnie az A33:A198
tartományt. Ügyeljen a dollározásra! - ,ert a képletet
majd lefele kell kiterjesztenünk);
Tartomány 1: $Rendelések.G$33:G$198 (az
tételhez tartozó asztal sorszáma - ami a szûrõfeltételünket
tartalmazza);
Feltétel 1: A2 (az 'Asztalok' munkalapon, ahol
vagyunk - ez a szûrõfeltétel).
Ha kész, jóváhagyás, kiterjesztés az O10-es celláig.
|

|
9.3.: Az eredmény
Ha minden rendben, ezt kapja:
(Van ott egy 0 is, ez azt jelenti, hogy a 6. asztalhoz egész nap
senki sem ült. Nem baj!)
|

|
9.4.: Az 'Asztalok' munkalapon az egyes
asztalokat hányszor vették igénybe?
A képletet az 'Asztalok' munkalap B2 cellájában
készítjük el.
A kérdésre a választ az O oszlopban található
rendelésazonosító nagysága adja, pontosabban az O oszlop
számát meg kell fosztanunk az egyesek helyiértékén álló
számjegyétõl. (Pl. a 7. asztaltnál ott 87 áll, tehát a 7.
asztalt 8-szor foglalták el.)
|
|
9.5.: AZ O oszlop megfelelõ
számának megfosztása az egyesek helyiértékén álló számjegyétõl
Ezt legegyszerûbben úgy tehetjük meg, hogy elosztjuk
10-zel, és az eredményt lefele kerekítjük.
Álljon a B2 cellára, és kérje a
KEREK.LE függvényt!
A KEREK.LE paraméterei:
Szám: O2/10
Darab: 0 (vagyis 0 db tizedesjegyre kerekítse
lefele).
Jóváhagyás, kiterjesztés a B10 celláig...
|

|
9.6.: Az eredmény
Az eredmény remélhetõleg ez lesz:
|

|
10. feladat: Az Asztalok munkalap C2:C10 cellában adja
meg, mennyi az egyes asztalokra esõ összes fogyasztás értéke!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
10.1.: A SZUMHATÖBB függvény...
Össze kell adnunk az egyes tételek Fizetendõ oszlopát az Asztal
sorszáma oszlopban található bontásban. Ezt már ismerik:
SZUMHATÖBB
Álljon az 'Asztalok' munkalap C2 cellájára, és kérje a
SZUMHATÖBB függvényt! Paraméterei:
Összegzendõtartomány: $Rendelések.F$33:F$198
Tartomány 1: $Rendelések.G$33:G$198
Feltétel 1: A2
Jóváhagyás, kiterjesztés... (esetleg ezres csoportosítás
beállítása - szebb úgy!)
|

|
10.2.: Az eredmény
Az eredmény:
|

|
11. feladat: Az 'Asztalok' munkalapra készítsen
szalagdiagramot az egyes asztalok leterheltségérõl – vagyis arról, hogy
hány társaság ült oda a nap folyamán!
A diagram címe: „Az asztalok forgalma” legyen! A sávok sárga színûek
legyenek, narancssárga vékony szegéllyel! A legforgalmasabb asztal (vagy
asztalok) színe piros legyen!
A diagramhoz ne tartozzon jelmagyarázat! A tengelyeken jelenjen meg az
asztal sorszáma, az értéktengelyen a skála beosztása egyesével legyen!
Az elkészítés egy kicsit részletesebb leírása: (megjelenik)
↓ (eltûnik)
↑
11.1.: A diagram adatforrásának
kijelölése
Jelölje ki az 'Aztalok' munkalapon az A1:B10 cellatartományt!
(A fejléc is jó, ha benne van.)
Ezután indítsa el a diagram-varázslót! (Pl. Beszúrás/Diagram.)
|

|
Eddig jutottam... A többit a fantáziájukra bízom
- egyelõre.
|
MENNI FOG - csak csinálják!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Jó munkát!





