史上最全excel函数集合.docx
《史上最全excel函数集合.docx》由会员分享，可在线阅读，更多相关《史上最全excel函数集合.docx（108页珍藏版）》请在冰豆网上搜索。
史上最全excel函数集合
Excel函数应用之函数简介
Excel是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠Excel进行数据管理。
它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算，然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人却难以进一步深入。
编者以为，对Excel函数应用的不了解正是阻挡普通用户完全掌握Excel的拦路虎，然而目前这一部份内容的教学文章却又很少见，所以特别组织了这一个《Excel函数应用》系列，希望能够对Excel进阶者有所帮助。
《Excel函数应用》系列，将每周更新，逐步系统的介绍Excel各类函数及其应用，敬请关注！
Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头，几乎没有什么软件能够与它匹敌。
在您学会了Excel的基本操作后，是不是觉得自己一直局限在Excel的操作界面中，而对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上呢？
难道Excel只能做这些简单的工作吗？
其实不然，函数作为Excel处理数据的一个最重要手段，功能是十分强大的，在生活和工作实践中可以有多种应用，您甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。
请跟随笔者开始Excel的函数之旅。
这里，笔者先假设您已经对于Excel的基本操作有了一定的认识。
首先我们先来了解一些与函数有关的知识。
一、什么是函数
Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。
用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。
例如，SUM函数对单元格或单元格区域进行加法运算。
函数是否可以是多重的呢？
也就是说一个函数是否可以是另一个函数的参数呢？
当然可以，这就是嵌套函数的含义。
所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。
例如图1中所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。
这个公式的含义是：
如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。
图1嵌套函数
在学习Excel函数之前，我们需要对于函数的结构做以必要的了解。
如图2所示，函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。
如果函数以公式的形式出现，请在函数名称前面键入等号（=）。
在创建包含函数的公式时，公式选项板将提供相关的帮助。
图2函数的结构
公式选项板--帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。
单击编辑栏中的"编辑公式"按钮，或是单击"常用"工具栏中的"粘贴函数"按钮之后，就会在编辑栏下面出现公式选项板。
整个过程如图3所示。
图3公式选项板
二、使用函数的步骤
在Excel中如何使用函数呢？
1.单击需要输入函数的单元格，如图4所示，单击单元格C1，出现编辑栏
图4单元格编辑
2.单击编辑栏中"编辑公式"按钮，将会在编辑栏下面出现一个"公式选项板"，此时"名称"框将变成"函数"按钮，如图3所示。
3.单击"函数"按钮右端的箭头，打开函数列表框，从中选择所需的函数；
图5函数列表框
4.当选中所需的函数后，Excel2000将打开"公式选项板"。
用户可以在这个选项板中输入函数的参数，当输入完参数后，在"公式选项板"中还将显示函数计算的结果；
5.单击"确定"按钮，即可完成函数的输入；
6.如果列表中没有所需的函数，可以单击"其它函数"选项，打开"粘贴函数"对话框，用户可以从中选择所需的函数，然后单击"确定"按钮返回到"公式选项板"对话框。
在了解了函数的基本知识及使用方法后，请跟随笔者一起寻找Excel提供的各种函数。
您可以通过单击插入栏中的"函数"看到所有的函数。
图6粘贴函数列表
三、函数的种类
Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
1.数据库函数--当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。
例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1,000且小于2,500的行或记录的总数。
MicrosoftExcel共有12个工作表函数用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions，也称为D函数，每个函数均有三个相同的参数：
database、field和criteria。
这些参数指向数据库函数所使用的工作表区域。
其中参数database为工作表上包含数据清单的区域。
参数field为需要汇总的列的标志。
参数criteria为工作表上包含指定条件的区域。
2.日期与时间函数--通过日期与时间函数，可以在公式中分析和处理日期值和时间值。
3.工程函数--工程工作表函数用于工程分析。
这类函数中的大多数可分为三种类型：
对复数进行处理的函数、在不同的数字系统（如十进制系统、十六进制系统、八进制系统和二进制系统）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。
4.财务函数--财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。
财务函数中常见的参数：
未来值（fv）--在所有付款发生后的投资或贷款的价值。
期间数（nper）--投资的总支付期间数。
付款（pmt）--对于一项投资或贷款的定期支付数额。
现值（pv）--在投资期初的投资或贷款的价值。
例如，贷款的现值为所借入的本金数额。
利率（rate）--投资或贷款的利率或贴现率。
类型（type）--付款期间内进行支付的间隔，如在月初或月末。
5.信息函数--可以使用信息工作表函数确定存储在单元格中的数据的类型。
信息函数包含一组称为IS的工作表函数，在单元格满足条件时返回TRUE。
例如，如果单元格包含一个偶数值，ISEVEN工作表函数返回TRUE。
如果需要确定某个单元格区域中是否存在空白单元格，可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数，或者使用ISBLANK工作表函数确定区域中的某个单元格是否为空。
6.逻辑函数--使用逻辑函数可以进行真假值判断，或者进行复合检验。
例如，可以使用IF函数确定条件为真还是假，并由此返回不同的数值。
7.查询和引用函数--当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。
例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用VLOOKUP工作表函数。
如果需要确定数据清单中数值的位置，可以使用MATCH工作表函数。
8.数学和三角函数--通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。
9.统计函数--统计工作表函数用于对数据区域进行统计分析。
例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和y轴截距，或构成直线的实际点数值。
10.文本函数--通过文本函数，可以在公式中处理文字串。
例如，可以改变大小写或确定文字串的长度。
可以将日期插入文字串或连接在文字串上。
下面的公式为一个示例，借以说明如何使用函数TODAY和函数TEXT来创建一条信息，该信息包含着当前日期并将日期以"dd-mm-yy"的格式表示。
11.用户自定义函数--如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。
这些函数，称为用户自定义函数，可以通过使用VisualBasicforApplications来创建。
以上对Excel函数及有关知识做了简要的介绍，在以后的文章中笔者将逐一介绍每一类函数的使用方法及应用技巧。
但是由于Excel的函数相当多，因此也可能仅介绍几种比较常用的函数使用方法，其他更多的函数您可以从Excel的在线帮助功能中了解更详细的资讯。
Excel进阶技巧
（一）
无可见边线表格的制作方法
对Excel工作簿中的表格线看腻了吗？
别着急，Excel的这些表格线并非向人们想象的那样是必需的，它也是可以去掉的，我们也可以使整个Excel工作簿变成“白纸”，具体步骤为：
1.执行“工具”菜单中的“选项”命令，打开“选项”对话框。
2.单击“视图”选项卡。
3.清除“网格线”选项。
4.单击“确定”按钮，关闭“选项”对话框。
快速输入大写中文数字的简便方法
Excel具有将用户输入的小写数字转换为大写数字的功能（如它可自动将123.45转换为“壹佰贰拾叁点肆伍”），这就极大的方便了用户对表格的处理工作。
实现这一功能的具体步骤为：
1.将光标移至需要输入大写数字的单元格中。
2.利用数字小键盘在单元格中输入相应的小写数字（如123.45）。
3.右击该单元格，并从弹出的快捷菜单中执行“设置单元格格式”命令。
4.从弹出的“单元格格式”对话框中选择“数字”选项卡。
5.从“分类”列表框中选择“特殊”选项；从“类别”列表框中选择“中文大写数字”选项。
6.单击“确定”按钮，用户输入的123.45就会自动变为“壹佰贰拾叁点肆伍”，效果非常不错。
在公式和结果之间进行切换的技巧
一般来说，当我们在某个单元格中输入一些计算公式之后，Excel只会采用数据显示方式，也就是说它会直接将计算结果显示出来，我们反而无法原始的计算公式。
广大用户若拟查看原始的计算公式，只需单击“Ctrl-`"键（后撇号，键盘上浪线符~的小写方式），Excel就会在计算公式和最终计算结果之间进行切换。
不过此功能仅对当前活动工作簿有效，用户若拟将所有工作簿都设置为只显示公式，则应采用如下方法：
1.执行“工具”菜单的“选项”命令，打开“选项”对话框。
2.击“视图”选项卡。
3.选“窗口选项”栏中的“公式”选项。
4.单击“确定”按钮，关闭“选项”对话框。
Excel中自动为表格添加序号的技巧
Excel具有自动填充功能，它可帮助用户快速实现诸如“第1栏”、“第2栏”…“第10栏”之类的数据填充工作，具体步骤为：
1.在A1、B1单元格中分别输入“第1栏”、“第2栏”字样。
2.用鼠标将A1:
B1单元格定义为块。
3.为它们设置适当的字体、字号及对其方式（如居中、右对齐）等内容。
4.将鼠标移至B1单元格的右下角，当其变成十字形时，拖动鼠标向右移动，直至J1栏为止。
5.放开鼠标，则A1-J1栏就会出现诸如“第1栏”、“第2栏”…“第10栏”的栏号，且它们的格式、排列位置都完全相同，从而满足了用户为表格添加栏号的要求。
当然，我们也可采用同样的办法在Excel表格中自动设置F1、F2或第1行、第2行之类的行号，操作十分方便。
在多个工作表内输入相同内容的技巧
有时，我们会因为某些特殊原因而希望在同一个工作簿的不同工作表中输入相同的内容，这时我们既不必逐个进行输入，也不必利用复制、粘贴的办法，直接利用下述方法即可达到目的：
1.打开相应工作簿。
2.在按下Ctrl键的同时采用鼠标单击窗口需要输入相同内容的不同工作表（如Sheet1、Sheet2......），为这些工作表建立联系关系。
3.在其中的任意一个工作表中输入需要所入的内容（如表格的表头及表格线等），此时这些数据就会自动出现在选中的其它工作表之中。
4.输入完毕之后，再次按下Ctrl键，并使用鼠标单击所选择的多个工作表，解除这些工作表之间的联系关系（否则用户输入的内容还会出现在其它工作表中）。
在Excel97中插入超级链接的技巧
与Word等其它Office组件一样，Excel97也具有在工作簿中插入超级链接的功能，我们可以利用此功能将有关Internet网址、磁盘地址、甚至同一张Excel工作簿的不同的单元格链接起来，此后就可以直接利用这些链接进行调用，从而极大地方便了用户的使用。
在Excel97中插入超级链接的步骤为：
1.将光标移到需要插入超级链接的位置。
2.执行“插入”菜单中的“超级链接”命令，打开“插入超级链接”对话框。
3.在“链接到文件或URL"对话框中指定需要链接的文件位置或Internet网址（当用户需要链接同一个工作簿中的不同单元格时，此位置可空出不填）。
4.在“文件中有名称的位置”对话框中指定需要链接文件的具体位置（如Word文档的某个书签、Excel工作簿的某个单元格等）。
5.单击“确定”按钮，关闭“插入超级链接”对话框。
这样，我们就实现了在Excel工作簿中插入超级链接的目的，此后用户只需单击该超级链接按钮，系统即会自动根据超级链接所致显得内容做出相应的处理（若链接的对象为Internet网址则自动激活IE打开该网址；若链接的对象为磁盘文件，则自动打开该文件；若链接的对象为同一个工作簿的不同单元格则自动将当前单元格跳转至该单元格），从而满足了广大用户的需要。
复制样式的技巧
除可对已有的样式进行修改及自定义所需的样式之外，Excel还允许我们将某个工作簿所包含的样式拷贝到其它工作簿中使用，以进一步扩大样式的使用范围，具体步骤为：
1.打开包含有需要复制样式的源工作簿及目标工作簿。
2.执行“格式”菜单的“样式”命令，打开“样式”对话框。
3.单击“合并”按钮，打开“合并样式”对话框。
4.在“合并样式来源”框中选择包含有要复制样式的源工作簿并单击“确定”按钮，源工作簿中所包含的一切样式就会拷贝到目标工作簿中（对于同名的样式，系统将会要求用户选择是否覆盖），然后我们就可以在目标工作簿中直接加以使用了，从而免去了重复定义之苦。
利用粘贴函数功能简化函数的调用速度
Excel一共向用户提供了日期、统计、财务、文本、查找、信息、逻辑等10多类，总计达数百种函数，不熟悉的用户可能很难使用这些优秀功能，没关系，“粘贴函数”功能可在我们需要使用各种函数时提供很大的帮助。
“粘贴函数”功能是Excel为了解决部分用户不了解各种函数的功能及用法、而专门设置的一种一步一步指导用户正确使用各种函数的向导功能，广大用户可在它的指导下方便地使用各种自己不熟悉的函数，并可在使用的过程中同时进行学习、实践，效果非常好。
在Excel中使用“粘贴函数”功能插入各种函数的步骤为：
1.在Excel中将光标移至希望插入有关函数的单元格中。
2.执行Excel"插入”菜单的“函数”命令（或单击快捷工具栏上的“粘贴函数”按钮），打开“粘贴函数”对话框。
3.若Office向导没有出现，可单击“粘贴函数”对话框中的“Office向导”按钮，激活Office向导，以便在适当的时候获取它的帮助。
4.在参考“粘贴函数”对话框对各个函数简介的前提下，从“函数分类”列表框中选择欲插入函数的分类、从“函数名”列表框中选择所需插入的函数。
5.单击“确定”按钮。
6.此时，系统就会打开一个用于指导用户插入相应函数的对话框，我们可在它的指导下为所需插入的函数指定各种参数。
7.单击“确定”按钮。
Excel函数应用之数学和三角函数
学习Excel函数，我们还是从“数学与三角函数”开始。
毕竟这是我们非常熟悉的函数，这些正弦函数、余弦函数、取整函数等等从中学开始，就一直陪伴着我们。
首先，让我们一起看看Excel提供了哪些数学和三角函数。
笔者在这里以列表的形式列出Excel提供的所有数学和三角函数，详细请看附注的表格。
从表中我们不难发现，Excel提供的数学和三角函数已基本囊括了我们通常所用得到的各种数学公式与三角函数。
这些函数的详细用法，笔者不在这里一一赘述，下面从应用的角度为大家演示一下这些函数的使用方法。
一、与求和有关的函数的应用
SUM函数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。
相信这也是大家最先学会使用的Excel函数之一。
但是实际上，Excel所提供的求和函数不仅仅只有SUM一种，还包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2几种函数。
这里笔者将以某单位工资表为例重点介绍SUM（计算一组参数之和）、SUMIF（对满足某一条件的单元格区域求和）的使用。
（说明：
为力求简单，示例中忽略税金的计算。
）
图1函数求和
SUM
1、行或列求和
以最常见的工资表（如上图）为例，它的特点是需要对行或列内的若干单元格求和。
比如，求该单位2001年5月的实际发放工资总额，就可以在H13中输入公式：
=SUM（H3:
H12）
2、区域求和
区域求和常用于对一张工作表中的所有数据求总计。
此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式"=SUM（）"，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。
若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。
对于需要减去的单元格，则可以按住Ctrl键逐个选中它们，然后用手工在公式引用的单元格前加上负号。
当然你也可以用公式选项板完成上述工作，不过对于SUM函数来说手工还是来的快一些。
比如，H13的公式还可以写成：
=SUM（D3:
D12,F3:
F12）-SUM（G3:
G12）
3、注意
SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。
换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。
对需要参与求和的某个常数，可用"=SUM（单元格区域，常数）"的形式直接引用，一般不必绝对引用存放该常数的单元格。
SUMIF
SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。
仍以上图为例，在工资表中需要分别计算各个科室的工资发放情况。
要计算销售部2001年5月加班费情况。
则在F15种输入公式为
=SUMIF（$C$3:
$C$12,"销售部",$F$3:
$F$12）
其中"$C$3:
$C$12"为提供逻辑判断依据的单元格区域，"销售部"为判断条件即只统计$C$3:
$C$12区域中部门为"销售部"的单元格，$F$3:
$F$12为实际求和的单元格区域。
二、与函数图像有关的函数应用
我想大家一定还记得我们在学中学数学时，常常需要画各种函数图像。
那个时候是用坐标纸一点点描绘，常常因为计算的疏忽，描不出平滑的函数曲线。
现在，我们已经知道Excel几乎囊括了我们需要的各种数学和三角函数，那是否可以利用Excel函数与Excel图表功能描绘函数图像呢？
当然可以。
这里，笔者以正弦函数和余弦函数为例说明函数图像的描绘方法。
图2函数图像绘制
1、录入数据--如图所示，首先在表中录入数据，自B1至N1的单元格以30度递增的方式录入从0至360的数字，共13个数字。
2、求函数值--在第2行和第三行分别输入SIN和COS函数，这里需要注意的是：
由于SIN等三角函数在Excel的定义是要弧度值，因此必须先将角度值转为弧度值。
具体公式写法为（以D2为例）：
=SIN（D1*PI（）/180）
3、选择图像类型--首先选中制作函数图像所需要的表中数据，利用Excel工具栏上的图表向导按钮（也可利用"插入"／"图表"），在"图表类型"中选择"XY散点图"，再在右侧的"子图表类型"中选择"无数据点平滑线散点图"，单击[下一步]，出现"图表数据源"窗口，不作任何操作，直接单击[下一步]。
4、图表选项操作--图表选项操作是制作函数曲线图的重要步骤，在"图表选项"窗口中进行（如图3），依次进行操作的项目有：
标题--为图表取标题，本例中取名为"正弦和余弦函数图像"；为横轴和纵轴取标题。
坐标轴--可以不做任何操作；
网格线--可以做出类似坐标纸上网格，也可以取消网格线；
图例--本例选择图例放在图像右边，这个可随具体情况选择；
数据标志--本例未将数据标志在图像上，主要原因是影响美观。
如果有特殊要求例外。
5、完成图像--操作结束后单击[完成]，一幅图像就插入Excel的工作区了。
6、编辑图像--图像生成后，字体、图像大小、位置都不一定合适。
可选择相应的选项进行修改。
所有这些操作可以先用鼠标选中相关部分，再单击右键弹出快捷菜单，通过快捷菜单中的有关项目即可进行操作。
至此，一幅正弦和余弦函数图像制作完成。
用同样的方法，还可以制作二次曲线、对数图像等等。
三、常见数学函数使用技巧--四舍五入
在实际工作的数学运算中，特别是财务计算中常常遇到四舍五入的问题。
虽然，excel的单元格格式中允许你定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正的四舍五入，只是显示结果似乎四舍五入了。
如果采用这种四舍五入方法的话，在财务运算中常常会出现几分钱的误差，而这是财务运算不允许的。
那是否有简单可行的方法来进行真正的四舍五入呢？
其实，Excel已经提供这方面的函数了，这就是ROUND函数，它可以返回某个数字按指定位数舍入后的数字。
在Excel提供的"数学与三角函数"中提供了一个名为ROUND（number,num_digits）的函数，它的功能就是根据指定的位数，将数字四舍五入。
这个函数有两个参数，分别是number和num_digits。
其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。
如图3所示：
单元格B2中为初始数据0.123456，B3的初始数据为0.234567，将要对它们进行四舍五入。
在单元格C2中输入"=ROUND（B2,2）"，小数点后保留两位有效数字，得到0.12、0.23。
在单元格D2中输入"=ROUND（B2,4）"，则小数点保留四位有效数字，得到0.1235、0.2346。
图3对数字进行四舍五入
对于数字进行四舍五入,还可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。
因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。
仍然以图3为例，如果采用INT函数，则C2公式应写成：
"=INT（B2*100+0.5）/100"。
最后需要说明的是：
本文所有公式均在Excel97和Excel2000中验证通过，修改其中的单元格引用和逻辑条件值，可用于相似的其他场合。
附注：
Excel的数学和三角函数一览表
ABS工作表函数
返回参数的绝对值
ACOS工作表函数
返回数字的反余弦值
ACOSH工作表函数
返回参数的反双曲余弦值
ASIN工作表函数
返回参数的反正弦值
ASINH工作表函数
返回参数的反双曲正弦值
ATAN工作表函数
返回参数的反正切值
ATAN2工作表函数
返回给定的X及Y坐标值的反正切值
ATANH
升级会员 