《excel高级应用ppt课件》由会员分享，可在线阅读，更多相关《excel高级应用ppt课件（38页珍藏版）》请在金锄头文库上搜索。
1、EXCEL 高高 级级 应应 用用华南农业大学图书馆华南农业大学图书馆韩明明韩明明Email：ayrtonscau.edu华农大研究生信息群：华农大研究生信息群： 130082280ExcelExcel高级应用高级应用序言序言Excel用户水平的用户水平的5个层次个层次第2页刚刚开始接触刚刚开始接触 基本掌握常用功能基本掌握常用功能 熟练使用常用功能熟练使用常用功能+ +数据透视表数据透视表+ +公式公式 数组公式数组公式+VBA+VBA简单编程简单编程 ExcelExcel技能技能+ +专业知识专业知识+ +行业经验行业经验 新手新手 初级用户初级用户 中级用户中级用户 高级用户高级用户 专
2、家专家 ExcelExcel高级应用高级应用知识结构知识结构第3页ExcelExcel数据处理数据处理与操作与操作ExcelExcel函数与公式函数与公式ExcelExcel图表与图形图表与图形Excel VBAExcel VBA与宏与宏Excel Excel 数据分析数据分析ExcelExcel高级应用高级应用EXCEL使用状况使用状况1、根本不知道其他功能、根本不知道其他功能2、知道功能但不知道如何使用、知道功能但不知道如何使用3、暂时使用不上，不去了解、暂时使用不上，不去了解第4页使用了使用了20%，常用的只有，常用的只有5%ExcelExcel高级应用高级应用EXCEL学习方法学习方法
3、1、兴趣爱好，循序渐进、兴趣爱好，循序渐进2、善用资源，学以致用、善用资源，学以致用通过好书、网络等资源学习通过好书、网络等资源学习博大精深，不必深究但要了解博大精深，不必深究但要了解3、多阅读多实践、多阅读多实践实践，实践，再实践实践，实践，再实践有问题独立思考解决，提高能力有问题独立思考解决，提高能力归纳，总结，积累归纳，总结，积累第5页ExcelExcel高级应用高级应用主要内容主要内容一、基本功能一、基本功能二、单元格地址引用二、单元格地址引用三、公式与函数三、公式与函数四、数据分析四、数据分析五、图表五、图表六、特殊技巧六、特殊技巧第6页ExcelExcel高级应用高级应用一、基本功
4、能一、基本功能Excel版本兼容问题版本兼容问题第7页ExcelExcel高级应用高级应用一、基本功能一、基本功能功能区功能区第8页ExcelExcel高级应用高级应用一、基本功能一、基本功能工作表区工作表区第9页ExcelExcel高级应用高级应用一、基本功能一、基本功能工作表属性工作表属性第10页1、工作表缺省数量（、工作表缺省数量（3）2、工作表缺省用户名（、工作表缺省用户名（sheet1、 sheet2、 sheet3）3、工作表列：、工作表列：214；工作表行：；工作表行：2204、单元格地址：列标行标、单元格地址：列标行标单元格属性单元格属性1、单元格所在行、列的宽度和高度、单元格
5、所在行、列的宽度和高度2、填充柄、填充柄3、单元格格式：字体、填充、边框等、单元格格式：字体、填充、边框等4、单元格批注：为单元格添加注释，以便说明单元格数、单元格批注：为单元格添加注释，以便说明单元格数据或公式的意义。据或公式的意义。ExcelExcel高级应用高级应用一、基本功能一、基本功能保护工作簿保护工作簿第11页1、选择、选择“审阅审阅”选项卡中选项卡中“更改更改”组的组的“保护工作簿保护工作簿”2、保护结构：不能插入、删除工作表，更改工作表名称、保护结构：不能插入、删除工作表，更改工作表名称3、保护窗口：保留窗口的大小及位置等、保护窗口：保留窗口的大小及位置等保护工作表保护工作表选
6、择选择“审阅审阅”选项卡中选项卡中“更改更改”组的组的“保护工作表保护工作表”;保护保护工作表中数据不被任意修改工作表中数据不被任意修改ExcelExcel高级应用高级应用一、基本功能一、基本功能Excel打印打印第12页1 1、小表格的打印设置、小表格的打印设置、小表格的打印设置、小表格的打印设置面积较小，不足一张纸，可以使用居中面积较小，不足一张纸，可以使用居中面积较小，不足一张纸，可以使用居中面积较小，不足一张纸，可以使用居中2 2、大表格的打印设置、大表格的打印设置、大表格的打印设置、大表格的打印设置超超超超出出出出一一一一张张张张纸纸纸纸，但但但但超超超超出出出出有有有有限限限限，可
7、可可可以以以以使使使使用用用用缩缩缩缩放放放放的的的的方方方方式式式式3 3、跨页打印表头设置、跨页打印表头设置、跨页打印表头设置、跨页打印表头设置如如果果工工作作表表的的首首行行是是标标题题，而而且且工工作作表表内内容容较较多多，可可能能打打印印多多页页。可可以以通通过过页页面面设设置置，将将首首行行指指定定为标题为标题ExcelExcel高级应用高级应用二、单元格地址引用二、单元格地址引用引用引用第13页引用即在公式中用到了其他单元格在表格中的位置。引用即在公式中用到了其他单元格在表格中的位置。引引用用的的作作用用在在于于标标识识工工作作表表中中的的单单元元格格或或单单元元格格区区域域，并
8、指明公式中所使用的数据的单元格位置并指明公式中所使用的数据的单元格位置 引用不同工作簿中的单元格称为链接引用不同工作簿中的单元格称为链接ExcelExcel高级应用高级应用二、单元格地址引用二、单元格地址引用相对引用相对引用第14页相相对对引引用用的的公公式式复复制制到到目目标标单单元元格格时时，目目标标单单元元格格公公式式中中单单元元格格引引用用被被更更新新，引引用用单单元元格格与与公公式式所所在在单单元元格的相对位置不变。格的相对位置不变。例如：例如：A1中的公式为：中的公式为：=B2*C3， A1中中的的公公式式复复制制到到D3，D3中的公式变为：中的公式变为：=E4*F5ExcelEx
9、cel高级应用高级应用二、单元格地址引用二、单元格地址引用绝对引用绝对引用第15页绝绝对对引引用用的的公公式式复复制制到到目目标标单单元元格格时时，目目标标单单元元格格公公式中单元格引用保持不变。式中单元格引用保持不变。绝对引用表示方法：行列前加绝对引用表示方法：行列前加$符号符号例例如如：A1中中的的公公式式为为：=$B$2*$C$3，A1中中的的公公式式复制到复制到D3，D3中的公式仍为：中的公式仍为：=$B$2*$C$3ExcelExcel高级应用高级应用二、单元格地址引用二、单元格地址引用混合引用混合引用第16页混混合合引引用用的的单单元元格格地地址址，一一个个是是相相对对引引用用，另
10、另一一个个是是绝对引用，如：绝对引用，如：$A1、A$1。ExcelExcel高级应用高级应用二、单元格地址引用二、单元格地址引用三种引用类型的转换三种引用类型的转换第17页将将插插入入点点定定位位在在编编辑辑栏栏某某单单元元格格的的引引用用位位置置，按按F4功功能能键键可可以以实实现现相相对对引引用用、绝绝对对引引用用、混混合合引引用用之之间间的转换。的转换。例如：编辑栏上为：例如：编辑栏上为：=B2*C3，选中公式中的，选中公式中的B2，每，每按一下按一下F4变为：变为：=$B$2*C3、=B$2*C3、 =$B2*C3ExcelExcel高级应用高级应用二、单元格地址引用二、单元格地址引
11、用三维引用三维引用第18页格式：格式：!例如：例如：=Sheet2!F5 + Sheet3!C2说说明明：如如果果工工作作表表名名称称包包含含空空格格，必必须须用用单单引引号号把把工工作表名称括起来。作表名称括起来。例如：例如：=汇汇 总总!C25ExcelExcel高级应用高级应用二、单元格地址引用二、单元格地址引用内部引用与外部引用内部引用与外部引用第19页1、引用相同工作表中的单元格，例：、引用相同工作表中的单元格，例：=G3+G5+G10*102、引用同一工作簿不同工作表中的单元格，例：、引用同一工作簿不同工作表中的单元格，例：=Sheet1!G3+Sheet2!G5+Sheet2!E
12、273、引用已打开的不同工作簿中的单元格，例：、引用已打开的不同工作簿中的单元格，例：=Book1Sheet1!$IL$4+Book2Sheet2!$E$74、引用未打开的不同工作薄中的单元格，例：、引用未打开的不同工作薄中的单元格，例：=C:dkBook1.xlsxSheet2!$B$4+C:dkBook1.xlsxSheet1!$C$6ExcelExcel高级应用高级应用运算符优先级运算符优先级引用运算符（：、，）引用运算符（：、，）算术运算符（算术运算符（+、-、*、/）文本运算符（文本运算符（&）比较运算符（比较运算符（、=）三、公式与函数三、公式与函数第20页公式的概念公式的概念1、
13、Excel的的公公式式由由运运算算符符、数数值值、字字符符串串、变变量量和和函函数数组组成成2、公公式式必必须须以以等等号号“=”开开头头，即即在在Excel的的单单元元格格中中，凡是以等号开头的输入数据都被认为是公式。凡是以等号开头的输入数据都被认为是公式。3、在在等等号号的的后后面面可可以以跟跟数数值值、运运算算符符、变变量量或或函函数数，在在公式中还可以使用括号。公式中还可以使用括号。例：例： =SUM(A1:A20)高高低低ExcelExcel高级应用高级应用三、公式与函数三、公式与函数第21页常见的错误信息常见的错误信息错误值错误值错误值错误值原因原因原因原因#DIV/0!当数字被零
14、 (0) 除时，出现错误#N/A没有可用的数值。通常情况是，您将数值直接输入某些工作表单元格。虽然这些单元格中会包含数据，但目前尚无数据引用这些单元格的公式将返回而不会计算数值#NAME?当 Excel 未识别公式中的文本时，出现错误#NULL!指定的两个区域不相交#NUM!公式或函数中使用无效数字值时，出现这种错误#REF!公式引用了无效的单元格（比如我们删除了有其他公式引用的单元格，或者把移动单元格粘贴到了其他公式引用的单元格中。）#当列不够宽，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现这种错误ExcelExcel高级应用高级应用三、公式与函
15、数三、公式与函数第22页函数的概念函数的概念函函数数是是Excel已已经经定定义义好好了了的的一一些些特特殊殊公公式式，它它们们可可以以对对一一个或多个数据进行计算，然后把计算的结果存放在某个单元格中个或多个数据进行计算，然后把计算的结果存放在某个单元格中函数的类型函数的类型工工作作表表函函数数、财财务务函函数数、日日期期函函数数、时时间间函函数数、数数学学与与三三角角函数、统计类函数、数据库管理函数、文本函数及信息类函数等函数、统计类函数、数据库管理函数、文本函数及信息类函数等函数的使用函数的使用函数函数由函数名和参数组成由函数名和参数组成，例：，例：SUM(A1:A10)公公式式以以一一个
16、个函函数数开开始始，则则应应该该像像其其它它公公式式一一样样，在在函函数数前前面面加一个等号。加一个等号。函数的左右两个括号必须成对出现，括号前后都不能有空格。函数的左右两个括号必须成对出现，括号前后都不能有空格。 一个公式最多可以嵌套七层函数。一个公式最多可以嵌套七层函数。ExcelExcel高级应用高级应用三、公式与函数三、公式与函数第23页函数的使用函数的使用格式：格式： =(, ,.)举例：举例： = SUM(C2:C6) = AVERAGE(A1:A3,C2:C6)常用的函数常用的函数SUM 求和数求和数AVERAGE 求平方值求平方值MAX 求最大值求最大值MIN 求最小值求最小值
17、COUNT 求数值数据的个数求数值数据的个数COUNTIF 条件求数值数据的个数条件求数值数据的个数IF 条件选择条件选择ExcelExcel高级应用高级应用四、数据分析四、数据分析数据筛选数据筛选第24页1、自自动动筛筛选选：采采用用简简单单条条件件来来快快速速筛筛选选记记录录，将将不不满满足足条条件件的的记记录录暂暂时时隐隐藏藏起起来来，而而将将满满足足条条件件的的记记录录显显示示在在工工作作表表上上；通通过过“数数据据”菜菜单单中中“筛筛选选”命命令令的的“自动筛选自动筛选”来实现的来实现的例：筛选出粮食种植面积大于等于例：筛选出粮食种植面积大于等于20的记录的记录ExcelExcel高
18、级应用高级应用四、数据分析四、数据分析数据筛选数据筛选第25页2、高高级级筛筛选选采采用用复复合合条条件件来来筛筛选选记记录录，并并允允许许把把满满足足条条件件的的记记录录复复制制到到另另外外的的区区域域，以以生生成成一一个个新新的的数数据据清清单单。先先建建立立条条件件区区域域。条条件件区区域域的的第第一一行行为为条条件件标标记记行行，第第二行开始是条件行二行开始是条件行 。例例：在在产产量量单单中中查查找找粮粮食食产产量量100000，小小麦麦产产量量80000的的记记录录 筛筛选选条条件件为为： (粮粮食食产产量量100000，小麦产量小麦产量80000 ）ExcelExcel高级应用高
19、级应用四、数据分析四、数据分析数据排序数据排序第26页1、表格数据的排序原则表格数据的排序原则表格数据的排序原则表格数据的排序原则区域命名必须包括区域命名必须包括区域命名必须包括区域命名必须包括“ “表头表头表头表头” ”区域区域区域区域排序前要添加连续编号，以保护原始序列排序前要添加连续编号，以保护原始序列排序前要添加连续编号，以保护原始序列排序前要添加连续编号，以保护原始序列多重排序时只有主要关健字重复时才进行次要关健字的排序多重排序时只有主要关健字重复时才进行次要关健字的排序多重排序时只有主要关健字重复时才进行次要关健字的排序多重排序时只有主要关健字重复时才进行次要关健字的排序符符符符号
20、号号号优优优优先先先先，数数数数字字字字其其其其次次次次，日日日日期期期期由由由由远远远远而而而而近近近近为为为为升升升升序序序序，逻逻逻逻辑辑辑辑值值值值falsefalse在在在在前，包含公式按结果排序前，包含公式按结果排序前，包含公式按结果排序前，包含公式按结果排序2 2、排序方法、排序方法、排序方法、排序方法 单一条件排序单一条件排序单一条件排序单一条件排序多重条件排序多重条件排序多重条件排序多重条件排序序列排序序列排序序列排序序列排序ExcelExcel高级应用高级应用四、数据分析四、数据分析数据排序数据排序第27页例：将成绩表按例：将成绩表按“语文成绩语文成绩”从高到低进行排序从高
21、到低进行排序 (1) 选定数据清单选定数据清单 (2) 选选择择“数数据据”菜菜单单中中的的“排排序序”命命令令，打打开开“排排序序”对话框对话框 (3) 设置对话框设置对话框ExcelExcel高级应用高级应用四、数据分析四、数据分析数据分类汇总数据分类汇总第28页分类原则，要分类的项目必须单独一列分类原则，要分类的项目必须单独一列凡凡是是需需要要汇汇总总的的列列，数数据据区区域域中中不不要要有有空空白白单单元元格格，以以免分类中遗漏免分类中遗漏数据区内格式应统一数据区内格式应统一操作原则：操作原则：先排序，再汇总先排序，再汇总例例：在在产产量量单单中中，按按“地地区区”字字段段分分类类汇汇
22、总总面面积积和和产量产量ExcelExcel高级应用高级应用四、数据分析四、数据分析第29页例：在产量单中，按例：在产量单中，按“地区地区”字段分类汇总面积和产量字段分类汇总面积和产量ExcelExcel高级应用高级应用五、图表五、图表第30页图表标题图表标题 坐标轴坐标轴Y 坐标轴坐标轴X 图例图例 绘图区绘图区 图表区图表区 数据系列数据系列 图表的6要素ExcelExcel高级应用高级应用五、图表五、图表ExcelExcel提供了多种图表类型提供了多种图表类型第31页ExcelExcel高级应用高级应用五、图表五、图表ExcelExcel图表制作图表制作第32页明确图表分析的目标与正确选
23、择表格数据的关系明确图表分析的目标与正确选择表格数据的关系明确图表分析的目标与正确选择表格数据的关系明确图表分析的目标与正确选择表格数据的关系合理的确定图表类型以正确表现分析的目标合理的确定图表类型以正确表现分析的目标合理的确定图表类型以正确表现分析的目标合理的确定图表类型以正确表现分析的目标图表的编辑、修饰及打印图表的编辑、修饰及打印图表的编辑、修饰及打印图表的编辑、修饰及打印ExcelExcel高级应用高级应用六、特殊技巧六、特殊技巧绘制斜线单元格绘制斜线单元格第33页1、利利用用Excel“边边框框”选选项项卡卡的的两两个个斜斜线线按按钮钮，可可以以在单元格中画左、右斜线在单元格中画左、
24、右斜线2、如如果果想想在在单单元元格格中中画画多多条条斜斜线线，就就必必须须利利用用“绘绘图图”工工具具。方方法法是是:打打开开Excel的的“绘绘图图”工工具具，单单击击“直直线线”按按钮钮，待待光光标标变变成成小小十十字字后后拖拖动动光光标标，即即可可画画出出需要的多条斜线需要的多条斜线3、斜斜线线单单元元格格的的数数据据输输入入要要麻麻烦烦一一些些，通通常常的的做做法法是是让让数数据据在在单单元元格格内内换换行行(按按“Alt+回回车车键键”)，再再添添加加空格即可将数据放到合适位置空格即可将数据放到合适位置4、还有一种数据输入方法是添加文本框的方式、还有一种数据输入方法是添加文本框的方
25、式ExcelExcel高级应用高级应用六、特殊技巧六、特殊技巧在在Excel中不丢掉列标题的显示中不丢掉列标题的显示第34页如如果果工工作作表表中中数数据据列列超超过过一一屏屏，当当我我们们利利用用滚滚动动条条来来浏浏览览数数据据时时，该该数数据据文文件件的的行行标标题题或或列列标标题题就就无无法法在在下下一一屏屏显显示示，也也就就是是只只看看到到数数据据，无无法法知知道道数数据据的的具具体意义，这样是很不明了的。体意义，这样是很不明了的。解解决决方方法法: 选选择择紧紧邻邻列列标标题题的的下下一一行行，单单击击“窗窗口口冻冻结结窗窗格格”命命令令，被被冻冻结结的的列列标标题题不不会会滚滚动动
26、，而而且且在在你移动工作簿的其他部分时，列标题会保持可见。你移动工作簿的其他部分时，列标题会保持可见。ExcelExcel高级应用高级应用六、特殊技巧六、特殊技巧禁止复制隐藏行或列中的数据禁止复制隐藏行或列中的数据第35页如如果果你你复复制制了了包包含含隐隐藏藏列列(或或行行)的的一一个个数数据据区区域域，然然后后把把它它粘粘贴贴到到一一个个新新的的工工作作表表，那那么么Excel把把隐隐藏藏列列也粘贴过来了。也粘贴过来了。要要想想避避免免这这种种情情况况，可可以以选选取取你你要要复复制制的的数数据据区区域域，然然后后选选择择“编编辑辑定定位位”命命令令，单单击击“定定位位条条件件”按按钮钮，
27、出出现现“定定位位条条件件”对对话话框框，选选中中“可可见见单单元元格格”选选项项，再再复复制制和和粘粘贴贴这这个个选选定定区区域域就就会会得得到到你你所所希希望望的结果。的结果。ExcelExcel高级应用高级应用六、特殊技巧六、特殊技巧将单元格转换成图片形式插入到Word中第36页假假如如要要把把Excel中中某某些些单单元元格格区区域域转转换换成成图图片片形形式式，可可先先选选中中要要进进行行转转换换的的单单元元格格区区域域，单单击击“开开始始复复制制图图片片”命命令令，在在弹弹出出的的“复复制制图图片片”对对话话框框中中，可可选选择择“如屏幕所示如屏幕所示”和和“如打印效果如打印效果”
28、两种显示方式两种显示方式在在这这里里选选择择“如如屏屏幕幕所所示示”和和“图图片片”单单选选按按钮钮，单单击击“确确定定”按按钮钮。然然后后进进入入Word中中，单单击击“编编辑辑粘粘贴贴”命命令令，即即可可将将选选中中的的Excel单单元元格格区区域域以以图图片片形形式式粘粘贴过来。贴过来。如如果果用用户户没没用用添添加加表表格格框框线线，那那么么选选择择“如如打打印印效效果果”后后，在在进进行行“粘粘贴贴图图片片”操操作作后后图图片片中中没没有有边边框框;如如果果选选择择“如如屏屏幕幕所所示示”选选项项，“粘粘贴贴图图片片”操操作作后后，图图片会有和屏幕显示一样的边框。片会有和屏幕显示一样的边框。ExcelExcel高级应用高级应用六、特殊技巧六、特殊技巧将Excel文档插入到Word中第37页假假如如要要把把Excel文文档档或或者者图图表表插插入入到到Word文文档档中中，可可单单击击“插插入入对对象象”命命令令，在在弹弹出出的的“对对象象”对对话话框框中中，可可选选择择“Microsoft Excel 97-2019工作表工作表”或者或者“Microsoft Excel 图表图表”ExcelExcel高级应用高级应用END第38页Email：ayrtonscau.edu华农大研究生信息群：华农大研究生信息群： 130082280
