EXCEL数据处理与分析217

Excel数据处理与分析应用教程第1章初识ExcelExcel启动与退出 Excel操作界面工作簿基本操作应用实例－创建工资和人事工作薄要点小结第1章初识Excel主要知识点

Excel窗口的组成工作薄的基本概念工作薄的创建工作薄的打开与关闭工作薄的保护1.1Excel启动与退出启动Excel退出Excel1.2Excel操作界面标题栏菜单栏工具栏编辑栏名称框状态栏任务窗格1.3工作簿基本操作认识工作薄创建工作簿保存工作簿打开工作薄保护工作薄关闭工作薄1.3工作簿基本操作认识工作薄创建工作簿保存工作簿打开工作薄保护工作薄关闭工作薄文件扩展名为.XLS每个新工作薄包含3个工作表每个工作薄最多包含255个工作表1.3工作簿基本操作认识工作薄创建工作簿

保存工作簿打开工作薄保护工作薄关闭工作薄1.3工作簿基本操作认识工作薄创建工作簿保存工作簿打开工作薄保护工作薄关闭工作薄初次保存工作薄另存为工作薄自动保存工作薄问题：保存与另存为区别？1.3工作簿基本操作认识工作薄创建工作簿保存工作簿打开工作薄保护工作薄关闭工作薄问题：如何改变文件历史记录？1.3工作簿基本操作认识工作薄创建工作簿保存工作簿打开工作薄保护工作薄关闭工作薄保护工作薄中元素添加工作薄密码1.3工作簿基本操作认识工作薄创建工作簿保存工作簿打开工作薄保护工作薄关闭工作薄问题：如何同时关闭所有打开的工作薄文件？1.4实例－创建工资和人事工作薄创建工资管理工作薄创建人事档案工作薄建立共享工作簿建立副本工作簿合并工作簿第1章初识Excel本章小结了解Excel的工作环境理解工作薄的基本概念掌握工作薄的基本操作Excel数据处理与分析应用教程

第2章建立工作表认识工作薄 快速输入数据选定单元格导入数据输入数据应用实例第2章建立工作表主要知识点

工作表、单元格的概念各种类型数据的输入自动填充数据使用下拉列表自动更正数据查找与替换数据设置数据的有效性数据的导入2.1认识工作表工作表由65536行和256列构成

行用数字1~65536表示；列用字母编号A，B，…，IV2.1认识工作表单元格行与列交叉位置形成的矩形区域单元格地址：列标+行号活动单元格

当前单元格2.1认识工作表单元格区域由若干个单元格构成的矩形区域

单元格区域地址：

单元格地址:单元格地址

2.2选定单元格选定单元格选定单元格区域选定连续的单元格区域选定非连续的单元格区域2.3输入数据数据类型数值型数据由0～9、+、-、（）、，、/、$、%、E或e等组成的串日期和时间是特殊的数值型数据文本型数据逻辑型数据2.3输入数据输入文本输入文本数据时，系统默认为左对齐

单元格中最多可以容纳32000个字符

2.3输入数据输入数值输入普通的数字输入小数型数值

输入货币型数值2.3输入数据输入日期和时间输入日期输入时间

2.3输入数据输入分数值输入顺序是：整数→空格→分子→正斜杠（/）→分母

输入百分比数值输入身份证号码2.4快速输入数据自动填充数据填充相同数据填充等差序列数据填充日期数据填充特殊数据填充自定义序列2.4快速输入数据使用下拉列表使用数据有效性在单元格中创建下拉列表在单元格中设置数据的输入范围设置提示信息2.4快速输入数据自动更正数据查找替换数据2.5导入数据从文本文件导入数据从Access数据库导入数据2.6应用实例－输入工资管理基础数据自动填充数据输入序号输入交通补贴及物价补贴查找替换数据输入性别2.6应用实例－输入工资管理基础数据使用下拉列表输入职务等：使用“从下拉列表中选择”命令输入职称等：使用数据有效性

