【学习】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了。。

推荐阅读更多精彩内容