OpenOffice.org OpenOffice - 3.3 Grundlegende Anleitung PDF herunterladen (Seite 336)

ADDRESS returns a string with a cell’s address

Use ADDRESS to return a text representation of a cell address based on the row,

column, and sheet; ADDRESS is frequently used with MATCH. The supported forms

for ADDRESS are as follows:

ADDRESS(row; column)

ADDRESS(row; column; abs)

ADDRESS(row; column; abs; sheet)

The row and column are integer values where ADDRESS(1; 1) returns $A$1. The abs

argument specifies which portion is considered absolute and which portion is

considered relative (see Table 21); an absolute address is specified using the $

character. The sheet is included as part of the address only if the sheet argument is

used. The sheet argument is treated as a string. Using

ADDRESS(MATCH("Bob";A1:A5 ; 0); 2) with the data in Table 20 returns $B$2.

Tip

Calc supports numerous powerful functions that are not discussed here.

For example, the ROW, COLUMN, ROWS, and COLUMNS statements are

not discussed; a curious person would investigate these functions.

Table 21. Values supported by the abs argument to ADDRESS.

Value Description

1 Use absolute addressing. This is the default value if the argument is

missing or an invalid value is used. ADDRESS(2; 5; 1) returns $E$2.

2 Use an absolute row reference and a relative column reference.

ADDRESS(2; 5; 2; "Blah") returns Blah.E$2.

Use a relative row reference and an absolute column reference.

ADDRESS(2; 5; 3) returns $E2.

Use relative addressing. ADDRESS(2; 5; 4) returns E2.

INDIRECT converts a string to a cell or range

Use INDIRECT to convert a string representation of a cell or range address to a

reference to the cell or range. Table 22 contains examples accessing data as shown in

Table 20.

Table 22. Examples using INDIRECT.

Example Comment

INDIRECT("A2")

Returns cell A2, which contains Bob.

INDIRECT(G1) If Cell G1 contains the text A2, then this returns

Bob.

SUM(INDIRECT("B1:B5"))

Returns the sum of the range B1:B5, which is 194.

INDIRECT(ADDRESS(2; 1))

Returns the contents of cell $A$2, which is Bob.

OFFSET returns a cell or range offset from another

Use OFFSET to return a cell or range offset by a specified number of rows and

columns from a given reference point. The first argument, specifies the reference

336 OpenOffice.org 3.3 Calc Guide

1 2 ... 331 332 333 334 335 336 337 338 339 340 341 ... 433 434

Kommentare zu diesen Handbüchern

Keine Kommentare

OpenOffice.org OpenOffice - 3.3 Grundlegende Anleitung Seite 336

Kommentare zu diesen Handbüchern

Verwandte Produkte und Handbücher für Software OpenOffice.org OpenOffice - 3.3