Építész-informatika 1 

Műveletek adattáblákkal

többszintes épület helyiségkönyv-adatainak elemzése

 IT alkalmazások 

Valid HTML 4.01 Tr.

Összes kinyitása⇓ | becsukása
VISSZA  

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

A gyakorlat során elkészített táblázat kizárólag oktatási célt szolgál. A "Lásd még:" megjegyzések a Microsoft Office Online megfelelő oldalaira mutató linkek.

Külső adatok beolvasása (ismétlés)

Az Excel programmal nemcsak xls kiterjesztésű fájlokat lehet megnyitni. A legegyszerűbb, táblázatként is értelmezhető formátum az olyan szövegfájl, amelyben a táblázat sorainak a szöveg sorai felelnek meg, a különböző oszlopokba kerülő értékeket pedig valamilyen speciális karakter (pl. tabulátor vagy vessző) választja el.

Adatok beolvasása szövegfájlból

Olvassa be a kezdeti adatokat tartalmazó szövegfájlt: tabulatorral_tagolt_tobbszintes.txt. Hogyan?

Próbáljon ki két másfajta formátumú szövegfájlt is:

  1. Töltse le a munkamappába a szövegfájlt (Jobb klikk a linkre, majd Cél mentése más néven...)
  2. Fájl/Megnyitás (File/Open) (A megnyitás ablakban csak akkor jelennek meg a txt, csv, prn típusú fájlok, ha a fájltípust Szövegfájlokra állítjuk.)
  3. A szövegben az adatok tabulátorokkal tagoltak, ezért a Szövegbeolvasó Varázsló 1. lépésében válassza a Tagolt (Delimited) szöveget, a 2. lépésben pedig határoló jelként (Delimiters) adja meg a tabulátort.
  4. pontosvesszovel_tagolt_helyiseglista.txt: Tagolt (Delimited) szöveg, határoló jel: pontosvessző
    szokozzel_tagolt_formazott_helyiseglista.txt: Fix széles (Fixed width) szöveg, a 2. lépésben az oszlopszélességeket kell megadni

Hibakeresés: Figyeljen arra, hogy ha a táblázatot szöveges (.txt) formátumban menti el, a képletek, formázások stb. elvesznek, ha tehát ezeket szeretné megőrizni, mentésnél az Microsoft Office Excel munkafüzet (.xls) formátumot válassza.

Ha sikeresen beolvasta ezeket a fájlokat, csak egy dokumentumot hagyjon meg, a többit zárja be.

Táblázat formázása (ismétlés)

Formázza meg a táblázatot.

Egyszerű számítások (ismétlés)

Egyszerű képletek (ismétlés)

Egy új oszlopban számítsa ki a helyiségek burkolandó falfelületeit. Hogyan?

  1. Az első üres oszlopba írja be a mezőnevet, majd az alatta lévő cellába a Kerület x Álmennyezet magassága - Ajtók összfelülete - Ablakok összfelülete képlettel számolja ki a burkolandó falfelületet.
  2. Kattintson duplán az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" a többi cellát is.

Egy új oszlopban számítsa ki a helyiségek térfogatát Hogyan?

  1. Az első üres oszlopba írja be a mezőnevet, majd az alatta lévő cellába a Terület x Álmennyezet magassága képlettel számolja ki a térfogatot.
  2. Kattintson duplán az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" a többi cellát is.

Minden helyiségbe 12 cm aljzatbeton kerül. Mennyi beton kell egy-egy helyiségbe? A betonvastagság értéke könnyen változtatható legyen. Hogyan?

  1. A következő üres oszlop fejlécsorába írjon mezőnevet (Aljzatbeton), majd az alatta lévő cellában a beton térfogatát a Terület * vastagság képlettel lehet kiszámolni. Az Excel nem vesz figyelembe mértékegységet, így ha a beton mennyiségét m³-ben szeretnénk megkapni, akkor a =Terület*0,12 képletet kell beírni. Így viszont az aljzatbeton vastagsága nem látható (nem dokumentálható), valamint a módosítása is nehézkes.
  2. Szúrjon be egy új üres sort a fejléc fölé: a teljes első sor kijelölése után jobb klikk/Beszúrás [Insert], és az Aljzatbeton mező fölé írja be a 12 értéket, a tőle balra lévő cellába pedig az "Aljzatbeton vastagsága:" szöveget jobbra igazítva. A mértékegységet megjelenítheti egyéni számformátum megadásával, vagy a 12-től jobbra lévő cellába beírva.
    Hibakeresés: A cellába ne írja be a 12cm-t, mert ez szöveg, és nem lehet vele műveletet végezni.
  3. A képletben a 0,12 konstans értéket törölje, majd kattintson a 12-t tartalmazó cellára és ossza el 100-zal. A képletet most végigmásolva még rossz eredményt kapunk, mert a 12-re való hivatkozás nem marad fix (relatív hivatkozás).
  4. A képletben kattintson a cellahivatkozásra, majd nyomja meg az F4 gombot a billentyűzeten. Ennek hatására a hivatkozásban megjelennek a $ jelek, így a relatív hivatkozást abszolút (fix) hivatkozássá alakítottuk. Most már a jobb alsó sarokon dupla kattintással végigmásolható a képlet.

