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

1、Efficient data collection and visual representation in ExcelExcelExcel 高效数据汇总与可视化展示主办：四川纳税人俱乐部主讲：罗惠民日期：2015-08-23QQ群： 171824249(示例文件密码）网名：龙逸凡，Excel爱好者一枚，资深表哥作品：1、“偷懒”的技术：打造财务Excel达人2、龙逸凡Excel培训手册之潜龙在渊3、龙逸凡Excel培训手册之飞龙在天4、Excel你真会了吗？系列文章5、逸凡工作簿合并助手6、逸凡对账能手7、逸凡个税筹划助手8、逸凡账务系统9、会计凭证填制的误区、素养与技巧10、财务人员不可不

2、知的几项风险罗惠民资料课前调查你用过Excel哪些功能？SUMIF函数、VLOOKUP函数、INDEX函数、INDIRECT函数、定位、分列、条件格式、数据透视表数组公式、VBA课前问题怎样才能学好Excel？要有由头 工作上有需要要想学好EXCEL吃点苦头 低效率的加班有了劲头 学习的内在动力才有奔头 提高效率的巨大收益尝点甜头 提高了效率Excel的主要功能/需求示例01ExcelExcel操作全过程数据的输入与导入数据的展示与汇报数据的加工数据的选择数据的整理目录页 CONTENTS PAGE 高效选择数据的操作技巧高效选择数据的操作技巧1财务经典求和技巧与函数讲解财务经典求和技巧与函数

3、讲解2高效合并数据的方法与技巧高效合并数据的方法与技巧3表格美化与数据可视化展示表格美化与数据可视化展示4高效数据选择的操作技巧用鼠标高效选择数据的技巧 跳转 选择 移动 插入 填充 删除示例02高效数据选择的操作技巧用键盘高效选择数据的技巧 跳转：Ctrl+方向键 扩展：Shift+方向键 选择：Ctrl+Shift+箭头 填充：Ctlr+D，Ctrl+R 批量输入：Ctrl+Enter F4的作用查找的操作技巧与示例 查找特定格式的单元格 使用查找进行删除（删除零值、分行符） 定位含有指定字符、特定区间值的单元格 使用通配符 转置公式 修改公式报表翻新（财务报表）示例03定位的操作技巧与示

4、例 定位功能解释 即按条件选择单元格。 定位应用案例1、报表翻新（定位常量）2、查看本单元格数据变化后将影响哪些单元格3、整理合并单元格，等于上面、下面的单元格（定位空值）4、可见单元格，复制分类汇总筛选结果5、比对数字6、删除空行（行内容差异）、插入空行示例04目录页 CONTENTS PAGE 高效选择数据的操作技巧高效选择数据的操作技巧1财务经典求和技巧与函数讲解财务经典求和技巧与函数讲解2高效合并数据的方法与技巧高效合并数据的方法与技巧3表格美化与数据可视化展示表格美化与数据可视化展示4简单求和SUM函数 简单的求和SUM，快捷键Alt+，批量输入求和公式 对多个表格的同一单元格区域求

5、和方法1：=SUM(*!B2) （即：对除本表之外的其他工作表的B2单元格求和）方法2：在公式编辑栏输入:sum(,然后选中1月工作表，再按住【Shift】键，点选12月工作表，再点选B2单元格，再回车即可。u 要充分理解*和?在工作表名称中的含义，如果方法1换成=SUM(?!B2)和=SUM(?!B2)会是什么结果u 如果表格再调换一下顺序呢？示例06示例05单条件求和SUMIF函数作用：对条件区域进行判断，如果某些单元格满足指定条件，则对求和区域所对应的若干单元格进行求和。定位原理：第三参数单元格区域起作用的就是左上角那单元格，此单元格的作用是定位定点，只要有此定位点，SUMIF会自动以此

6、单元格为原点，按照第一参数区域符合条件的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。示例07SUMIF函数的多条件求和 多条件求和：SUMIFS（见下一节）、SUMPRODUCT（见下一页）=SUM(SUMIF(A2:A11,龙逸凡,罗惠民,B2:B11) =SUM(SUMIF(B2:B10,&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$

7、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表格美化与数

8、据可视化展示表格美化与数据可视化展示4课前问题怎样才算精通Excel？数据透视表数据透视表应用讲解应用讲解 应用举例：用透视表作本年累计、同比增长、环比增长分析方法：分析项目分析项目显示方式显示方式基本字段基本字段基本项基本项本年累计按某一字段汇总订单日期同比增长差异百分比年上一个环比增长差异百分比订单日期上一个快速制表、汇总的前提与思路1、前提：表格布局、文件名称、项目名称要统一；数据规范。2、保障手段：数据有效性、保护工作表/工作簿3、思路： 充分利用已有的报表，利用已有的报表布局及公式：删除数值保留公式利用已有报表的文件夹和表格布局：快速翻新公式 使用INDIRECT函数，提高表格的设计

9、效率 使用查找替换批量修改公式，提高表格设计效率一、同一工作簿多工作表的汇总1、工作表结构布局相同 不需列示分项数据：示例06（略） 需要列示分项数据： 查找替换法、使用INDIRECT2、工作表结构布局不同使用VLOOKUP、INDIRECT使用合并计算，参见后一节示例12示例11二、不同工作簿多工作表的汇总（一）、需要列示分项数据的汇总1、使用查找替换快速修改引用公式（报表翻新）示例参见财务报表文件夹2、使用INDIRECT函数实现快速引用（略）注意：INDIRECT引用其他工作簿，该表必须处理打开状态，否则 INDIRECT会 返回 #REF! 错误财务报表二、不同工作簿多工作表的汇总（

10、二）、仅需列示汇总结果1、使用合并计算进行跨工作簿汇总2、使用数据透视表进行跨工作簿汇总（略）合并计算二、不同工作簿多工作表的汇总（三）、集团公司如何快速汇总下属公司报表？设计总体要求：作为集团的财务人员一定要有服务的理念，在设计表格时，除了要便于集团公司汇总，还要考虑下属矿井填列数据是否方便。汇总表格要使用公式根据下属公司填报的数据自动汇总，不必手工填列。集团公司设计的表格不合理，下属公司应怎么解决不方便填列的问题？工具方法：参见前述方法营运周报 目录页 CONTENTS PAGE 高效选择数据的操作技巧高效选择数据的操作技巧1财务经典求和技巧与函数讲解财务经典求和技巧与函数讲解2表格美化与

11、数据可视化展示表格美化与数据可视化展示4高效合并数据的方法与技巧高效合并数据的方法与技巧3一、锦上添花表格的美化（一）、表格外观设计的常见表格外观设计的常见坏习惯示例13 一、锦上添花表格的美化（二）、表格美化的必要性表格美化的必要性你提交的报表有没有美化过？你认为表格美化有无必要？1、让表格变得更美观；2、让表格更方便阅读；3、体现自己的专业素养；一、锦上添花表格的美化（三）、表格美化的方法1、正确布局让逻辑更合理 注意数据的逻辑性； 注意数据记录有无排序要求； 注意版式的选择； 注意合计行/列的位置一、锦上添花表格的美化2、去粕降噪让版面更清爽仅在表格区域设置单元格边框和填充色，删除表格之

12、外单元格的内容和格式；尽量少用批注，如果必须使用批注，至少要做到不遮挡其他数据。也可以吸取国外著名财经杂志的经验，需要批注的加*号标注，然后在表格末尾备注；公式检验完后应使用IF、ISNA、IFERROR来消除错误值，比如=IF(ISERROR(A1/B1),0, A1/B1)、=IFERROR(A1/B1,0)， 公式未校验时最好不要先消除错误值。隐藏或删除零值或显示成小短横线。一、锦上添花表格的美化3、层次分明让结构更清晰 将同一类记录归组在一起；并将不同类别的记录或字段增加间距、拉开距离，同时将标题与数据间的间距拉开。一、锦上添花表格的美化3、层次分明让结构更清晰 缩进：明细级的项目应该

13、与上一层级保持一定的缩进，以凸显各项目的逻辑层次。同级的项目保持同样的缩进距离。 边框：用添加边框线条与否或其粗细来区分数据的层级：可只在重要层级的数据上设置边框或加粗。同一层级应使用同一粗细的线条边框。如果不是出于以下四个目的之一就不必使用边框：结构化表格、引导阅读、强调突出数据、美化表格。一、锦上添花表格的美化3、层次分明让结构更清晰 字号：使用不同粗细的字号来体现数据的层级，与边框同理， 填充色：为不同层次的数据设置不同的填充色，填充色不能太暗也不能太亮，颜色要与字体颜色相协调。填充色种类不能太多，多了显得花哨。一、锦上添花表格的美化4、鹤立鸡群让重点更突出对需要强调的重点数据，施以特别

14、的字体颜色、添加不同的单元格底色、加大加粗的字体、较粗的边框、加图形标注等。在颜色运用上可使用表格主色的互补色进行强调。一、锦上添花表格的美化5、格式得体让表格更美观字体要与表格用途协调、要与其他字体风格、大小相协调，颜色表格颜色不能太多，最好不要超过三种；如果要选用多种颜色，尽量使用类似色，可避免表格色彩杂乱。或者选定一种颜色，然后搭配此种颜色系列不同亮度的颜色（变淡或加深），这样的搭配，既可实现颜色的统一，也可体现层次感。造型可以添加简单的边框或单元格底色进行美化，还可以将边框和底色相结合进行造型。示例14 二、锦上添花数据可视化展示的方法与技巧（一）、数据的显示目标：条理清晰、重点突出、

15、方便阅读、智能化 条理清晰：参见报表的美化 重点突出：单元格样式 方便阅读：冻结窗格（如何保持底部合计行不动？）分组显示照相机 智能化：自定义格式、条件格式示例15 示例16示例17二、锦上添花数据可视化展示的方法与技巧（二）、报表的展示目标：高端大气上档次方法： 链接 报表美化（已介绍） 分级显示（已介绍） 迷你图 切片器 日程管理器示例18 示例20示例18示例19 示例21示例22二、锦上添花数据可视化展示的方法与技巧（三）、一图胜千言用图表说话方法： 迷你图 财务经典图表 交互图表 仪表盘 Power View二、锦上添花数据可视化展示的方法与技巧（三）、一图胜千言用图表说话财务经典图

16、表： 发展趋势分析经典图表 对比分析经典图表 组成结构分析经典图表 达成及进度分析经典图表 影响因素分析经典图表 财务管理分析经典图表本量利分析图二、锦上添花数据可视化展示的方法与技巧 发展趋势分析经典图表示例23 二、锦上添花数据可视化展示的方法与技巧 对比分析经典图表示例24 二、锦上添花数据可视化展示的方法与技巧 对比分析经典图表二、锦上添花数据可视化展示的方法与技巧 对比分析经典图表二、锦上添花数据可视化展示的方法与技巧 组成结构分析经典图表示例25 Global investment banks,share of revenue by region,%Source:DealogicAmericasAsia PacificEurope,Middle East and Africa二、锦上添花数据可视化展示的方法与技巧 组成结构分析经典图表二、锦上添花数据可视化展示的方法与技巧 达成及进度分析经典图表示例26 二、锦上添花数据可视化展示的方法与技巧 达成及进度分析经典图表二、锦上添花数据可视化展示的方法与技巧 影响因素互分析经典图表示例27二、锦上添花数据可视化展示的方法与技巧 影响因素互分析经典图表二、锦上添花数据可视化展示的方法与技巧 本量利分析经典图表示例28 二、锦上添花数据展示方法与技巧 祝大家早日成为财务ExcEl达人!