PLUGIN
Page tree
Skip to end of metadata
Go to start of metadata

根据Microsoft Excel官方文档，下表列出了Smart Tables for Confluence中包含的所有公式.

具体使用可以在表格编辑器中进行计算



公式作用
1ABSMath and trigonometry: 返回数字的绝对值
2ACCRINTFinancial: 返回支付定期利息的证券的应计利息
3ACOSMath and trigonometry: 返回数字的反余弦
4ACOSHMath and trigonometry: 返回数字的反双曲余弦
5ACOTMath and trigonometry: 返回数字的余切
6ACOTHMath and trigonometry: 返回数字的双曲余切
7AGGREGATEMath and trigonometry: 返回列表或数据库中的聚合
8ANDLogical:  如果其所有参数均为TRUE，则返回TRUE 
9ARABICMath and trigonometry: 将罗马数字转换为阿拉伯数字
10ASINMath and trigonometry: 返回数字的反正弦 
11ASINHMath and trigonometry:返回数字的反双曲正弦
12ATANMath and trigonometry: 返回数字的反正切
13ATAN2Math and trigonometry: 从x坐标和y坐标返回反正切
14ATANHMath and trigonometry: 返回数字的反双曲正切
15AVEDEVStatistical: 返回数据点与其平均值的绝对偏差的平均值
16AVERAGEStatistical: 返回其参数的平均值
17AVERAGEAStatistical: 返回其参数的平均值，包括数字、文本和逻辑值
18AVERAGEIFStatistical: 返回满足给定条件的范围内所有单元格的平均值（算术平均值）
19AVERAGEIFSStatistical:返回满足多个条件的所有单元格的平均值（算术平均值）
20BASEMath and trigonometry: 将数字转换为具有给定基数（基数）的文本表示形式
21BESSELIEngineering: 返回（x）中修改过的贝塞尔函数
22BESSELJEngineering: 返回贝塞尔函数Jn（x）
23BESSELKEngineering:  返回修改后的贝塞尔函数Kn（x） 
24BESSELYEngineering: 返回贝塞尔函数Yn（x）
25BETA.DISTStatistical: 返回beta累积分布函数
26BETA.INVStatistical: 返回指定beta分布的累积分布函数的倒数
27BETADISTCompatibility: 返回beta累积分布函数
28BETAINVCompatibility: 返回指定beta分布的累积分布函数的倒数
29BIN2DECEngineering: Converts a binary number to decimal
30BIN2HEXEngineering: 将二进制数转换为十六进制数
31BIN2OCTEngineering:  返回单个项的二项分布概率 
32BINOM.DISTStatistical: 返回单个项的二项分布概率
33BINOM.DIST.RANGEStatistical: 使用二项分布返回试验结果的概率
34BINOM.INVStatistical: 返回累积二项分布小于或等于标准值的最小值
35BINOMDISTCompatibility: 返回单个项的二项分布概率
36BITANDEngineering:返回两个数字的“按位And”
37BITLSHIFTEngineering:返回按移位量位左移的数值
38BITOREngineering:返回2个数字的按位或
39BITRSHIFTEngineering:返回按移位量位右移的数值
40BITXOREngineering:返回两个数字的按位“异或”
41CEILINGMath and trigonometry: 将数字舍入为最接近的整数或最接近的有效倍数
42CHARText: 返回由代码编号指定的字符
43CHISQ.DISTStatistical: 返回累积贝塔概率密度函数
44CHISQ.DIST.RTStatistical: 返回卡方分布的单尾概率
45CHISQ.INVStatistical: 返回累积贝塔概率密度函数
46CHISQ.INV.RTStatistical: Returns the inverse of the one-tailed probability of the chi-squared distribution
47CHOOSELookup and reference: 从值列表中选择一个值
48CLEANText: 从文本中删除所有不可打印的字符
49CODEText: 返回文本字符串中第一个字符的数字代码
50COLUMNLookup and reference: 返回引用的列号
51COLUMNSLookup and reference: Returns the number of columns in a reference
52COMBINMath and trigonometry: 返回给定数量对象的组合数
53COMBINAMath and trigonometry:返回给定项目数的重复组合数
54COMPLEXEngineering: 将实系数和虚系数转换为复数
55CONCATENATEText: 将多个文本项合并为一个文本项
56CONFIDENCECompatibility: 返回总体平均值的置信区间
57CONFIDENCE.NORMStatistical: 返回总体平均值的置信区间
58CONFIDENCE.TStatistical:使用Student t分布返回总体平均值的置信区间
59CONVERTEngineering: 将数字从一个测量系统转换为另一个测量系统
60CORRELStatistical: 返回两个数据集之间的相关系数
61COSMath and trigonometry: 返回数字的余弦
62COSHMath and trigonometry: 返回数字的双曲余弦
63COTMath and trigonometry:返回数字的双曲余弦
64COTHMath and trigonometry:返回角度的余切
65COUNTStatistical: 计算参数列表中有多少个数字
66COUNTAStatistical: 计算参数列表中的值数
67COUNTBLANKStatistical: 计算范围内的空白单元格数
68COUNTIFStatistical: 统计满足给定条件的范围内的单元格数
69COUNTIFSStatistical: 统计满足多个条件的范围内的单元格数
70COVARIANCE.PStatistical: Returns covariance, the average of the products of paired deviations
71COVARIANCE.SStatistical: Returns the sample covariance, the average of the products deviations for each data point pair in two data sets
72CSCMath and trigonometry: Returns the cosecant of an angle
73CSCHMath and trigonometry: Returns the hyperbolic cosecant of an angle
74CUMIPMTFinancial: Returns the cumulative interest paid between two periods
75CUMPRINCFinancial: Returns the cumulative principal paid on a loan between two periods
76DATEVALUEDate and time: Converts a date in the form of text to a serial number
77DAYDate and time: Converts a serial number to a day of the month
78DAYSDate and time: Returns the number of days between two dates
79DAYS360Date and time: Calculates the number of days between two dates based on a 360-day year
80DBFinancial: Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
81DDBFinancial: Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
82DEC2BINEngineering: Converts a decimal number to binary
83DEC2HEXEngineering: Converts a decimal number to hexadecimal
84DEC2OCTEngineering: Converts a decimal number to octal
85DECIMALMath and trigonometry: Converts a text representation of a number in a given base into a decimal number
86DEGREESMath and trigonometry: Converts radians to degrees
87DELTAEngineering: Tests whether two values are equal
88DEVSQStatistical: Returns the sum of squares of deviations
89DOLLARDEFinancial: Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
90DOLLARFRFinancial: Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
91EFFECTFinancial: Returns the effective annual interest rate
92EOMONTHDate and time: Returns the serial number of the last day of the month before or after a specified number of months
93ERFEngineering: Returns the error function
94ERFCEngineering: Returns the complementary error function
95EVENMath and trigonometry: Rounds a number up to the nearest even integer
96EXACTText: Checks to see if two text values are identical
97EXPMath and trigonometry: Returns e raised to the power of a given number
98EXPON.DISTStatistical: Returns the exponential distribution
99EXPONDISTCompatibility: Returns the exponential distribution
100F.DISTStatistical: Returns the F probability distribution
101F.DIST.RTStatistical: Returns the F probability distribution
102F.INVStatistical: Returns the inverse of the F probability distribution
103F.INV.RTStatistical: Returns the inverse of the F probability distribution
104FACTDOUBLEMath and trigonometry: Returns the double factorial of a number
105FALSELogical: Returns the logical value FALSE
106FDISTCompatibility: Returns the F probability distribution
107FINVStatistical: Returns the inverse of the F probability distribution
108FISHERStatistical: Returns the Fisher transformation
109FISHERINVStatistical: Returns the inverse of the Fisher transformation
110FLOORCompatibility: Rounds a number down, toward zero
111FORECASTStatistical: Returns a value along a linear trend
112FREQUENCYStatistical: Returns a frequency distribution as a vertical array
113FVFinancial: Returns the future value of an investment
114FVSCHEDULEFinancial: Returns the future value of an initial principal after applying a series of compound interest rates
115GAMMA.DISTStatistical: Returns the gamma distribution
116GAMMA.INVStatistical: Returns the inverse of the gamma cumulative distribution
117GAMMADISTCompatibility: Returns the gamma distribution
118GAMMAINVCompatibility: Returns the inverse of the gamma cumulative distribution
119GAMMALNStatistical: Returns the natural logarithm of the gamma function, Γ(x)
120GAMMALN.PRECISEStatistical: Returns the natural logarithm of the gamma function, Γ(x)
121GAUSSStatistical: Returns 0.5 less than the standard normal cumulative distribution
122GCDMath and trigonometry: Returns the greatest common divisor
123GEOMEANStatistical: Returns the geometric mean
124GESTEPEngineering: Tests whether a number is greater than a threshold value
125GROWTHStatistical: Returns values along an exponential trend
126HEX2BINEngineering: Converts a hexadecimal number to binary
127HEX2DECEngineering: Converts a hexadecimal number to decimal
128HEX2OCTEngineering: Converts a hexadecimal number to octal
129HOURDate and time: Converts a serial number to an hour
130HYPGEOM.DISTStatistical: Returns the hypergeometric distribution
131HYPGEOMDISTCompatibility: Returns the hypergeometric distribution
132IMABSEngineering: Returns the absolute value (modulus) of a complex number
133IMAGINARYEngineering: Returns the imaginary coefficient of a complex number
134IMARGUMENTEngineering: Returns the argument theta, an angle expressed in radians
135IMCONJUGATEEngineering: Returns the complex conjugate of a complex number
136IMCOSEngineering: Returns the cosine of a complex number
137IMCOSHEngineering: Returns the hyperbolic cosine of a complex number
138IMCOTEngineering: Returns the cotangent of a complex number
139IMCSCEngineering: Returns the cosecant of a complex number
140IMCSCHEngineering: Returns the hyperbolic cosecant of a complex number
141IMDIVEngineering: Returns the quotient of two complex numbers
142IMEXPEngineering: Returns the exponential of a complex number
143IMLNEngineering: Returns the natural logarithm of a complex number
144IMLOG10Engineering: Returns the base-10 logarithm of a complex number
145IMLOG2Engineering: Returns the base-2 logarithm of a complex number
146IMPOWEREngineering: Returns a complex number raised to an integer power
147IMPRODUCTEngineering: Returns the product of complex numbers
148IMREALEngineering: Returns the real coefficient of a complex number
149IMSECEngineering: Returns the secant of a complex number
150IMSECHEngineering: Returns the hyperbolic secant of a complex number
151IMSINEngineering: Returns the sine of a complex number
152IMSINHEngineering: Returns the hyperbolic sine of a complex number
153IMSQRTEngineering: Returns the square root of a complex number
154IMSUBEngineering: Returns the difference between two complex numbers
155IMSUMEngineering: Returns the sum of complex numbers
156IMTANEngineering: Returns the tangent of a complex number
157INTMath and trigonometry: Rounds a number down to the nearest integer
158INTERCEPTStatistical: Returns the intercept of the linear regression line
159IPMTFinancial: Returns the interest payment for an investment for a given period
160IRRFinancial: Returns the internal rate of return for a series of cash flows
161ISBLANKInformation: Returns TRUE if the value is blank
162ISEVENInformation: Returns TRUE if the number is even
163ISLOGICALInformation: Returns TRUE if the value is a logical value
164ISNONTEXTInformation: Returns TRUE if the value is not text
165ISNUMBERInformation: Returns TRUE if the value is a number
166ISODDInformation: Returns TRUE if the number is odd
167ISODDInformation: Returns TRUE if the number is odd
168ISOWEEKNUMDate and time: Returns the number of the ISO week number of the year for a given date
169ISTEXTInformation: Returns TRUE if the value is text
170LCMMath and trigonometry: Returns the least common multiple
171LEFTText: Returns the leftmost characters from a text value
172LENText: Returns the number of characters in a text string
173LINESTStatistical: Returns the parameters of a linear trend
174LNMath and trigonometry: Returns the natural logarithm of a number
175LOGMath and trigonometry: Returns the logarithm of a number to a specified base
176LOG10Math and trigonometry: Returns the base-10 logarithm of a number
177LOGESTStatistical: Returns the parameters of an exponential trend
178LOGNORM.DISTStatistical: Returns the cumulative lognormal distribution
179LOGNORM.INVStatistical: Returns the inverse of the lognormal cumulative distribution
180LOGNORMDISTCompatibility: Returns the cumulative lognormal distribution
181LOWERText: Converts text to lowercase
182MAXStatistical: Returns the maximum value in a list of arguments
183MAXAStatistical: Returns the maximum value in a list of arguments, including numbers, text, and logical values
184MEDIANStatistical: Returns the median of the given numbers
185MINStatistical: Returns the minimum value in a list of arguments
186MINAStatistical: Returns the smallest value in a list of arguments, including numbers, text, and logical values
187MINUTEDate and time: Converts a serial number to a minute
188MIRRFinancial: Returns the internal rate of return where positive and negative cash flows are financed at different rates
189MODMath and trigonometry: Returns the remainder from division
190MODE.MULTStatistical: Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
191MODE.SNGLStatistical: Returns the most common value in a data set
192MONTHDate and time: Converts a serial number to a month
193MROUNDMath and trigonometry: Returns a number rounded to the desired multiple
194MULTINOMIALMath and trigonometry: Returns the multinomial of a set of numbers
195NEGBINOM.DISTStatistical: Returns the negative binomial distribution
196NEGBINOMDISTCompatibility: Returns the negative binomial distribution
197NETWORKDAYSDate and time: Returns the number of whole workdays between two dates
198NOMINALFinancial: Returns the annual nominal interest rate
199NORM.DISTStatistical: Returns the normal cumulative distribution
200NORM.INVCompatibility: Returns the inverse of the normal cumulative distribution
201NORM.S.DISTStatistical: Returns the standard normal cumulative distribution
202NORM.S.INVStatistical: Returns the inverse of the standard normal cumulative distribution
203NORMDISTCompatibility: Returns the normal cumulative distribution
204NORMINVStatistical: Returns the inverse of the normal cumulative distribution
205NORMSDISTCompatibility: Returns the standard normal cumulative distribution
206NORMSINVCompatibility: Returns the inverse of the standard normal cumulative distribution
207NOTLogical: Reverses the logic of its argument
208NOWDate and time: Returns the serial number of the current date and time
209NPERFinancial: Returns the number of periods for an investment
210NPVFinancial: Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
211OCT2DECEngineering: Converts an octal number to decimal
212OCT2HEXEngineering: Converts an octal number to hexadecimal
213ODDMath and trigonometry: Rounds a number up to the nearest odd integer
214ORLogical: Returns TRUE if any argument is TRUE
215PEARSONStatistical: Returns the Pearson product moment correlation coefficient
216PERMUTStatistical: Returns the number of permutations for a given number of objects
217PERMUTATIONAStatistical: Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
218PHIStatistical: Returns the value of the density function for a standard normal distribution
219PIMath and trigonometry: Returns the value of pi
220PMTFinancial: Returns the periodic payment for an annuity
221POISSON.DISTStatistical: Returns the Poisson distribution
222POWERMath and trigonometry: Returns the result of a number raised to a power
223PPMTFinancial: Returns the payment on the principal for an investment for a given period
224PROBStatistical: Returns the probability that values in a range are between two limits
225PRODUCTMath and trigonometry: Multiplies its arguments
226PROPERText: Capitalizes the first letter in each word of a text value
227PVFinancial: Returns the present value of an investment
228QUARTILE.EXCStatistical: Returns the quartile of the data set, based on percentile values from 0..1, exclusive
229QUARTILE.INCStatistical: Returns the quartile of a data set
230QUOTIENTMath and trigonometry: Returns the integer portion of a division
231RANDMath and trigonometry: Returns a random number between 0 and 1
232RANDBETWEENMath and trigonometry: Returns a random number between the numbers you specify
233RANK.AVGStatistical: Returns the rank of a number in a list of numbers
234RANK.EQStatistical: Returns the rank of a number in a list of numbers
235RATEFinancial: Returns the interest rate per period of an annuity
236REPTText: Repeats text a given number of times
237RIGHTText: Returns the rightmost characters from a text value
238ROMANMath and trigonometry: Converts an arabic numeral to roman, as text
239ROUNDMath and trigonometry: Rounds a number to a specified number of digits
240ROUNDDOWNMath and trigonometry: Rounds a number down, toward zero
241ROUNDUPMath and trigonometry: Rounds a number up, away from zero
242ROWLookup and reference: Returns the row number of a reference
243ROWSLookup and reference: Returns the number of rows in a reference
244RRIFinancial: Returns an equivalent interest rate for the growth of an investment
245RSQStatistical: Returns the square of the Pearson product moment correlation coefficient
246SECMath and trigonometry: Returns the secant of an angle
247SECHMath and trigonometry: Returns the hyperbolic secant of an angle
248SECONDDate and time: Converts a serial number to a second
249SERIESSUMMath and trigonometry: Returns the sum of a power series based on the formula
250SIGNMath and trigonometry: Returns the sign of a number
251SINMath and trigonometry: Returns the sine of the given angle
252SINHMath and trigonometry: Returns the hyperbolic sine of a number
253SKEWStatistical: Returns the skewness of a distribution
254SKEW.PStatistical: Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean
255SLNFinancial: Returns the straight-line depreciation of an asset for one period
256SLOPEStatistical: Returns the slope of the linear regression line
257SMALLStatistical: Returns the k-th smallest value in a data set
258SQRTMath and trigonometry: Returns a positive square root
259SQRTPIMath and trigonometry: Returns the square root of (number * pi)
260STANDARDIZEStatistical: Returns a normalized value
261STDEV.PStatistical: Calculates standard deviation based on the entire population
262STDEV.SStatistical: Estimates standard deviation based on a sample
263STDEVAStatistical: Estimates standard deviation based on a sample, including numbers, text, and logical values
264STDEVPCompatibility: Calculates standard deviation based on the entire population
265STDEVPAStatistical: Calculates standard deviation based on the entire population, including numbers, text, and logical values
266STEYXStatistical: Returns the standard error of the predicted y-value for each x in the regression
267SUBSTITUTEText: Substitutes new text for old text in a text string
268SUBTOTALMath and trigonometry: Returns a subtotal in a list or database
269SUMMath and trigonometry: Adds its arguments
270SUMIFMath and trigonometry: Adds the cells specified by a given criteria, only two params SUMIF(RANGE, CRITERIA)
271SUMIFSMath and trigonometry: Adds the cells in a range that meet multiple criteria, only two params SUMIFS(RANGE, CRITERIA)
272SUMPRODUCTMath and trigonometry: Returns the sum of the products of corresponding array components
273SUMSQMath and trigonometry: Returns the sum of the squares of the arguments
274SUMX2MY2Math and trigonometry: Returns the sum of the difference of squares of corresponding values in two arrays
275SUMX2PY2Math and trigonometry: Returns the sum of the sum of squares of corresponding values in two arrays
276SUMXMY2Math and trigonometry: Returns the sum of squares of differences of corresponding values in two arrays
277SWITCHLogical: Evaluates 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.
278SYDFinancial: Returns the sum-of-years' digits depreciation of an asset for a specified period
279T.DISTStatistical: Returns the Percentage Points (probability) for the Student t-distribution
280T.DIST.2TStatistical: Returns the Percentage Points (probability) for the Student t-distribution
281T.DIST.RTStatistical: Returns the Student's t-distribution
282T.INVStatistical: Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom
283T.INV.2TStatistical: Returns the inverse of the Student's t-distribution
284TANMath and trigonometry: Returns the tangent of a number
285TANHMath and trigonometry: Returns the hyperbolic tangent of a number
286TBILLEQFinancial: Returns the bond-equivalent yield for a Treasury bill
287TBILLPRICEFinancial: Returns the price per $100 face value for a Treasury bill
288TBILLYIELDFinancial: Returns the yield for a Treasury bill
289TDISTCompatibility: Returns the Student's t-distribution
290TIMEDate and time: 
291TIMEVALUEDate and time: 将文本形式的时间转换为序列号
292TINV

