201401函中课前练习七

meililin

思考了很久也没想出简短明了的公式，逻辑混乱中

1. d3=CHOOSE(COLUMN()-3,IF(AND($B3="",$C3<>""),$D$1,""),IF(AND($C3="",$B3<>""),$E$1,""),IF(AND($B3="",$C3=""),$F$1,""))

复制代码

1. d3=IF(IF($B3=$C3="","",IF(AND($B3="",$C3<>""),$D$1,IF(AND($C3="",$B3<>""),$E$1,$F$1)))=D$1,D$1,"")

复制代码

MCQUEEN

1. =IF(IF(($B3="")*($C3=""),$F$1,IF(($B3>0)*($C3>0),"",IF($B3>0,$E$1,$D$1)))=D$1,D$1,"")

复制代码

投机取巧下：前提G1单元格为空单元格

1. =INDEX($D$1:$G$1,IF(SUM(IF($B3:$C3=0,COLUMN($A:$B),))=COLUMN(A1),COLUMN(A1),4))&""

复制代码

珺珺妈

老师辛苦啦。题太难。

车仁静

交练习来了~~~

再砍一下，

jio1ye

1. =IF(AND($B3="",$C3=""),IF(COLUMN()=6,D$1,""),IF(AND($B3="",COLUMN()=4),D$1,IF(AND($C3="",COLUMN()=5),D$1,""))

复制代码

再次体验裹脚布功力，109字符

这次改善了下，不进反退{:031:}110字符

1. =CHOOSE(COLUMN()-3,IF(AND($B3="",$C3<>""),D$1,""),IF(AND($B3<>"",$C3=""),D$1,""),IF(AND($B3="",$C3=""),D$1,""))

复制代码

不想了，108字符

1. '=CHOOSE(COLUMN()-3,IF(AND($B3="",$C3<>""),D$1,""),IF(AND($B3<>"",$C3=""),D$1,""),IF(COUNT($B3:$C3)=0,D$1,""))

复制代码

云子

1. =IF(LOOKUP(SUM($B3:$C3),{0,"上下班均未打卡";0.2,"下班未打卡";0.5,"上班未打卡";1,""})=D$1,D$1,"")

复制代码

先交个长长的

ldxhzy

D3公式:
=CHOOSE(COLUMN()-3,IF(($B3="")*($C3<>""),$D$1,""),IF(($B3<>"")*($C3=""),$E$1,""),IF(($B3="")*($C3=""),$F$1,""))

笨笨四

普通公式：D3右拉下拉，IF(SUM(($A3:$C3<>"")*{6,-1,-2})=COLUMN(),D$1,"")

取个巧，就题而言：D3：F3区域数组下拉：IF(HOUR(B3+C3)={17,8,0},D$1:F1,"")
（上班都是8点过打卡，下班都是17点过打卡）

zac0226

1. =IF(CHOOSE(IF(($B3="")*($C3=""),1,IF(($B3="")*($C3<>""),2,IF(($B3<>"")*($C3=""),3,4))),"上下班均未打卡","上班未打卡","下班未打卡","")=D$1,D$1,"")

复制代码

tgy0451

太难了，求最佳答案……