Excel数据分析与图表可视化

《Excel数据分析与图表可视化》由会员分享，可在线阅读，更多相关《Excel数据分析与图表可视化（36页珍藏版）》请在金锄头文库上搜索。

1、Excel数据分析与图表可视化数据分析中心Excel数据处理分析数据预处理 公式与函数 透视表与分析功能Excel图表可视化展现图表类型选型原则 图表可视化介绍 自定义图表定制主题需要分析什么问题 希望得到什么结果 结果用在什么地方学会排列组合。这个排列组合不是真的排列组合，日常我们在进 行数据处理时，基本上80%以上的工作只需要20%左右的函数和 方法就可以解决，比如vlookup，sumif，countif，if，条件筛 选，排序等等，这也是符合幂律分布的。尽管面临不同的问题和 需求，但是通过公式的嵌套，组合，最后基本上都能解决数据处 理的分析需求，进一步就是如何优化，提高分析的效率Exc

2、el数据分析思路解决问题获取答 案提出问 题Excel数据预处理主题数据预处理-基本操作单元格 选择通过名称框地址或名称选定利用名称框下拉列表利用定位功能按条件选定开始查找与选择转到(定位条件)单元格 输入数据 手动输入数据 选中键入 单元格 方向键() Enter()/Tab() Shift + Enter/Tab反向 快速移动光标选中单元格 连续单元格区域 Enter/Tab/Shift+Enter/Shift+Tab 在区域内快速移动选中 鼠标/方向键 取消对区域的选择 多个单元格/区域同时输入相同数据 选中目标单元格/区域在活动单元格中键入数据Ctrl+Enter数据预处理-基本操作E

3、xcel图表可视化展现图表类型选型原则 图表可视化介绍 自定义图表定制单元格 输入数据 自动输入数据序列序列自定义序列自定义序列数据预处理-基本操作单元格 设置格式 条件格式条件格式(基于条件更改单元格区域的外观)数据预处理-条件格式选择性粘贴数据合并转换 数据行列转置 表格同步更新 区域格式复制数据预处理-选择性粘贴Excel数据分析 排序与筛选数据排序与筛选数据排序与筛选自定义排序排序条件高级筛选通配符的使用请使用请使用 若要查找若要查找?（问号） 任何单个字符 *（星号） 任何字符数 （波形符）后跟 ?、* 或问号、星号或波形符 数据处理 分类汇总 对数据按类别进行汇总统计分析 分类汇总

4、之前一定要先进行排序 各列须有列标题（字段名） 分类汇总类型 单字段 多字段 嵌套汇总 数据预处理-分类汇总Excel公式与函数主题 公式 组成 以等号（=）开始 引用（单元格引用/区域引用/名称引用） 运算符 函数 常量(数字或文本)公式：= 表达式=(A3-average)/SUM(A1:A20)+12 数据预处理-公式与函数 公式 使用引用 引用引用：通过指定单元格或区域的地址/名称来使用其中的数据 使用数据的地址使用数据的地址/ /名称并非数据本身名称并非数据本身 建立数据之间的联系 赋予工作表动态特性(随被引单元格的值变化) 引用引用类型类型： 单元格引用 单元格区域引用 名称引用数

5、据预处理-公式与函数 F4F4键键 切换引用方式切换引用方式 输入公式时，用方向键选取引用单元格输入公式时，用方向键选取引用单元格 编辑公式时，用方向键在公式中移动光标编辑公式时，用方向键在公式中移动光标 移动公式时，引用均不变化，就是原有单元格中的引用地址移动公式时，引用均不变化，就是原有单元格中的引用地址公式 单元格引用 相对引用：当公式复复制制到其他单元格时，行和列引用相应改变 （引、被引单元格相对位置不变） A1 绝对引用：当公式复制复制到其他单元格时，行和列引用不会改变 （行标和列标之前加上$） $A$1 混合引用：行、列引用中一个是相对引用，另一个是绝对引用 &A1、A&1数据预处

6、理-公式与函数 3.2 公式 计算运算符类别运算符含义示例优先级引用运算符引用运算符 :区域运算，生成对两个引用之间所有单元格的 引用C1:E61空格交叉运算，生成对两个引用中共有单元格的引 用A1:C5 B2:E92,联合运算，合并多个引用为一个引用 A1:C5,B2:E93算术运算术运算符算符负号-104%百分比60%5乘方536* /乘、除5*9/37+ 加、减7+238文本运算符文本运算符&连接两/多个文本值合并为一个文本值“3”&“m” “3m”9比较运算符比较运算符= = 比较两个值的大小，结果为逻辑值106FALSE10数据预处理-公式与函数 IF函数 IF函数也称条件函数，它根

7、据参数条件的真假，返回不同 的结果。在实践中，经常使用函数IF对数值和公式进行条 件检测。公式为 = IF( logical_test, value_if_true, value_if_false)数据预处理-公式与函数COUNT函数和COUNTIF函数 COUNT函数的功能是计算给定区域内数值型参数的数目。公式为= COUNT（参数1，参数2，参数N）COUNTIF函数的功能是计算给定区域内满足条件的单元格的数目。公式为= COUNTIF（range，criteria）range需要计算其中满足条件的单元格数目的单元格区域；criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、

8、表达式或文本。COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。数据预处理-公式与函数Lookup相关函数功能 在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。语法 标准格式： VLOOKUP(lookup_value, table_array, col_index_num , range_lookup)解释： VLOOKUP(你要检索的内容或指定单元格,你要检索的范围,检索到内容时返回你 检索表的第几列中的内容, 真或假参数真代表查询的表已经排序，假代表没有排序)例：VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE) 说 明：在

