Tieni traccia delle modifiche nelle celle delle formule di Excel. Puoi mostrare quali elementi sono appena cambiati a seguito della modifica di determinate celle di input?
Guarda un video
- Tenere traccia delle modifiche in Excel è un po 'bizzarro.
- L'obiettivo è tenere traccia delle modifiche alle celle della formula in Excel.
- Salva con nome per salvare la cartella di lavoro come XLSM.
- Cambia protezione macro.
- Registra una macro per capire il codice per impostare la formattazione condizionale per i numeri diversi da 2.
- Scegli la formattazione che desideri.
- Registra un'altra macro per imparare a rimuovere CF dal foglio di lavoro.
- Nella macro, aggiungi un ciclo per ogni foglio di lavoro.
- Aggiungi un'istruzione IF per impedirne l'esecuzione su Title.
- Aggiungi un ciclo per controllare ogni cella della formula.
- Aggiungi la formattazione condizionale per vedere se viene eseguita la macro del valore della cella al momento.
- Torna a Excel.
- Aggiungi una forma. Assegna la macro alla forma.
- Fare clic sulla forma per eseguire la macro.
- Suggerimento bonus: trascinamento di un modulo VBA in una nuova cartella di lavoro.
Trascrizione del video
Impara Excel da Podcast, episodio 2059: Excel rileva le modifiche (nei risultati delle formule)
Ehi, bentornato al netcast, sono Bill Jelen. Domanda di oggi inviata da Montreal sui cambiamenti di traccia. Tieni traccia delle modifiche, va bene. Quindi ecco cosa abbiamo. Abbiamo 4 celle di input e un intero gruppo di celle di formula che si basano su queste celle di input. E se dovessi accenderlo, tornerò alla scheda Revisione, attiverò Evidenzia modifiche, Tieni traccia delle modifiche durante la modifica, fai clic su OK, va bene. E mi hanno avvertito che devono salvare la cartella di lavoro e che le macro non possono essere utilizzate nelle cartelle di lavoro condivise. Lo sai? Questo è il problema quando si tengono traccia delle modifiche, condividono la cartella di lavoro e ci sono un sacco di cose che non possono accadere nelle cartelle di lavoro condivise, come le macro e un sacco di altre cose. Ma diamo solo un'occhiata a come funzionano le modifiche alla traccia in Excel oggi.
Prendiamo questo 2 e cambiamo da 2 a 22, e prendiamo questo 4 e lo cambiamo da 4 a 44. Va bene, e vedete, quello che hanno notato nelle modifiche alla traccia è che queste due celle sono cambiate, va bene, quei triangoli viola sono l'attuale traccia cambia. Tutte queste cose rosse, che non accadono, ma ho appena illustrato che tutti questi globuli rossi stanno cambiando e tenere traccia dei cambiamenti non dice nulla su questi cambiamenti, va bene? Quindi, dice solo, queste due cellule sono state cambiate ma anche tutte queste altre cellule sono state cambiate. E quindi la domanda da Montreal è: c'è un modo per fare in modo che i cambiamenti di traccia ci mostrino effettivamente tutto ciò che cambia, non solo queste celle di input sono cambiate?
Va bene, quindi, la prima cosa che dobbiamo fare è disattivare il rilevamento modifiche integrato di Excel. E poi, c'è un modo in cui possiamo ottenere: possiamo costruire il nostro sistema di modifiche alla traccia che ci permetterà di vedere tutte le celle della formula che sono cambiate? Va bene, quindi il passaggio 1 e questo passaggio è il passaggio più importante, non saltare questo. Guarda il tuo file, il tuo file si chiama qualcosa XLSX, devi salvarlo: File, Salva con nome, Come cartella di lavoro abilitata per macro, o niente di tutto questo funzionerà. Devi fare clic con il pulsante destro del mouse, personalizzare la barra multifunzione, attivare lo sviluppatore, una volta arrivato a sviluppatore, andare su sicurezza macro, cambiare da questa impostazione - quella che dice che non lasceremo eseguire le macro o non lo diremo nemmeno tu che sono lì per questa impostazione. Devi fare questi due passaggi. Ho già fatto questi due passaggi. Vivo ogni giorno con quei due passaggi.Già risolto, ma se non conosci le macro, questo è nuovo per te. E poi, dobbiamo capire che tipo di formattazione desideri. Va bene, quindi sceglierò solo alcune celle qui, registrerò una macro chiamata HowToCFRed, non assegnerò un tasto di scelta rapida perché non verrà mai più eseguita. Sto solo registrando il codice per capire come funziona la formattazione condizionale. E entreremo in Home, Formattazione condizionale, Evidenzia celle che non sono uguali a - Quindi, più regole, Formatta celle non uguali a - Vedi quello? Non è nell'elenco a discesa originale, ma se entri qui, non è uguale a 2, quindi scegli il formato. Questa è la parte importante. Quindi sceglierò uno sfondo rosso. Scegli il colore che vuoi qui, va bene? Vai anche su Altri colori, scegli un altro rosso,vai in Custom, scegli un altro rosso, va bene? Questa è la bellezza del registratore di macro, ci daranno del rosso perfetto per te o del blu o qualunque cosa tu voglia. Va bene, fai clic su OK. E poi smetteremo di registrare, va bene. Di nuovo, il punto centrale è solo vedere qual è il codice per i formati condizionali.
Vado a Macro, Come formattare il rosso e modificare. Va bene, quindi ecco le parti importanti di questo codice. Vedo che stanno aggiungendo un formato condizionale usando xlNotEqual e stiamo dicendo che non è uguale a 2. E poi stiamo cambiando l'interno della cella in quel colore.
Va bene, devo anche capire come eliminare tutta la formattazione condizionale sul foglio. Quindi, torna a Excel, Registra un'altra macro, Come eliminare tutto il condizionale, OK. Vieni qui nella scheda Home, vai a Formattazione condizionale, Cancella regola dall'intero foglio, Interrompi registrazione e andremo a dare un'occhiata a quel codice. Fantastico, è una macro di una riga. E mi piace anche che il modo in cui lo fanno per l'intero foglio si riferisca solo alle celle. Quindi, in altre parole, tutte le celle sul foglio attivo.
Now, I need to make this macro, the recorded macro, a little bit more generic. And I've written lots of books about how to do VBA in Excel and I've done videos on how to do VBA in Excel, and here's the simple thing: you need to be able to record a macro like this but then, add about five or six lines in order to be able to make the macro generic enough.
And I'm going to talk about those lines, alright. So the first thing I want to do is I want to say, I want to go through the active workbook, go through all of the worksheets. So for each worksheet, WS is the object variable, I'll go through all the worksheets. And the person from Montreal said, “Hey, there is one sheet that I don't want to have this happen on.” So, if the WS.Name, with the worksheet dot name, is not equal to Title then we're going to do the code in the macro. Here's the sheet name: .Cells.FormatConditions.Delete. So, we're going to go through each individual of the sheet except for the title and delete all the format conditions, then we're going to go through each cell in the sheet but not all the cells, just the cells that have formulas. If it doesn't have a formula then I don't need to format it because it's not going to change. Cell.FormatConditions.Add, this is directly from the macro although the recorded macro said Selection - I don't want to have to select it so I'm just going to say Cell, that's each individual cell. We're going to use the xlNotEqual and instead of Formula:=”=”2 which is what the recorded code did right there, I've concatenated whatever's in that cell. So checking to see if it's not equal to the current value. So if the cell currently has 2, we're saying not equal to 2. If the cell currently has 16.5, we're saying not equal to 16.5. And then the rest of this is just straight recorded macro, recorded macro, recorded macro, recorded macro. All of that is from a recorded macro. Finish this If with an End If. Finish this For with a Next WS.
Alright, so I have a macro called ApplyCF. Go back to Excel, add a shape. Easy to have a shape here: Insert, I always choose a rounded rectangle, type Reset To Current Values. We’ll apply Home, the center, and the center make it a little bit larger. I love the glow. I suppose you think it's silly seeing it's not there, the glow, the setting I like isn't there so I always go to Page Layout and Effects and choose that second one. And then when I go back to the format, I can choose one that actually has a little bit of glow. To me, I think it looks cool, I think it's worth it. Right-click, Assign Macro and say ApplyCF, click OK. Alright, and then what this will do is when I click it, it’ll go through all of these sheets, find all of the formula cells and set up a conditional formatting that says: If these cells not equal to 7, change the color, alright? That's it. It's that fast it, happened that fast. BAM! It's done. And now, watch if I change this one to 11, all of those cells just changed. Now if it goes back to the 1, ahh, the colors changed. So, whatever the value was, when we change- if I change this cell, all of those cells change. If I change this cell, all of those cells change. If I change this cell, all of those cells change.
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.
Ehi, voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2059.xlsm
