Excel办公软件培训课件

2018-05-17
目录：
Excel基本操作与数据处理
01
Excel数据分析
02
Excel公式与函数
03
Excel图表与图形
04
•启动与退出Excel •创建工作簿与界面介绍•工作簿操作•单元格操作
第一章：Excel 基础操作与数据处理基础篇提升篇•设置表格边框和底纹
•设置单元格格式、行高和列宽•数据录入
•启动与退出Excel •创建工作簿与界面介绍•工作簿操作•单元格操作
•选择性粘贴
•窗口冻结
•单元格分列
第一章：Excel基础操作与数据处理•启动与退出Excel
1、启动
（1）利用“开始”菜单启动Excel2016；（2）利用桌面上的快捷图标启动；
（3）直接利用已创建的文档；
2、退出
（1）双击Excel窗口左上角的控制菜单图标；（2）选择“文件”菜单中的“退出”命令；（3）单击窗口右上角的“关闭”按钮；（4）按快捷键Alt+F4；
•创建工作簿
桌面--右键--新建Excel文档
第一章：Excel 基础操作与数据处理
快速访问工具栏
编辑
栏
单元格
名称
工作表区/标签状态
栏
工作表
视图
后台视
图功能区/选项
/命令
•界面介绍
第一章：Excel基础操作与数据处理
•工作簿（重命名、移动、复制、删除）
1、重命名
光标移至sheet名，双击；
2、移动和复制
同一文档内移动：左键+拖移，复制：Ctrl+左键+拖移；
3、删除
选择sheet--右键--删除；
•单元格（插入、删除、移动、复制、清除）
1、插入：选择单元格--右键--插入、删除；
2、移动和复制：Ctrl+C/Ctrl+V；
3、清除：delete；
备注：对应需要处理的sheet表，通常可备份复制一个，防止错误操作；
第一章：Excel基础操作与数据处理
•设置表格边框和底纹
1、选中需设置的单元格--选择边框/底纹；
2、奇偶行填充：
“开始”>“条件格式”“新建规则”>“使用公式确定要设置格式的单元格”，输入公式=MOD(ROW(),2)=0 并选取一个填充颜色，即可将所有行号为偶数的行添加颜色；
•设置单元格格式、行高和列宽
1、单元格格式：选中单元格--右键--在数据栏选择格式；
2、行高和列宽：光标移至表格顶端--选择单元格（行/列）--设置行高/列宽；
3、批量调整：选择批量行/列；
•数据录入（文本、数值、日期、序列数、时间、批量数据、自动填充）
输入文本后设置单元格格式（一般使用格式刷）；
自动填充：光标移至单元格右下角--出现“+”（自动填充柄）--拖移；
小提示：
如需增加新序列，路径：
Excel2003中：工具--选项--自定义序列；
Excel2007中：office按键--选项--常规--编辑自定义列表；
Excel2010中：文件--选项--高级--常规--编辑自定义列表；
Excel2016中：开始--排序和筛选--自定义排序--自定义系列；
使用以下快捷建可快速插入当前日期或时间：
在单元格插入当前日期（ctrl+；），插入当前时间（Ctrl+shift+；），输入（Ctrl+；）后输入一个空格，再按（Ctrl+shift+；），可以插入当前日期和时间，不会自动刷新；
强制换行（alt+enter），自动换行enter；
•选择性粘贴
带实时预览的粘贴功能
粘贴方式常用功能：
【公式】：只粘贴文本和公式，不粘贴字体、格式（字体、对齐、文字方向、数字格式、底纹等）、边框、注释、内容校验等；
【数值】：只粘贴文本，单元格的内容是计算公式的，只粘贴计算的数字结果，这两项不改变目标单元格的格式；
【图片】：将表格以图片形式复制，可以较好的保持原有的形状；
•选择性粘贴
【格式】：仅粘贴源单元格格式，但不能粘贴单元格的有效性，粘贴格式，包括字体，对齐，文字方向，边框，底纹等，不改变目标单元格的文字内容（内容相当于格式刷）；
【批注】：把源单元格的批注内容拷贝过来，不改变目标单元格的内容和格式；
【有效性验证】：将复制单元格的数据有效性规则，粘贴到粘贴区域，只粘贴有效性验证内容，其它不变；
【除边框外的所有内容和格式】：粘贴除边框外的所有内容和格式，保持目标单元格和源单元格相同的内容和格式；
【列宽】：将某个列宽或列的区域粘贴到另一个列或列的区域，使目标单元格和源单元格拥有同样的列宽，不改变内容和格式；
【公式和数字格式】：仅从选中的单元格粘贴公式和所有数字格式选项；【值和数字格式】：仅从选中的单元格粘贴值和所有数字格式选项；【置换】：行列进行互换；
•数据有效性：
目的：方便录入、规范输入、利用预定的条件校验数据，以防止无效数据的产生；
例如，对部门列，用下拉菜单选择代替手工输入。

