Configuration d'une formule de mise en forme conditionnelle qui utilise une référence mixte. La plupart des formules de mise en forme conditionnelle nécessitent une référence absolue. Mais cette feuille de calcul pour suivre les camions dans un chantier nécessite
Regarder la vidéo
- Anderson cherche un moyen de pouvoir copier des blocs de données contenant une mise en forme conditionnelle mixte
- Existe-t-il un moyen de supprimer les signes dollar une fois le formatage conditionnel configuré?
- Non - non sans introduire des dizaines de nouvelles règles
- Ma solution: des cellules d'assistance qui utilisent des références relatives pour remplacer la référence mixte dans la mise en forme conditionnelle
- Autres techniques dans cet épisode:
- Si vous avez quatre règles de mise en forme conditionnelle, configurez les 3 premières, puis faites de la quatrième règle la couleur par défaut
- Prise n ° 1: appuyez sur F2 pour empêcher Excel d'insérer des références de cellule dans la boîte de dialogue de mise en forme conditionnelle
- Outtake # 2: configuration du formatage conditionnel
Transcription vidéo
Apprendre Excel à partir de l'épisode 2105 du podcast: Copie d'un format conditionnel avec des références mixtes
Salut, bon retour sur le netcast. Cela va être compliqué aujourd'hui. Je faisais un séminaire hier et l'une des personnes présentes au séminaire, Anderson, avait une feuille de calcul intéressante avec un problème. D'accord, et Anderson gère un chantier - les remorques arrivent et les remorques doivent être déchargées dans les trois jours. D'accord, alors c'est - il commence, vous savez, c'était le jour, c'étaient les remorques qui sont arrivées et ensuite il a mis en place un formatage conditionnel pour qu'une fois la remorque déchargée, elle vire au bleu. Une fois que quelque chose est bleu, tout va bien. Mais ensuite, il veut coder les choses par couleur. Si quelque chose est arrivé aujourd'hui ou hier, il est codé en vert. Donc, aujourd'hui, c'est le 29 juin 2017, c'est arrivé hier et tout ce qui n'est pas déchargé est vert mais quand il a plus d'un jour,nous voulons mettre en évidence les choses en jaune et quand elles ont plus de deux jours, ce sont les problèmes que nous voulons mettre en évidence les choses en rouge. Et ce n'est pas ça, vous savez, c'est une feuille de travail pour gérer tout le chantier, non? Ce n'est pas qu'il y ait une feuille pour les choses qui sont arrivées le 26 et une autre pour le 27 et une autre pour le 28. Et vous savez que la difficulté vient du fait qu'un nouveau jour arrive, soit ils copient la veille ici, soit ici.ils copient la veille ici ou ici.ils copient la veille ici ou ici.
Très bien maintenant, le but de cette vidéo n'est pas de savoir comment configurer cette mise en forme conditionnelle. Je vais donc accélérer le processus, mais si vous souhaitez savoir comment configurer ce formatage conditionnel, je mettrai la version non accélérée en tant que sortie à la fin de la vidéo.
D'accord, nous y voilà. Accélérez cela, vous pouvez regarder à la fin pour voir comment cela fonctionne. Je fais juste un test ici, CTRL; deviendra bleu. Si cela remonte au 26/06, il passera au rouge et si c'est aujourd'hui, cela ne fonctionne pas. C'est vrai parce que voici ce que je vais faire, ma quatrième règle verte est arrivée aujourd'hui ou hier, je vais juste l'utiliser par défaut. Si aucune de ces trois autres règles n'est vraie, alors ce sera vert que cela me donnera une règle de moins que je dois traiter ici, d'accord?
D'accord, nous en sommes donc maintenant au point où nous avons essentiellement le problème d'Anderson. Je vais mettre le 25/06/2017, ceux-ci deviendront tous rouges à l'exception de ceux qui ont été déchargés. Et maintenant la vie avance, c'est le lendemain. Nous avons reçu des bandes-annonces le 26 juin et Anderson copie donc ces données, collez-les ici, formatez l'ajustement automatique des colonnes, et ce sera la bande-annonce 15. Cliquez pour copier cela vers le bas et incrémentez, débarrassez-vous de celles qui sont arrivées. Et donc celui-ci est arrivé aujourd'hui, donc ils devraient tous devenir verts mais ils ne deviennent pas verts. Pourquoi ne deviennent-ils pas verts? Ils ne deviennent pas verts parce que ces formules, ces formules de mise en forme conditionnelle ici, nous allons les examiner. Ils sont codés en dur pour utiliser $ A $ 1. Oh, c'est vraiment mauvais.
D'accord, essayons donc d'améliorer les choses ici. La première chose que je peux faire, je vais me débarrasser de tout cela et revenir à cet ensemble de données d'origine et être un peu plus intelligent lors du deuxième passage et dire que nous n'avons pas vraiment besoin de le verrouiller dans la colonne A. Je vais me débarrasser de ce signe $. En d'autres termes, ce sera toujours la colonne à gauche de nous, donc ce sera une référence mixte, mais nous devons toujours pointer vers le $ 1. Nous modifierons cette règle, cliquez sur OK. Très bien maintenant, avec ce changement lorsque nous avons copié vers la droite et mis une nouvelle donnée, comme la date d'aujourd'hui, cela fonctionne. D'accord, c'est génial. La vie va être belle le 26/06 et la vie va être belle le 27/06. Très bien, fonctionne très bien. Mais maintenant, nous nous heurtons au problème où nous manquons d'espace sur la page et donc ce qu'Anderson a fait est en panne,commence essentiellement une nouvelle ligne et colle et ce serait 6/28 mais il ne devient pas vert.
Pourquoi ne devient-il pas vert? Il ne devient pas vert parce que je devais encore utiliser le $ pour revenir au 1. D'accord, et maintenant voici l'énigme, voici le problème. Que faites-vous maintenant? Et je suis sérieux, que fais-tu maintenant? Je veux entendre dans les commentaires YouTube ce que vous feriez maintenant.
Vous savez, alors regardez, il y a un argument qui dit que c'est bien, nous pourrions nous arrêter ici parce qu'en utilisant le A $ 1, nous l'avons fait de cette façon, la vie est facile le jour 1, copiez vers le jour 2, la vie est belle . Jour 3, la vie est géniale. Ce n'est que tous les 4 jours que nous copions ici qu'Anderson devrait entrer et configurer la mise en forme conditionnelle, éditer celle-ci, éditer la règle, changer ce 1 en 18. Cliquez sur OK, éditez cette règle et changez ce 1 en 18. Cliquez sur OK, cliquez sur OK. Très bien, donc le jour 4, cette petite copie d'ajustement pour le jour 5, copiez pour le jour 6, puis copiez pour le jour 7. Répétez ces étapes. Mais bon, avouons-le. Cette feuille de calcul a été créée il y a six mois avec ces règles de mise en forme conditionnelle et elles ont juste besoin de fonctionner. Nous n'avons pas besoin d'entrer et de faire du formatage conditionnel encore et encore et encore.
Ma première réaction a été que je vais faire comme si c'était une feuille de calcul où j'ai quelques formules ici et ces formules ont été construites avec des références absolues, mais j'ai besoin de ces formules pour pouvoir être copiées ou réduites, et être relatives dans la copie - à la fois quand je copie ici et quand je copie ici. D'accord, et pour que cela fonctionne, je vais utiliser des références absolues lorsque je configure les choses, mais ensuite je vais utiliser Rechercher et remplacer, Ctrl H.Et disons débarrassons-nous de ces références relatives, changez chaque $ A $ 1 en A1, Remplacez tout, cliquez sur Fermer et maintenant ce bloc, toutes ces formules sont différentes tout en bas, copiez, collez et collez et cela fonctionnera. Ce sera relatif. Alors j'ai dit, d'accord, c'est bien ce que nous devons faire. Nous devons retirer ces $ de la formule.Et donc j'allais écrire une macro qui me permettrait d'éditer chacune de ces règles de mise en forme conditionnelle. D'accord, et avant d'écrire cette macro, j'allais enregistrer la macro de modification d'une règle de mise en forme conditionnelle, mais ce n'est pas qu'il y ait 14 règles de mise en forme conditionnelle ici. Ce n'est même pas les règles de mise en forme conditionnelle 14 * 3, 42 ici. Il n'y a que 3 règles de mise en forme conditionnelle ici et nous appliquons ces 3 règles de mise en forme conditionnelle à une plage de cellules.Il n'y a que 3 règles de mise en forme conditionnelle ici et nous appliquons ces 3 règles de mise en forme conditionnelle à une plage de cellules.Il n'y a que 3 règles de mise en forme conditionnelle ici et nous appliquons ces 3 règles de mise en forme conditionnelle à une plage de cellules.
Donc, si je pouvais changer cela, la première chose que je devrais faire est de prendre ces 3 règles de formatage conditionnel et d'en faire 42 règles de formatage conditionnel. Et puis, je commence à grincer des dents parce qu'Anderson copie d'ici à ici, il va introduire 42 nouvelles règles, puis 42 nouvelles règles. Et sur une feuille de papier avec probablement 15 jours, il va introduire plus de 600 règles, 600 formats différents et ça va être horrible. Vous allez finalement toucher le problème des règles de mise en forme trop nombreuses, sans compter que cela sera difficile à configurer même si nous avons une macro pour le configurer. Cela va être difficile à mettre en place.
Très bien, alors on fait quoi? Voici ce que j'ai proposé et je veux savoir si vous avez quelque chose de mieux que ça. J'ai dit à Anderson, j'ai dit: «Vous savez, regardez c'est assez simple. Tous ces éléments examinent un calcul et ce calcul est = AUJOURD'HUI - la date qui se trouve à ma gauche. " Et ne serait-ce pas cool, si nous pouvions avoir cette réponse dans une petite colonne d'aide ici à droite. Et en fait, nous n'avons pas du tout à utiliser de $, nous allons simplement mettre toutes ces cellules tout en bas avec cette simple petite formule.
Je peux voir le visage d'Anderson, il ne veut pas que ce truc supplémentaire soit effacé, mais ce n'est pas grave. Nous pouvons cacher, cacher cela plus tard afin de revenir dans ces cellules et d'entrer dans notre mise en forme conditionnelle. Tout ce TODAY-A1 va simplement pointer vers C3 et ce sera une référence relative. Donc, en d'autres termes, quelle que soit la cellule dans laquelle nous nous trouvons, nous allons toujours regarder dans la cellule de droite, cliquez sur OK, écrivez sur celle-ci, cliquez sur OK. Nous voulons cacher ces données ici donc je vais entrer et CTRL 1. Je vais utiliser les trois points-virgules - ;;;, cliquez sur OK. Je vais faire exactement la même chose là-bas. Je vais appuyer sur F4, répéter la dernière action.
Now, the weird thing here is I have to convince myself that this little part, this blank column is part of the whole thing. So I want to just add a light grey there to remind myself that when I copy and paste, I'm going to have to include the grey. Alright, so here's our test now. I will choose this CTRL C and then I'm actually going to paste there and paste there and paste here. Alright, big tests go to 6/26, go to 6/27, change this to yellow, come to 6/28, it should change to green. Beautiful!
Alright, so now it's working, we have essentially replaced that conditional formatting mixed reference with a relative reference and we should be relatively, relatively good to go.
Alright, topics in this episode. We're looking for a way to be able to copy blocks of data containing conditional formatting that essentially is a mixed reference. So, is there some way to remove the $ once the conditional formatting is set up? Well maybe with a macro but you'd be introducing dozens of new rules instead of just one formula applying to a whole block. So my solution was to use helper cells that use relative references and then just use regular references in the conditional formatting.
Other topics in this episode, if you have 4 conditional formatting rules just set the first three and make the fourth rule be the default color. The outtake coming up next is press F2 to stop Excel from inserting cell references in the conditional formatting dialogue and then setting up the conditional formatting dialogue.
Well, I want to thank Anderson for being in my seminar and hopefully, you know, this helps him. I want to thank you for stopping by. We'll see you next time for another netcast from.
I'll take number 1. When you're dealing with conditional formatting in that stupid dialogue box and you need to edit something that's already in there, you better be darn good at clicking in the right spot. Like if I wanted to change that 1 to be 18, and I clicked right there and then press the Right Arrow key then I have to swear because they're inserting cell references instead. Alright, and so many times when I was recording this episode, I clicked in the wrong spot and hit the Right Arrow key or the Left Arrow key or Shift Arrow key and how to back out of it.
Alright, here is the key if this has been driving you crazy for years. Well key number 1, just be perfect about where you click. Right then you don't have any problem at all, life is great but that's not realistic. Here's the whole trick. When you are in this dialogue box, down here in the lower left-hand corner it says that we are in Enter mode and when you're in Enter mode using Left or Right Arrow keys is going to insert cell references for you like that, right? Really, really annoying. But what you want to do is you want to press the F2 key and that changes us from Enter mode to Edit mode. Bingo! Now we can do whatever we want. We can use the Left Arrow key or the Right Arrow key and we're not inserting cells like that.
Alright, next up is I'll take number 2 where I built this original conditional formatting. I showed that in fast motion before here. Just in case you're interested is the slow motion.
So we're going to have a date here. I'm going to put in yesterday's date just or two days ago date so we have something - this is going to work. Alright, and we're going to assume that there's going to be some number of space for trailers to come in. In this case I'll go down to Trailer 14 and then here, we're going to build conditional format. And there are four rules that we want to do. And the first one, the easy one is if there's a date here then we’re going to turn this thing blue, so alt="" O D to get into conditional formatting. I'm going to create a new rule and that new rule is going to be the easy one format, only cells that contain a value that is greater than 0. Then we're going to format this using a blue color like that, click OK, click OK. Alright, first rule done.
Second rule is the thing- the date up in A1, more than one days old. This one is going to be the tricky one and this is where we have to look at a specific cell. So I’m going to have to use a formula and we'll say =TODAY, today will be today’s date minus that date up in A1. If that is>2,>1 then we're going to format it in yellow.
Alright, and I don't know if I need parentheses here, I'm going to just be safe and put the ( ) in and copy that whole thing so I can create the red color. So copy and then we'll add a new rule and rule is going to be if it’s> or =2, we’ll format as red.
Alright, now we have to be careful here. The first thing I want to do is I want to check to see if the thing is filled in. If the thing is filled in, we get the blue, we stop if true. Then the next thing to do, we have to check for the red before we check for the yellow because this formula for yellow is also going to be true on the days when it should be red.
D'accord, nous y voilà. Accélérez cela, vous pouvez regarder à la fin pour voir comment cela fonctionne. Je fais juste un test ici. CTRL; deviendra bleu. Si cela revient au 26/06, il passera au rouge. Et si c'est aujourd'hui, ça ne marche pas. C'est vrai parce que voici ce que je vais faire. Ma quatrième règle, verte est arrivée aujourd'hui ou hier, je vais simplement l'utiliser par défaut. Si aucune de ces trois autres règles n'est vraie, alors ce sera vert que cela me donnera une règle de moins que je dois traiter ici. Bien.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2105.xlsx
