☰

Excel で、膨大な数のタイムスタンプをカウントし、そのカウントを 5 分間隔で曜日順に並べるにはどうすればよいでしょうか。

tag-icon • tag-icon microsoft-excel worksheet-function
Excel で、膨大な数のタイムスタンプをカウントし、そのカウントを 5 分間隔で曜日順に並べるにはどうすればよいでしょうか。

スプレッドシートには 300,000 を超える行があり、各行にはタイムスタンプ (HH:MM:SS AM) と日付 (DD/MM/YYYY) が含まれています。

これらのタイムスタンプの数を取得し、5 分間隔で曜日ごとにグループ化したいと思います (つまり、日曜日の午前 12:00 から午前 12:05 まで、日曜日の午前 12:05 から午前 12:10 までなどのタイムスタンプの数を取得したいです)。COUNTIF何らかの方法で使用する必要があると感じていますが、実装方法を正確に把握できません。結果を次の図のような別のシートに整理しようとしています。

ここに画像の説明を入力してください

各 5 分間のカウントは、曜日の下の中央の列に入力されます (そのため、そこに数式を入力します)。

ご協力いただければ幸いです、本当に感謝いたします!

編集: 私のデータは次のようになることを記載すべきだったかもしれません:

ここに画像の説明を入力してください

答え1

データにいくつかのフィールドを追加し、ピボット テーブルを生成してカウントを取得できます。

まず、曜日 ( DOW) フィールドを追加します。取得するには、列に日付が含まれるDOW以下の数式を使用します。B

=CHOOSE(WEEKDAY(B2,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

次に、タイムスタンプが含まれる 5 分間の期間を表すフィールドを追加します。Time Rangeタイムスタンプが列にある場合、以下の数式を使用してこれを計算しますA。

=TIME(HOUR(A2),ROUNDDOWN(MINUTE(A2)/5,0)*5,0)

これらの列をデータに追加したら、すべてのデータをデータ ソースとして使用するピボット テーブルを作成します。

ピボット テーブルをDOW列ラベルとして 、Time Range行ラベルとして で設定します。 値を に設定しますCount of Time Range。

ここに画像の説明を入力してください

注: このサンプルは、ピボット テーブルを生成するためにスパース データ (1 週間で 20 件のレコード) を使用したため、少し奇妙に見えます。大規模なデータ セットでは、想定しているものに近いものになるはずです。

答え2

@Excellll が推奨したのと同様に、曜日を指定する列を追加することができます。つまり、生データが存在し、列 A と列 B にそれぞれ日付と時刻が格納されているシート 1 に、列 C でこの関数を使用できます。

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",WEEKDAY(A1))

私が使用しているアプローチは、純粋に関数に基づいています。共有された画像のように、シート 2 の数式をカスタマイズして、同様の構造を生成しました。次の条件は、タイムスタンプがテーブルの最初の列で宣言されている特定の範囲に存在すること、さらにそれが特定の曜日であることを確認します。次に、そのような値をすべてカウントします。

=COUNTIFS(Sheet1!$B:$B, ">"&$A3,Sheet1!$B:$B,"<="&$A4,Sheet1!$C:$C,"="&B$2)

画像は、上記の数式がセル B1 に入力され、時刻が午前 12:00 から午前 12:10 の範囲内にあり、日が日曜日または 1 に設定されていることを確認する条件が含まれている様子を示しています。

ここに画像の説明を入力してください

ここでの時間差は 10 分であることに気づいたかもしれませんが、カスタマイズ可能にしたので、必要に応じて 5 分または 2 時間に設定できます。

ここでは関数を使用しているため、データに 4 つの空白行があっても 10 の空白行があっても問題ありません。関数は、空白でないデータのみが収集されるようにします。データが十分でなかったため、例はかなり空っぽに見えます。添付の​​サンプル xls をご覧ください。ここ。

答え3

注: 今晩このコードのトラブルシューティングを行う予定ですが、値のリストを下に移動するときにまだエラーが残っています。現在は動作しませんが、コード行を再フォーマットしたくないだけです。自由に試してみてください。コメントと手順を同時に追加します。

誰かが、これは VBA でできると言っていました。私は VBA が好きなので、試しにやってみました。

いくつかの仮定:

1 - タイムスタンプと日付が同じセルに含まれていません (例: 12:00:00 AM | 7/21/2014 ではなく 7/21/2014 00:00:00)

2 - すべてのタイムスタンプのカウントを単一の日のリスト内にグループ化します (つまり、日曜日から土曜日までの 1 セットのみを表示し、追加の日ごとに新しい列セットを作成しません。金曜日に開始した場合、金曜日にグループ化は開始されません。また、2 週間後の火曜日に終了した場合、グループ化の列は 16 列にはなりません)。

3 - 最初の行から最後の行まで、データに null セルはありません。

4 - タイムスタンプと日付スタンプのデータにはヘッダーがあります

Alt+F11 キーを押して、データとソース ワークシートがあるブックを開き、このコードを入力します。次に を押しますF5。

Public Sub PrintDateGroups()
Dim icontrol As Integer
Dim iweeknum As Integer
Dim ipasscount As Integer
Dim lngwalktimevalues As Long
Dim ipasstimecount As Integer
icontrol = 1

Do Until icontrol = -1:
If ThisWorkbook.Sheets("Data").Cells(icontrol,2).Value = "" Then
    icontrol = -1

Else

    icontrol = icontrol + 1
    iweeknum = Weekday(ThisWorkbook.Sheets("Data").Cells(icontrol, 1).Value, vbSunday)

    For lngwalktimevalues = 0 To 99999999 Step 694444.4375
        If (TimeValue(Format(ThisWorkbook.Sheets("Data").Cells(icontrol, 2).Value, "hh:mm:ss")) * 100000000) <= lngwalktimevalues Then
            If iweeknum = 1 Then
                If ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value <> "" Then
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, 2).Value + 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = 1
                    Exit For
                End If
            Else
                If ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = "" Then
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, ((3 * iweeknum) + (iweeknum - 2))).Value + 1
                    Exit For
                End If
            End If
        End If
    Next lngwalktimevalues
End If
Loop
End Sub

答え4

各レコードを最も近い 5 分に丸めることができるはずです。次に、Excel の小計関数を使用してカウントを取得します。

日付の新しい列 (=Day(A1)) とタイムスタンプの 2 番目の列を作成して丸めを行います。

関連情報