Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

[cm1864]

Bonjour,

Après avoir reçu une précieuse aide et pas mal progressé sur de nombreux points, je reviens vers vous concernant la création de formules qui sont bien trop complexes à mon niveau.

Vous trouverez ci-joint un fichier comprenant deux onglets importants (SUIVI DOX et SUIVI CLIENTS). Les formules sont à placer dans l'onglet rouge : SUIVI CLIENTS.

Voici ma demande, qui vous est rappelée dans le fichier Excel :

Si C3 = "une date", Si C4 = "Solde", alors B8 affiche le nom du client qui doit payer son solde à la date du jour écrit en C3. Si plusieurs personnes doivent payer leurs solde le même jour, les noms s'affichent en B9, B10, B11, etc.
Nota Bene : Le solde correspond à la date de départ - 30 jours (date de départ située ici : 'SUIVI DOX'!G10:G2000)

Si C3 = "une date", Si C4 = "Avant départ", alors B8 affiche le nom du client que je dois appeler (pour lui souhaiter de bonnes vacances) à la date du jour écrit en C3. Si je dois appeler plusieurs personnes le même jour, les noms s'affichent en B9, B10, B11, etc.
Nota Bene : L'avant départ correspond à la date de départ - 5 jours (date de départ située ici : 'SUIVI DOX'!G10:G2000)

