☰

Как в Excel подсчитать огромное количество временных меток и расположить их в 5-минутных периодах, отсортировав по дням недели?

tag-icon • tag-icon microsoft-excel worksheet-function
Как в Excel подсчитать огромное количество временных меток и расположить их в 5-минутных периодах, отсортировав по дням недели?

У меня в электронной таблице чуть более 300 000 строк, и каждая из этих строк содержит временную метку (ЧЧ:ММ:СС AM) и дату (ДД/ММ/ГГГГ).

Я хотел бы получить подсчеты для этих временных меток и сгруппировать их в 5-минутные периоды по дням недели (то есть, я хотел бы получить количество временных меток между 12:00 и 12:05 в воскресенье, 12:05 и 12:10 в воскресенье и т. д.). Я чувствую, что мне нужно использовать это COUNTIFкаким-то образом, но я не могу точно понять, как это реализовать. Я пытаюсь организовать результаты в другой лист, который выглядит примерно так.

введите описание изображения здесь

Подсчет для каждого 5-минутного периода будет вестись в этом среднем столбце под днем ​​недели (то есть именно там я буду вводить формулу).

Заранее спасибо за любую помощь, буду очень признателен!

EDIT: Вероятно, мне следовало бы указать, что мои данные выглядят следующим образом:

введите описание изображения здесь

решение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, вы можете добавить дополнительный столбец, указывающий день недели, т. е. на листе 1, где присутствуют ваши необработанные данные, а столбцы A и B содержат дату и время соответственно, вы можете использовать эту функцию в столбце C.

=ЕСЛИ(ИЛИ(ЕСТЬПУСТО(A1),ЕСТЬПУСТО(B1)),"",ДЕНЬНЕД(A1))

Подход, который я использую, основан исключительно на функциях. Как и на изображении, которым вы поделились, я настроил формулы для Листа 2, чтобы сгенерировать похожую структуру. Следующее условие проверяет, что временные метки существуют в определенном диапазоне, как объявлено в первом столбце таблицы, и, кроме того, что это заданный день недели. Затем он подсчитывает все такие значения.

=СЧЁТЕСЛИМН(Лист1!$B:$B, ">"&$A3,Лист1!$B:$B,"<="&$A4,Лист1!$C:$C,"="&B$2)

На изображении показано, как указанная выше формула вводится в ячейку B1 и содержит условия для проверки того, что время находится в диапазоне от 12:00 до 12:10, а день установлен на воскресенье или 1.

введите описание изображения здесь

Как вы могли заметить, разница во времени здесь составляет 10 минут. Я сделал ее настраиваемой, так что вы можете установить ее на 5 минут или 2 часа в зависимости от ваших потребностей.

Поскольку мы используем здесь функции, здесь не имеет значения, есть ли у ваших данных 4 пустых строки или 10 пустых строк между ними. Функции гарантируют, что будут собраны только непустые данные. У меня было недостаточно данных, поэтому пример выглядит довольно пустым. Пожалуйста, найдите прилагаемый пример xlsздесь.

решение3

Примечание: собираюсь устранить неполадки в этом коде сегодня вечером, все еще есть некоторые ошибки, перемещающиеся вниз по списку значений. В настоящее время неработоспособно, просто не хочу переформатировать строки кода. Можете свободно экспериментировать. Я добавлю комментарии и инструкции одновременно.

Кто-то упомянул, что это можно сделать с помощью VBA. Мне нравится VBA, поэтому я решил попробовать.

Пара предположений:

1 - Метка времени и дата не содержатся в одной и той же ячейке (например, 12:00:00 AM | 21.07.2014, а не 21.07.2014 00:00:00)

2 - Вы хотите, чтобы все подсчеты временных меток были сгруппированы в одном списке дней (т. е. отображался только один набор с воскресенья по субботу, и не создавался новый набор столбцов для каждого дополнительного дня - если бы мы начали в пятницу, мы бы не начали группировку в пятницу, а если бы мы закончили во вторник через две недели, у нас не было бы 16 столбцов группировок)

3 — В ваших данных нет пустых ячеек от первой строки до последней.

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

Вы должны иметь возможность округлить каждую запись до ближайших пяти минут. Затем используйте функцию промежуточных итогов Excel, чтобы получить подсчеты.

Я бы выполнил округление, создав новый столбец даты (=День(A1)) и второй столбец временной метки.

Связанный контент