Advanced Excel Functions 简明教程

Advanced Excel Functions - Quick Guide

Advanced Excel - Compatibility Functions

在 Excel 2010 或更高版本中，此类别中列出的函数已被提供改进的准确性和具有更能反映其使用情况的名称的新函数所取代。可在统计函数以及数学和三角函数中找到新函数。如果不需要向后兼容性，您应开始使用新函数。

In Excel 2010 or later, the functions listed in this category were replaced with new functions that provide improved accuracy and have names that reflect their usage better. The new functions can be found in Statistical functions and Math and trigonometry functions. If backward compatibility is not required, you should start using the new functions.

您仍然可以使用早期版本的函数来兼容早期版本的 Excel。如果您正在使用 Excel 2007，您将在“公式”选项卡上的“统计”或“数学和三角”类别中找到这些函数。

You can still use these earlier versions of functions for compatibility with earlier versions of Excel. If you are using Excel 2007, you will find these functions in the Statistical or Math & Trig categories on the Formulas tab.

Compatibility Functions

下表列出了所有兼容性函数 −

The following table lists all the Compatibility functions −

S.No.	Function and Description
1	BETADISTReturns the cumulative beta probability density function
2	BETAINVReturns the inverse of the cumulative beta probability density function
3	BINOMDISTReturns the individual term binomial distribution probability
4	CEILINGRounds a number to the nearest integer or to the nearest multiple of significance
5	CHIDISTReturns the one-tailed probability of the chi-squared distribution
6	CHIINVReturns the inverse of the one-tailed probability of the chi-squared distribution
7	CHITESTReturns the test for independence
8	CONFIDENCEReturns the confidence interval for a population mean
9	COVARReturns covariance, the average of the products of paired deviations
10	CRITBINOMReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
11	EXPONDISTReturns the exponential distribution
12	FDISTReturns the F probability distribution
13	FINVReturns the inverse of the F probability distribution
14	FLOORRounds a number down, toward 0
15	FTESTReturns the result of an F-Test
16	GAMMADISTReturns the gamma distribution
17	GAMMAINVReturns the inverse of the gamma cumulative distribution
18	HYPGEOMDISTReturns the hypergeometric distribution
19	LOGINVReturns the inverse of the lognormal distribution
20	LOGNORMDISTReturns the cumulative lognormal distribution
21	MODEReturns the most common value in a data set
22	NEGBINOMDISTReturns the negative binomial distribution
23	NORMDISTReturns the normal cumulative distribution
24	NORMINVReturns the inverse of the normal cumulative distribution
25	NORMSDISTReturns the standard normal cumulative distribution
26	NORMSINVReturns the inverse of the standard normal cumulative distribution
27	PERCENTILEReturns the kth percentile of values in a range
28	PERCENTRANKReturns the percentage rank of a value in a data set
29	POISSONReturns the Poisson distribution
30	QUARTILEReturns the quartile of a data set
31	RANKReturns the rank of a number in a list of numbers
32	STDEVEstimates standard deviation based on a sample, ignoring text and logical values
33	STDEVPCalculates standard deviation based on the entire population, ignoring text and logical values
34	TDISTReturns the student’s t-distribution
35	TINVReturns the inverse of the student’s t-distribution
36	TTESTReturns the probability associated with a student’s t-Test
37	VAREstimates variance based on a sample, ignoring logical values and text
38	VARPCalculates variance based on the entire population, ignoring logical values and text
39	WEIBULLReturns the Weibull distribution
40	ZTESTReturns the two-tailed P-value of a z-test

Advanced Excel - Cube Functions

Excel Cube 函数能够将 OLAP 多维数据集中的数据导入到 Excel 中以执行计算。这些函数通过连接到 Microsoft SQL Server 2005 Analysis Services 或更高版本的数据源来支持。

The Excel Cube functions enable data from OLAP cubes to be brought into Excel to perform calculations. These functions are supported with a connection to Microsoft SQL Server 2005 Analysis Services or later data source.

由于 PowerPivot 创建了与 OLAP 多维数据集兼容的数据源，因此它也可与这些函数一起使用。

As PowerPivot creates a data source, which is compatible with OLAP cubes, it can also be used with these functions.

Cube Functions

下表列出了所有 Cube 函数−

The following table lists all the Cube functions −

