エクセルのデータ検出

質問

時給計算の方法について
Ａ列にスタッフＩＤ番号がありＢ列に名前Ｃ列に時給（スタッフ事に時給が違うため）
Ｊ列にイン時間　Ｋ列にアウト時間があります。
Ｅ列に今日来る人だけのＩＤ番号を入力すればＦ列にＡ列の全スタッフの中からＩＤを検出してＣ列の時給データを持ってきてＦ列に今日稼ぐ給料を自動で入力されるような式を作りたいと思っています。
どのような式を作れば可能でしょうか？またもっと簡単な方法があれば教えてください。

回答No.1

okormazd · ベストアンサー

質問にあるようなことをやるとすれば、添付図のようになる。時間の丸めなどは考慮していない。
F4:
=OFFSET(E4,MATCH(E4,A4:A15,0)-1,-2)*(OFFSET(E4,MATCH(E4,A4:A15,0)-1,6)-OFFSET(E4,MATCH(E4,A4:A15,0)-1,5))*24

回答No.2

39Ring

名前と時給はID番号を使ってLOOKUP関数で引っ張れます。
時間計算ですが、分の切捨て、切り上げの問題があるので、一旦時間を丸めさせる必要があります。（例えば30分単位の場合、イン時間が09:10だったら09:30に、アウト時間が18:56だったら、18:30にといった具合に）
イン時間を丸める式は、=IF(AND(MINUTE(イン時間)>0,MINUTE(イン時間)<=29),TIME(HOUR(イン時間),30,0),IF(AND(MINUTE(イン時間)>=30,MINUTE(B3)<=59),TIME(HOUR(イン時間)+1,0,0),TIME(HOUR(イン時間),0,0)))※分が1～29は30分に、30から59は時間を1時間足して、分を0分に、0分丁度の場合はそのままにしています。
アウト時間を丸める式は、=IF(AND(MINUTE(アウト時間)>=0,MINUTE(アウト時間)<=29),TIME(HOUR(アウト時間),0,0),TIME(HOUR(アウト時間),30,0))
次に時給の計算ですが、丸めたアウト時間からイン時間を引いた物を=HOUR(アウト時間-イン時間)*時給+MINUTE(アウト時間-イン時間)/60*時給で算出します。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/hour.htm