excel高级应用教程
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
找和选择】菜单下的【替换】） (3) 在弹出的“查找和替换”对话框中的查找内容处输入0，在替换为处不输入任何内容，选中“单元格匹
配”，单击【全部替换】命令按钮。即可将（E2：F204）区域中所有值为0的单元格清除。也可以用 “查找”命令去做
教师教育学院
《演示文稿的设计与制作》
10
EXCEL高级应用
教师教育学院
《演示文稿的设计与制作》
4
EXCEL高级应用
一、基本概念及数据输入技巧
5、学号录入后三位即可显示为2006152XXX 6、班级录入编号（如1）即可显示为全称（2006级数控技术1班） 7、性别可通过下拉框选择 8、身份证号具有提示功能及位数较验功能
教师教育学院
《演示文稿的设计与制作》
《EXCEL高级应用》
EXCEL高级应用
内容
1、EXCEL基本概念及数据输入技巧 2、清除值为0的单元格 3、在空单元格中输入相同的值 4、数据的同步变化、跨工作簿计算 5、各种函数(rank match index vlookup offset) 6、确定年级班名次 7、将不及格的成绩用红色表示 8、定位查找 9、等级考试发证问题 10、盘库打印问题 11、数据透视表 12、高级筛选问题 13、双轴图表的绘制 14、宏与VBA
2、IF函数，根据给定的条件确定相应的值。例：实发工资大于等于600，评价为“高”， 实发工资大于等于500小于600评价为“中”;实发工资小于500评价为“低”
在K2单元格中输入 =IF(I2>=600,"高",IF(I2>=500,"中","低")) (excel函数在2007版本及以后版本中最多能嵌套64层）
教师教育学院
《演示文稿的设计与制作》
2
EXCEL高级应用
一、基本概念及数据输入技巧
一、基本概念 1、工作簿（一个文件，等价于一本活页夹） 2、工作表（等价于活页夹中的活页纸）
(一个工作簿中最多可包含255个工作表) 3、单元格（等价于活页纸上的小方格）
4、活动工作簿
5、活动工作表
6、活动单元格
7、单元格地址（相对地址、绝对地址、混合地址）
3、MATCH函数，匹配函数，如工作表中的C46位置中的=MATCH(B46,B2:B40,0),得出B46中 名字与B2:B40中的第几个名字匹配，成功给出第几个的值。匹配类型为1时，找小于等 于要找值的最大值，数据必须从小到大排序.匹配类型为0时,查找等于要找值的第一个 值，数据无需排序。匹配类型为-1时，找大于等于要找值的最小值，数据必须从大到 小排序。
教师教育学院
《演示文稿的设计与制作》
7
EXCEL高级应用
一、基本概念及数据输入技巧
教师教育学院
《演示文稿的设计与制作》
8
EXCEL高级应用
一、基本概念及数据输入技巧
教师教育学院
《演示文稿的设计与制作》
9
EXCEL高级应用
二、清除值为0的单元格
操作方法：（在“基础操作表”工作簿中进行） (1) 选定需要清除值为0的数据区域（E2：F204） (2) 单击【编辑】菜单下的【替换】命令或按快捷键Ctrl+H（或【开始】菜单下的【编辑】菜单下的【查
5
EXCEL高级应用
一、基本概念及数据输入技巧
在“考试报名表”工作簿中进行操作 操作步骤： （1）单击“格式”菜单下的“单元格”（或“开始”菜单下“单元格”菜单下的“设置单元格格式….”） （2）进行自定义格式的设置：“2006152”000，“2006级数控技术”0“班” （3）选定学号下的区域，设定为自定义格式：“2006152”000 （4）选定班级下的区域，设定为自定义格式，“2006级数控技术”0“班”
例:
B2
$B$2 B$2或 $B2
打开“投资额”工作簿，计算投资比例
教师教育学院
《演示文稿的设计与制作》
3
EXCEL高级应用
一、基本概念及数据输入技巧
二、数据输入技巧 1、等差或等比数列的输入方法
(1)先输入二个数据 (2)选定这二个单元格 (3)鼠标靠向填充柄，等鼠标指针变为【】 (4)按住鼠标右键拖动至目的地，松开鼠标标右键，从弹出的快捷菜单中选【等差序列】或 【等比序列】 2、日期输入方法 （1）先输入一个日期 （2）选定这个单元格 （3）鼠标靠向填充柄，等变为【】 （4）按住鼠标右键拖动至目的地，松开鼠标，从弹出的快捷菜单中选【以年填充】或 【以月填充】等。 3、在一个区域内输入相同的数据CTRL+ENTER键 4、自定义序列 单击【工具】菜单下的【选项…】,再单击【自定义序列…】(【 office按钮】下的【 EXC EL选项】下的【常用】标签的【编辑自定义列表…】,,进行相应的操作即可
也可以用“查找”命令去做
教师教育学院
《演示文稿的设计与制作》
11
EXCEL高级应用
四、数据的同步变化、数据跨工作簿计算、合并计算
打开“成绩表”工作簿，将成绩表中的语文、数学、英语成绩分 别复制相应的三张工作表中 １、要求数据同步变化。
使用“选择性粘贴”中的“粘贴链接”即可 ２、将计算结果复制到其它单元格中（用“选择性粘贴”中的“数值”
教师教育学院
《EL高级应用
一、基本概念及数据输入技巧
操作步骤： （4）选定性别下的区域，单击“数据”菜单下的“数据有效性”，允许选“序列”，来源框 中输入：男，女（或单击“数据”菜单下的“数据有效性”下的“数据有效性”） （5）选定身份证号下的区域，从E3开始，单击“数据”菜单下的“数据有效性”， 进行设置
３、数据的跨工作簿计算 打开“跨工作簿计算用数据表”文件夹中的四个工作簿可实现数
据的跨工作薄计算。 ４、打开“工资单”工作簿，用合并计算的方法求出男女同志的平均工
资、平均奖金
教师教育学院
《演示文稿的设计与制作》
12
EXCEL高级应用
五、各种函数的应用
打开“工资单”工作簿 1、RANK函数，用于乱序数据的排序号。例：在J2中输入 =RANK(I2,$I$2:$I$40,0)，0表示从大到小
三、在空单元格中输入相同的值
例:如何将借方金额和贷方金额中为空的单元格输入0
操作方法：(在基础表中进行操作） (1)选定数据区域（E2：F204） (2)单击【编辑】菜单下的【定位】命令（或【开始】菜单下的【编辑】
菜单下的【查找和选择】菜单下的【定位条件】） (3)在弹出的“定位”对话框中，单击【定位条件…】按钮 (4)在弹出“定位条件”对话框中选“空值”，单击【确定】 (5)原区域中所有空值的单元格均被选中 (6)输入0值，然后按CTRL+ENTER
配”，单击【全部替换】命令按钮。即可将（E2：F204）区域中所有值为0的单元格清除。也可以用 “查找”命令去做
教师教育学院
《演示文稿的设计与制作》
10
EXCEL高级应用
教师教育学院
《演示文稿的设计与制作》
4
EXCEL高级应用
一、基本概念及数据输入技巧
5、学号录入后三位即可显示为2006152XXX 6、班级录入编号（如1）即可显示为全称（2006级数控技术1班） 7、性别可通过下拉框选择 8、身份证号具有提示功能及位数较验功能
教师教育学院
《演示文稿的设计与制作》
《EXCEL高级应用》
EXCEL高级应用
内容
1、EXCEL基本概念及数据输入技巧 2、清除值为0的单元格 3、在空单元格中输入相同的值 4、数据的同步变化、跨工作簿计算 5、各种函数(rank match index vlookup offset) 6、确定年级班名次 7、将不及格的成绩用红色表示 8、定位查找 9、等级考试发证问题 10、盘库打印问题 11、数据透视表 12、高级筛选问题 13、双轴图表的绘制 14、宏与VBA
2、IF函数，根据给定的条件确定相应的值。例：实发工资大于等于600，评价为“高”， 实发工资大于等于500小于600评价为“中”;实发工资小于500评价为“低”
在K2单元格中输入 =IF(I2>=600,"高",IF(I2>=500,"中","低")) (excel函数在2007版本及以后版本中最多能嵌套64层）
教师教育学院
《演示文稿的设计与制作》
2
EXCEL高级应用
一、基本概念及数据输入技巧
一、基本概念 1、工作簿（一个文件，等价于一本活页夹） 2、工作表（等价于活页夹中的活页纸）
(一个工作簿中最多可包含255个工作表) 3、单元格（等价于活页纸上的小方格）
4、活动工作簿
5、活动工作表
6、活动单元格
7、单元格地址（相对地址、绝对地址、混合地址）
3、MATCH函数，匹配函数，如工作表中的C46位置中的=MATCH(B46,B2:B40,0),得出B46中 名字与B2:B40中的第几个名字匹配，成功给出第几个的值。匹配类型为1时，找小于等 于要找值的最大值，数据必须从小到大排序.匹配类型为0时,查找等于要找值的第一个 值，数据无需排序。匹配类型为-1时，找大于等于要找值的最小值，数据必须从大到 小排序。
教师教育学院
《演示文稿的设计与制作》
7
EXCEL高级应用
一、基本概念及数据输入技巧
教师教育学院
《演示文稿的设计与制作》
8
EXCEL高级应用
一、基本概念及数据输入技巧
教师教育学院
《演示文稿的设计与制作》
9
EXCEL高级应用
二、清除值为0的单元格
操作方法：（在“基础操作表”工作簿中进行） (1) 选定需要清除值为0的数据区域（E2：F204） (2) 单击【编辑】菜单下的【替换】命令或按快捷键Ctrl+H（或【开始】菜单下的【编辑】菜单下的【查
5
EXCEL高级应用
一、基本概念及数据输入技巧
在“考试报名表”工作簿中进行操作 操作步骤： （1）单击“格式”菜单下的“单元格”（或“开始”菜单下“单元格”菜单下的“设置单元格格式….”） （2）进行自定义格式的设置：“2006152”000，“2006级数控技术”0“班” （3）选定学号下的区域，设定为自定义格式：“2006152”000 （4）选定班级下的区域，设定为自定义格式，“2006级数控技术”0“班”
例:
B2
$B$2 B$2或 $B2
打开“投资额”工作簿，计算投资比例
教师教育学院
《演示文稿的设计与制作》
3
EXCEL高级应用
一、基本概念及数据输入技巧
二、数据输入技巧 1、等差或等比数列的输入方法
(1)先输入二个数据 (2)选定这二个单元格 (3)鼠标靠向填充柄，等鼠标指针变为【】 (4)按住鼠标右键拖动至目的地，松开鼠标标右键，从弹出的快捷菜单中选【等差序列】或 【等比序列】 2、日期输入方法 （1）先输入一个日期 （2）选定这个单元格 （3）鼠标靠向填充柄，等变为【】 （4）按住鼠标右键拖动至目的地，松开鼠标，从弹出的快捷菜单中选【以年填充】或 【以月填充】等。 3、在一个区域内输入相同的数据CTRL+ENTER键 4、自定义序列 单击【工具】菜单下的【选项…】,再单击【自定义序列…】(【 office按钮】下的【 EXC EL选项】下的【常用】标签的【编辑自定义列表…】,,进行相应的操作即可
也可以用“查找”命令去做
教师教育学院
《演示文稿的设计与制作》
11
EXCEL高级应用
四、数据的同步变化、数据跨工作簿计算、合并计算
打开“成绩表”工作簿，将成绩表中的语文、数学、英语成绩分 别复制相应的三张工作表中 １、要求数据同步变化。
使用“选择性粘贴”中的“粘贴链接”即可 ２、将计算结果复制到其它单元格中（用“选择性粘贴”中的“数值”
教师教育学院
《EL高级应用
一、基本概念及数据输入技巧
操作步骤： （4）选定性别下的区域，单击“数据”菜单下的“数据有效性”，允许选“序列”，来源框 中输入：男，女（或单击“数据”菜单下的“数据有效性”下的“数据有效性”） （5）选定身份证号下的区域，从E3开始，单击“数据”菜单下的“数据有效性”， 进行设置
３、数据的跨工作簿计算 打开“跨工作簿计算用数据表”文件夹中的四个工作簿可实现数
据的跨工作薄计算。 ４、打开“工资单”工作簿，用合并计算的方法求出男女同志的平均工
资、平均奖金
教师教育学院
《演示文稿的设计与制作》
12
EXCEL高级应用
五、各种函数的应用
打开“工资单”工作簿 1、RANK函数，用于乱序数据的排序号。例：在J2中输入 =RANK(I2,$I$2:$I$40,0)，0表示从大到小
三、在空单元格中输入相同的值
例:如何将借方金额和贷方金额中为空的单元格输入0
操作方法：(在基础表中进行操作） (1)选定数据区域（E2：F204） (2)单击【编辑】菜单下的【定位】命令（或【开始】菜单下的【编辑】
菜单下的【查找和选择】菜单下的【定位条件】） (3)在弹出的“定位”对话框中，单击【定位条件…】按钮 (4)在弹出“定位条件”对话框中选“空值”，单击【确定】 (5)原区域中所有空值的单元格均被选中 (6)输入0值，然后按CTRL+ENTER