46. óra: Táblázatkezelés: Szállás

Egy fiktív zarándokház elõre lefoglalt szálláshelyeinek listáját találja az Import/szallas.ods fájlban. (De le is töltheti a fájl nevére kattintva.)
Nyissa meg a fájlt, majd mentse az Export könyvtárába ugyanezen a néven! Az adatok a 2024. év április-május hónapjára vonatkoznak.

A fõ tábla nem nyilvánvaló adatainak jelentése:

• Egyházi ünnep: Azokban a napokban van ott adat, amelyek kiemelt egyházi ünnepek. (Pl. 'Pünkösd'.)
• Foglalt létszám: Egy szám, amely megmutatja, hogy arra a napra mekkora létszámnyi helyet foglaltak le elõzetesen.

A többi hiányzó adatot majd önnek kell meghatároznia a feladatok megoldásában.

A segédtáblák jelentése:

• M21 cella: a szállás maximális befogadóképessége. (Jelenleg 120, de úgy kell dolgoznia, hogy ha ezt megváltoztatjuk, az eredmények értéke ahhoz alkalmazkodjon.)
• L24:M29 tartomány: annak szöveges minõsítése, hogy a foglaltság mennyire teljes.
• L32:M33 tartomány: a szállás díja annak megfelelõen, hogy kiemelt napról van-e szó, vagy sem.

Oldja meg a tanult eszközökkel az alábbi feladatokat! Ügyeljen arra, hogy az eremények az adatok esetleges késõbbi módosítását kövessék! (Vagyis, ha valamit átírunk, akkor az eredmények azt kövessék)

(A feladatok megoldásához a lenyíló dobozokban segítséget kaphat, ha nem tudná az eddig tanultakkal megoldani.)

Megoldás segédoszlopokkal (megjelenik) ↓ (eltûnik) ↑

Bár az eredményt a D24 cellában várják, most segédszámításokat fogunk végezni a P oszlopban és attól jobbra, mert a feltétel, amit adtak - összetett. Három feltétel teljesülését kell vizsgálnunk:

16. A B oszlopban a 'Szo' szöveg szerepel-e?
17. A B oszlopban a 'V' szöveg szerepel-e?
18. A C oszlop tartalmaz-e valamit?

Ha bármelyikre 'IGEN' a válasz, akkor ez egy kiemelt nap. Ha mindháromra 'NEM' a válasz, akkor az adott nap hétköznap.
 
A három kérdésre a P, Q és R segédoszlopokban fogunk válasz kicsikarni.
 

1.1. képlet: A "Szo" szöveg van-e a B oszlopban? Álljon a P24-es cellára, indtsa el a függvényvarázslót. és kérje a HA függvényt!   Három kitöltendõ adatot kér a gép: Teszt: ide kell írni a feltételt; Akkorérték: mi legyen a cellában, ha a teszt igaz; Különbenérték: mi legyen, ha a teszt nem igaz.
A teszt (feltétel) most az, hogy a B24 cella tartalma "Szo", vagy nem. Akkorérték legyen 1 (igaz, számmal kifejezve).   Különbenérték legyen 0 (hamis, számmal kifejezve).
Hagyja jóvá a képletet, éa terjessze ki a képletet a P oszlop aljáig (a P84 celláig)!
1.2. képlet: A "V" szöveg van-e a B oszlopban?   A Q oszlopban kb. ugyanazt kell tennie, mint a P oszlopban, csak a teszt más egy kicsit: Most azt kell vizsgálni, hogy a B24 cella tartalma "V" vagy sem. (A többi marad.)
1.3. képlet: Van-e valami a C oszlopban? (R24 cella.)   A teszt a lényeges kérdés. Kiemelt nap, tehát az 1-est szeretnénk, ha a C24 cellában van valami - vagyis nem üres. Az üres cella tartalma "" (a két idõzõjel közt a semmi). A teszt tehát: C24<>"" (A '<>' a nemegyenlõ jele.)   (A kész képletet terjessze ki az R84 celláig.)
1.4. képlet: Az D oszlop képlete (D24 cella)   Ez is egy HA függvény. A teszt azt kell viszgálja, hogy a P, Q, R oszolpok cellának valamelyikében van-e 1. (Ezt praktikusan úgy lehet viszgálni, hogy az összeg pozitív.)   Teszt: P24+Q24+R24>0 (ha mind 0, akkor ez nem teljesül) Akkorérték: "kiemelt" Különbenérték: "hétköznap" (A kész képletet terjessze majd ki!)

 
Megjegyzés: A képlet 'egyben' is elkészíthetõ, ha a D24 cella tesztjében a VAGY függvénnyel összekapcsolja a P, Q, R oszlopokban alkalmazott három feltételt.

