验证栏的行使验证

By admin in 天文学 on 2019年3月9日

怎么给栏添加验证公式

成立栏界面第③栏就足以给该栏添加验证公式:

天文学 1

 

 

多少个常用的表明公式(函数)

Len()

注明:重回字段值的尺寸

用法:

天文学 2

注解是“填写周期”那个字段的值长度是16的话再次来到“是”,验证通过,否则重临“否”,验证不通过,展现错误提示。

 

MID()

评释:取字段值字符串的某3个字符

用法:

天文学 3

证实“填写周期”这么些字段值的第③个字符是否为“2”,验证通过,不然重返“否”,验证不经过,展现错误提醒。

 

code()

表明:取字符的ASCII值

用法:

天文学 4

上段公式,能够=((A+B)=2),A的值为(CODE(MID(填写周期,15,1))>47),B的值为(CODE(MID(填写周期,15,1))<58),

(CODE(MID(填写周期,15,1))>47)
解释为:先用MID()函数取“填写周期”字段值字符串的第一伍个字符,再用CODE()函数取上一步得到的字符的ASCII值,假诺超过47则赶回1,否则重回 0.
透过MID()和CODE()的匹配,可以把字符转成ASCII码,这样能够举行高低相比,那48~57是数字0~9的ASCII码。

归咎后,上段公式用于注脚该字段值第三三人必需是多少个数字。

 

更加多公式能够查看微软官方网站:http://office.microsoft.com/en-us/sharepoint-foundation-help/CH010372694.aspx

逻辑条件

And 运算

很遗憾那么些公式表明式中我们不能写and 或者or的表达式,不过这几个正常,其实上边包车型地铁以身作则已经给了三个and条件的例子。

万一我们要用and逻辑表明式,我们能够因此“+”运算,判断最终的值即可。

例如:

认清一个电话号码:

=(LEN([Phone])=12) 
+(CODE(MID([Phone],1,1))>47) 
+(CODE(MID([Phone],1,1))<58) 
+(CODE(MID([Phone],2,1))>47) 
+(CODE(MID([Phone],2,1))<58) 
+(CODE(MID([Phone],3,1))>47) 
+(CODE(MID([Phone],3,1))<58) 
+(MID([Phone],4,1)="-") 
+(CODE(MID([Phone],5,1))>47) 
+(CODE(MID([Phone],5,1))<58) 
+(CODE(MID([Phone],6,1))>47) 
+(CODE(MID([Phone],6,1))<58) 
+(CODE(MID([Phone],7,1))>47) 
+(CODE(MID([Phone],7,1))<58) 
+(MID([Phone],8,1)="-") 
+(CODE(MID([Phone],9,1))>47) 
+(CODE(MID([Phone],9,1))<58) 
+(CODE(MID([Phone],10,1))>47) 
+(CODE(MID([Phone],10,1))<58) 
+(CODE(MID([Phone],11,1))>47) 
+(CODE(MID([Phone],11,1))<58) 
+(CODE(MID([Phone],12,1))>47) 
+(CODE(MID([Phone],12,1))<58) 
=23

OR 运算

譬如想表达“血型”字段值只可以是“A”、“B”恐怕“O”时,能够用上边包车型大巴方法:

=(LEN([BLOODTYPE])=1) 
+(MID([Phone],1,1)="A") 
+(MID([Phone],1,1)="B") 
+(MID([Phone],1,1)="O") 
=2

其它可能还会遇见其余O大切诺基逻辑的或是,比如,我们的MOSS平台上有叁个列表记录职员和工人的周月报,其中“填写周期”一栏依照填写指标差别,须要使用三种格式,一种是周报格式必要用YYYY.MM.DD-MM.DD格式,其余一种是月报格式,须要运用YYYY.MM格式。以福利最后做总结视图。

俺们可以用”IF”逻辑运算进行处理:

