Urmăriți modificările din celulele cu formula Excel. Puteți arăta ce elemente tocmai s-au schimbat ca urmare a modificării anumitor celule de intrare?
Urmăriți videoclipul
- Urmărirea modificărilor în Excel este puțin bizară.
- Scopul este de a urmări ce formulă modifică celulele din Excel.
- Salvați ca pentru a salva registrul de lucru ca XLSM.
- Schimbați securitatea macro.
- Înregistrați o macro pentru a afla codul pentru a configura formatarea condiționată pentru numere care nu sunt egale cu 2.
- Alegeți formatarea dorită.
- Înregistrați o altă macrocomandă pentru a afla cum să eliminați CF din foaia de lucru.
- În macro, adăugați o buclă pentru fiecare foaie de lucru.
- Adăugați o instrucțiune IF pentru a preveni rularea acesteia pe Title.
- Adăugați o buclă pentru a verifica fiecare celulă formulă.
- Adăugați Formatare condiționată pentru a vedea dacă se execută valoarea celulei la momentul macro.
- Reveniți la Excel.
- Adăugați o formă. Atribuiți macrocomanda la formă.
- Faceți clic pe Formă pentru a rula macro-ul.
- Sfat bonus: Tragerea unui modul VBA într-un nou registru de lucru.
Transcriere video
Aflați Excel din Podcast, Episodul 2059: Modificări ale urmăririi Excel (în rezultatele Formulei)
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi trimisă din Montreal despre schimbările de cale. Urmăriți modificările, bine. Iată deci ce avem. Avem 4 celule de intrare și o grămadă de celule Formula care se bazează pe aceste celule de intrare. Și dacă aș activa, mă voi întoarce la fila Revizuire, voi activa Evidențierea modificărilor, urmăresc modificările în timp ce editați, faceți clic pe OK, bine. Și m-au avertizat că trebuie să salveze registrul de lucru și că macrocomenzile nu pot fi utilizate în registrele de lucru partajate. Tu stii asta? Aceasta este problema când urmăriți modificările, acestea împărtășesc registrul de lucru și există o grămadă de lucruri care nu se pot întâmpla în registrele de lucru partajate, știți, cum ar fi macrocomenzile și o grămadă de alte lucruri. Dar să aruncăm o privire asupra modului în care funcționează schimbările de urmărire în Excel astăzi.
Să luăm acest 2 și să schimbăm de la 2 la 22 și să luăm acest 4 și să-l schimbăm de la 4 la 44. Bine, și vedeți, ceea ce au observat în modificările de cale este că aceste două celule s-au schimbat, bine, acele triunghiuri violete sunt pista reală se schimbă. Toate aceste lucruri roșii, asta nu se întâmplă, dar tocmai am ilustrat că toate aceste celule roșii se schimbă și urmărirea modificărilor nu spune nimic despre aceste modificări, bine? Deci, spune doar, aceste două celule au fost schimbate, dar toate celelalte celule au fost, de asemenea, schimbate. Și atunci întrebarea de la Montreal este, există o modalitate de a avea modificări de urmărire care să ne arate de fapt tot ce se schimbă, nu doar aceste celule de intrare s-au schimbat?
Bine, deci, primul lucru pe care trebuie să-l facem este să dezactivăm modificările de urmărire încorporate în Excel. Și apoi, există o modalitate pe care o putem obține - putem construi propriul nostru sistem de schimbări de urmărire care să ne permită să vedem toate celulele formulei care s-au schimbat? Bine, deci Pasul 1 și acest pas este cel mai important pas, nu treceți peste acest lucru. Uitați-vă la fișierul dvs., fișierul dvs. se numește ceva XLSX, trebuie să salvați acest lucru: Fișier, Salvați ca, Ca registru de lucru activat pentru macro, sau nimic din toate acestea nu va funcționa. Trebuie să faceți clic dreapta, să personalizați panglica, să activați Developer, odată ce ajungeți la Developer, accesați Macro Security, schimbați din această setare - cea care spune că nu vom lăsa macro-urile să ruleze sau nici măcar nu vom spune că sunteți acolo la această setare. Trebuie să faci acești doi pași. Am făcut deja acești doi pași. Trăiesc în fiecare zi cu acești doi pași.Deja a fost remediat, dar dacă sunteți nou la macrocomenzi, acest lucru este nou pentru dvs. Și apoi, trebuie să ne dăm seama ce tip de formatare doriți. Bine, așa că voi alege doar câteva celule aici, voi înregistra o macro care se numește HowToCFRed, nu voi atribui o tastă de comandă rapidă, deoarece aceasta nu va mai rula niciodată. Înregistrez doar cod pentru a afla cum funcționează formatarea condiționată. Și vom intra în Acasă, Formatare condiționată, Evidențierea celulelor care nu sunt egale cu - Deci, Mai multe reguli, Formatarea celulelor nu egale cu - Vezi asta? Nu se află în meniul derulant original, dar dacă intrați aici, nu este egal cu 2, apoi alegeți formatul. Aceasta este partea importantă. Așa că voi alege un fundal roșu. Alegeți ce culoare doriți aici, bine? Mergeți chiar la Mai multe culori, alegeți alt roșu,mergeți la Custom, alegeți alt roșu, bine? Aceasta este frumusețea Macro Recorderului, ne vor oferi un roșu perfect pentru dvs. sau albastru sau orice doriți. Bine, faceți clic pe OK. Și apoi, vom opri înregistrarea, bine. Din nou, tot acest punct este doar să vedem care este codul pentru formatele condiționate.
Mă duc la Macrocomenzi, Cum să formatează condiționat roșu și să editez. Bine, deci iată părțile importante ale acestui cod. Văd că adaugă un format condițional folosind xlNotEqual și cităm cu greu să nu fie egal cu 2. Și apoi schimbăm interiorul celulei cu acea culoare.
În regulă, trebuie să îmi dau seama cum să șterg toate formatările condiționate de pe foaie. Deci, înapoi la Excel, Înregistrați o altă macro, Cum să ștergeți toate condițional, OK. Vino aici la fila Acasă, accesează Formatarea condiționată, Șterge regula din întreaga foaie, Oprește înregistrarea și vom analiza codul respectiv. Grozav, este o macrocomandă cu o singură linie. Și chiar îmi place aici că modul în care o fac pentru întreaga foaie se referă doar la celule. Deci, cu alte cuvinte, toate celulele de pe foaia activă.
Acum, trebuie să fac această macro, macro-ul înregistrat, puțin mai generic. Și am scris o mulțime de cărți despre cum să faci VBA în Excel și am făcut videoclipuri despre cum să faci VBA în Excel și iată ce este simplu: trebuie să poți înregistra o macro de acest gen, dar apoi, adaugă aproximativ cinci sau șase linii pentru a putea face macro suficient de generic.
Și voi vorbi despre acele linii, bine. Așadar, primul lucru pe care vreau să-l fac este să vreau să spun, vreau să trec prin registrul de lucru activ, să trec prin toate foile de lucru. Deci, pentru fiecare foaie de lucru, WS este variabila obiect, voi parcurge toate foile de lucru. Iar cel din Montreal a spus: „Hei, există o foaie pe care nu vreau să se întâmple asta”. Deci, dacă WS.Name, cu numele punctului foii de lucru, nu este egal cu Title, vom face codul în macro. Iată numele foii: .Cells.FormatConditions.Delete. Deci, vom parcurge fiecare fișă individuală, cu excepția titlului și vom șterge toate condițiile de format, apoi vom parcurge fiecare celulă din foaie, dar nu toate celulele, ci doar celulele care au formule . Dacă nu are o formulă, atunci nuNu trebuie să-l formatați, deoarece nu se va schimba. Cell.FormatConditions.Add, aceasta este direct din macro, deși macro-ul înregistrat a spus Selecție - Nu vreau să trebuiască să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS. Nu se va schimba. Cell.FormatConditions.Add, aceasta este direct din macro, deși macro-ul înregistrat a spus Selecție - Nu vreau să trebuiască să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.Nu se va schimba. Cell.FormatConditions.Add, aceasta este direct din macro, deși macro-ul înregistrat a spus Selecție - Nu vreau să trebuiască să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci verificarea pentru a vedea dacă nu este egală cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.aceasta este direct din macro, deși macro-ul înregistrat a spus Selecție - Nu vreau să trebuiască să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.aceasta este direct din macro, deși macro-ul înregistrat a spus Selecție - Nu vreau să trebuiască să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.Nu vreau să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.Nu vreau să o selectez, așa că voi spune doar Cell, care este fiecare celulă individuală. Vom folosi xlNotEqual și în loc de Formula: = ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.= ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.= ”=” 2, ceea ce a făcut codul înregistrat chiar acolo, am concatenat orice este în celula respectivă. Deci, verificând dacă nu este egal cu valoarea curentă. Deci, dacă celula are în prezent 2, spunem că nu este egal cu 2. Dacă celula are în prezent 16,5, spunem că nu este egală cu 16,5. Și apoi restul este doar macro înregistrat direct, macro înregistrat, macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.macro înregistrat, macro înregistrat. Toate acestea provin dintr-o macro înregistrată. Finalizați acest If cu un End If. Finalizați acest lucru cu un Next WS.
Bine, așa că am o macro numită ApplyCF. Reveniți la Excel, adăugați o formă. Ușor să aveți o formă aici: Inserați, aleg întotdeauna un dreptunghi rotunjit, tastați Resetare la valorile curente. Vom aplica Acasă, centrul și centrul îl fac puțin mai mare. Îmi place strălucirea. Presupun că crezi că este o prostie, văzând că nu există, strălucirea, setarea care îmi place nu există, așa că merg mereu la Aspect pagină și Efecte și o aleg pe a doua. Și atunci când mă întorc la format, pot alege unul care are de fapt un pic de strălucire. Pentru mine, cred că arată mișto, cred că merită. Faceți clic dreapta, Alocați macro și spuneți ApplyCF, faceți clic pe OK. Bine, și atunci ce va face acest lucru este când fac clic pe ea, va trece prin toate aceste foi, va găsi toate celulele de formulă și va configura o formatare condiționată care spune: Dacă aceste celule nu sunt egale cu 7,schimbați culoarea, bine? Asta este. Este atât de repede, sa întâmplat atât de repede. BAM! Este gata. Și acum, uitați-vă dacă îl schimb pe acesta în 11, toate acele celule tocmai s-au schimbat. Acum, dacă se întoarce la 1, ahh, culorile s-au schimbat. Deci, oricare ar fi valoarea, când ne schimbăm - dacă schimb această celulă, toate celulele respective se schimbă. Dacă schimb această celulă, toate aceste celule se schimbă. Dacă schimb această celulă, toate aceste celule se schimbă.toate aceste celule se schimbă.toate aceste celule se schimbă.
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.
Ei bine, vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2059.xlsm
