OpenOffice.org 2.x Betriebsanweisung PDF herunterladen (Seite 261)

Chapter 4: Spreadsheets in Calc

Addressing Cells

Cells can be addressed directly by means of their coordinates in the row-and-column matrix.

Use absolute or relative referencing, or give cell ranges their own names.

Addresses and References, Absolute and Relative

Relative Addressing

The cell in column A, row 1 is addressed as A1. Address a range of adjacent cells by first

entering the coordinates of the upper left cell of the area, then a colon followed by the

coordinates of the lower right cell. For example, the square formed by the first four cells in

the upper left corner is addressed as A1:B2. By addressing an area in this way, one is

making a relative reference to A1:B2. Relative here means that the reference to this area will

be adjusted automatically when the formulae are copied.

Absolute Addressing

Absolute references are the opposite of relative addressing. A dollar sign is placed before

each letter and number in an absolute reference, e.g., $A$1:$B$2.

Calc can convert the current reference, in which the cursor is positioned in the input line,

from relative to absolute and vice versa by pressing Shift +F4. When starting with a

relative address such as A1, the first time this key combination is pressed, both row and

column are set to absolute references ($A$1). The second time, only the row (A$1), and the

third time, only the column ($A1). Pressing the key combination once more, both column

and row references are switched back to relative (A1)

Calc shows the references to a formula. If, for example the formula

=SUM(A1:C5;D15:D 24) is clicked in a cell, the two referenced areas in the sheet will be

highlighted in colour. For example, the formula component A1:C5 may be in blue and the

cell range in question bordered in the same shade of blue. The next formula component

D15:D 24 can be marked in red in the same way.

When to Use Relative and Absolute References

First examine what happens without a relative reference. Assume a calculation in cell E1 that

sums the cells in range A1:B2. The formula to enter into E1 would be: =SUM(A1:B2). If,

later, a new column must be in front of column A, the elements to be added would then be in

B1:C2 and the formula would be in F1, not in E1. After inserting the new column, all

formulae would therefore have to be checked and corrected in the current sheet, and possibly

in other sheets.

Fortunately, Calc does this work automatically. After inserting a new column A, the formula

=SUM(A1:B2) is updated to =SUM(B1:C2). Row numbers are also automatically adjusted

when a new row 1 is inserted. Absolute and relative references are always adjusted in Calc

whenever the referenced area is moved. But be careful when copying a formula since in that

case only the relative references will be adjusted but not any absolute references.

OpenOffice.org User Guide for 2.x 227

1 2 ... 256 257 258 259 260 261 262 263 264 265 266 ... 507 508

Keine Kommentare

OpenOffice.org 2.x Betriebsanweisung Seite 261