=IF( 
  LEN(填写周期)=16 
    ,( (MID(填写周期,1,1)="2") 
      +(MID(填写周期,2,1)="0")
      +(MID(填写周期,3,1)="1")
      +(MID(填写周期,5,1)=".")
      +(MID(填写周期,8,1)=".")  
      +(MID(填写周期,11,1)="-")
      +(MID(填写周期,14,1)=".")
      =7) 
    ,(IF( 
      LEN(填写周期)=7 
        ,((MID(填写周期,1,1)="2") 
         +(MID(填写周期,2,1)="0")
         +(MID(填写周期,3,1)="1") 
         +(MID(填写周期,5,1)=".")
        =4) 
        ,FALSE 
    )) 
)

那边,我们只验证了字符长度和年份前三个人,当然也足以成功一心注脚,如下:

=OR( 
  ( 
    IF(ISERROR( 
        ((LEN([填写周期])=16) 
        +(CODE(MID([填写周期],1,1))>47) 
        +(CODE(MID([填写周期],1,1))<58) 
        +(CODE(MID([填写周期],2,1))>47) 
        +(CODE(MID([填写周期],2,1))<58) 
        +(CODE(MID([填写周期],3,1))>47) 
        +(CODE(MID([填写周期],3,1))<58) 
        +(CODE(MID([填写周期],4,1))>47) 
        +(CODE(MID([填写周期],4,1))<58) 
        +(MID([填写周期],5,1)=".") 
        +(CODE(MID([填写周期],6,1))>47) 
        +(CODE(MID([填写周期],6,1))<58)
        +(CODE(MID([填写周期],7,1))>47) 
        +(CODE(MID([填写周期],7,1))<58)
        +(MID([填写周期],8,1)=".") 
        +(CODE(MID([填写周期],9,1))>47) 
        +(CODE(MID([填写周期],9,1))<58)
        +(CODE(MID([填写周期],10,1))>47) 
        +(CODE(MID([填写周期],10,1))<58)
        +(MID([填写周期],11,1)="-") 
        +(CODE(MID([填写周期],12,1))>47) 
        +(CODE(MID([填写周期],12,1))<58)
        +(CODE(MID([填写周期],13,1))>47) 
        +(CODE(MID([填写周期],13,1))<58)
        +(MID([填写周期],14,1)=".") 
        +(CODE(MID([填写周期],15,1))>47) 
        +(CODE(MID([填写周期],15,1))<58)
        +(CODE(MID([填写周期],16,1))>47) 
        +(CODE(MID([填写周期],16,1))<58)
        =29) 
      ) 
      ,FALSE 
      ,IF( 
        ((LEN([填写周期])=16) 
        +(CODE(MID([填写周期],1,1))>47) 
        +(CODE(MID([填写周期],1,1))<58) 
        +(CODE(MID([填写周期],2,1))>47) 
        +(CODE(MID([填写周期],2,1))<58) 
        +(CODE(MID([填写周期],3,1))>47) 
        +(CODE(MID([填写周期],3,1))<58) 
        +(CODE(MID([填写周期],4,1))>47) 
        +(CODE(MID([填写周期],4,1))<58) 
        +(MID([填写周期],5,1)=".") 
        +(CODE(MID([填写周期],6,1))>47) 
        +(CODE(MID([填写周期],6,1))<58)
        +(CODE(MID([填写周期],7,1))>47) 
        +(CODE(MID([填写周期],7,1))<58)
        +(MID([填写周期],8,1)=".") 
        +(CODE(MID([填写周期],9,1))>47) 
        +(CODE(MID([填写周期],9,1))<58)
        +(CODE(MID([填写周期],10,1))>47) 
        +(CODE(MID([填写周期],10,1))<58)
        +(MID([填写周期],11,1)="-") 
        +(CODE(MID([填写周期],12,1))>47) 
        +(CODE(MID([填写周期],12,1))<58)
        +(CODE(MID([填写周期],13,1))>47) 
        +(CODE(MID([填写周期],13,1))<58)
        +(MID([填写周期],14,1)=".") 
        +(CODE(MID([填写周期],15,1))>47) 
        +(CODE(MID([填写周期],15,1))<58)
        +(CODE(MID([填写周期],16,1))>47) 
        +(CODE(MID([填写周期],16,1))<58)
        =29) 
        ,TRUE 
        ,FALSE 
       ) 
      )) 
    ,(IF(ISERROR( 
        ((LEN([填写周期])=7) 
        +(CODE(MID([填写周期],1,1))>47) 
        +(CODE(MID([填写周期],1,1))<58) 
        +(CODE(MID([填写周期],2,1))>47) 
        +(CODE(MID([填写周期],2,1))<58) 
        +(CODE(MID([填写周期],3,1))>47) 
        +(CODE(MID([填写周期],3,1))<58) 
        +(CODE(MID([填写周期],4,1))>47) 
        +(CODE(MID([填写周期],4,1))<58) 
        +(MID([填写周期],5,1)=".") 
        +(CODE(MID([填写周期],6,1))>47) 
        +(CODE(MID([填写周期],6,1))<58)
        +(CODE(MID([填写周期],7,1))>47) 
        +(CODE(MID([填写周期],7,1))<58)
        =14) 
      ) 
      ,FALSE 
      ,IF( 
        ((LEN([填写周期])=7) 
        +(CODE(MID([填写周期],1,1))>47) 
        +(CODE(MID([填写周期],1,1))<58) 
        +(CODE(MID([填写周期],2,1))>47) 
        +(CODE(MID([填写周期],2,1))<58) 
        +(CODE(MID([填写周期],3,1))>47) 
        +(CODE(MID([填写周期],3,1))<58) 
        +(CODE(MID([填写周期],4,1))>47) 
        +(CODE(MID([填写周期],4,1))<58) 
        +(MID([填写周期],5,1)=".") 
        +(CODE(MID([填写周期],6,1))>47) 
        +(CODE(MID([填写周期],6,1))<58)
        +(CODE(MID([填写周期],7,1))>47) 
        +(CODE(MID([填写周期],7,1))<58)
        =14) 
         ,TRUE 
         ,FALSE 
    )) 
  ) 
)

