Problème de recherche multi-critères

[dontpanic]

Bonjour,

mon problème est le suivant :

Je dispose d'un journal de banques (feuille 1) où à chaque écriture de trésorerie mon solde se calcule automatiquement grâce à une formule matricielle.
Je précise que dans le solde se calcule différemment pour chaque banque sélectionnée dans le même journal. (colonne M = Banques, colonne N = soldes, colonne J = date de paiement).
Donc mon solde se calcule pour chacune des 3 banques dans le même journal de trésorerie à chaque ligne saisie.

Dans ma feuille 2, je souhaite instituer un contrôle des soldes, à savoir :

- 1 tableur reprenant les soldes inscrits manuellement sur mes relevés de compte papier avec d'une part sur chaque ligne en colonne 1 le dernier jour de chaque mois de l'année et d'autre part, 1 colonne par banque pour inscrire mes soldes en face du dernier jour du mois.

- 1 tableur sensé reprendre les soldes calculés dans le journal pour chaque banque, sur le même format que le premier tableur où j'incris manuellement les soldes de mes relevés de compte.

Mon problème est donc le suivant : j'ai beau essayer de faire fonctionner une recherche en matricielle ou pas, je n'y arrive pas.

A savoir que la fonction doit intégrer les éléments suivants :

Je recherche la valeur de la colonne N dont la date en colonne J est la plus proche du dernier jour du mois (critère inscrit dans la première colonne du tableau de la feuille 2) et dont la banque en colonne M est égale à la cellule contenant le même nom de banque en feuille 2.

Bref autant dire que je n'y arrive pas et je vous remercie grandement pour votre aide le cas échéant.
N'hésitez pas si besoin de plus d'informations.

Merci

 

[Tibo]

Re : Problème de recherche multi-critères

Bonjour dontpanic et bienvenue sur XLD,

Le plus simple pour se faire aider est de joindre un extrait de ton fichier (sans données confidentielles).

La description que tu fais est peut-être complète, mais elle oblige à reconstituer le fichier avec un risque que ça ne soit pas exactement le même fichier que le tien.

A te (re)lire donc avec un extrait de ton fichier.

@+

 

[Hippolite]

Re : Problème de recherche multi-critères

Bonjour et bienvenue sur le forum,
Un petit fichier joint (sans infos personnelles) faciliterait la réponse.

 

[hbenalia]

Re : Problème de recherche multi-critères

Bonjour à tous,

Essayes avec la formule matricielle suivante:

=index(Feuille1!$N$1:$N$1000;equiv(1;(Feuille1!$J$1:$J$1000=A2)*(Feuille1!$M$1:$M$1000=B2);-1))

en supposant que dans la feuille 1 les données sont dans la plage A1:M1000, et dans la feuille 2, la date est en A2 et le nom de la banque est en B2

Cordialement

 

[dontpanic]

Re : Problème de recherche multi-critères

Bonjour et merci pour vos réponses déjà très rapides.

Alors en fait, vous verrez dans le fichier joint que j'ai réussi à récupérer un solde, néanmoins je suis face à un problème.

D'abord il a fallu que je récupère la date effective de la dernière écriture du mois, ce qui m'a permis avec une formule sommeprod de récupérer le solde de cette ligne.
Sauf que quand j'ai deux écritures le même jour, je n'ai que le 1er solde alors que je voudrais avoir celui de la dernière écriture.
Il a aussi fallu que j'ajoute des conditions car si j'ai pas d'écriture pour la banque dans le mois en cours il fallait que ça me donne le solde précédent, et idem pour les soldes suivants il fallait pas que ça me répète un solde qui n'était pas encore arrivé.

En ce qui concerne la formule matricielle index que j'ai adaptée bien entendu, j'obtiens un résultat #N/A.

Bref peut-être que vous y verrez un peu plus clair avec ça, j'ai laissé pas mal de données pour que vous puissiez voir quels types de problèmes je peux rencontrer lors de ma saisie.

Merci à tous,

 

[Tibo]

Re : Problème de recherche multi-critères

Bonjour,

Pas sûr de tout avoir compris.

Peut-être ceci en M16 :

=SI(L16="";"";SOMMEPROD(($L$16:$L16=$L16)*($I$16:$I16))+SOMME.SI($L$11:$L$15;$L16;$M$11:$M$15))

à recopier vers le bas.

Si pas ça, reviens avec plus de précisions sur tes souhaits.

@+

 

[dontpanic]

Re : Problème de recherche multi-critères

Merci beaucoup pour ta réponse.

En fouillant un peu le forum (merci il est vraiment génial ce forum !!), j'ai trouvé une formule matricielle qui me permet de supprimer mon petit tableau où je récupère les dates.

En effet, je fais d'une pierre deux coups puisque plus besoin de ce tableau de dates, et ça me prend ma dernière ligne :

{=INDEX(Journal!$M$10:$M$74;EQUIV(2;1/('Contrôle Soldes'!$H$8=Journal!$L$10:$L$74)*1/(Journal!$J$10:$J$74<='Contrôle Soldes'!G10)*1/(Journal!$J$10:$J$74>'Contrôle Soldes'!G9)))}

