Excel之数据分析

最新推荐文章于 2024-08-13 13:48:31 发布

M大王派我来巡山

最新推荐文章于 2024-08-13 13:48:31 发布

阅读量945

点赞数 26

文章标签： excel 笔记

本文链接：https://blog.csdn.net/m0_47531010/article/details/135049401

版权

数据格式设置的重要性

在Excel中输入身份证号或者手机号时，莫名变成科学计数法的形式，或者数字前面的0消失不见或后面的数字被0代替。

数据分类

如何查看Excel中有多少种数据。如下图所示
在这里插入图片描述

常规：自动识别的功能；剩下的都是常见的数值格式。

数字：
文本：比如身份证号，手机号
在单元格中输入身份证号的时候，切换到英文输入法，在身份证号的最前面加一个逗号，这样输入的身份证号就不会变成科学计数法的形式展示。
在这里插入图片描述
也可以在开始选项卡中进行设置

逻辑值：就是在进行运算时的一个结果。比如在单元格中输入“=B1<C1”得出的结果是true，因为B1单元格中的数字是12，C1是250.反之则结果为false。

错误值：在输入公式，比如把sum输成sun的时候，得到的结果就是错误值。

日期与时间格式的转换

将2023/12/17转换成2023年12月17日这种格式

快捷键：Ctrl+1
复制要的那列，在空白处Ctrl+V粘贴，在选中状态下（就是单元格处在绿色框中，如图所示），在开始选项卡中选择其他数字格式，进入到日期中，进行设置。
也可以直接用快捷键ctrl+1，调出图二的选项框。
在这里插入图片描述

将对应日期转换成星期几

如果要计算当前日期对应的是星期几，操作步骤和上面一样。进入到日期这个选项中，选择星期几这个选项，具体如下图所示：
在这里插入图片描述

计算两个时间之间的时长

直接在对应的单元格中输入“=C2-B2”按下回车键，即可得出结果。
在这里插入图片描述
选中单元格，鼠标移到该单元格的右下角，变成黑色实心十字的时候，双击，这一列的后续单元格自动完成填充。

在进行计算之前要先设置一下这个单元格的格式，是日期还是时间

详细的可以Ctrl+1，时间选项卡中进行详细设置

如果要把当前时间变成**分钟的格式。ctrl+1-》自定义-》找到[m]“分”“钟”-》点击确定完成设置
在这里插入图片描述

消失的0如何找回（比如输入工号000001的时候前面的0会消失）

像工号或者编号这种，都是有固定位数的，长度都是固定数。
直接ctrl+1-》自定义-》输入一个相当于占位符的东西（这里的工号前面是0，所以输入5个0，表示工号的长度是5，这样工号前面的0就会被保留）-》点击确定完成设置
在这里插入图片描述

不建议在单元格的数字后面加单位的符号，加上之后该单元格就变成了文本格式，不利于后续的计算。可以在最上方的文字那一栏加

在这里插入图片描述

如何批量添加数字格式单位

ctrl+1-》自定义-》G/通用格式"单位"-》点击确定完成设置
在这里插入图片描述

表格数据直观展示

选中要展示的列-》插入选项卡-》插入柱状图
结果见图二所示
在这里插入图片描述

选中销售人员和销售额这两栏-》插入选项卡-》插入柱状图-》展示的柱状图中就能看到销售人员对应的销售额，更加直观。

如何只在表格本身去操作，不借助信息图表
选中要突出显示的数据-》开始选项卡-》条件格式-》突出显示单元格规则-》选择需要的
在这里插入图片描述
直接在单元格中加入柱形图
选中数据-》开始选项卡-》条件格式-》数据条-》选择自己需要的

效果图

为了更加方便观看，可以给所选的单元列新建一个排序。
色阶：也是一种展示数据的形式。

像同比增长率，可以用上升或者下降的箭头，这样更加直观。要用到的是条件格式里面的图标集。
在这里插入图片描述
在使用之前需要自行设置一下规则，具体如下图所示：

在这里插入图片描述
设置后的效果：

公司系统导出数据分列

在这里插入图片描述
如图是一个公司导出的数据，列与列之间只用空格隔开，如果要把他变成一个完整的表格，可以把原来的空格替换成表格线
选中表格-》进入到数据选项卡-》分列

设置完的结果如下所示：

改下文字的颜色，就可以看见了。
在这里插入图片描述

出现#号是因为单元格太小，装不下。可以自己手动调整，或者在开始选项卡-》行和列-》最适合的行高-》最适合的列宽
在这里插入图片描述

如何把连在一起的日期分开

把下图A列中的日期变成右边的这种样式
在这里插入图片描述
首先选中单元格-》数据选项卡-》分列-》固定宽度-》自己设置下分割线-》日期-》

设置完毕后的效果，最上方的年月日可以自己打上

也可以用分列选项卡中的智能分列，这种情况用智能分列会更加快捷方便。
在这里插入图片描述

当表格中的分隔符都不一样的时候如何处理