S.No.	Function and Description
1	CUBEKPIMEMBERReturns a key performance indicator name, property, and measure, and displays the name and property in the cell.
2	CUBEMEMBERReturns a member or tuple in a cube hierarchy.
3	CUBEMEMBERPROPERTYReturns the value of a member property in the cube.
4	CUBERANKEDMEMBERReturns the nth, or ranked, member in a set.
5	CUBESETDefines a calculated set of members or tuples by sending a set expression to the cube on the server.
6	CUBESETCOUNTReturns the number of items in a set.
7	CUBEVALUEReturns an aggregated value from a cube.

Advanced Excel - Database Functions

Excel Database 函数使用 Excel Database。这通常采用大型数据表的形式，其中表中的每一行都存储一个单独的记录。工作表表中的每一列都存储每条记录的不同字段。

The Excel Database functions work with an Excel Database. This typically takes the form of a large table of Data, where each row in the table stores an individual record. Each column in the Worksheet table stores a different field for each record.

Database 函数执行基本操作，例如求和、平均数、计数等，此外还使用条件参数，让你仅针对 Database 中记录的指定子集执行计算。Database 中的其他记录将被忽略。

The Database functions perform basic operations, such as Sum, Average, Count, etc., and additionally use criteria arguments, that allow you to perform the calculation only for a specified subset of the records in your Database. Other records in the Database are ignored.

Database Functions

下表列出了所有 Database 函数−

The following table lists all the Database functions −

S.No.	Function and Description
1	DAVERAGEAverages the values in a column of a list or database that match conditions you specify.
2	DCOUNTCounts the cells that contain numbers in a column of a list or database that match conditions you specify.
3	DCOUNTACounts the nonblank cells in a column of a list or database that match conditions you specify.
4	DGETReturns a single value from a column of a list or database that matches conditions you specify.
5	DMAXReturns the largest number in a column of a list or database that matches conditions you specify.
6	DMINReturns the smallest number in a column of a list or database that matches conditions you specify.
7	DPRODUCTMultiplies the values in a column of a list or database that match conditions you specify.
8	DSTDEVEstimates the standard deviation of a population based on a sample by using the numbers in a column of a list or database that match conditions you specify.
9	DSTDEVPCalculates the standard deviation of a population based on the entire population, using the numbers in a column of a list or database that match conditions you specify.
10	DSUMAdds the numbers in a column of a list or database that match conditions you specify.
11	DVAREstimates the variance of a population based on a sample by using the numbers in a column of a list or database that match conditions you specify.
12	DVARPCalculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match conditions you specify.

Advanced Excel - Date & Time Functions

Excel 日期和时间函数可用于从 Excel 日期和时间中提取信息，并在其上执行运算。

Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times.

一些 Excel 日期和时间函数是 Excel 2010 或 Excel 2013 的新函数，因此在早期版本的 Excel 中不可用。您可以在函数的适用性部分中找到此信息。

Some of the Excel Date & Time functions are new to Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. You can find this information in the applicability section of the function.

Date and Time Functions

下表列出了所有日期和时间函数 −

The following table lists all the Date & Time functions −

S.No.	Function and Description
1	DATEReturns the serial number of a particular date.
2	DATEDIFCalculates the number of days, months, or years between two dates.
3	DATEVALUEConverts a date in the form of text to a serial number.
4	DAYConverts a serial number to a day of the month.
5	DAYSReturns the number of days between two dates.
6	DAYS360Calculates the number of days between two dates, based on a 360-day year.
7	EDATEReturns the serial number of the date that is the indicated number of months before or after the start date.
8	EOMONTHReturns the serial number of the last day of the month before or after a specified number of months.
9	HOURConverts a serial number to an hour.
10	ISOWEEKNUMReturns the number of the ISO week number of the year for a given date.
11	MINUTEConverts a serial number to a minute.
12	MONTHConverts a serial number to a month.
13	NETWORKDAYSReturns the number of whole workdays between two dates.
14	NETWORKDAYS.INTLReturns the number of whole workdays between two dates (international version).
15	NOWReturns the serial number of the current date and time.
16	SECONDConverts a serial number to a second.
17	TIMEReturns the serial number of a particular time.
18	TIMEVALUEConverts a time in the form of text to a serial number.
19	TODAYReturns the serial number of today’s date.
20	WEEKDAYConverts a serial number to a day of the week.
21	WEEKNUMReturns the week number in the year.
22	WORKDAYReturns the serial number of the date before or after a specified number of workdays.
23	WORKDAY.INTLReturns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days.
24	YEARConverts a serial number to a year.
25	YEARFRACReturns the year fraction representing the number of whole days between start_date and end_date.

