excel常用函数使用技巧分享课件.ppt

1、主讲人：XXXexcel常用函数使用技巧分享Excel常用函数办公软件使用技巧分享之CV市场研究部数据共享、程序开发数据共享、程序开发简单动画、协同办公简单动画、协同办公提高效率、潜力无限提高效率、潜力无限图表演示、统计分析图表演示、统计分析辅助计算、报表分析辅助计算、报表分析制作表格、表单应用制作表格、表单应用Excel 能做什么，还能做什么？数据分析数据分析n排序排序n筛选筛选n列表列表n数据透视表数据透视表n假设分析假设分析n高级分析高级分析数据操作数据操作图表与图形图表与图形n录入与导入录入与导入n常规编辑常规编辑n格式化格式化n高级编辑高级编辑n自定义自定义n打印打印n图表的构成图表

2、的构成n图表美化图表美化n高级图表高级图表n交互式图表交互式图表公式与函数公式与函数n公式与基础公式与基础n函数应用函数应用n函数嵌套函数嵌套n数组公式数组公式n自定义函数自定义函数宏与宏与VBAVBAn运行录制宏运行录制宏nVBAVBA基础基础nVBAVBA对象对象n窗体与控件窗体与控件n类模块类模块Excel常用函数使用第二章Excel基本操作技巧第一章第三章Excel图表制作案例ExcelExcel基本操作技巧基本操作技巧快速输入数据快速输入数据限定输入内容限定输入内容格式设置格式设置数据处理数据处理Excel基本操作技巧第一章 Excel基本操作技巧快速输入数据 自动填充已知：单元格内

3、输入一月二月，鼠标拖拉后，可自动生成三月、四月。演示：如何实现输入体系课，拖拉鼠标后自动生成所有课室？单元格设置法演示：如何实现输入1，显示男，输入2，显示女？多单元格输入演示：Ctrl+Enter/Ctrl+D，同时输入相同数据。第一章限定输入内容-数据有效性 有没有遇到过这样的情况，当您让别人给您填写一个资料时，老是不能按您的要求填写，更有甚者，会把您的表格乱改一通，比如您让别人填写个人简历，其中性别一栏共有以下内容：男、帅哥、男性、man、GG女、靓女、超女、女性、MM、PLMM晕，让我如何统计分析？报怨别人不按你的要求填写报表！我觉得：不要抱怨，是您给了别人这个自由！第一章格式设置 清

4、零方法一：工具选项视图零值方法二：查找和替换 单元格文字换行快捷键组合：Alt+Enter 合适列宽行高鼠标移至行/列标右边框，双击。第一章数据处理 选择性粘贴转置演示：如何将竖向排序色数据重新横向排列。按行排序演示：如何使选定区域按行数据顺序排序。高级筛选演示：如何实现多条件筛选。第一章其他ExcelExcel操作，键盘有话说快捷键用法操作，键盘有话说快捷键用法学会授权，学会授权，ExcelExcel做得比您更快更好做得比您更快更好人非圣贤，孰能无错出错信息详解人非圣贤，孰能无错出错信息详解分组显示使您的数据层次更加清晰分组显示使您的数据层次更加清晰快速输入系统日期：快速输入系统日期：Ctr

5、l+Ctrl+；快速输入系统时间：快速输入系统时间：Ctrl+Shift+Ctrl+Shift+；第一章函数公式结构第二章 Excel常用函数案例ExcelExcel函数函数单元格地址单元格地址引用区域引用区域常量常量(数值型数值型)运算符运算符公式符号公式符号函数公式的标点符号必须在英文状态下输入，字符应用双引号引起。函数公式的标点符号必须在英文状态下输入，字符应用双引号引起。Excel Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式函数即是预先定义，执行计算、分析等处理数据任务的特殊公式公式是由用户自行设计对工作表进行计算和处理的公式公式是由用户自行设计对工作表进行计

