excel2003時間帯の値を返したい

質問

エクセル2003

各時間帯に何時間いたか、その個数を返したいのですがうまくできません。

例 データ：B列には出勤時間、C列には退社時間　Ｄ列には滞在時間
　 返すセル：I列には滞在時間が1時間ごとに、1行目には8～12、等
時間帯を入力済みで、J2:L32の間。

B2 8:25 C2 11:26 D2 3:01（他行には時間入力済み）
I2 1時間　J1 8～12 K1 12～19 L1 19～24と入力済み。

J2に　=COUNTIF($B:$B,">=8:00")-COUNTIF($B:$B,">=12:00")と入力
するとB列の8時～11時59分までの個数が返されます。

=COUNTIF($D:$D,">=0:00")-COUNTIF($D:$D,">=1:00")と入力すると
D列の1時間未満の個数が返されます。

そこで　=OR(COUNTIF($B:$B,">=8:00")-COUNTIF($B:$B,">=12:00"),COUNTI($D:$D,">=0:00")-COUNTIF($D:$D,">=1:00"))と入れてみましたが、TRUE　と返されてCOUNTIF
の
重複ではうまくいきません。　今回のような場合はどのように対
処すればよいのでしょうか？できれば関数で、できればと思います。

他調べても　SUMPRODUCT　などでてくるのですがよくわかりません。

仕事で至急作らなければならなくなりましたよろしくお願いします。

回答No.11

MackyNo1

>no3の方の　8～12部
=SUMPRODUCT(($B$2:$B$201*24>=8)*(($B$2:$B$201*24<12)*($D$2:$D$201*24>=ROW(L1)-1)*($D$2:$D$201*24<ROW(L1))))　

が求めていた結果になってはいるのですが（意味合いはイマイチ理解できていませんが・・）、0～8時を質問でつけていませんでしたのでここの部分が修正しきれていません。

0～8時の時間の入力が25：00のような形で入力していないなら、以下のように数字を変更するだけで表示できるはずです。

=SUMPRODUCT(($B$2:$B$201*24>=0)*(($B$2:$B$201*24<8)*($D$2:$D$201*24>=ROW(L1)-1)*($D$2:$D$201*24<ROW(L1))))　

ただし、この数式を使う場合はD列の数式を「=TEXT(C2-B2,"h:m")*1」にしておかないと、誤ってカウントしますので注意が必要です。

例えば、B2セルが「10:00」、C2セルが「11:00」のときD2セルは「1:00」になりますが、この「1：00」は手入力した「1：00」よりも小さい値になっていますので（もちろんわずかに大きな値になるケースもあります）、上記のような「以上」や「未満」を含む数式では、期待した答えが返らないケースがあるためです。

