excel高级应用教学课件

1、EXCEL 高 级 应 用华南农业大学图书馆韩明明Email：华农大研究生信息群： 130082280第1页，共38页。序言Excel用户水平的5个层次第2页刚刚开始接触 基本掌握常用功能 熟练使用常用功能+数据透视表+公式 数组公式+VBA简单编程 Excel技能+专业知识+行业经验 新手 初级用户 中级用户 高级用户 专家 第2页，共38页。知识结构第3页Excel数据处理与操作Excel函数与公式Excel图表与图形Excel VBA与宏Excel 数据分析第3页，共38页。EXCEL使用状况1、根本不知道其他功能2、知道功能但不知道如何使用3、暂时使用不上，

2、不去了解第4页使用了20%，常用的只有5%第4页，共38页。EXCEL学习方法1、兴趣爱好，循序渐进2、善用资源，学以致用通过好书、网络等资源学习博大精深，不必深究但要了解3、多阅读多实践实践，实践，再实践有问题独立思考解决，提高能力归纳，总结，积累第5页第5页，共38页。主要内容一、基本功能二、单元格地址引用三、公式与函数四、数据分析五、图表六、特殊技巧第6页第6页，共38页。一、基本功能Excel版本兼容问题第7页第7页，共38页。一、基本功能功能区第8页第8页，共38页。一、基本功能工作表区第9页第9页，共38页。一、基本功能工作表属性第10页1、工作表缺省数量（3）2、工作表缺省用户名

3、（sheet1、 sheet2、 sheet3）3、工作表列：214；工作表行：2204、单元格地址：列标行标单元格属性1、单元格所在行、列的宽度和高度2、填充柄3、单元格格式：字体、填充、边框等4、单元格批注：为单元格添加注释，以便说明单元格数据或公式的意义。第10页，共38页。一、基本功能保护工作簿第11页1、选择“审阅”选项卡中“更改”组的“保护工作簿”2、保护结构：不能插入、删除工作表，更改工作表名称3、保护窗口：保留窗口的大小及位置等保护工作表选择“审阅”选项卡中“更改”组的“保护工作表”;保护工作表中数据不被任意修改第11页，共38页。一、基本功能Excel打印第12页1、小表格的

4、打印设置面积较小，不足一张纸，可以使用居中2、大表格的打印设置超出一张纸，但超出有限，可以使用缩放的方式3、跨页打印表头设置如果工作表的首行是标题，而且工作表内容较多，可能打印多页。可以通过页面设置，将首行指定为标题第12页，共38页。二、单元格地址引用引用第13页引用即在公式中用到了其他单元格在表格中的位置。引用的作用在于标识工作表中的单元格或单元格区域，并指明公式中所使用的数据的单元格位置 引用不同工作簿中的单元格称为链接第13页，共38页。二、单元格地址引用相对引用第14页相对引用的公式复制到目标单元格时，目标单元格公式中单元格引用被更新，引用单元格与公式所在单元格的相对位置不变。例如：

5、A1中的公式为：=B2*C3，A1中的公式复制到D3，D3中的公式变为：=E4*F5第14页，共38页。二、单元格地址引用绝对引用第15页绝对引用的公式复制到目标单元格时，目标单元格公式中单元格引用保持不变。绝对引用表示方法：行列前加$符号例如：A1中的公式为：=$B$2*$C$3，A1中的公式复制到D3，D3中的公式仍为：=$B$2*$C$3第15页，共38页。二、单元格地址引用混合引用第16页混合引用的单元格地址，一个是相对引用，另一个是绝对引用，如：$A1、A$1。第16页，共38页。二、单元格地址引用三种引用类型的转换第17页将插入点定位在编辑栏某单元格的引用位置，按F4功能键可以实现

6、相对引用、绝对引用、混合引用之间的转换。例如：编辑栏上为：=B2*C3，选中公式中的B2，每按一下F4变为：=$B$2*C3、=B$2*C3、 =$B2*C3第17页，共38页。二、单元格地址引用三维引用第18页格式：!例如：=Sheet2!F5 + Sheet3!C2说明：如果工作表名称包含空格，必须用单引号把工作表名称括起来。例如：=汇 总!C25第18页，共38页。二、单元格地址引用内部引用与外部引用第19页1、引用相同工作表中的单元格，例：=G3+G5+G10*102、引用同一工作簿不同工作表中的单元格，例：=Sheet1!G3+Sheet2!G5+Sheet2!E273、引用已打开的

7、不同工作簿中的单元格，例：=Book1Sheet1!$IL$4+Book2Sheet2!$E$74、引用未打开的不同工作薄中的单元格，例：=C:dkBook1.xlsxSheet2!$B$4+C:dkBook1.xlsxSheet1!$C$6第19页，共38页。运算符优先级引用运算符（：、，）算术运算符（+、-、*、/）文本运算符（&）比较运算符（、=）三、公式与函数第20页公式的概念1、Excel的公式由运算符、数值、字符串、变量和函数组成2、公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。3、在等号的后面可以跟数值、运算符、变量或函数，在公式中还

8、可以使用括号。例： =SUM(A1:A20)高低第20页，共38页。三、公式与函数第21页常见的错误信息错误值原因#DIV/0!当数字被零 (0) 除时，出现错误#N/A没有可用的数值。通常情况是，您将数值直接输入某些工作表单元格。虽然这些单元格中会包含数据，但目前尚无数据引用这些单元格的公式将返回而不会计算数值#NAME?当 Excel 未识别公式中的文本时，出现错误#NULL!指定的两个区域不相交#NUM!公式或函数中使用无效数字值时，出现这种错误#REF!公式引用了无效的单元格（比如我们删除了有其他公式引用的单元格，或者把移动单元格粘贴到了其他公式引用的单元格中。）#当列不够宽，或者使用

