Частота появления данных в столбце Excel на основе определенного условия

У меня есть столбец (таблица 1 ниже) с данными времени в формате 24 часа, сохраненным как текст. Мне нужно, чтобы таблица 2 была заполнена часом и добавила все вхождения в течение часа.

Таким образом, одно событие находится на 0102. Это относится к диапазону hor от 12:30 до 13:30. Я хочу, чтобы счет был 1 на 01:00. Если с 01:30 до 02:30 произойдет сбой, я хочу, чтобы счет был 1 на 02:00. Также, если время 04:55, оно должно показать счет на 05:00 как 1. В основном округление до ближайшего времени и отображение количества для этого. Он должен округлить до ближайшего времени и добавить количество вхождений. То почему 06:00 2. для учета 0622 и 0614.

Например:

TABLE 1 TABLE2 (Expected results) Column A Column A Column B 0102 01:00 1 0222 02:00 2 0223 03:00 0 0455 04:00 0 0622 05:00 1 0614 06:00 2 

ПРИМЕЧАНИЕ. В B1 мы получаем 1, потому что значение в A1 таблицы 1 равно 0102 и находится между 12:30 и 01:30, что представлено 01:00 в A1 в ТАБЛИЦЕ 2 . Мы имеем два значения в таблице 1 между 01:30 и 02:30: один находится в A2, а второй – в A3 , поэтому мы ожидаем 2 в B2 в таблице 2 , временной диапазон, который представлен 02:00 в A2 . Мы связываем 0 с ячейками B3 и B4 в таблице 2, потому что в таблице 1 нет данных в диапазоне между 02: 30-03: 30 и 03: 30-04: 30, поэтому они получают 0 в своих соответствующих ячейках в таблице 2 . Однако у нас есть два экземпляра данных, 0622 и 0612 в Таблице 1, которые падают между 05:30 и 06:30, поэтому значение 2 относится к B6 .

Как это сделать с помощью формулы?

Пожалуйста помоги.

Поэтому, пытаясь понять, что происходит, вот простое решение:

сначала позвольте мне уточнить еще раз:
столбец A : но вставлен как текст изначально и в формате hhmm , но вам нужно переформатировать их как простые числа, поэтому вы потеряете эти 0 с в начале определенных данных.
столбец B : мы откладываем наши интервалы времени, как вы сказали, начиная с 12:30, поэтому я поставил его как 0030, из которых нули больше не видны в числовом формате. Этот диапазон начинается с 30 и увеличивается на 100, пока он не остановится при 2330 вместо перехода на 2430. Это будет отражено в формуле для последней ячейки в столбце D.
столбец C : диапазоны часов, по которым мы округляем и подсчитываем данные в A.
столбец D : в D2 мы приводим следующую формулу:

 =COUNTIFS(A:A,">"&$B2,A:A,"<"&$B3) 

и перетащите и заполните до последней, но одной ячейки. для последней ячейки D25 мы меняем большую / меньшую, чем знаки, и используем следующую формулу (только для этой ячейки):

 =COUNTIFS(A:A,"<"&$B25,A:A,"<"&$B2) 

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

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

Давайте будем гением компьютера.