Digitális ábrázolás / BMEEPAGA205

Adat­­­kezelés

A gyakorlat során egy többszintes intézmény helyiségkönyvét dolgozzuk fel.

A leírás egy már formázott, rendezett táblázattal indul. dowload LETÖLTÉS

A letölthető fájlban teljes oszlopok lettek elnevezve (így nem okoz gondot, ha változik a sorok száma), ezért az újabb Excel verziókban (pl. 365) a kategória oszlopának aktuális sorába eső értékére való hivatkozáskor be kell szúrni egy „@” jelet (pl. =@Terület).

Az eredeti, szöveges fájl betöltése, a táblázat (pl. ismétlődő sorok) és celláinak (pl. számformátumok) formázása, a táblázat sorainak rendezése, szűrése, és az egyszerű összegzések, átlagok tekintetében elérhető egy rövid ismétlés.

A leírásban szereplő „dinamikus” működés csak az újabb verziókban érhető el (365 / 2021).

Fontosabb bemutatott függvények:  SZUMHATÖBB() ⮂ SUMIFS() |  DARABHATÖBB() ⮂ COUNTIFS() |  EGYEDI() ⮂ UNIQUE() |  SZŰRŐ() ⮂ FILTER() |  TRANSZPONÁLÁS() ⮂ TRANSPOSE() |  XKERES() ⮂ XLOOKUP().

Egy lehetséges megoldás: dowload LETÖLTÉS

 1. Szöveg kezelése

1.A. Rendezzük sorba a helyiségeket szintenként, azon belül ajtószám szerint!

buttonA 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.)

  1. Hozzunk létre három új üres oszlopot: jelöljük ki pl. az A–C oszlopokat, majd jobb gomb, és Beszúrás ⮂ Insert.
  2. A fejléc-sorba írjuk be a mezőneveket: Típus, Szint, Ajtó.
  3. A Típus a bal oldali karakter:
    =BAL(@ID;1) ⮂ =LEFT(@ID;1)+0
  4. A Szint a 3. karaktertől két karakter:
    =KÖZÉP(@ID;3;2)+0 ⮂ =MID(@ID;3;2)+0
  5. Az Ajtó a jobb oldali két karakter:
    =JOBB(@ID;2)+0 ⮂ =RIGHT(@ID;2)+0
  6. Jelöljük ki a három képletet tartalmazó cellát, majd kattintsunk 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álhatjuk 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. Legegyszerűbb, 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 megjelení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").

 2. Összesítés kategóriák szerint

Az aggregátor függvényeknek általában van olyan változata, amely csak a bizonyos feltételeknek megfelelő elemeket veszi számításba. Ez sokszor valamiféle kategóriák alapján történik, a feladat megoldásának így gyakran előfeltétele a kategóriák listájának létrehozása.

2.A. Összesítsük, hogy hány helyiség van az egyes szinteken!

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

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

A =DARABHATÖBB() ⮂ =COUNTIFS() függvény megadja az adott feltételek mindegyikének megfelelő elemek számát.

  • A függvénynek kétféle paramétere van (N kritérium esetén így 2N argumentuma lesz):
    –  kritériumtartományN ⮂ criteria_rangeN: az a tartomány, amelynek elemeire a feltétel vonatkozik
    –  kritériumN ⮂ criteriaN: a feltétel, amelynek az előbbi tartomány elemei meg kell feleljenek
  • A függvény azon elemek darabszámát adja meg, melyekre minden feltétel igaz.
  • Ha egy kritérium konstans szám, és nem egyenlőséget vizsgálunk, akkor azt idézőjelek között kell megadni (pl. ">2", "<=1", "<>0").
  • Szöveges kritérium szintén idézőjelek között adható meg, és egy vagy több karaktere helyettesíthető a * és ? joker karakterekkel.
  • A kritérium egyérteleműbbé és könnyebben módosíthatóvá tehető, ha külön cellában szerepel (ekkor nem kell idézőjel).

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) írjuk 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ásoljuk végig a szintszámok felett a képletet – pl. húzzuk oldalt a cella jobb alsó sarkában lévő fogópontot!

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

2.B. Összesítsük, hogy mekkora területek tartoznak az egyes szinteken az egyes helyiség kategóriákhoz!

buttonA 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ásoljuk á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álasszuk az Adatok ⮂ Data sávmenün az Ismétlődések eltávolítása ⮂ Remove Duplicates parancsot.
  3. Ellenőrizzük, 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.

