Help !! Imbriquer fonction SI comprenant des recherchev ??

[muhrtag]

Bonjour, je n'arrive pas à trouver la bonne formule pour ce que je souhaite faire... explication :

Sur une feuille j'ai un crédit avec des dates, et le montant restant à payer à ces dates.
Dans une autre feuille, je souhaite afficher pour ce crédit, le montant restant à payer chaque fin d'année.

J'ai donc utiliser une formule MOIS pour extraire le mois et année de chaque date du crédit, sachant qu'il n'y a qu'un remboursement par mois. Donc mon but est d'utiliser recherchev pour aller chercher le mois de décembre, puis le montant restant à payer... Jusqu'ici tout va bien, mais pour un autre crédit, le dernier remboursement de l'année se fait en novembre au lieu de décembre. Comment faire pour que la formule aille chercher décembre, puis s'il ne trouve pas, cherche novembre, et ainsi de suite, pour n'avoir que le montant restant à payer suivant le dernier mois de remboursement de l'année ???

Pour essayer de faire plus clair, il me faudrait une formule incluant des "RechercheV", qui sont testées une à une. Exemple :

Si (recherchev décembre) est FAUX, alors (recherchev novembre) si faux, alors (recherchev octobre) --> vrai donc sera affiché la valeur d'octobre

mais je n'arrive pas à imbriquer les si avec à l'intérieur des recherchev...

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonjour

Essayes en C13, en matriciel (donc à valider avec Ctrl+maj tempo+entrer), avec

SIERREUR(INDEX(DECALER(INDIRECT("'" & $B13 & "'!H1");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16);MAX(SI(ANNEE(DECALER(INDIRECT("'" & $B13 & "'!C17");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16))=C$11;LIGNE(INDIRECT("$17:"&EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16)))));"")

La méthode utilisée n'est pas du tout celle dont tu parles.
On compare toutes les années de la colonne credit 1!C avec celle en SUIVI DETTES!C$11 avec

SI(ANNEE(DECALER(INDIRECT("'" & $B13 & "'!C17");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16))=C$11

(2011 donc). J'ai mis une plage dynamique défini grace au DECALER (avec le début forcément en C17 et le nombre de lignes déterminés par EQUIV("zz", car si j'avais mis comme toi la fin de la plage en ligne 327, il y avait des problèmes avec les #VALEUR qu'il y a dans certaines de tes colonnes, en dessous des valeurs intéressantes).

La formule renvoie les n° des lignes pour lesquelles il y a égalité (aucune pour 2011), c-à-d lorsque le test ci-dessus renvoie VRAI.
On ne garde que le n° de la dernière ligne où il y a égalité avec MAX.

MAX(SI(ANNEE(DECALER(INDIRECT("'" & $B13 & "'!C17");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16))=C$11;LIGNE(INDIRECT("$17:"&EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16))))

On affiche le résultat désiré avec INDEX( colonne H; N°...).

On élimine les erreurs avec SIERREUR(INDEX( colonne H;N°...);"")

En D13, cela donne

SIERREUR(INDEX(DECALER(INDIRECT("'" & $B13 & "'!H1");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16);MAX(SI(ANNEE(DECALER(INDIRECT("'" & $B13 & "'!C17");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16))=D$11;LIGNE(INDIRECT("$17:"&EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16)))));"")

En D$11, on a 2012, donc le test renvoie les lignes 17, 18 et 19, le max garde 19 et la formule affiche le contenu de INDEX(credit 1!H1:H49;19) donc de credit 1!H19.

@ plus

P.S : Bien sûr, on peut simplifier la formule en nommant certaines parties qui se répètent souvent comme EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1) par exemple

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Merci, pour cette formule elle parait plus légère que la mienne, cependant j'ai ajouté un crédit et je ne comprends pas pourquoi la formule prend le mauvais chiffre... Je ne connais pas vraiment ces formules donc je n'arrive pas à trouver l'erreur. je remet le fichier en pj. Merci pour votre aide. et également pour les explications qui m'aident à comprendre la démarche !

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonsoir

Il y avait deux -16 en trop dans la formule en C13

SIERREUR(INDEX(DECALER(INDIRECT("'" & $B13 & "'!H1");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1));MAX(SI(ANNEE(DECALER(INDIRECT("'" & $B13 & "'!C17");;;EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1)-16))=C$11;LIGNE(INDIRECT("$17:"&EQUIV("zz";INDIRECT("'" & $B13 & "'!A:A");1))))));"")

