带有单位的数据如何快速求和?

今天,成都office培训速成班小编将和大家分享的小技巧是关于如何对带有单位的数据进行汇总求和

看到这样的数据,要进行计算的你是不是整苦恼的抓耳挠腮想不出一个好的办法快速解决,只能手动计算后再输入答案?如果是的话,那你一定要把这篇文章看完,你会发现,有了方法,这个计算不过就是半分钟不到的时间就能够完美解决哦!

第1种解决思路:只需要把C列单价数据后面的单位“元”删除即可,方法如下:

方法1:替换法

  1. 选择C列数据—【开始】—【查找和选择】—【替换】:【查找内容】为“元”,【替换为】为空(即为不填写任何内容)—【全部替换】。完成后即可删除数据后的单位。

(截图说明:为了方便截图,本图片中查找和选择的位置有做过调整,实际位置以软件界面为准)

方法2:分列法

选择C2:C11区域—【数据】—【分列】—【分隔符号】—【下一步】

勾选【其他】,然后在后面输入“元”—【下一步】

直接点击【完成】即可

如何给合并单元格添加序号?

为了让表格结构看起来更合理,很多小伙伴在做表格的时候都会选择使用合并单元格。不过任何事情都有其两面性。合并单元格虽然可以让表格结构更清晰合理,却也给后续的表格统计计算等操作带来了很大的难题,就比如需要在下表的合并单元格中填充序号:

看到这,小伙伴们有想到什么好办法吗?如果没有的话,就跟着成都office培训中心小编往下看吧:

方法1:COUNTA函数法

选择A2:A13区域,输入公式:=COUNTA(A$1:A1)后按Ctrl+Enter(回车)结束即可

公式说明:COUNTA(A$1:A1)的作用为统计动态区域A1:A1、A1:A2……A1:A13区域内非空单元格的数量。而A2:A4为合并单元格,其中只有A2单元格内存有内容,A3到A4单元格实际为空,所以COUNTA统计A2:A4的数量为1.往下的合并单元格同理。

方法2:COUNT函数法

选择A2:A13区域,输入公式:=COUNT(A$1:A1)+1后按Ctrl+Enter(回车)结束即可

公式说明:计算当前公式单元格之前包含数字的单元格个数,在该个数的基础上加1。

方法3:MAX函数法

选择A2:A13区域,输入公式:=MAX(A$1:A1)+1后按Ctrl+Enter(回车)结束即可

公式说明:使用MAX函数统计当前单元格之前的最大值,再最大值加1。

方法4:LOOKUP函数法

选择A2:A13区域,输入公式:=IFERROR(LOOKUP(9E+307,A$1:A1),0)+1后按Ctrl+Enter(回车)结束即可

公式说明:使用LOOKUP函数查找当前单元格之前查找数值9E+307(本数字是一个非常大的数字),当找不到这个数字时,则返回小于等于这个数字的最大值。然后再加上1,得到序号

满足其一条件即可的多对多查询

前面小编和小伙伴们分享了怎么实现多对多查询,今天成都office培训机构小编和大家分享如何在多个条件满足其中一个时查询相对应的数据。

如上图所示,现在需要根据给出的两个部门查询出满足其中某一个部门的员工姓名(查询销售部或质检部的员工姓名)

具体步骤如下所示:

1.制作辅助数据

在D2单元格输入公式:=(B2=F$1)+(B2=F$3)+D1后往下填充

公式说明:

(B2=F$1)部分:用B列的部门与F1单元格指定的部门进行对比,如果相同,返回逻辑值TRUE,否则返回逻辑值FALSE。

(B2=F$3)部分:用B列的部门与F3单元格指定的部门进行对比,如果相同,返回逻辑值TRUE,否则返回逻辑值FALSE。

(B2=F$1)+(B2=F$3)部门:两个判断条件相加,如两个条件其中之一满足,则本部分公式返回TRUE+FALSE或者FALSE+TRUE

然后将返回的逻辑值与前一个单元格中的数值相加。(因此,D1单元格内不能存放除0之外的数据)

在四则运算中,TRUE的作用相当于1,FALSE的作用相当于0。也就是如果部门和性别两个条件都符合了,D列的数值就增加1,否则还等于前一个数值。

注意观察,1、2、3、4……这些序号首次出现的行,就是符合条件的记录。

2.使用INDEX函数查询员工姓名

在F5单元格输入公式:=IFERROR(INDEX(A:A,MATCH(ROW(A1),D:D,0)),””)后往下拖动填充

本公式逻辑和之前文章(多对多查询)内的查询公式逻辑一样,如有不理解的小伙伴,可查看之前文章哦