Megoldás (megjelenik) ↓ (eltûnik) ↑

Az eredményt az F24 cellában kell elkészítenünk. Álljon tehát erre a cellára.
 
A megoldást az FKERES függvény adja.
 

Az F24 cellában indítsa el a függvényvarázslót, majd keresse ki az FKERES függvényt. A függvénynek négy paramétere van: Keresésifeltétel: mi alapján fog eldõlni a keresés?   Mátrix: Itt kell megadni a keresõtáblát, ahol a keresés majd zajlik. (A tábla elsõ oszlopában.)   Index: miután a keresõtábla 1. oszlopában a keresés megtörtént, a keresõtábla hányadik oszlopában van a visszatétési érték? (Ezt kell itt megadni.)   Rendezett tartomány...: értéke 0 v. 1. A 0 pontos egyezéssel keres (az 1. oszlopban), az 1 laza illesztéssel. (Tól-ig határokkal, mint egy dolgozat értékelésénél.)
A 'Keresésifeltétel' kijelölése Nyomja le a tartomány-jelölõt ('vonatablak') és jelölje ki a D24 cellát! (Azt keressük a segédtáblában.)
A 'Mátrix' megadása (Nem szerencsés ez az elnevezés. Most jelöljük ki a segédtáblát, ahol a keresés történik. Annak 1. oszlopában.)   Nyomja le a tartományjelölõt, és jelölje ki az L35:M36 cellatartományt (a keresõtáblát)!
Az 'Index' és a 'Rendezett tartomány...'   Az index értéke 2, mert a második oszlopban van a visszatérési érték (az ár), a rendezett izé értéke 0, mert pontos egyezéssel keresünk (vagy kiemelt, vagy hétköznap; tól-ig keresés szóba sem jön).
Ha jól csinálta, az E24 cellában megjelenik a megfelelõ egységár.   Terjessze ki a képletet a teljes E oszlopra!

 
Megjegyzés: A feladat az INDEX-HOL.VAN függvénypárral is megoldható - de talán így egyszerûbb...

Megoldás (megjelenik) ↓ (eltûnik) ↑

Az eredményt a G24 cellában kell elkészítenünk. Álljon tehát erre a cellára!
 
A kérdésre a választ a E24 és az F24 cella szorzata adja.
 

A G24 cellába gépelje be az alábbi képletet:   =E24*F24   A jóváhagyás után (mielõtt kiterjesztené) még állítsuk be az ezres csoportosítást...
Az ezres csoportosítás A G24 cellán állva indítsa el a Formátum/cellák menüpontot (CTRL-1 gyorsbillentyûvel is lehet).   Válassza a Formátumok közül az ezres csoporosítású egész megjelenést jelképezõ '-1 235' lehetõséget!   Ha ezután kiterjeszti a képletet, egyben a formátumot is kiterjeszti.

Megoldás (megjelenik) ↓ (eltûnik) ↑

Álljon a H24 cellára! (Oda készítjük el a képletet.)
 
Elõzetes tudás: A táblázatkezelõben a százalékot egyszerû osztással képezzük; a részt osztjuk az egésszel. Ilyenkor egyszerû tizedes törtet kapunk, és csak a megjelenítését (formátumát) kérjük százalékos formátumban.
(A cella értéke marad a törtszám.)
 
A 'rész', amit osztunk az E24 cella, az 'egész', amivel osztjuk az M24 cella, de óvatosnak kell lennünk! Az M24 cellára való hivatkozásunkban a 24 védendõ, mert majd lefele fogjuk kiterjeszteni a képletet, és az 24 nem 'jöhet velünk' - az meg kell maradjon 24-nek!

A H24 cellába gépelje be az alábbi képletet (az M24-ben a '$' jel a 24-re hivatkozást rögzíti):   =E24/M$24   A jóváhagyás után (mielõtt kiterjesztené) még állítsuk be a százalékos megjelenítést...

