Seguimiento de cambios en celdas de fórmulas de Excel. ¿Puede mostrar qué elementos acaban de cambiar como resultado de cambiar ciertas celdas de entrada?
Ver video
- Hacer un seguimiento de los cambios en Excel es un poco extraño.
- El objetivo es rastrear qué celdas de fórmula en Excel cambian.
- Guardar como para guardar el libro de trabajo como XLSM.
- Cambiar seguridad macro.
- Grabe una macro para averiguar el código para configurar el formato condicional para números que no sean iguales a 2.
- Elija el formato que desee.
- Grabe otra macro para aprender cómo eliminar CF de la hoja de trabajo.
- En la macro, agregue un bucle para cada hoja de trabajo.
- Agregue una instrucción IF para evitar que se ejecute en el título.
- Agregue un bucle para verificar cada celda de fórmula.
- Agregue formato condicional para ver si el valor de celda en el momento en que se ejecuta la macro.
- Regrese a Excel.
- Agrega una forma. Asigne la macro a la forma.
- Haga clic en la forma para ejecutar la macro.
- Consejo adicional: arrastrar un módulo VBA a un nuevo libro de trabajo.
Transcripción del video
Aprenda Excel de Podcast, Episodio 2059: Seguimiento de cambios de Excel (en los resultados de la fórmula)
Oye, bienvenido de nuevo al netcast, soy Bill Jelen. La pregunta de hoy enviada desde Montreal sobre los cambios de pista. Seguimiento de cambios, está bien. Así que esto es lo que tenemos. Tenemos 4 celdas de entrada y un montón de celdas de fórmula que se basan en estas celdas de entrada. Y si lo enciende, volveré a la pestaña Revisar, activar Resaltar cambios, Seguir los cambios durante la edición, hacer clic en Aceptar, está bien. Y me advirtieron que tienen que guardar el libro de trabajo y que las macros no se pueden usar en libros de trabajo compartidos. ¿Tú lo sabes? Este es el problema cuando realiza un seguimiento de los cambios, ellos comparten el libro de trabajo y hay un montón de cosas que no pueden suceder en los libros de trabajo compartidos, como macros y muchas otras cosas. Pero echemos un vistazo a cómo funciona el seguimiento de cambios en Excel hoy.
Tomemos este 2 y cambiemos de 2 a 22, y tomemos este 4 y cámbielo de 4 a 44. Muy bien, y verá, lo que han notado en los cambios de pista es que estas dos celdas cambiaron, está bien, esos triángulos morados son la pista real cambia. Todas estas cosas rojas, eso no sucede, pero acabo de ilustrar que todos estos glóbulos rojos están cambiando y los cambios de seguimiento no dicen nada sobre estos cambios, ¿de acuerdo? Entonces, simplemente dice, estas dos celdas se cambiaron, pero todas estas otras celdas también se cambiaron. Entonces, la pregunta de Montreal es, ¿hay alguna manera de que los cambios de seguimiento realmente nos muestren todo lo que cambia, no solo estas celdas de entrada han cambiado?
Muy bien, entonces, lo primero que tenemos que hacer es desactivar el Control de cambios integrado en Excel. Y luego, ¿hay alguna manera de que podamos construir nuestro propio sistema de seguimiento de cambios que nos permitirá ver todas las celdas de fórmula que cambiaron? Muy bien, entonces el Paso 1 y este paso es el paso más importante, no omita esto. Mire su archivo, su archivo se llama algo XLSX, debe guardar esto: Archivo, Guardar como, Como un libro de trabajo habilitado para macros, o nada de esto funcionará. Debe hacer clic con el botón derecho, personalizar la cinta, activar Desarrollador, una vez que llegue a Desarrollador, vaya a Seguridad de macros, cambie esta configuración, la que dice que no vamos a dejar que las macros se ejecuten o que ni siquiera lo vamos a decir. usted que están allí en este escenario. Tienes que seguir esos dos pasos. Ya hice esos dos pasos. Vivo todos los días con esos dos pasos.Ya está arreglado, pero si es nuevo en macros, esto es nuevo para usted. Y luego, debemos averiguar qué tipo de formato desea. Muy bien, solo voy a elegir algunas celdas aquí, voy a grabar una macro que se llama HowToCFRed, no la voy a asignar a una tecla de acceso directo porque esto nunca se ejecutará nuevamente. Solo estoy grabando código para descubrir cómo funciona el formato condicional. Y entraremos en Inicio, Formato condicional, Resaltar celdas que no son iguales a - Entonces, Más reglas, Formato de celdas no igual a - ¿Ves eso? No está en el menú desplegable original, pero si vienes aquí, no es igual a 2 y luego elige el formato. Esta es la parte importante. Entonces voy a elegir un fondo rojo. Elige el color que quieras aquí, ¿de acuerdo? Incluso vaya a Más colores, elija otro rojo,entra en Custom, elige otro rojo, ¿de acuerdo? Esa es la belleza de la grabadora de macros, nos darán un rojo perfecto para ti o azul o lo que sea que quieras. Muy bien, haz clic en Aceptar. Y luego, vamos a dejar de grabar, está bien. Una vez más, el objetivo de esto es solo ver cuál es el código para los formatos condicionales.
Voy a Macros, Cómo dar formato condicional al rojo y editar. Muy bien, estas son las partes importantes de este código. Puedo ver que están agregando un formato condicional usando xlNotEqual y es difícil citarlo para que no sea igual a 2. Y luego cambiamos el interior de la celda a ese color.
Muy bien, también necesito averiguar cómo eliminar todo el formato condicional en la hoja. Entonces, volvamos a Excel, Grabe otra macro, Cómo eliminar todo condicional, OK. Venga aquí a la pestaña Inicio, vaya a Formato condicional, Borrar regla de toda la hoja, Detener grabación y veremos ese código. Genial, es una macro de una línea. E incluso me gusta que la forma en que lo hacen para toda la hoja es simplemente referirse a celdas. En otras palabras, todas las celdas de la hoja activa.
Ahora, necesito hacer esta macro, la macro grabada, un poco más genérica. Y he escrito muchos libros sobre cómo hacer VBA en Excel y he hecho videos sobre cómo hacer VBA en Excel, y esta es la cosa simple: necesita poder grabar una macro como esta pero luego, agregue unas cinco o seis líneas para poder hacer que la macro sea lo suficientemente genérica.
Y voy a hablar de esas líneas, está bien. Entonces, lo primero que quiero hacer es decir, quiero revisar el libro activo, revisar todas las hojas de trabajo. Entonces, para cada hoja de trabajo, WS es la variable de objeto, revisaré todas las hojas de trabajo. Y la persona de Montreal dijo: "Oye, hay una hoja en la que no quiero que esto suceda". Entonces, si WS.Name, con el nombre del punto de la hoja de trabajo, no es igual al Título, entonces vamos a hacer el código en la macro. Aquí está el nombre de la hoja: .Cells.FormatConditions.Delete. Entonces, vamos a revisar cada individuo de la hoja excepto el título y eliminar todas las condiciones de formato, luego vamos a revisar cada celda de la hoja, pero no todas las celdas, solo las celdas que tienen fórmulas. . Si no tiene fórmula, entonces noNo es necesario formatearlo porque no va a cambiar. Cell.FormatConditions.Add, esto es directamente de la macro, aunque la macro grabada decía Selección: no quiero tener que seleccionarla, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16.5, estamos diciendo que no es igual a 16.5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS. no va a cambiar. Cell.FormatConditions.Add, esto es directamente de la macro, aunque la macro grabada decía Selección: no quiero tener que seleccionarla, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16,5, estamos diciendo que no es igual a 16,5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.no va a cambiar. Cell.FormatConditions.Add, esto es directamente de la macro, aunque la macro grabada decía Selección: no quiero tener que seleccionarla, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16.5, estamos diciendo que no es igual a 16.5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.esto es directamente de la macro, aunque la macro grabada decía Selección: no quiero tener que seleccionarla, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16,5, estamos diciendo que no es igual a 16,5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.esto es directamente de la macro, aunque la macro grabada decía Selección: no quiero tener que seleccionarla, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16,5, estamos diciendo que no es igual a 16,5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.No quiero tener que seleccionarlo, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16,5, estamos diciendo que no es igual a 16,5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.No quiero tener que seleccionarlo, así que solo voy a decir Celda, que es cada celda individual. Vamos a usar xlNotEqual y en lugar de Formula: = ”=” 2, que es lo que hizo el código grabado allí mismo, concatené lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16,5, estamos diciendo que no es igual a 16,5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.= ”=” 2 que es lo que hizo el código grabado allí mismo, he concatenado todo lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16.5, estamos diciendo que no es igual a 16.5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con un End If. Termine esto con un siguiente WS.= ”=” 2 que es lo que hizo el código grabado allí mismo, he concatenado todo lo que hay en esa celda. Entonces, verificando si no es igual al valor actual. Entonces, si la celda tiene actualmente 2, estamos diciendo que no es igual a 2. Si la celda tiene actualmente 16,5, estamos diciendo que no es igual a 16,5. Y luego el resto de esto es simplemente macro grabada, macro grabada, macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.macro grabada, macro grabada. Todo eso es de una macro grabada. Termine este If con End If. Termine esto con un siguiente WS.
Muy bien, tengo una macro llamada ApplyCF. Regrese a Excel, agregue una forma. Es fácil tener una forma aquí: Insertar, siempre elijo un rectángulo redondeado, escriba Restablecer a valores actuales. Aplicaremos Home, el centro y el centro lo haremos un poco más grande. Amo el resplandor. Supongo que piensas que es una tontería ver que no está ahí, el brillo, la configuración que me gusta no está ahí, así que siempre voy a Diseño de página y efectos y elijo el segundo. Y luego, cuando vuelvo al formato, puedo elegir uno que realmente tenga un poco de brillo. Para mí, creo que se ve genial, creo que vale la pena. Haga clic derecho, Asignar macro y diga ApplyCF, haga clic en Aceptar. Muy bien, y luego lo que hará es que cuando haga clic en él, revisará todas estas hojas, buscará todas las celdas de fórmula y configurará un formato condicional que dice: Si estas celdas no son iguales a 7,cambia el color, ¿de acuerdo? Eso es todo. Es así de rápido, sucedió así de rápido. ¡BAM! Está hecho. Y ahora, fíjense si cambio este a 11, todas esas celdas simplemente cambiaron. Ahora si vuelve al 1, ah, los colores cambiaron. Entonces, sea cual sea el valor, cuando cambiamos, si cambio esta celda, todas esas celdas cambian. Si cambio esta celda, todas esas celdas cambian. Si cambio esta celda, todas esas celdas cambian.todas esas células cambian.todas esas células cambian.
Alright, now this is the new normal. Now from here, I want to track again. So I Reset To Current Values and then if I change this one to a 3, those sales change. Oh, by the way, these cells back here and these other sheets also changed in response to this. Track changes in Excel as it exists? Yeah, it's really lame. It doesn't show you the things that changed and having to show the workbook is a horrible, horrible thing. But with this simple, simple little macro, it works.
Alright now, the question said, alright, so if this is working how do I now get this to work in my other workbook? So I have some other workbook and I want to copy this over. Alright, so this is a great little bonus tip here. I'll create a brand new workbook and we'll put some stuff in here and I'll have a couple of formulas, and put a cell up there, alright. So we changed that cell, those 4 cells are all formula cells. Now if I want this workbook, Book2, to also have the code from podcast 2059, well I could retype it all again but that would be silly. So we come here to the Developer tab, and go to Visual Basic. And I want to make sure that I can see Book2 and then I can see Podcast 2059. I simply take that module and drag it and drop it on Book2, right there. And now, that code is also in Book2. Coming back to Book2, just add a shape, right click, Assign Macro, click OK. Alright, it works. And then 3, see, we've now applied that setting to this workbook.
Great question. Great question sent in from Montreal. And in this case, great question that my initial reaction is, well yeah, you're right. Track changes is horrible in Excel. And I wonder if I could create something that would actually track the changes. What are the downsides here and I'm sure I'm going to hear about this in the YouTube comments. If you had 10,000 formula cells, well now, all of these conditional formattings are going to be volatile, the things going to slow down, too many Excel format errors. Yeah, I can see all that but, you know, for a nice small workbook 5,6, 7 sheets, maybe 50 rows per sheet, I would think that this has some chance- some chance are working.
Okay, episode recap: Track changes in Excel, it's a little bizarre especially because they share the workbook. Our goal is to track what formula cells in Excel change. You have to save the workbook as XLSM, change your Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers and not equal to 2, that’s just to figure out what red you want to use. Choose the formatting you want, Record another macro to learn how to remove conditional formatting from the worksheet. And then, to that macro that we recorded the first one, add a loop for each worksheet, an IF statement to prevent it from running on the title sheet, then a loop to check each formula cell. Add conditional formatting to each cell that says, if this CELL.VALUE is not equal to the value at the time that it ran, then we're going to highlight the things. Go back to Excel, assign a shape, add a shape, assign a macro to the shape, click the shape to run the macro. And I also showed you the bonus tip: dragging a VBA module to a new workbook.
Bueno, oye, quiero darte las gracias por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2059.xlsm