课前预习
课堂笔记
1. Excel数据处理基本流程
重点：懂得Excel的底层逻辑与原理
2. Excel高效工作4步
- 规范化记录
- 批量化整理
- 多维度分析
- 视觉化输出
内功心法
3. 基础表格七宗罪
3.1 一宗罪：多余的表格大标题
3.2 二宗罪：缺失的标题字段
3.3 三宗罪：不一致的字段次序
3.4 四宗罪：挤在同一单元格里的数据
3.5 五宗罪：破坏灵活性的合并单元格
3.6 六宗罪：割裂数据区的空行空列小计行
3.7 七宗罪：不一致的数据
4. 表格设计五步走
梳理需求 >> 用途拆解 >> 挑选字段 >> 排定次序 >> 预设规则
4.1 梳理需求：以终为始，逆向分析
4.2 用途拆解：一表医用，关联查询
4.3 挑选字段：最小单元，不重不漏
4.4 排定次序：先主后次，保持一致
4.5 预设规范：格式匹配，数据验证
5. 本课学习内容
6. 实操训练
6.1 快捷键的使用（快速输入数据）
Tab向右，Shift+Tab反向，向左；
Enter向下，Shift+Tab反向，向上；
Ctrl+;=输入当天日期。
6.2 数据验证（数据有效性）
办理日期和出生日期：可用TODAY()函数进行条件约束；涉及到格式（文本，格式，日期）的使用；
会员编号：
- 【数据验证】设置 - 以字母QY开头的，7位，非重复序列； =AND(LEN(A1)=7,LEFT(A1,2)=”QY”,COUNTIF($A$1:$A$16,A1)=1)=TRUE；
- 若前两位字符为随机字母，则可用=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))；
- 随机生成字母：可使用函数嵌套 INDEX函数+RANDBETWEEN函数组合提取随机
- 在【数据】选项卡中点击【数据验证】，【允许】中选择【文本长度】
- 设置输入信息以提示输入者
- 设置出错警告
性别、学历、省、市：可设置一级、二级下拉列表；设置二级下拉列表，涉及到名称定义。
注意：使用二级下拉列表时，=INDIRECT($A1)中的目标单元格不能锁死。
手机号码：
- 【数据验证】设置 - 11位，非重复序列，有效数字（整数）
=AND(INT(A1)=A1,LEN(A1)=11,COUNTIF($A$1:$A$16,A1)<2,LEFT(A1,1)=”1”)=TRUE
- 在【数据】选项卡中点击【数据验证】，【允许】中选择【文本长度】
- 设置输入信息以提示输入者
- 设置出错警告
6.3 Ctrl+T 智能表格（超级表格）
创建动态引用区域；本课中用于准备好的选项列表，下拉列表中新增选项自动更新。
如何撤销智能表格：选中表格，【设计】-转换为（普通）区域
超级表格自动汇总：勾选【设计】-汇总行
删除智能表格中的内容或空格：
（1）Ctrl+【-】（减号），删除单元格
（2）鼠标拖拽右下角的智能表格结束标记，缩小范围6.4 锁定工作表格和可编辑区域
（1）在【审阅】选项卡中点击【保护工作表】
（2）设置允许编辑区域
（3）点击新建，可以在【新区域】中重命名和是指区域密码
（4）可以设置密码以及允许用户操作的事项
注意：千万记住设置的密码
6.5 同时收集很多人填写的表格信息时
可以先设计好表单格式，然后使用在线协同功能（例如金山文档、腾讯文档、语雀、钉钉在线编辑等）。
7. 本课作业与答疑（部分重点）
7.1 身份乱码
分列转换文本：【数据】-【分列】；前2步都直接点【下一步】；
第3步【列数据格式】选择【文本】，点击完成。
注意分列时，选择“不导入此列”，避免覆盖右边一列的数据。
7.2 保留单位并进行计算
【设置单元格格式】-【自定义】，输入#”单位”（英文“双引号”）
7.3 加餐问题：Textjoin函数
Textjoin函数仅存在于Excel2016以上的版本（含Office365）。简单来说就是一个连接文本的函数，一个通过分隔符的，连接所有符合条件的单元格内容和区域的函数。如下图，语法结构是TEXTJOIN(分隔符, ignore_empty, text1, [text2], …)，翻译成中文就是=TEXTJOIN(分隔符,TRUE,需要合并的字符串区域或列表，需要合并的字符串区域或列表)。
第一参数分隔符是必备的，代表我们可以任意设置任何符号来连接数据，比如顿号、逗号、短横线等；第二参数也是必备的，TRUE代表忽略空值，也就是不存在的值会自动忽略；第三参数代表对应的数据区域。
7.4 加餐问题：数字取证运算
问题描述：如下图，运算规则：数字取正，小于10的取0，介于10-20的取10（包括10），介于20-30的取20（包括20），以此类推。
方法：用=ROUNDDOWN(A1,-1)，第二位“-1”代表取舍十位上的数字。
7.5 分列的适用场景有哪些呢？
若有收获，就点个赞吧
0 人点赞