A százalékos megjelenítés (formázással) A H24 cellán állva indítsa el a Formátum/cellák menüpontot (CTRL-1 gyorsbillentyûvel is lehet).   A Kategória oszlopban válassza a 'Százalék' pontot,   a Formátumok közül az egész százalékos megjelenést jelképezõ '-13%' lehetõséget! (Ez nem jelent valódi kerekítést, csak a tizedes vesszõ utáni rész nem fog látszani.)   Ha ezután kiterjeszti a képletet, egyben a formátumot is kiterjeszti.

Megoldás (megjelenik) ↓ (eltûnik) ↑

Az eredményt az I24 cellában kell elkészítenünk. Álljon tehát erre a cellára!
 
A megoldást az FKERES függvény adja. (Csak most laza, tól-ig illesztéssel.)
 

Az I24 cellában indítsa el a függvényvarázslót, majd keresse ki az FKERES függvényt. A függvénynek négy paramétere van: Keresésifeltétel: mi alapján fog eldõlni a keresés?   Mátrix: Itt kell megadni a keresõtáblát, ahol a keresés majd zajlik. (A tábla elsõ oszlopában.)   Index: miután a keresõtábla 1. oszlopában a keresés megtörtént, a keresõtábla hányadik oszlopában van a visszatétési érték? (Ezt kell itt megadni.)   Rendezett tartomány...: értéke 0 v. 1. A 0 pontos egyezéssel keres (az 1. oszlopban), az 1 laza illesztéssel. (Tól-ig határokkal, mint egy dolgozat értékelésénél.)
A 'Keresésifeltétel' kijelölése Nyomja le a tartomány-jelölõt ('vonatablak') és jelölje ki a H24 cellát! (Azt keressük a segédtáblában.)
A 'Mátrix' megadása (Nem szerencsés ez az elnevezés. Most jelöljük ki a segédtáblát, ahol a keresés történik. Annak 1. oszlopában.)   Nyomja le a tartományjelölõt, és jelölje ki az L28:M32 cellatartományt (a keresõtáblát)!
Az 'Index' és a 'Rendezett tartomány...'   Az index értéke 2, mert a második oszlopban van a visszatérési érték (az ár), a rendezett... értéke 1, mert 'laza' illesztéssel keresünk (pontos egyezésre nincs ilyenkor esély).
Ha jól csinálta, az I24 cellában megjelenik a megfelelõ minõsítés.   Terjessze ki a képletet a teljes I oszlopra!

Megoldás (megjelenik) ↓ (eltûnik) ↑

Álljon a J24 cellára! (Oda kerül a képlet.)
 
A szabad helyek száma nyilván az M24 és az E24 cella különbsége, de most is óvatosnak kell lennünk! Az M24 cellára való hivatkozásunkban a 24 védendõ, mert majd lefele fogjuk kiterjeszteni a képletet, és az 24 nem 'jöhet velünk' - az meg kell maradjon 24-nek!

A J24 cellába gépelje be az alábbi képletet (az M24-ben a '$' jel a 24-re hivatkozást rögzíti):   =M$24-E24   A jóváhagyás után terjessze ki a képletet a teljes J oszlopra (a J84 celláig).

Megoldás (megjelenik) ↓ (eltûnik) ↑

Az eredményt az N28 cellában készítjük el, aztán majd kiterjesztjük a többire. Álljon tehát az N28 cellára!
 
A megoldást az DARABTELI függvény adja. (Meg kell számolnunk, hányszor fordul elõ az M28 cella tartalma az I oszlopban.)
 

Az N28 cellában indítsa el a függvényvarázslót, majd keresse ki az DARABTELI függvényt!   A függvénynek két dolgot kell megadnunk: Tartomány: Hol kell megszámlálnunk?   Feltételek: Mit kell megszámlálnunk?
A 'Tartomány' kijelölése Nyomja le a tartomány-jelölõt ('vonatablak') és jelölje ki az I24:I84 cellatartományt! (Ott végezzük a megszámlálást.)   Megjegyzés: A 'gép' ügyesen dollározza a hivatkozást, ami így van jól.)
A 'Feltételek' megadása   Nyomja le a tartományjelölõt, és jelölje ki az M28 cellát (azt kell megszámlálnunk)!
Hagyja jóvá a képletet és terjessze ki a megfelelõ tartományra!

