当前位置：网学 > 设计资源 > Excel教程 > 正文

OfficeExcel的公式技巧

论文降重修改服务、格式排版等获取论文论文降重及排版论文发表相关服务

和 2002 年 8 月 15 日之间有 440 天。

DATEDIF("2001/6/1","2002/8/15","YD") 等于 75，即在 6 月 1 日与 8 月 15 日之间有 75 天，忽略日期中的年。

DATEDIF("2001/6/1","2002/8/15","MD") 等于 14，即开始日期 1 和结束日期 15 之间的差，忽略日期中的年和月。

1.7 在一个单元格中指定字符出现的次数

例如在A1单元格中有“abcabca”字符串，求“a”在单元格A1内出现次数，用下列公式：

=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))。

1.8 日期形式的转换

我们在有些情况下写日期会用“20060404”表示，如何转换成“2006-04-04”的标准日期格式，用下面的两个公式之一（假定在A1单元格中有原始日期）：

=TEXT(A1,"0000-00-00")

=TEXT(A1,"????-??-??")。

也可以使用以下公式，转换成“2006-4-4”的格式。

=LEFT(A1,4)&SUBSTITUTE(RIGHT(A1,4),0,"-")。

反之，如何把“2006年4月4日”转换成“20060404”？可以利用下面的公式之一（假定在A1单元格中有原始日期）：

=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00" )

=YEAR(A1)&IF(MONTH(A1)<10,"0"&MONTH(A1),MONTH(A1))&IF(DAY(DAY(A1)<10),"0"&DAY(A1),DAY(A1))

=TEXT(A1,"yyyymmdd")。

也可以直接自定义格式：yyyymmdd。

1.9 用“定义名称”的方法突破IF函数的嵌套限制

Excel中的IF()函数的一个众所周知的限制是嵌套不能超过7层。例如下面的公式是错误的，因为嵌套层数超过了限制。

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))

通常的方法会考虑用VBA代替。但是也可以可以通过对公式的一部分”定义名称”来解决这种限制定义一个名叫”OneToSix”的名称, 里面包括公式：

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

再定义另一个名叫”SevenToThirteen”的名称，里面包括公式：

=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,IF(Sheet1!$A$4=13,130,"NotFound")))))))

最后单元格中输入下面的公式：

=IF(OneToSix,OneToSix,SevenToThirteen)

1.10 动态求和

举一个简单例子：例如对于A列，求出A1到当前单元格行标前面一行的单元格中的数值之和，更直接地说，如果当前单元格在B17，那么求A1:A16之和。利用下面的公式：

=SUM(INDIRECT("A1:A"&ROW()-1))。

1.11 COUNTIF函数的16种公式设置（设DATA为区域名称）

（1）返加包含值12的单元格数量：=COUNTIF(DATA,12)

（2）返回包含负值的单元格数量：=COUNTIF(DATA,"<0")

（3）返回不等于0的单元格数量：=COUNTIF(DATA,"<>0")

（4）返回大于5的单元格数量：=COUNTIF(DATA,">5")

（5）返回等于单元格A1中内容的单元格数量：=COUNTIF(DATA,A1)

（6）返回大于单元格A1中内容的单元格数量：=COUNTIF(DATA,“>”&A1)