第一章-数据规范-数据分析报表设计标准

薄荷杂学

已于 2022-05-05 22:04:49 修改

阅读量3k

点赞数

分类专栏： Excel数据分析与处理文章标签：数据分析

于 2022-05-05 01:13:44 首次发布

本文链接：https://blog.csdn.net/weixin_43825323/article/details/124580619

版权

Excel数据分析与处理专栏收录该内容

8 篇文章 9 订阅

订阅专栏

0 前言

本笔记是对“潭州课堂”的“Excel数据处理与分析（湖南大学通识课）”课程的整理，建议大家到平台上通过视频进行学习，效果更好（老师超级有趣，课程干货满满）。

1 Excel数据应用层工作流程

1.1 Excel工作流程

数据存储-数据处理-数据分析-数据的呈现
Excel工作流程

1.2 Excel相关名词解析

工作簿：通常所说的Excel文件
在这里插入图片描述

1.3 认识鼠标的三大状态

选择柄：用于选择单元格区域
移动柄：用于移动或配合ctrl键盘复制单元格区域
填充柄：复制、填充单元格区域，包括数据、公式等，双击有惊喜！

1.4 认识单元格

单元格储存容量：不区分中英文32767字符
在这里插入图片描述

单元格存储数据的几种特殊情况	举例	原因说明	解决方法
超过15位的数字不能正常显示	比如超过15位数字的身份证默认是科学计数法	因为Excel最大计算精度是15位	第一种是将格式设置成文本；第二种是在输入前先输入’号开头

本节总结

1、Excel的基本工作流程是:数据存储-数据整理-数据分析-数据呈现
2、一个工作簿默认最多能建255个工作表，内存大的可以更多
3、一个工作表有1048576行、16384列
4、一个工作表约172亿个单光格，一个单元格能存放32767个字符
5、鼠标三大状态:选择柄、移动柄、填充柄。双击填充柄快速填充需要临近列有数据。

2 规范制表之经典的三表结构

2.1 为什么要用Excel?

1-工作中使用Excel的目的，是为了得到各式各样用于决策的分析报表。而分析报表需要一个规范的源数据表提供数据。有了规范的源数据表，我们就可以制作出各式各样的决策报表。一句话总结就是设计一个标准的源数据表，制作出N个分析汇总表!
2-其实很多时候，就是因为制表不规范、录入数据格式不统一，对数据汇总分析造成极大的困扰，需要花大量的时间去整理数据，不仅效率慢，而且还存在数据不准确现象。

2.2 三表结构=1个源数据表+1个参数表+N个汇总表

源数据表

源数据表: 也叫数据明细表，用于存放录入或导入的表格数据。
【注意】只要源数据表里面的数据规范，我们可以不用学太多的复杂函数和复杂技巧来弥补制表不规范的不足，成倍的提升工作效率!

参数表

参数表:对源数据表的字段录入说明，为汇总表提供分析的维度。
在这里插入图片描述

汇总表

汇总表: Excel最终的分析报表，可以是数据汇总表、透视表、图表。一份源数据表可以制作出N张汇总表。
在这里插入图片描述

本节小结

在这里插入图片描述

3 源数据表常见错误暨规范化处理方法

3.1 正确表格

在这里插入图片描述

3.2 错误1-合并单元格

在这里插入图片描述

3.3 错误2-没有序号

在这里插入图片描述

3.4 错误3-乱加表头

在这里插入图片描述

3.5 错误4-胡乱合计

【注意】源数据表最好不要有合计，会影响后续的数据透视表操作。
在这里插入图片描述

3.6 错误5-二维表格数据结构

在这里插入图片描述

3.7 错误6-数据有缺少

在这里插入图片描述
【批量填充】选中数据列——选择”查找和选中“的”定位条件“——点击“空值”，然后“确定”——在单元格输入数值——ctrl键+enter键

3.8 错误7-分裂表格

在这里插入图片描述

3.9 错误8-数字文本化

在这里插入图片描述

3.10 错误9-单元格非原子化

在这里插入图片描述
【数据分列】选中数据列——选择“数据”中的“分列”——可以根据情况选择“分隔符号”或“固定宽度”

3.11 错误10-内容不统一

在这里插入图片描述

本节小结

【数据分列】选中数据列——选择“数据”中的“分列”——可以根据情况选择“分隔符号”或“固定宽度”
【批量填充】选中数据列——选择”查找和选中“的”定位条件“——点击“空值”，然后“确定”——在单元格输入数值——ctrl键+enter键

4 跨工作表计算技术

本节练习如下：
在这里插入图片描述
【快速切换工作表】

【快速求和】ctrl+a全选数据；alt+=快速求和的快捷键
【批量删除工作表】shift连续选中；ctrl不连续的选择

5 数据高级操作技巧

【自动调整行高和列宽】选择行或者列，或者是全选——把光标放到行之间或者列之间，双击

