UbuntuHelp:SpreadsheetFunctions

文章出处:

https://help.ubuntu.com/community/SpreadsheetFunctions

点击翻译:

English

请不要直接编辑翻译本页，本页将定期与来源同步。

This will hold an extensive list of spreadsheet functions in the (near) future! Note to self: func315,func329,func424,func446

Supported Spreadsheet applications

For now, the functions are tested in the english version of the following applications.

Application

OpenOffice Spreadsheet

KOffice KSpread

Functions

MIN - Finding the smallest value

To find the smallest value in an array, you should use the function "MIN" Use:

=MIN(array)

Example:

=MIN(A1:A10)

This evaluates A1 through A10, and output the smallest value.

MAX - Finding the largest value

To find the largest value in an array, you should use the function "MAX" Use:

=MAX(array)

Example:

=MAX(A1:A10)

This evaluates A1 through A10, and output the largest value.

AVERAGE - Calculate the average of a list of values

To find the average value, use the function "AVERAGE" Use:

=AVERAGE(array)

Example:

=AVERAGE(A1:A10)

This will output the average value of A1 through A10. (blanks will not the used in the calculations)

MEDIAN - Finding the median of a given array

Use:

=MEDIAN(array)

Example:

=MEDIAN(A1:A10)

This will output the value in the "middle" in we ordered the values.

MODE - Finding the most frequent value

This function finds the MODE value. Use:

=MODE(array)

CHOOSE

This function choose the output based on an integer that refers to one of the following values. Use:

=CHOOSE(n,v1,v2,v3,..........,vn)

Example:

=CHOOSE(2,12,34,56)

The above will output 34 since that is the 2nd value (n=2)

ROUND - round a value to fit another purpose

This function will round a value to the defined simplicity. Use:

=ROUND(number,num_digits)

"number" is the value you would like to round. "num_digits" is the number of digits you would like (how precise - the larger the number, the MORE precise.) A negative value of "num_digits" will also round the respective digits before the comma. Examples:

=ROUND(2.56;1)

Will output 2.6

=ROUND(2123.56;-2)

Will output 2100

ROUNDUP

The same as ROUND, but it rounds up!

ROUNDDOWN

The same as ROUND, but it rounds down!

EVEN - Round a number up to the next EVEN integer

Use:

=EVEN(number)

Example:

=EVEN(2.13)

This will output 4

ODD - Round a number up to the next ODD integer

Use:

=ODD(number)

Example:

=ODD(2.13)

This will output 3

INT - Round a number down to the nearest integet

Use:

=INT(number)

Example: (we use a negative value, so that the difference between INT and TRUNC is obvious)

=INT(-4.6)

This will output -5

TRUNC - Truncates a number to an integer, removing the fractional part of the number

Use:

=TRUNC(number)

Example: (we use a negative value, so that the difference between INT and TRUNC is obvious)

=TRUNC(-4.6)

This will output -4

SMALL - Finding the n'th smallest value

To find the n'th smallest value in an array, you should use the function "SMALL" Use:

=SMALL(array;n)

Example:

=SMALL(A1:A10;3)

This evaluates A1 through A10, and output the 3rd smallest value. (because of the argument "3")

LARGE - Finding the n'th largest value

To find the n'th largest value in an array, you should use the function "LARGE" Use:

=LARGE(array;n)

Example:

=LARGE(A1:A10;4)

This evaluates A1 through A10, and output the 4th largest value. (because of the argument "4")

COUNT - Count the number of values in a list

This function enables you to count the number of values in an array. Use:

=COUNT(array)

Example:

=COUNT(A1:A10)

This will output how many of the cells had a value in them (blanks excluded)

COUNTIF - Count how many times a criteria is met

To count the number of times a given array meets a given criteria, use the function "COUNTIF" Use:

=COUNTIF(range,criteria)

Example with text:

=COUNTIF(A1:A10;"foo")

This will output the number of times a celltext was "foo". Example with a number:

=COUNTIF(A1:A10;5)

This will output the number of times a cell value was 5. Example with boolean:

=COUNTIF(A1:A10;TRUE)

This will output the number of times a cell status was TRUE. Example with ">":

=COUNTIF(A1:A10;">1")

This will output the number of times a cell value was larger than 1. See the operator-guide in the bottom of the page!

SUMIF - Sum the values in one array, if the values in another array meet the criteria

This function is somehow similar to the COUNTIF function. Use:

=SUMIF(range,criteria,sum_range)

Example:

=SUMIF(A1:A10;56;B1:B10)

Here the values in B1 through B10 is summed if the value in the corresponding A cell equals 56. If the sum_range is not defined, the range will be used as sum_range. See the operator-guide in the bottom of the page!

RANK - Ranking cell values

To rank the values of an array, use the function "RANK" Use:

=RANK(number,reference,order)

"number" is the value you want to rank. "reference" is the array you want to compare "number" with. "order" is whether the ranking should be ascending or descending. (0=descending,1=ascending) Example:

=RANK(A1;A1:A10;1)

This will show the rank of A1 compared to the values in A1 through A10 in ascending order. Equal values will share the same ranking, and the following ranking-number will be skipped.

STDEV - Finding the standard deviation, typically used in statistics

To find the standard deviation, use the function "STDEV" Use:

=STDEV(array)

Example:

=STDEV(A1;A1:A10;1)

This will output the standard deviation of A1 through A10.

RANDBETWEEN - Finding a random number between 2 values

This function is used when you want to output a controlled random number to a cell. Use:

=RANDBETWEEN(bottom,top)

Example:

=RANDBETWEEN(1;10)

The above example will output 1,2,3,4,5,6,7,8,9 or 10.

RAND - Finding a random value between 0 and 1

This function is used to create a random number between 0 and 1 (with 15 significant decimals) Use:

=RAND()

If you want the function to output values between 0 and 1000000, simply multiply it with 1000000.

VLOOKUP - Lookup in a vertical-aligned data area (data ordered in rows - most common)

This function is used to look up data elsewhere, based on a given parameter. Use:

=VLOOKUP(value,array,data_column,range_lookup)

"value" is the value you would like to match. "array" is the range that contains the data you want to look up. The first column lists the key data. (matching values) "data_column" is the number of the column that contains the data you want to find. "range_lookup" is whether you want a greater value (1) or exact value (0). Example:

=VLOOKUP(10;A1:B10;2;FALSE)

The above function will find the row that equals 10 in column A, and output the respective data in column B (column 2 of the selection).

HLOOKUP - Lookup in a horizontal-aligned data area (data ordered in columns)

Same as VLOOKUP() but used in horizontal-aligned data areas.

LOOKUP

This lookup method can compare lookup and data in both vertical and horizontal alignment. Use:

=LOOKUP(lookup,lookup_list,data_list)

The size of the list may one be 1 row or 1 column. The 2 lists must be of equal size, so that the data for the lookup is present. In the following example, A1:A3 holds the lookup 1,2,3 respectively, B1:D1 holds the data A,B,C respectively. Example:

=LOOKUP(1,A1:A3,B1:D1)

This will find the lookup with the value 1, and output the corresponding data, which is A in this case.

INDEX

Use:

=INDEX(range,row_number;col_number,area)

"range" is the data you would like to search within. "row_number" is the number of the row, from which you would like to fetch you result. "col_number" is the number of the column, from which you would like to fetch you result. "area" defines which area to be used. (you can use an infinite number of areas.) Example:

=INDEX(A10:C12,2,3)

The above example output the value from the ranges row 2 and column 3. (Which is C11) Example:

=INDEX((area1,area2,area3),row,col,area)

Or more precise:

=INDEX((A10:C12,A15:C17,A20:C22),1,2,3)

This example will output the value from the ranges row 1 and column 2, and the area 3 will be selected. (area 3 is A20:C22)

Text manipulation

CONCATENATE

This function is used to combine several cells with text (mainly) into one. Use:

=CONCATENATE(text1,text2,text3,.......,textn)

TRIM

This function removes space in a given text string. (except spaces BETWEEN words) Use:

=TRIM(text)

FIND

This function finds the position of a given character in a given string. Use:

=FIND("character",text,start_character)

Example:

=FIND("-","12345-67890")

Will output 6, since "-" is the 6th character. Example:

=FIND("-","12345-67890-11123",7)

Will output 12, since we start counting from 7, and "-" is the 12th character there after.

Naming cells or ranges

Naming cells or ranges can be quite smart. Instead of remembering that the cell that holds the TAX percentage is C6, you can name that cell. In OpenOffice it is quite easy to name a cell or range. Select the cell, and click in the menubar: "Insert -> Names -> Define" Write a name, and click OK. The name can also be edited left of the function button. After naming a cell or a range, you can now refer to them by their name instead of their position.

Operator guide - different evaluations

Sometimes you would like to use other than absolute values. This list of larger-than, smaller-than, etc. should help you with the basics in using spreadsheet functions.

Operator	Description
>	Greater than
<	Smaller than
>=	Greater than or equal to
<=	Smaller than or equal to
=	Equal to
	Not equal to
+	Addition
-	Subtraction
*	Multiplication
/	Division
^	Exponent

Understanding errors

Here is a list of the most common errors:

Error	Description
#####	The width of the cell is insufficient to show the data - or it indicated a negative date/time
#NAME?	Unrecognized text in a formula
#NUM!	Invalid numeric values in a formula or function
#N/A	No answer
#DIV/0!	Division by zero
#REF!	Invalid reference
#VALUE!	Wrong argument type or operand

Do you understand the above functions

If you didn't understand the above functions, I didn't do my job good enough! Please mail me with suggestions: [email protected] Alternately, since this is a wiki, you can edit the page yourself!