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

The search value and search range are the same as for LOOKUP. The final argument,

search type, controls how the search is performed. A search type of 1, sorted in

ascending order, is the default. A search type of -1 indicates that the list is sorted in

descending order. A search type of 0 indicates that the list is not sorted. Regular

expressions can only be used on an unsorted list.

Use MATCH when:

• You need an index into the range rather than the value.

• The search data is in descending order and the data is large enough that the

data must be searched assuming that it is sorted; because it is faster to sort a

sorted list.

Examples

Consider the data in Table 23. Each student’s information is stored in a single row.

Write a formula to return the average grade for Fred. The problem can be restated as

Search column A in the range A1:G16 for Fred and return the value in column F

(column F is the sixth column). The obvious solution is

=VLOOKUP("Fred"; A2:G16; 6). Equally obvious is

=LOOKUP("Fred"; A2:A16; F2:F16).

It is common for the first row in a range to contain column headers. All of the search

functions check the first row to see if there is a match and then ignore it if it does not

contain a match, in case the first row is a header.

What if the column heading Average is known, but not the column containing the

average? Find the column containing Average rather than hard coding the value 6. A

slight modification using MATCH to find the column yields

=VLOOKUP("Fred"; A2:G16; MATCH("Average"; A1:G1; 0)); notice that the

heading is not sorted. As an exercise, use HLOOKUP to find Average and then

MATCH to find the row containing Fred.

As a final example, write a formula to assign grades based on a student’s average

score. Assume that a score less than 51 is an F, less than 61 is an E, less than 71 is a

D, less than 81 is a C, less than 91 is a B, and 91 to 100 is an A. Assume that the

values in Table 20 are in Sheet2.

Table 20. Associate scores to a grade.

A B

Score Grade

0 F

51 E

61 D

71 C

81 B

91 A

The formula =VLOOKUP(83; $Sheet2.$A$2:$B$7; 2) is an obvious solution. Dollar

signs are used so that the formula can be copied and pasted to a different location

and it will still reference the same values in Table 20.

Chapter 13 Calc as a Simple Database 335

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

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

Žádné komentáře

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

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

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