Einrichten einer bedingten Formatierungsformel, die eine gemischte Referenz verwendet. Die meisten bedingten Formatierungsformeln erfordern eine absolute Referenz. Diese Tabelle zur Verfolgung von Lastwagen in einem Hof erfordert jedoch
Schau Video
- Anderson sucht nach einer Möglichkeit, Datenblöcke mit gemischten bedingten Formatierungen zu kopieren
- Gibt es eine Möglichkeit, die Dollarzeichen zu entfernen, sobald die bedingte Formatierung eingerichtet ist?
- Nein - nicht ohne Dutzende neuer Regeln einzuführen
- Meine Lösung: Hilfszellen, die relative Referenzen verwenden, um die gemischte Referenz in der bedingten Formatierung zu ersetzen
- Andere Techniken in dieser Episode:
- Wenn Sie vier bedingte Formatierungsregeln haben, richten Sie die ersten drei ein und legen Sie dann die vierte Regel als Standardfarbe fest
- Outtake # 1: Drücken Sie F2, um zu verhindern, dass Excel Zellreferenzen in den Dialog zur bedingten Formatierung einfügt
- Outtake # 2: Einrichten der bedingten Formatierung
Video-Transkript
Lernen Sie Excel aus Podcast Episode 2105: Kopieren des bedingten Formats mit gemischten Referenzen
Hey, willkommen zurück im Netcast. Dies wird heute kompliziert sein. Ich habe gestern ein Seminar gemacht und einer der Teilnehmer des Seminars, Anderson, hatte eine interessante Tabelle mit einem Problem. In Ordnung, und Anderson verwaltet einen Hof - Anhänger kommen an und Anhänger müssen innerhalb von drei Tagen entladen werden. Also gut, das ist - er fängt an, weißt du, das war der Tag, das waren die Trailer, die angekommen sind, und dann hat er eine bedingte Formatierung eingerichtet, die sich nach dem Entladen des Trailers in blau ändert. Sobald etwas blau ist, ist alles großartig. Aber dann möchte er die Dinge farblich kennzeichnen. Wenn etwas heute oder gestern angekommen ist, wird es als grün farbcodiert. Heute ist also der 29. Juni 2017, also ist dies gestern angekommen und alles, was nicht entladen wird, ist grün, aber wenn es mehr als einen Tag alt ist,Wir möchten Dinge als gelb hervorheben, und wenn sie älter als zwei Tage sind, sind dies die Probleme, die wir als rot hervorheben möchten. Und es ist nicht so, dass dies ein Arbeitsblatt ist, um den gesamten Hof zu verwalten, oder? Es ist nicht so, dass es ein Blatt für Dinge gibt, die am 26. und ein anderes für das 27. und ein anderes für das 28. angekommen sind. Und Sie wissen, dass die Schwierigkeit darin besteht, dass ein neuer Tag kommt. Entweder kopieren sie den vorherigen Tag hierher oder hierher.Sie kopieren entweder den Vortag hierher oder hierher.Sie kopieren entweder den Vortag hierher oder hierher.
In diesem Video geht es nicht darum, wie diese bedingte Formatierung eingerichtet wird. Ich werde das also beschleunigen, aber wenn Sie daran interessiert sind, wie diese bedingte Formatierung eingerichtet wird, werde ich die nicht beschleunigte Version am Ende des Videos als Outtake einfügen.
Okay, da sind wir also. Beschleunigen Sie das, Sie können am Ende sehen, wie das funktioniert. Ich mache hier nur einen Test, STRG; wird zu blau wechseln. Wenn dies auf den 26.06. Zurückgeht, wird es rot und wenn es heute ist, funktioniert es nicht. Das ist richtig, denn hier ist, was ich tun werde: Meine vierte Regel Grün ist heute oder gestern angekommen. Ich werde das nur als Standard verwenden. Wenn keine dieser anderen drei Regeln wahr ist, wird es grün sein, dass es mir eine Regel weniger gibt, mit der ich mich hier befassen muss, okay?
Okay, wir sind jetzt an dem Punkt angelangt, an dem wir im Wesentlichen Andersons Problem haben. Ich werde in 25.06.2017 setzen, diese werden alle rot bis auf diejenigen, die entladen wurden. Und jetzt geht das Leben weiter, es ist der nächste Tag. Wir haben einige Trailer am 26.06. Eingeladen, und so kopiert Anderson diese Daten, fügt sie hier ein, formatiert Column AutoFit und dies wird Trailer 15. Klicken Sie auf, um diese zu kopieren und zu erhöhen, und entfernen Sie die angekommenen. Und so ist dieser heute angekommen, also sollten diese alle grün werden, aber sie werden nicht grün. Warum werden sie nicht grün? Sie werden nicht grün, weil diese Formeln, diese bedingten Formatierungsformeln genau hier, wir werden uns diese ansehen. Sie sind fest codiert, um $ A $ 1 zu verwenden. Oh, das ist wirklich schlimm.
Okay, also lasst uns versuchen, die Dinge hier zu verbessern. Das erste, was ich tun kann, ist, all diese Dinge loszuwerden und auf diesen ursprünglichen Datensatz zurückzukommen und beim zweiten Durchgang ein bisschen schlauer zu sein und zu sagen, dass wir ihn nicht wirklich auf Spalte A beschränken müssen. Ich werde dieses $ -Zeichen loswerden. Mit anderen Worten, es wird immer die Spalte links von uns sein, also wird das eine gemischte Referenz sein, aber wir müssen immer auf den $ 1 zeigen. Wir werden diese Regel bearbeiten, klicken Sie auf OK. Okay, jetzt, mit dieser einen Änderung, als wir nach rechts kopierten und neue Daten einfügten, wie das heutige Datum, funktioniert es. Okay, das ist großartig. Das Leben wird am 26. Juni großartig sein und das Leben wird am 27. Juni großartig sein. Okay, es funktioniert großartig. Aber jetzt stoßen wir auf das Problem, dass uns der Platz auf der Seite ausgeht und Anderson untergeht.Im Wesentlichen beginnt eine neue Reihe und Pasten und dies wäre 6/28, aber es wird nicht grün.
Warum wird es nicht grün? Es wird nicht grün, weil ich immer noch den $ verwenden musste, um wieder auf den 1. zu kommen. Okay, und jetzt ist hier das Rätsel, hier ist das Problem. Was tust du jetzt? Und ich meine es ernst, was machst du jetzt? Ich möchte in den YouTube-Kommentaren hören, was du jetzt tun würdest.
Weißt du, also schau mal, es gibt ein Argument dafür, dass dies gut ist. Wir könnten hier aufhören, denn mit der Verwendung von A $ 1 haben wir es so gemacht, das Leben ist an Tag 1 einfach, kopiere auf Tag 2, das Leben ist großartig . Tag 3 Leben ist großartig. Erst jeden vierten Tag, an dem wir hier unten kopieren, muss Anderson die bedingte Formatierung einrichten, diese bearbeiten, die Regel bearbeiten, diese 1 in 18 ändern. Klicken Sie auf OK, bearbeiten Sie diese Regel und ändern Sie diese 1 in 18. Klicken Sie auf OK und dann auf OK. Okay, also Tag 4, diese kleine Korrekturkopie für Tag 5, Kopie für Tag 6 und dann Kopie für Tag 7. Führen Sie diese Schritte erneut aus. Aber hey, seien wir ehrlich. Dieses Arbeitsblatt wurde vor sechs Monaten mit diesen bedingten Formatierungsregeln erstellt und muss nur funktionieren. Wir müssen nicht immer wieder bedingte Formatierungen vornehmen.
Meine erste Reaktion war, dass ich so tun werde, als wäre dies eine Tabelle, in der ich einige Formeln habe und diese Formeln mit absoluten Referenzen erstellt wurden, aber ich brauche diese Formeln, um über oder nach unten kopiert werden zu können und innerhalb der Kopie relativ zu sein - sowohl wenn ich hierher kopiere als auch wenn ich hierher kopiere. Okay, und um das zum Laufen zu bringen, werde ich beim Einrichten absolute Referenzen verwenden, aber dann werde ich Suchen und Ersetzen verwenden, Strg H. Und sagen wir, lassen Sie uns diese relativen Referenzen loswerden. Ändern Sie alle $ A $ 1 in A1, ersetzen Sie alle, klicken Sie auf Schließen, und jetzt, in diesem Block, unterscheiden sich alle diese Formeln bis zum Ende, kopieren, einfügen und einfügen und es wird funktionieren. Es wird relativ sein. Also sagte ich, okay, genau das müssen wir tun. Wir müssen diese $ aus der Formel herausnehmen.Und so wollte ich ein Makro schreiben, mit dem ich jede dieser bedingten Formatierungsregeln bearbeiten konnte. Okay, und bevor ich dieses Makro schrieb, wollte ich das Makro zum Ändern einer bedingten Formatierungsregel aufzeichnen, aber es ist nicht so, dass es hier 14 bedingte Formatierungsregeln gibt. Es geht hier nicht einmal um die 14 * 3, 42 bedingten Formatierungsregeln. Hier gibt es nur drei Regeln für die bedingte Formatierung, und wir wenden diese drei Regeln für die bedingte Formatierung auf einen Bereich von Zellen an.Es gibt hier nur 3 Regeln für die bedingte Formatierung, und wir wenden diese 3 Regeln für die bedingte Formatierung auf einen Bereich von Zellen an.Es gibt hier nur 3 Regeln für die bedingte Formatierung, und wir wenden diese 3 Regeln für die bedingte Formatierung auf einen Bereich von Zellen an.
Wenn ich dies ändern würde, müsste ich zuerst diese 3 Regeln für die bedingte Formatierung verwenden und sie zu 42 Regeln für die bedingte Formatierung machen. Und dann fange ich an zu erschrecken, denn wenn Anderson von hier nach hier kopiert, wird er 42 neue Regeln und dann 42 neue Regeln einführen. Und im Laufe eines Blattes Papier mit wahrscheinlich 15 Tagen wird er über 600 Regeln und 600 verschiedene Formate einführen, und das wird einfach schrecklich. Sie werden irgendwann auf die zu viele Formatierungsregeln stoßen, ganz zu schweigen davon, dass das Einrichten schwierig sein wird, selbst wenn wir ein Makro zum Einrichten haben. Es wird schwierig sein, sich einzurichten.
Also gut, was machen wir? Folgendes habe ich mir ausgedacht und ich möchte hören, ob Sie etwas Besseres haben. Ich sagte zu Anderson: „Weißt du, es ist ziemlich einfach. Alle diese betrachten eine Berechnung und diese Berechnung ist = HEUTE - das Datum, das links von mir liegt. “ Und wäre es nicht cool, wenn wir diese Antwort in einer kleinen Hilfsspalte hier rechts haben könnten? Und in der Tat müssen wir überhaupt kein $ verwenden, wir werden einfach alle diese Zellen mit dieser einfachen kleinen Formel ganz ablegen.
Ich kann den Ausdruck auf Andersons Gesicht sehen, er möchte nicht, dass das zusätzliche Zeug da draußen gelöscht wird, aber das ist okay. Wir können das später verbergen, also kehren wir in diese Zellen zurück und gehen in unsere bedingte Formatierung. Das ganze HEUTE-A1 wird einfach auf C3 zeigen und das wird eine relative Referenz sein. Mit anderen Worten, egal in welcher Zelle wir uns befinden, wir werden immer in die Zelle rechts schauen, auf OK klicken, auf diese schreiben, auf OK klicken. Wir möchten diese Daten hier verstecken, also gehe ich hinein und drücke STRG 1. Ich verwende die drei Semikolons - ;;;, klicke auf OK. Ich werde dort genau das Gleiche tun. Ich drücke F4 und wiederhole die letzte Aktion.
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.
Okay, da sind wir also. Beschleunigen Sie das, Sie können am Ende sehen, wie das funktioniert. Ich mache hier nur einen Test. STRG; wird zu blau wechseln. Wenn dies auf 26/26 zurückgeht, wird es rot. Und wenn es heute ist, funktioniert es nicht. Das ist richtig, denn hier ist was ich tun werde. Meine vierte Regel, Grün, ist heute oder gestern angekommen. Ich werde das nur als Standard verwenden. Wenn keine dieser anderen drei Regeln wahr ist, wird es grün sein, dass es mir eine Regel weniger gibt, mit der ich mich hier befassen muss. In Ordung.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2105.xlsx