このような丸め誤差によるエラーを避けるには（手入力、数式での計算値、オートフィル入力での時間はすべて異なるデータになる可能性がありますが、これらを無視した計算式にするには）、以下のように微小値を加減して数式に取り込むような工夫をします(また、この方が配列の数が少なくなるので計算負荷も少なくなります）。

=SUMPRODUCT(($B$2:$B$201*24>=0)*(($B$2:$B$201*24<8)*(INT($D$2:$D$201*24-10^-10)=ROW(L1)-1))　

ちなみに、SUMPRODUCT関数の数式の意味は、B2:B201セルを24倍した値（24時間を１とする時間のシリアル値を「時間」単位で表した数字）が、0以上かつ8未満で、さらにD2:D201セルの「時間」表示の数字から10のマイナス１０乗という小さい値（0.0000000001）を引いた値の整数部分が０（ROW(L1)はL1セルの行番号＝１ですので右辺は０となります、その下のセルはROW(L2)が２ですので右辺は１となります）に等しい数の組み合わせをカウントする式になっています。

この仕組みを理解すれば、その他のデータのカウントもできます。

>Ｅ列に100円とびの金額を入力されているのですが（0～2000円）　
I列に金額が入力済み　I31 0 I32 100 I33 200 ～　I51　2000　となっています。
J30 には8～12　K30 には12～19　L30 には19～24　M30 には0～8となっています。
同じく　J31に8～12且つ　Ｅ列の０円の数を返したいのですが、できないでしょうか？

=SUMPRODUCT(($B$2:$B$201*24>=8)*(($B$2:$B$201*24<12)*($E$2:$E$201=I31))

回答No.10

kagakusuki

>Ｄ列の滞在時間が3：40なら　Ｉ列の4（時間）のところに反映させたいのです。（時間の位を繰り上げと同様になりなます）

　それでしたら、J2セルに入力する関数を次の様に修正して下さい。（勿論、I列に入力する数は1から始まる様に書き替えて下さい）

=SUMPRODUCT(($B$2:INDEX($B:$B,MATCH(99,$D:$D))-(LEFT(J$1,FIND("～",J$1)-1)&":00")>=0)*($B$2:INDEX($B:$B,MATCH(99,$D:$D))-(REPLACE(J$1,1,FIND("～",J$1),)&":00")<0)*(HOUR($D$2:INDEX($D:$D,MATCH(99,$D:$D))+"0:59:1")=$I2))

　ROUNDUP関数か、CEILING関数を使った方が楽なのですが、Excelの時刻を表すデータには微小な誤差が含まれていて、切り上げや切り捨てを行う際に、その誤差が悪さをする恐れが無いとは言えないため、上記の様な処理としています。

回答No.9

kagakusuki

回答番号ANo.7です。

>さっそくやってみたのですが、思ったような結果がでません。

　変ですね、回答を投稿する前に、ANo.7の添付画像の様に表を作成し、関数が実際に動作する事を確認した上で投稿致しましたので、結果が出ない筈はないのですが・・・・・
　確認したいのですが、D列に時間のデータが表示されているのは、2行目からでしょうか？
　それから、D列にはANo.7の添付画像の様に、時間と分で表された滞在時間（例えば5:59等）が表示されているのでしょうか？
　又、時間帯を指定するセル（J1～L1）には、
8～12
12～19
19～24
という様に、「半角数字で表した最初の時刻」＋「～」＋「半角数字で表した最後の時刻」という形式で入力されていて、「時」や「分」等の余計な文字（例えば空白や改行）は含まれていない様になっているでしょうか？

　もし、それらの条件に沿っていない場合には、条件に沿う様に、表を修正して下さい。

>上記の関数を　0～8　用にしていただけたら助かります。

　上記の条件に沿っている場合には、時間帯を指定するセルに
0～8
と入力するだけでOKです。

お礼

度々、ありがとうございます。
説明不足ですみません。

Ｉ2に0が入力されI3に1とＩ列に時間がはいいていますが、Ｉ2には1からスタートしたいのです。
Ｄ列の滞在時間が3：40なら　Ｉ列の4（時間）のところに反映させたいのです。（時間の位を繰り上げと同様になりなます）

現状のまま（画像を頂いたものをそのまま）入力してみましたが　0～8の０時間部（2行目に）190という値が返ってきます。　申し訳ありませんが0時間は不要状態です。（Ｄ列　0：40の場合　Ｉ列の　1　にカウントしたい）

ご迷惑かけます。よろしくお願いいたします。

回答No.8

KURUMITO

M1セルおよびＭ30セルにはには0:00から8:00の文字が入っているとします。
そこでＪ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I2="","",SUMPRODUCT(($D$2:$D$500>=TIME(ROW(A1)-1,0,0))*($D$2:$D$500<TIME(ROW(A1),0,0))*($B$2:$B$500>="8:00"*1)*($B$2:$B$500<"12:00"*1)))

Ｋ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I2="","",SUMPRODUCT(($D$2:$D$500>=TIME(ROW(A1)-1,0,0))*($D$2:$D$500<TIME(ROW(A1),0,0))*($B$2:$B$500>="12:00"*1)*($B$2:$B$500<"19:00"*1)))

Ｌ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I2="","",SUMPRODUCT(($D$2:$D$500>=TIME(ROW(A1)-1,0,0))*($D$2:$D$500<TIME(ROW(A1),0,0))*($B$2:$B$500>="19:00"*1)*($B$2:$B$500<"24:00"*1)))

