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

Question

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

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

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

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

РЕДАКТИРОВАТЬ: Я, вероятно, должен был включить, что мои данные выглядят так:

Answer 1

Подобно тому, что рекомендовано @Excellll, вы можете добавить дополнительный столбец с указанием дня недели, то есть на листе 1, где присутствуют ваши необработанные данные, а столбцы A и B содержат дату и время соответственно, вы можете использовать эту функцию в столбце C

= ЕСЛИ (ИЛИ (ЕПУСТО (А1), ЕПУСТО (В1)), "", ДЕНЬНЕД (А1))

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

= COUNTIFS(Лист1!$ B:$ B, ">" & $ A3, Sheet1!$ B:$ B, "<=" & $ A4, Лист1!$ C:$ C, "=" & B $ 2)

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

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

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

Answer 2

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

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

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

1 - Отметка времени и дата не содержатся в одной и той же ячейке (т. Е. 12:00:00 AM | 21.07.2014, NOT 7/21/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

Answer 3

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

Сначала добавьте поле дня недели (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 записей за неделю) для создания сводной таблицы. Это должно выглядеть больше как то, что вы имеете в виду с большим набором данных.

Answer 4

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

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