【快速选中行和列】ctrl+shift+方向键（向下|向右|向左|向上）——【多选了咋办】shift+向上键

【精确选中】通过名称框的方式
在这里插入图片描述

【行列数据位置调整】
1）将D列放到B列的左边：点中D1单元格，ctrl+shift+方向键向下——将鼠标放到边框位置，变成移动柄的状态——按住shift键进行移动
2）将A、B、C列都进行移动：按住ctrl键，选中A1,B1,C1，ctrl+shift+方向键向下——将鼠标放到边框位置，变成移动柄的状态——按住shift键进行移动

【空单元格定位填充】选中数据列——选择”查找和选中“的”定位条件“——点击“空值”，然后“确定”——在单元格输入数值——ctrl键+enter键

6 数据序列排序技巧

6.1 基础排序和多关键字排序

在这里插入图片描述
【基础降序】点击单元格——“数据”——“排序与筛选”——根据需要选择升序或者降序
【多关键字排序】“数据”——“排序与筛选”——“筛选”
【注意】如果整行排序，则不需要选择整列数据，只需要鼠标点中该列数据任意单元格即可
在这里插入图片描述

6.2 按颜色排序

在这里插入图片描述
【多关键字排序】“数据”——“排序与筛选”——“筛选”

6.3 自定义序列排序

在这里插入图片描述
操作1：在"excel选项"中，选择"高级"选项卡下的"编辑自定义列表"

操作2：在"从单元格中导入序列"中选入刚才填的序列即可。

操作3：“数据”——“排序与筛选”——“筛选”——“次序"选择"自定义序列”，然后选择刚才设置的序列就行。
在这里插入图片描述

6.4 利用排序巧妙制作工资条

原始表：
在这里插入图片描述
效果图：每个人都有标题

操作1：增加一列，具体如下——然后进行"升序"操作

操作2：“ctrl+shift+键盘向右"选中第一行，“复制”——类似操作，选中第一列——“开始”、“查找与删除”、“定位条件”——选择"空值"后"确定”——"ctrl+v"粘贴即可！可以对表格美观度进行完善！
在这里插入图片描述

7 数据查找替换技巧

7.1 常规查找替换

在这里插入图片描述

要求1：查找到凭证号为"记-0029"的单元格数据。

操作：在选项卡找到"查找和选择"——在下拉菜单找到"查找"——在弹出的窗口"查找和替换"中的"查找内容"输入目标数据——点击"查找全部"即可。（显示的窗口如果和下图不一样，可以点击"选项"按钮）
在这里插入图片描述

要求2：替换"一车间"为"一部门"

操作：在选项卡找到"查找和选择"——在下拉菜单找到"替换"——在弹出的"查找和替换"窗口中输入目标数据——点击"全部替换"即可。
在这里插入图片描述

要求3：将深圳替换成"深圳市"

操作：在选项卡找到"查找和选择"——在下拉菜单找到"替换"——在弹出的"查找和替换"窗口中输入目标数据——勾选"单元格匹配"——点击"全部替换"即可。
在这里插入图片描述

7.2 通配符查重与替换

在这里插入图片描述

要求1：将姓张的员工都改为"优秀员工"。

操作：查找内容"张*“，替换为"优秀员工”。
在这里插入图片描述

要求2：将姓张且名字为两个字的员工都改为"优秀员工"。

操作：查找内容"张?“，替换为"优秀员工”。
在这里插入图片描述

本节小结

1-选择查找替换时，需要选定查找区域，不选定单元格区域，默认查找的范围是整个工作表。
2-在Excel里面有三个符号比较特殊，波浪号~，星号*，问号?这三个符号要记牢。
*表示任意多个字符
?表示任意单个字符
_*、?、~~表示符号本身
【注意】这些符号必须在英文状态输入时才有效！
3-"查找和替换"窗口的"单元格匹配"有什么用？选中单元格匹配后,你查找的单元格内容必须和查找内容一致.如果不选中该复选,则在单元格中包含查找内容就可以。

8 数据筛选技巧

8.1 常规筛选

在这里插入图片描述

要求1：筛选一车间的数据

操作：点击单元格——在"数据"选项卡的"排序和筛选"中找到"筛选"按钮，点击后可以看到表格变化——在下拉菜单中选择目标数据即可。
在这里插入图片描述

要求2：筛选发生额大于500且小于1000的数据

操作：点击单元格——在"数据"选项卡的"排序和筛选"中找到"筛选"按钮，点击后可以看到表格变化——在下拉菜单的"数字筛选"中，选择"介于"——在弹出的"自定义自动筛选方式"输入要求即可。
在这里插入图片描述

要求3：筛选一车间的邮寄费

操作：与要求1基本类似，就是筛选后再筛选。

要求4：筛选所有车间的数据