Advanced Excel - Engineering Functions

Excel 工程函数执行最常用的工程计算，其中许多与贝塞尔函数、复数或不同进制之间的转换有关。

The Excel Engineering functions perform the most commonly used Engineering calculations, many of which relate to Bessel functions, complex numbers, or converting between different bases.

Engineering Functions

下表列出了所有工程函数−

The following table lists all the Engineering functions −

S.No.	Function and Description
1	BESSELIReturns the modified Bessel function In(x)
2	BESSELJReturns the Bessel function Jn(x)
3	BESSELKReturns the modified Bessel function Kn(x)
4	BESSELYReturns the Bessel function Yn(x)
5	BIN2DECConverts a binary number to decimal
6	BIN2HEXConverts a binary number to hexadecimal
7	BIN2OCTConverts a binary number to octal
8	BITANDReturns a 'Bitwise And' of two numbers
9	BITLSHIFTReturns a value number shifted left by shift_amount bits
10	BITORReturns a bitwise OR of 2 numbers
11	BITRSHIFTReturns a value number shifted right by shift_amount bits
12	BITXORReturns a bitwise 'Exclusive Or' of two numbers
13	COMPLEXConverts real and imaginary coefficients into a complex number
14	CONVERTConverts a number from one measurement system to another
15	DEC2BINConverts a decimal number to binary
16	DEC2HEXConverts a decimal number to hexadecimal
17	DEC2OCTConverts a decimal number to octal
18	DELTATests whether two values are equal
19	ERFReturns the error function
20	ERF.PRECISEReturns the error function
21	ERFCReturns the complementary error function
22	ERFC.PRECISEReturns the complementary error function
23	GESTEPTests whether a number is greater than a threshold value
24	HEX2BINConverts a hexadecimal number to binary
25	HEX2DECConverts a hexadecimal number to decimal
26	HEX2OCTConverts a hexadecimal number to octal
27	IMABSReturns the absolute value (modulus) of a complex number
28	IMAGINARYReturns the imaginary coefficient of a complex number
29	IMARGUMENTReturns the argument theta, an angle expressed in radians
30	IMCONJUGATEReturns the complex conjugate of a complex number
31	IMCOSReturns the cosine of a complex number
32	IMCOSHReturns the hyperbolic cosine of a complex number
33	IMCOTReturns the cotangent of a complex number
34	IMCSCReturns the cosecant of a complex number
35	IMCSCHReturns the hyperbolic cosecant of a complex number
36	IMDIVReturns the quotient of two complex numbers
37	IMEXPReturns the exponential of a complex number
38	IMLNReturns the natural logarithm of a complex number
39	IMLOG2Returns the base-2 logarithm of a complex number
40	IMLOG10Returns the base-10 logarithm of a complex number
41	IMPOWERReturns a complex number raised to an integer power
42	IMPRODUCTReturns the product of complex numbers
43	IMREALReturns the real coefficient of a complex number
44	IMSECReturns the secant of a complex number
45	IMSECHReturns the hyperbolic secant of a complex number
46	IMSINReturns the sine of a complex number
47	IMSINHReturns the hyperbolic sine of a complex number
48	IMSQRTReturns the square root of a complex number
49	IMSUBReturns the difference of two complex numbers
50	IMSUMReturns the sum of complex numbers
51	IMTANReturns the tangent of a complex number
52	OCT2BINConverts an octal number to binary
53	OCT2DECConverts an octal number to decimal
54	OCT2HEXConverts an octal number to hexadecimal

Advanced Excel - Financial Functions

Excel 财务函数 выполняет许多常见的财务计算，比如收益、利率、期限、价值和资产贬值计算。

Excel Financial functions perform many of the common financial calculations, such as the calculation of yield, interest rates, duration, valuation and asset depreciation.

