Excel高效数据分析

Excel高效数据分析关于课程•课程目标：帮您树立一种正确高效使用Excel的基本理念和思路为您提供一套高效实用的Excel技能和方法课程目录•一、使用Excel的正确理念与注意事项•二、设计标准规范的Excel表格•三、快速掌握实用的Excel函数•四、快速掌握Excel数据透视表•五、从原始数据到分析图表一、使用Excel的正确理念与注意事项•1、Excel是什么•2、如何使用Excel做财务管理•3、基本原则•4、设计模版的基本步骤•5、财务分析模版设计应考虑的几个问题•6、正确处理基础表格与报告表格•7、如何学习Excel精细化管理日常控制科学决策企业经营报表•沟通•控制•决策•业绩评价•等等Excel是什么6工作设计Step2标准化技能技能Stpe3成果模版Step4理念Step1思想基本原则•从原始的表单→最终的报告–尽量使用原始数据–尽量减少中间环节–简洁的报告–高效的公式设计模版的基本步骤•Step1：获取数据–分析数据的来源（手工表格；导入表格）–源数据的结构（单表数据，多表数据）•Step2：表格结构设计–基础表格–报告表格•Step3：呈现方式–Excel表格形式–Word书面形式–PPT演示形式财务分析模版设计应考虑的几个问题•设计财务分析模版应考虑的几个问题–层次结构：单层结构还是多层结构？–布局方向：横向布局还是纵向布局？–动与静：静态分析还是滚动分析？–灵活性：分析项目是一种还是多种？–直观性：图形展示分析结果–整合性：要不要与财务软件整合起来？–报告性：是否做成一个最终的汇报性的报告？•案例解析–案例01单表分析模版演示案例–案例02多表分析模版演示案例正确处理基础表格与报告表格•Excel的两类表格–基础表格•按照数据的功能来分别保存在不同的工作表•结构越简单越好–报告表格•要考虑汇总过程的效率性和方便性，分析结果的呈现性•结构越清楚越好•设计表格应遵循的总体原则：•结构的科学性•数据的易读性•汇总的便宜性•分析的灵活性•外观的美观性如何学习Excel•Excel一定要结合企业管理来学习•从最初的表单设计，一直到最终报表，都需要按照企业精细化管理的要求来进行•必须掌握Excel的实用技能–高效办公技能–数据跟踪技能–分类汇总技能–链接查询技能–合并工作表技能–数据挖掘技能–图形展示技能–自动化技能二、设计标准规范的Excel表格•1、标准化表格解析•2、表格数据的整理与规范•3、使用数据有效性控制规范数据输入•4、使用基本的函数获取基本数据•5、使用条件格式自动美化表格•6、创建列表自动复制公式•7、设计表格综合练习标准化表格解析•标准化表格的基本要求–数据库结构–结构最简单–填写数据方便–没有合并单元格–没有空行和空列–没有不必要的小计和总计–没有不必要的公式–数据规范表格数据的整理与规范•很多表格中的数据是非法的、错误的、不规范的，需要进行整理规范和标准化。•正确处理日期数据•正确处理文本和数字正确处理日期数据•Excel是如何处理日期的？–日期是特殊的数字。日期永远是大于零的正整数。•正确输入日期–正确输入日期：2010-10-8，2010/10/8，8/oct/2010，8-oct-2010–错误的输入日期方式：2010.10.08，或者20101008•错误日期的类型–输入错误–从系统导入的日期是文本格式•如何把日期显示为需要的格式？——使用“分列”功能•修改非法日期正确处理文本和数字•如何将文本型数字转换为纯数字有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，需要转换为纯数字。–方法1：利用智能标记–方法2：利用VALUE函数–方法3：利用公式（两个负号或者乘以1或除以1）–方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）–方法5：利用分列工具(只能一列一列改）•如何把数字转换为文本型数字?–使用分列工具–使用TEXT函数使用数据有效性控制规范数据输入•利用有效性，可以控制规范数据输入，快速准确输入数据，让别人按照您的要求填列数据。•比如：–只能输入规定格式的数据–只能输入规定的序列数据–只有在上一行所有单元格都输入数据后，才能在下一行输入新的数据–多重限制输入数据–不允许输入重复数据–……•案例03有效性典型应用其他数据整理技巧•批量修改数据•转置数据位置•复制可见单元格数据•批量插入空行和空列•批量填充单元格•……使用基本的函数获取基本数据•使用有关的查找函数，可以快速准确输入基本资料，避免复制粘贴等手工操作。•常用的函数有–查找函数VLOOKUP–分类汇总函数SUMIF、SUMPRODUCT–文本函数LEFT、RIGHT、MID、TEXT–日期函数TODAY、EDATE、DATEDIF•案例练习–案例04使用函数输入数据使用条件格式自动美化表格•使用条件格式，可以自动美化表格，既便于快速输入数据，有使表格干净美观。•案例练习–案例05条件格式美化表格创建列表自动复制公式•使用列表工具，不仅可以自动建立筛选，还可以自动复制公式、格式、有效性等。•案例练习–案例06使用列表自动复制公式三、快速掌握实用的Excel函数•1、函数基础•2、处理日期常用的3个函数•3、处理文本常用的4个函数•4、逻辑判断常用的4个函数•5、分类汇总常用的3个函数•6、数据查找常用的4个函数•7、函数综合练习函数基础•创建公式（以“＝”开始，可以包括：运算符、单元格引用、值或常量以及工作表函数。）•公式中的运算符–例如:^（求幂）、&（文本连接运算符）、（不等于）等•公式实例：=150*.01=A1+A2=SUM(A1:A2)=A1=C12=(B2-B3)*B4一个公式最多可以容纳1024个字符函数基础•绝对引用/相对引用（演示）引用（说明）更改为$A$1（绝对列和绝对行）$A$1A$1（相对列和绝对行）C$1$A1（绝对列和相对行）$A3A1（相对列和相对行）C3函数基础错误值原因说明####该列宽度不够，或者包含一个无效时间或日期#DIV/0!该公式使用了0作为除数，或者公式中使用了一个空单元格#N/A公式中引用的数据对函数或公式不可用#NAME?公式中使用了Excel不能辨认的文本或名称#NULL!公式中使用了一种不允许出现相交却交叉了的两个区域#NUM!使用了无效的数字值#REF!公式引用了一个无效的单元格#VALUE!函数中使用的变量或参数类型错误•函数使用常见的错误处理日期常用的3个函数•3个重要的日期函数–TODAY函数：获取当天日期–EDATE函数：计算到期日–DATEDIF函数：计算期限•案例练习–案例07计算新车库存成本–案例08计算合同到期日处理文本常用的4个函数•4个重要的文本函数–LEFT–RIGHT–MID–TEXT•案例练习–案例04使用函数输入数据逻辑判断常用的4个函数•4个重要的逻辑判断函数–IF函数（嵌套IF）–AND函数–OR函数–ISERROR函数（IFERROR函数）•案例练习–案例09计算工龄工资分类汇总常用的3个函数•3个重要的分类汇总函数–SUMIF–SUMIFS–SUMPRODUCT•案例练习–案例10分类汇总函数基本练习数据查找常用的4个函数•4个重要的查找引用函数–VLOOKUP函数–MATCH函数–INDEX函数–INDIRECT函数•案例练习–案例11VLOOKUP函数应用–案例12查找函数高级应用函数综合练习•针对从系统导出的维修记录表，设计一个动态的查询系统，可以：–任选年份–任选月份–任选日期•使用技能和函数–滚动查找技术–MATCH函数–INDEX函数–INDIRECT函数–IF函数–ISERROR函数四、快速掌握Excel数据透视表•1、什么是数据透视表•2、制作数据透视表的基本方法•3、了解数据透视表的结构•4、数据透视表的美化•5、利用数据透视表分析数据•6、数据透视表的特殊应用什么是数据透视表•什么是数据透视表？–一种动态的汇总分析报表•数据透视表能做什么？–分类汇总–多维度统计分析–汇总大量工作表–制作明细表–高效数据查询–对比分析•怎样灵活使用数据透视表？–制作正确的数据透视表–掌握数据透视表的各种功能制作数据透视表的基本方法•基本步骤–Step1：标准化规范化基础表格–Step2：采用合适的方法制作数据透视表–Step3：布局透视表，完成报告了解数据透视表的结构•行字段：是分类字段，是指在数据透视表中被指定为行方向的字段。•列字段：是分类字段，是指在数据透视表中被指定为列方向的字段。•页字段：是筛选字段，允许筛选整个数据透视表。•值字段：是汇总计算字段。数据透视表的美化•在数据透视表上对数据透视表进行重新布局•修改字段名称•设置字段的数字格式•取消数据透视表的行或列汇总•取消字段的分类汇总•让数据透视表数据按照某一字段进行排序•合并数据标志•设置错误值的显示方式•更新数据透视表的数据利用数据透视表分析数据•基本方法：–布局透视表–设置字段的分类汇总方式–设置字段的显示方式–自定义数据透视表–组合字段–制作明细表•案例练习–案例13数据透视表综合分析数据透视表的特殊应用•快速合并大量的工作表多个二维表格合并计算•案例14全年管理费用汇总分析•快速对比两个表格–案例15快速核对社保五、从原始数据到分析图表•制作图表的基本方法•使用组合图表分析深层次信息•使用动态图表灵活分析数据制作图表的基本方法•两种基本的绘图方法–直接命令法–手工添加法•案例练习–案例16绘制图表基本练习使用组合图表分析深层次信息•组合图表：通过数据的重新调整，或者通过图表的格式设置，所制作的特殊分析图表。•常用方法–辅助绘图区域法–多图表组合法–设置涨跌柱线、高低点连线、趋势线•案例练习–案例17同比分析图–案例18价格变化趋势图–案例19差异分析图–案例20资金流动图使用动态图表灵活分析数据•动态图表–可以按照图表使用者任意显示的图表•动态图表的基本原理–使用控件控制单元格数据，进而控制图表显示•需要掌握的基本技能–窗体控件（表单控件）的使用方法–查找函数（尤其是INDEX）的熟练使用–数据逻辑的正确判断和处理•案例练习–案例21动态图表基本练习课程结束祝您工作生活愉快！1YEAR2YEAR