Ｍ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I2="","",SUMPRODUCT(($D$2:$D$500>=TIME(ROW(A1)-1,0,0))*($D$2:$D$500<TIME(ROW(A1),0,0))*($B$2:$B$500>="0:00"*1)*($B$2:$B$500<"8:00"*1)*($B$2:$B$500<>"")))

さらにＩ３１には０、Ｉ３２には100、のように100ずつプラスされた数字が入力されているとしてＪ31セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I31="","",SUMPRODUCT(($E$2:$E$500>=(ROW(A1)-1)*100)*($E$2:$E$500<ROW(A1)*100)*($B$2:$B$500>="8:00"*1)*($B$2:$B$500<"12:00"*1)))

Ｋ31セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I31="","",SUMPRODUCT(($E$2:$E$500>=(ROW(A1)-1)*100)*($E$2:$E$500<ROW(A1)*100)*($B$2:$B$500>="12:00"*1)*($B$2:$B$500<"19:00"*1)))

Ｌ31セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I31="","",SUMPRODUCT(($E$2:$E$500>=(ROW(A1)-1)*100)*($E$2:$E$500<ROW(A1)*100)*($B$2:$B$500>="19:00"*1)*($B$2:$B$500<"24:00"*1)))

Ｍ31セルには次の式を入力して下方にオートフィルドラッグします。

=IF($I2="","",SUMPRODUCT(($E$2:$E$500>=(ROW(A1)-1)*100)*($E$2:$E$500<ROW(A1)*100)*($B$2:$B$500>="0:00"*1)*($B$2:$B$500<"8:00"*1)*($B$2:$B$500<>"")))

なお、滞在時間の式ですがＤ2セルには次の式を入力して下方にオートフィルドラッグしてはいかがでしょう。

=IF(COUNT(B2:C2)<>2,"",IF(C2>B2,C2-B2,C2+"24:00"-B2))

回答No.7

kagakusuki

次の様な関数をJ2セルに入力してから、J2セルをコピーして、J列～K列の2行目以下に貼り付けると良いと思います。（列ごとに関数を修正する必要は御座いません）

=SUMPRODUCT(($B$2:INDEX($B:$B,MATCH(99,$D:$D))-(LEFT(J$1,FIND("～",J$1)-1)&":00")>=0)*($B$2:INDEX($B:$B,MATCH(99,$D:$D))-(REPLACE(J$1,1,FIND("～",J$1),)&":00")<0)*(HOUR($D$2:INDEX($D:$D,MATCH(99,$D:$D)))=$I2))

　但し、時間帯を指定するセル（J1～L1）には、
8～12
12～19
19～24
という様に、「半角数字で表した最初の時刻」＋「～」＋「半角数字で表した最後の時刻」という形式で入力されていて、「時」や「分」等の余計な文字は含まれていない様にして下さい。

　それから、この関数は、D列に時間が入力されているセルの中で、最もし下の行あるセルの行番号を自動的に判断していますから、表の行数に制限は御座いません。（行数が増えて行っても気にしないで使用出来る）

お礼

ありがとございます。
さっそくやってみたのですが、思ったような結果がでません。

Ｎｏ3の方がつくってくれました、8～12部
=SUMPRODUCT(($B$2:$B$50*24>=8)*(($B$2:$B$50*24<12)*($D$2:$D$50*24>=ROW(J1)-1)*($D$2:$D$50*24<ROW(J1))))
がズバリの結果が出ています。

時間もないので（私の理解力がないのがいけないのですが）0～8部用に変更できればと思っています、
折角、立派なものを時間をかけて作っていただきましたが上記の関数を　0～8　用にしていただけたら助かります。
（上記関数でなくても0～8　がうまくできる内容なら構いません）お助けください。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

＞時間がなく、またあまりレイアウト変えたくないために（E列やＦ列など）
IR列より右を使用していないなら、
E:H列を選択し、右クリック、挿入 で４列挿入されます
その後、作業用の数式を入力し、４列分非表示にしておけば、
レイアウトは崩れません。

>こちらを使用させていただいています。（意味合いは後日調べます。）
>0～8時の時間帯がうまく修正できません。
回答に直接関係する(理解するための)質問なら歓迎しますが、丸投げ的な追加の質問はお断りします。
作業列を理解すれば、変更もたやすいはず。。。