Az utolsó lépés helyett alternatív lehetőség, hogy a 12-t tartalmazó cellát elnevezzük, majd a képletben a névre hivatkozunk. A névre hivatkozás mindig abszolút (fix) hivatkozás (ld. következő pont: Cellák elnevezése).

Cellák elnevezése (ismétlés)

Adjon nevet a teljes táblázatnak. Javítsa ki a felület és térfogatszámítást úgy, hogy könnyen olvasható képleteket kapjon: nevezze el az oszlopokat, és a képletekben ezekre a nevekre hivatkozzon. Hogyan?

  1. Jelölje ki az elnevezendő cellatartományt vagy oszlopot, majd Képletek/Név megadása (Formula/Define Name). Adja meg a nevét.
  2. Jelölje ki az elnevezendő oszlopot, majd kattintson a Név mezőbe (a felső ikonsor alatt, a szerkesztőléctől balra), és adja meg a nevet, majd Enter.

Lásd még: Cella elnevezése munkafüzetben
Hibakeresés: A Képletek/Névkezelő menüpont alatt lehet törölni az esetlegesen rosszul megadott cellaneveket.
Hibakeresés: Ha olyan nevet használ egy képletben, amit még nem definiált, a cellában a #NÉV (#NAME) hibajelzés jelenik meg.

Összesítések (ismétlés)

Összesítő (aggregátor) függvények azok, amelyek egy numerikus adathalmaz összes értékét együtt jellemzik. Ide tartozik többek között az összeg, átlag (számtani, mértani, etc.), darabszám, maximum, minimum.

Mennyi az épület nettó alapterülete? Hogyan?Nettó alapterület az épület helyiségeinek összterülete. Ez mindig kisebb, mint a befoglaló kontúr területe (bruttó terület).

Több megoldás is lehetséges:

Hány helyiség van az épületben? Hogyan?

Mekkora a legnagyobb helyiség az épületben? Hogyan?

Hány helyiséghez nincs megadva tűzszakasz? Hogyan?

ID oszlop celláinak száma minusz a Tűzszakasz oszlop kitöltött celláinak száma.

Rendezés (ismétlés)

Rendezze sorba a helyiségeket nagyság szerint. Hogyan?

Leggyorsabban az Adatok [Data] menü Rendezés és szűrés [Sort & Filter] részén a gyorsrendezés ikonok rendezés segítségével lehet listát sorrendezni. A rendezés véglegesen módosítja a sorok sorrendjét, az eredeti sorrend csak közvetlenül a rendezés után, a Vissza gombbal állítható helyre.

Kattintson abban az oszlopban (Terület) tetszőleges cellára, amelyik szerint rendezni szeretne, majd válassza a megfelelő gyorsrendezés ikont.

Hibakeresés: Vigyázat! Ha több cella (pl. egy teljes oszlop) van kijelölve, akkor csak a kijelölést rendezi, így az adatok összekeverednek. Vagy jelölje ki a teljes táblázatot (ezt érdemes elnevezni), vagy ne jelöljön ki semmit, az aktív cella legyen a lista tetszőleges cellája. Az utóbbi esetben az Excel az első teljesen üres sorig és oszlopig ismeri fel a táblázatot, tehát ne legyen a listában üres oszlop, még minimális szélességű, vagy elrejtett se.

A gyorsrendezés ikonok rendezés az aktív cella oszlopa szerint rendeznek és az előbbiek szerint ismerik fel a lista méreteit. Ha bármelyik oszlopnak üres a fejléce (nincs mezőnév), akkor a fejlécsort is adatsornak tekinti, és belerendezi a listába! Ezért minden oszlopnak legyen – egymástól különböző – fejléce.