Bon bien entendu faut que j'ajoute un petit si(esterreur(... pour pas que j'ai du #N/A dans le reste du tableau quand j'ai pas de solde à la date voulue, mais à part ça ça fonctionne.

Néanmoins, il reste un petit détail.
Admettons que j'enregistre une écriture après la dernière qui donne le solde, mais dont la date est antérieure à cette dernière :

date du dernier solde 17/01/2011, solde = 85.000
nouvelle écriture : date 15/01/2011, solde = 15.000

Pour le coup, je me retrouve dans mon tableau avec le solde à 15.000 au lieu de 85.000.

Est-il possible d'ajouter une condition qui limite la date au maximum du mois?
J'ai essayé d'ajouter une fonction max pour l'intervalle de date, mais ça ne change rien.

Merci,

 

[Tibo]

Re : Problème de recherche multi-critères

re,

Je ne sais pas où cette formule doit être placée.

Je te propose celle-ci en N10 onglet Contrôle Soldes :

=INDEX(Journal!$M:$M;MAX(SI((Journal!$J$11:$J$73<=$G10)*(Journal!$J$11:$J$73<>"")*(Journal!$L$11:$L$73=N$8);LIGNE(Journal!$A$11:$N$73))))

Formule matricielle à valider par CTRL + MAJ + ENTREE

à recopier vers le bas et vers la droite.

Je te laisse tester et vérifier.

Si pas ça, reviens avec plus de détails (où cette formule doit aller, que doit-elle donner, ...)

@+

 

[dontpanic]

Re : Problème de recherche multi-critères

Après un essai avec ta formule, le résultat est le même.

Je te rejoins le fichier avec la formule que j'ai donnée tout à l'heure bien positionnée dans la feuille Contrôle Soldes colonne N10:N25 pour le moment.

Dans le journal tu verras la dernière ligne d'écriture avec une date au 15/01 alors que la précédente est au 17/01. Mon solde se calculant suivant une autre formule en fonction des dates et de la Banque, il est juste à la date de la ligne en question.

Mais pour le coup, dans mon contrôle solde en cellule N14, il ne me sort pas le solde de la ligne du 17/01 mais celle du 15/01 qui est la dernière enregistrée.

Bon, tu pourrais me dire que j'ai qu'à enregistrer mes écritures dans l'ordre et insérer des lignes au bon endroit le cas échéant, mais j'anticipe sur d'éventuelles erreurs qu'il pourrait y avoir à la saisie ^^

J'espère que mes explications sont claires car je vais avoir du mal à faire mieux en explications

Merci beaucoup.

 

[Tibo]

Re : Problème de recherche multi-critères

re,

Une autre tentative pour gérer les dates qui ne seraient pas triées ainsi que les doublons.

en N10 :

=SI(SOMME((TEXTE(Journal!$J$11:$J$73;"mmaa")=TEXTE('Contrôle Soldes'!$G10;"mmaa"))*(Journal!$L$11:$L$73='Contrôle Soldes'!N$8))=0;"";INDEX(Journal!$M$11:$M$73;EQUIV(MAX(SI((MOIS(Journal!$J$11:$J$73+LIGNE(Journal!$J$11:$J$73)/9^9)=MOIS($G10))*(ANNEE(Journal!$J$11:$J$73+LIGNE(Journal!$J$11:$J$73)/9^9)=ANNEE($G10))*(Journal!$L$11:$L$73=N$8);Journal!$J$11:$J$73++LIGNE(Journal!$J$11:$J$73)/9^9));Journal!$J$11:$J$73+LIGNE(Journal!$J$11:$J$73)/9^9;0)))

Toujours matricielle

à recopier vers le bas et vers la droite

@+

 

[dontpanic]

Re : Problème de recherche multi-critères

C'est super tu m'as solutionné tous mes problèmes !!
Car oui j'en avais un autre mais avec ta formule j'ai pu ajouter un petit quelque chose ^^

Si je n'avais pas d'écriture pour le compte durant un mois par exemple, je me retrouvais à avoir un solde à 0 dans mes cellules Q15 Q14 et Q13.

Aussi, voilà comment j'ai transformé ta formule dans la cellule Q15, que j'ai ensuite copié :

=SI(SOMME((TEXTE(Journal!$J$11:$J$73;"mmaa")=TEXTE('Contrôle Soldes'!$G15;"mmaa"))*(Journal!$L$11:$L$73='Contrôle Soldes'!Q$8))=0;SI((AUJOURDHUI()>$G14);Q14;"");INDEX(Journal!$M$11:$M$73;EQUIV(MAX(SI((MOIS(Journal!$J$11:$J$73+LIGNE(Journal!$J$11:$J$73)/9^9)=MOIS($G15))*(ANNEE(Journal!$J$11:$J$73+LIGNE(Journal!$J$11:$J$73)/9^9)=ANNEE($G15))*(Journal!$L$11:$L$73=Q$8);Journal!$J$11:$J$73++LIGNE(Journal!$J$11:$J$73)/9^9));Journal!$J$11:$J$73+LIGNE(Journal!$J$11:$J$73)/9^9;0)))

En tout cas un grand merci !