T81-秋叶EXCEL@拉登直播 第一课【规范篇】

【导图来源】来自Excel拉登老师真人视频精讲，他是个幽默的段子手，“请梳头”！ 【内容包含】 认识数据规范、表格七宗罪、表格设计五步走，批量处理经典案例。 【小钟共享】希望我的总结助你提升Excel技能，成为办公效率达人。

秋叶EXCEL@拉登直播 第一课【规范篇】

• 认识数据规范
• 1-美化的专业素质
  • 1||| 考虑表格用途
  • 2||| 结构合理、层次清晰、重点突出、排版美观、方便阅读和打印
• 2-数据分析的专业素质
  • 1||| 数据注意勾稽对比，校验机制
  • 2||| 计标准的表格模板，让别人按你的要求填写数据
  • 3||| 时性操作最好在复制的副本上进行
• 3-规范表格，提现精益求精的工作态度
  • 1||| 同一类型的工作表放同一工作簿，同一类工作簿放同一文件夹【同时可以通过文件目录进行管理，工作更有条理】
  • 2||| 主要字段排在前面
  • 3||| 最好一个单元格，单列数据就代表一个项。按列可筛选，数据透视表。
• 表格统计变麻烦的原因
• 案例1： 一维表
  • 1||| 需求：统计工时数总和
  • 2||| 实现思路
    • SUM求和即可
  • 3||| 遇到困难
    • 简单的一维表，统计也非常容易
• 案例2： 二维表
  • 1||| 需求：统计每天的工时数总和
  • 2||| 实现思路
    • SUMIF函数
    • 或者数据透视表
  • 3||| 遇到的困难
    • 增加了日期的维度
    • 统计公式开始变的复杂
• 案例3： 时间段混装
  • 1||| 需求：统计每天的工时数总和
  • 2||| 实现思路
    • 先根据时间段，计算出工时
    • 在根据日期，统计每天的公式总和
  • 3||| 遇到的困难
    • 日期格式不统一的条件求和
    • 日期和周别混装的复杂条件求和
    • 时间格式不规范，缺少工时字段
• 案例4： 阅读型表格
  • 1||| 需求：统计每天的工时数总和
  • 2||| 实现思路
    • 使用复杂的VBA编程，把数据整理成简单的清单表格
  • 3||| 遇到的困难
    • 阅读型表格，完全没有思路，只能手动统计
• 表格七宗罪（数据不规范+结构不规范）
• 案例1: 缺失的数据
  • 问题截图
  • 解决方法
    • 将数据记录在单元格中
• 案例2: 不统一的数据
  • 问题截图
  • 解决方法
    • 查找替换
• 案例3: 挤一起的数据
  • 问题截图
  • 解决方法
    • 【分列】功能，拆分数据
    • 查找替换
    • 文本拆分
• 案例4: 合并单元格
  • 问题截图
  • 解决方法
    • 取消合并
    • Ctrl+G定位空值
    • Ctrl+Enter批量填充
    • 规范篇预习视频
  • 拓展案例
    • 合并单元格技巧
• 案例5: 空白的行/列
  • 问题截图
  • 解决方法
    • Ctrl+G定位空值
    • 批量删除整行
    • 【规范篇】预习视频
  • 拓展案例
    • 行列操作
• 案例6: 每天/月一张表
  • 问题截图
  • 解决方法
    • 工作表/簿合并
• 案例7: 前任遗留的表格
  • 问题截图
  • 解决方法
    • 一开始就把表格设计成清单，避免给统计带来麻烦
  • 拓展案例
    • 行列操作
• 好的表格是设计出来的
• 规范的数据
  • 1. 公式都是用来统计数据的
  • 2. 数据统计简单、快速。
• 不规范的数据
  • 1. 公式都是用来清洗数据的
  • 2. 复杂的函数公式，都是在给不规范的数据擦屁股
• 设置下拉列表，让数据高效录入不出错
  • 常见问题
    • 参数表里的数据，如果修改了，对已经录入的数据，不会有影响。
    • 如果删除了参数表的内容，已经录入的数据依然还在，只是下拉列表选项没有了。
    • 如果要添加新的下拉列表选项，则需要修改数据验证的源区域。
  • 如何设置自动更新的下拉列表
    • 文字补充
