Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

编辑工具框允许在指定的表格中,通过excel的函数来对列和行进行计算并显示到指定的单元格式中。

函数包含

Financial 

FunctionResult

ACCRINT

Returns the accrued interest for a security that pays periodic interest

返回支付定期利息的证券的应计利息

CUMIPMT

Returns the cumulative interest paid on a loan between start_period and end_period

CUMPRINC

Returns the cumulative principal paid on a loan between start_period and end_period

DDBReturns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify

DOLLARDE

Converts a dollar price expressed as an integer part and a fraction part, such as 1.02, into a dollar price expressed as a decimal number

DOLLARFR

Convert decimal numbers to fractional dollar numbers

EFFECT

Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year

FV

Calculates the future value of an investment based on a constant interest rate

FVSCHEDULE

Returns the future value of an initial principal after applying a series of compound interest rates

IPMT

Returns the interest payment for an investment for a given period

IRR

Returns the internal rate of return for a series of cash flows

ISPMT

Returns the interest paid during a specific period of an investment

MIRR

Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash

NOMINAL

Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year

NPER

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate

NPV

Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values)

PPMT

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate

PV

Calculates the present value of a loan or an investment, based on a constant interest rate

RATE

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions

RRI

Returns an equivalent interest rate for the growth of an investment

SLN

Returns the straight-line depreciation of an asset for one period

TBILLEQ

Returns the bond-equivalent yield for a Treasury bill

TBILLPRICE

Returns the price per $100 face value for a Treasury bill

TBILLYIELD

Returns the yield for a Treasury bill

XIRR

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

XNPV

Returns the net present value for a schedule of cash flows that is not necessarily periodic

Date and time 

FunctionResult

DATE

Returns the serial number of a particular date

DATEVALUE

Converts a date that is stored as text to a serial number

DAY

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31

DAYS

Returns the number of days between two dates

DAYS360

Returns the number of days between two dates based on a 360-day year (twelve 30-day months)

EDATE

Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date)

EOMONTH

Returns the serial number for the last day of the month that is the indicated number of months before or after start_date

HOUR

Converts a serial number to an hour

INTERVAL

Returns a temporal representation of a number

ISOWEEKNUM

Returns the number of the ISO week number of the year for a given date

MINUTE

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59

MONTH

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December)

NETWORKDAYS

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays

NOW

Returns the serial number of the current date and time

PDURATION

Returns the number of periods required by an investment to reach a specified value

PMT

Calculates the payment for a loan based on constant payments and a constant interest rate

SECOND

Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59

SYD

Returns the sum-of-years' digits depreciation of an asset for a specified period

TIME

Returns the decimal number for a particular time

TIMEVALUE

Returns the decimal number of the time represented by a text string

TODAY

Returns the serial number of the current date

WEEKDAY

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default

WEEKNUM

Returns the week number of a specific date

WORKDAY

Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date)

YEAR

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999

YEARFRAC

Calculates the fraction of the year represented by the number of whole days between two dates


Engineering 

FunctionResult

BESSELI

Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments

BESSELJ

Returns the Bessel function

BESSELK

Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments

BESSELY

Returns the Bessel function, which is also called the Weber function or the Neumann function

BIN2DEC

Converts a binary number to decimal

BIN2HEX

Converts a binary number to hexadecimal

BIN2OCT

Converts a binary number to octal

BITAND

Returns a bitwise 'AND' of two numbers

BITLSHIFT

Returns a number shifted left by the specified number of bits

BITOR

Returns a bitwise 'OR' of two numbers

BITRSHIFT

Returns a value number shifted right by shift_amount bits

BITXOR

Returns a bitwise 'XOR' of two numbers

COMPLEX

Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj

CONVERT

Converts a number from one measurement system to another

DEC2BIN

Converts a decimal number to binary

DEC2HEX

Converts a decimal number to hexadecimal

DEC2OCT

Converts a decimal number to octal

DELTA

Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise

ERF

Returns the error function integrated between lower_limit and upper_limit

ERFC

Returns the complementary ERF function integrated between x and infinity

GESTEP

Tests whether a number is greater than a threshold value