2.6应用实例－输入工资管理基础数据使用数据有效性输入“基本工资”、“职务工资”、“岗位冿贴”、以及“奖金”等数据设置有效性规则输入特殊数据输入参加工作日期输入身份证号第2章建立工作表本章小结理解工作表、单元格、单元格区域的基本概念掌握数据输入的基本步骤和要点Excel数据处理与分析应用教程

第3章编辑工作表选定当前对象 编辑数据编辑单元格编辑工作表应用实例—维护人事档案表第3章编辑工作表主要知识点

选定当前操作对象增加、修改、清除单元格数据复制、移动单元格数据插入删除行、列或单元格合并单元格插入、删除、移动、复制工作表保护工作表3.1选定当前对象选定行或列选定一行或一列选定连续的行或列选定不连续的行或列

选定整表3.1选定当前对象选定当前工作表设置工作表组选定多个连续工作表（Shfit键）选定多个不连续工作表（Ctrl键）选定工作簿中的所有工作表3.2编辑数据修改数据通过编辑栏修改在单元格内直接修改清除数据使用清除命令使用鼠标快捷操作3.2编辑数据移动与复制数据移动或复制数据选择性粘贴

3.3编辑单元格插入整行、整列或单元格插入有内容的单元格插入空单元格插入行或列

3.3编辑单元格删除整行、整列或单元格删除单元格或单元格区域

删除行或列合并单元格

3.4编辑工作表重命名工作表使用鼠标操作使用菜单命令插入与删除工作表插入工作表删除工作表3.4编辑工作表复制与移动工作表移动工作表复制工作表隐藏或显示工作表隐藏工作表取消隐藏工作表3.4编辑工作表保护工作表保护工作表单元格保护取消不需保护的单元格锁定设置保护工作表3.5应用实例－维护人事档案复制工作表表列的调整选定、复制删除、移动调整列宽工作表的保护第3章编辑工作表本章小结掌握对Excel工作表的基本编辑操作

掌握工作表维护的方法

Excel数据处理与分析应用教程

第4章美化工作表调整行与列 设置单元格格式设置工作表格式使用对象美化工作表应用实例—修饰人事档案表第4章美化工作表主要知识点

调整行高与列宽设置字体格式设置数字格式设置对齐方式设置边框和底纹使用格式刷使用条件格式和自动套用格式使用图片、图形、艺术字4.1调整行与列调整行高与列宽隐藏行与列隐藏行或列取消对行或列的隐藏4.2设置单元格格式设置数字格式使用预定义数字格式创建自定义数字格式<正数格式>;<负数格式>;<零值格式>;<文本格式>

4.2设置单元格格式设置字体格式设置对齐方式设置边框和底纹隐藏网格线设置单元格边框添加底纹和图案4.2设置单元格格式使用格式刷设置条件格式设置条件格式添加条件格式删除条件格式更改条件格式4.3设置工作表格式设置工作表背景添加工作表背景删除工作表背景改变工作表标签颜色自动套用格式4.4美化工作表插入图片插入剪贴画

插入图片文件

绘制图形绘制基本图形

绘制自选图形

使用艺术字4.5应用实例－修饰人事档案表添加标题设置表头格式调整行高列宽居中对齐字体、字形4.5应用实例－修饰人事档案表设置表体格式设置数字格式

设置边框

强调显示某些数据

使用条件格式设置基本工资显示格式第4章美化工作表本章小结掌握对Excel工作表的单元格格式的设置掌握行列格式以及整表格式进行格式设置

Excel数据处理与分析应用教程

第5章打印工作表显示工作表 设置工作表页面打印工作表应用实例－打印人事档案表第5章打印工作表主要知识点

拆分窗口冻结窗格页面设置打印设置5.1显示工作表调整显示方式调整屏幕上的显示元素

设置工作表的显示比例

拆分窗口冻结窗口5.2设置工作表页面设置页面设置页边距设置页眉/页脚设置打印标题5.3打印工作表设置打印区域使用“设置打印区域”菜单命令使用“页面设置”对话框使用“打印内容”对话框

