【学习】EXCEL

还是来学习EXCEL的便捷使用了- -。。
https://space.bilibili.com/397447219?spm_id_from=333.788.b_765f7570696e666f.1

ps:
alt+enter：内容换行
ctrl+shift+↓ ：选中该列的有效区域
F4 ：绝对引用

一、基础

1.1 表格排版基础

image.png

涉及到的知识点：

• 垂直居中和水平居中处理；
• 批量处理行距；
• 批量处理间隔灰度；
• 边框（颜色）处理；
• 视图选项隐藏网格；
• 其他：合并/插入/隐藏单元格

1.2 自定义格式

image.png

• 数字占位符：
  0：固定显示为多少位，缺少则用0补充；
  *：只显示有数学意义的0，小数后如果不够的话用0不足，小数前的话缺少位数也不用0补足；
  ?：固定显示为多少位，缺少则用空格补充（这个适合对齐）；也可以用来控制显示为分母形式；
  .：小数点形式
  ,：用来显示千分位分隔符（#，###），或者用来缩小1000的n次方倍（#,；#,,）；
  @:用来在文本中增加固定的字符串，@用来指代原字符串，常用的就是在数字面前加上人民币标志表示钱的用法。

  
  image.png
  

  　 * ：重复下一个字符，直至填充满列宽
  [条件]：就是case when 的用法，只不过这里条件最多是三个，前两个是case when，最后一个是else

1.3 分列数据

image.png

• 分隔符号分列
• 固定宽度分列
• 忽略列/导出数据
  一般是配合固定宽度分列，然后忽略掉里面的几列；
• 修正数据
  修正字符型数值：'1234修正为1234（因为excel里的开头'是特殊字符，都是不会显示的，除非用两个''才能显示其中一个，如果是放在字符串中间是可以显示。）

1.4 填充

image.png

• 拉动填充：下拉或者双击都可以（双击填充感觉有一个智能填充的逻辑在里面，他必须识别到旁边有数据的，然后才能智能填充多少列）
• 等差序列/等比序列/日期填充：都是在选项卡里面设置
• 填充格式：为了保留格式
• 快速填充：ctrl+E，这个是最好用的，综合了智能填充和自动分列的功能。
  image.png
  给定一个示例，比如把苹果手机6999元里面的苹果手机提取出来，然后ctrl+E能够自动填充为： image.png
  字符串和数字的分列逻辑很容易，但是字符串如果内部分列的话就不准确了，显而易见哈哈哈

1.5 查找与替换

image.png

• 内容查找与替换
  查找出结果后，可以ctrl+A选出所有查找结果
• 格式的查找与替换
  可以将特定格式选出，然后将其替换为其他字符或者格式，例如选出红色字体替换为蓝色字体

• 查找/替换高级选项

  
  image.png
• 通配符
  ？表示位数必须匹配，表示随意匹配，~则是表示匹配*这个字符

1.6 文档保护与打印

加密之类的

二、函数

2.1 常用数学函数

image.png

• 引用：常规操作

• 常用函数
  sum和sumif和sumifs：sumif适用于有明确条件区域和求和区域的，sumifs适用于条件区域不明确，还要多级细化的

  
  image.png
  

  sumif因为相当于是groupby求和，所以如果中间有条件判断，比如把>5的累加求和，那么还不能直接用sumif，得先用一列标记把>5的标记出来，再进行groupby求和。或者就是其他方法。
  round：四舍五入保留n位小数
  mod：求余数
  int:向下取整，要四舍五入取整的话得用round(x,0)

2.2 常用逻辑函数

image.png

• 算数逻辑符号
• IF和IFS：IF可以实现IFS的功能，采用多级嵌套循环就行了。。。if elif ..
• 与或逻辑符号

2.3 常用时间和日期函数

image.png

• date：规范日期的格式
• year/month/day：返回年月日
• today/now：current
• datedif：计算日期差

2.4 查找和引用函数

image.png

• match：返回符合条件的值的位置，相当于返回index/column
• index：返回n行n列的值，相当于loc/iloc
  match和index经常是搭配起来用

• row/column：就是返回行/列的序号，这个的适用场景是为了解决动态列表中序列号经常变化的问题，用序列填充后的序列值，在插入新行之后不会变动，但是如果用row来返回的序列值的话，插入新行也不会影响序列值的实用性：

  
  image.png

2.5 VLOOKUP

image.png

VLOOPUP的本质是用df[df[xxx]='xxx']，所以还是一个查找数据的方法，需要注意的是，如果查找范围的序列（也就是第一列）是惟一的，那么也可以用sumif来返回对应的值（因为只有唯一值，所以不存在累加）

2.6 字符串函数

image.png

• left/right函数：从左/右截取
• mid：从中间截取
• len：返回长度
• find：返回要查找的字符串在目标字符串的位置

• search：高阶的find，支持通配符

  
  image.png
  

  上面这个查找A开头-结尾的字符串，就是用search里面的通配符完成

三、图表制作

3.1 图表基础知识

不管

3.2 动态图表原理

image.png

A区域为原始数据，C区域为制作的图表，B区域为C区域中选定季度选项后出来的对应数据。。。
所以相当于比传统数据多了一个筛选数据源的筛选器，然后筛选了数据源后不仅可以作图，还会出来一个对应的转化数据表格。
方法不研究，反正也用不到。

四、透视表

image.png

用透视表还是很容易做的，因为自带筛选器。具体过程略过。

五、邮件合并发送

image.png

以发送工资条为例，原始数据为excel表格，然后在word中形成发送的邮件模板：

image.png

然后选择“邮件合并”，按步骤导入excel中的各项数据；
然后用outlook发送就over了。。