Excel（エクセル）活用コミュの時間の振り分け　?

またまたすみません。
前回皆様に教えて頂いたことを私のできる範囲で思考錯誤して
考えてみましたが、どうもいけません。

条件も多くなってしまったのですが、使うべき関数や何か良いアイディアが
ありましたら教えて頂けますでしょうか。

具体的に作りたい勤務表を添付してみました。
詳細は入れたい条件みたいなものです。

まずG3とH3の関数を入れてみました。

G列には月～金の5:00～9:00
　　　月～金の17:30～21:00
H列には月～金の0:00～5:00
　　　月～金の21:00～24:00

G3には
=IF(C3=1,IF(P3<"60:00"*1,IF(D3<"21:00"*1,
IF(E3<"21:00"*1,E3-D3,"21:00"*1-D3),""),""),"")

を入れたのですが、
=IF(D2>"5:00"*1,IF(E2<"5:00"*1,E2-D2,"5:00"*1-D2),"")

も加えたいのですが、OR関数を使ったらよいのでしょうか？
関数自体も合っているか自信がありません。

H3には下記のを入れてみましたが、６日のような時間帯だとエラーになります。

=IF(C3=1,IF(G3=F3,"",F3-G3),"")

宜しくお願いします。

コメント(14)

最初
全て
最新の40件

[1] mixiユーザー 09月11日 22:59

やりたいことを数式ではなく日本語で書いたほうがいいと思いますがいかが？

[2] mixiユーザー 09月11日 23:13

IFやORを何階層にも使っていますが、それぞれが機能しているか検証していますか？
一番内側の階層から機能しているかをチェックして、一階層ずつ増やしていますか？

ココでアドバイスくれるベテランさんは、もう慣れているから直ぐに出来るかも知れませんが、旨く行かない時は分解して、それぞれを確認する事もしていると思いますよ。

そして質問する際は「エラーになります」ではなく、具体的にどんなエラーが出るかを書けば直ぐに判るかもしれませんし。　相談を受けた方は何のエラーなのかを試さないといけないですからね。
　

[3] mixiユーザー 09月11日 23:18

わかりずらいですよね。すみません。

まず、G３に入れたいのは
平日で月の残業時間が60時間以下。
月～金の5:00～9:00 、月～金の17:30～21:00 に
働いた場合の時間数。

H３に入れたいのは

平日で月の残業時間が60時間以下。
月～金の0:00～5:00、　月～金の21:00～24:00　に
働いた場合の時間数。

C列には月～金を示す　1　を入れてあります。
D列は残業開始時間
E列は残業終了時間
P列には月の残業時間の累計

という感じなのですが、この説明でご理解頂けるでしょうか。

[4] mixiユーザー 09月11日 23:36

If関数を何層にもネストさせないといけない場合、もちろん一層一層数式をチェックする必要があるのは間違いありません。

でも、得てしてそういう時は、エクセル関数の問題というよりも、先ず論理のステップがちゃんとできているかという問題のほうが多いように思います。

ですから先ずすべきは論理の流れを日本語を使って整理することだと思います。

３
>という感じなのですが、この説明でご理解頂けるでしょうか。
ご自身が他人になったつもりで読み返し、そのように書いた背景なりを考え直してみるのも悪くないと思います。（決して、お書きになったことがヘンだから、というわけではありません。）

If関数の場合、いきなりエクセルのシートに向かうよりは、こうして日本語を使って、また紙とペンを片手に、論理の進め方を書き出す作業のほうが、結局は近道だと思います。わたしがよくするのは、フローチャート化ですとか、図解化です。

あせらずかんばってね！！

[5] mixiユーザー 09月12日 00:01

八神かかしさんと同じ意見ですが、

エクセルがないとして、紙と鉛筆と電卓でどんな手順で計算しますか？
それを書いて整理すると解決すると思いますよ。

maviさんが、うまくいかないと悩んでいるのはエクセルの使い方がわからないのではなく、計算手順の整理ができていないためだと思います。

[7] mixiユーザー 09月12日 03:35

