aide sur la fonction RECHERCHE V ou H

Question

Bonjour, 

Je vous expose mon souci, j'ai 3 tableaux d'emprunt (voir exemple ci dessous) sur 10 ans dans un classeur excel sur 3 feuilles différents et nommées Emprunt 1,....  et je voudrai en créant une 4ème feuille regrouper certaines données relatives a ces emprunts en utilisant la fonction RECHERCHE V ou H pour que si par exemple je tape l'année 2010 il va chercher tout les montants qui correspondent a l'année 2010 (intérêts, amortissement, capital restant du).

Pouvez vous m'aider car quand je mets la formule suivante =SI(DATECLOTURE<>"";RECHERCHEV(PRET91006!B22;PRET91006;7;1);"") il me renvoie 24.80 € ce qui est juste mais quand je saisi 2003 il devrait me renvoyé 94.80 € or il me renvoie 24.80 € quel est le problème dans ma formule.  Merci de bien vouloir m'aider s'il vous plait.

NB : DATE CLOTURE etant une cellule ou je mets la date recherchée et PRET91006!B22;PRET91006;6 étant le tableau ci dessous



N°	Date	         Montant 	      Amortissement    Intérêts	       K Restant	    Interets Courus
1	2002				20 882,81 € 	  102,98 € 
2	2003	2 704,42 €	1 660,28 €	1 044,14 €  19 222,53 € 	   94,80 € 
3	2004	2 704,42 €	1 743,29 €	961,13 €	   17 479,24 € 	   86,20 € 
4	2005	2 704,42 €	1 830,46 €	873,96 €	   15 648,78 € 	   77,17 € 
5	2006	2 704,42 €	1 921,98 €	782,44 €	   13 726,80 € 	   67,69 € 
6	2007	2 704,42 €	2 018,08 €	686,34 €	   11 708,72 € 	   57,74 € 
7	2008	2 704,42 €	2 118,98 €	585,44 €	   9 589,74 € 	   47,29 € 
8	2009	2 704,42 €	2 224,93 €	479,49 €	   7 364,80 € 	   36,32 € 
9	2010	2 704,42 €	2 336,18 €	368,24 €	   5 028,63 € 	   24,80 € 
10	2011	2 704,42 €	2 452,99 €	251,43 €	   2 575,64 € 	   12,70 € 


Merci d'avance a vous de tous vos conseils.





Configuration: Windows XP / Firefox 3.5.17

pilas31 · Answer

Bonjour, 


Attention car RECHERCHEV cherche dans la première colonne du tableau et retourne la Niéme valeur du tableau (en relatif). 

Hors le tableau montré a 7 colonnes et on comprends que la recherche se fait sur l'année. Donc soit la 1° colonne existe et il recherche sur 1, 2, 3 ... et non pas sur l'année, soit le tableau commence avec l'année mais alors c'est le 7 qui est faux il faut plutôt mettre 6... 



EDIT : J'ai testé et à mon avis le tableau englobe les chiffres du début. Donc il faut soit modifier soit créer un autre nom de tableau qui prends seulement :
Date	, Montant ,Amortissement, Intérêts, K Restant, Interets Courus et modifier le recherchev dans la formule par :

RECHERCHEV(PRET91006!B22;NOUVEAU_TABLEAU;6;FAUX)

A+ 



Cordialement,

aramis74 · Answer

Merci pour ton aide Pilas31,  

j'ai fait ce que tu proposais, j'ai supprimé la 1ere colonne et de ce fait la 1ere colonne devient les années 2002;.... et la formule devient RECHERCHEV(PRET91006!A22;PRET91006;6;FAUX), mais par contre si je rentre n'importe quel année le montant est toujours identique (ici c'est 102.98 €) 
Donc le 1er résultat de la 6éme colonne 
Voici ci dessous le dessous que je veux alimenter avec ces formules recherche V et H, quand je rentre l'année 2002 il me renvoie 102.98€ OK, mais quand je rentre une autre année j'ai toujours le même montant donc peut la coupler avec une recherche HORIZONTALE, comme cela il me renverra les données de la 6ème colonne en VERTICALE et quelque soit l'année en Horizontale. 

