数据分析基础知识学习之Excel

最近在学习数据分析，看了b站上戴戴戴师兄的课，在这里记录一下笔记 ~

数据透视表

• 数据透视图字段

  

• 分析-显示-字段列表
• 拖动字段自动生成行、值
• 数据透视表中的筛选器和切片器类似，都是用来筛选。差别是：筛选器只作用于数据透视表，而切片器可以作用于整个表，也可直接复制到其他工作表。
• 数据透视图（可按需选择各种类型的图标自动生成）

常用函数

Tips

• 视图-新建窗口：方便数据查看；
• win+方向键：多窗口视图（先新建窗口再多窗口查看）
• 视图-冻结窗格：固定首行或首列（固定首行和首列可通过：点击B2单元格再选冻结拆分窗格）
• fn+f4：【绝对引用】添加 $，锁定单元格
• !!!  向下拖动时，只有行索引标签会变；而向左右拖动时，列索引标签会变，因此适时考虑绝对引用（例：B111，$B$111是锁定该单元格、$B111可单独锁定列，111可变、B$111即单独锁定行）
• 日期和数字可以转换（1对应的是1900-1-1）

求和函数

• SUMIF(range, criteria, [sum_range]) 单条件求和：range是指与判断条件作对比的区域
• SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 多条件求和（sumifs 也可实现 sumif）
  注意!! sumif 和 sumifs 的求和区域一个在最前一个在最后
• 条件中的大于等于之类的运算符需要加 "" 和 & !!
• subtotal 会根据筛选变化，而 sumifs 不会

计算日期相关函数

• YEAR(日期)：提取年份
• MONTH(日期)：提取月份
• DAY(日期)：提取几号
• DATE(year,month,day):但在一些30、31的日子上有错，待探究其他方法
• EXCEL中 永远不要拿EXCEL中的日期格式去存储日期！！！！要用字符串的格式！！
• 每个月的第一天：=DATE(YEAR(B39),MONTH(B39),1)；每个月的最后一天：用下一个月的第一天-1 =DATE(YEAR(B39),MONTH(B39)+1,1)-1

IF函数及IF函数嵌套

不难就是一些逻辑判断问题

vlookup

• 查找的那一列必须在区域的第一列，否则会报错
• 如果表中的查找的数据后面有空格，那么将会查找失败
• 添加 &"*" 字符可以找到以查找对象开头的所有对象，但vlookup仅仅显示查找到的第一个对象
• * 代替不定数量的字符
• ? 占位符：（英文输入状态下）代替一个字符，输入几个？就代替几个字符
• 注意拖动时，数据区域也会位移，记得添加锁定符

index和match

• 实现表格的灵活获取对应表头查找数据
• MATCH(lookup_value,lookup_array,[match_type])
• INDEX(array,row_num,column_num)
  index中如果行数或者列数为0的话，将会返回整行或整列
• index(数据区域，match(行查找项，index数据区域的相对区域,0),match(列查找项，index数据区域的相对区域,0))
• 可以用锁定符单独锁定行或列

大厂周报模板开发

单元格的下拉菜单选择：本质是一个条件验证，使用 数据-数据验证 ，再将该单元格放入函数就能变成筛选器。

运用 MATCH、INDEX、SUMIFS 等前面学过的函数，巧妙结合

进度条、上下箭头、标记低于平均值等美化工作：

开始 - 条件格式 - 新建规则

最终周报效果图