Excel数据分析与图表可视化培训课件

EXCEL培训课程Excel数据分析与图表可视化培训：

Excel数据处理分析数据预处理公式与函数透视表与分析功能

Excel图表可视化展现图表类型选型原则图表可视化介绍自定义图表定制主题Excel数据分析思路需要分析什么问题希望得到什么结果结果用在什么地方学会排列组合。这个排列组合不是真的排列组合，日常我们在进行数据处理时，基本上80%以上的工作只需要20%左右的函数和方法就可以解决，比如vlookup，sumif，countif，if，条件筛选，排序等等，这也是符合幂律分布的。尽管面临不同的问题和需求，但是通过公式的嵌套，组合，最后基本上都能解决数据处理的分析需求，进一步就是如何优化，提高分析的效率Excel数据分析思路解决问题获取答案提出问题数据预处理-基本操作单元格—选择通过名称框→地址或名称选定利用名称框下拉列表利用定位功能按条件选定开始→查找与选择→转到(定位条件)单元格—输入数据

①手动输入数据

选中→键入单元格

方向键(↓↑→←)

Enter(↓)/Tab(→)[Shift+Enter/Tab反向]

→快速移动光标选中单元格连续单元格区域

Enter/Tab/Shift+Enter/Shift+Tab→在区域内快速移动选中

×鼠标/方向键→取消对区域的选择多个单元格/区域同时输入相同数据

选中目标单元格/区域→在活动单元格中键入数据→Ctrl+Enter数据预处理-基本操作Excel数据预处理

Excel图表可视化展现图表类型选型原则图表可视化介绍自定义图表定制单元格—输入数据

自动输入数据

序列自定义序列数据预处理-基本操作单元格—设置格式条件格式(基于条件更改单元格区域的外观)数据预处理-条件格式

选择性粘贴数据合并转换数据行列转置表格同步更新区域格式复制数据预处理-选择性粘贴

Excel数据分析–排序与筛选数据排序与筛选自定义排序排序条件高级筛选通配符的使用请使用

若要查找

?（问号）

任何单个字符

*（星号）

任何字符数

~（波形符）后跟

?、*

或

~

问号、星号或波形符

数据处理—分类汇总对数据按类别进行汇总→统计分析分类汇总之前一定要先进行排序各列须有列标题（字段名）分类汇总类型单字段多字段嵌套汇总数据预处理-分类汇总公式—组成以等号（=）开始引用（单元格引用①/区域引用②/名称引用③）运算符④函数⑤常量(数字或文本)⑥公式：=表达式=(A3-average)/SUM(A1:A20)+12①③⑤②④⑥数据预处理-公式与函数公式—使用引用引用：通过指定单元格或区域的地址/名称来使用其中的数据

→使用数据的地址/名称并非数据本身建立数据之间的联系赋予工作表动态特性(随被引单元格的值变化)引用类型：单元格引用单元格区域引用名称引用数据预处理-公式与函数F4键→切换引用方式输入公式时，用方向键选取引用单元格编辑公式时，用方向键在公式中移动光标移动公式时，引用均不变化，就是原有单元格中的引用地址公式—单元格引用

相对引用：当公式复制到其他单元格时，行和列引用相应改变

（引、被引单元格相对位置不变）—A1绝对引用：当公式复制到其他单元格时，行和列引用不会改变

（行标和列标之前加上$）—$A$1混合引用：行、列引用中一个是相对引用，另一个是绝对引用

—&A1、A&1数据预处理-公式与函数3.2公式—计算运算符类别运算符含义示例优先级引用运算符:区域运算，生成对两个引用之间所有单元格的引用C1:E61[空格]交叉运算，生成对两个引用中共有单元格的引用A1:C5B2:E92,联合运算，合并多个引用为一个引用A1:C5,B2:E93算术运算符–负号-104%百分比60%5^乘方5^36*/乘、除5*9/37+–加、减7+2–38文本运算符&连接两/多个文本值合并为一个文本值“3”&“m”→“3m”9比较运算符=><>=<=<>比较两个值的大小，结果为逻辑值10<6→FALSE10数据预处理-公式与函数IF函数IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数IF对数值和公式进行条件检测。公式为=IF(logical_test,value_if_true,value_if_false)数据预处理-公式与函数COUNT函数和COUNTIF函数COUNT函数的功能是计算给定区域内数值型参数的数目。公式为=COUNT（参数1，参数2，…，参数N）COUNTIF函数的功能是计算给定区域内满足条件的单元格的数目。公式为=COUNTIF（range，criteria）range需要计算其中满足条件的单元格数目的单元格区域；criteria—确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。数据预处理-公式与函数Lookup相关函数功能