打印预览5.3打印工作表打印工作表打印整个工作薄

打印部分工作表

打印多份相同的工作表

5.4应用实例－打印人事档案表设置人事档案表页面方向

纸张

页边距

居中方式

顶端标题行

网络线

5.4应用实例－打印人事档案表设置人事档案表页眉和页脚页眉页脚打印预览人事档案表

分部门显示人事档案表5.4应用实例－打印人事档案表打印人事档案表打印机名称

打印范围

打印对象

打印份数

第5章打印工作表本章小结掌握显示工作表的各种功能设置掌握打印工作表的各种功能设置

Excel数据处理与分析应用教程

第6章使用公式计算数据认识公式输入公式 编辑公式引用单元格合并计算审核公式应用实例－计算工资应用实例－调整人事档案基本工资第6章使用公式计算数据主要知识点

公式组成运算符种类及用法公式输入与编辑单元格引用合并计算公式审核6.1认识公式什么是公式是对Excel工作表中的数据进行计算和操作的等式。它由前导符等号（=）常量、单元格引用、区域名称、函数、括号及相应的运算符组成

6.1认识公式公式中的运算符算术运算符

文本运算符关系运算符运算符的优先级

6.2输入公式手工输入单击单元格输入

6.3编辑公式复制公式显示公式删除公式

注意：按【Delete】键，将删除包括数值和公式在内的所有内容6.4引用单元格相对引用公式所在的单元格与公式中引用的单元格之间的相对位置相对引用：列标+行号6.4引用单元格绝对引用在公式中引用的固定单元格，无论将该单元格复制或者填充到什么位置，都不发生任何改变在列标和行号前分别加上“$”符号

6.4引用单元格混合引用公式中使用的单元格引用的一部分固定不变，而另一部分自动改变

行使用相对引用，列使用绝对引用

行使用绝对引用，列使用相对引用

6.4引用单元格外部引用引用同一工作薄中的其他工作表

引用不同工作簿中的单元格

[工作簿名字]工作表引用!单元格引用三维引用

工作表名1:工作表名N!单元格引用6.5合并计算按位置合并计算要求合并的各工作表必须格式相同按分类合并计算合并计算的各工作表格式不完全相同6.6审核公式错误信息追踪单元格追踪引用单元格

追踪从属单元格

6.6审核公式追踪错误

添加监视分步查看公式计算结果6.7应用实例－计算工资计算公积金公积金统一按基本工资和职务工资之和的10%计算 则L2的计算公式为：

=(D2+E2)*0.1引用同表中的单元格相对地址:列号+行号(D2,B2)公式中使用相对地址称为相对引用6.7应用实例－计算工资计算医疗险和养老险按基本工资和职务工资之和的2%和4%扣除

则M2的计算公式为：

=(D2+E2)*0.026.7应用实例－计算工资计算应发工资应发工资=(基本工资+职务工资+岗位津贴+工龄补贴+交通补贴+物价补贴+洗理费+书报费+其他+奖金)-(公积金+医疗险+养老险)

则Q2的计算公式为：=D2+E2+F2+G2+H2+I2+J2+K2+O2+P2-(L2+M2+N2)6.7应用实例－计算工资计算实发工资实发工资应发工资-所得税

则Q2的计算公式为：=Q2-R26.8应用实例－调整人事档案基本工资工资的调整统一调整

按不同幅度调整个别调整将员工基本工资统一上调806.8应用实例－调整人事档案基本工资工资的调整统一调整

按不同幅度调整个别调整高级职称上调100元中级职称上调50元初级职称上调30元6.8应用实例－调整人事档案基本工资工资的调整统一调整

按不同幅度调整个别调整合并计算第6章使用公式计算数据本章小结理解相对引用、绝对引用和混合引用的概念

理解按位置合并计算和按分类合并计算的不同特点

理解公式的基本形式

Excel数据处理与分析应用教程

第7章使用函数计算数据认识函数输入函数 常用函数应用实例－计算工资应用实例－计算销售业绩奖金第7章使用函数计算数据主要知识点

