平日と土日を分類し、時間帯別の件数をカウントする関数について

A列に画像のような、yyyy/mm/dd hh:mm というデータが
現在500～600あり、今後も増え続けます。
このA列の日時を、平日と土日に分けて、時間帯別の件数を
カウントする関数を教えていただけないでしょうか。

また、可能であれば、祝日を土日カウントする方法もあれば
教えてください。

No.2 ベストアンサー 回答者： tom04 回答日時： 2019/11/16 00:14

こんばんは！

画像の配置通りだと
祝日を考慮しない場合は
E2セルに
=SUMPRODUCT((WEEKDAY(A$2:A$1000,2)<6)*(HOUR(A$2:A$1000)=ROW(A1)-1))

H2セルに
=SUMPRODUCT((A$2:A$1000>0)*(WEEKDAY(A$2:A$1000,2)>5)*(HOUR(A$2:A$1000)=ROW(A1)-1))

という数式を入れフィルハンドルで下へコピーしてみてください。

祝日を考慮する場合は結構厄介なので、作業用の列を1列設けてみてはどうでしょうか？
Sheet2のA列に祝日データがシリアル値で作成してあるとします。

作業列B2セルに
=IF(A2<>"",(WEEKDAY(A2,2)>5)+(COUNTIF(Sheet2!A:A,INT(A2))),"")

という数式を入れこれ以上データはない！というくらいまで下へコピーしておきます。
E2セルに
=SUMPRODUCT((A$2:A$1000>0)*(B$2:B$1000=0)*(HOUR(A$2:A$1000)=ROW(A1)-1))

H2セルに
=SUMPRODUCT((A$2:A$1000>0)*(B$2:B$1000>0)*(HOUR(A$2:A$1000)=ROW(A1)-1))

という数式を入れフィルハンドルで下へコピー！

※　セル範囲を指定する場合は文字列セル（項目行など）は範囲に入れないようにしてください。
（SUMPRODUCT関数で範囲内に文字列セルがあるとエラーになります）

※　作業列が目障りであれば、遠く離れた列にするか
非表示にしてください。m(_ _)m

この回答へのお礼

ありがとうございます！！！
教えてもらった関数は、9割理解できませんが、
カウントできました。
祝日については、明日職場で試してみます。
本当にありがとうございます。助かりました。

お礼日時：2019/11/16 00:23

No.5 回答者： GooUserラック 回答日時： 2019/11/16 03:14

マクロで作ってみました。

６万行で２秒位で完了です。

☆ 事前準備
私のＨＰ（ https://luckshp.web.fc2.com/index.html ）から「holiday_list.zip」をダウンロードして解凍して得られた「祝日一覧.csv」をこのマクロを書き込んだファイルと同じフォルダに置いて下さい。
※ レイアウトが違うと誤動作します

☆ コード
Sub Sample()
　Dim 祝日データ　As String
　Dim 日付 As Date
　Dim 祝日名 As String
　Dim 表 As Variant
　Dim 位置 As Long
　Dim 行 As Long
　Dim 横 As Long
　Dim 祝日 As Object
　Range("E2:E25,H2:H25").ClearContents
　Set 祝日 = CreateObject("Scripting.Dictionary")
　Open ThisWorkbook.Path & "\祝日一覧.csv" For Input As #1
　　Do Until EOF(1)
　　　Line Input #1, 祝日データ
　　　日付 = CDate(Left(祝日データ, 10))
　　　祝日名 = Mid(祝日データ, 12)
　　　If 祝日.Exists(日付) = False Then 祝日.Add 日付, 祝日名
　　Loop
　Close #1
　表 = Range(Cells(2, 4), Cells(25, 8))
　For 行 = 2 To Cells(Rows.Count, 1).End(xlUp).Row
　　日付 = Int(Cells(行, 1).Value)
　　位置 = Format(Cells(行, 1).Value, "h") + 1
　　If 祝日.Exists(日付) Then
　　　表(位置, 2) = 表(位置, 2) + 1
　　Else
　　　If 日付 Mod 7 <= 1 Then
　　　　表(位置, 5) = 表(位置, 5) + 1
　　　Else
　　　　表(位置, 2) = 表(位置, 2) + 1
　　　End If
　　End If
　Next
　Range(Cells(2, 4), Cells(25, 8)) = 表
　Set 祝日 = Nothing
End Sub

No.4 回答者： GooUserラック 回答日時： 2019/11/16 01:45

マクロ（ＶＢＡ）を使っても良いですか？

なお祝日データですが、私のＨＰに置いてありますので必要ならダウンロードして下さい
ＵＲＬ「 https://luckshp.web.fc2.com/index.html 」の左側にある「祝日名一覧」からダウンロードして下さい。

No.3 回答者： mike_g 回答日時： 2019/11/16 00:46

セル A6 は平日でも土日でもないけど、どうするどうする？

No.1 回答者： GooUserラック 回答日時： 2019/11/15 23:51

エクセルのバージョンは何でしょうか？

祝日をカウントしたいなら祝日データが必要になります。

この回答へのお礼

自宅のエクセルは2016です。職場のエクセルは2013だったと思います。

お礼日時：2019/11/15 23:58