【excel 使用函数技巧】excel常用函数

DAVERAGE 返回选定数据库项的平均值

DCOUNT 计算数据库中包含数字的单元格个数

DCOUNTA 计算数据库中非空单元格的个数

DGET 从数据库中提取满足指定条件的单个记录

DPRODUCT 将数据库中满足条件的记录的特定字段中的数值相乘

DSUM 对数据库中满足条件的记录的字段列中的数字求和

GETPIVOTDATA 返回存储于数据透视表中的数据

日期和时间函数

DATE 返回特定日期的序列号

DATEVALUE 将文本格式的日期转换为序列号

DAY 将序列号转换为月份中的日

EDATE 返回在开始日期之前或之后指定月数的日期的序列号

MONTH 将序列号转换为月

NOW 返回当前日期和时间的序列号

TIMEVALUE 将文本格式的时间转换为序列号

TODAY 返回今天日期的序列号

WEEKDAY 将序列号转换为星期几

WEEKNUM 将序列号转换为一年中相应的周数

逻辑函数 IF 指定要执行的逻辑检测

数学和三角函数

CEILING 将数字舍入为最接近的整数，或最接近的有效数字的倍数 ROUND 将数字舍入到指定位数

SUBTOTAL 返回数据库列表或数据库中的分类汇总

SUMIF 按给定条件将指定单元格求和

SUMPRODUCT 返回相对应的数组部分的乘积和

TRUNC 将数字截尾取整

信息函数

CELL 返回有关单元格格式、位置或内容的信息

ISBLANK 如果值为空，则返回 TRUE

ISNA 如果值为 #N/A 错误值，则返回 TRUE

查找和引用函数

ADDRESS 以文本形式返回对工作表中某个单元格的引用

CHOOSE 从值的列表中选择一个值

HLOOKUP 在数组的首行查找并返回指定单元格的值

LOOKUP 在向量或数组中查找值

TRANSPOSE 返回数组的转置

VLOOKUP 在数组第一列中查找，然后在行之间移动以返回单元格的值 统计函数

CORREL 返回两个数据集之间的相关系数

COUNT 计算参数列表中数字的个数

COUNTA 计算参数列表中值的个数

COUNTBLANK 计算区间内的空白单元格个数

COUNTIF 计算满足给定标准的区间内的非空单元格的个数

MODE 返回数据集中出现最多的值

PERCENTRANK 返回数据集中值的百分比排位

文本和数据函数

ASC 将字符串内的全角（双字节）英文字母或片假名更改为半角（单字节）字符

CONCATENATE 将若干文本项合并到一个文本项中

EXACT 检查两个文本值是否完全相同

FIND 在一文本值内查找另一文本值（区分大小写）

FIXED 将数字设置为具有固定小数位的文本格式

LEFT 返回文本值最左边的字符

LEN 返回文本字符串中的字符个数

LOWER 将文本转换为小写形式

MID 从文本字符串中的指定位置起返回特定个数的字符

REPLACE 替换文本内的字符

RIGHT 返回文本值最右边的字符

SEARCH 在一文本值中查找另一文本值（不区分大小写）

SUBSTITUTE 在文本字符串中以新文本替换旧文本

TEXT 设置数字的格式并将数字转换为文本

TRIM 删除文本中的空格

UPPER 将文本转换为大写形式

A

1 数据

2 Ceramic Insulators #124-TD45-87

3 Copper Coils #12-671-6772

4 Variable Resistors #116010

公式 说明（结果）

=MID(A2,1,FIND(" #",A2,1)-1) 在上面第一个字符串中，抽取从第一个字符到“#”的文本 (Ceramic Insulators)

=MID(A3,1,FIND(" #",A3,1)-1) 在上面第二个字符串中，抽取从第一个字符到“#”的文本 (Copper Coils)

=MID(A4,1,FIND(" #",A4,1)-1) 在上面第三个字符串中，抽取从第一个字符到“#”的文本 (Variable Resistors)

A

1 数据

2 Statements

3 Profit Margin

4 margin

公式 说明（结果）