然则有个不幸的新闻,SharePoint的求证填只支持102五个字符,所以,笔者用了第3种。

其它

 

附一

一个老外写的用来验证Email地址的公式:

=(LEN(LEFT([Email],FIND("@",[Email])-1))>0) 
+(LEN(RIGHT([Email],LEN([Email])-FIND(".",[Email],FIND("@",[Email]))))>0) 
+(LEN(MID([Email],FIND("@",[Email])+1,FIND(".",[Email],FIND("@",[Email]))-FIND("@",[Email])-1))>0) 
+(ISERROR(FIND(" ",[Email]))=TRUE) 
=4

附二

微软网站上的连带扶持,方便现在查找,直接附在此处了。

公式概述

公式是对列表或库中的值进行总计的等式。公式以等号 (=)
初叶。例如,在下边包车型大巴公式中,结果至极 2 乘以 3 再加 5。

=5+2*3

能够在总计列中行使公式,也足以动用公式总结某一列的私下认可值。公式中得以包罗函数、列引用、运算符和常量,如下例所示。

=PI()*[Result]^2

元素 说明
函数 PI() 函数返回圆周率 pi 的值 3.141592654。
引用(或列名) [Result] 表示当前行的“Result”列中的值。
常量 直接输入到公式中的数字或文本值,如 2。
运算符 *(星号)运算符执行乘法运算,^(插入符号)运算符表示将数字乘幂。

公式可以运用上表中的3个或多少个因素。上边是局地依照复杂程度排序的公式的言传身教。

简言之公式(如 =128+345)

下列公式包涵常量和平运动算符。

示例 说明
=128+345 将 128 与 345 相加
=5^2 计算 5 的平方

富含列引用的公式(如 =[REVENUE] >[COST])

下列公式引用同多个列表或库中的其余列。

示例 说明
=[Revenue] 使用“Revenue”列中的值。
=[Revenue]*10/100 “Revenue”列中的值的 10%。
=[Revenue] > [Cost] 如果“Revenue”列中的值大于“Cost”列中的值,则返回“Yes”。

调用函数的公式(如 =AVERAGE(1, 2, 3, 4, 5))

下列公式调用内置函数。

