常见公式示例

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

可以在列表或库的计算栏中使用下面的示例。不包含栏引用的示例可用于指定栏的默认值。

本文内容

条件公式

日期和时间的公式

数学公式

文本公式

条件公式

可以使用下面的公式测试语句的条件并返回值“Yes”或“No”,或者测试“OK”或“Not OK”等替代值,或者返回代表空值的空白或短划线。

检查某数字是否大于或小于另一个数字

使用 IF 函数进行此比较。

Column1

Column2

公式

说明(可能的结果)

15000

9000

=[Column1]>[Column2]

Column1 是否大于 Column2?(Yes)

15000

9000

=IF([Column1]<=[Column2], "OK", "Not OK")

Column1 小于或等于 Column2 吗?(Not OK)

在比较列内容后返回逻辑值

对于逻辑值(Yes 或 No)结果,可使用 AND、OR 和 NOT 函数。

Column1

Column2

Column3

公式

说明(可能的结果)

15

9

8

=AND([Column1]>[Column2], [Column1]<[Column3])

15 是否大于 9 并小于 8?(No)

15

9

8

=OR([Column1]>[Column2], [Column1]<[Column3])

15 是否大于 9 或小于 8?(Yes)

15

9

8

=NOT([Column1]+[Column2]=24)

15 加 9 是否不等于 24?(No)

对于其他计算结果,或 Yes 或 No 之外的其他任何值,可使用 IF、AND 和 OR 函数。

Column1

Column2

Column3

公式

说明(可能的结果)

15

9

8

=IF([Column1]=15, "OK", "Not OK")

如果 Column1 中的值等于 15,则返回“OK”。(OK)

15

9

8

=IF(AND([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK")

如果 15 大于 9 并小于 8,则返回“Not OK”。(Not OK)

15

9

8

=IF(OR([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK")

如果 15 大于 9 或小于 8,则返回“OK”。(OK)

将零显示为空白或短划线

要显示零,请执行一次简单计算。要显示空白或短划线,请使用 IF 函数。

C olumn1

Column2

公式

说明(可能的结果)

10

10

=[Column1]-[Column2]

从第一个数字中减去第二个数字 (0)

15

9

=IF([Column1]-[Column2],"-",[Column1]-[Column2])

值为零时返回一条短划线 (-)

隐藏列中的错误值

若要在错误值的位置显示短划线、#N/A 或 NA,可使用 ISERROR 函数。

Column1

Column2

公式

说明(可能的结果)

10

0

=[Column1]/[Column2]

导致错误 (#DIV/0)

10

0

=IF(ISERROR([Column1]/[Column2]),"NA",[Column1]/[Column2])

当值是错误值时,返回 NA

10

0

=IF(ISERROR([Column1]/[Column2]),"-",[Column1]/[Column2])

值为错误时返回一条短划线

返回页首

日期和时间公式

可以使用下面的公式执行基于日期和时间的计算,例如向某个日期添加若干天、若干月或若干年,计算两个日期之间的差值,以及将时间转换为小数值。

增加日期

要向某个日期添加若干天,请使用加法运算符 (+)。

注意: 对日期进行操作时,计算栏的返回类型必须设置为“日期和时间”

Column1

Column2

公式

说明(结果)

2007-6-9

3

=[Column1]+[Column2]

向 6/9/2007 添加 3 天 (6/12/2007)

2008-12-10

54

=[Column1]+[Column2]

向 12/10/2008 添加 54 天(2/2/2009)

若要增加日期的月数,可使用 DATE、YEAR、MONTH 和 DAY 函数。

Column1

Column2

公式

说明(结果)

2007-6-9

3

=DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1]))

向 6/9/2007 添加 3 个月 (9/9/2007)

2008-12-10

25

=DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1]))

向 12/10/2008 添加 25 个月 (1/10/2011)

若要增加日期的年数,可使用 DATE、YEAR、MONTH 和 DAY 函数。

Column1

Column2

公式

说明(结果)

2007-6-9

3

=DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1]))

向 6/9/2007 添加 3 年 (6/9/2010)

2008-12-10

25

=DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1]))

向 12/10/2008 添加 25 年 (12/10/2033)

若要同时增加日期的天数、月数和年数,可使用 DATE、YEAR、MONTH 和 DAY 函数。

Column1

公式

说明(结果)

6/9/2007

=DATE(YEAR([Column1])+3,MONTH([Column1])+1,DAY([Column1])+5)

