数据分析笔记-EXCEL（二）

一、基本概念

—表格组成：工作簿-工作表-行/列-单元格
—功能区
—菜单

二、基础操作

—源数据备份

1.右键工作表-移动或复制工作表-移至最后

2.右键新工作表-隐藏工作表（备份）

—导出pdf

三、理解数据

—数据量级
—数据类型
—数据含义
—统计口径

        进行数据的统计的项目内容，如可支配收入，包括工资、财产收入、奖金等。

四、报表逻辑

—统计时间
—报表主题
—数据看板
—数据详情

五、图表制作

—数据透视表

位置：当前工作表、新工作表（两种）

以新工作表为例：

WPS office可能出现没有分析栏的问题

 方法1：点击透视图表能直接出现

 方法2：文件-选项-自定义功能区-添加

—数据透视图(柱形图、折线图、组合图等等)

—图表类型切换（右键透视图，更换图表类型即可）
—切片器（主要用于数据透视图的快速筛选）

六、常用函数

—sum：求和

        公式：SUM（参数1，参数2，....）

        返回值：各参数和

        快捷键：ALT+=

—sumif：单条件求和

        公式：SUMIF（条件区域，条件，求和区域）

        eg:SUMIF(GMV列,时间列,">="&49999)

        时间在EXCEL都是以数字形式存在

        判断大小时注意使用    ">="&
—sumifs：多条件求和

        公式：SUMIFS（求和区域，条件区域1,条件1，条件区域2,条件2，...）

        eg:判断2020/1/1 -2020/1/7的GMV

        SUMIFS（GMV列，日期列,">="&XXXX，日期列,"<="&XXXX+7，...）

—subtotal：根据筛选求和

        与sum区别：

                1.sum 是将所有数据进行求和，无论有无筛选数据。

                2.subtotal是只求和筛选的单元数据，如果筛选全部则功能才与sum相同。

                3.subtotal可以使用sum以外更多的函数计算

        公式：SUBTOTAL（函数序号，引用1，引用2...）

—if：逻辑

        公式：IF(测试条件，真值，假值)

        技巧：

                1.可嵌套使用

                        IF("A1>A2","A1大"，IF("A1<A2","A2大"，"A1=A2"))

—VLOOKUP：连接匹配数据

        场景：新表格需要通过ID查询名称

        

         公式：VLOOKUP（"查找值（ID）"，数据表（整个原数据表），返回的值在第几列（如第2列就写2，精确查询/模糊查询））

        
—match：查找数值在区域中的位置

        公式：MATCH("查找值"，"该值在原表的哪个区域"，精确/模糊查找)

        场景：需要查找GMV，在原数据的哪一列

        案例：MATCH（"GMV","XX表A1：X1"，0）

        返回：如GMV是第三列，返回值为3

        技巧：参数1：GMV可以直接选单元格，这个更改单元格内容会直接查找对应内容，

        如GMV改成，商品单价

        
—index：根据区域的位置返回数值

        公式：INDEX（"区域"，行坐标，列坐标）

        技巧：坐标写0代表整行或者整列，可与SUMIFS一起用

—match、index：一起使用：自动根据列名查找数据

        
—sumifs、match、index顶级用法：自动根据列名的数据和条件进行求和

七、自动化报表开发

—报表框架、指标梳理、联动筛选、条件求和、数据自动识别、迷你图、同环比计算、进度条、图标指示、突出显示、细节美化

八、实用小技巧

1.新建窗口

        场景：需要使用多个工作表同时工作，如需要源数据与周报同时使用。

        方法：视图-新建窗口

2.分屏

        场景：一个屏幕同时显示左右两张工作表，与技巧1搭配味道更好嘿嘿

        方法：WIN+右方向键

3.快速生成筛选栏

        场景：需要筛选数据，如日期等

        方法：CTRL + SHIFT + L

暂时就想到这么多哈，后面有的话再补充~