数据管理函数

最新推荐文章于 2024-09-15 22:12:08 发布

M大王派我来巡山

最新推荐文章于 2024-09-15 22:12:08 发布

阅读量768

点赞数 17

文章标签： excel

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

版权

本文详细介绍了如何在Excel中使用数据透视表进行数据分析，包括冻结表头、字段布局、筛选器的应用、格式规范以及各种基础运算（如加单位、求和、计数、日期函数等）。同时讲解了VLOOKUP、MATCH和INDEX函数在数据查找中的作用。

摘要由CSDN通过智能技术生成

数据透视表

如何固定表头
视图选项卡-》冻结窗格-》冻结首行
在这里插入图片描述

建立数据透视表的话，一般都是新建一个表，如果在原来的表当中操作，容易把原本的数据覆盖。
在这里插入图片描述

字段列表：默认是表头数据
在这里插入图片描述
选中任意一个数据，双击之后会新建另外一个表，具体如下图所示

表中对应的是白子画在哥哥国家和地区的销售明细

数据透视表的四大功能区域

行和列：不同数据放的位置不一样，得到的表也是不一样的，如图一，是把国家和地区放在列的位置，销售人员放在行的位置
在这里插入图片描述
在行或者列当中，哪一个字段在前面，表格中也是一样，具体如下图一，二所示

如果要计算每个销售人员总共去了某个国家多少次，可以将字段中的订单ID加入到数据透视表区域中的“值”这个位置，在值字段设置中进行相关的设置
在这里插入图片描述

筛选器是在表的外边进行筛选，详细见下图，我们设置了订购日期，可以看到订购日期不在之前这个表里面，而是在他外部出现，当点击下拉小三角的时候，可以选则要看的对应日期，点击确定下方的表会呈现相应数据。这就是筛选器的功能。
在这里插入图片描述

在这里插入图片描述
字段放的位置不一样，我们得到的表也不一样。可以根据自己想要的表去放置各个字段的位置。

数据透视表的规范格式

①在Excel中，不要合并单元格，这样在后期计算的时候会出错。比如在插入数据透视表的时候会多出一个空白，因为合并单元格之后，Excel会把当中空白的部分也当做一个字段
②在输入名字的时候，不要中间加空格什么的，就正常输入即可
③关于日期，要么写成2023-12-29的格式，要么是2023/12/29这种格式，其他格式Excel识别不出来
在这里插入图片描述

④在单元格里面，不要输入这种斜杠。不利于后面计算。表里面也不用合计这列了，因为数据分析自己有个自动求和的功能。把求和这一列放在表格里面会让Excel误以为这也是要计算的一部分。
在这里插入图片描述
⑤输入数据的时候，格式统一。对于是空值的，可以全部都用0表示

⑥也是合并单元格的问题。可以在姓名后面新增班级这一列。

⑦单元格中，这些合在一起的数据，要给他分列

利用数据透视表分析数据

如下一张表，分析哪个品牌明年值得继续合作
在这里插入图片描述
插入选项卡-》数据透视表-》选中表格数据范围

在新建的数据透视表中，我们可以在最右侧的字段列表中选择想要的。以下图为例，选择了品牌名和订单金额，所以在左侧的表格中，可以看到每个品牌对应的总的订单金额
在这里插入图片描述
为了能更加直观的看到每个品牌的订单金额，可以给他加一个数据条，具体操作如下图所示

为了更直观的分析，可以加上每个品牌整年的订单数量。
在字段列表中选择订单ID后出现下图所示的结果

但我们想知道的是订单的数，而不是订单ID，所以将订单ID移到值这里面来，具体如下图所示
在这里插入图片描述
进入值字段设置中，将值字段的汇总方式改为计数

得到如下结果，为了直观的观看，可以加上数据条

根据日期筛选每个季度销售额的情况

字段列表中，勾选日期之后，调整下行列，会发现表中有些地方是空着的，为了更好的分析，需要将相同月份的整合到一起。
在这里插入图片描述
在原先的表格中新增一列月份-》在英文输入法的状态下输入函数MONTH，点击回车后就能自动提取日期中的月份-》将鼠标放在单元格右下角，出现黑色实心十字架的时候，双击，后面的内容会自动进行填充。
在这里插入图片描述

