Créer une mise en forme conditionnelle "très" spécifique par semaine
Bonjour à tous (ou rebonjour :) )
Je continue la création de mon tableau de commandes mais je bloque toujours...
Ceci est un excercice pour les pros d'Excel...
Sur ce tableau, je dois faire surbriller automatiquement des lignes de clients spécifiques, uniquement si le chiffre d'affaire total du dépôt1 , PAR SEMAINE dépasse un seuil d'alerte, prédéfini au préalable par le directeur... et ceci à répéter pour chaque semaine (comme si ce n'était déjà pas assez compliqué comme cela pour moi...)
Sur le tableau en pièces jointes, si une commande est passée pour le dépôt1 et uniquement pour ce dépôt, je dois mettre une croix en colonne M. Si la commande ne contient qu'une partie des produits du dépôt1, je dois mettre une croix dans la colonne N. (jusque là, je n'ai besoin de personne, je me débrouille comme un grand
Là où cela se complique, ce sont les mises en surbrillance d'une partie des clients (ou une catégorie de client) qui ont commandés des produits du dépôt 1 si le chiffre d'affaire de la semaine concernée dépasse la limite fixée par mon directeur pour cette semaine là...
Si je devais résumer par formule "simple" : (et là, je sens que je vais en perdre les 3/4 :) )
Si les clients, ayant une croix dans la colonne M ou N, ne font pas partie de la liste des magasins prioritaires (voir tout en bas du tableau, onglet "Commandes2021"), ET si (et uniquement si) le CA de la semaine du dépôt 1 est supérieur à la limite programmé par le Directeur (voir onglet "CA + Alerte"), ALORS, les clients, qui ne font pas parties de la liste des magasins prioritaires, seront sur-brillés en rouge.
Pour celles et ceux qui sont restés jusque là, et qui aurait trouvé une formule magique, je leur dit déjà "chapeau bas l'artiste"
Mais il resterait encore une embûche... Celle de trouver une formule générale pour qu'elle se répercute en une fois (si c'est possible, bien sûr) pour les 52 semaines de l'année...
Cette modification de ce tableau est uniquement pour m'aider dans mon travail car je gère les saisies de ces commandes et doit prévoir, à l'avance, et au fur et à mesure des saisies, de décaler des rdv de livraisons, uniquement pour certaines enseignes (mais pas touche aux magasins prioritaires, pour mon directeur
j'ai demandé si je pouvais changer ce tableau, le faire par mois ou tout simplement accumuler le total pour l'année. Il m'autorise à rajouter des formules, des couleurs, sur ce tableaux, mais interdiction de toucher au colonnes et à la "répartition" par semaine des commandes... Il ne souhaite pas non plus, que j'utilise le mode filtrage avec l'entonnoir, car il veut que le tableau reste visible dans son entièreté..
Pour l'exemple, je n'ai mis que 12 ou 13 commandes par semaine, mais dans la réalité, il y a plus de 150 à 200 commandes par semaine, ce qui me rend fou lorsque je dois voir qui est prioritaire ou non ...
Pour cela, j'ai eu l'idée de surbriller ces clients "non prioritaires" pour les mettre en évidence si le total de la semaine pour le dépôt 1 est atteint, et ainsi décaler leurs dates de livraison...
J'espère que parmi vous, quelques uns pourront m'aider à me sortir de ce pétrin...
Merci, par avance
Bonjour,
J'ai bien ça comme premier essai mais je ne vois pas comment rendre automatique la fin de la MEFC pour lier les semaines, en gros renvoyer Q22 et C4 ...
=ET(NB.SI($B$87:$B$96;$E4)=0;NB.SI($M4:$N4;"X")>0;$Q$22>'CA + ALERTE'!$C$4)La construction du tableau est complexe et aurait largement pu être simplifiée par un TCD et une vraie BDD ... Ton responsable ne doit pas maîtriser les fondements d'Excel qui est un tableur ..
Si j'arrive à rattacher une colonne à la semaine alors peut être que ... Comment sais-tu quelle commande entre dans quelle semaine ? C'est basé sur la date de confirmation ou la date de livraison ?
Bonne journée.
Mon entreprise utilise ce tableau (qu'elle sauvegarde pour l'année concernée puis supprime les données et le réutilise pour l'année d'après) depuis quelques années... Peut être que c'est mon directeur qui l'a fait et pour cela il ne veut pas qu'il soit modifié
Je suis dans l'entreprise que depuis deux mois. Mais sans vouloir m'imposer, je souhaite changer la façon de faire sur le suivi des commandes avec ce tableau, surtout que c'est moi qui le remplis tous les jours et qui doit ensuite trouver les lignes des clients non prioritaires pour décaler les dates de livraison...
Pour les commandes saisies dans le tableau, je saisie en fonction de la date de livraison (exemple : pour une livraison demandée le 05/01/2021, je la place en semaine 1)
Bonjour,
Ta réponse était ce qui m'intéressait merci.
Du coup ci-joint un test, je te laisse me faire un retour en conséquence. Je n'ai tiré la MEFC que jusqu'à la semaine 02 car j'ai dû modifier les valeurs afin d'avoir des dates de livraisons représentatives de ta réponse.
Seul modification que j'ai apporté : même écriture de semaine, 1 devient 01, ou 01 devient 1 dans ces cas là pas besoin de la fonction TEXTE().
En attendant ton retour.
Bonne journée.
Bonjour,
ton tableau ne se prête pas facilement aux analyses et synthèses car il ne respecte pas les règles d'une BDD correcte.
A lire : http://www.xlerateur.com/divers/2010/05/14/les-13-regles-d%E2%80%99or-pour-utiliser-excel-comme-gest...
Il faudrait donc supprimer tes lignes intermédiaires de totaux, ajouter une colonne N° Semaine pour simplifier les traitements.
Tes totaux semaine se feront ensuite avec un Tableau Croisé Dynamique (TCD) : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=109
Prend le temps de lire et réfléchis-y
eric
@Ergotamine : Trop enthousiaste de pouvoir voir le résultat !! Mais excel me fait toujours des messages d'erreurs lorsque je tente d'ouvrir un fichier téléchargé
Mon pc a de la mémoire... J'arrive à ouvrir ou enregistrer mes propres documents... Mais pas ceux téléchargés
peux tu m'envoyer ici le code que tu as trouvé stp ?
@eriiic :Merci de ton retour. Je vais potasser les liens que tu me proposes :)
Bonjour,
Dans ce cas la MEFC de appliqué sur $C$4:$C$42 :
=ET(NB.SI($B$87:$B$96;$E4)=0;NB.SI($M4:$N4;"X")>0;INDIRECT(ADRESSE(EQUIV(CONCATENER("TOTAL SEMAINE ";TEXTE(NO.SEMAINE($L4;2);"00"));$F:$F;0);17))>RECHERCHEV(CONCATENER("SEMAINE ";TEXTE(NO.SEMAINE($L4;2);"00"));'CA + ALERTE'!$B$4:$E$55;4;FAUX))F22, F42, etc ... Doivent contenir TOTAL SEMAINE 01, TOTAL SEMAINE 02, etc ... Pour le reste je n'ai rien modifié.
Bonne journée.
Ca ne marche pas chez moi :(
pourtant pour voir tout de suite je résultat, j'ai fait simplement un copier coller dans une nouvelle règle MFC et appliquer sur la plage voulue... mais rien... j'ai changé les limites, mais toujours rien...
Alors j'ai essayé de comprendre la formule, même si mes connaissances sont limitées...
Je ne sais pas ce que veux dire INDIRECT(ADRESS(EQUIV...
CONCATENER c'est ok
"TOTAL SEMAINE ";TEXTE(NO.SEMAINE($L4;2);"00"));$F:$F;0);17) je ne comprend pas le 17 a la fin...
INDIRECT(ADRESSE(EQUIV(CONCATENER("TOTAL SEMAINE ";TEXTE(NO.SEMAINE($L4;2);"00"));$F:$F;0);17))Me renvoie le total de la semaine associé à la semaine de ta date de livraison. Ca ne fonctionne pas car si tu n'as pas changé tes dates de ton exemple les semaines renvoyées ne sont pas la semaine 01.
Si en L4 j'ai 01/01/2021 alors la formule ci-dessus doit me renvoyer 30 272.00.
EQUIV renvoie le numéro de ligne du concatener de la semaine de la date de livraison donc ici ligne 22 si en L4 j'ai 01/01/2021.
Associé à 17, adresse me renvoie Q22, Q étant la 17ème colonne de ta table.
A partir de là INDIRECT me renvoie la valeur de Q22, soit ton total pour la semaine dépendant de la date de livraison (tout se fait à partir de la colonne L).
Enooormeee !!! Ca marche !!!
Un très grand merci à toi Ergotamine !!!
Chapeau bas l'artiste
Tout est possible tu vois !
Ca fonctionne mais comme l'a dit eriiic (que je salue au passage) il vaut clairement mieux avoir une vraie BDD et je t'encourage à faire transiter ton entreprise vers cette solution. Comme tu peux le voir la formule est longue et complexe, et très peu robuste : un 0, une colonne insérer, ou une date qui ne colle pas et c'est la MEFC qui ne s'applique plus.
Bon samedi.
Juste une dernière question sans vouloir abuser de ton temps et de ta gentillesse
Concernant les noms de magasins prioritaire, Magasin 1 (ou 2 ou 3) ne représentait qu'un exemple. Mais dans mon tableau, il faut que je mette la ville du magasin (Magasin de Paris, ou Magasin Toulon...) Ma liste de magasin prioritaire ne comportait que les mots Magasin :) Comme un mot clé
Mais si je met dans mon tableau Magasin Paris, il ne le considère pas comme magasin prioritaire
Je pensais qu'en entourant la plage de ma liste de magasins prioritaire, avec des "* *" cela deviendrait : Si, sur une ligne il y est inscrit le mot clé Magasin, alors il est prioritaire...
Je ne sais pas si je formule bien ma question, ou mon souhait
Bon et puis zut, ce n'est pas non plus un secret... dans ma liste de magasin, il faut que je mette des mots clés comme Bricomarché, Bricorama, Jardiland, etc... Ce sont eux les magasins prioritaires...
Je retire mon dernier message...
C'est moi qui avait mal écrit le nom du magasin
Je vais aller me taper la tête contre les murs et je te remercie une nouvelle fois pour ton aide précieuse :)
Bon samedi
Bonjour,
Dans ce cas là essaies ça :
=ET(SOMMEPROD(ESTNUM(CHERCHE($B$87:$B$96;$E4))*1)=0;NB.SI($M4:$N4;"X")>0;INDIRECT(ADRESSE(EQUIV(CONCATENER("TOTAL SEMAINE ";TEXTE(NO.SEMAINE($L4;2);"00"));$F:$F;0);17))>RECHERCHEV(CONCATENER("SEMAINE ";TEXTE(NO.SEMAINE($L4;2);"00"));'CA + ALERTE'!$B$4:$E$55;4;FAUX))NB.SI() ne permet pas une recherche partielle de caractère contrairement à CHERCHE() qui me renvoie la position de la chaîne de caractère cherchée dans le texte, je teste alors si le résultat est numérique (dont si une position a été trouvée), qui me renvoie vrai, que je multiplie par 1.
La fonction SOMMEPROD() me permet de boucler la recherche des valeurs B87 à B96 et d’additionner les différents résultats en comparant E4 à B87, puis E4 à B88 etc ... Si le résultat de SOMMEPROD() est 0 c'est que B87 à B96 n'a jamais été trouvé en E4, donc que ce n'est pas un magasin prioritaire. Dans ce cas le résultat est VRAI et la MEFC s'applique.
Je te laisse tester et me faire un retour.
Bon samedi.
Tu es un génie !!! (excuse moi de te tutoyer ;) )
Merci 1000 fois !!!
Et niveau de technique avancé +++ (car jusqu'à présent on est bien avancé je pense :) )
Penses tu qu'il soit faisable de faire un échelon des commandes saisies... au fur et à mesure...
Par exemple, je ne sais pas si tu as pris une attention dans l'onglet CA + Alerte, j'avais projeté l'idée de mettre un % d'alerte (ou de surveillance) avant d'atteindre la limite... Comme pour me faire un signal que le CA du dépot 1 approche de la limite... C'est à dire pour la semaine 01, le 30% restant avant la limite, les clients apparaitraient en orange (exemple : pour la semaine 01 : sur une limite fixée à 10000 €, les clients non prioritaires apparaitraient en orange à partir de 7000 € de commande pour le Dépôt 1, jusqu'à 10000 €.
A partir de 10000 €, les commandes suivantes passeraient en rouge, et qui marche dejà grâce à toi
Du coup, j'aurais deux couleurs... Ceux qui ont validé leurs commandes et qui étaient encore en orange... et en +, les nouvelles commandes qui auraient été validés plus tard que les autres, passeraient en rouge (puisqu'au delà des 10000 €) Car je trouverais injuste d'appeler un client qui auraient réservé sa commande depuis 3 mois, pour lui dire qu'au final sa livraison sera décalée d'une semaine ou deux (parce que d'autres clients non prioritaires eux aussi, ont validés leurs commandes il y a une semaine à peine...
Penses tu que techniquement ou en formule ce "tri", cet affichage, soit possible ?
Faire la MEFC en orange oui.
Par contre pour la suite il faudrait classer les commande en fonction de leur rang et des montants des commandes précédentes, de la priorité des clients. Et là, ça dépasse mes compétences désolé, surtout sans réelle BDD (la formule que j'ai pondu révèle plus de la bidouille pour coller à ta structure de fichier).
En soit dès lors que la MEFC orange apparaît, tu sais dors et déjà que tu ne peux potentiellement plus accepter de commandes de clients non prioritaires car tu te rapproche de ton seuil limite. Ensuite il te suffirait de remonter tes commandes des clients non prioritaires (décocher M ou N) jusqu'à n'avoir plus de MEFC rouge malgré les commandes des clients prioritaires.
En tout cas de nombreuses choses sont possibles.
Bonne journée.
C'était un petit plus qui m'aurait aider davantage dans mon travail, mais ta solution pour la limite est déjà énorme !
Merci encore 1000 fois et excellent week end !
Bonjour Ergotamine :)
Après avoir supprimer toutes les commandes fictives pour la démo, pour mettre un tableau propre, et déplacer ma liste des magasins prioritaires dans une autre feuille, pour que ca fasse plus propre, j'ai voulu retester sur la première semaine du tableau pour voir si tout était toujours ok. Ce qui n'était pas le cas.
Je ne me suis pas inquiéter et j'ai recopier ta formule, en indiquant dans celle ci le nouvel emplacement de ma liste des magasins prios dans "CA + ALERTES"... J'ai bien redéfinis l'emplacement pour la MFC.. Mais le code ne fonctionne plus...
Un dernier help stp ?
Bonjour,
Un nouveau test avec une plage dynamique pour tes magasins prio : tu peux en rajouter autant que tu le souhaites ils sont automatiquement comptabilisés.
J'ai aussi changé la MEFC qui se fait par rapport à la colonne TOTAL de la colonne G et non plus Q de ta feuille COMMANDES2021.
De plus j'ai appliqué la MEFC sur toute ta plage de commandes, c'est plus lisible et on ne risque pas de louper une ligne.
Bonne journée