=SEARCH("e",A2,6) 上面第一个字符串中的第一个 e 出现的位置，开始于第六个位置 (7)

=SEARCH(A4,A3) margin 在 Profit Margin 中的位置 (8)

=REPLACE(A3,SEARCH(A4,A3),6,"Amount") 用 Amount 替换 Margin

常用函数及技巧详解

1. 求平均值.

函数名称：AVERAGE

主要功能：求出所有参数的算术平均值。

使用格式：AVERAGE(number1,number2,……)

参数说明：number1,number2,……：需要求平均值的数值或引用单元格（区域），参数不能超过30个。

应用举例：如在B8单元格中输入公式：=AVERAGE(B7:D7)，即可求出B7至D7区域值的平均值.

也可求出指定区域的平均值，如：在B8单元格中输入公式：

=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。

特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。

2. 求一组数据的和

函数名称：SUM

主要功能：计算所有参数数值的和。

使用格式：SUM （Number1,Number2……）

参数说明：Number1、Number2……代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。

应用举例：如在D64单元格中输入公式：=SUM(D2:D63)，确认后即可求出D 列中2---63格中的和。

特别提醒：如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：=SUM(LARGE(D2:D63,{1,2,3,4,5}))，则可以求出前5名成绩的和。

3、求出部分指定数据的和

函数名称：SUMIF

主要功能：计算符合指定条件的单元格区域内的数值和。

使用格式：SUMIF （Range,Criteria,Sum_Range）

参数说明：Range 代表条件判断的单元格区域；Criteria 为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。

应用举例：如，在D64单元格中输入公式：=SUMIF(B3:B500,"七1",E3:E500) .即：可求出在B3—B500中（为班名）E3—E500(某科目) 的和，另外输入： =SUMIF(B3:B500,"七1",E3:E500)/R17，（R17代表人数栏），确认后即可求出“七1”班的某科成绩的平均值（班平均分）。

补充：如果把上述公式修改为：=SUMIF(C2:C63,"女",D2:D63)，即可求出“女”生的语文成绩和。特别提醒：其中的“男”和“女”由于是文本型的，需要放在英文状态下的双引号（" 男" 、" 女" ）中。

4. 对一组数据进行自动排名

函数名称：RANK

主要功能：返回某一数值在一列数值中的相对于其他数值的排位。

使用格式：RANK （Number,ref,order ）

参数说明：Number 代表需要排序的数值；ref 代表排序数值所处的单元格区域；order 代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排

名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。

应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出某位同学的成绩在B2---B31中的排名结果。

特别提醒：在上述公式中，我们让Number 参数采取了相对引用形式，而让ref 参数采取了绝对引用形式（增加了一个绝对引用“$”符号），这样设置后，选中C2单元格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按住左键向下拖拉，即可将上述公式快速复制到C 列下面的单元格中，完成其他同学的排名统计。

5. 求出一组数中的最大值和最小值

函数名称：MAX （求最大值）

主要功能：求出一组数中的最大值。

使用格式：MAX(number1,number2……)

参数说明：number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个。

应用举例：输入公式：=MAX(E4:E100)，确认后即可显示出E4至E100单元中的最大值。

特别提醒：如如果参数中有文本或逻辑值，则忽略

MIN 函数 （求最小值）

函数名称：MIN

主要功能：求出一组数中的最小值。

使用格式：MIN(number1,number2……)

参数说明：number1,number2……代表需要求最小值的数值或引用单元格（区域），参数不超过30个。

应用举例：输入公式：=MAX(E4:E100)，确认后即可显示出E4至E100单元中的最小值特别提醒：如果参数中有文本或逻辑值，则忽略

6、根据给定的条件，返回相对应的指定内容

函数名称：IF

主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式：=IF(Logical,Value_if_true,Value_if_false)

参数说明：Logical 代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE ）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE ）”时的显示内容，如果忽略返回“FALSE”。

应用举例：在D26单元格中输入公式：=IF(C26>=60,"及格"," 不及格") ，确认以后，如果C26单元格中的数值大于或等于60，则D26单元格显示" 及格" 字样，反之显示“不及格”字样。也可根据实际可多设几档条件和内容，如：=IF（A3

