EXCEL常用函数的应用技巧与方法(学习版)

（一）数学函数

求和：SUM() 条件求和：SUMIF() 求个数：COUNT() COUNTA()
条件求个数：COUNTIF() 求算术平均数：AVERAGE() 四舍五入函数：ROUND()

排位函数：RANK()
求和：SUM() SUM(数值1,数值2,…) 例： A1=6 A2=7 A3=8 =SUM(A1:A3) =6+7+8=21
快速汇总
对复杂的数据进行快速求个数、求和、求平均值等。 快速整理数据 在数据投诉表中可以任意拖拉行/列标签，快速整理得到需求的报表。
excel常用操作
excel常用函数
1
函数的相关概念
2
常用函数的应用
公式中的运算符
算术运算符：加（+）减（-）乘（*）除（ /）百分号（%） 比较运算符：等于（=）小于（<）大于（>）小于等于（<=）
0-降序
=RANK(A1,A1:A4,1) =1 =RANK(A1,A1:A4,0) =4 =RANK(A1,A1:C4,1) =5
（二）
逻辑函数

条件函数：IF() 且函数：AND() 或函数：OR() 反函数：NOT()
条件函数：IF() IF(条件,执行条件真,执行条件假) * 可执行七层嵌套 例 A1=5 A2=3 A3=6 A4=7
COUNTA()
求单元格中为数值的个数： COUNT(数值1,数值2,…) 求单元格中为字符的个数：COUNTA(字符1,字符2,…) 例
A 1 2 3 4 5 A产品 100 200 300 400 B C产品 20 40 60 80 C D产品 1000 1500 2000 2500
=COUNT(A1:C4) =12
七、条件格式
通过条件格式，可以让符合条件的数据以不同的格式显示出来，便于查看。 1、对满足条件的数据进行特殊标示。 如：自动更改字体颜色，自动标示数据底纹。
2、在条件格式中运用公式，隔行标示。 如：自动间隔一行标示底纹。
八、锁定/隐藏
锁定 将符合条件的数据锁定，可以防止修改。
隐藏 将符合条件的数据在编辑栏隐藏， *常用在隐藏公式，便于查看计算后的数据。
=IF(A1>A2,1,0) =1
=IF(A1<A2,1,0) =0 =IF(A1>A2,IF(A3>A4,8,9),1) =9
（三）：文本函数
截取函数：LEFT()、RIGHT()、MID() 计算字符长度：LEN() 合并字符函数：CONCATENATE() 在字符串中查找特定字符：FIND() 比较两个字符是否完全相符：EXACT() 将数值转化为文本：TEXT()
四、设置批注
批注：可以对单元格的数据添加文字说明
五、分列整理/合并
分列整理 可以将一列组合型字符，分开到若干列中保存。 例：
2009/7/3 12:25
按照“/”符号，可以快速分列得到年、月。
字符合并 将若干列字符合并到一列中保存。 例：
1 A 上海市 B 嘉定区
A1&B1= 上海市嘉定区
六、冻结窗体
求算术平均数：AVERAGE() AVERAGE(数值1,数值2,…) 例
A 1 2 100 200 B 20 40 C 1000 1500
3
4
300
400
60
80
2000
2500
=AVERAGE(A1,B2) =60
=AVERAGE(A1:A4) =250
四舍五入函数：ROUND() ROUND(数值,保留的小数位数) 例
=COUNTA(A1:C4) =15
条件求个数：COUNTIF() COUNTIF(范围,条件) 例
A 1 2 3 4 100 200 300 400 B 20 40 60 80 C 1000 1500 2000 2500
=COUNTIF(A1:A4，”<>200”) =3 =COUNTIF(A1:C4,”>=1000”) =4 =COUNTIF(A1:C4,”>=100”)-COUNTIF(A1:C4,”>2000”) =7
多重条件函数：if (if ()) IF(E3=100%,100,IF(E3<95%,-100,"")) 解析：
IF(E3=100%,100,参数)
当E3=100%时，则等于100，否则计算参数 IF(E3<95%,-100,"") 当E3<95%时，则等于-100，否则为空值 条件： 当E3=100%时，则等于100； 当E3<95%时，则等于-100。