Compatibility: 返回Student-t分布的倒数

293TODAYDate and time: 返回今天日期的序列号
294TRANSPOSELookup and reference:返回数组的转置
295TRENDStatistical:沿线性趋势返回值
296TRIMText: 从文本中删除空格
297TRIMMEANStatistical: Returns the mean of the interior of a data set
298TRUELogical: 返回逻辑值TRUE
299TRUNCMath and trigonometry:将数字截断为整数
300UNICODEText:返回与文本的第一个字符对应的数字（代码点）
301UNIQUELookup and reference: 返回列表或范围中唯一值的列表
302UPPERText: 将文本转换为大写
303VAR.PStatistical:基于整个总体计算方差
304VAR.SStatistical: 基于样本估计方差
305VARAStatistical:基于样本估计方差，包括数字、文本和逻辑值
306VARPCompatibility: 基于整个总体计算方差
307VARPAStatistical: 基于整个总体（包括数字、文本和逻辑值）计算方差
308WEEKDAYDate and time: 将序列号转换为一周中的某一天
309WEEKNUMDate and time: 将序列号转换为一个数字，该数字表示一周与一年的数字一致
310WEIBULL.DISTStatistical:返回威布尔分布
311WORKDAYDate and time: 返回指定工作日数之前或之后的日期序列号
312XNPVFinancial: 返回不一定是周期性的现金流明细表的净现值
313XORLogical:返回所有参数的逻辑异或
314YEARFRACDate and time: 返回表示开始日期和结束日期之间的天数的年份分数
  • No labels

experts@hktx.cn
武汉汇科天下科技有限公司
www.hktx.cn