HEX2BIN

Converts a hexadecimal number to binary

HEX2DEC

Converts a hexadecimal number to decimal

HEX2OCT

Converts a hexadecimal number to octal

IMABS

Returns the absolute value (modulus) of a complex number

IMAGINARY

Returns the imaginary coefficient of a complex number

IMARGUMENT

Returns the argument theta, an angle expressed in radians

IMCONJUGATE

Returns the complex conjugate of a complex number

IMCOS

Returns the cosine of a complex number

IMCOSH

Returns the hyperbolic cosine of a complex number

IMCOT

Returns the cotangent of a complex number

IMCSC

Returns the cosecant of a complex number

IMCSCH

Returns the hyperbolic cosecant of a complex number

IMDIV

Returns the quotient of two complex numbers

IMEXP

Returns the exponential of a complex number

IMLN

Returns the natural logarithm of a complex number

IMLOG10

Returns the base 10 logarithm of a complex number

IMLOG2

Returns the base 2 logarithm of a complex number

IMPOWER

Returns a complex number raised to an integer power

IMPRODUCT

Returns the product of complex numbers

IMREAL

Returns the real coefficient of a complex number

IMSEC

Returns the secant of a complex number

IMSECH

Returns the hyperbolic secant of a complex number

IMSIN

Returns the sine of a complex number

IMSINH

Returns the hyperbolic sine of a complex number

IMSQRT

Returns the square root of a complex number

IMSUB

Returns the difference between two complex numbers

IMSUM

Returns the sum of complex numbers

IMTAN

Returns the tangent of a complex number

OCT2BIN

Converts an octal number to binary

OCT2DEC

Converts an octal number to decimal

OCT2HEX

Converts an octal number to hexadecimal


Information

FunctionResult

EQ

Tests if two values are equal

ISBLANKReturns TRUE if the value is blank

ISBINARY

Returns TRUE if the value is binary

ISERRReturns TRUE if the value is any error value except #N/A
ISERRORReturns TRUE if the value is any error value
ISEVENReturns TRUE if the number is even
ISLOGICALReturns TRUE if the value is a logical value
ISNAReturns TRUE if the value is the #N/A error value
ISNONTEXTReturns TRUE if the value is not text
ISNUMBERReturns TRUE if the value is a number
ISODDReturns TRUE if the number is odd
ISTEXTReturns TRUE if the value is text

LTE

Returns true if number1 is lower than or equal to number2

NAReturns the error value #N/A

NE

Returns true if number1 does not equal number2


Statistical

FunctionResult
AVEDEVReturns the average of the absolute deviations of data points from their mean
AVERAGEReturns the average of its arguments

BETA.DIST /

BETADIST

Returns the the cumulative beta probability density function

BETA.INV /

BETAINV

Returns the inverse of the beta cumulative probability density function

BINOM.DIST /

BINOMDIST

Returns the individual term binomial distribution probability

BINOM.INV

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value

CHISQ.DIST

Returns the chi-squared distribution

CHISQ.DIST.RT

Returns the right-tailed probability of the chi-squared distribution

CHISQ.INV

Returns the inverse of the left-tailed probability of the chi-squared distribution

CHISQ.INV.RT

Returns the inverse of the right-tailed probability of the chi-squared distribution

CONFIDENCE

Returns the confidence interval for a population mean, using a normal distribution

CONFIDENCE.NORM

Returns the confidence interval for a population mean, using a normal distribution

CONFIDENCE.T

Returns the confidence interval for a population mean, using a Student's t distribution

CORREL

Returns the correlation coefficient of the Array1 and Array2 cell ranges

COUNTCounts how many numbers are in the list of arguments
COUNTACounts how many values are in the list of arguments
COUNTBLANKCounts the number of blank cells within a range

COUNTIF

Counts the number of cells that meet a criterion

COUNTIFS

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met

COUNTIN

Counts the number of cells containing Value

COUNTUNIQUE

Counts the number of unique values in the given ranges

COVARIANCE.P

Returns population covariance, the average of the products of deviations for each data point pair in two data sets

COVARIANCE.S

Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets

DEVSQ

