excel函数基础知识

篇一：2015年Excel函数基础

Excel 函数基础

第一节 函数的基本概念

Excel函数的作用是根据特定的计算要求，Excel进行一系列特定操作后，得到一个或一组计算结果，得到的计算结果称为函数值。函数可应用于各种计算公式中。

一、函数使用的基本格式

函数使用的基本格式：函数名（参数）

函数名由英文字母或单词或字母与单词的混合体组成，用于标识函数并在一定程度上说明功能的功能。

例如：

SUM（参数） ——求和； AVERAGE（参数）——求平均值； POWER（参数） ——求乘方； RATE（参数） ——求利率；；

SLN（参数） ——用直线法求折旧额； HLOOKUP（参数）——水平方向查找； VLOOKUP（参数）——垂直方向查找； PI（） ——求圆周率л的值。

函数参数用于说明函数在执行的过程中所需要的数据、或数据所在的单元格区域，具体要求等，函数的参数是紧跟在函数名后且由一对圆括号括起来。

例如：

MATCH(27,A2:F2) ——查找A2：F2区域中是否有值为27的单元格

MATCH(27,A2:F2，1) ——查找A2：F2区域中值为27或如果没有值为27的单元格，则

找值小于27的最大值的单元格。

EXCEL函数的参数比较灵活，可以以多种方式给定：

（1）Excel函数的参数可以是常量，单元格引用，单元格区域引用，也可以是另一个函数的值。

例如：

=SUM(3,5,6) —— 参数为常量；

=SUM(D35,F35) ——参数为单元格引用；

=SUM(D35:F35) ——参数为单元格区域引用；

=SUM(AVERAGE(D34:F34),AVERAGE(D35,F35)) ——参数为其他函数为值（函数嵌套）。 在函数嵌套的情况下，excel先计算出内层函数的值，再以内层函数的值作为外层函数的参数计算外层函数的值。

（2）Excel中有一些特殊的函数，在使用时不需要参数，例如计算圆周率的函数PI( ) ，在这种情况下，函数名后面的圆括号必须要有，这样excel才知道这是一个函数。

（3）有些函数对于参数的多少及顺序没有太多的要求，如SUM( )、AVERAGE( )等，可以有一个参数，也可以有多个参数，且哪个参数在前，哪个参数在后都没有关系。但在大多数函数对参数的个数、参数的顺序、参数的含义等都有严格的规定，必须按规定的顺序、规定的格式正确地给定每一个参数，才能得到函数的正确结果。因此，在学习excel的函数时，要正确掌握excel各个函数的参数代表的含义，参数的顺序及参数的格式才能得到正确的计算结果。

（4）有些函数的其中一些参数可以省略，例如，FV( )函数，在学习时应该掌握什么时候可以省略哪些参数，省略了的参数的默认值是多少。

二、excel函数的分类

根据函数的计算结果是一个或多个，可以分为单值函数和多值函数（也叫做数组函数）。一般的经常使用的函数都是单值函数，如sum( )函数。而有些函数的结果则是有多个计算结果，例如，计算逆矩阵的函数MINVERSE( )。

对于单值函数，其计算公式的输入和修改都比较简单，选定要输入或修改的单元格后，根据excel表达式的规则输入计算公式及函数，然后按回车键（Enter键）即可。

包含多值函数的计算公式也叫做数组公式，其计算公式的输入和修改则有所不同。方法如下：

1、根据函数及计算要求，选定要存放计算结果的单元格区域（一般是一个矩形区域），或选定要修改的数组公式所在的单元格区域；

2、输入计算公式或修改计算公式；

3、按组合Ctrl+Shift+Enter表示数组公式输入或修改完毕。组合键的输入方法：先用一只手按住Ctrl键和Shift键不放，然后用另一只手点击Enter键，最后松开Ctrl键和Shift键。

注意，不能单独清除一个单元格中的数组公式，必须删除数组公式所在的单元格区域中每一个单元格中的数据公式，清除方法是，先选定数组公式所在的区域，然后再清除。

第二节 常用函数