B2*24 で時間単位に換算する。
B2:B50*24 49行1列の配列が返る。

日経PC21 / エクセル（Excel）「配列数式」講座
http://pc.nikkeibp.co.jp/pc21/special/hr/

配列数式が出来なくても、作業列で対処できます。

お礼

何分急いでいたもので理解（作業列もやってみたのですが0～8がわからず状態）するまだも時間がかかるもので教えていただけたらと思いまして質問させていただきました。
気分を害させてしまい申し訳ありません。

回答No.5

MackyNo1

いまひとつ質問内容が不明確です。

例えば、出勤時間が11:25、退社時間が 2１:00の時、どのような基準でカウントするのでしょうか？

8～12では0：35と1時間未満なのでカウントなし（すなわち、1時間単位で集計）、12～19 は7：00なので７時間のところに１カウント、19～24はちょうど2時間なので2時間のところに1カウントということでよいのでしょうか？

また確認ですが、もちろん滞在時間は集計に関係しないのですよね。

上記のような条件の場合は、かなり複雑な数式を駆使する必要がありますので、集計条件は、いくつかの具体例を交えて、できる限り正確に提出するようにしてください。

お礼

ありがとうございます。
分かりずらく申し訳ありません。

＞例えば、出勤時間が11:25、退社時間が2１:00の時、どのような基準でカウントするのでしょうか？
　出勤時間が11:25なので8～12でカウントします。　8～12　12～19 19～24 0～8は出勤してきた時間になります。（12：00に出勤してきたら12～19にカウントします）　

出勤時間が11:25、退社時間が2１:00なのでＤ列に9：35の滞在時間となりますので、（時間部分9が繰り上げになります）Ｉ列の10時間のところにカウントされます。（そういう意味で滞在時間は関係してきます）

Ｄ列の滞在時間は0～1時間未満時はＩ列の1のところに、滞在時間が2：35ならとI列の3にカウントされます。

Ｊ2には　8～12時未満までに出勤され、且つ、滞在時間が１時間未満の数を返したいのです。

no3の方の　8～12部
=SUMPRODUCT(($B$2:$B$201*24>=8)*(($B$2:$B$201*24<12)*($D$2:$D$201*24>=ROW(L1)-1)*($D$2:$D$201*24<ROW(L1))))　

が求めていた結果になってはいるのですが（意味合いはイマイチ理解できていませんが・・）、0～8時を質問でつけていませんでしたのでここの部分が修正しきれていません。（12～19、19～24はできました）

　宜しくお願いします。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃3です。当方も修正 m(_ _)m
F2セル 作業列2
=IF(($B2*24>=12)*($B2*24<19),$D2*24,"")
下へオートフィル
G2セル 作業列3
=IF(($B2*24>=19)*($B2*24<24),$D2*24,"")
下へオートフィル

ちなみに、複合参照は変更部分を少なくした結果です。
作業手順の効率化のためなので、相対参照で
=IF((B2*24>=8)*(B2*24<12),D2*24,"")
=IF((B2*24>=12)*(B2*24<19),D2*24,"")
=IF((B2*24>=19)*(B2*24<24),D2*24,"")
でもまったく問題ありません。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

>他調べても　SUMPRODUCT　などでてくるのですがよくわかりません。
配列の概念は難しいので、まず、作業列を使うことから覚えるとよいと思います。
また、配列を指定するとき、列全体を使うことができません。

D2セル 小数点誤差対策(念のため)
=TEXT(C2-B2,"h:m")*1
下へオートフィル

E2セル 作業列1
=IF(($B2*24>=8)*($B2*24<12),$D2*24,"")
下へオートフィル
=IF(AND($B2*24>=8,$B2*24<12),$D2*24,"")
と同じ意味です。同様に

F2セル 作業列2
=IF(AND($B2*24>=8,$B2*24<12),$D2*24,"")
下へオートフィル
G2セル 作業列3
=IF(AND($B2*24>=19,$B2*24<24),$D2*24,"")

