SharePoint2007计算列支持的公式

翻译 2007年09月20日 08:45:00

这些是从下载版的 WSS 3.0 SDK中节选出来的。不知什么原因,在线的SDK上没有这部分内容:(

Conditional formulas

You can use the following formulas to test the condition of a statement and return a Yes or No value, to test an alternate value such as OK or Not OK, or to return a blank or dash to represent a null value.

Determine whether a number is greater than or less than another number

Use the IF function to perform this comparison.

Column1

Column2

Formula

Description (possible result)

15000

9000

=[Column1]>[Column2]

Is Column1 greater than Column2? (Yes)

15000

9000

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

Is Column1 less than or equal to Column2? (Not OK)

Return a logical value after comparing column contents

For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.

Column1

Column2

Column3

Formula

Description (possible result)

15

9

8

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

Is 15 greater than 9 and less than 8? (No)

15

9

8

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

Is 15 greater than 9 or less than 8? (Yes)

15

9

8

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

Is 15 plus 9 not equal to 24? (No)

For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.

Column1

Column2

Column3

Formula

Description (possible result)

15

9

8

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

If the value in Column1 equals 15, return "OK". (OK)

15

9

8

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

If 15 is greater than 9 and less than 8, return "OK". (Not OK)

15

9

8

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

If 15 is greater than 9 or less than 8, return "OK". (OK)

Display zeroes as blanks or dashes

To display a zero, perform a simple calculation. To display a blank or a dash, use the IF function.

Column1

Column2

Formula

Description (possible result)

10

10

=[Column1]-[Column2]

Second number subtracted from the first. (0)

15

9

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

Returns a dash when the value is zero. (-)

Hide error values in columns

To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.

Column1

Column2

Formula

Description (possible result)

10

0

=[Column1]/[Column2]

Results in an error (#DIV/0)

10

0

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

Returns NA when the value is an error

10

0

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

Returns a dash when the value is an error

Date and time formulas

You can use the following formulas to perform calculations that are based on dates and times, such as adding a number of days, months, or years to a date, calculating the difference between two dates, and converting time to a decimal value.

Add dates

To add a number of days to a date, use the addition (+) operator.

NoteNote:

When you manipulate dates, the return type of the calculated column must be set to Date and Time.

Column1

Column2

Formula

Description (possible result)

6/9/2007

3

=[Column1]+[Column2]

Adds 3 days to 6/9/2007 (6/12/2007)

12/10/2008

54

=[Column1]+[Column2]

Adds 54 days to 12/10/2008 (2/2/2009)

To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1

Column2

Formula

Description (possible result)

6/9/2007

3

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

Adds 3 months to 6/9/2007 (9/9/2007)

12/10/2008

25

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

Adds 25 months to 12/10/2008 (1/10/2011)

To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1

Column2

Formula

Description (possible result)

6/9/2007

3

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

Adds 3 years to 6/9/2007 (6/9/2010)

12/10/2008

25

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

Adds 25 years to 12/10/2008 (12/10/2033)

To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1

Formula

Description (possible result)

6/9/2007

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

Adds 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010)

12/10/2008

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

Adds 1 year, 7 months, and 5 days to 12/10/2008 (7/15/2010)

Calculate the difference between two dates

Use the DATEDIF function to perform this calculation.

Column1

Column2

Formula

Description (possible result)

01-Jan-1995

15-Jun-1999

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

Returns the number of days between the two dates (1626)

01-Jan-1995

15-Jun-1999

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

Returns the number of months between the dates, ignoring the year part (5)

01-Jan-1995

15-Jun-1999

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

Returns the number of days between the dates, ignoring the year part (165)

Calculate the difference between two times

To present the result in the standard time format (hours:minutes:seconds), use the subtraction operator (-) and the TEXT function. For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.

Column1

Column2

Formula

Description (possible result)

06/09/2007 10:35 AM

06/09/2007 3:30 PM

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

Hours between two times (4)

06/09/2007 10:35 AM

06/09/2007 3:30 PM

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

Hours and minutes between two times (4:55)

06/09/2007 10:35 AM

06/09/2007 3:30 PM

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

Hours, minutes, and seconds between two times (4:55:00)

To present the result in a total that is based on one time unit, use the INT function, or HOUR, MINUTE, or SECOND function.

Column1

Column2

Formula

Description (possible result)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

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

Total hours between two times (28)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

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

Total minutes between two times (1735)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

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

Total seconds between two times (104100)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=HOUR([Column2]-[Column1])

Hours between two times, when the difference does not exceed 24 (4)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=MINUTE([Column2]-[Column1])

Minutes between two times, when the difference does not exceed 60 (55)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=SECOND([Column2]-[Column1])

Seconds between two times, when the difference does not exceed 60 (0)

Convert times

To convert hours from the standard time format to a decimal number, use the INT function.

Column1

Formula

Description (possible result)

10:35 AM

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

Number of hours since 12:00 AM (10.583333)

12:15 PM

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

Number of hours since 12:00 AM (12.25)

To convert hours from a decimal number to the standard time format (hours:minutes:seconds), use the division operator and the TEXT function.

Column1

Formula

Description (possible result)

23:58

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

Hours, minutes, and seconds since 12:00 AM (00:59:55)

2:06

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

Hours and minutes since 12:00 AM (0:05)

Insert Julian dates

A Julian date refers to a date format that is a combination of the current year and the number of days since the beginning of the year. For example, January 1, 2007, is represented as 2007001 and December 31, 2007, is represented as 2007365. This format is not based on the Julian calendar.

To convert a date to a Julian date, use the TEXT and DATEVALUE functions.

Column1

Formula

Description (possible result)

6/23/2007

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

Date in Julian format, with a two-digit year (07174)

6/23/2007

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

Date in Julian format, with a four-digit year (2007174)

To convert a date to a Julian date that is used in astronomy, use the constant 2415018.50. This formula works only for dates after 3/1/1901, and if you are using the 1900 date system.

Column1

Formula

Description (possible result)

6/23/2007

=[Column1]+2415018.50

Date in Julian format, used in astronomy (2454274.50)

Show dates as the day of the week

To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions.

Column1

Formula

Description (possible result)

19-Feb-2007

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

Calculates the day of the week for the date and returns the full name of the day (Monday)

3-Jan-2008

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

Calculates the day of the week for the date and returns the abbreviated name of the day (Thu)

Mathematical formulas

You can use the following formulas to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, and dividing numbers; calculating the average or median of numbers; rounding a number; and counting values.

Add numbers

To add numbers in two or more columns in a row, use the addition operator (+) or the SUM function.

Column1

Column2

Column3

Formula

Description (possible result)

6

5

4

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

Adds the values in the first three columns (15)

6

5

4

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

Adds the values in the first three columns (15)

6

5

4

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

If Column1 is greater than Column2, adds the difference and Column3. Else add 10 and Column3 (5)

Subtract numbers

To subtract numbers in two or more columns in a row, use the subtraction operator (-) or the SUM function with negative numbers.

Column1

Column2

Column3

Formula

Description (possible result)

15000

9000

-8000

=[Column1]-[Column2]

Subtracts 9000 from 15000 (6000)

15000

9000

-8000

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

Adds numbers in the first three columns, including negative values (16000)

Calculate the difference between two numbers as a percentage

Use the subtraction (-) and division (/) operators and the ABS function.

Column1

Column2

Formula

Description (possible result)

2342

2500

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

Percentage change (6.75% or 0.06746)

Multiply numbers

To multiply numbers in two or more columns in a row, use the multiplication operator (*) or the PRODUCT function.

Column1

Column2

Formula

Description (possible result)

5

2

=[Column1]*[Column2]

Multiplies the numbers in the first two columns (10)

5

2

=PRODUCT([Column1], [Column2])

Multiplies the numbers in the first two columns (10)

5

2

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

Multiplies the numbers in the first two columns and the number 2 (20)

Divide numbers

To divide numbers in two or more columns in a row, use the division operator (/).

Column1

Column2

Formula

Description (possible result)

15000

12

=[Column1]/[Column2]

Divides 15000 by 12 (1250)

15000

12

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

Adds 15000 and 10000, and then divides the total by 12 (2083)

Calculate the average of numbers

The average is also called the mean. To calculate the average of numbers in two or more columns in a row, use the AVERAGE function.

Column1

Column2

Column3

Formula

Description (possible result)

6

5

4

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

Average of the numbers in the first three columns (5)

6

5

4

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

If Column1 is greater than Column2, calculate the average of the difference and Column3. Else calculate the average of the value 10 and Column3 (2.5)

Calculate the median of numbers

The median is the value at the center of an ordered range of numbers. Use the MEDIAN function to calculate the median of a group of numbers.

A

B

C

D

E

F

Formula

Description (result)

10

7

9

27

0

4

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

Median of numbers in the first 6 columns (8)

Calculate the smallest or largest number in a range

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.

Column1

Column2

Column3

Formula

Description (possible result)

10

7

9

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

Smallest number (7)

10

7

9

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

Largest number (10)

Count values

To count numeric values, use the COUNT function.

Column1

Column2

Column3

Formula

Description (possible result)

Apple

12/12/2007

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

Counts the number of columns that contain numeric values. Excludes date and time, text, and null values (0)

$12

#DIV/0!

1.01

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

Counts the number of columns that contain numeric values, but excludes error and logical values (2)

Increase or decrease a number by a percentage

Use the percent (%) operator to perform this calculation.

Column1

Column2

Formula

Description (possible result)

23

3%

=[Column1]*(1+5%)

Increases number in Column1 by 5% (24.15)

23

3%

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

Increases number in Column1 by the percent value in Column2: 3% (23.69)

23

3%

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

Decreases number in Column1 by the percent value in Column2: 3% (22.31)

Raise a number to a power

Use the exponentiation operator (^) or the POWER function to perform this calculation.

Column1

Column2

Formula

Description (possible result)

5

2

=[Column1]^[Column2]

Calculates five squared (25)

5

3

=POWER([Column1], [Column2])

Calculates five cubed (125)

Round a number

To round up a number, use the ROUNDUP, ODD, or EVEN function.

Column1

Formula

Description (possible result)

20.3

=ROUNDUP([Column1],0)

Rounds 20.3 up to the nearest whole number (21)

-5.9

=ROUNDUP([Column1],0)

Rounds -5.9 up to the nearest whole number (-5)

12.5493

=ROUNDUP([Column1],2)

Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)

