Excel常用技巧(共50张)
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
❖ 在这个工作夹里面有许多工作纸，这些工作纸就是工作表。
“工作夹”
工作表
第4页，共50页。
4
Excel 工作(gōngzuò)界 面
名字框
行号
当前单元格
列号
工作表标签
第5页，共50页。
工作簿
公式栏
工作表
5
常用(chánɡ yònɡ)快捷键
❖ (1) 复制、粘贴、剪切选中区域
复制选定区域
CTRL+C
" ")
当条件为真时进入Index函数
)
当条件为假时返回空值
第24页，共50页。
24
7.6函数编写逻辑思路
例:工资打印切割公式
=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据
!$A$1:$G$349,(ROW()+4)/3,COLUMN())," "))
8、Excel公式(gōngshì)的常见错误信息
错误信息 #####！ #DIV/0!
#N/A #NAME? #NULL! #NUM!
#REF! #VALUE!
2009-6-16 =datedif(A6,B6,"yd")
显示结果 9 12
366 30 11 364
当单位代码为"YM"时,计算结果是两个日期间隔的月份数，不计相差年数。
如忽略年份后，开始日期必须大于结束日期，否则公式计算错误。
第17页，共50页。
17
7.3----数字计算函数（取整函数Round、Roundup、Rounddown）
Excel常用 内容 (chánɡ yònɡ) 第1页，共50页。
目录(mùlù)
1.基础知识 2.函数 3.数据有效性 4.条件格式 5.图表
第2页，共50页。
2
一、Excel 基础知识
第3页，共50页。
3
•工作(gōngzuò)簿与工作(gōngzuò)表
❖ 工作簿是Excel使用的文件架构，我们可以将它想象成是一个工作夹，
第8页，共50页。
8
2、EXCEL公式分类 1、函数(hánshù)分类 2、用好EXCEl自带的帮助，活用函数的关键是要具备清晰的程序逻辑思维。
第9页，共50页。
9
•3、EXCEL公式(gōngshì)表达式
公式特定引导符号 单元格地址
区域范围引用
=（B4+25）/ SUM（D5:F5）
数值型常量
Excel函数
运算操作符号
第10页，共50页。
10
4、EXCEL公式(gōngshì)运算符号
符号类型 算术运算
比较运算 文本运算 区域运算 交叉运算 联合运算
符号 +；-；*；/； ^
>;<;=;<>；>=；<=； & :
_(空格) ，（逗号）
举例 =6^2 =3+2*5 =5<2=false ="天龙"&"八部" =Sum(A1:A3) =sum(A1:B5 A4:D9)=sum(A4:B5) =rank(A1,(A1:A10，C1:C10))
!$A$1:$G$349,(ROW()+4)/3,COLUMN())," "))
3、解读如下：
第1个IF的条件参数
当条件为真时返回该值
=IF(MOD(ROW(),3)=1,
当条件为假时进入下一个IF
源数据!A$1,
IF(
MOD(ROW(),3)=2,
第2个IF的条件参数
INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN()),
❖ 注：将最好先打开工作簿，再编写跨表引用公式
第13页，共50页。
13
7、EXCEL常用函数
类型
函数 Sum Average
用途 求和函数 平均值函数
类型
数值
Max/Min sumproduct
最大/最小函数 数组求和函数
文本
统计 比较 逻辑
SUMIF Sumifs Count CountA countblank RANK LARGE
第7页，共50页。
பைடு நூலகம்
7
1、什么(shén me)是函数？什么(shén me)是公式？
序号 1 2 3
4
公式 =15*3+20*2 =A1*3+A2*2 =单价*数量
=SUM(A1*3,A2*2)
说明 包含常量运算的公式 包含单元格引用的公式 包含名称的公式
包含函数的公式
公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。 函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。
❖ 常用函数 VLOOKUP、SUMIF、COUNTIF、COLUMN、 ROW
语法 =VLOOKUP（需查找值，范围，相对列，false） =SUMIF(条件区域，条件，值区域） =COUNTIF(条件区域，条件） =COLUMN(目标列/单元格) =ROW(目标行/单元格)
用途
根据指定条件对若干单元格、区域或引用求和 统计某一区域中符合条件的单元格数目
2008-6-17
结束日期
公式
2009-6-16 =datedif(A1,B1,"y")
2009-6-16 =datedif(A2,B2,"m")
2009-6-16 =datedif(A3,B3,"d")
2009-6-16 =datedif(A4,B4,"md")
2009-6-16 =datedif(A5,B5,"ym")
1、IF函数的嵌套：IF(A1>1, SUM(B1:G1),"")
在这个公式(gōngshì)中，sum函数是2级函数，它是if函数的参数，(Excel2007允许64层的嵌套，2003只能7层)
2、复杂案例 =IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据
第11页，共50页。
11
• 5、EXCEL公式(gōngshì)的相对引用、绝对引用和混合引用
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
姓名 =mid(a1,11,3)
张三 李世民
注意：MID函数(hánshù)有3个参数，而LEFT、RIGHT只有2个参数
第15页，共50页。
15
7.2----日期与时间函数1
❖（1）取出当前系统时间/日期信息，无参数(cānshù)。NOW()、TODAY()。
❖（2）取得日期/时间的部分字段值（年份、月份、日数或小时），无参数。 YEAR()、MONTH()、DAY()、HOUR()
123
=roundup(A3,2)
123.46 =rounddown(A3,2)
123.45
=roundup(A3,-2)
1300 =rounddown(A3,-2)
1200
区别(qūbié)
第18页，共50页。
18
7.3----数字(shùzì)计算函数（取整函数 Int、Trunc）
数值
公式
3.2 =Int(a1)
数值
123.456 123.456 1234.56
Round--四舍五入
公式
结果
=round(A2,0) 123
=round(A3,2) 123.46
=round(A4,-2) 1200
Roundup--向上取整
Rounddown--向下取整
公式
结果
公式
结果
=roundup(A3,0)
124 =rounddown(A3,0)
1、引用其他工作表的数据 例： =sheet2!A1
=工作表名称！目标单元格
2、引用已打开的工作簿的数据 =[工作簿名称]工作表名称！目标单元格
例： =[Book2.xls]sheet1!$A$1
3、引用未打开的工作簿的数据 =‘文件路径[工作簿名称]工作表名称’！目标单元格
例： =‘D:\Excel案例[Book2.xls]sheet1’!$A$1
日期
年 =Year(a1)
2009-6-16
2009
月
日
=Month(a1) =Day(a1)
6
16
日期 =DATE(A1,B1,C1)
2009-6-16
第16页，共50页。
16
7.2----日期与时间函数2
❖ （3）计算两个(liǎnɡ ɡè)日期之间的天数、月数或年数之差。 Datedif(start_date，end_date，unit)
第22页，共50页。
22
7.6逻辑(luó jí)函数IF
IF用途：按指定的条件计算满足条件选项
A列 序号
1 2 3 4
B列 姓名 张三 李四 王五 贾六
C列 出勤 20 23 21 22
D列 =If(C1>=22,"全勤",C1) 备注 20 全勤 21 全勤
第23页，共50页。
23
7.6逻辑函数IF
第20页，共50页。
20
7.4--统计(tǒngjì)函数
❖ 求数据集的满足不同要求的数值的函数：
1、最大值MAX 2、最小值MIN 3、平均值AVERAGE 4、排名RANK
第21页，共50页。
21
7.5查找(chá zhǎo)引用函数
❖ 查询与引用函数可用来在数据数组或表格中查找特定数值，或查找某一单元格的引用。
粘贴选定区域
CTRL+V
剪切选定区域
CTRL+X
撤消最后一次操作 CTRL+Z
复制上行单元格 CTRL+D
复制左侧单元格 CTRL+R
重复上一步操作 CTRL+Y
❖ (2) 打印
显示“打印”对话框 CTRL+P
显示“打印”预览 CTRL+F2
第6页，共50页。
6
二、Excel 函数 (hánshù)
SMALL IF AND OR
IFERROR
条件求和 多条件求和
单元格统计函数 时间
排名 第K大的值 第K小的值 判断函数
且函数 或函数
错误判断
查找与 引用
以上函数(hánshù)基本能满足一般工作需求第14页。，共50页。
函数 len left
right
mid
CONCATENATE text value DAY COLUMN ROW LOKKUP
❖语法：SUM(number1，number2，...)。 ❖参数：Number1，number2，...为1到30（07以上版本254）个需要求和的数值、区域或引用(yǐnyòng)。
❖巧用：格式相同的多个工作表的同位置单元格求和。 若Sheet1!A1=1、Sheet1!A1=2、Sheet1!A1=3
unit代码
函数返回值
开始日期
"y"
时间段中的整年数
2000-6-15
"m" 时间段中的整月数
2008-6-15
"d" 时间段中的天数
2008-6-15
"md" 忽略日期中的年和月，计算天数差 2008-3-17
"ym" 忽略日期中的年和日，计算月数差 2008-6-17
"yd" 忽略日期中的年，计算天数差
3、混合引用：复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1 当将公式复制到D1单元格时变为：=$A1+C$1
不同的引用在拖动公式时具有不同的结果。 引用的转换快捷键：F4
第12页，共50页。
12
•6、EXCEL公式中的跨工作(gōngzuò)表引用
1、目的(mùdì)：按规律切割工资条
2、分析数据规律，确定公式组合
结果的第一行：永远是原数据中的第一行。
结果的第二行：结果第2行对应原数据第2行，结果第5行对应原数据第3行，第8对应第4，第11对应第5。
结果的第三行：永远为空。
这里面第二个最难算，找找看它们之间有什么规律。
第25页，共50页。
25
VLOKKUP HLOOKUP
MATCH INDEX offset
用途 计算文本长度 文本截取（左）
文本截取（右）
文本截取（指定）
文本合并 文本化 数值化 日期 列号 行号 查找 列查找 行查找 匹配 引用 偏移
14
7.1----文本处理函数（提取字符Left 、 Right 、Mid ）
身份证号 320102781001492
地区 =left(a1,6)
320102
出生日期 =mid(a1,7,6)
781001
性别 =right(a1,1)
2
为什么是3而不是2？ 为什么不用Right函数？
员工代码
0002337084张三 0000656246李世民
社保号 =left(a1,10)
0002337084 0000656246
-3.2 =Int(a1)
3.2 =Trunc(a1)
-3.2 =Trunc(a1)
结果 3 -4 3 3
说明 返回不大于3.2的最大整数3
备注
返回不大于-3.2的最大整数-4
返回截去小数部分后的整数3
返回截去小数部分后的整数-3 =ROUNDDOWN(a1,0)
第19页，共50页。
19
7.4----数字计算函数----sum
“工作夹”
工作表
第4页，共50页。
4
Excel 工作(gōngzuò)界 面
名字框
行号
当前单元格
列号
工作表标签
第5页，共50页。
工作簿
公式栏
工作表
5
常用(chánɡ yònɡ)快捷键
❖ (1) 复制、粘贴、剪切选中区域
复制选定区域
CTRL+C
" ")
当条件为真时进入Index函数
)
当条件为假时返回空值
第24页，共50页。
24
7.6函数编写逻辑思路
例:工资打印切割公式
=IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据
!$A$1:$G$349,(ROW()+4)/3,COLUMN())," "))
8、Excel公式(gōngshì)的常见错误信息
错误信息 #####！ #DIV/0!
#N/A #NAME? #NULL! #NUM!
#REF! #VALUE!
2009-6-16 =datedif(A6,B6,"yd")
显示结果 9 12
366 30 11 364
当单位代码为"YM"时,计算结果是两个日期间隔的月份数，不计相差年数。
如忽略年份后，开始日期必须大于结束日期，否则公式计算错误。
第17页，共50页。
17
7.3----数字计算函数（取整函数Round、Roundup、Rounddown）
Excel常用 内容 (chánɡ yònɡ) 第1页，共50页。
目录(mùlù)
1.基础知识 2.函数 3.数据有效性 4.条件格式 5.图表
第2页，共50页。
2
一、Excel 基础知识
第3页，共50页。
3
•工作(gōngzuò)簿与工作(gōngzuò)表
❖ 工作簿是Excel使用的文件架构，我们可以将它想象成是一个工作夹，
第8页，共50页。
8
2、EXCEL公式分类 1、函数(hánshù)分类 2、用好EXCEl自带的帮助，活用函数的关键是要具备清晰的程序逻辑思维。
第9页，共50页。
9
•3、EXCEL公式(gōngshì)表达式
公式特定引导符号 单元格地址
区域范围引用
=（B4+25）/ SUM（D5:F5）
数值型常量
Excel函数
运算操作符号
第10页，共50页。
10
4、EXCEL公式(gōngshì)运算符号
符号类型 算术运算
比较运算 文本运算 区域运算 交叉运算 联合运算
符号 +；-；*；/； ^
>;<;=;<>；>=；<=； & :
_(空格) ，（逗号）
举例 =6^2 =3+2*5 =5<2=false ="天龙"&"八部" =Sum(A1:A3) =sum(A1:B5 A4:D9)=sum(A4:B5) =rank(A1,(A1:A10，C1:C10))
!$A$1:$G$349,(ROW()+4)/3,COLUMN())," "))
3、解读如下：
第1个IF的条件参数
当条件为真时返回该值
=IF(MOD(ROW(),3)=1,
当条件为假时进入下一个IF
源数据!A$1,
IF(
MOD(ROW(),3)=2,
第2个IF的条件参数
INDEX(源数据!$A$1:$G$349,(ROW()+4)/3,COLUMN()),
❖ 注：将最好先打开工作簿，再编写跨表引用公式
第13页，共50页。
13
7、EXCEL常用函数
类型
函数 Sum Average
用途 求和函数 平均值函数
类型
数值
Max/Min sumproduct
最大/最小函数 数组求和函数
文本
统计 比较 逻辑
SUMIF Sumifs Count CountA countblank RANK LARGE
第7页，共50页。
பைடு நூலகம்
7
1、什么(shén me)是函数？什么(shén me)是公式？
序号 1 2 3
4
公式 =15*3+20*2 =A1*3+A2*2 =单价*数量
=SUM(A1*3,A2*2)
说明 包含常量运算的公式 包含单元格引用的公式 包含名称的公式
包含函数的公式
公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。 函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。
❖ 常用函数 VLOOKUP、SUMIF、COUNTIF、COLUMN、 ROW
语法 =VLOOKUP（需查找值，范围，相对列，false） =SUMIF(条件区域，条件，值区域） =COUNTIF(条件区域，条件） =COLUMN(目标列/单元格) =ROW(目标行/单元格)
用途
根据指定条件对若干单元格、区域或引用求和 统计某一区域中符合条件的单元格数目
2008-6-17
结束日期
公式
2009-6-16 =datedif(A1,B1,"y")
2009-6-16 =datedif(A2,B2,"m")
2009-6-16 =datedif(A3,B3,"d")
2009-6-16 =datedif(A4,B4,"md")
2009-6-16 =datedif(A5,B5,"ym")
1、IF函数的嵌套：IF(A1>1, SUM(B1:G1),"")
在这个公式(gōngshì)中，sum函数是2级函数，它是if函数的参数，(Excel2007允许64层的嵌套，2003只能7层)
2、复杂案例 =IF(MOD(ROW(),3)=1,源数据!A$1,IF(MOD(ROW(),3)=2,INDEX(源数据
第11页，共50页。
11
• 5、EXCEL公式(gōngshì)的相对引用、绝对引用和混合引用
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
姓名 =mid(a1,11,3)
张三 李世民
注意：MID函数(hánshù)有3个参数，而LEFT、RIGHT只有2个参数
第15页，共50页。
15
7.2----日期与时间函数1
❖（1）取出当前系统时间/日期信息，无参数(cānshù)。NOW()、TODAY()。
❖（2）取得日期/时间的部分字段值（年份、月份、日数或小时），无参数。 YEAR()、MONTH()、DAY()、HOUR()
123
=roundup(A3,2)
123.46 =rounddown(A3,2)
123.45
=roundup(A3,-2)
1300 =rounddown(A3,-2)
1200
区别(qūbié)
第18页，共50页。
18
7.3----数字(shùzì)计算函数（取整函数 Int、Trunc）
数值
公式
3.2 =Int(a1)
数值
123.456 123.456 1234.56
Round--四舍五入
公式
结果
=round(A2,0) 123
=round(A3,2) 123.46
=round(A4,-2) 1200
Roundup--向上取整
Rounddown--向下取整
公式
结果
公式
结果
=roundup(A3,0)
124 =rounddown(A3,0)
1、引用其他工作表的数据 例： =sheet2!A1
=工作表名称！目标单元格
2、引用已打开的工作簿的数据 =[工作簿名称]工作表名称！目标单元格
例： =[Book2.xls]sheet1!$A$1
3、引用未打开的工作簿的数据 =‘文件路径[工作簿名称]工作表名称’！目标单元格
例： =‘D:\Excel案例[Book2.xls]sheet1’!$A$1
日期
年 =Year(a1)
2009-6-16
2009
月
日
=Month(a1) =Day(a1)
6
16
日期 =DATE(A1,B1,C1)
2009-6-16
第16页，共50页。
16
7.2----日期与时间函数2
❖ （3）计算两个(liǎnɡ ɡè)日期之间的天数、月数或年数之差。 Datedif(start_date，end_date，unit)
第22页，共50页。
22
7.6逻辑(luó jí)函数IF
IF用途：按指定的条件计算满足条件选项
A列 序号
1 2 3 4
B列 姓名 张三 李四 王五 贾六
C列 出勤 20 23 21 22
D列 =If(C1>=22,"全勤",C1) 备注 20 全勤 21 全勤
第23页，共50页。
23
7.6逻辑函数IF
第20页，共50页。
20
7.4--统计(tǒngjì)函数
❖ 求数据集的满足不同要求的数值的函数：
1、最大值MAX 2、最小值MIN 3、平均值AVERAGE 4、排名RANK
第21页，共50页。
21
7.5查找(chá zhǎo)引用函数
❖ 查询与引用函数可用来在数据数组或表格中查找特定数值，或查找某一单元格的引用。
粘贴选定区域
CTRL+V
剪切选定区域
CTRL+X
撤消最后一次操作 CTRL+Z
复制上行单元格 CTRL+D
复制左侧单元格 CTRL+R
重复上一步操作 CTRL+Y
❖ (2) 打印
显示“打印”对话框 CTRL+P
显示“打印”预览 CTRL+F2
第6页，共50页。
6
二、Excel 函数 (hánshù)
SMALL IF AND OR
IFERROR
条件求和 多条件求和
单元格统计函数 时间
排名 第K大的值 第K小的值 判断函数
且函数 或函数
错误判断
查找与 引用
以上函数(hánshù)基本能满足一般工作需求第14页。，共50页。
函数 len left
right
mid
CONCATENATE text value DAY COLUMN ROW LOKKUP
❖语法：SUM(number1，number2，...)。 ❖参数：Number1，number2，...为1到30（07以上版本254）个需要求和的数值、区域或引用(yǐnyòng)。
❖巧用：格式相同的多个工作表的同位置单元格求和。 若Sheet1!A1=1、Sheet1!A1=2、Sheet1!A1=3
unit代码
函数返回值
开始日期
"y"
时间段中的整年数
2000-6-15
"m" 时间段中的整月数
2008-6-15
"d" 时间段中的天数
2008-6-15
"md" 忽略日期中的年和月，计算天数差 2008-3-17
"ym" 忽略日期中的年和日，计算月数差 2008-6-17
"yd" 忽略日期中的年，计算天数差
3、混合引用：复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1 当将公式复制到D1单元格时变为：=$A1+C$1
不同的引用在拖动公式时具有不同的结果。 引用的转换快捷键：F4
第12页，共50页。
12
•6、EXCEL公式中的跨工作(gōngzuò)表引用
1、目的(mùdì)：按规律切割工资条
2、分析数据规律，确定公式组合
结果的第一行：永远是原数据中的第一行。
结果的第二行：结果第2行对应原数据第2行，结果第5行对应原数据第3行，第8对应第4，第11对应第5。
结果的第三行：永远为空。
这里面第二个最难算，找找看它们之间有什么规律。
第25页，共50页。
25
VLOKKUP HLOOKUP
MATCH INDEX offset
用途 计算文本长度 文本截取（左）
文本截取（右）
文本截取（指定）
文本合并 文本化 数值化 日期 列号 行号 查找 列查找 行查找 匹配 引用 偏移
14
7.1----文本处理函数（提取字符Left 、 Right 、Mid ）
身份证号 320102781001492
地区 =left(a1,6)
320102
出生日期 =mid(a1,7,6)
781001
性别 =right(a1,1)
2
为什么是3而不是2？ 为什么不用Right函数？
员工代码
0002337084张三 0000656246李世民
社保号 =left(a1,10)
0002337084 0000656246
-3.2 =Int(a1)
3.2 =Trunc(a1)
-3.2 =Trunc(a1)
结果 3 -4 3 3
说明 返回不大于3.2的最大整数3
备注
返回不大于-3.2的最大整数-4
返回截去小数部分后的整数3
返回截去小数部分后的整数-3 =ROUNDDOWN(a1,0)
第19页，共50页。
19
7.4----数字计算函数----sum