9、了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现这种错误第21页，共38页。三、公式与函数第22页函数的概念函数是Excel已经定义好了的一些特殊公式，它们可以对一个或多个数据进行计算，然后把计算的结果存放在某个单元格中函数的类型工作表函数、财务函数、日期函数、时间函数、数学与三角函数、统计类函数、数据库管理函数、文本函数及信息类函数等函数的使用函数由函数名和参数组成，例：SUM(A1:A10)公式以一个函数开始，则应该像其它公式一样，在函数前面加一个等号。函数的左右两个括号必须成对出现，括号前后都不能有空格。 一个公式最多可以嵌套七层函数。第22页，共38页

10、。三、公式与函数第23页函数的使用格式： =(, ,.)举例： = SUM(C2:C6) = AVERAGE(A1:A3,C2:C6)常用的函数SUM 求和数AVERAGE 求平方值MAX 求最大值MIN 求最小值COUNT 求数值数据的个数COUNTIF 条件求数值数据的个数IF 条件选择第23页，共38页。四、数据分析数据筛选第24页1、自动筛选：采用简单条件来快速筛选记录，将不满足条件的记录暂时隐藏起来，而将满足条件的记录显示在工作表上；通过“数据”菜单中“筛选”命令的“自动筛选”来实现的例：筛选出粮食种植面积大于等于20的记录第24页，共38页。四、数据分析数据筛选第25页2、高级筛选

11、采用复合条件来筛选记录，并允许把满足条件的记录复制到另外的区域，以生成一个新的数据清单。先建立条件区域。条件区域的第一行为条件标记行，第二行开始是条件行 。例：在产量单中查找粮食产量100000，小麦产量80000的记录 筛选条件为： (粮食产量100000，小麦产量80000 ）第25页，共38页。四、数据分析数据排序第26页1、表格数据的排序原则区域命名必须包括“表头”区域排序前要添加连续编号，以保护原始序列多重排序时只有主要关健字重复时才进行次要关健字的排序符号优先，数字其次，日期由远而近为升序，逻辑值false在前，包含公式按结果排序2、排序方法 单一条件排序多重条件排序序列排序第26

12、页，共38页。四、数据分析数据排序第27页例：将成绩表按“语文成绩”从高到低进行排序 (1) 选定数据清单 (2) 选择“数据”菜单中的“排序”命令，打开“排序”对话框 (3) 设置对话框第27页，共38页。四、数据分析数据分类汇总第28页分类原则，要分类的项目必须单独一列凡是需要汇总的列，数据区域中不要有空白单元格，以免分类中遗漏数据区内格式应统一操作原则：先排序，再汇总例：在产量单中，按“地区”字段分类汇总面积和产量第28页，共38页。四、数据分析第29页例：在产量单中，按“地区”字段分类汇总面积和产量第29页，共38页。五、图表第30页图表标题 坐标轴Y 坐标轴X 图例 绘图区 图表区

13、数据系列 图表的6要素第30页，共38页。五、图表Excel提供了多种图表类型第31页第31页，共38页。五、图表Excel图表制作第32页明确图表分析的目标与正确选择表格数据的关系合理的确定图表类型以正确表现分析的目标图表的编辑、修饰及打印第32页，共38页。六、特殊技巧绘制斜线单元格第33页1、利用Excel“边框”选项卡的两个斜线按钮，可以在单元格中画左、右斜线2、如果想在单元格中画多条斜线，就必须利用“绘图”工具。方法是:打开Excel的“绘图”工具，单击“直线”按钮，待光标变成小十字后拖动光标，即可画出需要的多条斜线3、斜线单元格的数据输入要麻烦一些，通常的做法是让数据在单元格内换行

14、(按“Alt+回车键”)，再添加空格即可将数据放到合适位置4、还有一种数据输入方法是添加文本框的方式第33页，共38页。六、特殊技巧在Excel中不丢掉列标题的显示第34页如果工作表中数据列超过一屏，当我们利用滚动条来浏览数据时，该数据文件的行标题或列标题就无法在下一屏显示，也就是只看到数据，无法知道数据的具体意义，这样是很不明了的。解决方法: 选择紧邻列标题的下一行，单击“窗口冻结窗格”命令，被冻结的列标题不会滚动，而且在你移动工作簿的其他部分时，列标题会保持可见。第34页，共38页。六、特殊技巧禁止复制隐藏行或列中的数据第35页如果你复制了包含隐藏列(或行)的一个数据区域，然后把它粘贴到一

15、个新的工作表，那么Excel把隐藏列也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择“编辑定位”命令，单击“定位条件”按钮，出现“定位条件”对话框，选中“可见单元格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果。第35页，共38页。六、特殊技巧将单元格转换成图片形式插入到Word中第36页假如要把Excel中某些单元格区域转换成图片形式，可先选中要进行转换的单元格区域，单击“开始复制图片”命令，在弹出的“复制图片”对话框中，可选择“如屏幕所示”和“如打印效果”两种显示方式在这里选择“如屏幕所示”和“图片”单选按钮，单击“确定”按钮。然后进入Word中，单击“编辑粘贴”命令，即可将选中的Excel单元格区域以图片形式粘贴过来。如果用户没用添加表格框线，那么选择“如打印效果”后，在进行“粘贴图片”操作后图片中没有边框;如果选择“