今日的分享到这就结束了,希望对你有所帮助!

如何快速进行多对多查询?

今天,有小伙伴问成都office培训机构小编如何能够快速实现多对多查询。今天,小编就给大家分享一下这个案例吧:

如上图所示,需要根据所给出的部门和性别,在左侧表格内才哈讯对应的员工性别。

此案例要完成计算,我们只需要使用一个辅助列数据和一个查询函数即可。不会的小伙伴且跟着小编一步步往下操作吧:

1.制作辅助数据

在D2单元格内输入公式:=(B2=F$1)*(C2=F$2)+D1后往下填充

公式说明:

(B2=F$1)部分,用B列的部门与F1单元格指定的部门进行对比,如果相同,返回逻辑值TRUE,否则返回逻辑值FALSE。

(C2=F$2)部分 ,用C列的性别与F2单元格指定的性别进行对比,如果相同,返回逻辑值TRUE,否则返回逻辑值FALSE。

而(B2=F$1)*(C2=F$2)部分将两个条件相乘,则表示需要两部分条件同时满足,即B列部门与F1单元格指定的部门相同,且C列性别与F2单元格指定的性别相同时,返回逻辑值TRUE,否则返回逻辑值FALSE。

然后将返回的逻辑值与前一个单元格中的数值相加。(因此,D1单元格内不能存放除0之外的数据)

在四则运算中,TRUE的作用相当于1,FALSE的作用相当于0。也就是如果部门和性别两个条件都符合了,D列的数值就增加1,否则还等于前一个数值。

注意观察,1、2、3、4……这些序号首次出现的行,就是符合条件的记录。

2.查询数据

在F4单元格内输入公式:=IFERROR(INDEX(A:A,MATCH(ROW(A1),D:D,0)),””)后往下拖动 即可

公式说明:

MATCH(ROW(A1),D:D,0)部分,ROW(A1)部分返回A1单元格的行号,往下拖动时,会依次返回A2、A3……的行号,1、2、3……,然后再使用MATCH函数在D列中分别去定位ROW函数所得到的数字所在的行数(如有重复,则返回此数字第一次出现的行数)

然后,再使用INDEX函数去A列中查询MATCH函数所定位得到行数相对应的姓名

最后,在最外层套用IFERROR函数对INDEX查询得到的结果进行检查,如INDEX函数所得到的结果为错误值,则返回””(空)

到此,本案例与大家分享的多对多查询就完成了。当然,本案例中,使用的查询函数为INDEX和MATCH函数的结合。如果想使用VLOOKUP函数进行查询,可将辅助列数据放到姓名列前面即可。

今天的分享到此就结束了,希望对你有所帮助!

使用VLOOKUP实现一对多查找

说起VLOOKUP函数的一对多查询,相信有很多小伙伴想到的都是使用COUNTIF函数做辅助数据,然后再使用VLOOKUP查询得到所需结果就可以了,而成都office培训学校小编今天给大家介绍的也是关于使用辅助列和VLOOKUP函数进行一对多查询的方法,只不过辅助列内所使用的不是COUNTIF函数,而是另一个公式,具体内容请往下看:

具体操作步骤如下:

1.制作辅助列数据

在A2单元格内输入公式:=(C2=G$1)+A1后往下填充

公式说明:

(C2=G$1)部分,用C列的部门与G1单元格指定的部门进行对比,如果相同,返回逻辑值TRUE,否则返回逻辑值FALSE。

然后将返回的逻辑值与前一个单元格中的数值相加。(因此,A1单元格内不能存放除0之外的数据)

在四则运算中,TRUE的作用相当于1,FALSE的作用相当于0。也就是如果部门和性别两个条件都符合了,D列的数值就增加1,否则还等于前一个数值。

注意观察,1、2、3、4……这些序号首次出现的行,就是符合条件的记录。

2.使用VLOOKUP函数进行一堆多查询

在G4单元格内输入公式:=IFERROR(VLOOKUP(ROW(A1),A:B,2,0),””)后往下填充

公式说明:

  • B列的部门每重复出现一次,A列的序号增加1。
  • VLOOKUP函数使用ROW(A1)得到1至N的递增序列作为查询值,在A:B列这个查询区域中,依次返回与递增序号相对应的B列的姓名。由于VLOOKUP在有多个匹配结果时,默认返回第一条记录,所以只会返回A列中各个序号首次出现的记录。
  • 注意查找区域必须由辅助列A列开始。
  • 最后将辅助列进行隐藏即可。

本次分享到这就结束了,希望对你有所帮助!

如何快速计算日期属于哪个季度?