向 6/9/2007 添加 3 年 1 个月零 5 天 (7/14/2010)

12/10/2008

=DATE(YEAR([Column1])+1,MONTH([Column1])+7,DAY([Column1])+5)

向 12/10/2008 添加 1 年 7 个月零 5 天 (7/15/2010)

计算两个日期之间的差值

请使用 DATEDIF 函数执行此计算。

Column1

Column2

公式

说明(结果)

1995-1-1

1999-6-15

=DATEDIF([Column1], [Column2],"d")

返回两个日期之间的天数 (1626)

1995-1-1

1999-6-15

=DATEDIF([Column1], [Column2],"ym")

返回两个日期之间的月数,忽略年 (5)

1995-1-1

1999-6-15

=DATEDIF([Column1], [Column2],"yd")

返回两个日期之间的天数,忽略年 (165)

计算两个时间之间的差值

要使用标准时间格式(时:分:秒)显示结果,请使用减法运算符 (-) 和 TEXT 函数。要使此方法正常工作,小时不得超过 24,且分和秒不得超过 60。

Column1

Column2

公式

说明(结果)

2007-6-9 10:35 AM

2007-6-9 3:30 PM

=TEXT([Column2]-[Column1],"h")

两个时间之间的小时数 (4)

2007-6-9 10:35 AM

2007-6-9 3:30 PM

=TEXT([Column2]-[Column1],"h:mm")

两个时间之间的小时数和分钟数 (4:55)

2007-6-9 10:35 AM

2007-6-9 3:30 PM

=TEXT([Column2]-[Column1],"h:mm:ss")

两个时间之间的小时数、分钟数和秒数 (4:55:00)

要用基于一个时间单位的总计值显示结果,请使用 INT 函数或 HOUR、MINUTE 或 SECOND 函数。

Column1

Column2

公式

说明(结果)

2007-6-9 10:35 AM

2007-6-10 3:30 PM

=INT(([Column2]-[Column1])*24)

两个时间之间的总小时数 (28)

2007-6-9 10:35 AM

2007-6-10 3:30 PM

=INT(([Column2]-[Column1])*1440)

两个时间之间的总分钟数 (1735)

2007-6-9 10:35 AM

2007-6-10 3:30 PM

=INT(([Column2]-[Column1])*86400)

两个时间之间的总秒数 (104100)

2007-6-9 10:35 AM

2007-6-10 3:30 PM

=HOUR([Column2]-[Column1])

当差值不超过 24 时,两个时间之间的小时数 (4)

2007-6-9 10:35 AM

2007-6-10 3:30 PM

=MINUTE([Column2]-[Column1])

当差值不超过 60 时,两个时间之间的分钟数 (55)

2007-6-9 10:35 AM

2007-6-10 3:30 PM

=SECOND([Column2]-[Column1])

当差值不超过 60 时,两个时间之间的秒数 (0)

转换时间

要将小时从标准时间格式转换为小数,请使用 INT 函数。

Column1

公式

说明(结果)

10:35 AM

=([Column1]-INT([Column1]))*24

12:00 AM 以后的小时数 (10.583333)

12:15 PM

=([Column1]-INT([Column1]))*24

自 12:00 AM 以后的小时数 (12.25)

要将小时从小数转换为标准时间格式(时:分:秒),请使用除法运算符和 TEXT 函数。

Column1

公式

说明(结果)

23:58

=TEXT(Column1/24, "hh:mm:ss")

自 12:00 AM 以后的时、分和秒 (00:59:55)

2:06

=TEXT(Column1/24, "h:mm")

自 12:00 AM 以后的时和分 (0:05)

插入儒略历日期

儒略历日期指的是一种日期格式,是当前年份和从当年年初算起的天数的组合。例如,2007 年 1 月 1 日表示为 2007001,而 2007 年 12 月 31 日则表示为 2007365。此格式并不基于儒略历。

若要将日期转换为 Julian 日期,可使用 TEXT 和 DATEVALUE 函数。

Column1

公式

说明(结果)

6/23/2007

=TEXT([Column1],"yy")&TEXT(([Column1]-DATEVALUE("1/1/"& TEXT([Column1],"yy"))+1),"000")

采用儒略历格式的日期,年份用两位数字表示 (07174)

6/23/2007

=TEXT([Column1],"yyyy")&TEXT(([Column1]-DATEVALUE("1/1/"&TEXT([Column1],"yy"))+1),"000")