在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。语法

标准格式：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)解释：VLOOKUP(‘你要检索的内容或指定单元格’,‘你要检索的范围,检索到内容时返回你检索表的第几列中的内容’,‘真或假参数真代表查询的表已经排序，假代表没有排序)例：VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE)说明：在表SHEET2中检索当前表中A2中的内容，如果检索到，就返回表SHEET2中B2中的内容，因为B2是表SHEET2中的第二列，所以VLOOKUP的第三个参数，使用2，表示如果满足条件，就返回查询表的第二列，最后的参数FALSE表示‘假’，意思是被查询的表，没有排序，这种情况下，会从被查询的表中第一行开始，一直查询到结束。3.5公式错误值类型错误值类型说明######单元格列宽不足以显示内容，或日期或时间为负值#DIV/0!0作为除数，或引用空白单元格作为除数#N/A无信息/数值可引用#NAME?引用无法识别的文本/不存在的名称#NULL!指定两个并不相交区域的交点#NUM!引用无效的数值（超限，应介于-1*10307~1*10307之间）#REF!引用无效的单元格（被引单元格被删除或覆盖）#VALUE!引用不正确的数据类型数据处理-透视表数据透视表是交互式报表，可快速合并和比较大量数据。旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表切片与筛选值汇总方式日期，数值分组Excel图表可视化展现图表可视化-类型选择图表尽量简洁明了，突出主题。充分考虑读者的习惯，选则更容易被接受和理解的图表类型，避免过多的解释。常用图表的一般用途：柱形图、条形图：数量对比；折线图、面积图、柱形图：反映趋势；饼图、堆积百分比柱形/条形图：反映结构；散点图、气泡图：反映数据间的联系。不恰当的图表类型可能会传达错误信息。案例：用折线图描绘一组没有时序关系的数据尽量不要用三维图形貌似美观，但没有传递更多的信息，反倒分散了关注点。维图无法做组合图。组合图可以在一张图中有效反映关联数据，但不可滥用。一张图中不宜反映太多信息，否则无法聚焦，容易使人迷惑。不同系列之间的关联关系如果不可靠，图形反映的将是错误信息。

图表可视化-配色原则Excel图表的默认配色采用的主题颜色，可以选择其他主题或自定义主题。无论采用什么配色方案，都应保持一致性的延续性——风格化。颜色也可以传递信息。要注意配色惯例，如红、橙、黄通常具有警告含义，可用于异常数据的突出显示，蓝色通常代表进展，绿色通常代表成长。避免不协调的色彩搭配：红绿搭配通常会引起不舒服的感觉；低反差的配色不易分辨；各系列均采用浓重的颜色不但无法突出重点，而且容易产生头重脚轻的感觉。稳妥的配色方案：没有时序关系的系列采用不同色系搭配；具有时序关系的系列采用同色系不同明度的方式搭配；尽量不使用太浅的颜色。图表可视化-配色原则1.遵循你公司的Logo的颜色-为品牌做可视化图象。2.让你的内容来决定所用的色彩-为市场内容的可视化图象。3.使用季节或者节日属性的色彩-为季度性市场可视化图象。图表—组成图表区绘图区数据系列（某一行/列）横坐标轴（分类轴）纵坐标轴（数值轴）网格线图例标题（图表标题、坐标轴标题）数据标签①②③④⑤⑥⑦⑧⑧⑧⑨图表可视化图表可视化-组合图表在二维图表中，每一个系列可单独更改为不同的图表类型，由此成为组合图表。图表中包含多个系列，其中一个系列与其它系列的计量单位不同时，应将其放在次坐标轴上，并更改为不同的图表类型，形成组合图表。技巧：当要修改的系列与其它系列的值差别特别大，使得图形紧贴坐标轴时（如百分比），该如何选中这个系列？图表可视化-折线图分离单位利用坐标轴的最大值和最小值，将折线图推到上面，避免覆盖，使整个图形更加清晰。图表可视化-突出重点数据在基础数据基础上增加业务标识列在图表中添加数据系列设置系列的图表类型为柱状图调整图表的具体展现格式(间距，填充颜色等)图表可视化-对称条形图成对条形图（或旋风图）常用于对比两类事物在不同特征项目的数据情况，例如美国总统选举中不同地区投支持票和反对票的比例情况在图表中添加数据系列图表的特点是两组条形图的数据条沿中间的纵轴分别朝左右两个方向伸展，通过把两个数据系列分别置于主坐标轴和次坐标轴，然后对其中一个坐标轴逆序显示并且对称地设置主次坐标轴的最大值和最小值即可实现图表效果。图表可视化-复合饼图与双层饼图复合饼图和双层饼图可以提高小百分比的可读性或者强调一组数值，对多组数据或多个系列的数据能更好的显示对比的效果。双层饼图经常用于表示两个数据系列的比例结构，由大小不同的两个饼图叠加在一起组成，一个普通的饼和一个分离的饼，分别位于主坐标轴和次坐标轴，叠加后再调整各自的大小，以区分显示的一种饼图。这是与复合饼图的最大区别。图表可视化-象限图象限图是利用两个关键性能指标分别在横坐标和纵坐标交叉划分出四个区域，用于分析和发现两个指标的匹配优劣程度。魔力象限（GartnerMagicQuadrant）是设计来监控、评估公司在专业、技术市场的发展与位置的研究方法。不管是对寻找适合自身需求公司的投资者还是对那些对比市场竞争者并可能占上风的企业来说，魔力象限创建的报告可能是高度实用的工具。套餐活跃用户注册用户资费套餐23214套餐35132套餐416410套餐5953套餐61167套餐11211117套餐1212125