1、PI、EXP、LN等常用数值计算函数 （1）PI函数 格式：PI（）

功能：计算圆周率π的值

PI（）函数是一个无参数的函数，在使用中，必须在函数名后加一对圆括号，说明这是一个函数。

（2）EXP函数 格式：EXP( x )

功能：计算e的值

x可是一个常量，也可以将x的值先存放在一个单元格中，在函数中给出x的值所在单元格的引用即可。

（3）LN函数 格式：LN（x）

功能：计算以自然数e为底的x的对数。 （4）LOG函数

格式：LOG（x，BASE）

功能：计算以BASE为底的x的对数。 （5）SIN函数 格式：SIN（x）

功能：计算x弧度的正弦函数值 （6）COS函数

COS（x）计算x弧度的余弦函数值

x

对于三角函数，其参数x的单位为弧度，如果参数x的单位是以360度的角度值，在计算时需要将角度值转换为弧度。EXCEL中角度转换为弧度的基本公式为：

弧度＝角度*PI（）/180 （7）计算函数例题 =PI()*3^2

作用为计算半径为3的圆的面积。 =3.5+EXP(-2) 作用为计算3.5?e =SIN(45*PI()/180)

作用为计算45度的正弦函数值

2、SUM、AVERAGE、MIN、MAX函数 （1）SUM函数

格式：SUM（参数）

功能：求参数中所给数值数据之和 （2）AVERAGE函数

格式：AVERAGE（参数）

功能：求参数中所给数值数据的平均值 （3）MIN函数

格式：MIN（参数）

功能：求参数中所给数值数据的最小值 （4）MAX函数

格式：MAX（参数）

功能：求参数中所给数值数据的最大值

这几个函数中，参数可以1个，也可以是多个，参数之间不讲究顺序；参数可以是以常量的形式给定，也可以是单元格引用或单元格区域引用，也可以其他函数的计算结果；如果给定的参数中，包含了非数值数据，则非数值数据勿略不记。

例如：

表1：SUM等函数计算示例

?2

在这个例子中，参数使用的都是单元格区域引用，函数SUM（A1:C2）表示求A1:C2区域中所有数值数据之和，这个区域中，仅有3个数值数据：A1单元格中的12，C1单元格的1900-1-10（日期数据视为整数，自动转换为对应整数后再计算，1900-1-10对应整数10），C2单元格中的35，所以计算结果为57。

AVERAGE（A1:C2）表示求A1:C2中所有数值数据的平均值，从上面所述知道这个区域

中有3个数值数据，总和为57，所以平均值为19。

因为1900-1-10对就的整数是10，所以这个区域的最小的数值数据为10。

3、SMALL、LARGE函数 （1）SMALL函数

格式：SMALL（数据组，K） 功能：求数据组中第K个最小值 （2）LARGE函数

格式：LARGE（数据组，K） 功能：求数据组中第K个最大值

数据组一般是以单元格区域引用给定一组数据所在的单元格区域；数据组也可以以常量的形式给定，格式这：{数据1，数据2，??，数据n}。

例：

表2：LARGE、SMALL函数示例

在本例中，LARGE（B3：B17，3）得到数组B3：B17中，第3名的分数，因为成绩中有2个95分，占据了第一名和第二名，所以第三名的分数就是93。

4、RANK函数

格式：RANK（指定数据，数组，排序方式）

功能：确定“指定数据”在数组列表中的排位（名次），即“指定数据”是“数组”中的第几个最大数或第几个最小数。

“排序方式”只能取值为0或1。如果排序方式取值为0，则得到的排位（名次）是使用降序排序方式，即第1名的数值在数组中是最大的；如果排序方式取值为1，则得到的排位（名次）是使用降序排序方式，即第1名的数值最小。

该参数可以省略，如果在函数中省备了排序方式参数，则默认为该参数为0。

“数组”列表必须先存放在工作表的一个连续区域中，在应用该函数时，只能以单元格区域引用的形式给出该数组，不能使用常量数组。

“指定数据”可以是某个单元格中的数据，在应用该函数时可以使用该数据所在单元格的引用，也可以以常量的形式给定该数据。