Rendezze sorba a helyiségeket előbb helyiség kategória kódonként, azon belül fordított nagyság szerint (előre a nagyok). Hogyan?

  1. Több szempont szerint a Rendezés [Sort] gomb segítségével lehet (Adat [Data] sávmenü) rendezni. Az Excel az előbbiekhez hasonlóan ismeri fel a teljes táblázatot, ezért előre vagy egy cellát jelöljön ki, vagy a teljes táblázatot.
  2. A rendezés ablakban ellenőrizze, hogy az Adatok fejlécet tartalmaznak [My data has headers] kapcsoló be van kapcsolva.
    Hibakeresés: ez akkor marad kikapcsolva, ha a táblázat első sora üres cellát vagy többször előforduló értéket tartalmaz. Ezekben az esetekben az első sor nem lehet fejléc, az Excel adatsornak tekinti, és a többi adatsorral együtt rendezi.
  3. Ha a fejléc rendben van, akkor az Oszlop [Column] legördülőben a Fejlécnevek látszanak, és kényelmesen kiválasztható az első rendezési szempont (Helyiség kategória kód).
  4. Második rendezési szempontot az Újabb szint [Add level] gomb ad. Az oszlop (Terület) kiválasztása után a Sorrend [Order] oszlopban adható meg a csökkenő rendezés.

Szűrés (ismétlés)

Válogassa ki az oktatótermeket. Hogyan?

Az Adatok [Data] sávmenü Szűrés [Filter] funkciója lehetővé teszi, hogy a listának csak bizonyos sorai látszódjanak. A többi sort ilyenkor elrejti (0 magasságúvá teszi), de nem törli ki. A szűrés kikapcsolása után újra a teljes lista látható. A szűrő gombjai rendezésre is használhatók.

  1. Kattinson a lista tetszőleges cellájára, vagy jelölje ki a teljes táblázatot, majd kattintson az Adatok [Data] sávmenü Rendezés és szűrés [Sort & Filter] részén a Szűrés [Filter] gombra. Ekkor az első (fejléc) sorban kis legördíthető ikonok jelennek meg.
  2. A Helyiség kategória kód oszlop legördülőjében az Összes kijelölése [Select All] elemre kattintva törölje a pipákat, és a listából válassza az Okt elemet.
    Ekkor az oktatótermeken kívül az összes többi sor eltűnik. Az aktív szűrésre utal a szűrt oszlop fejlécében megjelenő tölcsér jel, valamint a kék színűre váltó sorindexek.
  3. A szűrés kikapcsolásához a szűrt oszlop legördülőjében válassza a Szűrő törlése [Clear filter] elemet.

Hibakeresés: A szűrő a rendezéshez hasonlóan ismeri fel a táblázat méreteit. Ha ez nem megfelelő, akkor kattintson újra a sávmenü Szűrés [Filter] gombjára, ezzel törölhetők a szűrőgombok, majd a kívánt tartomány kijelölése után újra be kell kapcsolni a szűrést.

Válogassa ki a 100 m²-nél nagyobb oktatótermeket. Hogyan?

A legördülő lista Számszűrő [Number Filters] menüpontja segítségével relációkat is használhatunk szűréshez. Szöveget tartalmazó oszlopoknál a szöveg tartalmára szűrhetünk ugyanitt.
Több oszlopban egyszerre is bekapcsolható szűrés, ezek egyszerre érvényesülnek, tehát közöttük ÉS kapcsolat lesz.

  1. Az előző szűrés megtartásával a Terület oszlop szűrőgombján válassza a Számszűrő [Number Filters] menüpontot.
  2. Válassza a Nagyobb, mint... [Greater Than...] elemet, és a megjelenő ablak aktív mezőjébe írjon 100-at.

Ha még aktív az oszlopban a feltételes formázás a 100 m²-nél nagyobb helyiségekre, akkor használhatja a Szín szerinti szűrést [Filter by Color] is.

Szövegkezelő függvények

Rendezze sorba a helyiségeket előbb szintenként és azon belül helyiségsorszám szerint. Hogyan?

A helyiségek szintje csak az ID oszlopból olvasható ki (Típus/Szint-Ajtó), de nem ez szerepel a cella elején, ezért eszerint nem lehet sorrendezni. Az ID-t fel kell darabolni három oszlopba, hogy rendezni lehessen.

  1. Hozzon létre három új üres oszlopot a bal szélen: jelölje ki az első három oszlopot, majd jobb gomb/Beszúrás [Insert].
  2. A fejléc-sorba írja be a mezőneveket (Típus, Szint, Ajtó).
  3. A Típus balról 1 karakter, az Ajtó jobbról 2 karakter, a Szint pedig a 4. karaktertől 2 karakter. Ezeket rendre a =bal() [=left()], =jobb() [=right()] és =közép() [=mid()] függvények szolgáltatják:
    • =BAL(ID;1)
    • =JOBB(ID;2)
    • =KÖZÉP(ID;4;2)
    A képletek eredménye szöveg. Ha számként szeretnénk kezelni, akkor konvertálni kell őket. Az =érték() [=value()] függvény a számként értelmezhető szöveget számmá alakítja (ezek után már működnek rá a számformázások). Párja a =szöveg() [=text()] függvény, amely számot szöveggé konvertál az egyéni számformázásnál megadható sablonokhoz hasonló formátumkód alapján. Így pl. a Szint függvénye: =ÉRTÉK(KÖZÉP(ID;4;2))
  4. Kattintson duplán az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" lefelé a többi cellát is.

