Impostazione di una formula di formattazione condizionale che utilizza un riferimento misto. La maggior parte delle formule di formattazione condizionale richiedono un riferimento assoluto. Ma questo foglio di calcolo per monitorare i camion in un cortile richiede
Guarda un video
- Anderson sta cercando un modo per poter copiare blocchi di dati contenenti formattazione condizionale mista
- C'è un modo per rimuovere i segni del dollaro una volta impostata la formattazione condizionale?
- No, non senza introdurre dozzine di nuove regole
- La mia soluzione: celle helper che utilizzano riferimenti relativi per sostituire il riferimento misto nella formattazione condizionale
- Altre tecniche in questo episodio:
- Se hai quattro regole di formattazione condizionale, imposta le prime 3 e poi imposta la quarta regola come colore predefinito
- Risultato n. 1: premere F2 per impedire a Excel di inserire riferimenti di cella nella finestra di dialogo di formattazione condizionale
- Outtake # 2: impostazione della formattazione condizionale
Trascrizione del video
Impara Excel da Podcast Episode 2105: Copia del formato condizionale con riferimenti misti
Ehi, bentornato al netcast. Oggi sarà complicato. Ieri stavo facendo un seminario e una delle persone al seminario, Anderson, aveva un foglio di calcolo interessante con un problema. Va bene, e Anderson gestisce un cantiere: i rimorchi arrivano e i rimorchi devono essere scaricati entro tre giorni. Va bene, quindi questo è - inizia, sai, questo era il giorno, questi erano i trailer che sono arrivati e poi ha impostato la formattazione condizionale che una volta che il trailer è stato scaricato, diventa blu. Una volta che qualcosa è blu, tutto è fantastico. Ma poi, vuole colorare le cose. Se qualcosa è arrivato oggi o ieri, viene codificato con il colore verde. Quindi oggi è il 29 giugno 2017, quindi questo è arrivato ieri e tutto ciò che non è stato scaricato è verde ma quando ha più di un giorno,vogliamo evidenziare le cose come il giallo e quando hanno più di due giorni, questi sono i problemi che vogliamo evidenziare come il rosso. E non è quello, sai, questo è un foglio di lavoro per gestire l'intero cortile, giusto? Non è che ci sia un foglio per le cose arrivate il 26, un altro per il 27 e un altro per il 28. E sai che la difficoltà è quando arriva un nuovo giorno, o copiano il giorno precedente qui o giù fino a qui.o copiano il giorno precedente fin qui o fin qui.o copiano il giorno precedente fin qui o fin qui.
Va bene ora, il punto di questo video non riguarda come impostare questa formattazione condizionale. Quindi accelererò tutto questo, ma se sei interessato a come impostare questa formattazione condizionale, inserirò la versione non accelerata come outtake alla fine del video.
Ok, quindi eccoci qui. Spediscilo, puoi guardare alla fine per vedere come funziona. Sto solo facendo un test qui, CTRL; cambierà in blu. Se risale al 26/6, diventerà rosso e se è oggi, non funziona. Esatto perché ecco cosa farò, la mia quarta regola verde è arrivata oggi o ieri, la userò come impostazione predefinita. Se nessuna di queste altre tre regole è vera, allora sarà verde che mi darà una regola in meno che devo affrontare qui, va bene?
Ok, quindi ora siamo al punto in cui essenzialmente abbiamo il problema di Anderson. Li metto il 25/6/2017, questi diventeranno tutti rossi tranne quelli che sono stati scaricati. E ora la vita sta andando avanti, è il giorno successivo. Abbiamo ricevuto alcuni trailer il 26/6 e quindi Anderson copia questi dati, incollali qui, formatta Column AutoFit, e questo sarà Trailer 15. Fai clic per copiarlo e incrementarlo, sbarazzati di quelli che sono arrivati. E così questo è arrivato oggi, quindi dovrebbero diventare tutti verdi ma non diventeranno verdi. Perché non stanno diventando verdi? Non stanno diventando verdi perché queste formule, queste formule di formattazione condizionale proprio qui, le esamineremo. Sono hardcoded per utilizzare $ A $ 1. Oh, è davvero brutto.
Va bene, quindi proviamo a migliorare le cose qui. La prima cosa che posso fare, mi sbarazzerò di tutti quelli e tornerò a questo set di dati originale e sarò un po 'più intelligente al secondo passaggio e dirò che non abbiamo davvero bisogno di bloccarlo nella colonna A. Mi sbarazzerò di quel segno $. In altre parole, sarà sempre la colonna a sinistra di noi, quindi sarà un riferimento misto, ma dobbiamo sempre puntare a $ 1. Modificheremo questa regola, fare clic su OK. Va bene ora, con quella modifica quando abbiamo copiato a destra e inserito nuovi dati, come la data odierna, funziona. Ok, quindi è fantastico. La vita sarà fantastica il 26/6 e la vita sarà fantastica il 27/6. Va bene, funziona alla grande. Ma ora ci imbattiamo nel problema in cui esauriamo lo spazio sulla pagina e quindi quello che ha fatto Anderson è andato giù,essenzialmente inizia una nuova riga e incolla e questo sarebbe 6/28 ma non sta diventando verde.
Perché non sta diventando verde? Non sta diventando verde perché dovevo ancora usare $ per tornare all'1. Va bene, quindi ora ecco l'enigma, ecco il problema. Cosa fai adesso? E dico sul serio, cosa fai adesso? Voglio sentire nei commenti di YouTube cosa faresti ora.
Sai, quindi ehi guarda, c'è una discussione che è buono, potremmo fermarci qui perché usando l'A $ 1, abbiamo fatto in questo modo, la vita è facile il giorno 1, copia fino al giorno 2, la vita è fantastica . La vita del terzo giorno è fantastica. È solo ogni 4 giorni quando copiamo qui che Anderson dovrebbe entrare e impostare la formattazione condizionale, modificare questa, modificare la regola, cambiare quella 1 in 18. Fare clic su OK, modificare questa regola e cambiare quella 1 in 18. Fare clic su OK, quindi su OK. Va bene, quindi il giorno 4, quella piccola modifica copia per il giorno 5, copia per il giorno 6 e poi copia per il giorno 7. Ripeti questi passaggi. Ma ehi, ammettiamolo. Questo foglio di lavoro è stato impostato sei mesi fa con queste regole di formattazione condizionale e devono solo funzionare. Non abbiamo bisogno di entrare e fare la formattazione condizionale ancora e ancora e ancora.
La mia prima reazione è stata che fingo che questo sia un foglio di calcolo in cui ho alcune formule qui e quelle formule sono state costruite con riferimenti assoluti, ma ho bisogno di quelle formule per poter essere copiate sopra o sotto ed essere relative all'interno della copia - sia quando copio qui che quando copio qui. Va bene, e per farlo funzionare, userò riferimenti assoluti quando imposterò le cose, ma poi userò Trova e sostituisci, Ctrl H. E diciamo di sbarazzarci di quei riferimenti relativi, cambia ogni $ A $ 1 in A1, Sostituisci tutto, fai clic su Chiudi e ora questo blocco, tutte queste formule sono completamente diverse, copia, incolla e incolla e funzionerà. Sarà relativo. Quindi ho detto, va bene, beh questo è quello che dobbiamo fare. Dobbiamo togliere quei $ dalla formula.E così stavo per scrivere una macro che mi permettesse di modificare ognuna di queste regole di formattazione condizionale. Va bene, e prima di scrivere quella macro stavo per registrare la macro per modificare una regola di formattazione condizionale, ma non è che qui ci siano 14 regole di formattazione condizionale. Non è nemmeno per le 14 * 3, 42 regole di formattazione condizionale qui. Ci sono solo 3 regole di formattazione condizionale qui e stiamo applicando quelle 3 regole di formattazione condizionale a un intervallo di celle.s solo 3 regole di formattazione condizionale qui e stiamo applicando queste 3 regole di formattazione condizionale a un intervallo di celle.s solo 3 regole di formattazione condizionale qui e stiamo applicando queste 3 regole di formattazione condizionale a un intervallo di celle.
Quindi, se cambiassi questo, la prima cosa che dovrei fare è prendere queste 3 regole di formattazione condizionale e renderle 42 regole di formattazione condizionale. E poi, sto iniziando a rabbrividire perché mentre Anderson copia da qui a qui, introdurrà 42 nuove regole e poi 42 nuove regole. E nel corso di un foglio di carta con probabilmente 15 giorni, introdurrà oltre 600 regole, 600 formati diversi e sarà orribile. Alla fine raggiungerai la questione delle troppe regole di formattazione, per non parlare del fatto che sarà difficile da configurare anche se abbiamo una macro per configurarla. Sarà difficile da configurare.
Va bene, quindi cosa facciamo? Ecco cosa mi è venuto in mente e voglio sapere se hai qualcosa di meglio. Ho detto ad Anderson, ho detto: “Sai, guarda è piuttosto semplice. Tutti questi stanno guardando un calcolo e quel calcolo è = OGGI, la data che è alla mia sinistra. " E non sarebbe bello se potessimo avere quella risposta in una piccola colonna di aiuto qui a destra. E in effetti, non dobbiamo usare affatto $, inseriremo tutte quelle celle fino in fondo con quella semplice piccola formula.
Riesco a vedere lo sguardo sul viso di Anderson, non vuole che quella roba in più venga cancellata ma va bene. Possiamo nasconderlo, nasconderlo in seguito in modo da tornare in queste celle e passare alla nostra formattazione condizionale. L'intero TODAY-A1 punterà semplicemente a C3 e questo sarà un riferimento relativo. Quindi, in altre parole, qualunque sia la cella in cui ci troviamo, guarderemo sempre nella cella a destra, fare clic su OK, scrivere in questa, fare clic su OK. Vogliamo nascondere questi dati qui, quindi entrerò e CTRL 1. Userò i tre punti e virgola - ;;;, fare clic su OK. Farò esattamente la stessa cosa lì. Premerò F4, ripeterò l'ultima azione.
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.
Ok, quindi eccoci qui. Spediscilo, puoi guardare alla fine per vedere come funziona. Sto solo facendo un test qui. CTRL; cambierà in blu. Se torna a 6/26, diventerà rosso. E se è oggi, non funziona. Esatto perché ecco cosa farò. La mia quarta regola, il verde è arrivato oggi o ieri, la userò come impostazione predefinita. Se nessuna di queste altre tre regole è vera, allora sarà verde che mi darà una regola in meno che devo affrontare qui. Tutto a posto.
Download file
Scarica il file di esempio qui: Podcast2105.xlsx
