Transcript jz8 - 华南农业大学图书馆

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