S.No.	Function and Description
1	ACCRINTReturns the accrued interest for a security that pays periodic interest
2	ACCRINTMReturns the accrued interest for a security that pays interest at maturity
3	AMORDEGRCReturns the depreciation for each accounting period
4	AMORLINCReturns the depreciation for each accounting period (the depreciation coefficient depends on the life of the assets)
5	COUPDAYBSReturns the number of days from the beginning of the coupon period to the settlement date
6	COUPDAYSReturns the number of days in the coupon period that contains the settlement date
7	COUPDAYSNCReturns the number of days from the settlement date to the next coupon date
8	COUPNCDReturns the next coupon date after the settlement date
9	COUPNUMReturns the number of coupons payable between the settlement date and maturity date
10	COUPPCDReturns the previous coupon date before the settlement date
11	CUMIPMTReturns the cumulative interest paid between two periods
12	CUMPRINCReturns the cumulative principal paid on a loan between two periods
13	DBReturns the depreciation of an asset for a specified period, using the fixed-declining-balance method
14	DDBReturns the depreciation of an asset for a specified period, using the double-declining-balance method or some other method that you specify
15	DISCReturns the discount rate for a security
16	DOLLARDEConverts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
17	DOLLARFRConverts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
18	DURATIONReturns the annual duration of a security with periodic interest payments
19	EFFECTReturns the effective annual interest rate
20	FVReturns the future value of an investment
21	FVSCHEDULEReturns the future value of an initial principal after applying a series of compound interest rates
22	INTRATEReturns the interest rate for a fully invested security
23	IPMTReturns the interest payment for an investment for a given period
24	IRRReturns the internal rate of return for a series of cash flows
25	ISPMTCalculates the interest paid during a specific period of an investment
26	MDURATIONReturns the Macauley modified duration for a security with an assumed par value of $100
27	MIRRReturns the internal rate of return where positive and negative cash flows are financed at different rates
28	NOMINALReturns the annual nominal interest rate
29	NPERReturns the number of periods for an investment
30	NPVReturns the net present value of an investment based on a series of periodic cash flows and a discount rate
31	ODDFPRICEReturns the price per $100 face value of a security with an odd first period
32	ODDFYIELDReturns the yield of a security with an odd first period
33	ODDLPRICEReturns the price per $100 face value of a security with an odd last period
34	ODDLYIELDReturns the yield of a security with an odd last period
35	PDURATIONReturns the number of periods required by an investment to reach a specified value
36	PMTReturns the periodic payment for an annuity
37	PPMTReturns the payment on the principal for an investment for a given period
38	PRICEReturns the price per $100 face value of a security that pays periodic interest
39	PRICEDISCReturns the price per $100 face value of a discounted security
40	PRICEMATReturns the price per $100 face value of a security that pays interest at maturity
41	PVReturns the present value of an investment
42	RATEReturns the interest rate per period of an annuity
43	RECEIVEDReturns the amount received at maturity for a fully invested security
44	RRIReturns an equivalent interest rate for the growth of an investment
45	SLNReturns the straight-line depreciation of an asset for one period
46	SYDReturns the sum-of-years’ digits depreciation of an asset for a specified period
47	TBILLEQReturns the bond-equivalent yield for a Treasury bill
48	TBILLPRICEReturns the price per $100 face value for a Treasury bill
49	TBILLYIELDReturns the yield for a Treasury bill
50	VDBReturns the depreciation of an asset for a specified or partial period using a declining-balance method
51	XIRRReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic
52	XNPVReturns the net present value for a schedule of cash flows that is not necessarily periodic
53	YIELDReturns the yield on a security that pays periodic interest
54	YIELDDISCReturns the annual yield for a discounted security, for example, a Treasury bill
55	YIELDMATReturns the annual yield of a security that pays interest at maturity

Advanced Excel - Information Functions

Information 函数提供有关 Excel 工作表中单元格的内容、格式和位置的信息。

Information functions provide information about the content, formatting and location of cells in an Excel Worksheet.

Information Functions

下表列出所有 Information 函数 −

The following table lists all the Information functions −

