Computer Literacy 2 

 2006/2007. spring semester 

Book of Premises

* Question, remark   Ő Back   

Topic: Spreadsheets basics

During this lecture we create a book of premises for a three-storey building. To do this, we:

The table created in this lesson is purely for educational purposes. The "See also" remarks refer to the corresponding page of Microsoft Office Online.

Reading external data

In Excel we can work not only with xls files, but also we can import other formats. The most simple format that can be interpreted as a table is a text file, where table rows corresponds to text rows, and the columns are separated by a special character in each row (e.g. tabulator or semicolon).

Read data from text file

Read the text file containing the initial data: tab_delimited.txt

Try different formats also:

  1. Download the text file to a temporary folder (right-click on the link and Save target as...)
  2. File> Open (In the Open dialog select Text files (*.prn, *.txt, *.csv) in the Files of type scroll-down, otherwise the downloaded file doesn't appear)
  3. Excel starts the Text Import Wizard, where in the first step select Delimited (if there is a special delimiter, e.g. tabulator or semicolon, first two cases), or Fixed width (if the area between columns are filled with spaces, third case).
  4. In the second step set the delimiter character or the column widths.

Best practice: Save the spreadsheet immediately and be careful to change the file type to Microsoft Excel Workbook (*.xls). If you don't change the file type than you save it as a text file, and thus you loose all formattings, formulae and diagrams.

If you successfully read these files, leave open only one.

Simple calculations

Simple formulae

Calculate the area of the premises in a new column.

    Enter simple formula: =D3*E3
  1. In the first empty column, in the second row (F2 cell) write the formula and press Enter
  2. Select the F2 cell again, copy, select the range below the cell (F3:F21) and paste. Check, that copied formulae are correct (click on F15 cell, and the formula should be =D15*E15).
    Faster way to copy in Excel is to drag the small button in the lower right corner of the active cell border. Double-clicking this button fills in the range automatically.

Calculate the volume of the premises, if the height is 2,70 m

  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

Absolute and relative references (the use of the $ sign)

Modify the calculation of the volumes so that each formulae should refer to a common cell that containes the height.

  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.

Defining names for cells

Modify the calculation of the volumes so that the formula should be "human readable". Define a name for the G1 cell (e.g. "Height") and for the F column (e.g. "Area"), and in the formulae use these names.

  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.

Define names for the other columns also: storey, premises, coating, width, length, volume.

Aggregation: the SUM() and AVERAGE() functions

Calculate the total net area of the building

Calculate the total net volume of the premises

  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

Calculate the average width of the premises

Calculate the average volume of the premises

  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

Formatting and ordering

These tasks are repetiotions, you may find help in the previous lesson.

Formatting

Display all values with two decimals and use the appropriate units (m, m², m3).

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

Ordering

Sort the premises by storeys and then in alphabetic order.

A továbbfejleszett táblázat

Tip: Be careful with the sorting: it sorts only the selected columns or range. If you select just one column, the table may get confused. If you just click in the table, Excel tries to find the range automatically: it extends until the first empty row and column.

Using conditions

Conditional branch: IF() function

Net area of the building should not exceed 580 m². Print the text "Adequate" if the total net area is smaller or equal to 580 m² in the neighboring cell. Print "Not adequate" if it is greater.

Debug your formula: reduce the area of a depository room by 5 m², and check the result: "Not adequate" should change to "Adequate".

  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)

How much m² of parquet do we need?
Tip: in a new column write the area of the room only if the coating is parquet, otherwise write 0, and sum the column.

  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

How much m² of coating do we need from the different types?
Tip: use new columns for each type of coating. Write the names of coating to header row, and the formulae should refer to these names. Use the $ sign appropriate to be able to copy the formula horizontally and vertically.

  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.

Conditional summation: the SUMIF() function

Calculate the total area of parquet without the auxiliary column.
Tip: use the SUMIF() function.

  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

Calculate the total are of the different coatings without the auxiliary columns.
Tip: use the SUMIF() function.

  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.

Nested conditions

The small (<25 m²) office rooms are to let, the others are for sale. For each premises write if it is to let or for sale, for premises that are not office rooms don't write anything.
Tip: use nested IF() functions.

  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

All coating corresponds to a footing. Indicate the footing for each premises:

Tip: use nested IF() functions.

  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

Look up values

Look up values in a table: VLOOKUP() and HLOOKUP() functions

Indicate the footing like in the previous task, but suppose, that more types of coatings and foongs should not imply more complicated formula.
Tip: use the VLOOKUP() function.

  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

Repetition

How much m of footing do we need for the different footing types?
Tip: calculate the perimeters and use the SUMIF() function.

  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

Look up values in ordered table: VLOOKUP() and HLOOKUP() functions

Premises that are smaller than 50 m³ need "small heaters", "medium heaters" are sufficient for rooms <100 m³, others need "large heaters". Indicate the appropriate heater for ech premises.
Tip: use the VLOOKUP() function.

  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.

Conditional counting of cells

Repetition

How much do we need from the different heaters?
Tip: use auxiliary columns for each type of heater to print 1 or 0 for each premises, and sum the columns.

Conditional counting of cells: COUNTIF() function

How many office rooms are in the building? Tip: use the COUNIF() function.

  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)

How much do we need from the different heaters? Solve without auxiliary columns. Tip: use the COUNTIF() function.

  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.

Final version:

A kész táblázat