将文本型字符转换为数值：VALUE()
截取函数：LEFT()、RIGHT()、MID()
LEFT(文本,截取长度)--从左边截取
例 =LEFT(“abcdefg”,3) =abc
RIGHT(文本,截取长度)--从右边截取 例 =RIGHT(“abcdefg”,3) =efg
MID(文本,开始位,截取长度)--从中间截取 例 =MID(“abcdefg”,2,3) =bcd
A 1 2 3 4 100 200 300 400 B 20 40 60 80 C 1000 1500 2000 2500
=SUMIF(A1:A4,”>=200”,B1:B4) =40+60+80=180 =SUMIF(A1:A4,”<300”,C1:C4) =1500+1000=2500
求个数：COUNT()
通过冻结窗体，让行/列始终在窗体中显示，便于查看数据。 冻结首行 把光标定位在工作表内，直接点击“冻结首行”即可完成， 这时首行就会始终在窗体中显示。 冻结拆分 1、对工作表进行拆分：把光标定位在需要冻结窗体的内侧， 然后点击“拆分”，完成窗体拆分。 2、冻结拆分窗体：直接点击“冻结拆分窗体”即可完成， 这时需要显示的行/列就会在窗体中始终显示。
连接符：& 如：1985&07=198507
半角引号：”” 常在函数中出现，用以引用或表示空值、空格 “”表示空值；” ”表示空格。 优先级顺序：算术运算符→字符运算符→比较运算符→逻辑函数符 (使用括号可确定运算顺序)
函数语法/结构 1、函数语法：由函数名+括号+参数组成
参数与参数之 间使用半角逗 号进行分隔
A 1 2 3 4 100 200 300 400 B 20 40 60 80 C 1000 1500 2000 2500
=ROUND(A1/A3,2) =0.33
排位函数：RANK() RANK(数值,范围,序别) 序别：1-升序 例
A 1 2 3 4 100 200 300 400 B 20 40 60 80 C 1000 1500 2000 2500
大于等于（>=）不等于（<>）
逻辑运算符：同时（and）或者(or)否定（not）常以函数形式出现 引用运算符：区域运算符（：）：(A1：M1)指A1到M1连续的数据区域 联合运算符（,）：常在函数中出现
通配符：*
如：*市*指定区域内所有带“市”的数据， 常用于“包含”函数
定位符：$
如：$A1,定位A列；A$1,定位第一行； $A$1定位A1单元格
注意：MATCH 函数返回“范围”中对象的位置，而不是数值本身。
（五）：其他函数 检查函数 isna () 取余函数mod () 引用函数 row ()


随机函数rand ()
取整函数 int () Tranc ()

