OpenOffice.org OpenOffice - 3.3 Guía básica descargar pdf (Pagina 337)

point. The second and third arguments specify the number of rows and columns to 
move from the reference point; in other words, where the new range starts. The 
OFFSET function has the following syntax:
OFFSET(reference; rows; columns)
OFFSET(reference; rows; columns; height)
OFFSET(reference; rows; columns; height; width)
Tip
If the width or height is included, the OFFSET function returns a range. If 
both the width and height are missing, a cell reference is returned.
If the height or width are missing, they default to 1. If the height is present, then a 
range reference is returned rather than a cell reference. Using values from Table 23, 
Listing 19 uses OFFSET to obtain the quiz scores for the student named Bob.
Listing 19. Complex example of OFFSET.
=SUM(OFFSET(INDIRECT(ADDRESS(MATCH("Bob";A1:A16; 0); 4)); 0; 0; 1; 
2))
In its entirety, Listing 19 is complex and difficult to understand. Table 12 isolates 
each function in Listing 19, providing an easy to understand explanation of how the 
example works.
Table 23. Breakdown of Listing 19.
Function Description
MATCH("Bob";A1:A16; 0) Return 4 because Bob is the fourth entry in column 
A.
ADDRESS(4; 4) Return $D$4.
INDIRECT("$D$4") Convert $D$4 into a reference to the cell D4.
OFFSET($D$4; 0; 0; 1; 2) Return the range D4:E4.
SUM(D4:E4) Return the sum of Bob’s quiz scores.
Although Listing 19 works as intended, it breaks easily and unexpectedly. Consider, 
for example, what happens if the range is changed to A2:A16. MATCH returns an 
offset into the provided range, so MATCH("Bob";A2:A16 ; 0) returns 3 rather than 4. 
ADDRESS(3; 4) returns $D$3 rather than $D$4 and Betty’s quiz scores are returned 
instead of Bob’s. Listing 20 uses a slightly different method to obtain Bob’s quiz 
scores.
Listing 20. Better use of OFFSET.
=SUM(OFFSET(A1; MATCH("Bob"; A1:A16; 0)-1; 3; 1; 2))
Table 15 contains a description of each function used in Listing 20. To help convince 
yourself that Listing 20 is better than Listing 19, replace A1 with A2 in both Listing
20 and Table 15 and notice that you still obtain Bob’s quiz scores.
Chapter 13  Calc as a Simple Database 337

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

Comentarios a estos manuales

Sin comentarios

OpenOffice.org OpenOffice - 3.3 Guía básica Pagina 337

Comentarios a estos manuales

Relacionado con productos y manuales para Software OpenOffice.org OpenOffice - 3.3