20.3

=EVEN([Column1])

Rounds 20.3 up to the nearest even number (22)

20.3

=ODD([Column1])

Rounds 20.3 up to the nearest odd number (21)

To round down a number, use the ROUNDDOWN function.

Column1

Formula

Description (possible result)

20.3

=ROUNDDOWN([Column1],0)

Rounds 20.3 down to the nearest whole number (20)

-5.9

=ROUNDDOWN([Column1],0)

Rounds -5.9 down to the nearest whole number (-6)

12.5493

=ROUNDDOWN([Column1],2)

Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54)

To round a number to the nearest number or fraction, use the ROUND function.

Column1

Formula

Description (possible result)

20.3

=ROUND([Column1],0)

Rounds 20.3 down, because the fractional part is less than .5 (20)

5.9

=ROUND([Column1],0)

Rounds 5.9 up, because the fractional part is greater than .5 (6)

-5.9

=ROUND([Column1],0)

Rounds -5.9 down, because the fractional part is less than -.5 (-6)

1.25

=ROUND([Column1], 1)

Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)

30.452

=ROUND([Column1], 2)

Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.

Column1

Formula

Description (possible result)

5492820

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

Rounds the number to 3 significant digits (5490000)

22230

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

Rounds the bottom number down to 3 significant digits (22200)