Année Prêt 91006 Prêt 0069009101 
2002  102,98 €       


Je sais pas si assez clair pour toi, tu suis mon raisonnement ? 

Merci en tout cas pour ton aide 
ARAMIS 74

pilas31 · Answer

Bonjour Aramis 74, 

J'étais absent, je viens juste de voir ta réponse. 

Comme je ne comprend pas bien pourquoi cela ne fonctionne pas, j'ai fait un exemple (en Excel 2003) sur cijoint que tu peux consulter ici : 

http://www.cijoint.fr/cjlink.php?file=cj201103/cijJ0VNRM2.xls 

Dis moi quelle différence il y a avec ton cas car dans l'exemple cela fonctionne correctement. 

A+ 
Cordialement,

aramis74 · Answer

Youpi ça marche merci a toi pilas31, je sais pourquoi ça ne marchait pas, la cellule pour faire la recherche n'était pas au mauvais endroit de ce fait ça bloquait autant pour moi.  Un grand merci a toi, heureusement que des forums d'entraide comme cela existe.

Oserais je encore abuser de tes lumières  pour autre chose, si tu le permets.

toujours dans le même tableau

N° Date Montant     Amortis     Intérêts     K Restant         Interets Courus
1   2002                                                        20 882,81 € 102,98 €
2   2003 2 704,42 € 1 660,28 € 1 044,14 € 19 222,53 €    94,80 €
3   2004 2 704,42 € 1 743,29 € 961,13 €    17 479,24 €    86,20 €
4   2005 2 704,42 € 1 830,46 € 873,96 €    15 648,78 €    77,17 €
5   2006 2 704,42 € 1 921,98 € 782,44 €    13 726,80 €    67,69 €
6   2007 2 704,42 € 2 018,08 € 686,34 €    11 708,72 €    57,74 €
7   2008 2 704,42 € 2 118,98 € 585,44 €      9 589,74 €    47,29 €
8   2009 2 704,42 € 2 224,93 € 479,49 €      7 364,80 €    36,32 €
9   2010 2 704,42 € 2 336,18 € 368,24 €      5 028,63 €    24,80 €
10 2011 2 704,42 € 2 452,99 € 251,43 €      2 575,64 €    12,70 €

je dois lister dans un tableau le K restant du des emprunts a moins d'1 an, de 1 a 5 ans et de plus de 5 ans en fonction de l'année de référence que je rentre.  avec la fonction recherche peut on par exemple avec le tableau ci dessus, si je rentre l'année 2004(31/12/2004 en fait), dans la colonne moins d'1 an (a partir du 01/01/2005 donc) on aura 15648.78 €, de 1 a 5 an on aura 47418.69 € (somme de 13726.80 + 11708.72 + 9589.74 + 7364.80 + 5028.63) et a plus de 5 ans on aura 2575.64 €.

dans ce cas la les données a renvoyer sont horizontales, qu'en penses tu? est ce faisable.

Merci pour tes lumières en tout cas.

pilas31 · Answer

Bonjour,

Pour la dernière question :

Si l'année est en colonne de B2 à B11,
Le K restant en colonne de F2 à F11 
L'année de référence en C15 par exemple voici les formules qui doivent fonctionner :

à moins de 1 an

=SOMME.SI(B2:B11;"="&C15+1;F2:F11)

De 1 à 5 ans 

=SOMME.SI(B2:B11;"<="&C15+6;F2:F11)-SOMME.SI(B2:B11;"<"&C15+2;F2:F11)

Plus de 5 ans

=SOMME.SI(B2:B11;">="&C15+7;F2:F11)

A tester...

aramis74 · Answer

PILAS 31 C'EST PARFAIT , un grand merci a toi sinon j'aurais galéré, par contre tu peux m'expliquer en quoi consiste ces formules que tu m'as donné, car j'aime bien comprendre et surtout apprendre si ça te dérange pas.

En tout cas mille mercis pour ton aide

aramis74 · Answer

Merci a toi pour tes lumières cela permet d'approfondir un peu plus mes connaissances sur excel. et bonne journée. 