S.No.	Function and Description
1	CELLReturns information about the formatting, location, or contents of a cell
2	ERROR.TYPEReturns a number corresponding to an error type
3	INFOReturns information about the current operating environment
4	ISBLANKReturns TRUE if the value is blank
5	ISERRReturns TRUE if the value is any error value except #N/A
6	ISERRORReturns TRUE if the value is any error value
7	ISEVENReturns TRUE if the number is even
8	ISFORMULAReturns TRUE if there is a reference to a cell that contains a formula
9	ISLOGICALReturns TRUE if the value is a logical value
10	ISNAReturns TRUE if the value is the #N/A error value
11	ISNONTEXTReturns TRUE if the value is not text
12	ISNUMBERReturns TRUE if the value is a number
13	ISODDReturns TRUE if the number is odd
14	ISREFReturns TRUE if the value is a reference
15	ISTEXTReturns TRUE if the value is text
16	NReturns a value converted to a number
17	NAReturns the error value #N/A
18	SHEETReturns the sheet number of the referenced sheet
19	SHEETSReturns the number of sheets in a reference
20	TYPEReturns a number indicating the data type of a value

Advanced Excel - Logical Functions

逻辑函数包括布尔运算符和条件测试，它们是许多工作电子表格的重要组成部分。

Logical functions include the boolean operators and conditional tests, which will be an essential part of many working spreadsheets.

Logical Functions

下表列出了所有逻辑函数 −

The following table lists all the Logical functions −

S.No.	Function and Description
1	ANDReturns TRUE if all its arguments are TRUE.
2	FALSEReturns the logical value FALSE.
3	IFSpecifies a logical test to perform.
4	IFERRORReturns a different result if the first argument evaluates to an error.
5	IFNAReturns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.
6	IFSChecks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
7	NOTReverses the logic of its argument.
8	ORReturns TRUE if any argument is TRUE.
9	SWITCHEvaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
10	TRUEReturns the logical value TRUE.
11	XORReturns a logical exclusive OR of all arguments.

Lookup and Reference Functions

查找和引用函数可帮助你处理数据数组，并在你需要在不同数据集之间交叉引用时尤其有用。它们执行的任务包括提供有关一个范围内信息，返回给定地址或值的位置，或查找特定值。

Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific values.

Lookup and Reference Functions

下表列出了所有查找和引用函数 −

The following table lists all the Lookup & Reference functions −

S.No.	Function and Description
1	ADDRESSReturns a reference as text to a single cell in a worksheet
2	AREASReturns the number of areas in a reference
3	CHOOSEChooses a value from a list of values
4	COLUMNReturns the column number of a reference
5	COLUMNSReturns the number of columns in a reference
6	FORMULATEXTReturns the formula at the given reference as text
7	GETPIVOTDATAReturns data stored in a PivotTable
8	HLOOKUPSearches for a value in the top row of a table and then returns a value in the same column from a row you specify in the table
9	HYPERLINKCreates a shortcut that opens a document on your hard drive, a server, or the Internet
10	INDEXUses an index to choose a value from a reference or array
11	INDIRECTReturns a reference indicated by a text value
12	LOOKUPReturns a value either from a one-row or one-column range or from an array
13	MATCHReturns the relative position of an item in an array
14	OFFSETReturns a reference offset from a given reference
15	ROWReturns the row number of a reference
16	ROWSReturns the number of rows in a reference
17	RTDReturns real-time data from a program that supports COM automation
18	TRANSPOSEReturns the transpose of an array
19	VLOOKUPSearches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table

Math and Trigonometric Functions

Excel 数学和三角函数执行许多常见的数学计算，包括基本算术、条件求和和乘积、指数和对数，以及三角比。

The Excel Math & Trig functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms, and the trigonometric ratios.

一些更多与数学相关的函数也将在统计函数和工程函数类别中讨论。

Some more math-related functions are also discussed in the Statistical functions and Engineering functions categories.

Math and Trigonometric Functions

下表列出所有数学和三角函数-

The following table lists all the Math & Trigonometric functions −