检查函数 isna ()
用于判断对象是否为“#N/A”，常用于判断函数返回值是否为“#N/A” ISNA(对象) 对象----需要查找的文本或参数 例 =isna(A1)
九、数据有效性
限定字符 对列/行的输入的字符进行控制，仅能输入限定的字符。
防止重复 对列/行的输入的字符进行控制，防止重复输入。
限定范围 对数据区域的数据范围进行控制，防止错误输入。
十、数据透视表
数据透视表是一种特殊形式的表，它可以把源数据的行和 列进行互换后汇总并显示汇总结果。 * 特别适用于分析，组织复杂的数据。
注意事项 数据类型选择正确的格式 在英文输入状态下使用函数 函数运用准确、适用 复制公式，要只复制公式，不要复制单元格 输入或复制/粘贴公式之后切记敲“回车” 灵活运用清除内容、选择性粘贴
常用函数 （一） （二） （三） （四） （五） （六） 数学函数 逻辑函数 文本函数 查找与引用函数 其他函数 嵌套函数
=SUM(A1,A3 ) =6+8=14 鼠标拖拉法
选中存放求和结果的单元格，点击工具栏中的“∑”（自动 求和）按钮 用鼠标拖过需要求和的所有单元格，然后回车即可 注：如果需要求和的单元格是不连续的，则可按住Ctrl 键分别拖过它们。
条件求和：SUMIF() SUMIF(查找的范围，条件（即对象），要求和的范围) 例：
例： 求和函数：SUM（A1,B2,…) 。参数与参数之 间用逗号“,”隔开 2、Excel 函数结构: 单 一 结 构 也有一些函数是 没有参数的,如 ROW()
嵌 套 结 构
左右括号成 对出现
Excel的错误信息
序号 错误信息 原因 公式所产生的结果太长，该单元格容纳不下 日期或时间格式的单元格中出现负值 公式中出现被零除的现象
二、字符替换
excel不仅可以替换单元格中的字符，
而且可以替换公式中的字符。
*注意：使用公式的工作表谨慎使用字符替换。
三、选择性粘贴和倒置
选择性粘贴 1、可以将利用对自动筛选出来的数据复制、 Copy出需要的数据。 2、可以将利用公式或透视表处理出来的数据 转换成需要的数据格式。
倒置： 对工作表进行快速行/列转换。
若A1为#N/A
= True
若A不为#N/A = False
六、嵌套函数 多重条件函数：if (if ()) 判断函数：if (countif()) 并列及左右函数：if ( and/or()) 引用函数： vlookup( match()) 条件引用函数：if (vlookup ()) 判断引用函数：if ( isna (vlookup())) 多重函数综合运用：if (isna(vlookup (and/or ()))) 偶数行标色：mod (row())
判断函数：if (countif()) IF(COUNTIF(A:A,A2)>1,"重复","") 解析：
IF(参数>1,"重复",""))
当满足参数条件时，则显示“重复”，否则显示空值 参数：COUNTIF(A:A,A2) 计算A2在A列存在的个数。 条件： 判断A2是否重复。
并列及左右函数：if ( and/or()) IF(AND(B4=100%,C4=100%,D4=100%), "优", IF(AND(B4=100%,C4>97%,D4>97%), "良", IF(AND(OR(B4<100%,C4<97%,D4<97%),E4>95%),"一般","差"))) 解析：
（四）：查找函数

列引用函数：VLOOKUP()

行引用函数：HLOOKUP()

查找位置函数：match()
列引用函数：VLOOKUP() VLOOKUP(对象,范围,数值,FALSE) 对象----需要查找的文本 范围----至少包含对象及需要返回的值所在 的列的连续范围 数值----需要返回的值所在列的列数 FALSE----精确查找，FALAE常用“0”标示。 例 =VLOOKUP(A1,A:D,3,0) 若A:D区域中不存在A1 = #N/A 若A:D区域中存在A1且在第一行 =C1
EXCEL常用函数应用技巧及方法
工作薄
菜单
按钮 编辑栏 行 列 单元格
光标 数据区域
分类汇总表
数据源表
参数表
工作表
excel常用操作
excel常用函数
一、筛选
注意：首行 不要存在空 白单元格
自动筛选：可以将符合条件的数据在“源数据表”里筛选出来；
高级筛选：可以将符合条件的数据在新的“数据区域”里筛选出来。
查找位置函数：match() 返回在指定方式下与指定数值匹配的数组中元素的相 应位置(不是具体的单元格) MATCH(对象,范围,数值) 对象----需要查找的文本 范围----至少包含对象及需要返回的值所在 的列的连续范围 数值----表示函数MATCH 查找等于A 的第一个数值， 常用“0”表示。
1
2
#####！
#DIV/0!
3
4 5 6 7 8
#N/A
#NAME? #NULL! #NUM! #REF! #VALUE!
在函数或公式中没有可用数值时
在公式中出现excel不能识别的文本时 当试图为两个并不交叉的区域指定交叉点时 当函数或公式中某个数字有问题时 当单元格引用无效时 使用错误的参数或运算对象类型