J2セルは
=COUNTIF(E$2:E$50,">="&ROW(J1)-1)-COUNTIF(E$2:E$50,">="&ROW(J1))
となります。右へ下へオートフィル
ROW関数は行番号を返します

ちなみに、「8～12」のSUMPRODUCT関数を使った数式は J2セルに
=SUMPRODUCT(($B$2:$B$50*24>=8)*(($B$2:$B$50*24<12)*($D$2:$D$50*24>=ROW(J1)-1)*($D$2:$D$50*24<ROW(J1))))
下へオートフィル。
OR関数の使い方もままならない状態では、SUMPRODUCT関数は使わないほうが良いと思います。

お礼

早速ありがとうございます。

　時間がなく、またあまりレイアウト変えたくないために（E列やＦ列など）
=SUMPRODUCT(($B$2:$B$50*24>=8)*(($B$2:$B$50*24<12)*($D$2:$D$50*24>=ROW(J1)-1)*($D$2:$D$50*24<ROW(J1))))

こちらを使用させていただいています。（意味合いは後日調べます。）

0～8時の時間帯がうまく修正できません。
=SUMPRODUCT(($B$2:$B$50*24>=0)*(($B$2:$B$50*24<24)*($D$2:$D$50*24>=ROW(J1)-1)*($D$2:$D$50*24<ROW(J1))))
*24>=8部の　8を0　*24<12部の　12を8にしましたがかなり大きな値が返ってきます。（他12～20時部は同様に変更するとできました）

あと少し、助けていただけないでしょうか。　宜しくお願いします。

補足

後申し訳ありません、　Ｅ列に100円とびの金額を入力されているのですが（0～2000円）　
時間帯　8～12　12～19　19～24　0～8　は同じで

I列に金額が入力済み　I31 0 I32 100 I33 200 ～　I51　2000　となっています。
J30 には8～12　K30 には12～19　L30 には19～24　M30 には0～8となっています。

同じく　J31に8～12且つ　Ｅ列の０円の数を返したいのですが、できないでしょうか？
　　　　J32に8～12且つ　Ｅ列の100円　
　　　　M34に0～8且つ 　Ｅ列の300円など　　です。何度もすみません。

回答No.2

KURUMITO

回答Ｎｏ1です。次のように式を訂正してください。
Ｉ２セルへは1時間と入力しますが1時間の意味は1時間から2時間未満の意味をあらわしているとします。
そこでＪ2セルには例えば次の式を入力して下方にオートフィルドラッグします。

=SUMPRODUCT((HOUR(D$2:D$100)=ROW(A1))*(HOUR(B$2:B$100)>=8)*(HOUR(C$2:C$100)<12))

Ｋ2セルには次の式を入力して下方にオートフィルドラッグします。

=SUMPRODUCT((HOUR(D$2:D$100)=ROW(A1))*(HOUR(B$2:B$100)>=12)*(HOUR(C$2:C$100)<19))

Ｌ2セルには次の式を入力して下方にオートフィルドラッグします。

=SUMPRODUCT((HOUR(D$2:D$100)=ROW(A1))*(HOUR(B$2:B$100)>=19)*(HOUR(C$2:C$100)<24))

お礼

早速ありがとうございます。
＞Ｉ２セルへは1時間と入力しますが1時間の意味は1時間から2時間未満の意味をあらわしているとします。

説明不足ですみません。　0時から1時間未満を表します。

後、0～8という時間帯があるのですがそこはどのようにしたらよいのでしょうか？　とりあえず完成させないといけないものでご迷惑かけますが　宜しくお願いいたします。

補足

後申し訳ありません、　Ｅ列に100円とびの金額を入力されているのですが（0～2000円）　
時間帯　8～12　12～19　19～24　0～8　は同じで

I列に金額が入力済み　I31 0 I32 100 I33 200 ～　I51　2000　となっています。
J30 には8～12　K30 には12～19　L30 には19～24　M30 には0～8となっています。

同じく　J31に8～12且つ　Ｅ列の０円の数を返したいのですが、できないでしょうか？
　　　　J32に8～12且つ　Ｅ列の100円　
　　　　M34に0～8且つ 　Ｅ列の300円など　　です。何度もすみません。