SharePoint 列表中常用公式的示例

Column1

Column2

15000

9000

=[Column1]>[Column2]

Column1 是否大于 Column2？ (Yes)

15000

9000

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

Column1 是否小于或等于 Column2？ (Not OK)

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)

Column1

Column2

Column3

15

9

8

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

15

9

8

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

15

9

8

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

Column1

Column2

10

10

=[Column1]-[Column2]

15

9

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

Column1

Column2

10

0

=[Column1]/[Column2]

10

0

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

10

0

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

 Column1 公式 说明（可能的结果） “果冻豆” =ISBLANK ([Column1] 如果为空，则返回“是”或“否” “Steel” =IF (ISBLANK ([Column1]) 、“不确定”、“确定”) 填写你自己的选择 - 首先是如果为空，第二，如果不是

Column1

Column2

6/9/2007

3

=[Column1]+[Column2]

12/10/2008

54

=[Column1]+[Column2]

Column1

Column2

6/9/2007

3

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

12/10/2008

25

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

Column1

Column2

6/9/2007

3

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

12/10/2008

25

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

Column1

6/9/2007

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

12/10/2008

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

Column1

Column2

01-Jan-1995

15-Jun-1999

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

01-Jan-1995

15-Jun-1999

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

01-Jan-1995

15-Jun-1999

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

Column1

Column2

06/09/2007 10:35 AM

06/09/2007 3:30 PM

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

06/09/2007 10:35 AM

06/09/2007 3:30 PM

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

06/09/2007 10:35 AM

06/09/2007 3:30 PM

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

Column1

Column2

06/09/2007 10:35 AM

06/10/2007 3:30 PM

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

06/09/2007 10:35 AM

06/10/2007 3:30 PM

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

06/09/2007 10:35 AM

06/10/2007 3:30 PM

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

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=HOUR([Column2]-[Column1])

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=MINUTE([Column2]-[Column1])

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=SECOND([Column2]-[Column1])

Column1

10:35 AM

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

12:15 PM

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

Column1

23:58

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

2:06

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

Column1

6/23/2007

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

6/23/2007

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

Column1

6/23/2007

=[Column1]+2415018.50

Column1

19-Feb-2007

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

3-Jan-2008

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

Column1

Column2

Column3

6

5

4

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

6

5

4

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

6

5

4

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

Column1

Column2

Column3

15000

9000

-8000

=[Column1]-[Column2]

15000

9000

-8000

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

Column1

Column2

2342

2500

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

Column1

Column2

5

2

=[Column1]*[Column2]

5

2

=PRODUCT([Column1], [Column2])

5

2

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

Column1

Column2

15000

1.2

=[Column1]/[Column2]

15000 除以 12 (1250)

15000

1.2

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

Column1

Column2

Column3

6

5

4

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

6

5

4

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

A

B

C

D

E

F

10

7

9

27

0

4

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

Column1

Column2

Column3

10

7

9

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

10

7

9

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

Column1

Column2

Column3

Apple

12/12/2007

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

\$12

#DIV/0!

1.01

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

Column1

Column2

23

3%

=[Column1]*(1+5%)

23

3%

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

23

3%

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

Column1

Column2

5

2

=[Column1]^[Column2]

5

3

=POWER([Column1], [Column2])

Column1

20.3

=ROUNDUP([Column1],0)

-5.9

=ROUNDUP([Column1],0)

12.5493

=ROUNDUP([Column1],2)

20.3

=EVEN([Column1])

20.3

=ODD([Column1])

Column1

20.3

=ROUNDDOWN([Column1],0)

-5.9

=ROUNDDOWN([Column1],0)

12.5493

=ROUNDDOWN([Column1],2)

Column1

20.3

=ROUND([Column1],0)

5.9

=ROUND([Column1],0)

-5.9

=ROUND([Column1],0)

1.25

=ROUND([Column1], 1)

30.452

=ROUND([Column1], 2)

Column1

5492820

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

22230

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

5492820

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

Column1

nina Vietzen

=UPPER([Column1])

nina Vietzen

=LOWER([Column1])

nina Vietzen

=PROPER([Column1])

Column1

Column2

Carlos

Carvallo

=[Column1]&[Column2]

Carlos

Carvallo

=[Column1]&" "&[Column2]

Carlos

Carvallo

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

Carlos

Carvallo

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

Column1

Column2

Yang

28

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

Dubois

40%

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

Yang

28

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

Column1

Column2

Billing Date

5-Jun-2007

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

Billing Date

5-Jun-2007

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

Column1

Column2

BD122

BD123

=EXACT([Column1],[Column2])

BD122

BD123

=EXACT([Column1], "BD122")

Column1

Vietzen

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

Vietzen

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

BD123

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

Column1

Column2

Column3

Sales

19

=COUNTA([Column1], [Column2])

Sales

19

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

Column1

Vitamin A

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

Vitamin B1

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

Column1

Hello there!

=TRIM([Column1])

=REPT(".",3)

=REPT("-",10)

×