如果指定的数据不是给定数组中的一员，则得不到该数据在该数组中的排位，这时excel将给出错误信息——#N/A。

例：

其中，C3单元格中的计算公式 =RANK(C3,$C$3:$C$18,0)的含义是计算C3单元格中的数据（陈曦阳的平均成绩）在数组C3：C18（平均成绩数组）中的排名。

在本例中，由于学生的成绩排名是按从高到低的顺序（降序），所以就该函数的第3个参数为0，当然也可以省略该参数。

由于每个学生的平均成绩依次排在C3：C18单元格区域中，所以要计算第2个学生的平均成绩排名只需要将该公式中的C3改为C4即可，这可以利用excel相对引用在公式复制时自动变化的规律直接将上一行公式复制到下一行就可得到。但排名的范围还应该是C3：C18，为了使上一行的公式复制到下一行时，平均成绩数组的范围不发生变化，对平均成绩数组就必须使用单元格区域的绝对引用。这样只需要输入计算第一个学生平均成绩排名的公式，其余学生的排名计算公式通过“复制”、“粘贴”操作就可得到，节省了公式输入，也能有效地减少输入错误。

从排序结果可以看出，有两个学生的平均成绩均是82分，排名并列第10，这样就没有第11名了。

5、COUNT、COUNTA、COUNTBLANK函数

篇二：Excel函数入门

第 1 章 函数应用基础

在本章中，将首先讲解关于 Excel 函数的基础知识。主要包括如何使用公式和函数，

以及在使用公式和函数过程中，需要注意的内容。本章是本书后面章节的基础，希望用户 能够通过本章的知识，能够对公式和函数的内容有个整体的了解。

1.1 公式基础

公式和函数是 Excel 处理数据的重要工具，当用户在使用 Excel 处理和分析问题的时 候，当涉及到各种运算时，一般会需要用到公式和函数。本小节首先讲解关于公式和函数 的基本内容，对其使用过程中的问题加以分析。

1.1.1 什么是公式

公式是用户自行定义的计算过程，在 Excel 中以输入“＝”开始，来输入公式（或者 函数），如图 1.1 所示。

图 1.1 开始输入公式

从上图中可以看出，当用户在单元格中开始输入“=”以后，Excel 就会准备接收后面 输入的公式或者函数。在标题栏的左侧会显示“SUM”函数名称，提示用户输入函数或者 公式。在输入公式时，用户除了输入“=”之外，还需要输入一些运算符号，进行运算。 在实际应用中，四则运算是最常见的运算符号，也就是说，+、—、×和÷。用户在 输入等号之后，使用上面的运算符号，开始数值运算。在单元格 A5 中输入公式 “=A1+A2+A3”，表示计算 A1～A3 中单元格数值的和，如图 1.2 所示。

图 1.2输入公式

在公式中，用户除了可以引用单元格的数值之外，还可以直接输入具体的数值。例如，

需要在 Excel 中计算“890×653”的数值，可以在单元格中直接输入“=890*653”，得到的 结果如图 1.3 所示。

图 1.3 直接计算数值运算结果

说明：在公式中，当用户输入单元格作为参数的时候，实际上是使用对单元格的引用。引用是 Excel

中十分重要的概念，在后面的章节中将详细讲解。

1.1.2 切换公式

从小节中的例子可以看出，公式的功能十分强大，几乎可以完成常见的各种运算。但 是，这同样带来一个问题：当公式中的参数比较多的时候，将很难跟踪公式的运算过程。 当用户需要查看公式的具体运算时，可以双击包含公式的单元格，如图 1.4 所示。

图 1.4显示公式的表达式

笔者在这里提供一个快捷方式，按下“Ctrl”＋“～”快捷键，可以显示单元格中的

公式表达式，如图 1.5 所示。

图 1.5 使用快捷方式

从上图中可以看出，当用户按下“Ctrl”＋“～”快捷键，就可以在单元格中显示具 体的公式。再次按下“Ctrl”＋“～”快捷键，就可以恢复原来的计算结果。

1.1.3 公式的引用方式