采用儒略历格式的日期,年份用四位数字表示 (2007174)

要将日期转换为在天文学中使用的儒略历日期,请使用常量 2415018.50。如果使用 1900 日期系统,则此公式只对 1901 年 3 月 1 日之后的日期起作用。

Column1

公式

说明(结果)

6/23/2007

=[Column1]+2415018.50

在天文学中使用的儒略历格式的日期 (2454274.50)

将日期显示为一周中的某一天

若要将日期转换为星期名称文本,可使用 TEXT 和 WEEKDAY 函数。

Column1

公式

说明(可能的结果)

19-Feb-2007

=TEXT(WEEKDAY([Column1]), "dddd")

计算该日期的星期名称,并返回该日的全称 (Monday)

2008-1-3

=TEXT(WEEKDAY([Column1]), "ddd")

计算该日期在一周中所对应的天数,并返回这一天的缩写名称 (Thu)

返回页首

数学公式

可以使用下面的公式执行各种数学计算,如数字的加、减、乘、除,计算一组数字的平均值或中值,对数字进行四舍五入以及对数值进行计数。

将数字相加

要将一行中两栏或更多个栏中的数字相加,请使用加法运算符 (+) 或 SUM 函数。

Column1

Column2

Column3

公式

说明(结果)

6

5

4

=[Column1]+[Column2]+[Column3]

将前三个栏中的值相加 (15)

6

5

4

=SUM([Column1],[Column2],[Column3])

将前三个栏中的值相加 (15)

6

5

4

=SUM(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3])

如果 Column1 大于 Column2,则将它们的差值与 Column3 相加。否则将 10 与 Column3 相加 (5)

将数字相减

要将一行中两栏或更多个栏中的数字相减,请使用减法运算符 (-) 或带负数的 SUM 函数。

Column1

Column2

Column3

公式

说明(结果)

15000

9000

-8000

=[Column1]-[Column2]

从 15000 中减去 9000 (6000)

15000

9000

-8000

=SUM([Column1], [Column2], [Column3])

将前三个栏中的数字相加,包括负值 (16000)

计算两个数字相差的百分比

请使用减法运算符 (-)、除法运算符 (/) 和 ABS 函数。

Column1

Column2

公式

说明(结果)

23420 23420

2500

=([Column2]-[Column1])/ABS([Column1])

百分比变动(6.75% 或 0.06746)

将数字相乘

要将一行中两栏或更多个栏中的数字相乘,请使用乘法运算符 (*) 或 PRODUCT 函数。

Column1

Column2

公式

说明(结果)

5

2

=[Column1]*[Column2]

将前两列中的数字相乘 (10)

5

2

=PRODUCT([Column1], [Column2])

将前两列中的数字相乘 (10)

5

2

=PRODUCT([Column1],[Column2],2)

将前两个栏中的数字及数字 2 相乘 (20)

将数字相除

要将一行中两栏或更多个栏中的数字相除,请使用除法运算符 (/)。

Column1

Column2

公式

说明 (结果)

15000

12

=[Column1]/[Column2]

15000 除以 12 (1250)

15000

12

=([Column1]+10000)/[Column2]

将 15000 与 10000 相加,然后用和除以 12 (2083)

计算数字的平均值

平均值也称为平均数。若要计算一行中两列或多个列中数字的平均值,可使用 AVERAGE 函数。

Column1

Column2

Column3

公式

说明(结果)

6

5

4

=AVERAGE([Column1], [Column2],[Column3])

前三列中数字的平均值 (5)

6

5

4

=AVERAGE(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3])

如果 Column1 大于 Column2,则计算其差值与 Column3 的平均值。否则计算数值 10 与 Column3 的平均值 (2.5)

计算数字的中值

中值是一系列有序数字的中间值。请使用 MEDIAN 函数计算一组数字的中值。

"a"

B

C

D

E

F

公式

说明(结果)

10

7

9

27

0

4

=MEDIAN(A, B, C, D, E, F)

前 6 列中数字的中值 (8)

计算某个范围内的最小或最大数

若要计算一行中两列或多个列中的最小数字或最大数字,可使用 MIN 和 MAX 函数

Column1

Column2

Column3

公式

说明(结果)

10

7

9

=MIN([Column1], [Column2], [Column3])

最小数字 (7)

10

7

9

=MAX([Column1], [Column2], [Column3])

最大数 (10)

对值进行计数

若要计算数字值的个数,可使用 COUNT 函数。