A =SZUMHATÖBB() ⮂ =SUMIFS() függvény összegzi az adott feltételeknek megfelelő elemek valamely tulajdonságát.

  • A függvénynek háromféle paramétere van (N kritérium esetén így 1+2N argumentuma lesz):
    – összegtartomány ⮂ sum_range: az összegzésre kerülő, egy oszlopból vagy sorból álló tartomány
    –  kritériumtartományN ⮂ criteria_rangeN: az előbbivel egyező méretű tartomány, amelynek elemeire a következő feltétel vonatkozik
    –  kritériumN ⮂ criteriaN: a feltétel, amelynek az előbbi tartomány elemei meg kell feleljenek
  • A függvény az összeg_tartomány azon elemeit összegzi, melyekre minden feltétel igaz.
  • A kritérium konstans szám, és nem egyenlőséget vizsgálunk, akkor azt idézőjelek között kell megadni (pl. ">2", "<=1", "<>0").
  • Szöveges kritérium szintén idézőjelek között adható meg, és egy vagy több karaktere helyettesíthető a * és ? joker karakterekkel.
  • A kritérium egyérteleműbbé és könnyebben módosíthatóvá tehető, ha külön cellában szerepel (ekkor nem kell idézőjel).
 • Összegzés
  1. A területek tartományának bal felső cellájába (AB4) írjuk 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épletet végigmásolhatjuk a teljes tartományon (AB4:AI16).

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

2.C. Módosítsuk az előbbi megoldást úgy, hogy dinamikusan kövesse a változásokat!

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.

Az =EGYEDI() ⮂ =UNIQUE() függvény megadja egy tartomány egyedi értékeinek listáját – például az összes helyiség kategória kódot.

A =SZŰRŐ() ⮂ =FILTER() függvény segítségével adott feltétel szerint szűrhetők a tartományok – pl. kiejthetők a felesleges elemek.

A =TRANSZPONÁLÁS() ⮂ =TRANSPOSE() függvény 90°-kal elforgatja a paraméterként kapott tartományt.

A =SORBA.RENDEZ() ⮂ =SORT() függvény értelemszerűen az elemek abc-rendbe rendezésére szolgál.

 • Táblázat definiálása

buttonAz 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ünk 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áljuk a táblázatot!
  3. A Táblázattervezés ⮂ Table Design sávmenün nevezzük át a táblát (HsgLista), és válasszuk ki annak stílusát.