Si C3 = "une date", Si C4 = "Départ", alors B8:B13 affiche le nom des clients qui ont un dossier sur le mois indiqué en C3 (peut importe le jour, l'année, seul le MOIS importe !)

Si C3 = "une date", Si C4 = "Feedback", alors B8 affiche le nom du client que je dois appeler (pour savoir comment se sont passées les vacances) à la date du jour écrit en C3. Si je dois appeler plusieurs personnes le même jour, les noms s'affichent en B9, B10, B11, etc.
Nota Bene : L'avant départ correspond à la date de retour + 5 jours (date de départ située ici : 'SUIVI DOX'!G10:G2000).

Si C3 = "", Si C4 = "Emailing", Si C5 = "un Tour Opérateur" (exemple "Club Med") alors B8 affiche le nom de tous les clients ayant fait une réservation au Club Med pour que je les informe d'une éventuelle promotion intéressante, etc.. Le nom de tous les clients s'affichent en B9, B10, B11, etc.
Mais Si C3 indique une date "27 aug 2011", alors B8, B9, etc. affichent les noms des personnes ayant réservé en AOUT au CLUB MED (peut importe le jour, l'année, seul le MOIS importe !)

Je suis vraiment désolé, mais j'ai un véritable besoin de formules pouvant assimiler l'ensemble de ces scénarios dans un seul tableau, sans passer par le VBA (sauf si impossible), et sans TCD.

Je suis à votre entière disposition pour vous apporter plus amples informations si besoin.

Je vous remercie déjà pour le temps que vous me consacrerez.
MERCI POUR VOTRE AIDE, VRAIMENT.

Amicalement,
cm1864

PS. J'utilise Excel 2010 sur mon laptop pro mais mes conseillères utiliserons Excel 2003 à l'agence.

 

[CHALET53]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour,

J'ai commencé à développer sur la base d'une gestion événementielle qui s'active dès qu'une des 3 zones de saisie est modifiée
Elle appelle une procédure dans le module 1.
Quelques exemples sont développées (sans trop de contrôles) : Date et Solde, Emailing,
Feedback, Avant départ
Teste
Si c'est l'esprit et que tu peux poursuivre tout seul, à toi de jouer

a+

 

[Dranreb]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour.
En mettant en colonne Y de la feuille SUIVI DOX une formule qui commencerait par :

=SI(DateCslt<>"";SI(TypRelCslt="Solde";DateDépart-30=DateCslt);(SI(TypRelCslt="Avant départ";DateDépart+5=DateCslt);si(typrelcslt="Départ";...

à supposer que ses colonnes portent ces noms ainsi que les 3 zone de consultation, ça irait ?
Il serait alors possible de lister tous les VRAIs de cette colonne dans la feuille SUIVI CLIENTS
Et cela sans aucune programmation, juste une colonne de service à droite du tableau résultant portant les numéros de lignes à restituer, par un EQUIV simple sur la 1ère ligne, puis relatif au précédent sur les suivantes.
À+

 

[Monique]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour,

Sauf erreur, tous les critères sont rassemblés dans cette formule :

=(ESTNUM(CxDte))*((CxTyp="Avant départ")*(DteDep-CxDte=5)+((CxTyp="Solde")*(DteDep-CxDte=30))+(CxTyp="Départ")*(MOIS(DteCrea)=MOIS(CxDte))+(CxTyp="Feedback")*(CxDte-DteRet=5)+(CxTyp="Emailing")*(CxTo=To)*((MOIS(DteCrea)=MOIS(CxDte))+(CxDte="")))

Elle renvoie 0 ou 1 (Faux ou Vrai)

Cette formule est nommée « Critères »

Pour avoir le n° des dossiers :

=SI(SOMME(Criteres*1)<LIGNES(D$8:D8);"";INDEX(NoDoss;PETITE.VALEUR(SI(Criteres;LIGNE(Nom)-9);LIGNES(D$8:D8))))

Pour les autres rubriques, c’est Index Equiv de basant sur le n° des dossiers

A droite de la feuille : les étapes de la construction de la formule

 

[cm1864]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour !

J'ai eu peur que ce soit impossible vu le nombre d'affichages vs. réponses.

J'ai un niveau assez faible, alors je vais étudier l'ensemble de vos propositions et revenir vers vous dès que possible !
Merci beaucoup, à tout à l'heure.

cm1864

 

[cm1864]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Re bonjour,

CHALET53 : ta méthode utilise le VBA ; malheureusement je ne sais pas du tout utiliser ce langage et ne pourrait donc pas continuer seul ou améliorer en fonction de besoins futurs.

Dranreb : Je ne comprends pas bien le fonctionnement ...

Monique : Ta solution et le fichier sont incroyables ! Je ne maîtrise pas INDEX et EQUIV et encore moins lorsque ces formules sont imbriquées avec d'autres, ce qui implique que j'ai beaucoup de mal à comprendre comment cela fonctionne, mais j'y travaille !!

Si le scénario "Solde" "Avant départ" et "Feedback" fonctionne, les scénarios "Emailing" et "Départ" ne fonctionne pas.

1 / Si je sélectionne "Départ" en C4 et 01-05-2011 en C3, le résultat doit afficher 8 résultats car il y a 8 départ en MAI 2011. Actuellement, le tableau affiche 4 entrées avec des mois MAI AOUT JUIN.

2 / De sont côté le scénario "Emailing" n'affiche pas de résultats.

Mes questions :
1 / Peux tu m'aider à résoudre ces problèmes ?
2 / Le tableau est il modifiable en coupant / collant, en insérant des lignes / colonnes ?
3 / Puis-je supprimer les données à droite et sous le tableau pour faciliter l'utilisation à mes collègues ?
4 / Si je dois rajouter des scénarios (ce qui est probable à l'avenir), est-ce possible ou faut-il tout recommencer ?

Je vous remercie pour votre précieuse aide, c'est un travail remarquable et la générosité dont vous faites tous preuves ici est surprenante, je ne suis pas habitué à utiliser de forum et suis vraiment impressionné !

MERCI BEAUCOUP

Amicalement,
cm1864

 

[Dranreb]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Dranreb : Je ne comprends pas bien le fonctionnement ...

Ça fonctionnerait avec une formule en Y à toutes les lignes de SUIVI DOX et une formule avec des EQUIV en M dans la SUIVI CLIENT. Les données seraient reproduites à l'aide de INDEX comme dans la solution de Monique.

Monique, dans votre solution brillante, ne vaudrait-il pas mieux quand même ne calculer qu'une fois EQUIV($D8;NoDoss;0), en M8 par exemple ?

À+

 

[Monique]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Re,

J’ai pris le mois de création pour le mois de départ…
La formule nommée « criteres » devient :

=(ESTNUM(CxDte))*((CxTyp="Avant départ")*(DteDep-CxDte=5)+((CxTyp="Solde")*(DteDep-CxDte=30))+(CxTyp="Départ")*(MOIS(DteDep)=MOIS(CxDte))+(CxTyp="Feedback")*(CxDte-DteRet=5)+(CxTyp="Emailing")*(CxTo=To)*((MOIS(DteCrea)=MOIS(CxDte))+(CxDte="")))

Je vois la suite tout à l’heure

Dranreb,
j'avais commencé comme ça. En colonne A il y avait la formule qui est en Z ou AB ou AE (c'est la même plus ou moins nommée). Je ne sais pas pourquoi j'ai abandonné cette méthode.

 

[Monique]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Re,

Les deux critères Email ont bien fonctionné ensemble
Mélangés aux autres ça ne fonctionnait plus
J'ai fini par les mettre avant les autres et ça marche. Pourquoi ?

Ce qui ne fonctionne plus, c'est le nb de lignes répondant aux critères (là où on demande à la formule de ne pas calculer et de laisser la cellule vide).
La formule donnant les n° de ligne est donc en M8:M14

Tu peux supprimer tout ce qu'il y a à droite et en bas de la feuille
(à droite, c'est le l'info, en bas c'est pratique quand on ne connaît pas le fichier)
Tu peux couper, coller, insérer etc.
Mais si tu insères ou supprimes en feuille de suivi, au-dessus de la ligne de titres, tu devras modifier la formule en M9:M15
Si tu insères, il faudra mettre 10, 11, 12, etc. à la place de 9
Si tu supprimes, ce sera 8, 7, 6 etc.

Si tu veux ajouter d'autres critères, tu aurais peut-être intérêt à l'ajouter, une fois nommé, dans la formule en M8:M15
Je viens de faire un essai et ça fonctionne comme ça :
=SI(ESTERR(PETITE.VALEUR(SI(Criteres+CritF;LIGNE(Nom)-9);LIGNES(M$8:M8)));"";PETITE.VALEUR(SI(Criteres+CritF;LIGNE(Nom)-9);LIGNES(M$8:M8)))

 

[cm1864]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonsoir,

Merci pour vos réponses ! Je regarde tout ça ce soir, je propose le test demain et mardi à mes collègues, et je reviens vers vous dès que possible pour vous dire si tout est okay.

Merci énormément pour votre aide, c'est vraiment très gentil car je ne suis vraiment pas assez compétent pour le moment pour créer de telles fonctions.

A bientôt, bonne soirée,
Amicalement,
cm1864

 

[cm1864]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour Monique,

Je te remercie une fois de plus pour ton aide.
J'ai voulu ajouter à mon tableau complet la feuille que tu as créé pour la mise en forme, les formules restent comme à l'origine mais plus rien ne fonctionne.

J'ai retourné le problème dans tous les sens, mais il semble existe une liaison que je ne trouve pas ...
Alors voilà, tu trouveras en pièce jointe mon tableau complet (il y a des feuilles en plus), peux tu résoudre ce problème sur ce fichier ou faut il que je reconstruise tout ??

Merci beaucoup à l'avance !!
A tout à l'heure,

Amicalement,
cm1864

 

[Monique]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour,

Pas évident à faire mais il n’y a plus de liaisons.

Plages nommées en "Suivi Dox" (les formules ne calculent plus que sur le nb de lignes nécessaire)
Noms utilisés dans les formules de la feuille « Evolution » seulement, à toi de faire la même chose en feuille « Suivi annuel »

Quelques formules modifiées pour ne plus avoir à les valider par ctrl, maj et entrée.

Tu avais des formats conditionnels (des flèches de couleur) qui ne sont pas compatibles avec Excel 2003.

 

[Monique]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour,

Les plages nommées à la place des références de cellules : fini
(le fichier a perdu du poids)

La plage nommée "Criteres"
La formule, avant d'être nommée, faisait 244 caractères, juste au-dessous de la limite (248 ou 256, je ne sais jamais)

Autre méthode : nommer chaque critère ou série de critères de façon indépendante

CritereAvantDepart =((ESTNUM(CxDte))*((CxTyp="Avant départ")*(DteDep-CxDte=5)))
CritereDepart =(CxTyp="Départ")*(MOIS(DteDep)=MOIS(CxDte))
CritereEmailing =(CxTyp="Emailing")*(CxTo=To)*((MOIS(DteCrea)=MOIS(CxDte))+(CxDte=""))
CritereFeedback =(CxTyp="Feedback")*(CxDte-DteRet=5)
CritereSolde =(CxTyp="Solde")*(DteDep-CxDte=30)

Et rassembler toute la série sous cette forme :
CriteresTous =CritereAvantDepart+CritereDepart+CritereEmailing+CritereFeedback+CritereSolde

Si tu veux ajouter d'autres critères, c'est beaucoup plus évident
Dans le fichier joint, tu as le choix entre les deux, colonnes M et N (formule raccourcie, d’ailleurs)

 

[cm1864]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour Monique,
Je suis de retour en France, et je te remercie vraiment énormément pour ton aide.
J'ai pu faire testé à mes vendeuses, elles apprécient le système. Je dois encore me replonger dans le tableur pour bien le comprendre.
Merci, merci, merci ... mille fois !
Peut-être à bientôt, bonne journée, bye.
Amicalement,
Paul

 

[Monique]

Re : Fonction de recherche en fonction de 5 scénarios différents et 3 variables !

Bonjour,

Bien contente que ça te convienne

J’ai pensé à une autre méthode : des cases à cocher, ça me semble plus intuitif.

Tu as, pour l’instant, 5 séries de critères
Ils sont, tous les 5, en ligne 1 (on peut en ajouter)
Tu coches 1 case (on ne peut pas en cocher plus d’une à la fois, de toute façon)
La cellule ou les deux cellules où tu dois effectuer un choix (date ou Tour Op) se met ou se mettent en couleur. (Lignes 1 et 2)

Et la suite est la même.

Les nouvelles cellules nommées, formules nommées et la formule qui englobe le tout sont en feuille « Listes » (Les noms sont un peu différents mais j’ai supprimé les autres)