一年有三百六十五个日出,我送你三百六十五个祝福……一年三百六十多天,那怎么才能知道所给日期是属于那个季度呢?

今天,office培训小编给大家分享一个或许你不曾想到过的方法去计算日期所属季度。在本案例在中小编使用的是LEN函数和MONTH函数结合来计算日期所属季度哦,在C2单元格内输入公式:=”第”&LEN(2^MONTH(B2))&”季度”

看到这个公式,可能有些小伙伴不知道该怎么去理解,且听小编慢慢道来:

  1. 先使用MONTH函数提取出B列日期所属月份
  2. 再计算2^MONTH(B2)的结果,得到结果后,第1季度所属日期得到的结果都是1位数的数字,第2季度所属日期得到的结果都是2位数的数字,第3季度所属日期得到的结果都是3位数的数字,第4季度所属日期得到的结果都是4位数的数字
  3. 再使用LEN函数计算2^MONTH(B2)的字符数,所得结果和日期所属季度相同
  4. 最后再使用连接符号“&”连接上“第”字和“季度”俩字。最终计算出B列日期所属季度

今天的分享到这就结束咯,希望对你有所帮助。

INDEX和MODE统计出现次数最多的数据

“发给你的文件,是公司本月内的客户咨询表,下班前把客户咨询最多的课程找出来”

(此图只截取了部分数据)

看了一眼电脑上的表格,再看了一眼墙上已经指向59的时钟,成都office培训小编马上进入了疯狂的头脑风暴中:怎样才能在这不到一分钟的时间内把这一列中出现此处最后的课程名称找出来呢?查询的话可以使用INDEX函数,那怎么才能知道那个课程名称是出现次数最多的呢?……我好想记得有一个函数是可以返回一组数据之间出现次数最多的数字的,是哪个函数呢?…………在经过反复回忆后,我终于想起来了,这个函数就是:MODE函数啊。瞧我这记性!

在F2单元格中输入公式:=INDEX(D2:D45,MODE(MATCH(D2:D45,D2:D45,0)))

那么这个公式怎么去理解呢?

  1. MODE函数的作用为:返回一组数据中的众数(出现次数最多的数据为众数)
  2. MATCH(D2:D45,D2:D45,0):使用MATCH函数去分别定位D2:D45区域中每个数据出现的行数;返回:{1;2;3;4;5;1;2;3;4;5;2;4;2;4;1;2;3;3;4;3;5;4;5;2;2;3;1;2;3;4;3;2;4;2;1;3;5;2;1;3;4;2;1;3}
  3. 再使用MODE函数返回所有行数中出现次数最多的行数
  4. 最后再使用INDEX函数去查找C2:C45中该行数所对应的数据

今天的分享到这里就结束了,希望对你有所帮助。

VLOOKUP合并单元格多条件查询

同事A:你知道要根据姓名查找联系方式该用什么函数吗?

同事B:这你都不知道啊,你也太小白了吧。要说查找匹配类的问题,那肯定首选VLOOKUP函数了呀。这么简单的问题,都还要问我,还不快去学起来!

说起VLOOKUP函数,想必很多小伙伴都不会陌生了。作为查询匹配界的大佬,VLOOKUP函数为提高我们的工作效率做了非常大的贡献。而今天成都office培训班小编想要跟大家分享的是,如何使用VLOOKUP函数在带有合并单元格的数据中实现多条件查找。如下图所示:需要根据指定的部门和考核名次,查询相应的员工姓名和业绩

VLOOKUP函数的参数,想来小伙伴们也已经很熟悉了。不过,我了防止看到这篇文章的小伙伴部清楚或忘了,小编在这里还是再多嘴一句,VLOOKUP的参数形式为:VLOOKUP(查询依据,查找范围,查找内容在查找范围的第几列,0)

H2单元格的公式为:=VLOOKUP(G2,OFFSET(B1:C1,MATCH(F2,A2:A21,),0,5),2,1)

公式说明:

在本案例的公式VLOOKUP(G2,OFFSET(B1:C1,MATCH(F2,A2:A21,),0,5),2,1)中:

1.MATCH(F2,A2:A21,0):先使用MATCH定位F2单元格内的部门在A2:A21区域内的第几行

2.再使用OFFSET函数以B1:C1为参考区域,向下偏移MATCH(F2,A2:A21,0)行,不向右偏移列,形成的新的引用区域为5行1列

3.最后再使用VLOOKUP函数在offset函数形成的新的引用区域中,根据部分查找对应姓名

4.简单来说:先用MATCH函数根据班级定位查找区域起始位置,再借助OFFSET函数引用目标区域,最后传递给VLOOKUP函数作为查询区域。