Az oszlop szétdarabolható a szövegbeolvasó varázslóval is. Ennek hátránya, hogy ha később változik a helyiséglista (márpedig egy tervezési folyamat során biztosan változik), akkor ezt a műveletet előlről kell kezdeni, mig a képleteket használó módszernél csak be kell illeszteni az új listát a megfelelő helyre, és minden frissül.

  1. Hozzon létre két üres oszlopot az ID oszloptól jobbra: jelöljön ki 2 oszlopot az ID oszloptól jobbra, majd jobb gomb/Beszúrás [Insert].
  2. Jelölje ki az ID oszlop tartalmát a fejléc nélkül: kattintson az első elemre, majd ctrl + shift + ↓.
  3. Indítsa el a szövegbeolvasó varázslót: Adatok [Data] sávmenü Szövegből oszlopok [Text to Columns] gomb.
  4. A varázsló első lépésében válassza a Tagolt [Delimited] opciót.
  5. A második lépésben Egyéb határoló jelként [Other Delimiter] adja meg a / jelet.
  6. A harmadik lépésben a második oszlopra állítsa be, hogy Szöveg [Text], különben egyes értékeket hajlamos lesz dátumként értelmezni.
  7. Ismételje meg mégegyszer a - menti szétválasztásra.

A mi esetünkben a varázsló első lépésénél választható a Fix széles [Fixed width] opció is, mert az emelet és a helyiségszám is mindig két számjegyű. Ekkor érdemes 4 új oszlopot létrehozni, az elválasztó jeleket (/ és -) külön oszlopba darabolni, és ezt a két oszlopot a varázsló harmadik lépésében kihagyni [Do not import], vagy utólag letörölni (teljes oszlop kijelölése után jobb klikk / Törlés [Delete]).

Feltételes összesítés (ismétlés)

Az épület hány százaléka oktatóterem? Hogyan?

Meg kell határozni az oktatótermek összterületét, és osztani kell az épület (nettó) összterületével. Az egyszerűbb, kézi módszer a Szűrést használja, de ez nem dokumentálható:

  1. Kapcsolja be a Szűrést [Filter], és a Helyiség neve oszlopban válogassa le az Oktatótermeket.
  2. Jelölje ki a Terület oszlopot. Az Excel láblécében látható a leválogatott helyiségek összterülete.
  3. A Windows Számológép [Calculator] segítségével határozza meg az eredményt.

Képlet segítségével az eredmény dokumentálható lesz. Ha csak adott feltételnek megfelelő elemeket kell összeadni, akkor a =szumha() [=sumif()], vagy =szumhatübb() [=sumifs()] függvényt kell használni. A függvénynek három paramétere van:

  1. Egy másik munkalapon egy cellába írja be az Oktatóterem szöveget.
  2. Tőle jobbra lévő cellában írja be a képletet: =szumha(Helyiség neve,"Oktatóterem",Terület)
  3. A konstans kritérium helyett hivatkozhat a tőle balra lévő cellára is (ekkor macskakörmök nélkül), így nem kell kétszer gépelni.
  4. Az alattuk lévő cellákba írja be az Össz terület szöveget és mellette számolja ki az épület teljes nettó területét: =szum(Terület)
  5. Az alattuk lévő cellákba írja be a Oktatótermek aránya: szöveget és mellette írja be a fenti két érték hányadosát kiszámító képletet. Az eredmény formázza százalék formára.

Hány helyiség van az egyes szinteken? Hogyan?

