Заполнение пропущенного времени набора данных с разбитым временным рядом

Question

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

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

Я преобразовал «Час дня» (столбец A) и «Минуту» (столбец B) в «Время» (столбец G), используя

=TIME(RIGHT(A3,2),B3,0)

Я пытался использовать индекс / совпадение

=INDEX($C$3:$C$954,MATCH(H3,$G$3:$G$954,0))

в столбце I ссылаясь на непрерывный временной ряд в столбце H , чтобы вернуть данные в столбце C но он возвращает #N/A

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

Поскольку H6 - 12:03:00 AM , I6 должен содержать 2 , что является значением от C3 , поскольку G3 - 12:03:00 AM .

Аналогично, H11 = G4 = 12:08:00 AM → I11 = C4 = 1 . 

Строки, значение H которых отсутствует в столбце G должны иметь 0 в столбце I

Answer 1

Проблема возникает из-за расхождений с плавающей запятой, когда вы используете заполнение линейных рядов Excel для автоматического заполнения времен в столбце H по сравнению с временами, сгенерированными функцией TIME() .

Это можно подтвердить, введя =H6-G3 в любой ячейке или выбрав H6 , затем нажав F2, а затем Enter. Обратите внимание, что хотя метод F2 можно использовать для коррекции значений времени по одному, это не удобно для любого значительного числа ячеек.

Решение состоит в том, чтобы генерировать значения Continuous Time по формуле.

Введите следующую формулу в H3 и ctrl-enter/copy-paste/fill-down/auto-fill как можно дальше от конца столбца H таблицы:

=IF(ISTEXT(H2),TIME(RIGHT(A3,2),0,0),TIME(HOUR(H2),MINUTE(H2)+1,0))

Обратите внимание, что значения времени можно оставить как есть или преобразовать в литеральные значения, скопировав и вставив их в качестве значений.

Как вы можете видеть, ваша формула индекса / соответствия в столбце I теперь выберет «Пользователи» из столбца C когда в столбце G время совпадения.

---

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

Хотя приведенная выше формула является "надежной" и позволяет вставлять / удалять строки над заголовком, не разбивая его, как указывал Скотт (особенно если копировать / вставлять как значения), существует более простая и менее надежная версия:

=TIME(RIGHT($A$3,2),ROW()-3,0)

Дальнейшее упрощение этого приводит к следующей простейшей формуле:

=TIME(0,ROW()-3,0)

---

Осталось только изменить формулу, чтобы генерировать нули, когда время отсутствует:

=IFERROR(INDEX($C$3:$C$954,MATCH(H3,$G$3:$G$954,0)),0)