A helyiségek szintje és ajtószáma kiolvasható az ID oszlopból (Típus/Szint-Ajtó), ám mivel a tartalom nem ezekkel kezdődik, nem lehet ezek szerint sorba rendezni – ehhez előbb fel kell darabolni az ID-t. (Talán jobb mindhárom adatot kibontani, így pl. bármelyik alapján lehet szűrni, rendezni.)

Új oszlopok

1. Hozzon létre három új üres oszlopot: jelölje ki pl. az A–C oszlopokat, majd jobb gomb, és Beszúrás / Insert.
2. A fejléc-sorba írja be a mezőneveket: Típus, Szint, Ajtó.
3. A Típus a bal oldali karakter:
   =BAL(@ID) ⇄ =LEFT(@ID)
4. A Szint a 3. karaktertől két karakter (szám):
   =KÖZÉP(@ID;3;2)+0 ⇄ =MID(@ID;3;2)+0
5. Az Ajtó a jobb oldali két karakter (szám):
   =JOBB(@ID;2)+0 ⇄ =RIGHT(@ID;2)+0
6. Jelölje ki a három képletet tartalmazó cellát, majd kattintson duplán a kijelölés jobb alsó sarkában a fogópontra, hogy az „autokitöltés” feltöltse a többi sort is.
7. Ezek után már működik a sorba rendezés: használhatja pl. az Adatok ⇄ Data sávmenün az Rendezés ⇄ Sort parancsot.

---

A képletek eredménye alapesetben szöveg, így ha számokként szeretnénk kezelni őket, akkor konvertálni kell – érdemes viszont gondolni arra, hogy számként nem értelmezhető karakterek esetén hibaüzenetet kaphatunk). Ennek legegyszerűbb módja, ha pl. hozzáadunk nullát, de létezik erre függvény is.

• Az =ÉRTÉK() ⇄ =VALUE() függvény a számként értelmezhető szöveget számmá alakítja.
• Ellentéte a =SZÖVEG() ⇄ =TEXT() függvény, amely számértéket szöveggé konvertál, az egyéni számformázásnál használatos formátumkód szerinti megjelítésben.
• Az ID oszlop tehát akár újra összeállítható az előbbi három oszlop kombinálásával:
  =@Típus&"/"&TEXT(@Szint;"00")&"-"&@TEXT(@Ajtó;"00").

Kategóriák – Szintek

Az alábbi megoldáshoz először létre kell hozni a szintek listáját – rendezzük ezt most víszszintesen.

1. Külön területen (AB3) írja be egy cellába a 0 értéket.
2. Az előbbi cella mellé (AC3) írja be az 1 értéket.
3. Jelölje ki a két cellát, és a jobb alsó sarokban lévő fogópont húzásával töltse fel a cellákat 7-ig
   – a két első érték alapján a program folytatja a számtani sorozatot.

Az alábbi megoldás feltételezi, hogy létezik a Szint oszlop (ennek megoldása a szöveg kezelésénél).

Összegzés

1. A 0 szint feletti cellába (AB1) írja be a képletet:
   =COUNTIFS(Szint; AB3)
   – a szint-kritérium a Szintek lista első elemére (AB3) mutató relatív hivatkozás,
   – a Szint célszerűen abszolút hivatkozás (oszlopa szerint kötött kell legyen), hiszen a képletet vízszintesen másoljuk – de elnevezett tartományra hivatkozva ez automatikusan teljesül.
   
2. Másolja végig a szintszámok felett a képletet – pl. húzza oldalt a cella jobb alsó sarkában lévő fogópontot.

Ellenőrzésképpen jelölje ki a darabszámokat – a láblécben leolvasható összeg meg kell egyezzen az össz helyiségszámmal.

A szintek listája az előbbi feladat révén már rendelkezésre áll, így elég a helyiség kategóriák listáját előállítani.

Kategóriák – KatKódok