Az épület 8 szintes, így a kézi módszer igen fáradságos lenne, ezért ezt most nem alkalmazzuk. A legtöbb összesítő (aggregátor) függvénynek megvan a feltételes párja. Adott feltételnek megfelelő darabszámot a =darabteli() [=countif()], vagy =darabhatöbb() [=countifs()] függvény adja meg. A feltétel alkalmazásához szükséges, hogy a Szint egy külön oszlop legyen (ennek megoldása a szövegkezelő függvényekről szóló részben található), és kell a szintek listája.

  1. A szintek listájához a munkafüzet egy másik munkalapján írja be egy cellába a 0 értéket, alá az 1 értéket, majd a két cellát kijelölve másolja őket lefelé. Az első két értékből az Excel kitalálja a sorozat szabályát. Az épület hét emeletes, így 7-ig kell másolni.
  2. Ha a Szint oszlopban nem használta az =érték() [=val()] függvényt, akkor a szintszámok ott szövegként szerepelnek, ezért itt is így kell beírni. Ebben az esetben az első cellába írja: '00 (aposztróffal kezdődő cella tartalma szöveg lesz), majd alatta '01 és kijelölésük után másolja lefelé. Az Excel szövegben is észreveszi a sorozatot.
  3. A 0-tól jobbra lévő cellába írja a =darabteli(Szint,szomszédos cella) képletet. A Szint legyen abszolút (fix) hivatkozás (kijelölés után rögtön F4). Ha elnevezett tartományra hivatkozik, vagy a teljes oszlopot jelölte ki, akkor nem kell F4. A szomszédos cella mindenképpen maradjon relatív hivatkozás.
  4. Másolja végig a szintszámok mellett a képletet.
  5. Ellenőrzésképpen jelölje ki a darabszámokat. Az Excel láblécében leolvasott összeg meg kell egyezzen az össz helyiségszámmal.

Mekkora területek tartoznak az egyes helyiség kategóriákhoz? Hogyan?

Elég sokféle kategória van, így az összes kategória területének meghatározása a kézi módszerrel szintén igen fáradságos lenne. A =szumha() függvénnyel viszont elég gyorsan meghatározható, csak szükségünk lesz a kategóriák listájára. A Szűrő ezt listázza, de innen nem lehet kimásolni a listát, helyette az Adatok [Data] sávmenün az Ismétlődések eltávolítása [Remove Duplicates] parancsot használjuk:

  1. A Helyiség kategória kód oszlopot másolja át a munkafüzeten belül egy másik munkalapra.
  2. A kijelölés megtartásával válassza 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 (ld. Rendezés), majd OK.
  4. A másolat helyén létrejön a Helyiség kategória kódok ismétlődésmentes listája.

Az Ismétlődések eltávolítása [Remove Duplicates] parancs és nem függvény. Így ha későbbiekben a listát lecseréljük egy újabb változatra amely új kategóriá(ka)t is tartalmaz, akkor az(ok) nem fog(nak) automatikusan megjelenni az ismétlődésmentes listán.

Az összegzés most már hasonló, mint az előző feladatban:

  1. Az első Helyiség kategória kódtól jobbra lévő cellában írja be a képletet: =szumha(Helyiség neve,szomszédos cella,Terület)
  2. A Helyiség neve és a Terület tartomány legyen abszolút (fix) hivatkozás (kijelölés után rögtön F4). Ha elnevezett tartományra hivatkozunk, vagy a teljes oszlopot jelöltük ki, akkor nem kell F4.
  3. A képlet végigmásolható a Helyiség kategória kódok mellett.
  4. Ellenőrzésképpen jelölje ki a feltételes összegeket. Az Excel láblécében leolvasott összeg pontosan meg kell egyezzen a Terület oszlop összegével.

Kategóriákba sorolás (ismétlés)

Mekkora a hasznos alapterület aránya? 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. Hogyan?

Minden helyiséget be kell sorolni a hasznos vagy az egyéb kategóriába: egy új oszlopban (pl. Hasznos) minden helyiségnél meg kell adni, hogy a hasznos vagy az egyéb kategóriába esik-e. A hasznosság a feladatkiírás szerint a Helyiség kategória kód alapján dönthető el: ha a kód Alap, Int, Okt vagy Spec kezdetű, akkor beleszámít a hasznos alapterületbe.

Ez eldönthető a =ha() [=if()] függvénnyel: 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 "vagy" feltétel miatt, és ezért utólag nehezen áttekinthető, javítható, bővíthető, másrészt – mivel az Excelben a képlet nem látszik, csak az eredmény, – nem dokumentálható.

Ezért inkább é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 hasznossági besorolás, ez átlátható és dokumentálható:

Helyiség kategória kód Hasznos
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

