课前预习

【第2课】整理篇 - 课前预习 - 狮城小白.png

课堂笔记

表格整理和转换，以及汇总数据

1. 新课部分

1.1 数据整理的重要性

表格不规范体现在：（案例分析）
（1）简单规范 （2）增加统计纬度
（3）指定了输出方式 （4）数字格式不统一
（5）日期与星期混在一起 （6）缺失工时字段
（7）合并单元格导致日期缺失 （8）不规范的数据登记表
image.pngimage.png

1.2 数据整理心法

  • 数据规范的三个要点

    • 避免合并单元格
    • 数据要不可拆分（数据字段设计到最小单位）
    • 拒绝数据空行、空列

      1.3 表格设计原则

  • 三表原则：参数表、记录表、汇总表

  • Excel高效4步法：按数据分析的工作流程划分
    • 规范化记录
    • 批量化处理
    • 多维度分析
    • 视觉化输出
  • 数据整理目标：一维数据表

2. 实操训练

2.1 合并单元格

合并单元格的本质是只有左上角的单元格里有数据，其余单元格为空值。
方法1：取消单元格的合并后居中； >> Ctrl+G定位条件选空值；
>> 向下快速填充Ctrl+Enter； >> 复制公式，选择性粘贴，选数值（V），便于后续排序计算。
注意：若最后一步不做选择性粘贴处理，后续排序会导致运算错误。
方法2：方方格子 >> 【合并转换】 >> 取消合并相同单元格
image.png
方法3：Power Query
（1）选中数据区域，点击【数据】-“从表格”，窗口弹出勾选包含标题，确定后进入Power Query编辑器。
我目前是Microsoft Excel 2016版本的，所以进入Power Query编辑器的入口如下。
image.png image.png
（2）选中要拆分合并单元格的列，【转换】-【填充】-向下填充
image.png image.png
（3）点击【主页】-选择【关闭并上载】（或【关闭并上载至……】）
看是否存在合并单元格的方法：
方法1：看合并单元格的图标是否显示灰色；
方法2：用数据透视表，如果存在合并单元格就有空值，存在空值时就不能使用数据透视表；
方法3：定位-空值，原理同方法2；
方法4：遇到左右合并的单元格，可以保留第一列，直接删除其余列，数据仍存在。

2.2 二维表格转一维

方法1：ALT+D+P，依次按下
方法2：方方格子 >> 【合并转换】 >> 选择“行列转换”
image.png
image.png
方法3：Power Query
不同版本的Microsoft Excel开启PQ编辑器的不同入口：
image.png
（1）选择要保留的列 >> 【转换】-【逆透视列】-【逆透视其他列】
image.png
image.png
（2）重命名表头字段标题
（3）点击【主页】-选择【关闭并上载】（或【关闭并上载至……】）

2.3 Power Query进行单元格拆分

按字符数进行拆分：
（1）选择需要进行拆分的列 >> 【转换】-【拆分列】-选择“按字符数”
image.png
（2）输入“字符数”，选择“拆分”类型，再点击“确定”。（如有换行需求，可设置“高级选项”）
一次：表示拆分成两列
靠左：表示字段从左边开始数“字符数”
靠右：表示字段从右边开始数“字符数”
重复：表示能根据“字符数”拆分成多列
image.png
image.png
按分隔符进行拆分：
（1）选择需要进行拆分的列 >> 【转换】-【拆分列】-选择“按分隔符”
（2）选择或输入“分隔符”，选择“拆分位置”，再点击“确定”。（如有换行需求，可设置“高级选项”）
image.png
拆分后结果如下：
image.png

3. 本科学习内容

image.png

4. 本科作业与答疑

4.1 Power Query (PQ) 进行自定义筛选

先按分店排序，再在同一分店里对工号进行排序筛选。
方法1：常规筛选法
【开始】-【排序和筛选】-选择“自定义排序”
image.png
筛选后效果图：
image.png
方法2：PQ筛选法
在PQ输出的表格里按顺序筛选，筛选完以后PQ中会有筛选排序。
image.png

4.2 PQ输出表格的格式保持不变