Returns the sum of squares of deviations of data points from their sample mean

EXPON.DIST /

EXPONDIST

Returns the exponential distribution. Use EXPON.DIST to model the time between events, such as how long an automated bank teller takes to deliver cash

F.DIST /

FDIST

Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets

F.DIST.RT/

FDISTRT

Returns the (right-tailed) F probability distribution (degree of diversity) for two data sets

F.INV /

FINV

Returns the inverse of the F probability distribution. If p = F.DIST(x,...), then F.INV(p,...) = x

F.INV.RT /

FINVRT

Returns the inverse of the (right-tailed) F probability distribution. If p = F.DIST.RT(x,...), then F.INV.RT(p,...) = x

FISHER

Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed

FISHERINV

Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x

FORECAST

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression

FREQUENCY

Calculates how often values occur within a range of values, and then returns a vertical array of numbers

GAMMA

Returns the Gamma function value

GAMMA.DIST /

GAMMADIST

Returns the gamma distribution

GAMMA.INV /

GAMMAINV

Returns the inverse of the gamma cumulative distribution

GAMMALN

Returns the natural logarithm of the gamma function, Γ(x)

GAMMALN.PRECISE

Returns the natural logarithm of the gamma function, Γ(x)

GAUSSReturns 0.5 less than the standard normal cumulative distribution

GEOMEAN

Returns the geometric mean

GROWTH

Returns values along an exponential trend

HARMEAN

Returns the harmonic mean

HYPGEOM.DIST /

HYPGEOMDIST

Returns the hypergeometric distribution

INTERCEPT

Returns the intercept of the linear regression line

KURT

Returns the kurtosis of a data set

LARGE

Returns the k-th largest value in a data set

LINEST

Calculates the statistics for a line by using the 'least squares' method to calculate a straight line that best fits your data, and then returns an array that describes the line

LOGEST

Calculates an exponential curve that fits your data and returns an array of values that describes the curve

LOGNORM.DIST /

LOGNORMDIST

Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters Mean and Standard_dev

LOGNORM.INV /

LOGNORMINV

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters Mean and Standard_dev

MAXReturns the maximum value in a list of arguments

MAXA

Returns the largest value in a list of arguments

MEDIAN

Returns the median of the given numbers. The median is the number in the middle of a set of numbers

MIN

Returns the minimum value in a list of arguments

MINA

Returns the smallest value in the list of arguments

MODE.MULT /

MODEMULT

Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data

MODE.SNGL /

MODESNGL

Returns the most frequently occurring, or repetitive, value in an array or range of data

NEGBINOM.DIST /

NEGBINOMDIST

Returns the negative binomial distribution, the probability that there will be Number_f failures before the Number_s-th success, with Probability_s probability of a success

NORM.DIST /

NORMDIST

Returns the normal distribution for the specified mean and standard deviation

NORM.INV /

NORMINV

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation

NORM.S.DIST /

NORMSDIST

Returns the standard normal distribution (has a mean of zero and a standard deviation of one)

NORM.S.INV /

NORMSINV

Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one

PEARSON

Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets

PERCENTILEEXC

Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive

PERCENTILEINC

Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive

PERCENTRANKEXC

Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set

PERCENTRANKINC

Returns the rank of a value in a data set as a percentage (0..1, inclusive) of the data set

PERMUTReturns the number of permutations for a given number of objects that can be selected from number objects
PERMUTATIONAReturns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
PHI

Returns the value of the density function for a standard normal distribution

POISSON.DIST /

POISSONDIST

Returns the Poisson distribution

PROB

Returns the probability that values in a range are between two limits

QUARTILE.EXC /

QUARTILEEXC

Returns the quartile of the data set, based on percentile values from 0..1, exclusive

QUARTILE.INC /

QUARTILEINC

Returns the quartile of a data set, based on percentile values from 0..1, inclusive

RANK.AVG /

RANKAVG

Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned

RANK.EQ /

RANKEQ

Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned

RSQ

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's

SKEW

Returns the skewness of a distribution

SKEW.P /

SKEWP

Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean

SLOPE

Returns the slope of the linear regression line through data points in known_y's and known_x's

