Avis aux Pro de l'Excel

24
23 septembre 2020
Bonjour à tous,

je sais qu'il y a des connaisseurs en Excel ici donc j'en profite pour poser une question :

J'ai un tableau avec plusieurs lignes et colonnes :

1968721.jpg

J'aimerai qu'Excel me mette en surligné rouge les valeurs de la 3ème ligne (quantité x prix) de chaque poste si la valeur est < à 30% de la moyenne des valeurs des entreprises.

J'ai réussi en ajoutant une colonne sur le coté et faire une mise en forme conditionnelle, mais je dois répéter l'opération à chaque ligne (j'ai beaucoup beaucoup de lignes) et plusieurs fichiers....

Merci d'avance pour votre aide
Informations supplémentaires
Blabla
24 commentaires
Le faire sur une ligne c'est simple, mais si tu veux l'appliquer après sur plein de lignes qui sont éclatées dans ton fichier alors ça risque d'être galère et je ne vois pas comment gérer ça rapidement.
Édité par "Pastek" 23 septembre
Pastek23/09/2020 09:16

Le faire sur une ligne c'est simple, mais si tu veux l'appliquer après sur …Le faire sur une ligne c'est simple, mais si tu veux l'appliquer après sur plein de lignes qui sont éclatées dans ton fichier alors ça risque d'être galère et je ne vois pas comment gérer ça rapidement.



Oui j'aimerai le faire sur plusieurs lignes mais à chaque fois c'est 4 lignes en dessous de la précédente (est-ce qu'on peut automatiser cela?)....
sylv1du4223/09/2020 09:18

Oui j'aimerai le faire sur plusieurs lignes mais à chaque fois c'est 4 …Oui j'aimerai le faire sur plusieurs lignes mais à chaque fois c'est 4 lignes en dessous de la précédente (est-ce qu'on peut automatiser cela?)....



Si tu peux appliquer la même règle conditionnelle sur l'ensemble de ces lignes alors il suffirait de générer le texte des plages de cellules concernées (exemple : "=$D$8:$G$8;$D$10:$G$10").
Je ne suis pas certain de bien comprendre le problème.
Qu'est-ce qui vous empêche de faire un "copier" de votre 4 lignes et un "coller" du seul formatage sur des dizaines de milliers de lignes ? Ou faire une macro ? Ou identifier les 4èmes lignes d'une façon spécifique quelconque et mettre cet identifiant dans la contrainte de votre formatage conditionnel ?

Sans la feuille excel complète et un exemple visuel de ce que vous voulez faire c'est difficile (pour moi) d'être spécifique.
Voici un exemple du fichier avec la mise en page que je souhaiterai

Lien du téléchargement
we.tl/t-S…hQJ

Je suis obligé d'ajouter la colonne moyenne et moyenne -30% manuellement sur chaque fichier et faire la mise en forme conditionnelle sur chaque ligne manuellement ...
Édité par "sylv1du42" 23 septembre
Mince je ne suis pas un pro Excel mais seulement expérimenté.

Je suis aussi preneur pour avoir le fichier en entier.
Bon, clairement il faut que vous mettiez un identifiant sur toutes les "3èmes lignes" sous la colonne "Unité" (disons, "xxx" pour l'exemple)
Après vous pourrez faire votre formatage conditionnel en rajoutant une condition "SI"
Le plus simple serait probablement de donner un nom à la colonne "Unité"

Idem pour votre ligne 22, vous pourrez faire un "SOMME.SI" qui n'additionnera que les lignes avec "xxx" dans la colonne "Unité"

A partir de là, votre feuille sera "automatisée" (gros guillemets )
Je la joue un peu sadique : je ne vous donne pas la solution clé en main pour vous donner l'occasion d'en apprendre un peu plus sur les fonctions incluses dans excel (ou open office, je n'utilise pas excel).
Édité par "PaoloC" 27 septembre
PaoloC23/09/2020 10:36

Bon, clairement il faut que vous mettiez un identifiant sur toutes les …Bon, clairement il faut que vous mettiez un identifiant sur toutes les "3èmes lignes" sous la colonne "Unité" (disons, "xxx" pour l'exemple)Après vous pourrez faire votre formatage conditionnel en rajoutant une condition "SI"Le plus simple serait probablement de donner un nom à la colonne "Unité"Idem pour votre ligne 22, vous pourrez faire un "SOMME.SI" qui n'additionnera que les lignes avec "xxx" dans la colonne "Unité"A partir de là, votre feuille sera "automatisée" (gros guillemets )Je la joue un peu sadique : je ne vous donne pas la solution clé en main pour vous donner l'occasion d'en apprendre un peu plus sur les fonctions incluses dans execl (ou open office, je n'utilise pas excel).



D'accord je vais regarder mais ça fait pas mal de manipulation à faire, mon problème c'est surtout la mise en forme conditionnelle que je n'arrive pas à répéter automatiquement toutes les 4 lignes ...
Édité par "sylv1du42" 23 septembre
Vous ne la répétez pas toutes les 4 lignes, vous la conditionnez par l'existence de la valeur "xxx" dans la colonne "Unité".
Ensuite vous copier la mise en forme conditionnelle sur TOUTES les cellules (ou sur toute la feuille).

A votre place (mais, je n'y suis pas !), je mettrais la synthèse (lignes 22 à 24) en tête de la feuille (ou carrément sur une feuille "Synthèse") parce que dans le cas actuel si vous rajoutez des lignes vous êtes obligé de "jongler" pour les placer correctement entre la dernière ligne du tableau et le bloc "synthèse".
Je vous donne une piste pour votre formatage conditionnel :
Si la cellule de la colonne B est égale à "xxx" et que la valeur de la cellule actuelle est inférieure à la valeur de la colonne M alors j'applique le formatage.
Édité par "PaoloC" 23 septembre
PaoloC23/09/2020 11:17

Je vous donne une piste pour votre formatage conditionnel :Si la cellule …Je vous donne une piste pour votre formatage conditionnel :Si la cellule de la colonne B est égale à "xxx" et que la valeur de la cellule actuelle est inférieure à la valeur de la colonne M alors j'applique le formatage.



Merci pour votre aide je vais essayer
sylv1du4223/09/2020 11:20

Merci pour votre aide je vais essayer


Non, vous allez réussir !
Pistes pour la synthèse (exemple pour C22):
- Si le tableau B2 à J99999 contient "xxx" additionner C2 à C21 (si la synthèse était en tête de feuille, ce serait encore plus facile quand vous rajoutez des lignes...)
- il faut jouer avec les cellules relatives et fixes
- Fonction "somme conditionnelle"
Je viens de tomber sur la discussion.
Avez-vous trouvé ? Si non je peux aussi regarder, ça m'éclate bien Excel 😁
nico5069024/09/2020 20:01

Je viens de tomber sur la discussion.Avez-vous trouvé ? Si non je peux …Je viens de tomber sur la discussion.Avez-vous trouvé ? Si non je peux aussi regarder, ça m'éclate bien Excel 😁


Je n'ai pas eu le temps de m'y remettre dessus mais si tu as une solution plus rapide que de mettre des xxx sur chaque ligne comme conseillé précédemment, je suis à l'écoute 😁
sylv1du4224/09/2020 20:34

une solution plus rapide que de mettre des xxx sur chaque ligne


Si c'est votre seule contrainte, il y a une façon simple de le faire qui vous prendra 3 minutes, même si votre feuille fait une dizaine de milliers de lignes. Et je ne suis pas un cador des tableurs.
Comme je suis particulièrement sympa ce soir, je vous la livre (sur la base de votre exemple)
Mettre les formules suivantes dans les cellules A2, A3 et A4 (ne rien mettre dans A5)
=B2
=SI(B2<>"";"";"xxx")
=SI(B3<>"";"";"xxx")
Sélectionnez les cellules A2 à A5
Faire un « Copier »
Sélectionnez les cellules A6 jusqu'à la fin de votre colonne A (ici A21)
Faites un « Coller »
Sélectionnez les cellules A2 jusqu'à la fin de votre colonne A (ici A21)
Faites un « Copier »
Sélectionnez la cellule B2
Faites un « Copier Spécial » et choisissez l'option « Texte »
C'est fait ! Vous pouvez effacer le contenu de la colonne A
Édité par "PaoloC" 24 septembre
PaoloC24/09/2020 21:18

Si c'est votre seule contrainte, il y a une façon simple de le faire qui …Si c'est votre seule contrainte, il y a une façon simple de le faire qui vous prendra 3 minutes, même si votre feuille fait une dizaine de milliers de lignes. Et je ne suis pas un cador des tableurs.Comme je suis particulièrement sympa ce soir, je vous la livre (sur la base de votre exemple) Mettre les formules suivantes dans les cellules A2, A3 et A4 (ne rien mettre dans A5)=B2=SI(B2<>"";"";"xxx")=SI(B3<>"";"";"xxx") Sélectionnez les cellules A2 à A5 Faire un « Copier » Sélectionnez les cellules A6 jusqu'à la fin de votre colonne A (ici A21) Faites un « Coller » Sélectionnez les cellules A2 jusqu'à la fin de votre colonne A (ici A21) Faites un « Copier » Sélectionnez la cellule B2 Faites un « Copier Spécial » et choisissez l'option « Texte » C'est fait ! Vous pouvez effacer le contenu de la colonne A


Wahou top !
Merci beaucoup, j'essaye de regarder demain !
Je n'aurais pas trouvé cela tout seul ^^, ça veux dire quoi les ""?
sylv1du4224/09/2020 21:30

Wahou top !Merci beaucoup, j'essaye de regarder demain !Je n'aurais pas …Wahou top !Merci beaucoup, j'essaye de regarder demain !Je n'aurais pas trouvé cela tout seul ^^, ça veux dire quoi les ""?


Le guillemet " c'est le délimiteur de texte. Dans le cas qui nous occupe, "xxx" ça veut dire qu'on met xxx dans la cellule et "" ça veut dire qu'on n'y met rien (du vide). Evidemment, vous êtes supposé mettre un libellé plus intelligent que "xxx"...
sylv1du4223/09/2020 10:11

Je suis obligé d'ajouter la colonne moyenne et moyenne -30% manuellement …Je suis obligé d'ajouter la colonne moyenne et moyenne -30% manuellement sur chaque fichier


En fait, non, il n'y a aucune obligation d'ajouter ces colonnes.
Si vous voulez absolument faire ces calculs de cette façon, une unique colonne suffirait mais vous pouvez vous passer complètement de toute colonne, ces calculs peuvent tout aussi bien faire partie intégrante de la mise en forme conditionnelle.

Un autre problème que j'entrevois c'est que si (ou quand) vous voulez changer la valeur du pivot, vous devez changer tous vos calculs et formules. Il serait donc souhaitable de mettre votre valeur de "pivot" (30% dans votre exemple) et d'utiliser cette cellule dans vos calculs et formules de sorte que la feuille se recalcule automatiquement en ne changeant que la valeur du "pivot".
Édité par "PaoloC" 25 septembre
Exemple d'ajout d'une valeur "pivot" utilisée dans les calculs pour recalculer automatique
30499344-1cUrG.jpg
Personnellement (et donc très subjectif), je préférerais un tableau comme le suivant qui me permettrait de rajouter un nombre quasi infini de lignes sans jamais devoir retoucher aux calculs, formules et formatage de ma feuille de calcul. Bon, je rajouterais aussi des libellés sur toutes les lignes parce que, me connaissant, au bout de 10 jours j'aurais oublié à quoi se rapportent tous ces chiffres...

30499344-j9FCR.jpg
Après, je ne sais pas à quoi vous sert cette feuille de calcul.
Si c'est pour le boulot et qu'elle doit être utilisée dans la durée, ça vaut le coup de se casser un peu la tête (calculs, présentation) pour être hyper tranquille par la suite. J'ai toujours eu un peu tendance à automatiser et simplifier les trucs qui me retombaient régulièrement dessus au taff...
Si c'est perso, faites au plus simple.
PaoloC25/09/2020 17:44

Exemple d'ajout d'une valeur "pivot" utilisée dans les calculs pour …Exemple d'ajout d'une valeur "pivot" utilisée dans les calculs pour recalculer automatique[Image] Personnellement (et donc très subjectif), je préférerais un tableau comme le suivant qui me permettrait de rajouter un nombre quasi infini de lignes sans jamais devoir retoucher aux calculs, formules et formatage de ma feuille de calcul. Bon, je rajouterais aussi des libellés sur toutes les lignes parce que, me connaissant, au bout de 10 jours j'aurais oublié à quoi se rapportent tous ces chiffres... [Image] Après, je ne sais pas à quoi vous sert cette feuille de calcul. Si c'est pour le boulot et qu'elle doit être utilisée dans la durée, ça vaut le coup de se casser un peu la tête (calculs, présentation) pour être hyper tranquille par la suite. J'ai toujours eu un peu tendance à automatiser et simplifier les trucs qui me retombaient régulièrement dessus au taff... Si c'est perso, faites au plus simple.


Wahou c'est top tout ça, je regarde ce week-end quand j'ai 5 min car au taf en ce moment je n'ai pas le temps...
Oui c'est un travail professionnel, que je fait régulièrement manuellement donc je souhaitais l'automatiser afin de gagner du temps. J'aimerai toucher le moins possible au tableau que génère mon logiciel, est-il obligatoire d'ajouter le terme "tot" pour faire le travail ?
sylv1du4225/09/2020 19:34

est-il obligatoire d'ajouter le terme "tot" pour faire le travail ?


Il n'y a jamais rien d'obligatoire mais déjà à la base (et à mon sens, donc subjectif) un tableau sans libellés attachés aux lignes, ça pue un peu beaucoup...
Peut-être que c'est indispensable à la CIA/DGSI/etc. de ne pas mettre de descriptions sur les lignes de chiffres des feuilles Excel mais comme je n'ai jamais été agent secret j'y ai toujours préféré des descriptions.

Il est possible de conditionner le formatage conditionnel sur le fait que la ligne 1 de la colonne "Unité" de chaque bloc de 4 lignes est renseigné d'une valeur quelconque et de n'appliquer le formatage que sur les nombres situés que 2 lignes plus bas.
Tout est possible.

En revanche, pour calculer la synthèse, il n'y aura rien de simple...

sylv1du4225/09/2020 19:34

J'aimerai toucher le moins possible au tableau que génère mon logiciel


Je doute que ce soit ce logiciel qui génère la ligne 3 avec des formules de calcul Excel (multiplication des 2 cellules au dessus) ni qui ajoute le tableau de synthèse à la fin du tableau et qui rajoute le formatage conditionnel. Donc vous devez bien déjà toucher beaucoup à ce tableau, non ?
Bon, c'est difficile (et vain) de spéculer sur ce qui doit être fait pour automatiser le bestiau sans connaître le déroulement du processus (d'où viennent les données brutes, sous quelle forme, comment sont-elles importées dans Excel, etc).

Bon courage.
Édité par "PaoloC" 25 septembre
PaoloC25/09/2020 20:45

Il n'y a jamais rien d'obligatoire mais déjà à la base (et à mon sens, don …Il n'y a jamais rien d'obligatoire mais déjà à la base (et à mon sens, donc subjectif) un tableau sans libellés attachés aux lignes, ça pue un peu beaucoup... Peut-être que c'est indispensable à la CIA/DGSI/etc. de ne pas mettre de descriptions sur les lignes de chiffres des feuilles Excel mais comme je n'ai jamais été agent secret j'y ai toujours préféré des descriptions.Il est possible de conditionner le formatage conditionnel sur le fait que la ligne 1 de la colonne "Unité" de chaque bloc de 4 lignes est renseigné d'une valeur quelconque et de n'appliquer le formatage que sur les nombres situés que 2 lignes plus bas.Tout est possible.En revanche, pour calculer la synthèse, il n'y aura rien de simple...Je doute que ce soit ce logiciel qui génère la ligne 3 avec des formules de calcul Excel (multiplication des 2 cellules au dessus) ni qui ajoute le tableau de synthèse à la fin du tableau et qui rajoute le formatage conditionnel. Donc vous devez bien déjà toucher beaucoup à ce tableau, non ?Bon, c'est difficile (et vain) de spéculer sur ce qui doit être fait pour automatiser le bestiau sans connaître le déroulement du processus (d'où viennent les données brutes, sous quelle forme, comment sont-elles importées dans Excel, etc).Bon courage.


Le tableau généré par mon logiciel est bien tel quelle, j'ai seulement ajouté les colonnes moyenne et moyenne-30%. Je génère une dizaine de tableau du genre par mois, je dois faire apparaître les valeurs inférieures à 30% de la moyenne des autres valeurs (hors estimation). Donc j'ai créé le formatage conditionnel mais je dois le répéter à chaque ligne... Donc je perds plus de temps que de le faire manuellement.
J'aimerai qu'une fois le tableau généré, que je colle une mise en forme ou quelque chose du genre me permettant de mettre en valeur les valeurs désirées.
Le nombre de ligne peut être d'une 20aine à 200 voir plus...
Je connais seulement les formules de base d'excel donc j'arrive à faire ce que je veux mais je suis sûr qu'il y a moyen d'automatiser tout ça... Je vais essayer demain de regarder toutes les info que tu m'a donné, afin de les comprendre et de faire la technique qui m'arrangerait le plus.
Merci encore pour ton aide et le temps passé à me renseigner
Édité par "sylv1du42" 25 septembre
Je vais être un peu indiscret mais ce "logiciel", qui à la main dessus ?
Ce sont des données qui vous sont envoyées par quelqu'un d'autre ou générées par vous même ?

Au lieu de vous envoyer ça :
30502943-KhkbC.jpg
Est-ce qu'il peut vous envoyer uniquement ce qui est utile ? A savoir sans la synthèse en bas de tableau : elle va vous pourrir la vie ! D'autant que si le nombre de lignes du tableau est vraiment grand, le calcul de la synthèse risque d'être faux en faisant une addition de cellules individuelles (limite du nombre d'arguments de la fonction SOMME). Pas besoin de reprendre, dans votre feuille Excel, les éventuelles erreurs faites en amont !
La seule question sur la synthèse c'est : que représente le taux de 20% ? La TVA ? Un indicateur variable qui vous est connu ?
Les calculs de la 3ème ligne de chaque bloc de 4 lignes sont, eux aussi, inutiles. Une ligne vide fera aussi bien l'affaire.

Si vous receviez qqchose comme ça, ce serait plus que suffisant et facile à automatiser :
30502943-YuqEG.jpg
Admettons que vous ayez ce tableau "Excel de données" (ci-dessus) dans la "feuille 1" de votre classeur (commence en B2 ici).
Sur la "Feuille 2" vous vous créez un "squelette" de ce genre avec autant de "blocs" de 4 lignes que vous pensez nécessaire (j'ai fait un squelette de 374 blocs mais vous pouvez en caser 250000 dans Excel), les formats conditionnels qui vont bien et "en selle, Marcel !" :
30502943-rGwkA.jpgnote : évidemment, vous n'avez que la "synthèse" et le premier bloc de 4 lignes à créer, après c'est juste une question de "Copier/Coller" ce bloc en autant de blocs que vous voulez !

Chaque fois que vous mettrez un tableau "Excel de données" dans la "feuille 1", la "Feuille 2" se mettra à jour automatiquement

En ayant mis des nombres aléatoires dans "Feuille 1" (là où les nombres variaient dans votre exemple) et si je ne me suis pas planté dans mon "squelette", ça donne ça dans "Feuille 2" de façon totalement automatique :
30502943-MyJ77.jpg
Édité par "PaoloC" 25 septembre
Laisser un commentaire
Avatar
@
    Texte