## 统计公式

关键词:  统计公式

Formulas Description Examples
AVEDEV Syntax: AVEDEV(number1, [number2], ...)
Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.
=AVEDEV(A1,A2,A3)
=AVEDEV(A1:A3)
AVERAGE Syntax: AVERAGE(number1, number2, ...)
Returns the average of the arguments. Number1, number2, ... are numerical values or ranges. Text is ignored.
=AVERAGE(A1,A2,A3)
=AVERAGE(A1:A3)
AVERAGEIF Syntax: AVERAGEIF(range, criteria, average_range)
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
=AVERAGEIF(B2:B5,"<23000")
=AVERAGEIF(A2:A6,"=*West",B2:B6)
AVERAGEIFS Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ....])
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
=AVERAGEIFS(B2:B5,B2:B5,">70",B2:B5,"<90")
=AVERAGEIFS(D2:D5,D2:D5, "<>Incomplete", D2:D5, ">80")
BINOM.DIST Syntax: BINOM.DIST(number_s,trials,probability_s,cumulative)
Returns the individual term binomial distribution probability.
=BINOM.DIST(6, 10, 0.5, FALSE)
CORREL Syntax: CORREL(array1, array2)
Returns the correlation coefficient of the array1 and array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties.
=CORREL(a1:a10, b1:b10)
COUNT Syntax: COUNT(value1, value2, ...)
Counts how many numbers are in the list of arguments. Text entries are ignored. Value1, value2, ... value30 are values or ranges which are to be counted.
=COUNT(a1:a10)
=COUNT(a1,a2,a3)
COUNTA Syntax: COUNTA(value1, [value2], ...)
Counts the number of cells that are not empty in a range.
=COUNTA(a1,a2,a3,a4)
COUNTIF Syntax: COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given criteria.
range is the range of cells that you want to count based on the criteria.
criteria is used to determine which cells to count.
=COUNTIF(A2:A25,"temp")
COUNTIFS Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]â¦)
Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")
COUNTBLANK Syntax: COUNTBLANK(range)
Counts empty cells in a specified range of cells.
=COUNTBLANK(A1:B25)
DEVSQ Syntax: DEVSQ(number1, [number2], ...)
Returns the sum of squares of deviations of data points from their sample mean.
=DEVSQ(A1,A2,A3)
=DEVSQ(A1:A3)
EXPON.DIST Syntax:EXPON.DIST(x,lambda,cumulative)
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.
=EXPON.DIST(x,lambda,cumulative)
FISHER Syntax:FISHER(x)
Returns the Fisher transformation at x.
=FISHER(x)
FISHERINV Syntax:FISHERINV(x)
Returns the inverse of the Fisher transformation.
=FISHERINV(x)
GAMMA Syntax: GAMMA(number)
Return the gamma function value.
=GAMMA(2.5)
GAMMALN Syntax: GAMMALN(number)
Returns the natural logarithm of the gamma function.
=GAMMALN(2.5)
GEOMEAN Syntax: GEOMEAN(number1, [number2], ...)
Returns the geometric mean of an array or range of positive data.
=GEOMEAN(A1:B25)
HARMEAN Syntax: HARMEAN(number1, [number2], ...)
Returns the harmonic mean of an array or range of positive data.
=HARMEAN(A1:B25)
LARGE Syntax: LARGE(array, nth_position)
Returns the nth largest value from a set of values.
=LARGE(A1:A5, 1)
MAX Syntax: MAX(number1, number2, ...)
Returns the maximum value in a list of arguments. Ignores logic values and text.
=MAX(a1:a5)
=MAX(a1,a2,a3,10)
MEDIAN Syntax: MEDIAN(number1, number2, ...)
Median function returns the median of the numbers provided.
=MEDIAN(a1, a2, a3)
=MEDIAN(a1:a3)
MIN Syntax: MIN(number1, number2, ...)
Returns the minimum value in a list of arguments. Ignores logic values and text.
=MIN(a1:a5)
=MIN(a1,a2,a3,10)
MODE Syntax: MODE(number1, number2, ...)
Mode return the most frequently occurring value in a list of numbers.
=MODE(A1, A2)
MODE.SNGL Syntax: MODE.SNGL(number1, number2, ...)
Mode return the most frequently occurring value in a list of numbers.
=MODE.SNGL(A1, A2)
PEARSON Syntax: PEARSON(array1, array2)
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.
=PEARSON(A2:A6,B2:B6)
PERCENTILE.EXC Syntax: PERCENTILE.EXC(array,k)
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.
=PERCENTILE.EXC(A1:A5,0.5)
PERCENTILE.INC Syntax: PERCENTILE.INC(array,k)
Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive.
=PERCENTILE.INC(A1:A5,0.5)
QUARTILE.EXC Syntax: QUARTILE.EXC(array,quart)
Returns the quartile of the data set, based on percentile values from 0..1, exclusive.
=QUARTILE.EXC(A1:A5,1)
QUARTILE.INC Syntax: QUARTILE.INC(array,quart)
Returns the quartile of a data set, based on percentile values from 0..1, inclusive.
=QUARTILE.INC(A1:A5,1)
RANK.AVG Syntax: RANK.AVG(number,ref,[order])
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.AVG(1, B1:B5)
RANK.EQ Syntax: RANK.EQ(number,ref,[order])
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.
=RANK.EQ(1, B1:B5)
RSQ Syntax: RSQ(known_y's, known_x's)
Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
=RSQ(A1:A5, B1:B5)
SLOPE Syntax: SLOPE(known_y's, known_x's)
Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.
=SLOPE(A1:A5, B1:B5)
SMALL Syntax: SMALL(array, nth_position)
Returns the nth smallest value from a set of values.
=SMALL(A1:A5, 2)
=SMALL(A1,A2,A3,A4...)
STDEV.S Syntax: STDEV.S(number1,[number2],...])
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).
=STDEV.S(A1:A5])
STDEV.P Syntax: STDEV.P(number1,[number2],...])
Calculates standard deviation based on the entire population given as arguments (ignores logical values and text).
=STDEV.P(A1:A5])
VAR.S Syntax: VAR.S(number1,[number2],...])
Estimates variance based on a sample (ignores logical values and text in the sample).
=VAR.S(A1:A5])
VAR.P Syntax: VAR.P(number1,[number2],...])
Calculates standard deviation based on the entire population given as arguments (ignores logical values and text).
=VAR.P(A1:A5])