sqlseverexcle数据都到了一个单元格_王佩丰老师数据透视表学习笔记

了解数据透视表在数据分析中的角色&＃xff0c;掌握如何调整透视表设置达到预想的效果&＃xff0c;掌握如何更好的展示数据&＃xff0c;外部数据及跨表/跨文件数据的汇总&＃xff0c;将数据学习系列工具应用到工作中。

1.数据透视表基础应用

1.1认识数据透视表

①规范的数据记录&＃xff08;适不适合数据透视表&＃xff09;&＃xff0c;点击任一单元格&＃xff0c;插入 数据透视表

②先看问题&＃xff0c;再去调整样式。

③xlsx是新表样式 需设置传统样式&＃xff08;右键 数据透视表选项 显示 经典数据&＃xff09; 。

④右键 字段列表显示/隐藏。右上角字段列表显示设置。

1.2数据透视表选项调整

①字段列表排序显示&＃xff08;右键 数据透视表选项 显示 升序/数据源显示&＃xff09;

②刷新时保留单元格格式 &＃xff08;右键 数据透视表选项 布局和格式 更新时保留单元格格式&＃xff09;

③双击数据可以查看数据明细&＃xff1f;&＃xff08;右键 数据透视表选项 数据 启用显示明细数据&＃xff09;&＃xff08;发文件时会泄密&＃xff1f; 复制右下角 黏贴 黏贴为数值 &＃xff09;

④下拉框中不显示字段已经删除的项目 &＃xff08;右键 数据透视表选项 数据 保留从数据源删除的项目 无&＃xff09;

⑤删除行列总计&＃xff08;右键 数据透视表选项 汇总和筛选 行列总计&＃xff09;

1.3修改透视表布局

①多个行、列、页字段布局

页字段可以多选&＃xff1b;修改筛选页字段布局&＃xff08;右键 数据透视表选项 布局和格式 报表筛选区显示字段格式 水平或垂直&＃xff09;&＃xff1b;显示一个筛选条件下所有的明细表&＃xff0c;如月份显示1-12月的每张表&＃xff1f;&＃xff1f;&＃xff08;分析 选项 显示报表筛选页&＃xff09;&＃xff1b;可以做出1-12月命名的表名&＃xff08;shfit所有表&＃xff0c;选择右方内容删除整行&＃xff09;

②删除字段的分类汇总&＃xff08;分类字段 右键 不显示汇总/双击 汇总和筛选 无 &＃xff09;

③多个数据字段布局 &＃xff08;移到列字段&＃xff09;修改值字段设置-值汇总方式/值显示方式&＃xff08;统计方法和显示方法&＃xff09; 修改列名&＃xff08;直接修改 回车&＃xff09;&＃xff1b;统计多字段&＃xff0c;统计金额、数量、成本

④布局按钮 设计 报表布局&＃xff08;表格形式 重复标签&＃xff09;

2.字段组合

2.1字段自动组合

①日期 选择某一个日期&＃xff0c;右键&＃xff0c;组合可被直接组合为季度&＃xff1b;季度1和第一季可能是语言习惯的设置问题。

②周 数据为1个月的&＃xff1a;组合 起始日期&＃xff08;注意起始日期为周一&＃xff09;、步长7 命名为第一周&＃xff1b;取消组合回到原始表

③数值 组合起始于、终止于、步长

④文本 多选类别 右键 组合

2.2函数创建辅助列组合

①上中下旬 &＃61;IF(DAY(A2)<11,"上旬",IF(DAY(A2)<21,"中旬","下旬"))

②特殊月份&＃xff08;从一个月的5号开始&＃xff09;&＃61;A2-4

③不等步长数值组合 &＃61;IF(C2<&＃61;60,"0-60",IF(C2<81,"60-80",IF(C2<91,"80-90","90-100"))) 剥洋葱

2.3常见组合错误以及排查

①合并单元格引起的错误 有空白的日期不能分组&＃xff1a;选中a列&＃xff0c;取消合并&＃xff0c;定位条件&＃xff0c;空白值&＃xff0c;&＃61;A7&＃xff0c;ctrl&＃43;enter&＃xff0c;复制右键黏贴为复制

②文本型日期如何变真正的日期格式?分列 改为数值型数据 刷新两次

③文本型数值不能求和&＃xff1f;选择 感叹号 转换为数字&＃xff08;文本与其他类型不能通用&＃xff09;

3.定义公式

3.1使用值显示样式完成计算

①更改数据项的值显示样式&＃xff1a;占总计的百分比、父行汇总的百分比

② 计算差异、计算差异百分比&＃xff08;差异百分比 年 2009年&＃xff09;

3.2在透视表中自定义公式

①插入计算字段&＃xff08;列于列的计算&＃xff09;&＃xff1a;分析、字段项目集、计算字段 利润&＃61;金额-成本 利润率&＃61;利润/成本&＃xff08;可以修改公式&＃xff09;、设置单元格格式百分比样式

②计算中的除0错误处理显示&＃xff1a;右键 数据透视表选项 布局和格式 错误值显示为空

③插入计算项&＃xff08;同一列运算&＃xff09;&＃xff1a; 选中右上角分类字段、计算项

④在计算字段中使用函数&＃xff1a;&＃61;if(利润<1000000,利润*0.1,if(利润<2000000,利润*0.15,利润*0.2))

⑤查看公式以及公式顺序对计算结果的影响:分析 列出公式、求解顺序&＃xff08;&＃43;2000的问题&＃xff09;

4.排序、筛选、切片器