まずはやりたいことを整理してANDとORが必要なところを整理してみましょう。
IFを使う前にまずはそこからです。
例えばG列の関数
=IF(C3=1,IF(P3<"60:00"*1,IF(D3<"21:00"*1,　～以下略～
この部分ですがC3が1、残業数が60以下の場合としてるのでここは
ANDを使えばIF文が一つ省略できます。
そして5:00～9:00 、17:30～21:00の残業時間数を計算する個所は
例として挙げると
IF(開始時間 < 9:00,9:00前の残業時間,0)-
IF(開始時間 <= 5:00,5:00前の残業時間,0)+
IF(終了時間 > 17:30,17:30後残業時間,0)-
IF(終了時間 <= 21:00,21:00後の残業時間,0)
解説すると5:00より前と21:00より後の残業時間を時間外時間から引いてます。
数式にすると以下のような感じです。
=IF((C3=1)*(P3<60),
　IF(D3<TIME(9,0,0),TIME(9,0,0)-D3,0)-
　IF(D3<=TIME(5,0,0),TIME(5,0,0)-D3,0)+
　IF(E3>TIME(17,30,0),E3-TIME(17,30,0),0)-
　IF(E3>=TIME(21,0,0),E3-TIME(21,0,0),0),
0)
こんな感でいかがでしょうか？
他にももっといいやり方があるかもしれませんが、
これが１番単純な方法かと思います。
IF関数は全部いっぺんに考えようとするとごちゃごちゃするので
まず一つずつ処理を考えていくといいと思います。
あとH列のエラーですが、24:00をその日の0:00と認識しており
時刻の形式でマイナスの値になっているから出ているエラーと思われます。
表示形式を時刻にして24:00と入力すると自動で0:00になりませんか？

[8] mixiユーザー 09月12日 21:40

ミキスタさん　fenwickさん　八神かかしさん

皆さん、一様に同じように言っているようですね。
入れたい項目を表にしてみたのですが、そういうことではなく
日本語で式を書いていくことの必要性がわかりました。
IF関数も最初のカッコと後のカッコの数が同じ位しか数えず、
ひとつひとつを検証しませんでした。
（もしこれならば、これ、そうでなければ・・とブツブル言いながらは
　　やっていましたが。）
急がばまわれ、ですね。ありがとうございます。

qyukip　さん

ありがとうございます。qyukip　さんのコメントをみて
日本語で式を書いていく大切さがわかりました。
そもそも、IF関数どうしを足したり引いたりってことは思いつきませんでした。
IFでネスト、ネストするより、わかりやすいですね。

ただ、わからないのが、最初の　(C3=1)*(P3<60)
ここはANDを使うべき所ですが、なぜ上記のようになるのですか？
「エクセル、関数、＊」で検索してもわかりませんでした。

もうひとつ。
「H列のエラーですが、24:00をその日の0:00と認識している」
とのことですが、表示形式を時刻にすると0:00になりました。
24:00と表示させるにはVALUE関数を使えば良いとまではわかったのですが
残業終了時間のE列にどのように反映すれば良いのでしょうか。

調べて見たのですが、どうもドンピシャリがありません。
どうぞ、宜しくお願いします。

[10] mixiユーザー 09月13日 03:13

G列は空白になる場合があり、H8は、空白を計算しようとしているのでエラーと
なっているようです。
空白の場合は無視(0として計算)する1つの方法として、SUM関数を使いましょう。

H3=IF((C3=1)*(G3<F3),F3-SUM(G3),"")

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/if_is.htm
【余談】AND関数を使用しない方法：論理演算子（論理積「*」）を使用する方法
【余談】OR関数を使用しない方法：論理演算子（論理和「+」）を使用する方法

[11] mixiユーザー 09月13日 05:58

複雑な式をそのまま関数式にすると、可読性も低下して、何をやりたいのか
読み取れない場合が多くなるのが難点ですね。今回のケースならば、VBAで
ユーザー定義関数を作って、ワークシートからそれを呼び出すようにしても
すっきり記述できるように思います。

ところで例として挙がっているシートに気になった点があるので
以下に疑問点を書きます。(私が表の意味を誤解しているだけかも
知れませんが…)

添付画像にあるシートのD列：開始時間、およびE列：終了時間は、
全て平日は17:30以降のものとなっていますね。このことから、この欄は
所定労働分を含む労働開始/終了時刻ではなく、時間外労働の開始/終了時刻を
入力するものと読み取れますが、その認識は合っていますか？

その場合、仮に1日目に6:30～21:00まで勤務した場合は、時間外にあたる部分が
1日に2箇所発生する事になりますが、どのようにこの表に入力するのでしょうか？
方法としてはいくつか考えられますが、運用時の入力の手間を考えると
(3)のやり方が楽そうです。

　(1)1日を5:00～9:00の時間外労働分と17:30～の時間外労働分との
　　2行に分けて入力する。

　(2)新たに5:00～9:00までの時間外労働分を入力するための列を設ける。

　(3)D列、E列は時間外労働の開始/終了時刻ではなく、所定労働分も含む
　　労働の開始/終了時刻を入力する欄とする。

余計なお世話かも知れませんが、各列にどのような値域のデータが
入力され得るかを明確に定義する事も帳票設計では大事な事だと思います。

1つの表にする事が必須でなければ、平日、休日、祝日はそれぞれ独立した
ルールが適用されるので別々の表に入力した方が考え方は楽になりそうですね。
私には、1つの表で無理やり表現しようとして余計な苦労をしているように
思えてしまいます…。

[12] mixiユーザー 09月13日 21:36

たつや.com　さん

ありがとうございます。
教えて頂いた式をH列に当てはめ、じっくり理解をしていこうと思います。
リンクを張り付けてくださった所も勉強します。
きっとまた、質問がでると思いますので、お時間のある時に見てみてください。
宜しくお願いします。

Minon　さん

コメントありがとうございます。

D列、E列は時間外労働の開始/終了時刻を入力するもので間違いないです。

この表は既存のもので、残業時間のみ（D,E列のみ）手書き記入されたものを
他部署が手書きで表を完成させ、２回目のチェックをするのが私の仕事です。
仕様を変更するのは、難しいです。

手書きの勤務表が主となるので、これは検証用になります。
なので、列の追加は可能ですが、削除、変更は無理です。
（１行目の☆、△は私が非表示にしたりして使うつもりで追加した列です。）

あと、表の例が悪かったのですが、
３列目以降は全て空欄です。
通常勤務時間が9:00～17:30なので
平日の２日（月）に時間外（通常勤務時間以外）があったらその
時間帯を書きます。
同じ日の前後にあったら
4:00～9:00
17:30～22:00
と２行に分けて書いてきます。

土、日曜、祝日は働いた時間のみを
6:00～10:00
11:00～13:00
と細かく２行にも３行にも分けて書いてきます。

今日も一日会社でこの表とにらめっこ。
完成までにはまだまだかかりそうです。

ちなみにアドバイスにあります、
VBAでユーザー定義関数を作って、ワークシートからそれを呼び出す
というのは私には無理です。

またお時間のある時に見て頂き、アドバイス頂けたら助かります。
宜しくお願いします。

[13] mixiユーザー 09月13日 22:19

補足説明有難うございます。
D、E列は時間外労働の開始および終了するものであり、その労働区間が1日のうち
複数個所に発生する場合は行方向に分けて記入する方式であること、この入力方式は
既定のもので変更できない事、了解しました。

こういう情報によって、ある1行のデータは1日に複数あり得る時間外労働となる
時間区分のうち、どれか1つに必ず所属する事が分かったので、大きなヒントに
なります。(これにより数式を多少簡略化できる可能性があります)

＞VBAでユーザー定義関数を作って、ワークシートからそれを呼び出す
＞というのは私には無理です。

たぶん、お手本みたいなのを一度見れば、関数式をいじるのと大差無い手間で
簡単なものなら書けるようになると思います。
お急ぎでなければ、私の方でも時間が取れたら試しに書いてみたいと思います。
(他の回答者さんの方が対応が早そうですけど…^^;)

[14] mixiユーザー 09月14日 21:26