6、算和处理的公式常用运算符第二章类型类型运算符运算符功能功能示例示例算术运算符算术运算符+加加A1+B1A1+B1-减减A1-B1A1-B1*乘乘A1A1*B1B1/除除A1/B1A1/B1%百分比百分比A1%A1%乘方乘方A12A12比较运算符比较运算符=等于等于A1=B1A1=B1 大于大于A1B1A1B1 小于小于A1B1A1=大于等于大于等于A1=B1A1=B1=小于等于小于等于A1=B1A1=B1不等于不等于A1B1A1B1文本运算符文本运算符&文本值连接文本值连接“单价单价”&”50&”50元元”返回值：单价返回值：单价5050元元引用运算符引用运算符:区域运算符区域运算符A1:B1

7、A1:B1(A1,A2,B1,B2)=(A1:B2)(A1,A2,B1,B2)=(A1:B2),合并多个引用区域合并多个引用区域(A1:B1,C1:D1)(A1:B1,C1:D1)相对引用、绝对引用、混合引用第二章引用类型切换键引用类型切换键 F4，符号，符号$列号列号行号行号B1B1相对引用：公式所在单元格的位置改变，相对引用：公式所在单元格的位置改变，引用单元格的位置也随之改变。引用单元格的位置也随之改变。如如B1B1绝对引用：引用单元格的位置始终固定不变。如绝对引用：引用单元格的位置始终固定不变。如$B$1$B$1混合引用：引用单元格未使用混合引用：引用单元格未使用$符号固定的行号或列号

8、，符号固定的行号或列号，随公式所在单元格的位置改变。如随公式所在单元格的位置改变。如$B1$B1或或B$1B$1第二章常用函数类型类型类型名称名称语法语法用途用途日期日期时间时间YEARYEARYEAR(单元格)选定日期序列号或日期格式单元格中的年份DATEDIFDATEDIFDATEDIF(开始日期,结束日期,”Y”)两个日期之间的年数文本文本TEXTTEXTTEXT(单元格,指定格式)将单元格数值转换为按指定数字格式表示文本MIDMIDMID(单元格,开始位置,提取字符数量)在单元格中按指定开始位置提取指定数量的字符统计统计AVERAGEAVERAGE AVERAGE(单元格1,单元格2,

9、)选定单元格为数字类型的数据的加权平均数COUNTCOUNTCOUNT(单元格1,单元格2,)选定单元格为数字类型数据的个数COUNTIFCOUNTIFCOUNTIF(单元格区域,条件)选定单元格区域内满足条件的单元格个数MAXMAXMAX(单元格1,单元格2,)选定单元格中数值最大的值逻辑逻辑IF IFIF(参数条件,结果1,结果2)满足参数条件,返回结果1,否则返回结果2ANDANDAND(参数1,参数2,)过程中所有参数都需满足OROROR(参数1,参数2,)过程中只需满足一个参数即可数学数学SUMSUMSUM(单元格1,单元格2,)选定单元格内所有数据之和SUMIFSUMIFSUMIF

10、(条件区域,条件,取值区域)符合条件的条件区域所对应的取值区域内数据之和ROUNDROUNDROUND(单元格,保留位数)对选定单元格进行四舍五入查找查找引用引用VLOOKUPVLOOKUP VLOOKUP(单元格,查找区域,查找区域第N列,FALSE)查找区域首列符合指定单元格的查找区域第N列数值HLOOKUPHLOOKUP HLOOKUP(单元格,查找区域,查找区域第N行,FALSE)查找区域首行符合指定单元格的查找区域第N行数值第二章函数应用示例-日期时间函数第二章函数应用示例-逻辑函数（IF、AND、OR）第二章函数应用示例-逻辑函数（IF）逻辑函数（逻辑函数（IF函数特殊情况）函数特