Column1

Column2

Column3

公式

说明(结果)

苹果

2007-12-12

=COUNT([Column1], [Column2], [Column3])

计算包含数值的栏数。排除日期和时间、文本以及空值 (0)

¥12

#DIV/0!

1.01

=COUNT([Column1], [Column2], [Column3])

计算包含数值的栏数,但排除错误和逻辑值 (2)

按百分比增加或减少某个数字

请使用百分数 (%) 运算符执行此计算。

Column1

Column2

公式

说明 (结果)

23

3%

=[Column1]*(1+5%)

将 Column1 中的数字增加 5% (24.15)

23

3%

=[Column1]*(1+[Column2])

将 Column1 中的数字增加 Column2 中的百分数值:3% (23.69)

23

3%

=[Column1]*(1-[Column2])

将 Column1 中的数字减少 Column2 中的百分数值:3% (22.31)

求数字的幂

使用乘幂运算符 (^) 或 POWER 函数执行此计算。

Column1

Column2

公式

说明(结果)

5

2

=[Column1]^[Column2]

计算 5 的平方 (25)

5

3

=POWER([Column1], [Column2])

计算 5 的立方 (125)

对数字进行舍入

要对数字向上舍入,请使用 ROUNDUP、ODD 或 EVEN 函数。

Column1

公式

说明(结果)

20.3

=ROUNDUP([Column1],0)

将 20.3 向上舍入到最接近的整数 (21)

-5.9

=ROUNDUP([Column1],0)

将 -5.9 向上舍入为最接近的整数 (-5)

12.5493

=ROUNDUP([Column1],2)

将 12.5493 向上舍入到最接近的百分位,两位小数 (12.55)

20.3

=EVEN([Column1])

将 20.3 向上舍入到最接近的偶数 (22)

20.3

=ODD([Column1])

将 20.3 向上舍入到最接近的奇数 (21)

若要对数字进行向下舍入运算,可使用 ROUNDDOWN 函数。

Column1

公式

说明(结果)

20.3

=ROUNDDOWN([Column1],0)

将 20.3 向下舍入到最接近的整数 (20)

-5.9

=ROUNDDOWN([Column1],0)

将 -5.9 向下舍入为最接近的整数 (-6)

12.5493

=ROUNDDOWN([Column1],2)

将 12.5493 向下舍入到最接近的百分位,两位小数 (12.54)

若要将数字舍入到最接近的数字或小数,可使用 ROUND 函数。

Column1

公式

说明(结果)

20.3

=ROUND([Column1],0)

将 20.3 向下舍入,因为小数部分小于 .5 (20)

5.9

=ROUND([Column1],0)

将 5.9 向上舍入,因为小数部分大于 .5 (6)

-5.9

=ROUND([Column1],0)

将 -5.9 向下舍入,因为小数部分小于 -.5 (-6)

1.25

=ROUND([Column1], 1)

将数字舍入到最接近的十分位(一个小数位)。因为要舍入的部分是 0.05 或更大,所以数字向上舍入(结果是 1.3)

30.452

=ROUND([Column1], 2)

将数字舍入到最接近的百分位(两个小数位)。因为要舍入的小数部分为 0.002,它小于 0.005,所以数字向下舍入(结果是 30.45)

若要将某个数字舍入到 0 以上的有效位,可使用 ROUND、ROUNDUP、ROUNDDOWN、INT 和 LEN 函数。

Column1

公式

说明(结果)

5492820

=ROUND([Column1],3-LEN(INT([Column1])))

将数字舍入到 3 个有效位 (5490000)

22230

=ROUNDDOWN([Column1],3-LEN(INT([Column1])))

将底部的数字向下舍入到 3 个有效位 (22200)

5492820

=ROUNDUP([Column1], 5-LEN(INT([Column1])))

将数字向上舍入到 5 个有效位数 (5492900)

返回页首

文本公式

可以使用下面的公式处理文本,例如组合或连接多个栏中的值,比较一些栏中的内容,删除字符或空格以及重复字符。

更改文本大小写

要更改文本的大小写,请使用 UPPER、LOWER 或 PROPER 函数。

Column1

公式

说明(结果)

nina Vietzen

=UPPER([Column1])

将文本更改为大写形式 (NINA VIETZEN)

nina Vietzen

=LOWER([Column1])

将文本更改为小写形式 (nina vietzen)

nina Vietzen

=PROPER([Column1])