1. Másolja át a Helyiség kategória kód oszlopot egy külön területre (AA oszlop).
2. Az összes cella kijelölése mellett válassza az Adatok ⇄ Data  sávmenün az Ismétlődések eltávolítása ⇄ Remove Duplicates parancsot.
3. Ellenőrizze, hogy a megjelenő ablakban az Adatok fejlécet tartalmaznak ⇄ My data has headers be van pipálva, majd OK.
4. A másolat helyén létrejön a helyiség kategória kódok ismétlődésmentes listája (unique list).
   E lista sajnos nem frissül, így ha később új kategóriák kerülnek a táblázatba, itt nem fognak automatikusan megjelenni.

Összegzés

1. A területek tartományának bal felső cellájába (AB4) írja be a képletet:
   =SUMIFS(Terület; Szint; AB$3; HelyiségKatKód; $AA4))
   – a szint-kritérium a Szintek lista első elemére (AB$3) mutató vegyes hivatkozás,
   – a kód-kritérium a KatKódok lista első elemére ($AA4 mutató relatív hivatkozás),
   – a HelyiségKatKód, a Szint és a  Terület célszerűen abszolút hivatkozások (hogy a képlet függőleges másolásakor ne mozduljon el) – de elnevezett tartományok esetén ez automatikusan teljesül.
2. A képlet végigmásolható a teljes tartományon (AB4:AI16).

Ellenőrzésképpen jelölje ki az összegeket – a láblécben leolvasható összeg meg kell egyezzen a Terület oszlop összegével.

Ha az elemtípusok listája bővülhet, érdemes azt dinamikusan kialakítani.

A dinamikus függvények csak az újabb program-verziókban működnek, és mivel eredményük tipikusan több cellát is megtölt, figyelni kell arra, hogy a képletet tartalmazó cellák alatt mindig legyen elég üres hely, különben csak a #KITÖLTÉS! ⇄ #SPILL! hibaüzenet jelenik meg.

Táblázat definiálása – HsgLista

Az egyedi lista egyszerűbben létrehozható, ha csak a lehetséges értékek tartományát adjuk meg paraméterként (a teljes oszlopra való hivatkozás nyilván tartalmazza a fejlécet és az üres sorokat is) – célszerűbb ezért az adattáblát táblázatként definiálni.

1. Győződjön meg róla, hogy az aktív cella a helyiségkönyv tábla területén található
   – a táblázat automatikusan az első üres sorig illetve oszlopig fog terjedni.
   
2. A Beszúrás ⇄ Insert sávmenü Táblázat ⇄ Table parancsával (Ctrl+R) definiálja a táblázatot.
3. A Táblázattervezés ⇄ Table Design sávmenün nevezze át a táblát (HsgLista), és válassza ki annak stílusát.

Kategóriák – kritérium listák

1. Törölje a Szintek lista elemeit (AB3:AI3), és a KatKód lista elemeit (AA4:AA16).
2. A KatKódok lista első elemeként (AA4) írja be az alábbi dinamikus képletet:
   =SORT(UNIQUE(HsgLista[Helyiség kategória kód]))
   – a Helyiség Kategória Kód a definiált HsgLista tábla azonos nevű oszlopára mutató hivatkozás.