Ez alapján már hozzárendelhető az egyes helyiségekhez a hasznosság az =fkeres() [=vlookup()] függvénnyel. A függvénynek négy paramétere van:

  1. Egy másik munkalapon készítse el a Helyiség kategória kódok ismétlődésmentes listáját (ld. Adatok > Ismétlődések eltávolítása). Ha megvan a korábbi feladatból, akkor az is használható.
  2. Egy új oszlopban mindengyik mellé írja, hogy Hasznos vagy Egyéb kategóriába tartozik.
  3. A Helyiséglista táblázat jobb szélén készítsen egy új oszlopot: a fejléc legyen Hasznos, az első helyiség sorában a képlet legyen: =fkeres(Helyiség kategória kód;Összerendelési táblázat;2;hamis). Ha korábbi feladatból átvett összerendelési táblázatot készített, akkor a második oszlopban a területek vannak, a harmadik oszlopban pedig a hasznosság, így az összerendelési táblázat 3 oszlopból áll, és az =fkeres() harmadik paramétere 2 helyett 3 lesz.
  4. A hasznos alapterület most már összesíthető a Hasznos oszlop, mint feltétel segítségével a =szumha() függvénnyel, és ezt az össz területtel elosztva megkapja a hasznos alapterület arányát.

Alternatív megoldás: ha a korábbi feladatban a kódok szerinti összegzést már elkészítette, akkor az egyes kódok mellé írva a Hasznos vagy Egyéb kategóriákat újabb =szumha() függvénnyel közvetlenül is megkaphatja a hasznos alapterületet.

Az álmennyezet befüggesztéséhez m²-enként 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. Gazdaságossági okokból érdemes a lehető legrövidebb rudakat használni. Hány darab kell az egyes rudakból a teljes épülethez? Hogyan?

A helyiséglista egy új oszlopában minden helyiséghez meg kell adni, hogy milyen hosszú rudak kellenek, egy másik oszlopban pedig, hogy hány rúd kell. Az utóbbi egyszerűbb: a helyiség m²-ben mért területét fel kell kerekíteni a következő egész számra. Ehhez a =kerek.fel() [=roundup()] függvény használható.

A befüggesztési hossz a Belmagasság és az Álmennyezet magassága oszlopok különbségeként számítható, bár meglepő módon néhol negatív érték jön ki. Ezeket hagyjuk figyelmen kívül: ebben az esetben legyen 0 a befüggesztési hossz. Ezt elérhetjük a =ha() [=if()] függvénnyel: =ha(Belmagasság<Álmennyezet magassága;0;Belmagasság-Álmennyezet magassága), de egyszerűbben is megoldható: =max(Belmagasság-Álmennyezet magassága;0).

Az így kapott befüggesztési hosszakat a feladatban megadott tartományokba (kategóriákba) kell besorolni, amit az =fkeres() [=vlookup()] függvénnyel tehetünk meg. A Keresési érték most az előbb kiszámolt befüggesztési hossz, de az összerendelési Táblát el kell készítenünk. A Tábla első oszlopa a kategória határokat kell tartalmazza. Most a pontos egyezéses keresést nem használhatjuk, hiszen a befüggesztési hossz bármilyen szám lehet, az összerendelési táblában viszont csak az egyes tartományok határai szerepelnek. Ezért a Tartományban keres paraméter értéke igaz [true] lesz, és ilyenkor a Tábla első oszlopa a az egyes tartományok alsó határait kell tartalmazza számként, növekvő sorrendben:

-tól -ig Függesztőrúd típusa
0,00 -0,05 Nem kell
0,05 -0,10 10-es rúd
0,10 -0,25 25-ös rúd
0,25 -0,50 50-es rúd
0,50 -1,00 100-as rúd
1,00 -2,00 200-as rúd
2,00 - N/A

A második oszlop csak az érthetőség miatt került bele, a számításban nincs szerepe, ezért ide bármit írhatunk. A harmadik oszlop tartalmazza a rúd típusokat, ez lesz a hozzárendelés eredménye.