示例 说明
=AVERAGE(1, 2, 3, 4, 5) 返回一组数值的平均值。
=MAX([Q1], [Q2], [Q3], [Q4]) 返回一组数值中的最大值。
=IF([Cost]>[Revenue], "Not OK", "OK") 如果成本大于收入,则返回“Not OK”。否则,返回“OK”。
=DAY("15-Apr-2008") 返回日期中的天。此公式返回数字 15。

带有嵌套函数的公式(如 =SUM(IF([A]>[B], [A]-[B], 10), [C]))

下列公式将一个或四个函数钦点为函数参数。

示例 说明
=SUM(IF([A]>[B], [A]-[B], 10), [C])

IF 函数返回列 A 与列 B 中值的差值或 10。

SUM 函数将 IF 函数的返回值与列 C 中的值相加。

=DEGREES(PI())

PI 函数返回数字 3.141592654。

DEGREES 函数将弧度值转换为角度值。此公式返回数值 180。

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

FIND 函数在 Column1 中搜索字符串 BD,并返回该字符串的起始位置。如果未找到该字符串,则返回一个错误值。

如果 FIND 函数返回一个数值,则 ISNUMBER 函数返回“Yes”。否则,它返回“No”。

 

函数概述

函数是预订义的公式,那些公式使用叫做参数的一定值按一定顺序或结构实行测算。函数可用以实践不难或复杂的猜测。例如,上面的ROUND 函数实例可将“Cost”列的数字四舍五入为小数点后两位。

=ROUND([Cost], 2)

学学函数和公式时,下列词汇很有救助:

结构     函数的结构以等号 (=)
开端,后跟函数名、左括号、以逗号分隔的函数参数,以右括号停止。

函数名称    
列表或库协助的函数的称谓。种种函数都引用特定个数的参数,并对那一个参数实行处理,然后回到四个值。

参数     参数能够是数字、文本、逻辑值(如 True 或
False)或列引用。钦命的参数必须是该参数的有效值。参数也足以是常量、公式或其余函数。

在一些情形下,只怕要求将二个函数作为另一个函数的二个参数使用。例如,上面的公式使用了嵌套的
AVERAGE 函数,将结果与两列的值之和开始展览相比较。

=AVERAGE([Cost1], SUM([Cost2]+[Discount]))

使得重临值    
当函数作为参数使用时,其再次来到值的类型必须与参数所用值的类型相同。例如,倘诺参数使用“Yes”或“No”,则嵌套函数必须再次来到“Yes”或“No”;不然,列表或库将展现
#VALUE! 错误值。

嵌套层数限制     一个公式最多能够分包八层嵌套函数。当函数 B 作为函数
A 中的参数使用时,函数 B 是第壹层函数。例如,在上头的以身作则中,SUM
函数是第2层函数,因为它是 AVERAGE 函数的参数。在 SUM
函数中嵌套的函数是第贰层函数,依此类推。

 注释 

  • 列表和库不支持 RAND 和 NOW 函数。
  • 总计列中不援救 TODAY 和 ME
    函数,但在某一列的私下认可值设置中帮忙那四个函数

 

在公式中利用列引用

引用标识当前行中的叁个单元格,并向列表或库提醒在哪里搜索要在公式中选用的值或数额。例如,[Cost]
引用当前行中“Cost”列中的值。借使当前行中“Cost”列的值为 100,则
=[Cost]*3 返回 300。

经过引用,可以在七个或八个公式中利用列表或库的不比列中所包罗的数量。能够在公式中援引下列数据类型的列:单行文本、数字、货币、日期和岁月、采用、是/否以及计算列。

可以利用列的来得名称在公式中援引该列。假使名称中蕴藏空格或特殊字符,则必须将名称括在方括号
([ ]) 中。引用不区分轻重缓急写。例如,能够在公式中应用 [Unit Price] 或
[unit price] 来引用“Unit Price”这一列。

 注释 

  • 不能够引用当前行以外的行中的值。
  • 不能够引用别的列表或库中的值。
  • 无法透过行 ID 引用新插入的行。因为执行总括时该 ID 尚不存在。
  • 不可能在为某列创制暗中认可值的公式中引用别的列。

 