函数结构和分类函数输入与编辑数学函数统计函数日期与时间函数逻辑函数文本函数查找与引用函数财务函数7.1认识函数函数结构格式：函数名(参数1,参数2,……)7.1认识函数函数种类日期与时间函数、数学与三角函数统计函数、查找与引用函数数据库函数、文本函数逻辑函数、信息函数

7.2输入函数使用“插入函数”向导输入使用“自动求和”按钮输入手工直接输入函数的嵌套输入7.3常用函数数学函数INT函数MOD函数ROUND函数SUM函数SUMIF注意掌握：格式功能说明灵活应用7.3常用函数统计函数

AVERAGE函数COUNT函数COUNTA函数COUNTIF函数MAX函数MIN函数RANK函数7.3常用函数日期函数DATE函数MONTH函数NOW函数TODAY函数YEAR函数7.3常用函数逻辑函数AND函数IF函数7.3常用函数文本函数FIND函数LEN函数REPLACE函数REPT函数RIGHT函数UPPER函数7.3常用函数查找函数HLOOKUP函数MATCH函数VLOOKUP函数7.3常用函数财务函数FV函数NPER函数NPV函数PMT函数7.4应用实例－计算工资计算洗理费男职工每人每月30元,女职工每人每天月50元 则J2的计算公式为：

=IF(人员清单!D2="男",30,50)

引用不同表中的单元格7.4应用实例－计算工资计算工龄补贴每满一年增加10元,但最高不超过300元

G2工龄的计算公式为

=YEAR(TODAY())-YEAR(人员清单!G2)G2工龄补贴的计算公式为

=MIN(300,(YEAR(TODAY())-YEAR(人员清单!G2))*10)7.4应用实例－计算工资计算所得税7.4应用实例－计算工资计算所得税=IF(Q2<1000,0,IF(Q2<2000,(Q2-1000)*0.05,IF(Q2<5000,(Q2-2000)*0.1+50,(Q2-5000)*0.15+350)))7.5应用实例－计算销售业绩奖金

业绩奖金计算方法简介基本奖金标准及算法

7.5应用实例－计算销售业绩奖金

业绩奖金计算方法简介基本奖金标准及算法奖励奖金标准及算法当累计销售业绩达到5万元时，发放1000元奖励奖金。为了不重复发放奖金，奖励奖金发放后，从累计销售业绩中扣除5万元，剩余金额累计到下月累计销售业绩中

7.5应用实例－计算销售业绩奖金

计算累计销售业绩7.5应用实例－计算销售业绩奖金

计算累计销售业绩=IF(VLOOKUP(A3,'1月业绩奖金表'!$A$3:$G$13,5,0)=0,'1月业绩奖金表'!B3+'1月业绩奖金表'!D3,'1月业绩奖金表'!B3+'1月业绩奖金表'!D3-50000)7.5应用实例－计算销售业绩奖金

计算本月销售业绩=SUMIF(销售情况表!$E$22:$E$33,A3,销售情况表!$H$22:$H$33)

7.5应用实例－计算销售业绩奖金

计算奖金比例=HLOOKUP(D3,奖金标准!$A$3:$F$4,2)7.5应用实例－计算销售业绩奖金

计算奖励奖金=IF((B3+D3)>=50000,1000,0)7.5应用实例－计算销售业绩奖金

计算总奖金=(D3*C3)+E3

7.5应用实例－计算销售业绩奖金

计算累计销售额='1月业绩奖金表'!G3+D3

第7章使用函数计算数据本章小结理解函数的概念及其基本形式

理解函数中各参数含义及其使用方法

灵活运用函数

Excel数据处理与分析应用教程

第8章使用图表显示数据认识图表创建图表 编辑图表修饰图表应用实例－显示销售业绩奖金第8章使用图表显示数据主要知识点

图表组成和种类创建图表编辑图表修饰图表应用图表8.1认识图表图表组成