11、殊情况）IF IF函数最多嵌套七层函数最多嵌套七层IF(条件条件,A,IF(条件条件,B,IF(条件条件,C,IF(条件条件,D,IF(条件条件,E,IF(条件条件,F,IF(条件条件,G,H)七层以上可使用公式七层以上可使用公式IF(条件条件,A,0)+IF(条件条件,B,0)+IF(条件条件,C,0)+IF(条件条件,D,0)思考：结合以上所列举的日期函数和逻辑IF函数，对柳汽所生产的车辆进行超期判断监控，判断标准如下所示，那公式该怎么写呢？超期判断填写（截止今天）：3个月内、3-6个月、6-12个月、12个月以上第二章综合应用案例思考=IF(EDATE(A5,3)TODAY(),3=IF

12、(EDATE(A5,3)TODAY(),3个月内个月内,IF(EDATE(A5,6)TODAY(),3-6,IF(EDATE(A5,6)TODAY(),3-6个月个月,IF(EDATE(A5,12)TODAY(),6-12,IF(EDATE(A5,12)TODAY(),6-12个月个月,12,12个月以上个月以上)第二章函数应用示例-文本函数第二章综合应用案例思考思考：结合以上所列举的文本函数，需要对东风完整车型代码进行处理，要求保留红色字体字母，剔除黑色字体字母，该如何填写公式呢？前两个代码可以通过LEFT、LEN(B)综合进行代码截取，但第3个车型代码由于代码类型无法批量进行处理，应该如何

13、进行公式填写？需要运用什么函数？通过通过LEFTLEFT函数，以及函数，以及FINDFIND的嵌入使用的嵌入使用 =LEFT(A4,FIND(-,A4,FIND(-,A4,FIND(-,A4,1)+1)+1)+2)=LEFT(A4,FIND(-,A4,FIND(-,A4,FIND(-,A4,1)+1)+1)+2)第二章函数应用示例-数学函数第二章函数应用示例-统计函数CCountount 只计数只计数，文本、逻辑值、错误信息、空单元格都不统计的。，文本、逻辑值、错误信息、空单元格都不统计的。CCountaounta 统计非空单元格，统计非空单元格，只要单元格有内容，就会被统计只要单元格有内容，

14、就会被统计，包括有些看不见的字符。，包括有些看不见的字符。第二章函数应用示例-查找引用函数（VLOOKUP）通过通过 IF IF(1,0,(1,0,区域区域1,1,区域区域2)2)函数，函数，将两个区域重新组合成一个区域将两个区域重新组合成一个区域第二章函数应用示例-查找引用函数（HLOOKUP）第二章函数应用示例-查找引用函数（MATCH）MATCHMATCH(lookup_value,lookup_array,match_type)(lookup_value,lookup_array,match_type)，在单元格区域中搜索指定项，然后返，在单元格区域中搜索指定项，然后返回该项在单元格区

15、域中的相对位置。回该项在单元格区域中的相对位置。Match_type Match_type 可以为数字可以为数字 -1-1、0 0 或或 1 1。第二章函数应用示例-返回引用函数（INDEX、COLUMN(S)、ROW(S)）INDEX(arrayINDEX(array，row_numrow_num，column_num)column_num)返回数组中指定返回数组中指定的单元格或单元格数组的数值，的单元格或单元格数组的数值，row_numrow_num或或column_numcolumn_num可缺可缺省一个省一个COLUMN(reference)COLUMN(reference)返回给定引

16、用的列标返回给定引用的列标COLUMNS(array)COLUMNS(array)返回某一引用或数组的返回某一引用或数组的列数列数ROW(reference)ROW(reference)返回给定引用的行号返回给定引用的行号ROWS(array)ROWS(array)返回某一引用或数组的行数返回某一引用或数组的行数第二章综合应用案列分享VLOOKUPVLOOKUP与与COLUMNCOLUMN的巧妙组合的巧妙组合比比VLOOKUPVLOOKUP强大强大N N倍的查找返回函数组合：倍的查找返回函数组合：INDEX+MATCHINDEX+MATCHINDEX+MATCHINDEX+MATCH，同样可以

17、达到从右向左查找的，同样可以达到从右向左查找的效果；再加上效果；再加上COLUMNCOLUMN函数，也可以达到函数，也可以达到VLOOKUP+COLUMNVLOOKUP+COLUMN组合的效果。组合的效果。第二章其他实用函数/组合清理工作日连接PHONETIC函数不支持空白单元格、数字、日期、时间以及任何公式生成的值的连接首数字第二章函数应用自学表函数深入学习第三章 Excel图表制作案例图表常用类型第三章双层饼图第三章悬浮累积图第三章甘特图用于工程、项目进度监控的甘特图可以通过Excel图表作图实现左边的图么？第三章堆积式南丁格尔玫瑰图第三章动态图动态图一：销售动态报表动态图二：全国中重卡上牌监控动态图制作步骤：办公软件学习的一点建议磨刀不误砍柴工多问、学会搜索学以致用课件制作谢谢观看学习交流共同提高感谢观赏