Megoldás segédoszloppal (megjelenik) ↓ (eltûnik) ↑

A megoldás alapötlete
 
Az érkezés és a távozás dátuma által leszûkítve a szabad helyek listáját (J oszlop), abban megkeressük a minimumot. Tehát hogy a kért idõben mennyi a szabad helyek minimális száma.
Ha ez a minimális szám nagyobb vagy egyenlõ a kérésben megadott létszámnál, akkor a kérés teljesíthetõ; egyébként nem.
  A T (segéd)oszlopba tegyünk 0-t, ha az adott nap nem esik az új kérés által megadott idõintervallumba, és 1-et, ha oda esik.
 

Képlet a T oszlopban   Álljon az T24-es cellára, indtsa el a függvényvarázslót. és kérje a HA függvényt!   Három kitöltendõ adatot kér a gép: Teszt: ide kell írni a feltételt; Akkorérték: mi legyen a cellában, ha a teszt igaz; Különbenérték: mi legyen, ha a teszt nem igaz.
A teszt összetett. Akkor kell igaz legyen, ha a megfelelõ sorban álló nap dátuma (A oszlop) nagyobb-egyenlõ a kérésben megadott érkezési dátumnál ÉS ugyanezen nap dátuma kisebb a távozás dátumánál.   A HA függvény Teszt mezõjében most a belsõ függvényvarázslót indítsa el (bal oldalt az fx gomb), majd kérje az ÉS függvényt!   (Közben figyelje meg, hogy alul a 'képlet' ablakban készül a képlet - a HA függvénybe ágyazott ÉS függvény!))
Az ÉS függvény paraméterezése   Az ÉS-nek logikai értékeket adhatunk meg tetszõleges számban - mi most két ilyet adunk meg.   Logikaiérték 1: A24>=M$39 (a nap dátuma nagyobb-egyenlõ az érkezés napjánál)   Logikaiérték 2: A24<M$40 (a nap dátuma kisebb a távozás napjánál)
A HA függvény folytatása   Lent a 'képlet' ablakban kattintson a HA szóra! Ekkor visszatér a HA függvény. (A tesztben az 'odavarázsolt' ÉS.)   Akkorérték: 1   Külöbenérték: 0   Hagyja jóvá a képletet, és terjessze ki a teljes T oszlopra (a T84 celláig).
Következik a feltételes minimum   Egy segédcellában fogluk lekérni az 1-esekkel jelölt dátumokra a szabad helyek számának minimumát.   Legyen ez a cella a T85. Álljon rá, indítsa el a függvényvarázslót, és kérje a MINHA függvényt!
A MINHA paraméterei   Mintartomány: Ahol a feltételes minimumot keressük   Tartomány 1: Ahol a feltétel van (párhuzamos kell legyen a 'Mintartomány'-nyal)   Feltétel 1: A 'Tartomány 1'-re vonatkozó feltétel.   (A 'Mintartomány'-nak csak azok az elemei jönnek szóba, amelyeknél 'Tartomány 1' elemei teljesítik a 'Feltétel 1'-et.) ((Vannak még lehetõségek, de az most nem kell.))
A Mintartomány   Nyomja le a tartományjelölõt ('vonatablak') és jelölje ki a J24:J84 tartományt!   (Ott keressük a minimumot - feltételesen.)
A Tartomány 1   A tartományjelölõvel jelölje ki a T24:T84 cellatartományt!   (Ott van a feltétel.)
A Feltétel 1   Értéke: 1.   (Hiszen csak azon napok vonatkozásában kérjük a minimumot, amelyeknél a párhuzamos T oszlopban 1 áll.)
Ha minden rendben, hagyja jóvá a képletet.   Az eredmény azt jeleníti meg, hogy az új (tervezett) foglalás idõntjaiban mennyi a szabad helyek számának minimuma.
És végül a HA függvény az L42 cellában   Indítsa el az L42 cellában a függvényvarázslót, és ott kérje a HA függvényt! A paraméterek: Teszt: M41<=T85 (a rendelt létszám nem több, mint ami rendelkezésre áll, vagyis üres)   Akkorérték: "szabad"   Különbenérték: "foglalt"
Próbálja ki a képlet mûködését úgy, hogy a tervezett rendelés adatait megváltoztatja.

Jó munkát!