图表可视化-象限图魔力象限（GartnerMagicQuadrant）使用What-If假设分析模拟变量表方案管理器使用反转的假设分析单变量求解规划求解使用分析工具库方差分析/相关系数/协方差/描述统计/指数平滑F-检验/傅里叶分析/直方图/移动平均/随机数发生器排位与百分比排位/回归分析/抽样分析/t-检验/z-检验Excel高级数据分析Excel数据分析与图表可视化EXCEL操作培训Excel技巧与操作培训演讲人：ONTENTSC目录Excel操作与技巧2314基础常识报表制作图表制作常用技巧基础常识1Excel基础常识（1）工作簿（2）工作表（3）单元格工作簿菜单栏单元格，通过位置（列标行标）进行标识。工作表状态栏1张工作表（sheet）由65536行*256列，即65536*256=1677万个单元格组成；1个工作簿（book）最多可含有255张工作表。注：EXCEL2007以下版本。工具栏Excel基础常识（1）公式（2）函数公式与函数公式：在工作表中对数据进行计算的式子，它可以对工作表数值进行加、减、乘、除等运算。函数：EXCEL预定义的内置程序，可以使用在公式中。公式与函数数据类型（1）数值型：一般的数字格式。（2）货币型：货币格式，对一列数值进行小数点对齐。（3）日期时间型：可以对两个时间段的间隔时长进行计算。（4）文本型：在文本单元格中，数字、公式作为文本处理。公式与函数运算符（1）算术运算：+（加）、-（减）、*（乘）、/（除）、%（百分比）、^（乘方)（2）比较运算：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）（3）字符运算：&（连接）（4）引用运算：（：冒号）、（，逗号）、（空格）要创建一个公式，首先需要选定一个单元格，输入一个等于号“=”，然后在其后输入公式的内容，按回车键就可以按公式计算得出结果公式与函数单元格引用引用运算符：（：冒号）、（，逗号）、（空格）（1）：（冒号）——区域运算符，如B2:F6表示B2单元格到F6单元格矩形区域内的所有单元格。（2），（逗号）——联合运算符，将多个引用合并为一个引用，如SUM(B5:B26,D4:D22)，表示B5至B26以及D4至D22所有单元格求和（SUM是求和函数）（3）（空格）——交叉运算符，如SUM(B5:B26A4:D22)表示求B5至B26与A4至D22两区域交叉单元格之和，即B5至B22之和。公式与函数绝对地址与相对地址相对地址：=B3+B4+B5+B6绝对地址：=$B$3+$B$4+$B$5+$B$6混合运用，F4切换。两者的差异是：相对地址：如果在A7单元格内输入公式=B3+B4+B5+B6，那么将A7中的公式复制至A8单元格时，A8的结果变成=B4+B5+B6+B7，再将A8中的公式复制至B8时，B8的结果变成=C4+C5+C6+C7，也就是相对地址在公式复制时，会随单元格的不同而发生变化。绝对地址：在公式复制时，不会随单元格的不同而发生变化。不管复制到哪个单元格，结果都是同一个公式，即=$B$3+$B$4+$B$5+$B$6。输入公式公式的输入类似于数据的输入，在单元格中输入公式时以等号（=）作为开始，然后输入公式表达式。（1）启动Excel2010，在工作簿中创建工作表。在工作表中选择需要创建公式的单元格，在单元格中输入公式，如表1所示。（2）完成公式输入后，按Enter键或单击其他的单元格，输入公式的单元格中将显示计算结果。输入公式在工作表中可以将公式复制到其他的单元格中。（1）将鼠标指针放置到需要复制的单元格右下角的控制柄上，鼠标指针变为十字形，按住鼠标左键拖动鼠标至最后一个单元格释放鼠标，公式将被复制到鼠标拖动过的单元格中并显示计算结果。（2）在工作表中选择源单元格，打开“开始”选项卡，在“剪贴板”组中单击“复制”按钮。在工作表中选择目标单元格，单击“粘贴”按钮上的下三角按钮，在下拉列表中“公式”选项，此时选择的单元格中将能预览公式粘贴后的计算结果。使用函数向导插入函数使用函数栏输入使用公式记忆输入功能在单元格中输入函数的第一个字母时，Excel2010会给出一个以该字母开头的函数列表，显示一个列表，按“↓”键在列表中移动光村选择需要的函数，按Tab键将选择的函数输入到单元格中。函数功能数值统计（SUM、SUMIF、COUNT、COUNTIF等）逻辑判断（AND、IF、ISERROR、NOT、TRUE等）文本处理（MID、MIDB、RIGHT、FIND等）日期时间（NOW、TODAY、TIME、TIMEVALUE等）查找引用（VLOOKUP、ROWS、HYPERLINK等）财务计算工程分析公式与函数SUM、SUMPRODUCT、SUMIF、SUMIFS函数公式与函数COUNT、COUNTA、COUNTIF、COUNTIFS函数公式与函数AND、OR、NOT函数公式与函数IF、ISERROR、IFERROR函数公式与函数MID、LEFT、RIGHT函数FIND函数NOW、TODAY函数TIME&TIMEVALUE函数VLOOKUP函数HYPERLINK函数注意事项(一)（1）括号（）要对应显示，不可出现单独的括号，或少括号（2）输入法的半角全角、大小写需注意，如：“”不可输入成“”*不可输入成×（3）如条件是寻找某个文字：南京，在函数中应输入为:“南京”如条件是寻找某个单元格或数字：A5(A列第5行)或5，在函数中应输入为：A5或5，无需加""符号（4）函数中设置的计算范围需一致（5）需在函数公式打开状态下,同时按下Ctrl+Shift+Enter,当公式头尾出现花括号｛｝时才算计算完成注意事项(二)（1）源数据较大时，应将“菜单栏/工具”的重新计算调整为“手动计算”（2）格式：不得出现空格，如：“黑电”和“黑电”（3）某一字段暂不需/无法统计，如是需量化的单元格，则需以“0”填充，不可写“无”（4）不可将明细全部删除后，在原有模板的基础上重写贴入明细再次使用，将会改变计算范围，应重新使用新模板（5）注意统计周期的输入（6）注意EXCEL函数设置中某条件的计算列是否是需要的计算列（7）如需从一个EXCEL表格的数据复制到另一表格中，应点击“菜单栏/编辑”的链接中“断开链接”报表制作2数据排序一次排序最多可以选择三个关键字：主关键字、次关键字、第三关键字。或者利用工具栏上的两个排序按钮。数据筛选数据透视数据透视表实际上是一种交互式表格，能够方便地对大量数据进行快速汇总，并建立交叉表，还能根据用户的需要显示区域中的细节数据。报表排版报表应当含有：1、报表名称（在表格抬头，通常字号略大些）2、表头（在表格主体的顶端与侧端，通常可用颜色着重标注）3、各字段数据值（如有文字内容应相对简略，避免视觉疲劳）报表需要：1、合适的列宽和行高（鼠标放置需求列或行线的最末端，双击即可）2、当表格过长或过宽时，应使用“冻结窗口”功能。图表制作3图表制作在处理电子表格时，需要对大量繁琐的数据进行分析和研究，我们需要利用图形方式再现数据变动和发展趋势。EXCEL提供了强有力的图表处理功能，使得用户很快就可得到所要的图表。我们日常工作和生活中经常使用的图表类型有：1、柱形图：比较分析2、折线图：趋势分析3、饼图：占比分析着重标色以柱形图为例常用技巧4快捷键复制Ctrl+C粘贴