Configurar una fórmula de formato condicional que usa una referencia mixta. La mayoría de las fórmulas de formato condicional requieren una referencia absoluta. Pero esta hoja de cálculo para rastrear camiones en un patio requiere
Ver video
- Anderson está buscando una forma de poder copiar bloques de datos que contienen formato condicional mixto
- ¿Hay alguna forma de eliminar los signos de dólar una vez que se configura el formato condicional?
- No, no sin introducir docenas de nuevas reglas.
- Mi solución: celdas auxiliares que usan referencias relativas para reemplazar la referencia mixta en formato condicional
- Otras técnicas en este episodio:
- Si tiene cuatro reglas de formato condicional, configure las primeras 3 y luego haga que la cuarta regla sea el color predeterminado
- Resultado n. ° 1: presione F2 para evitar que Excel inserte referencias de celda en el cuadro de diálogo de formato condicional
- Outtake # 2: configurar el formato condicional
Transcripción del video
Aprenda Excel de Podcast Episodio 2105: Copiar formato condicional con referencias mixtas
Oye, bienvenido de nuevo al netcast. Hoy va a ser complicado. Ayer estaba haciendo un seminario y una de las personas en el seminario, Anderson, tenía una hoja de cálculo interesante con un problema. Muy bien, y Anderson administra un patio: llegan los remolques y los remolques deben descargarse en tres días. Muy bien, esto es: comienza, ya sabes, este fue el día, estos fueron los avances que llegaron y luego tiene configurado el formato condicional que una vez que se descarga el avance, cambia a azul. Una vez que algo es azul, todo es genial. Pero luego, quiere codificar con colores las cosas. Si algo llegó hoy o ayer, se codifica en color como verde. Así que hoy es 29 de junio de 2017, así que llegó ayer y todo lo que no se descargó es verde, pero cuando tiene más de un día,queremos resaltar las cosas en amarillo y cuando tienen más de dos días, esos son los problemas que queremos resaltar en rojo. Y no es eso, ya sabes, esta es una hoja de trabajo para administrar todo el patio, ¿verdad? No es que haya una hoja para las cosas que llegaron el día 26 y otra para el 27 y otra para el 28. Y sabes que la dificultad es que a medida que llega un nuevo día, copian el día anterior para aquí o para aquí.copian el día anterior aquí o aquí abajo.copian el día anterior aquí o aquí abajo.
Muy bien, el objetivo de este video no es cómo configurar este formato condicional. Así que voy a acelerar esto, pero si está interesado en cómo configurar este formato condicional, pondré la versión sin velocidad como una toma descartada al final del video.
Bien, ahí estamos. Acelerado, puedes mirar al final para ver cómo funciona. Solo estoy haciendo una prueba aquí, CTRL; cambiará a azul. Si vuelve a 6/26, cambiará a rojo y si es hoy, no funciona. Eso es correcto porque esto es lo que voy a hacer, mi cuarta regla verde llegó hoy o ayer, solo voy a usar eso como predeterminado. Si ninguna de estas otras tres reglas es cierta, entonces va a ser verde que me dará una regla menos con la que tengo que lidiar aquí, ¿de acuerdo?
Bien, ahora estamos en el punto en el que esencialmente tenemos el problema de Anderson. Voy a poner el 25/6/2017, todos se volverán rojos excepto los que se han descargado. Y ahora la vida sigue adelante, es el día siguiente. Recibimos algunos trailers el 26/6 y Anderson copia estos datos, los pega aquí, formatea Column AutoFit, y este será el Trailer 15. Vaya a hacer clic para copiarlo e incrementarlo, deshacerse de los que llegaron. Y este llegó hoy, por lo que todos deberían ponerse verdes, pero no se están volviendo verdes. ¿Por qué no se vuelven verdes? No se vuelven verdes porque estas fórmulas, estas fórmulas de formato condicional aquí mismo, las veremos. Están codificados para usar $ A $ 1. Oh, eso es realmente malo.
Muy bien, intentemos mejorar las cosas aquí. Lo primero que puedo hacer es deshacerme de todos esos y volver a este conjunto de datos original y ser un poco más inteligente en la segunda pasada y decir que realmente no necesitamos bloquearlo en la Columna A. Me deshaceré de ese signo $. En otras palabras, siempre será la columna a la izquierda de nosotros, por lo que será una referencia mixta, pero siempre tenemos que apuntar al $ 1. Editaremos esta regla, haga clic en Aceptar. Muy bien ahora, con ese cambio cuando copiamos a la derecha y pusimos nuevos datos, como la fecha de hoy, funciona. Bien, entonces esto es genial. La vida será genial el 26/6 y la vida será genial el 27/6. Muy bien, funcionando muy bien. Pero ahora nos encontramos con el problema de que nos quedamos sin espacio en la página y, por lo tanto, lo que Anderson ha estado haciendo es bajar,esencialmente comienza una nueva fila y pega y esto sería 6/28 pero no se vuelve verde.
¿Por qué no se pone verde? No se pone verde porque todavía tuve que usar el $ para volver al 1. Muy bien, y ahora aquí está el acertijo, aquí está el problema. ¿Qué haces ahora? Y hablo en serio, ¿qué haces ahora? Quiero escuchar en los comentarios de YouTube lo que harías ahora.
Sabes, mira, hay un argumento de que esto es bueno, podríamos detenernos aquí mismo porque al usar A $ 1, lo hicimos de esa manera, la vida es fácil el día 1, copia al día 2, la vida es genial . El día 3 la vida es genial. Solo cada cuarto día, cuando copiamos aquí abajo, Anderson tendría que entrar y configurar el formato condicional, editar este, editar la regla, cambiar ese 1 a 18. Haga clic en Aceptar, editar esta regla y cambiar ese 1 para que sea 18. Haga clic en Aceptar, haga clic en Aceptar. Muy bien, entonces el día 4, ese pequeño ajuste se copia para el día 5, copia para el día 6 y luego copia para el día 7. Repite esos pasos. Pero bueno, seamos sinceros. Esta hoja de trabajo se configuró hace seis meses con estas reglas de formato condicional y solo necesitan funcionar. No necesitamos entrar y aplicar formato condicional una y otra vez.
Mi primera reacción fue que voy a fingir que esta es una hoja de cálculo donde tengo algunas fórmulas aquí y esas fórmulas se crearon con referencias absolutas, pero necesito que esas fórmulas se puedan copiar sobre o hacia abajo, y ser relativo dentro de la copia. - tanto cuando copio aquí como cuando copio aquí. Muy bien, y para que eso funcione, usaré referencias absolutas cuando configure las cosas, pero luego usaré Buscar y reemplazar, Ctrl H. Y digamos que eliminemos esas referencias relativas, cambie cada $ A $ 1 a A1, Reemplace todo, haga clic en Cerrar y ahora este bloque, todas estas fórmulas son diferentes en todos los sentidos, copie, pegue y pegue y funcionará. Será relativo. Entonces dije, está bien, bueno, eso es lo que tenemos que hacer. Necesitamos sacar esos dólares de la fórmula.Y entonces iba a escribir una macro que me permitiera editar cada una de estas reglas de formato condicional. Muy bien, y antes de escribir esa macro, iba a grabar la macro de cambiar una regla de formato condicional, pero no es que haya 14 reglas de formato condicional aquí. Ni siquiera se trata de las 14 * 3, 42 reglas de formato condicional aquí. Aquí solo hay 3 reglas de formato condicional y estamos aplicando esas 3 reglas de formato condicional a un rango de celdas.Aquí solo tenemos 3 reglas de formato condicional y estamos aplicando esas 3 reglas de formato condicional a un rango de celdas.Aquí solo tenemos 3 reglas de formato condicional y estamos aplicando esas 3 reglas de formato condicional a un rango de celdas.
Entonces, si cambiara esto, lo primero que tendría que hacer es tomar estas 3 reglas de formato condicional y convertirlas en 42 reglas de formato condicional. Y luego, estoy empezando a sentir vergüenza porque a medida que Anderson copia de aquí para aquí, introducirá 42 reglas nuevas y luego 42 reglas nuevas. Y en el transcurso de una hoja de papel con probablemente 15 días, va a introducir más de 600 reglas, 600 formatos diferentes y eso será horrible. Eventualmente llegará a la regla de demasiadas reglas de formato, sin mencionar que será difícil de configurar incluso si tenemos una macro para configurarlo. Va a ser difícil de configurar.
Muy bien, entonces, ¿qué hacemos? Esto es lo que se me ocurrió y quiero saber si tienes algo mejor que eso. Le dije a Anderson, le dije: “Mira, es bastante simple. Todos ellos miran un cálculo y ese cálculo es = HOY, la fecha que está a mi izquierda ". Y no sería genial, si pudiéramos tener esa respuesta en una pequeña columna de ayuda aquí a la derecha. Y de hecho, no tenemos que usar ningún $ en absoluto, simplemente colocaremos todas esas celdas hasta el final con esa pequeña fórmula simple.
Puedo ver la expresión del rostro de Anderson, no quiere que se borren esas cosas extra, pero está bien. Podemos ocultarlo, ocultarlo más tarde para volver a estas celdas y entrar en nuestro formato condicional. Todo ese TODAY-A1 simplemente apuntará a C3 y esa será una referencia relativa. Entonces, en otras palabras, sea cual sea la celda en la que estemos, siempre miraremos en la celda de la derecha, haga clic en Aceptar, escriba en esta, haga clic en Aceptar. Queremos ocultar estos datos aquí, así que entraré y presionaré CTRL 1. Voy a usar los tres puntos y comas - ;;;, haga clic en Aceptar. Voy a hacer exactamente lo mismo allí. Presionaré F4, repetiré la última acción.
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.
Bien, ahí estamos. Acelerado, puedes mirar al final para ver cómo funciona. Solo estoy haciendo una prueba aquí. CTRL; cambiará a azul. Si vuelve a 6/26, cambiará a rojo. Y si es hoy, no está funcionando. Eso es correcto porque esto es lo que voy a hacer. Mi cuarta regla, el verde llegó hoy o ayer, solo la usaré como predeterminada. Si ninguna de estas otras tres reglas es cierta, entonces será verde que me dé una regla menos con la que tengo que lidiar aquí. Bien.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2105.xlsx
