Excel高效数据分析及可视化展示课件.ppt

Efficient data collection and visual representation in Excel,Excel 高效数据汇总与可视化展示,主办：四川纳税人俱乐部主讲：罗惠民日期：2015-08-23QQ群：171824249(示例文件密码）,网名：龙逸凡，Excel爱好者一枚，资深表哥作品：1、“偷懒”的技术：打造财务Excel达人2、龙逸凡Excel培训手册之潜龙在渊3、龙逸凡Excel培训手册之飞龙在天4、Excel你真会了吗？系列文章5、逸凡工作簿合并助手6、逸凡对账能手7、逸凡个税筹划助手8、逸凡账务系统9、会计凭证填制的误区、素养与技巧10、财务人员不可不知的几项风险,罗惠民资料,课前调查,你用过Excel哪些功能？,SUMIF函数、VLOOKUP函数、INDEX函数、INDIRECT函数、定位、分列、条件格式、数据透视表数组公式、VBA,课前问题,怎样才能学好Excel？,要有由头 工作上有需要,要想学好EXCEL,吃点苦头 低效率的加班,有了劲头 学习的内在动力,才有奔头 提高效率的巨大收益,Excel的主要功能/需求,示例01,目录页 CONTENTS PAGE,高效选择数据的操作技巧,1,财务经典求和技巧与函数讲解,2,高效合并数据的方法与技巧,3,表格美化与数据可视化展示,4,高效数据选择的操作技巧,用鼠标高效选择数据的技巧,跳转选择移动插入填充删除,示例02,高效数据选择的操作技巧,用键盘高效选择数据的技巧,跳转：Ctrl+方向键扩展：Shift+方向键选择：Ctrl+Shift+箭头填充：Ctlr+D，Ctrl+R批量输入：Ctrl+EnterF4的作用,查找的操作技巧与示例,查找特定格式的单元格使用查找进行删除（删除零值、分行符）定位含有指定字符、特定区间值的单元格使用通配符转置公式修改公式报表翻新（财务报表）,示例03,定位的操作技巧与示例,定位功能解释 即按条件选择单元格。定位应用案例1、报表翻新（定位常量）2、查看本单元格数据变化后将影响哪些单元格3、整理合并单元格，等于上面、下面的单元格（定位空值）4、可见单元格，复制分类汇总筛选结果5、比对数字6、删除空行（行内容差异）、插入空行,示例04,目录页 CONTENTS PAGE,高效选择数据的操作技巧,1,财务经典求和技巧与函数讲解,2,高效合并数据的方法与技巧,3,表格美化与数据可视化展示,4,简单求和SUM函数,简单的求和SUM，快捷键Alt+，批量输入求和公式对多个表格的同一单元格区域求和方法1：=SUM(*!B2)（即：对除本表之外的其他工作表的B2单元格求和）方法2：在公式编辑栏输入:sum(,然后选中1月工作表，再按住【Shift】键，点选12月工作表，再点选B2单元格，再回车即可。要充分理解*和?在工作表名称中的含义，如果方法1换成=SUM(?!B2)和=SUM(?!B2)会是什么结果如果表格再调换一下顺序呢？,示例06,示例05,单条件求和SUMIF函数,作用：对条件区域进行判断，如果某些单元格满足指定条件，则对求和区域所对应的若干单元格进行求和。定位原理：第三参数单元格区域起作用的就是左上角那单元格，此单元格的作用是定位定点，只要有此定位点，SUMIF会自动以此单元格为原点，按照第一参数区域符合条件的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。,示例07,SUMIF函数的多条件求和,多条件求和：SUMIFS（见下一节）、SUMPRODUCT（见下一页）=SUM(SUMIF(A2:A11,龙逸凡,罗惠民,B2:B11)=SUM(SUMIF(B2:B10,=&3000,3500)*-1,1)多表单条件求和假设有三张表，分别为sheet1、sheet2、sheet3，三张表格式见示例7所示，要求三表中A列为龙逸凡的金额之和，公式为：=SUM(SUMIF(INDIRECT(sheet&1,2,3&!A2:A11),龙逸凡,INDIRECT(sheet&1,2,3&!B2:B11),示例07,不务正业的最佳劳模：SUMPRODUCT函数,1、使用SUMPRODUCT进行多条件计数=SUMPRODUCT(B2:B13=男)*(C2:C13=中级会计师)2、使用SUMPRODUCT进行多条件求和=SUMPRODUCT(B2:B13=男)*(C2:C13=中级会计师)*D2:D13)=SUMPRODUCT(B2:B13=“男”)*(C2:C13中级会计师)，D2:D13)3、使用“，”和“*”的区别：当拟求和的区域中无文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值#VALUE!，而使用“，”时SUMPRODUCT函数会将非数值型的数组元素作为 0 处理，不会报错。4、SUMPRODUCT函数不支持通配符“*”和“？”5、求某数值列前三名分数之和=SUMPRODUCT(LARGE(A1:A16,1,2,3)6、统计指定区域唯一值的个数=SUMPRODUCT(1/COUNTIF(A1:A16,A1:A16),示例08,财务常见求和公式,清单式表格中的求和本月产量=SUMIFS(表2生产数量（吨）,表2日期,&EOMONTH(C5,-1),表2日期,=&DATE(YEAR(C5),1,1),表2日期,=&DATE(YEAR(C5),1,1),表2日期,=&C5),示例09,财务经典求和公式,报表型表格中的求和1、每月数据相邻：求累计=SUM(OFFSET(D4,0,0,1,MATCH($B$2,$D$3:$O$3,0)2、每隔四列求和：名称固定：SUMIF名称不固定：=SUMPRODUCT(B2:F2)*(MOD(COLUMN(B2:F2),2)=0)3、指定区间求和：=SUMIF(INDIRECT(R3C6:R3C&$B$2*3+5,0),*预算,INDIRECT(RC6:RC&$B$2*3+5,0)4、仅对筛选出的行求和=SUBTOTAL(9,D5:D75),示例10,示例17 OFFSET函数,目录页 CONTENTS PAGE,高效选择数据的操作技巧,1,财务经典求和技巧与函数讲解,2,高效合并数据的方法与技巧,3,表格美化与数据可视化展示,4,课前问题,怎样才算精通Excel？,数据透视表应用讲解,应用举例：用透视表作本年累计、同比增长、环比增长分析方法：,快速制表、汇总的前提与思路,1、前提：表格布局、文件名称、项目名称要统一；数据规范。2、保障手段：数据有效性、保护工作表/工作簿3、思路：充分利用已有的报表，利用已有的报表布局及公式：删除数值保留公式利用已有报表的文件夹和表格布局：快速翻新公式使用INDIRECT函数，提高表格的设计效率使用查找替换批量修改公式，提高表格设计效率,一、同一工作簿多工作表的汇总,1、工作表结构布局相同不需列示分项数据：示例06（略）需要列示分项数据：查找替换法、使用INDIRECT2、工作表结构布局不同使用VLOOKUP、INDIRECT使用合并计算，参见后一节,示例12,示例11,二、不同工作簿多工作表的汇总,（一）、需要列示分项数据的汇总1、使用查找替换快速修改引用公式（报表翻新）示例参见财务报表文件夹2、使用INDIRECT函数实现快速引用（略）注意：INDIRECT引用其他工作簿，该表必须处理打开状态，否则 INDIRECT会 返回#REF!错误,财务报表,二、不同工作簿多工作表的汇总,（二）、仅需列示汇总结果1、使用合并计算进行跨工作簿汇总2、使用数据透视表进行跨工作簿汇总（略）,合并计算,二、不同工作簿多工作表的汇总,（三）、集团公司如何快速汇总下属公司报表？设计总体要求：作为集团的财务人员一定要有服务的理念，在设计表格时，除了要便于集团公司汇总，还要考虑下属矿井填列数据是否方便。汇总表格要使用公式根据下属公司填报的数据自动汇总，不必手工填列。集团公司设计的表格不合理，下属公司应怎么解决不方便填列的问题？工具方法：参见前述方法,营运周报,目录页 CONTENTS PAGE,高效选择数据的操作技巧,1,财务经典求和技巧与函数讲解,2,表格美化与数据可视化展示,4,一、锦上添花表格的美化,（一）、表格外观设计的常见坏习惯,示例13,一、锦上添花表格的美化,（二）、表格美化的必要性,你提交的报表有没有美化过？你认为表格美化有无必要？,1、让表格变得更美观；2、让表格更方便阅读；3、体现自己的专业素养；,一、锦上添花表格的美化,（三）、表格美化的方法1、正确布局让逻辑更合理注意数据的逻辑性；注意数据记录有无排序要求；注意版式的选择；注意合计行/列的位置,一、锦上添花表格的美化,2、去粕降噪让版面更清爽仅在表格区域设置单元格边框和填充色，删除表格之外单元格的内容和格式；尽量少用批注，如果必须使用批注，至少要做到不遮挡其他数据。也可以吸取国外著名财经杂志的经验，需要批注的加*号标注，然后在表格末尾备注；公式检验完后应使用IF、ISNA、IFERROR来消除错误值，比如=IF(ISERROR(A1/B1),0,A1/B1)、=IFERROR(A1/B1,0)，公式未校验时最好不要先消除错误值。隐藏或删除零值或显示成小短横线。,一、锦上添花表格的美化,3、层次分明让结构更清晰将同一类记录归组在一起；并将不同类别的记录或字段增加间距、拉开距离，同时将标题与数据间的间距拉开。,一、锦上添花表格的美化,3、层次分明让结构更清晰缩进：明细级的项目应该与上一层级保持一定的缩进，以凸显各项目的逻辑层次。同级的项目保持同样的缩进距离。边框：用添加边框线条与否或其粗细来区分数据的层级：可只在重要层级的数据上设置边框或加粗。同一层级应使用同一粗细的线条边框。如果不是出于以下四个目的之一就不必使用边框：结构化表格、引导阅读、强调突出数据、美化表格。,一、锦上添花表格的美化,3、层次分明让结构更清晰字号：使用不同粗细的字号来体现数据的层级，与边框同理，填充色：为不同层次的数据设置不同的填充色，填充色不能太暗也不能太亮，颜色要与字体颜色相协调。填充色种类不能太多，多了显得花哨。,一、锦上添花表格的美化,4、鹤立鸡群让重点更突出对需要强调的重点数据，施以特别的字体颜色、添加不同的单元格底色、加大加粗的字体、较粗的边框、加图形标注等。在颜色运用上可使用表格主色的互补色进行强调。,一、锦上添花表格的美化,5、格式得体让表格更美观字体要与表格用途协调、要与其他字体风格、大小相协调，颜色表格颜色不能太多，最好不要超过三种；如果要选用多种颜色，尽量使用类似色，可避免表格色彩杂乱。或者选定一种颜色，然后搭配此种颜色系列不同亮度的颜色（变淡或加深），这样的搭配，既可实现颜色的统一，也可体现层次感。造型可以添加简单的边框或单元格底色进行美化，还可以将边框和底色相结合进行造型。,示例14,二、锦上添花数据可视化展示的方法与技巧,（一）、数据的显示目标：条理清晰、重点突出、方便阅读、智能化条理清晰：参见报表的美化重点突出：单元格样式方便阅读：冻结窗格（如何保持底部合计行不动？）分组显示照相机智能化：自定义格式、条件格式,示例15,示例16,示例17,二、锦上添花数据可视化展示的方法与技巧,（二）、报表的展示目标：高端大气上档次方法：链接报表美化（已介绍）分级显示（已介绍）迷你图切片器日程管理器,示例18,示例20,示例18,示例19,示例21,示例22,二、锦上添花数据可视化展示的方法与技巧,（三）、一图胜千言用图表说话方法：迷你图财务经典图表交互图表仪表盘Power View,二、锦上添花数据可视化展示的方法与技巧,（三）、一图胜千言用图表说话财务经典图表：发展趋势分析经典图表对比分析经典图表组成结构分析经典图表达成及进度分析经典图表影响因素分析经典图表财务管理分析经典图表本量利分析图,二、锦上添花数据可视化展示的方法与技巧,发展趋势分析经典图表,示例23,二、锦上添花数据可视化展示的方法与技巧,对比分析经典图表,示例24,二、锦上添花数据可视化展示的方法与技巧,对比分析经典图表,二、锦上添花数据可视化展示的方法与技巧,对比分析经典图表,二、锦上添花数据可视化展示的方法与技巧,组成结构分析经典图表,示例25,二、锦上添花数据可视化展示的方法与技巧,组成结构分析经典图表,二、锦上添花数据可视化展示的方法与技巧,达成及进度分析经典图表,示例26,二、锦上添花数据可视化展示的方法与技巧,达成及进度分析经典图表,二、锦上添花数据可视化展示的方法与技巧,影响因素互分析经典图表,示例27,二、锦上添花数据可视化展示的方法与技巧,影响因素互分析经典图表,二、锦上添花数据可视化展示的方法与技巧,本量利分析经典图表,示例28,二、锦上添花数据展示方法与技巧,祝大家早日成为财务Excel达人!,