Excel的公式技巧.docx

Excel的公式技巧.docx

《Excel的公式技巧.docx》由会员分享，可在线阅读，更多相关《Excel的公式技巧.docx（17页珍藏版）》请在冰豆网上搜索。

Excel的公式技巧

Excel的公式技巧

1.公式技巧

1.1在单元格中显示工作表和工作簿的名称

在单元格中显示工作表的名称，有两种方法：

（1）建立如下自定义函数：

Functionbookname（）

bookname=ActiveSheet.Name

EndFunction

使用时在单元格中输入公式：

=bookname（），即可返回当前工作簿的标签名字。

（2）自定义名称的方法。

定义如下名称：

点击[插入]à[名称]à[定义]，名称的定义为“T_B”，引用位置输入：

“=replace（get.document

（1）,1,find（"]",get.document

（1））,）&t（now（））”，在单元格输入“=T_B”就可以显示当前表名。

值得注意的是，返回的工作表名称随着工作表名称的变化而变化。

在此引用中，GET.DOCUMENT（）是宏表函数，当数据变动时无法自动计算，now（）是易失性函数，任何变动都会强制计算，宏表函数所以加上now（）就可以自动重算了，T（）用来将now（）产生的数值转化为空文本。

在单元格中显示工作簿的名称，使用系统函数Cell（）：

在单元格中输入公式：

=Cell（"filename"），就会返回该工作簿和工作表的名字（包括绝对路径名），然后根据自己的需要运用一些文本处理函数进行处理即可。

注意：

该函数必须在工作簿已经保存的情况下才生效。

1.2简单判断单元格最后一位是数字还是字母

在有些情况下，需要判断单元格的最后一位是数字还是字母，可以用下面三个公式之一：

（2）=IF（ISNUMBER（--RIGHT（A1,1））,"数字","字母"），直接返回数字或字母。

其中“--”的含义是将文本型数字转化为数值以便参与运算。

（3）=IF（ISERR（RIGHT（A1）*1）,"字母","数字"），直接返回数字或字母。

1.3如何求出一个人到某指定日期的周岁？

=DATEDIF（起始日期,结束日期,"Y"）

1.4判断单元格中存在特定字符

假如判断A栏里是否存在"$"字符，有则等于1，没有则等于0，公式为：

=IF（COUNTIF（A:

A,"*$*"）>0,1,0）。

1.5计算某单元格所在的列数

通常情况下，A列为第1列，AA列为27列。

可以在A1单元格中输入列标，通过下列公式计算出任何列标的列数：

=COLUMN（INDIRECT（A1&"1"））。

例如：

“FG”列为第163列。

1.6DATEDIF函数的作用

DATEDIF函数计算两个日期之间的天数、月数或年数。

提供此函数是为了与Lotus1-2-3兼容。

语法：

DATEDIF（start_date,end_date,unit）

Start_date为一个日期，它代表时间段内的第一个日期或起始日期。

日期有多种输入方法：

带引号的文本串（例如"2001/1/30"）、系列数（例如，如果使用1900日期系统则36921代表2001年1月30日）或其他公式或函数的结果（例如，DATEVALUE（"2001/1/30"））。

End_date为一个日期，它代表时间段内的最后一个日期或结束日期。

Unit为所需信息的返回类型。

Unit返回"Y"时间段中的整年数。

"M"时间段中的整月数。

"D"时间段中的天数。

"MD"start_date与end_date日期中天数的差。

忽略日期中的月和年。

"YM"start_date与end_date日期中月数的差。

忽略日期中的日和年。

"YD"start_date与end_date日期中天数的差。

忽略日期中的年。

说明：

MicrosoftExcel按顺序的系列数保存日期，这样就可以对其进行计算。

如果工作簿使用1900日期系统，则Excel会将1900年1月1日保存为系列数1。

而如果工作簿使用1904日期系统，则Excel会将1904年1月1日保存为系列数0，（而将1904年1月2日保存为系列数1）。

例如，在1900日期系统中Excel将1998年1月1日保存为系列数35796，因为该日期距离1900年1月1日为35795天。

请查阅MicrosoftExcel如何存储日期和时间。

ExcelforWindows和ExcelforMacintosh使用不同的默认日期系统。

有关详细信息，请参阅NOW。

示例

DATEDIF（"2001/1/1","2003/1/1","Y"）等于2，即时间段中有两个整年。

DATEDIF（"2001/6/1","2002/8/15","D"）等于440，即在2001年6月1日和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.11COUNTIF函数的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）

（7）返回包含文本内容的单元格数量：

=COUNTIF（DATA,“*”）

（8）返回包含三个字符内容的单元格数量：

=COUNITF（DATA,“?

?

?

”）

（9）返回包含单词"GOOD"（不分大小写）内容的单元格数量：

=COUNTIF（DATA,“GOOD”）

（10）返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量：

=COUNTIF（DATA,“*GOOD*”）

（11）返回包含以单词"AB"（不分大小写）开头内容的单元格数量：

=COUNTIF（DATA,“AB*”）

（12）返回包含当前日期的单元格数量：

=COUNTIF（DATA,TODAY（））

（13）返回大于平均值的单元格数量：

=COUNTIF（DATA,">"&AVERAGE（DATA））

（14）返回平均值上面超过三个标准误差的值的单元格数量：

=COUNTIF（DATA,“>"&AVERAGE（DATA）STDEV（DATA）*3）

（15）返回包含值为或-3的单元格数量：

=COUNTIF（DATA,3）COUNIF（DATA,-3）

（16）返回包含值逻辑值为TRUE的单元格数量：

=COUNTIF（DATA,TRUE）

1.12计算一个日期是一年中的第几天

例如2006年7月29日是本年中的第几天？

在一年中，显示是第几天用什么函数呢？

假定A1中是日期，利用下列公式：

=A1-DATE（YEAR（A1）,1,0），将单元格格式设置为常规，返回210，即2006年7月29日是2006年的第210天。

1.13如何用公式求出最大值所在的行？

如A1：

A10中有10个数，怎么求出最大的数在哪个单元格？

=MATCH（LARGE（A1:

A10,1）,A1:

A10,0）

=ADDRESS（MATCH（SMALL（A1:

A10,COUNTA（A1:

A10））,A1:

A10,0）,1）

=ADDRESS（MATCH（MAX（A1:

A10,1）,A1:

A10,0）,1）

1.14在Excel中的绝对引用与相对引用之间切换

在Excel中创建公式时，该公式可以使用相对引用，即相对于公式所在的位置引用单元；也可以使用绝对引用，即引用特定位置上的单元。

引用由所在单元格的“列的字母”和“行的数字”组成，绝对引用由在“列的字母”和“行的数字”前面加“$”表示，例如，$B$1是对第一行B列的绝对引用。

公式中还可