操作步骤：
数据--数据验证（数据有效性）--设置--“允许”中选择“序列”--来源栏中输入允许输入的值（优化部、竞价部、阿里部）；
中间用英文逗号隔开；
•冻结窗口
效果：始终显示首行的标题栏（或者首列）；
操作：选中开始冻结的单元格--视图--冻结窗口；
•数据分列
Excel中分列是对某一数据按一定的规则分成两列以上；分列时，选择要进行分列的数据列或区域，再从数据菜单中选择分列，分列有向导，按照向导进行即可；关键是分列的规则，有固定列宽，但一般应视情况选择某些特定的符号，如空格、逗号、分好等。

分列前需保证所分的新列不会替换到原有的数据；
由一列分为两列，需保证紧邻分列行有一列空白行；若分为三列，需保证有两列空白列；若无空白列，选中分列紧邻的右边列，右键插入列；
•插入形状/超链接/文本框/对象
插入形状/超链接/文本框按导向进行即可；插入对象的操作步骤：
对象--由文件创建--浏览--显示为图标--更改图标--更改标题--确定；
•打印设置（页边距、页面缩放、打印区域）假如Excel表格编辑的最后一项任务是打印，在实际工作中，我们的打印任务需要一定的设置；比如只打印工作表中需要的区域，或者希望打印公式本身而不是公式的计算结果等；Excel可提供灵活的打印选择；
依据打印区域的形状，选择“纵向”或“横向”设置；
若计划打印到一张上面，可设置缩放，将工作表调整为一页；
目录：
Excel基本操作与数据处理
01
Excel数据分析
02
Excel公式与函数
03
Excel图表与图形
04
•数据的排序与筛选
第二章：Excel 数据分析基础篇提升篇•数据的分类汇总与数据透视
•数据的排序
Excel数据的排序是把一列或多列无序的数据变成有序的数据，这样更能方便的管理数据，在进行数据排序时可以按照默认排序顺序，也可以按照单列或多列排序顺序和自定义排序顺序来排序；
排序时，Excel将利用制定的排序顺序重新排列行、列以及各单元格；
操作：选择标题栏--开始--排序与筛选--自定义排序--选择是否带扩展（扩展同行数据会联动）--添加筛选条件；
•数据的筛选
选择标题栏--开始--排序与筛选--筛选；
操作：单击下拉菜单键--选择颜色或勾选需要显示的内容；
•数据的分类与汇总
对重复数据中的值进行分类汇总；
如下表，依据“部门”为分类字段，选定汇总项“语种”，则汇总结果出现在语种所在列的下方；
•数据透视表
数据透视表是交互式报表，可快速合并和比较大量数据；可旋转行与列，以便看到源数据的不同汇总，可进行特定条件的筛选统计汇总；
•数据透视表
数据透视表选中某列指标，右键--值字段设置，可选择该字段的计算类型；
求和：所有相关数值相加；
计数：该字段出现的频次；
•数据透视表
透视表字段设置，常用的选项为：排序（升序/降序）、值汇总依据（计算类型）、值显示方式（参照下图）、值字段设置、数据透视表选项、隐藏字段列表；
目录：
Excel基本操作与数据处理
01
Excel数据分析
02
Excel公式与函数
03
Excel图表与图形
04
•单元格引用相对引用绝对引用混合引用
第三章：Excel 公式与函数
基础篇
提升篇
•常用函数基础运算函数COUNT 函数日期和时间函数
•进阶函数IF 函数
VLOOKUP 函数SUMIF 函数字符串截取函数
•嵌套函数
SUMPRODUCT 函数多重IF 函数
•单元格引用
主要分为三种：绝对引用($C$1)、相对引用(C1)、混合引用($C1、C$1)；
相对引用：在一个工作表中，在C4、C5中的数据分别是60、50；若在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的内容就是变成了50，里面的公式就是“=C5”，将D4向右拖动到E4，E4中的内容是60，里面的公式变成了
“=D4”；
列绝对引用：在D4单元格中输入“= $C4”，将D4向右拖动到E4，E4中的公式还是“= $C4”，向下拖动到D5时，D5中的公式就成了“= $C5”；
行绝对引用：若在D4单元格中输入“=C$4”，将D4拖动到E4时，E4公式为
“=D$4”，而将D4向下拖动到D5时，D5中的公式还是“=C$4”；
绝对引用：若在D4中输入“=$C$4”，不管将D4向任何方向拖动，自动填充的公式都是=$C$4 ；谁在前面带上了“$”号，在进行拖动的时候就不变；若都带上了$，在拖动时两个位置都不变；
•常用函数
分类
函数名称作用说明
数据源函数公式运算结果SUM 求和1,2,3,4=SUM(A2:A6)10AVERAGE 求平均1,2,3,4=AVERAGE(A2:A6) 2.5MAX
取最大值1,2,3,4=MAX(A2:A6)4MIN 取最小值
1,2,3,4=MIN(A2:A6)1ROUND 对数字四舍五入的保留指定位数的小数点位数 3.1415726=ROUND(A2,2) 3.14LEFT 对目标依次从左边开始提取相应的字符串5216688=LEFT(A2,3)521RIGHT 对目标依次从右边开始提取相应的字符串5216688=RIGHT(A2,4)6688LEN 统计单元格内的字符数
2018=LEN(A2)4COUNT
统计数值类单元格个数（只有数字类的单元格才被统计）1,a,3,4=COUNT(A2:A6)3COUNTA 统计非空单元格个数（含空格、日期、逻辑值、文字的单元格）1,a,3,4=COUNTA(A2:A6)4COUNTIF 统计符合指定条件的单元格个数（非文本格式下的数字才被统计）1,2,3,4=COUNTIF(A2:A6,">2")
2YEAR 提取年2018-5-19 09:58=YEAR(A2)2018MONTH
提取月2018-5-19 09:58=MONTH(A2)5DAY 提取日2018-5-19 09:58=DAY(A2)19HOUR 提取小时2018-5-19 09:58
=HOUR(A2)9TODAY
返回当前日期
无
=TODAY（）
2018-5-19
基础运算函数
COUNT函数
日期和时间函数
•进阶函数
IF函数
根据逻辑表达式测试的结果，返回相应的值；函数语法：IF（条件表达式，结果1，结果2）语法理解：如果~~~就~~~否则~~~；VLOOKUP函数
在表达或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值；函数语法：VLOOKUP（查找值，查找区域，返回结果的列号，查找方式）
语法理解：在“查找区域”的首列查找“查找值”，并由此返回区域内当前行中指定列的数值；
•进阶函数
SUMIF函数
根据指定条件对若干单元格、区域或引用求和；
函数语法：SUMIF（条件判断区域，条件，求和区域）
语法理解：如果~~~满足什么条件~~~就求和，其实就是按条件求和；
MID函数
函数语法：MID（文本串，文本中要提取的第一个字符的位置，要提取的字符个数）
开始日期
结束日期
UNIT类型
函数公式
运算结果“Y”两个日期间隔年数（通常用计算年龄）=DATEDIF(A2,B2,"Y")27“M”两个日期间隔整月数（通常用计算年龄）
=DATEDIF(A2,B2,"M")331“D”时间段中的天数
=DATEDIF(A2,B2,"D")10077“MD”两个日期间隔的天数，忽略年份和月份之差=DATEDIF(A2,B2,"MD")3“YM”两个日期间隔的月份数，忽略年份之差=DATEDIF(A2,B2,"YM")7“YD”两个日期间隔的天数，忽略年份之差
=DATEDIF(A2,B2,"YD")
215
2018-5-19
1990-10-16
•进阶函数
DATEDIF 函数
返回两个日期之间的年/月/日间隔数；
函数语法：DATEDIF （开始日期，结束日期，单位代码）
年龄或年限的计算方法（在英文输入状态下进行）
语法：=YEAR （“终止日期”）-YEAR （“出生或开始日期”）
•嵌套函数
SUMPRODUCT函数
1、计算相应的区域或数组乘积的和
公式运算=SUMPRODUCT（A2：A6，B2：B6，C2：C6）
实际运算=A2*B2*C2+ A3*B3*C3+ A4*B4*C4+ A5*B5*C5+ A6*B6*C6
2、计算符合2个及以上条件的数据个数或数据的和
公式运算=SUMPRODUCT(($C$1:$C$9=$G5)*($D$1:$D$9=H$4),$E$1:$E$9)实际运算=SUMPRODUCT（条件1*条件2，要引用的数据区域）
3、多条件引用/匹配
公式运算=SUMPRODUCT(($C$1:$C$9=$G5)*($D$1:$D$9=H$4)*$E$1:$E$9)实际运算=SUMPRODUCT（条件1*条件2*要引用的数据区域）
•嵌套函数
IF函数嵌套
IF函数嵌套就是有多个IF函数，一层套一层；当有多个条件要分别判断时会用到，
语法：=IF（true，1，IF（true，2，IF （true，3，“end”）））
语法解释：IF（第一个条件，第一个条件成立时返回的值，IF（第二个条件，第二个条件成立时返回的值，IF（第三个条件，第三个条件成立时返回的值·······IF（））））
分数评级的示例：=IF(Q64<60,"不及格",IF(Q64<70,"及格",IF(Q64<85,"良好","优秀")))
目录：
Excel基本操作与数据处理
01
Excel数据分析
02
Excel公式与函数
03
Excel图表与图形
04
•图表图形的作用•使用图表的步骤•图表要素
第四章：Excel 图表与图形
基础篇
操作篇
•柱形图
•条形图介绍•饼图
•
图形美化
•图表图形的作用
1、图表是一种高效、直观、形象的表达工具；
迅速传达信息，轻松理解；直接专注重点，直奔主题；更直观地显示其相互关系；使信息的表达形象生动；
2、图形六要素：
标题、横轴、纵轴、条形、数值、图例；
•使用图表的步骤
•使用图表
步骤1、确定主题
想要表达什么观点？比较图表还是文字更有效？
想要表达的意思若可以一句话表达清楚，就没必要用图表；图表应节省一千句话，而不是需要一千句话来解释，图表越简洁越好；
62
53
66
张三李四王五
Q1业绩达成
20
26
16
1820153016
201月2月3月
业务员月度业绩达成
张三李四王五
姓名1月2月3月Q1张三20261662李四18201553王五
30
16
20
66
根据想要表达的主题确定图形，若主题是“业务员季度达成”就选左图；若主题是“业务员月度达成差异”，就选右图。