表格中连在一起的部分可以分为三列，
PS：在做表格的时候，一定要注意表格中的数据格式。数字超过11位，自动变成科学计数法。在分列的时候要注意下数据的长度
看下数据中有没有要注意的地方，那一列需要专门设置文本格式，尤其是数字格式文本-》观察表格中的内容可以分为几列-》在当前列的后面添加对应的列数，防止后面的列被吞掉
具体步骤如下图所示：
在这里插入图片描述

有的就勾选，表格中有冒号，但是选项卡中没有，就勾选其他这个选项，在英文输入法的状态下自己输入

对于数字，要专门设置下文本格式。直接点击那一列，然后更改数据类型即可。

对于显示不全的内容，调整下行高和列宽。或者自己手动调整
在这里插入图片描述

下拉复选框怎么做

选中-》数据选项卡-》插入下拉列表-》输入选项
在这里插入图片描述
对于部门这个，可以用有效性，具体操作如下：

输入的是下拉列表的形式，所以第一个选择的是序列-》在来源中输入各个部门的名称，供后面选择

身份证号码这列，可以限制编辑长度。
选中目标列-》数据选项卡-》有效性-》允许（文本长度）-》数据（等于）-》来源（18）
在这里插入图片描述
在输入信息中可以输入个提示，当鼠标放到单元格上的时候就会出现

出错警告是当输入的东西，不符合规则之后，弹出的提示。

在Excel中输入日期一般都是两种（2023-12-16或者2023/12/16），所以也可以在有效性中对日期进行下设置。具体操作如下:
在这里插入图片描述

简单排序的实际应用

在这里插入图片描述

对总分进行排序

简单的多重排序，语文成绩和数学成绩好的排在前面

工资条表头批量添加

在这里插入图片描述
新建辅助列，先输入1-10，再输入一个1.1-10.1，再对这些进行升序，这样，每一行下面都能得到一个空行。

空出来的每一行，可以用来添加表头。
将要进行定位的区域选中-》Ctrl+G，将选中区域中空的地方都进行定位-》ctrl+V，将复制的表头进行粘贴
在这里插入图片描述
操作后的结果如下所示：

筛选条件看数据

要筛选出语文成绩前三的
选中单元格-》开始选项卡-》筛选-》目标单元格的右下角会出现一个下拉小三角，如图二所示：
在这里插入图片描述

点击下拉小三角-》前十项-》输入筛选的条件

结果如图所示，分数相同的会默认放在一起展示

筛选语文成绩大于80，数学成绩大于90的
点击下拉小三角-》数字筛选-》选择要筛选的范围（大于多少或者等于多少）
在这里插入图片描述

多项筛选

要在左边的表格中筛选出符合右边小表格种的项
在这里插入图片描述
开始选项卡-》筛选-》高级筛选-》选中条件区域和列表区域-》点击确定

按照右下第二个表格中的条件筛选出来的结果：

PS：列表区域和条件区域中单元格里面的名称要一致，比如列表区域里面用的是货品名称，条件区域中也要写货品名称。这样便于筛选。

大量重复数据如何批量删除

选中目标列-》开始-》条件格式-》突出显示单元格-》重复值-》设置颜色
在这里插入图片描述
重复的部分会被标记出来，但是无法被删除

选中目标列-》数据选项卡-》高亮重复项-》设置高亮重复项-》扩展选区-》
在这里插入图片描述
重复项会被突出标记

选择扩展选定区域。因为当前只选中一列，但是其他连带的信息也会重复，所以要一并删除

全选

最后结果如下所示:

在根本上解决问题，即在输入时，如果输入重复项就提示
选中目标列-》数据选项卡-》重复项-》拒绝录入重复项-》选择拒绝重复录入的区域-》点击确定
在这里插入图片描述

输入重复项的话，会有提示

分类汇总数据

按项目分类汇总这个表，知道每个项目的总金额是多少。
首先先对项目名称这列进行一下排序，把相同项目的放在一起-》选中整个表格-》数据选项卡-》分类汇总-》选择分类字段，汇总方式，汇总项等-》点击确定
在这里插入图片描述
排序后的结果：

这里选定汇总项中不选择项目名称是因为，在一开始的分类字段中就选择按项目名称来汇总了，这里不需要在勾选，只勾选金额就可以。因为是要知道每个项目总的花费金额。

下图就是汇总结果。观察下图会发现最左侧有个123，图中显示的结果就是点击最左侧的数字3的结果
在这里插入图片描述
点击2出现：

点击1出现的是所有项目的总金额：

按照负责人来汇总也是一样的操作
先对负责人这一列单元格进行排序，把相同的放在一起，便于Excel去汇总-》选中整个表格-》数据选项卡-》分类汇总-》设置下具体的细节-》点击确定

开始选项卡中有排序，数据选项卡中也有排序
在这里插入图片描述

表格合并计算

汇总的时候，标题一定要保持一致
数据选项卡-》合并计算-》选择需要的函数（这里选择的是求和）-》引用位置（框选需要计算的表格的范围）-》选完之后点击下面的添加，然后在框选第二个要计算的表格-》选定引用位置之后，勾选最下方的首行和最左列，表示在汇总的时候带上这个行和列
在这里插入图片描述

销售人员这个可以自己自行补上，问题不大。