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

Tip

Do not forget that the SUBTOTAL function ignores cells that use the

SUBTOTAL function. Say you have a spreadsheet that tracks investments.

The retirement investments are grouped together with a subtotal. The

same is true of regular investments. You can use a single subtotal that

includes the entire range without worrying about the subtotal cells.

Using formulas to find data

Calc offers numerous methods to find data in a sheet. For example, Edit > Find &

Replace moves the display cursor based on simple and advanced searching. Use

Data > Filter to limit what is displayed rather than simply moving the cursor. Calc

also offers lookup functions used in formulas, for example a formula to look up a

student’s grade based on their test score.

Search a block of data using VLOOKUP

Use VLOOKUP to search the first column (columns are vertical) of a block of data and

return the value from another column in the same row. For example, search the first

column for the name “Fred” and then return the value in the cell two columns to the

right. VLOOKUP supports two forms:

VLOOKUP(search_value; search_range; return_column_index)

VLOOKUP(search_value; search_range; return_column_index;

sort_order)

The first argument, search_value, identifies the value to find. The search value can

be text, a number, or a regular expression. For example, Fred searches for the text

Fred, 4 searches for the number 4, and F.* is the regular expression for finding

something that starts with the letter F.

The second argument, search_range, identifies the cells to search; only the first

column is searched. For example, B3:G10 searches the same sheet containing the

VLOOKUP formula and Sheet2.B3:G10 searches the range B3:G10 on the sheet

named Sheet2.

The return_column_index identifies the column to return; a value of 1 returns the

first column in the range. The statement =VLOOKUP("Bob"; A1:G9; 1) finds the first

row in A1:G9 containing the text Bob, and returns the value in the first column. The

first column is the searched column, so the text Bob is returned. If the column index

is 2, then the value in the cell to the right of Bob is returned: column B.

The final column, sort_order, is optional. The default value for sort_order is 1,

which specifies that the first column is sorted in ascending order; a value of 0

specifies that the data is not sorted. A non-sorted list is searched by sequentially

checking every cell in the first column for an exact match. If an exact match is not

found, the text #N/A is returned.

A more efficient search routine is used if the data is sorted in ascending order. If one

exact match exists, the returned value is the same as for a non-sorted list; but it is

faster. If a match does not exist, the largest value in the column that is less than or

equal to the search value is returned. For example, searching for 7 in (3, 5, 10)

returns 5 because 7 is between 5 and 10. Searching for 27 returns 10, and searching

for 2 returns #N/A because there is no match and no value less than 2.

Chapter 13 Calc as a Simple Database 333

1 2 ... 328 329 330 331 332 333 334 335 336 337 338 ... 433 434

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

Žádné komentáře

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

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

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