图例图表区图表标题网络线数据序列数值轴分类轴8.1认识图表图表种类柱形图、条形图、折线图

饼图、XY散点图

面积图、圆环图、雷达图、曲面图气泡图、股股价圆柱图、圆锥图、凌锥图

8.2创建图表使用功能键创建图表使用“图表”工具栏创建图表使用“图表向导”创建图表函数的嵌套输入8.3编辑图表改变图表类型添加数据系列添加数据系列

删除数据系列8.3编辑图表编辑图表对象添加数据系列调整图表对象位置

调整图表对象大小在图表中添加浮动文字

8.4修饰图表设置图表格式设置图表区格式

设置绘图区格式

设置数据系列格式

设置坐标轴格式设置网格线格式

设置图例格式

设置标题格式

8.4修饰图表设置三维图表设置三维图表的高度和角度

设置三维图表的深度和宽度

设置三维图表的图形形状添加趋势线8.5应用实例－显示销售业绩奖金比较销售人员销售业绩创建图表修饰图表组合图表8.5应用实例－显示销售业绩奖金比较销售人员销售业绩创建的图表8.5应用实例－显示销售业绩奖金比较销售人员销售业绩修饰后的图表8.5应用实例－显示销售业绩奖金比较销售人员销售业绩组合后的图表8.5应用实例－显示销售业绩奖金显示超级销售人员计算最大值创建折线图

标识最大值修饰图表

8.5应用实例－显示销售业绩奖金显示超级销售人员计算最大值8.5应用实例－显示销售业绩奖金显示超级销售人员创建折线图8.5应用实例－显示销售业绩奖金显示超级销售人员标识最大值8.5应用实例－显示销售业绩奖金显示超级销售人员修饰图表8.5应用实例－显示销售业绩奖金显示产品销售情况绘制图形

更换数据系列图形

8.5应用实例－显示销售业绩奖金显示产品销售情况绘制图形8.5应用实例－显示销售业绩奖金显示产品销售情况更换数据系列图形第8章使用图表显示数据本章小结理解各种图表的形式以及图表中各组成部分的含义

掌握创建图表的基本要点和方法

掌握各种图表的适应范围

8.5应用实例－显示销售业绩奖金比较销售人员销售业绩创建图表修饰图表组合图表8.5应用实例－显示销售业绩奖金比较销售人员销售业绩创建的图表8.5应用实例－显示销售业绩奖金比较销售人员销售业绩修饰后的图表8.5应用实例－显示销售业绩奖金比较销售人员销售业绩组合后的图表8.5应用实例－显示销售业绩奖金显示超级销售人员计算最大值创建折线图

标识最大值修饰图表

8.5应用实例－显示销售业绩奖金显示超级销售人员计算最大值8.5应用实例－显示销售业绩奖金显示超级销售人员创建折线图8.5应用实例－显示销售业绩奖金显示超级销售人员标识最大值8.5应用实例－显示销售业绩奖金显示超级销售人员修饰图表8.5应用实例－显示销售业绩奖金显示超级销售人员修饰图表8.5应用实例－显示销售业绩奖金显示产品销售情况绘制图形

更换数据系列图形

8.5应用实例－显示销售业绩奖金显示产品销售情况绘制图形8.5应用实例－显示销售业绩奖金显示产品销售情况更换数据系列图形第8章使用图表显示数据本章小结理解各种图表的形式以及图表中各组成部分的含义

掌握创建图表的基本要点和方法

掌握各种图表的适应范围

Excel数据处理与分析应用教程

第9章管理数据排序筛选 分类汇总应用实例—人事档案管理应用实例—销售管理第9章管理数据主要知识点

简单排序复合排序自定义排序自动筛选高级筛选分类汇总9.1排序简单排序多重排序自定义排序9.1排序简单排序多重排序

自定义排序利用工具按钮排序只能按一个字段排利用排序命令排序可按一个或以上字段排9.1排序简单排序多重排序自定义排序