• 案例1： 【手机号】限制输入长度为11位
  • 规范文本长度
• 案例2： 【下单金额】只能输入数字
  • 规范数字输入
• 案例3：【办理日期】限制输入日期格式
  • 数据验证
  • 在数据验证里，设置日期选项
• 案例4：【办理日期】增加输入提醒
  • 输入提示
    • 效果如图
• 案例5： 【下单金额】增加错误提醒
  • 出错提示
    • 效果如图
• 案例6： 如何删除数据规范
  • 常见问题
    • 删除数据验证之后，添加录入的数据，不会被删除
• 案例7： 限制【办理日期】>今天的日期
  • 公式如下：
    • =TODAY（）函数，返回当前的日期
• 案例8： 限制【会员编号】必须是QY开头
  • ”提取字符“公式如下：
    • =LEFT(J5,2)="QY"
• 表格设计5步走
• 1-设计大框架
  • 三表原则
  • 录入表：记录和收集数据，通常都会有表格7宗罪的问题
  • 数据表：录入表的数据进行规范整理，转换成明细表
  • 汇总表：数据统计汇总，或者用图表形式呈现结果
• 2-设计录入表
  • 信息分类：用合并单元格的方式，让信息分类清晰
  • 方便打印：尽量在一页中排版，而且打印出来好看
  • 眼睛看着舒服：用合并单元格、空白行、颜色标记等方式，让眼睛看着舒服，好看
• 3-设计数据表
  • 明细表、流水表：就像银行流水、话费清单，不需要考虑排版的问题。
  • 给汇总表提供规范数据源：可以基于透视表、函数公式等方法，快速完成数据统计。
  • 注意做好数据规范：把录入表中不规范的数据，清洗、转换成规范的数据表
  • 善用外部工具
    • 石墨表单
    • 腾讯问卷
    • 腾讯文档https://docs.qq.com/
    • 语雀
    • 问卷星等等
• 4-设计汇总表
  • 总结观点：通过汇总统计，挖掘和总结数据背后的观点、结论。
  • 直观清晰表达：通过图表、颜色、形状等可视化形式，让数据更加简单、直观的呈现。
  • 汇总表的形式
    • 汇总表。用来统计汇总相关指标的表。比如透视表的结果
    • 可视化。把统计结果转成图表，输出数据看板的表。
    • 查询表。可以根据搜索词，查询所有相关记录的表。
• 5-表格自动化
  • 使用Excel技术功能，完成原始数据->需求表->图表的自动化统计
  • Excel自动化技术包括但不限于
    • 函数公式
    • 透视表
    • 超级表格
    • Power Query
    • VBA
    • Python
    • SQL
  • 新手常见表格设计错误
    • 录入表、汇总表都在一张表格中，统计维度被固定死，无法进行灵活的分析。
    • 数据表缺失，在汇总统计数据是，编写复杂的函数公式，对数据进行清洗、整理。
• 1-表格需求
  • 某幼儿园在收学费的时候，每个月建立了一张表格，记录了每个同学的费用。
  • 现在，需要把每个月的费用，按照【资金类型】，汇总到【资金汇总】工作表中。
• 2-表格结构梳理
  • 1||| 录入表
    • 每天一张表就是录入表
    • 为了方便录入数据，所以每个月一张表，记录当月的费用明细。
    • 为了方便录入设计的表格，都可以认为是【录入表】
  • 2||| 数据表
    • 目前缺失数据表
    • 目标结果，是统计【资金类型】的金额汇总。
    • 但是每个月的费用是按照学生的姓名记录的，和汇总结果不同
    • 数据汇总起来比较的困难，就是因为缺失了【数据表】
  • 3||| 汇总表
    • 按照【资金类型】汇总每个月的「总收入」和「总支出」
