Comment automatiser une formule de recherche avec une plage variable?

**Malar** · 26/05/2016, 20h00

Bonsoir,

Je travaille sur un fichier me permettant d'automatiser le calcul de provisions mensuelles.
Nous recevons chaque mois un fichier contenant des échelles d'intérêts pour plusieurs sociétés, dans son format excel, ce fichier les présente toutes les unes à la suite des autres, sans espace particulier.
Je joins un fichier d'exemple pour que les explications suivantes soient plus compréhensibles.

Détail des onglets :
1) Résultat : c'est dans cet onglet que l'on veut récupérer des valeurs : le solde du dernier jour du mois et le montant total des intérêts du mois, j'ai identifié ces infos par des couleurs de cellule pour se repérer (jaune et vert).
2)Echelle d'intérêts du mois : c'est dans cet onglet que figure toutes les échelles d'intérêts que nous recevons, dans l'exemple 2. Le détail est indiqué comme suit : des lignes de présentation de la période et de la société concernée; puis viennent les soldes journaliers et enfin quelques informations dont le montant total des intérêts à payer.
3)Echelle d'Avril pour test : comme tous les mois n'ont pas le même nombre de jours, cet onglet me sert à vérifier que les formules marchent bien en copiant/collant les valeurs dans le 2ème onglet à la place de celles renseignées.

Mon problème : selon les mois, le solde du dernier jour du mois se trouve décalé dans le fichier car tous les mois n'ont pas 31 jours. L'idéal serait de trouver une solution pour qu'à partir du moment où le nom abrégé de la société apparaît et selon la date de situation (dernier jour du mois), la recherche trouve directement le solde correspondant et le montant des intérêts. Pour isoler le nom, j'ai des débuts de résultats avec la fonction stxt combinée à un cherche mais ça coince lorsque le nombre de jours et inférieur...

Désolée, je n'ai pas fait très succint. Mais j'espère avoir été relativement claire et que certains d'entre vous pourront m'aider en me trouvant une solution la plus simple et compréhensible possible, le tout sans passer par l'étape du code!

D'avance je vous remercie.

Bonne soirée,
Cdlt,
ML

**parmi** · 26/05/2016, 20h57

Bonjour,

En C8, écrire ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(MOIS(C$7)=MOIS('Echelle du mois'!$B$6);INDEX('Echelle du mois'!$B$3:$E$80;EQUIV("*"&$A7;'Echelle du mois'!$B$3:$B$80;0)+JOUR(C$7)+2;2);0)

En C9, ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(MOIS(C$7)=MOIS('Echelle du mois'!$B$6);INDEX('Echelle du mois'!$B$3:$E$80;EQUIV("*"&$A7;'Echelle du mois'!$B$3:$B$80;0)+JOUR(C$7)+6;4);0)

Ça me semble bien fonctionner avec le data tel que dans le fichier.
À modifier les plages selon la réalité: B3:E80 peut devenir B3:E10000

**78chris** · 26/05/2016, 21h26

Bonjour

En C8 à recopier à droite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(INDEX('Echelle du mois'!$B:$B;EQUIV(Résultat!C$13;DECALER('Echelle du mois'!$A$1;EQUIV("Entité: "&Résultat!$A7;'Echelle du mois'!$A:$A;0);0;100;1);0)+EQUIV("Entité: "&Résultat!$A7;'Echelle du mois'!$A:$A;0);1);0)

En C9

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(INDEX('Echelle du mois'!$D:$D;EQUIV(Résultat!C$13;'Echelle du mois'!$A:$A;0)+1+EQUIV("Entité: "&Résultat!$A7;'Echelle du mois'!$A:$A;0);1);0)

Recopier le ligne 8 en ligne 14 et la ligne 9 en ligne 15

Respecter toujours le même pas entre les sociétés : 6 lignes

Le souci est de définir l'onglet : tu n'as pas précisé comment sont nommés les onglets. Il faudrait adapter la formule pour que chaque colonne se réfère au bon onglet...

Edit : Pas rafraîchi. Parmi a été plus rapide mais on est raccord sur le principe.

**Malar** · 30/05/2016, 11h22

Bonjour,

Merci pour ces 2 solutions.
Je n'ai pas réussi à utiliser la 1ère, désolée Parmi, je dois avoir loupé quelque chose...