9、表SHEET2中检索当前表中A2中的内容，如果检索到，就返回表SHEET2中B2中的内容，因为B2是 表SHEET2中的第二列，所以 VLOOKUP的第三个参数，使用2，表示如果满足条件，就返回查询表的第二列 ，最后的参数FALSE表示假，意思是被查询的表，没有排序，这种情况 下，会从被查询的表中第一行开 始，一直查询到结束。 3.5 公式错误值类型错误值类型说明#单元格列宽不足以显示内容，或日期或时间为负值#DIV/0! 0作为除数，或引用空白单元格作为除数#N/A无信息/数值可引用#NAME?引用无法识别的文本/不存在的名称#NULL! 指定两个并不相交区域的交点#NUM! 引用无效的数值

10、（超限，应介于 -1*103071*10307之间）#REF!引用无效的单元格（被引单元格被删除或覆盖）#VALUE! 引用不正确的数据类型数据处理-透视表数据透视表是交互式报表，可快速合并和比较大量数据。旋转其行和列以看 到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 分析相关的汇总 值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数 据透视表切片与筛选值汇总方式日期，数值分组Excel图表定制主题图表可视化-类型选择图表尽量简洁明了，突出主题。充分考虑读者的习惯，选则更容易被接受和理解的图表类型，避免过多的解释。常用图表的一般用途： 柱形图、条形图：数量对比； 折线图

11、、面积图、柱形图：反映趋势； 饼图、堆积百分比柱形/条形图：反映结构； 散点图、气泡图：反映数据间的联系。不恰当的图表类型可能会传达错误信息。 案例：用折线图描绘一组没有时序关系的数据尽量不要用三维图形 貌似美观，但没有传递更多的信息，反倒分散了关注点。 维图无法做组合图。组合图可以在一张图中有效反映关联数据，但不可滥用。 一张图中不宜反映太多信息，否则无法聚焦，容易使人迷惑。 不同系列之间的关联关系如果不可靠，图形反映的将是错误信息。图表可视化-类型选择图表可视化-配色原则Excel图表的默认配色采用的主题颜色，可以选择 其他主题或自定义主题。无论采用什么配色方案，都应保持一致性的延续 性风

12、格化。颜色也可以传递信息。要注意配色惯例，如红、 橙、黄通常具有警告含义，可用于异常数据的突 出显示，蓝色通常代表进展，绿色通常代表成 长。避免不协调的色彩搭配： 红绿搭配通常会引起不舒服的感觉； 低反差的配色不易分辨； 各系列均采用浓重的颜色不但无法突出重点， 而且容易产生头重脚轻的感觉。稳妥的配色方案： 没有时序关系的系列采用不同色系搭配； 具有时序关系的系列采用同色系不同明度的方 式搭配； 尽量不使用太浅的颜色。图表可视化-配色原则1.遵循你公司的Logo的颜色 - 为品牌做可视化图象。 2.让你的内容来决定所用的色彩 - 为市场内容的可视化图象。 3.使用季节或者节日属性的色彩 - 为

13、季度性市场可视化图象。 图表 组成 图表区 绘图区 数据系列（某一行/列） 横坐标轴（分类轴） 纵坐标轴（数值轴） 网格线 图例 标题（图表标题、坐标轴标题 ） 数据标签 图表可视化图表可视化-组合图表在二维图表中，每一个系列可单独更改为不同的图表类型，由此成为组合图 表。图表中包含多个系列，其中一个系列与其它系列的计量单位不同时，应将其放 在次坐标轴上，并更改为不同的图表类型，形成组合图表。 技巧：当要修改的系列与其它系列的值差别特别大，使得图形紧贴坐标轴时（如百分 比），该如何选中这个系列？图表可视化-折线图分离单位利用坐标轴的最大值和最小值，将折线图推到上面，避免覆盖，使整个图形更 加清

14、晰。图表可视化-突出重点数据在基础数据基础上增加业务标识列 在图表中添加数据系列 设置系列的图表类型为柱状图 调整图表的具体展现格式(间距，填充颜色等)图表可视化-对称条形图成对条形图（或旋风图）常用于对比两类事物在不同特征项目的数据情况，例如美国总统 选举中不同地区投支持票和反对票的比例情况在图表中添加数据系列图表的特点是两组条形图的数据条沿中间的纵轴分别朝左右两个方向伸展，通过把两个数 据系列分别置于主坐标轴和次坐标轴，然后对其中一个坐标轴逆序显示并且对称地设置主 次坐标轴的最大值和最小值即可实现图表效果。图表可视化-复合饼图与双层饼图复合饼图和双层饼图可以提高小百分比的可读性或者强调一组

15、数值，对多组数据或多个系 列的数据能更好的显示对比的效果。双层饼图经常用于表示两个数据系列的比例结构，由大小不同的两个饼图叠加在一起组成 ，一个普通的饼和一个分离的饼，分别位于主坐标轴和次坐标轴，叠加后再调整各自的大 小，以区分显示的一种饼图。这是与复合饼图的最大区别。图表可视化-象限图象限图是利用两个关键性能指标分别在横坐标和纵坐标交叉划分出四个区域，用于分析和 发现两个指标的匹配优劣程度。魔力象限（Gartner Magic Quadrant）是设计来监控、评估公司在专业、技术市场的发 展与位置的研究方法。不管是对寻找适合自身需求公司的投资者还是对那些对比市场竞争 者并可能占上风的企业来说，魔力象限创建的报告可能是高度实用的工具。套餐活跃跃用 户户注册用户户 资费资费套餐23214套餐35132套餐416410套餐5953套餐61167套餐11211117套餐1212125图表可视化-象限图魔力象限（Gartner Magic Quadrant） 使用What-If假设分析 模拟变量表 方案管理器 使用反转的假设分析 单变量求