Excel 2010: formule pour la somme et la date de retour

J'ai un cahier avec une fiche récapitulative et une fiche de Data Set avec un tableau lié contenant des enregistrements de 700k + sur les données financières sur les investissements. (Dans mon cas particulier, ce sont des propriétés immobilières, mais il s'agit du même scénario que le suivi des stocks et des dividendes.) La fiche récapitulative contient une rangée par propriété (investissement) et indique le montant (de l'argent) initialement injecté (c.-à-d. Ou investi) par propriété, dans l'espoir que la production monétaire (ou le retour sur investissement) sera à un certain point équivalent à ce montant d'injection / investissement initial. L'ensemble de données montre la production quotidienne (c'est-à-dire la production monétaire) par propriété, avec une ligne par propriété par date (c.-à-d., A plusieurs lignes pour chaque propriété) et ne peut pas être modifiée. Il convient de noter que la colonne PropName n'est pas fiable (inexacte / incohérente), et donc PropID devrait être utilisé pour corréler les deux feuilles.

J'aimerais avoir une formule dans la colonne «Révision de la date de récupération à 100%» de la fiche récapitulative qui indiquera la date à laquelle la sortie totale (cumulative) atteint ou dépasse 100% de l'entrée (c'est-à-dire la date à laquelle l'investissement a été Récupéré). J'ai supposé que j'aurais besoin d'une formule pour faire ce qui suit, même si je me trompe peut-être:

  1. Additionnez toutes les données de production (retour sur investissement) pour chaque propriété, du plus ancien au plus récent (l'ensemble de données est déjà trié)

    Jusqu'à ce qu'il atteigne 100% de l'entrée / investissement d'origine (montré dans la colonne Entrée totale sur la fiche récapitulative),

  2. Puis renvoyez la date (située dans un champ dans la feuille de configuration de données) lorsque la récupération à 100% est remplie,

  3. Si la récupération à 100% n'est pas remplie, renvoyez "TBD".

Par exemple, le RecoveryDate à 100% souhaité pour la propriété 0764 ("Prop 1") serait le 6/7/2013 (à partir de la sixième ligne de données de la feuille Data Set) car 1.667000055+ 5.000999928 +1.667000055 + 3.334000111 + 5.000999928 (depuis le premier Six lignes de la feuille Set de données) = 16.670000077, qui est supérieur à 13 (entrée totale pour la propriété 0764).

Tous les commentaires constructifs sont les bienvenus et les plus appréciés!

Exemple de fiche récapitulative:

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% 

Exemple de jeu de données:

 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 

Créez une feuille factice. Reliez-le à la feuille de données comme suit:

  • Cliquez dans la cellule A1 , et soit

    • Type ='Data Set'!A1 , ou
    • Tapez = , cliquez sur l'onglet "Ensemble de données" et cliquez dans la cellule A1 sur cette feuille

    Puis tapez Entrer ou cliquez sur la case à cocher à gauche de la barre des formules.

  • Cliquez dans la zone de nom (à gauche de la barre de formule) et tapez une plage qui couvre toutes les lignes de la feuille de jeu de données, y compris (au minimum) les colonnes "PropID", "Date" et "Sortie". (Ce sont les colonnes A , C et D dans l'exemple, et vous dites que vous avez 700K + lignes, vous pouvez donc taper A1:D999999 .)
  • Type Entrez . À titre d'illustration: Illustration de la gymnastique Excel
  • Cliquez dans la barre de formule (qui devrait dire ='Data Set'!A1 ) et tapez Ctrl + Entrée .

Alors,

  • Enter =IF(SUMIFS($D$2:$D2,$A$2:$A2,$A2)>VLOOKUP($A2, Summary!A:C, 3,FALSE), ROW(), "") dans la cellule G2 Et tapez Enter .
  • Enter =MIN(IF(($A$2:$A$999998=$A2), ($G$2:$G$999998), 999999)) dans la cellule H2 et tapez Ctrl + Shift + Enter , ce qui en fait une "formule de tableau ".
  • Faites-les glisser vers la ligne 999999.

    Malheureusement, je ne peux pas comprendre comment obtenir le truc de la boîte de nom pour fonctionner pour la formule de tableau; Vous devrez peut-être simplement le faire glisser manuellement.

  • Accédez à la fiche récapitulative.
  • Dans la cellule E2 (le premier "100% RecoveryDate"), entrez: =IF(ISERROR(VLOOKUP(A2,Dummy!A:H,8,FALSE)), "No Data!", IF(VLOOKUP(A2,Dummy!A:H,8,FALSE)=999999, "TBD", INDEX(Dummy!C:C,VLOOKUP(A2,Dummy!A:H,8,FALSE)))) .
  • Formatez la cellule en tant que date.
  • Centrez-le si vous le souhaitez.
  • Ensuite, faites-le glisser vers la dernière ligne de données de la fiche récapitulative.

Remarque: Vous devrez modifier ce qui précède si votre Data Set atteint 1000000 (un million) de lignes. J'espère que les lieux sont évidents.