5492820

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

Rounds the top number up to 5 significant digits (5492900)

Text formulas

You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.

Change the case of text

To change the case of text, use the UPPER, LOWER, or PROPER function.

Column1

Formula

Description (possible result)

nina Vietzen

=UPPER([Column1])

Changes text to uppercase (NINA VIETZEN)

nina Vietzen

=LOWER([Column1])

Changes text to lowercase (nina vietzen)

nina Vietzen

=PROPER([Column1])

Changes text to title case (Nina Vietzen)

Combine first and last names

To combine first and last names, use the ampersand operator (&) or the CONCATENATE function.

Column1

Column2

Formula

Description (possible result)

Carlos

Carvallo

=[Column1]&[Column2]

Combines the two strings (CarlosCarvallo)

Carlos

Carvallo

=[Column1]&" "&[Column2]

Combines the two strings, separated by a space (Carlos Carvallo)

Carlos

Carvallo

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

Combines the two strings, separated by a comma and a space (Carvallo, Carlos)

Carlos

Carvallo

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

Combines the two strings, separated by a comma (Carvallo,Carlos)

Combine text and numbers from different columns

To combine text and numbers, use the CONCATENATE function, the ampersand operator (&), or the TEXT function and the ampersand operator.

Column1

Column2

Formula

Description (possible result)

Yang

28

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

Combines contents above into a phrase (Yang sold 28 units.)

Dubois

40%

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

Combines contents above into a phrase (Dubois sold 40% of the total sales.)

Note   The TEXT function appends the formatted value of Column2 instead of the underlying value, which is 0.4.

Yang

28

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

Combines contents above into a phrase (Yang sold 28 units.)

Combine text with a date or time

To combine text with a date or time, use the TEXT function and the ampersand operator (&).

Column1

Column2

Formula

Description (possible result)

Billing Date

5-Jun-2007

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

Combines text with a date (Statement date: 5-Jun-2007)

Billing Date

5-Jun-2007

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

Combines text and date from different columns into one column (Billing Date Jun-05-2007)

Compare column contents

To compare one column to another column or a list of values, use the EXACT and OR functions.

Column1

Column2

Formula

Description (possible result)

BD122

BD123

=EXACT([Column1],[Column2])

Compares contents of first two columns (No)

BD122

BD123

=EXACT([Column1], "BD122")

Compares contents of Column1 and the string "BD122" (Yes)