此时，重新创建一个数据透视表，选择品牌名，月份，订单金额，就能看到不同品牌在每个月份的销售金额

常用的基础运算

利用函数加单位
选中要加单位的那个单元格-》加上&-》加上双引号，在英文状态下输入的双引号-》把要加的单位名写在双引号里面-》点击回车，完成设置
在这里插入图片描述
将鼠标放在单元格右下角，出现黑色实心十字架的时候，双击，后面的内容会自动进行填充。
这个涉及到的是相对引用

绝对引用
例如现在总的工资=基本工资加上补贴
选中1800这个单元格-》在左上角名称框中，将这个单元格的名字改为补贴-》求和的时候直接输入补贴，右下角会出现一个小标签，点击一下这个，点击回车，完成计算-》将鼠标放在单元格右下角，出现黑色实心十字架的时候，双击，后面的内容会自动进行填充。
在这里插入图片描述

在公司选项卡-》名称管理器。在这里面也可以对名称框进行管理。
但是最简单的就是直接在左上角输入，结果都是一样的。

混合引用
求工资占比的话，就是把每个人的工资除以基本工资的和。在引用前要把基本工资的和固定下来，按F4或者FN+F4。也可以在英文状态下，在前面加上这个“$”符号
在这里插入图片描述

相对引用：引用的时候，每个地方都会发生变化
绝对引用：引用的时候，有一个地方是固定的，有一个是不断变化的
混合引用：结合了相对引用和绝对引用

平均值函数

在这里插入图片描述
点击回车之后，出现的值是1900，这是因为没有对该单元格的文本格式进行设置

开始选项卡-》将文本格式设置为数字，默认保留两位小数

设置完后的年龄平均值就如下图所示

日期函数

提取年份用YEAR()，提取月份用MONTH()，记得设置下文本格式
在这里插入图片描述

文本函数

提取工号中的数字，即000033
用的是RIGHT()函数，选择要提取的字符串，还有提取的数字个数，因为我们要提取的是后面的数字，有6位，所以函数中第二个参数写的是6.也可以用LEFT()函数。
在这里插入图片描述

if函数

如下图，公司有一个活动，年龄在25岁以下的员工可以参加。符合条件的就通过，不符合的就淘汰。把结果写在双引号里面，注意是要在英文输入法状态的双引号。
在这里插入图片描述

计数函数

如下图，要求每个月有多少员工入职
用到的函数是COUNTA
在这里插入图片描述
输入之后，选中要计算的范围，按下回车键即可完成计算

往右拖拽，可自动填充右边的内容

如果要计算1月到6月，入职的员工数，也是使用COUNTA函数，此时选中的范围变成了整个表格

如果要计算表格中空白单元格的个数，用到的函数是COUNTBLANK
在这里插入图片描述

取整函数

例如要求平均每月入职的员工人数，如果直接用AVERAGE函数的话，会有小数点，所以需要再加一个取整的函数，如图三所示。
在这里插入图片描述

在这里插入图片描述

取小数点ROUND

在这里插入图片描述

除负号函数ABS()

在这里插入图片描述

SUMIF函数

计算在北京的月销售额
在这里插入图片描述
可以通过最下方，进行验证，看计算得出的结果是否与其一致

AVERAGEIF函数

求汤圆的月平均销售情况
在这里插入图片描述

COUNTIF函数

求月销量超过25的地区一共有多少
在这里插入图片描述

多表格求和

新建一个表格，然后使用sum函数，将李逍遥的每月的瓷砖销售额选中。跳选的话，sum函数中每个参数之间用逗号隔开，如果是连选，参数之间用的是冒号隔开。
在这里插入图片描述
求得一个结果之后，剩下的可以用自动填充功能。

旁边一列也是同样的计算方式，直接将向右拖拽即可完成自动填充，得出要求的结果。

