заполнить значения в Excel в соответствии с датами

Question

У меня есть вызов в Excel 2010. У меня есть список идентификаторов со следующими переменными: Issue_Date, совокупное значение, дата погашения. Каждый идентификатор имеет разные дату выпуска и дату погашения, но все они находятся в диапазоне (период анализа) между 2 января 2007 года и 23 декабря 2011 года. Краткий пример ниже:

Идентификатор имеет 3 различных значения: одно на 2 января 2007 г. (значение: 1000); еще один за 5 мая 2007 г. (значение: 1500) и последний за 4 декабря 2007 г. (значение: 2700). То, что я хочу, чтобы этот идентификатор дублировал совокупные значения в течение периода, когда он был активен.

Таким образом, в этом случае в период с 2 января 2007 года по 4 мая 2007 года значение будет (1000); затем за период с 5 мая 2007 г. по 3 декабря 2007 г. стоимость будет 1500; наконец, за период с 4 декабря 2007 года до даты погашения (скажем, 3 марта 2010 года) значение останется на уровне 2700.

Это сложная задача для меня, потому что у меня 355 разных идентификаторов. Некоторые идентификаторы имеют уникальные значения, что означает, что их совокупное значение будет реплицироваться до даты погашения. Тем не менее, существуют другие идентификаторы, которые имеют разные совокупные значения (в разные даты), которые должны быть расположены правильно.

Окончательным результатом будет набор последовательных дат между 2 января 2007 г. и 23 декабря 2011 г. (в столбцах), а затем будет выделено 355 строк данных, соответственно. Я могу отправить краткий пример в Excel, если необходимо иметь более четкое представление о запросе.

Answer 1

Для этого вам нужно немного помассировать данные перед созданием матрицы.

1. Сортируйте данные по ID и issue_date (от А до Я) в указанном порядке или приоритете.

2. Добавьте столбец end_date . Значение end_date будет более ранним из даты погашения для этой записи и за один день до следующей date_date для этого конкретного идентификатора. Предполагая , что ID находится в колонке А, issue_date в столбце В, и maturity_date находится в столбце D, введите следующую формулу для end_date первой записи. Заполнить вниз

   =TEXT(IF(A3=A2,MIN(B3-1,D2),D2),"mmm d, yyyy")

   

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

4. В верхней левой ячейке вашей матрицы (в данном примере B3) введите следующую формулу. «Сырой» - это название листа с вашими исходными данными.

   =SUMPRODUCT(Raw!$C$2:$C$23,--(Raw!$A$2:$A$23=Matrix!$A3),--(DATEVALUE(Raw!$B$2:$B$23)<=Matrix!B$2),--(DATEVALUE(Raw!$E$2:$E$23)>=Matrix!B$2))

   Заполните все, чтобы заполнить матрицу. Это вернет значение идентификатора на эту дату и 0, если для этой даты нет доступных данных.

   

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

Я использую даты в качестве текста, но эти формулы должны работать и для дат, отформатированных как даты в Excel. Биты TEXT() и DATEVALUE() в этом случае излишни, но все должно работать одинаково.