按笔划排序按自定义序列排序9.2筛选自动筛选高级筛选输入筛选参数执行筛选命令1.第一行为字段名2.“与”条件写在一行上3.“或”条件写在不同行上如果有输出区域，应先在所确定的输出区域的第一行输入需输出的字段名9.2筛选自动筛选高级筛选输入筛选参数执行筛选命令删除重复记录9.3分类汇总

分类汇总是将数据清单中关键字相同的一些记录合并为一组，对于每组记录按列进行象求和等一类的统计运算9.3分类汇总创建分类汇总分级显示数据复制分类汇总清除分类汇总9.4应用实例－人事档案管理人事档案排序按姓氏笔划排序

按部门、性别和婚姻状况排序

按学历排序

9.4应用实例－人事档案管理人事档案查询查询籍贯为四川的业务员

查询高级职称的员工

查询基本工资1500~2000的员工

查询年龄最大的5位职工

9.5应用实例－销售管理按产品汇总按产品品牌分类汇总

按产品规格分类汇总

按业务员汇总

第9章管理数据本章小结全面了解Excel在数据管理方面的强大功能

理解数据清单的概念

熟练掌握有关数据排序、筛选以及分类汇总地操作

Excel数据处理与分析应用教程

第10章透视数据创建数据透视表 应用数据透视表应用数据透视图应用实例—分析销售情况第10章透视数据主要知识点

数据透视表的概念数据透视表的建立数据透视表的编辑数据透视表的应用数据透视图的应用10.1创建数据透视表认识数据透视表“透视”的特性“只读”的特性建立数据透视表10.1创建数据透视表编辑数据透视表添加、删除字段

排序

修饰数据透视表更新数据透视表10.1创建数据透视表编辑数据透视表添加、删除字段

排序

修饰数据透视表更新数据透视表9.1排序简单排序多重排序

自定义排序利用工具按钮排序只能按一个字段排利用排序命令排序可按一个或以上字段排9.1排序简单排序多重排序自定义排序

按笔划排序按自定义序列排序9.2筛选自动筛选高级筛选输入筛选参数执行筛选命令1.第一行为字段名2.“与”条件写在一行上3.“或”条件写在不同行上如果有输出区域，应先在所确定的输出区域的第一行输入需输出的字段名10.2应用数据透视表组合数据项选择计算函数改变显示方式改变显示布局改变数据显示方式隐藏汇总项显示明细数据

10.3应用数据透视图创建数据透视图调整数据透视图10.4应用实例－分析销售情况产品销售情况分析业务员销售业绩分析第10章透视数据本章小结全面了解Excel有关数据透视表的强大功能

理解数据透视表的特性

Excel数据处理与分析应用教程

第11章分析数据模拟运算表

单变量求解方案分析规划求解第11章分析数据主要知识点

单变量模拟运算表双变量模拟运算表单变量求解方案分析规划求解11.1模拟运算表单变量模拟运算表

单变量模拟运算表主要用来分析当其他因素不变时，一个变量的变化对目标值的影响11.1模拟运算表双变量模拟运算表

双变量模拟运算表主要用于分析当其他因素不变时，两个变量的变化对目标值的影响11.2单变量求解

单变量求解是在设置了目标值后，求其决策变量的解，即由公式中的“果”计算“因”11.3方案分析

根据提出的多种可能，确定不同的方案，以帮助分析人员分析、选择11.3方案分析命名单元格创建方案建立方案总结报告11.4规划求解安装规划求解工具建立规划模型输入规划模型求解规划模型分析求解结果第11章分析数据本章小结掌握Excel模拟运算表、单变量求解、方案和规划求解工具的应用方法

能够熟练地运用上述工具在信息管理领域中进行假设分析、目标分析、方案分析和优化分析

Excel数据处理与分析应用教程

第12章设置更好的操作环境创建宏 自定义工具栏自定义菜单在工作表中应用控件应用实例－创建销售管理用户界面第12章设置更好的操作环境主要知识点

录制宏窗体控件自定义菜单自定义工具栏12.1创建宏创建宏编辑宏运行宏12.2自