networkdays函数

求持续天数=完工日期-开工日期
在这里插入图片描述
求除去周末剩下的工作时间：

求除去周末和节假日剩下的工作时间：

正常把259加上6天的节假日，得到的工作时间是265，但是这里是264，仔细观察会发现，最后一个节假日的时间在完工日期之后，所以虽然运算的时候加上了，但也是形同虚设，不影响。
在这里插入图片描述

networkdays.intl函数

对于那些休息时间不确定的，要计算其工作日可以用函数networkdays.intl
在这里插入图片描述

对于休息时间可以按下图中的数字来代表休息的时间

workday函数

例如项目提案日期=开工日期+3
在这里插入图片描述

datedif函数

要计算员工的年龄，用的是datedif函数，第一个参数是员工的出身日期，第二个参数是现在的日期，输入的是today()这个函数，第三个参数，如果要计算多少岁，输入的就是year，这边直接在双引号中输入Y即可，不加双引号的话会出错。
在这里插入图片描述

or函数

下图中的表统计了各个季度，每个销售人员的销售额情况，现在要筛选出每个季度销售额超过2万的人员名单
在这里插入图片描述

or函数中，筛选的逻辑值中只要有一个是true，他返回的结果就是true

and函数

在这里插入图片描述

and函数或者or函数配合if函数使用

满足每个季度月销售额超过2万的通过，不满足的淘汰
在这里插入图片描述
具体结果如下图所示，为了更直观的观看，可以在结果列加上一个筛选

标题

下图中的表是公司销售人员一年的销售额情况
①筛选年销售额超过6万的人员，通过；低于6万，不通过
在这里插入图片描述
②优秀：年销售额超过10万；
通过：年销售额在6-10万；
不通过：年销售额小于6万

if函数中，再嵌套一个if函数。按alt+enter回车换行，这样方便观看不容易出错

案例二：
在这里插入图片描述

文本函数

案例一：
要提取下表中的数字，前五位都是数字，并且都很有规律，可以用left函数
在这里插入图片描述

案例二：
要提取后面的岗位名称，用mid函数
第一个参数：要提取的字符串个；
第二个参数：开始提取的位置；
第三个参数：这里输入的是最长的字符个数，比如输入的是8，那么他提取的时候，字符长度可以从0-8
在这里插入图片描述

案例三：用right函数提取岗位名称
因为岗位代码中前五位都是规律的数字，后面是岗位名称，所以只要求出岗位代码的长度，再减去前面的5个数字，后面剩下的就是我们要的。

求字符长度用的len函数，这里减去5，要放在外面，不能放在len函数里面减。len函数求的是字符长度，我们要把求出来的字符长度减去5
在这里插入图片描述

rank函数

第一个参数：要比较的数值
第二个参数：比较的范围，这里记得按FN+F4把范围固定
第三个参数：降序-0或者不输入；升序-非0，除0以外的任何数字都可以
这里的降序意思是：考的分数越高，排名的数字越低，和平常的降序不大一样。
在这里插入图片描述

match函数和index函数

在这里插入图片描述

最终查找匹配的结果如下图所示：

vlookup函数

这个函数的功能：根据已有的，去找到遗失的内容
也叫列查找函数，行查找函数用的比较少。
在这里插入图片描述
左表中人员的年龄缺失，右表中的各项都是完整的。两张表都有年龄这个字段，所以现在要用vlookup函数，找右表中找到左表缺失的年龄。

第一个参数：要查找的字段，两个表中相同的字段
第二个参数：查找的区域，查找区域是绝对引用，位置不能变，记得固定（按FN+F4）
第三个参数：要查找的年龄，这个字段在右表中的第三列，所以第三个参数填3
第四个参数：匹配条件，这里选择精确匹配
在这里插入图片描述

查找出一个之后，剩下的可以用自动填充功能。

案例二：
根据图书编号查找图书名称，具体操作如图二所示

因为前期已经在名称框中对整个表格进行了命名，所以输入第二个参数的时候，直接输入之前命名的名字即可。
在这里插入图片描述