SMALL

Returns the k-th smallest value in a data set

STANDARDIZE

Returns a normalized value from a distribution characterized by mean and standard_dev

STDEV.P /

STDEVP

Calculates standard deviation based on the entire population given as arguments

STDEV.S /

STDEVS

Estimates standard deviation based on a sample

STDEVA

Estimates standard deviation based on a sample

STDEVP

Calculates standard deviation based on the entire population given as arguments

STDEVPA

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean)

STEYX

Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x

T.DIST /

TDIST

Returns the Student's left-tailed t-distribution

T.DIST.2T /

TDIST2T

Returns the two-tailed Student's t-distribution

T.DIST.RT /

TDISTRT

Returns the right-tailed Student's t-distribution

T.INV /

TINV

Returns the left-tailed inverse of the Student's t-distribution

T.INV.2T /

TINV2T

Returns the two-tailed inverse of the Student's t-distribution

TREND

Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's

TRIMMEAN

Returns the mean of the interior of a data set

VAR.P /

VARP

Calculates variance based on the entire population (ignores logical values and text in the population)

VAR.S /

VARS

Estimates variance based on a sample (ignores logical values and text in the sample)

VARA

Estimates variance based on a sample

VARPA

Calculates variance based on the entire population

WEIBULL.DIST /

WEIBULLDIST

Returns the Weibull distribution



Maths

FunctionResult
ABSReturns the absolute value of a number
ACOSReturns the arccosine of a number
ACOSHReturns the inverse hyperbolic cosine of a number
ACOTReturns the arccotangent of a number
ACOTHReturns the hyperbolic arccotangent of a number
ADD

Returns the sum of two numbers

AGGREGATE

Returns an aggregate in a list or database

ARABICConverts a Roman number to Arabic, as a number

ARGS2ARRAY

Converts the given arguments to an array

ASINReturns the arcsine of a number
ASINHReturns the inverse hyperbolic sine of a number
ATANReturns the arctangent of a number
ATAN2Returns the arctangent from x- and y-coordinates
ATANHReturns the inverse hyperbolic tangent of a number

AVERAGEA

Returns the numerical average value of its arguments

AVERAGEIF

Returns the average of a range depending on criteria

AVERAGEIFS

Returns the average of a range depending on multiple criteria

BASEConverts a number into a text representation with the given radix (base)
CEILINGRounds a number to the nearest integer or to the nearest multiple of significance
CEILING.MATH / CEILINGMATHRounds a number up, to the nearest integer or to the nearest multiple of significance
CEILING.PRECISE / CEILINGPRECISERounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
COMBINReturns the number of combinations for a given number of objects
COMBINAReturns the number of combinations with repetitions for a given number of items
COSReturns the cosine of a number
COSHReturns the hyperbolic cosine of a number
COTReturns the cotangent of an angle
COTHReturns the hyperbolic cotangent of a number
CSCReturns the cosecant of an angle
CSCHReturns the hyperbolic cosecant of an angle
DECIMALConverts a text representation of a number in a given base into a decimal number
DEGREESConverts radians to degrees

DIVIDE

Returns the result of the division of two numbers

EVENRounds a number up to the nearest even integer
EXPReturns e raised to the power of a given number
FACTReturns the factorial of a number
FACTDOUBLEReturns the double factorial of a number
FLOORRounds a number down, toward zero
FLOOR.MATHRounds a number down, to the nearest integer or to the nearest multiple of significance
FLOOR.PRECISERounds a number down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.
GCDReturns the greatest common divisor
INTRounds a number down to the nearest integer
ISO.CEILINGReturns a number that is rounded up to the nearest integer or to the nearest multiple of significance
LCMReturns the least common multiple
LNReturns the natural logarithm of a number
LOGReturns the logarithm of a number to a specified base
LOG10Returns the base-10 logarithm of a number

MINUS

Return the result of number1 minus number2

MODReturns the remainder from division
MROUNDReturns a number rounded to the desired multiple
MULTINOMIALReturns the multinomial of a set of numbers

MULTIPLY

Returns the product of two numbers

