EXCELの関数を教えてください

質問

EXCEL2000を使用しています。
カレンダーを作成しています。その中に残業時間を日々入力していって最終的に月の残業時間を合計で出します。
そこまではいいのですが、残業時間を二つの種類に分けたいのです。
内容は
午後５時以降から残業タイムになります。
午後１０時までは普通残業です。
午後１０時以降から深夜残業となります。
そこで例えば
午後１１時まで残業した場合その日の入力時間は「6」と入力します。
別のセルに「普通残業合計」「深夜残業合計」と分けて設定します。
それぞれに「普通残業合計」に「5」、「深夜残業合計」に「1」とそれぞれ自動に入るようにしたいわけです。
そしてそれぞれの残業時間も日々足ささっていくという設定にしたいのですが。
どのような関数をどのように設定すれば良いのか、詳しく教えていただきたいのですが。
なにとぞ宜しくお願い致します。

回答No.1

KODAMAR · ベストアンサー

残業時間は自分で手打ちでするんですよね？
時間も5時までが定時、と毎日決まっているんですよね？

総残業時間が入ったセルをA1
普通残業時間が入ったセルをB1
深夜残業時間が入ったセルをC1とします。

B1のセルには
=IF(A1>5,5,A1)
と入力すればでませんか？
C1のところには
=(A1-5)
ででませんか？

深夜残業のところに0が表示されるのがいやなら
=IF(B1<=4,"",(A1-B1))
で空欄になりますよ。

お礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
KODAMARさんの回答では一日一日振り分けられるのですがその合計が
出ませんでした。
例えば11月1日に「1」を入力するとそれぞれのセル「普通残業」には1、「深夜残業」には0と表示されるのですが11月2日に「5」と入力すると「普通残業」には５、「深夜残業」には1と表示されるのです。
実際その場合は普通残業には「6」深夜残業に「0」と表示されたいのです。
私の質問内容も今一分かりずらいみたいでした。
もう一度ちゃんと分かりやすいように質問しなおしたいと思います。
本当にありがとうございました。

回答No.6

yohsshi

(各行)A列　　 B列　　 C列　　　　　　D列
( 1行)11月1日 ６　　＝MAX(5，B1) ＝MIN(0，B1-5)
( 2行)11月2日　Ｘ 上のセルをコピー貼付け
　　（中略）
(30行)11月30日 Ｘ 上のセルをコピー貼付け
(31行) 合計 ＝SUM(C1:C30) =SUM(D1:D30)

ずれてしまいました。以下も参照してください
C1の数式 ： ＝MAX(5，B1)
D1の数式 ： ＝MIN(0，B1-5)
C30の数式 ： ＝SUM(C1:C30)
D30の数式 ： ＝SUM(D1:D30)

お礼

この場をお借りしまして今回回答してくれました皆様に再度お礼を申し上げます。
本当にありがとうございました。
私の説明内容の悪さで皆様には大変ご迷惑をおかけ致しました。本当に申し訳ございませんでした。
皆様の回答を見ていますと、きっとちゃんと説明できていたなら皆さん簡単に答えられるものだと思いました。
ただ、私が技量のないだけにまた再び質問させていただきます。
もし、また回答願えましたら宜しくお願い致します。

回答No.5

yohsshi

要は残業時間を入力（B列）すれば、自動的に普通残業（C列）と深夜残業（D列）に分けられれば良いのですよね

A列　　 B列　　 C列　　　　　　D列
(1行)11月1日 ６　　　＝MAX(5，B1) ＝MIN(0，B1-5)
(2行)11月2日　　Ｘ 上のセルをコピー貼付け
　　（中略）
(30行)11月30日 Ｘ 上のセルをコピー貼付け
(31行) 合計 ＝SUM(C1:C30) =SUM(D1:D30)

C列とD列は数式です。B列に残業時間を入力すれば、C31のセルに普通残業時間合計が、D31に深夜残業時間の合計がアウトプットされます。

これでいいと思いますが。

お礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
皆様には貴重なお時間を費やしてもらったにもかかわらず、私の質問内容が分かりずらかったです。
もう一度質問しなおしてみたいと思います。
本当にありがとうございました。

回答No.4

kon-ta

使う関数はIF関数がいいと思います（個人的に結構使うので）
以下はA1のセルにその日の残業時間がかかれている場合の関数です。

普通残業時間の列　=IF(A1<6,A1,5)
深夜残業時間の列　=IF(A1>5,A1-5,0)

お礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
私の質問内容が説明不足でした。本当に申し訳ございません。
もう一度分かりやすいように質問しなおしたいと思います。
回答ありがとうございました。

回答No.3

peso

普通残業時間の合計は
　　=SUMIF(残業時間の範囲,"<=5",残業時間の範囲)
　　　　　+COUNTIF(残業時間の範囲,">5")*5

深夜残業時間の合計は
　　=SUMIF(残業時間の範囲,">=5",残業時間の範囲)
　　　　　-COUNTIF(残業時間の範囲,">=5")*5

でどうでしょうか？
普通残業時間が確実に5以下だと仮定していますが・・・

お礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
pesoさんの回答は私の技量では解読不能でした。
何度やってみてもエラーが出てしまいまして、あまり詳しくないので数式を入力の際に例えば「、」の記号にしても「"」の記号にしても入力要領があるのでしょうか？
本当にありがとうございました。
もう一度質問しなおしてみたいと思います。

回答No.2

taranko

ＩＦを使ってデータを分けてやったらどうでしょうか。
Ａ１のセルに合計の残業時間が入っていて
Ｂ１に通常残業Ｃ１に深夜残業の時間を入れる場合です。

Ｂ１のセルに=IF(A1>5,5,A1)
Ｃ１のセルに=IF(A1>5,A1-5,0)
このような式を入れるとよいと思います。

式の説明は次の通りです。

Ｂ１はもしＡ１のセルが５以上ならＢ１に５を入れなさい。
違うのであればＡ１と同じ数値を入れなさい

Ｃ１はもしＡ１のセルが５以上ならＡ１から５を引いた数値を入れなさい
違うのであればゼロを入れなさい。

いかがなものでしょうか。

お礼

大変お礼が遅れまして申し訳ございませんでした。
やっと環境も落ち着き皆様からの回答参考に試してみました。
tarankoさんの回答も前の人と同様でした。
私の質問内容が分かりずらかったことを深くお詫びいたします。
本当にありがとうございました。