数据分析-excel

数据分析-excel

• 拿到excel表，先备份一份源数据
• 然后对源数据进行分析，快速筛选有助于分析（ctrl+shift+L ——筛选）

一、数据透视表

• 制作数据透视表（插入-数据透视表）
  • 将已有固定字段添加到报表（筛选、列、行、行列对应的值）
  • 创建新的字段（数据透视表分析-字段、项目和集-计算字段）
  • 筛选
    • 插入切片器（作用筛选，位置：数据透视表分析-插入切片器）
    • 直接把要筛选的字段拖入到筛选中
    • 区别：切片器可以在透视表外进行控制
  • 透视图表的制作
    • 选择要制作图表的标签，并选择要制作的数据透视表类型

二、Excel常用函数

• 常用技巧，冻结首行首列、新建窗口(位置：视图）

1. sum

• 语法：sum(num1,num2,…) 整行sum(A:A) sum(行名)
• sum(单个或多个单元格）、sum(A1:A22)

2. sumif

• 语法：sumif(条件判断区域，条件，用来求和的数值区域）
• 锁定列——=$B15，B不会发生变化，15会变化
• 日期：例如B15的值为2020/07/01，想要得到2020/07/16，只需要输入“=B15+15”

3. sumifs–多条件求和

• sumifs(用来求和的数值区域，条件1判断所在区域，条件1，条件2判断所在区域，条件2…)
• 环比、同比的求法
  • 2020年环比=2020年数据/2019年数据-1
  • 2020年7月环比=2020年7月数据/2020年6月数据-1
  • 2020年7月同比=2020年7月数据/2019年7月数据-1
  • 2020年7月1日环比=2020年7月1日数据/2020年6月30日数据-1
  • 2020年7月1日月同比=2020年7月1日数据/2020年6月1日数据-1
  • 2020年7月1日周同比=2020年7月1日数据/2020年6月24日数据-1
• DATE（year,month,day)
• year(B30)——年，month(B30)——月，day（B30)——日
• 不要用excel的日期格式存储日期——用字符串
• 每个月的第一天：DATE(YEAR(B39),MONTH(B39),1)
• 每个月的最后一天：DATE(YEAR(B39),MONTH(B39)+1,1)-1

4. sum和subtotal的区别

• subtotal(指定函数，选择区域1，选择区域2，…)
• subtotal可以根据筛选改变

5. if函数

• if(逻辑比较条件，结果成立返回值，结果不成立返回值）

6. vlookup函数

• 语法：vlookup(要查找的数据,要查找的位置（必须在第一行）和要返回的数据区域，要返回的数据在区域中的列号，返回近似匹配为1或精确匹配为0-指示为1/TRUE或0/FALSE)
• 通配符：*——代替不定数量的字符，？——代替一个字符
• 符合条件的可能有多个，但是只返回第一个
• vlookup基于聚合运算结果（透视表）进行查找

7. index和match函数（关键）

• match作用：在选定的行或列里找出已给出的值的位置
• 语法：match(查找项，查找区域，0）
• index作用：得到索引值
• 语法：index(区域，行号，列号）
• 组合用法：
• index(数据区域,match(行查找项，index数据区域的相对区域,0),match(列查找项，indexB数据区域的相对区域，0)

三、大厂周报表

1. 日期只填一个，其他的都是所填日期的应用，以此达到整表变换
2. GMV的计算，if(平台=“全部”，sumif(日期列，日期，gmv),sumifs(gmv,日期列，日期，平台列，平台)
3. 灵活的图表在于正则化，及将所在列用正则化的形式匹配。
4. 根据数据类型的不同，设置格式