Pour la seconde, en adaptant un peu les formules données à mon cas concret (quelques différences dans les libellés et dans les nombres de lignes) ça marche! En revanche, serait-il possible d'avoir la "traduction" de la synthaxe de la formule svp? Je ne vois pas trop à quoi correspond le "+1" dans la formule permettant de retrouver le montant des intérêts.. Dans mon fichier, il a fallu que je rentre "+10" pour avoir la bonne valeur, mais j'avoue avoir rentré ce chiffre après plusieurs essais, un peu "au hasard".

Merci encore!

Bonne journée.
Cdlt,
ML

**78chris** · 30/05/2016, 15h18

Bonjour

Envoyé par Malar

...Pour la seconde, en adaptant un peu les formules données à mon cas concret (quelques différences dans les libellés et dans les nombres de lignes) ça marche!
En revanche, serait-il possible d'avoir la "traduction" de la synthaxe de la formule svp? Je ne vois pas trop à quoi correspond le "+1" dans la formule permettant de retrouver le montant des intérêts.. Dans mon fichier, il a fallu que je rentre "+10"
pour avoir la bonne valeur, mais j'avoue avoir rentré ce chiffre après plusieurs essais, un peu "au hasard"...

F1 est ton ami pour l'aide en ligne sur les diverses fonctions.

J'ai mal recopié mes formules :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(INDEX('Echelle du mois'!$B:$B;EQUIV(Résultat!C7;DECALER('Echelle du mois'!$A$1;EQUIV("Entité: "&Résultat!$A7;'Echelle du mois'!$A:$A;0);0;100;1);0)+EQUIV("Entité: "&Résultat!$A7;'Echelle du mois'!$A:$A;0);1);0)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(INDEX('Echelle du mois'!$D:$D;EQUIV(Résultat!C7;'Echelle du mois'!$A:$A;0)+1+EQUIV("Entité: "&Résultat!$A7;'Echelle du mois'!$A:$A;0);1);0)

Concernant le + 1 :
on cherche la dernière date du mois située sur la ligne 7 de la feuille résultat, qui donne la ligne 36 pour mars,
on cherche la nom de l'entité située en colonne 1 de la ligne 7 de la feuille résultat : ce qui donne 3 pour AGFIBEST
le total donne donc 39 pour un mois de 31 jours mais le chiffre recherché est une ligne plus bas d'où le +1

sur un mois de 28 jours le total donne 33+3+1 soit la ligne 37.

+10 me parait beaucoup sauf si ton cas réel est très différent de l'exemple (d'où la nécessité quand on poste un exemple, qu'il soit représentatif)

**Malar** · 30/05/2016, 18h58

Bonsoir,

Merci pour ces explications. Désolée pour les imprécisions du fichier test de départ, mais cela m'a bien aidé (et j'aime bien comprendre ce que fait la formule, un peu de grattage de tête n'est pas mauvais ^^). Je remets mon fichier un peu modifié, j'avais fait un exemple de tête, je ne me souvenais plus du nombre exact de lignes séparant celle où on trouve le solde et celle où on trouve le total des intérêts. Après vérif, il y en a 17. J'ai également mis le montant des intérêts, qui peuvent être débiteurs ou créditeurs mais le total correspond bien à leur somme.

En tout cas, ça m'a bien dépannée

Encore merci.

Bonne soirée,
Cdlt,
ML

**78chris** · 30/05/2016, 20h43

Bonjour

Le souci est que maintenant il devient difficile de trouver l'entité avec Entité: AGFIBEST (AGFI chez BEST) dans l'échelle du mois et AGFIBEST dans résultat.

Il faudrait faire la recherche en formule matricielle mais cela rame terriblement...

Il faut trouver un moyen d’homogénéiser les noms des entités dans les 2 onglets.

**Malar** · 31/05/2016, 08h57

Bonjour,

Non pas de souci de recherche du nom (merci Parmi) : "Entité: "&RésultatA7&"*" fonctionne très bien

C'est juste le +1 qu'il faut adapter dans la seconde formule pour bien trouver le total des intérêts, sur la ligne "total" car le même montant est repris plusieurs fois, mais c'est bien cette dernière ligne qu'il faut trouver.

Merci!
Bonne journée

ML