時間別カウント

質問

エクセルで、別のセルに入った日付データと時間データをひとつのセルに表示させ1時間ごとに時間別にカウントをしたいのですがどのようにすればいいのでしょうか

例
「2002/9/1」「8：44:33」 ⇒ 「2002/9/1 8：44:33」
「2002/9/1」「8：52:33」 ⇒ 「2002/9/1 8：52:33」
「2002/9/1」「9：44:33」 ⇒ 「2002/9/1 9：44:33」
「2002/9/1」「9：44:34」 ⇒ 「2002/9/1 9：44:34」
「2002/9/1」「9：44:35」 ⇒ 「2002/9/1 9：44:35」
「2002/9/1」「10：44:33」 ⇒ 「2002/9/1 10：44:33」
「2002/9/2」「8：44:33」 ⇒ 「2002/9/1 8：44:33」

「2002/9/1 8：00」⇒2
「2002/9/1 9：00」⇒3
「2002/9/1 10：00」⇒1
「2002/9/2 8：00」⇒2
なお日付や時間は毎回違うので固定しないでほしいです。

回答No.4

bunjii

回答No.3の追加です。
D列とF列の値を次のように算出すればシリアル値になりますので、G列の計数をCOUNTIFS関数で可能になります。
D2=A2+B2
F2=TEXT(D2,"yyyy/m/d h")&":00"*1
G2=IF(F2="","",COUNTIFS(D$2:D$8,">="&F2,D$2:D$8,IF(F3="","<"&"9999/12/31 23:59:59"*1,"<"&F3)))

回答No.3

bunjii

>エクセルで、別のセルに入った日付データと時間データをひとつのセルに表示
用語を確認してからどのように数式を組み立てるかを決めたいと思います。
「時間」は「時刻１」と「時刻２」の間を言いますので、あなたが提示している時間とは「時刻」として良いでしょうか？
「2002/9/1」「8：44:33」 ⇒ 「2002/9/1 8：44:33」の処理は文字列の連結を使います。
A2=2002/9/1
B2= 8:44:33
D2=A2&" "&B2
「2002/9/1 8：00」を検索値としてF列に取り出すときは次のようにすると良いでしょう。
F2=TEXT(B2*1,"yyyy/m/d h")&":00"
F2セルを下へ必要数コピーします。
F列を選択してコピーでクリップボードへ記憶させ、同じセルへ値のみを貼り付けます。
次にF列を選択して状態でデータの「重複の削除」を行えば単一データに絞られます。
時間帯毎にカウントするにはCOUNTIFS関数がで良いと思われますが対象データが文字列のため数値化の時点でエラーが起りますので、SUMPRODUCT関数を使います。
G2=IF(F3="",SUMPRODUCT((D$2:D$8*1>=F2*1)*1,(D$2:D$8*1<"9999/12/31 23:59:59"*1)*1),SUMPRODUCT((D$2:D$8*1>=F2*1)*1,(D$2:D$8*1<F3*1)*1))
G2セルを下へ必要数コピーして完成です。
但し、データの行数を提示の模擬データの範囲にしてありますので、実際のデータ数に合わせて数式の修正を行ってください。

>「2002/9/2」「8：44:33」 ⇒ 「2002/9/1 8：44:33」
>「2002/9/2 8：00」⇒2
これは誤りのようですね。
「2002/9/2」「8：44:33」 ⇒ 「2002/9/2 8：44:33」
「2002/9/2 8：00」⇒1

回答No.2

kagakusuki

Excelでは本当の日時のデータであろうと、「日時のデータに見える文字列データ」であろうと、どちらの場合であっても単純に足し算をしてしまう事で日付データと時間データをまとめてしまう事が出来ます。
　只、足し算をするようにしただけでは、万が一、日時のデータとして扱う事が出来ない文字列データが入力されていた場合にはエラーとなってしまいますので、一方が日付のデータであり、もう一方が時刻のデータである事を判定して、日時のデータではない場合には何も表示しない様にするための判定方法が複雑になります。
　今仮に、

