Ρύθμιση ενός τύπου μορφοποίησης υπό όρους που χρησιμοποιεί μεικτή αναφορά. Οι περισσότεροι τύποι μορφοποίησης υπό όρους απαιτούν μια απόλυτη αναφορά. Αλλά αυτό το υπολογιστικό φύλλο για την παρακολούθηση φορτηγών σε μια αυλή απαιτεί
Δες το βίντεο
- Ο Άντερσον αναζητά έναν τρόπο να μπορεί να αντιγράψει μπλοκ δεδομένων που περιέχουν μικτή μορφοποίηση υπό όρους
- Υπάρχει τρόπος να αφαιρέσετε τα σημάδια του δολαρίου μόλις ρυθμιστεί η μορφοποίηση υπό όρους;
- Όχι - όχι χωρίς την εισαγωγή δεκάδων νέων κανόνων
- Η λύση μου: βοηθητικά κελιά που χρησιμοποιούν σχετικές αναφορές για να αντικαταστήσουν τη μικτή αναφορά σε μορφοποίηση υπό όρους
- Άλλες τεχνικές σε αυτό το επεισόδιο:
- Εάν έχετε τέσσερις κανόνες μορφοποίησης υπό όρους, ρυθμίστε τους πρώτους 3 και, στη συνέχεια, κάντε τον τέταρτο κανόνα το προεπιλεγμένο χρώμα
- Εκτέλεση # 1: Πατήστε F2 για να σταματήσετε το Excel από την εισαγωγή αναφορών κυττάρων στο πλαίσιο διαλόγου μορφοποίησης υπό όρους
- Εκτέλεση # 2: ρύθμιση μορφοποίησης υπό όρους
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast Episode 2105: Αντιγραφή μορφής υπό όρους με μικτές αναφορές
Γεια σας, καλώς ήλθατε πίσω στο δίκτυο. Αυτό θα είναι περίπλοκο σήμερα. Έκανα ένα σεμινάριο χθες και ένας από τους ανθρώπους στο σεμινάριο, ο Άντερσον, είχε ένα ενδιαφέρον υπολογιστικό φύλλο με ένα πρόβλημα. Εντάξει, και ο Άντερσον καταφέρνει μια αυλή - τα ρυμουλκά φτάνουν και τα ρυμουλκούμενα πρέπει να εκφορτωθούν μέσα σε τρεις ημέρες. Εντάξει, έτσι είναι - ξεκινάει, ξέρετε, αυτή ήταν η μέρα, αυτά ήταν τα τρέιλερ που έφτασαν και στη συνέχεια έχει ρυθμίσει τη μορφοποίηση υπό όρους ότι μόλις ξεφορτωθεί το τρέιλερ, αλλάζει σε μπλε. Μόλις κάτι είναι μπλε, όλα είναι υπέροχα. Αλλά τότε, θέλει να χρωματίσει τα πράγματα. Εάν έφτασε κάτι σήμερα ή χθες, αποκτά χρώμα ως πράσινο. Οπότε σήμερα είναι 29 Ιουνίου 2017, οπότε αυτό έφτασε χθες και όλα όσα δεν έχουν εκφορτωθεί είναι πράσινο αλλά όταν είναι περισσότερο από μία μέρα,θέλουμε να επισημάνουμε τα πράγματα ως κίτρινα και όταν είναι περισσότερο από δύο ημέρες, αυτά είναι τα προβλήματα που θέλουμε να επισημάνουμε τα πράγματα ως κόκκινο. Και δεν είναι αυτό, ξέρετε, αυτό είναι ένα φύλλο εργασίας για τη διαχείριση ολόκληρης της αυλής, σωστά; Δεν είναι ότι υπάρχει ένα φύλλο για πράγματα που έφτασαν στις 26 και ένα άλλο για το 27ο και ένα άλλο για το 28ο. Και ξέρετε ότι η δυσκολία είναι καθώς έρχεται μια νέα μέρα, είτε αντιγράφουν την προηγούμενη μέρα προς εδώ είτε κάτω προς εδώ.είτε αντιγράφουν την προηγούμενη μέρα εδώ ή εδώ.είτε αντιγράφουν την προηγούμενη μέρα εδώ ή εδώ.
Εντάξει τώρα, το σημείο αυτού του βίντεο δεν αφορά τον τρόπο ρύθμισης αυτής της μορφοποίησης υπό όρους. Έτσι θα το επιταχύνω, αλλά αν σας ενδιαφέρει πώς να ρυθμίσετε αυτήν τη μορφοποίηση υπό όρους, θα βάλω την ανεπεξέργαστη έκδοση ως αποτέλεσμα στο τέλος του βίντεο.
Εντάξει, έτσι είμαστε. Επιταχύνετε, μπορείτε να παρακολουθήσετε στο τέλος για να δείτε πώς λειτουργεί. Απλά κάνοντας μια δοκιμή εδώ, CTRL. θα αλλάξει σε μπλε. Εάν αυτό επανέλθει στις 6/26, θα αλλάξει σε κόκκινο και αν είναι σήμερα, δεν λειτουργεί. Αυτό είναι σωστό γιατί εδώ θα κάνω, ο τέταρτος κανόνας μου έφτασε σήμερα ή χθες, απλώς πρόκειται να το χρησιμοποιήσω ως προεπιλογή. Εάν κανένας από αυτούς τους άλλους τρεις κανόνες δεν ισχύει, τότε θα είναι πράσινο που θα μου δώσει έναν λιγότερο κανόνα που πρέπει να ασχοληθώ εδώ, εντάξει;
Εντάξει, οπότε βρισκόμαστε τώρα στο σημείο που έχουμε ουσιαστικά το πρόβλημα του Άντερσον. Θα βάλω στις 25/6/2017, όλα αυτά θα γίνουν κόκκινα εκτός από αυτά που έχουν εκφορτωθεί. Και τώρα η ζωή συνεχίζεται, είναι η επόμενη μέρα. Έχουμε μερικά ρυμουλκούμενα στις 6/26 και έτσι ο Άντερσον αντιγράφει αυτά τα δεδομένα, επικολλήστε εδώ, μορφοποιήστε τη στήλη Αυτόματη προσαρμογή και αυτό θα είναι το τρέιλερ 15. Πηγαίνετε κλικ για να αντιγράψετε αυτό και αυξήστε, ξεφορτωθείτε αυτά που έφτασαν. Και έτσι έφτασε σήμερα, οπότε όλα αυτά θα γίνουν πράσινα αλλά δεν θα γίνουν πράσινα. Γιατί δεν γίνονται πράσινα; Δεν γίνονται πράσινοι επειδή αυτοί οι τύποι, αυτοί οι τύποι μορφοποίησης υπό όρους εδώ, θα τους εξετάσουμε. Είναι δύσκολο να χρησιμοποιήσουν $ 1 $. Ω, αυτό είναι πολύ κακό.
Εντάξει, οπότε ας προσπαθήσουμε να βελτιώσουμε τα πράγματα εδώ. Το πρώτο πράγμα που μπορώ να κάνω, θα ξεφορτωθώ όλα αυτά και θα επιστρέψω σε αυτό το αρχικό σύνολο δεδομένων και θα είμαι λίγο πιο έξυπνος στο δεύτερο πέρασμα και θα πω ότι δεν χρειάζεται πραγματικά να το κλειδώσουμε στη στήλη A. Θα ξεφορτωθώ αυτό το σύμβολο $. Με άλλα λόγια, θα είναι πάντα η στήλη στα αριστερά μας, οπότε πρόκειται να είναι μια μικτή αναφορά, αλλά πρέπει πάντα να επισημαίνουμε το $ 1. Θα επεξεργαστούμε αυτόν τον κανόνα, κάντε κλικ στο OK. Εντάξει τώρα, με αυτήν την αλλαγή όταν αντιγράψαμε προς τα δεξιά και τοποθετήσαμε νέα δεδομένα, όπως η σημερινή ημερομηνία, λειτουργεί. Εντάξει, οπότε αυτό είναι υπέροχο. Η ζωή θα είναι υπέροχη στις 6/26 και η ζωή θα είναι υπέροχη στις 6/27. Εντάξει, δουλεύοντας υπέροχα. Αλλά τώρα αντιμετωπίζουμε το πρόβλημα όπου εξαντλείται ο χώρος στη σελίδα και έτσι αυτό που έκανε ο Άντερσον είναι κάτω,ουσιαστικά ξεκινά μια νέα σειρά και πάστες και αυτό θα είναι 6/28 αλλά δεν γίνεται πράσινο.
Γιατί δεν γίνεται πράσινο; Δεν γίνεται πράσινο γιατί έπρεπε ακόμα να χρησιμοποιήσω το $ για να επιστρέψω στο 1. Εντάξει, και τώρα εδώ είναι το αίνιγμα, εδώ είναι το πρόβλημα. Τι κάνεις τώρα? Και είμαι σοβαρός, τι κάνεις τώρα; Θέλω να ακούσω στα σχόλια του YouTube τι θα κάνατε τώρα.
Ξέρετε, λοιπόν, κοίτα, υπάρχει ένα επιχείρημα ότι αυτό είναι καλό, θα μπορούσαμε να σταματήσουμε εδώ γιατί χρησιμοποιώντας το A $ 1, το κάναμε έτσι, η ζωή είναι εύκολη την Ημέρα 1, η αντιγραφή στην Ημέρα 2, η ζωή είναι υπέροχη . Η ζωή της 3ης ημέρας είναι υπέροχη. Μόνο κάθε 4η μέρα όταν αντιγράφουμε εδώ, ο Άντερσον θα έπρεπε να εισέλθει και να ρυθμίσει τη μορφοποίηση υπό όρους, να επεξεργαστεί αυτόν, να επεξεργαστεί τον κανόνα, να αλλάξει το 1 σε 18. Κάντε κλικ στο OK, επεξεργαστείτε αυτόν τον κανόνα και αλλάξτε το 1 σε 18. Κάντε κλικ στο OK, κάντε κλικ στο OK. Εντάξει, έτσι την Ημέρα 4, αυτό το μικρό αντίγραφο προσαρμογής για την Ημέρα 5, αντιγράψτε το για την Ημέρα 6 και μετά αντιγράψτε το για την Ημέρα 7. Κάντε αυτά τα βήματα ξανά Αλλά, ας το παραδεχτούμε. Αυτό το φύλλο εργασίας δημιουργήθηκε πριν από έξι μήνες με αυτούς τους κανόνες μορφοποίησης υπό όρους και απλώς πρέπει να δουλέψουν. Δεν χρειάζεται να μπαίνουμε και να κάνουμε μορφοποίηση υπό όρους ξανά και ξανά και ξανά.
Η πρώτη μου αντίδραση ήταν ότι θα προσποιηθώ ότι αυτό είναι ένα υπολογιστικό φύλλο όπου έχω κάποιους τύπους εδώ και αυτοί οι τύποι δημιουργήθηκαν με απόλυτες αναφορές, αλλά χρειάζομαι αυτούς τους τύπους για να μπορέσω να αντιγράψω πάνω ή κάτω και να είμαι σχετικός στο αντίγραφο - τόσο όταν αντιγράφω εδώ όσο και όταν αντιγράφω εδώ. Εντάξει, και για να το κάνω αυτό να λειτουργήσει, θα χρησιμοποιήσω απόλυτες αναφορές όταν ρυθμίζω τα πράγματα, αλλά μετά θα χρησιμοποιήσω το Find and Replace, Ctrl H. Και ας πούμε ας απαλλαγούμε από τις σχετικές αναφορές αλλάξτε κάθε $ A $ 1 σε A1, Αντικαταστήστε όλα, κάντε κλικ στο Κλείσιμο και τώρα σε αυτό το μπλοκ, όλοι αυτοί οι τύποι είναι διαφορετικοί, αντιγράψτε, επικολλήστε και επικολλήστε και θα λειτουργήσει. Θα είναι σχετικό. Έτσι είπα, εντάξει, λοιπόν αυτό πρέπει να κάνουμε. Πρέπει να βγάλουμε αυτά τα $ από τον τύπο.Και έτσι επρόκειτο να γράψω μια μακροεντολή που θα μου επέτρεπε να επεξεργαστώ καθεμία από αυτές τις προϋποθέσεις μορφοποίησης υπό όρους. Εντάξει, και προτού γράψω αυτήν τη μακροεντολή θα έγραφα τη μακροεντολή της αλλαγής ενός κανόνα μορφοποίησης υπό όρους, αλλά δεν είναι ότι υπάρχουν 14 κανόνες μορφοποίησης υπό όρους εδώ. Δεν ισχύει καν για τους κανόνες μορφοποίησης υπό όρους 14 * 3, 42 εδώ. Υπάρχουν μόνο 3 κανόνες μορφοποίησης υπό όρους εδώ και εφαρμόζουμε αυτούς τους 3 κανόνες μορφοποίησης υπό όρους σε μια σειρά κελιών.Υπάρχουν μόνο 3 κανόνες μορφοποίησης υπό όρους εδώ και εφαρμόζουμε αυτούς τους 3 κανόνες μορφοποίησης υπό όρους σε μια σειρά κελιών.Υπάρχουν μόνο 3 κανόνες μορφοποίησης υπό όρους εδώ και εφαρμόζουμε αυτούς τους 3 κανόνες μορφοποίησης υπό όρους σε μια σειρά κελιών.
Αν λοιπόν το αλλάξω αυτό, το πρώτο πράγμα που θα έπρεπε να κάνω είναι να λάβω αυτούς τους 3 κανόνες μορφοποίησης υπό όρους και να τους κάνω 42 κανόνες μορφοποίησης υπό όρους. Και τότε, αρχίζω να τσαλακώνω γιατί καθώς ο Άντερσον αντιγράφει από εδώ προς εδώ, θα εισαγάγει 42 νέους κανόνες και έπειτα 42 νέους κανόνες. Και κατά τη διάρκεια ενός φύλλου χαρτιού με πιθανώς 15 ημέρες, θα εισαγάγει πάνω από 600 κανόνες, 600 διαφορετικές μορφές και αυτό θα είναι απλώς φρικτό. Τελικά πρόκειται να χτυπήσετε πάρα πολλά πράγματα για τον κανόνα μορφοποίησης, για να μην αναφέρουμε ότι θα είναι δύσκολο να ρυθμιστεί ακόμα κι αν έχουμε μια μακροεντολή για να τη ρυθμίσουμε. Θα είναι δύσκολο να δημιουργηθεί.
Εντάξει, λοιπόν τι κάνουμε; Εδώ είναι αυτό που βρήκα και θέλω να ακούσω αν έχετε κάτι καλύτερο από αυτό. Είπα στον Άντερσον, είπα: «Ξέρεις, φαίνεται ότι είναι πολύ απλό. Όλα αυτά βλέπουν έναν υπολογισμό και αυτός ο υπολογισμός είναι = ΣΗΜΕΡΑ - η ημερομηνία που βρίσκεται στα αριστερά μου. " Και δεν θα ήταν δροσερό, αν μπορούσαμε να έχουμε αυτήν την απάντηση σε μια μικρή στήλη βοηθού εδώ προς τα δεξιά. Και στην πραγματικότητα, δεν χρειάζεται να χρησιμοποιήσουμε καθόλου $, θα βάλουμε όλα αυτά τα κελιά σε όλη τη διαδρομή με αυτήν την απλή μικρή φόρμουλα.
I can see the look at Anderson’s face, he doesn't want that extra stuff out there erased but that's okay. We can hide, hide that later so we come back into these cells and go into our conditional formatting. That whole TODAY-A1 is simply going to be pointing to C3 and that's going to be a relative reference. So in other words, whatever cell we're in we're always going to look in the cell to the right, click OK, write at this one, click OK. We want to hide this data over here so I'll go in and CTRL 1. I'm going to use the three semicolons - ;;;,click OK. I’m going to do the exact same thing there. I’ll press F4, repeat the last 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.
Εντάξει, έτσι είμαστε. Επιταχύνετε, μπορείτε να παρακολουθήσετε στο τέλος για να δείτε πώς λειτουργεί. Απλά κάνω μια δοκιμή εδώ. CTRL; θα αλλάξει σε μπλε. Εάν αυτό επανέλθει στις 6/26, θα αλλάξει σε κόκκινο. Και αν είναι σήμερα, δεν λειτουργεί. Αυτό είναι σωστό γιατί εδώ θα κάνω. Ο τέταρτος κανόνας μου, το πράσινο έφτασε σήμερα ή χθες, θα το χρησιμοποιήσω ως προεπιλογή. Εάν κανένας από αυτούς τους άλλους τρεις κανόνες δεν ισχύει, τότε θα είναι πράσινο που θα μου δώσει έναν λιγότερο κανόνα που πρέπει να ασχοληθώ εδώ. Καλώς.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2105.xlsx
