Учитывая несколько дат начала, как назначить счетчики относительным значениям даты?

Question

Я пытаюсь создать в Excel диаграмму «до и после», выстраивая несколько дат вехи в нулевом месяце и назначая относительные номера месяцев месяцам до и после месяца вех.

Моя электронная таблица содержит около 5000 строк и выглядит примерно так:

+------------+-------------+---------+---------+---------+---------+---------+---------+
|  UserID    |MilestoneDate| 2014-01 | 2014-02 | 2014-03 | 2014-04 | 2014-05 | 2014-06 |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    7       | 2014-01-02  | 232     | 22      | 19      | 77      | 11      | 89      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    89      | 2014-04-02  | 345     | 45      | 564     | 13      | 122     | 77      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    678     | 2014-03-11  | 55      | 14      | 17      | 222     | 109     | 111     |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    897     | 2014-03-07  | 234     | 56      | 201     | 19      | 55      | 78      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    789     | 2014-02-22  | 331     | 33      | 67      | 108     | 111     | 56      |
+------------+-------------+---------+---------+---------+---------+---------+---------+
|    989     | 2014-01-09  | 12      | 89      | 97      | 125     | 323     | 99      |
+------------+-------------+---------+---------+---------+---------+---------+---------+

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

В конечном итоге я хотел бы сгенерировать линейный график с относительным месяцем # на оси X и всеми месяцами MilestoneDate, установленными на ноль. Посещения до MilestoneDate должны рассчитываться на соответствующие отрицательные месяцы (например, мартовское действие пользователя 89 должно происходить в месяце «-1», а его февральское действие - в месяце «-2»).

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

Любая помощь?

Answer 1

Посмотрите, соответствует ли это требованиям.

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

=MONTH(B5)-MONTH(MonthZero)

Метки в ряду 15 от -5 до 6 вводятся вручную. Формула заполняет таблицу, начиная с C16, копируется вниз и поперек

=IF(IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())=$A5,NA(),IF(IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())=$B5,NA(),IFERROR(OFFSET($C5:$H5,0,$A16+C$15,1,1),NA())))

Формула захватывает исходные данные пользователя и размещает их относительно месяца объединения, используя рассчитанное смещение. Два вложенных IF используются, чтобы избежать появления в таблице имен пользователей и даты их соединения, а IFError гарантирует, что будут возвращены только допустимые смещения.

Затем создайте линейную диаграмму со строками 14 и 15 в качестве оси X и идентификаторами пользователей в качестве ряда.

Я поделился файлом, использованным для создания скриншота на моем OneDrive.

Примечание. Этот тип визуализации данных не следует использовать для серии 5000 для 5000 пользователей. Вы хотели бы объединить данные каким-либо образом.

Изменить: вот еще одна версия с другой формулой. Я начал другой лист и расположил исходные данные в столбцах от A до H, а источник в диаграмме - рядом в столбцах J и далее.

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

=IFERROR(IF($J5+L$4>=0,INDEX($C5:$H5,($J5+L$4)+1),NA()),NA())

Вы можете найти это на Листе 2 в файле OneDrive.