特别提醒：1. 公式中是文字的引号要用英文状态输入.

2. 括号要对称（正反一样多）

7、在表中统计符合指定条件的人数（单元格数目）

函数名称：COUNTIF

主要功能：统计某个单元格区域中符合指定条件的单元格数目。

使用格式：COUNTIF(Range,Criteria)

参数说明：Range 代表要统计的单元格区域；Criteria 表示指定的条件表达式。 应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,">=80")，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。 特别提醒：允许引用的单元格区域中有空白单元格出现

8、巧用函数组合进行多条件的计数统计（原创）

举例说明：第一行为表头，A 列是“姓名”，B 列是“班级”，C 列是“语文成绩”，现在要统计“班级”为“2”，“语文成绩”大于等于100的人数。统计结果存放在本工作表的其他列。

公式如下：

=SUM(IF((B2:B500="2")*(C2:C500>=100),1,0))

输入完公式后按Ctrl+Shift+Enter键, 让它自动加上数组公式符号"{ }"。

另：如果只求某一分数段的人数，假如B 列是学生的语文成绩，则求从B2:B56这一范围内分数在80-90之间的人数，则可以用这样一个公式:=COUNTIF (B2:B56,">=80")-COUNTIF (B2:B56,">=90") 输入公式回车即可。 注意：函数COUNTIF 是条件统计函数，只能对一个条件进行限制，如果判断条件有两个以上，则不能用。另外，公式中的各种符号都是在英文状态下输入的。 还有一个办法：求出一个数目，然后采用递减的办法，=COUNTIF (B2:B56,">=80")-A2 - …….)

9、核对两列数据或文本是否一样

举例说明：比较A 列和B 列是否相同

方法一：在c 列输入：

=IF(a1=b1,"一样"," 不一样")

即：相同的显示“一样”，不相同的显示“不一样”.

方法二：（把B 列与A 列不同之处标识出来）

（1）、如果是要求A 、B 两列的同一行数据相比较：

假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为: “单元格数值” “不等于” =B2

点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。

用格式刷将A2单元格的条件格式向下复制。

B 列可参照此方法设置。不同的自动显示为红色.

（2）、如果是A 列与B 列整体比较（即相同数据不在同一行）：

假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为: “公式”=COUNTIF(B:B,A2)=0

点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。

用格式刷将A2单元格的条件格式向下复制。

B 列可参照此方法设置。注：实际用时要将被用来比较的复制到相邻两列来，整体设条件，按第一格设.

按以上方法设置后，AB 列均有的数据不着色，A 列有B 列无或者B 列有A 列无的数据标记为红色字体。

提示：如果AB 两列整体区别时，先要用鼠标点击成为整体选中再设置.

10、跨表调取数据方法 (原创)

用VLOOKUP 函数

主要功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。

这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列……。假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列……。

语法： VLOOKUP （查找值，区域，列序号，逻辑值）

“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。 “区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。

“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP 返回错误值 #REF!。

“逻辑值”：为TRUE 或FALSE 。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE ，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。另外：

·如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。

·如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE ，函数 VLOOKUP 返回错误值 #N/A。

下面用示意图来说明VLOOKUP 函数的使用方法。如：

“=VLOOKUP(A10,模拟1!$C$3:$K$500,3,0)”

“A10”表示要查找的对象（在表2中）.

“模拟1!”表示被引用表的名称

“$C$3:$K$500”表示被引用表的要引用的区域，“C3”表示要引用的第一格，“K500”表示要引用的最后一格.

“$”表示绝对引用，是锁定的意思，没有为不锁定，复制时要变化。

“3”表示要引用区域的第3列数据，(如例中的从C 开始为1).

“0”表示准确查找，如果是“1”表示近似查找.

11. 自动给出当时日期

函数名称：TODAY

主要功能：给出系统日期。

使用格式：TODAY()

参数说明：该函数不需要参数。

应用举例：输入公式：=TODAY()，确认后即刻显示出系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。 特别提醒：显示出来的日期格式，可以通过单元格格式进行重新设置