A táblázat fejléc-celláinak tartalma hivatkozásként is működik – emiatt viszont nem lehet közöttük sem üres, sem azonos tartalmú (hiány vagy ütközés esetén a program kiegészíti a fejlécet). A táblázaton belüli hivatkozásoknál elég az oszlopcímkét használni (=[@Terület]*[@Belmagasság]), a táblázaton kívüli képleteknél szükséges a táblázatnév is (=SUM(HsgLista[Terület])).

 • Kategóriák – kritérium listák
  1. Töröljük a Szintek lista elemeit (AB3:AI3), és a KatKód lista elemeit (AA4:AA16)!
  2. A KatKódok lista első elemeként (AA4) írjuk be az alábbi dinamikus képletet:
    =SORT(UNIQUE(HsgLista[Helyiség kategória kód]))
    – ahol a Helyiség Kategória Kód a definiált HsgLista tábla azonos nevű oszlopára mutató hivatkozás!
  3. Definiáljuk (Ctrl+F3) a listát KatKódok néven (=helyiséglista!$AA$4#)!
  4. A Szintek lista első elemeként (AB3) írjuk be az alábbi dinamikus képletet:
    =TRANSPOSE(SORT(UNIQUE(HsgList[Szint])))
    – ahol a Szint a definiált HsgLista tábla azonos nevű oszlopára mutató hivatkozás!
  5. Definiáljuk (Ctrl+F3) a listát Szintek néven (=helyiséglista!$AB$3#)!
 • Összegzés
  1. A bal felső elem kivételével töröljük a területek képleteit!
  2. A bal felső elem (AB4) képletét módosítsuk 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áljuk (Ctrl+F3) a kapott eredményeket Területek néven (=helyiséglista!$AB$4#)!

Ellenőrzésképpen összegezzük az így besorolt területeket (=SUM(AB4#)), és vessük össze az összterülettel (=SUM(Terület))!

Természetesen nincs mindig szükség kategória-listára – például ha csak egy elemre vagyunk kíváncsiak.

2.D. Számoljuk ki, hogy egy adott szinten a helyiségek hány százaléka oktatóterem!

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) írjuk be egy cellába az 1 értéket!
  2. Az előbbi cella alá (Z21) írjuk be a *Oktatóterem szöveget!
  3. Az alatta lévő cellában (Z22) számoljuk ki az oktatási termek arányát:
    =COUNTIFS(Szint; Z20; HelyiségNév; Z21) / COUNTIFS(Szint; Z20)
  4. Az eredményt formázzuk 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.

buttonHasználjuk 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 adjuk 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.

 3. Kategóriákba sorolás

A hasznos alapterületbe az alapfunkciók (AlapX), intézményi funkciók (IntX), oktatási (Okt) és speciális képzési funkciók (Spec) területei számítanak bele.

3.A. Számoljuk ki, hogy mekkora a hasznos alapterület!

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ítsük 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áljuk ezt a listát Hasznos_e néven.

Az =XKERES() ⮂ =XLOOKUP() függvénynek három kötelező paramétere van:

  • keresési_érték ⮂ lookup_value: a keresendő érték
    – most az aktuális helyiség Helyiség kategória kódja
  • keresési_tömb ⮂ lookup_array: a tartomány, melyben az előbbi értéket keresni kell
    – most az összerendelési tábla első oszlopa (fix hivatkozásként!)
  • visszaadandó_tömb ⮂ return_array: a tartomány, melyből az előbbi keresés szerinti sorszámú elem kiolvasható
    – most az összerendelési tábla második oszlopa (fix hivatkozásként!)
  • [ha_nincs_találat] ⮂ [if_not_found]: hibaüzenet helyett ezt adja vissza, ha nem található érvényes egyezés
  • [egyeztetési_mód] ⮂ [match_mode]: a keresett egyezés típusa:
     0 🡢 pontos egyezés,
    -1 🡢 ha nincs pontos egyezés, a következő kisebb elemet adja vissza,
     1 🡢 ha nincs pontos egyezés, a következő nagyobb elemet adja vissza,
     2 🡢 keresés *, ?, ~ helyettesítő karakterekkel
  • [keresési_mód] ⮂ [search_mode]: a keresés módszere:
     1 🡢 keresés az első elemtől kezdve,
    -1 🡢 keresés az utolsó elemtől kezdve,
     2 🡢 bináris keresés az első elemtől, csakis növekvő sorrendbe rendezett keresési táblában,
    -2 🡢 bináris keresés az utolsó elemtől kezdve, csakis csökkenő sorrendbe rendezett keresési táblában

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 nevezzünk el Hasznosságnak.
  2. Soroljuk 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).
  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"),

Tegyük fel, hogy az álmennyezet felfüggesztéséhez m²-enként átlagosan egy rúd kell. A rudak az alábbi hosszakban állnak rendelkezésre: 10 cm, 25 cm, 50 cm, 1 m, 2 m. Ha a távolság 5 cm-nél kisebb, akkor az álmennyezetet közvetlenül a plafonra erősítik. Nyilvánvalóan a lehető legrövidebb rudakat érdemes használni.

3.B. Összesítsük, hogy hány darab kell az egyes függesztőrudakból!

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 hozzuk létre (vagy másoljuk oda) az összerendelési táblázatot!
  2. Definiáljuk a táblázat keresési oszlopát R_hossz néven!
  3. Definiáljuk 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 nevezzünk el Rúdtípusnak.
  2. Számoljuk 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ítsük ki az előbbi képletet úgy, hogy az elő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ítsük 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ó sort nyilván nem kell számolni):
    =ROUNDUP(SUMIFS(Terület; Rúdtípus; @R_típ);0).

 4. Kimutatás / Pivot Tablebutton

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. További szempontok esetén természetesen a kategóriák egymásba is ágyazhatók, de ez általában kevéssé áttekinthető megoldást eredményez.

4.A. Készítsünk kimutatást a területekről szintenként és helyiség kategória kódonként! Jelenítsük meg az adatokat a szintterületek százalékában!
  1. Győződjünk 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álasszuk a 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.
    Ezzel a 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.

4.B. Készítsünk kimutatást a helyiségek számáról helyiség- és burkolat-típusonként! A kimutatás legyen szűrhető szintenként!
  1. Az előző feladattal azonos módon hozzuk létre a kimutatás vázát pl. az AA50 cellánál kezdve!
  2. A kimutatás vázát 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úzzuk a Szint mezőt az oszlopok rovatba, a Helyiségtípus elé
    – ha viszont csak egy (vagy több) szintet akarunk egyszerre megjeleníteni, a húzzuk 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ó.

 5. Kétdimenziós keresési tábla

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.

5.A. Készítsünk egy keresést, mely megadja, hogy mekkora terület tartozik egy adott szinten egy adott helyiségkategóriába!
  • 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.

A mellékelt táblázat megadja, hogy adott padló- és falburkolat esetén milyen lábazatot kell elhelyezni.

5.B. A helyiséglista egy új oszlopában jelenítsük meg az adott helyiséghez tartozó lábazat kódját!
  • Másoljuk át a mellékelt táblázatot a munkafüzet egy új, lábazat nevű lapjára!
  • 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))