ODDRounds a number up to the nearest odd integer
PIReturns the value of pi
POWERReturns the result of a number raised to a power
PRODUCTMultiplies its arguments
QUOTIENTReturns the integer portion of a division
RADIANSConverts degrees to radians
RANDReturns a random number between 0 and 1
RANDBETWEENReturns a random number between the numbers you specify
ROUNDRounds a number to a specified number of digits
ROUNDDOWNRounds a number down, toward zero
ROUNDUPRounds a number up, away from zero
SECReturns the secant of an angle
SECHReturns the hyperbolic secant of an angle

SERIESSUM

Returns the sum of a power series

SIGNReturns the sign of a number
SINReturns the sine of the given angle
SINHReturns the hyperbolic sine of a number
SQRTReturns a positive square root
SQRTPIReturns the square root of (number * pi)
SUMAdds its arguments

SUBTOTAL

Returns a subtotal in a list or database

SUMIF

Sums the values in a range that meet criteria that you specify

SUMIFS

Sums the values in multiples ranges that meet multiple criterisa that you specify

SUMPRODUCT

Multiplies corresponding components in the given arrays, and returns the sum of those products

SUMSQ

Returns the sum of the squares of the arguments

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays

SUMXMY2

Returns the sum of squares of differences of corresponding values in two arrays

TANReturns the tangent of a number
TANHReturns the hyperbolic tangent of a number
TRUNCTruncates a number to an integer

Logical

FunctionResult
ANDReturns TRUE if all of its arguments are TRUE
FALSEReturns the logical value FALSE
IFSpecifies a logical test to perform
IFERRORReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFNAReturns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
NOTReverses the logic of its argument
ORReturns TRUE if any argument is TRUE

SWITCH

Evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value

TRUEReturns the logical value TRUE
XORReturns a logical exclusive OR of all arguments

Lookup and reference 

FunctionResult

CHOOSE

Uses index_num to return a value from the list of value arguments

COLUMN

Returns the column number of the given cell reference

COLUMNS

Returns the number of columns in an array or reference

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

INDEX

Uses an index to choose a value from a reference or array

LOOKUP

Looks up values in a vector or array

MATCH

Searches for a specified item in a range of cells, and then returns the relative position of that item in the range

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference or array

TRANSPOSE

Returns a vertical range of cells as a horizontal range, or vice versa

UNIQUE

Returns a list of unique values in a list or range

VLOOKUP

Finds things in a table or a range by row

Text

FunctionResult
CHARReturns the character specified by the code number
CLEANRemoves all nonprintable characters from text
CODEReturns a numeric code for the first character i a text string
CONCATENATEJoins several text items into one text item

DOLLAR

Converts a number to text using dollars currency format, with the decimals rounded to the number of places you specify

EXACTChecks to see if two text values are identical
FINDFinds one text value within another (case-sensitive)
FIXEDFormats a number as text with a fixed number of decimals

HTML2TEXT

Converts HTML to text

JOIN

Joins texts from a range, using a separator (comma by default)

LEFTReturns the leftmost characters from a text value
LENReturns the number of characters in a text string
LOWERConverts text to lowercase
MIDReturns a specific number of characters from a text string starting at the position you specify

NUMBERS

Returns values that are numbers

NUMERAL

Returns a formatted number value

PROPERCapitalizes the first letter in each word of a text value

REGEXEXTRACT

Extracts matching substrings according to a regular expression

REGEXMATCH

Returns true if a piece of text matches a regular expression

REGEXREPLACE

Replaces part of a text string with a different text string using regular expressions

REPLACEReplaces characters within text
REPTRepeats text a given number of times
RIGHTReturns the rightmost characters from a text value

ROMAN

Converts an arabic numeral to roman, as text

SEARCH

Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string

SPLIT

Divides text around a specified character

SUBSTITUTESubstitutes new text for old text in a text string
TConverts its arguments to text
TRIMRemoves spaces from text
UNICHARReturns the Unicode character that is references by the given numeric value
UNICODEReturns the number (code point) that corresponds to the first character of the text
UPPERConverts text to uppercase
VALUEConverts a text argument to a number