• 第1部分：建立数据表
  • 1- 在【资金汇总】表中，选择B20:G32单元格区域，按下Ctrl+C复制单元格。
    • 这个区域就是【数据表】区域，和汇总的结果一样，按照【资金类型】来汇总数据的。
  • 2- 选择【9月】工作表，然后按住Shift键点击【12月】工作表的数据。
    • 这样可以批量选择9月~12月之间所有的工作表。
  • 3- 在【9月】工作表中，选择V20单元格，按下Ctrl+V粘贴数据。
    • 此时，刚复制的数据，会被批量粘贴到9月~12月每个工作表中去
  • 4- 在【9月】工作表中的X21单元格中，使用SUMIF函数，按照【资金类型】统计「总收入」
    • 公式
      • =SUMIF($L$5:$L$35,W21,$M$5:$M$35)
  • 5- 选择X21单元格，双击右下角，快速填充公式到所有单元格。
  • 6- 在Y21单元格，使用SUMIF函数，统计总支出，并双击右下角，填充到所有单元格。
    • 公式
      • =SUMIF($O$5:$O$35,W21,$N$5:$N$35)
• 第2部分：快速汇总求和
  • 选择【资金汇总】工作表
  • 在D21单元格公式中，使用SUM函数实现跨表求和，对数据表中的数据进行求和统计。
    • 公式如下
      • =SUM('9月:12月 '!X21)
    • 公式说明
      • 9月:12月
        • 代表【9月】~【12月】之间的所有工作表
      • '9月:12月 '!X21
        • 代表这些工作表中的X21单元格
    • 公式截图
    • 快速输入公式的方法
      • 1- 在D21单元格中输入下面的公式
        • =SUM(
      • 2- 选择【9月】工作表的X21单元格
      • 3- 按住Shift键，选择【12月】工作表，此时，公式就变成了下面的样子，输入右括号，按下回车就可以了。
        • 公式
          • =sum('9月:12月 '!X21
  • 填充公式到所有单元格，完成跨表数据的汇总
• 选修进阶课程
• 1-会员编号必须以A开头
  • 1||| LEFT函数获取左边的符号
    • =LEFT(J5,1)
    • 示意图
    • 函数说明
  • 2||| 判断提取结果是否="A"
    • =LEFT(J5,1)="A"
  • 3||| 公式填入到数据验证中去
• 2-限制出生日期必须是2000/1/1之前的日期
• 3-出生日期必须是1990/5/6的格式
  • 1||| 输入提示
  • 2||| 警告提示
  • 3||| 提示内容
    • 提示标题"注意日期格式"，
    • 提示内容"请按照1990/5/6的格式输入日期。"
• 4-如何正确输入身份证号码？
  • 1||| 设置单元格格式为“文本”
  • 2||| 重新输入身份证号码
• 5-如何在数字前面补0
  • 1||| 方法1，文本输入
    • 设置单元格格式为“文本”
    • 输入001的序号
  • 2||| 方法2，单元格格式法
    • 选中所有单元格，按下Ctrl+1，打开单元格格式对话框
    • 设置单元格格式为【自定义】【000】
    • 设置完成后，输入1，会自动补齐前面的0
• 6-批量规范日期格式
  • 1||| 2017.2.4格式，查找替换转换
    • 查找.替换为/即可
  • 2||| 20170930格式，分列方法转换
    • 选择日期整列，在【数据】选项卡中，点击【分列】
    • 一直点下一步，在第3步中，设置分列后的格式为【日期】，点击完成。
    • 日期就自动变成规范的格式了
  • 3||| 42823格式，设置为日期格式即可。
    • 选择日期整列，设置单元格格式为“日期”
• 7-批量删除单位
  • 1||| 按下Ctrl+H，打开查找替换对话框
  • 2||| 查找“元”，替换为不写，表示删除，全部替换即可
• 8-批量删除空格
  • 1||| 使用CLEAN函数，可以删除不可见的空格
  • 2||| 公式修改如下：
    • =CLEAN(H74)*I74
• 9-批量统一名称
  • 1||| 方法1，查找替换
    • 按下Ctrl+H，打开查找对话框
    • 查找"生产*"，替换为"生产部"，点击全部替换
    • 把“制造部”修改为“生产部”即可
  • 2||| 方法2，筛选批量填充
    • 筛选“部门”列，搜索“生产”
    • 筛选“部门”列，搜索“制造部”，勾选“将当前所选内容，添加到筛选器”
    • 选中筛选的结果，输入“生产部”，按下Ctrl+Enter，批量修改所有选择的单元格
• 第1课【规范篇】答疑合集案例
  • 超链接网址
  • 合并单元格，问题合集(PDF)
  • 数据录入规范，问题合集(PDF)
• 【规范篇】作业练习表
  • 附件链接