Microsoft Office Excel
在工作中的高级应用
目录
一. 数据统计分析,丰富强大的功能来帮您 (3)
二. 用规范高效的方式制作表格 (7)
三. 公式和函数:摆脱手工,一切交给电脑 (11)
Excel功能分布图
一.数据统计分析,丰富强大的功能来帮您
1.数据透视图表(PivotTable)
案例:
应用Excel强大的数据透视功能,对历年销售数据进行多维度分析,包括各销售区域业绩分析、按时间段的销售分析、人员销售业绩排名、销售数据的同比与环比、自动生成销售业绩明细清单,等
知识点:
-理解基于数据源的透视分析模式
多维度拖曳与多种统计方式
-编辑和格式化数据透视表
?按区域、时间、级别、人员、科目等多维度数据统计分析(页、列、行、数据)?将数据分类组合:日期、数字范围、指定条件
?对数据进行排序、筛选、自动显示
?对数据进行同比、环比、总数占比等量化分析
?双击自动导出明细数据报表
?手动刷新变化的数据
-数据透视图的展现和应用技术
双轴图
2.实用的数据统计分析功能
知识点:
-条件格式(Conditional Formatting):将达到条件的数据醒目标示出来
-排序和筛选:2007和2010版可按颜色排序和筛选(Sort & Filter)
二.用规范高效的方式制作表格
案例:
通过制作一张人员信息清单(数据列表),并对表格进行常见的应用和操作,以学习和熟练excel最为常用的功能和知识点
知识点:
-严谨的数据表格必须把握的原则
?不能有空的抬头(列标题)或者重复抬头
?不能有合并单元格
?不能有空行和空列
-自定义单元格格式(Cells Format)
?选项设置:数字、对齐、字体、边框、图案、保护
?常见单元格格式:常规、数值、货币、日期、文本、自定义,等
-导入外部数据:分列(Data - Text to Columns)?两种方式:“分隔符号”与“固定宽度”
?用“分列”强制转换单元格成为标准格式文本左对齐,数字和日期右对齐
-数据有效性(Data - Validation)
?有效性的应用范围
限定数字范围、限定文本长度、下拉列表选择,等?定义有效性的三个要素:规则设置、信息提示、出错警告
-保护工作表和工作簿(Protection)
?工作表保护:只能输入需要输入的单元格,其它不能修改
?工作簿保护:限制对工作表(Sheet)的添加、删除、修改、隐藏等操作
-打印的“页面设置”:页面、页边距、页眉/页脚、工作表(Page Setup)?快速调整打印比例、多表头、网格线等的打印设置
三.公式和函数:摆脱手工,一切交给电脑
案例:
应用公式和函数功能制作员工薪资表,包括所得税和各项补贴的自动计算
知识点:
-公式和函数(Function)
?如何理解公式和函数
?公式:表达式、操作数、运算符
?函数:命名、参数、返回值
?编辑公式和使用函数的正确方法:使用插入函数功能
-单元格的引用
?三种类型:相对引用(A1)、绝对引用($A$1)、混合引用($A1、A$1)
-常用函数
?逻辑:if()、and()、or()、iferror()
?统计:average()、rank()、counta()、countif()
?数学:sum()、sumif()、round()、mod()、subtotal()
?查找与引用:vlookup()、index()、match()、offset()
?日期与时间:date()、today()、year()、month()、day()、edate()
?文本:trim()、left()、right()、mid()、len()、find() -VLOOKUP()函数的两种应用模式
?精确匹配:最后参数为FALSE,适合唯一关键词查找
?模糊匹配:最后参数为TRUE,适合数字分段(如查找税率)
-学会使用函数的帮助:看帮助中的例子
-显示公式单元格:ctrl+~
?神奇的演示小工具ZoomIt:放大、标注、倒计时