☰

Excel에서 엄청난 수의 타임스탬프를 계산하고 요일별로 정렬하여 5분 단위로 계산을 정렬하는 방법은 무엇입니까?

tag-icon • tag-icon microsoft-excel worksheet-function
Excel에서 엄청난 수의 타임스탬프를 계산하고 요일별로 정렬하여 5분 단위로 계산을 정렬하는 방법은 무엇입니까?

스프레드시트에는 300,000개가 조금 넘는 행이 있고 각 행에는 타임스탬프(HH:MM:SS AM)와 날짜(DD/MM/YYYY)가 포함되어 있습니다.

이 타임스탬프의 수를 확인하고 요일별로 5분 단위로 그룹화하고 싶습니다(즉, 일요일 오전 12시부터 오전 12시 5분 사이, 일요일 12시 5분부터 일요일 오전 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.

여기에 이미지 설명을 입력하세요

참고: 피벗 테이블을 생성하기 위해 희소 데이터(일주일에 20개 레코드)를 사용했기 때문에 이 샘플은 약간 이상해 보입니다. 이는 대규모 데이터 세트를 통해 염두에 두고 있는 것과 더 비슷해 보일 것입니다.

답변2

@Excellll이 권장하는 것과 유사하게 요일을 지정하는 추가 열을 추가할 수 있습니다. 즉, 원시 데이터가 있고 A열과 B열이 각각 날짜와 시간을 보유하는 시트 1에 C열에서 이 함수를 사용할 수 있습니다.

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

내가 사용하는 접근 방식은 순전히 기능을 기반으로 합니다. 공유해 주신 이미지와 같이 유사한 구조를 생성하기 위해 시트 2에 대한 수식을 사용자 정의했습니다. 다음 조건은 테이블의 첫 번째 열에 선언된 특정 범위에 타임스탬프가 있는지 확인하고 추가로 해당 요일이 지정된 요일인지 확인합니다. 그런 다음 그러한 모든 값을 계산합니다.

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

이미지는 위 수식이 셀 B1에 입력되는 방법을 보여 주며 시간이 오전 12시부터 오전 12시 10분까지 범위에 있고 요일이 일요일 또는 1로 설정되어 있는지 확인하는 조건이 있습니다.

여기에 이미지 설명을 입력하세요

여기의 시차는 10분이라는 것을 아셨을 수도 있겠지만, 저는 이를 사용자 정의 가능하게 만들었으므로 요구 사항에 따라 5분 또는 2시간으로 설정할 수 있습니다.

여기서는 함수를 사용하고 있으므로 데이터에 4개의 빈 행이 있는지 아니면 그 사이에 10개의 빈 행이 있는지는 중요하지 않습니다. 이 기능은 공백이 아닌 데이터만 수집되도록 합니다. 데이터가 충분하지 않아 예제가 꽤 비어 보입니다. 첨부된 샘플 xls를 확인하세요.여기.

답변3

참고 - 오늘 저녁에 이 코드 문제를 해결할 예정이지만 값 목록 아래로 이동하는 동안 여전히 일부 오류가 있습니다. 현재 실행 가능하지 않습니다. 코드 줄의 형식을 다시 지정하고 싶지 않습니다. 자유롭게 놀아보세요. 댓글과 안내사항을 동시에 추가하겠습니다.

누군가 VBA를 사용하면 이 작업을 수행할 수 있다고 언급했습니다. 저는 VBA를 좋아해서 한번 봤습니다.

몇 가지 가정:

1 - 타임스탬프와 날짜가 동일한 셀에 포함되어 있지 않습니다(예: 오전 12:00:00 | 2014년 7월 21일 아님 2014년 7월 21일 00:00:00)

2 - 모든 타임스탬프 수를 단일 날짜 목록 내에서 그룹화하기를 원합니다(예: 일요일부터 토요일까지의 한 세트만 표시하고 추가 날짜마다 새 열 세트를 생성하지 않음). 금요일에 시작한 경우 금요일에 그룹화를 시작하지 않고, 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))의 새 열과 타임스탬프의 두 번째 열을 만들어 반올림을 수행하겠습니다.

관련 정보