在公式中应用常量

常量是永不总括的值。例如,日期 10/9/二〇〇玖 、数字 210
以及文本“季度收入”都以常量。常量能够是下列数据类型:

  • String(示例:=[Last Name] = “Smith”)

String 常量括在引号中,最多能够分包 255 个字符。

  • Number(示例:=[Cost] >= 29.99)

Numeric 常量能够分包小数位数,能够是正数或负数。

  • Date(示例:=[Date] > DATE(2007,7,1))

Date 常量须求选择 DATE(year,month,day) 函数。

  • Boolean(示例:=IF([Cost]>[Revenue], “Loss”, “No Loss”))

“Yes”和“No”是 Boolean 常量。能够在原则发挥中选用 Boolean
常量。在上边的示范中,假若“Cost”大于“Revenue”,则 IF
函数再次回到“Yes”,该公式再次回到字符串“Loss”。如若“Cost”等于或小于“Revenue”,则该函数重回“No”,并且该公式重临字符串“No
Loss”。

 

在公式中应用运算符

运算符钦赐要对公式中的成分执行的演算的门类。列表和库帮忙两种不一致品种的运算符:算术运算符、相比较运算符和文书运算符。

算术运算符

可采用下列算术运算符来执行加法、减法或乘法等主导数学生运动算,组合数字仍然发生数字结果。

算术运算符 含义(示例)
+(加号) 加法运算 (3+3)
–(减号) 减法运算 (3–1)
负数 (–1)
*(星号) 乘法运算 (3*3)
/(正斜杠) 除法运算 (3/3)
%(百分号) 百分比 (20%)
^(插入符号) 乘幂运算 (3^2)

比较运算符

能够利用下列运算符对三个值进行比较。使用这个运算符对五个值实行比较时,结果是二个逻辑值“Yes”或“No”。

比较运算符 含义(示例)
=(等号) 等于 (A=B)
>(大于号) 大于 (A>B)
<(小于号) 小于 (A<B)
>=(大于等于号) 大于或等于 (A>=B)
<=(小于等于号) 小于或等于 (A<=B)
<>(不等号) 不等于 (A<>B)

文本运算符

动用与号 (&) 联接或一而再1个或更四个文本字符串以发生一串文本。

文本运算符 含义(示例)
&(与号) 将两个值连接或联接起来产生一个连续的文本值 ("North"&"wind")

列表或库实施公式中的运算的逐一

公式依据一定的依次总括值。公式能够使用等号(=)起头。等号前面紧跟着要总结的要素(操作数),它们中间用运算符分隔。依据公式中每种运算符的特定顺序,列表和库从左向右计算公式。

运算符优先级

若果四个公式中用到多个运算符,列表和库将按下表所示的逐一举办演算。若是公式中隐含具有相同优先级的运算符,例如,公式中并且富含乘法运算符和除法运算符,则列表和库将从左到右总括运算符。  

运算符 说明
负号(例如 –1)
% 百分比
^ 乘幂
* 和 / 乘和除
+ 和 – 加和减
& 连接(将两个文本串连接在一起)
= < > <= >= <> 比较
利用括号

要改变求值顺序,请将公式中要先计算的一些用括号括起来。例如,上面公式的结果是
11,因为列表或库先进行乘法运算后开始展览加法运算。该公式将 2 与 3
相乘,然后再添加 5,所得的数值正是终极结果。

=5+2*3

与此相反,假设采纳括号改变语法,则列表或库先将 5 与 2
相加,再用所得的结果乘以 3,得到的最终结出为 21。

=(5+2)*3

在底下的以身作则中,公式中第②局地周围的括号强制列表或库先计算
[Cost]+25,然后用结果除以列 EC1 和 EC2 中的值之和。

=([Cost]+25)/SUM([EC1]+[EC2])

科普公式示例

 

规则公式

 