说在最后:当然,本公式也存在一定的限制,那就是需要合并单元格所占用的行数是一样的,就像本案例中,每个部门都只是录入了考核成绩前五名的数据哦。

今天的分享就到这了,你学会了吗?如果你有更好更简单的方法,欢迎留言和小编一起分享学习哦!

如何使用DGET函数进行条件求和?

提到查询匹配时,相信很多小伙伴首先想到的就是VLOOKUP函数,又或者是LOOKUP、HLOOKUP、INDEX和MATCH等。如果所使用的是office 365的版本呢,还有比VLOOKUP更方便一点的XLOOKUP的存在。而成都office培训短期班小编今天提到的却不是这些常见的函数,而是DGET函数。在某些场景下,使用DGET函数会比VLOOKUP函数更简单一点,不信的话,就跟着小编往下看吧:

1.DGET函数的作用:从列表或数据库的列中提取符合指定条件的单个值。

2.DGET函数的参数:DGET(Database, Field, Criteria)

参数说明:

Database:构成列表或数据库的单元格区域。

Field:指定函数所使用的列。 输入两端带双引号的列标签,如 “使用年数” 或 “产量”;或是代表列表中列位置的数字(不带引号):1 表示第一列,2 表示第二列,依此类推

Criteria:包含所指定条件的单元格区域。即为条件区域

3.函数使用案例

3.1 使用Dget函数实现单条件/逆向查找

如下图所示,需要根据所给工号查找对应的姓名

F2单元格公式为:=DGET(A1:C37,1,E1:E2)

公式说明:DGET函数以E1:E2位条件区域(条件区域内要包含标题和条件值哦)查找位于A1:C37区域中(此为数据区域,需要包含标题行在内)第1列中与条件值A001对应的数据,即为黄蓉

根据DGET函数第2参数的特点,此公式还可以书写为:

=DGET(A1:C37,”姓名”,E1:E2)

=DGET(A1:C37,F1,E1:E2)

3.2 使用DGET函数多条件查找

如下图所示,需要根据给出的工号和姓名查找相应的业绩数据

G2单元格公式为:=DGET(A1:C37,”业绩”,E1:F2)

公式的书写逻辑和第1个案例中是一样的,在这里小编就不再重复说明了哦,勤快点的小伙伴们也可以练一下另外两种公式的书写方法哦

3.3 Dget和column函数实现多列查找

如下图所示,需要根据给出的工号查找对应的姓名和业绩数据

F2单元格公式为:=DGET($A$1:$C$37,F1,$E$1:$E$2)。输入完成后向右拖动填充至G2单元格

公式说明:因为F2单元格的公式需要向右填充至G2单元格,所以Dget函数的数据区域A1:C37和条件区域E1:E2需要按F4进行锁定哦

说到这,有的小伙伴可能就很疑惑了,就上面的这些案例来说,Dget在查询数据方面很实用啊,那为什么这个函数的使用却没有VLOOKUP函数那样广泛呢?其实啊,这个原因咋子与Dget函数也有一个“致命”的缺点,那就是Dget函数只适合用于查找单条数据,不能够向下填充哦。

今天的分享到这里就结束了,你学会了吗?

Ctrl+反斜线的求和小技巧

正所谓条条大路通罗马,小编在前面也陆陆续续的介绍了一些关于求和的小技巧,如快速求和的快捷键Alt+=;使用SUMIF函数隔列求和等。而今天成都office培训速成班小编又给大家带来了一个奇葩的求和案例。如下图所示,计算每个部门的每个月的总业绩(计算小计)

在前面分享快速计算小计的案例中,使用的方法是定位空值后按Alt+=即可完成。而在本案例中,却不能直接定位空值,因为在业绩数据中,有空白单元格,如果直接定位空值的话,最后的计算结果并不是我们想要的结果,如下图所示:

那到底怎样才能在本案例中快速计算小计行的数据呢?跟着小编的步骤操作吧:

选择B2:H19后按Ctrl+\(Ctrl+\的作用:定位行内差异单元格。如果此快捷键无法使用,小伙伴们也可以通过Ctrl+G定位—定位条件—行内差异单元格实现哦)选择业绩所在区域(原理说明:当B列内为汉字时,C列到H列无论是数字还是空白,都和汉字有差异,因此按完Ctrl+\后都会被选中,而当B列为空时,C列到H列也为空,没有差异,所以不会被选择

然后再按求和快捷键Alt+=即可完成小计行数据的计算哦

怎么样,这个求和小技巧很方便吧,你学会了吗?