将文本更改为词首大写形式 (Nina Vietzen)

合并姓和名

要将名字和姓氏进行组合,请使用“与”运算符 (&) 或 CONCATENATE 函数。

Column1

Column2

公式

说明(结果)

Carlos

Carvallo

=[Column1]&[Column2]

对两个字符串进行组合 (CarlosCarvallo)

Carlos

Carvallo

=[Column1]&" "&[Column2]

对两个字符串进行组合,并用空格分隔 (Carlos Carvallo)

Carlos

Carvallo

=[Column2]&", "&[Column1]

对两个字符串进行组合,并用逗号和空格分隔 (Carvallo, Carlos)

Carlos

Carvallo

=CONCATENATE([Column2], ",", [Column1])

对两个字符串进行组合,并用逗号分隔 (Carvallo,Carlos)

合并不同列中的文本和数字

要将文本和数字进行组合,请使用 CONCATENATE 函数、“与”运算符 (&) 或 TEXT 函数和“与”运算符。

Column1

Column2

公式

说明(结果)

Yang

28

=[Column1]&" sold "&[Column2]&" units."

将上面的内容组合成一个短语 (Yang sold 28 units.)

Dubois

40%

=[Column1]&" sold "&TEXT([Column2],"0%")&" of the total sales."

将上面的内容组合成一个短语 (Dubois sold 40% of the total sales.)

注意: TEXT 函数追加 Column2 的格式化值而不是基础值 .4。

Yang

28

=CONCATENATE([Column1]," sold ",[Column2]," units.")

将上面的内容组合成一个短语 (Yang sold 28 units.)

合并带日期或时间的文本

要将文本与日期或时间进行组合,请使用 TEXT 函数和“与”运算符 (&)。

Column1

Column2

公式

说明(结果)

Billing Date

2007-6-5

="Statement date: "&TEXT([Column2], "d-mmm-yyyy")

将文本与日期进行组合 (Statement date: 5-Jun-2007)

Billing Date

2007-6-5

=[Column1]&" "&TEXT([Column2], "mmm-dd-yyyy")

将不同栏中的文本与日期组合为一栏 (Billing Date Jun-05-2007)

比较列的内容

若要将某一列与另一列或一个值列表相比较,可使用 EXACT 和 OR 函数。

Column1

Column2

公式

说明(可能的结果)

BD122

BD123

=EXACT([Column1],[Column2])

对前两栏中的内容进行比较 (No)

BD122

BD123

=EXACT([Column1], "BD122")

将 Column1 中的内容与字符串“BD122”进行比较 (Yes)

检查列值或其一部分是否与特定文本相同

要检查栏值或栏值的一部分是否与特定文本匹配,请使用 IF、FIND、SEARCH 和 ISNUMBER 函数。

Column1

公式

说明(可能的结果)

Vietzen

=IF([Column1]="Vietzen", "OK", "Not OK")

检查确定 Column1 是否为 Vietzen (OK)

Vietzen

=IF(ISNUMBER(FIND("v",[Column1])), "OK", "Not OK")

检查 Column1 是否包含字母 v (OK)

BD123

=ISNUMBER(FIND("BD",[Column1]))

检查确定 Column1 中是否包含 BD (Yes)

对非空列进行计数

要计算非空栏的数目,请使用 COUNTA 函数。

Column1

Column2

Column3

公式

说明(结果)

Sales

19

=COUNTA([Column1], [Column2])

计算非空列的数量 (2)

Sales

19

=COUNTA([Column1], [Column2], [Column3])

计算非空列的数量 (2)

删除文本中的字符

要删除文本中的字符,请使用 LEN、LEFT 和 RIGHT 函数。

Column1

公式

说明(结果)

Vitamin A

=LEFT([Column1],LEN([Column1])-2)

从左边开始返回 7 (9-2) 个字符 (Vitamin)

Vitamin B1

=RIGHT([Column1], LEN([Column1])-8)

从右边开始返回 2 (10-8) 个字符 (B1)

删除列开头和结尾处的空格

要删除栏中的空格,请使用 TRIM 函数。

Column1

公式

说明(结果)

 Hello there!

=TRIM([Column1])

删除开头和结尾处的空格 (Hello there!)

在列中重复字符

要重复栏中的字符,请使用 REPT 函数。

公式

说明(结果)

=REPT(".",3)

重复句点 3 次 (...)

=REPT("-",10)

重复短划线 10 次 (----------)

返回页首

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×