在前面小节中，用户已经接触过引用的概念。在 Excel 中，用户可以在公式或者函数 中，引用本工作表中的数据，也可以引用不同工作表上或者其他工作薄中的数据。在 EXCEL 中有多种引用的方式，一般可以分为 A1 引用方式和 R1C1 引用方式，也可以分为相对引 用和绝对引用方式。

在默认的情况下，EXCEL 中的引用方式是 A1 引用方式。在这种引用中，用字母和数 字标示单元格。如果引用的是单元格区域，那么可以使用区域的左上角的单元格的标示符、 冒号和区域的右下角的单元格的标示符来共同标识。表 1.1 中列出了常用的几种引用格式 和对应的引用区域。

表.1.1

A1样式引用对照表

EXCEL

选项的设置修改引用方式。选择“Office 按钮”|“Excel 选项”命令，打开“Excel 选项” 对话框，选择“公式”选项卡，然后在“使用公式”选框下面的“R1C1 引用样式”，如 图 1.6 所示。

图 1.6 修改引用方式

在 R1C1 引用样式中，R 代表行数字，C 代表列数字，用行和列数字共同指示单元格 的位置。

1.1.4 相对引用和绝对引用

在 EXCEL 中，默认情况下公式使用的是相对引用，“相对”是指公式计算的单元格和 引用数据的单元格中的相对位置。当用户在 EXCEL 中复制公式时，复制的结果也会采用 相对引用方式，如图 1.7 所示。

图 1.7 使用相对引用

从上面的结果中，用户可以看出，当用户复制 A5 中的单元格到 B5 单元格时，复制 的是相对引用，A5 中的“=A1+A2+A3”会转换为“=B1+B2+B3”。当用户在使用 EXCEL 时，有时可能不希望使用相对引用，而需要使用绝对引用，也就是说，公式处理的是单元 格的精确地址。在 A1 样式中，使用绝对引用的方法是在行号和列标前面加上“$”符号， R1C1 引用样式中，直接在 R 和 C 后面接上行号和列号就可，如图 1.8 所示。

图 1.8 使用绝对引用

当用户使用绝对引用后，用户复制单元格中的公式时，Excel 会复制绝对引用，结果 如图 1.9 所示。

篇三：EXCEL函数公式大全

excel常用函数公式及技巧搜集（常用的）

【身份证信息？提取】

从身份证号码中提取出生年月日

=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0

=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1

=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)

显示格式均为yyyy-m-d。（最简单的公式，把单元格设置为日期格式）

=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2))

显示格式为yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-” 换成”/”即可）

=IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日

"),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日"))))

显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd）

=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6))

显示格式为yyyymmdd。

=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0

=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))

=MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日"

=IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))

从身份证号码中提取出性别

=IF(MOD(MID(A1,15,3),2),"男","女")（最简单公式）

=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女")

=IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)

=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女")

从身份证号码中进行年龄判断

=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)

=DATEDIF（A1，TODAY（），“Y”）

（以上公式会判断是否已过生日而自动增减一岁）

=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900

=YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4))

=YEAR(TODAY())-VALUE(MID(B1,7,4))&"岁"

=YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄

=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")

以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式

=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")

按身份证号分男女年龄段

按身份证号分男女年龄段，身份证号在K列，年龄段在J列（身份证号为18位）

男性16周岁以下为1

男性16周岁（含16周岁）以上至50周岁为 2

男性50周岁（含50周岁）以上至60周岁为 3

男性60周岁（含60周岁）以上为4

女性16周岁以下为1

女性16周岁（含16周岁）以上至45周岁为 2

女性45周岁（含45周岁）以上至55周岁为 3

女性55周岁（含55周岁）以上为4

=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))

=SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【年龄和工龄计算】

根据出生年月计算年龄

＝DATEDIF(A1,TODAY(),"y")

＝DATEDIF(A1,TODAY(),"y")&"周岁"

＝DATEDIF(A1,NOW(),"y")

根据出生年月推算生肖

