OpenOffice.org OpenOffice - 3.3 Základní průvodce stáhnout pdf (Strana 338)

Table 24. Breakdown of Listing 20.
Function Description
MATCH("Bob";A1:A16; 0)-1 Return 3 because Bob is the fourth entry in column 
A.
OFFSET(A1; 3; 3; 1; 2) Return the range D4:E4.
SUM(D4:E4) Return the sum of Bob’s quiz scores.
Tip
The first argument to OFFSET can be a range so you can use a defined 
range name.
INDEX returns cells inside a specified range
INDEX returns the cells specified by a row and column number. The row and column 
number are relative to the upper left corner of the specified reference range. For 
example, using  =INDEX(B2:D3; 1; 1) returns the cell B2. Table 25 lists shows the 
syntax for using the INDEX function.
Table 25. Syntax for INDEX.
Syntax Description
INDEX(reference) Return the entire range.
INDEX(reference; row) Return the specified row in the range.
INDEX(reference; row; 
column)
Return the cell specified by row and column. A row 
and column of 1 returns the cell in the upper left 
corner of the range.
INDEX(reference; row; 
column; range)
A reference range can contain multiple ranges. The 
range argument specifies which range to use.
The INDEX function can return an entire range, a row, or a single column (see Table
25). The ability to index based on the start of the reference range provides some 
interesting uses. Using the values shown in Table 23, Listing 21 finds and returns 
Bob’s quiz scores. Table 26 contains a listing of each function used in Listing 21.
Listing 21. Return Bob’s quiz scores.
=SUM(OFFSET(INDEX(A2:G16; MATCH("Bob"; A2:A16; 0)); 0; 3; 1; 
2))
Table 26. Breakdown of Listing 21.
Function Description
MATCH("Bob";A2:A16; 0) Return 3 because Bob is the third entry in column 
A2:A16.
INDEX(A2:A16; 3) Return A4:G4—the row containing Bob’s quiz 
scores.
OFFSET(A4:G4; 0; 3; 1; 2) Return the range D4:E4.
SUM(D4:E4) Return the sum of Bob’s quiz scores.
338 OpenOffice.org 3.3 Calc Guide

1 2 ... 333 334 335 336 337 338 339 340 341 342 343 ... 433 434

Komentáře k této Příručce

Žádné komentáře

OpenOffice.org OpenOffice - 3.3 Základní průvodce Strana 338

Komentáře k této Příručce

Související produkty a manuály pro Software OpenOffice.org OpenOffice - 3.3