Excel 2010: формула для суммы и даты возвращения

Question

У меня есть рабочая тетрадь с таблицей сводных данных и таблица наборов данных со связанной таблицей, содержащей более 700 тыс. Записей финансовых данных об инвестициях. (В моем конкретном случае это объекты недвижимости; но это тот же сценарий, что и для отслеживания акций и дивидендов.) Сводная ведомость содержит одну строку для каждого свойства (инвестиции) и показывает сумму (денег), первоначально вложенную (т. Е. Вложенную или вложенную) для каждого свойства, в расчете на то, что денежный выпуск (или возврат инвестиций) в какой-то момент будет равен первоначальная сумма впрыска / инвестиции. Набор данных показывает ежедневное производство (т. Е. Денежную продукцию) для каждого свойства, с одной строкой на свойство на дату (т. Е. Имеет много строк для каждого свойства) и не может быть отредактировано. Следует отметить, что столбец PropName является ненадежным (неточным / непоследовательным), поэтому для сопоставления двух листов следует использовать PropID.

Я хотел бы иметь формулу в столбце «Дата восстановления 100%» на листе «Сводка», в которой будет указана дата, когда общий (совокупный) выход достигнет или превысит 100% ввода (т. Е. Дату, когда инвестиции были окупиться). Я предположил, что мне понадобится формула, чтобы сделать следующее, хотя я могу ошибаться:

1. Суммируйте все производственные данные (возврат инвестиций) для каждого свойства, от самого старого до самого нового (набор данных уже отсортирован таким образом),

   ДО достижения 100% первоначального ввода / инвестиций (показано в столбце "Общий ввод" на сводном листе),

2. затем вернуть дату (расположенную в поле на листе набора данных) при достижении 100% восстановления,

3. Если 100% восстановление не выполнено, верните "TBD".

Например, желаемое значение 100% RecoveryDate для свойства 0764 («Пропорция 1») будет 7 июня 2013 г. (из шестой строки данных таблицы данных), поскольку 1.667000055+ 5.000999928 +1.667000055 + 3.334000111 + 5.000999928 (с первого шесть строк таблицы набора данных) = 16,670000077, что больше 13 (итоговый вход для свойства 0764).

Любые конструктивные отзывы приветствуются и приветствуются!

Образец сводной ведомости:

PropID   PropName   TotalInput   CurrentOutput  100%RecoveryDate  ProgressToRecovery
 0764     Prop 1          13         71,820                              189%
 0736     Prop 2      30,711        134,746                              439%
 1680     Prop 3      25,014         52,887                              211%
 4078     Prop 4      29,494         36,705                              124%
 5226     Prop 5      43,983         41,438                               94%
 6427     Prop 6      28,786         50,855                              177%
 6683     Prop 7      19,231         60,501                              315%
 6739     Prop 8      28,350         48,229                              170%
 9153     Prop 9      37,888         28,125                               74%
 8020     Prop 10     31,429         41,094                              131%

Образец набора данных:

PropID   PropName     Date          Output
 0764     Prop 1    6/1/2013      1.667000055
 0764     Prop 1    6/3/2013      5.000999928
 0764     Prop 1    6/7/2013      1.667000055
 0764     Prop 1    6/10/2013     3.334000111
 0736     Prop 2    6/19/2013   361
 0764     Prop 1    6/19/2013     5.000999928
 0764     Prop 1    6/22/2013     6.668000221
 0764     Prop 1    7/12/2013     3.334000111
 1680     Prop 3    7/17/2013   389
 0764     Prop 1    7/23/2013    10.00200081
 0736     Prop 2    8/2/2013    236
 4078     Prop 4    8/22/2013   236
 0764     Prop 1    8/25/2013     6.668000221
 0764     Prop 1    8/30/2013     3.334000111
 0764     Prop 1    8/31/2013     5.000999928
 0764     Prop 1    9/11/2013     1.667000055
 6427     Prop 6    9/15/2013  1018
 1680     Prop 3    9/16/2013   389
 0764     Prop 1    9/20/2013     6.668000221
 0764     Prop 1    9/22/2013    10.00200081
 0764     Prop 1    9/23/2013    10.00200081
 5226     Prop 5    9/23/2013   125

Answer 1

Создайте фиктивный лист.  Свяжите это с листом набора данных следующим образом:

• Нажмите в ячейке A1 , и либо

  • type ='Data Set'!A1 или
  • введите = , щелкните вкладку «Набор данных» и щелкните ячейку A1 на этом листе.

  и затем введите Enter или нажмите на галочку слева от панели формул.

• Щелкните в поле «Имя» (слева от панели формул) и введите диапазон, охватывающий все строки таблицы «Набор данных», включая (как минимум) столбцы «PropID», «Date» и «Output».  (Это столбцы A , C и D в примере, и вы говорите, что у вас есть 700K+ строк, поэтому вы можете ввести A1:D999999 .)
• Введите Enter .  Для иллюстрации:
• Нажмите на панели формул (которая должна сказать ='Data Set'!A1) и введите Ctrl+ Enter .

Затем,

• Введите =IF(SUMIFS($D$2:$D2,$A$2:$A2,$A2)>VLOOKUP($A2, Summary!A:C, 3,FALSE), ROW(), "") в ячейку G2 и введите Enter.
• Введите =MIN(IF(($A$2:$A$999998=$A2), ($G$2:$G$999998), 999999)) в ячейку H2 и введите Ctrl+Shift+Enter, что делает его "формулой массива". ».
• Перетащите их в строку 999999.

  К сожалению, я не могу понять, как заставить трюк Name Box работать с формулой массива; возможно, вам просто нужно перетащить его вручную.

• Перейти к итоговому листу.
• В ячейке E2 (первая «100% дата восстановления») введите: = IF (ISERROR (VLOOKUP (A2, Dummy!)A: H, 8, FALSE)), "Нет данных!", ЕСЛИ (ВЛООКУП (А2, ДУМАК!A: H, 8, FALSE) = 999999, "TBD", INDEX (Пустышка!C: C, ВПР (A2, пустышка!A: H, 8, ЛОЖЬ)))).
• Отформатируйте ячейку как дату.
• Сосредоточьте это, если хотите.
• Затем перетащите его вниз до последней строки данных на сводном листе.

Примечание. Вам нужно будет изменить вышеприведенное, если ваш набор данных достигнет 1000000 (один миллион) строк.  Я надеюсь, что места очевидны.