中国人有12生肖，属什么可以推算出来。即用诞生年份除以12，再用除不尽的余数对照如下：0→猴，1→鸡，2→狗，3→猪，4→鼠，5→牛，6→虎，7→兔，8→龙，9→蛇，10→马，11→羊例如：XXX出生于1921年，即用1921年除以12，商得数为160，余数为1，对照上面得知余数1对应生肖是鸡，XXX就属鸡。

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(A2),12)+1,1)（2007）

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

=DATEDIF(起始日期,结束日期,"Y")

计算距离退休年龄的公式

=IF(E2="","",IF(E2>=V2,"已经退休","距离退休还有

"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Y")&"年

"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"YM")&"个月

"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),"Md")&"天"))

其中E2为年龄（可用身份证号码的公式生成）；

V2为法定退休年龄（男60，女50）公式为：=IF(D2="","",IF(D2="男",60,50))

D2为男或女（可用身份证号码的公式生成）；U2为出生年月日（可用身份证号码的公式生成）。 求工齡

=DATEDIF(B2,TODAY(),"y")

=DATEDIF(B2,TODAY(),"ym")

=DATEDIF(B2,TODAY(),"md")

=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月

"&DATEDIF(B2,TODAY(),"md")&"日"

计算工龄

=DATEDIF(C6,C8,"y")求两日期间的年数

=DATEDIF(C6,C8,"ym")求两日期间除去整年数剩余的月数

=DATEDIF(C6,C8,"m")求两日期间的总月数

如果只需要算出周年的话，可以用=datedif("1978-8","2006-5","Y")

年龄及工龄计算

有出生年月如何求年龄？

有工作时间如何求工龄？（求出的结果为多少年另几个月，如：0303的形式，即3年零3个月）。

a1是出生年月或工作时间：

=datedif(a1,today(),"y")

=text(datedif(a1,today(),"y"),"00")&text(datedif(a1,today(),"m"),"00")

如 [B2]=1964-9-1 则:

=TEXT(DATEDIF(B2,TODAY(),"y"),"00")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12),"00")'显示 4009

=TEXT(DATEDIF(B2,TODAY(),"y"),"00年

")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12),"00月") '显示 40年09月

如果你找不到 DATEDIF 函数，也可以不用 DATEDIF 函数,

如 [B2]=1964-9-1 则:

=TEXT(RIGHT(YEAR(NOW()-B2),2),"00")&TEXT(MOD(MONTH(NOW()-B2)-1,12),"00")'显示 4009

=TEXT(RIGHT(YEAR(NOW()-B2),2)&"年"&MOD(MONTH(NOW()-B2)-1,12)&"个月

","") '显示 40年09个月

自动算出工龄日期格式为(yyyy.mm.dd)

能否用：（yyyy.mm.dd）这种格式来计算出工龄有多长呢~？

以前用这样一段（=TEXT(RIGHT(YEAR(NOW()-A1),2)&"年"&MOD(MONTH(NOW()-A1)-1,12)&"个月","") ）。

但这种方法只能用：（yyyy-mm-dd）这样的日期格式才能实现！

你不妨把“.”替换成“-”，不就行了吗，再说后者是日期的一种标准格式， =TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,".","-")),2)&"年

"&MOD(MONTH(NOW()-SUBSTITUTE(A1,".","-"))-1,12)&"个月","") ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【时间和日期应用】

自动显示当前日期公式

=YEAR(NOW()) 当前年

=MONTH(NOW()) 当前月

=DAY((NOW())) 当前日

如何在单元格中自动填入当前日期

Ctrl+;

如何判断某日是否星期天

=WEEKDAY(A2,2)

=TEXT(A1,"aaaa")

=MOD(A1,7)<2

某个日期是星期几

比如2007年2月9日，在一单元格内显示星期几。

=TEXT(A1,"aaa") （五）

=TEXT(A1,"aaaa") （星期五）

=TEXT(A1,"ddd") （Fri）

《excel函数基础知识》由：免费论文网互联网用户整理提供；
链接地址：http://www.csmayi.cn/meiwen/24939.html
转载请保留,谢谢!

• 上一篇：coreldraw,x4字体包
• 下一篇：三菱笔圆珠笔