3. Definiálja (Ctrl+F3) a listát KatKódok néven (=helyiséglista!$AA$4#).
   
4. A Szintek lista első elemeként (AB3) írja be az alábbi dinamikus képletet:
   =TRANSPOSE(SORT(UNIQUE(HsgList[Szint])))
   – a Szint a definiált HsgLista tábla azonos nevű oszlopára mutató hivatkozás.
5. Definiálja (Ctrl+F3) a listát Szintek néven (=helyiséglista!$AB$3#).

Összegzés

1. A bal felső elem kivételével törölje a területek képleteit.
2. A bal felső elem (AB4) képletet módosítsa egy-egy „#” hozzáadásával (a kritériumok után írt „#” azt jelzi, hogy a képlet a dinamikus tartalom minden cellájára vonatkozik, így a képlet automatikusan kitölti az eredmények megjelenítéséhez szükséges tartományt):
   =SUMIFS(Terület; Szint; AB$3#; HelyiségKatKód; $AA4#), vagy nevekkel:
   =SUMIFS(Terület; Szint; Szintek; HelyiségKatKód; KatKódok).
3. Definiálja (Ctrl+F3) a kapott eredményeket Területek néven (=helyiséglista!$AB$4#).

Ellenőrzésképpen összegezze az így besorolt területeket (=SUM(AB4#)), és vesse össze az összterülettel (=SUM(Terület)).

A keresett arány meghatározásához az oktatótermek számát osztani kell az épület helyiségeinek számával.

1. Külön területen (Z20) írja be egy cellába az 1 értéket.
2. Az előbbi cella alá (Z21) írja be a *Oktatóterem szöveget.
3. Az alatta lévő cellában (Z22) számolja ki az oktatási termek arányát:
   =COUNTIFS(Szint; Z20; HelyiségNév; Z21) / COUNTIFS(Szint; Z20)
4. Az eredményt formázza százalék formátumra.

---

Ha másik szintre is kíváncsiak vagyunk, értelemszerűen módosítani kell a szint kritériumot (Z20). Ez esetben ugyan csak számokat kell beírni, ám a bevitel könnyítésére, és az elgépelések elkerülésére ekkor is érdemes lehet egy listát megadni, ahonnan az értékek kiválaszthatók.

Használja az Adatok ⇄ Data sávmenün az Adatok érvényesítése ⇄ Data Validation parancsot: válassza ki a Beállítások ⇄ Settings lap Megengedve ⇄ Allow listájában a Lista ⇄ List elemet, majd adja meg a lista tartalmát (azaz a kiválasztható elemeket):
– direkt felsorolásként: 0; 1; 2; 3; 4; 5; 6; 7,
– vagy hivatkozásként: =$AB$3#,
– vagy elnevezett tartományként: =Szintek.

A megoldáshoz minden helyiséget be kell sorolni a Hasznos vagy az Egyéb kategóriába.

---

Ez eldönthető a =HA() ⇄ =IF() függvénnyel is: pl. ha az első három betű "Ala", "Int", "Okt" vagy "Spe", akkor Hasznos, különben Egyéb. Mégsem célszerű ez a megoldás, egyrészt mert a képlet elég hosszú lesz a négy feltétel miatt (ezért utólag nehezen áttekinthető, javítható, bővíthető), másrészt nehezen dokumentálható, hiszen normál esetben a képlet nem látszik, csak az eredmény.

---

KatKód	Hasznos-e
Alap1	Hasznos
Alap2	Hasznos
Alap3	Hasznos
Egyéb	Egyéb
Gép1	Egyéb
Int1	Hasznos
Int2	Hasznos
Int3	Hasznos
Int4	Hasznos
Kieg1	Egyéb
Kieg2	Egyéb
Okt	Hasznos
Spec	Hasznos

Kategóriák – Hasznos-e

Érdemes egy összerendelési táblázatot készíteni, ahol az első oszlopban a Helyiség kategória kódok egyedi listája van, mellette pedig a besorolás, ez könnyen átlátható, dokumentálható, módosítható.

1. Egy külön területen készítse el a Helyiség kategória kódok ismétlődésmentes listáját,
   – ha megvan a korábbi feladatból, természetesen az is használható (AA4).
2. Egy új oszlopban minden sorba írja be, hogy a Hasznos vagy az Egyéb kategóriába tartozik (nem kell közvetlenül egymás mellé kerülniük).
3. Definiálja ezt a listát Hasznos_e néven.

Az összerendelési tábla alapján már csoportba sorolhatók a helyiségek.

1. A helyiséglista (azaz a HsegLista táblázat) jobb szélén (V) egy új oszlopot nevezzen el Hasznosságnak.
   
2. Sorolja be a helyiségeket („autokitöltéssel”):
   =XLOOKUP(@HelyiségKatKód; KatKódok; Hasznos_e), vagy táblázatként:
   =XLOOKUP([@[Helyiség kategória kód]]; KatKódok; Hasznos_e).
   Alternatív megoldás (mivel az XLOOKUP csak az újabb verziókban működik): a =MATCH ⇄ =HOL.VAN megadja a keresési érték pozícióját a keresési tartományban, az =INDEX pedig megadja a visszaadandó tömb azon pozícióban található elemét: =INDEX(Hasznos_e; MATCH(@HelyiségKatKód; _KatKódok)).
3. Az új Hasznosság oszlop alapján már összegezhető a hasznos terület:
   =SUMIFS(Terület; Hasznosság; "Hasznos"), vagy táblázatként:
   =SUMIFS(HsgLista[Terület]; HsgLista[Hasznosság]; "Hasznos"),
   

Mivel a rúdhosszak egy adott mérettartományban használhatók, keresésükkor nyilván nem követelhető meg a pontos egyezés. Fontos kérdés továbbá, hogy az intervallum határa hova kell tartozzon – mivel 1 m-es távolsághoz nyilván nem kell 2 m-es rúd, ez esetben az egyeztetési mód 1-es kell legyen: „ha nincs pontos egyezés, a következő nagyobb elemet adja vissza”.

Hossz	Típus
99,00	Túl nagy
2,00	200-as rúd
1,00	100-as rúd
0,50	50-es rúd
0,25	25-ös rúd
0,10	10-es rúd
0,05	Nem kell

Kategóriák – rúdtípusok

1. Egy külön területen hozza létre (vagy másolja oda) az összerendelési táblázatot.
2. Definiálja a táblázat keresési oszlopát R_hossz néven.
3. Definiálja a táblázat érték oszlopát R_típus néven.

Besorolás

1. A helyiséglista (a HsegLista táblázat) szélén egy új oszlopot nevezzen el Rúdtípusnak.
2. Számolja ki a Belmagasság és az Álmennyezet különbségét minden helyiségben:
   =MAX(@Belmagasság-@Álmennyezet;0)
   – meglepő módon néhol negatív érték adódna, ilyenkor a rúdhosszt nullának vesszük.
3. Az így kapott befüggesztési hosszakat kell a feladatban megadott tartományokba (kategóriákba) besorolni. Ehhez egészítse ki az előbbi képletet úgy, hogy az ielőbbi hossz legyen a keresési érték:
   =XLOOKUP(MAX(@Belmagasság-@Álmennyezet;0);R_hossz;R_típ;"?!";1).

Összegzés

1. Az összerendelési táblázatot egészítse ki egy új, Darab című oszloppal, mely minden rúdtípus (R_típus) sorában összesíti, hogy az adott típusból hány darab kell (az utolsó sor nyilván nem kell számolni):
   =ROUNDUP(SUMIFS(Terület; Rúdtípus; @R_típ);0).

Az XLOOKUP függvény jól használható akár kétdimenziós keresési tábla esetén is, mert egyrészt dinamikus, másrészt önmagába ágyazható. Ez jól demonstrálható a 2.C pontban létrehozott táblázat segítségével.

A táblázat vízszintes fejlécsora a Szintek (=AB3#), függőleges fejlécoszlopa a Katkódok (=AA4#), kétdimenziós adattáblája pedig a Területek (=AB4#) nevet kapta.

Ha egy üres területre beírjuk az =XLOOKUP("Gép1"; KatKódok; Területek) képletet, akkor egy vízszintes tömböt kapunk, mely tartalmazza Gép1 helyiségkategória sorába eső összes terület-értéket.

Ha egy másik cellába beírjuk az =XLOOKUP(4;_Szintek;_Területek) képletet, akkor egy függőleges tömböt kapunk, mely tartalmazza a 4. szint oszlopába eső összes terület-értéket.

A két előbbi megoldás szabadon kombinálható oly módon, hogy az első („belső”) kereséssel a teljes Területek táblából kiválasztjuk az első feltétel szerinti sorszámú sort vagy oszlopot, így a második („külső”) keresés már ebben az egydimenziós tartományban válaszja ki a második feltétel szerinti sorszámú elemet:
=XLOOKUP(4;_Szintek; XLOOKUP("Gép1"; KatKódok; Területek)), vagy
=XLOOKUP("Gép1"; KatKódok; XLOOKUP(4; Szintek; Területek)).

Természetesen elegánsabb megoldás, ha a konkrét értekek (4, Gép1) helyett egy-egy cellára hivatkozunk – főleg ha beállítjuk, hogy azokba egy legördülő listából lehessen értéket választani.

Alternatív megoldás: a =MATCH ⇄ =HOL.VAN sorban és oszlopban is tud keresni, az =INDEX pedig sor és oszlop pozíciót is elfogad: =INDEX(Területek; MATCH("Gép1"; KatKódok); MATCH(4; Szintek)).

Másolja át a mellékelt táblázatot a munkafüzet egy új, lábazat nevű lapjára. Az előbbi leírásnak megfelelően ez esetben is két XLOOKUP függvényt kell egymásba ágyazni, a belső a teljes adattáblából választja ki az adott Falburkolattípushoz tartozó oszlopot, a külső ebből az oszlopból választja ki az aktuális Burkolattípushoz tartozó cellát:
=XLOOKUP(@Burkolattípus; lábazat!$A$3:$A$17; XLOOKUP(@Falburkolattípus; lábazat!$C$1:$U$1; lábazat!$C$3:$U$17))

A kimutatás egy kétdimenziós táblázatot hoz létre, így egyszerre két egymástól független szempont szerint lehet összesíteni (természetesen a kategóriák egymásba is ágyazhatók, de ez általában kevéssé áttekinthető megoldást eredményez).

1. Győződjön meg róla, hogy az aktív cella a helyiségkönyv tábla (a HségLista táblázat) területén található.
2. Válassza az Beszúrás ⇄ Insert sávmenün a Kimutatás ⇄ Pivot Table parancsot
   
3. A kimutatás forrása legyen Táblázat vagy tartomány  ⇄ Table/Range,
   és kerüljön a Létező munkalapra  ⇄ Existing Worksheet, pl. az AA30 cellához.
   
4. Létrejön a kimutatás váza, amit fel kell tölteni a Kimutatásmezők ⇄ PivotTable Fields listából:
   – a Szint kerül az Oszlopok ⇄ Columns rovatba,
   – a Helyiség kategória kód kerül a Sorok ⇄ Rows rovatba,
   – a Terület kerül az Értékek ⇄ Values rovatba.
Ekkor korábbi feladatban készítetthez igen hasonló táblázat áll össze.
5. Az adatterületen a jobb gombbal kattintva válassza az Értékmező-beállítások  ⇄ Value Field Settings parancsot,
   – az Értékek megjelenítése  ⇄ Show values as rovatban válassza az Oszlopösszeg százaléka ⇄ % of Column Total opciót,
   – a Számformátum  ⇄ Number Format gombra kattintva adja meg a megjelenítési számformátumot (pl. 0,0%;-0,0%;–_%).
   

A kimutatás könnyen át is alakítható a mezőneveket a táblázaton kívülre húzva – de persze új kimutatás is beszúrható akár ugyanazon lapra.

1. Az előző feladattal azonos módon hozza létre a kimutatás vázát pl. az AA50 cellánál kezdve.
   
2. Létrejön a kimutatás váza, amit fel kell tölteni a Kimutatásmezők ⇄ PivotTable Fields listából:
   – a Burkolat típusa kerül az Oszlopok ⇄ Columns rovatba,
   – a Helyiségtípus kerül a Sorok ⇄ Rows rovatba,
   – a ID kerül az Értékek ⇄ Values rovatba.
3. A szintenkénti összesítéshez húzza a Szint mezőt az oszlopok rovatba, a Helyiségtípus elé
   – ha viszont csak egy (vagy több) szintet akar egyszerre megjeleníteni, a húzza a Szűrők ⇄ Filters rovatba
4. Mivel az értékekhez nem számot tartalmazó mező került, a program automatikusan a Darab ⇄ Count függvényt használja
   – szükség esetén az értekmező összegzésének alapja az Értékmező-beállítások  ⇄ Value Field Settings paranccsal módosítható.