Ez alapján már hozzárendelhető az egyes helyiségekhez a rúd típus. Ha minden helyiségnél ismert a rudak száma és a szükséges rúd típusa, akkor a =szumha() [=sumif()] függvénnyel meghatározható, hogy melyik rúdtípusból mennyi kell.

  1. A Helyiséglista táblázat jobb szélén készítsen egy új oszlopot: a fejléc legyen Függesztőrudak száma, az első helyiség sorában a képlet legyen: =kerek.fel(Terület;0) [=roundup(Terület;0)]. Másolja végig a képletet.
  2. A Helyiséglista táblázat jobb szélén készítsen egy új oszlopot: a fejléc legyen Függesztőrúd hossza, az első helyiség sorában a képlet legyen: =max(Belmagasság-Álmennyezet magassága;0). Másolja végig a képletet.
    Ez a lépés el is maradhat, ha ezt a képletet közvetlenül az =fkeres() függvénybe írja be (ld. alább).
  3. Egy másik munkalapon készítse el az Összerendelési táblát:
    1. A -tól oszlopba írja be a tartományhatárokat m-ben, és formázza meg, hogy két tizedes látszódjon (Egyéni formátumkód: 0,00).
    2. Az -ig oszlopra csak esztétikai (érthetőségi) okból lehet szükség. Kényelmes előállításához írja be: ="-‍"&szöveg(eggyel balra és lefelé lévő cella;"0,00"). Az & jel összefűz két szöveget: az állandó - jelet és a következő tartomány elejét (eggyel balra és lefelé lévő cella), amit a =szöveg() [=text()] függvény a 0,00 formátumkóddal formázva ír ki.
    3. A Függesztőrúd oszlopba gépelje be a rúd megnevezését.
  4. A Helyiséglista táblázat jobb szélén készítsen egy új oszlopot: a fejléc legyen Függesztőrúd, az első helyiség sorában a képlet legyen: =fkeres(max(Belmagasság-Álmennyezet magassága;0);Összerendelési tábla;3;igaz) [=vlookup(max(Belmagasság-Álmennyezet magassága;0);Összerendelési tábla;3;true)]. Másolja végig a képletet.
    Itt az =fkeres() első paraméterébe (Keresési érték) egy másik függvényt ágyaztunk be: a függesztőrúd hosszát számító képletet. Ebben az esetben a 2. pontban létrehozott oszlopra nincs szükség.
  5. Az Összerendelési táblázat jobb szélén készítsen egy új oszlopot: a fejléc legyen Mennyiség, az első sorában a képlet legyen: =szumha(Függesztőrúd;Függesztőrúd típusa;Függesztőrudak száma). Másolja végig a képletet.

Adattisztítás

A Helyiség kategória kód és név oszlopok összetartoznak: adott kódhoz adott név tartozik. Ennek ellenére mind a kód, mind a név külön adatként szerepel, ráadásul többféle írásmóddal, hiszen ezeket az adatokat a CAAD programban többé-kevésbé egyesével, helyiségenként írták be. A későbbi kigyűjtések, összegzések pontos elkészítéséhez szükséges, hogy ezek egyféle írásmódúak és hibamentesek legyenek.

A kategória név egyértelműen kell, hogy következzen a kódból, ezért készítsen egy külön táblázatot a kód–név összerendelésről, és a név az így kapott segédtáblázat alapján legyen hozzárendelve a kódhoz. Ennek külön előnye, hogy a név-elírások automatikusan megjavulnak. Hogyan?

Előszőr elő kell állítani a Helyiség kategória kódok és nevek ismétlődésmentes listáját. Ezt az Adatok> Ismétlődések eltávolítása parancs segítségével tehetjük meg. A parancs egy ismétlődéseket (is) tartalmazó listát lerövidít úgy, hogy a többször előforduló elemeket kiszűri, és az eredeti listát helyettesíti az ismétlődésmentes listával.

  1. Készítsen másolatot a Helyiség kategória kód és a Helyiség kategória neve oszlopokról egy üres lapon.
  2. Jelölje ki a az átmásolt két oszlopot, és indítsa el az Ismétlődések eltávolítása parancsot.
  3. Az ismétlődések elátvolítása után rendezze Helyiség kategória kód szerint a listát

A kész segédtábla rendezése után látszik, hogy hibás adatok (elgépelés) miatt egyes sorok többször szerepelnek. Javítsa ki a segédtáblában az elgépeléseket.

A segédtábla adatait felhasználva az eredeti adattábla "Helyiség kategória neve" oszlopának adatait cserélje le egy képletre, ami a segédtábla alapján határozza meg a kategória nevet. Ez az FKERES() / VLOOKUP() függvénnyel tehető meg. Előszőr egy új, szomszédos oszlopban hozza létre a segédtábla alapján generált "Helyiség kategória neve" oszlopot.

Az elgépelések miatt egyes helyiségeknél nem fog megjelenni helyesen a kategória név. Szűrje le ezeket a sorokat úgy, hogy egy új oszlopban összehasonlítja a Kategória név számított és eredeti értékét, pl.: =(D1=E1) és javítsa ki a hibás kategória kódokat.

Kétdimenziós keresési táblázat használata

A mellékelt táblázat megadja, hogy adott padló- és falburkolat esetén milyen lábazatot kell elhelyezni. Egy új oszlopban jelenítse meg a kívánt lábazat kódját. Hogyan?

Másolja át a mellékelt táblázatot az Excel munkafüzet egy új lapjára.

Az INDEX(Segédtábla;Sor;Oszlop) függvény a megadott tartomány (segédtábla) adott sorából és oszlopából kivett értéket adja vissza, pl.:

A sor és oszlopszámhoz az aktuális helyiség Burkolat típusát és Falburkolat típusát kell megkeresni a Lábazat táblázat fejléc oszlopában és sorában. A VLOOKUP (FKERES) és HLOOKUP (VKERES) függvények viszont most nem használhatók, mert a megtalált értéket adják vissza, nem a keresési listában lévő pozíciót. Helyettük a MATCH (HOL.VAN) függvényt kell használni: MATCH(Keresési érték;Lista;Típus). Ez a függvény a Keresési érték listában elfoglalt pozícióját adja eredményül, ha a Típus 0, akkor pontos egyezés kell, ha 1, akkor közbenső értékeket is megtalál (mint a VLOOKUP hamis vagy igaz értéke). Így a 2 dimenziós táblában való keresés az alábbi módon nézhet ki:
INDEX(Lábazat;MATCH(Burkolat típusa;Padlóburkolatok;0);MATCH(Falburkolat típusa;Falburkolatok;0))
vagy a fenti táblázatban:
INDEX(Lábazat!$A$3:$U$19;MATCH(L2;Lábazat!$A$3:$A$19;0);MATCH(M2;Lábazat!$A$3:$U$3;0))

Lásd még: Kereső függvények

Adatok elemzése

Kimutatás (Pivot Table)

Összegezze egy táblázatban szintenként és Helyiség kategóriánként a nettó alapterületet. Hogyan?

A Kimutatás egy 2D táblázatot hoz létre, így egyszerre két – egymástól független – szempont szerint lehet összesíteni.

  1. Az aktív cella legyen a táblázat tetszőleges cellája (vagy jelölje a teljes táblázatot: ha elnevezte, akkor a név mezőt legördítve csak ki kell választani a nevet).
  2. Indítsa el a sávmenü Beszúrás fülén a Kimutatás parancsot [Insert > Pivot Table].
  3. A kimutatás forrása legyen Táblázat vagy tartomány (Table/Range), helye Új munkalap (New Worksheet)
  4. A táblázattartományt jól felismeri, ha a varázsló indítása előtt belekattintottunk. Ha mégsem, itt megadható a táblázat a fejlécsorral együtt.
  5. Az Excel egy új munkalapon létrehoz egy kimutatásvázat, amibe bele kell húzni a mezőneveket a Kimutatás mezőlistából. Húzzuk be a
    • Szint mezőt a Sor mezők (Row fields) helyére,
    • Helyiség kategória kód mezőt az Oszlop mezők (Column fields) helyére,
    • Terület mezőt az Adat mezők (Data Items) helyére.
    Ekkor a jobb szélen a szintenkénti, alul pedig a kategóriánkénti összes területet láthatjuk.

Összegezze egy táblázatban helyiségtípusonként és burkolat-típusonként a nettó alapterületet. Hogyan?

  1. Létrehozhat új kimutatást, vagy törölheti a meglévő kimutatás mezőit is (a mezőnevet tartalmazó fejléceket vonszolja a táblázaton kívülre).
  2. Az üres kimutatásvázba húzza bele a
    • Helyiségtípus mezőt a Sor mezők (Row fields) helyére,
    • Burkolat típusa mezőt az Oszlop mezők (Column fields) helyére,
    • Terület mezőt az Adat mezők (Data Items) helyére.
  3. Ha szintenként összegek is érdekesek, akkor húzza a Szint mezőt
    • az Oldal mezők területére, és így lapozni lehet a szintek között, vagy
    • az Oszlop mezők területére, a Helyiségtípus mező elé, így szintenkénti részösszegeket is kapunk.

Összegezze egy táblázatban szintenként és Falburkolat-típusonként a szükséges falburkolat mennyiségeket.

Összegezze egy táblázatban szintenként és Helyiség kategória kódonként a helyiségek számát. Hogyan?

Az adatterületre az ID mezőt húzza, majd itt a jobb gombbal kattintva válassza a Mezőbeállítások (Field Settings) parancsot. Itt módosítható az aggregátorfüggvény Darabra (Count).

Összegezze egy táblázatban szintenként és Helyiség kategóriánként a nettó alapterületet (keresse elő az első kimutatást). Jelenítse meg az adatokat a szintterületek százalékában. Hogyan?

Az adatterületen a jobb gombbal kattintva válassza a Mezőbeállítások (Field Settings) parancsot, és kattinston az Egyebek (Options) gombra. Az adatok megjelenítése (Show data as) legördülőben a Normál helyett válassza a Sor százalékát (% of row).

Egy lehetséges megoldás a szövegkezelő függvényekig.

Egy alternatív megoldás a szövegkezelő függvényekig (Teams).

Egy lehetséges megoldás a teljes gyakorlatra..



BME Építészeti Ábrázolás Tanszék