请问excel的timesheet的一个formula问题

luoluoyehua

请问各位比较懂formular的同学们，我想写个formular来使表简单，可实在不知怎么用google去描述。。是这样举个小例子说明：
         
        Date from                                         days     Mon   Tues  Wed        Thur         Frid          sat           sun
david-8 hrs/day        18.08.14-24.08.14          5             8                8         8           8            8               
jason-9 hrs/day        18.08.14-24.08.14          6             9                9         9           9            9             9       
patrick-10 hrs/day        18.08.14-24.08.14          5.5            10       10        10          10           10             5       

一堆人，每周工作几天不同，但是每个人的每天工作hrs是定的，有8，9，10小时不等的。然后原本的report就是mon－sun每天都是满的。要根据前面那列工作的days来manually改写到底上了几天。我想在mon－sun这几个栏写个什么公式，
使得前面column if＝5，then mon－fri就5天，if=6.5，then mon－sat，外加sun 0.5天。

每个人的daily hours在别的mastersheet有。请问这个有解决办法没？而不是每次我都要用days那栏sort出几天来删减。。。

谢谢大家！！

jy00064164

sorry i got no Chinese input and no excel with me at the moment, here is my thought:

add an extra row 'threshold' above your first row, input integer threshold 0-6 for Mon-Sun

for argument sake let's say 'threshold' row is in 'row 2', your 'days' column is in 'column D', your first person's Monday cell is 'E4'

In E4 input formula =if($D4-E$2>1,$D4,if($D4-E$2>0,($D4-E$2)*$D4,0))
drag it across and down

Basically the 'days' is above the threshold, it covered that day/part of the day; if below threshold, it hasn't covered yet therefore it's 0 hrs.

However your whole idea of using 'days' column as a control is rather restricted as it assumed the data is always continuous. If a person was sick on Wednesday, or worked half day on both saturday and sunday,  the formula won't pick it up, though it met your requirement.

sidneygan

jy00064164 发表于 2014-8-21 03:12
sorry i got no Chinese input and no excel with me at the moment, here is my thought:

add an extra r ...

基本ok，但是在 formula =if($D4-E$2>1,$D4,if($D4-E$2>0,($D4-E$2)*$D4,0))，第一个if判定成功后应该是hours，而不是$d4,同理第二个if判定成功以后也应该是（$D4-e$20*hours。

wcsydney2000

looks like the DAYS=SUM OF TOTAL WEEKLY HOURS/HOURS PER DAY

FOR EXAMPLE: 5=40/8
                             6=54/9
                             5.5=55/10

sidneygan

思路跟2楼相似，
A 员工名字
B Daily Hours
C Days
D-J Monday to Sunday （第一排 D1 to J2 对应放入0-6的数字）
公式第一步放在D3，=IF($C3-D$1>1,$B3,IF($C3-D$1>0,($C3-D$1)*$B3,""))
然后拖拽到相应的格子，

Aries2012

如果days至少是5

D2=IFERROR(IF(COLUMN()-3>$C2,IF(($C2>5)*($C2>COLUMN()-4),$C2-IF(COLUMN()-3>6,6,5),""),1)*MID($A2,FIND("-",$A2)+1,2),"")

右下拉

niuren

把daily hour算作一列，比如L， 结合2楼的，把D4换成L4

=if($D4-E$2>1,$L4,if($D4-E$2>0,($D4-E$2)*$L4,0))

samolano

jy00064164 发表于 2014-8-21 03:12
sorry i got no Chinese input and no excel with me at the moment, here is my thought:

add an extra r ...

不好意思，($D4-E$2)*$D4，是什么意思呢？

luoluoyehua

samolano 发表于 2014-8-21 10:12
不好意思，($D4-E$2)*$D4，是什么意思呢？

我也不懂想问呢！怎么dollar sign一会在前一会在后呢，同志们咋写得这么厉害的公式，等下班回家好好研究下

luoluoyehua

感谢大家！成功了！只是有几个个别人是算hrs的不是days的，就是一周共干了12hrs，不适用这个fomular，我只好手动改一下，分配成周一至周六每天2hrs。
都太厉害了！

sidneygan

luoluoyehua 发表于 2014-8-21 11:35
感谢大家！成功了！只是有几个个别人是算hrs的不是days的，就是一周共干了12hrs，不适用这个fomular，我只 ...

$在D3前面表示固定D但不固定3，反之亦然。
主要是方便你拖拽。。

niuren

luoluoyehua 发表于 2014-8-21 11:35
感谢大家！成功了！只是有几个个别人是算hrs的不是days的，就是一周共干了12hrs，不适用这个fomular，我只 ...

绝对引用和相对引用

1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1
当将公式复制到C2单元格时变为：=A2+B2
当将公式复制到D1单元格时变为：=B1+C1

2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1
当将公式复制到C2单元格时仍为：=$A$1+$B$1
当将公式复制到D1单元格时仍为：=$A$1+$B$1

3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1
当将公式复制到C2单元格时变为：=$A2+B$1
当将公式复制到D1单元格时变为：=$A1+C$1

csq84

=IF(COLUMNS($C1:C1)<=$B2,$A2,IF((COLUMNS($C1:C1)-$B2)<1,(COLUMNS($C1:C1)-$B2)*$A2,0))

Hours 在A1，该公式的好处是，可以自由上下移动，适合叠加

测试可用，略有复杂

Hours        days        Monday        Tuesday        Wednesday        Thursday        Friday        Saturday        Sunday
8        5        8        8        8        8        8        0        0
9        6        9        9        9        9        9        9        0
10        5.5        10        10        10        10        10        5        0

windfox

如果员工人数少，就可以用简单的公式加手动拖拽实现；如果员工人数很多，建议做宏，然后把宏链接到一个按钮上可以自动填充。