可以动用上边包车型大巴公式测试语句的规格并重回值“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、OTiguan 和 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 和 O帕杰罗 函数。

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,则返回“OK”。(Not OK)
15 9 8 =IF(OR([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK") 如果 15 大于 9 或小于 8,则返回“OK”。(OK)

 

要出示零,请执行三遍简单总结。要展现空白或短划线,请使用 IF 函数。

COLUMN1 COLUMN2 公式 说明(可能的结果)
10 10 =[Column1]-[Column2] 从第一个数字中减去第二个数字 (0)
15 9 =IF([Column1]-[Column2],"-",[Column1]-[Column2]) 值为零时返回一条短划线 (-)

 

要显示短划线、#N/A 或 NA 而不是大错特错值,请使用 ISE瑞虎RO福睿斯 函数。

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]) 值为错误时返回一条短划线

 

日期和时间公式

能够动用上边包车型地铁公式执行基于日期和岁月的计量,例如向有个别日期添加若干天、若干月或若干年,计算五个日子之间的差值,以及将时刻更换为小数值。

计算值获取当今日子

=DATE(YEAR(创建时间),MONTH(创建时间),DAY(创建时间))

天文学 5

足够日期

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

 注释   对日期实行操作时,总计栏的回来类型必须安装为“日期和岁月”

COLUMN1 COLUMN2 公式 说明(结果)
6/9/2007 3 =[Column1]+[Column2] 向 6/9/2007 添加 3 天 (6/12/2007)
12/10/2008 54 =[Column1]+[Column2] 向 12/10/2008 添加 54 天(2/2/2009)

要向某些日期添加若干月,请使用 DATE、YEA科雷傲、MONTH 和 DAY 函数。

COLUMN1 COLUMN2 公式 说明(结果)
6/9/2007 3 =DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) 向 6/9/2007 添加 3 个月 (9/9/2007)
12/10/2008 25 =DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) 向 12/10/2008 添加 25 个月 (1/10/2011)

要向有些日期添加若干年,请使用 DATE、YEAPRADO、MONTH 和 DAY 函数。

COLUMN1 COLUMN2 公式 说明(结果)
6/9/2007 3 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) 向 6/9/2007 添加 3 年 (6/9/2010)
12/10/2008 25 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) 向 12/10/2008 添加 25 年 (12/10/2033)

要向有个别日期添加天数、月数和年数的重组,请使用 DATE、YEARAV4、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)

天文学 6测算三个日子之间的差值

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

COLUMN1 COLUMN2 公式 说明(结果)
01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"d") 返回两个日期之间的天数 (1626)
01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"ym") 返回两个日期之间的月数,忽略年 (5)
01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"yd") 返回两个日期之间的天数,忽略年 (165)

天文学 7算算八个小时里面包车型客车差值

要选用专业时间格式(时:分:秒)显示结果,请使用减法运算符 (-) 和 TEXT
函数。要使此措施平常干活,小时不足超越 24,且分和秒不得超越 60。

COLUMN1 COLUMN2 公式 说明(结果)
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h") 两个时间之间的小时数 (4)
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h:mm") 两个时间之间的小时数和分钟数 (4:55)
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h:mm:ss") 两个时间之间的小时数、分钟数和秒数 (4:55:00)

要用基于1个时刻单位的总结值呈现结果,请使用 INT 函数或 HOU锐界、MINUTE 或
SECOND 函数。

COLUMN1 COLUMN2 公式 说明(结果)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*24) 两个时间之间的总小时数 (28)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*1440) 两个时间之间的总分钟数 (1735)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*86400) 两个时间之间的总秒数 (104100)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =HOUR([Column2]-[Column1]) 当差值不超过 24 时,两个时间之间的小时数 (4)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =MINUTE([Column2]-[Column1]) 当差值不超过 60 时,两个时间之间的分钟数 (55)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =SECOND([Column2]-[Column1]) 当差值不超过 60 时,两个时间之间的秒数 (0)

天文学 8转移时间

要将时辰从规范时间格式转换为小数,请使用 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)

天文学 9插入儒略历日期