une dernière chose, j'ai écrit une formule pour le calcul des interets courus non échus : E9*((12-B3)*30+30-B2+1)/360
avec  le tableau ci dessous : 

Caractéristiques du Prêt						
jour	1			Montant de l'emprunt	 100 000,00 € 	
Mois	7			Taux d'intérêt nominal	5,00%	
Année 1998		Durée  (années)	5	
				Versements / an	1	
						
N°	Éché Montant 	Amortissement        Intérêts	     K Restant           Int.Courus
1	1998 0,00 €	        0,00 €	        0,00 €	       100000,00 €	2500,00 €
2	1999 23097,48 €	18097,48 €	5000,00 €	81902,52 €	2 047,56 €
3	2000 23097,48 €	19002,35 €	4095,13 €	62900,17 €	1 572,50 €
4	2001 23097,48 €	19952,47 €	3145,01 €	42947,69 €	1 073,69 €
5	2002 23097,48 €	20950,10 €	2 147,38 €	21997,60 €	
6	2003 23097,48 €	21997,60 €	1 099,88 €	0,00 €	

ou E9 = 5000 €, B3=7 (mois de juillet) et B2=1( jour) dans les caractéristiques du prêt.  Mon souci est que le calcul se fait sur une année de 360 jours et donc des mois de 30 jours, il ne prend pas les jours exact de l'année (soit 365), Exemple pour la ligne 2 on a en Intérêts 2047.56 € soit 4095.13 €/360*180 alors qu'en réel on devrait avoir 4095.13 €/365*184 soit 2064.39 €, une différence 16.83 €
						
Ma question peut on utiliser une autre formule avec les DATES pour être le plus juste possible. 

Merci par avance.


ARAMIS 74

pilas31 · Answer

Bonjour,    

J'avoue que ta dernière question m'a fait réfléchir un certain temps.    

Voila ma conclusion, j'ai trouvé 2 solutions. Une simple, une complexe.    

1/La simple

Le calcul que tu cherches à faire c'est trouver quelle fraction de l'année correspond à l'écart entre deux dates si on prends une année à 365 jours.    

Cette fonction existe dans Excel la voici.    

=FRACTION.ANNEE(DATE(B2;7;1);DATE(B2;12;31);3)    

J'ai utilisé DATE() car en prenant l'année dans la colonne B on part ainsi du 1° juillet au 31 décembre. Le 3 est un paramètre qui précisément indique qu'il faut compter une année à 365 jours    

Ainsi si tu multiplies cette fraction d'année par le montant des intérêts dus pour l'année complète tu obtiens bien le résultat c'est à dire :    

1998 2 506,85 €    
1999 2 053,17 €    
2000 1 576,81 €    
2001 1 076,63 €    
2002 551,45 €    


2/ La solution complexe

Il existe dans les fonctions d'analyse complémentaire d'Excel une fonction financière qui calcule directement le montant des intérêts non échus pas pour un prêt mais pour un placement. Ce qui au fond est la même chose.    

Voici cette fonction :    

=INTERET.ACC.MAT(DATE(B2;7;1);DATE(B2;12;31);5%;F2;3)    

Avec toujours pareil, l'année en B2, le taux, le K restant en F2 et le 3 qui est le même paramètre indiquant que l'on calcule avec des années à 365 jours.    

et Miracle cette fonction donne exactement le même résultat.    

Voila voila....    

Je précise que chez moi, en Excel 2003, pour avoir ces fonctions il faut aller dans le menu Outils/Macros complémentaires et cocher "utilitaire d'analyse"    


EDIT : J'ajoute encore une solution plutôt plus simple : 

Il existe la fonction DATEDIF(date_début; date_fin;"d") qui permet de calculer le nombre de jours entre 2 dates. 

D'ou la formule ci dessous pour calculer directement la fraction réelle en nombre de jours : 


=DATEDIF(DATE(B2;7;1);DATE(B2;12;31);"d")/DATEDIF(DATE(B2;7;1);DATE(B2+1;7;1);"d") 






A+    

Cordialement,

Aide sur la fonction RECHERCHE V ou H

8 réponses

Votre réponse

Discussions similaires

Newsletters