Advanced Excel 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.