Excel 更表設計問題

本帖最後由 LikPan 於 2016-6-21 15:45 編輯

整咗個更表,但入開
A 更
B 更
Full 更

類似咁:
A BC D E F
1 Mon Tue Wed Thu Fri
2 A F F A B


但月尾要計算幾個加埋嘅時數係幾多
A,B = 4
Full = 8

想請教咁應該點搞好,google 咗搵果D唔係自己意思
可能唔知用咩 keyword

本帖最後由 simontang 於 2016-6-21 17:53 編輯

=SUMIF(range, criteria, [sum_range])
or
=countif(range, criteria)

TOP

=SUMIF(range, criteria, [sum_range])
or
=COUNTIF(range, criteria, [sum_range])
simontang 發表於 2016-6-21 16:15


=COUNTIF(B6:AE6,IF(B6:AE6="F",VALUE("8"),VALUE("4")), [sum_range])

係咪咁? 但最尾果個應該填?

TOP

=COUNTIF(B6:AE6,IF(B6:AE6="F",VALUE("8"),VALUE("4")), [sum_range])

係咪咁? 但最尾果個應該填? ...
LikPan 發表於 2016-6-21 17:29

sorry, it's should be"
countif(range, criteria)

=COUNTIF($B$2:$F$2,"A")+COUNTIF($B$2:$F$2,"B")
=COUNTIF($B$2:$F$2,"F")

TOP

本帖最後由 stanley_chung 於 2016-6-21 18:12 編輯

我會整多個table, 然後用vlookup 找到對應時數,再用Sum 加埋
如下圖
Screen Shot 2016-06-21 at 6.12.09 PM.png
2016-6-21 18:12

TOP

4*COUNTIF(B2:F2,"A")+4*COUNTIF(B2:F2,"B")+8*COUNTIF(B2:F2,"F")

TOP