基于ExpressSpreadSheet
何况我做这个的目的是以定义条件方式对数据库中的数据进行统计，如果纯数字表格，直接用EXCEL好了。
以下这么多函数不知道够不够，我想对我这个程序而言足够了。
Mnemonics Operand Type Example Explanation
+ Two arguments = 5 + B1 Adds the first argument抯 value to the value of the second argument.
- Two arguments = A5 - B1 Subtracts the second argument抯 value from the value of the first one.
* Two arguments = A2 * A3 Multiplies the first argument抯 value by the value of the second argument.
/ Two arguments = A1 / PI() Divides the first argument抯 value by the value of the second argument.
^ Two arguments = B1 ^ A2 Raises the first argument抯 value to the power determined by the second argument抯 value.
Logical operators
Logical operators perform comparison operations and return a logical value (TRUE or FALSE) as a result.
Mnemonics Operand Type Example Explanation
= Two arguments of logical types = A1=A2 The equality operation. Returns TRUE if the first argument is equal to the second one. Otherwise, returns FALSE.
<> Two arguments of logical types = A1<>B2 The inequality operation. Returns TRUE if values within the list are not equal. Otherwise, returns FALSE.
< Two arguments of logical types = A1<B1 The "Less than"
operation. Returns TRUE if the first argument is less than the second one. Otherwise, returns FALSE.
> Two arguments of logical types = A1>100 The "Greater than"
operation. Returns TRUE if the first argument is greater than the second one. Otherwise, returns FALSE.
<= Two arguments of logical types = A2<=C4 The "Less than or equal to"
operation. Returns TRUE if the first argument is less than or equal to the second one. Otherwise, returns FALSE.
>= Two arguments of logical types = A2>=1.57 The "Greater than or equal to"
operation. Returns TRUE if the first argument is greater than or equal to the second one. Otherwise, returns FALSE.
Arithmetic functions
A set of mathematic and trigonometric functions.
Mnemonics Operand Type Example Explanation
ABS One argument ofdo
uble type =ABS(B6) Returns the absolute value.
ACOS One argument ofdo
uble type =ACOS(B16) Returns the arccosine.
ACOSH One argument ofdo
uble type =ACOSH(A1) Returns the inverse hyperbolic cosine.
ASIN One argument ofdo
uble type =ASIN(B6) Returns the arcsine.
ASINH One argument ofdo
uble type =ASINH(C2) Returns the inverse hyperbolic sine of the argument.
ATAN One argument ofdo
uble type =ATAN(C5) Returns the arctangent.
ATAN2 Two arguments ofdo
uble type =ATAN2(A1, A2) Returns the arctangent using x- and y- coordinates.
ATANH One argument ofdo
uble type =ATANH(D4) Returns the inverse hyperbolic tangent.
CEILING Two argument ofdo
uble type =CEILING(2.5, 1) Rounds the first argument up to the nearest multiple based on the significance specified by the second argument.
COS One argument ofdo
uble type =COS(A1) Returns the cosine.
COSH One argument ofdo
uble type =COSH(D7) Returns the hyperbolic cosine.
COUNTIF Two arguments: the range of cells and the condition of type string. =COUNTIF(A1:A7,">2"
Counts the number of nonblank cells within a range which meet a given condition.
DEGREES One argument ofdo
uble type that specifies angle in radians =DEGREES(PI()/2) Converts radians to degrees.
EVEN One argument ofdo
uble type =EVEN(-1) Rounds the argument up to the nearest even integer.
EXP One argument ofdo
uble type =EXP(C1) Returns the exponent value of the argument.
FACT One nonnegative argument =FACT(2) Returns the factorial of the argument.
FLOOR Two arguments ofdo
uble type =FLOOR(-2.5, -2) Rounds the first argumentdo
wn, towards zero, to the nearest multiple of the significance specified by the second argument.
INT Argument of typedo
uble =INT(E4) Rounds the argumentdo
wn to the nearest integer.
LN One argument ofdo
uble type =LN(C1) Returns the natural logarithm.
LOG Two arguments: the number and the base =LOG(100, 10) Returns the logarithm of a number to the specified base.
LOG10 One argument ofdo
uble type =LOG10(1000) Returns the base-10 logarithm of the argument.
MOD Two arguments: a number and divisor =MOD(4,3) Returns the remainder after the number is divided by the divisor.
ODD One argument ofdo
uble type =ODD(3.5) Rounds the argument up to the nearest odd integer.
PI No arguments required =PI() Returns the value of Pi.
POWER Two arguments ofdo
uble type: the number and the power =POWER(A1,4) Raises the number to the base.
RADIANS One argument of typedo
uble that specifies angle in degrees. =RADIANS(180) Converts degrees to radians.
RAND No arguments required =RAND() Returns a random number between 0 and 1.
ROUND Two arguments: the number ofdo
uble type and the number of digits =ROUND(20.57, 1) Rounds the first argument to the specified number of digits.
ROUNDDOWN Two arguments ofdo
uble type =ROUNDDOWN(2.75,0) Rounds the argument toward zero. The first argument specifies a number to round up. The second argument defines the number of digits to which you want to round the first argument.
ROUNDUP Two arguments ofdo
uble type =ROUNDUP(-3.42,1) Rounds the argument toward infinity. The first argument specifies a number to round up. The second argument defines the number of digits to which you want to round the first argument.
SIGN One argument ofdo
uble type =SIGN(A2) Returns the sign.
SIN One argument ofdo
uble type =SIN(A1) Returns the sine.
SINH One argument ofdo
uble type =SINH(1) Returns the hyperbolic sine.
SQRT One argument ofdo
uble type =SQRT(B5) Returns the square root.
SUM A list of arguments =SUM(A1:C12)=SUM(A1, 3.14, 1.57) Sums all the values in the list.
SUMSQ A list of arguments =SUMSQ(B1
1)=SUM(1, 2, 3, 4) Sums the square of values in the list
TAN One argument ofdo
uble type =TAN(C3) Returns the tangent.
TANH One argument ofdo
uble type =TANH(0) Returns the hyperbolic tangent.
TRUNC One argument ofdo
uble type =TRUNC(PI()) Returns the integer part. Compare this with the Int function, which returns ado
uble
Statistical functions
Basic statistical functions.
Mnemonics Operand Type Example Explanation
AVERAGE A list of arguments =AVERAGE(A1:A5)=AVERAGE(10.3, 9.1) Calculates the average value of values within the list.
AVERAGEA A list of arguments =AVERAGEA(A1:A5) Calculates the average value of the non-empty cells referenced.AVERAGEA(range) equals SUM(range)/COUNTA(range).
COUNT A list of arguments =COUNT(A1:A4)=COUNT(1, 5, 8) Returns the number of cells in a given range.
COUNTA A list of arguments =COUNTA(A1:A4) Counts the number of non-empty cells in a given range.
COUNTBLANK A list of arguments =COUNTBLANK(A1:E1) Counts the number of empty cells.
MAX A list of arguments =MAX(A1
1)=MAX(A1, 100, C2) Returns the largest argument value.
MIN A list of arguments =MIN(A1
1)=MIN(0, C2) Returns the smallest argument value.
SUM A list of arguments =SUM(A1:C12)=SUM(A1, 3.14, 1.57) Sums values within the list.
SUMSQ A list of arguments =SUMSQ(B1
1)=SUMSQ(1, 2, 3, 4) Sums squares of values within the list.
Logical functions
Logical functions take logical values as arguments and return a logical value as a result.
Mnemonics Operand Type Example Explanation
AND A list of logical arguments =AND(1<B4, B4<100) Logical AND operation.Returns TRUE if all values within the list are TRUE;
returns FALSE if one or more values within the list evaluates to FALSE.
FALSE No arguments required =FALSE() Returns the logical value FALSE.
IF Three arguments: the logical expression, the value to return if the expression succeeds, the value to return the expression fails. =IF(A10<=100, "Within budget", "Over budget"
Returns the second argument if the logical expression evaluates to TRUE and the third argument otherwise.
NOT One argument of logical type =NOT(1>D4) Logical NOT operation.Reverses the value of its argument.
OR A list of logical arguments =OR(A1>=10, A1<=?0) Logical OR operation.Returns TRUE if any argument is TRUE;
returns FALSE if all values within the list evaluate to FALSE.
TRUE No arguments required. =TRUE() Returns the logical value TRUE.
Date and Time functions
Mnemonics Operand Type Example Explanation
DATE Three operands defining the year, month and day. =DATE(1900,1,1) Calculates the serial number that represents a specified date.
DAY One argument defining the serial number of the required date. =DAY(TODAY()) Returns the day portion of a given date.
HOUR One argument defining the serial number of the required date/time value. =HOUR(NOW()) Returns the hour portion of a given date/time value.
MONTH One argument defining the serial number of the required date. =MONTH(TODAY()) Returns the month portion of a given date.
MINUTE One argument defining the serial number of the required date/time value. =MINUTE(NOW()) Returns the minutes portion of a given date/time value.
NOW No arguments required. =NOW() Returns the current time in general format. You can apply further formatting to the result of the function.
SECOND One argument defining the serial number of the required date/time value. =SECOND(NOW()) Returns the seconds portion of a given date/time value.
TIME Three arguments defining hour, minute and second parts of a time value. =TIME(16, 48, 10) Returns a decimal number for a specified time.
TODAY No arguments required. =TODAY() Returns the serial number of the current date.
WEEKDAY Two arguments: the serial number of the required date, weekday base. =WEEKDAY(TODAY())=WEEKDAY(DATE( 2002, 12, 1),1) returns 1(Sunday) Returns the day of the week corresponding to the specified date. The weekday base identifies the first day of the week and determines the return value type:1 or omitted: the first day of the week is Sunday. The function returns 1 for Sunday, 2 for Monday, etc.2: the first day of the week is Monday. The function returns 1 for Monday, 2 for Tuesday, etc.3: the first day of the week is Monday. The function returns 0 for Monday, 1 for Tuesday, etc.
YEAR One argument defining the serial number of the required date. =YEAR(TODAY()) Returns the year portion of a given date.
Text functions
Mnemonics Operand Type Example Explanation
& (ampersand) Two arguments of type string = "Yellow "&"river" Concatenates specified strings.
CONCATENATE A list of strings =CONCATENATE("Developer", "
Express"
Joins several text strings in one text string. An alternative to "&".
DOLLAR Two arguments defining the value and the number of digits to the right of the decimal point in the output string. =DOLLAR(957.344, 2) Converts the number to text using currency format $#,##0.00_);($#,##0.00), with the decimals rounded to the specified number of places.
FIXED The first argument of typedo
uble is required. The second argument of type integer is optional (the default value is 2). The third argument of type Boolean is optional. =FIXED(1234.567, 1) Rounds the first argument to the number of decimals determined by the second argument and returns it as a string. The third parameter specifies whether to omit commas in the output string.
LEFT The first argument of type string is required. The second argument of type integer is optional. =LEFT(A1)=LEFT(A1, 3) Returns the first character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1.
LEN One argument of type string. =LEN("ABC"
Returns the length of a given string.
LOWER One argument of type string. =LOWER(A1) Converts a string to lowercase.
MID The first argument is of type string, the second and the third arguments are of type integer. =MID("ABC",3,1) Returns the substring of a given text string. The position of the substring is defined by the second parameter. The third parameter specifies the number of characters to extract.
RIGHT The first argument of type string is required. The second argument of type integer is optional. =RIGHT(B2, 3) Returns the last character or characters in a text string. The second parameter defines the number of characters to extract. The default value is 1.
TRIM One argument of string type =TRIM("
Express Spread Sheet "
Removes all spaces from text except for single spaces between words.
UPPER One argument of type string. =UPPER(A1&A2) Converts a string to uppercase.
IS functions
Functions which test the type of a value and return a Boolean result.
Mnemonics Operand Type Example Explanation
ISBLANK A value of any type =ISBLANK(A1) Returns TRUE if cell is empty.
ISERR A value of any type =ISERR(H1) Returns TRUE if the cell contains any error value except #N/A.
ISERROR A value of any type =ISERROR(A7) Returns TRUE if the cell contains any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL A value of any type =ISLOGICAL(C3) Returns TRUE if the specified value refers to a logical value.
ISNA A value of any type =ISNA(E5) Returns TRUE if the cell contains #N/A (value not available) error value.
ISNONTEXT A value of any type =ISNONTEXT(D4) Returns TRUE if the celldo
es not contain text. Returns TRUE for blank cells.
ISNUMBER A value of any type =ISNUMBER(A2) Returns TRUE if the cell contains a number.
ISTEXT A value of any type =ISTEXT(D1) Returns TRUE if the specified cell contains text.
