Excel 权威教案由浅入深全面系统搞定Excel.docx
《Excel 权威教案由浅入深全面系统搞定Excel.docx》由会员分享，可在线阅读，更多相关《Excel 权威教案由浅入深全面系统搞定Excel.docx（25页珍藏版）》请在冰点文库上搜索。
Excel权威教案由浅入深全面系统搞定Excel
EXCEL2007
第一讲EXCEL概述
一.简介
1.产生：
美国微软公司推出的电子表格软件，可进行繁琐的表格处理和数据分析，广泛应用于财务、金融、统计、管理等各行各业，OFFICE系列办公软件的组件之一。
2.版本：
……Excel2003、Excel2007、Excel2010、Excel2013……
3.功能
（1）.制作表格
在EXCEL中可以使用多种方法绘制表格。
一般需要大量计算（如工资表，成绩表等）或需要对数据进行分析处理（如查询符合条件）的表格大都在EXCEL里完成，而WORD中更适合完成文字为主的表格，如成绩表，简历表，学籍表等。
（2）.表中计算
在EXCEL中可以使用公式、函数完成数据的运算，并且EXCEL中包含几百个函数用于完成不同类型的数据计算，如数学和三角函数、财务函数、概率分析函数、查找引用函数等。
（3）.数据处理及分析
EXCEL可以方便的对数据进行修改、添加、删除、排序、查询、分类汇总等。
（4）.图表制作
图表比数据表更能直观的体现数据的变化趋势。
在EXCEL中，用户可以方便地将数据表格生成各种二维或三维的图表，如柱形图、条形图、折线图等。
二.启动与退出
1.启动
（1）.开始/所有程序/Microsoftoffice2007/Microsoftofficeexcel2007
（2）.双击桌面上的快捷图标
（3）.开始/运行/EXCEL/回车
（4）.打开任意一个已建工作簿
2.退出
（1）.直接单击标题上的“关闭”按钮
（2）.双击Office按钮
（3）.Office按钮/退出
（4）.Alt+F4
三.窗口的组成
1.标题栏：
显示当前程序的名称和文件的名称；
2.窗口按钮：
调整窗口的不同状态，包括最小化、最大化和还原、关闭；
3.快速访问工具栏：
提供默认的按钮或用户添加的按钮，可以快速执行相关命令；
4.office按钮：
相当于早期Office版本中的【文件】菜单，执行与文档有关的基本操作，如打开、保存、关闭、打印、退出等；
5.功能区：
excel窗口界面的重要组成部分，由一组不同的选项卡面板组成，单击选项卡标签可以切换到不同的选项卡功能面板。
每个选项卡又包含多个命令组，每个命令组通常都由一组密切相关的命令组成；
6.编辑栏：
由名称框、取消输入、确认输入、插入函数、编辑框组成；
7.工作表区：
进行录入、编辑等操作的区域；
8.工作表标签Sheet（N）用于切换不同的工作表；
9.列标：
用英文字母表示（例：
A、B、C……XFD）
10.行号：
用数字表示（例：
1、2、3……1048576）；
11.滚动条分为垂直滚动条和水平滚动条两种。
利用滚动条可使文档左右或上下移动，确定显示范围，以方便查看文档的内容。
；
注：
按CTRL+方向键可以快速完成首行/末行，首列/末列的切换。
12.状态栏：
显示当前程序的工作状态和其他信息（视图切换按钮、显示比例滑动条）
四.基本术语
1.单元格：
是组成工作表的最基本单位，不能拆分。
单元格地址=列标+行号（如A1、B5、C8等）
单元格区域=左上角单元格:
右下角单元格（如B2：
E10）
2.活动单元格：
当前可以直接输入内容的单元格
注：
活动单元格的地址显示在名称框中。
3.工作表：
由多个单元格连续排列形成的一张表格。
注：
Excel2007每张工作表由1048576行和16384列组成，而Excel2003每张工作表由65536行和256列组成，工作表名称均用Sheet表示。
4.工作簿：
就是计算和储存数据的文件，是多张工作表的集合。
注：
一个工作簿中最多有255个工作表，默认3张，工作簿名称用Book表示，扩展名为xlsx
五.工作簿的基本操作
1.新建工作簿
（1）.Office按钮/新建
（2）.Ctrl+N
2.保存工作簿
（1）.Office按钮/保存
（2）.Ctrl+S
（3）.F12
注：
保存时，如果工作簿已存在，将以最新内容代替原来内容，保存完毕不做任何提示；如果工作薄是新建工作簿，则弹出另存为对话框，输入新名称。
3.关闭工作簿
（1）.Office按钮/关闭
（2）.CTRL+F4
（3）.CTRL+W
注：
1、如果想同时关闭所有打开的工作簿，可以先按SHIFT键，在文件/全部关闭；
2、关闭工作簿，不是退出程序；
4.打开工作簿
（1）.Office按钮/打开
（2）.Ctrl+O
5.工作簿的加密保护
Office按钮/准备/加密文档，可以设置打开和修改的权限密码
第二讲工作表基本操作
一.工作表的基本操作
1.工作表的选定
（1）.单个工作表的选定：
单击工作表标签
（2）.多个相邻工作表的选定：
单击第一个工作表标签，shift+单击最后一个工作表标签
（3）.多个不相邻工作表的选定：
ctrl+单击工作表标签
注：
工作组1、形成：
多个工作表同时被选定
2、作用：
可以一次对多个工作表完成相同的操作（复写纸）。
3、取消：
方法1：
右击工作组任一成员标签，选“取消成组工作表”
方法2：
单击工作组外任一成员标签
2.工作表的添加
（1）.添加当前工作簿中的工作表
A.单击插入工作表按钮
B.右击工作表标签/插入
选中连续的多个工作表后执行插入操作，可同时插入多个工作表。
或按F4重复操作。
（2）.改变新建工作薄中的工作表数
office按钮/excel选项/常用/设置新建工作簿中的工作表数
3.工作表的重命名
（1）.双击工作表标签
（2）.右击工作表标签/重命名
（3）.开始/格式/重命名单元格
4.工作表的移动
（1）.同一工作薄移动：
鼠标指向工作表标签/拖动
（2）.不同工作薄移动
5.工作表的复制
（1）.同一工作薄复制：
Ctrl+拖动
（2）.不同工作薄复制
6.工作表的删除
（1）.右击工作表标签/删除
（2）.开始/删除/删除工作表
二.单元格的基本操作
1.单元格选定
（1）.单个单元格的选定：
单击
（2）.多个不连续的选定：
ctrl+单击
（3）.多个连续的选定
1.按住左键直接拖动
2.按shift键+单击
3.名称框输入
大范围的选择通过上述两种方法都不方便，如选择A1到E50000。
快速完成
大范围选定的方法是在名称框中输入左上角地址：
右下角地址，如A1：
E50000。
（4）.一行的选定：
单击行号
（5）.一列的选定：
单击列标
（6）.工作表：
单击行号与列标的交界处或Ctrl+A
2.单元格的重命名
为单元格命名的优点是能使单元格更容易记忆，而且还可以进行快速定位操
作，并且在编辑复杂公式时事半功倍。
（1）.选中目标单元格，在名称框中输入新名，按Enter键；
注：
重命名的原则
A.必须以汉字或字母开头
B.长度不超过256个字符
C.不能包含空格
D.不能使用类似的形式，如A1,B5
（2）.公式/名称管理器
第三讲美化表格
一.表格结构调整
1.插入行、列
（1）.选中要插入的行（列）数/右击/插入/行（列）/确定
（2）.开始/单元格/插入
2.删除行、列
（1）.选中要删除的行（列）/右击/删除
（2）.开始/单元格/删除
3.行高、列宽
（1）.选定行（列）/格式/行（列）/行高（列宽）/设置数值
（2）.光标置于行号（列标）分界线处，按左键拖动
注：
选定多个行号（列标）拖动可同时调整为相同值
行高单位是磅：
1磅=0.35mm列宽单位是0.1英寸：
1英寸=25.4mm
4.自动调整行高/列宽
（1）.选定行（列）/格式/行（列）/自动调整行高（列宽）
（2）.光标置于行号（列标）分界线处双击
注：
选定多个行号（列标）可同时设置
5.隐藏行、列
（1）.选择要隐藏的行（列）/格式/行（列）/隐藏
二.表格格式设置
1.数字选项卡；
2.对齐选项卡；
3.字体选项卡；
4.边框选项卡；
5.填充选项卡；
6.保护选项卡：
（1）.锁定：
锁定单元格内容不被修改；
（2）.隐藏：
隐藏公式；
（3）.方法：
只有保护工作表后，锁定或隐藏单元格才有效。
三.单元格样式
1.作用：
指一组特定单元格格式的组合，可快速完成相同格式单元格的格式化，提过工作效率并使工作表格式规范统一。
2.应用内置样式
3.创建自定义样式
4.合并样式
创建的自定义样式，只会保存在当前工作簿中，不会影响到其他工作簿的样式，如需要在其他工作簿中使用当前新创建的自定义样式，可以使用合并样式来实现。
四.自动套用格式
1.作用：
将计算机中已有格式应用到当前表格
2.套用格式
3.更换格式
4.清除格式
五.条件格式
1.作用：
设立不同条件范围，符合不同范围的数据以不同格式进行显示。
2.方法：
第四讲数据编辑
一.数据输入
1.文本输入：
直接输入/回车
（1）.长文本输入：
右侧单元格若无内容，会延续到下一单元格；右侧单元格若有内容，则部分文本会被隐藏。
（2）.文本格式数字：
‘（英文状态下） + 数字
例：
身份证号、邮编、电话号码、学生证号、VIP卡号、前带0的数字等。
注：
文本型数据默认的对齐方式是左对齐；
2.数值输入：
不同数值输入方法不同
（1）.整数和小数：
定位/直接输入/ENTER；
（2）.负数：
利用负号或括号；
（3）.分数：
以“0”加空格开头，再输入分数（直接输入会变为日期）
注：
输入后结果单元格中以分数显示，编辑栏中以小数形式显示；
3.日期输入：
用“/”或“—”隔开，例：
2006/12/4或2006-12-4；
注：
1、任意日期：
直接输入；
2、当前日期：
Ctrl+；
4.时间输入：
用“：
”隔开，例：
19：
47；
注：
1、任意时间：
直接输入；
2、当前时间：
Ctrl+Shift+：
5.公式或函数
公式、函数是Excel中一种非常重要的数据，Excel很多计算功能都是通过公式或函数来实现的。
6.逻辑值
逻辑值是比较特殊的一类参数，它只有TRUE（真）和FLASE（假）两种类型。
7.错误值
这类数据往往是用户输入错误之后Excel给予的提示信息，如#####：
单元格所录入的数据比单元格宽，#DIV/0：
公式被零除……
二.数据填充
1.等值填充
（1）.输入/选定/点击“填充柄”/拖动；
（2）.选定一个区域/在编辑栏输入填充内容/Ctrl+Enter；
2.等差序列填充：
输入前两个值并选定，拖动填充柄
3.等比序列填充
4.序列填充
（1）.预定义序列：
11种
（2）.自定义序列：
Office按钮/Excel选项/常用/创建自定义列表，输入序列值（按ENTER分隔）/添加
三.数据修改
1.修改
（1）.部分：
双击目标单元格/选定/输入修改或直接利用编辑栏修改
（2）.全部：
选定/直接修改
2.删除
（1）.选中/Delete
注：
只删除内容，保留格式
（2）.选中/编辑/清除/内容、格式、批注、全部
3.移动
（1）.选中/剪切/定位/粘贴
（2）.选中/拖动到目标位置
注：
移动复制时，将指针置于选区的边缘拖动
4.复制
（1）.选中/复制/定位/粘贴
（2）.选中/Ctrl+拖动
5.选择性粘贴
（1）.作用：
有选择地复制单元格中的数据；
（2）.操作：
选中/复制/编辑/选择性粘贴/选择需要粘贴的内容/确定；
1.全部：
复制单元格中的全部内容，与粘贴命令相同
2.公式：
仅复制单元格中的公式
3.数值：
仅复制单元格中的数值
4.格式：
仅复制单元格中设定的格式
5.批注：
仅复制单元格中的批注（如果有批注）
6.有效数据：
仅复制单元格中所定义的数据有效性规则
7.边框除外：
复制单元格中除边框以外的全部属性
8.跳过空单元：
避免复制区中的空格替换粘贴区中的数值
9.运算：
将被复制区的内容与粘贴区中的内容经本选项指定的方式运算后，放置在粘贴区内
10.转置：
将被复制的内容在粘贴时转置放置，即把一行数据换成工作表的一列数据
四.查找和替换
1.查找：
选中要查找区域/编辑/查找和选择/查找（CTRL+F）
2.替换：
选中要替换区域/编辑/查找和选择/替换（CTRL+H）
第五讲公式
一.公式概述
1.定义：
指由“=”、运算体和运算符在单元格中按特定顺序连接而成的运算表达式。
2.运算体：
指能够参与运算的数据或数据所在的单元格地址、函数等。
3.运算符：
指使Excel自动执行特定运算的符号。
（1）.算术运算符：
+，—，*，/，%，^（脱字号：
乘方）等
（2）.比较运算符：
=，<>,>=,<=,>,<（注意区别写法）
比较运算的结果是逻辑值TRUE（真）或FALSE（假）
（3）.文本运算符：
&（与号：
连接文本）
（4）.引用运算符
A.联合运算符：
（，）对多个不连续单元格进行引用。
例如：
=sum（a1,b1,c1,d1）,求a1,b1,c1,d1四个单元格的和。
B.区域运算符：
（：
）对单元格区域中的所有单元格进行引用。
例如：
=sum（a1:
d1）,求a1,b1,c1,d1四个单元格的和.。
C.交叉运算符：
（空格）引用两个单元格区域的交集。
例如：
=sum（a1:
c1b1:
d1）,求的是b1,c1两个单元格的和。
二.创建公式
1.创建公式
（1）.选择结果单元格；
（2）.以“=”开头，依次输入运算体和运算符；
（3）.敲回车或单击工具栏上的“√”；
2.注意事项
（1）.输入公式时，一般在英文状态下输入；
（2）.结果单元格：
用于存放公式显示结果的单元格；
（3）.引用单元格：
公式中出现的被引用到的单元格；
三.编辑公式
1.修改公式
（1）.双击含公式的单元格（F2）
（2）.选中公式单元格，直接在编辑栏中修改
2.删除公式
（1）.选中公式单元格/delete
（2）.选中公式单元格/编辑/清除/内容
四.单元格的引用
1.相对引用
（1）.相对地址：
例A1，B2等
（2）.特点：
当复制公式时，引用单元格的地址会随结果单元格位置的改变而发生相应的变化。
2.绝对引用
（1）.绝地地址：
$列标$行号例$A$1,$B$2等
（2）.特点：
当复制公式时，引用单元格的地址不会随结果单元格位置的改变而发生相应的变化。
3.混合引用
（1）.混合地址：
$列标行号或列标$行号例$A1或A$1
（2）.当复制公式时，单元格的地址中行或列中的一项会随结果单元格位置的改变而发生相应的变化
4.在三种引用间切换的方法：
光标定位于单元格地址上，按F4键
五.跨工作表单元格引用
六.跨工作簿单元格引用
第六讲函数
一.函数概述
1.定义：
由EXCEL提供的可供直接调用的一种特殊公式，Excel2007提供了10大类上百种函数，每种函数都有不同的功能和用途。
2.组成
（1）.=
（2）.函数名：
可以使用小写输入，确定后自动变为大写
（3）.小括号
（4）.参数：
计算和处理的必要条件，类型和内容因函数而不同。
有的函数有参数，有的函数没有参数，如TODAY（），NOW（）就不需要参数。
3.使用
（1）.先选定结果单元格//单元格或编辑栏输入函数//Enter
（2）.先选定结果单元格/公式/fx插入函数
（3）.先选定结果单元格/单击编辑栏上的fx按钮
（4）.使用函数库插入已知类别的函数
（5）.先选定结果单元格/公式/自动求和
（6）.先选定结果单元格/开始/编辑/自动求和
二.数学和三角函数
1.SUM（求和）
2.SUMIF（条件求和）
（1）.使用格式：
=SUMIF（条件区域，“条件”，运算范围）
（2）.例题：
分别计算性别为男和女各科成绩的总分
=SUMIF（$B$2:
$B$6，“男”，C2:
C6）
3.ABS（求绝对值）
4.INT（将数值向下取整为最接近的整数）
5.MOD（求余数）
使用格式：
=MOD（被除数，除数）
6.ROUND（用来对数值进行四舍五入）
（1）.使用格式：
=ROUND（需要进行四舍五入的数值值,表示需要取多少位的参数）
>0时，表示对小数点后对应位数进行四舍五入。
=0时，表示则将数字四舍五入到最接近的整数。
<0时，表示对小数点左侧前几位进行四舍五入。
三.统计函数
1.MAX（最大值）
2.MIN（最小值）
3.COUNT（计数）
4.AVERAGE（平均值）
5.COUNTIF（条件计数）
使用格式：
=COUNTIF（数据范围，“条件”）
6.AVERAGEIF（条件求平均值）
（1）.使用格式：
=AVERAGEIF（条件区域，“条件”，运算范围）
（2）.例题：
分别计算性别为男和女各科成绩的平均分
7.RANK（返回一个数值在一组数值中的排位）
（1）.使用格式：
=RANK（单元格地址，数据范围,升/降序）
0表示按降序排列,1表示按升序排列。
降序排列时0可省略
（2）.例题:
根据总分从大到小求王伟的名次
=rank（h3,$h$3:
$h$17,0）
四.日期与时间函数
1.YEAR（返回某日期对应的年份）
2.MONTH（返回某日期对应的月份）
3.DAY（返回某日期对应的天数）
4.TODAY显示计算机中当前日期
使用格式：
=TODAY（）
5.NOW显示计算机中当前时间
使用格式：
=NOW（）
6.DATEDIF计算指定起始日和结束日间的时间间隔
（1）.使用格式：
=DATEDIF（起始日期，结束日期，“指定日期的计算方式”）
日期的计算方式
Y：
计算期间内的整年数
M：
计算期间内的整月数
D：
计算期间内的整日数
（2）.例题：
求王伟的工龄？
=DATEDIF（B2,TODAY（）,”Y”）
A
B
C
1
姓名
入职时间
工龄
2
王伟
2007-09-16
五.文本函数
1.LEN（返回文本字符串中的字符数）
2.RIGHT（右侧截取字符）
使用格式：
=RIGHT（要截取的字符，截取字符个数）
3.LEFT（左侧截取字符）
使用格式：
=LEFT（要截取的字符，截取字符个数）
4.MID（中间截取字符）
使用格式：
=MID（要截取的字符，从第几位开始截取，截取字符个数）
六.逻辑函数
1.IF：
根据条件判断，显示不同的结果
（1）.使用格式：
=IF（条件表达式，条件成立，条件不成立）
（2）.嵌套函数:
七.查找与引用函数
1.MATCH（返回指定数值在指定数组区域中的位置）
（1）.使用格式：
=MATCH（查询的指定内容,查询的指定区域，查询的指定方式用数字-1、0或者1表示）
-1：
查找大于或等于指定内容的最小值，而且指定区域必须按降序排列；
0：
查找等于指定内容的第1个数值；
1：
查找大于或等于指定内容的最大值，而且指定区域必须按升序排列；
2.INDEX（返回指定位置的数据）
（1）.使用格式：
=INDEX（要返回数值的单元格区域或数组，返回值所在的行号，返回值所在的列标）
3.LOOKUP
4.VLOOKUP
5.HLOOKUP
第七讲数据处理
（一）
一.排序
1.定义及分类：
针对某些列的数据，重新组织行的顺序，使其按升序、降序或特定顺序排列。
Excel提供了快速排序和多关键字排序两种排序方法。
2.快速排序：
使用“升序”或“降序”按扭
注：
只能指定一个关键字，且只能按数值进行排序；
3.多关键字排序：
使用排序对话框
（1）.按数值排序
1.数字：
按数值大小排序，如8>5；
2.日期或时间：
按先后顺序，如2015-8-1>2015-1-1
3.大小写字母：
按在字母表中的顺序，如D>A，A>a；
4.文本：
按汉语拼音中的字母在字母表中的顺序排序或按笔划排序，如张三>李四，张小五>张三丰；
（2）.按单元格颜色排序
（3）.按字体颜色排序
（4）.按单元格图案排序
注：
可以指定多个关键字，且排序依据四选一；
二.筛选
1.定义及分类：
设定一定条件，显示符合条件的记录，隐藏不符合条件的记录。
Excel提供了自动筛选和高级筛选两种筛选方法。
2.自动筛选
（1）.使用自动筛选：
选定数据库中任意单元格/数据/筛选/自动筛选/单击字段名后下拉按钮设置
1.按数据特征筛选
A.数字筛选
a.比较筛选
b.前10个
c.高/低于平均值
d.自定义筛选
B.文本筛选
a.等于或不等于
b.开头是或结尾是
c.包含或不包含
C.日期筛选
2.按颜色筛选
3.特定值的筛选（等于某一值）
4.多列数据筛选
（2）.取消自动筛选：
筛选框下的清除或筛选命令组中清除
（3）.取消筛选按钮：
单击排序和筛选命令组的筛选
3.高级筛选
（1）.使用高级筛选：
建立条件区域/单击数据清单内的任意单元格/数据/筛选/高级筛选/选择结果的位置、数据区域、条件区域、（复制到的位置）/确定
（2）.取消高级筛选：
同自动筛选
第八讲数据处理
（二）
一.分类汇总
1.概念：
按某一字段（列）对所有记录（行）进行分类，并对某些字段进行汇总运算的数据分析方法。
2.简单分类汇总：
数据/分类汇总；
3.多级分类汇总：
多次重复使用简单分类汇总，但不要替换当前分类汇总；
注：
无论简单还是多级分类汇总谨记：
先对分类字段排序，然后再分类汇总
4.删除分类汇总
5.分级显示结果
二.分级显示
1.作用：
将某个范围的单元格关联起来，从而可将其折叠或展开。
Excel提供了自动和手动两种方法以建立分级显示。
2.自动建立分级显示（汇总项目必须是用公式或者函数求出来的）
3.手动建立分级显示（通过组合、取消组合实现）
4.清除分级显示
三.合并运算
1.作用及分类：
将具有相似结构或内容的多个表格合并到当前工作表中。
Excel提供了按类别合并和按位置合并两种合并方法。
2.按类别合并
（1）.选择结果表格的位置，并定位左上角单元格；
（2）.数据/合并计算
A.函数——选合并计算方式
B.引用位置——分别选定合并数据（不含标题）/添加
C.选中“首行”和“最左列”
（3）.确定
3.按位置合并：
同按类别合并，只是不选中“首行”和“最左列”
4.建连至源数据的连接——如果源数据被修改时，自动更新合并计算，就选取，否则不选。
5.具体应用
（1）.多表汇总计算
（2）.筛选不重复值
（3）.数值型数据核对
（4）.文本型数据核对
四.数据有效性
1.定义：
通常用来限制单元格中输入数据的类型和范围，防止用户输入无效数据。
此外，用户还可以使用数据有效性定义帮助信息，或是圈释无效数据。
2.设置有效性
（1）.任何值：
在单元格输入任何数据不受限制
（2）.整数：
限制单元格只能输入整数
（3）.小数：
限制单元格只能输入小数
（4）.序列：
要求在单元格中必须输入某一特定序列中的内容项，序列中的内容可以是单元格引用、公式，也可以是手动输入
（5）.日期：
限制单元格只能输入日期
（6）.时间：
限制单元格只能输入时间
（7）.文本长度：
限制输入数据的字符个数
（8）.自定义：
主要通过公式与函数实现更为复杂的条件
3.输入信息
4.出错警告
5.取消有效性
6.圈释无效数据及取消
五.分列
六.删除重复项
第九讲图表
一.图表作用
Excel的图表功能就是数据图形化，将工作表中的数据转换为各种统计图表的形式。
与工作表相比，图表更为清晰直观、易于理解。
二.图表类型
升级会员 