Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 1902|回复: 9

[已解决]未解决急请高手大神再出手,考勤表里包含自定义列里多单元格的字符条件求和

[复制链接]
发表于 2017-8-26 09:25 | 显示全部楼层 |阅读模式
本帖最后由 kangdiwu 于 2017-8-29 22:13 编辑

考勤表里统计包含自定义列里多单元格的字符条件任一的对应和,怎么用公式来实现?如附件或者如下:
A列
B列
C列
D列
E列
F列
G列
H列
I列
L列
M列
N列
统计非上班天数
姓名
7月1日
7月2日
7月3日
7月4日
7月5日
7月6日
7月7日
摘要
对应天数
备注
4.75
张三
休息一天
请假一天
病假一天
休息一天
上午上班2小时
休息一天
1
2.625
李四
上午休
上午旷工
上午休
下午上班3小时
休息半天
0.5
3
王五
下午休
请假一天
休息半天
旷工
下午休
0.5
下午旷工
1
加倍处罚
 
上午休
0.5
上午旷工
1
加倍处罚
说明上班3小时
0.625
1、L列和M列是对应自定义列,如休息则对应为1天,上午休则对应为0.5天,上班2小时对应0.25天等;上班2小时
0.75
按每天8小时比例
2、怎么用公式在一个单元格上统计出一个总数来?如A2单元格统计张三的非上班天数全天休息
0
3、C-I列是不规则的字符串,是包含L列而不一定完全相同;全天请假
0
4、如"病假"是相同,而"上午上班2小时"包含了"上班2小时";全天旷工
0
全天病假
0
请假一天
1
旷工一天
2
加倍处罚
 
旷工半天
1
加倍处罚
 
病假一天
1
备用
0
 



最佳答案
2017-8-30 05:05
A2=SUMPRODUCT((NOT(ISERROR(MATCH("*"&$L$3:$L$23&"*",C3:I3,))))*(COUNTIF(C3:I3,"*"&$L$3:$L$23&"*")* $M$3:$M$23))  下拉复制到A5 ,
分别得到
4.75 (已经解决C3/H3在不同日期出现同一请假的计算问题)
2.625  (已经解决D4/H4在不同日期出现同一请假的计算问题)
2 ( 与你的模拟答案3不符,是因为I5的“矿工”输入不规范,没有具体说明是什么时段的旷工,没有被计算在内。把I5改为 上午矿工/下午矿工/矿工半天后与你的答案匹配。


按照你的说明3 “C-I列是不规则的字符串,是包含L列而不一定完全相同;”,现有I5字符是没有包含L列字符

统计包含自定义列里多单元格的字符条件任一的对应和.rar

8.51 KB, 下载次数: 8

 楼主| 发表于 2017-8-27 10:15 | 显示全部楼层
急用,麻烦高手大神帮忙解决,谢谢。
回复

使用道具 举报

发表于 2017-8-27 11:35 | 显示全部楼层
A3=SUMPRODUCT((RIGHT(C3:I3,5)=$L$3:$L$23)*($M$3:$M$23))   下拉复制
回复

使用道具 举报

 楼主| 发表于 2017-8-27 11:40 | 显示全部楼层
cabcyvr 发表于 2017-8-27 11:35
A3=SUMPRODUCT((RIGHT(C3:I3,5)=$L$3:$L$23)*($M$3:$M$23))   下拉复制

谢谢高手的解答,但是你的答案规则化了RIGHT(C3:I3,5),我的原意是不规则的一串字符,然后包含L列的某字符。
有没有不规则化的相关公式?
回复

使用道具 举报

发表于 2017-8-27 11:55 | 显示全部楼层
A2=SUMPRODUCT((NOT(ISERROR(MATCH("*"&$L$3:$L$23&"*",C3:I3,))))*($M$3:$M$23))

A4的结果为4.875,之前是2.875,原因是L6的“矿工”命名含糊,没有和 L11 L12 的字符完全区别,被模糊查找后计算在内。建议你先自己先规范L列的 名称

评分

参与人数 1 +1 收起 理由
kangdiwu + 1 很给力

查看全部评分

回复

使用道具 举报

发表于 2017-8-27 11:59 | 显示全部楼层
C: I列可以与L列不完全匹配,但是一定要包含L列字符,但同时要保证L列自身没有互相包含的字符。如果你把L6 改成“全天旷工”,就OK
回复

使用道具 举报

 楼主| 发表于 2017-8-28 12:29 | 显示全部楼层
cabcyvr 发表于 2017-8-27 11:59
C: I列可以与L列不完全匹配,但是一定要包含L列字符,但同时要保证L列自身没有互相包含的字符。如果你把L6 ...

多谢高手大神的解答。非常感谢。
回复

使用道具 举报

 楼主| 发表于 2017-8-29 22:18 | 显示全部楼层
cabcyvr 发表于 2017-8-27 11:55
A2=SUMPRODUCT((NOT(ISERROR(MATCH("*"&$L$3:$L$23&"*",C3:I3,))))*($M$3:$M$23))

A4的结果为4.875,之 ...

高手大神,出了问题,比如一个人他不同日期休息半天时,公式只能统计一次,不能统计实际多次的。麻烦高手再看看怎么办?已经更新附件等了。
回复

使用道具 举报

发表于 2017-8-30 05:05 | 显示全部楼层    本楼为最佳答案   
A2=SUMPRODUCT((NOT(ISERROR(MATCH("*"&$L$3:$L$23&"*",C3:I3,))))*(COUNTIF(C3:I3,"*"&$L$3:$L$23&"*")* $M$3:$M$23))  下拉复制到A5 ,
分别得到
4.75 (已经解决C3/H3在不同日期出现同一请假的计算问题)
2.625  (已经解决D4/H4在不同日期出现同一请假的计算问题)
2 ( 与你的模拟答案3不符,是因为I5的“矿工”输入不规范,没有具体说明是什么时段的旷工,没有被计算在内。把I5改为 上午矿工/下午矿工/矿工半天后与你的答案匹配。


按照你的说明3 “C-I列是不规则的字符串,是包含L列而不一定完全相同;”,现有I5字符是没有包含L列字符
回复

使用道具 举报

 楼主| 发表于 2017-8-30 09:15 | 显示全部楼层
cabcyvr 发表于 2017-8-30 05:05
A2=SUMPRODUCT((NOT(ISERROR(MATCH("*"&$L$3:$L$23&"*",C3:I3,))))*(COUNTIF(C3:I3,"*"&$L$3:$L$23&"*")* $ ...

非常多谢高手大神的再次出手解答。谢谢!!!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 05:24 , Processed in 0.488727 second(s), 11 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表