Számítógépek alkalmazása 1 

 2005/2006. év - I. félév 

A táblázatkezelés alapjai II.

* Kérdés, észrevétel   Ő Vissza   

Összes kinyitása/becsukása

A gyakorlat témája: A táblázatkezelés alapjai.

A gyakoralat során egy helyiségkönyvet készítünk el:

Egy lehetséges megoldás:
A kész táblázat

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 Ofiice Online megfelelő oldalaira mutató linkek.

Külső adatok beolavasása

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_helyiseglista.txt Megoldás

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 kiterjesztésű fálok, 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 mentji 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.

Egyszerű képletek és függvények

Egyszerű képletek

Egy új oszlopban számítsa ki a helyiségek alapterületeit. Megoldás

  1. Az első üres oszlop fejléc utáni első sorába (az F2 cellába) írja be az alábbi képletet: =D2*E2 (a D és E oszlopok tartalmazzák a helyiségek szélességeit és hosszúságait)
  2. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

Egy új oszlopban számítsa ki a helyiségek térfogatát, ha a belmagasság 2,70 m Megoldás

  1. Az első üres oszlop fejléc utáni első sorába (az G2 cellába) írja be az alábbi képletet: =F2*2,70 (az F oszlop tartalmazza a helyiségek alapterületeit)
  2. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Abszolút és relatív hivatkozások (A dollárjel használata)

Javítsa ki a térfogatszámítást úgy, hogy a belmagasság értékét minden képlet ugyanabból a cellából olvassa ki! Megoldás

  1. A fejléc fölé szúrjon be egy sort, és egy tetszőleges cellába (itt: G1) írja be a belmagasságot (2,70)
  2. A térfogatadatokat tartalmazó oszlop fejléc utáni első sorában (a G3 cellába) javítsa ki a képletet: =F3*G$1 (az F oszlop tartalmazza a helyiségek alapterületeit, a G1 cella pedig a belmagasságot)
  3. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Hibakeresés: Kattintson a hibásnak vélt képletet tartalmazó cellára színes keretek jelennek meg, amelyek jelzik azokat a cellákat, amelyekre ez a képlet hivatkozik. Ellenőrizze, hogy a képlet a megfelelő cellákra hivatkozik-e.
Hibakeresés: Ha a fenti képletben a dollárjelről elfeledkezünk, az automatikus kitöltés után hibás képletek keletkeznek.

Cellák elnevezése

Javítsa ki a térfogatszámítást úgy, hogy könnyen olvasható képleteket kapjon: nevezze el a G1 cellát és az F oszlopot, és a képletekben ezekre a nevekre hivatkozzon! Megoldás

  1. Jelölje ki az elnevezendő cellát (G1), majd Beszúrás/Név/Név megadása (INsert/Name/Define). Adja meg a belmagassag nevet.
  2. Jelölje ki az elnevezendő oszlopot (F), majd Beszúrás/Név/Név megadása (INsert/Name/Define). Adja meg a terulet nevet.
  3. A térfogatadatokat tartalmazó oszlop fejléc utáni első sorában (az G3 cellába) javítsa ki a képletet: =terulet*belmagassag (a terulet és belmagassag a korábban megadott nevek)
  4. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Lásd még: Cella elnevezése munkafüzetben
Hibakeresés: A Beszúrás/Név/Név megadása 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 hibajelzést jelenik meg.

Nevezze el a többi oszlopot is: szint, nev, burkolat, szelesseg, hossz, terulet, terfogat.

Összegzés: a SZUM() [angolul: SUM()] függvény

Számítsa ki az összes nettó alapterületet Megoldás

Számítsa ki az összes nettó helyiségtérfogatot Megoldás

  1. Írja az F23 cellába az alábbi képletet: =SZUM(F3:F22)
  2. Írja a G23 cellába az alábbi képletet: =SZUM(G3:G22)

Lásd még: A SZUM() függvény

Átlagszámítás: az ÁTLAG() [angolul: AVERAGE()] függvény

Számítsa ki a helyiségek átlagos szélességét. Megoldás

Számítsa ki az átlagos helyiségtérfogatot. Megoldás

  1. Írja a D24 cellába az alábbi képletet: =ÁTLAG(D3:D22)
  2. Írja a G24 cellába az alábbi képletet: =ÁTLAG(G3:G22)

A továbbfejleszett táblázat

Lásd még: Az ÁTLAG() függvény

Számformázás és sorbarendezés

Az alábbi feladatok nem igényelnek új ismereteket, megoldásukhoz segítséget az első gyakorlat szövegében talál.

Számformázás

A számokat kerekítse két tizedesjegyre és jelenítse meg a megfelelő mértékegységeket! (m, m2, m3). Megoldás

Tipp a felsőindexben levő 2-es és 3-as megjelenítéséhez: Speciális karaktereket szúrhat be a következőképpen: Váltson angol billentyűzetkiosztásra, majd (bekapcsolt NUM LOCK mellett) tartsa nyomva az ALT billentyűt és a numerikus billentyűzeten írja be a karakter Unicode értékét (kettes felsőindexben: 0178, hármas felsőindexben: 0179).