使用PQ上载数据到表格里，输出的表格已经进行了美化（如字体、行高、列宽等格式），再次刷新时如何保证已美化的格式不发生改变？
（1）选中PQ输出的表格任意一个单元格，右键选择“表格”-“外部数据属性”
image.png
（2）取消勾选“调整列宽”即可
image.png

4.3 必修作业3：PQ处理多行表头

方法1：从工作簿开启PQ进行处理
（1）【数据】-【新建查询】（其他版本可能是【获取数据】）-【来自文件】-【从工作簿】
image.png
（2）选择要操作的表，点“转换数据”
image.png
（3）删除空行和不需要的列
（4）选中第一列，【转换】-【转置】
image.png
（5）【转换】-【填充】-选择“向下”（企业）
（6） 合并第一列和第二列
image.png
image.png
（7）选中第一列，再次进行转置，【转换】-【转置】（同第（4）步）
（8）【转换】-【将第一行用作标题】，最后关闭并上载（上载前必须手动修改数据类型）
image.png
方法2：以原数据表为基础，【数据】-【从表格】开启PQ
（1）原数据表格以不包含标题形式进入PQ，取消“表包含标题”的勾选image.png
完成后的原数据表效果：有新增的表头字段
image.png

4.4 合并单个工作簿中的多个子表格

（1）【数据】-【新建查询】（其他版本可能是【获取数据】）-【来自文件】-【从工作簿】
（2）在导航器中选择文件，点击“转换数据”，进入PQ
image.png
（3）选择源，将公式中的“null”换成“true（全字母小写）”
image.png
（4）保留列“Name”和“Data”，删除其他列
image.png
（5）在列“Name” 中筛选需要合并的表格，筛选包含“月”（表名筛选切勿太精确）
image.pngimage.png
（6）展开列“Data”的数据；展开后，手动修改数据类型；改完后，关闭并上载
image.png
（7）若后续有新增数据表需要一并更新，记得添加数据表以后先保存，再“刷新”数据

4.5 合并文件夹中的多个工作簿

（1）先新建一个Excel文件
（2）【数据】-【新建查询】（其他版本可能是【获取数据】）-【来自文件】-【从文件夹】
image.png
（3）选择“文件夹路径”，确定；然后，点击“转换数据“进入 PQ
image.png
image.png
（4）保留“Content”列，【删除列】-【删除其他列】（“name”列表示数据来源文件）
（5）【添加列】-【自定义列】，输入公式 Excel.Workbook([Content],true)，其中[Content]是通过双击右边“可用列”进行添加（一定要注意公式的大小写）
image.png image.png
（6）从“自定义”列展开数据
image.png image.png
（7）保留“Data”列，【删除列】-【删除其他列】（亦可保留“Name”列，知道数据来源的文件）
（8）展开“Data”列；展开后，手动修改数据类型；改完后，关闭并上载
image.png

4.6 文件、文件夹路径变更后，重新链接处理

问题：移动原数据文件或文件夹后，文件或文件夹的路径发生了变更，导致进入PQ后，发现源错误；
image.png
方法：（1）进入PQ后，点击源后面的齿轮符号；
image.png
（2）进入选择模式后，重新选择文件或文件夹路径，点击确定完成。
image.png

4.7 PQ的坑

（1）PQ在输出表格前，记得一定要手动修改所有表头的数据类型；鼠标左键点击即可根据需求修改类型；
image.png image.png
（2）进PQ导入数据时，记得删除自动生成的一个操作步骤“更改数据类型”，不然可能影响后续的完成效果；
（3）如遇无法开启PQ，窗口弹出错误提示时，可先手动将原数据表Ctrl+T变为智能表格，然后再开启PQ；原因多为原数据表进行了某些操作后无法在进入PQ时自动转为智能表格造成的。

4.8 PQ的注意小点

  • 在PQ里，不显示的导出时就等于删除，所见即所得；

image.png

  • 如何删除重复表头？将源公式中的“null”改为“true”

image.png

  • 一旦数据源有更新，进入PQ后，第一件事就是【主页】-【刷新预览】

image.png