S.No.	Function and Description
1	ABSReturns the absolute value of a number
2	ACOSReturns the arccosine of a number
3	ACOSHReturns the inverse hyperbolic cosine of a number
4	ACOTReturns the arccotangent of a number
5	ACOTHReturns the hyperbolic arccotangent of a number
6	AGGREGATEReturns an aggregate in a list or database
7	ARABICConverts a Roman number to Arabic, as a number
8	ASINReturns the arcsine of a number
9	ASINHReturns the inverse hyperbolic sine of a number
10	ATANReturns the arctangent of a number
11	ATAN2Returns the arctangent from x and y coordinates
12	ATANHReturns the inverse hyperbolic tangent of a number
13	BASEConverts a number into a text representation with the given radix (base)
14	CEILING.MATHRounds a number up, to the nearest integer or to the nearest multiple of significance
15	COMBINReturns the number of combinations for a given number of objects
16	COMBINAReturns the number of combinations with repetitions for a given number of items
17	COSReturns the cosine of a number
18	COSHReturns the hyperbolic cosine of a number
19	COTReturns the cotangent of an angle
20	COTHReturns the hyperbolic cotangent of a number
21	CSCReturns the cosecant of an angle
22	CSCHReturns the hyperbolic cosecant of an angle
23	DECIMALConverts a text representation of a number in a given base into a decimal number
24	DEGREESConverts radians to degrees
25	EVENRounds a number up to the nearest even integer
26	EXPReturns e raised to the power of a given number
27	FACTReturns the factorial of a number
28	FACTDOUBLEReturns the double factorial of a number
29	FLOOR.MATHRounds a number down, to the nearest integer or to the nearest multiple of significance
30	GCDReturns the greatest common divisor
31	INTRounds a number down to the nearest integer
32	LCMReturns the least common multiple
33	LNReturns the natural logarithm of a number
34	LOGReturns the logarithm of a number to a specified base
35	LOG10Returns the base-10 logarithm of a number
36	MDETERMReturns the matrix determinant of an array
37	MINVERSEReturns the matrix inverse of an array
38	MMULTReturns the matrix product of two arrays
39	MODReturns the remainder from division
40	MROUNDReturns a number rounded to the desired multiple
41	MULTINOMIALReturns the multinomial of a set of numbers
42	MUNITReturns the unit matrix or the specified dimension
43	ODDRounds a number up to the nearest odd integer
44	PIReturns the value of pi
45	POWERReturns the result of a number raised to a power
46	PRODUCTMultiplies its arguments
47	QUOTIENTReturns the integer portion of a division
48	RADIANSConverts degrees to radians
49	RANDReturns a random number between 0 and 1
50	RANDBETWEENReturns a random number between the numbers that you specify
51	ROMANConverts an Arabic numeral to Roman, as text
52	ROUNDRounds a number to a specified number of digits
53	ROUNDDOWNRounds a number down, toward 0
54	ROUNDUPRounds a number up, away from 0
55	SECReturns the secant of an angle
56	SECHReturns the hyperbolic secant of an angle
57	SERIESSUMReturns the sum of a power series based on the formula
58	SIGNReturns the sign of a number
59	SINReturns the sine of the given angle
60	SINHReturns the hyperbolic sine of a number
61	SQRTReturns a positive square root
62	SQRTPIReturns the square root of pi
63	SUBTOTALReturns a subtotal in a list or database
64	SUMAdds its arguments
65	SUMIFAdds the cells specified by a given criteria
66	SUMIFSAdds the cells specified by a multiple criteria
67	SUMPRODUCTReturns the sum of the products of corresponding array components
68	SUMSQReturns the sum of the squares of the arguments
69	SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays
70	SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays
71	SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays
72	TANReturns the tangent of a number
73	TANHReturns the hyperbolic tangent of a number
74	TRUNCTruncates a number (you specify the precision of the truncation)

Advanced Excel - Statistical Functions

统计函数执行从基本平均值、中位数和众数到更复杂的统计分布和概率测试的计算。

Statistical functions perform calculations ranging from basic mean, median & mode to the more complex statistical distribution and probability tests.

Statistical Functions

下表列出了所有统计函数：

The following table lists all the Statistical functions −

