Építész-informatika 1 |
2012/2013. év - 2. félév |
Műveletek adattáblákkal |
Összes kinyitása/becsukása
A gyakorlat témája: Műveletek adattáblákkal
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.
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.
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:
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.
Formázza meg a táblázatot.
Hogyan?
Megjegyzés: csoportok között mindenképpen szükséges egy elválasztó oszlop, a közvetlenül egymás mellett létrehozott csoportokat az Excel összeolvasztja egy csoportba. Az elválasztó oszlop ne legyen egy új, üres oszlop, mert akkor a rendezés és egyéb műveletek szempontjából a lista szétesik külön listákra.
Egy új oszlopban számítsa ki a helyiségek burkolandó falfelületeit.
Hogyan?
Egy új oszlopban számítsa ki a helyiségek térfogatát. Egyes helyiségekben az álmennyezet magassága nagyobb, mint a belmagasság, itt a belmagassággal számoljon.
Hogyan?
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?
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.
Rendezze sorba a helyiségeket nagyság szerint.
Hogyan?
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 összekeveredhetnek. 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 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).
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 az elején,
ezért eszerint nem lehet sorrendezni. Az ID-t fel kell darabolni három oszlopba, hogy rendezni lehessen.
Hogyan?
Állítsa be, hogy csak az oktatási termek látszódjanak. Hány m² oktatási terület van az épületben?
Hogyan?
Az Adatok lapon a Rendezés és Szűrés részen kapcsolja be a Szűrőt. Ekkor a fejlécsorban megjelennek a szűrő gombjai. A Helyiség kategória neve oszlopban kattintson a szűrőgombra, törölje az összes kijelölését, majd jelölje ki az OKTATÓTERMEK sort.
Ekkor azokat a helyiségeket, amelyek nem oktatási termek, elrejti. A megmaradó sorok száma kék színű lesz, és a Helyiség kategória neve oszlopban a szűrőgombon egy tölcsér jelenik meg, jelezve, hogy itt aktív a szűrés.
A Terület oszlopot kijelölve a jobb alsó sarokban, az Állapotsorban látszik a kijelölt cellák száma és az értékek összege. Az Állapotsoron jobb-klikkelve állítható, hogy milyen információkat jelenítsen meg itt a program.
A szűrés megszüntetéséhez az aktív szűrőgombot lenyitva válassza a Szűrő törlését.
Hány darab 100 m²-nél nagyobb oktatási terem van?
Hogyan?
Az előbbi szűrés beállítása után válassza a Terület oszlopban a Számszűrők közül a Nagyobb, mint lehetőséget, és a megjelenő ablakban adja meg a 100 m²-t.
Egy tetszőleges oszlop kijelölése után az Állapotsorban olvassa le a cellák számát.
Van minden emeleten akadálymentes WC?
Határozza meg szintenként a nettó alapterületet (nettó alapterület a helyiségek területeinek az összege).
Hogyan?
A szintenkénti rendezésnél már szétválasztottuk az ID mezőt, így egy új munkalapon 0–7-ig felsorolva a számokat a SZUMHA/SUMIF függvénnyel meghatározható a Terület oszlop összegzésével az egyes szintek nettó területe.
Hány m² kell az egyes burkolatfajtákból?
Hogyan?
Előszőr elő kell állítani a burkolattípusok ismétlődésmentes listáját.
Ezt az Adatok/Ismétlődések eltávolítása paranccsal tehetjük meg. Mivel a parancs az eredeti adatok helyén hozza létre az ismétlődésmentes listát, előszőr másolja át a Burkolat típusa oszlopot egy új munkalapra, és ezután indítsa a parancsot a kijelölés megtartásával.
Ezután a SZUMHA/SUMIF függvény segítségével összegezhető az egyes burkolatfajtákhoz tartozó Terület: =SZUMHA(Burkolat típusa;burkolatfajta;Terület)
Határozza meg ugyanezt a falburkolatokra.
Hány helyiség van az egyes kategóriákban?
Hogyan?
Új munkalapon hozza létre a kategóriák listáját a Helyiség kategória kód oszlopot átmásolva és az ismétlődéseket eltávolítva. Ezután a DARABTELI/COUNTIF függvény segítségével meghatározható az egyes kategóriákhoz tartozó helyiségek száma.
A mellékelt táblázat megadja az egyes burkolatokhoz tartozó lábazatokat. Hány folyóméter kell az egyes lábazatfajtákból?
Hogyan?
A Helyiséglista egy új oszlopában meg kell jeleníteni a lábazatot.
Ehhez másolja előszőr a lábazatok táblázatát egy új munkalapra, a helyiséglista táblázat melletti első üres oszlop fejléce legyen Lábazat, és az FKERES(...;HAMIS)/VLOOKUP(...;FALSE) függvénnyel keresse meg minden helyiségnél a burkolattípusnak megfelelő lábazatot.
Ezután a lábazatok táblázata mellett, egy új oszlopban meghatározható a kerületek összege a SZUMHA/SUMIF függvénnyel.
Ellenőrizze a bevilágítás mértékét az egyes helyiségekre, és jelölje meg a nem megfelelő helyiségeket. A bevilágítási arány az ablakok területének és az alapterületnek a hányadosa, huzamosabb emberi tartózkodásra alkalmas helyiségekben értéke legalább 20% kell legyen.
Hogyan?
A nem megfelelő helyiségeket feltételes formázással vagy szűréssel lehet kiemelni. Bármelyiket is választjuk, kell egy oszlop, amelyben látszik a bevilágítási arány, vagy az, hogy a helyiség nem alkalmas huzamosabb emberi tartózkodásra (pl. raktár, szerverszoba, irattár, mosdó, etc.).
A huzamosabb emberi tartózkodásra alkalmasságot talán a Helyiség neve alapján lehet eldönteni, az egyes Helyiség kategóriák és Speciális kategóriák láthatóan tartalmaznak mindkét féle helyiséget, tehát ezek alapján nehezebb lenne dönteni. Ezért a Helyiség neveket kell átmásolni egy új lapra, és az ismétlődések eltávolítása után minden Helyiség név mellé lehet írni, hogy alkalmas, vagy nem alkalmas.
Mielőtt a bevilágítási arány kiszámolnánk minden helyiségre, a helyiség nevét megkeressük az előbbi táblázatban (FKERES(...;HAMIS)/VLOOKUP(...;FALSE)), és csak akkor számolunk bevilágítási arányt, ha alkalmas, különben kiírjuk, hogy pl. N/A (Nem alkalmas). Ehhez a HA/IF függvény használható: =HA(FKERES(...;HAMIS)="Alkalmas";Ablakfelület/Terület;"N/A")
Másik lehetőség, hogy csak az alkalmas helyiségneveket írjuk le egy listába. Ekkor az FKERES függvény helyett használhatjuk a HOL.VAN(...;0)/MATCH(...;0) függvényt, amely ugyanúgy működik, de a megtalált elem táblázatbeli pozícióját (indexét) adja eredményül. Ha a helyiség nem alkalmas, akkor a HOL.VAN/MATCH függvény hibaüzenetet ad (hasonlóan az FKERES függvényhez), amelyet a HIBÁS/ISERROR függvénnyel vizsgálhatunk meg: =HA(HIBÁS(HOL.VAN(...;HAMIS));Ablakfelület/Terület;"N/A")
Az egyes helyiségekben radiátorokat kell elhelyezni a fűtött térfogat arányában. A fűtési teljesítményigény általában 30 W/légm³. 15 légm³ alatt nem fűtjük a helyiséget, 100 légm³ felett egyedileg állapítjuk meg fűtést, 500 légm³ felett pedig légfűtést alkalmazunk. A 15–100 légm³ közötti tartományban 600x400 mm, 600x600 , 600x1000 , vagy 600x1600 mm méretű, 22K típusú lapradiátorokat alkalmazunk a szükséges számban. A radiátorok teljesítménye rendre 764 W, 1146 W, 1911 W és 3057 W. Határozza meg, hogy az egyes radiátorokból hány darab kell, és hány helyen kell egyedi vagy légfűtést alkalmazni.
Hogyan?
Létre kell hozni a segédtáblázatot, amelyből a térfogatok alapján meg lehet határozni a fűtési rendszert. Ennek a táblázatnak az első oszlopában a térfogat lépcsők vannak (0, 15, 764/30, 1146/30, 1911/30, 100 és 500), második oszlopában pedig a hozzájuk tartozó fűtési rendszerek (nincs, 600x400, 600x600, 600x1000, 600x1600, egyedi fűtés, légfűtés). A táblázat alapján FKERES(...;IGAZ)/VLOOKUP(...;TRUE) függvénnyel minden helyiséghez meghatározható a fűtési rendszer.
A segédtáblázat új oszlopában a DARABTELI/COUNTIF függvénnyel meghatározható az egyes fűtési rendszerek szükséges darabszáma.
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 paranccsal tehetjük meg. Mivel a parancs az eredeti adatok helyén hozza létre az ismétlődésmentes listát, előszőr másolja át a Helyiség kategória kód és a Helyiség kategória neve oszlopokat egy új munkalapra, és ezután indítsa a parancsot a kijelölés megtartásával.
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.
A lábazat fajtája sokszor nem csak a burkolattól, hanem falburkokat fajtájától is függ. 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. Az FKERES/VLOOKUP és VKERES/HLOOKUP 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 HOL.VAN/MATCH függvényt kell használni: MATCH(Keresési érték;Lista;Típus), itt a lista lehet függőleges, vagy vízszintes vektor is. 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 az FKERES 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ábazatok táblája;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
Számítsa ki a szintenkénti és a teljes nettó alapterületet a Részösszegek paranccsal.
Hogyan?
A Részösszegek parancs használata előtt az adattáblát rendezni kell a kiválasztott csoportosítási szempont szerint. Emiatt egy táblázatot csak egyszerre csak egy szempont szerint lehet összesíteni, ha az adatokra szükség van később is, akkor másolatot kell készíteni az összegzett adatokról.
Lásd még: A Részösszegek (Subtotals) parancs
Számítsa ki falburkolat-típusonkénti és a teljes burkolandó felületet.
Hogyan?
Lásd még: A Részösszegek (Subtotals) parancs
Számítsa ki a szintenkénti és a teljes nettó alapterületet az Összesítés paranccsal.
Hogyan?
Az Adatok/összesítés parancs egy külön táblázatban képes a Részösszeg parancshoz hasonló összesítéseket létrehozni. A csoportosítási szempont csak a táblázat első oszlopa lehet, amely szerint rendezve kell legyen a táblázat. Az összesítő táblázat az aktív cellától jobbra és lefelé jön létre.
Számítsa ki falburkolat-típusonkénti és a teljes burkolandó felületet.
Hogyan?
A falburkolat-típusonkénti burkolandó felület számításához előre kell venni a Falburkolat típusa oszlopot, és eszerint kell rendezni a táblázatot.
Tipp: az oszlop átmozgatásához jelölje ki a teljes oszlopot, majd a kijelölés peremét megfogva a Shift gomb nyomvatartása mellett vonszolja az oszlopot az új helyére.
Határozza meg a helyiségek átlagos területét szintenként és összesen. Használja a Részösszegek parancsot.
Hogyan?
Határozza meg szintenként és összesen a helyiségek számát. Használja az Összesítés parancsot.
Hogyan?
Lásd még: A Részösszegek (Subtotals) parancs
Összegezze egy táblázatban szintenként és Helyiség kategóriánként a nettó alapterületet.
Hogyan?
A Kimutatás az Összesítéshez hasonlóan egy 2D táblázatot hoz létre, így egyszerre két – egymástól független – szempont szerint lehet összesíteni, ráadásul nem szükséges előre rendezni a táblázatot.
Összegezze egy táblázatban helyiségtípusonként és burkolat-típusonként a nettó alapterületet.
Hogyan?
Ö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).