Création de formules reprenant le nom de la colonne et non le n° de cellule
Bonjour à tous,
je me tire les cheveux depuis ce matin.
En effet, dans le cadre de mon activité pro, je dois effectuer un traitement de données préalable avant transmission aux équipes et j'aimerai l'automatiser via excel.
J'extrais une base de données reprenant des info comme "n° offre, montant de l'offre, direction commerciale, nom du client, adresse client, clé de groupe" pour les colonnes qui nous intéressent.
J'aimerai pouvoir mettre en évidence les clients à ne pas relancer (ou l'inverse peu importe) selon plusieurs critères :
- au moins une offre comprise entre 500 et 3500€
- selon la direction commerciale certains clients sont "exclus", que ce soit via le nom du client, le code client ou la clé de groupe.
Le code client est généré par la concatanation de nom client + adresse (ça c'est ok, je maitrise!)
1ere colonne : vérification que la condition montant est ok : formule NB.SI.ENS (ok elle fonctionne)
exclusions DR 2101 et 2116 : Formule si(et(k2=2101;(ou(estnum(cherche("client1";L2));[...];"Exclus";"ok")
Là où ça se corse, c'est qu'une fois tous mes clients exclus renseignés dans ma formule, Excel me l'a refusé car trop d'arguments. J'ai donc "scindé" ma formule en 2, avec l'insertion d'une nouvelle colonne. Et là... Catastrophe ! Toutes mes références à des numéros de cellule se sont retrouvées erronées dans mes formules précédentes car colonnes décalées ! (et je risque de devoir rajouter des colonnes si de nouvelles directions commerciales se rajoutent!)
J'ai alors pensé à créer des plages nommées, mais même problème, font référence à des cellules/colonne.
J'ai alors mis mes données sous forme de tableau afin de taper mes formules selon la syntaxe suivante
: =SI(ET(K2=2116;OU(ESTNUM(CHERCHE("CLIENT1";Tableau1[[#En-têtes];[Client : Nom du client]])
Mais ça n'a pas l'air très efficient...
Si vous pouviez m'aider ça serait bien sympathique :)
je vous joins un extrait de fichier pour vous donner une idée de la bête
Bonjour Nanomoy,
Vous n'utilisez pas le TS et les formules comme il faut
Voici ce que vous aviez en A2
=NB.SI.ENS(J:J;">=500";J:J;"<=3500";M:M;M2)Voici ce qu'il faut avoir
=NB.SI.ENS([Montant];">=500";[Montant];"<=3500";[[Code client ]];[@[Code client ]])A+
Merci pour ce retour!
Le problème venait surtout de ma recherche de client qui ne fonctionne pas pour tous, sans que je n'en trouve l'erreur.
de plus, j'ai des clés de groupes reprenant cette forme AlexA - AlexB... et j'ai l'impression qu'il n'y qu'ALEX tout court qui soit cherché
EDIT : dans le doute je viens de tenter cette formule : =SI(ET(K2=2101;OU(ESTNUM(CHERCHE("CLIENT1";Tableau1[[#En-têtes];[Client : Nom du client]]));ESTNUM(CHERCHE("CLIENT2";Tableau1[[#En-têtes];[Client : Nom du client]]));ESTNUM(CHERCHE("CLIENT3";Tableau1[[#En-têtes];[Client : Nom du client]]))));"Exclus";"OK")
Et... ce fut un échec, mes noms de clients ne sont pas trouvés
bonjour,
si j'ai bien compris (on ajoute un tableau des clients à exclure)
Bonjour
Il faut lister à part les clients exclus selon que l'exclusion est liée à une direction, un montant... (il faut voir quelles colonnes sont nécessaires) et utiliser cette liste pour simplifier la formule
Tu as les 2 même formules en C et D : quel intérêt ?
Je n'ai pas le même résultat que toi
On pourrait simplifier en listant les clients de type CLIENTx-1 mais je n'ai pas compris le pourquoi des ces index
Edit : que du beau monde ! Bonjour à tous
Bonjour,
rajout du tableau à exclure, oui mais sous quelle forme ? car il faut différencier les différentes directions commerciales ?
J'ai les 2 mêmes formules pour DR 2116 -1 et DR 2116 -2 car j'ai été bloqué dans le nombre d'arguments possible, j'ai donc mis une partie des clients exclu dans la colonne DR 2116-1 et l'autre dans DR 2116-2
Pour reprendre l'intégralité du message ci-dessus et y répondre correctement :)
Je ne dois garder que les clients ayant au moins une offre entre 500 et 3500 € et ne faisant pas partie de mes exclusions.
Les exclusions peuvent être trouvées selon la raison sociale (nom du client) OU le code client OU la clé de groupe.
Je ne comprends pas la dernière phrase par contre "On pourrait simplifier en listant les clients de type CLIENT x-1...".
Et enfin, pourquoi n'avons nous pas le même résultat :(
RE
Tes conditions ne sont pas très claires pour moi concernant les montants : on ne relance que cette tranche si pas dans la liste des exclus ou bien cette tranche est aussi un motif d’exclusion car tu dis une chose et son contraire
"J'aimerai pouvoir mettre en évidence les clients à ne pas relancer (ou l'inverse peu importe) selon plusieurs critères :
- au moins une offre comprise entre 500 et 3500€"
"Je ne dois garder que les clients ayant au moins une offre entre 500 et 3500 € et ne faisant pas partie de mes exclusions."
Ma question porte sur "pourquoi y a t-il CLIENT1 et CLIENT1-1" : ces noms n'ont aucun sens pour nous, les tirets et les chiffres encore moins... donc difficile d'être efficace dans ces conditions... et si ce sont des clients différents alors ils doivent être tous listés dans les exclusions et non traités depuis la racine du nom...
Re,
dans le fichier j'ai inscrit client1 et client1-1 car que ce soit dans mes noms de clients ou mes clés de groupe originelles, j'ai plusieurs magasins de grande chaine qui sont inscrit de cette manière selon le secteur facturé chez eux et j'ai l'impression qu'en ne cherchant que "Grandmagasin", GrandmagasinA ou B n'est pas trouvé
exemple : GrandmagasinA pour l'accueil
GrandmagasinB pour le rayon frais...
Grandmagasin A et B étant le même magasin.
RE
Dans ce cas mets tout dans la liste ce sera plus simple que de découper et surtout plus rapide en calcul
Tu n'as pas répondu concernant les conditions d'inclusion par rapport au montant...
oups désolée !
voici la bonne version : "Je ne dois garder que les clients ayant au moins une offre entre 500 et 3500 € et ne faisant pas partie de mes exclusions."
Et pour le découpage, je peux avoir 15 sortes de "Grandmagasin" ou de "grandsupermarches", je voulais donc réduire le nombre d'arguments de mes fonctions afin de tout englober et éviter les loupés si "grandmagasinZ" était non prévu dans la liste mais apparait (mon client arrive à mettre plein de dérivation possible dans les noms de clients ou clés de groupe, et la consigne reçue est "on exclue tous les "Grandmagasin")
RE
J'ai laissé sur la racine mais sinon cela raccourcirait la formule te le temps de calcul comme déjà dit
=SI(ET(ESTNA(INDEX(Exclusions;EQUIV([@[Direction Commerciale]]&[@[Client : Nom du client]];Exclusions[Direction]&Exclusions[Client];0);1));[@Montant]>=MIN(Limites[Montant]);[@Montant]<=MAX(Limites[Montant]));"";"NE PAS ")&"RELANCER"au lieu de
=SI(ET(ESTNA(INDEX(Exclusions;EQUIV([@[Direction Commerciale]]&SIERREUR(GAUCHE([@[Client : Nom du client]];CHERCHE("-";[@[Client : Nom du client]])-1);[@[Client : Nom du client]]);Exclusions[Direction]&Exclusions[Client];0);1));[@Montant]>=MIN(Limites[Montant]);[@Montant]<=MAX(Limites[Montant]));"";"NE PAS ")&"RELANCER"Sinon une liste des relances est faisable via PowerQuery (intégéré à Excel) ce qui évite les temps de calcul permanents
Je n'ai pas de pc à titre perso donc je regarde ça lundi après midi.
un grand merci pour le temps pris !
Bonjour, ça a l'air de correspondre à ce que je voudrais oui :)
par contre, j'aimerai également exclure une colonne appelée "clé de groupe" (qui correspond à un regroupement de plusieurs clients d'un même secteur ou groupe commercial), pourriez-vous me dire où le rajouter dans la formule ?
Ne comprenant pas ce que vous avez fait, c'est compliqué pour moi.
Et je ne maitrise absolument pas PowerQuery...
Bonjour
par contre, j'aimerai également exclure une colonne appelée "clé de groupe" (qui correspond à un regroupement de plusieurs clients d'un même secteur ou groupe commercial), pourriez-vous me dire où le rajouter dans la formule ?
Cette colonne n'existe pas dans le classeur fourni donc difficile de répondre... Ou alors c'est déjà ce qui est dans la formule que tu appelles clé et moi racine...Ne comprenant pas ce que vous avez fait, c'est compliqué pour moi.
Et je ne maitrise absolument pas PowerQuery...
Si tu ne maîtrises, ni les formules aavnacés, ni PowerQuery, l'un ou l'autre ne te poseront ni plus, ni moins de difficultés...