操作：点击单元格——在"数据"选项卡的"排序和筛选"中找到"筛选"按钮，点击后可以看到表格变化——在下拉菜单的"文本筛选"中，选择"包含"——在弹出的"自定义自动筛选方式"输入要求即可。
在这里插入图片描述

8.2 高级筛选

要求1：筛选出”科目划分"字段中的不重复值,在原有区域显示筛选结果

操作：点击单元格——在"数据"选项卡的"排序和筛选"中找到"高级"按钮——
弹出"高级筛选"窗口，“列表区域"选择科目划分列，勾选"选择不重复的记录”。
在这里插入图片描述

要求2：筛选出财务部或发生额大于3000的数据

步骤1：设置高级筛选条件区域
在这里插入图片描述
步骤2：点击单元格——在"数据"选项卡的"排序和筛选"中找到"高级"按钮——
弹出"高级筛选"窗口，"列表区域"默认是所有数据区域，"筛选条件"选中步骤1设置的条件区域。

要求3：筛选出经理室或二车间中发生额大于3000或01月中发生额大于10000的数据记录

步骤1：首选设置高级筛选条件区域
在这里插入图片描述

步骤2：点击单元格——在"数据"选项卡的"排序和筛选"中找到"高级"按钮——
弹出"高级筛选"窗口，"列表区域"默认是所有数据区域，"筛选条件"选中步骤1设置的条件区域。

本节小结

1-筛选非重复的数据，在高级筛选的时候，区域只能选择要去重的一列数据2-普通筛选有条件限制，而高级筛选则无条件限制
3-构建高级筛选条件区域时，标题必须要和源数据区的标题字段保持一致，否则筛选无结果
同一行表示且的关系；不同行表示或的关系。
条件所在行之间不能有空行！

9 自定义数字格式

9.1 基本含义

在这里插入图片描述

9.2 自定义格式代码结构

在这里插入图片描述
举例1：正常显示文本——“类型(T):“设置为”;;;G/通用格式”
举例2：隐藏0——"类型(T):“设置为"G/通用格式;G/通用格式;;G/通用格式”

9.3 颜色代码

在这里插入图片描述

9.4 实例练习

条件代码的应用

在这里插入图片描述
设置为：“[红色][>60]G/通用格式;[蓝色]G/通用格式;;”

隐藏正数、负数、零值或文本

在这里插入图片描述
隐藏正数：;G/通用格式;G/通用格式;G/通用格式
隐藏负数：G/通用格式;;G/通用格式;G/通用格式
隐藏零值：G/通用格式;G/通用格式;;G/通用格式
隐藏文本：G/通用格式;G/通用格式;G/通用格式;

显示无意义的0

在这里插入图片描述
设置为：00000

数字占位符#的使用

在这里插入图片描述

?的使用

设置为：?.??
在这里插入图片描述

9.5 案例讲解【重要】

案例1

在这里插入图片描述

案例2

在这里插入图片描述

案例3

在这里插入图片描述

案例4

在这里插入图片描述

本节小结

1-自定义数字格式允许用户创建符合一定规则的数字格式，不会改变数值本身，只改变数值的显示方式心
2-自定义的格式代码具有以下结构:正数;负数;零值;文本。
3-#和0同样是占位符，#不显示无意义的0，0可以显示。

10 常用工作技巧

10.1 公式转换为数值

选定数据，复制，然后仅粘贴值

10.2 填充数据不改变格式

在这里插入图片描述

10.3 向上填充数据

在这里插入图片描述
步骤1：“定位条件"选择"空值”

步骤2：单元格引用B4——ctrl+enter

10.4 金额万元显示

设置单元格格式——0!.0,“万元”

10.5 激活文本型数据

方法1：
在这里插入图片描述
方法2：在其他单元格输入1，复制它，选择性粘贴

在这里插入图片描述

10.6 表格行列转置

选中表格——选择性粘贴——选中"转置"
在这里插入图片描述

10.7 选择性粘贴中跳过空单元格的应用

在这里插入图片描述

10.8 批量删除空行

在这里插入图片描述

10.9 定位公式

在这里插入图片描述

10.10 单元格内容合并

在这里插入图片描述

10.11 数据分列

在"数据"选项卡中点击"分列"按钮
在这里插入图片描述

薄荷杂学

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
第一章-数据规范-数据分析报表设计标准

0 前言本笔记是对“潭州课堂”的“Excel数据处理与分析（湖南大学通识课）”课程的整理，建议大家到平台上通过视频进行学习，效果更好（老师超级有趣，课程干货满满）。1 Excel数据应用层工作流程1.1 Excel工作流程数据存储-数据处理-数据分析-数据的呈现1.2 Excel相关名词解析工作簿：通常所说的Excel文件1.3 认识鼠标的三大状态选择柄：用于选择单元格区域移动柄：用于移动或配合ctrl键盘复制单元格区域填充柄：复制、填充单元格区域，包括数据、公式等，双击有惊喜！1
复制链接

扫一扫

专栏目录