•使用图表
步骤2、选择类型
图表的样式很多，常用的主要为：饼图、条形图、柱形图、折线图、散点图；不能凭感觉随便选择，在选择样式前，要先确定好数据间的对比关系，依照对比关系选择图表样式；
•使用图表
步骤3、制作图表
（1）展示在时间序列上，各地区销量的对比情况--柱形图；（2）展示Q1各地区销量的占比情况--饼图；
（3）展示上半年各地区总销量情况--条形图、柱形图；（4）展示各月销量达标趋势--柱形图、折线图；
•使用图表
步骤3、制作图表
展示在时间序列上，各地区销量的对比情况--使用柱形图；步骤如下：（1）选中要展示的数据区域；
（2）菜单栏选择“插入”；
（3）选择“柱形图”--“簇状柱形图”；
•使用图表
步骤4、强调主题
关键点1：不同图表表达的意思不尽相同（选类型）；
关键点2：注意力不同，表达的信息也不相同（做标记）；
关键点3：标题要体现你想表达的关键信息（点题）；
下图：饼图，强调A公司和B公司的组合销售量不同；
右下图，条形图，强调A公司和B公司的销售额所占的百分比随着地域的不同而改变
•使用图表
左下图：突出单个元素的条形图，强调A公司销售额是南部最高，B公司销售额是北部最高；右下图，突出两个重点的条形图，反映在南部地区，A公司与B公司相比在业绩上差额较大；
•使用图表
图表美化原则：
•每张图表都传达一个明确的信息；•图表与标题相辅相成；
•图表元素少而精；
•图表样式清晰易读；
几种美化技巧：
•图例字号比绘图区字号小1-2号；•网格线尽量浅或者没有；
•图形用合适的图片代替；
•图表样式清晰易读；
•选图技巧
第五章：选学内容
常识篇
操作篇
组合图表•柱-折图•双饼图•复合饼图
•选图技巧
（一）成分：占总体的百分比
•表示成分最好使用饼图，在使用中不宜多于6种成分；
•若使用超过6种，则选择6种最重要的，并将未选中的列为“其他”范畴；人眼比较习惯于按顺时针方向进行观察，所以应该将最重要的部分放在紧靠12点钟的位置，并且使用强烈的颜色对比以显示突比，或者在黑色圈中使用最强烈的阴影效果，或者将此部分与其他部分分离开；
•若没有重点突出的部分，则应该考虑让它们以大到小的顺序排列，并且以同一种颜色或者干脆不使用阴影来绘制图表的每一个部分；
•示例说明如右图：
•选图技巧
（二）项目：项目的排名
•在条形图中，要做到保持条形图之间合适的距离；
•使用最能形成对比的颜色及阴影来强调重要的项目，以此来强调信息的标题；
•为了突显数值，可在顶端（或者在底端）使用一个刻度尺，或者在条形末尾标注数字；•若只想简单标识项目间的关联，可用刻度尺；
•若数字标签很重要就用数字；
•使用刻度尺的同时标示了一个需要特殊强调的数值；
•选图技巧
（三）时间：随着时间变化
•选择柱状图或折线图取决于用哪一个比较方便；若图表中只有少的几个点（如
几个），则需用柱形图；反之，若图中
展示20多年来每个季度的变化趋势，则
最好使用折线图；
•柱状图强调的是数量的级别，更适合于在一小段时间内发生的事件，产量的数
据很适合这个领域；
•折线图强调的是角度的运动及图像的变换，因此展示数据的发展趋势最好使用
它；
•选图技巧
（四）频率：项目的分布情况
•两种主要应用，第一种是所有样本中进行归纳，在这里是用来预测风险、可能性或者机会的；
•另一种是描述确定性（不确定性）；当只使用几行数据时，柱形图要好一些，而数量很多时最好使用折线图；
（五）相对性：变量之间的关系
•显示两种变量符合或不符合你所希望出现的模板；
成对条形图方案只在数据组较少时才会起作用；•若数据超过了15组甚至更多时，则最好不要再标记出每个点，应该换为使用一个更加紧密的散点图；
•组合图表
柱-折图（绘制、调整数值轴及数据标志、修改图形颜色）将抽象的数据以容易观察、最有效、最简单的方法描述出来；
制作步骤：
•组合图表
柱-折图制作步骤：
选中“完成率”右键选择“次坐标轴”，然后选中灰色的完成率柱子，右键选择“更改系列图表类型”，换成折线图，同时勾选次坐标轴；
•组合图表
柱-折图制作步骤：
1500
2000
1000
1300
1500
800
87%
75%
80%
68%
70%72%74%76%78%
80%82%84%86%88%0
50010001500
20002500
竞价
优化
阿里
部门任务达成率
目标
业绩
完成率。