　　　　　　A列　　　　　　B列
1行目　　2002/9/1　　　8:44:33
2行目　　2002/9/1　　　8:52:33
3行目　　2002/9/1　　　9:44:33
4行目　　2002/9/1　　　9:44:34
5行目　　2002/9/1　　　9:44:35
6行目　　2002/9/1　　 10:44:33
7行目　　2002/9/2　　　8:44:33

という具合に元データが並んでいるものとします。
　そして、それらのデータを基にして、同じ行のC列に

2002/9/1 8:44:33
2002/9/1 8:52:33
2002/9/1 9:44:33
2002/9/1 9:44:34
2002/9/1 9:44:35
2002/9/1 10:44:33
2002/9/2 8:44:33

等の日時を合わせたデータを表示させるものとします。
　又、

2002/9/1 8:00　　2
2002/9/1 9:00　　3
2002/9/1 10:00　 1
2002/9/2 8:00　　1

等の時間帯とカウント結果はE列とF列の1行目以下に表示するものとします。

　まず、C1セルに次の関数を入力して下さい。

=IF(AND(OR(ISNUMBER(1/($A1=INT($A1))/DAY($A1)/(CELL("format",$A1)="D1")),ISNUMBER(1/(TEXT($A1+0,"yyyy/m/d")=$A1))),OR(ISNUMBER(1/($B1=MOD($B1,1))/((CELL("format",$B1)="D8")+(CELL("format",$B1)="D9"))),ISNUMBER(1/(TEXT($B1+0,"h:mm:ss")=$B1)),ISNUMBER(1/(TEXT($B1+0,"h:mm")=$B1)))),$A1+$B1,"")

　次に、C1セルの書式設定の表示形式を[ユーザー定義]の

yyyy/m/d h:mm:ss

にして下さい。
　次に、C1セルをコピーして、C2以下に貼り付けて下さい。

　次に、E1セルに次の関数を入力して下さい。

=IF(COUNT($C:$C),(TEXT(MIN($C:$C),"yyyy/m/d h")&":00")+0,"")

　次に、E2セルに次の関数を入力して下さい。

=IF(COUNTIF($C:$C,">"&E1+"1:00"-0.000001),(TEXT(LARGE($C:$C,COUNTIF($C:$C,">"&E1+"1:00"-0.000001)),"yyyy/m/d h")&":00")+0,"")

　次に、F1セルに次の関数を入力して下さい。

=IF(ISNUMBER($E1),COUNTIFS($C:$C,">"&$E1-0.000001,$C:$C,"<"&$E1+"1:00"-0.000001),"")

　次に、E1セルとE2セルセルの書式設定の表示形式を[ユーザー定義]の

yyyy/m/d h:mm

にして下さい。
　次に、F1セルをコピーしてF2セルに貼り付けて下さい。
　次に、E2～F2のセル範囲をまとめてコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　以上です。

回答No.1

okormazd

まず、例として挙げられているデータがめちゃくちゃです。だから、そのとおりなら合理的な処理は困難です。
常識的な処理としてたとえば8時台にはいくつかということなら、次のようにする。
1.
「2002/9/1 8:44:33」などのデータが文字列ではなく、日付時刻「yyyy/m/d h:m:s」として正しく設定されていること。
2.
「2002/9/1 8:00」などのデータが文字列ではなく、日付時刻「yyyy/m/d h:mm」として正しく設定されていること。
3.
たとえば、もとのデータが、A2:A8に入力されていて、「2002/9/1 8:00」の右側セルにカウント数をだしたければ、そのセルに「=COUNTIFS($A$2:$A$8,">="&A11,$A$2:$A$8,"<"&A11+1/24)」という数式を入力する。
4.
後は、フィルドラッグです。
5.
「日付データと時間データをひとつのセルに表示させ」たとき、日付時刻「yyyy/m/d h:m:s」として正しく設定されないということなら、カウントとは別のことだから、あらためて質問してください。

添付参照。