儒略历日恒生期货指数的是一种日期格式,是现阶段年度和从当年开春算起的大运的整合。例如,二〇〇七年 1 月 1 日代表为 二〇〇七001,而 二〇〇七 年 12 月 31 日则象征为
二零零五365。此格式并不依照儒略历。

要将日期转换为儒略历日期,请使用 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。假诺使用 一九〇二 日期系统,则此公式只对 一九〇二 年 3 月 二十日以往的日期起成效。

COLUMN1 公式 说明(结果)
6/23/2007 =[Column1]+2415018.50 在天文学中使用的儒略历格式的日期 (2454274.50)

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

要将日期转换为表示四日中某一天的文件,请使用 TEXT 和 WEEKDAY 函数。

COLUMN1 公式 说明(可能的结果)
19-Feb-2007 =TEXT(WEEKDAY([Column1]), "dddd") 计算该日期在一周中所对应的天数,并返回这一天的全名 (Monday)
3-Jan-2008 =TEXT(WEEKDAY([Column1]), "ddd") 计算该日期在一周中所对应的天数,并返回这一天的缩写名称 (Thu)

 

数学公式

能够动用上面包车型地铁公式执行种种数学总结,如数字的加、减、乘、除,计算一组数字的平均值或中值,对数字实行四舍五入以及对数值举行计数。

天文学 11数字相加

要将一行中两栏或更七个栏中的数字相加,请使用加法运算符 (+) 或 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)

天文学 12数字相减

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

COLUMN1 COLUMN2 COLUMN3 公式 说明(结果)
15000 9000 -8000 =[Column1]-[Column2] 从 15000 中减去 9000 (6000)
15000 9000 -8000 =SUM([Column1], [Column2], [Column3]) 将前三个栏中的数字相加,包括负值 (16000)

天文学 13将多少个数字之间的差值总计为百分比

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

COLUMN1 COLUMN2 公式 说明(结果)
2342 2500 =([Column2]-[Column1])/ABS([Column1]) 百分比变动(6.75% 或 0.06746)

天文学 14数字相乘

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

COLUMN1 COLUMN2 公式 说明(结果)
5 2 =[Column1]*[Column2] 将前两个栏中的数字相乘 (10)
5 2 =PRODUCT([Column1], [Column2]) 将前两个栏中的数字相乘 (10)
5 2 =PRODUCT([Column1],[Column2],2) 将前两个栏中的数字及数字 2 相乘 (20)

天文学 15数字相除

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

COLUMN1 COLUMN2 公式 说明(结果)
15000 12 =[Column1]/[Column2] 用 15000 除以 12 (1250)
15000 12 =([Column1]+10000)/[Column2] 将 15000 与 10000 相加,然后用和除以 12 (2083)

天文学 16计量一组数字的平均值

平均值也称为平平均数量。要计算一行中两栏或更几个栏中的数字的平均值,请使用
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)

天文学 17算算一组数字的中值

中值是一文山会海有序数字的中间值。请使用 MEDIAN 函数总结一组数字的中值。

A B C D E F 公式 说明(结果)
10 7 9 27 0 4 =MEDIAN(A, B, C, D, E, F) 前六个栏中的数字的中值 (8)

天文学 18测算某些范围中的最小数或最大数

要计算一行中两栏或越来越多个栏中的数字中的最小数或最大数,请使用 MIN 和 MAX
函数。

COLUMN1 COLUMN2 COLUMN3 公式 说明(结果)
10 7 9 =MIN([Column1], [Column2], [Column3]) 最小数 (7)
10 7 9 =MAX([Column1], [Column2], [Column3]) 最大数 (10)

天文学 19对数值实行计数

要对数值实行计数,请使用 COUNT 函数。

COLUMN1 COLUMN2 COLUMN3 公式 说明(结果)
苹果树   12/12/2007 =COUNT([Column1], [Column2], [Column3]) 计算包含数值的栏数。排除日期和时间、文本以及空值 (0)
$12 #DIV/0! 1.01 =COUNT([Column1], [Column2], [Column3]) 计算包含数值的栏数,但排除错误和逻辑值 (2)

天文学 20将多少个数字高居不下或减少贰个百分点