@ plus

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonsoir

Comme les dates en colonne C sont classées dans l'ordre croissant, on peut faire avec un EQUIV(9^9 sur cette colonne.

En définissant deux noms, plageannee1, commençant en C1, et plageannée17, commençant en C17, la formule devient, toujours en C13 :

SIERREUR(INDEX(DECALER(Plageannée1;;5);MAX(SI(ANNEE(plageannée17)=C$11;LIGNE(plageannée17))));"")

avec les plages dynamiques
plageannee1=INDIRECT("'" & 'SUIVI DETTES'!$B13 & "'!C1:C"&EQUIV(9^9;INDIRECT("'" & 'SUIVI DETTES'!$B13 & "'!C:C");1))
plageannee17=INDIRECT("'" & 'SUIVI DETTES'!$B13 & "'!C17:C"&EQUIV(9^9;INDIRECT("'" & 'SUIVI DETTES'!$B13 & "'!C:C");1))

Ces dernières définitions sont plutôt longues pour pouvoir aller faire la recherche sur le bon onglet.

@ plus

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonsoir

Tant qu'à y être, et toujours parce que les dates en colonne C sont mises dans l'ordre croissant, on peut faire sans le MAX avec la formule suivante en C13, toujours en matriciel, à tirer vers le bas et vers la droite :

SIERREUR(INDEX(DECALER(plageannée17;;5);EQUIV(C$11+0,1;ANNEE(plageannée17);1));"")

@ plus

P.S : Pourquoi as-tu mis en vert toute la plage credit 1!C14:C30 ? Mes trois dernières propositions prennent en compte les formules cachées par cette couleur, ce qui ne te convient peut-être pas. A toi de me le préciser.

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Nn pas de problème pour les dates cachées en vert, au contraire ta formule est super ! elle fonctionne sans problème. Je ne sais pas pourquoi les lignes sont en vert. Encore merci !

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonjour, je suis toujours dans la construction de mon "outil" j'ai bien avancé grâce à CISCO, mais une fois de plus je suis bloqué
J'ai essayé avec les formules matricielles mais j'ai du mal. Je voudrais construire un planning/échéancier des dates de paiement (principal+intérêt) pour chaque crédit. Cette fois je voudrais pouvoir suivre les jour précis de prélèvement...

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonsoir

C'est-à-dire ?

@ plus

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonjour,
désolé du délai de réponse. Je voudrais dans l'onglet "échéancier" reproduire le même principe que l'onglet "suivi dettes" , mais en plus détaillé. Je m'explique : l'onglet "suivi dettes" représente des crédits et ce qu'il reste à rembourser en fin d'année en capital (sans les intérêts). Dans l'onglet "échéancier" je voudrais pouvoir visualiser les remboursements (capital+intérêts cette fois) à chaque date pour chaque année des différents crédits. J'essaye d'aller chercher les infos de la colonne D pour le montant à rembourser et la colonne C pour la date de remboursement ; et ce, dans chaque onglet représentant un crédit.

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonjour

Je pense avoir compris, mais bon, par précaution, pourrais-tu mettre quelques valeurs sur la feuille ECHEANCIER ? Je ne vois pas exactement combien de colonnes tu veux avoir par année.

@ plus

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Oui je suis en train d'y réfléchir. Je te met le fichier dès que je trouve la bonne mise en forme. Merci.

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Voila, j'ai trouvé une mise en forme. J'espère que c'est faisable. Comme le "suivi dette" on inscrirait le nom du crédit en haut cette fois, et les échéances apparaîtraient en bas. J'espère que je suis compréhensible Je met le fichier en PJ.

 

[CISCO]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Bonjour

Tu peux faire en ECHEANCIER!D6 avec

SIERREUR(INDEX(INDIRECT("'"&D$5&"'!D17:D76");EQUIV($B6;INDIRECT("'"&D$5&"'!C17:C76");0));"")

formule à tirer vers la droite et vers le bas.

J'ai mis 76, mais par précaution, tu peux mettre un nombre plus grand, permettant de couvrir correctement les plages utiles des feuilles "crédit".

@ plus

 

[muhrtag]

Re : Help !! Imbriquer fonction SI comprenant des recherchev ??

Parfait, ça marche ! Merci ! le 76 c'est pour la longueur de la zone crédit couverte c'est bien ça ?