Determine whether a column value or a part of it matches specific text

To determine whether a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBER functions.

Column1

Formula

Description (possible result)

Vietzen

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

Determines whether Column1 is Vietzen (OK)

Vietzen

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

Determines whether Column1 contains the letter v (OK)

BD123

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

Determines whether Column1 contains BD (Yes)

Count nonblank columns

To count nonblank columns, use the COUNTA function.

Column1

Column2

Column3

Formula

Description (possible result)

Sales

19

=COUNTA([Column1], [Column2])

Counts the number of nonblank columns (2)

Sales

19

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

Counts the number of nonblank columns (2)

Remove characters from text

To remove characters from text, use the LEN, LEFT, and RIGHT functions.

Column1

Formula

Description (possible result)

Vitamin A

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

Returns 7 (9-2) characters, starting from left (Vitamin)

Vitamin B1

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

Returns 2 (10-8) characters, starting from right (B1)

Remove spaces from the beginning and end of a column

To remove spaces from a column, use the TRIM function.

Column1

Formula

Description (possible result)

Hello there!

=TRIM([Column1])

Removes the spaces from the beginning and end (Hello there!)

Repeat a character in a column

To repeat a character in a column, use the REPT function.

Formula

Description (possible result)

=REPT(".",3)

Repeats a period 3 times (...)

=REPT("-",10)

Repeats a dash 10 times (----------)

 

MySQL-创建计算字段

在谈计算字段之前,我们先想一想。储存在数据库中的原始表给我们提供了足够的数据,比如products表中就有商品id,商品名,供应商id,单价。但是实际生活中,比如超市收银员,他们在打单据的时候可不是直...
  • PythonKidDz
  • PythonKidDz
  • 2017年02月13日 22:39
  • 1536

实现SQL动态列计算的示例

被数据库厂商扩展后的SQL可以动态拼接出语句执行,但SQL的集合运算和有序计算不方便,实现动态列计算很繁琐,往往要借助高级语言拼出动态SQL。   免费的集算器支持动态脚本、有序计算、显式集合,...
  • u012388497
  • u012388497
  • 2015年11月13日 09:13
  • 2134

计算字段公式(Microsoft.SharePoint.SPFieldCalculated 类)

计算字段公式 下表提供有关您可以通过使用 Microsoft.SharePoint.SPFieldCalculated 类的 Formula 在计算字段中实现的多种公式的信息。 条...
  • lance_lot1
  • lance_lot1
  • 2012年09月03日 13:57
  • 1476

在Windows2008R2上安装SharePoint2007

 安装好Windows2008R2之后,开始安装SharePoint2007,结果提示兼容性问题,让我查看KB962935.原来,R2要求sharepoint2007必须是带sp2了,如果是2008则...
  • hugeng
  • hugeng
  • 2009年09月02日 17:32
  • 1163

SQL语句练习-计算字段

拼接操作符
  • joyzml
  • joyzml
  • 2011年04月04日 20:21
  • 1847

powerbuilder datawindow中插入计算列

在pb中创建一个gird类型的datawindow,设置好数据源表格后,想对其中的某几列进行计算操作如下: 点击如图按钮: 点击后界面不会有任何反应,此时在dw的空白处点击一下鼠标左键,会...
  • wolfalcon
  • wolfalcon
  • 2012年12月06日 16:23
  • 3108

SQL SERVER 创建计算列

在CREATE TABLE或ALTER TABLE语句中定义的列可以源于独立的或者基于列的计算。如果需要在相关查询中对相同的数据重复计算,则计算列就变得很有用。计算列以创建表或修改表的时候定义的表达式...
  • i1j2k3
  • i1j2k3
  • 2014年08月27日 10:23
  • 2780

poi 操作 excel 里面设置的公式不会自动计算 需要双击才会触发

//excel设置公式自动计算 sheet.setForceFormulaRecalculation(true);
  • liujs_vb
  • liujs_vb
  • 2014年11月28日 11:20
  • 2878

计算SharePoint两个日期和时间字段之间的时间差值

在SharePoint中,有一个“日期和时间(Date and Time)”类型的字段,使用此字段不仅可以只存储日期值,还可以用来存储日期和时间值。在列表或库中创建此字段时,默认为日期值,不能存储时间...
  • u012025054
  • u012025054
  • 2013年11月19日 10:50
  • 2417

MySQL创建计算字段

1使用场景存储在数据库中的数据一般不是应用程序所需要的格式,需要直接从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户机应用程序中重新格式化。 例如: 一张学生成绩表,想要...
  • chenmeng930601
  • chenmeng930601
  • 2017年05月03日 21:12
  • 173
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SharePoint2007计算列支持的公式
举报原因:
原因补充:

(最多只允许输入30个字)