☰

Excel — суммирование последовательных данных за каждые 30 минут

tag-icon • tag-icon microsoft-excel worksheet-function
Excel — суммирование последовательных данных за каждые 30 минут

У меня есть данные, полученные с дождемера, в следующем формате:

Индекс данных Метка времени

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T01:00:00
3. 1    2020-01-05T01:20:15
4. 0    2020-01-05T02:00:00
5. 1    2020-01-05T02:09:00
6. 1    2020-01-05T02:09:45
7. 1    2020-01-05T02:20:00
8. 1    2020-01-05T02:20:01
9. 0    2020-01-05T03:00:00

Я хотел бы суммировать последовательные данные каждые 30 минут. Что-то вроде следующего:

Индекс данных Метка времени

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T00:30:00
3. 0    2020-01-05T01:00:00
4. 1    2020-01-05T01:30:00
5. 0    2020-01-05T02:00:00
6. 4    2020-01-05T02:30:00
7. 0    2020-01-05T03:00:00

Я уже получил только 24-часовое время из временной метки и попытался выяснить, будет ли вычитание 2 временных меток меньше 30 минут, по следующей формуле:

=IF(((time)-(time-1))<=0.020833,"ok","not within 30min interval")

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

Я попытался создать что-то вроде COUNTIFпроверки последовательных данных в течение 30-минутного интервала и попытался разработать функцию a SUMIFSи a SUMPRODUCT, но мне трудно связать все воедино и использовать логику.

Спасибо

решение1

Вы можете использовать эту формулу:

=DATEVALUE(SUBSTITUTE(C2,"T"," "))+CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")

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

Разбито на шаги:

  1. Чтобы Excel распознавал текст как дату/время, удалите T:

    =ПОДСТАВИТЬ(C2;"T"; ")

  2. Чтобы получить дату из этой даты/времени:

    =ДАТАЗНАЧЕНИЕ(D2)

  3. Чтобы узнать время для этой даты/времени:

    =ОКРУГЛ(ВРЕМЯЗНАЧЕНИЕ(D2),5)

(Чуть ниже я объясню, зачем здесь нужен ROUND)

  1. Чтобы рассчитать следующий 30-минутный интервал:

    =ПОТОЛОК.МАТ(ОКРУГЛ(ЗНАЧЕНИЕВРЕМЕНИ(ПОДСТАВИТЬ(C2;"T"," ")),5),"0:30")

Затем объединение даты с 30-минутным шагом дает вам формулу в верхней части ответа. Затем вы можете повернуть таблицу, чтобы просуммировать значение по шагу.

Примечание по вызову ROUND на шаге 3:

Когда я впервые написал формулу, я не использовал ОКРУГЛ на шаге 3, но обнаружил, что приращение, выходящее для 2:00:00, было 2:30:00, что было неправильно. При проверке я обнаружил, что функция ВРЕМЗНАЧ не возвращала правильный уровень точности. Округление до 5 десятичных знаков исправило это.

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

РЕДАКТИРОВАТЬ:

Поскольку у вас Excel 365, я предполагаю, что у вас также есть доступ к функции ПОСЛЕДОВАТЕЛЬНОСТЬ.

Чтобы выполнить агрегацию без сводной таблицы, вы можете сделать следующее (при условии, что у вас нет готового списка приращений):

  1. Рассчитайте количество 30-минутных интервалов между MIN TimestampGroup и MAX группой временных меток.
  2. Создайте последовательность, содержащую столько приращений (чтобы приращения, отсутствующие в исходных данных, также были представлены)
  3. Используйте функцию SUMIF, чтобы вернуться к исходным данным и суммировать данные по TimestampGroup.
  4. При желании можно создать GroupIndex для агрегированной таблицы.

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

Формулы в сводной таблице следующие:

GroupIndex:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1)

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

Группа временной метки:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1,1,MIN($D$3:$D$11),1/48)

Снова, используя функцию последовательности, верните список TimestampGroups, но вместо того, чтобы начинать последовательность с 1 и увеличивать на 1, что является третьим и четвертым параметрами по умолчанию, начните последовательность с минимальной TimestampGroup и увеличивайте на 30 минут, что составляет 1/48 дня.

Ценить:

=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)

Суммируйте столбец «Данные» для строк, указанных TimestampGroup в текущей строке агрегированной таблицы.

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