12. 其它一些常用的但又特殊的方法

（一）如何在EXCEL 中设置单元格编辑权限(保护部分单元格)

1、先选定所有单元格, 点" 格式"->"单元格"->"保护", 取消" 锁定" 前面的"√"。

2、再选定你要保护的单元格, 点" 格式"->"单元格"->"保护", 在" 锁定" 前面打上"√"。

3、点" 工具"->"保护"->"保护工作表", 输入两次密码, 点两次" 确定" 即可。

（二）在EXCEL 中建立下拉列表按钮

选定你要设置下拉列表的单元格，点“数据”->“有效性”->“设置”，在“允许”下面选择“序列”，在“来源”框中输入你的下拉列表内容，各项之间用半角逗号隔开，如： A,B,C,D

选中“提供下拉前头”，点“确定”。

（三）自动从学生的身份证号“提取”出生日期和性别。

第一步：转换身份证号码格式

选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。

第二步：“提取出”出生日期

将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。然后输入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，你会发现在C2单元格内已经出现了该学生的出生日期。然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。

还可以提取出“性别”：判断性别“男女”

选中“性别”列的单元格，如D2。输入

“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女"," 男")”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入

（四）解释：单元格的相对引用、绝对引用和混合引用

相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。

具体情况举例说明：

1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2

当将公式复制到D1单元格时变为：=B1+C1

2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1

当将公式复制到C2单元格时仍为：=$A$1+$B$1

当将公式复制到D1单元格时仍为：=$A$1+$B$1

3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1

当将公式复制到C2单元格时变为：=$A2+B$1

当将公式复制到D1单元格时变为：=$A1+C$1

规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。

注意：工作薄和工作表都是绝对引用，没有相对引用。

技巧：在输入单元格地址后可以按F4键切换“绝对引用”、“混合引用”和“相对引用”状态

（五）出错自动提示设置方法1．使用“数据有效性” 选定我们需要检查的数据，如“B1:E20”，单击“数据→有效性”，出现“数据有效性”对话框。在“允许”列表框处选择“自定义”，“公式”文本框中输入如下公式：“=COUNTIF($B$1:$E$20,B1)=1”。单击“出错警告”标签，勾选“输入无效数据时显示出错警告”，“样式”列表处选择“停止”，右侧为出错提示信息，在“标题”处输入“数据重复”，“错误信息”处输入“数据重复，请重新输入 ，当我们在B1:E20区域中的D7单元格中输入“张三”后，因为与同在该区域的B4单元格内容重复，所以立刻会弹出一个警告对话框，阻止重复输入的数据。只有取消或更换输入的内容后，输入操作才得到允许 方法

2．使用“条件格式” 选定我们需要检查的数据区域，如A1:E20，单击“格式→条件格式”，出现“条件格式”对话框，“条件1”列表框处选择“公式”，在右侧的文本框中输入如下公式“=COUNTIF($A$1:$E$20,E20)>1”。单击[格式]按钮，在“单元格格式”对话框中选择出现重复数据单元格的外观格式，如字体、边框和图案(本例中设置为浅绿色背景) 。 当我们在A1:E20区域中的C10单元格中输入“张三”后，该区域中包含相同内容的C10和A6单元格底纹颜色立刻变为浅绿色

（六）保护表格中的数据

如选取单元格A1∶F8，点击“格式→单元格”选单，选择“保护”选项，消除锁定复选框前的对勾，单击确定。然后，再选取单元格F4∶F7和 B8∶F8，点击“格式→单元格”选单，选择“保护”选项，使锁定复选框选中，单击确定，这样，就把这些单元格锁定了。接着，点击“工具→保护→保护工作表”选单，这时，会要求你输入密码，输入两次相同的密码后，点击确定，工作表就被保护起来了，单元格的锁定也就生效了。今后，可以放心地输入数据而不必担心破坏公式。如果要修改公式，则点击“工具→保护→撤消保护工作表”选单，这时，会要求你输入密码，输入正确的密码后，就可任意修改公式了。