Совпадение нескольких столбцов временных рядов?

Question

Я новый нуб на форуме, был бы признателен, если кто-нибудь может оказать некоторую помощь.

Я использую Excel 2013. У меня есть 5 наборов столбцов; каждый включает в себя столбец Time и столбец Price. Однако 5 отдельных столбцов времени из 5 наборов не совпадают, т. Е. Даты не являются непрерывными (в каждом наборе имеются разные временные перерывы), и они начинаются с разных начальных дат.

Я хочу сделать две вещи, вторая является необязательной (так как я считаю, что Excel не в состоянии доставить).

(1) Вставьте новый набор столбцов с общими датами (общие даты / записи от времени 1 до времени 5) слева и ценами 1-5 справа. Например (обратите внимание, что даты НЕ являются непрерывными):

Common Date   Price 1   Price 2   Price 3   Price 4   Price 5
20010304      938       33        988       5532      5732
20010308      25        23325     2358      664       8798
20010309      677       3         234       32423     234
20010311      9835      32434     3529      352       392
20010314      498       4534      43398     945       495483

(2) Это очень отличается от (1); сделать столбец дат непрерывным. То есть, включите также необычные даты, отображая пустые / разрывные даты. Например (обратите внимание, что даты непрерывны, без перерывов, даже по выходным):

Date       Price 1   Price 2   Price 3   Price 4   Price 5
20010304
20010305
20010306   677       3
20010307
20010308                       234
20010309             9845                          234
20010310                                 32423
20010311   43435    
20010312             234                           324
20010313
20010314   898

К вашему сведению, я пробовал формулы IF(COUNTIF()) ; они просто сортируют общие записи в одной строке. Я возился с VLOOKUP и был слишком глуп, чтобы разобраться, то же самое касается INDEX/MATCH . Возможно, решения лежат в PivotTable или VBA, о которых я ничего не знаю.

Answer 1

Если я правильно понимаю, у вас есть макет данных чередующихся времени и цены, где время действительно число, представляющее ггггммдд.

Вариант 2 на самом деле довольно прост. Определите наименьшее значение времени и поместите его в ячейку. В ячейке под ней увеличьте это время на один день. Затем выберите обе ячейки и перетащите маркер заполнения вниз. Excel продолжит автоматически увеличивать значения, и вы получите список последовательных дат. (вы также можете использовать формулу вдоль линий = A1+1 и перетащить ее вниз)

В следующих пяти столбцах будут храниться значения цен, и они будут проверены по формуле. Вот скриншот сценария:

Последовательные значения времени начинаются в ячейке A13. Ячейка B13 имеет формулу:

=IFERROR(VLOOKUP($A13,dataset1,2,0),"")

"dataset1" - это имя диапазона, которое я присвоил ячейкам от A1 до B6, но вместо этого вы можете использовать прямые ссылки на ячейки $ A $ 1:$ B $ 6. Я также создал имена диапазонов для других наборов данных, например, "dataset2", "dataset3" и т.д. Формула в ячейке C13:

=IFERROR(VLOOKUP($A13,dataset2,2,0),"")

С именами диапазонов мне нужно только настроить номер набора данных, а затем скопировать формулу вниз.