S.No.	Function and Description
1	AVEDEVReturns the average of the absolute deviations of data points from their mean
2	AVERAGEReturns the average of its arguments
3	AVERAGEAReturns the average of its arguments and includes evaluation of text and logical values
4	AVERAGEIFReturns the average for the cells specified by a given criterion
5	AVERAGEIFSReturns the average for the cells specified by multiple criteria
6	BETA.DISTReturns the beta cumulative distribution function
7	BETA.INVReturns the inverse of the cumulative distribution function for a specified beta distribution
8	BINOM.DISTReturns the individual term binomial distribution probability
9	BINOM.DIST.RANGEReturns the probability of a trial result using a binomial distribution
10	BINOM.INVReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
11	CHISQ.DISTReturns the cumulative beta probability density function
12	CHISQ.DIST.RTReturns the one-tailed probability of the chi-squared distribution
13	CHISQ.INVReturns the cumulative beta probability density function
14	CHISQ.INV.RTReturns the inverse of the one-tailed probability of the chi-squared distribution
15	CHISQ.TESTReturns the test for independence
16	CONFIDENCE.NORMReturns the confidence interval for a population mean
17	CONFIDENCE.TReturns the confidence interval for a population mean, using a Student’s t distribution
18	CORRELReturns the correlation coefficient between two data sets
19	COUNTCounts how many numbers are in the list of arguments
20	COUNTACounts how many values are in the list of arguments
21	COUNTBLANKCounts the number of blank cells in the argument range
22	COUNTIFCounts the number of cells that meet the criteria you specify in the argument
23	COUNTIFSCounts the number of cells that meet multiple criteria
24	COVARIANCE.PReturns covariance, the average of the products of paired deviations
25	COVARIANCE.SReturns the sample covariance, the average of the products deviations for each data point pair in two data sets
26	DEVSQReturns the sum of squares of deviations
27	EXPON.DISTReturns the exponential distribution
28	F.DISTReturns the F probability distribution
29	F.DIST.RTReturns the F probability distribution
30	F.INVReturns the inverse of the F probability distribution
31	F.INV.RTReturns the inverse of the F probability distribution
32	F.TESTReturns the result of an F-test
33	FISHERReturns the Fisher transformation
34	FISHERINVReturns the inverse of the Fisher transformation
35	FORECASTReturns a value along a linear trend
36	FORECAST.ETSCalculates a future value based on existing values using the Exponential Triple Smoothing (ETS) algorithm
37	FORECAST.ETS.CONFINTReturns a confidence interval for the forecast value at the specified target date
38	FORECAST.ETS.SEASONALITYReturns the length of the repetitive pattern detected for the specified time series
39	FORECAST.ETS.STATReturns a statistical value as a result of time series forecasting
40	FORECAST.LINEARCalculates a future value by using existing values, using linear regression.
41	FREQUENCYReturns a frequency distribution as a vertical array
42	GAMMAReturns the Gamma function value
43	GAMMA.DISTReturns the gamma distribution
44	GAMMA.INVReturns the inverse of the gamma cumulative distribution
45	GAMMALNReturns the natural logarithm of the gamma function, G(x)
46	GAMMALN.PRECISEReturns the natural logarithm of the gamma function, G(x)
47	GAUSSReturns 0.5 less than the standard normal cumulative distribution
48	GEOMEANReturns the geometric mean
49	GROWTHReturns values along an exponential trend
50	HARMEANReturns the harmonic mean
51	HYPGEOM.DISTReturns the hypergeometric distribution
52	INTERCEPTReturns the intercept of the linear regression line
53	KURTReturns the kurtosis of a data set
54	LARGEReturns the kth largest value in a data set
55	LINESTReturns the parameters of a linear trend
56	LOGESTReturns the parameters of an exponential trend
57	LOGNORM.DISTReturns the cumulative lognormal distribution
58	LOGNORM.INVReturns the inverse of the lognormal cumulative distribution
59	MAXReturns the maximum value in a list of arguments, ignoring logical values and text
60	MAXAReturns the maximum value in a list of arguments, including logical values and text
61	MAXIFSReturns the maximum value among cells specified by a given set of conditions or criteria.
62	MEDIANReturns the median of the given numbers
63	MINReturns the minimum value in a list of arguments, ignoring logical values and text
64	MINAReturns the minimum value in a list of arguments, including logical values and text
65	MINIFSReturns the minimum value among cells specified by a given set of conditions or criteria.
66	MODE.MULTReturns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
67	MODE.SNGLReturns the most common value in a data set
68	NEGBINOM.DISTReturns the negative binomial distribution
69	NORM.DISTReturns the normal cumulative distribution
70	NORM.INVReturns the inverse of the normal cumulative distribution
71	NORM.S.DISTReturns the standard normal cumulative distribution
72	NORM.S.INVReturns the inverse of the standard normal cumulative distribution
73	PEARSONReturns the Pearson product moment correlation coefficient
74	PERCENTILE.EXCReturns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
75	PERCENTILE.INCReturns the k-th percentile of values in a range
76	PERCENTRANK.EXCReturns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
77	PERCENTRANK.INCReturns the percentage rank of a value in a data set
78	PERMUTReturns the number of permutations for a given number of objects
79	PERMUTATIONAReturns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
80	PHIReturns the value of the density function for a standard normal distribution
81	POISSON.DISTReturns the Poisson distribution
82	PROBReturns the probability that values in a range are between two limits
83	QUARTILE.EXCReturns the quartile of the data set, based on percentile values from 0..1, exclusive
84	QUARTILE.INCReturns the quartile of a data set
85	RANK.AVGReturns the rank of a number in a list of numbers
86	RANK.EQReturns the rank of a number in a list of numbers
87	RSQReturns the square of the Pearson product moment correlation coefficient
88	SKEWReturns the skewness of a distribution
89	SKEW.PReturns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean
90	SLOPEReturns the slope of the linear regression line
91	SMALLReturns the kth smallest value in a data set
92	STANDARDIZEReturns a normalized value
93	STDEV.PCalculates standard deviation based on the entire population
94	STDEV.SEstimates standard deviation based on a sample
95	STDEVAEstimates standard deviation based on a sample, including text and logical values
96	STDEVPACalculates standard deviation based on the entire population, including text and logical values
97	STEYXReturns the standard error of the predicted y-value for each x in the regression
98	T.DISTReturns the Percentage Points (probability) for the Student t-distribution
99	T.DIST.2TReturns the Percentage Points (probability) for the Student t-distribution
100	T.DIST.RTReturns the Student’s t-distribution
101	T.INVReturns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom
102	T.INV.2TReturns the inverse of the Student’s t-distribution
103	T.TESTReturns the probability associated with a Student’s t-test
104	TRENDReturns values along a linear trend
105	TRIMMEANReturns the mean of the interior of a data set
106	VAR.PCalculates variance based on the entire population
107	VAR.SEstimates variance based on a sample
108	VARAEstimates variance based on a sample, including logical values and text
109	VARPACalculates variance based on the entire population, including logical values and text
110	WEIBULL.DISTReturns the Weibull distribution
111	Z.TESTReturns the one-tailed probability-value of a z-test
112	BAHTTEXTConverts a number to Thai text and adds a suffix of "Baht."
113	CHARReturns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.
114	CLEANRemoves all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system.
115	CODEReturns a numeric code for the first character in a text string. The returned code corresponds to the ANSI character set for the Windows System.
116	CONCATCombines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.
117	CONCATENATEJoins two or more text strings into one string.
118	DOLLARConverts a number to text format and applies a currency symbol. The name of the Function and the symbol that it applies depend upon your language settings.
119	ExactCompares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
120	FINDFIND and FINDB 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.
121	FIXEDRounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
122	LEFTReturns the first character or characters in a text string, based on the number of characters you specify.
123	LENLEN returns the number of characters in a text string. LENB returns the number of bytes used to represent the characters in a text string.
124	LOWERConverts all uppercase letters in a text string to lowercase.
125	MIDReturns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
126	NUMBERVALUEConverts text to a number, in a locale-independent way.
127	PROPERCapitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
128	REPLACEReplaces part of a text string, based on the number of characters you specify, with a different text string.
129	REPTRepeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.
130	RIGHTReturns the last character or characters in a text string, based on the number of characters you specify.
131	SEARCHThe SEARCH and SEARCHB functions 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.
132	SUBSTITUTESubstitutes new_text for old_text in a text string.
133	TThe T function returns the text referred to by value.
134	TEXTConverts a numeric value to text and lets you specify the display formatting by using special format strings.
135	TEXTJOINCombines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
136	TRIMRemoves all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
137	UNICHARReturns the Unicode character that is referenced by the given numeric value.
138	UNICODEReturns the number (code point) corresponding to the first character of the text.
139	UPPERConverts text to uppercase.
140	VALUEConverts a text string that represents a number to a number.
141	ENCODEURLReturns a URL-encoded string.
142	FILTERXMLReturns specific data from the XML content by using the specified XPath.
143	WEBSERVICEReturns data from a web service on the Internet or Intranet.