Sorbarendezés

Rendezze sorba a helyiségeket előbb szintenként, majd ABC sorrendben. Megoldás

A továbbfejleszett táblázat

Hibakeresés: Vigyázat! Csak a kijelölt oszlopok sorrendje változik meg, ha pl. csak egyik oszlopot jelöljük ki rendezésre, az adatok összekeveredhetnek!

Feltételes elágazások, feltételes összegzés

Feltételes elágazás: a HA() [angolul: IF()] függvény

Az épület nettó alapterülete nem lehet nagyobb, mint 580 m2. Írja az alapterület mellé, hogy MEGFELEL, ha kisebb vagy egyenlő, mint 580 és NEM FELEL MEG, ha nagyobb. Megoldás

  1. Az alapterületek összegét tartalmazó cella alá (a F24 cellába) írja be az alábbi képletet: =HA(F23<=580;"MEGFELEL";"NEM FELEL MEG")

A továbbfejleszett táblázat

Lásd még: A HA() függvény
Hibakeresés: Az egyik tároló alapterületét csökkentse 5 m2-rel, és ellenőrizze az eredményt. (egyenlő, tehát MEGFELEL)
Hibakeresés: Az egyik tároló alapterületét csökkentse további 5 m2-rel, és ellenőrizze az eredményt. (kisebb, tehát MEGFELEL)

Hány m2 parketta kell? (egy új oszlopba írja be a helyiség alapterületét, de csak akkor, ha ott parketta van, egyébként írjon 0-t. majd az így kapott értékeket adja össze!) Megoldás

  1. Egy új oszlop első sorába (a H3 cellába) írja be az alábbi képletet: =HA(burkolat="parketta";terulet;0)
  2. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.
  3. Az új oszlop alatt helyezzen el egy SZUM() függvényt, ami az oszlop celláit összegzi.

A továbbfejleszett táblázat

Hány m2 burkolat kell az egyes típusokból? (Vegyen fel újabb oszlopokat, a burkolatnevek kerüljenek a fejlécbe, a képletek erre hivatkozzanak! Használja a dollárjelet, hogy az automatikus képletkitöltés mindkét irányba működjön.) Megoldás

  1. Vegyen fel újabb oszlopokat, a burkolatnevek kerüljenek a fejlécbe.
  2. A H3 cellában javítsa a képletet: =HA(burkolat=H$2;terulet;0)
  3. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa jobbra) a sor többi celláját is.
  4. Kattintson az új képleteket tartalmazó cellák jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.
  5. Az új oszlopok alatt helyezzen újabb SZUM() függvényeket, amik az oszlop celláit összegzik.

A továbbfejleszett táblázat

Hibakeresés: Adja össze a burkolatonként kapott részösszegeket, és ellenőrizze, hogy az összeg megegyezik-e a teljes nettó alapterülettel.

Javítsa ki a fentieket úgy, hogy a képletek könnyen olvashatók legyenek. Alkalmazzon cellaneveket. Megoldás

  1. -
  2. -
  3. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

Feltételes összegzés: a SZUMHA() [angolul: SUMIF()] függvény

Oldja meg a fenti feladatot (parketta alapterületének kiszámítása) segédtáblázat nélkül! (Használja a SZUMHA függvényt) Megoldás

  1. Egy üres cellába (itt:D26) írja be a következő képletet: =SZUMHA(C$3:C$22;"parketta";F$3:F$22)

Lásd meg: A SZUMHA() függvény

Oldja meg a fenti feladatot (burkolatonkénti alapterületek kiszámítása) segédtáblázat nélkül! (Használja a SZUMHA függvényt) Megoldás

  1. Írja a C26, C27 és C28 cellákba a 3 különböző burkolatnevet.
  2. A D26 cellában levő képletet javítsa ki: =SZUMHA(C$3:C$22;C26;F$3:F$22)
  3. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Hibakeresés: Adja össze a burkolatonként kapott részösszegeket, és ellenőrizze, hogy megegyezik-e a teljes nettó alapterülettel.

Egymásba ágyazott HA() [angolul: IF()] függvények

A tulajdonos kiadja a 25 m2-nél kisebb irodákat, a többit eladja. Írja egy új oszlopba minden helyiséghez hogy az adott helyiség ELADÓ vagy KIADÓ. A többi helyiséghez (nem irodák) ne írjon semmit. (Használjon két egymásba ágyazott HA() függvényt!) Megoldás

  1. A H3 cellába írja be az alábbi képletet: =HA(nev="Iroda";HA(terulet<25;"KIADÓ";"ELADÓ");"")
  2. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Egy új oszlopban tüntesse fel helyiségenként a lábazat anyagát. Minden burkolattípushoz tartozik egy lábazat:

Használjon egymásba ágyazott HA() függvényeket!) Megoldás

  1. Az I3 cellába írja be az alábbi képletet: =HA(burkolat="parketta";"szegőléc";HA(burkolat="greslap";"gres szegély";"nincs"))
  2. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Értékek keresése

Értékek keresése egy táblázatban: az FKERES() és a VKERES() függvény [angolul: VLOOKUP() és HLOOKUP()]

Oldja meg ugyanezt a feladatot úgy, hogy a burkolatok sokfélesége ne jelentsen bonyolultabb képleteket! (Használja az FKERES() függvényt!) Megoldás

  1. Az E26, E27 és E28 cellákba írja be az adot burkolatnak megfelelő lábazatok megnevezéseit.
  2. Nevezze el a C26:E28 tartományt burkolatok-nak
  3. Az I3 cellában javítsa a képletet: =FKERES(burkolat;burkolatok;3;HAMIS)
  4. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Lásd még: Az FKERES() függvény

ISMÉTLÉS: A lábazat mennyiségének kiszámítása

Hány m lábazat kell lábazattípusonként? (Egy új oszlopba számítsa ki a kerületeket, és használja a SUMIF() függvényt.) Megoldás

  1. Szúrjon be egy új sort az alapterületek után, ebben számítsa ki a kerületeket.
  2. Írja az F26 cellába az alábbi képletet: =SZUMHA(J$3:J$22;E26;G$3:G$22)
  3. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Az FKERES() és VKERES() [angolul: VLOOKUP() és HLOOKUP()] függvény használata rendezett tartományokban való kereséséhez

Az 50 m3-nél kisebb helyiségekbe "kis fűtőtestek" kerülnek, 50-m3-től "közepes fűtőtestek", 100 m3-től pedig "nagy fűtőtesek". Egy új oszlopban tüntesse fel helyiségenként a fűtőtest típusát. (Használjon két egymásba ágyazott HA() függvényt!) Megoldás

  1. Írja a K3 cellába az alábbi képletet: =HA(terulet<20;"kis fűtőtest";HA(terulet<50;"közepes fűtőtest";"nagy fűtőtest"))
  2. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Oldja meg ugyanezt a feladatot úgy, hogy a fűtőtestek sokfélesége ne jelentsen bonyolultabb képleteket! (Használja az FKERES() függvényt!) Megoldás

  1. Készítsen egy segédtáblázatot a J25:K28 tartományban:
    HatárértékFűtőtest
    0Kis fűtőtest
    20Közepes fűtőtest
    50Nagy fűtőtest
  2. A J26:K28 tartományt (a fejlévet ne!) nevezze el futotestek-nek
  3. Írja a K3 cellába az alábbi képletet: =FKERES(terulet;futotestek;2;IGAZ)
  4. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Lásd még: Az FKERES() függvény
Hibakeresés: Ha tartományban keres, a táblázat (amelyben keres) első oszlopának/sorának étékei növekvő sorrendben kell legyenek, különben hibás eredményt kap.

Cellák összeszámlálása feltételek alapján

ISMÉTLÉS: Fűtőtestek mennyiségének kiszámítása

Melyik fűtőtestből hány db van? (Ismétlés: használjon fűtőtesttípusonként egy segédoszlopot, ezek közül az adott fűtőtestnek megfelelőben jelenítsen meg egy 1-est, a többiben 0-t, ehhez hesználja a HA() függvényt. Ezután összegezze az oszlopk tartalmát.)

Cellák összeszámlálása feltételek alapján: a DARABTELI() [angolul: COUNTIF()] függvény

Hány iroda van az épületben? (Használja a DARABTELI függvényt.) Megoldás

  1. Egy üres cellába (itt:B24) írja az alábbi kéletet: =DARABTELI(B3:B22;"iroda")
  2. Állítsa be a számformátumot (db).

A továbbfejleszett táblázat

Lásd még: A DARABTELI() függvény
Hibakeresés: Szöveges értékeket (pl. Iroda) mindig idézőjelbe kell tenni a képletekben: pl. =DARABTELI("Iroda";a1:a10)

Oldja meg a fenti feladatot (Melyik fűtőtestből hány db van?) segédtáblázat nélkül! (Használja a DARABTELI függvényt) Megoldás

  1. Egészítse ki a fűtőtesteket tartalmazó segédtáblázatot (J25:K28) egy újabb oszloppal (L25:L28), a fejlécbe írja be: Mennyiség
  2. Írja az L26 cellába az alábbi képletet: =DARABTELI(K$3:K$22;K26)
  3. Kattintson az új képletet tartalmazó cella jobb alsó sarkára, és "automatikusan töltse ki" (tartsa nyomva az egér bal gombját és az egeret mozgassa lefelé) a többi cellát is.

A továbbfejleszett táblázat

Hibakeresés: Adja össze a fűtőtesttípusonként kapott darabszámokat, és ellenőrizze, hogy az összeg megegyezik-e a helyiségek számával.

A kész táblázat:
A kész táblázat