请使用比例 (%) 运算符执行此测算。

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)

天文学 21对贰个数字举行乘幂运算

选用乘幂运算符 (^) 或 POWE翼虎 函数执行此总括。

COLUMN1 COLUMN2 公式 说明(结果)
5 2 =[Column1]^[Column2] 计算 5 的平方 (25)
5 3 =POWER([Column1], [Column2]) 计算 5 的立方 (125)

天文学 22对数字进行四舍五入

要对数字向上舍入,请使用 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)

 

文本公式

能够运用下边包车型大巴公式处理公事,例如组合或一而再几个栏中的值,比较一些栏中的内容,删除字符或空格以及重复字符。

天文学 23转移文本的深浅写

要改成文本的大小写,请使用 UPPE索罗德、LOWE安德拉 或 PROPECR-V 函数。

COLUMN1 公式 说明(结果)
nina Vietzen =UPPER([Column1]) 将文本更改为大写形式 (NINA VIETZEN)
nina Vietzen =LOWER([Column1]) 将文本更改为小写形式 (nina vietzen)
nina Vietzen =PROPER([Column1]) 将文本更改为词首大写形式 (Nina Vietzen)

天文学 24将名字和姓氏进行结合

要将名字和姓氏进行重组,请使用“与”运算符 (&) 或 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)

天文学 25将差别列中的文本和数字举办结合

要将文件和数字举行理并了结合,请使用 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.)

天文学 26将文件与日期或时间开始展览结合

要将文件与日期或时刻开展重组,请使用 TEXT 函数和“与”运算符 (&)。

COLUMN1 COLUMN2 公式 说明(结果)
Billing Date 5-Jun-2007 ="Statement date: "&TEXT([Column2], "d-mmm-yyyy") 将文本与日期进行组合 (Statement date: 5-Jun-2007)
Billing Date 5-Jun-2007 =[Column1]&" "&TEXT([Column2], "mmm-dd-yyyy") 将不同栏中的文本与日期组合为一栏 (Billing Date Jun-05-2007)

天文学 27比较栏中的内容

要将一栏同另一栏或值列表进行相比,请使用 EXACT 和 O本田UR-V 函数。

COLUMN1 COLUMN2 公式 说明(可能的结果)
BD122 BD123 =EXACT([Column1],[Column2]) 对前两栏中的内容进行比较 (No)
BD122 BD123 =EXACT([Column1], "BD122") 将 Column1 中的内容与字符串“BD122”进行比较 (Yes)

天文学 28反省栏值或栏值的一部分是不是与一定文本匹配

要检查栏值或栏值的一片段是否与特定文本匹配,请使用 IF、FIND、SEA昂CoraCH 和
ISNUMBECRUISER 函数。

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)

天文学 29算算非空栏的数额

要总计非空栏的多寡,请使用 COUNTA 函数。

COLUMN1 COLUMN2 COLUMN3 公式 说明(结果)
销售额 19   =COUNTA([Column1], [Column2]) 计算非空栏的数目 (2)
销售额 19   =COUNTA([Column1], [Column2], [Column3]) 计算非空栏的数目 (2)

天文学 30去除文本中的字符

要去除文本中的字符,请使用 LEN、LEFT 和 大切诺基IGHT 函数。

COLUMN1 公式 说明(结果)
Vitamin A =LEFT([Column1],LEN([Column1])-2) 从左边开始返回 7 (9-2) 个字符 (Vitamin)
Vitamin B1 =RIGHT([Column1], LEN([Column1])-8) 从右边开始返回 2 (10-8) 个字符 (B1)

天文学 31删除栏初步和结尾处的空格

天文学,要删除栏中的空格,请使用 T陆风X8IM 函数。

COLUMN1 公式 说明(结果)
    Hello there! =TRIM([Column1]) 删除开头和结尾处的空格 (Hello there!)

天文学 32重复栏中的字符

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

公式 说明(结果)
=REPT(".",3) 将句号重复 3 次 (…)
=REPT("-",10) 将短划线重复 10 次 (———-)

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2019 亚洲必赢手机官网 版权所有