SharePoint 列表中常用公式的示例

# 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

4。6

9

8

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

15 是否大于 9 且小于 8？(No)

4。6

9

8

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

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

4。6

9

8

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

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

Column1

Column2

Column3

4。6

9

8

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

4。6

9

8

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

4。6

9

8

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

Column1

Column2

10

=[Column1]-[Column2]

4。6

9

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

Column1

Column2

1

=[Column1]/[Column2]

1

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

1

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

 Column1 公式 说明（可能的结果） "Jelly bean" = ISBLANK ([Column1] 如果为空则返回 "是" 或 "否" 钢 = IF (ISBLANK ([Column1]), "Not OK", "ok") 填写你自己的选择-第一项是如果是空白, 则第二个是如果不

## 日期和时间公式

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

=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

4gb

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

6

5

4gb

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

6

5

4gb

=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

12

=[Column1]/[Column2]

15000 除以 12 (1250)

15000

12

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

Column1

Column2

Column3

6

5

4gb

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

6

5

4gb

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

A

B

C

D

E

F

7

9

1

4gb

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

Column1

Column2

Column3

7

9

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

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)

×